private void writeSheet( List<Map<String, String>> valueMaps, String worksheetName, HSSFWorkbook wb) { // Set column widths HSSFSheet sheet = wb.createSheet(worksheetName); sheet.setColumnWidth(Short.parseShort("0"), Short.parseShort("15000")); sheet.setColumnWidth(Short.parseShort("1"), Short.parseShort("30000")); // header style HSSFCellStyle headerStyle; HSSFFont headerFont = wb.createFont(); headerFont.setFontHeightInPoints((short) 11); headerStyle = wb.createCellStyle(); headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); headerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); headerStyle.setFillForegroundColor(HSSFColor.GREY_50_PERCENT.index); headerStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); headerStyle.setFont(headerFont); headerStyle.setWrapText(true); // header row HSSFRow headerRow = sheet.createRow(0); headerRow.setHeightInPoints(30); HSSFCell headerCell0 = headerRow.createCell((short) 0); HSSFCell headerCell1 = headerRow.createCell((short) 1); headerCell0.setCellStyle(headerStyle); setText(headerCell0, "Layer Name"); headerCell1.setCellStyle(headerStyle); setText(headerCell1, "Message"); int counter = 1; for (Map<String, String> valueMap : valueMaps) { HSSFRow dataRow = sheet.createRow(counter); String layer = valueMap.get("layer"); String status = valueMap.get("status"); status = HtmlUtils.htmlUnescape(status); HSSFCell currentCell0 = dataRow.createCell((short) 0); HSSFCell currentCell1 = dataRow.createCell((short) 1); setText(currentCell0, layer); setText(currentCell1, status); counter++; } }
public void generateExcel(OutputStream out, XLSCallBack<T> xlscaCallBack) throws RuntimeException, IOException { HSSFWorkbook workbook = new HSSFWorkbook(); // create a sheet with specified name HSSFSheet sheet = workbook.createSheet(xlscaCallBack.getSheetName()); for (int i = 0; i < xlscaCallBack.getColumnWidth().length; i++) { sheet.setColumnWidth(i, xlscaCallBack.getColumnWidth()[i] * 256); } // create a title for sheet title sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, xlscaCallBack.getHeaders().length - 1)); HSSFCellStyle titleCellStyle = workbook.createCellStyle(); // create titleCellStyle for cell titleCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFFont titleFont = workbook.createFont(); // set font titleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); titleFont.setFontName("宋体"); titleFont.setFontHeight((short) (240)); titleFont.setColor(HSSFColor.AUTOMATIC.index); titleCellStyle.setFont(titleFont); titleCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); // set border titleCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); titleCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); titleCellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); titleCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // set background titleCellStyle.setFillForegroundColor(HSSFColor.LIGHT_BLUE.index); titleCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); titleCellStyle.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index); HSSFRow title = sheet.createRow(0); title.setHeight((short) 300); for (int i = 0; i < xlscaCallBack.getHeaders().length; i++) { // create tytle for title HSSFCell cell = title.createCell(i); cell.setCellValue(xlscaCallBack.getTitle()); cell.setCellStyle(titleCellStyle); } HSSFCellStyle headerCellStyle = workbook.createCellStyle(); // create headerCellStyle for cell headerCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFFont headerFont = workbook.createFont(); // set font headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); headerFont.setFontName("幼圆"); headerFont.setColor(HSSFColor.AUTOMATIC.index); headerCellStyle.setFont(headerFont); headerCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); // set border headerCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); headerCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); headerCellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); headerCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // set background headerCellStyle.setFillForegroundColor(HSSFColor.LIGHT_BLUE.index); headerCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); headerCellStyle.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index); HSSFRow header = sheet.createRow(1); for (int i = 0; i < xlscaCallBack.getHeaders().length; i++) { // create tytle for header HSSFCell cell = header.createCell(i); cell.setCellValue(xlscaCallBack.getHeaders()[i]); cell.setCellStyle(headerCellStyle); } HSSFCellStyle rowCellStyle = workbook.createCellStyle(); // create headerCellStyle for cell rowCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFFont rowFont = workbook.createFont(); // set font rowFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL); rowFont.setFontName("幼圆"); rowFont.setColor(HSSFColor.AUTOMATIC.index); rowCellStyle.setFont(rowFont); rowCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); // set border rowCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); rowCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); rowCellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); for (int i = 0; i < lists.size(); i++) { HSSFRow row = sheet.createRow(i + 2); String values[] = xlscaCallBack.getValue(lists.get(i)); for (int j = 0; j < values.length; j++) { HSSFCell cell = row.createCell(j); cell.setCellValue(values[j]); cell.setCellStyle(rowCellStyle); } } HSSFCellStyle bottomCellStyle = workbook.createCellStyle(); // create titleCellStyle for cell bottomCellStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT); HSSFFont bottomFont = workbook.createFont(); // set font bottomFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL); bottomFont.setFontName("幼圆"); bottomFont.setColor(HSSFColor.AUTOMATIC.index); bottomCellStyle.setFont(bottomFont); bottomCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); // set border bottomCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); bottomCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); bottomCellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); bottomCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // set background bottomCellStyle.setFillForegroundColor(HSSFColor.LIGHT_BLUE.index); bottomCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); bottomCellStyle.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index); sheet.addMergedRegion( new CellRangeAddress( lists.size() + 2, lists.size() + 2, 0, xlscaCallBack.getHeaders().length - 1)); // creaet bootom for xls HSSFRow bottomRow = sheet.createRow(lists.size() + 2); for (int i = 0; i < xlscaCallBack.getHeaders().length; i++) { // create tytle for title HSSFCell cell = bottomRow.createCell(i); SimpleDateFormat sf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss"); cell.setCellValue("共计导出 " + lists.size() + " 条记录 ,导出日期:" + sf.format(new Date())); cell.setCellStyle(bottomCellStyle); } try { workbook.write(out); } catch (IOException e) { e.printStackTrace(); throw new RuntimeException("create Excel failed due to some unkonw reasion~"); } }