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

对 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

测试二：

看起来效果还不错，当然还有很多不完善的地方，有需要的朋友可以在此基础上扩展定制，例如读取表数据结构方式，实现行数增删改查据或者创建表标题等等。

或者有朋友有更好的实现方案，欢迎前来交流！

最后的最后，当然忘不了附上笨工具的源码啦！

https://github.com/yumiaoxia/excel-commom-demo.git

来源：https://www.cnblogs.com/yumiaoxia/p/9545750.html