POI处理Excel工具类

TOC

POI处理Excel

工具类

/**
* @author jsy
* @date 2018/8/30 15:13
* @description poi导出表格工具类
*/
@Slf4j
public class PoiUtils {


   /**
     * 导出(前端导出)
     */
    public static void export(HttpServletResponse response, HttpServletRequest request, Workbook wb, String fileName) throws Exception {
        String s = DateUtils.formatDate(new Date(), DateUtils.YYYYMMDD_NO_LINE);
        fileName += s;
        response.reset();
        String name = URLEncoder.encode(fileName + ".xls", "UTF-8");
        response.setContentType("application/msexcel");// 定义输出类型
        if (request.getHeader("User-Agent").toLowerCase().contains("firefox")) {
            response.setHeader("Content-Disposition", "attachment; filename*=UTF-8''" + name);
        } else {
            response.setHeader("content-disposition", "attachment; filename=" + name);
        }
        OutputStream out = response.getOutputStream();
        wb.write(out);
        out.close();
    }

    /**
     * 导出(本地导出,主要是测试用)
     */
    public static void exportLocal(Workbook wb, String url, String name) throws Exception {
        url = StringUtils.isBlank(url) ? "c:" : url;
        FileOutputStream fileOut = new FileOutputStream(url + "\\\\" + name + ".xls");
        wb.write(fileOut);
        fileOut.close();
    }

    /**
     * 创建标题样式
     */
    public static CellStyle getTitleStyle(Workbook wb) {
        Font font = wb.createFont();
        font.setFontHeightInPoints((short) 19);
        CellStyle style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setFont(font);
        return style;
    }

    /**
     * 创建居中样式
     */
    public static CellStyle getjzStyle(Workbook wb) {
        CellStyle style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER); // 创建一个居中格式
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// 垂直居中
        return style;
    }

    /**
     * 创建边框样式(上下左右)
     */
    public static CellStyle getBorderStyle(Workbook wb, boolean top, boolean button, boolean left, boolean right, short color, CellStyle style) {
        if (top) {
            style.setBorderTop(CellStyle.BORDER_THIN);
            style.setTopBorderColor(color);
        }
        if (button) {
            style.setBorderBottom(CellStyle.BORDER_THIN);
            style.setBottomBorderColor(color);
        }
        if (left) {
            style.setBorderLeft(CellStyle.BORDER_THIN);
            style.setLeftBorderColor(color);
        }
        if (right) {
            style.setBorderRight(CellStyle.BORDER_THIN);
            style.setRightBorderColor(color);
        }
        return style;
    }

    /**
     * 复制行样式
     */
    public static Row setRowStyle(Workbook wb, Row row, int start, int end, CellStyle style) {
        Cell cell;
        while (start <= end) {
            cell = row.createCell(start++);
            cell.setCellStyle(style);
        }
        return row;
    }

    /**
     * 读取模板
     *
     * @param excelName 模板name
     */
    public static Workbook getWorkbook(String excelName) throws Exception {
        InputStream inp = PoiUtils.class.getResourceAsStream("/templates/" + excelName + ".xls");
        return WorkbookFactory.create(inp);
    }


    /**
     * 多行复制
     *
     * @param wb 表格
     * @param sheet 页
     * @param from 开始行
     * @param to 复制行
     * @param copyValueFlag true则连同cell的内容一起复制
     * @param count 复制的行数
     */
    public static void copyMoreRow(Workbook wb, Sheet sheet, int from, int to, boolean copyValueFlag, int count) {
        Row fromRow, toRow;
        for (int i = 0; i < count; i++) {
            fromRow = sheet.getRow(from + i);
            toRow = sheet.createRow(to + i);

            PoiUtils.copyRow(wb, sheet, fromRow, toRow, copyValueFlag);
        }
    }

    /**
     * 行复制功能
     *
     * @param fromRow 开始行
     * @param toRow 目的行
     * @param copyValueFlag true则连同cell的内容一起复制
     */
    public static void copyRow(Workbook wb, Sheet sheet, Row fromRow, Row toRow, boolean copyValueFlag) {
        toRow.setHeight(fromRow.getHeight());
        for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
            CellRangeAddress cellRangeAddress = sheet.getMergedRegion(i);
            if (cellRangeAddress.getFirstRow() == fromRow.getRowNum()) {
                CellRangeAddress newCellRangeAddress = new CellRangeAddress(toRow.getRowNum(), (toRow.getRowNum() + (cellRangeAddress.getLastRow() - cellRangeAddress.getFirstRow())),
                                                                            cellRangeAddress.getFirstColumn(), cellRangeAddress.getLastColumn());
                sheet.addMergedRegion(newCellRangeAddress);
            }
        }
        for (Iterator cellIt = fromRow.cellIterator(); cellIt.hasNext(); ) {
            Cell tmpCell = (Cell) cellIt.next();
            Cell newCell = toRow.createCell(tmpCell.getColumnIndex());
            copyCell(wb, tmpCell, newCell, copyValueFlag);
        }

    }

    /**
     * 复制单元格
     *
     * @param srcCell 原单元格
     * @param distCell 目的单元格
     * @param copyValueFlag true则连同cell的内容一起复制
     */
    public static void copyCell(Workbook wb, Cell srcCell, Cell distCell, boolean copyValueFlag) {
        CellStyle newstyle = wb.createCellStyle();
        newstyle.cloneStyleFrom(srcCell.getCellStyle());
        // 样式
        distCell.setCellStyle(newstyle);
        // 评论
        if (srcCell.getCellComment() != null) {
            distCell.setCellComment(srcCell.getCellComment());
        }
        // 不同数据类型处理
        int srcCellType = srcCell.getCellType();
        distCell.setCellType(srcCellType);
        if (copyValueFlag) {
            if (srcCellType == Cell.CELL_TYPE_NUMERIC) {
                if (DateUtil.isCellDateFormatted(srcCell)) {
                    distCell.setCellValue(srcCell.getDateCellValue());
                } else {
                    distCell.setCellValue(srcCell.getNumericCellValue());
                }
            } else if (srcCellType == Cell.CELL_TYPE_STRING) {
                distCell.setCellValue(srcCell.getRichStringCellValue());
            } else if (srcCellType == Cell.CELL_TYPE_BLANK) {
                // nothing21
            } else if (srcCellType == Cell.CELL_TYPE_BOOLEAN) {
                distCell.setCellValue(srcCell.getBooleanCellValue());
            } else if (srcCellType == Cell.CELL_TYPE_ERROR) {
                distCell.setCellErrorValue(srcCell.getErrorCellValue());
            } else if (srcCellType == Cell.CELL_TYPE_FORMULA) {
                distCell.setCellFormula(srcCell.getCellFormula());
            } // nothing29

        }
    }


    // 获取值,封装的方法
    private static String getValue(Cell cell) {
        if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {// 若是Boolean类型
            return String.valueOf(cell.getBooleanCellValue());
        } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {// 若是数字类型
            return String.valueOf(cell.getNumericCellValue());
        } else {// 其他格式直接转换即可
            return String.valueOf(cell.getStringCellValue());
        }
    }

    private static String getString(String aa) {
        if (StringUtils.isBlank(aa)) {
            return "";
        } else {
            return aa;
        }
    }

    private static String getIntString(Integer aa) {
        if (aa == null) {
            return "";
        } else {
            return String.valueOf(aa);
        }
    }

    private static String getDoubleString(Double aa) {
        if (aa == null) {
            return "";
        } else {
            return DataUtils.doubleToString(aa);
        }
    }

    /**
     * 多行复制--多个表格
     *
     * @param wb 表格
     * @param fromSheet 被复制的页
     * @param toSheet 目的页
     * @param from 开始行
     * @param to 复制行
     * @param copyValueFlag true则连同cell的内容一起复制
     * @param count 复制的行数
     */
    public static void copyMoreRowToExcel(Workbook wb, Sheet fromSheet, Sheet toSheet, int from, int to, boolean copyValueFlag, int count) {
        Row fromRow, toRow;
        for (int i = 0; i < count; i++) {
            fromRow = fromSheet.getRow(from + i);
            toRow = toSheet.createRow(to + i);
            PoiUtils.copyRowToExcel(wb, fromSheet, toSheet, fromRow, toRow, copyValueFlag);
        }
        fromRow = fromSheet.getRow(1);
        //设置列宽
        for (int i = 0; i < fromRow.getPhysicalNumberOfCells(); i++) {
            toSheet.setColumnWidth(i, fromSheet.getColumnWidth(i));
        }
    }

    /**
     * 行复制功能--两个表格
     *
     * @param wb 复制到的表格
     * @param fromSheet 被复制的页
     * @param toSheet 目的页
     * @param fromRow 原行
     * @param toRow 目的行
     * @param copyValueFlag true则连同cell的内容一起复制
     */
    public static void copyRowToExcel(Workbook wb, Sheet fromSheet, Sheet toSheet, Row fromRow, Row toRow, boolean copyValueFlag) {
        toRow.setHeight(fromRow.getHeight());//设置行高
        for (int i = 0; i < fromSheet.getNumMergedRegions(); i++) {//得到所有区域
            CellRangeAddress cellRangeAddress = fromSheet.getMergedRegion(i);//合并单元格
            if (cellRangeAddress.getFirstRow() == fromRow.getRowNum()) {//若是被合并了的
                CellRangeAddress newCellRangeAddress = new CellRangeAddress(toRow.getRowNum(), (toRow.getRowNum() + (cellRangeAddress.getLastRow() - cellRangeAddress.getFirstRow())),
                                                                            cellRangeAddress.getFirstColumn(), cellRangeAddress.getLastColumn());
                toSheet.addMergedRegion(newCellRangeAddress);
            }
        }
        for (Iterator cellIt = fromRow.cellIterator(); cellIt.hasNext(); ) {
            Cell tmpCell = (Cell) cellIt.next();
            Cell newCell = toRow.createCell(tmpCell.getColumnIndex());
            copyCell(tmpCell, newCell, copyValueFlag);
        }

    }

    /**
     * 复制单元格
     *
     * @param oldCell 原数据
     * @param newCell 目的数据
     * @param ifvalue 是否复制文字
     */
    public static void copyCell(Cell oldCell, Cell newCell, boolean ifvalue) {
        newCell.setCellStyle(oldCell.getCellStyle());
        if (ifvalue) {
            switch (oldCell.getCellType()) {
                case Cell.CELL_TYPE_STRING:
                    newCell.setCellValue(oldCell.getStringCellValue());
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    newCell.setCellValue(oldCell.getNumericCellValue());
                    break;
                case Cell.CELL_TYPE_BLANK:
                    newCell.setCellType(Cell.CELL_TYPE_BLANK);
                    break;
                case Cell.CELL_TYPE_BOOLEAN:
                    newCell.setCellValue(oldCell.getBooleanCellValue());
                    break;
                case Cell.CELL_TYPE_ERROR:
                    newCell.setCellErrorValue(oldCell.getErrorCellValue());
                    break;
                case Cell.CELL_TYPE_FORMULA:
                    newCell.setCellFormula(oldCell.getCellFormula());
                    break;
                default:
                    break;
            }
        }
    }

    /**
     * 合并单元格
     *
     * @param sheet
     * @param firstRow
     * @param lastRow
     * @param firstCol
     * @param lastCol
     */
    public static void setMergedRegion(Sheet sheet, int firstRow, int lastRow, int firstCol, int lastCol) {
        Row row;
        Cell cell;
        CellStyle cellStyle = sheet.getRow(firstRow).getCell(firstCol).getCellStyle();
        for (int i = firstRow; i <= lastRow; i++) {
            row = sheet.getRow(i);
            for (int j = firstCol; j <= lastCol; j++) {
                if (i == firstRow && j == firstCol) {
                    continue;
                }
                cell = row.createCell(j);
                cell.setCellStyle(cellStyle);
            }
        }
        sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol, lastCol));
    }


    /**
     * 删除模板表格(除了 id之外所有的)
     *
     * @param wb
     */
    public static void removeModelSheet(Workbook wb, int id) {
        int numberOfSheets = wb.getNumberOfSheets();
        for (int i = numberOfSheets - 1; i > -1; i--) {
            if (i != id) {
                wb.removeSheetAt(i);
            }
        }
        //设置默认显示第一页
        wb.setActiveSheet(0);
    }
    /**
     * 删除模板表格(除了 name之外所有的)
     *
     * @param wb
     */
    public static void removeModelSheet(Workbook wb, String name) {
        int sheetIndex = wb.getSheetIndex(wb.getSheet(name));
        removeModelSheet(wb,sheetIndex);
    }
    /**
     * 删除模板表格(除了 name之外所有的)
     *
     * @param wb
     */
    public static void removeModelSheet(Workbook wb, Sheet noDelSheet) {
        int sheetIndex = wb.getSheetIndex(noDelSheet);
        removeModelSheet(wb,sheetIndex);
    }

}

测试

同一个表格内,设置图片

    private Workbook startExportWhCg(Entity whCg) throws Exception {
        // 读取模板数据
        Workbook wb = PoiUtils.getWorkbook("demo");
        // 获取页
        Sheet sheet = wb.getSheet("页面1");

        Drawing patriarch = sheet.createDrawingPatriarch();
       PoiUtils.exportWhCg(wb, sheet, patriarch, whCg);
        // 删除其余的模板
        PoiUtils.removeModelSheet(wb, wb.getSheetIndex(sheet));
        return wb;
    }
    public static void exportWhCg(Workbook wb, Sheet sheet, Drawing patriarch, Entity whCg) throws Exception {

        // 获取列数
        Row row = sheet.getRow(start + 1);
        row.getCell(2).setCellValue( whCg.getName());
       ....
        /* 遍历详情数据 */
        int rowIndex = start + 4;
        for (Detail weightingDetail : whCg.getPageInfo().getList()) {
            Row rowDetail = sheet.getRow(rowIndex++);
            int i = 2, j = 0;
            rowDetail.getCell(i++).setCellValue(weightingDetail.getName());
            ....
        }

         //设置图片 base64字符串设置图片
        //位置
         HSSFClientAnchor anchor = new HSSFClientAnchor(400, 5, 0, 254, (short) 2, start + 13, (short) 3, start + 13);
        //设置
         patriarch.createPicture(anchor, wb.addPicture(Base64.decodeBase64(whCg.getStr().substring(17)), HSSFWorkbook.PICTURE_TYPE_JPEG));

    }

不同sheet内(实时设置页面)

    /**
     * 导出总账
     *
     * @param response
     * @param request
     * @param re
     * @return
     * @author jingshiyu
     * @date 2020/3/17 17:08
     */
    public void exportQueryGLVoucherData(HttpServletResponse response, HttpServletRequest request, Date date, Integer isNoAccount) throws Exception {
        //查询数据
        List<AccV> totalList = ...;
        String time = DateUtils.formatDate(date, "yyyy年MM月");
        String s = time + "账";
        // 导出表格
        // 读取模板数据
        Workbook wb = PoiUtils.getWorkbook("demo");
        Sheet modelSheet = wb.getSheet("账2");// 创建页
        Sheet sheet = wb.createSheet(s);// 创建页
        final int  row = 18;
        List<AccV> allList = new ArrayList<>();
        for (AccV accVoucherTotal : totalList) {
            allList.add(accVoucherTotal);
            if (CollectionUtils.isNotEmpty(accVoucherTotal.getList())) {
                allList.addAll(accVoucherTotal.getList());
            }

            accVoucherTotal.setList(null);
        }

        //分页
        int totalPage = (int) Math.ceil(allList.size() * 1.0 / row);
        for (int i = 0; i < totalPage; i++) {
            int start = i * (row + 7);
            //表头复制
            PoiUtils.copyMoreRowToExcel(wb, modelSheet, sheet, 0, start, true, 4);
            //具体内容行
            for (int j = 0; j < row; j++) {
                PoiUtils.copyRowToExcel(wb, modelSheet, sheet, modelSheet.getRow(4), sheet.createRow(start + 4 + j), false);

            }
            PoiUtils.copyMoreRowToExcel(wb, modelSheet, sheet, 5, start + 4 + row, true, 2);
            //合并
            PoiUtils.setMergedRegion(sheet, start, (start + 4 + row), 4, 4);
//            PoiUtils.setMergedRegion(sheet,start,start+6+row,6,6);
            String page = "单\r\n据\r\n" + (i + 1) + "/" + totalPage;
            //设置内容
            int end = Math.min(allList.size(), (i + 1) * row);
            PoiUtils.exportQueryGLVoucherData(wb, sheet, allList.subList(i * row, end), time, start, page, DateUtils.formatDate(date, DateUtils.CN_DATE));
        }
        // 删除其余的模板
        PoiUtils.removeModelSheet(wb, s);
        PoiUtils.export(response, request, wb, s);
    }
    public static void exportQueryGLVoucherData(Workbook wb, Sheet sheet, List<AccVoucherTotal> subList, String time, int start, String page, String date) {

        // 获取列数
        Row row = sheet.getRow(start);
//        row.getCell(0).setCellValue(time + "总账");
        row.getCell(4).setCellValue(page);
        sheet.getRow(start + 1).getCell(0).setCellValue("日期：" + date);
        sheet.getRow(start + 1).getCell(2).setCellValue("填制日期：" + DateUtils.formatDate(new Date(), DateUtils.CN_DATE));
        /* 遍历详情数据 */
            if (CollectionUtils.isNotEmpty(subList)) {
                int rowIndex = start + 4;
//                int c = 0;
                    for (AccVoucherTotal oneRow : subList) {
                        row=sheet.getRow(rowIndex++);
                        int c=0;
                        row.getCell(c++).setCellValue(getString(oneRow.getVn())+getString(oneRow.getName()));
                        row.getCell(c++).setCellValue(getDoubleString(oneRow.getDebitPrice()));
                        row.getCell(c++).setCellValue(getDoubleString(oneRow.getLendPrice()));
                        row.getCell(c).setCellValue(getDoubleString(oneRow.getBalance()));
                    }

            }

    }





posted @   紫月java  阅读(931)  评论(0)  编辑  收藏  举报
编辑推荐：
· Linux系列：如何用heaptrack跟踪.NET程序的非托管内存泄露
· 开发者必知的日志记录最佳实践
· SQL Server 2025 AI相关能力初探
· Linux系列：如何用 C#调用 C方法造成内存泄露
· AI与.NET技术实操系列（二）：开始使用ML.NET
阅读排行：
· 无需6万激活码！GitHub神秘组织3小时极速复刻Manus，手把手教你使用OpenManus搭建本
· C#/.NET/.NET Core优秀项目和框架2025年2月简报
· 什么是nginx的强缓存和协商缓存
· 一文读懂知识蒸馏
· Manus爆火，是硬核还是营销？
点击右上角即可分享
微信分享提示