我想读取一个Excel文件(Office 2003)。需要上传该Excel文件并解析其内容。
通过谷歌,我只能找到与此相关(且不充分)的主题:生成Excel文件、读取Excel XML文件、读取Excel CSV文件或未完成的项目。我拥有Office 2003,因此如果需要那里的任何文件,它们是可用的。它已安装在我的电脑上,但不能安装在共享主机上。
编辑: 到目前为止,所有答案都指向PHP-ExcelReader和/或这篇额外的文章,介绍如何使用它。
我想读取一个Excel文件(Office 2003)。需要上传该Excel文件并解析其内容。
通过谷歌,我只能找到与此相关(且不充分)的主题:生成Excel文件、读取Excel XML文件、读取Excel CSV文件或未完成的项目。我拥有Office 2003,因此如果需要那里的任何文件,它们是可用的。它已安装在我的电脑上,但不能安装在共享主机上。
编辑: 到目前为止,所有答案都指向PHP-ExcelReader和/或这篇额外的文章,介绍如何使用它。
PHPExcel使用Spreadsheet_Excel_Reader来读取Office 2003格式的文件。
更新:我曾经需要读取一些Excel文件,但是我使用了Office 2003 XML格式来读取它们,并告诉使用该应用程序的人仅保存和上传该类型的Excel文件。
我使用PHP-ExcelReader来读取xls文件,效果很好。
阅读XLSX(Excel 2003+)
https://github.com/shuchkin/simplexlsx
if ( $xlsx = SimpleXLSX::parse('book.xlsx') ) {
print_r( $xlsx->rows() );
} else {
echo SimpleXLSX::parseError();
}
输出
数组( [0] => 数组 ( [0] => ISBN [1] => 标题 [2] => 作者 [3] => 出版商 [4] => 国家 ) [1] => 数组 ( [0] => 618260307 [1] => 霍比特人 [2] => J.R.R.托尔金 [3] => 霍顿米夫林 [4] => 美国 )
)
读取XLS(Excel 97-2003)
https://github.com/shuchkin/simplexls
if ( $xls = SimpleXLS::parse('book.xls') ) {
print_r( $xls->rows() );
} else {
echo SimpleXLS::parseError();
}
CSV PHP读取器
https://github.com/shuchkin/simplecsv
if ( $csv = SimpleCSV::import('book.csv') ) {
print_r( $csv );
}
// 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
<?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
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
)
)
我已经使用以下代码来读取“xls和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);