How can i edit xlsx file in php?

How can i edit xlsx file in php?


  • 4 years ago
  • Zaid Bin Khalid
  • 55753 Views
  • 10

PHPExcel is a very powerful library to read and write data into excel. Now this project is archived by author visit to see detail. PHPExcel is officially known as PhpSpreadsheet. I am going to tell you how you can create an excel file with XLSX extension. And how you can read XLSX file with PHPExcel or PhpSpreadsheet.

Installation: The PHPSpreadsheet can be installed with the help of Composer

On Terminal: The following command runs on the terminal to install PHPSpreadsheet:

composer require phpoffice/phpspreadsheet

Download PhpSpreadsheet and add it to your project like below.

How to write data and save the XLSX file.

The below example is the complete working example that you can use to write the XLSX file with the help of the PhpSpreadsheet.

getActiveSheet(); 
  
// Set the value of cell A1 
$sheet->setCellValue('A1', 'A1 Cell Data Here'); 
$sheet->setCellValue('B1', 'B1 Cell Data Here'); 
   
// Write an .xlsx file  
$writer = new Xlsx($spreadsheet); 
  
// Save .xlsx file to the current directory 
$writer->save('lcw.xlsx'); 

How to read the XLSX file in PhpSpreadsheet?

You can use PhpSpreadsheet to read the XLSX file with the help of the below code.

$spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load('lcw.xlsx');

$sheet = $spreadsheet->getActiveSheet();

// Store data from the activeSheet to the varibale in the form of Array
$data = array(1,$sheet->toArray(null,true,true,true)); 
  
// Display the sheet content 
var_dump($data);

In the PHPExcel

If you are using the older version of PHPExcel then consider below code to read XLSX file.

Example code.

include_once('Excel/Classes/PHPExcel.php');
$inputFileName = 'sample.xlsx';
//Read your Excel workbook
try{
    $inputFileType 	= 	PHPExcel_IOFactory::identify($inputFileName);
    $objReader 		= 	PHPExcel_IOFactory::createReader($inputFileType);
    $objPHPExcel 	= 	$objReader->load($inputFileName);
}catch(Exception $e){
    die('Error loading file "'.pathinfo($inputFileName,PATHINFO_BASENAME).'": '.$e->getMessage());
}

//  Get worksheet dimensions
$sheet = $objPHPExcel->getActiveSheet(); 
$highestRow = $sheet->getHighestRow(); 
$highestColumn = $sheet->getHighestColumn();

//  Loop through each row of the worksheet in turn
for ($row = 1; $row <= $highestRow; $row++){ 
    //  Read a row of data into an array
	$rowData = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row,
                                    NULL,
                                    TRUE,
                                    FALSE);
    //  Use foreach loop and insert data into Query
	
}

Note: In the above example, you need to understand the highlighted area.

$inputFileName = 'sample.xlsx'; This line indicates the excel file that you want to read. You can read any sheet with the help of getSheet method. You can also use getActiveSheet() method if you are using PHPExcel Archive.

$sheet = $objPHPExcel->getSheet(1); // Change sheet number

Now you just need to read the rows and columns of the excel sheet.

In the FOR LOOP, all data stored in $rowData. Now you just need to dump data into your DB.

For that, use a foreach loop and within loop use your insert query to store data. Below snippet is the example of a foreach loop.

foreach($rowData as $val){
      mysqli_query('YOUR-QUERY',$connection)
}

You can also use the SimpleXLSX class to read the excel file. To download the simpleXLSX class click here. The below snippet is the simple example code to read XLSX file.

require_once 'SimpleXLSX.php';

if ( $xlsx = SimpleXLSX::parse('pricelist.xlsx') ) {
  print_r( $xlsx->rows() );
} else {
  echo SimpleXLSX::parse_error();
}

  • 4 years ago
  • Zaid Bin Khalid
  • 55753 Views
  • 10


How do you create and edit Excel spreadsheets in php?

You may use the following code: header('Content-Type: application/vnd. ms-excel'); header('Content-Disposition: attachment;filename="file. xlsx"'); header('Cache-Control: max-age=0'); $writer->save('php://output');

How do I edit an XLSX file?

About This Article.
Get the Microsoft Excel mobile app from the Google Play Store..
Open Excel..
Open your XLSX file..
Tap a cell to edit it..
Tap the back arrow..

How can I open XLSX file in php?

EasyXLS on Linux, Mac, Windows using Java with PHP.
Step 1: Download and install EasyXLS Excel Library for Java. To download the trial version of EasyXLS Excel Library, press the below button: ... .
Step 2: Install PHP/Java Bridge. ... .
Step 3: Setup EasyXLS library in Tomcat. ... .
Step 4: Run PHP code that reads XLSX file..

How connect Excel to php?

Establish a Connection Open the connection to Excel by calling the odbc_connect or odbc_pconnect methods. To close connections, use odbc_close or odbc_close_all. $conn = odbc_connect("CData ODBC Excel Source","user","password"); Connections opened with odbc_connect are closed when the script ends.