function save_detail_excel($sql, $file_name, $file_type = "xls")
{
global $db_conn;
if ($sql != "") {
set_time_limit(0);
ini_set('memory_limit', '600M');
$rows = mysqli_query($db_conn, $sql);
$row_counts_list = mysqli_num_rows($rows);
$field_count = mysqli_num_fields($rows);
$fields = mysqli_fetch_fields($rows);
if ($row_counts_list != 0) {
//文档类型
if ($file_type == "xls") {
//记录集个数小于2000，使用phpexcel生成xls
/*if ($row_counts_list<2000 and $field_count<12) {
include("../plugin/excel.php");
//当前活动表
$objPHPExcel->setActiveSheetIndex(0);
//sheet表名
$objPHPExcel->getActiveSheet()->setTitle("".$file_name."");
$field_count2=$field_count-1;
$objPHPExcel->getActiveSheet()->setSharedStyle($excel_tit_left, "A1:$tit_arr[$field_count2]1");
$objPHPExcel->getActiveSheet()->setSharedStyle($excel_list_left, "A2:$tit_arr[$field_count2]".($row_counts_list+1));
for ($i=0;$i<$field_count;$i++){
$objPHPExcel->getActiveSheet()->setCellValue("$tit_arr[$i]1","".$fields[$i]->name."");
}
$i=1;
while($rs= mysqli_fetch_array($rows)){
$i+=1;
for ($k=0;$k<$field_count;$k++){
$objPHPExcel->getActiveSheet()->getCell($tit_arr[$k].$i)->setValueExplicit($rs[$k], PHPExcel_Cell_DataType::TYPE_STRING);
}
//if($i%100==0){
// ob_flush();
// flush();
//}
}
$objPHPExcel->setActiveSheetIndex(0);
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$file_path=excel_file("".$file_name."_","xls");
$objWriter->save(str_replace('.php', '.xls',$file_path[2]));
}else{*/
//记录集个数大于2000，使用XML生成xls
include "Spreadsheet/Excel/Writer.php";
$file_path = excel_file("" . $file_name . "_", "xls");
$workbook = new Spreadsheet_Excel_Writer($file_path[2]);
$workbook->setCustomColor(20, 146, 208, 80);
$format_title_sty =& $workbook->addformat(array('Size' => 10, 'Bold' => 1, 'Border' => 1, 'FgColor' => 20, 'FontFamily' => utf82gb("宋体")));
$format_cont_sty =& $workbook->addformat(array('Size' => 10, 'Border' => 1, 'FontFamily' => utf82gb("宋体")));
$sheet_rows = 40000;
//单Sheet记录集个数
$sheet_id = 0;
$i = 0;
$sheet_i = 0;
while ($rs = mysqli_fetch_array($rows)) {
$i++;
$sheet_i++;
$row_ = $i % $sheet_rows;
if ($row_ == 1) {
if ($row_counts_list < $sheet_rows + 1) {
$sheet_id = "";
} else {
$sheet_id++;
}
$sheets =& $workbook->addWorksheet("" . utf82gb($file_name . $sheet_id) . "");
for ($f = 0; $f < $field_count; $f++) {
$sheets->writeString(0, $f, utf82gb($fields[$f]->name), $format_title_sty);
}
$sheet_i = 1;
}
for ($k = 0; $k < $field_count; $k++) {
$sheets->writeString($sheet_i, $k, utf82gb($rs[$k]), $format_cont_sty);
}
}
$workbook->close();
//}
} elseif ($file_type == "xml_xls") {
$file_type = "xls";
$file_path = excel_file("" . $file_name . "_", $file_type);
$fp = fopen($file_path[2], "w");
$sheet_rows = 40000;
//单Sheet记录集个数
$xml_head = "<?xml version=\"1.0\"?>\n<?mso-application progid=\"Excel.Sheet\"?>\n<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\" xmlns:o=\"urn:schemas-microsoft-com:office:office\" xmlns:x=\"urn:schemas-microsoft-com:office:excel\" xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\" xmlns:html=\"http://www.w3.org/TR/REC-html40\"><DocumentProperties xmlns=\"urn:schemas-microsoft-com:office:office\"><Title>详单数据导出</Title><Author>亚铭科技</Author><Category>详单、汇总</Category>\n<Company>亚铭科技</Company></DocumentProperties>\n<OfficeDocumentSettings xmlns=\"urn:schemas-microsoft-com:office:office\"><AllowPNG/> <RemovePersonalInformation/></OfficeDocumentSettings><ExcelWorkbook xmlns=\"urn:schemas-microsoft-com:office:excel\"> <WindowHeight>8010</WindowHeight><WindowWidth>14805</WindowWidth><WindowTopX>240</WindowTopX><WindowTopY>105</WindowTopY>\n<ProtectStructure>False</ProtectStructure><ProtectWindows>False</ProtectWindows></ExcelWorkbook>\n<Styles>\n<Style ss:ID=\"Default\" ss:Name=\"Normal\"><Alignment ss:Vertical=\"Bottom\"/><Borders/><Font ss:FontName=\"宋体\" x:CharSet=\"134\" ss:Size=\"10\" ss:Color=\"#000000\"/><Interior/><NumberFormat/><Protection/></Style><Style ss:ID=\"s71\"><Alignment ss:Vertical=\"Center\"/><Borders><Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/><Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/><Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/><Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/></Borders><Font ss:FontName=\"宋体\" x:CharSet=\"134\" ss:Color=\"#000000\"/><NumberFormat ss:Format=\"@\"/></Style><Style ss:ID=\"s72\"><Alignment ss:Vertical=\"Center\"/><Borders>\n<Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/><Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\n <Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/><Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/></Borders><Font ss:FontName=\"宋体\" x:CharSet=\"134\" ss:Color=\"#000000\" ss:Bold=\"1\"/> <Interior ss:Color=\"#92D050\" ss:Pattern=\"Solid\"/><NumberFormat ss:Format=\"@\"/></Style></Styles>";
fwrite($fp, $xml_head);
for ($i = 0; $i < $field_count; $i++) {
$filed_list .= "<Cell ss:StyleID=\"s72\"><Data ss:Type=\"String\">" . $fields[$i]->name . "</Data></Cell>";
}
$xml_sheet_head_row = "<Row>" . ($filed_list .= "</Row>");
$i = 0;
$sheet_id = 0;
while ($rs = mysqli_fetch_array($rows)) {
$i++;
$style = "";
$f_list = "";
$row_ = $i % $sheet_rows;
if ($row_ == 1) {
if ($row_counts_list < $sheet_rows + 1) {
$sheet_id = "";
} else {
$sheet_id++;
}
fwrite($fp, "\n<Worksheet ss:Name=\"" . $file_name . $sheet_id . "\">\n<Table x:FullRows=\"1\" ss:DefaultRowHeight=\"15\">" . $xml_sheet_head_row);
ob_flush();
flush();
}
for ($k = 0; $k < $field_count; $k++) {
$f_list .= "<Cell ss:StyleID=\"s71\"><Data ss:Type=\"String\">" . $rs[$k] . "</Data></Cell>";
}
fwrite($fp, "<Row>" . $f_list . "</Row>");
if ($row_ == 0) {
fwrite($fp, "</Table>\n<WorksheetOptions xmlns=\"urn:schemas-microsoft-com:office:excel\"><Unsynced/><Print><ValidPrinterInfo/>\n<PaperSizeIndex>9</PaperSizeIndex>\n<HorizontalResolution>-3</HorizontalResolution><VerticalResolution>0</VerticalResolution></Print><Selected/><Panes><Pane><Number>3</Number><ActiveCol>1</ActiveCol></Pane></Panes><ProtectObjects>False</ProtectObjects><ProtectScenarios>False</ProtectScenarios></WorksheetOptions></Worksheet>\n\n");
}
}
if ($row_ < $sheet_rows) {
fwrite($fp, "</Table>\n<WorksheetOptions xmlns=\"urn:schemas-microsoft-com:office:excel\"><Unsynced/><Print><ValidPrinterInfo/>\n<PaperSizeIndex>9</PaperSizeIndex>\n<HorizontalResolution>-3</HorizontalResolution><VerticalResolution>0</VerticalResolution></Print><Selected/><Panes><Pane><Number>3</Number><ActiveCol>1</ActiveCol></Pane></Panes><ProtectObjects>False</ProtectObjects><ProtectScenarios>False</ProtectScenarios></WorksheetOptions></Worksheet>\n\n");
}
fwrite($fp, "</Workbook>");
fclose($fp);
unset($xml_head);
unset($filed_list);
unset($f_list);
} elseif ($file_type == "csv") {
//生成Csv文档
$file_path = excel_file("" . $file_name . "_", $file_type);
$fp = fopen($file_path[2], "w");
for ($i = 0; $i < $field_count; $i++) {
$filed_list .= $fields[$i]->name . ",";
}
fwrite($fp, utf82gb($filed_list) . "\n");
$i = 1;
while ($rs = mysqli_fetch_array($rows)) {
$f_list = "";
$i++;
for ($k = 0; $k < $field_count; $k++) {
$f_list .= $rs[$k] . ",";
}
$row_list = $f_list . "\n";
fwrite($fp, utf82gb($row_list));
if ($i % 100 == 0) {
ob_flush();
flush();
}
}
fclose($fp);
unset($f_list);
unset($row_list);
} elseif ($file_type == "txt_n") {
$file_path = excel_file("" . $file_name . "_", "txt");
$fp = fopen($file_path[2], "w");
$filed_list = "C011|130002|861300005101|dxhbfc|客户姓名|客户姓别|客户生日|客户手机号|销售日期|0|职业";
fwrite($fp, utf82gb($filed_list) . "\r\n");
$i = 1;
while ($rs = mysqli_fetch_array($rows)) {
$f_list = "";
$i++;
for ($k = 0; $k < $field_count; $k++) {
$f_list .= $rs[$k] . "|";
}
$row_list = substr($f_list, 0, -1) . "\r\n";
fwrite($fp, utf82gb($row_list));
if ($i % 100 == 0) {
ob_flush();
flush();
}
}
fclose($fp);
unset($f_list);
unset($row_list);
} else {
//生成TXT文档
$file_path = excel_file("" . $file_name . "_", $file_type);
$fp = fopen($file_path[2], "w");
for ($i = 0; $i < $field_count; $i++) {
$filed_list .= $fields[$i]->name . "\t";
}
fwrite($fp, utf82gb($filed_list) . "\r\n");
$i = 1;
while ($rs = mysqli_fetch_array($rows)) {
$f_list = "";
$i++;
for ($k = 0; $k < $field_count; $k++) {
$f_list .= $rs[$k] . "\t";
}
$row_list = $f_list . "\r\n";
fwrite($fp, utf82gb($row_list));
if ($i % 100 == 0) {
ob_flush();
flush();
}
}
fclose($fp);
unset($f_list);
unset($row_list);
}
$do_res = array("counts" => "1", "file_path" => gb2utf8(str_replace("./data/", "/data/", $file_path[1])), "file_name" => gb2utf8($file_path[0]), "des" => "文件导出完成，请点击下载！");
} else {
$do_res = array("counts" => "0", "file_path" => "", "file_name" => "", "des" => "未找到符合条件的数据...");
}
} else {
$do_res = array("counts" => "-1", "file_path" => "", "file_name" => "", "des" => "数据查询条件有误，请检查后重试...");
}
return $do_res;
mysqli_free_result($rows);
}
function process_file($filename, &$tempname)
{
global $_SGLOBAL;
$isfile = 1;
$data = new Spreadsheet_Excel_Reader();
$data->setOutputEncoding('UTF-8');
$data->read($filename);
$tempname = $_SGLOBAL['timestamp'] . '_download_' . rand() . '.xls';
$filedownload = S_ROOT . './plugin/invite/download/' . $tempname;
//初始化一个反馈结果的文件
require_once S_ROOT . './plugin/invite/phpwritexcel/Writer.php';
$workbook = new Spreadsheet_Excel_Writer($filedownload);
$worksheet =& $workbook->addWorksheet('Sheet1');
$worksheet->setInputEncoding('UTF-8');
$format_column =& $workbook->addformat(array('Size' => 9, 'Bold' => 1));
//读取结果文件的信息
$rows = 0;
$recordindex = array('realname', 'sex', 'birthday', 'otheremail', 'academy', 'startyear', 'collegeid', 'class', 'mobile', 'unit');
$record = array('好友姓名（必填）', '好友性别', '生日（8位）', '邮箱（必填）', '学院', '入学年份', '学号', '班别', '手机（必填）', '所在单位', '备注');
$dataindexinfo = array();
encode_record(&$record);
input_downloadfile($worksheet, $rows, $record, $format_column);
for ($i = 2; $i <= $data->sheets[0]['numRows']; $i++) {
$datainfo = array();
for ($j = 1; $j <= $data->sheets[0]['numCols']; $j++) {
$datainfo[$j - 1] = $data->sheets[0]['cells'][$i][$j];
$dataindexinfo[$recordindex[$j - 1]] = $data->sheets[0]['cells'][$i][$j];
}
$nopass = checkinfo($datainfo[0], $datainfo[3], $datainfo[8]);
if (!empty($nopass)) {
$datainfo[$j - 1] = $nopass;
encode_record(&$datainfo);
input_downloadfile($worksheet, &$rows, $datainfo, $format_column);
continue;
}
$dataindexinfo[inviter] = $_SGLOBAL[supe_uid];
$dataindexinfo[usertype] = '6';
inserttable('baseprofile', $dataindexinfo, 1);
invite_alumni($datainfo);
}
$workbook->close();
return $rows;
}
function process_file($filename, &$tempname)
{
global $_SGLOBAL;
$isfile = 1;
$data = new Spreadsheet_Excel_Reader();
//exit('adsf');
$data->setOutputEncoding('gbk');
$data->read($filename);
$tempname = date("Y-m-d") . '_download_' . rand() . '.xls';
$filedownload = S_ROOT . './plugin/invite/download/' . $tempname;
//初始化一个反馈结果的文件
require_once S_ROOT . './plugin/invite/phpwritexcel/Writer.php';
$workbook = new Spreadsheet_Excel_Writer($filedownload);
$worksheet =& $workbook->addWorksheet('Sheet1');
$worksheet->setInputEncoding('utf-8');
$format_column =& $workbook->addformat(array('Size' => 9, 'Bold' => 1));
//读取结果文件的信息
$rows = 0;
$recordindex = array('realname', 'sex', 'birthday', 'otheremail', 'academy', 'collegeid', 'class', 'mobile', 'unit');
$record = array('好友姓名（必填）', '好友性别', '生日（8位）', '邮箱（必填）', '学院', '学号', '班别', '手机（必填）', '所在单位', '备注');
$dataindexinfo = array();
encode_record(&$record);
input_downloadfile($worksheet, $rows, $record, $format_column);
for ($i = 2; $i <= $data->sheets[0]['numRows']; $i++) {
//以下注释的for循环打印excel表数据
$datainfo = array();
//$datainfo = array('name', 'sex', 'birthday', 'otheremail', 'academy', 'collegeid', 'class', 'mobile', 'unit');
for ($j = 1; $j <= $data->sheets[0]['numCols']; $j++) {
$datainfo[$j - 1] = $data->sheets[0]['cells'][$i][$j];
$dataindexinfo[$recordindex[$j - 1]] = $data->sheets[0]['cells'][$i][$j];
}
//var_dump($datainfo[0]);
//检查记录的正确性
$nopass = checkinfo($datainfo[0], $datainfo[2], $datainfo[3], $datainfo[7], $isfile);
if (!empty($nopass)) {
//没有通过的记录加入到一个新文件中
$datainfo[$j - 1] = $nopass;
encode_record(&$datainfo);
//var_dump($datainfo);
//var_dump($rows);
input_downloadfile($worksheet, &$rows, $datainfo, $format_column);
continue;
}
//邀请处理
//var_dump($dataindexinfo);
inserttable('baseprofile', $dataindexinfo, 1);
//var_dump($datainfo);
invite_alumni($datainfo);
}
$workbook->close();
return $rows;
}
<?php
//加载类
require_once 'Spreadsheet/Excel/Writer.php';
//初始化类
$workbook = new Spreadsheet_Excel_Writer();
//设置版本
$workbook->setVersion(8);
//设置字体
$format_title =& $workbook->addformat(array('Size' => 10, 'Bold' => 1, 'Color' => 'red'));
//添加一个工作表
$worksheet =& $workbook->addWorksheet('sheet-1');
//设置列宽
$worksheet->setColumn(0, 19, 13);
//冻结行列
$worksheet->freezePanes(array(3, 5));
//追加数据
$worksheet->writeString(0, 0, '対象社員一覧（一次評価）', $format_title);
// 发送 Excel 文件名供下载
$workbook->send('demo.xls');
// 完成下载
$workbook->close();
