POI导出excel表---HSSFWorkBook工具类

小咪咪 2023-10-18 20:59 121阅读 0赞

**导出excel表有三种方式，分别是HSSFworkbook,XSSFworkbook,SXSSFworkbook,这里我只写了HssfWorkBook导出excel表，她支持2003版本的格式为.xsl的excel,下面就开始导出excel表**

1、在pom.xml里面添加依赖  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNTcwODc5_size_16_color_FFFFFF_t_70]

2、HSSFWorkBook导出excel表的代码编写ExcelUtil（摘录其它博主的代码）\`\`\`  
public class ExcelUtil \{

/**
     * 导出excel
     * @param title  导出表的标题
     * @param rowsName 导出表的列名
     * @param dataList  需要导出的数据
     * @param fileName  生成excel文件的文件名
     * @param response
     */
    public void exportExcel(String title, String[] rowsName, List<Object[]> dataList, String fileName, HttpServletResponse response) throws Exception{
        OutputStream output = response.getOutputStream();
        response.reset();
        response.setHeader("Content-disposition",
                "attachment; filename="+fileName);
        response.setContentType("application/msexcel");
        this.export(title,rowsName,dataList,fileName,output);
        this.close(output);
    
    }
    
    
    
    /*
     * 导出数据
     */
    private void export(String title,String[] rowName,List<Object[]> dataList,String fileName,OutputStream out) throws Exception {
        try {
            HSSFWorkbook workbook = new HSSFWorkbook(); // 创建工作簿对象
            HSSFSheet sheet = workbook.createSheet(title); // 创建工作表
            HSSFRow rowm = sheet.createRow(0);  // 产生表格标题行
            HSSFCell cellTiltle = rowm.createCell(0);   //创建表格标题列
            // sheet样式定义;    getColumnTopStyle();    getStyle()均为自定义方法 --在下面,可扩展
            HSSFCellStyle columnTopStyle = this.getColumnTopStyle(workbook);// 获取列头样式对象
            HSSFCellStyle style = this.getStyle(workbook); // 获取单元格样式对象
            //合并表格标题行，合并列数为列名的长度,第一个0为起始行号，第二个1为终止行号，第三个0为起始列好，第四个参数为终止列号
            sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, (rowName.length - 1)));
            cellTiltle.setCellStyle(columnTopStyle);    //设置标题行样式
            cellTiltle.setCellValue(title);     //设置标题行值
            int columnNum = rowName.length;     // 定义所需列数
            HSSFRow rowRowName = sheet.createRow(2); // 在索引2的位置创建行(最顶端的行开始的第二行)
            // 将列头设置到sheet的单元格中
            for (int n = 0; n < columnNum; n++) {
                HSSFCell cellRowName = rowRowName.createCell(n); // 创建列头对应个数的单元格
                cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING); // 设置列头单元格的数据类型
                HSSFRichTextString text = new HSSFRichTextString(rowName[n]);
                cellRowName.setCellValue(text); // 设置列头单元格的值
                cellRowName.setCellStyle(columnTopStyle); // 设置列头单元格样式
            }
    
            // 将查询出的数据设置到sheet对应的单元格中
            for (int i = 0; i < dataList.size(); i++) {
                Object[] obj = dataList.get(i);   // 遍历每个对象
                HSSFRow row = sheet.createRow(i + 3);   // 创建所需的行数
                for (int j = 0; j < obj.length; j++) {
                    HSSFCell cell = null;   // 设置单元格的数据类型
                    if (j == 0) {
                        cell = row.createCell(j, HSSFCell.CELL_TYPE_NUMERIC);
                        cell.setCellValue(i + 1);
                    } else {
                        cell = row.createCell(j, HSSFCell.CELL_TYPE_STRING);
                        if (!"".equals(obj[j]) && obj[j] != null) {
                            cell.setCellValue(obj[j].toString()); // 设置单元格的值
                        }
                    }
                    cell.setCellStyle(style); // 设置单元格样式
                }
            }
    
            // 让列宽随着导出的列长自动适应
            for (int colNum = 0; colNum < columnNum; colNum++) {
                int columnWidth = sheet.getColumnWidth(colNum) / 256;
                for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {
                    HSSFRow currentRow;
                    // 当前行未被使用过
                    if (sheet.getRow(rowNum) == null) {
                        currentRow = sheet.createRow(rowNum);
                    } else {
                        currentRow = sheet.getRow(rowNum);
                    }
                   /* if (currentRow.getCell(colNum) != null) {
                        HSSFCell currentCell = currentRow.getCell(colNum);
                        if (currentCell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                            int length = currentCell.getStringCellValue()
                                    .getBytes().length;
                            if (columnWidth < length) {
                                columnWidth = length;
                            }
                        }
                    }*/
                }
                if (colNum == 0) {
                    sheet.setColumnWidth(colNum, (columnWidth - 2) * 256);
                } else {
                    sheet.setColumnWidth(colNum, (columnWidth + 4) * 256);
                }
            }
            workbook.write(out);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    
    /*
     * 列头单元格样式
     */
    private HSSFCellStyle getColumnTopStyle(HSSFWorkbook workbook) {
    
        // 设置字体
        HSSFFont font = workbook.createFont();
        // 设置字体大小
        font.setFontHeightInPoints((short) 11);
        // 字体加粗
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        // 设置字体名字
        font.setFontName("Courier New");
        // 设置样式;
        HSSFCellStyle style = workbook.createCellStyle();
        // 设置底边框;
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        // 设置底边框颜色;
        style.setBottomBorderColor(HSSFColor.BLACK.index);
        // 设置左边框;
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        // 设置左边框颜色;
        style.setLeftBorderColor(HSSFColor.BLACK.index);
        // 设置右边框;
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        // 设置右边框颜色;
        style.setRightBorderColor(HSSFColor.BLACK.index);
        // 设置顶边框;
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        // 设置顶边框颜色;
        style.setTopBorderColor(HSSFColor.BLACK.index);
        // 在样式用应用设置的字体;
        style.setFont(font);
        // 设置自动换行;
        style.setWrapText(false);
        // 设置水平对齐的样式为居中对齐;
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        // 设置垂直对齐的样式为居中对齐;
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
    
        return style;
    
    }
    
    /*
     * 列数据信息单元格样式
     */
    private HSSFCellStyle getStyle(HSSFWorkbook workbook) {
        // 设置字体
        HSSFFont font = workbook.createFont();
        // 设置字体大小
        // font.setFontHeightInPoints((short)10);
        // 字体加粗
        // font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        // 设置字体名字
        font.setFontName("Courier New");
        // 设置样式;
        HSSFCellStyle style = workbook.createCellStyle();
        // 设置底边框;
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        // 设置底边框颜色;
        style.setBottomBorderColor(HSSFColor.BLACK.index);
        // 设置左边框;
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        // 设置左边框颜色;
        style.setLeftBorderColor(HSSFColor.BLACK.index);
        // 设置右边框;
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        // 设置右边框颜色;
        style.setRightBorderColor(HSSFColor.BLACK.index);
        // 设置顶边框;
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        // 设置顶边框颜色;
        style.setTopBorderColor(HSSFColor.BLACK.index);
        // 在样式用应用设置的字体;
        style.setFont(font);
        // 设置自动换行;
        style.setWrapText(false);
        // 设置水平对齐的样式为居中对齐;
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        // 设置垂直对齐的样式为居中对齐;
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        return style;
    }
    
    /**
     * 关闭输出流
     * @param os
     */
    private void close(OutputStream os) {
        if (os != null) {
            try {
                os.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

3、编写测试类
    public class Test{
         public void static main(String[ ]args){
                HttpServletResponse response=new HttpServletResponse ();
                UserDto  dto =new UserDto();
                exportExcelData1(response,dto, String info ) ;
         }
    }
        public void static exportExcelData1(HttpServletResponse response,UserDto dto, String info ) {
            List<UserDto> userDtos;
            // 定义表的标题
            String title = "历史评分汇总-员工列表";
            //定义表的列名
            String[] rowsName = new String[]{"序号", "用户姓名", "发薪号", "所属部门", "所属岗位", "季结状态", "季节季度", "A", "B", "C", "D", "总分"};
            //定义表的内容
            List<Object[]> dataList = new ArrayList<>();
            Object[] objs = null;
            //json字符串转对象
            JSONObject jsonObject=JSONObject.fromObject(info);
            Info info1 = (Info)JSONObject.toBean(jsonObject, Info.class);
            dto.setUsername(info1.getUsername());
            dto.setStationcode(info1.getStationcode());
            dto.setYear(info1.getYear());
            dto.setMonth(info1.getMonth());
            dto.setState(info1.getState());
            userDtos = dtoService.selectUserDtoLike(dto);
            // 获取人员相关信息 如岗位 部门 分数
            getStationName(userDtos);
            //获取评分汇总/历史评分会总数据
            getHistoryUserDtosInfo(userDtos, rowsName, dataList);
            // 创建ExportExcel对象
            ExcelUtil excelUtil = new ExcelUtil();
    
            try {
                String fileName = new String("历史评分汇总文档.xls".getBytes("UTF-8"), "iso-8859-1");    //生成word文件的文件名
                excelUtil.exportExcel(title, rowsName, dataList, fileName, response);
    
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    
    
        private void getHistoryUserDtosInfo(List<UserDto> userDtos, String[] rowsName, List<Object[]> dataList) {
            Object[] objs;
            for (int i = 0; i < userDtos.size(); i++) {
                objs = new Object[rowsName.length];
                objs[0] = i;
                objs[1] = userDtos.get(i).getUsername();
                objs[2] = userDtos.get(i).getMoneycard();
                objs[3] = userDtos.get(i).getDepartmentname();
                objs[4] = userDtos.get(i).getStationname();
                objs[5] = userDtos.get(i).getStatename();
                objs[6] = userDtos.get(i).getYear() + "-"+userDtos.get(i).getMonth();
                objs[7] = userDtos.get(i).getAScore();
                objs[8] = userDtos.get(i).getBScore();
                objs[9] = userDtos.get(i).getCScore();
                objs[10] = userDtos.get(i).getDScore();
                objs[11] = userDtos.get(i).getTotalScore();
                dataList.add(objs);
            }
        }

4、文档生成  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNTcwODc5_size_16_color_FFFFFF_t_70 1]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNTcwODc5_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20190705144319524.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNTcwODc5,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNTcwODc5_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20190705145716487.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNTcwODc5,size_16,color_FFFFFF,t_70