Java 操作 Excel(2)--POI 用户模式读写Excel
Apache POI 是基于 Office Open XML 标准（OOXML）和 Microsoft 的 OLE 2复合文档格式（OLE2）处理各种文件格式的开源框架。本文主要介绍使用 POI 的用户模式来读写 Excel，POI 的用户模式使用简单但比较消耗内存，适合小数据量。本文中所使用到的软件版本：jdk1.8.0_181、POI 5.0.0。
1、引入依赖
<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>5.0.0</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>5.0.0</version> </dependency>
2、编写工具类
2.1、Excel 2003 工具类
package com.abc.demo.general.excel.user; import com.abc.demo.general.util.DateUtil; import org.apache.commons.lang3.StringUtils; import org.apache.poi.hssf.usermodel.*; import org.apache.poi.ss.usermodel.*; import java.util.List; /** * Excel 2003 工具类 */ public class Excel2003Util { private Excel2003Util() { } /** * 创建单元格样式 * @param workbook * @param bgColor * @param fontColor * @return */ public static HSSFCellStyle createCellStyle(HSSFWorkbook workbook, short bgColor, short fontColor) { HSSFCellStyle cellStyle = workbook.createCellStyle(); cellStyle.setBorderTop(BorderStyle.THIN); cellStyle.setBorderBottom(BorderStyle.THIN); cellStyle.setBorderLeft(BorderStyle.THIN); cellStyle.setBorderRight(BorderStyle.THIN); cellStyle.setFillForegroundColor(bgColor); cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); cellStyle.setAlignment(HorizontalAlignment.CENTER); cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); HSSFFont font = workbook.createFont(); font.setColor(fontColor); cellStyle.setFont(font); return cellStyle; } public static HSSFCellStyle createCellStyle(HSSFWorkbook wb, short bgColor) { return createCellStyle(wb, bgColor, Font.COLOR_NORMAL); } public static HSSFCellStyle createCellStyle(HSSFWorkbook wb) { return createCellStyle(wb, IndexedColors.WHITE.index, Font.COLOR_NORMAL); } /** * 设置单元格的值 * @param sheet 工作表 * @param rowNum 行数 * @param colNum 列数 * @param cs 单元格样式（从外部传进来，减少对象个数及“不同样式的单元格太多”的问题） * @param value 设的值 */ public static void setCellValue(HSSFSheet sheet, int rowNum, int colNum, HSSFCellStyle cs, Object value) { HSSFRow row = sheet.getRow(rowNum); if (row == null) { row = sheet.createRow(rowNum); } HSSFCell cell = row.getCell(colNum); if (cell == null) { cell = row.createCell(colNum); } cell.setCellType(CellType.STRING); if (cs != null) { cell.setCellStyle(cs); } if (value == null) { value = ""; } cell.setCellValue(new HSSFRichTextString(value.toString())); } /** * 设置一行单元格的值 * @param sheet 工作表 * @param rowNum 行数 * @param startColNum 起始列数 * @param cs 单元格样式 * @param values 值的数组 */ public static void setLineValue(HSSFSheet sheet, int rowNum, int startColNum, HSSFCellStyle cs, Object[] values) { for (int i = 0; i < values.length; i++) { setCellValue(sheet, rowNum, startColNum++, cs, values[i]); } } /** * 设置一行单元格的值 * @param sheet 工作表 * @param rowNum 行数 * @param startColNum 起始列数 * @param cs 单元格样式 * @param values 值的集合 */ public static void setLineValue(HSSFSheet sheet, int rowNum, int startColNum, HSSFCellStyle cs, List<Object> values) { for (int i = 0; i < values.size(); i++) { setCellValue(sheet, rowNum, startColNum++, cs, values.get(i)); } } /** * 获取单元格的值 * @param row 行对象 * @param cellNum 列索引 * @return */ public static String getCellValue(HSSFRow row, int cellNum) { HSSFCell cell = row.getCell(cellNum); if (cell == null) { return ""; } String cellValue = ""; if (cell.getCellType() == CellType.STRING) { cellValue = cell.getRichStringCellValue().toString().trim(); } else if (cell.getCellType() == CellType.BOOLEAN) { cellValue = cell.getBooleanCellValue() + ""; } else if (cell.getCellType() == CellType.FORMULA) { cellValue = cell.getCellFormula() + ""; } else if (cell.getCellType() == CellType.BLANK || cell.getCellType() == CellType._NONE) { cellValue = ""; } else if (cell.getCellType() == CellType.ERROR) { cellValue = cell.getErrorCellValue() + ""; } else if (cell.getCellType() == CellType.NUMERIC) { if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) { cellValue = DateUtil.getDateString(org.apache.poi.ss.usermodel.DateUtil.getJavaDate(cell.getNumericCellValue()), "yyyy-MM-dd hh:mm:ss"); } else { double val = cell.getNumericCellValue(); int val2 = (int)val; if (val - val2 == 0) { cellValue = String.valueOf(val2); } else { cellValue = String.valueOf(val); } } } else { throw new RuntimeException("无效的单元格类型:" + cell.getCellType()); } return cellValue.trim(); } /** * 获取单元格的值 * @param sheet 工作表 * @param rowNum 行数 * @param cellNum 列数 * @return */ public static String getCellValue(HSSFSheet sheet, int rowNum, int cellNum) { HSSFRow row = sheet.getRow(rowNum); return getCellValue(row, cellNum); } /** * 判断一行的值是否为空 * @param row 行对象 * @param start 从第几列开始判断 * @param num 判断多少列 * @return */ public static boolean lineIsNull(HSSFRow row, int start, int num) { for (int i = start; i < start + num; i++) { if (StringUtils.isNotBlank(getCellValue(row, i))) { return false; } } return true; } }
2.2、Excel 2007 工具类
package com.abc.demo.general.excel.user; import com.abc.demo.general.util.DateUtil; import org.apache.commons.lang3.StringUtils; import org.apache.poi.ss.usermodel.*; import org.apache.poi.xssf.streaming.SXSSFRow; import org.apache.poi.xssf.streaming.SXSSFSheet; import org.apache.poi.xssf.streaming.SXSSFWorkbook; import org.apache.poi.xssf.usermodel.*; import java.util.Date; import java.util.List; /** * Excel 2007 工具类 */ public class Excel2007Util { private Excel2007Util() {} /** * 创建单元格样式 * @param workbook * @param bgColor * @return */ public static XSSFCellStyle createCellStyle(XSSFWorkbook workbook, short bgColor) { XSSFCellStyle cellStyle = workbook.createCellStyle(); cellStyle.setBorderTop(BorderStyle.THIN); cellStyle.setBorderBottom(BorderStyle.THIN); cellStyle.setBorderLeft(BorderStyle.THIN); cellStyle.setBorderRight(BorderStyle.THIN); cellStyle.setFillForegroundColor(bgColor); cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); cellStyle.setAlignment(HorizontalAlignment.CENTER); cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); return cellStyle; } /** * 创建单元格样式 * @param wb * @return */ public static XSSFCellStyle createCellStyle(XSSFWorkbook wb) { return createCellStyle(wb, IndexedColors.WHITE.index); } /** * 设置单元格的值 * @param sheet 工作表 * @param rowNum 行数 * @param colNum 列数 * @param cs 单元格样式（从外部传进来，减少对象个数及“不同样式的单元格太多”的问题） * @param value 设的值 */ public static void setCellValue(XSSFSheet sheet, int rowNum, int colNum, XSSFCellStyle cs, Object value) { XSSFRow row = sheet.getRow(rowNum); if (row == null) { row = sheet.createRow(rowNum); } XSSFCell cell = row.getCell(colNum); if (cell == null) { cell = row.createCell(colNum); } cell.setCellType(CellType.STRING); if (cs != null) { cell.setCellStyle(cs); } if (value == null) { value = ""; } cell.setCellValue(new XSSFRichTextString(value.toString())); } /** * 设置一行单元格的样式 * @param sheet * @param rowNum excel行 * @param startColNum 起始列数 * @param endColNum 终止列数 * @param cs 样式 */ public static void setLineCellStyle(XSSFSheet sheet, int rowNum, int startColNum, int endColNum, XSSFCellStyle cs) { XSSFRow row = sheet.getRow(rowNum); if (row == null) { row = sheet.createRow(rowNum); } for (int i = startColNum; i <= endColNum; i++) { XSSFCell cell = row.getCell(i); if (cell == null) { cell = row.createCell(i); } cell.setCellStyle(cs); } } /** * 设置一行单元格的值 * @param sheet 工作表 * @param rowNum 行数 * @param startColNum 起始列数 * @param cs 单元格样式 * @param values 值(集合) */ public static void setLineValue(XSSFSheet sheet, int rowNum, int startColNum, XSSFCellStyle cs, List<Object> values) { for (int i = 0; i < values.size(); i++) { setCellValue(sheet, rowNum, startColNum++, cs, values.get(i)); } } /** * 设置一行单元格的值 * @param sheet 工作表 * @param rowNum 行数 * @param startColNum 起始列数 * @param cs 单元格样式 * @param values 值(数组) */ public static void setLineValue(XSSFSheet sheet, int rowNum, int startColNum, XSSFCellStyle cs, Object[] values) { for (int i = 0; i < values.length; i++) { setCellValue(sheet, rowNum, startColNum++, cs, values[i]); } } /** * 获得单元格的值 * @param row 行对象 * @param cellNum 列索引 * @return */ public static String getCellValue(XSSFRow row, int cellNum) { XSSFCell cell = row.getCell(cellNum); if (cell == null) { return ""; } String cellValue = ""; if (cell.getCellType() == CellType.STRING) { cellValue = cell.getRichStringCellValue().toString().trim(); } else if (cell.getCellType() == CellType.BOOLEAN) { cellValue = cell.getBooleanCellValue() + ""; } else if (cell.getCellType() == CellType.FORMULA) { cellValue = cell.getCellFormula() + ""; } else if (cell.getCellType() == CellType.BLANK || cell.getCellType() == CellType._NONE) { cellValue = ""; } else if (cell.getCellType() == CellType.ERROR) { cellValue = cell.getErrorCellString(); } else if (cell.getCellType() == CellType.NUMERIC) { if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) { Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(cell.getNumericCellValue()); cellValue = DateUtil.getDateString(date, "yyyy-MM-dd hh:mm:ss"); } else { double val = cell.getNumericCellValue(); long val2 = (long)val; if (val - val2 == 0) { cellValue = String.valueOf(val2); } else { cellValue = String.valueOf(val); } } } else { throw new RuntimeException("无效的单元格类型:" + cell.getCellType()); } return cellValue.trim(); } /** * 判断一行的值是否为空 * @param row 行对象 * @param start 从第几列开始判断 * @param num 判断多少列 * @return */ public static boolean lineIsNull(XSSFRow row, int start, int num) { for (int i = start; i < start + num; i++) { if (StringUtils.isNotBlank(getCellValue(row, i))) { return false; } } return true; } /****************************以下为POI流式相关API****************************/ /** * 创建单元格样式 * @param wb * @param bgColor * @return */ public static CellStyle createCellStyle(SXSSFWorkbook wb, short bgColor) { CellStyle cellStyle = wb.createCellStyle(); cellStyle.setBorderTop(BorderStyle.THIN); cellStyle.setBorderBottom(BorderStyle.THIN); cellStyle.setBorderLeft(BorderStyle.THIN); cellStyle.setBorderRight(BorderStyle.THIN); cellStyle.setFillForegroundColor(bgColor); cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); cellStyle.setAlignment(HorizontalAlignment.CENTER); cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); return cellStyle; } /** * 设置单元格的值(excel流) * * @param sheet 工作表 * @param rowNum 行数 * @param colNum 列数 * @param cs 单元格样式（从外部传进来，减少对象个数及“不同样式的单元格太多”的问题） * @param value 设的值 */ public static void setCellValue(SXSSFSheet sheet, int rowNum, int colNum, CellStyle cs, Object value) { SXSSFRow row = sheet.getRow(rowNum); if (row == null) { row = sheet.createRow(rowNum); } Cell cell = row.getCell(colNum); if (cell == null) { cell = row.createCell(colNum); } if (cs != null) { cell.setCellStyle(cs); } if (value == null) { value = ""; } cell.setCellValue(value.toString()); } /** * 设置一行单元格的值 * @param sheet 工作表 * @param rowNum 行数 * @param startColNum 起始列数 * @param cs 单元格样式 * @param values 值的数组 */ public static void setLineValue(SXSSFSheet sheet, int rowNum, int startColNum, CellStyle cs, Object[] values) { for (int i = 0; i < values.length; i++) { setCellValue(sheet, rowNum, startColNum++, cs, values[i]); } } /** * 设置一行单元格的值 * @param sheet 工作表 * @param rowNum 行数 * @param startColNum 起始列数 * @param cs 单元格样式 * @param values 值(集合) */ public static void setLineValue(SXSSFSheet sheet, int rowNum, int startColNum, CellStyle cs, List<Object> values) { for (int i = 0; i < values.size(); i++) { setCellValue(sheet, rowNum, startColNum++, cs, values.get(i)); } } }
3、编写样例
3.1、Excel 2003 样例
/** * Excel 2003 写 * @throws IOException */ @Test public void excel2003Write() throws IOException { OutputStream outputStream = null; try { outputStream = new FileOutputStream("d:/a.xls"); HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet(); HSSFCellStyle cellStyle = Excel2003Util.createCellStyle(wb, IndexedColors.WHITE.index); int row = 0; Random random = new Random(); Excel2003Util.setLineValue(sheet, row++, 0, cellStyle, Arrays.asList("姓名", "年龄", "身高", "体重")); for (int i = 0; i < 100; i++) { Excel2003Util.setLineValue(sheet, row++, 0, cellStyle, Arrays.asList(RandomStringUtils.randomAlphanumeric(6), random.nextInt(10) + 10, random.nextInt(20) + 160, random.nextInt(20) + 50)); } wb.write(outputStream); } finally { FileUtil.close(outputStream); } } /** * Excel 2003 读 * @throws IOException */ @Test public void excel2003Read() throws IOException { InputStream inputStream = null; try { inputStream = new FileInputStream("d:/a.xls"); HSSFWorkbook workbook = new HSSFWorkbook(inputStream); HSSFSheet sheet = workbook.getSheetAt(0); List<String> rowData = new ArrayList<>(); for (int i = 0; i <= sheet.getLastRowNum(); i++) { HSSFRow row = sheet.getRow(i); rowData.clear(); for (int j = 0; j < row.getLastCellNum(); j++) { rowData.add(Excel2003Util.getCellValue(row, j)); } logger.info("第{}行数据:{}", i, rowData); } } finally { FileUtil.close(inputStream); } }
3.2、Excel 2007 样例
/** * Excel 2007 写 * @throws IOException */ @Test public void excel2007Write() throws IOException { OutputStream outputStream = null; try { outputStream = new FileOutputStream("d:/a.xlsx"); XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet sheet = workbook.createSheet(); XSSFCellStyle cellStyle = Excel2007Util.createCellStyle(workbook, IndexedColors.WHITE.index); int row = 0; Random random = new Random(); Excel2007Util.setLineValue(sheet, row++, 0, cellStyle, Arrays.asList("姓名", "年龄", "身高", "体重")); for (int i = 0; i < 100; i++) { Excel2007Util.setLineValue(sheet, row++, 0, cellStyle, Arrays.asList(RandomStringUtils.randomAlphanumeric(6), random.nextInt(10) + 10, random.nextInt(20) + 160, random.nextInt(20) + 50)); } workbook.write(outputStream); } finally { FileUtil.close(outputStream); } } /** * Excel 2007 读 * @throws IOException */ @Test public void excel2007Read() throws IOException { InputStream inputStream = null; try { inputStream = new FileInputStream("d:/a.xlsx"); XSSFWorkbook workbook = new XSSFWorkbook(inputStream); XSSFSheet sheet = workbook.getSheetAt(0); List<String> rowData = new ArrayList<>(); for (int i = 0; i <= sheet.getLastRowNum(); i++) { XSSFRow row = sheet.getRow(i); rowData.clear(); for (int j = 0; j < row.getLastCellNum(); j++) { rowData.add(Excel2007Util.getCellValue(row, j)); } logger.info("第{}行数据:{}", i, rowData); } } finally { FileUtil.close(inputStream); } }
3.3、Excel 2007 样例（流模式）
使用流模式来写 Excel，只有部分记录放在内存，其他的写入到临时文件，可以避免对内存的大量使用。
/** * Excel 2007 写(流方式) * @throws IOException */ @Test public void excel2007WriteStream() throws IOException { OutputStream outputStream = null; SXSSFWorkbook workbook = null; try { outputStream = new FileOutputStream("d:/a2.xlsx"); workbook = new SXSSFWorkbook(1000); SXSSFSheet sheet = workbook.createSheet(); CellStyle cellStyle = Excel2007Util.createCellStyle(workbook, IndexedColors.WHITE.index); int row = 0; Random random = new Random(); Excel2007Util.setLineValue(sheet, row++, 0, cellStyle, Arrays.asList("姓名", "年龄", "身高", "体重")); for (int i = 0; i < 10000; i++) { Excel2007Util.setLineValue(sheet, row++, 0, cellStyle, Arrays.asList(RandomStringUtils.randomAlphanumeric(6), random.nextInt(10) + 10, random.nextInt(20) + 160, random.nextInt(20) + 50)); } workbook.write(outputStream); } finally { FileUtil.close(outputStream); if (workbook != null) { workbook.dispose(); } } }
3.4、完整代码
package com.abc.demo.general.excel.user; import com.abc.demo.general.util.FileUtil; import org.apache.commons.lang3.RandomStringUtils; import org.apache.poi.hssf.usermodel.HSSFCellStyle; 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.CellStyle; import org.apache.poi.ss.usermodel.IndexedColors; import org.apache.poi.xssf.streaming.SXSSFSheet; import org.apache.poi.xssf.streaming.SXSSFWorkbook; import org.apache.poi.xssf.usermodel.XSSFCellStyle; import org.apache.poi.xssf.usermodel.XSSFRow; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.junit.Test; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import java.io.*; import java.util.ArrayList; import java.util.Arrays; import java.util.List; import java.util.Random; /** * POI用户模式读写Excel */ public class PoiUserCase { private static Logger logger = LoggerFactory.getLogger(PoiUserCase.class); /** * Excel 2003 写 * @throws IOException */ @Test public void excel2003Write() throws IOException { OutputStream outputStream = null; try { outputStream = new FileOutputStream("d:/a.xls"); HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet(); HSSFCellStyle cellStyle = Excel2003Util.createCellStyle(wb, IndexedColors.WHITE.index); int row = 0; Random random = new Random(); Excel2003Util.setLineValue(sheet, row++, 0, cellStyle, Arrays.asList("姓名", "年龄", "身高", "体重")); for (int i = 0; i < 100; i++) { Excel2003Util.setLineValue(sheet, row++, 0, cellStyle, Arrays.asList(RandomStringUtils.randomAlphanumeric(6), random.nextInt(10) + 10, random.nextInt(20) + 160, random.nextInt(20) + 50)); } wb.write(outputStream); } finally { FileUtil.close(outputStream); } } /** * Excel 2003 读 * @throws IOException */ @Test public void excel2003Read() throws IOException { InputStream inputStream = null; try { inputStream = new FileInputStream("d:/a.xls"); HSSFWorkbook workbook = new HSSFWorkbook(inputStream); HSSFSheet sheet = workbook.getSheetAt(0); List<String> rowData = new ArrayList<>(); for (int i = 0; i <= sheet.getLastRowNum(); i++) { HSSFRow row = sheet.getRow(i); rowData.clear(); for (int j = 0; j < row.getLastCellNum(); j++) { rowData.add(Excel2003Util.getCellValue(row, j)); } logger.info("第{}行数据:{}", i, rowData); } } finally { FileUtil.close(inputStream); } } /** * Excel 2007 写 * @throws IOException */ @Test public void excel2007Write() throws IOException { OutputStream outputStream = null; try { outputStream = new FileOutputStream("d:/a.xlsx"); XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet sheet = workbook.createSheet(); XSSFCellStyle cellStyle = Excel2007Util.createCellStyle(workbook, IndexedColors.WHITE.index); int row = 0; Random random = new Random(); Excel2007Util.setLineValue(sheet, row++, 0, cellStyle, Arrays.asList("姓名", "年龄", "身高", "体重")); for (int i = 0; i < 100; i++) { Excel2007Util.setLineValue(sheet, row++, 0, cellStyle, Arrays.asList(RandomStringUtils.randomAlphanumeric(6), random.nextInt(10) + 10, random.nextInt(20) + 160, random.nextInt(20) + 50)); } workbook.write(outputStream); } finally { FileUtil.close(outputStream); } } /** * Excel 2007 读 * @throws IOException */ @Test public void excel2007Read() throws IOException { InputStream inputStream = null; try { inputStream = new FileInputStream("d:/a.xlsx"); XSSFWorkbook workbook = new XSSFWorkbook(inputStream); XSSFSheet sheet = workbook.getSheetAt(0); List<String> rowData = new ArrayList<>(); for (int i = 0; i <= sheet.getLastRowNum(); i++) { XSSFRow row = sheet.getRow(i); rowData.clear(); for (int j = 0; j < row.getLastCellNum(); j++) { rowData.add(Excel2007Util.getCellValue(row, j)); } logger.info("第{}行数据:{}", i, rowData); } } finally { FileUtil.close(inputStream); } } /** * Excel 2007 写(流方式) * @throws IOException */ @Test public void excel2007WriteStream() throws IOException { OutputStream outputStream = null; SXSSFWorkbook workbook = null; try { outputStream = new FileOutputStream("d:/a2.xlsx"); workbook = new SXSSFWorkbook(1000); SXSSFSheet sheet = workbook.createSheet(); CellStyle cellStyle = Excel2007Util.createCellStyle(workbook, IndexedColors.WHITE.index); int row = 0; Random random = new Random(); Excel2007Util.setLineValue(sheet, row++, 0, cellStyle, Arrays.asList("姓名", "年龄", "身高", "体重")); for (int i = 0; i < 10000; i++) { Excel2007Util.setLineValue(sheet, row++, 0, cellStyle, Arrays.asList(RandomStringUtils.randomAlphanumeric(6), random.nextInt(10) + 10, random.nextInt(20) + 160, random.nextInt(20) + 50)); } workbook.write(outputStream); } finally { FileUtil.close(outputStream); if (workbook != null) { workbook.dispose(); } } } }
package com.abc.demo.general.util; import java.io.IOException; import java.io.InputStream; import java.io.OutputStream; /** * 文件操作工具 */ public class FileUtil { private FileUtil() {} public static void close(InputStream in) { try { if (in != null) { in.close(); } } catch (IOException e) { e.printStackTrace(); } } public static void close(OutputStream out) { try { if (out != null) { out.close(); } } catch (IOException e) { e.printStackTrace(); } } }
