private void createSummerySheet() {
sheet0 = workbook.createSheet("Summary");
PrintSetup printSetup = sheet0.getPrintSetup();
printSetup.setLandscape(true);
sheet0.setFitToPage(true);
sheet0.setHorizontallyCenter(true);
// title row
Row titleRow = sheet0.createRow(0);
titleRow.setHeightInPoints(45);
Cell titleCell = titleRow.createCell(0);
titleCell.setCellValue("File Health Report");
titleCell.setCellStyle(styles.get("title"));
sheet0.addMergedRegion(CellRangeAddress.valueOf("$A$1:$L$1"));
for (int i = 0; i < titles.length; i++) {
Row _row = sheet0.createRow(i + 1);
Cell headerCell = _row.createCell(0);
headerCell.setCellValue(titles[i]);
headerCell.setCellStyle(styles.get("header"));
_row.setHeightInPoints(20);
}
// finally set column widths, the width is measured in units of 1/256th
// of a character width
sheet0.setColumnWidth(0, 50 * 256); // 30 characters wide
}
@Test
public void rotatedText() throws Exception {
Workbook workbook = _testDataProvider.createWorkbook();
fixFonts(workbook);
Sheet sheet = workbook.createSheet();
Row row = sheet.createRow(0);
CellStyle style1 = workbook.createCellStyle();
style1.setRotation((short) 90);
Cell cell0 = row.createCell(0);
cell0.setCellValue("Apache Software Foundation");
cell0.setCellStyle(style1);
Cell cell1 = row.createCell(1);
cell1.setCellValue("Apache Software Foundation");
for (int i = 0; i < 2; i++) sheet.autoSizeColumn(i);
int w0 = sheet.getColumnWidth(0);
int w1 = sheet.getColumnWidth(1);
assertTrue(
w0 * 5 < w1); // rotated text occupies at least five times less horizontal space than normal
// text
workbook.close();
}
@Test
public void numericCells() throws Exception {
Workbook workbook = _testDataProvider.createWorkbook();
fixFonts(workbook);
DataFormat df = workbook.getCreationHelper().createDataFormat();
Sheet sheet = workbook.createSheet();
Row row = sheet.createRow(0);
row.createCell(0)
.setCellValue(0); // getCachedFormulaResult() returns 0 for not evaluated formula cells
row.createCell(1).setCellValue(10);
row.createCell(2).setCellValue("10");
row.createCell(3).setCellFormula("(A1+B1)*1.0"); // a formula that returns '10'
Cell cell4 = row.createCell(4); // numeric cell with a custom style
CellStyle style4 = workbook.createCellStyle();
style4.setDataFormat(df.getFormat("0.0000"));
cell4.setCellStyle(style4);
cell4.setCellValue(10); // formatted as '10.0000'
row.createCell(5).setCellValue("10.0000");
// autosize not-evaluated cells, formula cells are sized as if the result is 0
for (int i = 0; i < 6; i++) sheet.autoSizeColumn(i);
assertTrue(
sheet.getColumnWidth(0)
< sheet.getColumnWidth(1)); // width of '0' is less then width of '10'
assertEquals(
sheet.getColumnWidth(1), sheet.getColumnWidth(2)); // 10 and '10' should be sized equally
assertEquals(
sheet.getColumnWidth(3),
sheet.getColumnWidth(0)); // formula result is unknown, the width is calculated for '0'
assertEquals(sheet.getColumnWidth(4), sheet.getColumnWidth(5)); // 10.0000 and '10.0000'
// evaluate formulas and re-autosize
evaluateWorkbook(workbook);
for (int i = 0; i < 6; i++) sheet.autoSizeColumn(i);
assertTrue(
sheet.getColumnWidth(0)
< sheet.getColumnWidth(1)); // width of '0' is less then width of '10'
assertEquals(
sheet.getColumnWidth(1),
sheet.getColumnWidth(2)); // columns 1, 2 and 3 should have the same width
assertEquals(
sheet.getColumnWidth(2),
sheet.getColumnWidth(3)); // columns 1, 2 and 3 should have the same width
assertEquals(sheet.getColumnWidth(4), sheet.getColumnWidth(5)); // 10.0000 and '10.0000'
workbook.close();
}
@Override
public void writeRow(List<?> row) {
Row record = sheet.createRow(sheet.getLastRowNum() + 1);
for (int i = 0; i < row.size(); i++) {
Cell cell = record.createCell(i);
Object value = row.get(i);
if (value == null) {
cell.setCellValue("");
} else if (value instanceof String) {
cell.setCellType(Cell.CELL_TYPE_STRING);
cell.setCellValue((String) value);
} else if (value instanceof Number) {
cell.setCellType(Cell.CELL_TYPE_NUMERIC);
cell.setCellValue(converters.convert(value, Double.class));
} else if (value instanceof Date || value instanceof DateTime || value instanceof Calendar) {
cell.setCellType(Cell.CELL_TYPE_NUMERIC);
cell.setCellStyle(dateCellStyle);
cell.setCellValue(converters.convert(value, Date.class));
} else if (value instanceof Boolean) {
cell.setCellType(Cell.CELL_TYPE_BOOLEAN);
cell.setCellValue((Boolean) value);
} else {
cell.setCellType(Cell.CELL_TYPE_STRING);
cell.setCellValue(converters.convert(value, String.class));
}
}
}
public void format() {
wb = new XSSFWorkbook();
Map styles = createStyles(wb);
Sheet sheet = wb.createSheet("Loan Calculator");
sheet.setPrintGridlines(false);
sheet.setDisplayGridlines(false);
PrintSetup printSetup = sheet.getPrintSetup();
printSetup.setLandscape(true);
sheet.setFitToPage(true);
sheet.setHorizontallyCenter(true);
sheet.setColumnWidth(0, 768);
sheet.setColumnWidth(1, 768);
sheet.setColumnWidth(2, 2816);
sheet.setColumnWidth(3, 3584);
sheet.setColumnWidth(4, 3584);
sheet.setColumnWidth(5, 3584);
sheet.setColumnWidth(6, 3584);
Row titleRow = sheet.createRow(0);
titleRow.setHeightInPoints(35F);
for (int i = 1; i <= 7; i++)
titleRow.createCell(i).setCellStyle((CellStyle) styles.get("title"));
Cell titleCell = titleRow.getCell(2);
titleCell.setCellValue("Simple");
}
/** When shifting rows, the page breaks should go with it */
public void testShiftRowBreaks() { // TODO - enable XSSF test
Workbook b = _testDataProvider.createWorkbook();
Sheet s = b.createSheet();
Row row = s.createRow(4);
row.createCell(0).setCellValue("test");
s.setRowBreak(4);
s.shiftRows(4, 4, 2);
assertTrue("Row number 6 should have a pagebreak", s.isRowBroken(6));
}
@Override
public void writeHeader() {
if (header != null) {
Row record = sheet.createRow(0);
for (int i = 0; i < header.size(); i++) {
Cell cell = record.createCell(i);
cell.setCellValue(header.get(i));
}
}
}
/**
* @see
* net.sourceforge.squirrel_sql.fw.gui.action.exportData.AbstractDataExportFileWriter#addHeaderCell(int,
* java.lang.String)
*/
@Override
protected void addHeaderCell(int colIdx, String columnName) throws Exception {
this.withHeader = true;
Row headerRow = sheet.getRow(0);
if (headerRow == null) {
headerRow = sheet.createRow(0);
}
Cell cell = headerRow.createCell(colIdx);
cell.setCellValue(columnName);
}
@Test
public void stringCells() throws Exception {
Workbook workbook = _testDataProvider.createWorkbook();
fixFonts(workbook);
Sheet sheet = workbook.createSheet();
Row row = sheet.createRow(0);
Font defaultFont = workbook.getFontAt((short) 0);
CellStyle style1 = workbook.createCellStyle();
Font font1 = workbook.createFont();
font1.setFontHeight((short) (2 * defaultFont.getFontHeight()));
style1.setFont(font1);
row.createCell(0).setCellValue("x");
row.createCell(1).setCellValue("xxxx");
row.createCell(2).setCellValue("xxxxxxxxxxxx");
row.createCell(3)
.setCellValue("Apache\nSoftware Foundation"); // the text is splitted into two lines
row.createCell(4).setCellValue("Software Foundation");
Cell cell5 = row.createCell(5);
cell5.setCellValue("Software Foundation");
cell5.setCellStyle(
style1); // same as in column 4 but the font is twice larger than the default font
for (int i = 0; i < 10; i++) sheet.autoSizeColumn(i);
assertTrue(
2 * sheet.getColumnWidth(0)
< sheet.getColumnWidth(1)); // width is roughly proportional to the number of characters
assertTrue(2 * sheet.getColumnWidth(1) < sheet.getColumnWidth(2));
assertEquals(sheet.getColumnWidth(4), sheet.getColumnWidth(3));
boolean ignoreFontSizeX2 = JvmBugs.hasLineBreakMeasurerBug();
assertTrue(
ignoreFontSizeX2
|| sheet.getColumnWidth(5)
> sheet.getColumnWidth(4)); // larger font results in a wider column width
workbook.close();
}
@Test
public void booleanCells() throws Exception {
Workbook workbook = _testDataProvider.createWorkbook();
fixFonts(workbook);
Sheet sheet = workbook.createSheet();
Row row = sheet.createRow(0);
row.createCell(0)
.setCellValue(0); // getCachedFormulaResult() returns 0 for not evaluated formula cells
row.createCell(1).setCellValue(true);
row.createCell(2).setCellValue("TRUE");
row.createCell(3).setCellFormula("1 > 0"); // a formula that returns true
// autosize not-evaluated cells, formula cells are sized as if the result is 0
for (int i = 0; i < 4; i++) sheet.autoSizeColumn(i);
assertTrue(sheet.getColumnWidth(1) > sheet.getColumnWidth(0)); // 'true' is wider than '0'
assertEquals(
sheet.getColumnWidth(1), sheet.getColumnWidth(2)); // 10 and '10' should be sized equally
assertEquals(
sheet.getColumnWidth(3),
sheet.getColumnWidth(0)); // formula result is unknown, the width is calculated for '0'
// evaluate formulas and re-autosize
evaluateWorkbook(workbook);
for (int i = 0; i < 4; i++) sheet.autoSizeColumn(i);
assertTrue(sheet.getColumnWidth(1) > sheet.getColumnWidth(0)); // 'true' is wider than '0'
assertEquals(
sheet.getColumnWidth(1),
sheet.getColumnWidth(2)); // columns 1, 2 and 3 should have the same width
assertEquals(
sheet.getColumnWidth(2),
sheet.getColumnWidth(3)); // columns 1, 2 and 3 should have the same width
workbook.close();
}
public final void testShiftWithMergedRegions() {
Workbook wb = _testDataProvider.createWorkbook();
Sheet sheet = wb.createSheet();
Row row = sheet.createRow(0);
row.createCell(0).setCellValue(1.1);
row.createCell(1).setCellValue(2.2);
CellRangeAddress region = new CellRangeAddress(0, 0, 0, 2);
assertEquals("A1:C1", region.formatAsString());
sheet.addMergedRegion(region);
sheet.shiftRows(0, 1, 2);
region = sheet.getMergedRegion(0);
assertEquals("A3:C3", region.formatAsString());
}
public final void testAutoCreateOtherCells() {
Workbook workbook = _testDataProvider.createWorkbook();
Sheet sheet = workbook.createSheet("Sheet1");
Row row1 = sheet.createRow(0);
Cell cellA1 = row1.createCell(0);
Cell cellB1 = row1.createCell(1);
String formula = "42";
sheet.setArrayFormula(formula, CellRangeAddress.valueOf("A1:B2"));
assertEquals(formula, cellA1.getCellFormula());
assertEquals(formula, cellB1.getCellFormula());
Row row2 = sheet.getRow(1);
assertNotNull(row2);
assertEquals(formula, row2.getCell(0).getCellFormula());
assertEquals(formula, row2.getCell(1).getCellFormula());
}
public boolean CreateExcel(String path) {
try {
wb = new HSSFWorkbook(); // 创建新的Excel工作簿
sheet = wb.createSheet("民政婚姻登记");
Row row = sheet.createRow(3);
Cell cell = row.createCell(0);
cell.setCellType(Cell.CELL_TYPE_STRING);
cell.setCellValue(1);
// OutputStreamReader in = new OutputStreamReader(new FileOutputStream(path),"UTF-8");
fileOut = new FileOutputStream(path);
wb.write(fileOut);
fileOut.close();
} catch (IOException e) {
System.out.println("CreateExcel() ERRO\n");
return false;
}
return true;
}
public void testModifyArrayCells_removeCell() {
Workbook workbook = _testDataProvider.createWorkbook();
Sheet sheet = workbook.createSheet();
// single-cell array formulas behave just like normal cells
CellRangeAddress cra = CellRangeAddress.valueOf("B5");
CellRange<? extends Cell> srange = sheet.setArrayFormula("SUM(A4:A6,B4:B6)", cra);
Cell scell = srange.getTopLeftCell();
Row srow = sheet.getRow(cra.getFirstRow());
assertSame(srow, scell.getRow());
srow.removeCell(scell);
assertNull(srow.getCell(cra.getFirstColumn()));
// re-create the removed cell
scell = srow.createCell(cra.getFirstColumn());
assertEquals(Cell.CELL_TYPE_BLANK, scell.getCellType());
assertFalse(scell.isPartOfArrayFormulaGroup());
// we cannot remove cells included in a multi-cell array formula
CellRange<? extends Cell> mrange =
sheet.setArrayFormula("A1:A3*B1:B3", CellRangeAddress.valueOf("C1:C3"));
for (Cell mcell : mrange) {
int columnIndex = mcell.getColumnIndex();
Row mrow = mcell.getRow();
try {
mrow.removeCell(mcell);
fail("expected exception");
} catch (IllegalStateException e) {
CellReference ref = new CellReference(mcell);
String msg =
"Cell "
+ ref.formatAsString()
+ " is part of a multi-cell array formula. You cannot change part of an array.";
assertEquals(msg, e.getMessage());
}
// a failed invocation of Row.removeCell leaves the row
// in the state that it was in prior to the invocation
assertSame(mcell, mrow.getCell(columnIndex));
assertTrue(mcell.isPartOfArrayFormulaGroup());
assertEquals(Cell.CELL_TYPE_FORMULA, mcell.getCellType());
}
}
/**
* @param filePath : 写入指定的路径
* @param args ： 待写入的内容
* @throws IOException
* @throws InvalidFormatException ： 抛出格式错误异常
*/
public void WriteExcel(String filePath, String[] args)
throws IOException, InvalidFormatException {
try {
inp = new FileInputStream(filePath);
wb = WorkbookFactory.create(inp);
sheet = wb.getSheetAt(0);
Row row = sheet.createRow(0);
Cell cell;
for (int i = 0; i < 20; i++) {
cell = row.createCell(i);
cell.setCellType(Cell.CELL_TYPE_STRING);
cell.setCellValue(args[i]);
}
OutputStream out = new FileOutputStream(filePath);
// fileOut = new FileOutputStream(filePath);
wb.write(out);
} catch (InvalidFormatException e) {
System.out.println("WriteExcel() ERRO in tag\n");
}
}
public final void testShiftWithNames() {
Workbook wb = _testDataProvider.createWorkbook();
Sheet sheet1 = wb.createSheet("Sheet1");
wb.createSheet("Sheet2");
Row row = sheet1.createRow(0);
row.createCell(0).setCellValue(1.1);
row.createCell(1).setCellValue(2.2);
Name name1 = wb.createName();
name1.setNameName("name1");
name1.setRefersToFormula("Sheet1!$A$1+Sheet1!$B$1");
Name name2 = wb.createName();
name2.setNameName("name2");
name2.setRefersToFormula("Sheet1!$A$1");
// refers to A1 but on Sheet2. Should stay unaffected.
Name name3 = wb.createName();
name3.setNameName("name3");
name3.setRefersToFormula("Sheet2!$A$1");
// The scope of this one is Sheet2. Should stay unaffected.
Name name4 = wb.createName();
name4.setNameName("name4");
name4.setRefersToFormula("A1");
name4.setSheetIndex(1);
sheet1.shiftRows(0, 1, 2); // shift down the top row on Sheet1.
name1 = wb.getNameAt(0);
assertEquals("Sheet1!$A$3+Sheet1!$B$3", name1.getRefersToFormula());
name2 = wb.getNameAt(1);
assertEquals("Sheet1!$A$3", name2.getRefersToFormula());
// name3 and name4 refer to Sheet2 and should not be affected
name3 = wb.getNameAt(2);
assertEquals("Sheet2!$A$1", name3.getRefersToFormula());
name4 = wb.getNameAt(3);
assertEquals("A1", name4.getRefersToFormula());
}
@Test
public void mergedCells() throws Exception {
Workbook workbook = _testDataProvider.createWorkbook();
fixFonts(workbook);
Sheet sheet = workbook.createSheet();
Row row = sheet.createRow(0);
sheet.addMergedRegion(CellRangeAddress.valueOf("A1:B1"));
Cell cell0 = row.createCell(0);
cell0.setCellValue("Apache Software Foundation");
int defaulWidth = sheet.getColumnWidth(0);
sheet.autoSizeColumn(0);
// column is unchanged if merged regions are ignored (Excel like behavior)
assertEquals(defaulWidth, sheet.getColumnWidth(0));
sheet.autoSizeColumn(0, true);
assertTrue(sheet.getColumnWidth(0) > defaulWidth);
workbook.close();
}
/**
* Auto-Sizing a column needs to work when we have rows passed the 32767 boundary. See bug #48079
*/
@Test
public void largeRowNumbers() throws Exception {
Workbook workbook = _testDataProvider.createWorkbook();
fixFonts(workbook);
Sheet sheet = workbook.createSheet();
Row r0 = sheet.createRow(0);
r0.createCell(0).setCellValue("I am ROW 0");
Row r200 = sheet.createRow(200);
r200.createCell(0).setCellValue("I am ROW 200");
// This should work fine
sheet.autoSizeColumn(0);
// Get close to 32767
Row r32765 = sheet.createRow(32765);
r32765.createCell(0).setCellValue("Nearly there...");
sheet.autoSizeColumn(0);
// To it
Row r32767 = sheet.createRow(32767);
r32767.createCell(0).setCellValue("At the boundary");
sheet.autoSizeColumn(0);
// And passed it
Row r32768 = sheet.createRow(32768);
r32768.createCell(0).setCellValue("Passed");
Row r32769 = sheet.createRow(32769);
r32769.createCell(0).setCellValue("More Passed");
sheet.autoSizeColumn(0);
// Long way passed
Row r60708 = sheet.createRow(60708);
r60708.createCell(0).setCellValue("Near the end");
sheet.autoSizeColumn(0);
workbook.close();
}
public static void main(String[] args) throws Exception {
Workbook wb;
if (args.length > 0 && args[0].equals("-xls")) wb = new HSSFWorkbook();
else wb = new XSSFWorkbook();
Map<String, CellStyle> styles = createStyles(wb);
Sheet sheet = wb.createSheet("Timesheet");
PrintSetup printSetup = sheet.getPrintSetup();
printSetup.setLandscape(true);
sheet.setFitToPage(true);
sheet.setHorizontallyCenter(true);
// title row
Row titleRow = sheet.createRow(0);
titleRow.setHeightInPoints(45);
Cell titleCell = titleRow.createCell(0);
titleCell.setCellValue("Weekly Timesheet");
titleCell.setCellStyle(styles.get("title"));
sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$L$1"));
// header row
Row headerRow = sheet.createRow(1);
headerRow.setHeightInPoints(40);
Cell headerCell;
for (int i = 0; i < titles.length; i++) {
headerCell = headerRow.createCell(i);
headerCell.setCellValue(titles[i]);
headerCell.setCellStyle(styles.get("header"));
}
int rownum = 2;
for (int i = 0; i < 10; i++) {
Row row = sheet.createRow(rownum++);
for (int j = 0; j < titles.length; j++) {
Cell cell = row.createCell(j);
if (j == 9) {
// the 10th cell contains sum over week days, e.g. SUM(C3:I3)
String ref = "C" + rownum + ":I" + rownum;
cell.setCellFormula("SUM(" + ref + ")");
cell.setCellStyle(styles.get("formula"));
} else if (j == 11) {
cell.setCellFormula("J" + rownum + "-K" + rownum);
cell.setCellStyle(styles.get("formula"));
} else {
cell.setCellStyle(styles.get("cell"));
}
}
}
// row with totals below
Row sumRow = sheet.createRow(rownum++);
sumRow.setHeightInPoints(35);
Cell cell;
cell = sumRow.createCell(0);
cell.setCellStyle(styles.get("formula"));
cell = sumRow.createCell(1);
cell.setCellValue("Total Hrs:");
cell.setCellStyle(styles.get("formula"));
for (int j = 2; j < 12; j++) {
cell = sumRow.createCell(j);
String ref = (char) ('A' + j) + "3:" + (char) ('A' + j) + "12";
cell.setCellFormula("SUM(" + ref + ")");
if (j >= 9) cell.setCellStyle(styles.get("formula_2"));
else cell.setCellStyle(styles.get("formula"));
}
rownum++;
sumRow = sheet.createRow(rownum++);
sumRow.setHeightInPoints(25);
cell = sumRow.createCell(0);
cell.setCellValue("Total Regular Hours");
cell.setCellStyle(styles.get("formula"));
cell = sumRow.createCell(1);
cell.setCellFormula("L13");
cell.setCellStyle(styles.get("formula_2"));
sumRow = sheet.createRow(rownum++);
sumRow.setHeightInPoints(25);
cell = sumRow.createCell(0);
cell.setCellValue("Total Overtime Hours");
cell.setCellStyle(styles.get("formula"));
cell = sumRow.createCell(1);
cell.setCellFormula("K13");
cell.setCellStyle(styles.get("formula_2"));
// set sample data
for (int i = 0; i < sample_data.length; i++) {
Row row = sheet.getRow(2 + i);
for (int j = 0; j < sample_data[i].length; j++) {
if (sample_data[i][j] == null) continue;
if (sample_data[i][j] instanceof String) {
row.getCell(j).setCellValue((String) sample_data[i][j]);
} else {
row.getCell(j).setCellValue((Double) sample_data[i][j]);
}
}
}
// finally set column widths, the width is measured in units of 1/256th of a character width
sheet.setColumnWidth(0, 30 * 256); // 30 characters wide
for (int i = 2; i < 9; i++) {
sheet.setColumnWidth(i, 6 * 256); // 6 characters wide
}
sheet.setColumnWidth(10, 10 * 256); // 10 characters wide
// Write the output to a file
String file = "timesheet.xls";
if (wb instanceof XSSFWorkbook) file += "x";
FileOutputStream out = new FileOutputStream(file);
wb.write(out);
out.close();
}
private void saveExcelPoject(File file) throws IOException {
Workbook wb = new XSSFWorkbook();
Sheet sheet = wb.createSheet("timeplan");
// Заголовок в 0 строке
Row row = sheet.createRow(0);
Cell cell = row.createCell(0);
cell.setCellValue("Филиал");
cell = row.createCell(1);
cell.setCellValue("Город");
Calendar cal = Calendar.getInstance();
cal.set(2017, 0, 5); // Начальная дата проекта
SimpleDateFormat sdf = new SimpleDateFormat("dd.MM.yy");
for (int i = 0; i < 3 * 52; i++) { // Счетчик по неделям
cell = row.createCell(i + 2);
cell.setCellValue(sdf.format(cal.getTime()));
cal.add(Calendar.WEEK_OF_YEAR, 1); // Следующая неделя
}
// sheet.setColumnWidth(0, 256);
// Цвета ячеек
CellStyle[] styles = new CellStyle[6];
styles[0] = wb.createCellStyle();
styles[0].setFillForegroundColor(HSSFColor.RED.index);
styles[0].setFillPattern(FillPatternType.SOLID_FOREGROUND);
styles[1] = wb.createCellStyle();
styles[1].setFillForegroundColor(HSSFColor.GREEN.index);
styles[1].setFillPattern(FillPatternType.SOLID_FOREGROUND);
styles[2] = wb.createCellStyle();
styles[2].setFillForegroundColor(HSSFColor.BLUE.index);
styles[2].setFillPattern(FillPatternType.SOLID_FOREGROUND);
styles[3] = wb.createCellStyle();
styles[3].setFillForegroundColor(HSSFColor.ROSE.index);
styles[3].setFillPattern(FillPatternType.SOLID_FOREGROUND);
styles[4] = wb.createCellStyle();
styles[4].setFillForegroundColor(HSSFColor.LIGHT_BLUE.index);
styles[4].setFillPattern(FillPatternType.SOLID_FOREGROUND);
styles[5] = wb.createCellStyle();
styles[5].setFillForegroundColor(HSSFColor.LIGHT_GREEN.index);
styles[5].setFillPattern(FillPatternType.SOLID_FOREGROUND);
short rowIdx = 0;
for (Region region : this.regions) {
row = sheet.createRow(++rowIdx);
cell = row.createCell(0);
cell.setCellValue(region.filial);
cell = row.createCell(1);
cell.setCellValue(region.name);
cal = Calendar.getInstance();
cal.set(2017, 0, 5); // Начальная дата проекта
for (int i = 0; i < 3 * 52; i++) { // Счетчик по неделям
short color = region.getDateColorIndex(cal.getTime());
if (color >= 0) {
cell = row.createCell(i + 2);
cell.setCellStyle(styles[color]);
}
cal.add(Calendar.WEEK_OF_YEAR, 1); // Следующая неделя
}
}
try (FileOutputStream fileOut = new FileOutputStream(file)) {
wb.write(fileOut);
}
}
private Cell getXlsCell(ColumnDisplayDefinition colDef, int colIdx, int curRow, Object cellObj) {
Row row = sheet.getRow(curRow);
if (row == null) {
row = sheet.createRow(curRow);
}
Cell retVal = row.createCell(colIdx);
if (null == cellObj || null == colDef) {
retVal.setCellValue(getDataXLSAsString(cellObj));
return retVal;
}
int colType = colDef.getSqlType();
switch (colType) {
case Types.BIT:
case Types.BOOLEAN:
if (null == cellObj) {
// retVal.setCellValue((Boolean)null);
} else {
retVal.setCellValue((Boolean) cellObj);
}
break;
case Types.INTEGER:
if (null == cellObj) {
// retVal.setCellValue((Integer)null);
} else {
retVal.setCellValue(((Number) cellObj).intValue());
}
break;
case Types.SMALLINT:
case Types.TINYINT:
if (null == cellObj) {
// retVal.setCellValue(((Short) null));
} else {
retVal.setCellValue(((Number) cellObj).shortValue());
}
break;
case Types.NUMERIC:
case Types.DECIMAL:
case Types.FLOAT:
case Types.DOUBLE:
case Types.REAL:
if (null == cellObj) {
// retVal.setCellValue((Double) null);
} else {
retVal.setCellValue(((Number) cellObj).doubleValue());
}
break;
case Types.BIGINT:
if (null == cellObj) {
// retVal.setCellValue((Long)null);
} else {
retVal.setCellValue(Long.parseLong(cellObj.toString()));
}
break;
case Types.DATE:
makeTemporalCell(retVal, (Date) cellObj, "m/d/yy");
break;
case Types.TIMESTAMP:
makeTemporalCell(retVal, (Date) cellObj, "m/d/yy h:mm");
break;
case Types.TIME:
makeTemporalCell(retVal, (Date) cellObj, "h:mm");
break;
case Types.CHAR:
case Types.VARCHAR:
case Types.LONGVARCHAR:
cellObj = CellComponentFactory.renderObject(cellObj, colDef);
retVal.setCellValue(getDataXLSAsString(cellObj));
break;
default:
cellObj = CellComponentFactory.renderObject(cellObj, colDef);
retVal.setCellValue(getDataXLSAsString(cellObj));
}
return retVal;
}
@Test
public void test_poi() {
final int rowNum = 27;
final int colNum = 15;
HSSFWorkbook wb = null;
Sheet sheet = null;
String today = "2013/8/31";
String sign = "Month to date";
String[] titles = {
"",
"",
"",
"Chinapay eMail\r\n 商城总计",
"Japan Page\r\n 日本馆首页",
"Taiwan Page\r\n 台湾馆首页",
"USA Page\r\n 美国馆首页",
"Anhui Page\r\n 安徽馆首页",
"China Page\r\n 中国馆首页"
};
String[] colNames = {
"",
"Page View (PV)\r\n 浏览量",
"Unique Visitor (UV)\r\n 独立访客",
"Completed Orders\r\n 确认订单",
"Transaction Amount\r\n 交易金额",
"1st Top Seller\r\n 最佳销量",
"Unit Price 单价",
"Qty Sold 销量",
"2nd Top Seller\r\n 第二销量",
"Unit Price 单价",
"Qty Sold 销量",
"3rd Top Seller\r\n 第三销量",
"Unit Price 单价",
"Qty Sold 销量",
"1st Top Seller\r\n 最佳销量",
"Unit Price 单价",
"Qty Sold 销量",
"2nd Top Seller\r\n 第二销量",
"Unit Price 单价",
"Qty Sold 销量",
"3rd Top Seller\r\n 第三销量",
"Unit Price 单价",
"Qty Sold 销量"
};
int n = 0;
int len = 1;
String fileName = "D:/日报.xls";
File f = new File(fileName);
ByteArrayOutputStream byteArrayOut = null;
BufferedImage bufferImg = null;
String[] jpgUrls = {
"http://img.chinapay.com/data/files/store_37452/goods_93/small_201303271804531386.jpg",
"http://img.chinapay.com/data/files/store_44066/goods_37/201308280953576580.jpg",
"http://img.chinapay.com/data/files/store_289253/goods_95/small_201309031434558044.jpg",
"http://img.chinapay.com/data/files/store_289253/goods_180/small_201309031403003861.jpg",
"http://img.chinapay.com/data/files/store_37452/goods_98/small_201309121508186810.jpg",
"http://img.chinapay.com/data/files/store_37452/goods_24/small_201301241133447193.jpg"
};
String[] https = {
"http://emall.chinapay.com/goods/37452/1010000109792.html",
"http://emall.chinapay.com/goods/44066/1010000119323.html",
"http://emall.chinapay.com/goods/289253/1010000119621.html?jpsv=laoxcashback6",
"http://emall.chinapay.com/goods/289253/1010000119627.html?jpsv=laoxcashback6",
"http://emall.chinapay.com/goods/37452/1010000120588.html",
"http://emall.chinapay.com/goods/37452/1010000107096.html"
};
URL url = null;
HSSFHyperlink link = null;
HSSFPatriarch patri = null;
HSSFClientAnchor anchor = null;
try {
if (!f.exists()) {
wb = new HSSFWorkbook();
} else {
FileInputStream in = new FileInputStream(fileName);
wb = new HSSFWorkbook(in);
}
CellStyle style = wb.createCellStyle();
style.setFillForegroundColor(HSSFCellStyle.THIN_BACKWARD_DIAG);
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 垂直居中
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// style.setLeftBorderColor(HSSFColor.RED.index);
style.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框
style.setBorderLeft(HSSFCellStyle.BORDER_THIN); // 左边框
style.setBorderTop(HSSFCellStyle.BORDER_THIN); // 上边框
style.setBorderRight(HSSFCellStyle.BORDER_THIN); // 右边框
style.setWrapText(true);
sheet = wb.createSheet("sheet " + ((int) (100000 * Math.random())));
// 设置列的宽度
sheet.setDefaultColumnWidth(20);
sheet.setDefaultRowHeight((short) 400);
Row row = null;
Cell cell = null;
for (int r = 0; r < rowNum; r++) {
row = sheet.createRow(r);
// 设置第1行当高度
if (r == 0) {
row.setHeightInPoints(30);
}
// 设置第2列以后的宽度(即列号>=2的列，列号从0开始)
if (r >= 2) {
sheet.setColumnWidth(r, 3020);
}
for (int c = 0; c < colNum; c++) {
cell = row.createCell(c);
cell.setCellStyle(style);
// 处理第一行
if (r == 0) {
sheet.addMergedRegion(new CellRangeAddress(r, r, 3, 4));
sheet.addMergedRegion(new CellRangeAddress(r, r, 5, 6));
sheet.addMergedRegion(new CellRangeAddress(r, r, 7, 8));
sheet.addMergedRegion(new CellRangeAddress(r, r, 9, 10));
sheet.addMergedRegion(new CellRangeAddress(r, r, 11, 12));
sheet.addMergedRegion(new CellRangeAddress(r, r, 13, 14));
if (c < 3) {
cell.setCellValue(titles[n++]);
} else {
if ((c & 1) == 1) {
System.out.println("c===" + c);
cell.setCellValue(titles[n++]);
}
}
}
// 处理第2~8行
if (r > 0 && r <= 8) {
if (c == 0) {
if (r < 8 && (r & 1) == 1) {
sheet.addMergedRegion(new CellRangeAddress(r, r + 1, 0, 0));
System.err.println("row----->" + r + " len----->" + (len));
cell.setCellValue(colNames[len++]);
} else if (r > 8) {
System.out.println("len+++++++++>" + (len));
cell.setCellValue(colNames[len++]);
}
} else if (c == 1) {
cell.setCellValue((r & 1) == 1 ? today : sign);
System.err.println("r---->" + r);
} else if (c == 2) {
cell.setCellValue((r & 1) == 1 ? "当天" : "当月");
} else {
if ((c & 1) == 1) {
sheet.addMergedRegion(new CellRangeAddress(r, r, c, c + 1));
cell.setCellValue("26.55");
}
}
}
// 处理第8行以后的数据(不包括第8行)
if (r > 8) {
// 设置列高(图片的高度)
if (r % 3 == 0) {
sheet.getRow(r).setHeightInPoints(110);
}
if (c == 0) {
System.err.println("r---->" + r);
cell.setCellValue(colNames[r - 4]);
} else if (c == 1) {
cell.setCellValue((r % 3) == 0 ? today : (r % 3 == 1 ? "PV 浏览量" : "Total Sales 总额"));
} else if (c == 2) {
if (r % 9 == 0) {
sheet.addMergedRegion(new CellRangeAddress(r, r + 8, c, c));
if (r / 9 == 1) cell.setCellValue("当天");
else cell.setCellValue("当月");
cell.setCellStyle(style);
}
} else {
if (r % 3 == 0) {
if ((c & 1) == 1) {
sheet.addMergedRegion(new CellRangeAddress(r, r, c, c + 1));
// 添加远程图片信息
url = new URL(jpgUrls[(c - 3) / 2]);
bufferImg = ImageIO.read(url.openStream());
byteArrayOut = new ByteArrayOutputStream();
ImageIO.write(bufferImg, "jpg", byteArrayOut);
patri = (HSSFPatriarch) sheet.createDrawingPatriarch();
anchor = new HSSFClientAnchor(10, 2, 0, 0, (short) c, r, (short) (c + 2), r + 1);
patri.createPicture(
anchor,
wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
bufferImg.flush();
// link = new HSSFHyperlink(HSSFHyperlink.LINK_URL);
// System.out.println(https[(c-3)/2]);
// link.setAddress("fetion/"+https[(c-3)/2]);
// cell.setHyperlink(link);
// link = (HSSFHyperlink) cell.getHyperlink();
// link = new HSSFHyperlink(HSSFHyperlink.LINK_URL);
// link.setAddress(https[(c-3)/2]);
// cell.setHyperlink(link);
}
} else {
if ((c & 1) == 0) {
link = wb.getCreationHelper().createHyperlink(Hyperlink.LINK_URL);
link.setAddress(https[(c - 3) / 2]);
cell.setHyperlink(link); // 设定单元格的链接
cell.setCellValue("图片超链接");
} else {
cell.setCellValue("Number");
}
}
}
}
}
}
// 备注
row = sheet.createRow(27);
cell = row.createCell(0);
sheet.addMergedRegion(new CellRangeAddress(27, 27, 0, colNum - 1));
cell.setCellValue("* 销量排名不以销售金额计算，如相同销量者，则以PV量少者为优胜");
FileOutputStream out = new FileOutputStream(fileName);
wb.write(out);
out.close();
} catch (Exception e) {
e.printStackTrace();
}
System.out.println("++++++++++++ EXCEl文件 success +++++++++++++");
}
private Workbook handleExcel(List objs, Class clz, boolean isXssf, String message) {
XSSFWorkbook wb = null;
try {
if (isXssf) {
XSSFWorkbook w = new XSSFWorkbook();
} else {
HSSFWorkbook w = new HSSFWorkbook();
}
wb = new XSSFWorkbook();
XSSFDataFormat format = wb.createDataFormat();
XSSFSheet sheet = wb.createSheet(message + "备份记录"); // 取excel工作表对象
XSSFCellStyle cellStyle = wb.createCellStyle(); // 设置excel单元格样式
XSSFCellStyle passwordCellStyle = wb.createCellStyle(); // 设置密码单元格样式
XSSFDataFormat passwordFormat = wb.createDataFormat();
passwordCellStyle.setDataFormat(passwordFormat.getFormat(";;;"));
List<ExcelHeader> headers = getHeaderList(clz);
Collections.sort(headers);
sheet.addMergedRegion(new CellRangeAddress(0, (short) 0, 0, (short) (headers.size() - 1)));
Row r0 = sheet.createRow(0);
Cell cell = r0.createCell(0);
r0.setHeightInPoints(28);
cell.setCellValue(message + "备份记录");
Row r = sheet.createRow(1);
r.setHeightInPoints(25);
cell.setCellStyle(cellStyle);
// 输出标题
for (int i = 0; i < headers.size(); i++) {
Cell cell1 = r.createCell(i);
if (headers.get(i).getTitle().equals("密码")) cell1.setCellStyle(passwordCellStyle);
else cell1.setCellStyle(cellStyle);
cell1.setCellValue(headers.get(i).getTitle());
}
Object obj = null;
// 输出用户资料信息
if (message.indexOf("用户资料 ") > 0) {
sheet.setColumnWidth(3, 32 * 150);
sheet.setColumnWidth(4, 32 * 110);
sheet.setColumnWidth(7, 32 * 120);
for (int i = 0; i < objs.size(); i++) {
r = sheet.createRow(i + 2);
obj = objs.get(i);
for (int j = 0; j < headers.size(); j++) {
Cell cell2 = r.createCell(j);
copyDefaultCellStyle(null, cell2, cellStyle, 0);
if (getMethodName(headers.get(j)).equals("nabled"))
cell2.setCellValue(BeanUtils.getProperty(obj, "enabled"));
else if (getMethodName(headers.get(j)).equals("password")) {
cell2.setCellStyle(passwordCellStyle);
cell2.setCellValue(BeanUtils.getProperty(obj, getMethodName(headers.get(j))));
} else cell2.setCellValue(BeanUtils.getProperty(obj, getMethodName(headers.get(j))));
}
}
}
// 输出房间使用信息数据
else {
sheet.setColumnWidth(0, 32 * 80);
sheet.setColumnWidth(2, 32 * 100);
sheet.setColumnWidth(3, 32 * 190);
sheet.setColumnWidth(4, 32 * 190);
sheet.setColumnWidth(5, 32 * 190);
sheet.setColumnWidth(10, 32 * 130);
for (int i = 0; i < objs.size(); i++) {
r = sheet.createRow(i + 2);
obj = objs.get(i);
for (int j = 0; j < headers.size(); j++) {
Cell cell2 = r.createCell(j);
if (j == 3 || j == 4 || j == 5) {
XSSFCellStyle cs3 = wb.createCellStyle();
cell2.setCellValue(new Date());
copyDefaultCellStyle(format, cell2, cs3, 1);
}
if (j == 10) {
XSSFCellStyle cs2 = wb.createCellStyle();
copyDefaultCellStyle(format, cell2, cs2, 2);
}
copyDefaultCellStyle(null, cell2, cellStyle, 0);
cell2.setCellValue(BeanUtils.getProperty(obj, getMethodName(headers.get(j))));
}
}
}
// 设置行列的默认宽度和高度
} catch (IllegalAccessException e) {
e.printStackTrace();
logger.error(e);
} catch (InvocationTargetException e) {
e.printStackTrace();
logger.error(e);
} catch (NoSuchMethodException e) {
e.printStackTrace();
logger.error(e);
}
return wb;
}
public static void writeExcelFile(String fileLocation) {
try {
FileOutputStream fileOut = new FileOutputStream(fileLocation);
HSSFWorkbook workbook = new HSSFWorkbook();
Font bold = workbook.createFont(); // Create font
bold.setBoldweight(Font.BOLDWEIGHT_BOLD); // Make font bold
CellStyle correctCell = workbook.createCellStyle();
correctCell.setFillForegroundColor(HSSFColor.GREEN.index);
correctCell.setFillBackgroundColor(HSSFColor.GREEN.index);
correctCell.setFillPattern(CellStyle.SOLID_FOREGROUND);
CellStyle incorrectCell = workbook.createCellStyle();
incorrectCell.setFillForegroundColor(HSSFColor.RED.index);
incorrectCell.setFillBackgroundColor(HSSFColor.RED.index);
incorrectCell.setFillPattern(CellStyle.SOLID_FOREGROUND);
CellStyle classificationCells = workbook.createCellStyle();
classificationCells.setFillForegroundColor(HSSFColor.YELLOW.index);
classificationCells.setFillBackgroundColor(HSSFColor.YELLOW.index);
classificationCells.setFillPattern(CellStyle.SOLID_FOREGROUND);
CellStyle attributeNameCells = workbook.createCellStyle();
attributeNameCells.setFont(bold);
CellStyle classificationAttributeCell = workbook.createCellStyle();
classificationAttributeCell.setFillForegroundColor(HSSFColor.YELLOW.index);
classificationAttributeCell.setFillBackgroundColor(HSSFColor.YELLOW.index);
classificationAttributeCell.setFillPattern(CellStyle.SOLID_FOREGROUND);
classificationAttributeCell.setFont(bold);
Sheet worksheet = workbook.createSheet("Results");
Row currRow = worksheet.createRow(0);
for (int attribute = 0; attribute < metadataLL.size() + 1; attribute++) {
Cell currCell = currRow.createCell(attribute);
if (attribute < metadataLL.size()) {
currCell.setCellValue(metadataLL.get(attribute)[0]);
if (metadataLL.get(attribute)[2].compareTo("classifier") == 0) {
currCell.setCellStyle(classificationAttributeCell);
} else {
currCell.setCellStyle(attributeNameCells);
}
} else {
currCell.setCellValue("Guessed Classification");
currCell.setCellStyle(attributeNameCells);
}
}
int correct = 0;
int incorrect = 0;
for (int node = 0; node < testDataLL.size(); node++) {
currRow = worksheet.createRow(node + 1); // Offset by one since first row is header data
int classifierCompleted = 0; // Necessary for if data does not end in classifier
for (int attribute = 0;
attribute < metadataLL.size() + 2;
attribute++) // +1 for the row for guessed data +1 for the row that contains
{
Cell currCell = currRow.createCell(attribute);
if (attribute < metadataLL.size()) // Print testingData
{
if (metadataLL.get(attribute)[2].compareTo("classifier") == 0) {
currCell.setCellValue(actualClassifications.get(node));
currCell.setCellStyle(classificationCells);
classifierCompleted++;
} else {
currCell.setCellValue(testDataLL.get(node)[attribute - classifierCompleted]);
}
} else if (attribute == metadataLL.size()) // Print guessed classification
{
currCell.setCellValue(guessedClassifications.get(node));
if (guessedClassifications.get(node).compareTo(actualClassifications.get(node)) == 0) {
currCell.setCellStyle(correctCell);
correct++;
} else {
currCell.setCellStyle(incorrectCell);
incorrect++;
}
if (node == testDataLL.size() - 1) // If this is the last loop
{
double precentRight = (double) correct / (correct + incorrect);
currRow = worksheet.createRow(node + 2);
currCell = currRow.createCell(attribute);
currCell.setCellValue(precentRight);
if (precentRight > .90) {
correctCell.setDataFormat(workbook.createDataFormat().getFormat("0.000%"));
currCell.setCellStyle(correctCell);
} else {
incorrectCell.setDataFormat(workbook.createDataFormat().getFormat("0.000%"));
currCell.setCellStyle(incorrectCell);
}
}
} else if (attribute
== metadataLL.size() + 1) // Print potential bad training data if the flag is true
{
if (unseenDataFlag.get(node)) {
currCell.setCellValue(
"This node an attribute value not in the training set, classifier selected is based on most frequent classifier. If laplacian smoothing is 1 or more this likely wont happen"); // TODO make this a bit shorter
}
}
}
}
worksheet = workbook.createSheet("Training Data");
currRow = worksheet.createRow(0);
for (int attribute = 0; attribute < metadataLL.size(); attribute++) {
Cell currCell = currRow.createCell(attribute);
currCell.setCellValue(metadataLL.get(attribute)[0]);
currCell.setCellStyle(attributeNameCells);
}
for (int node = 0; node < trainingDataLL.size(); node++) {
currRow = worksheet.createRow(node + 1); // Offset by one since first row is header data
int classifierCompleted = 0; // Necessary for if data does not end in classifier
for (int attribute = 0; attribute < metadataLL.size(); attribute++) {
Cell currCell = currRow.createCell(attribute);
if (metadataLL.get(attribute)[2].compareTo("classifier") == 0) {
currCell.setCellValue(knownClassifications.get(node));
classifierCompleted++;
} else {
currCell.setCellValue(trainingDataLL.get(node)[attribute - classifierCompleted]);
}
}
}
worksheet = workbook.createSheet("Likelihood");
currRow = worksheet.createRow(0);
int largestAttributeSize = 0;
for (int attribute = 0; attribute < classifier.size(); attribute++) {
if (classifier.get(attribute).size() > largestAttributeSize) {
largestAttributeSize = classifier.get(attribute).size();
}
}
// Label attributes along the top
for (int i = 0; i < metadataLL.size(); i++) {
if (i == 0) {
Cell currCell = currRow.createCell(i);
// currCell.setCellValue("Attributes");
currCell.setCellStyle(attributeNameCells);
} else {
Cell currCell = currRow.createCell(i);
currCell.setCellValue(
metadataLL
.get(i - 1)[0]); // -1 since the first cell does not contain a attribute name
currCell.setCellStyle(attributeNameCells);
}
}
// List possible classifications on the side and classification likelihoods at the end
for (int i = 0;
i
< (largestAttributeSize * (classificationTypes.size() + 1)
+ classificationTypes.size()
+ 1);
i++) // +1 since the first row of each stride lists each attributes string of what
// occurrence the likelihoods are displaying
{ // +classificationTypes.size() so we can list the classification types likelihood at the end
currRow = worksheet.createRow(i + 1); // +1 since first row is attribute names
Cell currCell = currRow.createCell(0);
int currentClassificationType =
i
% (classificationTypes.size()
+ 1); // +1 since the first row of each stride lists each attributes string of
// what occurrence the likelihoods are displaying
// List the classification type of each row along the side
if (i
< largestAttributeSize
* (classificationTypes.size()
+ 1)) // +1 since the first row of each stride lists each attributes string of
// what occurrence the likelihoods are displaying
{
for (int j = 0;
j < classificationTypes.size() + 1;
j++) // +1 since the first row of each stride lists each attributes string of what
// occurrence the likelihoods are displaying
{
if (currentClassificationType == 0) {
// Do nothing for now may have it say something later
} else if (currentClassificationType == j) {
currCell.setCellValue(
classificationTypes.get(
j - 1)); // -1 since the first cell does not contain a classification type
}
}
} else // List the likelihood of each classification at the end
{
for (int j = 0;
j < classificationTypes.size() + 1;
j++) // +1 since the first row of each stride lists each attributes string of what
// occurrence the likelihoods are displaying
{
if (currentClassificationType == 0) {
// Do nothing for now may have it say value later
} else if (currentClassificationType == j) {
currCell.setCellValue(
"Likelihood of: "
+ classificationTypes.get(j - 1)
+ " is "
+ classificationLikelihood.get(
j - 1)); // -1 since the first cell does not contain a classification type
}
}
}
currCell.setCellStyle(attributeNameCells);
}
// List the data
for (int attribute = 0; attribute < classifier.size(); attribute++) {
for (int occurrences = 0; occurrences < classifier.get(attribute).size(); occurrences++) {
for (int classification = 0;
classification < classifier.get(attribute).get(occurrences).length;
classification++) {
currRow =
worksheet.getRow(
(occurrences * classifier.get(attribute).get(occurrences).length
+ classification)
+ 1); // +1 since first row is attribute names
Cell currCell =
currRow.createCell(
(attribute) + 1); // TODO figure out why this errors out at i:0 j:4 k:0
// largestAttributeSize:105 on kidney dataset
currCell.setCellValue(classifier.get(attribute).get(occurrences)[classification]);
}
}
}
workbook.write(fileOut);
workbook.close();
workbook.close();
} catch (FileNotFoundException e) {
System.out.println("Error file not found");
e.printStackTrace();
System.exit(0);
} catch (IOException e) {
System.out.println("Unable to output file, is the output destination writelocked?");
e.printStackTrace();
System.exit(0);
}
}
public File generateXLSResponse(QueryResult queryResult, Locale locale, String baseURL) {
File excelFile = new File("export_parts.xls");
// Blank workbook
XSSFWorkbook workbook = new XSSFWorkbook();
// Create a blank sheet
XSSFSheet sheet = workbook.createSheet("Parts Data");
String header = StringUtils.join(queryResult.getQuery().getSelects(), ";");
String[] columns = header.split(";");
Map<Integer, String[]> data = new HashMap<>();
String[] headerFormatted = createXLSHeaderRow(header, columns, locale);
data.put(1, headerFormatted);
Map<Integer, String[]> commentsData = new HashMap<>();
String[] headerComments = createXLSHeaderRowComments(header, columns);
commentsData.put(1, headerComments);
List<String> selects = queryResult.getQuery().getSelects();
int i = 1;
for (QueryResultRow row : queryResult.getRows()) {
i++;
data.put(i, createXLSRow(selects, row, baseURL));
commentsData.put(i, createXLSRowComments(selects, row));
}
// Iterate over data and write to sheet
Set<Integer> keyset = data.keySet();
int rownum = 0;
for (Integer key : keyset) {
Row row = sheet.createRow(rownum++);
String[] objArr = data.get(key);
int cellnum = 0;
for (String obj : objArr) {
Cell cell = row.createCell(cellnum++);
cell.setCellValue(obj);
}
CreationHelper factory = workbook.getCreationHelper();
Drawing drawing = sheet.createDrawingPatriarch();
String[] commentsObjArr = commentsData.get(key);
cellnum = 0;
for (String commentsObj : commentsObjArr) {
if (commentsObj.length() > 0) {
Cell cell = row.getCell(cellnum) != null ? row.getCell(cellnum) : row.createCell(cellnum);
// When the comment box is visible, have it show in a 1x3 space
ClientAnchor anchor = factory.createClientAnchor();
anchor.setCol1(cell.getColumnIndex());
anchor.setCol2(cell.getColumnIndex() + 1);
anchor.setRow1(row.getRowNum());
anchor.setRow2(row.getRowNum() + 1);
Comment comment = drawing.createCellComment(anchor);
RichTextString str = factory.createRichTextString(commentsObj);
comment.setString(str);
// Assign the comment to the cell
cell.setCellComment(comment);
}
cellnum++;
}
}
// Define header style
Font headerFont = workbook.createFont();
headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
headerFont.setFontHeightInPoints((short) 10);
headerFont.setFontName("Courier New");
headerFont.setItalic(true);
headerFont.setColor(IndexedColors.WHITE.getIndex());
CellStyle headerStyle = workbook.createCellStyle();
headerStyle.setFont(headerFont);
headerStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
headerStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
// Set header style
for (int j = 0; j < columns.length; j++) {
Cell cell = sheet.getRow(0).getCell(j);
cell.setCellStyle(headerStyle);
if (cell.getCellComment() != null) {
String comment = cell.getCellComment().getString().toString();
if (comment.equals(QueryField.CTX_PRODUCT_ID)
|| comment.equals(QueryField.CTX_SERIAL_NUMBER)
|| comment.equals(QueryField.PART_MASTER_NUMBER)) {
for (int k = 0; k < queryResult.getRows().size(); k++) {
Cell grayCell =
sheet.getRow(k + 1).getCell(j) != null
? sheet.getRow(k + 1).getCell(j)
: sheet.getRow(k + 1).createCell(j);
grayCell.setCellStyle(headerStyle);
}
}
}
}
try {
// Write the workbook in file system
FileOutputStream out = new FileOutputStream(excelFile);
workbook.write(out);
out.close();
} catch (Exception e) {
LOGGER.log(Level.FINEST, null, e);
}
return excelFile;
}
@Test
public void dateCells() throws Exception {
Workbook workbook = _testDataProvider.createWorkbook();
fixFonts(workbook);
Sheet sheet = workbook.createSheet();
DataFormat df = workbook.getCreationHelper().createDataFormat();
CellStyle style1 = workbook.createCellStyle();
style1.setDataFormat(df.getFormat("m"));
CellStyle style3 = workbook.createCellStyle();
style3.setDataFormat(df.getFormat("mmm"));
CellStyle style5 = workbook.createCellStyle(); // rotated text
style5.setDataFormat(df.getFormat("mmm/dd/yyyy"));
Calendar calendar = LocaleUtil.getLocaleCalendar(2010, 0, 1); // Jan 1 2010
Row row = sheet.createRow(0);
row.createCell(0).setCellValue(DateUtil.getJavaDate(0)); // default date
Cell cell1 = row.createCell(1);
cell1.setCellValue(calendar);
cell1.setCellStyle(style1);
row.createCell(2).setCellValue("1"); // column 1 should be sized as '1'
Cell cell3 = row.createCell(3);
cell3.setCellValue(calendar);
cell3.setCellStyle(style3);
row.createCell(4).setCellValue("Jan");
Cell cell5 = row.createCell(5);
cell5.setCellValue(calendar);
cell5.setCellStyle(style5);
row.createCell(6).setCellValue("Jan/01/2010");
Cell cell7 = row.createCell(7);
cell7.setCellFormula("DATE(2010,1,1)");
cell7.setCellStyle(style3); // should be sized as 'Jan'
// autosize not-evaluated cells, formula cells are sized as if the result is 0
for (int i = 0; i < 8; i++) sheet.autoSizeColumn(i);
assertEquals(sheet.getColumnWidth(2), sheet.getColumnWidth(1)); // date formatted as 'm'
assertTrue(sheet.getColumnWidth(3) > sheet.getColumnWidth(1)); // 'mmm' is wider than 'm'
assertEquals(sheet.getColumnWidth(4), sheet.getColumnWidth(3)); // date formatted as 'mmm'
assertTrue(
sheet.getColumnWidth(5) > sheet.getColumnWidth(3)); // 'mmm/dd/yyyy' is wider than 'mmm'
assertEquals(
sheet.getColumnWidth(6), sheet.getColumnWidth(5)); // date formatted as 'mmm/dd/yyyy'
// YK: width of not-evaluated formulas that return data is not determined
// POI seems to conevert '0' to Excel date which is the beginng of the Excel's date system
// evaluate formulas and re-autosize
evaluateWorkbook(workbook);
for (int i = 0; i < 8; i++) sheet.autoSizeColumn(i);
assertEquals(sheet.getColumnWidth(2), sheet.getColumnWidth(1)); // date formatted as 'm'
assertTrue(sheet.getColumnWidth(3) > sheet.getColumnWidth(1)); // 'mmm' is wider than 'm'
assertEquals(sheet.getColumnWidth(4), sheet.getColumnWidth(3)); // date formatted as 'mmm'
assertTrue(
sheet.getColumnWidth(5) > sheet.getColumnWidth(3)); // 'mmm/dd/yyyy' is wider than 'mmm'
assertEquals(
sheet.getColumnWidth(6), sheet.getColumnWidth(5)); // date formatted as 'mmm/dd/yyyy'
assertEquals(
sheet.getColumnWidth(4), sheet.getColumnWidth(7)); // date formula formatted as 'mmm'
workbook.close();
}
public void WriteExcel(String filePath, String cont, int r) {
String[] couple = cont.split(" ");
try {
inp = new FileInputStream(filePath);
wb = WorkbookFactory.create(inp);
sheet = wb.getSheetAt(0);
Row row = sheet.createRow(r);
Cell cell;
cell = row.createCell(0);
cell.setCellType(Cell.CELL_TYPE_STRING);
cell.setCellValue(couple[0]);
cell = row.createCell(1);
cell = row.createCell(2);
cell.setCellType(Cell.CELL_TYPE_STRING);
cell.setCellValue(couple[1]);
cell = row.createCell(3);
cell.setCellType(Cell.CELL_TYPE_STRING);
cell.setCellValue(couple[2]);
cell = row.createCell(4); // 身份证号码
cell.setCellType(Cell.CELL_TYPE_STRING);
cell.setCellValue(couple[5]);
cell = row.createCell(5); // 姓名
cell.setCellType(Cell.CELL_TYPE_STRING);
cell.setCellValue(couple[3]);
cell = row.createCell(6);
cell = row.createCell(7); // 户籍地址
cell.setCellType(Cell.CELL_TYPE_STRING);
cell.setCellValue(couple[6]);
cell = row.createCell(8);
cell = row.createCell(9);
cell = row.createCell(10); // 出生日期
cell.setCellType(Cell.CELL_TYPE_STRING);
if (couple[5].length() > 13) {
cell.setCellValue(
couple[5].substring(6, 10)
+ "-"
+ couple[5].substring(10, 12)
+ "-"
+ couple[5].substring(12, 14));
} else {
cell.setCellValue("erro");
}
cell = row.createCell(11); // 是否再婚
cell.setCellType(Cell.CELL_TYPE_STRING);
cell.setCellValue(couple[7]);
cell = row.createCell(12); // 身份证号码
cell.setCellType(Cell.CELL_TYPE_STRING);
cell.setCellValue(couple[14]);
cell = row.createCell(13); // 姓名
cell.setCellType(Cell.CELL_TYPE_STRING);
cell.setCellValue(couple[12]);
cell = row.createCell(0xe);
cell = row.createCell(15); // 户籍地址
cell.setCellType(Cell.CELL_TYPE_STRING);
cell.setCellValue(couple[15]);
cell = row.createCell(16);
cell = row.createCell(17);
cell = row.createCell(18); // 出生日期
cell.setCellType(Cell.CELL_TYPE_STRING);
if (couple[14].length() > 13) {
cell.setCellValue(
couple[14].substring(6, 10)
+ "-"
+ couple[14].substring(10, 12)
+ "-"
+ couple[14].substring(12, 14));
} else {
cell.setCellValue("erro");
}
cell = row.createCell(19); // 是否再婚
cell.setCellType(Cell.CELL_TYPE_STRING);
cell.setCellValue(couple[16]);
fileOut = new FileOutputStream(filePath);
wb.write(fileOut);
fileOut.close();
} catch (IOException e) {
System.out.print("Excel.Write() ERRO, Exist IOException in " + couple[0] + " line\n");
// e.printStackTrace();
} catch (InvalidFormatException e) {
System.out.print(
"Excel.Write() ERRO, ExistInvalidFormatException in " + couple[0] + " line\n");
// e.printStackTrace();
}
}