Java利用POI读写Excel文件工具类

 package com.test.app.utils; import java.io.File; import java.io.FileInputStream; import java.io.IOException; import java.io.InputStream; import java.lang.reflect.Field; import java.lang.reflect.Method; import java.util.ArrayList; import java.util.List; import org.apache.poi.hssf.usermodel.HSSFDateUtil; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.Cell; 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; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.util.CollectionUtils; /** * @Description: Excel读写工具类 * @Author: hunger.zhu * @CreateDate: 2019/4/10 13:21 */ public class ExcelUtils { private static final Logger logger = LoggerFactory.getLogger(ExcelUtils.class); /** * 读取Excel内容 * @param file 需要被读的文件对象 * @param startRow 从哪一行开始读 (rowIndex从0开始的) * @param isExcel2003  是否是excel2003还是更高的版本 * @param sheetIndex  读取哪一个sheet (sheetIndex也是从0开始) * @return List> * @throws Exception */ public static List> readExcel(File file, int startRow, boolean isExcel2003, int sheetIndex) throws Exception { List> dataLst; InputStream is = null; try { /** 创建读取文件的输入流 */ is = new FileInputStream(file); /** 根据版本选择创建Workbook的方式 */ Workbook wb; if (isExcel2003) { wb = new HSSFWorkbook(is); } else { wb = new XSSFWorkbook(is); } /** 调用本类的读取方法读取excel数据 */ dataLst = read(wb, startRow, sheetIndex); } catch (Exception ex) { logger.error("读取excel文件异常!", ex); ex.printStackTrace(); throw ex; } finally { if (is != null) { try { is.close(); } catch (IOException e) { e.printStackTrace(); } } } /** 返回最后读取的结果 */ return dataLst; } private static List> read(Workbook wb, int startRow, int sheetIndex) { /** 总列数 */ int totalCells = 0; /** 创建集合存储读取的数据 */ List> dataLst = new ArrayList>(); /** 得到第一个shell */ Sheet sheet = wb.getSheetAt(sheetIndex); /** 得到Excel的行数 */ int totalRows = sheet.getPhysicalNumberOfRows(); /** 得到Excel的列数 */ if (totalRows >= 1 && sheet.getRow(0) != null) { totalCells = sheet.getRow(0).getPhysicalNumberOfCells(); } /** 循环Excel的行 */ for (int r = startRow; ; r++) { Row row = sheet.getRow(r); if (row == null) { break; } List rowLst = new ArrayList(); /** 循环Excel的列 */ for (int c = 0; c > * @throws Exception */ public static List> readExcel(String filePath, int startRow, boolean isExcel2003, int sheetIndex) throws Exception { return readExcel(new File(filePath) , startRow, isExcel2003, sheetIndex); } /** * 将数据写入Excel工作簿 * @param header  表格的标题 * @param dataList 所需写入的数据 List> * @param isExcel2003  是否是excel2003还是更高的版本 * @param sheetName   生成的excel中sheet的名字 * @return Workbook  之后直接写出即可，如workbook.write(new FileOutputStream("E://test/20190410_test.xlsx")); */ public static Workbook getWorkbookFromList(List header, List> dataList, boolean isExcel2003, String sheetName) { Workbook wb; // 创建Workbook对象(excel的文档对象) if (isExcel2003) { wb = new HSSFWorkbook(); } else { wb = new XSSFWorkbook(); } // 建立新的sheet对象（excel的表单） Sheet sheet = wb.createSheet(sheetName); // 在sheet里创建第一行，参数为行索引(excel的行)，可以是0～65535之间的任何一个 int rowNum = 0; Row row0 = sheet.createRow(rowNum); if (!CollectionUtils.isEmpty(header)) { // 设置表头 for (int i = 0; i  cellList : dataList) { Row row = sheet.createRow(rowNum); for (int i = 0; i  * @param isExcel2003  是否是excel2003还是更高的版本 * @param sheetName   生成的excel中sheet的名字 * @return Workbook对象，之后直接写出即可，如workbook.write(new FileOutputStream("E://test/20190410_test.xlsx")); * @throws Exception */ public static Workbook getWorkbookFromObj(List header, List dataList, boolean isExcel2003, String sheetName) throws Exception { Workbook wb; // 创建Workbook对象(excel的文档对象) if (isExcel2003) { wb = new HSSFWorkbook(); } else { wb = new XSSFWorkbook(); } // 建立新的sheet对象（excel的表单） Sheet sheet = wb.createSheet(sheetName); // 在sheet里创建第一行，参数为行索引(excel的行)，可以是0～65535之间的任何一个 int rowNum = 0; Row row0 = sheet.createRow(rowNum); if (!CollectionUtils.isEmpty(header)) { // 设置表头 for (int i = 0; i  objClass = dataList.get(0).getClass(); Field[] fields = objClass.getDeclaredFields(); for (int i = 0; i 

 package com.test.app.utils; import org.apache.poi.ss.usermodel.*; import org.apache.poi.ss.usermodel.Font; import org.apache.poi.xssf.usermodel.XSSFCellStyle; import org.apache.poi.xssf.usermodel.XSSFColor; import org.apache.poi.xssf.usermodel.XSSFFont; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import java.awt.*; import java.awt.Color; /** * @Description: Excel的单元格样式 * @Author: hunger.zhu * @CreateDate: 2019/4/10 13:05 */ public class POIUtils { /** * 设置单元格的边框（细）且为黑色，字体水平垂直居中，自动换行 * @param workbook * @param fontName * @param fontSize * @return */ public static CellStyle getCellStyle(Workbook workbook, String fontName, short fontSize){ CellStyle style = workbook.createCellStyle(); Font font = workbook.createFont(); // 设置上下左右四个边框宽度 style.setBorderTop(BorderStyle.THIN); style.setBorderBottom(BorderStyle.THIN); style.setBorderLeft(BorderStyle.THIN); style.setBorderRight(BorderStyle.THIN); // 设置上下左右四个边框颜色 style.setTopBorderColor(IndexedColors.BLACK.getIndex()); style.setBottomBorderColor(IndexedColors.BLACK.getIndex()); style.setLeftBorderColor(IndexedColors.BLACK.getIndex()); style.setRightBorderColor(IndexedColors.BLACK.getIndex()); // 水平居中，垂直居中，自动换行 style.setAlignment(HorizontalAlignment.CENTER); style.setVerticalAlignment(VerticalAlignment.CENTER); style.setWrapText(false); // 设置字体样式及大小 font.setFontName(fontName); font.setFontHeightInPoints(fontSize); style.setFont(font); return style; } }