导入
require_once './includes/reader.php';
// ExcelFile($filename, $encoding);
$data = new Spreadsheet_Excel_Reader();
// Set output Encoding.
$data->setOutputEncoding('gbk');
//”data.xls”是指要导入到mysql中的excel文件
$data->read('date.xls');
@ $db = mysql_connect('localhost', 'root', '1234') or
die("Could not connect to database.");//连接数据库
mysql_query("set names 'gbk'");//输出中文
mysql_select_db('wenhuaedu'); //选择数据库
error_reporting(E_ALL ^ E_NOTICE);
for ($i = 1; $i <= $data->sheets[0]['numRows']; $i++) {
//以下注释的for循环打印excel表数据
/*
for ($j = 1; $j <= $data->sheets[0]['numCols']; $j++) {
echo """.$data->sheets[0]['cells'][$i][$j]."",";
}
echo "n";
*/
//以下代码是将excel表数据【3个字段】插入到mysql中，根据你的excel表字段的多少，改写以下代码吧！
$sql = "INSERT INTO test VALUES('".
$data->sheets[0]['cells'][$i][1]."','".
$data->sheets[0]['cells'][$i][2]."','".
$data->sheets[0]['cells'][$i][3]."')";
echo $sql.'
';
$res = mysql_query($sql);
ini_set('memory_limit', '-1');
set_time_limit(0);
$callStartTime = microtime(true);
PHPExcel_CachedObjectStorageFactory::cache_in_memory_serialized;
$objExcel = new PHPExcel();
if (isset($datas) && isset($startdate) && isset($enddate)){
$title = time();
$objProps = $objExcel->getProperties();
$objProps->setCreator("cbsi ");
$objProps->setLastModifiedBy("report for finance");
$objProps->setTitle("report for finance");
$objProps->setSubject("report for finance");
$objProps->setDescription("report for finance");
$objProps->setKeywords("report for finance");
$objProps->setCategory("report for finance");
$objExcel->setActiveSheetIndex(0);
$objActSheet = $objExcel->getActiveSheet();
//设置当前活动sheet的名称
$objActSheet->setTitle('report for finance');
/设置标题
$already_income_amount = 0;
$invoice_amount = 0;
$The_invoices_amount = 0;
foreach ($datas as $key => $val){
$already_income_amount = $already_income_amount + $val['already_income_amount'];
$invoice_amount = $invoice_amount + $val['invoice_amount'];
$The_invoices_amount = $The_invoices_amount + $val['The_invoices_amount'];
}
foreach ($datas as $key => $val){
$key = $key+3;
//$objActSheet->setCellValue('A'.$key, $val['id']);
$objActSheet->setCellValue('A'.$key, $key-2);
$objActSheet->setCellValue('B'.$key, $val['company_name']);
$objActSheet->setCellValue('C'.$key, $val['bu_name']);
$objActSheet->setCellValue('D'.$key, $val['customer_name']);
$objActSheet->setCellValue('E'.$key, $val['contract_number']);
$objActSheet->setCellValue('F'.$key, $val['already_income_amount']);
$objActSheet->setCellValue('G'.$key, $val['invoice_amount']);
$objActSheet->setCellValue('H'.$key, $val['The_invoices_amount']);
$objActSheet->setCellValue('I'.$key, $val['Differences_note']);
$objExcel->getActiveSheet()->setSharedStyle(Custom_ExcelStyle::line(), "A".$key.":I".$key);
}
$count = count($datas)+3; //=27
$objActSheet->setCellValue("A"."$count", '总计');
$objActSheet->setCellValue("B"."$count", '-');
$objActSheet->setCellValue("C"."$count", '-');
$objActSheet->setCellValue("D"."$count", '-');
$objActSheet->setCellValue("E"."$count", '-');
$objActSheet->setCellValue("F"."$count", $already_income_amount);
$objActSheet->setCellValue("G"."$count", $invoice_amount);
$objActSheet->setCellValue("H"."$count", $The_invoices_amount);
$objActSheet->setCellValue("I"."$count", '-');
$objExcel->getActiveSheet()->setSharedStyle(Custom_ExcelStyle::total(), "A".$count.":I".$count);
//表格第一行内容
$objActSheet->setCellValue('A1', 'CCOM月度/季度/年度销售及发票开具情况统计表');
$objActSheet->setCellValue('A2', '日期：'.$startdate."到".$enddate);
//显式指定内容类型
//$objActSheet->setCellValueExplicit('A5', '847475847857487584',PHPExcel_Cell_DataType::TYPE_STRING);
//合并单元格
$objActSheet->mergeCells('A1:I1');
$objActSheet->mergeCells('A2:I2');
//设置表格内容居中
//$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
//分离单元格
//$objActSheet->unmergeCells('B1:C22');
//*************************************
//设置宽度
$objActSheet->getColumnDimension('A')->setWidth(10);
$objActSheet->getColumnDimension('B')->setWidth(40);
$objActSheet->getColumnDimension('C')->setWidth(30);
$objActSheet->getColumnDimension('D')->setWidth(40);
$objActSheet->getColumnDimension('E')->setWidth(20);
$objActSheet->getColumnDimension('F')->setWidth(30);
$objActSheet->getColumnDimension('G')->setWidth(30);
$objActSheet->getColumnDimension('H')->setWidth(40);
$objActSheet->getColumnDimension('I')->setWidth(30);
$objExcel->createSheet();
$objExcel->getActiveSheet()->setSharedStyle(Custom_ExcelStyle::head(), "A1:I3");
$objWriter = new PHPExcel_Writer_Excel($objExcel); $objWriter->setUseDiskCaching(true); $outputFileName = "download/excel/"; $outputFileName .= Custom_Session::Get('id')."invoice_issue".".xlsx"; $objWriter->save($outputFileName); //$objPHPExcel->disconnectWorksheets(); unset($objPHPExcel); } $back = array(1,$outputFileName); return $back;
如果觉得《php导入excel 代码 php excel 导入导出源代码》对你有帮助，请点赞、收藏，并留下你的观点哦！
