參考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;
}