详解java封装实现Excel建表读写操作

2019-11-26 09:41:55

字体：大中小

来源：转载

供稿：网友

对 Excel 进行读写操作是生产环境下常见的业务，网上搜索的实现方式都是基于POI和JXL第三方框架，但都不是很全面。小编由于这两天刚好需要用到，于是就参考手写了一个封装操作工具，基本涵盖了Excel表（分有表头和无表头）的创建，并对它们进行读写操作。为方便大家，有需要者可以点击文后点解下载直接使用哦，当然也可以根据自己需求举一反三自己定制，相信对于聪明的你也不是什么难事。话不多说，直接贴源码

pom.xml 文件：

<properties> <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding> <maven.compiler.source>1.8</maven.compiler.source> <maven.compiler.target>1.8</maven.compiler.target> </properties> <dependencies> <dependency>  <groupId>junit</groupId>  <artifactId>junit</artifactId>  <version>4.11</version>  <scope>test</scope> </dependency> <dependency>  <groupId>org.apache.poi</groupId>  <artifactId>poi</artifactId>  <version>3.17</version> </dependency> <dependency>  <groupId>org.projectlombok</groupId>  <artifactId>lombok</artifactId>  <version>1.18.0</version>  <scope>provided</scope> </dependency> <dependency>  <groupId>org.slf4j</groupId>  <artifactId>slf4j-log4j12</artifactId>  <version>1.8.0-beta2</version>  <scope>test</scope> </dependency> <dependency>  <groupId>log4j</groupId>  <artifactId>log4j</artifactId>  <version>1.2.17</version> </dependency> <dependency>  <groupId>org.slf4j</groupId>  <artifactId>slf4j-api</artifactId>  <version>1.8.0-beta2</version> </dependency> </dependencies>

建表工具类：ExcelBuider.java

/**   * 建表工具类   * @author Sherman   * email:1253950375@qq.com   * created in 2018/8/24   */  @Slf4j  public class ExcelBuilder {   private static HSSFSheet sheet;   private static HSSFWorkbook wb;   private static boolean hasHeader;   /**    * 初始化    * @param excellName 表名    */   public ExcelBuilder(String excellName) {    wb = new HSSFWorkbook();    sheet = wb.createSheet(excellName);   }   /**    * 设置表头，装配表头数据    * @param value 字符串数组，用来作为表头的值    *    */   public ExcelBuilder header(String... value) {    if (value != null && value.length != 0) {     //设置表头样式     HSSFCellStyle cellStyle = wb.createCellStyle();     cellStyle.setFont(font("黑体", true, 12));     HSSFRow row = sheet.createRow(0);     for (int i = 0; i < value.length; i++) {      HSSFCell cell = row.createCell(i);      cell.setCellValue(value[i]);      cell.setCellStyle(cellStyle);     }     hasHeader = true;    }    return this; } /**  * excel 表内容装配  * @param content 待装配表格内容的二维数组  * @return  */ public ExcelBuilder content(List<List<Object>> content) {  if (content != null && !content.isEmpty()) {   int index;   for (int i = 0; i < content.size(); i++) {    index = hasHeader == false ? i : i + 1;    HSSFRow row = sheet.createRow(index);    for (int j = 0; j < content.get(i).size(); j++) {     String r = "";     Object value = content.get(i).get(j);     //根据数据类型装配     if (value instanceof String) {      r = (String) value;     } else if (value instanceof Number) {      r = String.valueOf(value);     } else if (value instanceof BigDecimal) {      r = String.valueOf(value);     } else {      if (!(value instanceof Date) && !(value instanceof Timestamp)) {       if (!(value instanceof ZonedDateTime) && !(value instanceof LocalDateTime)) {        if (value instanceof Enum) {         r = ((Enum) value).name();        } else if (value != null) {         log.info("Error of create row, Unknow field type: " + value.getClass().getName());        }       } else {        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd");        r = formatter.format((TemporalAccessor) value);       }      } else {       DateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");       r = sdf.format(value);      }     }     row.createCell(j).setCellValue(r);    }   }  }  return this; } /**  * 自动调整列宽大小  */ public ExcelBuilder autoColumnWidth() {  for (int j = 0; j < sheet.getRow(0).getLastCellNum(); j++) {   int maxLength = 0;   for (int i = 0; i <= sheet.getLastRowNum(); i++) {    String value = sheet.getRow(i).getCell(j).getStringCellValue();    int length = 0;    if (value != null) {     length = value.getBytes().length;    }    if (length > maxLength) {     maxLength = length;    }   }   sheet.setColumnWidth(j, maxLength > 30 ? (30 * 256 + 186) : (maxLength * 256 + 186));  }  return this; } /**  * 实例化  * @param hasHeader 是否有表头  * @return Excel表格  */ public AbstractExcel build(Boolean hasHeader) {  return hasHeader ? new HeaderExcel(sheet) : new NoHeaderExcel(sheet); } /**  *  * @param fontName 字体名字  * @param isBold 是否粗体  * @param fontSize 字体大小  * @return 字体  */ private HSSFFont font(String fontName, boolean isBold, int fontSize) {  HSSFFont font = wb.createFont();  if (fontName != null) font.setFontName(fontName);  else font.setFontName("黑体");  font.setBold(isBold);  font.setFontHeightInPoints((short) fontSize);  return font; }}

excel的抽象父类：

/** * @author Sherman * created in 2018/8/24 */public abstract class AbstractExcel { private final HSSFSheet sheet; public AbstractExcel() {  HSSFWorkbook wb = new HSSFWorkbook();  sheet = wb.createSheet(); } public AbstractExcel(String sheetName){  HSSFWorkbook wb = new HSSFWorkbook();  sheet = wb.createSheet(sheetName); } public AbstractExcel(HSSFSheet sheet) {  this.sheet = sheet; } public abstract List<Map<String, String>> getPayload(); public void write(OutputStream op) throws IOException {  sheet.getWorkbook().write(op);  sheet.getWorkbook().close(); } public String getStringFormatCellValue(HSSFCell cell) {  String cellVal = "";  DecimalFormat df = new DecimalFormat("#");  switch (cell.getCellTypeEnum()) {   case STRING:    cellVal = cell.getStringCellValue();    break;   case NUMERIC:    String dataFormat = cell.getCellStyle().getDataFormatString();    if (DateUtil.isCellDateFormatted(cell)) {     cellVal = df.format(cell.getDateCellValue());    } else if ("@".equals(dataFormat)) {     cellVal = df.format(cell.getNumericCellValue());    } else {     cellVal = String.valueOf(cell.getNumericCellValue());     df = new DecimalFormat("#.#########");     cellVal = df.format(Double.valueOf(cellVal));    }    break;   case BOOLEAN:    cellVal = String.valueOf(cell.getBooleanCellValue());    break;   case FORMULA:    cellVal = String.valueOf(cell.getCellFormula());    break;   default:    cellVal = "";  }  return cellVal; }}

有表头实现类

/** * @author Sherman * created in 2018/8/24 */public class HeaderExcel extends AbstractExcel { private final static boolean hasHeader = true; private final HSSFSheet sheet; public HeaderExcel(HSSFSheet sheet) {  super(sheet);  this.sheet = sheet; } public HeaderExcel(String sheetName, String excelPath) {  HSSFWorkbook wb = null;  try {   wb = new HSSFWorkbook(new POIFSFileSystem(new FileInputStream(excelPath)));  } catch (IOException e) {   e.printStackTrace();  }  sheet = sheetName == null || sheetName.isEmpty() ? wb.getSheetAt(0) : wb.getSheet(sheetName); } @Override public List<Map<String, String>> getPayload() {  List<Map<String, String>> payLoad = new ArrayList<>();  HSSFRow headRow = sheet.getRow(0);  for (int i = 1; i <= sheet.getLastRowNum(); i++) {   HSSFRow currentRow = sheet.getRow(i);   Map<String, String> map = new HashMap<>();   for (int j = 0; j < sheet.getRow(i).getLastCellNum(); j++) {    map.put(getStringFormatCellValue(headRow.getCell(j)), getStringFormatCellValue(currentRow.getCell(j)));   }   payLoad.add(map);  }  return payLoad; }}

无表头实现类

/** * @author Sherman * created in 2018/8/24 */public class NoHeaderExcel extends AbstractExcel { private final static boolean hasHeader = false; private HSSFSheet sheet; public NoHeaderExcel(HSSFSheet sheet) {  super(sheet);  this.sheet = sheet; } public NoHeaderExcel(String sheetName, String excelPath) {  HSSFWorkbook wb = null;  try {   wb = new HSSFWorkbook(new POIFSFileSystem(new FileInputStream(excelPath)));  } catch (IOException e) {   e.printStackTrace();  }  sheet = sheetName == null || sheetName.isEmpty() ? wb.getSheetAt(0) : wb.getSheet(sheetName); } @Override public List<Map<String, String>> getPayload() {  List<Map<String, String>> payLoad = new ArrayList<>();  for (int i = 0; i < sheet.getLastRowNum(); i++) {   HSSFRow currentRow = sheet.getRow(i);   Map<String, String> map = new HashMap<>();   for (int j = 0; j <= sheet.getRow(i).getLastCellNum(); j++) {    map.put(String.valueOf(j), getStringFormatCellValue(currentRow.getCell(j)));   }   payLoad.add(map);  }  return payLoad; }}

测试工具类：

/** * Unit test for simple App. */public class AppTest { /**  * 测试建表，写表操作  */ @Test public void testExportExcel() {  //测试数据  String[] headers = new String[]{"A","B","C","D","E"};   List<List<Object>> valueList = new LinkedList<>();  for (char i = 'A'; i <= 'E' ; i++) {   List<Object> rowList = new LinkedList<>();   for (int j = 0; j <= 4; j++) {    rowList.add(i+String.valueOf(j));   }   valueList.add(rowList);  } AbstractExcel excel = new ExcelBuilder("报名表")   .header(headers)   .content(valueList)   .autoColumnWidth()   .build(true);  try {   File file = new File("E://excel//test.xls");   FileOutputStream op = new FileOutputStream(file);   excel.write(op);  } catch (IOException e) {   e.printStackTrace();  } } /**  * 测试读取表数据操作  */ @Test public void testImportExcel(){  AbstractExcel excel = new HeaderExcel(null,"E:/excel/test.xls");  List<Map<String,String>> values = excel.getPayload();  values.forEach(stringStringMap -> {   stringStringMap.entrySet().forEach(stringStringEntry -> {    System.out.println(stringStringEntry.getKey()+"---->"+stringStringEntry.getValue());   });  }); }}

附图：

测试1