poi操作Excel整理

zhougl996

已于 2023-06-02 10:38:39 修改

阅读量1.4k

点赞数

分类专栏： Java基础文章标签： java

于 2022-04-12 11:01:40 首次发布

本文链接：https://blog.csdn.net/Aoutlaw/article/details/124118599

版权

Java基础专栏收录该内容

52 篇文章 2 订阅

订阅专栏

介绍

HSSFWorkbook:是操作Excel2003以前（包括2003）的版本，扩展名是.xls；

XSSFWorkbook 是 Apache POI 项目中针对 Excel 2007 及以上版本（.xlsx）的工作簿对象

SXSSFWorkbook 是 Apache POI 项目中一个针对大数据量 Excel 文件（.xlsx）的工作簿对象实现。

优缺点

HSSFWorkbook 此种方式的局限就是导出的行数至多为65535行，超出后就会报错(不建议使用)

SXSSFWorkbook 和 XSSFWorkbook 都是 Apache POI 项目中针对 Excel 2007 及以上版本（.xlsx）的工作簿对象实现。

他们之间的主要区别在于内存占用和性能方面：

1. 内存占用：SXSSFWorkbook 经常用于处理大量的数据，它通过写入到临时文件，并将临时文件放到 a row buffer 中，最终将 row buffer 写入到硬盘文件中的方式，使得你可以处理大量的数据而不会消耗太多的内存。但是 XSSFWorkbook 不遵循这种方式，所以内存使用会更高。

2. 性能：SXSSFWorkbook 经常用于处理大量的数据时，比 XSSFWorkbook 表现得更优秀，操作大数据量时，可以显著地提高性能；但是相对来说 SXSSFWorkbook 的操作速度会比XSSFWorkbook 慢一些。

因此，如果你需要处理大量数据(建议6w以上)的情况，使用 SXSSFWorkbook 来控制内存使用；如果数据量比较小，或者操作快速高效性能更重要，那么 XSSFWorkbook 可以是更好的选择。

读取Excel

// 创建文件输入流
FileInputStream inputStream = new FileInputStream(new File("example.xlsx"));

// 创建工作簿对象
XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
// 或 
// Workbook workbook = new XSSFWorkbook();
// XSSFWorkbook workbook = new XSSFWorkbook();
// XSSFWorkbook(File file);XSSFWorkbook(String path)等等方式都可以创建工作簿

// 获取第一个工作表对象
XSSFSheet sheet = workbook.getSheetAt(0);

// 循环遍历行数据
for (Row row : sheet) {
    // 循环遍历单元格数据
    for (Cell cell : row) {
        // 处理单元格数据
        System.out.print(cell.toString() + " ");
    }
    System.out.println();
}

// 关闭工作簿对象
workbook.close();

// 创建工作簿对象
SXSSFWorkbook workbook = new SXSSFWorkbook();

// 创建工作表对象
SXSSFSheet sheet = workbook.createSheet("sheet1");

// 写入数据
for (int i = 0; i < 100000; i++) {
    // 创建行对象
    SXSSFRow row = sheet.createRow(i);
    for (int j = 0; j < 10; j++) {
        // 创建单元格对象
        SXSSFCell cell = row.createCell(j);
        // 向单元格中写入数据
        cell.setCellValue("Hello World!");
    }
}

// 将数据保存到文件中
FileOutputStream fileOut = new FileOutputStream("example.xlsx");
workbook.write(fileOut);
fileOut.close();

// 关闭 SXSSFWorkbook 对象
workbook.close();

操作Excel

自适应宽度

// 自适应宽度
    int numberOfSheets = wb.getNumberOfSheets();
    for (int i = 0; i < numberOfSheets; i++) {
        Sheet sheet1 = wb.getSheetAt(i);
        Row row = sheet1.getRow(0);
        // 手动追踪所有列(可以去掉)
        ((SXSSFSheet) sheet1).trackAllColumnsForAutoSizing();
        // 自适应调整所有列的宽度;如果知道具体列数,也可以不用row,改用列数代替循环次数
        for (int j = 0; j < row.getLastCellNum(); j++) {
            sheet.autoSizeColumn(j, true); // 自适应设置列宽（包括中文）
            // 加上额外像素宽度避免中文字符显示不全
            int columnWidth = sheet.getColumnWidth(j);
            sheet.setColumnWidth(j, Math.max(columnWidth + 256, 10 * 256)); // 最小宽度10个字符宽度
        }
    }

cell填充值

填充值：setCellValue

填充公式：setCellFormula

obj为Object类型
// string
cell.setCellValue(String.valueOf(obj));

// number和string
if (obj instanceof Number) {
    cell.setCellValue(((Number) obj).doubleValue());
} else {
    cell.setCellValue(String.valueOf(obj));
}
// 或
if (isNumeric(value.toString())) {
    BigDecimal val = new BigDecimal(value.toString());
    cell.setCellValue(val.doubleValue());
} else {
    cell.setCellValue(value.toString());
}
// 判断一个字符串是不是整数、浮点数、科学计数（正则表达式）
public static boolean isNumeric(String str) {
    if ("".equals(str)) {
        return false;
    }
    String regx = "[+-]*\\d+\\.?\\d*[Ee]*[+-]*\\d+";
    Pattern pattern = Pattern.compile(regx);
    boolean isNumber = pattern.matcher(str).matches();
    if (isNumber) {
        return isNumber;
    }
    regx = "^[-\\+]?[.\\d]*$";
    pattern = Pattern.compile(regx);
    return pattern.matcher(str).matches();
}

/
// String、Double、Float、Short、Integer、Long、Boolean、LocalDate 和 LocalDateTime 类型
public void setCellValueByType(Cell cell, Object value) {
    if (value == null) {
        cell.setBlank();
        return;
    }
    Class<?> valueType = value.getClass();
    if (valueType == String.class) {
        cell.setCellValue((String) value);
    } else if (valueType == Double.class || valueType == Float.class ||
               valueType == Short.class || valueType == Integer.class ||
               valueType == Long.class) {
        cell.setCellValue(((Number) value).doubleValue());
    } else if (valueType == Boolean.class) {
        cell.setCellValue((Boolean) value);
    } else if (valueType == LocalDate.class || valueType == LocalDateTime.class) {
        CellStyle dateStyle = cell.getSheet().getWorkbook().createCellStyle();
        dateStyle.setDataFormat(cell.getSheet().getWorkbook().createDataFormat().getFormat("yyyy-mm-dd h:mm:ss"));
        cell.setCellStyle(dateStyle);
        if (valueType == LocalDate.class) {
            cell.setCellValue(Date.from(((LocalDate) value).atStartOfDay(ZoneId.systemDefault()).toInstant()));
        } else {
            cell.setCellValue(Date.from(((LocalDateTime) value).atZone(ZoneId.systemDefault()).toInstant()));
        }
    } else {
        cell.setCellValue(value.toString());
    }
}

复制Excel

/**
* 复制文件
* @param srcPathStr 来源文件地址
* @param desPathStr 输出文件地址
*/
private static void copyFile(String srcPathStr, String desPathStr) {
    try {
        FileInputStream fis = new FileInputStream(srcPathStr);
        FileOutputStream fos = new FileOutputStream(desPathStr);
        byte datas[] = new byte[1024 * 8];
        int len = 0;
        while ((len = fis.read(datas)) != -1) {
            fos.write(datas, 0, len);
        }
        fis.close();
        fos.close();
    } catch (Exception e) {
        e.printStackTrace();
    }
}

设置样式

private static Map<String, CellStyle> createCellStyle(Workbook wb) {
        CellStyle style = wb.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setBorderRight(BorderStyle.THIN);
        style.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setBorderLeft(BorderStyle.THIN);
        style.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setBorderTop(BorderStyle.THIN);
        style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setBorderBottom(BorderStyle.THIN);
        style.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        Font dataFont = wb.createFont();
        dataFont.setFontName("Arial");
        dataFont.setFontHeightInPoints((short) 10);
        style.setFont(dataFont);

        CellStyle styleHead = wb.createCellStyle();
        style.cloneStyleFrom(style);
        styleHead.setAlignment(HorizontalAlignment.CENTER);
        styleHead.setVerticalAlignment(VerticalAlignment.CENTER);
        styleHead.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
        styleHead.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        Font headerFont = wb.createFont();
        headerFont.setFontName("Arial");
        headerFont.setFontHeightInPoints((short) 10);
        headerFont.setBold(true);
        headerFont.setColor(IndexedColors.WHITE.getIndex());
        styleHead.setFont(headerFont);
        Map<String, CellStyle> map = new HashMap<>();
        map.put("data", style);
        map.put("header", styleHead);
        return map;
    }

设置合并单元格样式

CellRangeAddress region = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);

// 或

CellRangeAddress region0 = CellRangeAddress.valueOf("A1:B1");
sheet.addMergedRegion(region);

    // 设置合并单元的样式
    private static void setBorderStyle(Sheet sheet, CellRangeAddress region, Workbook wb, Cell cell) {
        // 单元格样式
        CellStyle style = wb.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        Font dataFont = wb.createFont();
        dataFont.setFontName("Arial");
        dataFont.setFontHeightInPoints((short) 10);
        style.setFont(dataFont);
        cell.setCellStyle(style);
        // 合并单元格边框样式
        RegionUtil.setBorderLeft(BorderStyle.THIN, region, sheet);
        RegionUtil.setBorderTop(BorderStyle.THIN, region, sheet);
        RegionUtil.setBorderRight(BorderStyle.THIN, region, sheet);
        RegionUtil.setBorderBottom(BorderStyle.THIN, region, sheet);
    }

获得单元格的值

    /**
     * 获取单元格值
     *
     * @param row    获取的行
     * @param column 获取单元格列号
     * @return 单元格值
     */
    public Object getCellValue(Row row, int column) {
        if (row == null) {
            return row;
        }
        Object val = "";
        try {
            Cell cell = row.getCell(column);
            if (StringUtils.isNotNull(cell)) {
                if (cell.getCellTypeEnum() == CellType.NUMERIC || cell.getCellTypeEnum() == CellType.FORMULA) {
                    val = cell.getNumericCellValue();
                    if (HSSFDateUtil.isCellDateFormatted(cell)) {
                        val = DateUtil.getJavaDate((Double) val); // POI Excel 日期格式转换
                    } else {
                        if ((Double) val % 1 > 0) {
                            val = new BigDecimal(val.toString());
                        } else {
                            val = new DecimalFormat("0").format(val);
                        }
                    }
                } else if (cell.getCellTypeEnum() == CellType.STRING) {
                    val = cell.getStringCellValue();
                } else if (cell.getCellTypeEnum() == CellType.BOOLEAN) {
                    val = cell.getBooleanCellValue();
                } else if (cell.getCellTypeEnum() == CellType.ERROR) {
                    val = cell.getErrorCellValue();
                }

            }
        } catch (Exception e) {
            return val;
        }
        return val;
    }

excel转png

// maven引入
//  <dependency>
//      <groupId> e-iceblue </groupId>
//      <artifactId>spire.office.free</artifactId>
//      <version>3.9.0</version>
//  </dependency>
    // 使用 com.spire工具类
    public List<String> ExcelToPng(String fileName) {
        String path = getAbsoluteFile(fileName);
        //加载Excel工作表
        com.spire.xls.Workbook wb1 = new com.spire.xls.Workbook();
        List<String> pngs = new ArrayList<>();
        wb1.loadFromFile(path);
        //获取工作表
        for (int i = 0; i < wb1.getWorksheets().size(); i++) {
            Worksheet sheet = wb1.getWorksheets().get(i);
            sheet.saveToImage(getAbsoluteFile(fileName) + i + ".png");
            pngs.add(fileName + i + ".png");
        }
        return pngs;
    }

导出Excel

写入磁盘

    /**
     * 写到磁盘
     *
     * @param file 返回值类型
     * @date 2021年7月7日
     * @author zhougl
     */
    public String exportExcel(String filename) {
        FileOutputStream out = null;
        this.wb.setForceFormulaRecalculation(true);// 刷新公式
        try {
            out = new FileOutputStream(getAbsoluteFile(filename));
            wb.write(out);
            out.close();
            log.info("写成成功");
        } catch (IOException e) {
            e.printStackTrace();
            log.error("文件出现错误");
        } finally {
            if (wb != null) {
                try {
                    wb.close();
                } catch (IOException e1) {
                    e1.printStackTrace();
                }
            }
            if (out != null) {
                try {
                    out.close();
                } catch (IOException e1) {
                    e1.printStackTrace();
                }
            }
        }
        return filename;
    }

zhougl996

关注

0
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
poi操作Excel整理

1.获取cell的值(只考虑了String的值,其他格式请自行匹配)FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();evaluator 处理excel公式private static String getCellValue(FormulaEvaluator evaluator, Cell cell) { if (cell == null) { return
复制链接

扫一扫

专栏目录