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++;
}
}
private HSSFFont matchFont(Font font) {
HSSFColor hssfColor =
workbook
.getCustomPalette()
.findColor(
(byte) foreground.getRed(),
(byte) foreground.getGreen(),
(byte) foreground.getBlue());
if (hssfColor == null)
hssfColor =
workbook
.getCustomPalette()
.findSimilarColor(
(byte) foreground.getRed(),
(byte) foreground.getGreen(),
(byte) foreground.getBlue());
boolean bold = (font.getStyle() & Font.BOLD) != 0;
boolean italic = (font.getStyle() & Font.ITALIC) != 0;
HSSFFont hssfFont =
workbook.findFont(
bold ? HSSFFont.BOLDWEIGHT_BOLD : 0,
hssfColor.getIndex(),
(short) (font.getSize() * 20),
font.getName(),
italic,
false,
(short) 0,
(byte) 0);
if (hssfFont == null) {
hssfFont = workbook.createFont();
hssfFont.setBoldweight(bold ? HSSFFont.BOLDWEIGHT_BOLD : 0);
hssfFont.setColor(hssfColor.getIndex());
hssfFont.setFontHeight((short) (font.getSize() * 20));
hssfFont.setFontName(font.getName());
hssfFont.setItalic(italic);
hssfFont.setStrikeout(false);
hssfFont.setTypeOffset((short) 0);
hssfFont.setUnderline((byte) 0);
}
return hssfFont;
}
/**
* runs a xls file where the user insert a row within a worksheet where two fields are set: CODE,
* NAME.
*
* @param document: Document to modify the name and code field.
*/
public void postProcessXLS(Object document) {
HSSFWorkbook book = (HSSFWorkbook) document;
HSSFSheet sheet = book.getSheetAt(0); // Se toma hoja del libro
HSSFRow row;
HSSFCellStyle cellStyle = book.createCellStyle();
HSSFFont font = book.createFont();
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
cellStyle.setFont(font);
row = sheet.createRow(0); // Se crea una fila dentro de la hoja
createCell(cellStyle, row, 0, "CODIGO"); // "100">#{rowX.column1}</p:column>
createCell(
cellStyle, row, 1,
"NOMBRE"); // "100">#{rowX.column23}</p:column>
countriesList = countriesFacade.findAll();
for (int i = 0; i < countriesList.size(); i++) {
row = sheet.createRow(i + 1);
createCell(row, 0, countriesList.get(i).getIdCountry().toString()); // CODIGO
createCell(row, 1, countriesList.get(i).getName()); // NOMBRE
}
}
public void testDoesNoHarmIfNothingToDo() {
HSSFWorkbook wb = new HSSFWorkbook();
// New files start with 4 built in fonts, and 21 built in styles
assertEquals(4, wb.getNumberOfFonts());
assertEquals(21, wb.getNumCellStyles());
// Create a test font and style, and use them
HSSFFont f = wb.createFont();
f.setFontName("Testing");
HSSFCellStyle s = wb.createCellStyle();
s.setFont(f);
HSSFSheet sheet = wb.createSheet();
HSSFRow row = sheet.createRow(0);
row.createCell(0).setCellStyle(s);
// Should have one more than the default of each
assertEquals(5, wb.getNumberOfFonts());
assertEquals(22, wb.getNumCellStyles());
// Optimise fonts
HSSFOptimiser.optimiseFonts(wb);
assertEquals(5, wb.getNumberOfFonts());
assertEquals(22, wb.getNumCellStyles());
assertEquals(f, s.getFont(wb));
// Optimise styles
HSSFOptimiser.optimiseCellStyles(wb);
assertEquals(5, wb.getNumberOfFonts());
assertEquals(22, wb.getNumCellStyles());
assertEquals(f, s.getFont(wb));
}
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~");
}
}
public void testOptimiseFonts() {
HSSFWorkbook wb = new HSSFWorkbook();
// Add 6 fonts, some duplicates
HSSFFont f1 = wb.createFont();
f1.setFontHeight((short) 11);
f1.setFontName("Testing");
HSSFFont f2 = wb.createFont();
f2.setFontHeight((short) 22);
f2.setFontName("Also Testing");
HSSFFont f3 = wb.createFont();
f3.setFontHeight((short) 33);
f3.setFontName("Unique");
HSSFFont f4 = wb.createFont();
f4.setFontHeight((short) 11);
f4.setFontName("Testing");
HSSFFont f5 = wb.createFont();
f5.setFontHeight((short) 22);
f5.setFontName("Also Testing");
HSSFFont f6 = wb.createFont();
f6.setFontHeight((short) 66);
f6.setFontName("Also Unique");
// Use all three of the four in cell styles
assertEquals(21, wb.getNumCellStyles());
HSSFCellStyle cs1 = wb.createCellStyle();
cs1.setFont(f1);
assertEquals(5, cs1.getFontIndex());
HSSFCellStyle cs2 = wb.createCellStyle();
cs2.setFont(f4);
assertEquals(8, cs2.getFontIndex());
HSSFCellStyle cs3 = wb.createCellStyle();
cs3.setFont(f5);
assertEquals(9, cs3.getFontIndex());
HSSFCellStyle cs4 = wb.createCellStyle();
cs4.setFont(f6);
assertEquals(10, cs4.getFontIndex());
assertEquals(25, wb.getNumCellStyles());
// And three in rich text
HSSFSheet s = wb.createSheet();
HSSFRow r = s.createRow(0);
HSSFRichTextString rtr1 = new HSSFRichTextString("Test");
rtr1.applyFont(0, 2, f1);
rtr1.applyFont(3, 4, f2);
r.createCell(0).setCellValue(rtr1);
HSSFRichTextString rtr2 = new HSSFRichTextString("AlsoTest");
rtr2.applyFont(0, 2, f3);
rtr2.applyFont(3, 5, f5);
rtr2.applyFont(6, 8, f6);
r.createCell(1).setCellValue(rtr2);
// Check what we have now
assertEquals(10, wb.getNumberOfFonts());
assertEquals(25, wb.getNumCellStyles());
// Optimise
HSSFOptimiser.optimiseFonts(wb);
// Check font count
assertEquals(8, wb.getNumberOfFonts());
assertEquals(25, wb.getNumCellStyles());
// Check font use in cell styles
assertEquals(5, cs1.getFontIndex());
assertEquals(5, cs2.getFontIndex()); // duplicate of 1
assertEquals(6, cs3.getFontIndex()); // duplicate of 2
assertEquals(8, cs4.getFontIndex()); // two have gone
// And in rich text
// RTR 1 had f1 and f2, unchanged
assertEquals(5, r.getCell(0).getRichStringCellValue().getFontAtIndex(0));
assertEquals(5, r.getCell(0).getRichStringCellValue().getFontAtIndex(1));
assertEquals(6, r.getCell(0).getRichStringCellValue().getFontAtIndex(3));
assertEquals(6, r.getCell(0).getRichStringCellValue().getFontAtIndex(4));
// RTR 2 had f3 (unchanged), f5 (=f2) and f6 (moved down)
assertEquals(7, r.getCell(1).getRichStringCellValue().getFontAtIndex(0));
assertEquals(7, r.getCell(1).getRichStringCellValue().getFontAtIndex(1));
assertEquals(6, r.getCell(1).getRichStringCellValue().getFontAtIndex(3));
assertEquals(6, r.getCell(1).getRichStringCellValue().getFontAtIndex(4));
assertEquals(8, r.getCell(1).getRichStringCellValue().getFontAtIndex(6));
assertEquals(8, r.getCell(1).getRichStringCellValue().getFontAtIndex(7));
}
public void testOptimiseStyles() {
HSSFWorkbook wb = new HSSFWorkbook();
// Two fonts
assertEquals(4, wb.getNumberOfFonts());
HSSFFont f1 = wb.createFont();
f1.setFontHeight((short) 11);
f1.setFontName("Testing");
HSSFFont f2 = wb.createFont();
f2.setFontHeight((short) 22);
f2.setFontName("Also Testing");
assertEquals(6, wb.getNumberOfFonts());
// Several styles
assertEquals(21, wb.getNumCellStyles());
HSSFCellStyle cs1 = wb.createCellStyle();
cs1.setFont(f1);
HSSFCellStyle cs2 = wb.createCellStyle();
cs2.setFont(f2);
HSSFCellStyle cs3 = wb.createCellStyle();
cs3.setFont(f1);
HSSFCellStyle cs4 = wb.createCellStyle();
cs4.setFont(f1);
cs4.setAlignment((short) 22);
HSSFCellStyle cs5 = wb.createCellStyle();
cs5.setFont(f2);
cs5.setAlignment((short) 111);
HSSFCellStyle cs6 = wb.createCellStyle();
cs6.setFont(f2);
assertEquals(27, wb.getNumCellStyles());
// Use them
HSSFSheet s = wb.createSheet();
HSSFRow r = s.createRow(0);
r.createCell(0).setCellStyle(cs1);
r.createCell(1).setCellStyle(cs2);
r.createCell(2).setCellStyle(cs3);
r.createCell(3).setCellStyle(cs4);
r.createCell(4).setCellStyle(cs5);
r.createCell(5).setCellStyle(cs6);
r.createCell(6).setCellStyle(cs1);
r.createCell(7).setCellStyle(cs2);
assertEquals(21, r.getCell(0).getCellValueRecord().getXFIndex());
assertEquals(26, r.getCell(5).getCellValueRecord().getXFIndex());
assertEquals(21, r.getCell(6).getCellValueRecord().getXFIndex());
// Optimise
HSSFOptimiser.optimiseCellStyles(wb);
// Check
assertEquals(6, wb.getNumberOfFonts());
assertEquals(25, wb.getNumCellStyles());
// cs1 -> 21
assertEquals(21, r.getCell(0).getCellValueRecord().getXFIndex());
// cs2 -> 22
assertEquals(22, r.getCell(1).getCellValueRecord().getXFIndex());
assertEquals(22, r.getCell(1).getCellStyle().getFont(wb).getFontHeight());
// cs3 = cs1 -> 21
assertEquals(21, r.getCell(2).getCellValueRecord().getXFIndex());
// cs4 --> 24 -> 23
assertEquals(23, r.getCell(3).getCellValueRecord().getXFIndex());
// cs5 --> 25 -> 24
assertEquals(24, r.getCell(4).getCellValueRecord().getXFIndex());
// cs6 = cs2 -> 22
assertEquals(22, r.getCell(5).getCellValueRecord().getXFIndex());
// cs1 -> 21
assertEquals(21, r.getCell(6).getCellValueRecord().getXFIndex());
// cs2 -> 22
assertEquals(22, r.getCell(7).getCellValueRecord().getXFIndex());
// Add a new duplicate, and two that aren't used
HSSFCellStyle csD = wb.createCellStyle();
csD.setFont(f1);
r.createCell(8).setCellStyle(csD);
HSSFFont f3 = wb.createFont();
f3.setFontHeight((short) 23);
f3.setFontName("Testing 3");
HSSFFont f4 = wb.createFont();
f4.setFontHeight((short) 24);
f4.setFontName("Testing 4");
HSSFCellStyle csU1 = wb.createCellStyle();
csU1.setFont(f3);
HSSFCellStyle csU2 = wb.createCellStyle();
csU2.setFont(f4);
// Check before the optimise
assertEquals(8, wb.getNumberOfFonts());
assertEquals(28, wb.getNumCellStyles());
// Optimise, should remove the two un-used ones and the one duplicate
HSSFOptimiser.optimiseCellStyles(wb);
// Check
assertEquals(8, wb.getNumberOfFonts());
assertEquals(25, wb.getNumCellStyles());
// csD -> cs1 -> 21
assertEquals(21, r.getCell(8).getCellValueRecord().getXFIndex());
}
