Java Excel

1、POI简介

Apache POI - the Java API for Microsoft Documents，顾名思义，Apache的三方包，用来操作微软office文档的，多数时候用来操作excel，在使用过程中需要引入的修改依赖如下：

  1. <dependency>
  2.     <groupId>org.apache.poi</groupId>
  3.     <artifactId>poi</artifactId>
  4.     <version>3.9</version>
  5. </dependency>
  6. <dependency>
  7.     <groupId>org.apache.poi</groupId>
  8.     <artifactId>poi-ooxml</artifactId>
  9.     <version>3.9</version>
  10. </dependency>

POI的组件列表中，针对excel的主要是HSSF和XSSF组件，前者针对97-2007的通用版excel，即后缀xls；后者针对2007或更高版的excel，即后缀xlsx。
官方概要如下：

  1. HSSF is the POI Project's pure Java implementation of the Excel '97(-2007) file format. 
  2. XSSF is the POI Project's pure Java implementation of the Excel 2007 OOXML (.xlsx) file format.

2、POI核心类

看一下POI操作excel内容的类。

2.1 工作簿 Workbook

创建或维护Excel工作簿的所有类的超接口，Workbook，属于org.apache.poi.ss.usermodel包。
其下有两个实现类：

  • HSSFWorkbook : 有读取.xls 格式和写入Microsoft Excel文件的方法。它与微软Office97-2003版本兼容
  • XSSFWorkbook : 有读写Microsoft Excel和OpenOffice的XML文件的格式.xls或.xlsx的方法。它与MS-Office版本2007或更高版本兼容

所以在针对不同版本的excel时，需要对应以上使用不同的Workbook。构造函数中，常用的：

HSSFWorkbook

  1. HSSFWorkbook()
  2. HSSFWorkbook(java.io.InputStream s)

XSSFWorkbook

  1. XSSFWorkbook()
  2. XSSFWorkbook(java.io.File file)
  3. XSSFWorkbook(java.io.InputStream is)

2.2 标签页 Sheet

HSSFSheet 和 XSSFSheet 都是Sheet接口的实现类，Sheet可以使用Workbook的两个方法获得：

  1. workbook.createSheet();
  2. workbook.createSheet(String sheetName);

2.3 行 Row

同理，Row是 HSSFRow 和 XSSFRow 的接口，通过Sheet获取：

  1. sheet.createRow(int rownum);

2.4 单元格 Cell

同理，Cell是 HSSFCell 和 XSSFCell 的接口，通过Row获取：

  1. row.createCell(int column);
  2. row.createCell(int column, int type);

3、创建和读取

POI中包含字体，公式，超链接等对应的封装类，这里只提出了核心的几个，需要了解更多的需要自行展开。

3.1 创建空白工作簿

  1. import java.io.*;
  2. import org.apache.poi.xssf.usermodel.*;
  3. public class CreateWorkBook {
  4.    public static void main(String[] args)throws Exception {
  5.       XSSFWorkbook workbook = new XSSFWorkbook(); 
  6.       FileOutputStream out = new FileOutputStream(new File("createworkbook.xlsx"));
  7.       workbook.write(out);
  8.       out.close();
  9.       System.out.println("createworkbook.xlsx written successfully");
  10.    }
  11. }

3.2 打开现有的工作簿

  1. import java.io.*;
  2. import org.apache.poi.xssf.usermodel.*;
  3. public class OpenWorkBook{
  4.    public static void main(String args[])throws Exception{ 
  5.       File file = new File("openworkbook.xlsx");
  6.       FileInputStream fIP = new FileInputStream(file);
  7.       XSSFWorkbook workbook = new XSSFWorkbook(fIP);
  8.       if(file.isFile() && file.exists()){
  9.          System.out.println("openworkbook.xlsx file open successfully.");
  10.       }else {
  11.          System.out.println("Error to open openworkbook.xlsx file.");
  12.       }
  13.    }
  14. }

3.3 任意对象List转至为Excel文档

可用注解定义标签名和列名，可以将某个类的List转换为对应的Excel文档，列名如果在不使用注解的情况下默认为属性名：
类：

  1. @Excel(name = "学生标签页")
  2. public class Student {
  3.     @Excel(name = "姓名")
  4.     private String name;
  5.     private boolean male;
  6.     @Excel(name = "身高")
  7.     private int height;
  8.     public String getName() {
  9.         return name;
  10.     }
  11.     public void setName(String name) {
  12.         this.name = name;
  13.     }
  14.     public boolean isMale() {
  15.         return male;
  16.     }
  17.     public void setMale(boolean male) {
  18.         this.male = male;
  19.     }
  20.     public int getHeight() {
  21.         return height;
  22.     }
  23.     public void setHeight(int height) {
  24.         this.height = height;
  25.     }
  26. }

测试方法：

  1. public static void main(String[] args) {
  2.     List<Student> list = new ArrayList<Student>();
  3.     Student student1 = new Student();
  4.     student1.setName("小红");
  5.     student1.setMale(false);
  6.     student1.setHeight(167);
  7.     Student student2 = new Student();
  8.     student2.setName("小明");
  9.     student2.setMale(true);
  10.     student2.setHeight(185);
  11.     list.add(student1);
  12.     list.add(student2);
  13.     File file = new File("C:/Users/Dulk/Desktop/1314.xls");
  14.     createExcel(list, file);
  15. }

注解：

  1. import java.lang.annotation.Retention;
  2. import java.lang.annotation.RetentionPolicy;
  3. @Retention(RetentionPolicy.RUNTIME)
  4. public @interface Excel {
  5.     public String name() default "";
  6. }

方法：

  1. import org.apache.log4j.Logger;
  2. import org.apache.poi.hssf.usermodel.HSSFWorkbook;
  3. import org.apache.poi.ss.usermodel.Cell;
  4. import org.apache.poi.ss.usermodel.Row;
  5. import org.apache.poi.ss.usermodel.Sheet;
  6. import org.apache.poi.ss.usermodel.Workbook;
  7. import org.apache.poi.xssf.usermodel.XSSFWorkbook;
  8. import java.io.File;
  9. import java.io.FileInputStream;
  10. import java.io.FileNotFoundException;
  11. import java.io.FileOutputStream;
  12. import java.io.IOException;
  13. import java.io.InputStream;
  14. import java.io.OutputStream;
  15. import java.lang.reflect.Field;
  16. import java.lang.reflect.InvocationTargetException;
  17. import java.lang.reflect.Method;
  18. import java.util.ArrayList;
  19. import java.util.List;
  20. public class ExcelUtil {
  21.     private static Logger log = Logger.getLogger(ExcelUtil.class);
  23.     public static Workbook gainWorkbook(File file) throws ExcelException {
  24.         if (!isExcel(file)) {
  25.             throw new ExcelException("文件不是Excel类型");
  26.         }
  28.         if (!file.exists()) {
  29.             try {
  30.                 OutputStream os = new FileOutputStream(file);
  31.                 Workbook workbook = isOlderEdition(file) ? new HSSFWorkbook() : new XSSFWorkbook();
  32.                 workbook.write(os);
  33.                 log.debug("文件不存在，新建该Excel文件");
  34.                 os.close();
  35.             } catch (FileNotFoundException e) {
  36.                 e.printStackTrace();
  37.             } catch (IOException e) {
  38.                 e.printStackTrace();
  39.             }
  40.         }
  41.         try {
  42.             InputStream is = new FileInputStream(file);
  43.             return isOlderEdition(file) ? new HSSFWorkbook(is) : new XSSFWorkbook(is);
  44.         } catch (FileNotFoundException e) {
  45.             e.printStackTrace();
  46.         } catch (IOException e) {
  47.             e.printStackTrace();
  48.         }
  49.         return null;
  50.     }
  52.     private static boolean isOlderEdition(File file) {
  53.         return file.getName().matches(".+\\.(?i)xls");
  54.     }
  56.     private static boolean isExcel(File file) {
  57.         String fileName = file.getName();
  58.         String regXls = ".+\\.(?i)xls";
  59.         String regXlsx = ".+\\.(?i)xlsx";
  60.         return fileName.matches(regXls) || fileName.matches(regXlsx);
  61.     }
  63.     public static <E> Workbook createExcel(List<E> list, File file) {
  64.         String sheetName = "default";
  65.         if (list.size() == 0) {
  66.             return null;
  67.         }
  68.         Workbook workbook = null;
  69.         try {
  70.             Class clazz = list.get(0).getClass();
  71.             Field[] fields = clazz.getDeclaredFields();
  72.             if (clazz.isAnnotationPresent(Excel.class)) {
  73.                 Excel excel = (Excel) clazz.getAnnotation(Excel.class);
  74.                 sheetName = excel.name();
  75.             }
  76.             workbook = gainWorkbook(file);
  77.             Sheet sheet = workbook.createSheet(sheetName);
  79.             Row line = sheet.createRow(0);
  80.             for (int k = 0; k < fields.length; k++) {
  81.                 Cell cell = line.createCell(k);
  82.                 String columnName = fields[k].getName();
  83.                 if (fields[k].isAnnotationPresent(Excel.class)) {
  84.                     Excel excel = fields[k].getAnnotation(Excel.class);
  85.                     columnName = excel.name();
  86.                 }
  87.                 cell.setCellValue(columnName);
  88.             }
  90.             for (int i = 1; i <= list.size(); i++) {
  91.                 Row row = sheet.createRow(i);
  92.                 for (int j = 1; j <= fields.length; j++) {
  93.                     Cell cell = row.createCell(j - 1);
  94.                     String fieldName = fields[j - 1].getName();
  95.                     String fieldFirstLetterUpper = fieldName.substring(0, 1).toUpperCase();
  96.                     String prefix = "get";
  97.                     if ("boolean".equals(fields[j - 1].getType().getName())) {
  98.                         prefix = "is";
  99.                     }
  100.                     String methodName = prefix + fieldFirstLetterUpper + fieldName.substring(1);
  101.                     Method method = clazz.getMethod(methodName);
  102.                     cell.setCellValue(String.valueOf(method.invoke(list.get(i - 1))));
  103.                 }
  104.             }
  105.             log.debug("List读入完毕");
  106.             OutputStream os = new FileOutputStream(file);
  107.             workbook.write(os);
  108.             os.close();
  109.         } catch (ExcelException e) {
  110.             e.printStackTrace();
  111.         } catch (InvocationTargetException e) {
  112.             e.printStackTrace();
  113.         } catch (NoSuchMethodException e) {
  114.             e.printStackTrace();
  115.         } catch (IllegalAccessException e) {
  116.             e.printStackTrace();
  117.         } catch (FileNotFoundException e) {
  118.             e.printStackTrace();
  119.         } catch (IOException e) {
  120.             e.printStackTrace();
  121.         }
  122.         return workbook;
  123.     }
  124. }