excel表格上傳和下載，斷斷續續寫了很久，趕緊記下來萬一以後忘記就虧大了= =
數據庫有三張表：
上傳一張表格，每個sheet對應一個if_table_n,if_user_table記錄上傳信息，if_column_map記錄每個if_table_n的列名與數據庫列名對應，if_system_config記錄表格數目。
1 public function uploadFile() {
2
3 if(!empty($_FILES)) {
4 $upload = new \Think\Upload();// 實例化上傳類
5 $upload->maxSize = 1000000000 ;// 設置附件上傳大小
6 $upload->rootPath = './Uploads/'; // 設置附件上傳根目錄
7 // $upload->savePath = $filePath.'/'; // 設置附件上傳（子）目錄
8 $info = $upload->upload();
9
10 if($info === false) {// 上傳錯誤提示錯誤信息
11 $this->error("上傳錯誤：".$upload->getError(),"http://192.168.151.175/basicinfo",3);
12 }
13
14 //判斷文件是否為excel格式
15 $fileName = $info["file"]['name'];
16
17
18 $fileType = substr($fileName,strrpos($fileName, '.') + 1);
19
20 if(strtolower($fileType) !== "xls" && strtolower($fileType) !== "xlsx") {
21 $this->error("文件格式錯誤！");
22 }
23
24 //文件名
25 $filen=substr($fileName,0,strrpos($fileName, '.'));
26
27 //判斷引入何種格式的phpexcel 對應兩種版本的excel
28 import("Org.Util.PHPExcel");
29
30 $PHPExcel = new \PHPExcel();
31
32 if($fileType === "xlsx") {
33 //如果excel文件後綴名為.xlsx，導入類
34 import("Org.Util.PHPExcel.Reader.Excel2007");
35 $PHPReader=new \PHPExcel_Reader_Excel2007();
36 }
37 else {
38 import("Org.Util.PHPExcel.Reader.Excel5");
39 $PHPReader=new \PHPExcel_Reader_Excel5();
40 }
41
42
43 $PHPExcel=$PHPReader->load(SITE_PATH."Uploads/".$info["file"]["savepath"].$info["file"]['savename']);
44 // 確定當前excel文件的數量
45 $res = D('IfSystemConfig')->getValueByKey('table_count');
46
47 //獲取工作表個數
48 $sheetCount = $PHPExcel->getSheetCount();
49
50 //獲取sheet的名字
51 $sheetname = $PHPExcel->getSheetNames();
52
53 // 當前表數量字段,加上工作表的數量
54 $result = D('IfSystemConfig')
55 ->setValueByKey("table_count",intval($res[0]['value'])+ $sheetCount);
56
57 if($result === false) {
58 $this->error("數據上傳失敗！");
59 }
60
61 $unique_name_id = $res[0]['value'];
62
63 for($s = 0;$s<$sheetCount;$s++)
64 {
65 /**
66 * 保存表的信息
67 * @access public
68 * @param string $tablename 表名
69 * @param string $filename 文件名（全路徑）
70 * @return null
71 */
72 $PHPExcel=$PHPReader->load($filename);
73
74 //選擇工作表
75 $currentSheet = $PHPExcel->getSheet($sheetnum);
76
77 //獲取總列數
78 $allColumn=$currentSheet->getHighestColumn();
79
80 //獲取總行數
81 $allRow=$currentSheet->getHighestRow();
82
83 //獲取整張表，寫入二維數組arr中 arr[行][列]
84 for($currentRow=1;$currentRow<=$allRow;$currentRow++){
85 //從哪列開始，A表示第一列
86 for($currentColumn='A';$currentColumn<=$allColumn;$currentColumn++){
87 //數據坐標
88 $address=$currentColumn.$currentRow;
89 $cvalue = $currentSheet->getCell($address)->getValue();
90
91 //讀取到的數據，保存到數組$arr中
92 $arr[$currentRow][$currentColumn]=$cvalue;
93 }
94 }
95
96 // 表、列、代表含義的映射
97 // 列位自定義
98 //field_0 為自增形id
99 $j = 1;
100 $data['map_table'] = $tablename;
101 $data['col_name'] = "field_".'0';
102 $data['col_meaning'] = "";
103
104 //從field_1 .... field_n,對應excel列名
105 $res = D('IfColumnMap')->saveData($data);
106 foreach ($arr[1] as $key => $value) {
107 $data['col_name'] = "field_".$j;
108 $data['col_meaning'] = $arr[1][$key];
109 $res = D('IfColumnMap')->saveData($data);
110 $j++;
111 }
112
113 // 查找每個字段數據的最大長度
114 // 用來確定每個字段的長度
115 $t = 0;
116 foreach ($arr[2] as $key => $value) {
117 $ml = 0;
118 for($i = 2;$i <= count($arr);$i++) {
119 if(strlen($arr[$i][$key]) > $ml) {
120 $ml = strlen($arr[$i][$key]);
121 }
122 }
123 $maxLenght[$t] = $ml;
124 $t++;
125 }
126
127 // 如果長度大於256，就將字段類型設置為text類型
128 for($i = 0;$i < count($maxLenght); $i++) {
129
130 if($maxLenght[$i] > 256) {
131 $type[$i] = "text";
132 }
133 else {
134 $type[$i] = "varchar(".($maxLenght[$i]+15).")";
135 }
136 }
137
138 //建立if_table_n的sql語句
139 //utf-8編碼 default charset=utf8
140 //自增類型 int primary key not null auto_increment
141 $sqlString = "CREATE TABLE ".$tablename." ( ";
142 $sqlString .= "field_0"." "."int primary key not null auto_increment,";
143 $sqlString .= "field_1"." ".$type[0];
144 for($i = 1;$i < count($maxLenght);$i++) {
145 $sqlString .= ","."field_".($i+1)." ".$type[$i];
146 }
147 $sqlString .= ") default charset=utf8";
148
149 // 數據表創建
150 $Model = new \Think\Model(); // 實例化一個model對象 沒有對應任何數據表
151 $Model->execute($sqlString);
152
153 // 為新建的數據表if_table_n添加數據
154 for($i = 2;$i <= count($arr);$i++) {
155 $k = 1;
156 foreach ($arr[$i] as $key => $value) {
157 $info['field_'.$k] = $arr[$i][$key];
158 $k++;
159 }
160 M($tablename)->add($info);
161 }
162
163 // 插入 用戶、表 數據之間的關系
164 //if_user_table
165 $data = array(
166 'userid' => session('if_userid'),
167 'unique_name' => 'if_table_'.$unique_name_id,
168 'file_name' => $filen,
169 'save_name' => $info["file"]['savename'],
170 'save_path' => $info["file"]["savepath"],
171 'submit_time' => date("Y-m-d h:i:s"),
172 'tag' => 1,
173 'file_id' => $res[0]['value'],
174 'sheet' => $s,
175 'sheetname' => $sheetname[$s]
176 );
177
178 $result = D('IfUserTable')->saveData($data);
179
180 if($result === false) {
181 $this->error("數據上傳失敗！");
182 }
183 $unique_name_id++;
184 }
185
186 $this->success("上傳成功！",__APP__."/Home/Index/index");
187
188 }
下載此表格：
還有一些未用到的設置：
設置單元格寬度
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(20);
設置單元格高度
$objPHPExcel->getActiveSheet()->getRowDimension($i)->setRowHeight(40);
合並單元格
$objPHPExcel->getActiveSheet()->mergeCells('A18:E22');
拆分單元格
$objPHPExcel->getActiveSheet()->unmergeCells('A28:B28');
設置保護cell,保護工作表
$objPHPExcel->getActiveSheet()->getProtection()->setSheet(true);
$objPHPExcel->getActiveSheet()->protectCells('A3:E13', 'PHPExcel');
設置格式
$objPHPExcel->getActiveSheet()->getStyle('E4')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_EUR_SIMPLE);
$objPHPExcel->getActiveSheet()->duplicateStyle( $objPHPExcel->getActiveSheet()->getStyle('E4'), 'E5:E13' );
設置加粗
$objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setBold(true);
設置垂直居中
$objPHPExcel->getActiveSheet()->getStyle('A18')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
設置字號
$objPHPExcel->getActiveSheet()->getDefaultStyle()->getFont()->setSize(10);
設置邊框
$objPHPExcel->getActiveSheet()->getStyle('A1:I20')->getBorders()->getAllBorders()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN);
設置邊框顏色
$objPHPExcel->getActiveSheet()->getStyle('D13')->getBorders()->getLeft()->getColor()->setARGB('FF993300');
$objPHPExcel->getActiveSheet()->getStyle('D13')->getBorders()->getTop()->getColor()->setARGB('FF993300');
$objPHPExcel->getActiveSheet()->getStyle('D13')->getBorders()->getBottom()->getColor()->setARGB('FF993300');
$objPHPExcel->getActiveSheet()->getStyle('E13')->getBorders()->getTop()->getColor()->setARGB('FF993300');
$objPHPExcel->getActiveSheet()->getStyle('E13')->getBorders()->getBottom()->getColor()->setARGB('FF993300');
$objPHPExcel->getActiveSheet()->getStyle('E13')->getBorders()->getRight()->getColor()->setARGB('FF993300');
插入圖像
$objDrawing = new PHPExcel_Worksheet_Drawing();
/*設置圖片路徑 切記：只能是本地圖片*/
$objDrawing->setPath('圖像地址');
/*設置圖片高度*/
$objDrawing->setHeight(180);//照片高度
$objDrawing->setWidth(150); //照片寬度
/*設置圖片要插入的單元格*/
$objDrawing->setCoordinates('E2');
/*設置圖片所在單元格的格式*/
$objDrawing->setOffsetX(5);
$objDrawing->setRotation(5);
$objDrawing->getShadow()->setVisible(true);
$objDrawing->getShadow()->setDirection(50);
$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());
設置單元格背景色
$objPHPExcel->getActiveSheet(0)->getStyle('A1')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
$objPHPExcel->getActiveSheet(0)->getStyle('A1')->getFill()->getStartColor()->setARGB('FFCAE8EA');
1 public function downloadFile() {
2 $file_id = I('file_id');
3 $tablename = D('IfUserTable')->getNameByFileid($file_id);
4
5 import("Org.Util.PHPExcel");
6 //不清楚為什麼\PHPExcel()前要加\，不加會報錯，大哥也沒解釋清楚
7 $objPHPExcel = new \PHPExcel();
8 import("Org.Util.PHPExcel.Reader.Excel5");
9
10 //或者include 'PHPExcel/Writer/Excel5.php'; 用於輸出.xls的
11
12 // 實例化Create new PHPExcel object
13
14 /* @func 設置文檔基本屬性 */
15 $objPHPExcel->getProperties()
16 ->setCreator("ctos") //設置創建人
17 ->setLastModifiedBy("ctos") //最後修改人
18 ->setTitle("Office 2007 XLSX Test Document") //標題
19 ->setSubject("Office 2007 XLSX Test Document") //備注
20 ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.") //設置描述
21 ->setKeywords("office 2007 openxml php") //設置關鍵字 | 標記
22 ->setCategory("Test result file"); //設置類別
23
24
25 for ($i = 0; $i < count($tablename); $i++) {
26
27 $Model = new \Think\Model();
28
29 $name = $tablename[$i]['unique_name'];
30
31 $sqlString = "select * from ".$name;
32
33 $column_info[$i] = D('IfColumnMap')->getDataByTable($name);
34
35 $res[$i] = $Model->query($sqlString);
36
37 //首先要創建一個sheet的空間，否則都會寫在同一個sheet中
38 $objPHPExcel->createSheet();
39 $objPHPExcel->setActiveSheetIndex($i);
40
41 // 表頭寫入
42 for($currentColumn='A',$j=1;$j<count($column_info[$i]);$currentColumn++,$j++){
43
44 $colunmname = $column_info[$i][$j]['col_meaning'];
45
46 $objPHPExcel->getActiveSheet()
47 ->setCellValue($currentColumn.'1', $colunmname);
48 }
49
50 // 寫入內容 某個內容寫進An，Bn...
51 for($currentRow=2,$j=0;$currentRow<=count($res[$i])+1;$currentRow++,$j++){
52
53 for($currentColumn='A',$k=1;$k<count($column_info[$i]);$currentColumn++,$k++){
54 //設置單元格左對齊
55 $objPHPExcel->getActiveSheet()
56 ->getStyle($currentColumn. $currentRow)
57 ->getAlignment()
58 ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
59
60 $objPHPExcel->getActiveSheet()->setCellValue($currentColumn. $currentRow, $res[$i][$j]['field_'.($k)]);
61 }
62
63 }
64 //設置sheet的標題
65 $objPHPExcel->getActiveSheet()->setTitle($tablename[$i]['sheetname']);
66
67 ob_end_clean(); //清空緩存
68 }
69 header("Pragma: public");
70
71 header("Expires: 0");
72
73 header("Cache-Control:must-revalidate,post-check=0,pre-check=0");
74
75 header("Content-Type:application/force-download");
76
77 header("Content-Type:application/vnd.ms-execl");
78
79 header("Content-Type:application/octet-stream");
80
81 header("Content-Type:application/download");
82 //設置文件的名稱
83 header('Content-Disposition:attachment;filename='.$tablename['0']['file_name'].'.xls');
84
85 header("Content-Transfer-Encoding:binary");
86
87 //不清楚為什麼\PHPExcel_IOFactory前要加\，不加會報錯，大哥也沒解釋清楚
88 $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
89
90 $objWriter->save('php://output');
91 }
