java 生成 excel 文档

迈不过友情╰ 2022-12-23 09:40 212阅读 0赞

开发过程我们经常需要生成excel 文档报表等，今天我们实战详细分析一下生成并下载的流程：

1、依赖 jar 导入pom文件：

<dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi</artifactId>
                <version>${version.poi}</version>
            </dependency>

2、controller 层只负责传参数 省略，，，，

3、service 业务层 ：

@Override
        public void exportHighExcel(HttpServletRequest request , HttpServletResponse response) {
     
               String fileName = courtName+regionName+companyName+"人员数据统计表";
        
            try {
                ByteArrayOutputStream os = new ByteArrayOutputStream();
                downloadExcelFile( os);
                byte[] content = os.toByteArray();
                InputStream is = new ByteArrayInputStream(content);
    
                String agent = request.getHeader("User-Agent").toLowerCase();
                logger.debug("User-Agent ---> " +agent);
                boolean isMSIE = (agent != null && (agent.indexOf("msie") != -1 || (agent.indexOf("rv") != -1 && agent.indexOf("firefox") == -1)));
    
                if (isMSIE) {
                    fileName = URLEncoder.encode(fileName, "UTF-8");
                } /*else {
    				  //  fileName = new String(fileName.getBytes("UTF-8"), "ISO-8859-1");
    				    fileName = new String(fileName.getBytes("UTF-8"), "UTF-8");
    				}*/
                // 设置response参数，可以打开下载页面
                response.reset();
                response.setContentType("application/vnd.ms-excel;charset=utf-8");
                response.setHeader("Content-Disposition", "attachment;filename="+ new String((fileName+".xls").getBytes("UTF-8"), "ISO-8859-1"));
                response.setContentLength(content.length);
                //response.setCharacterEncoding("utf-8");
                ServletOutputStream outputStream = response.getOutputStream();
                BufferedInputStream bis = new BufferedInputStream(is);
                BufferedOutputStream bos = new BufferedOutputStream(outputStream);
                byte[] buff = new byte[8192];
                int bytesRead;
                while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
                    bos.write(buff, 0, bytesRead);
                }
                bis.close();
                bos.close();
                outputStream.flush();
                outputStream.close();
            }catch (Exception e) {
                e.printStackTrace();
            }
    
        }

4、单独抽出来生成excel文档的方法 downloadExcelFile(so)

/**
    *此方法主要把数据合到excel表格里，并准备下载到页面
    /
    public void downloadExcelFile(  OutputStream out)  throws Exception{
            String sheetName = "数据统计";
            List<Map<String, Object>> statisticsList = new ArrayList<>();
            Map<String, Object> staticMap = new HashMap<>();
            staticMap.put("orgName","南道公司");
            staticMap.put("name","nandao");
            staticMap.put("age","18");
            Map<String, Object> staticMap1 = new HashMap<>();
            staticMap1.put("orgName","四方");
            staticMap1.put("name","dao");
            staticMap1.put("age","28");
            statisticsList.add(staticMap);
            statisticsList.add(staticMap1);
            String[][] dataList = null;//采用二维数组封装数据
            String[] columnName = new String[3];
            columnName = new String[]{ "区域名称", "姓名", "年龄" };
            dataList = new String[statisticsList.size()][columnName.length];
            for (int i = 0; i < statisticsList.size(); i++) {
                int s = 0;
                dataList[i][s] = statisticsList.get(i).get("orgName").toString() == null ? "" : statisticsList.get(i).get("orgName").toString();
                dataList[i][++s] = statisticsList.get(i).get("name").toString() == null ? "" : statisticsList.get(i).get("name").toString();
                dataList[i][++s] = statisticsList.get(i).get("age").toString() == null ? "0" : statisticsList.get(i).get("age").toString();
            }
    
            // 第一步，创建一个webbook，对应一个Excel文件
            HSSFWorkbook wb = new HSSFWorkbook();
            // 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
            HSSFSheet sheet = wb.createSheet(sheetName);
            // 创建第1行 也就是表头
            HSSFRow row = sheet.createRow((int) 0);
            row.setHeightInPoints(20);// 设置表头高度
            // 第四步，创建表头单元格样式 以及表头的字体样式
            HSSFCellStyle style = wb.createCellStyle();
            // style.setWrapText(true);// 设置自动换行
            style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 创建一个居中格式
    //			style.setFillForegroundColor(HSSFColor.YELLOW.index);
            style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            style.setBottomBorderColor(HSSFColor.BLACK.index);
            style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
            style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
            style.setBorderRight(HSSFCellStyle.BORDER_THIN);
            style.setBorderTop(HSSFCellStyle.BORDER_THIN);
            HSSFFont headerFont = (HSSFFont) wb.createFont(); // 创建字体样式
    //			headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 字体加粗
            headerFont.setFontName("黑体"); // 设置字体类型
            headerFont.setFontHeightInPoints((short) 10); // 设置字体大小
            style.setFont(headerFont); // 为标题样式设置字体样式
            // 第四.一步，创建表头的列
            for (int i = 0; i < columnName.length; i++) {
                //======  这里是重点，马上要自定义单元格的样式了  =============
                //拿到palette颜色板
                HSSFPalette palette = wb.getCustomPalette();
                //这个是重点，具体的就是把之前的颜色 HSSFColor.LIME.index
                //替换为  RGB(51,204,204) 宝石蓝这种颜色
                //你可以改为 RGB(0,255,127)
                palette.setColorAtIndex((short)i, (byte) 221, (byte) 235, (byte) 247);
                style.setFillForegroundColor((short)i);
                HSSFCell cell = row.createCell(i);
                cell.setCellValue(columnName[i]);
                cell.setCellStyle(style);
            }
    
            if(statisticsList != null){
                // 第五步，创建单元格，并设置值
                for (int i = 0; i < statisticsList.size(); i++) {
                    row = sheet.createRow((int) i + 1);
                    // 为数据内容设置特点新单元格样式1 自动换行 上下居中
                    HSSFCellStyle zidonghuanhang = wb.createCellStyle();
                    //			zidonghuanhang.setWrapText(true);// 设置自动换行
                    zidonghuanhang.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 创建一个居中格式
                    // 设置边框
                    zidonghuanhang.setBottomBorderColor(HSSFColor.BLACK.index);
                    zidonghuanhang.setBorderBottom(HSSFCellStyle.BORDER_THIN);
                    zidonghuanhang.setBorderLeft(HSSFCellStyle.BORDER_THIN);
                    zidonghuanhang.setBorderRight(HSSFCellStyle.BORDER_THIN);
                    zidonghuanhang.setBorderTop(HSSFCellStyle.BORDER_THIN);
                    // 为数据内容设置特点新单元格样式2 自动换行 上下居中左右也居中
                    HSSFCellStyle zidonghuanhang2 = wb.createCellStyle();
                    //			zidonghuanhang2.setWrapText(true);// 设置自动换行
                    zidonghuanhang2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 创建一个上下居中格式
                    zidonghuanhang2.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中
                    // 设置边框
                    zidonghuanhang2.setBottomBorderColor(HSSFColor.BLACK.index);
                    zidonghuanhang2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
                    zidonghuanhang2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
                    zidonghuanhang2.setBorderRight(HSSFCellStyle.BORDER_THIN);
                    zidonghuanhang2.setBorderTop(HSSFCellStyle.BORDER_THIN);
                    HSSFCell datacell = null;
                    for (int j = 0; j < columnName.length; j++) {
                        datacell = row.createCell(j);
                        try {
                            datacell.setCellValue(Double.parseDouble(dataList[i][j].replace(",","")));
                        } catch (Exception e) {
                            datacell.setCellValue(dataList[i][j]);
                        }
                        datacell.setCellStyle(zidonghuanhang2);
                    }
                }
            }
            // 第六步，将文件存到浏览器设置的下载位置
            try {
                wb.write(out);
                wb.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }

5、启动后访问就可以生成并下载了：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L25hbmRhbzE1OA_size_16_color_FFFFFF_t_70][]

分析到此结束！关于 jar 相关功能可以参考这篇文章：[https://www.cnblogs.com/xinaixia/p/6707868.html][https_www.cnblogs.com_xinaixia_p_6707868.html]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L25hbmRhbzE1OA_size_16_color_FFFFFF_t_70]: /images/20221120/f6a760ed5c4f4eae84aec9fad76c8777.png
[https_www.cnblogs.com_xinaixia_p_6707868.html]: https://www.cnblogs.com/xinaixia/p/6707868.html