//**讀取資料EXCEL

require 'vendor/autoload.php';

$inputFileName = $_SESSION["Upload_file"];//"/home/lifeservice/data/webfiles/file_.xls";//$_SESSION["Upload_file"];

//讀取EXCEL類

$PHPReader = new \PhpOffice\PhpSpreadsheet\Reader\Xls();

//讀取Excel

$PHPExcel = $PHPReader->load($inputFileName);

//讀取工作表1

$sheet = $PHPExcel->getSheet(0);

// 取得總列數

$talRow = $sheet->getHighestRow(); 

//最大欄位的英文代號

$colString = $sheet->getHighestColumn(); 

//最大欄位的數字編號。A=0, B=1, C=2....

$talCol = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($colString); 

if ($debug==1 AND $_SESSION["s_id"]=="tien")

echo "資料庫欄位共:".$_SESSION["pcnt"]."/匯入檔案共有筆數：$talRow,最後行名：$colString,行數：$talCol
";

 

//讀取一個欄位

$tpfldna = $sheet->getCellByColumnAndRow(($i),$fldline )->getValue();

//檢查主索引欄位是否有資料

$keyvalue = $sheet->getCellByColumnAndRow($keyidx, $irow)->getValue();

 

 

//**匯出EXCEL資料

if (isset($_SESSION["expfiletype"]) AND $_SESSION["expfiletype"]=="xlsx")

$exceltype = "xlsx";

else

$exceltype = "xls";

 

include("./lib/opendatapdo.php");

include_once("./lib/checkfunpdo.php");

include_once("rdlogpdo.php");

 

error_reporting(E_ALL);

date_default_timezone_set('Asia/Taipei');

/** PhpOffice\PhpSpreadsheet */

require 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;

use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

use PhpOffice\PhpSpreadsheet\IOFactory;

$outputFileName = date("Ymd",time()).'.'.$exceltype;

 

$PHPExcel = new Spreadsheet();

$currentSheet = $PHPExcel->getActiveSheet();

 

//寫入欄位

$fdst = Trxlsrow($i-1)."1";

$currentSheet->setCellValue($fdst,$_SESSION["fdstr"][$i][0]);

 

header("Content-Type: application/force-download");

header("Content-Type: application/octet-stream");

header("Content-Type: application/download");

header('Content-Disposition:attachment;filename="' .$outputFileName. '"');//輸出名稱

header("Content-Transfer-Encoding: binary");

header("Last-Modified:".gmdate("D, d M Y H:i:s")." GMT");

header('Pragma: public');

header('Expires: 30');

header('Cache-Control: public');

if ($exceltype == "xls")

$PHPWriter = IOFactory::createWriter($PHPExcel, 'Xls');

else

$PHPWriter = new Xlsx($PHPExcel);

$PHPWriter->save('php://output');

$PHPExcel->disconnectWorksheets();

unset($PHPExcel);

 

//**讀寫excel

require 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;

use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

use PhpOffice\PhpSpreadsheet\IOFactory;

 

//模板存放目錄

//$dir = "/home/aaaa/data/";

//2017.2.14修正由mg_config的homeroot設定

$dir = $_SESSION["homeroot"];

$timestr=time();

$ofno = substr($_REQUEST["idsn"],0,12);

$templateName = '/dataproc3/custform/ord2xlssam.xls';

$outputFileName = $_SESSION["formtemp"]["K_c_abbr"].$ofno.".".$exceltype;

 

//讀取Excel

$PHPExcel = \PhpOffice\PhpSpreadsheet\IOFactory::load($dir.$templateName);

//讀取工作表1

$currentSheet = $PHPExcel->setActiveSheetIndex(0);

//不分文字、數字

$currentSheet->setCellValue("P".$setrow,"TRUE");

//寫入數字TYPE_NUMERIC,文字:TYPE_STRING,公式:TYPE_FORMULA

$currentSheet->setCellValueExplicit($A_colna[$k].$setrow, (string)trim($celldata),\PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING);

 

 

// 設定字型大小

$currentSheet->getStyle("A1")->getFont()->setSize(11);

$currentSheet->getStyle("A2:B10")->getFont()->setSize(10);

// 粗體

$currentSheet->getActiveSheet()->getStyle("A1:B2")->getFont()->setBold(true);

$currentSheet->getActiveSheet()->getStyle("A6:I6")->getFont()->setBold(true);

$currentSheet->getActiveSheet()->getStyle("N2")->getFont()->setBold(false);

// 動態算位置技巧

$currentSheet->getActiveSheet()->getStyle("A".($rows_count).":O".($rows_count)."")->getFont()->setBold(true);

// 自動換行

$currentSheet->getActiveSheet()->getStyle("A3:B3")->getAlignment()->setWrapText(true);

 

 

header("Content-Type: application/force-download");

header("Content-Type: application/octet-stream");

header("Content-Type: application/download");

header('Content-Disposition:attachment;filename="' .$outputFileName. '"');//輸出名稱

header("Content-Transfer-Encoding: binary");

header("Last-Modified:".gmdate("D, d M Y H:i:s")." GMT");

header('Pragma: public');

header('Expires: 30');

header('Cache-Control: public');

if ($exceltype == "xls")

$PHPWriter = IOFactory::createWriter($PHPExcel, 'Xls');

else

$PHPWriter = new Xlsx($PHPExcel);

$PHPWriter->save('php://output');

$PHPExcel->disconnectWorksheets();

unset($PHPExcel);