第8章 POI報表的進階應用
掌握基于模闆列印的POI報表導出
了解自定義工具類的執行流程
熟練使用SXSSFWorkbook完成百萬資料報表列印
了解基于事件驅動的POI報表導入
1模闆列印
1.1概述
自定義生成Excel報表檔案還是有很多不盡如意的地方，特别是針對複雜報表頭，單元格樣式，字型等操作。手寫 這些代碼不僅費時費力，有時候效果還不太理想。那怎麼樣才能更友善的對報表樣式，報表頭進行處理呢？答案是 使用已經準備好的Excel模闆，隻需要關注模闆中的資料即可。
1.2模闆列印的操作步驟
1.制作模版檔案（模版檔案的路徑）
2.導入（加載）模版檔案，進而得到一個工作簿
3.讀取工作表
4.讀取行
5.讀取單元格
6.讀取單元格樣式
7.設定單元格内容
8.其他單元格就可以使用讀到的樣式了
1.3代碼實作
@RequestMapping(value = "/export/{month}", method = RequestMethod.GET)
public void export(@PathVariable(name = "month") String month) throws Exception {
//1.構造資料List<EmployeeReportResult> list =
userCompanyPersonalService.findByReport(companyId,month+"%");
//2.加載模闆流資料
Resource resource = new ClassPathResource("excel-template/hr-demo.xlsx"); FileInputStream fis = new FileInputStream(resource.getFile());
//3.根據檔案流，加載指定的工作簿
XSSFWorkbook wb = new XSSFWorkbook(fis);
//4.讀取工作表
Sheet sheet = wb.getSheetAt(0);
//5.抽取公共的樣式
Row styleRow = sheet.getRow(2);
CellStyle [] styles = new CellStyle[styleRow.getLastCellNum()]; for(int i=0;i<styleRow.getLastCellNum();i++) {
styles[i] = styleRow.getCell(i).getCellStyle();
}
//6.構造每行和單元格資料
AtomicInteger datasAi = new AtomicInteger(2);
Cell cell = null;
for (EmployeeReportResult report : list) {
Row dataRow = sheet.createRow(datasAi.getAndIncrement());
//編号
cell = dataRow.createCell(0); cell.setCellValue(report.getUserId());
cell.setCellStyle(styles[0]);
//姓名
cell = dataRow.createCell(1); cell.setCellValue(report.getUsername());
cell.setCellStyle(styles[1]);
//手機
cell = dataRow.createCell(2); cell.setCellValue(report.getMobile());
cell.setCellStyle(styles[2]);
//最高學曆
cell = dataRow.createCell(3); cell.setCellValue(report.getTheHighestDegreeOfEducation());
cell.setCellStyle(styles[3]);
//國家地區
cell = dataRow.createCell(4); cell.setCellValue(report.getNationalArea());
cell.setCellStyle(styles[4]);
//護照号
cell = dataRow.createCell(5); cell.setCellValue(report.getPassportNo());
cell.setCellStyle(styles[5]);
//籍貫
cell = dataRow.createCell(6); cell.setCellValue(report.getNativePlace());
cell.setCellStyle(styles[6]);
//生日
cell = dataRow.createCell(7); cell.setCellValue(report.getBirthday());
cell.setCellStyle(styles[7]);
//屬相
cell = dataRow.createCell(8); cell.setCellValue(report.getZodiac());
cell.setCellStyle(styles[8]);
//入職時間
cell = dataRow.createCell(9); cell.setCellValue(report.getTimeOfEntry());
cell.setCellStyle(styles[9]);
//離職類型
cell = dataRow.createCell(10);
cell.setCellValue(report.getTypeOfTurnover()); cell.setCellStyle(styles[10]);
//離職原因
cell = dataRow.createCell(11); cell.setCellValue(report.getReasonsForLeaving());
cell.setCellStyle(styles[11]);
//離職時間
cell = dataRow.createCell(12); cell.setCellStyle(styles[12]); cell.setCellValue(report.getResignationTime());
}
String fileName = URLEncoder.encode(month+"人員資訊.xlsx", "UTF-8"); response.setContentType("application/octet-stream"); response.setHeader("content-disposition", "attachment;filename=" + new
String(fileName.getBytes("ISO8859-1"))); response.setHeader("filename", fileName); wb.write(response.getOutputStream());
}
2自定義工具類
2.1自定義注解
（1）自定義注解
@Retention(RetentionPolicy.RUNTIME) @Target(ElementType.FIELD)
public @interface ExcelAttribute {
/** 對應的列名稱 */
String name() default "";
/** 列 序 号 */ int sort();
/** 字 段 類 型 對 應 的 格 式 */ String format() default "";
}
（2）導出工具類
@Getter @Setter
public class ExcelExportUtil<T> {
private int rowIndex; private int styleIndex; private String templatePath; private Class clazz;
private Field fields[];
public ExcelExportUtil(Class clazz,int rowIndex,int styleIndex) { this.clazz = clazz;
this.rowIndex = rowIndex; this.styleIndex = styleIndex; fields = clazz.getDeclaredFields();
}
/**
* 基于注解導出
*/
public void export(HttpServletResponse response,InputStream is, List<T> objs,String fileName) throws Exception {
XSSFWorkbook workbook = new XSSFWorkbook(is); Sheet sheet = workbook.getSheetAt(0);
CellStyle[] styles = getTemplateStyles(sheet.getRow(styleIndex));
AtomicInteger datasAi = new AtomicInteger(rowIndex); for (T t : objs) {
Row row = sheet.createRow(datasAi.getAndIncrement()); for(int i=0;i<styles.length;i++) {
Cell cell = row.createCell(i); cell.setCellStyle(styles[i]); for (Field field : fields) {
if(field.isAnnotationPresent(ExcelAttribute.class)){ field.setAccessible(true);
ExcelAttribute ea = field.getAnnotation(ExcelAttribute.class); if(i == ea.sort()) {
cell.setCellValue(field.get(t).toString());
}
}
}
}
}
fileName = URLEncoder.encode(fileName, "UTF-8"); response.setContentType("application/octet-stream"); response.setHeader("content-disposition", "attachment;filename=" + new
String(fileName.getBytes("ISO8859-1"))); response.setHeader("filename", fileName); workbook.write(response.getOutputStream());
}
public CellStyle[] getTemplateStyles(Row row) {
CellStyle [] styles = new CellStyle[row.getLastCellNum()]; for(int i=0;i<row.getLastCellNum();i++) {
styles[i] = row.getCell(i).getCellStyle();
}
return styles;
}
}
（3）導入工具類
public class ExcelImportUtil<T> {
private Class clazz; private Field fields[];
public ExcelImportUtil(Class clazz) { this.clazz = clazz;
fields = clazz.getDeclaredFields();
}
/**
* 基于注解讀取excel
*/
public List<T> readExcel(InputStream is, int rowIndex,int cellIndex) { List<T> list = new ArrayList<T>();
T entity = null; try {
XSSFWorkbook workbook = new XSSFWorkbook(is);
Sheet sheet = workbook.getSheetAt(0);
// 不準确
int rowLength = sheet.getLastRowNum();
System.out.println(sheet.getLastRowNum());
for (int rowNum = rowIndex; rowNum <= sheet.getLastRowNum(); rowNum++) { Row row = sheet.getRow(rowNum);
entity = (T) clazz.newInstance(); System.out.println(row.getLastCellNum());
for (int j = cellIndex; j < row.getLastCellNum(); j++) { Cell cell = row.getCell(j);
for (Field field : fields) { if(field.isAnnotationPresent(ExcelAttribute.class)){
field.setAccessible(true); ExcelAttribute ea =
field.getAnnotation(ExcelAttribute.class);
if(j == ea.sort()) {
field.set(entity, covertAttrType(field, cell));
}
}
}
}
list.add(entity);
}
} catch (Exception e) { e.printStackTrace();
}
return list;
}
/**
* 類型轉換 将cell 單元格格式轉為 字段類型
*/
private Object covertAttrType(Field field, Cell cell) throws Exception { String fieldType = field.getType().getSimpleName();
if ("String".equals(fieldType)) { return getValue(cell);
}else if ("Date".equals(fieldType)) {
return new SimpleDateFormat("yyyy-MM-dd hh:mm:ss").parse(getValue(cell)) ;
}else if ("int".equals(fieldType) || "Integer".equals(fieldType)) { return Integer.parseInt(getValue(cell));
}else if ("double".equals(fieldType) || "Double".equals(fieldType)) { return Double.parseDouble(getValue(cell));
}else {
return null;
}
}
/**
*格式轉為String
*@param cell
*@return
*/
public String getValue(Cell cell) { if (cell == null) {
return "";
}
switch (cell.getCellType()) { case STRING:
return cell.getRichStringCellValue().getString().trim(); case NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
Date dt = DateUtil.getJavaDate(cell.getNumericCellValue()); return new SimpleDateFormat("yyyy-MM-dd hh:mm:ss").format(dt);
} else {
// 防止數值變成科學計數法
String strCell = "";
Double num = cell.getNumericCellValue(); BigDecimal bd = new BigDecimal(num.toString()); if (bd != null) {
strCell = bd.toPlainString();
}
// 去除 浮點型 自動加的 .0
if (strCell.endsWith(".0")) {
strCell = strCell.substring(0, strCell.indexOf("."));
}
return strCell;
}
case BOOLEAN:
return String.valueOf(cell.getBooleanCellValue()); default:
return "";
}
}
}
2.2工具類完成導入導出
（1）導入資料
List<User> list = new ExcelImportUtil(User.class).readExcel(is, 1, 2);
（2）導出資料
@RequestMapping(value = "/export/{month}", method = RequestMethod.GET)
public void export(@PathVariable(name = "month") String month) throws Exception {
//1.構造資料List<EmployeeReportResult> list =
userCompanyPersonalService.findByReport(companyId,month+"%");
//2.加載模闆流資料
Resource resource = new ClassPathResource("excel-template/hr-demo.xlsx"); FileInputStream fis = new FileInputStream(resource.getFile());
new ExcelExportUtil(EmployeeReportResult.class,2,2). export(response,fis,list,"人事報表.xlsx");
}
3百萬資料報表概述
3.1概述
我們都知道Excel可以分為早期的Excel2003版本（使用POI的HSSF對象操作）和Excel2007版本（使用POI的XSSF 操作），兩者對百萬資料的支援如下：
Excel 2003：在POI中使用HSSF對象時，excel 2003最多隻允許存儲65536條資料，一般用來處理較少的資料量。這時對于百萬級别資料，Excel肯定容納不了。
Excel 2007：當POI更新到XSSF對象時，它可以直接支援excel2007以上版本，因為它采用ooxml格式。這時excel可以支援1048576條資料，單個sheet表就支援近百萬條資料。但實際運作時還可能存在問題，原因是執 行POI報表所産生的行對象，單元格對象，字型對象，他們都不會銷毀，這就導緻OOM的風險。
3.2JDK性能監控工具介紹
沒有性能監控工具一切推論都隻能停留在理論階段，我們可以使用Java的性能監控工具來監視程式的運作情況，包括CUP,垃圾回收，記憶體的配置設定和使用情況，這讓程式的運作階段變得更加可控，也可以用來證明我們的推測。這裡 我們使用JDK提供的性能工具Jvisualvm來監控程式運作。
3.2.1Jvisualvm概述
VisualVM 是Netbeans的profile子項目，已在JDK6.0 update 7 中自帶，能夠監控線程，記憶體情況，檢視方法的
CPU時間和記憶體中的對 象，已被GC的對象，反向檢視配置設定的堆棧
3.2.2Jvisualvm的位置
Jvisualvm位于JAVA_HOME/bin目錄下，直接輕按兩下就可以打開該程式。如果隻是監控本地的java程序，是不需要配 置參數的，直接打開就能夠進行監控。首先我們需要在本地打開一個Java程式，例如我打開員工微服務程序，這時在jvisualvm界面就可以看到與IDEA相關的Java程序了：
3.2.3Jvisualvm的使用
Jvisualvm使用起來比較簡單，輕按兩下點選目前運作的程序即可進入到程式的監控界面
概述：可以看到程序的啟動參數。
監視：左上：cpu使用率，gc狀态的監控，右上：堆使用率，永久記憶體區的使用率，左下：類的監控，右下： 線程的監控
線程：能夠顯示線程的名稱和運作的狀态，在調試多線程時必不可少，而且可以點進一個線程檢視這個線程 的詳細運作情況
3.3解決方案分析
對于百萬資料量的Excel導入導出，隻讨論基于Excel2007的解決方法。在ApachePoi 官方提供了對操作大資料量的導入導出的工具和解決辦法，操作Excel2007使用XSSF對象，可以分為三種模式：
使用者模式：使用者模式有許多封裝好的方法操作簡單，但建立太多的對象，非常耗記憶體（之前使用的方法）
事件模式：基于SAX方式解析XML，SAX全稱Simple API for XML，它是一個接口，也是一個軟體包。它是一種XML解析的替代方法，不同于DOM解析XML文檔時把所有内容一次性加載到記憶體中的方式，它逐行掃描文檔，一邊掃描，一邊解析。
SXSSF對象：是用來生成海量excel資料檔案，主要原理是借助臨時存儲空間生成excel
這是一張Apache POI官方提供的圖檔，描述了基于使用者模式，事件模式，以及使用SXSSF三種方式操作Excel的特性以及CUP和記憶體占用情況。
4百萬資料報表導出
4.1需求分析
使用Apache POI完成百萬資料量的Excel報表導出
4.2解決方案
4.2.1思路分析
基于XSSFWork導出Excel報表，是通過将所有單元格對象儲存到記憶體中，當所有的Excel單元格全部建立完成之後 一次性寫入到Excel并導出。當百萬資料級别的Excel導出時，随着表格的不斷建立，記憶體中對象越來越多，直至内 存溢出。Apache Poi提供了SXSSFWork對象，專門用于處理大資料量Excel報表導出。
4.2.2原理分析
在執行個體化SXSSFWork這個對象時，可以指定在記憶體中所産生的POI導出相關對象的數量（預設100），一旦記憶體中 的對象的個數達到這個指定值時，就将記憶體中的這些對象的内容寫入到磁盤中（XML的檔案格式），就可以将這些 對象從記憶體中銷毀，以後隻要達到這個值，就會以類似的處理方式處理，直至Excel導出完成。
4.3代碼實作
在原有代碼的基礎上替換之前的XSSFWorkbook，使用SXSSFWorkbook完成建立過程即可
//1.構造資料
List<EmployeeReportResult> list =
userCompanyPersonalService.findByReport(companyId,month+"%");
//2.建立工作簿
SXSSFWorkbook workbook = new SXSSFWorkbook();
//3.構造sheet
String[] titles = {"編号", "姓名", "手機","最高學曆", "國家地區", "護照号", "籍貫",
"生日", "屬相","入職時間","離職類型","離職原因","離職時間"};
Sheet sheet = workbook.createSheet();
Row row = sheet.createRow(0);
AtomicInteger headersAi = new AtomicInteger();
for (String title : titles) {
Cell cell = row.createCell(headersAi.getAndIncrement()); cell.setCellValue(title);
}
AtomicInteger datasAi = new AtomicInteger(1);
Cell cell = null;
for(int i=0;i<10000;i++) {
for (EmployeeReportResult report : list) {
Row dataRow = sheet.createRow(datasAi.getAndIncrement());
//編号
cell = dataRow.createCell(0);
cell.setCellValue(report.getUserId());
//姓名
cell = dataRow.createCell(1);
cell.setCellValue(report.getUsername());
//手機
cell = dataRow.createCell(2);
cell.setCellValue(report.getMobile());
//最高學曆
cell = dataRow.createCell(3);
cell.setCellValue(report.getTheHighestDegreeOfEducation());
//國家地區
cell = dataRow.createCell(4);
cell.setCellValue(report.getNationalArea());
//護照号
cell = dataRow.createCell(5);
cell.setCellValue(report.getPassportNo());
//籍貫
cell = dataRow.createCell(6);
cell.setCellValue(report.getNativePlace());
//生日
cell = dataRow.createCell(7);
cell.setCellValue(report.getBirthday());
//屬相
cell = dataRow.createCell(8);
cell.setCellValue(report.getZodiac());
//入職時間
cell = dataRow.createCell(9);
cell.setCellValue(report.getTimeOfEntry());
//離職類型
cell = dataRow.createCell(10);
cell.setCellValue(report.getTypeOfTurnover());
//離職原因
cell = dataRow.createCell(11);
cell.setCellValue(report.getReasonsForLeaving());
//離職時間
cell = dataRow.createCell(12); cell.setCellValue(report.getResignationTime());
}
}
String fileName = URLEncoder.encode(month+"人員資訊.xlsx", "UTF-8"); response.setContentType("application/octet-stream"); response.setHeader("content-disposition", "attachment;filename=" + new
String(fileName.getBytes("ISO8859-1"))); response.setHeader("filename", fileName); workbook.write(response.getOutputStream());
4.4對比測試
（1）XSSFWorkbook生成百萬資料報表
使用XSSFWorkbook生成Excel報表，時間較長，随着時間推移，記憶體占用原來越多，直至記憶體溢出
（2）SXSSFWorkbook生成百萬資料報表
使用SXSSFWorkbook生成Excel報表，記憶體占用比較平緩
5百萬資料報表讀取
5.1需求分析
使用POI基于事件模式解析案例提供的Excel檔案
5.2解決方案
5.2.1思路分析
使用者模式：加載并讀取Excel時，是通過一次性的将所有資料加載到記憶體中再去解析每個單元格内容。當Excel 資料量較大時，由于不同的運作環境可能會造成記憶體不足甚至OOM異常。
事件模式：它逐行掃描文檔，一邊掃描一邊解析。由于應用程式隻是在讀取資料時檢查資料，是以不需要将 資料存儲在記憶體中，這對于大型文檔的解析是個巨大優勢。
5.2.2步驟分析
（1）設定POI的事件模式根據Excel擷取檔案流
根據檔案流建立OPCPackage
建立XSSFReader對象
（2）Sax解析
自定義Sheet處理器
建立Sax的XmlReader對象設定Sheet的事件處理器逐行讀取
5.2.3原理分析
我們都知道對于Excel2007的實質是一種特殊的XML存儲資料，那就可以使用基于SAX的方式解析XML完成Excel的讀取。SAX提供了一種從XML文檔中讀取資料的機制。它逐行掃描文檔，一邊掃描一邊解析。由于應用程式隻是在讀取資料時檢查資料，是以不需要将資料存儲在記憶體中，這對于大型文檔的解析是個巨大優勢
5.3代碼實作
5.3.1自定義處理器
//自定義Sheet基于Sax的解析處理器
public class SheetHandler implements XSSFSheetXMLHandler.SheetContentsHandler {
//封裝實體對象
private PoiEntity entity;
/**
* 解析行開始
*/ @Override
public void startRow(int rowNum) { if (rowNum >0 ) {
entity = new PoiEntity();
}
}
/**
* 解析每一個單元格
*/ @Override
public void cell(String cellReference, String formattedValue, XSSFComment comment)
{
if(entity != null) {
switch (cellReference.substring(0, 1)) { case "A":
entity.setId(formattedValue); break;
case "B":
entity.setBreast(formattedValue); break;
case "C":
entity.setAdipocytes(formattedValue); break;
case "D":
entity.setNegative(formattedValue); break;
case "E":
entity.setStaining(formattedValue); break;
case "F":
entity.setSupportive(formattedValue); break;
default:
break;
}
}
}
/**
* 解析行結束
*/
public void endRow(int rowNum) { System.out.println(entity);
}
//處理頭尾
public void headerFooter(String text, boolean isHeader, String tagName) {
}
}
5.3.2自定義解析
/**
* 自定義Excel解析器
*/
public class ExcelParser {
public void parse (String path) throws Exception {
//1.根據Excel擷取OPCPackage對象
OPCPackage pkg = OPCPackage.open(path, PackageAccess.READ); try {
//2.建立XSSFReader對象
XSSFReader reader = new XSSFReader(pkg);
//3.擷取SharedStringsTable對象
SharedStringsTable sst = reader.getSharedStringsTable();
//4.擷取StylesTable對象
StylesTable styles = reader.getStylesTable();
//5.建立Sax的XmlReader對象
XMLReader parser = XMLReaderFactory.createXMLReader();
//6.設定處理器
parser.setContentHandler(new XSSFSheetXMLHandler(styles,sst, new SheetHandler(), false));
XSSFReader.SheetIterator sheets = (XSSFReader.SheetIterator)
reader.getSheetsData();
//7.逐行讀取
while (sheets.hasNext()) {
InputStream sheetstream = sheets.next();
InputSource sheetSource = new InputSource(sheetstream); try {
parser.parse(sheetSource);
} finally {
sheetstream.close();
}
}
} finally {
pkg.close();
}
}
}
5.3.3對比測試
使用者模式下讀取測試Excel檔案直接記憶體溢出，測試Excel檔案映射到記憶體中還是占用了不少記憶體；事件模式下可以 流暢的運作。
（1）使用使用者模型解析
（2）使用事件模型解析
5.4總結
通過簡單的分析以及運作兩種模式進行比較，可以看到使用者模式下使用更簡單的代碼實作了Excel讀取，但是在讀取大檔案時CPU和記憶體都不理想；而事件模式雖然代碼寫起來比較繁瑣，但是在讀取大檔案時CPU和記憶體更加占 優。