I'm trying to read an Excel file (Office 2003). There is an Excel file that needs to be uploaded and its contents parsed.
Via Google, I can only find answers to these related (and insufficient topics): generating Excel files, reading Excel XML files, reading Excel CSV files, or incomplete abandoned projects. I own Office 2003 so if I need any files from there, they are available. It's installed on my box but isn't and can't be installed on my shared host.
Edit: so far all answers point to PHP-ExcelReader and/or this additional article about how to use it.
This question is related to
php
import-from-excel
You have 2 choices as far as I know:
PHPExcel uses Spreadsheet_Excel_Reader for the Office 2003 format.
Update: I once had to read some Excel files but I used the Office 2003 XML format in order to read them and told the people that were using the application to save and upload only that type of Excel file.
I'm using below excel file url: https://github.com/inventorbala/Sample-Excel-files/blob/master/sample-excel-files.xlsx
Output:
Array
(
[0] => Array
(
[store_id] => 3716
[employee_uid] => 664368
[opus_id] => zh901j
[item_description] => PRE ATT $75 PNLS 90EXP
[opus_transaction_date] => 2019-10-18
[opus_transaction_num] => X2MBV1DJKSLQW
[opus_invoice_num] => O3716IN3409
[customer_name] => BILL PHILLIPS
[mobile_num] => 4052380136
[opus_amount] => 75
[rq4_amount] => 0
[difference] => -75
[ocomment] => Re-Upload: We need RQ4 transaction for October. If you're unable to provide the October invoice, it will be counted as EPin shortage.
[mark_delete] => 0
[upload_date] => 2019-10-20
)
[1] => Array
(
[store_id] => 2710
[employee_uid] => 75899
[opus_id] => dc288t
[item_description] => PRE ATT $50 PNLS 90EXP
[opus_transaction_date] => 2019-10-18
[opus_transaction_num] => XJ90419JKT9R9
[opus_invoice_num] => M2710IN868
[customer_name] => CALEB MENDEZ
[mobile_num] => 6517672079
[opus_amount] => 50
[rq4_amount] => 0
[difference] => -50
[ocomment] => No Response. Re-Upload
[mark_delete] => 0
[upload_date] => 2019-10-20
)
[2] => Array
(
[store_id] => 0136
[employee_uid] => 70167
[opus_id] => fv766x
[item_description] => PRE ATT $50 PNLS 90EXP
[opus_transaction_date] => 2019-10-18
[opus_transaction_num] => XQ57316JKST1V
[opus_invoice_num] => GONZABP25622
[customer_name] => FAUSTINA CASTILLO
[mobile_num] => 8302638628
[opus_amount] => 100
[rq4_amount] => 50
[difference] => -50
[ocomment] => Re-Upload: We have been charged in opus for $100. Provide RQ4 invoice number for remaining amount
[mark_delete] => 0
[upload_date] => 2019-10-20
)
[3] => Array
(
[store_id] => 3264
[employee_uid] => 23723
[opus_id] => aa297h
[item_description] => PRE ATT $25 PNLS 90EXP
[opus_transaction_date] => 2019-10-19
[opus_transaction_num] => XR1181HJKW9MP
[opus_invoice_num] => C3264IN1588
[customer_name] => SOPHAT VANN
[mobile_num] => 9494668372
[opus_amount] => 70
[rq4_amount] => 25
[difference] => -45
[ocomment] => No Response. Re-Upload
[mark_delete] => 0
[upload_date] => 2019-10-20
)
[4] => Array
(
[store_id] => 4166
[employee_uid] => 568494
[opus_id] => ab7598
[item_description] => PRE ATT $40 RTR
[opus_transaction_date] => 2019-10-20
[opus_transaction_num] => X8F58P3JL2RFU
[opus_invoice_num] => I4166IN2481
[customer_name] => KELLY MC GUIRE
[mobile_num] => 6189468180
[opus_amount] => 40
[rq4_amount] => 0
[difference] => -40
[ocomment] => Re-Upload: The invoice number that you provided (I4166IN2481) belongs to September transaction. We need RQ4 transaction for October. If you're unable to provide the October invoice, it will be counted as EPin shortage.
[mark_delete] => 0
[upload_date] => 2019-10-21
)
[5] => Array
(
[store_id] => 4508
[employee_uid] => 552502
[opus_id] => ec850x
[item_description] => $30 RTR
[opus_transaction_date] => 2019-10-20
[opus_transaction_num] => XPL7M1BJL1W5D
[opus_invoice_num] => M4508IN6024
[customer_name] => PREPAID CUSTOMER
[mobile_num] => 6019109730
[opus_amount] => 30
[rq4_amount] => 0
[difference] => -30
[ocomment] => Re-Upload: The invoice number you provided (M4508IN7217) belongs to a different phone number. We need RQ4 transaction for the phone number in question. If you're unable to provide the RQ4 invoice for this transaction, it will be counted as EPin shortage.
[mark_delete] => 0
[upload_date] => 2019-10-21
)
[6] => Array
(
[store_id] => 3904
[employee_uid] => 35818
[opus_id] => tj539j
[item_description] => PRE $45 PAYG PINLESS REFILL
[opus_transaction_date] => 2019-10-20
[opus_transaction_num] => XM1PZQSJL215F
[opus_invoice_num] => N3904IN1410
[customer_name] => DORTHY JONES
[mobile_num] => 3365982631
[opus_amount] => 90
[rq4_amount] => 45
[difference] => -45
[ocomment] => Re-Upload: Please email the details to Treasury and confirm
[mark_delete] => 0
[upload_date] => 2019-10-21
)
[7] => Array
(
[store_id] => 1820
[employee_uid] => 59883
[opus_id] => cb9406
[item_description] => PRE ATT $25 PNLS 90EXP
[opus_transaction_date] => 2019-10-20
[opus_transaction_num] => XTBJO14JL25OE
[opus_invoice_num] => SEVIEIN19013
[customer_name] => RON NELSON
[mobile_num] => 8653821076
[opus_amount] => 25
[rq4_amount] => 5
[difference] => -20
[ocomment] => Re-Upload: We have been charged in opus for $25. Provide RQ4 invoice number for remaining amount
[mark_delete] => 0
[upload_date] => 2019-10-21
)
[8] => Array
(
[store_id] => 0178
[employee_uid] => 572547
[opus_id] => ms5674
[item_description] => PRE $45 PAYG PINLESS REFILL
[opus_transaction_date] => 2019-10-21
[opus_transaction_num] => XT29916JL4S69
[opus_invoice_num] => T0178BP1590
[customer_name] => GABRIEL LONGORIA JR
[mobile_num] => 4322133450
[opus_amount] => 45
[rq4_amount] => 0
[difference] => -45
[ocomment] => Re-Upload: Please email the details to Treasury and confirm
[mark_delete] => 0
[upload_date] => 2019-10-22
)
[9] => Array
(
[store_id] => 2180
[employee_uid] => 7842
[opus_id] => lm854y
[item_description] => $30 RTR
[opus_transaction_date] => 2019-10-21
[opus_transaction_num] => XC9U712JL4LA4
[opus_invoice_num] => KETERIN1836
[customer_name] => PETE JABLONSKI
[mobile_num] => 9374092680
[opus_amount] => 30
[rq4_amount] => 40
[difference] => 10
[ocomment] => Re-Upload: Credit the remaining balance to customers account in OPUS and email confirmation to Treasury
[mark_delete] => 0
[upload_date] => 2019-10-22
)
.
.
.
[63] => Array
(
[store_id] => 0175
[employee_uid] => 33738
[opus_id] => ph5953
[item_description] => PRE ATT $40 RTR
[opus_transaction_date] => 2019-10-21
[opus_transaction_num] => XE5N31DJL51RA
[opus_invoice_num] => T0175IN4563
[customer_name] => WILLIE TAYLOR
[mobile_num] => 6822701188
[opus_amount] => 40
[rq4_amount] => 50
[difference] => 10
[ocomment] => Re-Upload: Credit the remaining balance to customers account in OPUS and email confirmation to Treasury
[mark_delete] => 0
[upload_date] => 2019-10-22
)
)
I have used following code to read "xls and xlsx" :
include 'PHPExcel/IOFactory.php';
$location='sample-excel-files.xlsx';
$objPHPExcel = PHPExcel_IOFactory::load($location);
$sheet = $objPHPExcel->getSheet(0);
$total_rows = $sheet->getHighestRow();
$total_columns = $sheet->getHighestColumn();
$set_excel_query_all=array();
for($row =2; $row <= $total_rows; $row++) {
$singlerow = $sheet->rangeToArray('A' . $row . ':' . $total_columns . $row, NULL, TRUE, FALSE);
$single_row=$singlerow[0];
$set_excel_query['store_id']=$single_row[0];
$set_excel_query['employee_uid']=$single_row[1];
$set_excel_query['opus_id']=$single_row[2];
$set_excel_query['item_description']=$single_row[3];
if($single_row[4])
{
$set_excel_query['opus_transaction_date']= date('Y-m-d', PHPExcel_Shared_Date::ExcelToPHP($single_row[4]));
}
$set_excel_query['opus_transaction_num']=$single_row[5];
$set_excel_query['opus_invoice_num']=$single_row[6];
$set_excel_query['customer_name']=$single_row[7];
$set_excel_query['mobile_num']=$single_row[8];
$set_excel_query['opus_amount']=$single_row[9];
$set_excel_query['rq4_amount']=$single_row[10];
$set_excel_query['difference']=$single_row[11];
$set_excel_query['ocomment']=$single_row[12];
$set_excel_query['mark_delete']=$single_row[13];
if($single_row[14])
{
$set_excel_query['upload_date']= date('Y-m-d', PHPExcel_Shared_Date::ExcelToPHP($single_row[14]));
}
$set_excel_query_all[]=$set_excel_query;
}
print_r($set_excel_query_all);
Try this...
I have used following code to read "xls and xlsx"
<?php
include 'excel_reader.php'; // include the class
$excel = new PhpExcelReader; // creates object instance of the class
$excel->read('excel_file.xls'); // reads and stores the excel file data
// Test to see the excel data stored in $sheets property
echo '<pre>';
var_export($excel->sheets);
echo '</pre>';
or
echo '<pre>';
print_r($excel->sheets);
echo '</pre>';
Reference:http://coursesweb.net/php-mysql/read-excel-file-data-php_pc
It depends on how you want to use the data in the excel file. If you want to import it into mysql, you could simply save it as a CSV formatted file and then use fgetcsv to parse it.
// Here is the simple code using COM object in PHP
class Excel_ReadWrite{
private $XLSHandle;
private $WrkBksHandle;
private $xlBook;
function __construct() {
$this->XLSHandle = new COM("excel.application") or die("ERROR: Unable to instantaniate COM!\r\n");
}
function __destruct(){
//if already existing file is opened
if($this->WrkBksHandle != null)
{
$this->WrkBksHandle->Close(True);
unset($this->WrkBksHandle);
$this->XLSHandle->Workbooks->Close();
}
//if created new xls file
if($this->xlBook != null)
{
$this->xlBook->Close(True);
unset($this->xlBook);
}
//Quit Excel Application
$this->XLSHandle->Quit();
unset($this->XLSHandle);
}
public function OpenFile($FilePath)
{
$this->WrkBksHandle = $this->XLSHandle->Workbooks->Open($FilePath);
}
public function ReadData($RowNo, $ClmNo)
{
$Value = $this->XLSHandle->ActiveSheet->Cells($RowNo, $ClmNo)->Value;
return $Value;
}
public function SaveOpenedFile()
{
$this->WrkBksHandle->Save();
}
/***********************************************************************************
* Function Name:- WriteToXlsFile() will write data based on row and column numbers
* @Param:- $CellData- cell data
* @Param:- $RowNumber- xlsx file row number
* @Param:- $ColumnNumber- xlsx file column numbers
************************************************************************************/
function WriteToXlsFile($CellData, $RowNumber, $ColumnNumber)
{
try{
$this->XLSHandle->ActiveSheet->Cells($RowNumber,$ColumnNumber)->Value = $CellData;
}
catch(Exception $e){
throw new Exception("Error:- Unable to write data to xlsx sheet");
}
}
/****************************************************************************************
* Function Name:- CreateXlsFileWithClmName() will initialize xls file with column Names
* @Param:- $XlsColumnNames- Array of columns data
* @Param:- $XlsColumnWidth- Array of columns width
*******************************************************************************************/
function CreateXlsFileWithClmNameAndWidth($WorkSheetName = "Raman", $XlsColumnNames = null, $XlsColumnWidth = null)
{
//Hide MS Excel application window
$this->XLSHandle->Visible = 0;
//Create new document
$this->xlBook = $this->XLSHandle->Workbooks->Add();
//Create Sheet 1
$this->xlBook->Worksheets(1)->Name = $WorkSheetName;
$this->xlBook->Worksheets(1)->Select;
if($XlsColumnWidth != null)
{
//$XlsColumnWidth = array("A1"=>15,"B1"=>20);
foreach($XlsColumnWidth as $Clm=>$Width)
{
//Set Columns Width
$this->XLSHandle->ActiveSheet->Range($Clm.":".$Clm)->ColumnWidth = $Width;
}
}
if($XlsColumnNames != null)
{
//$XlsColumnNames = array("FirstColumnName"=>1, "SecondColumnName"=>2);
foreach($XlsColumnNames as $ClmName=>$ClmNumber)
{
// Cells(Row,Column)
$this->XLSHandle->ActiveSheet->Cells(1,$ClmNumber)->Value = $ClmName;
$this->XLSHandle->ActiveSheet->Cells(1,$ClmNumber)->Font->Bold = True;
$this->XLSHandle->ActiveSheet->Cells(1,$ClmNumber)->Interior->ColorIndex = "15";
}
}
}
//56 is for xls 8
public function SaveCreatedFile($FileName, $FileFormat = 56)
{
$this->xlBook->SaveAs($FileName, $FileFormat);
}
public function MakeFileVisible()
{
//Hide MS Excel application window`enter code here`
$this->XLSHandle->Visible = 1;
}
}//end of EXCEL class
Read XLSX (Excel 97-2003)
https://github.com/shuchkin/simplexls
if ( $xls = SimpleXLS::parse('book.xls') ) {
print_r( $xls->rows() );
} else {
echo SimpleXLS::parseError();
}
Read XLSX (Excel 2003+)
https://github.com/shuchkin/simplexlsx
if ( $xlsx = SimpleXLSX::parse('book.xlsx') ) {
print_r( $xlsx->rows() );
} else {
echo SimpleXLSX::parseError();
}
Output
Array (
[0] => Array
(
[0] => ISBN
[1] => title
[2] => author
[3] => publisher
[4] => ctry
)
[1] => Array
(
[0] => 618260307
[1] => The Hobbit
[2] => J. R. R. Tolkien
[3] => Houghton Mifflin
[4] => USA
)
)
CSV php reader
https://github.com/shuchkin/simplecsv
Source: Stackoverflow.com