esaypoi导出一个sheet页导出多个表格

最新推荐文章于 2024-05-16 10:23:10 发布

上进青年张积极

最新推荐文章于 2024-05-16 10:23:10 发布

阅读量548

点赞数 1

文章标签： java Powered by 金山文档

本文链接：https://blog.csdn.net/z1515602927/article/details/128931642

版权

格式设置

package pegasus.integrated.dynamicWork.controller;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;

public class ExcelCS {
    /**
     * * 导出Excel
     *
     * @param sheetName   sheet名称
     * @param titleColumn 列标题
     * @param title       标题
     * @param values      表内容
     * @param wb          HSSFWorkbook对象
     * @return
     */
    public static HSSFWorkbook getHSSFWorkbook(String sheetName, String[] titleColumn, String title, String[][] values, HSSFWorkbook wb, int rownum) {

        // 第一步，创建一个HSSFWorkbook，对应一个Excel文件
        if (wb == null) {
            wb = new HSSFWorkbook();
        }
        HSSFSheet sheet;
        // 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
        if (wb.getSheet(sheetName) != null) {
            sheet = wb.getSheet(sheetName);
        } else {
            sheet = wb.createSheet(sheetName);
        }
        //创建表名的样式
        HSSFCellStyle tableNamestyle = wb.createCellStyle();
        tableNamestyle.setAlignment(HorizontalAlignment.CENTER); // 创建一个居中格式
        sheet.autoSizeColumn((short) 0);
        tableNamestyle.setBorderBottom(BorderStyle.THIN); //下边框
        tableNamestyle.setBorderLeft(BorderStyle.THIN);//左边框
        tableNamestyle.setBorderTop(BorderStyle.THIN);//上边框
        tableNamestyle.setBorderRight(BorderStyle.THIN);//右边框
        //设置字体
        HSSFFont tableNamefont = wb.createFont();
        tableNamefont.setFontHeightInPoints((short) 14); //字体高度
        tableNamefont.setColor(HSSFFont.COLOR_NORMAL); //字体颜色
        tableNamefont.setFontName("宋体"); //字体
        tableNamestyle.setFont(tableNamefont);


        //创建第一行
        HSSFRow row = sheet.createRow(rownum);
        //设置合并单元格样式
        setCellBorder(1, titleColumn.length - 1, row, tableNamestyle);
        //单元格合并
        CellRangeAddress cra = new CellRangeAddress(rownum, rownum, 0, titleColumn.length - 1);
        sheet.addMergedRegion(cra);
        //声明列对象
        HSSFCell cell = null;
        cell = row.createCell(0);
        cell.setCellValue(title);
        /**设置单元格格式为文本格式*/
        HSSFDataFormat format = wb.createDataFormat();
        tableNamestyle.setDataFormat(format.getFormat("@"));
        cell.setCellStyle(tableNamestyle);//设置单元格格式为"文本"
        cell.setCellType(CellType.STRING);


        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
        row = sheet.createRow(rownum + 1);

        // 第四步，创建单元格，并设置值表头 设置表头居中
        HSSFCellStyle titlestyle = wb.createCellStyle();
        titlestyle.setAlignment(HorizontalAlignment.CENTER); // 创建一个居中格式
        sheet.autoSizeColumn((short) 0);
        //设置字体
        HSSFFont font = wb.createFont();
        font.setFontHeightInPoints((short) 9); //字体高度
        font.setColor(HSSFFont.COLOR_NORMAL); //字体颜色
        font.setFontName("宋体"); //字体
        //font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); //宽度
        titlestyle.setFont(font);
        titlestyle.setBorderBottom(BorderStyle.THIN); //下边框
        titlestyle.setBorderLeft(BorderStyle.THIN);//左边框
        titlestyle.setBorderTop(BorderStyle.THIN);//上边框
        titlestyle.setBorderRight(BorderStyle.THIN);//右边框


        //创建单元格样式
        HSSFCellStyle cellstyle = wb.createCellStyle();
        //设置字体
        HSSFFont tableCountfont = wb.createFont();
        tableCountfont.setFontHeightInPoints((short) 10); //字体高度
        tableCountfont.setColor(HSSFFont.COLOR_NORMAL); //字体颜色
        tableCountfont.setFontName("宋体"); //字体
        //tableCountfont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL); //宽度
        cellstyle.setFont(tableCountfont);

        cellstyle.setAlignment(HorizontalAlignment.CENTER); // 创建一个居中格式
        cellstyle.setBorderBottom(BorderStyle.THIN); //下边框
        cellstyle.setBorderLeft(BorderStyle.THIN);//左边框
        cellstyle.setBorderTop(BorderStyle.THIN);//上边框
        cellstyle.setBorderRight(BorderStyle.THIN);//右边框

        /**设置单元格格式为文本格式*/
        HSSFDataFormat format1 = wb.createDataFormat();
        cellstyle.setDataFormat(format1.getFormat("@"));


        //创建标题
        for (int i = 0; i < titleColumn.length; i++) {
            cell = row.createCell(i);
            cell.setCellValue(titleColumn[i]);
            cell.setCellStyle(titlestyle);
        }

        //创建内容
        for (int i = rownum; i < values.length + rownum; i++) {
            HSSFCell cellcell = null;
            row = sheet.createRow(i + 2);
            for (int j = 0; j < values[i - rownum].length; j++) {
                //将内容按顺序赋给对应的列对象
                cellcell = row.createCell(j);
                cellcell.setCellValue(values[i - rownum][j]);
                cellcell.setCellStyle(cellstyle);
                cellcell.setCellType(CellType.STRING);
            }

        }
        // 设置为根据内容自动调整列宽
/*        for (int k = 0; k < values[0].length; k++) {
            sheet.autoSizeColumn(k);
            HSSFCellStyle cellstyle1 = wb.createCellStyle();
            HSSFDataFormat format2 = wb.createDataFormat();
            cellstyle1.setDataFormat(format2.getFormat("@"));
            sheet.setDefaultColumnStyle(k, cellstyle1);
        }
        setSizeColumn(sheet, values[0].length);*/
        return wb;
    }

    /**
     * 自适应宽度(中文支持)
     *
     * @param sheet
     * @param size
     */
    public static void setSizeColumn(HSSFSheet sheet, int size) {

        for (int columnNum = 0; columnNum < size; columnNum++) {
            int columnWidth = sheet.getColumnWidth(columnNum) / 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(columnNum) != null) {
                    HSSFCell currentCell = currentRow.getCell(columnNum);
                    if (currentCell.getCellType() == CellType.STRING) {
                        int length = currentCell.getStringCellValue().getBytes().length;
                        if (columnWidth < length) {
                            columnWidth = length;
                        }
                    }
                }
            }
            sheet.setColumnWidth(columnNum, columnWidth * 256);
        }
    }

    /**
     * 合并单元格加边框  水平
     * 参数说明：start和并的第二列，end为合并的最后一列，row就为当前行，style样式(里面有设置边框)
     * 例如从0-10列合并： ExcelUtil.setCellBorder(2,10,row,style);   这样可以设置
     */
    public static void setCellBorder(int start, int end, HSSFRow row, HSSFCellStyle style) {
        for (int i = start; i <= end; i++) {
            HSSFCell cell = row.createCell(i);
            cell.setCellValue("");
            cell.setCellStyle(style);
        }
    }


}

业务层导出方法

/**
     * 导出模板
     *
     * @return ActionResult
     */
    @GetMapping(value = "/exportData")
    public ActionResult<Object> exportExcelDetail(String workId) {
        List<DynamicWorkEntity> list = dynamicWorkSubtaskService.getModelList(workId);
        List<DynamicWorkSubtaskEntity> subList = list.get(0).getSubList();

        // 创建HSSFWorkbook
        HSSFWorkbook workbook = new HSSFWorkbook();
        // 表头标题
        String title = "工作任务";
        // 列标题
        String[] Mytitle = {"任务名称", "负责部门", "类别", "预计办结日期", "办理状态","工作内容"};
        // sheet名
        String sheetName = "动态工作模板";
        String content[][] = new String[list.size()][];
        int rownum = 0;
        if (!list.isEmpty()) {
            for (int i = 0; i < list.size(); i++) {
                content[i] = new String[Mytitle.length];
                content[i][0] = list.get(i).getName();
                content[i][1] = list.get(i).getResponsibleDepartmentName();
                content[i][2] = list.get(i).getCategory();
                String date = list.get(i).getExpectedCompletionDate();
                if (StringUtil.isNotEmpty(date)) {
                    content[i][3] = date.split(" ")[0];
                }
                content[i][4] = 1 == list.get(i).getStatus() ? "进行中" : "已完成";
                content[i][5] = list.get(i).getContent();
            }
            workbook = ExcelCS.getHSSFWorkbook(sheetName, Mytitle, title, content, workbook, rownum);
        }
        // 表头标题
        String title1 = "子任务";
        // 列标题
        String[] Mytitle1 = {"任务名称", "负责部门", "负责人", "预计办结日期", "办理状态",""};
        // sheet名
        String content1[][] = new String[subList.size()][];
        //加2代表加了title和Mytitle两行，后边加的1代表表格间空的行数
        int rownum1 = list.size() + 2;
        if (!subList.isEmpty()) {
            for (int i = 0; i < subList.size(); i++) {
                content1[i] = new String[Mytitle.length];
                content1[i][0] = subList.get(i).getName();
                content1[i][1] = subList.get(i).getDepartmentName();
                content1[i][2] = subList.get(i).getPeopleName();
                content1[i][3] = subList.get(i).getEstimatedCompletionTime();

                if (TaskStatus.ONE.getName().equals(subList.get(i).getProcessingStatus())) {
                    content1[i][4] = "进行中";
                } else if (TaskStatus.TWO.getName().equals(subList.get(i).getProcessingStatus())) {
                    content1[i][4] = "已完成";
                } else {
                    content1[i][4] = "已退回";
                }
                content1[i][5] = "";
            }
            workbook = ExcelCS.getHSSFWorkbook(sheetName, Mytitle1, title1, content1, workbook, rownum1);
        }
        DownloadVO vo = DownloadVO.builder().build();
        String name = "动态工作模板.xlsx";

        String fileName = configValueUtil.getTemporaryFilePath() + name;

        try {
            @Cleanup FileOutputStream output = new FileOutputStream(XSSEscape.escapePath(fileName));
            workbook.write(output);
            //上传文件
            UploadUtil.uploadFile(configValueUtil.getFileType(), fileName, FileTypeEnum.TEMPORARY, name);
        } catch (IOException e) {
            e.printStackTrace();
        }

        vo.setName(name);
        UserInfo userInfo = userProvider.get();
        vo.setUrl(UploaderUtil.uploaderFile(userInfo.getId() + "#" + name + "#" + "Temporary"));
        return ActionResult.success(vo);
    }

最终效果