public void testSheetFunctions() {
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet s = wb.createSheet("A");
HSSFRow r = null;
HSSFCell c = null;
r = s.createRow(0);
c = r.createCell(0);
c.setCellValue(1);
c = r.createCell(1);
c.setCellValue(2);
s = wb.createSheet("B");
r = s.createRow(0);
c = r.createCell(0);
c.setCellFormula("AVERAGE(A!A1:B1)");
c = r.createCell(1);
c.setCellFormula("A!A1+A!B1");
c = r.createCell(2);
c.setCellFormula("A!$A$1+A!$B1");
wb = HSSFTestDataSamples.writeOutAndReadBack(wb);
s = wb.getSheet("B");
r = s.getRow(0);
c = r.getCell(0);
assertTrue(
"expected: AVERAGE(A!A1:B1) got: " + c.getCellFormula(),
("AVERAGE(A!A1:B1)").equals(c.getCellFormula()));
c = r.getCell(1);
assertTrue(
"expected: A!A1+A!B1 got: " + c.getCellFormula(), ("A!A1+A!B1").equals(c.getCellFormula()));
}
public void testSumIf() {
String function = "SUMIF(A1:A5,\">4000\",B1:B5)";
HSSFWorkbook wb = openSample("sumifformula.xls");
HSSFSheet s = wb.getSheetAt(0);
HSSFRow r = s.getRow(0);
HSSFCell c = r.getCell(2);
assertEquals(function, c.getCellFormula());
wb = new HSSFWorkbook();
s = wb.createSheet();
r = s.createRow(0);
c = r.createCell(0);
c.setCellValue(1000);
c = r.createCell(1);
c.setCellValue(1);
r = s.createRow(1);
c = r.createCell(0);
c.setCellValue(2000);
c = r.createCell(1);
c.setCellValue(2);
r = s.createRow(2);
c = r.createCell(0);
c.setCellValue(3000);
c = r.createCell(1);
c.setCellValue(3);
r = s.createRow(3);
c = r.createCell(0);
c.setCellValue(4000);
c = r.createCell(1);
c.setCellValue(4);
r = s.createRow(4);
c = r.createCell(0);
c.setCellValue(5000);
c = r.createCell(1);
c.setCellValue(5);
r = s.getRow(0);
c = r.createCell(2);
c.setCellFormula(function);
HSSFTestDataSamples.writeOutAndReadBack(wb);
}
/**
* It is responsible for creating a cell within the row.
*
* @param fila: row where create the cell
* @param position: Determines the position where anger cell within the row.
* @param value: Sets the value that will be created within the cell.
*/
private void createCell(HSSFRow fila, int position, String value) {
HSSFCell cell;
cell =
fila.createCell(
(short) position); // Se crea una cell dentro de la fila
cell.setCellValue(new HSSFRichTextString(value));
}
private void writeData2Excel(HSSFSheet sheet, HSSFCellStyle dataStyle, HSSFCellStyle headerStyle)
throws ContainerException {
RowSet searchResult = executeFrm.getSearchResult();
if (searchResult != null && !searchResult.isEmpty()) {
List<ReportViewFrm> definedViews = executeFrm.getDefinedViews();
int fieldCount = definedViews.size();
int dataCount = searchResult.getSize();
int xlsRowNum = 0;
for (int i = 0; i < dataCount; i++) {
Row row = searchResult.getRow(i);
xlsRowNum = i;
if (isFirstFile) {
xlsRowNum++;
}
HSSFRow xlsRow = sheet.createRow(xlsRowNum);
xlsRow.setHeightInPoints(18);
for (short j = 0; j < fieldCount; j++) {
ReportViewFrm viewFrm = definedViews.get(j);
String fieldName = viewFrm.getFieldName();
HSSFCell xlsCell = xlsRow.createCell(j);
xlsCell.setCellStyle(dataStyle);
HSSFRichTextString richText = new HSSFRichTextString(row.getStrValue(fieldName));
xlsCell.setCellValue(richText);
}
}
}
}
public void testDateFormulas() {
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet s = wb.createSheet("testSheet1");
HSSFRow r = null;
HSSFCell c = null;
r = s.createRow(0);
c = r.createCell(0);
HSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));
c.setCellValue(new Date());
c.setCellStyle(cellStyle);
// assertEquals("Checking hour = " + hour, date.getTime().getTime(),
// HSSFDateUtil.getJavaDate(excelDate).getTime());
for (int k = 1; k < 100; k++) {
r = s.createRow(k);
c = r.createCell(0);
c.setCellFormula("A" + (k) + "+1");
c.setCellStyle(cellStyle);
}
HSSFTestDataSamples.writeOutAndReadBack(wb);
}
public void testRVAoperands() throws Exception {
File file = TempFile.createTempFile("testFormulaRVA", ".xls");
FileOutputStream out = new FileOutputStream(file);
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet s = wb.createSheet();
HSSFRow r = null;
HSSFCell c = null;
r = s.createRow(0);
c = r.createCell(0);
c.setCellFormula("A3+A2");
c = r.createCell(1);
c.setCellFormula("AVERAGE(A3,A2)");
c = r.createCell(2);
c.setCellFormula("ROW(A3)");
c = r.createCell(3);
c.setCellFormula("AVERAGE(A2:A3)");
c = r.createCell(4);
c.setCellFormula("POWER(A2,A3)");
c = r.createCell(5);
c.setCellFormula("SIN(A2)");
c = r.createCell(6);
c.setCellFormula("SUM(A2:A3)");
c = r.createCell(7);
c.setCellFormula("SUM(A2,A3)");
r = s.createRow(1);
c = r.createCell(0);
c.setCellValue(2.0);
r = s.createRow(2);
c = r.createCell(0);
c.setCellValue(3.0);
wb.write(out);
out.close();
assertTrue("file exists", file.exists());
}
private void writeDataHeader(HSSFSheet sheet, HSSFCellStyle headerStyle) throws ReportException {
try {
sheet.setDisplayGridlines(false); // 不显示网格线
HSSFRow xlsRow = sheet.createRow(0);
xlsRow.setHeightInPoints(18);
List<ReportViewFrm> definedViews = executeFrm.getDefinedViews();
int fieldCount = definedViews.size();
for (short j = 0; j < fieldCount; j++) {
ReportViewFrm viewFrm = definedViews.get(j);
HSSFCell xlsCell = xlsRow.createCell(j);
xlsCell.setCellStyle(headerStyle);
HSSFRichTextString richText = new HSSFRichTextString(viewFrm.getFieldDesc());
xlsCell.setCellValue(richText);
}
} catch (Throwable ex) {
Logger.logError(ex);
throw new ReportException(ex.getMessage());
}
}
/** Test for bug due to attempt to convert a cached formula error result to a boolean */
public void testUpdateCachedFormulaResultFromErrorToNumber_bug46479() {
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("Sheet1");
HSSFRow row = sheet.createRow(0);
HSSFCell cellA1 = row.createCell(0);
HSSFCell cellB1 = row.createCell(1);
cellB1.setCellFormula("A1+1");
HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
cellA1.setCellErrorValue((byte) HSSFErrorConstants.ERROR_NAME);
fe.evaluateFormulaCell(cellB1);
cellA1.setCellValue(2.5);
fe.notifyUpdateCell(cellA1);
try {
fe.evaluateInCell(cellB1);
} catch (IllegalStateException e) {
if (e.getMessage().equals("Cannot get a numeric value from a error formula cell")) {
throw new AssertionFailedError("Identified bug 46479a");
}
}
assertEquals(3.5, cellB1.getNumericCellValue(), 0.0);
}
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 testIfFormulas() {
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet s = wb.createSheet("testSheet1");
HSSFRow r = null;
HSSFCell c = null;
r = s.createRow(0);
c = r.createCell(1);
c.setCellValue(1);
c = r.createCell(2);
c.setCellValue(2);
c = r.createCell(3);
c.setCellFormula("MAX(A1:B1)");
c = r.createCell(4);
c.setCellFormula("IF(A1=D1,\"A1\",\"B1\")");
wb = HSSFTestDataSamples.writeOutAndReadBack(wb);
s = wb.getSheetAt(0);
r = s.getRow(0);
c = r.getCell(4);
assertTrue(
"expected: IF(A1=D1,\"A1\",\"B1\") got " + c.getCellFormula(),
("IF(A1=D1,\"A1\",\"B1\")").equals(c.getCellFormula()));
wb = openSample("IfFormulaTest.xls");
s = wb.getSheetAt(0);
r = s.getRow(3);
c = r.getCell(0);
assertTrue(
"expected: IF(A3=A1,\"A1\",\"A2\") got " + c.getCellFormula(),
("IF(A3=A1,\"A1\",\"A2\")").equals(c.getCellFormula()));
// c = r.getCell((short)1);
// assertTrue("expected: A!A1+A!B1 got: "+c.getCellFormula(),
// ("A!A1+A!B1").equals(c.getCellFormula()));
wb = new HSSFWorkbook();
s = wb.createSheet("testSheet1");
r = null;
c = null;
r = s.createRow(0);
c = r.createCell(0);
c.setCellFormula("IF(1=1,0,1)");
HSSFTestDataSamples.writeOutAndReadBack(wb);
wb = new HSSFWorkbook();
s = wb.createSheet("testSheet1");
r = null;
c = null;
r = s.createRow(0);
c = r.createCell(0);
c.setCellValue(1);
c = r.createCell(1);
c.setCellValue(3);
HSSFCell formulaCell = r.createCell(3);
r = s.createRow(1);
c = r.createCell(0);
c.setCellValue(3);
c = r.createCell(1);
c.setCellValue(7);
formulaCell.setCellFormula("IF(A1=B1,AVERAGE(A1:B1),AVERAGE(A2:B2))");
HSSFTestDataSamples.writeOutAndReadBack(wb);
}
