public void testModifyArrayCells_mergeCells() {
Workbook workbook = _testDataProvider.createWorkbook();
Sheet sheet = workbook.createSheet();
assertEquals(0, sheet.getNumMergedRegions());
// single-cell array formulas behave just like normal cells
CellRange<? extends Cell> srange =
sheet.setArrayFormula("SUM(A4:A6,B4:B6)", CellRangeAddress.valueOf("B5"));
Cell scell = srange.getTopLeftCell();
sheet.addMergedRegion(CellRangeAddress.valueOf("B5:C6"));
// we are still an array formula
assertEquals(Cell.CELL_TYPE_FORMULA, scell.getCellType());
assertTrue(scell.isPartOfArrayFormulaGroup());
assertEquals(1, sheet.getNumMergedRegions());
// we cannot merge cells included in an array formula
CellRange<? extends Cell> mrange =
sheet.setArrayFormula("A1:A3*B1:B3", CellRangeAddress.valueOf("C1:C3"));
CellRangeAddress cra = CellRangeAddress.valueOf("C1:C3");
try {
sheet.addMergedRegion(cra);
fail("expected exception");
} catch (IllegalStateException e) {
String msg =
"The range "
+ cra.formatAsString()
+ " intersects with a multi-cell array formula. You cannot merge cells of an array.";
assertEquals(msg, e.getMessage());
}
// the number of merged regions remains the same
assertEquals(1, sheet.getNumMergedRegions());
}
/** Set multi-cell array formula */
public final void testSetArrayFormula_multiCell() {
Workbook workbook = _testDataProvider.createWorkbook();
Sheet sheet = workbook.createSheet();
// multi-cell formula
// rows 3-5 don't exist yet
assertNull(sheet.getRow(3));
assertNull(sheet.getRow(4));
assertNull(sheet.getRow(5));
CellRangeAddress range = CellRangeAddress.valueOf("C4:C6");
Cell[] cells = sheet.setArrayFormula("SUM(A1:A3*B1:B3)", range).getFlattenedCells();
assertEquals(3, cells.length);
// sheet.setArrayFormula creates rows and cells for the designated range
assertSame(cells[0], sheet.getRow(3).getCell(2));
assertSame(cells[1], sheet.getRow(4).getCell(2));
assertSame(cells[2], sheet.getRow(5).getCell(2));
for (Cell acell : cells) {
assertTrue(acell.isPartOfArrayFormulaGroup());
assertEquals(Cell.CELL_TYPE_FORMULA, acell.getCellType());
assertEquals("SUM(A1:A3*B1:B3)", acell.getCellFormula());
// retrieve the range and check it is the same
assertEquals(range.formatAsString(), acell.getArrayFormulaRange().formatAsString());
}
}
public void testModifyArrayCells_removeRow() {
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();
assertEquals(Cell.CELL_TYPE_FORMULA, scell.getCellType());
Row srow = scell.getRow();
assertSame(srow, sheet.getRow(cra.getFirstRow()));
sheet.removeRow(srow);
assertNull(sheet.getRow(cra.getFirstRow()));
// re-create the removed row and cell
scell = sheet.createRow(cra.getFirstRow()).createCell(cra.getFirstColumn());
assertEquals(Cell.CELL_TYPE_BLANK, scell.getCellType());
assertFalse(scell.isPartOfArrayFormulaGroup());
// we cannot remove rows with 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 {
sheet.removeRow(mrow);
fail("expected exception");
} catch (IllegalStateException e) {
String msg =
"Row[rownum="
+ mrow.getRowNum()
+ "] contains cell(s) included in 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(mrow, sheet.getRow(mrow.getRowNum()));
assertSame(mcell, mrow.getCell(columnIndex));
assertTrue(mcell.isPartOfArrayFormulaGroup());
assertEquals(Cell.CELL_TYPE_FORMULA, mcell.getCellType());
}
}
public void testModifyArrayCells_setCellFormula() {
Workbook workbook = _testDataProvider.createWorkbook();
Sheet sheet = workbook.createSheet();
CellRange<? extends Cell> srange =
sheet.setArrayFormula("SUM(A4:A6,B4:B6)", CellRangeAddress.valueOf("B5"));
Cell scell = srange.getTopLeftCell();
assertEquals("SUM(A4:A6,B4:B6)", scell.getCellFormula());
assertEquals(Cell.CELL_TYPE_FORMULA, scell.getCellType());
assertTrue(scell.isPartOfArrayFormulaGroup());
scell.setCellFormula("SUM(A4,A6)");
// we are now a normal formula cell
assertEquals("SUM(A4,A6)", scell.getCellFormula());
assertFalse(scell.isPartOfArrayFormulaGroup());
assertEquals(Cell.CELL_TYPE_FORMULA, scell.getCellType());
// check that setting formula result works
assertEquals(0.0, scell.getNumericCellValue());
scell.setCellValue(33.0);
assertEquals(33.0, scell.getNumericCellValue());
// multi-cell array formula
CellRange<? extends Cell> mrange =
sheet.setArrayFormula("A1:A3*B1:B3", CellRangeAddress.valueOf("C1:C3"));
for (Cell mcell : mrange) {
// we cannot set individual formulas for cells included in an array formula
try {
assertEquals("A1:A3*B1:B3", mcell.getCellFormula());
mcell.setCellFormula("A1+A2");
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 Cell.setCellFormula leaves the cell
// in the state that it was in prior to the invocation
assertEquals("A1:A3*B1:B3", mcell.getCellFormula());
assertTrue(mcell.isPartOfArrayFormulaGroup());
}
}
public void testModifyArrayCells_shiftRows() {
Workbook workbook = _testDataProvider.createWorkbook();
Sheet sheet = workbook.createSheet();
// single-cell array formulas behave just like normal cells - we can change the cell type
CellRange<? extends Cell> srange =
sheet.setArrayFormula("SUM(A4:A6,B4:B6)", CellRangeAddress.valueOf("B5"));
Cell scell = srange.getTopLeftCell();
assertEquals("SUM(A4:A6,B4:B6)", scell.getCellFormula());
sheet.shiftRows(0, 0, 1);
sheet.shiftRows(0, 1, 1);
// we cannot set individual formulas for cells included in an array formula
CellRange<? extends Cell> mrange =
sheet.setArrayFormula("A1:A3*B1:B3", CellRangeAddress.valueOf("C1:C3"));
try {
sheet.shiftRows(0, 0, 1);
fail("expected exception");
} catch (IllegalStateException e) {
String msg =
"Row[rownum=0] contains cell(s) included in a multi-cell array formula. You cannot change part of an array.";
assertEquals(msg, e.getMessage());
}
/*
TODO: enable shifting the whole array
sheet.shiftRows(0, 2, 1);
//the array C1:C3 is now C2:C4
CellRangeAddress cra = CellRangeAddress.valueOf("C2:C4");
for(Cell mcell : mrange){
//TODO define equals and hashcode for CellRangeAddress
assertEquals(cra.formatAsString(), mcell.getArrayFormulaRange().formatAsString());
assertEquals("A2:A4*B2:B4", mcell.getCellFormula());
assertTrue(mcell.isPartOfArrayFormulaGroup());
assertEquals(Cell.CELL_TYPE_FORMULA, mcell.getCellType());
}
*/
}
/** Test that when reading a workbook from input stream, array formulas are recognized */
public final void testReadArrayFormula() {
Cell[] cells;
Workbook workbook = _testDataProvider.createWorkbook();
Sheet sheet1 = workbook.createSheet();
cells =
sheet1
.setArrayFormula("SUM(A1:A3*B1:B3)", CellRangeAddress.valueOf("C4:C6"))
.getFlattenedCells();
assertEquals(3, cells.length);
cells =
sheet1
.setArrayFormula("MAX(A1:A3*B1:B3)", CellRangeAddress.valueOf("A4:A6"))
.getFlattenedCells();
assertEquals(3, cells.length);
Sheet sheet2 = workbook.createSheet();
cells =
sheet2
.setArrayFormula("MIN(A1:A3*B1:B3)", CellRangeAddress.valueOf("D2:D4"))
.getFlattenedCells();
assertEquals(3, cells.length);
workbook = _testDataProvider.writeOutAndReadBack(workbook);
sheet1 = workbook.getSheetAt(0);
for (int rownum = 3; rownum <= 5; rownum++) {
Cell cell1 = sheet1.getRow(rownum).getCell(2);
assertTrue(cell1.isPartOfArrayFormulaGroup());
Cell cell2 = sheet1.getRow(rownum).getCell(0);
assertTrue(cell2.isPartOfArrayFormulaGroup());
}
sheet2 = workbook.getSheetAt(1);
for (int rownum = 1; rownum <= 3; rownum++) {
Cell cell1 = sheet2.getRow(rownum).getCell(3);
assertTrue(cell1.isPartOfArrayFormulaGroup());
}
}
public void testModifyArrayCells_setCellType() {
Workbook workbook = _testDataProvider.createWorkbook();
Sheet sheet = workbook.createSheet();
// single-cell array formulas behave just like normal cells -
// changing cell type removes the array formula and associated cached result
CellRange<? extends Cell> srange =
sheet.setArrayFormula("SUM(A4:A6,B4:B6)", CellRangeAddress.valueOf("B5"));
Cell scell = srange.getTopLeftCell();
assertEquals(Cell.CELL_TYPE_FORMULA, scell.getCellType());
assertEquals(0.0, scell.getNumericCellValue());
scell.setCellType(Cell.CELL_TYPE_STRING);
assertEquals(Cell.CELL_TYPE_STRING, scell.getCellType());
scell.setCellValue("string cell");
assertEquals("string cell", scell.getStringCellValue());
// once you create a multi-cell array formula, you cannot change the type of its cells
CellRange<? extends Cell> mrange =
sheet.setArrayFormula("A1:A3*B1:B3", CellRangeAddress.valueOf("C1:C3"));
for (Cell mcell : mrange) {
try {
assertEquals(Cell.CELL_TYPE_FORMULA, mcell.getCellType());
mcell.setCellType(Cell.CELL_TYPE_NUMERIC);
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 Cell.setCellType leaves the cell
// in the state that it was in prior to the invocation
assertEquals(Cell.CELL_TYPE_FORMULA, mcell.getCellType());
assertTrue(mcell.isPartOfArrayFormulaGroup());
}
}
public void testOneSeriePlot() throws Exception {
Workbook wb = new XSSFWorkbook();
Sheet sheet = new SheetBuilder(wb, plotData).build();
Drawing drawing = sheet.createDrawingPatriarch();
ClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 1, 1, 10, 30);
Chart chart = drawing.createChart(anchor);
ChartAxis bottomAxis = chart.getChartAxisFactory().createValueAxis(AxisPosition.BOTTOM);
ChartAxis leftAxis = chart.getChartAxisFactory().createValueAxis(AxisPosition.LEFT);
ScatterChartData scatterChartData = chart.getChartDataFactory().createScatterChartData();
ChartDataSource<String> xs =
DataSources.fromStringCellRange(sheet, CellRangeAddress.valueOf("A1:J1"));
ChartDataSource<Number> ys =
DataSources.fromNumericCellRange(sheet, CellRangeAddress.valueOf("A2:J2"));
ScatterChartSerie serie = scatterChartData.addSerie(xs, ys);
assertNotNull(serie);
assertEquals(1, scatterChartData.getSeries().size());
assertTrue(scatterChartData.getSeries().contains(serie));
chart.plot(scatterChartData, bottomAxis, leftAxis);
}
/** Test that we can set pre-calculated formula result for array formulas */
public void testModifyArrayCells_setFormulaResult() {
Workbook workbook = _testDataProvider.createWorkbook();
Sheet sheet = workbook.createSheet();
// single-cell array formula
CellRange<? extends Cell> srange =
sheet.setArrayFormula("SUM(A4:A6,B4:B6)", CellRangeAddress.valueOf("B5"));
Cell scell = srange.getTopLeftCell();
assertEquals(Cell.CELL_TYPE_FORMULA, scell.getCellType());
assertEquals(0.0, scell.getNumericCellValue());
scell.setCellValue(1.1);
assertEquals(1.1, scell.getNumericCellValue());
// multi-cell array formula
CellRange<? extends Cell> mrange =
sheet.setArrayFormula("A1:A3*B1:B3", CellRangeAddress.valueOf("C1:C3"));
for (Cell mcell : mrange) {
assertEquals(Cell.CELL_TYPE_FORMULA, mcell.getCellType());
assertEquals(0.0, mcell.getNumericCellValue());
double fmlaResult = 1.2;
mcell.setCellValue(fmlaResult);
assertEquals(fmlaResult, mcell.getNumericCellValue());
}
}
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());
}
private HSSFSheet createWorkSheet(HSSFWorkbook workbook) {
HSSFSheet sheet = workbook.createSheet("TIH-Demurage");
SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();
// Condition 1: Formula Is =A2=A1 (White Font)
ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("MOD(ROW(),2)");
org.apache.poi.ss.usermodel.PatternFormatting fill1 = rule1.createPatternFormatting();
fill1.setFillBackgroundColor(IndexedColors.LIGHT_GREEN.index);
fill1.setFillPattern(PatternFormatting.SOLID_FOREGROUND);
CellRangeAddress[] regions = {CellRangeAddress.valueOf("A1:C5")};
sheetCF.addConditionalFormatting(regions, rule1);
sheet.createRow(0).createCell(1).setCellValue("Shade Alternating Rows");
sheet
.createRow(1)
.createCell(1)
.setCellValue("Condition: Formula Is =MOD(ROW(),2) (Light Green Fill)");
return sheet;
}
@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();
}
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();
}
/**
* Processes requests for both HTTP <code>GET</code> and <code>POST</code> methods.
*
* @param request servlet request
* @param response servlet response
* @throws ServletException if a servlet-specific error occurs
* @throws IOException if an I/O error occurs
*/
protected void processRequest(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
HttpSession ss = request.getSession();
Account ac = (Account) ss.getAttribute("ac");
int cId = Integer.parseInt((Long) ss.getAttribute("cId") + "");
Course c = Course.getCourseByID(cId);
Workbook wb = new XSSFWorkbook();
Map<String, CellStyle> styles = createStyles(wb);
Sheet sheet = wb.createSheet("scoresheet");
PrintSetup printSetup = sheet.getPrintSetup();
printSetup.setLandscape(true);
sheet.setFitToPage(true);
sheet.setHorizontallyCenter(true);
Row titleRow = sheet.createRow(0);
titleRow.setHeightInPoints(45);
Cell titleCell = titleRow.createCell(0);
titleCell.setCellValue("Score sheet of " + c.getName() + " course");
titleCell.setCellStyle(styles.get("title"));
sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$N$1"));
List<Account> listStudentScore = (List<Account>) ss.getAttribute("listStudentScore");
int rownum = 2;
int cellcount = 1;
Row sumRow = sheet.createRow(rownum);
sumRow.setHeightInPoints(55);
Cell cell;
cell = sumRow.createCell(0);
cell.setCellValue("Student name");
cell.setCellStyle(styles.get("header"));
int countback = listStudentScore.get(0).getListStudentScore().size();
int maxScore = 0;
for (int i = countback - 1; i >= 0; i--) {
cell = sumRow.createCell(cellcount);
UserScore u = listStudentScore.get(0).getListStudentScore().get(i);
cell.setCellValue("(" + cellcount + ") " + u.getAm_name() + " (" + u.getFull_mark() + ")");
cell.setCellStyle(styles.get("header"));
cellcount++;
maxScore += u.getFull_mark();
}
cell = sumRow.createCell(cellcount);
cell.setCellValue("Total (" + maxScore + ")");
cell.setCellStyle(styles.get("header"));
rownum++;
for (Account account : listStudentScore) {
sumRow = sheet.createRow(rownum);
sumRow.setHeightInPoints(35);
cell = sumRow.createCell(0);
cell.setCellValue(account.getFirstname() + " " + account.getLastname());
int j = 1;
for (int i = account.getListStudentScore().size() - 1; i >= 0; i--) {
UserScore usc = (UserScore) account.getListStudentScore().get(i);
cell = sumRow.createCell(j);
Assignment a = null;
if (usc.getAss_type().equalsIgnoreCase("web")) {
a = Assignment.getAmTimeByAmID(usc.getStof().getAm_id());
String status = Assignment.lastedSentStatus(usc.getStof().getLasted_send_date(), a);
if (status.equalsIgnoreCase("ontime")
|| status.equalsIgnoreCase("hurryup")
|| status.equalsIgnoreCase("late")) {
cell.setCellValue(usc.getStof().getScore());
} else {
status = Assignment.calculateTime(a);
if (status.equalsIgnoreCase("miss")) {
cell.setCellValue(usc.getStof().getScore());
} else {
cell.setCellValue("-");
}
}
} else if (usc.getAss_type().equalsIgnoreCase("file")) {
a = Assignment.getAmTimeByAmID(usc.getStf().getAm_id());
String status = Assignment.lastedSentStatus(usc.getStf().getLasted_send_date(), a);
if (status.equalsIgnoreCase("ontime")
|| status.equalsIgnoreCase("hurryup")
|| status.equalsIgnoreCase("late")) {
cell.setCellValue(usc.getStf().getScore());
} else {
status = Assignment.calculateTime(a);
if (status.equalsIgnoreCase("miss")) {
cell.setCellValue(usc.getStf().getScore());
} else {
cell.setCellValue("-");
}
}
}
j++;
}
cell = sumRow.createCell(j);
int lastcol = account.getListStudentScore().size();
// calculate column
int dv = lastcol / 26;
String coltmp = "";
for (int i = 0; i < dv; i++) {
coltmp += "A";
}
coltmp += (char) ('A' + (lastcol - (dv * 26)));
System.out.println(coltmp);
//
String ref = (char) ('A' + 1) + "" + (rownum + 1) + ":" + coltmp + (rownum + 1);
System.out.println(ref);
cell.setCellFormula("SUM(" + ref + ")");
rownum++;
}
// Write the output to a file
String filename = "scoresheet_" + c.getName() + ".xlsx";
String file = getServletContext().getRealPath("/") + "/file/scoresheet/" + filename;
// String file = "C:\\Users\\Orarmor\\Desktop\\scoresheet.xlsx";
FileOutputStream out = new FileOutputStream(file);
wb.write(out);
out.close();
response.sendRedirect("file/scoresheet/" + filename);
//
// Workbook wb = new XSSFWorkbook();
// Sheet sheet = wb.createSheet("scoresheet");
// 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("Score sheet of " + "...." + " course");
// sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$D$1"));
//
// //row with totals below
// int rownum = 2;
// Row sumRow = sheet.createRow(rownum);
// sumRow.setHeightInPoints(35);
// Cell cell;
// cell = sumRow.createCell(0);
// cell.setCellValue("Name:");
//
// for (int j = 1; j < 12; j++) {
// cell = sumRow.createCell(j);
// String ref = (char) ('A' + j) + "3:" + (char) ('A' + j) + "12";
// cell.setCellFormula("SUM(" + ref + ")");
// }
//
// // Write the output to a file
// String file = "C:\\Users\\Orarmor\\Desktop\\scoresheet.xlsx";
// FileOutputStream out = new FileOutputStream(file);
// wb.write(out);
// out.close();
}