include 'Plugin/PHPExcel/Classes/PHPExcel/IOFactory.php';5.9335552883783E+14 41728.732916667
$excel = PHPExcel_IOFactory::load('2.xls');
$sheet = $excel->getActiveSheet();
echo $sheet->getCell('A1')->getValue(), ' ', $sheet->getCell('A2')->getValue();
$d = 25569;2014-03-30 17:35:24
$t = 24 * 60 * 60;
echo gmdate('Y-m-d H:i:s', (41728.732916667 - $d) * $t);
public function read($filename,$encode='utf-8'){
// vendor('Excel.PHPExcel');
import('Org.Util.PHPExcel');
// $upload = \PHPExcel_IOFactory::
$objReader = \PHPExcel_IOFactory::createReader(Excel5);
$objReader->setReadDataOnly(true);
$objPHPExcel = $objReader->load($filename);
$objWorksheet = $objPHPExcel->getActiveSheet();
$highestRow = $objWorksheet->getHighestRow();
$highestColumn = $objWorksheet->getHighestColumn();
$highestColumnIndex = \PHPExcel_Cell::columnIndexFromString($highestColumn);
$excelData = array();
for ($row = 1; $row <= $highestRow; $row++) {
for ($col = 0; $col < $highestColumnIndex; $col++) {
$excelData[$row][] =(string)$objWorksheet->getCellByColumnAndRow($col, $row)->getValue();
}
}
return $excelData;
}
public function excel(){
$this->display();
}
public function runexcel(){
if (! empty ( $_FILES ['file_stu'] ['name'] ))
{
$tmp_file = $_FILES ['file_stu'] ['tmp_name'];
$file_types = explode ( ".", $_FILES ['file_stu'] ['name'] );
$file_type = $file_types [count ( $file_types ) - 1];
/*判别是不是.xls文件，判别是不是excel文件*/
if (strtolower ( $file_type ) != "xls")
{
$this->error ( '不是Excel文件，重新上传' );
}
/*设置上传路径*/
$savePath = './Uploads/';
/*以时间来命名上传的文件*/
$str = date ( 'Ymdhis' );
$file_name = $str . "." . $file_type;
/*是否上传成功*/
if (! copy ( $tmp_file, $savePath . $file_name ))
{
$this->error ( '上传失败' );
}
/*
*对上传的Excel数据进行处理生成编程数据,这个函数会在下面第三步的ExcelToArray类中
注意：这里调用执行了第三步类里面的read函数，把Excel转化为数组并返回给$res,再进行数据库写入
*/
$res = $this->read ( $savePath . $file_name );
/*
重要代码 解决Thinkphp M、D方法不能调用的问题
如果在thinkphp中遇到M 、D方法失效时就加入下面一句代码
*/
// spl_autoload_register ( array ('Think', 'autoload' ) );
/*对生成的数组进行数据库的写入*/
unset($res[1]);
$r = 0;
$d = 25569;
$t = 24 * 60 * 60;
foreach ( $res as $k => $v )
{
if ($k != 0)
{
$data ['order_number'] = $v[0];
$data ['member_name'] = $v[1];
$data ['member_alipay'] = $v[2];
$data ['member_payment'] = $v[3];
$data ['member_postage'] = $v[4];
$data ['membe_integral'] = $v[5];
$data ['aggregate_amount'] = $v[6];
$data ['rebate_integral'] = $v[7];
$data ['member_actual_payments'] = $v[8];
$data ['member_actual_integral'] = $v[9];
$data ['order_status'] = $v[10];
$data ['member_message'] = $v[11];
$data ['consignee_name'] = $v[12];
$data ['consignee_address'] = $v[13];
$data ['Shipping_method'] = $v[14];
$data ['telephone_number'] = $v[15];
$data ['mobile_phone'] = $v[16];
$va = gmdate('Y-m-d H:i:s', ($v[17]- $d) * $t);
$vb = gmdate('Y-m-d H:i:s', ($v[18]- $d) * $t);
$data ['create_time'] = strtotime($va);
$data ['payment_time'] = strtotime($vb);
$data ['baby_title'] = $v[19];
$data ['baby_species'] = $v[20];
$data ['logistics_number'] = $v[21];
$data ['logistics_company'] = $v[22];
$data ['order_remarks'] = $v[23];
$data ['boby_number'] = $v[24];
$data ['shop_id'] = $v[25];
$data ['shop_name'] = $v[26];
$data ['order_closure'] = $v[27];
$data ['seller_service_fee'] = $v[28];
$data ['buyers_service_fee'] = $v[29];
$data ['commercial_invoice'] = $v[30];
$data ['phone_orders'] = $v[31];
$data ['Phase_order_information'] = $v[32];
$data ['deposit_ranking'] = $v[33];
$data ['modified_sku'] = $v[34];
$data ['modified_orders_address'] = $v[35];
$data ['error_info'] = $v[36];
// show_bug($data);die;
$r++;
$result = M ( 'financial' )->add ( $data );
if (! $result)
{
$this->error ( '导入数据库失败' );
}
}
}
$this->success("导入成功,共导入" . $r . "条数据" ,U('excel'),3);
}
}
我的代码好像用不了getFormattedValue();
$excel = PHPExcel_IOFactory::load('test.xls');
$sheet = $excel->getActiveSheet();
$w = $sheet->getHighestColumn(); //取得最大的列号
$h = $sheet->getHighestRow(); //取得一共有多少行
for($x='A'; $x!=$w; $x++) {
$t = array();
for($y=1; $y<=$h; $y++) {
if($y == 1) $t['h'] = $sheet->getCell("$x$y")->getValue();
elseif(in_array($x, array('R', 'S'))) {
$d = $sheet->getCell("$x$y")->getValue();
$t['d'][] = $d ? gmdate('Y-m-d H:i:s', ($d - 25569) * 86400) : '';
}else $t['d'][] = $sheet->getCell("$x$y")->getFormattedValue();
}
$res[$x] = $t;
}
Array
(
[A] => Array
(
[h] => 订单编号
[d] => Array
(
[0] => 593355528837832
[1] => 593051520633446
[2] => 592333520554850
[3] => 592198001144245
[4] => 592045929493597
)
)
[B] => Array
(
[h] => 买家会员名
[d] => Array
(
[0] => wangfeilixin123
[1] => 解玲9992
[2] => 蒋小丫0415
[3] => 吖呵呵18
[4] => 雄19910105
)
)
[C] => Array
(
[h] => 买家支付宝账号
[d] => Array
(
[0] => szxszglc@163.com
[1] => 15956089992
[2] => 13357122950
[3] => 15147596696
[4] => 18850173553
)
)
[D] => Array
(
[h] => 买家应付货款
[d] => Array
(
[0] => 296
[1] => 159
[2] => 131.4
[3] => 168
[4] => 131.4
)
)
[E] => Array
(
[h] => 买家应付邮费
[d] => Array
(
[0] => 12
[1] => 0
[2] => 0
[3] => 0
[4] => 0
)
)
[F] => Array
(
[h] => 买家支付积分
[d] => Array
(
[0] => 0
[1] => 49
[2] => 0
[3] => 0
[4] => 0
)
)
[G] => Array
(
[h] => 总金额
[d] => Array
(
[0] => 308
[1] => 159
[2] => 131.4
[3] => 168
[4] => 131.4
)
)
[H] => Array
(
[h] => 返点积分
[d] => Array
(
[0] => 0
[1] => 79
[2] => 65
[3] => 84
[4] => 65
)
)
[I] => Array
(
[h] => 买家实际支付金额
[d] => Array
(
[0] => 0
[1] => 159
[2] => 131.4
[3] => 168
[4] => 131.4
)
)
[J] => Array
(
[h] => 买家实际支付积分
[d] => Array
(
[0] => 0
[1] => 49
[2] => 0
[3] => 0
[4] => 0
)
)
[K] => Array
(
[h] => 订单状态
[d] => Array
(
[0] => 交易关闭
[1] => 交易成功
[2] => 交易成功
[3] => 交易成功
[4] => 交易成功
)
)
[L] => Array
(
[h] => 买家留言
[d] => Array
(
[0] =>
[1] =>
[2] =>
[3] => 申通就可以哦 要金色的链子
[4] =>
)
)
[M] => Array
(
[h] => 收货人姓名
[d] => Array
(
[0] => 王飞
[1] => 解玲
[2] => 张勇
[3] => 姜宁
[4] => 许小云
)
)
[N] => Array
(
[h] => 收货地址
[d] => Array
(
[0] => 辽宁省 葫芦岛市 绥中县 肖家菜市场东门王艳海鲜行(125200)
[1] => 安徽省 滁州市 天长市 恒福花苑6号楼一单元602(239300)
[2] => 浙江省 舟山市 普陀区 东港京汇广场5号楼丁哥黑鱼馆(316100)
[3] => 内蒙古自治区 通辽市 开鲁县 开鲁镇邮局(028400)
[4] => 天津 天津市 滨海新区 滨海新区大港学苑路600号天津国土资源和房屋职业学院(300270)
)
)
[O] => Array
(
[h] => 运送方式
[d] => Array
(
[0] => 快递
[1] => 快递
[2] => 快递
[3] => 快递
[4] => 快递
)
)
[P] => Array
(
[h] => 联系电话
[d] => Array
(
[0] =>
[1] =>
[2] =>
[3] =>
[4] =>
)
)
[Q] => Array
(
[h] => 联系手机
[d] => Array
(
[0] => 18204296511
[1] => 15956089992
[2] => 15906805620
[3] => 15147596696
[4] => 13321300935
)
)
[R] => Array
(
[h] => 订单创建时间
[d] => Array
(
[0] => 2014-03-30 17:35:24
[1] => 2014-03-30 14:12:14
[2] => 2014-03-29 22:09:29
[3] => 2014-03-29 20:52:17
[4] => 2014-03-29 19:13:12
)
)
[S] => Array
(
[h] => 订单付款时间
[d] => Array
(
[0] =>
[1] => 2014-03-30 14:12:21
[2] => 2014-03-29 22:09:36
[3] => 2014-03-29 22:45:39
[4] => 2014-03-29 21:14:35
)
)
[T] => Array
(
[h] => 宝贝标题
[d] => Array
(
[0] =>
[1] => 易昇 天然和田白玉吊坠 龙 时尚男士吊坠纯银项链挂坠男 龙飞凤舞
[2] => 情侣吊坠玉坠纯银项链一对 生日礼物 男女款 心心相印金镶玉吊坠
[3] => 送男友生日礼物 天然黑曜石镶金龙吊坠 男士纯银项链龙挂坠项坠
[4] => 情侣吊坠玉坠纯银项链一对 生日礼物 男女款 心心相印金镶玉吊坠
)
)
[U] => Array
(
[h] => 宝贝种类
[d] => Array
(
[0] => 0
[1] => 1
[2] => 1
[3] => 1
[4] => 1
)
)
[V] => Array
(
[h] => 物流单号
[d] => Array
(
[0] =>
[1] => No:768333728128
[2] => No:768333728127
[3] => No:868031587631
[4] => No:768333728126
)
)
[W] => Array
(
[h] => 物流公司
[d] => Array
(
[0] =>
[1] => 申通E物流
[2] => 申通E物流
[3] => 申通E物流
[4] => 申通E物流
)
)
[X] => Array
(
[h] => 订单备注
[d] => Array
(
[0] =>
[1] =>
[2] => 【土豆】
[3] => 【土豆】刻字：姜松 金色链子 大概4月1号发货 不能有价格在里面 代写贺卡：亲爱的哥哥： 亲爱的哥哥加我最爱的哥哥 这是我送你的第一份礼物 也是最珍贵的一份礼物 我知道你一直想要一个吊坠 现在我没有钱给你买好的 只能买我的能力范围内的 不要给我乱花钱 也不要感动哦 做妹妹的只能以自己的方式爱你 虽然争吵不断 但这就是别人无法代替的亲情 将来的事情我们预料不到 但我一直坚信一点 你是我亲哥哥 我们之间的感情谁都破坏不掉 哥哥 我爱你
[4] => 【土豆】金链子，写贺卡，内容关于生日的就可以，希望3月5号收到货 包装好点
)
)
[Y] => Array
(
[h] => 宝贝总数量
[d] => Array
(
[0] => 0
[1] => 1
[2] => 1
[3] => 1
[4] => 1
)
)
[Z] => Array
(
[h] => 店铺Id
[d] => Array
(
[0] => 0
[1] => 0
[2] => 0
[3] => 0
[4] => 0
)
)
[AA] => Array
(
[h] => 店铺名称
[d] => Array
(
[0] =>
[1] =>
[2] =>
[3] =>
[4] =>
)
)
[AB] => Array
(
[h] => 订单关闭原因
[d] => Array
(
[0] => 买家未付款
[1] => 订单未关闭
[2] => 订单未关闭
[3] => 订单未关闭
[4] => 订单未关闭
)
)
[AC] => Array
(
[h] => 卖家服务费
$excel = PHPExcel_IOFactory::load('test.xls');
$sheet = $excel->getActiveSheet();
$w = $sheet->getHighestColumn(); //取得最大的列号
$h = $sheet->getHighestRow(); //取得一共有多少行
//列与字段对照表
$dict = array(
'A' => 'order_number',
'B' => 'member_name',
'C' => 'member_alipay',
'D' => 'member_payment',
'E' => 'member_postage',
'F' => 'membe_integral',
'G' => 'aggregate_amount',
'H' => 'rebate_integral',
'I' => 'member_actual_payments',
'J' => 'member_actual_integral',
'K' => 'order_status',
'L' => 'member_message',
'M' => 'consignee_name',
'N' => 'consignee_address',
'O' => 'Shipping_method',
'P' => 'telephone_number',
'Q' => 'mobile_phone',
'R' => 'create_time',
'S' => 'payment_time',
'T' => 'baby_title',
'U' => 'baby_species',
'V' => 'logistics_number',
'W' => 'logistics_company',
'X' => 'order_remarks',
'Y' => 'boby_number',
'Z' => 'shop_id',
'AA' => 'shop_name',
'AB' => 'order_closure',
'AC' => 'seller_service_fee',
'AD' => 'buyers_service_fee',
'AE' => 'commercial_invoice',
'AF' => 'phone_orders',
'AG' => 'Phase_order_information',
'AH' => 'deposit_ranking',
'AI' => 'modified_sku',
'AJ' => 'modified_orders_address',
'AK' => 'error_info',
);
$w++; //最大的列号加一
for($y=2; $y<=$h; $y++) { //按行读取
$t = array();
for($x='A'; $x!=$w; $x++) {
if($y == 1) $t[$dict[$x]] = $sheet->getCell("$x$y")->getValue();
elseif(in_array($x, array('R', 'S'))) {
$d = $sheet->getCell("$x$y")->getValue();
$t[$dict[$x]] = $d ? gmdate('Y-m-d H:i:s', ($d - 25569) * 86400) : '';
}else $t[$dict[$x]] = $sheet->getCell("$x$y")->getFormattedValue();
}
print_r($t); //这里可做入库操作
}
都是这样的格式Array
(
[order_number] => 593355528837832
[member_name] => wangfeilixin123
[member_alipay] => szxszglc@163.com
[member_payment] => 296
[member_postage] => 12
[membe_integral] => 0
[aggregate_amount] => 308
[rebate_integral] => 0
[member_actual_payments] => 0
[member_actual_integral] => 0
[order_status] => 交易关闭
[member_message] =>
[consignee_name] => 王飞
[consignee_address] => 辽宁省 葫芦岛市 绥中县 肖家菜市场东门王艳海鲜行(125200)
[Shipping_method] => 快递
[telephone_number] =>
[mobile_phone] => 18204296511
[create_time] => 2014-03-30 17:35:24
[payment_time] =>
[baby_title] =>
[baby_species] => 0
[logistics_number] =>
[logistics_company] =>
[order_remarks] =>
[boby_number] => 0
[shop_id] => 0
[shop_name] =>
[order_closure] => 买家未付款
[seller_service_fee] => 0
[buyers_service_fee] => 0元
[commercial_invoice] =>
[phone_orders] => 手机订单
[Phase_order_information] =>
[deposit_ranking] =>
[modified_sku] =>
[modified_orders_address] =>
[error_info] =>
)
