ChenJiaHe
2020-12-28 486aaaeb4d234e5a1267845b4aaa60354b2b92b0
提交 | 用户 | 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
165
5c5945 166     private static String objToString(Object obj) {
E 167         if (obj == null) {
168             return "";
169         } else {
170             if (obj instanceof String) {
171                 return (String) obj;
172             } else if (obj instanceof Date) {
173                 return null;// DateUtil.dateToString((Date)
174                             // obj,DateUtil.DATESTYLE_SHORT_EX);
175             } else {
176                 return obj.toString();
177             }
178         }
179     }
180
181     /**
182      * 读取 Excel文件内容
183      *
184      * @param file
185      * @param header 是否包括表头
186      * @return
187      * @throws Exception
188      */
189     public static List<List<String>> readExcelByeFileData(MultipartFile file, boolean header) throws Exception {
190
191         String fileName = file.getOriginalFilename();
192         if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
193             throw new TipsException("上传文件格式不正确");
194         }
195
196         // 结果集
197         List<List<String>> list = new ArrayList<>();
198
199         HSSFWorkbook hssfworkbook = new HSSFWorkbook(file.getInputStream());
200
201         // 遍历该表格中所有的工作表,i表示工作表的数量 getNumberOfSheets表示工作表的总数
202         for(int s=0;s<hssfworkbook.getNumberOfSheets();s++) {
203             HSSFSheet hssfsheet = hssfworkbook.getSheetAt(s);
204             int col = 0;
205             // 遍历该行所有的行,j表示行数 getPhysicalNumberOfRows行的总数 去除标题
206             for (int j = 0; j < hssfsheet.getPhysicalNumberOfRows(); j++) {
207                 HSSFRow hssfrow = hssfsheet.getRow(j);
208                 if(hssfrow!=null){
209                     if(j == 0) {
210                         col = hssfrow.getPhysicalNumberOfCells();
211                         if(!header) {
212                             //不包括表头
213                             continue;
214                         }
215                     }
216                     // 单行数据
217                     List<String> arrayString = new ArrayList<>();
218                     for (int i = 0; i < col; i++) {
219                         HSSFCell cell = hssfrow.getCell(i);
220                         if (cell == null) {
221                             arrayString.add("");
222                         } else if (cell.getCellType() == 0) {
223                             // arrayString[i] = new Double(cell.getNumericCellValue()).toString();
224                             if (HSSFCell.CELL_TYPE_NUMERIC == cell.getCellType()) {
225                                 short format = cell.getCellStyle().getDataFormat();
226                                 if(format == 14 || format == 31 || format == 57 || format == 58){
227                                     //日期(中文时间格式的)
228                                     Date d = cell.getDateCellValue();
229                                     DateFormat formater = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
230                                     // DateFormat formater = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
231                                     arrayString.add(formater.format(d));
232                                     //arrayString[i] = formater.format(d);
233                                 }else if (HSSFDateUtil.isCellDateFormatted(cell)) {
234                                     Date d = cell.getDateCellValue();
235                                     //DateFormat formater = new SimpleDateFormat("yyyy年MM月dd日 HH时mm分ss秒");
236                                     DateFormat formater = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
237                                     arrayString.add(formater.format(d));
238                                     //arrayString[i] = formater.format(d);
239                                 } else {
240                                     if(HSSFCell.CELL_TYPE_STRING == cell.getCellType()){
241                                         arrayString.add(cell.getStringCellValue());
242                                         //arrayString[i] =cell.getStringCellValue();
243                                     }else if(HSSFCell.CELL_TYPE_FORMULA==cell.getCellType()){
244                                         arrayString.add(cell.getCellFormula());
245                                         //arrayString[i] =cell.getCellFormula();
246                                     }else if(HSSFCell.CELL_TYPE_NUMERIC== cell.getCellType()){
247                                         HSSFDataFormatter dataFormatter = new HSSFDataFormatter();
248                                         arrayString.add(dataFormatter.formatCellValue(cell));
249                                         //arrayString[i] =dataFormatter.formatCellValue(cell);
250                                     }
251                                 }
252                             }
253                         } else if(cell.getCellType() == Cell.CELL_TYPE_BLANK){
254                             arrayString.add("");
255                             //arrayString[i] = "";
256                         } else { // 如果EXCEL表格中的数据类型为字符串型
257                             arrayString.add(cell.getStringCellValue().trim());
258                             //arrayString[i] = cell.getStringCellValue().trim();
259                         }
260                     }
261                     list.add(arrayString);
262                 }
263             }
264         }
265         return list;
266     }
267
268 }