ChenJiaHe
2021-01-27 132d1a41ca120a6bc212a43ea2925e51128abbb7
提交 | 用户 | age
5c5945 1 package com.hx.util;
E 2
3 import java.io.File;
4 import java.io.FileInputStream;
5 import java.io.FileOutputStream;
6 import java.text.DateFormat;
7 import java.text.SimpleDateFormat;
8 import java.util.ArrayList;
9 import java.util.Date;
10
11 import java.util.List;
12 import java.util.Map;
13
14 import com.hx.exception.TipsException;
15 import org.apache.poi.hssf.usermodel.*;
16 import org.apache.poi.ss.usermodel.Cell;
17 import org.springframework.web.multipart.MultipartFile;
18
19
20 /**
21  * 
22  * @author hjr
23  */
24 public final class ExcelUtil {
25
26     /**
27      * @param excelName
28      *         文件名称
29      * @param outPath
30      *             保存路径
31      * @param headList
32      *        Excel文件Head标题集合
33      * @param fieldList
34      *        Excel文件Field标题集合 根据field来寻找位置填充表格
35      * @param dataList
36      *        Excel文件数据内容部分
37      * @throws Exception
38      */
39     public static String createExcel(String outPath, String excelName,
40             String[] headList, String[] fieldList,
41             List<Map<String, Object>> dataList) throws Exception {
42
43         String filePath = null;
44         // 创建新的Excel 工作簿
45         HSSFWorkbook workbook = new HSSFWorkbook();
46
47         // 在Excel工作簿中建一工作表,其名为缺省值
48         // 如要新建一名为"效益指标"的工作表,其语句为:
49         // HSSFSheet sheet = workbook.createSheet("效益指标");
50         HSSFSheet sheet = workbook.createSheet();
51         // 在索引0的位置创建行(最顶端的行)
52         HSSFRow row = sheet.createRow(0);
53         // ===============================================================
54         for (int i = 0; i < headList.length; i++) {
55
56             // 在索引0的位置创建单元格(左上端)
57             HSSFCell cell = row.createCell(i);
58             // 定义单元格为字符串类型
59             cell.setCellType(HSSFCell.CELL_TYPE_STRING);
60             // 在单元格中输入一些内容
61             cell.setCellValue(headList[i]);
62         }
63         // ===============================================================
64         if (dataList != null) {
65             for (int n = 0; n < dataList.size(); n++) {
66                 // 在索引1的位置创建行
67                 HSSFRow row_value = sheet.createRow(n + 1);
68                 Map<String, Object> dataMap = dataList.get(n);
69                 // ===============================================================
70                 for (int i = 0; i < fieldList.length; i++) {
71                     // 在索引0的位置创建单元格(左上端)
72                     HSSFCell cell = row_value.createCell(i);
73                     // 定义单元格为字符串类型
74                     cell.setCellType(HSSFCell.CELL_TYPE_STRING);
75                     // 在单元格中输入一些内容
76                     cell.setCellValue(objToString(dataMap.get(fieldList[i])));
77                 }
78                 // ===============================================================
79             }
80         }
81
82         // 新建一输出文件流
83         File file = SimpleTool.createFile(outPath,excelName);
84         FileOutputStream fOut = new FileOutputStream(file);
85         // 把相应的Excel 工作簿存盘
86         workbook.write(fOut);
87         fOut.flush();
88         // 操作结束,关闭文件
89         fOut.close();
90
91         if(outPath.endsWith("/")){
92             filePath = outPath + excelName;
93         }else{
94             filePath = outPath +"/"+ excelName;
95         }
96         return filePath;
97     }
98
db7fc9 99     /**生成临时文件
C 100      * @param headList
101      *        Excel文件Head标题集合
102      * @param fieldList
103      *        Excel文件Field标题集合 根据field来寻找位置填充表格
104      * @param dataList
105      *        Excel文件数据内容部分
106      * @throws Exception
107      */
108     public static File createExcel(String[] headList, String[] fieldList,
109                                      List<Map<String, Object>> dataList) throws Exception {
110         File file = File.createTempFile("temp", ".xls");
111         try{
112             // 创建新的Excel 工作簿
113             HSSFWorkbook workbook = new HSSFWorkbook();
114
115             // 在Excel工作簿中建一工作表,其名为缺省值
116             // 如要新建一名为"效益指标"的工作表,其语句为:
117             // HSSFSheet sheet = workbook.createSheet("效益指标");
118             HSSFSheet sheet = workbook.createSheet();
119             // 在索引0的位置创建行(最顶端的行)
120             HSSFRow row = sheet.createRow(0);
121             // ===============================================================
122             for (int i = 0; i < headList.length; i++) {
123
124                 // 在索引0的位置创建单元格(左上端)
125                 HSSFCell cell = row.createCell(i);
126                 // 定义单元格为字符串类型
127                 cell.setCellType(HSSFCell.CELL_TYPE_STRING);
128                 // 在单元格中输入一些内容
129                 cell.setCellValue(headList[i]);
130             }
131             // ===============================================================
132             if (dataList != null) {
133                 for (int n = 0; n < dataList.size(); n++) {
134                     // 在索引1的位置创建行
135                     HSSFRow row_value = sheet.createRow(n + 1);
136                     Map<String, Object> dataMap = dataList.get(n);
137                     // ===============================================================
138                     for (int i = 0; i < fieldList.length; i++) {
139                         // 在索引0的位置创建单元格(左上端)
140                         HSSFCell cell = row_value.createCell(i);
141                         // 定义单元格为字符串类型
142                         cell.setCellType(HSSFCell.CELL_TYPE_STRING);
143                         // 在单元格中输入一些内容
144                         cell.setCellValue(objToString(dataMap.get(fieldList[i])));
145                     }
146                     // ===============================================================
147                 }
148             }
149
150             // 新建一输出文件流
151             FileOutputStream fOut = new FileOutputStream(file);
152             // 把相应的Excel 工作簿存盘
153             workbook.write(fOut);
154             fOut.flush();
155             // 操作结束,关闭文件
156             fOut.close();
157         }catch (Exception e){
158
159         }finally {
160             file.deleteOnExit();
161         }
162         return file;
163     }
164
49f28b 165     /**生成临时文件
C 166      * @param headList
167      *        Excel文件Head标题集合
168      * @param fieldList
169      *        Excel文件Field标题集合 根据field来寻找位置填充表格
170      * @param dataList
171      *        Excel文件数据内容部分
172      * @throws Exception
173      */
174     public static File createExcel(String[] headList, String[] fieldList, List<Map<String, Object>> dataList
175             ,Integer height,Integer width) throws Exception {
176         File file = File.createTempFile("temp", ".xls");
177         try{
178
179             if(height == null){
180                 height = 450;
181             }
182             if(width == null){
183                 width = 5000;
184             }
185             // 创建新的Excel 工作簿
186             HSSFWorkbook workbook = new HSSFWorkbook();
187
188             //合并的单元格样式
189             HSSFCellStyle boderStyle = workbook.createCellStyle();
190             //垂直居中
191             boderStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
192             boderStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
193
194             // 在Excel工作簿中建一工作表,其名为缺省值
195             // 如要新建一名为"效益指标"的工作表,其语句为:
196             // HSSFSheet sheet = workbook.createSheet("效益指标");
197             HSSFSheet sheet = workbook.createSheet();
198             // 在索引0的位置创建行(最顶端的行)
199             HSSFRow row = sheet.createRow(0);
200             // ===============================================================
201             for (int i = 0; i < headList.length; i++) {
202                 //高度
203                 row.setHeight(height.shortValue());
204                 sheet.setColumnWidth(i,width);
205                 // 在索引0的位置创建单元格(左上端)
206                 HSSFCell cell = row.createCell(i);
207                 // 定义单元格为字符串类型
208                 cell.setCellType(HSSFCell.CELL_TYPE_STRING);
209                 // 在单元格中输入一些内容
210                 cell.setCellValue(headList[i]);
211                 cell.setCellStyle(boderStyle);
212             }
213             // ===============================================================
214             if (dataList != null) {
215                 for (int n = 0; n < dataList.size(); n++) {
216                     // 在索引1的位置创建行
217                     HSSFRow row_value = sheet.createRow(n + 1);
218                     row_value.setHeight(height.shortValue());
219                     Map<String, Object> dataMap = dataList.get(n);
220                     // ===============================================================
221                     for (int i = 0; i < fieldList.length; i++) {
222                         // 在索引0的位置创建单元格(左上端)
223                         sheet.setColumnWidth(i,width);
224                         HSSFCell cell = row_value.createCell(i);
225                         // 定义单元格为字符串类型
226                         cell.setCellType(HSSFCell.CELL_TYPE_STRING);
227                         // 在单元格中输入一些内容
228                         cell.setCellValue(objToString(dataMap.get(fieldList[i])));
229                         cell.setCellStyle(boderStyle);
230                     }
231                     // ===============================================================
232                 }
233             }
234
235             // 新建一输出文件流
236             FileOutputStream fOut = new FileOutputStream(file);
237             // 把相应的Excel 工作簿存盘
238             workbook.write(fOut);
239             fOut.flush();
240             // 操作结束,关闭文件
241             fOut.close();
242         }catch (Exception e){
243
244         }finally {
245             file.deleteOnExit();
246         }
247         return file;
248     }
249
db7fc9 250
5c5945 251     private static String objToString(Object obj) {
E 252         if (obj == null) {
253             return "";
254         } else {
255             if (obj instanceof String) {
256                 return (String) obj;
257             } else if (obj instanceof Date) {
258                 return null;// DateUtil.dateToString((Date)
259                             // obj,DateUtil.DATESTYLE_SHORT_EX);
260             } else {
261                 return obj.toString();
262             }
263         }
264     }
265
266     /**
267      * 读取 Excel文件内容
268      *
269      * @param file
270      * @param header 是否包括表头
271      * @return
272      * @throws Exception
273      */
274     public static List<List<String>> readExcelByeFileData(MultipartFile file, boolean header) throws Exception {
275
276         String fileName = file.getOriginalFilename();
277         if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
278             throw new TipsException("上传文件格式不正确");
279         }
280
281         // 结果集
282         List<List<String>> list = new ArrayList<>();
283
284         HSSFWorkbook hssfworkbook = new HSSFWorkbook(file.getInputStream());
285
286         // 遍历该表格中所有的工作表,i表示工作表的数量 getNumberOfSheets表示工作表的总数
287         for(int s=0;s<hssfworkbook.getNumberOfSheets();s++) {
288             HSSFSheet hssfsheet = hssfworkbook.getSheetAt(s);
289             int col = 0;
290             // 遍历该行所有的行,j表示行数 getPhysicalNumberOfRows行的总数 去除标题
291             for (int j = 0; j < hssfsheet.getPhysicalNumberOfRows(); j++) {
292                 HSSFRow hssfrow = hssfsheet.getRow(j);
293                 if(hssfrow!=null){
294                     if(j == 0) {
295                         col = hssfrow.getPhysicalNumberOfCells();
296                         if(!header) {
297                             //不包括表头
298                             continue;
299                         }
300                     }
301                     // 单行数据
302                     List<String> arrayString = new ArrayList<>();
303                     for (int i = 0; i < col; i++) {
304                         HSSFCell cell = hssfrow.getCell(i);
305                         if (cell == null) {
306                             arrayString.add("");
307                         } else if (cell.getCellType() == 0) {
308                             // arrayString[i] = new Double(cell.getNumericCellValue()).toString();
309                             if (HSSFCell.CELL_TYPE_NUMERIC == cell.getCellType()) {
310                                 short format = cell.getCellStyle().getDataFormat();
311                                 if(format == 14 || format == 31 || format == 57 || format == 58){
312                                     //日期(中文时间格式的)
313                                     Date d = cell.getDateCellValue();
314                                     DateFormat formater = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
315                                     // DateFormat formater = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
316                                     arrayString.add(formater.format(d));
317                                     //arrayString[i] = formater.format(d);
318                                 }else if (HSSFDateUtil.isCellDateFormatted(cell)) {
319                                     Date d = cell.getDateCellValue();
320                                     //DateFormat formater = new SimpleDateFormat("yyyy年MM月dd日 HH时mm分ss秒");
321                                     DateFormat formater = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
322                                     arrayString.add(formater.format(d));
323                                     //arrayString[i] = formater.format(d);
324                                 } else {
325                                     if(HSSFCell.CELL_TYPE_STRING == cell.getCellType()){
326                                         arrayString.add(cell.getStringCellValue());
327                                         //arrayString[i] =cell.getStringCellValue();
328                                     }else if(HSSFCell.CELL_TYPE_FORMULA==cell.getCellType()){
329                                         arrayString.add(cell.getCellFormula());
330                                         //arrayString[i] =cell.getCellFormula();
331                                     }else if(HSSFCell.CELL_TYPE_NUMERIC== cell.getCellType()){
332                                         HSSFDataFormatter dataFormatter = new HSSFDataFormatter();
333                                         arrayString.add(dataFormatter.formatCellValue(cell));
334                                         //arrayString[i] =dataFormatter.formatCellValue(cell);
335                                     }
336                                 }
337                             }
338                         } else if(cell.getCellType() == Cell.CELL_TYPE_BLANK){
339                             arrayString.add("");
340                             //arrayString[i] = "";
341                         } else { // 如果EXCEL表格中的数据类型为字符串型
342                             arrayString.add(cell.getStringCellValue().trim());
343                             //arrayString[i] = cell.getStringCellValue().trim();
344                         }
345                     }
346                     list.add(arrayString);
347                 }
348             }
349         }
350         return list;
351     }
352
353 }