PHP-如何使用PHP解析WPS格式的Excel文档

Question

客户那边使用WPS,生成的Excel是专有的 wps格式,我在网上 搜了好久,都没有这方面的解决方案,咋弄啊?

Answer 1

很久以前写的代码:
从xls文件格式读取。但是发现读取时间的时候，总是不对。
原来excel里面时间是从1900-1-1开始以天计算，比如1900-1-1是1，1970-1-125569，2011-06-01是40695.
使用spreadsheet_excel_reader excel类读取excel的内容
reader.php 中的下面这行要修改
将 require_once 'Spreadsheet/Excel/Reader/OLERead.php';
改为 require_once 'oleread.inc';
example.php 中
修改 $data->setOutputEncoding('CP1251');
为 $data->setOutputEncoding('CP936');
修改 nl2br(htmlentities($data->sheets[$sheet]['cells'][$row][$col]));
为 $table_output[$sheet] .= nl2br(htmlspecialchars($data->sheets[$sheet]['cells'][$row][$col]));
不然中文会有问题。
繁体的话可以修改为CP950、日文是CP932，具体可参考codepage说明。
修改 $data->read('jxlrwtest.xls') 为自己的 excel 文件名，zip 档中附的 jxlrwtest.xls 应该是坏了。
这是下载地址：

phpExcelReader：http://sourceforge.net/projects/phpexcelreader/
PHPExcel：http://www.codeplex.com/PHPExcel/Wiki/View.aspx?title=Documents&referringTitle=Home

代码如下

<?php

// Test CVS
error_reporting(E_ALL);
//echo "1";

include_once('//Excel/reader.php')；

// ExcelFile($filename, $encoding);
$data = new Spreadsheet_Excel_Reader();

// Set output Encoding.
//$data->setOutputEncoding('CP1251');
$data->setOutputEncoding('CP936');

/***
* if you want you can change 'iconv' to mb_convert_encoding:
* $data->setUTFEncoder('mb');
*
**/

/***
* By default rows & cols indeces start with 1
* For change initial index use:
* $data->setRowColOffset(0);
*
**/

/***
* Some function for formatting output.
* $data->setDefaultFormat('%.2f');
* setDefaultFormat - set format for columns with unknown formatting
*
* $data->setColumnFormat(4, '%.3f');
* setColumnFormat - set format for column (apply only to number fields)
*
**/

$data->read('1.xls');
//print_r($data->boundsheets);
//exit;
/*
print exceltimtetophp($data->sheets[0]['cells'][1][1]);
print $data->sheets[0]['cells'][1][3];
exit;
*/
/*

$data->sheets[0]['numRows'] - count rows
$data->sheets[0]['numCols'] - count columns
$data->sheets[0]['cells'][$i][$j] - data from $i-row $j-column

$data->sheets[0]['cellsInfo'][$i][$j] - extended info about cell

$data->sheets[0]['cellsInfo'][$i][$j]['type'] = "date" | "number" | "unknown"
if 'type' == "unknown" - use 'raw' value, because cell contain value with format '0.00';
$data->sheets[0]['cellsInfo'][$i][$j]['raw'] = value if cell without format
$data->sheets[0]['cellsInfo'][$i][$j]['colspan']
$data->sheets[0]['cellsInfo'][$i][$j]['rowspan']
*/

error_reporting(E_ALL ^ E_NOTICE);
$count=count($data->boundsheets);
$sheetitem = $data->boundsheets;

for ($k=0;$k<$count;$k++){
if ($k == 0){
$category = 1;
$handle=fopen("/home/zhukai/sql/{$category}.txt","a+");
}else if ($k == 1){
$category = 2;
$handle=fopen("/home/zhukai/sql/{$category}.txt","a+");
}else{
$category = 0;
}

for ($i = 1; $i <= $data->sheets[0]['numRows']; $i++) {
$dateline = strtotime(exceltimtetophp($data->sheets[$k]['cells'][$i][1]));
$content = addslashes(strreplace($data->sheets[$k]['cells'][$i][2]));
$note = addslashes(strreplace($data->sheets[$k]['cells'][$i][3]));
//echo """.$data->sheets[0]['cells'][$i][$j]."",";
if ($category != 0 && $content && $note){
$string="INSERT INTO test(category,content,note,datetline) values ('{$category}','{$content}','{$note}','{$dateline}');rn";
fwrite($handle,$string);
}
fclose($handle);
}
}
/*for ($i = 1; $i <= $data->sheets[0]['numRows']; $i++) {
for ($j = 1; $j <= $data->sheets[0]['numCols']; $j++) {
echo """.$data->sheets[0]['cells'][$i][$j]."",";
}
echo "n";

}*/

//print_r($data);
//print_r($data->formatRecords);*/

function exceltimtetophp($days,$time=false)
{
if(is_numeric($days))
{
$jd = GregorianToJD(1, 1, 1970);
$gregorian = JDToGregorian($jd+intval($days)-25569);
$myDate = explode('/',$gregorian);
$myDateStr= str_pad($myDate[2],4,'0', STR_PAD_LEFT)."-".str_pad($myDate[0],2,'0',STR_PAD_LEFT)."-".str_pad($myDate[1],2,'0', STR_PAD_LEFT).($time?"00:00:00":'');
return $myDateStr;
}
return $time;
}

function strreplace($str){
$foundarr=array("'",",");
$replacearr=array("‘","，");
return str_replace($foundarr,$replacearr,$str);
}
?>