EXPORT/IMPORT EXCEL WITH PHP

HOW TO EXPORT EXCEL

• It is very easy to export excel in PHP.
• There are different PHPEXCEL libraries available which can export and import excel.
• We can export excel with a simple table and we can modify with giving style.

STRUCTUREOFPHPEXCEL

HOW TO EXPORT EXCEL WITH TABLE

1. First of all add below headers top of the file.

•  header(“Content-Type: application/xls”);
•  header(“Content-Disposition: attachment; filename=filename.xls”); // here you can modify filename which is use at the time of the download.
•  header(“Pragma: no-cache”);
•  header(“Expires: 0”);

2. Make a simple table with data.

For ex.

<table id="list" border="1" align="center">
 <thead>
    <tr>
       <th align=”right” colspan=”1”>Id</th>
	 <th align=”left”>Name</th>
    </tr>
</thead>
 <tbody>
 <tr>
      <td bgcolor="#fff" colspan=”1”>1</td>
     <td style=”text-align:right” bgcolor="#fff"   rowspan=”1”> Test</td>		
    </tr>
</tbody>
<table>

• You can give inline basic style
  

1. Alignment

• Align = center/left/right

2. Center

• vertical-align : middle

3. Text alignment

• text-align:center

4.  Background Color

•   bgcolor=””

5. Colspan and rowspan you can modify excel

6. Width & Height (works in % type of value)

• Width = “100%”
• Height = “50%”

HOW TO EXPORT EXCEL WITH PHPEXCEL

1. Download PHPEXCEL from the link below.<br>https://github.com/PHPOffice/PHPExcel
2. Folder structure.

3. Create an object of PHPEXCEL.

$objPHPExcel = new Spreadsheet();

4. Set active sheet by using setActiveSheetIndex();

$objPHPExcel->setActiveSheetIndex(0);

5. Read data to active sheet getActivesheet().

6. There are multiple functions for set value to row column.

• SetCellValue 

$objPHPExcel->getActiveSheet()->SetCellValue('A1', 'Country Code');

• setCellValueByColumnAndRow

$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col_header, $row_header,'id');

7. Creating cell

• If cell value is not exists then phpexcel will create it by its own
  • getCell

$objPHPExcel->getActiveSheet()->getCell('E11')

• If check cell value and not to create than simply pass false in second value so it will not create cell and return null value if cell value is not exists.

8.  Retrieving a cell value by column and row

• getCellValueByColumnAndRow
  

 $objPHPExcel->getActiveSheet()->getCellByColumnAndRow(2, 5)->getValue();

• Calculate values are possible in PHPEXCEL
  

$objPHPExcel->getActiveSheet()->getCell('A4')->getCalculatedValue();

• There is also feature to format cell values(ex. Date or time value)
  

$objPHPExcel->getActiveSheet()->getCell('A6')->getFormattedValue();

• There is also feature to retrieve cell range into array
  

  $dataArray = $objPHPExcel->getActiveSheet() ->rangeToArray(
        'C3:E5', // worksheet cell range that we want to fetch
         NULL, // Value returned for empty cells
         TRUE,// formulas be calculated ( getCalculatedValue() for each cell)
        TRUE,//  formatted values ( getFormattedValue() for each cell)
        TRUE// Array indexed cell,row and cell column wise
    	);

For reference :-

<?php 
include('Classes/PHPExcel.php'); 
$objPHPExcel	=   new PHPExcel();
$result     	=   $db->query("SELECT * FROM countries") or die(mysql_error()); // query for result 
// set table header values
$objPHPExcel->setActiveSheetIndex(0); 
$objPHPExcel->getActiveSheet()->SetCellValue('A1', 'Country Code');
$objPHPExcel->getActiveSheet()->SetCellValue('B1', 'Country Name');
$objPHPExcel->getActiveSheet()->SetCellValue('C1', 'Capital');
$objPHPExcel->getActiveSheet()->getStyle("A1:C1")->getFont()->setBold(true); // give bold style to cell
$rowCount   =   2;
while($row  =   $result->fetch_assoc()){
//data print from the table
$objPHPExcel->getActiveSheet()->SetCellValue('A'.$rowCount,$row['countryCode']);
    $objPHPExcel->getActiveSheet()->SetCellValue('B'.$rowCount, $row['countryName']);
    $objPHPExcel->getActiveSheet()->SetCellValue('C'.$rowCount, $row['capital']);
    $rowCount++;

}
header('Content-Type: application/vnd.ms-excel'); //mime type
header('Content-Disposition: attachment;filename="you-file-name.xlsx"'); //tell browser what's the file name
header('Cache-Control: max-age=0'); //no cache

//for write make a library object
$objWriter  =   new PHPExcel_Writer_Excel2007($objPHPExcel);
//Or you can also use this library object for write
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007'); $objWriter->save('php://output');
?>

IMPORT EXCEL WITH PHP

• HOW TO IMPORT EXCEL WITH PHP

1 . There are different libraries available for import excel.

2.  PHPEXCEL/ SpreadSheet-reader is the latest version until now.

3. You can download it from the link below.

https://github.com/nuovo/spreadsheet-reader

4. This library reads files of the below filetypes.

• ‘application/vnd.ms-excel’,’text/xls’,’text/xlsx’,’application/vnd.openxmlformats-officedocument.spreadsheetml.sheet’.

5.  Use of spreadsheet data there are two files required which is in spreadsheet-reader library.

• spreadsheet-reader-master/php-excel-reader/excel_reader2.php

• spreadsheet-reader-master/SpreadsheetReader.php

6. Save import file to local folder.

7. Read file with SpreadsheetReader($filepath).Here add local file path

8.  You can retrieve information about sheet numbers by sheets().

9.  You can retrieve indexes by use of for loop and ChangeSheet($index) will give  data index by index.

For reference :-

$Reader = new SpreadsheetReader($filepath);
$sheetCount = count($Reader->sheets()); //gives sheets count from worksheet
    for($i=0;$i<$sheetCount;$i++)
     {
        $Reader->ChangeSheet($i); //give data index by index
         foreach($Reader as $row){
            print_r($row);//It will give sheet data
          }
    }

EXPORT/IMPORT EXCEL WITH JAVASCRIPT

• HOW TO EXPORT EXCEL WITH JAVASCRIPT

1. Export Excel With Blob

1. First create simple html table 

2. We use navigator for export excel in javascript

3. Basically, navigator is  the user agent. It allows scripts to assign themselves in some activities.

4. File interface is based on blob. So, we are using blob to save files.

5. Here, We use msSaveOrOpenBlob method which is used to save files or blob. It returns a true or false flag.

For reference :-

<html>
<head>
<title>Export HTML Table Data to Excel using JavaScript | Tutorialswebsite</title>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css" >
<script type="text/javascript">
function exportToExcel(tableID, filename = ''){
    var downloadurl;
    var dataFileType = 'application/vnd.ms-excel';
    var tableSelect = document.getElementById(tableID);
   // below replace code with space
    var tableHTMLData = tableSelect.outerHTML.replace(/ /g, '%20');
    // Specify file name
    filename = filename?filename+'.xls':'export_excel_data.xls';
    // Create download link element
    downloadurl = document.createElement("a");
    document.body.appendChild(downloadurl);
   // Check for save or open file
    if(navigator.msSaveOrOpenBlob){
        var blob = new Blob(['\ufeff', tableHTMLData], {
            type: dataFileType
        });
        navigator.msSaveOrOpenBlob( blob, filename);
    }else{
        // Create a link to the file
        downloadurl.href = 'data:' + dataFileType + ', ' + tableHTMLData;
        // Setting the file name
        downloadurl.download = filename;
        //triggering the function
        downloadurl.click();
    }
}
</script>
</head>
<body>
<div class="container">
<table id="tblexportData" class="table">
<thead>
    <tr>
        <th>Name</th>
        <th>Email</th>
        <th>Age</th>
        <th>Mobile</th>
    </tr>
	</thead>
  <tbody>
    <tr>
        <td>Robert</td>
        <td>[email protected]</td>
        <td>26</td>
         <td>9999999999</td>
    </tr>
    <tr>
        <td>Michael</td>
        <td>[email protected]</td>
         <td>24</td>
         <td>9999999999</td>
    </tr>
    <tr>
        <td>Julie</td>
        <td>[email protected]</td>
         <td>36</td>
         <td>9999999999</td>
    </tr>
<tr>
        <td>Trevor</td>
        <td>[email protected]</td>
         <td>28</td>
         <td>9999999999</td>
    </tr>
	 </tbody>
</table>
<button onclick="exportToExcel('tblexportData', 'user-data')" class="btn btn-success">Export Table Data To Excel File</button>
</div>
</body>
</html>

2. EXPORT EXCEL WITH Table2excel PLUGIN

1. Here is a link for table2excel plugin :

https://github.com/rainabba/jquery-table2excel .

2. Add library to file.

For reference :

<script src="//ajax.googleapis.com/ajax/libs/jquery/1.11.1/jquery.min.js"></script>
<script src="src/jquery.table2excel.js"></script>

2. Create a html table and button.

<table id=”table2excel”>
<tr class="noExl">
  <th>#</th>
  <th>Column heading</th>
  <th>Column heading</th>
  <th>Column heading</th>
</tr>
<button>Export</button></table>

3. Write script on button click.

$("button").click(function(){
  $("#table2excel").table2excel({
    // exclude CSS class
    exclude: ".noExl",
    name: "Worksheet Name",
    filename: "SomeFile", //do not include extension
    fileext: ".xls", // file extension
    preserveColors:true,// font color of background(default false)
    exclude_img: true, // Images include
    exclude_links: true, // Link include
    exclude_inputs: true  // Input include
  }); 
});

HOW TO IMPORT EXCEL WITH JAVASCRIPT

1. We use HTML5 FileReader to import excel file.
2. Here, IE browsers are read differently than other browsers. It does not understand binary string so we use a different method for it.
3. We use function readAsBinaryString(It reads as file or blob and stores read data in binary data) for other browser than IE and for IE browser we use readAsArrayBuffer (It reads as file or blob and stores read data in array buffer)
4. We use xlsx and jszip libraries for import excel.
5. For print data we use the XLSX read function.
6. Use a workbook object for select sheet and row column value for print.

For reference :-

1. Add HTML code for file upload

<input type="file" id="fileUpload" />
<input type="button" id="upload" value="Upload" />
<hr />
<div id="dvExcel"></div>

2. Add javascript files

<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>
<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.13.5/xlsx.full.min.js"></script>
<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.13.5/jszip.js"></script>

3. Add function to process excel file.

<script type="text/javascript">
	$("body").on("click", "#upload", function () { 
        var fileUpload = $("#fileUpload")[0];
        //Validate File is valid Excel file.
        var regex = /^([a-zA-Z0-9\s_\\.\-:])+(.xls|.xlsx)$/;
        if (regex.test(fileUpload.value.toLowerCase())) {
            if (typeof (FileReader) != "undefined") {
                var reader = new FileReader();
                //For other Browsers than IE.
                if (reader.readAsBinaryString) {
                	reader.onload = function (e) {
                    	ProcessExcel(e.target.result);
                	};
                	reader.readAsBinaryString(fileUpload.files[0]);
            	} else {
                    //For IE Browser.
                	reader.onload = function (e) {
                        var data = "";
                        var bytes = new Uint8Array(e.target.result);
                        for (var i = 0; i < bytes.byteLength; i++) {
                        	data += String.fromCharCode(bytes[i]);
                    	}
                      //Call function to print data from excel.
                    	ProcessExcel(data);
                	};
                	reader.readAsArrayBuffer(fileUpload.files[0]);
            	}
        	} else {
            	alert("This browser does not support HTML5.");
        	}
    	} else {
        	alert("Please upload a valid Excel file.");
    	}
	});
</script>

4. Print data from excel file

<script>
function ProcessExcel(data) {
        //Read the Excel File data.
        var workbook = XLSX.read(data, {
            type: 'binary'
        });
        //Fetch the name of First Sheet.
        var firstSheet = workbook.SheetNames;
        //Read all rows from Sheet into an JSON array.
        firstSheet.forEach(function(y) {
		    var worksheet = workbook.Sheets[y];
		    var headers = {};
		    var data = [];
		    var tables_data = [];
		    var row_value = "";
		    //Create a HTML Table element.
		    var table = $("<table />");
		    table[0].border = "1";
		    var cells = $("<td />");
			// Add the header row.
		    var rows = $(table[0].insertRow(-1));
		    for(z in worksheet) {
		        if(z[0] === '!') continue;
		        //parse out the column, row, and value
		        var tt = 0;
		        for (var i = 0; i < z.length; i++) {
		            if (!isNaN(z[i])) {
		                tt = i;
		                break;
		            }
		        };
		        var col = z.substring(0,tt);
		        var row = parseInt(z.substring(tt));
		        var value = worksheet[z].v;
		        //print header names
		        if(row == 1 && value) {
		            var headerCell = $("<th/>");
	        		headerCell.html(value);
	        		rows.append(headerCell);
		            continue;
		        }
		        if(row_value != row){
                      //use for next row
		        	var rows = $(table[0].insertRow(-1));
		        }
                //print table td value
	            cells.html(value);
	            rows.append(cells);
	            cells = $("<td />");
	            row_value = row;
		    }
		    var dvExcel = $("#dvExcel");
	          dvExcel.html("");
	          dvExcel.append(table);
		});
    };
</script>