Java解析Excel工具类(兼容xls和xlsx)
依赖jar
<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>4.0.1</version> </dependency>
ExcelUtils.java
package javax.utils; import java.io.File; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.FileOutputStream; import java.io.IOException; import java.io.InputStream; import java.io.OutputStream; import java.math.BigDecimal; import java.text.DateFormat; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Date; import java.util.List; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.DateUtil; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.xssf.usermodel.XSSFWorkbook; /** * Excel 工具类(兼容xls和xlsx) * * @author Logan * @version 1.0.0 * @createDate 2019-03-07 * */ public class ExcelUtils { private static final String XLS = "xls"; private static final String XLSX = "xlsx"; private static final DateFormat FORMAT = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss"); /** * 输出数据到自定义模版的Excel输出流 * * @param excelTemplate 自定义模版文件 * @param data 数据 * @param outputStream Excel输出流 * @throws IOException 错误时抛出异常，由调用者处理 */ public static void writeDataToTemplateOutputStream(File excelTemplate, List<List<Object>> data, OutputStream outputStream) throws IOException { Workbook book = ExcelUtils.getWorkbookFromExcel(excelTemplate); ExcelUtils.writeDataToWorkbook(null, data, book, 0); ExcelUtils.writeWorkbookToOutputStream(book, outputStream); } /** * 从Excel文件获取Workbook对象 * * @param excelFile Excel文件 * @return Workbook对象 * @throws IOException 错误时抛出异常，由调用者处理 */ public static Workbook getWorkbookFromExcel(File excelFile) throws IOException { try ( InputStream inputStream = new FileInputStream(excelFile); ) { if (excelFile.getName().endsWith(XLS)) { return new HSSFWorkbook(inputStream); } else if (excelFile.getName().endsWith(XLSX)) { return new XSSFWorkbook(inputStream); } else { throw new IOException("文件类型错误"); } } } /** * 把Workbook对象内容输出到Excel文件 * * @param book Workbook对象 * @param file Excel文件 * @throws FileNotFoundException 找不到文件异常，文件已创建，实际不存在该异常 * @throws IOException 输入输出异常 */ public static void writeWorkbookToFile(Workbook book, File file) throws FileNotFoundException, IOException { if (!file.exists()) { if (!file.getParentFile().exists()) { file.getParentFile().mkdirs(); } file.createNewFile(); } try ( OutputStream outputStream = new FileOutputStream(file); ) { writeWorkbookToOutputStream(book, outputStream); } } /** * 把Workbook对象输出到Excel输出流 * * @param book Workbook对象 * @param outputStream Excel输出流 * @throws IOException 错误时抛出异常，由调用者处理 */ public static void writeWorkbookToOutputStream(Workbook book, OutputStream outputStream) throws IOException { book.write(outputStream); } /** * 输出数据到Workbook对象中指定页码 * * @param title 标题，写在第一行，可传null * @param data 数据 * @param book Workbook对象 * @param page 输出数据到Workbook指定页码的页面数 */ public static void writeDataToWorkbook(List<String> title, List<List<Object>> data, Workbook book, int page) { Sheet sheet = book.getSheetAt(page); Row row = null; Cell cell = null; // 设置表头 if (null != title && !title.isEmpty()) { row = sheet.getRow(0); if (null == row) { row = sheet.createRow(0); } for (int i = 0; i < title.size(); i++) { cell = row.getCell(i); if (null == cell) { cell = row.createCell(i); } cell.setCellValue(title.get(i)); } } List<Object> rowData = null; for (int i = 0; i < data.size(); i++) { row = sheet.getRow(i + 1); if (null == row) { row = sheet.createRow(i + 1); } rowData = data.get(i); if (null == rowData) { continue; } for (int j = 0; j < rowData.size(); j++) { cell = row.getCell(j); if (null == cell) { cell = row.createCell(j); } setValue(cell, rowData.get(j)); } } } /** * 读取Excel文件第一页 * * @param pathname 文件路径名 * @return 第一页数据集合 * @throws IOException 错误时抛出异常，由调用者处理 */ public static List<List<Object>> readExcelFirstSheet(String pathname) throws IOException { File file = new File(pathname); return readExcelFirstSheet(file); } /** * 读取Excel文件第一页 * * @param file Excel文件 * @return 第一页数据集合 * @throws IOException 错误时抛出异常，由调用者处理 */ public static List<List<Object>> readExcelFirstSheet(File file) throws IOException { try ( InputStream inputStream = new FileInputStream(file); ) { if (file.getName().endsWith(XLS)) { return readXlsFirstSheet(inputStream); } else if (file.getName().endsWith(XLSX)) { return readXlsxFirstSheet(inputStream); } else { throw new IOException("文件类型错误"); } } } /** * 读取xls格式Excel文件第一页 * * @param inputStream Excel文件输入流 * @return 第一页数据集合 * @throws IOException 错误时抛出异常，由调用者处理 */ public static List<List<Object>> readXlsFirstSheet(InputStream inputStream) throws IOException { Workbook workbook = new HSSFWorkbook(inputStream); return readExcelFirstSheet(workbook); } /** * 读取xlsx格式Excel文件第一页 * * @param inputStream Excel文件输入流 * @return 第一页数据集合 * @throws IOException 错误时抛出异常，由调用者处理 */ public static List<List<Object>> readXlsxFirstSheet(InputStream inputStream) throws IOException { Workbook workbook = new XSSFWorkbook(inputStream); return readExcelFirstSheet(workbook); } /** * 读取Workbook第一页 * * @param book Workbook对象 * @return 第一页数据集合 */ public static List<List<Object>> readExcelFirstSheet(Workbook book) { return readExcel(book, 0); } /** * 读取指定页面的Excel * * @param book Workbook对象 * @param page 页码 * @return 指定页面数据集合 */ public static List<List<Object>> readExcel(Workbook book, int page) { List<List<Object>> list = new ArrayList<>(); Sheet sheet = book.getSheetAt(page); for (int i = 0; i <= sheet.getLastRowNum(); i++) { Row row = sheet.getRow(i); // 如果当前行为空，则加入空，保持行号一致 if (null == row) { list.add(null); continue; } List<Object> columns = new ArrayList<>(); for (int j = 0; j < row.getLastCellNum(); j++) { Cell cell = row.getCell(j); columns.add(getValue(cell)); } list.add(columns); } return list; } /** * 解析单元格中的值 * * @param cell 单元格 * @return 单元格内的值 */ private static Object getValue(Cell cell) { if (null == cell) { return null; } Object value = null; switch (cell.getCellType()) { case BOOLEAN: value = cell.getBooleanCellValue(); break; case NUMERIC: // 日期类型，转换为日期 if (DateUtil.isCellDateFormatted(cell)) { value = cell.getDateCellValue(); } // 数值类型 else { // 默认返回double，创建BigDecimal返回准确值 value = new BigDecimal(cell.getNumericCellValue()); } break; default: value = cell.toString(); break; } return value; } /** * 设置单元格值 * * @param cell 单元格 * @param value 值 */ private static void setValue(Cell cell, Object value) { if (null == cell) { return; } if (null == value) { cell.setCellValue((String) null); } else if (value instanceof Boolean) { cell.setCellValue((Boolean) value); } else if (value instanceof Date) { cell.setCellValue(FORMAT.format((Date) value)); } else if (value instanceof Double) { cell.setCellValue((Double) value); } else { cell.setCellValue(value.toString()); } } }
.
