Java POI 對Excel操作（備忘）

參考API ： POI : HSSFSheet API

@SuppressWarnings("unchecked")
private InputStream getExcel(List<P070301ReportVo> excelList) throws Exception {
_logger.debug("開始進行Excel檔案產製作業"); HSSFWorkbook wb = new HSSFWorkbook();// 建立Excel物件 // 設定分頁名稱 String sheetName = "XXXX清冊"; HSSFSheet sheet = wb.createSheet(sheetName); // sheet.autoSizeColumn(0);// 自動調整欄位寬度 sheet.getWorkbook().createCellStyle(); // 設定每一欄的寬度 sheet.setColumnWidth(0, 17 * 256); sheet.setColumnWidth(1, 24 * 256); sheet.setColumnWidth(2, 32 * 256); sheet.setColumnWidth(3, 55 * 256); sheet.setColumnWidth(4, 13 * 256); sheet.setColumnWidth(5, 17 * 256); sheet.setColumnWidth(6, 24 * 256); /** * 開始設定Excel文件格式 */ // 字體格式 HSSFFont font = wb.createFont(); font.setColor(HSSFColor.BLACK.index); // 顏色 font.setBoldweight(Font.BOLDWEIGHT_BOLD); // 粗體字 font.setFontName("標楷體"); font.setFontHeightInPoints((short) 16); HSSFFont font1 = wb.createFont(); font1.setColor(HSSFColor.BLACK.index); // 顏色 font1.setBoldweight(Font.BOLDWEIGHT_BOLD); // 粗體字 font1.setFontName("標楷體"); font1.setFontHeightInPoints((short) 12); HSSFFont font2 = wb.createFont(); font2.setFontName("標楷體"); font2.setFontHeightInPoints((short) 12); // 設定儲存格底色RGB HSSFPalette palette = wb.getCustomPalette(); HSSFColor hssfColor = palette.findColor((byte) 204, (byte) 204, (byte) 255); /** * 設定大標格式 */ HSSFCellStyle styleRow = wb.createCellStyle(); styleRow.setFont(font); styleRow.setAlignment(XSSFCellStyle.ALIGN_CENTER); // 水平置中 styleRow.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); // 垂直置中 /** * 設定標題列格式 */ HSSFCellStyle styleRow1 = wb.createCellStyle(); styleRow1.setFillForegroundColor(hssfColor.getIndex());// 填滿顏色 styleRow1.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); styleRow1.setFont(font1); // 設定字體 styleRow1.setAlignment(XSSFCellStyle.ALIGN_CENTER); // 水平置中 styleRow1.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); // 垂直置中 /** * 設定內容列格式 */ HSSFCellStyle styleRow2 = wb.createCellStyle(); styleRow2.setFont(font2); styleRow1.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); // 垂直置中 HSSFCellStyle styleRow3 = wb.createCellStyle(); styleRow3.setFont(font2); styleRow3.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); // 垂直置中 // 設定框線(1:細線；2：中線） styleRow1.setBorderBottom((short) 1); styleRow1.setBorderTop((short) 2); styleRow1.setBorderLeft((short) 1); styleRow1.setBorderRight((short) 1); styleRow2.setBorderBottom((short) 1); styleRow2.setBorderTop((short) 1); styleRow2.setBorderLeft((short) 1); styleRow2.setBorderRight((short) 1); styleRow3.setBorderBottom((short) 2); styleRow3.setBorderTop((short) 1); styleRow3.setBorderLeft((short) 1); styleRow3.setBorderRight((short) 1); /** * 開始寫入資料 */ HSSFRow title = sheet.createRow(0); title.createCell(0).setCellValue("XXXX通知"); title.setHeight((short) ((short) 30 * 20)); title.getCell(0).setCellStyle(styleRow); // 合併儲存格 CellRangeAddress region = new CellRangeAddress(0, 0, 0, 6); sheet.addMergedRegion(region); // 寫入第一列資料標示 HSSFRow titlerow = sheet.createRow(1); String[] titledate = { "申XX號", "證XXXX號)", "專XX人", "專XX稱", "繳XX次", "繳XX限", "補XX限" }; titlerow.setHeight((short) ((short) 26 * 20)); HSSFCell cell = null; for (int i = 0; i < titledate.length; i++) { cell = titlerow.createCell(i); cell.setCellStyle(styleRow1); cell.setCellValue(new HSSFRichTextString(titledate[i])); } // 寫入資料 for (int c = 0, r = 2; c < excelList.size(); c++, r++) { HSSFRow row = sheet.createRow(r); row.createCell(0).setCellValue(excelList.get(c).getApplNo()); row.createCell(1).setCellValue(excelList.get(c).getPatentNo()); row.createCell(2).setCellValue(excelList.get(c).getNameC()); row.createCell(3).setCellValue(excelList.get(c).getPatentNameC()); row.createCell(4).setCellValue(excelList.get(c).getChargeExpirYear()); row.createCell(5).setCellValue(excelList.get(c).getChargeExpirDate()); row.createCell(6).setCellValue(excelList.get(c).getC_2().replace("/", "")); // 設定每行資料高 row.setHeight((short) ((short) 16.5 * 20)); // 最後一列資料底線為中線 if (c == excelList.size() - 1) { row.getCell(0).setCellStyle(styleRow3); row.getCell(1).setCellStyle(styleRow3); row.getCell(2).setCellStyle(styleRow3); row.getCell(3).setCellStyle(styleRow3); row.getCell(4).setCellStyle(styleRow3); row.getCell(5).setCellStyle(styleRow3); row.getCell(6).setCellStyle(styleRow3); } else { row.getCell(0).setCellStyle(styleRow2); row.getCell(1).setCellStyle(styleRow2); row.getCell(2).setCellStyle(styleRow2); row.getCell(3).setCellStyle(styleRow2); row.getCell(4).setCellStyle(styleRow2); row.getCell(5).setCellStyle(styleRow2); row.getCell(6).setCellStyle(styleRow2); } } /** * 設定輸出 */ ByteArrayOutputStream bos = new ByteArrayOutputStream(); wb.write(bos); InputStream ExcelFileToReadD = new ByteArrayInputStream(bos.toByteArray()); bos.close(); return ExcelFileToReadD;
}