poi生成excel表格工具类

不想看废话的可以直接冲向代码 ->

目录

工具类：

测试：

最终效果：

---

最近的需求需要导出excel，更是涉及到各种格式，赋值等操作，在写工具类时踩了不少坑。

譬如：1. 前台接收.xlsx文件，后台工具类使用的HSSFWorkbook，自己测试没问题正常导出打开了，结果提测试，测试说excel打不开，折腾了半天才知道，前台接收.xlsx，后台工具类就必须用XSSFWorkbook，如果用HSSFWorkbook就会导致office打不开文件（WPS能打开），后台如果必须用HSSFWorkbook，那么前台接收时要用.xls

        2. 使用poi赋值时，使用sheet.createRow()会覆盖之前的单元格格式，最终是使用sheet.createRow()和sheet.getRow()配合使用赋值。

在这个工具类中包含的功能有：

        1. 绘制斜线表头

        2. 批量赋值（适用于一次性的导入赋值，没有需要单独赋值的单元格）

        3. 行批量赋值（顾名思义，就是一行一行的赋值）

        4. 列批量赋值（竖着一列一列的赋值）

        5. 创建单元格样式

        6. 合并单元格

        7. 设置空白单元格格式

        8. 单元格求和

使用的参数可能看起来比较多比较繁琐，但是已经尽量将使用的参数写明白是什么了，当然这不是最优最好的工具类，但是是目前博主的项目需求最适合的，有需要的小伙伴可以自取，不废话了，上代码

工具类：

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.xssf.usermodel.*;
import org.openxmlformats.schemas.drawingml.x2006.main.CTPositiveSize2D;

import javax.servlet.http.HttpServletResponse;
import java.awt.Color;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.Date;

/**
 * @author WEI_C0820
 * @create 2021-09-10 10:08
 */
public class ExcelUtil {

    public final int PERCENT_WIDTH = 50;
    public final int PERCENT_HEIGHT = 20;
    public final float PXTOPT = 0.75f;
    public String title;

    public String getTitle() {
        return title;
    }

    public void setTitle(String title) {
        this.title = title;
    }

    public void export(HttpServletResponse response, XSSFWorkbook workbook) {
        try {
            SimpleDateFormat sf = new SimpleDateFormat("yyyyMMddHHmmss");
            ExcelUtil excelUtil = new ExcelUtil();
            String fileName = URLEncoder.encode(this.getTitle() + sf.format(new Date()), "UTF-8");
            //设置Http响应头告诉浏览器下载这个附件
            response.setHeader("Content-Disposition", "attachment;Filename=" + fileName + ".xlsx");
            OutputStream outputStream = response.getOutputStream();
            workbook.write(outputStream);
            outputStream.close();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }


    /**
     * 绘制表头
     *
     * @param sheet 页签
     * @param i     行
     * @param j     列
     */
    public void drawLine(XSSFWorkbook wb, XSSFSheet sheet, int i, int j) {

        CreationHelper helper = wb.getCreationHelper();
        XSSFDrawing drawing = sheet.createDrawingPatriarch();
        ClientAnchor anchor = helper.createClientAnchor();
        // 设置斜线的开始位置
        anchor.setCol1(0);
        anchor.setRow1(i);
        // 设置斜线的结束位置
        anchor.setCol2(j + 1);
        anchor.setRow2(i + 1);
        XSSFSimpleShape shape = drawing.createSimpleShape((XSSFClientAnchor) anchor);
        // 设置形状类型为线型
        shape.setShapeType(ShapeTypes.LINE);
        // 设置线宽
        shape.setLineWidth(0.5);
        // 设置线的风格
        shape.setLineStyle(0);
        // 设置线的颜色
        shape.setLineStyleColor(0, 0, 0);

    }

    /**
     * 批量赋值，用于直接导出不会再进行其他操作的数据
     *
     * @param setRow  行
     * @param setCell 列
     * @param txt     赋值文本
     */
    public void setValue(int setRow, int setCell, XSSFCellStyle style, XSSFSheet sheet, String[] txt) {
        if (txt.length > 0) {
            XSSFRow row = sheet.createRow(setRow);
            for (int i = 0; i < txt.length; i++) {
                XSSFCell cell = row.createCell(setCell + i);
                cell.setCellStyle(style);
                cell.setCellValue(txt[i]);
            }

        }
    }

    /**
     * 为行批量赋值加格式
     *
     * @param setRow  行
     * @param setCell 列
     * @param txt     赋值文本
     * @param style   单元格格式，如果只赋值可以设置为null
     */
    public void setValueForRow(int setRow, int setCell, XSSFCellStyle style, XSSFSheet sheet, String[] txt) {
        if (txt.length > 0) {
            for (int i = 0; i < txt.length; i++) {
                XSSFCell cell = null;
                if (null != sheet.getRow(setRow) && null != sheet.getRow(setRow).getCell(i + setCell)) {
                    cell = sheet.getRow(setRow).getCell(i + setCell);
                    if (null != style) {
                        cell.setCellStyle(style);
                    }
                    cell.setCellValue(txt[i]);
                } else {
                    XSSFRow row = sheet.createRow(setRow);
                    cell = row.createCell(i + setCell);
                    if (null != style) {
                        cell.setCellStyle(style);
                    }
                    cell.setCellValue(txt[i]);
                }
            }

        }
    }

    /**
     * 为列批量赋值加格式
     *
     * @param setCell 列
     * @param txt     装着赋值文本的数组
     */
    public void setValueForCell(int setRow, int setCell, XSSFCellStyle style, XSSFSheet sheet, String[] txt) {

        if (txt.length > 0) {
            for (int i = 0; i < txt.length; i++) {
                XSSFCell cell = null;
                if (null != sheet.getRow(i + setRow) && null != sheet.getRow(i + setRow).getCell(setCell)) {
                    cell = sheet.getRow(i + setRow).getCell(setCell);
                    if (null != style) {
                        cell.setCellStyle(style);
                    }
                    cell.setCellValue(txt[i]);
                } else {
                    XSSFRow row = sheet.createRow(setRow + i);
                    cell = row.createCell(setCell);
                    if (null != style) {
                        cell.setCellStyle(style);
                    }
                    cell.setCellValue(txt[i]);
                }

            }

        }
    }

    /**
     * @param workbook
     * @param fontsize 字体大小
     * @param isBold   字体是否加粗
     * @param isCenter 是否水平居中
     * @param isLeft   是否左对齐
     * @param isRight  是否右对齐
     * @param isBorder 是否加边框线
     * @param isColor  是否填充背景色
     * @return 单元格样式
     */
    public XSSFCellStyle createCellStyle(XSSFWorkbook workbook, short fontsize, boolean isBold, boolean isCenter,
                                         boolean isLeft, boolean isRight, boolean isBorder, boolean isColor) {
        // TODO Auto-generated method stub
        XSSFCellStyle style = workbook.createCellStyle();

        style.setVerticalAlignment(VerticalAlignment.CENTER); // 居中
        //是否水平居中
        if (isCenter) {
            style.setAlignment(HorizontalAlignment.CENTER);//水平居中
        }
        if (isLeft) {
            style.setAlignment(HorizontalAlignment.LEFT);//靠左
        }
        if (isRight) {
            style.setAlignment(HorizontalAlignment.RIGHT);//靠右
        }
        if (isBorder) {
            style.setBorderBottom(BorderStyle.THIN);//下边框
            style.setBorderLeft(BorderStyle.THIN);//左边框
            style.setBorderRight(BorderStyle.THIN);//右边框
            style.setBorderTop(BorderStyle.THIN);  //上边框
        }

        if (isColor) {
            //背景填充色
            style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.index);
            style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        }


        //创建字体
        XSSFFont font = workbook.createFont();
        //是否加粗字体
        if (isBold) {
            font.setBold(true);
        }
        font.setFontHeightInPoints(fontsize);
        //加载字体
        style.setFont(font);
        return style;
    }

    /**
     * 合并单元格格式
     *
     * @param border
     * @param region
     * @param sheet
     */
    private void setRegionBorder(BorderStyle border, CellRangeAddress region, HSSFSheet sheet) {
        RegionUtil.setBorderBottom(border, region, sheet);
        RegionUtil.setBorderLeft(border, region, sheet);
        RegionUtil.setBorderRight(border, region, sheet);
        RegionUtil.setBorderTop(border, region, sheet);
    }

    /**
     * 合并单元格
     *
     * @param startrow 起始行
     * @param overrow  截止行
     * @param startcol 起始列
     * @param overcol  截止列
     * @param sheet
     */
    public void mergeCell(Integer startrow, Integer overrow, Integer startcol, Integer overcol, XSSFSheet sheet) {
        CellRangeAddress cra = new CellRangeAddress(startrow, overrow, startcol, overcol);
        sheet.addMergedRegion(cra);
//        if (flag){
//            this.setRegionBorder(BorderStyle.THIN, cra, sheet);
//        }

    }


    /**
     * 设置空白单元格格式
     *
     * @param startrow
     * @param overrow
     * @param startcol
     * @param overcol
     * @param sheet
     */
    public void setEmptyCellStyle(Integer startrow, Integer overrow, Integer startcol, Integer overcol, XSSFCellStyle style, XSSFSheet sheet) {
        for (int i = startrow; i <= overrow; i++) {
            XSSFRow row = sheet.createRow(i);
            for (int j = startcol; j <= overcol; j++) {
                XSSFCell cell = null;
                if (null != sheet.getRow(i) && null != sheet.getRow(i).getCell(j)) {
                    cell = sheet.getRow(i).getCell(j);
                    cell.setCellStyle(style);
                } else {
                    cell = row.createCell(j);
                    cell.setCellStyle(style);
                }


            }
        }
    }

    /**
     单元格求和(列)
     * @param startrow 起始行
     * @param overrow 结束行
     * @param startcol 起始列
     * @param overcol 结束列
     * @param sheet 工作表
     */
    public void sumValue(Integer startrow, Integer overrow, Integer startcol, Integer overcol, XSSFSheet sheet) {
        if (overrow >= startrow) {
            for (int i = startcol; i < overcol; i++) {
                String colString = CellReference.convertNumToColString(i);
                String sumstring = "SUM(" + colString + startrow + ":" + colString + overrow + ")";//求和公式
                sheet.getRow(overrow + 1).getCell(i).setCellFormula(sumstring);

            }
        }
    }




}

测试：

public void export(HttpServletRequest request, HttpServletResponse response) {
        //1.创建工作簿
        XSSFWorkbook workbook = new XSSFWorkbook();
        ExcelUtil excelUtil = new ExcelUtil();
        //2.创建表格模板
        this.ctreateExcle(workbook,excelUtil);
        excelUtil.export(response, workbook);
    }


    /**
     创建Excle模板
     */
    private void ctreateExcle(XSSFWorkbook workbook,ExcelUtil excelUtil) {

        //创建工作表
        XSSFSheet sheet = workbook.createSheet("测试合并单元格");
        //设置默认列宽
        sheet.setDefaultColumnWidth(15);
        sheet.setDefaultRowHeightInPoints(35);
        //开始创建工作表并赋值
        excelUtil.setTitle("测试工具类");
        // 设置单元格样式
        //大标题
        //参数依次为：是否加粗、是否居中、是否靠左、是否靠右、是否加边框线、是否加底色
        XSSFCellStyle headlineStyle = excelUtil.createCellStyle(workbook, (short) 14, true, false, true, false, false, true);
        //小标题
        XSSFCellStyle titleStyle = excelUtil.createCellStyle(workbook, (short) 12, true, false, false, true, true, false);
        //正文
        XSSFCellStyle valueStyle = excelUtil.createCellStyle(workbook, (short) 11, false, true, false, false, true, false);

        //先将使用的单元格范围设置为自己喜欢的格式
        //设置空白单元格格式
        excelUtil.setEmptyCellStyle(0, 0, 0, 8, headlineStyle, sheet);
        //赋值、注意合并单元格的赋值在第一个单元格，不然值会被覆盖
        excelUtil.setValueForRow(0, 0, null, sheet, new String[]{"测试合并单元格"});
        //合并单元格
        excelUtil.mergeCell(0, 0, 0, 8, sheet);

        //行赋值
        //表头
        excelUtil.drawLine(workbook, sheet, 1, 0);
        //给使用区域统一设置格式，后面有需要改变格式的单元格，在赋值时设置，如下面测试单独赋值的例子，不然会覆盖格式
        excelUtil.setEmptyCellStyle(1, 4, 0, 8, titleStyle, sheet);
        String[] hang = new String[]{"测试行赋值2", "测试行赋值3", "测试行赋值4", "测试行赋值5", "测试行赋值6", "测试行赋值7", "测试行赋值8","测试行赋值9"};
        excelUtil.setValueForRow(1, 1, null, sheet, hang);

        //列赋值
        String[] lie = new String[]{"测试列赋值1", "测试列赋值2", "测试列赋值3"};
        excelUtil.setValueForCell(2, 0, null, sheet, lie);

        //测试单独赋值
        String[] txt = new String[]{"测试4/6赋值"};
        excelUtil.setValueForRow(3, 5, valueStyle, sheet, txt);

    }

最终效果：

目前看来还比较繁琐，后续可能会继续优化，大家有什么好的建议欢迎留言