Java使用POI导出Excel .xlsx提示文件格式或文件扩展名有误

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表格

poi使用SXSSFWorkbook生成表格（用于生成复杂表头）

  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 }

View Code

效果：

posted @ 2020-05-15 13:25 梦想在风中飘扬阅读(6140) 评论(0) 编辑收藏举报

刷新页面返回顶部

Java使用POI导出Excel .xlsx提示文件格式或文件扩展名有误

公告