poi 通过浏览器导出excel文件打不开

冷不防 2023-07-07 08:07 85阅读 0赞

本机excel版本大于2007

maven引入jar包配置

<dependency>
    	<groupId>org.apache.poi</groupId>
    	<artifactId>poi-ooxml</artifactId>
    	<version>3.9</version>
    	<exclusions>
    		<exclusion>
    			<groupId>dom4j</groupId>
    			<artifactId>dom4j</artifactId>
    		</exclusion>
    	</exclusions>
    </dependency>

代码controller层

MemberSchoolController.java

package com.ztesoft.zsmart.zcm.monitor.controller;
    
    import javax.servlet.http.HttpServletResponse;
    
    import org.springframework.http.MediaType;
    import org.springframework.web.bind.annotation.*;
    
    import java.util.*;
    
    
    @RestController
    @RequestMapping(value="/memberschool")
    public class MemberSchoolController  {
    
    
        /**
         * @description:导出数据
         * @param:[schoolId, response]
         * @return:void
         * @date:2020/02/21
         * @author:tangyj
         * @remark:
         * */
        @RequestMapping(value = "/download/data/{schoolId}", method = RequestMethod.GET,consumes = "application/octet-stream",produces = MediaType.APPLICATION_OCTET_STREAM_VALUE)
        public void download(
                @PathVariable(value="schoolId",required = true)Long schoolId,
                HttpServletResponse response) throws Exception{
            //临时生成测试数据
            String fileName = "test7.xlsx";
            String headTitle = "test_title";
            int colunmNum = 10;
    
            List<String> headTitleList = new ArrayList<String>();
            for(int i=0;i<10; i++){
                headTitleList.add("first "+(i+1)+"xxx");
            }
            List<List<String>>  dataList = new ArrayList<List<String>>();
            for(int i=0;i<5; i++){
                List<String> datas = new ArrayList<>();
                for(int j=0;j<10; j++){
                    datas.add("yyy "+(i+1)+"yyyy"+(j+1)+"yyy");
                }
                dataList.add(datas);
            }
            //1-创建一个HSSFWorkbook
            ExcelObject excel = new ExcelObject("test_data");
            //2-写入头标题
            excel.createHeadTile(colunmNum,headTitle);//头标默认写在第一行
            //3-写入行标题
            excel.createRowTitle(headTitleList,1);
            //4-写入具体数据
            excel.createDataByRow(2,dataList);//因为没有行标题，所以从第二行开始
            //5-生成excel文件
            excel.buildExcelFile(fileName);
            //6-浏览器下载excel
            excel.buildExcelDocument(fileName,response);
        }
    }

逻辑层

ExcelObject.java

package com.ztesoft.zsmart.zcm.monitor.controller;
    
    import org.apache.poi.hssf.usermodel.*;
    import org.apache.poi.ss.usermodel.HorizontalAlignment;
    import org.apache.poi.ss.util.CellRangeAddress;
    import org.apache.poi.xssf.usermodel.XSSFCell;
    import org.apache.poi.xssf.usermodel.XSSFCellStyle;
    import org.apache.poi.xssf.usermodel.XSSFFont;
    import org.apache.poi.xssf.usermodel.XSSFRow;
    import org.apache.poi.xssf.usermodel.XSSFSheet;
    import org.apache.poi.xssf.usermodel.XSSFWorkbook;
    import org.springframework.web.bind.annotation.GetMapping;
    
    import javax.servlet.http.HttpServletResponse;
    import java.io.FileOutputStream;
    import java.io.OutputStream;
    import java.net.URLEncoder;
    import java.util.List;
    
    public class ExcelObject {
    
    //    private HSSFWorkbook workbook;
        //    private HSSFSheet sheet;
    
        private XSSFWorkbook workbook;
        private XSSFSheet sheet;
    
    
        public ExcelObject() {
            this.workbook = new XSSFWorkbook();
            this. sheet = this.workbook.createSheet("sheet1");
        }
    
        public ExcelObject(String sheetName) {
            this.workbook = new XSSFWorkbook();
            this. sheet = this.workbook.createSheet(sheetName);
            sheet.setDefaultRowHeight((short) (2 * 256)); //设置默认行高，表示2个字符的高度
            sheet.setDefaultColumnWidth(17);    //设置默认列宽，实际上回多出2个字符，不知道为什么
        }
    
        /**
         * @description:设置头标题格式
         * @param:[workbook, sheet]
         * @return:org.apache.poi.hssf.usermodel.HSSFCellStyle
         * @date:2020/02/21
         * @author:tangyj
         * @remark:
         * */
        private  XSSFCellStyle getHeadTitleStyle(){
            //设置为居中加粗
            XSSFCellStyle style = this.workbook.createCellStyle();
            XSSFFont font = this.workbook.createFont();
            font.setFontHeightInPoints((short)24);
            font.setBold(true);
            style.setAlignment( HorizontalAlignment.CENTER);
            style.setFont(font);
            style.setWrapText(true);//设置自动换行
            return style;
        }
        /**
         * @description:设置行标题格式
         * @param:[workbook, sheet]
         * @return:org.apache.poi.hssf.usermodel.HSSFCellStyle
         * @date:2020/02/21
         * @author:tangyj
         * @remark:
         * */
        private  XSSFCellStyle getRowTitleStyle(){
            //设置为居中加粗
            XSSFCellStyle style = this.workbook.createCellStyle();
            XSSFFont font = this.workbook.createFont();
            font.setFontHeightInPoints((short)12);
            font.setBold(true);
            style.setAlignment( HorizontalAlignment.CENTER);
            style.setFont(font);
            style.setWrapText(true);//设置自动换行
            return style;
        }
        /**
         * @description:设置普通单元格格式
         * @param:[workbook, sheet]
         * @return:org.apache.poi.hssf.usermodel.HSSFCellStyle
         * @date:2020/02/21
         * @author:tangyj
         * @remark:
         * */
        private  XSSFCellStyle getNormalCellTitleStyle(){
            //设置为居中加粗
            XSSFCellStyle style = this.workbook.createCellStyle();
            XSSFFont font = this.workbook.createFont();
            font.setFontHeightInPoints((short)12);
            style.setAlignment( HorizontalAlignment.CENTER);
            style.setFont(font);
            style.setWrapText(true);//设置自动换行
            return style;
        }
    
        /**
         * @description:1-写入头标题
         * @param:[workbook, sheet, column, headTitle]
         * @return:void
         * @date:2020/02/21
         * @author:tangyj
         * @remark:
         * */
        public  void createHeadTile(int column, String headTitle){
            //创建行(默认第一行)
            XSSFRow row = sheet.createRow(0);
            //合并单元格
            int lastColIndex = column > 0 ?  (column-1):0;
            sheet.addMergedRegion(new CellRangeAddress(0,0,0,lastColIndex));
            //单元格居中加粗格式
            XSSFCellStyle style = getHeadTitleStyle();
            //写入标题
            XSSFCell cell  = row.createCell(0);//获取当前行的第一列
            cell.setCellValue(headTitle);//写入数据
            cell.setCellStyle(style);//设置单元格格式
        }
    
        /**
         * @description:2-写入行标题
         * @param:[workbook, sheet, rowTitleList, firstRowIndex]
         * @return:void
         * @date:2020/02/21
         * @author:tangyj
         * @remark:
         * */
        public  void createRowTitle(List<String> rowTitleList,int firstRowIndex){
            //创建行
            XSSFRow row = sheet.createRow(firstRowIndex);
            //设置为居中加粗
            XSSFCellStyle style = getRowTitleStyle();
            XSSFCell cell;
            for(int i=0; i< rowTitleList.size(); i++){
                cell = row.createCell(i);//获取当前列
                cell.setCellValue(rowTitleList.get(i));//写入数据
                cell.setCellStyle(style);//设置单元格格式
            }
        }
        /**
         * @description:3-写入具体的单元格数据
         * @param:[firstRowIndex, lineDataList]
         * @return:void
         * @date:2020/02/21
         * @author:tangyj
         * @remark:
         * */
        public  void createDataByRow(int firstRowIndex,List<List<String>> lineDataList){
            //i代表行，j代表列
            XSSFCellStyle style = getNormalCellTitleStyle();
            for(int i=0; i< lineDataList.size(); i++){
                List<String> lineDatas = lineDataList.get(i);
                XSSFRow row = sheet.createRow(firstRowIndex + i);//行
                for(int j=0; j<lineDatas.size(); j ++){
                    XSSFCell cell = row.createCell(j);//列
                    cell.setCellValue(lineDatas.get(j));//写入数据
                    cell.setCellStyle(style);//设置单元格格式
                }
            }
        }
    
        /**
         * @description:4-生成excel文件
         * @param:[filename, workbook]
         * @return:void
         * @date:2020/02/21
         * @author:tangyj
         * @remark:
         * */
        public void buildExcelFile(String filename) throws Exception{
            FileOutputStream fos = new FileOutputStream(filename);
            workbook.write(fos);
            fos.flush();
            fos.close();
        }
    
        /**
         * @description:5-浏览器下载excel
         * @param:[filename, workbook, response]
         * @return:void
         * @date:2020/02/21
         * @author:tangyj
         * @remark:
         * */
        public void buildExcelDocument(String filename,HttpServletResponse response) throws Exception{
            response.reset();
            response.setCharacterEncoding("utf-8");
            response.setContentType("application/force-download");
    //        response.setContentType("application/vnd.ms-excel");
    //        response.setContentType("application/vnd.ms-excel;charset=utf-8");
    //        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setContentType("application/application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=UTF-8");
            response.setHeader("Content-Disposition", "attachment;filename="+ URLEncoder.encode(filename, "utf-8"));
            response.setHeader("Pragma", "no-cache");
            response.setHeader("Cache-Control", "no-cache");
            response.setDateHeader("Expires", 0);
            OutputStream outputStream = response.getOutputStream();
    //        response.setHeader("Content-Length", "10000");
    
            workbook.write(outputStream);
            outputStream.flush();
            outputStream.close();
        }
    
    
    }

请求

接口请求通过swagger

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzMzI2NDQ5_size_16_color_FFFFFF_t_70][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzMzI2NDQ5_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200221174727801.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzMzI2NDQ5,size_16,color_FFFFFF,t_70