Java使用POI导出Excel .xlsx提示文件格式或文件扩展名有误
分类:
IT文章
•
2024-05-14 10:55:48
1.前端如果用xlsx格式接收表格 后台用XSSFWorkbook workbook = new XSSFWorkbook();创建工作薄
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.addHeader("Content-Disposition", "attachment;filename=fileName" + ".xlsx");
2.前端如果用xls格式接收表格 后台 用HSSFWorkbook workbook = new HSSFWorkbook();创建工作薄
response.setContentType("application/vnd.ms-excel");
response.addHeader("Content-Disposition", "attachment;filename=fileName"+".xls");
步骤：
![]()
1 $("#exportBtn").click(function() {
2 var start_tjsj = $('#start_tjsj').val();
3 var end_tjsj = $('#end_tjsj').val();
4
5 var searchCondition = $('#searchCondition option:selected').val();
6 window.parent.location.href = "../QtAjtj/exportQtAgtj?start_tjsj=" + start_tjsj+ "&end_tjsj=" + end_tjsj;
7
8 });
View Code
![]()
1 public void exportAytj(List<Aytj> aytjlist,String titleName) {
2 ServletOutputStream out=null;
3
4 //向指定的Excel中写入数据
5 //设置最大数据行数
6 SXSSFWorkbook wb = new SXSSFWorkbook(5000);
7
8 try {
9
10 Map<String,List<CellModel>> map = new HashMap<String,List<CellModel>>();
11 // 设置数据
12 List<CellModel> firstRow = new ArrayList<CellModel>();
13 CellModel cellModel1 = new CellModel();
14 //总占用2行
15 Integer cellRow = 2;
16 cellModel1.setCellName("案由");
17 cellModel1.setStartRow(1);
18 cellModel1.setEndRow(2);
19 cellModel1.setStartColumn(0);
20 cellModel1.setEndColumn(0);
21
22 CellModel cellModel2 = new CellModel();
23 cellModel2.setCellName("侦监");
24 cellModel2.setStartRow(1);
25 cellModel2.setEndRow(1);
26 cellModel2.setStartColumn(1);
27 cellModel2.setEndColumn(4);
28
29 CellModel cellModel3 = new CellModel();
30 cellModel3.setCellName("公诉");
31 cellModel3.setStartRow(1);
32 cellModel3.setEndRow(1);
33 cellModel3.setStartColumn(5);
34 cellModel3.setEndColumn(8);
35
36 CellModel cellModel4 = new CellModel();
37 cellModel4.setCellName("合计");
38 cellModel4.setStartRow(1);
39 cellModel4.setEndRow(2);
40 cellModel4.setStartColumn(9);
41 cellModel4.setEndColumn(9);
42
43 firstRow.add(cellModel1);
44 firstRow.add(cellModel2);
45 firstRow.add(cellModel3);
46 firstRow.add(cellModel4);
47 map.put("0", firstRow);
48
49 List<CellModel> secondRow = new ArrayList<CellModel>();
50 CellModel cellModel5 = new CellModel();
51 cellModel5.setCellName("收案");
52 cellModel5.setStartRow(2);
53 cellModel5.setEndRow(2);
54 cellModel5.setStartColumn(1);
55 cellModel5.setEndColumn(1);
56
57 CellModel cellModel6 = new CellModel();
58 cellModel6.setCellName("分案");
59 cellModel6.setStartRow(2);
60 cellModel6.setEndRow(2);
61 cellModel6.setStartColumn(2);
62 cellModel6.setEndColumn(2);
63
64 CellModel cellModel7 = new CellModel();
65 cellModel7.setCellName("在办");
66 cellModel7.setStartRow(2);
67 cellModel7.setEndRow(2);
68 cellModel7.setStartColumn(3);
69 cellModel7.setEndColumn(3);
70
71 CellModel cellModel8 = new CellModel();
72 cellModel8.setCellName("结案");
73 cellModel8.setStartRow(2);
74 cellModel8.setEndRow(2);
75 cellModel8.setStartColumn(4);
76 cellModel8.setEndColumn(4);
77
78 CellModel cellModel9 = new CellModel();
79 cellModel9.setCellName("收案");
80 cellModel9.setStartRow(2);
81 cellModel9.setEndRow(2);
82 cellModel9.setStartColumn(5);
83 cellModel9.setEndColumn(5);
84
85 CellModel cellModel10 = new CellModel();
86 cellModel10.setCellName("分案");
87 cellModel10.setStartRow(2);
88 cellModel10.setEndRow(2);
89 cellModel10.setStartColumn(6);
90 cellModel10.setEndColumn(6);
91
92 CellModel cellModel11 = new CellModel();
93 cellModel11.setCellName("在办");
94 cellModel11.setStartRow(2);
95 cellModel11.setEndRow(2);
96 cellModel11.setStartColumn(7);
97 cellModel11.setEndColumn(7);
98
99 CellModel cellModel12 = new CellModel();
100 cellModel12.setCellName("结案");
101 cellModel12.setStartRow(2);
102 cellModel12.setEndRow(2);
103 cellModel12.setStartColumn(8);
104 cellModel12.setEndColumn(8);
105
106 secondRow.add(cellModel5);
107 secondRow.add(cellModel6);
108 secondRow.add(cellModel7);
109 secondRow.add(cellModel8);
110 secondRow.add(cellModel9);
111 secondRow.add(cellModel10);
112 secondRow.add(cellModel11);
113 secondRow.add(cellModel12);
114 map.put("1", secondRow);
115
116
117 List<LinkedHashMap> exportData = new ArrayList<LinkedHashMap>();
118 if(aytjlist != null && !aytjlist.isEmpty()) {
119 for (int i = 0; i < aytjlist.size(); i++) {
120
121 Aytj aytj = aytjlist.get(i);
122 LinkedHashMap<String, Object> rowPut = new LinkedHashMap<String, Object>();
123 rowPut.put("1", aytj.getAy());
124 rowPut.put("2", aytj.getZjsacount());
125 rowPut.put("3", aytj.getZjfacount());
126 rowPut.put("4", aytj.getZjzbcount());
127 rowPut.put("5", aytj.getZjbjcount());
128
129 rowPut.put("6", aytj.getGssacount());
130 rowPut.put("7", aytj.getGsfacount());
131 rowPut.put("8", aytj.getGszbcount());
132 rowPut.put("9", aytj.getGsbjcount());
133 rowPut.put("10", aytj.getAjcount());
134 exportData.add(rowPut);
135 }
136 }
137 Integer columnSize = 10;
138
139
140 wb = ExcelUtils.createCSVUtil("案由统计",titleName,wb, map, cellRow,columnSize, exportData,"aytj");
141
142 SimpleDateFormat format = new SimpleDateFormat("yyyyMMddHHmmss");
143 String fileName = "案由统计_" +format.format(new Date()) ;
144 //解决IE文件名乱码
145 String userAgent = request.getHeader("user-agent");
146 if (userAgent != null && userAgent.indexOf("Firefox") >= 0 || userAgent.indexOf("Chrome") >= 0
147 || userAgent.indexOf("Safari") >= 0) {
148 fileName= new String(fileName.getBytes("utf-8"),"ISO8859_1");
149 } else {
150 fileName= URLEncoder.encode(fileName,"UTF8"); //其他浏览器
151
152 }
153 response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
154 response.addHeader("Content-Disposition", "attachment;filename=" +fileName+ ".xlsx");
155 /* response.reset();
156 //设置response的Header
157 response.addHeader("Content-Disposition", "attachment;filename="+ fileName + ".xls");
158 //out = new BufferedOutputStream(response.getOutputStream());*/
159 out=response.getOutputStream();
160 // response.setContentType("application/vnd.ms-excel;charset=utf-8");
161
162 //创建一个输出流
163 ServletOutputStream outputStream = response.getOutputStream();
164 //写入数据
165 wb.write(outputStream);
166
167 // 关闭
168 outputStream.close();
169 wb.close();
170
171 } catch (Exception e) {
172 e.printStackTrace();
173 } finally {
174 if (out != null) {
175 try {
176 out.close();
177 } catch (IOException e) {}
178 }
179 if (wb != null) {
180 wb.dispose();//删除保存的临时文件。
181 }
182 }
183
184 }
后端调用生成Excel表格
![]()
1 package com.netmarch.ajgl.common.utils.exportExcel;
2
3 import org.apache.poi.ss.usermodel.*;
4 import org.apache.poi.ss.util.CellRangeAddress;
5 import org.apache.poi.ss.util.RegionUtil;
6 import org.apache.poi.xssf.streaming.SXSSFCell;
7 import org.apache.poi.xssf.streaming.SXSSFRow;
8 import org.apache.poi.xssf.streaming.SXSSFSheet;
9 import org.apache.poi.xssf.streaming.SXSSFWorkbook;
10 import org.apache.poi.xssf.usermodel.XSSFCell;
11
12 import java.util.Iterator;
13 import java.util.LinkedHashMap;
14 import java.util.List;
15 import java.util.Map;
16
17 /**
18 * @Author: songyaru
19 * @Date: 2020/5/14 8:58
20 * @Version 1.0
21 */
22 public class ExcelUtils {
23
24 /**
25 * 生成表格（用于生成复杂表头）
26 *
27 * @param sheetName sheet名称
28 * @param wb 表对象
29 * @param cellListMap 表头数据 {key=cellRowNum-1}
30 * @param cellRowNum 表头总占用行数
31 * @param exportData 行数据
32 * @return
33 * @throws Exception
34 */
35 @SuppressWarnings({"rawtypes", "unchecked"})
36 public static SXSSFWorkbook createCSVUtil(String sheetName,String titleName, SXSSFWorkbook wb, Map<String, List<CellModel>> cellListMap,
37 Integer cellRowNum,Integer columnSize, List<LinkedHashMap> exportData,String tjType) throws Exception {
38 //设置表格名称
39 SXSSFSheet sheet = (SXSSFSheet) wb.createSheet(sheetName);
40 sheet.trackAllColumnsForAutoSizing();
41 sheet.autoSizeColumn(1, true);
42
43 //标题字体
44 Font titleFont = wb.createFont();
45 titleFont.setFontName("宋体");
46 titleFont.setFontHeightInPoints((short) 18);
47 // headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示
48 titleFont.setBold(true);//粗体显示
49 titleFont.setColor(IndexedColors.BLACK.getIndex());
50
51 //表头
52 Font headerFont = wb.createFont();
53 headerFont.setFontName("宋体");
54 headerFont.setFontHeightInPoints((short) 14);
55 // headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示
56 headerFont.setBold(true);//粗体显示
57 headerFont.setColor(IndexedColors.BLACK.getIndex());
58
59 //正文字体
60 Font contextFont = wb.createFont();
61 contextFont.setFontName("宋体");
62 contextFont.setFontHeightInPoints((short) 12);
63
64 contextFont.setColor(IndexedColors.BLACK.getIndex());
65
66 //标题样式，左右上下居中
67 CellStyle titleStyle = wb.createCellStyle();
68 titleStyle.setFont(titleFont);
69 titleStyle.setAlignment(HorizontalAlignment.CENTER);// 左右居中
70 titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中
71 titleStyle.setLocked(true);
72 titleStyle.setBorderBottom(BorderStyle.THIN); //下边框
73
74 //表头样式，左右上下居中
75 CellStyle headerStyle = wb.createCellStyle();
76 headerStyle.setFont(headerFont);
77 headerStyle.setAlignment(HorizontalAlignment.CENTER);// 左右居中
78 headerStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中
79 headerStyle.setLocked(true);
80 headerStyle.setWrapText(false);// 自动换行
81 headerStyle.setBorderBottom(BorderStyle.THIN); //下边框
82 headerStyle.setBorderLeft(BorderStyle.THIN);//左边框
83 headerStyle.setBorderTop(BorderStyle.THIN);//上边框
84 headerStyle.setBorderRight(BorderStyle.THIN);//右边框
85
86 //单元格样式，左右上下居中 边框
87 CellStyle commonStyle = wb.createCellStyle();
88 commonStyle.setFont(contextFont);
89 commonStyle.setAlignment(HorizontalAlignment.CENTER);// 左右居中
90 commonStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中
91 commonStyle.setWrapText(false);// 自动换行
92 commonStyle.setBorderBottom(BorderStyle.THIN); //下边框
93 commonStyle.setBorderLeft(BorderStyle.THIN);//左边框
94 commonStyle.setBorderTop(BorderStyle.THIN);//上边框
95 commonStyle.setBorderRight(BorderStyle.THIN);//右边框
96
97 //单元格样式，竖向 边框
98 CellStyle verticalStyle = wb.createCellStyle();
99 verticalStyle.setFont(contextFont);
100 verticalStyle.setAlignment(HorizontalAlignment.CENTER);// 左右居中
101 verticalStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中
102 verticalStyle.setRotation((short) 255);//竖向
103 verticalStyle.setLocked(true);
104 verticalStyle.setWrapText(false);// 自动换行
105 verticalStyle.setBorderBottom(BorderStyle.THIN); //下边框
106 verticalStyle.setBorderLeft(BorderStyle.THIN);//左边框
107 verticalStyle.setBorderTop(BorderStyle.THIN);//上边框
108 verticalStyle.setBorderRight(BorderStyle.THIN);//右边框
109
110
111 // 产生表格标题行
112 Row titleRow = sheet.createRow(0);
113 Cell cellTiltle = titleRow.createCell(0);
114 sheet.setDefaultColumnWidth(15);
115 CellRangeAddress titelCellRange=new CellRangeAddress(0, 0, 0, (columnSize - 1));
116 sheet.addMergedRegion(titelCellRange);
117 RegionUtil.setBorderBottom(BorderStyle.THIN, titelCellRange, sheet);//设置合并单元格的边框
118 /*设置行高*/
119 titleRow.setHeight((short)800);
120
121 cellTiltle.setCellStyle(titleStyle);
122 cellTiltle.setCellValue(titleName);
123
124
125 for (int t = 0; t < cellRowNum; t++) {
126 SXSSFRow row = (SXSSFRow) sheet.createRow(t+1);
127 List<CellModel> cellNameList = cellListMap.get(String.valueOf(t));
128
129 for (CellModel cellModel : cellNameList) {
130 if (cellModel.getStartRow() == cellModel.getEndRow() && cellModel.getStartColumn() == cellModel.getEndColumn()) {
131 //【开始行和结束行在同一行】 和 【开始列和结束列在同一列】同时出现的话，那就没有合并单元格的意义了。
132 } else {
133 CellRangeAddress cellRangeAddress=new CellRangeAddress(cellModel.getStartRow(),
134 cellModel.getEndRow(), cellModel.getStartColumn(), cellModel.getEndColumn());
135 sheet.addMergedRegion(cellRangeAddress);
136 }
137 }
138
139 for (int i = 0; i < cellNameList.size(); i++) {
140 CellModel cellModel = cellNameList.get(i);
141 // 遍历插入表头
142 SXSSFCell cell = (SXSSFCell) row.createCell(cellModel.getStartColumn());
143 cell.setCellValue(cellModel.getCellName());
144 cell.setCellStyle(headerStyle);
145 }
146 }
147 for (LinkedHashMap hashMap : exportData) {
148 SXSSFRow rowValue = (SXSSFRow) sheet.createRow(cellRowNum+1);
149 Iterator<Map.Entry> iteratorRow = hashMap.entrySet().iterator();
150 while (iteratorRow.hasNext()) {
151 Map.Entry entryRow = iteratorRow.next();
152 Integer key = Integer.valueOf(entryRow.getKey().toString());
153 String value = "";
154 if (entryRow.getValue() != null) {
155 value = entryRow.getValue().toString();
156 } else {
157 value = "";
158 }
159 SXSSFCell cellValue = (SXSSFCell) rowValue.createCell(key - 1);
160 cellValue.setCellValue(value);
161 cellValue.setCellStyle(commonStyle);
162 }
163 cellRowNum++;
164 }
165
166
167 /*设置行宽*/
168 int columnWidth[] = {8000, 3000, 3000, 3000, 3000, 3000, 3000, 3000, 3000, 3000};
169 for (int i = 0; i < columnSize; i++) {
170 sheet.setColumnWidth(i, columnWidth[i]);
171 }
172 //合并单元框边框有问题，需要在全部单元格合并之后调整边框,否则会覆盖的
173 CellRangeAddress cellRangeAddress = new CellRangeAddress(1, 2, 9, 9);
174 RegionUtil.setBorderRight(BorderStyle.THIN, cellRangeAddress, sheet);//设置合并单元格的右边框
175
176
177
178 /*
179 // 设置为根据内容自动调整列宽 必须在单元格设值以后进行
180 for (int k = 0; k < exportData .size(); k++) {
181 sheet.autoSizeColumn(k);
182 }
183 // 处理中文不能自动调整列宽的问题
184 setSizeColumn(sheet, exportData.size());*/
185
186
187 return wb;
188 }
189
190 //poi设置自适应列宽
191 private static void setSizeColumn(SXSSFSheet sheet, int size) {
192 for (int columnNum = 0; columnNum < size; columnNum++) {
193 int columnWidth = sheet.getColumnWidth(columnNum) / 256;
194 for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {
195 SXSSFRow currentRow;
196 //当前行未被使用过
197 if (sheet.getRow(rowNum) == null) {
198 currentRow = sheet.createRow(rowNum);
199 } else {
200 currentRow = sheet.getRow(rowNum);
201 }
202
203 if (currentRow.getCell(columnNum) != null) {
204 SXSSFCell currentCell = currentRow.getCell(columnNum);
205 if (currentCell.getCellType() == XSSFCell.CELL_TYPE_STRING) {
206 int length = currentCell.getStringCellValue().getBytes().length;
207 if (columnWidth < length) {
208 columnWidth = length;
209 }
210 }
211 }
212 }
213 sheet.setColumnWidth(columnNum, columnWidth * 256);
214 }
215 }
216
217 }
