Import Data EXCEL ke TABEL - PHP, Javascript

Nah kali ini gw mau sharing buat temen2 yang mau import data dari excel ke dalam table, data ini cuma di tampung di dalam table dan tidak di simpan di database. Jika teman2 ingin menyimpan nya ke database silahkan di improve sesuai alur kodingan masing2. Gw sih biasanya pake CI, nanti gw share yah.

Sebelum nya download dulu file yang di butuhkan disini

Oke langsung ajah yah kita ke kodingan nya.

• Pertama seperti biasa kita masukin dulu javscript nya seperti berikut :

    <script src="import/modernizr-2.8.3.js"></script>
    <script src="import/jquery-1.11.3.min.js"></script>
    <script src="import/jquery-ui.min.js"></script>

    <script type="text/javascript" src="import/infragistics.core.js"></script>
    <script type="text/javascript" src="import/infragistics.lob.js"></script>

    <script type="text/javascript" src="import/infragistics.ext_core.js"></script>
    <script type="text/javascript" src="import/infragistics.ext_collections.js"></script>
    <script type="text/javascript" src="import/infragistics.ext_text.js"></script>
    <script type="text/javascript" src="import/infragistics.ext_io.js"></script>
    <script type="text/javascript" src="import/infragistics.ext_ui.js"></script>
    <script type="text/javascript" src="import/infragistics.documents.core_core.js"></script>
    <script type="text/javascript" src="import/infragistics.ext_collectionsextended.js"></script>
    <script type="text/javascript" src="import/infragistics.excel_core.js"></script>
    <script type="text/javascript" src="import/infragistics.ext_threading.js"></script>
    <script type="text/javascript" src="import/infragistics.ext_web.js"></script>
    <script type="text/javascript" src="import/infragistics.xml.js"></script>
    <script type="text/javascript" src="import/infragistics.documents.core_openxml.js"></script>
    <script type="text/javascript" src="import/infragistics.excel_serialization_openxml.js"></script>

• Lanjut kita masukin kodingan PHP nya : 

    <div>
        <ol>
            <li>Download this <a href="https://www.igniteui.com/HtmlSamples/javascript-excel-library/report.xlsx" download>sample  Excel file</a></li>
            <li>Click Choose File/Browse button below and pick the sample Excel file or another excel file.</li>
        </ol>
        <input type="file" id="input" accept="application/vnd.ms-excel, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"/>
        <div id="result"></div>
<br>
<br>
        <table id="grid1">
</table>
    </div>

• Nah baru sekarang kita masukin function javascript nya deh :

    <script>

        $(function () {
            $("#input").on("change", function () {
                var excelFile,
                    fileReader = new FileReader();

                $("#result").hide();

                fileReader.onload = function (e) {
                    var buffer = new Uint8Array(fileReader.result);

                    $.ig.excel.Workbook.load(buffer, function (workbook) {
                        var column, row, newRow, cellValue, columnIndex, i,
                            worksheet = workbook.worksheets(0),
                            columnsNumber = 3,
                            gridColumns = [],
                            data = [],
                            worksheetRowsCount;

                        for (columnIndex = 0; columnIndex < columnsNumber; columnIndex++) {
                            column = worksheet.rows(0).getCellText(columnIndex);
                            gridColumns.push({ headerText: column, key: column });
                        }

                        for (i = 1, worksheetRowsCount = worksheet.rows().count() ; i < worksheetRowsCount; i++) {
                            newRow = {};
                            row = worksheet.rows(i);

                            for (columnIndex = 0; columnIndex < columnsNumber; columnIndex++) {
                                cellValue = row.getCellText(columnIndex).toString();
        if(columnIndex==1){
         kode = cellValue;
         newRow[gridColumns[columnIndex].key] = '<input type="text" value="'+cellValue.toString()+'"/> ' ;
        }else if(columnIndex==2){
         newRow[gridColumns[columnIndex].key] = '<input type="text" value="'+cellValue.toString()+'"/> ' ;
        }else if(columnIndex==3){
         newRow[gridColumns[columnIndex].key] = '<input type="text" value="'+cellValue.toString()+'"/> ' ;
        }else{
         newRow[gridColumns[columnIndex].key] = cellValue.toString() ;
        }
                            }
                            data.push(newRow);
                        }

                        createGrid(data, gridColumns);
                    }, function (error) {
                        $("#result").text("The excel file is corrupted.");
                        $("#result").show(1000);
                    });
                }

                if (this.files.length > 0) {
                    excelFile = this.files[0];
                    if (excelFile.type === "application/vnd.ms-excel" || excelFile.type === "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" || (excelFile.type === "" && (excelFile.name.endsWith("xls") || excelFile.name.endsWith("xlsx")))) {
                        fileReader.readAsArrayBuffer(excelFile);
                    } else {
                        $("#result").text("The format of the file you have selected is not supported. Please select a valid Excel file ('.xls, *.xlsx').");
                        $("#result").show(1000);
                    }
                }

            })
        });

        function createGrid(data, gridColumns) {
            if ($("#grid1").data("igGrid") !== undefined) {
                $("#grid1").igGrid("destroy");
            }

            $("#grid1").igGrid({
                columns: gridColumns,
                autoGenerateColumns: true,
                dataSource: data,
                width: "30%"
            });
        }
    </script>

Jika sudah buka file tadi di browser, maka tampilannya akan seperti ini :

Nah pilih "choose file" dan cari file import.xlsx lalu open. Maka akan tampil seperti ini :

Oke sampe situ dulu yah, jika ada yang masih bingung bisa komentar dibawah ini.