报表excel下载

小鱼儿 2023-03-02 14:52 29阅读 0赞

> 这里是引用

<properties>
    		<poi.version>3.17</poi.version>
            <poi-ooxml.version>3.17</poi-ooxml.version>
        </properties>
     		<dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi</artifactId>
            </dependency>
            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi-ooxml</artifactId>
            </dependency>
    
     try { 
                String excelPath = req.getSession().getServletContext().getRealPath(
                        File.separator + fileName);
                excelfile = new File(excelPath);
                rd.setExtra1(excelPath);
                if (!excelfile.exists()) { 
                    HSSFWorkbook wb = ExcelUtil.getHSSFWorkbooks(selectMap,sheetName, title, content, null);
                    // ExcelUtil.setResponseHeader(response, fileName);
                    os = new FileOutputStream(excelPath);
                    // OutputStream os = response.getOutputStream();
                    wb.write(os);
                    rd.setExtra1(req.getScheme()+"://"+req.getServerName()+":"+req.getServerPort()+"/"+fileName);
                    rd.setSuccess(true);
                    os.flush();
                    os.close();
                }
            } catch (Exception e) { 
                e.printStackTrace();
                rd.setSuccess(false);
                rd.setMsg(e.getMessage());
            }finally { 
                if (os != null) { 
                    try { 
                        os.close();
                    } catch (IOException e) { 
                        e.printStackTrace();
                    }
                }
            }
            response.setContentType("application/octet-stream");
            response
                    .setHeader("Content-Disposition", "attachment;filename="
                            + fileName);
           
    
    
     /** * 导出Excel * @param sheetName sheet名称 * @param title 标题 * @param values 内容 * @param wb HSSFWorkbook对象 * @return */
        public static HSSFWorkbook getHSSFWorkbooks(Map<String , Object> map, String sheetName, String []title, String [][]values, HSSFWorkbook wb){ 
    
            // 第一步，创建一个HSSFWorkbook，对应一个Excel文件
            if(wb == null){ 
                wb = new HSSFWorkbook();
            }
            String startDate = (String)map.get("startDate");
            String endDate = (String)map.get("endDate");
            // 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
            HSSFSheet sheet = wb.createSheet(sheetName);
            CellRangeAddress cellRangeAddress=new CellRangeAddress(0,0,0,2);
            sheet.addMergedRegion(cellRangeAddress);
            // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
            HSSFRow rows = sheet.createRow(0);//第一行
            HSSFRow row = sheet.createRow(1);//第二行
    
    
            // 第四步，创建单元格，并设置值表头 设置表头居中
            HSSFCellStyle style = wb.createCellStyle();
            style.setAlignment(HorizontalAlignment.CENTER); // 创建一个居中格式
    
            //声明列对象
            HSSFCell cell1 = null;
            HSSFCell cell = null;
            cell1 = rows.createCell(0);
            cell1.setCellValue("查询时间范围："+startDate+"至"+endDate);
            cell1.setCellStyle(style);
    
            //创建标题
            for(int i=0;i<title.length;i++){ 
                cell = row.createCell(i);//第二行第零列
                cell.setCellValue(title[i]);
                cell.setCellStyle(style);
            }
    
            //创建内容
            for(int i=0;i<values.length;i++){ 
                row = sheet.createRow(i + 2); //第三行
                for(int j=0;j<values[i].length;j++){ 
                    //将内容按顺序赋给对应的列对象
                    row.createCell(j).setCellValue(values[i][j]);
                }
            }
            setSizeColumn(sheet , title.length);
            return wb;
        }
    
    
     public static void setSizeColumn(Sheet sheet , int size){ 
            for(int colunmNum = 0; colunmNum < size; colunmNum ++){ 
                int columnWidth = sheet.getColumnWidth(colunmNum) / 256;
                for(int rowNum = 0; rowNum < sheet.getPhysicalNumberOfRows(); rowNum ++){ 
                    Row currentRow;
                    if(sheet.getRow(rowNum) == null){ 
                        currentRow = sheet.createRow(rowNum);
                    }else{ 
                        currentRow = sheet.getRow(rowNum);
                    }
                    if(currentRow.getCell(colunmNum) != null){ 
                        Cell currentCell = currentRow.getCell(colunmNum);
                        if(currentCell.getCellType() == HSSFCell.CELL_TYPE_STRING){ 
                            int length = currentCell.getStringCellValue().getBytes().length;
                            if(columnWidth < length){ 
                                columnWidth = length;
                            }
                        }
                    }
                    sheet.setColumnWidth(colunmNum , columnWidth * 256);
                }
            }
    
        }