簡介:
POI是Apache軟件基金會用Java編寫的免費開源的跨平臺的 Java API,Apache POI提供API給Java程序對Microsoft Office格式檔案讀和寫的功能。POI為“Poor Obfuscation Implementation”的首字母縮寫,意為“簡潔版的模糊實現”。所以POI的主要功能是可以用Java操作Microsoft Office的相關文件,但是一般我們都是用來操作Excel相關文件。
需要依賴:
<code> org.apache.poi poi 3.17 org.apache.poi poi-ooxml 3.17 /<code>
導出效果如下,實現在網頁彈出選擇保存路徑,可以合併單元格,設置字體信息,單元格行寬、列高。
<code>private void exportExcel(String parentTitle, String sheetName, String[] headers, String[] fields, Collection dataset, OutputStream out, String pattern, String date, int[] columnWidth) { // 聲明一個工作薄 HSSFWorkbook workbook = new HSSFWorkbook(); // 生成一個表格 HSSFSheet sheet = workbook.createSheet(sheetName); // 設置表格默認列寬度為15個字節 // sheet.setDefaultColumnWidth((short) 18); // 設置表格列寬,列寬計算公式為(int)((columnWidth+0.72)*256) for (int i = 0; i < columnWidth.length; i++) { sheet.setColumnWidth(i, (int) ((columnWidth[i] + 0.72) * 256)); } // 聲明一個畫圖的頂級管理器 HSSFPatriarch patriarch = sheet.createDrawingPatriarch(); // 定義註釋的大小和位置,詳見文檔 HSSFComment comment = patriarch.createComment(new HSSFClientAnchor(0, 0, 0, 0, (short) 4, 2, (short) 6, 5)); // 設置註釋內容 comment.setString(new HSSFRichTextString(parentTitle)); // 設置註釋作者,當鼠標移動到單元格上是可以在狀態欄中看到該內容. comment.setAuthor("billz"); // 主標題 HSSFRow pTitleRow = sheet.createRow(0); HSSFCell pTitleCell = pTitleRow.createCell(0); pTitleCell.setCellStyle(getParentTitleStyle(workbook)); pTitleCell.setCellValue(new HSSFRichTextString(parentTitle)); // 跨第1行第1個到第2個單元格的操作為 CellRangeAddress rg = new CellRangeAddress(0, (short) 0, 0, (short) (headers.length - 1)); sheet.addMergedRegion(rg); pTitleRow.setHeightInPoints(35);// 目的是想把行高設置成35px // end // 顯示時間行,第二行 HSSFRow dateRow = sheet.createRow(1); sheet.addMergedRegion(new CellRangeAddress(1, 1, headers.length - 2, headers.length - 1)); dateRow.setHeight((short) 400); HSSFCell dateCell = dateRow.createCell(headers.length - 2); dateCell.setCellStyle(getDateStyle(workbook)); dateCell.setCellValue(new HSSFRichTextString(date)); // 產生表格標題行 HSSFRow row = sheet.createRow(2); row.setHeight((short) 0x279); for (int i = 0; i < headers.length; i++) { HSSFCell cell = row.createCell(i); cell.setCellStyle(getTitleStyle(workbook)); HSSFRichTextString text = new HSSFRichTextString(headers[i]); cell.setCellValue(text); } // 遍歷集合數據,產生數據行 Iterator it = dataset.iterator(); int index = 2; while (it.hasNext()) { index++; row = sheet.createRow(index); row.setHeight((short) 512); T t = it.next(); // 利用反射,根據javabean屬性的先後順序,動態調用getXxx()方法得到屬性值 for (int i = 0; i < fields.length; i++) { HSSFCell cell = row.createCell(i); cell.setCellStyle(getValueStyle(workbook)); String fieldName = fields[i]; String getMethodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1); try { Method getMethod = t.getClass().getMethod(getMethodName, new Class[] {}); Object value = getMethod.invoke(t, new Object[] {}); // 判斷值的類型後進行強制類型轉換 String textValue = null; if (value instanceof Boolean) { boolean bValue = (Boolean) value; textValue = "是"; if (!bValue) { textValue = "否"; } } else if (value instanceof Date) { Date date1 = (Date) value; SimpleDateFormat sdf = new SimpleDateFormat(pattern); textValue = sdf.format(date1); } else if (value instanceof byte[]) { // 有圖片時,設置行高為60px; row.setHeightInPoints(60); // 設置圖片所在列寬度為80px,注意這裡單位的一個換算 sheet.setColumnWidth(i, (short) (35.7 * 80)); // sheet.autoSizeColumn(i); byte[] bsValue = (byte[]) value; HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 1023, 255, (short) 6, index, (short) 6, index); anchor.setAnchorType(2); patriarch.createPicture(anchor, workbook.addPicture( bsValue, HSSFWorkbook.PICTURE_TYPE_JPEG)); } else if (value instanceof Long) { // long類型的時間字符串轉為固定格式(yy/MM/dd // hh:mm)String字符串 String dateStr = String.valueOf(value); String year = dateStr.substring(0, 4) + "/"; String month = dateStr.substring(4, 6) + "/"; String day = dateStr.substring(6, 8) + " "; String hour = dateStr.substring(8, 10) + ":"; String minute = dateStr.substring(10, 12); textValue = year + month + day + hour + minute; } else { // 其它數據類型都當作字符串簡單處理 textValue = value != null ? value.toString() : ""; } // 如果不是圖片數據,就利用正則表達式判斷textValue是否全部由數字組成 if (textValue != null) { Pattern p = Pattern.compile("^//d+(//.//d+)?$"); Matcher matcher = p.matcher(textValue); if (matcher.matches()) { // 是數字當作double處理 cell.setCellValue(Double.parseDouble(textValue)); } else { HSSFRichTextString richString = new HSSFRichTextString( textValue); HSSFFont font3 = workbook.createFont(); // font3.setColor(HSSFColor.BLUE.index); richString.applyFont(font3); cell.setCellValue(richString); } } } catch (Exception e) { e.printStackTrace(); } finally { // 清理資源 } } } try { workbook.write(out); } catch (IOException e) { e.printStackTrace(); } }/<code>