public static void copyCell(Cell oldCell, Cell newCell, boolean copyStyle) {
if (copyStyle) {
newCell.setCellStyle(oldCell.getCellStyle());
}
switch (oldCell.getCellType()) {
case Cell.CELL_TYPE_STRING:
newCell.setCellValue(oldCell.getRichStringCellValue());
break;
case Cell.CELL_TYPE_NUMERIC:
newCell.setCellValue(oldCell.getNumericCellValue());
break;
case Cell.CELL_TYPE_BLANK:
newCell.setCellType(Cell.CELL_TYPE_BLANK);
break;
case Cell.CELL_TYPE_BOOLEAN:
newCell.setCellValue(oldCell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_ERROR:
newCell.setCellErrorValue(oldCell.getErrorCellValue());
break;
case Cell.CELL_TYPE_FORMULA:
newCell.setCellFormula(oldCell.getCellFormula());
break;
default:
break;
}
}
public void insertFormula(int numRow, int numCol, String formula) {
try {
if (wb.getSheetAt(0) != null) {
Sheet aSheet = wb.getSheetAt(0);
Row row = aSheet.createRow((short) numRow);
Cell csCell = row.createCell((short) numCol);
csCell.setCellFormula(formula);
}
} catch (Exception e) {
System.out.println("insertDataToExcel" + e);
}
}
@SuppressWarnings("unchecked")
private void createBodyRow(Row currentRow, Object[] bodyRow, int[] types) {
Cell currentCell;
// write the section attributes to the row
for (int index = 0; index < bodyRow.length; index++) {
// create the cell
currentCell = currentRow.createCell(index);
// set the format of the cells
currentCell.setCellStyle(bodyStyle);
if (types[index] != Integer.MAX_VALUE) {
currentCell.setCellType(types[index]);
}
// put the value into the cell
switch (types[index]) {
case Cell.CELL_TYPE_NUMERIC:
currentCell.setCellValue((double) bodyRow[index]);
break;
case Cell.CELL_TYPE_FORMULA:
currentCell.setCellFormula(String.valueOf(bodyRow[index]));
break;
case Integer.MAX_VALUE:
HashMap<String, String> cellInfo = (HashMap<String, String>) bodyRow[index];
// sets the hyperlink to be blue and underline
HSSFCellStyle hyperlinkStyle = (HSSFCellStyle) GetBodyCellStyle(workbook);
Font hyperlinkFont = workbook.createFont();
hyperlinkFont.setUnderline(Font.U_SINGLE);
hyperlinkFont.setColor(HSSFColor.BLUE.index);
hyperlinkStyle.setFont(hyperlinkFont);
currentCell.setCellStyle(hyperlinkStyle);
currentCell.setCellValue(cellInfo.get("Label"));
currentCell.setHyperlink(LinkDataToMap(cellInfo));
break; // MAX_VALUE integer will be used to denote hyperlinks
case Cell.CELL_TYPE_STRING:
currentCell.setCellValue(String.valueOf(bodyRow[index]));
break;
default:
throw new UnsupportedOperationException(
"The type "
+ types[index]
+ " is not supported."
+ " Only types 0, 1, 2, and Integer.MAX_VALUE (for hyperlinks)"
+ " are supported. See the javadoc for org.apache.poi.ss.usermodel.Cell "
+ "for more information on the types of cells.");
}
}
}
private void addSumBottom() {
for (int i = 0; i < book.getNumberOfSheets(); i++) {
Sheet sheet = book.getSheetAt(i);
Row row = sheet.createRow(sheet.getLastRowNum() + 1);
row.setHeight((short) (ROW_HEIGHT + 100));
for (int j = 0; j < 1000000; j++) {
if (StringUtils.isBlank(CellUtils.getStringValue(sheet.getRow(0).getCell(j)))
&& StringUtils.isBlank(CellUtils.getStringValue(sheet.getRow(2).getCell(j)))) {
break;
}
Cell cell = row.createCell(j);
cell.setCellStyle(Style.get(book).SUM);
if (j == 0) {
cell.setCellValue("合计");
} else {
cell.setCellValue(0);
}
if (j >= 7) {
cell.setCellType(Cell.CELL_TYPE_FORMULA);
cell.setCellFormula(
String.format(
"SUM(%s%s:%s%s)",
CellUtils.convertToABC(j + 1),
5,
CellUtils.convertToABC(j + 1),
sheet.getLastRowNum()));
}
}
sheet.addMergedRegion(
new CellRangeAddress(sheet.getLastRowNum(), sheet.getLastRowNum(), 0, 6));
}
for (int i = 0; i < book.getNumberOfSheets(); i++) {
Sheet sheet = book.getSheetAt(i);
for (int j = 4; j <= sheet.getLastRowNum(); j++) {
Row row = sheet.getRow(j);
for (int k = 0; k <= row.getLastCellNum(); k++) {
Cell cell = row.getCell(k);
if (cell == null) {
continue;
}
if ("数量".equals(CellUtils.getStringValue(sheet.getRow(2).getCell(k)))) {
cell.setCellStyle(Style.get(book).SUM);
}
}
}
}
}
/** Verify that FormulaParser handles defined names beginning with underscores, see Bug #49640 */
public void testFormulasWithUnderscore() {
HSSFWorkbook wb = new HSSFWorkbook();
Name nm1 = wb.createName();
nm1.setNameName("_score1");
nm1.setRefersToFormula("A1");
Name nm2 = wb.createName();
nm2.setNameName("_score2");
nm2.setRefersToFormula("A2");
Sheet sheet = wb.createSheet();
Cell cell = sheet.createRow(0).createCell(2);
cell.setCellFormula("_score1*SUM(_score1+_score2)");
assertEquals("_score1*SUM(_score1+_score2)", cell.getCellFormula());
}
/**
* Discription:[写一行]
*
* @param row 行
* @param keyValues 一个beanMap对象，包含着这一行中的所有数据
* @param properties 哪些值需要写入行
* @param dateFormat 日期格式，默认：yyyy-MM-dd
* @author:[代超]
* @throws Exception
* @update:[日期YYYY-MM-DD] [更改人姓名][变更描述]
*/
public void writeRow(Row row, Map keyValues, Map properties, String dateFormat, T t)
throws Exception {
// 表格内容样式
CellStyle contentStyle = setContentSheetSysle(row.getSheet().getWorkbook());
if (keyValues == null || keyValues.size() < 1 || row == null) {
return;
}
if (dateFormat == null || "".equals(dateFormat.trim())) {
dateFormat = "yyyy-MM-dd";
}
Iterator it = keyValues.entrySet().iterator();
for (int i = 0; it.hasNext(); i++) {
Map.Entry next = (Map.Entry) it.next();
String dataValue = ObjectUtils.toString(properties.get(next.getKey()), "");
if (dataValue == null || "".equals(dataValue.trim())) {
// 无需导出当前字段
i--;
continue;
}
Object value = keyValues.get(next.getKey());
// 值为空时仍然创建单元格并且赋予样式。
Cell cell = row.createCell(i);
cell.setCellStyle(contentStyle);
if (value == null) {
// 当值为空的时候，不必做其他操作了
continue;
}
if (dataValue.toLowerCase().indexOf("formula") > -1) {
// 公式
String formula =
ExcelFormula.parseFormula(value.toString())
.replaceAll("-1", String.valueOf(cell.getRowIndex() + 1));
cell.setCellFormula(formula);
} else {
Class c = PropertyUtils.getPropertyType(t, next.getKey().toString());
writeCell(cell, value, c.getSimpleName(), dateFormat);
}
}
}
public void writeFormulas(FormulaResolver formulaResolver) {
Set sheetNames = sheetFormulasMap.keySet();
for (Iterator iterator = sheetNames.iterator(); iterator.hasNext(); ) {
String sheetName = (String) iterator.next();
List formulas = (List) sheetFormulasMap.get(sheetName);
for (int i = 0; i < formulas.size(); i++) {
Formula formula = (Formula) formulas.get(i);
String formulaString = formulaResolver.resolve(formula, null);
if (formulaString != null) {
Cell hssfCell =
Util.getOrCreateCell(
formula.getSheet().getPoiSheet(), formula.getRowNum(), formula.getCellNum());
try {
hssfCell.setCellFormula(formulaString);
} catch (RuntimeException e) {
log.error("Can't set formula: " + formulaString, e);
// hssfCell.setCellType( Cell.CELL_TYPE_BLANK );
throw new RuntimeException("Can't set formula: " + formulaString, e);
}
}
}
}
}
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();
}
/**
* 资产明细表
*
* @param response
* @param customer_2
* @param customer_0or1
* @throws IOException
*/
@RequestMapping("/report/frontequip/exportFrontEquipListReport_assetclean.do")
public void exportFrontEquipListReport_assetclean(
HttpServletResponse response, String customer_2, String customer_0or1) throws IOException {
String customer_2_name = customerService.get(customer_2).getName();
String customer_0or1_name = customerService.get(customer_0or1).getName();
List<FrontEquipListReport> list =
frontEquipReportRepository.queryFrontEquipListReport(customer_2, customer_0or1);
List<FrontEquipListReport_subtype> list_subtype_prod =
frontEquipReportRepository.queryFrontEquipListReport_header(customer_2, customer_0or1);
XSSFWorkbook wb = new XSSFWorkbook();
Sheet sheet = wb.createSheet();
int rownum = 0;
// 标题
Row title = sheet.createRow(rownum++);
Cell title_cell = title.createCell(0);
title_cell.setCellValue(customer_2_name + customer_0or1_name + "前端设备明细表");
CellStyle title_style = wb.createCellStyle();
Font title_font = wb.createFont();
title_font.setFontHeightInPoints((short) 16);
// f.setColor(IndexedColors.RED.getIndex());
title_font.setBoldweight(Font.BOLDWEIGHT_BOLD);
title_style.setFont(title_font);
title_style.setAlignment(CellStyle.ALIGN_CENTER);
title_style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
title_cell.setCellStyle(title_style);
// 和并单元格
// sheet.addMergedRegion(new CellRangeAddress(0, (short) 0, 0, (short) 15));
// ===========================================================================================
rownum = exportFrontEquipListReport_header_assetclean(list_subtype_prod, rownum, wb, sheet);
// =============================================================================================
// 开始构建整个excel的文件
// 处理对应的品名在excel的哪一列
Map<String, Integer> prod_col_index_map = new HashMap<String, Integer>();
int cellIndex = 3;
for (FrontEquipListReport_subtype subtype : list_subtype_prod) {
for (FrontEquipListReport_prod prod : subtype.getProds()) {
prod_col_index_map.put(prod.getProd_id(), cellIndex);
cellIndex = cellIndex + 2;
}
}
if (list != null && list.size() > 0) {
int i = 1;
for (FrontEquipListReport customer : list) {
Row row = sheet.createRow(rownum++);
Cell cell_prod_0 = row.createCell(0);
cell_prod_0.setCellValue(i);
// cell_prod_0.setCellStyle(style);
Cell cell_prod_1 = row.createCell(1);
cell_prod_1.setCellValue(customer.getPole_code());
Cell cell_prod_2 = row.createCell(2);
cell_prod_2.setCellValue(customer.getPole_name());
for (FrontEquipListReport_prod prod : customer.getProdes()) {
Cell cell_prod = row.createCell(prod_col_index_map.get(prod.getProd_id()));
cell_prod.setCellValue(prod.getNum());
// cell_prod.setCellStyle(style);
cell_prod = row.createCell(prod_col_index_map.get(prod.getProd_id()) + 1);
cell_prod.setCellValue(prod.getValue_net().doubleValue());
}
i++;
}
// 对标题行 进行单元格合并
sheet.addMergedRegion(
new CellRangeAddress(0, (short) 0, 0, (short) prod_col_index_map.size()));
}
sheet.createFreezePane(3, 3);
// 添加总计一行
CellStyle style_sum = wb.createCellStyle();
Font style_sum_font = wb.createFont();
style_sum_font.setFontHeightInPoints((short) 12);
// f.setColor(IndexedColors.RED.getIndex());
style_sum_font.setBoldweight(Font.BOLDWEIGHT_BOLD);
style_sum.setFont(style_sum_font);
style_sum.setAlignment(CellStyle.ALIGN_RIGHT);
style_sum.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
Row row_sum = sheet.createRow(rownum++);
Cell cell_sum_2 = row_sum.createCell(2);
cell_sum_2.setCellValue("小计:");
cell_sum_2.setCellStyle(style_sum);
for (int i = 3; i < cellIndex; i++) {
String col = CellReference.convertNumToColString(i);
Cell cell_sum = row_sum.createCell(i);
// =SUM(C4:C29) 从第4行开始到最后一样
// cell_sum.setCellValue("SUM("+col+"4:"+col+(rownum-1)+")");
cell_sum.setCellFormula("SUM(" + col + "5:" + col + (rownum - 1) + ")");
cell_sum.setCellStyle(style_sum);
Cell cell_net = row_sum.createCell(i);
cell_net.setCellFormula("SUM(" + col + "5:" + col + (rownum - 1) + ")");
cell_net.setCellStyle(style_sum);
}
String filename = customer_2_name + customer_0or1_name + "前端设备明细表-净资产.xlsx";
// FileOutputStream out = new FileOutputStream(filename);
response.setHeader(
"content-disposition",
"attachment; filename=" + new String(filename.getBytes("UTF-8"), "ISO8859-1"));
// response.setContentType("application/vnd.ms-excel;charset=uft-8");
response.setContentType(
"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=uft-8");
OutputStream out = response.getOutputStream();
wb.write(out);
out.flush();
out.close();
}
private void rebuildFormula(Sheet sheet) {
ProductType type = ProductType.get(sheet.getSheetName());
SizeInfo sizeInfo = SizeInfo.getByType(type);
// size -> list<columnIndex>
Map<String, List<Integer>> columns = new HashMap<String, List<Integer>>();
for (int colIndex = 6 + sizeInfo.getSizeNumber() + 1;
colIndex < sheet.getRow(2).getLastCellNum();
colIndex++) {
Row row = sheet.getRow(2);
String value = row.getCell(colIndex).getStringCellValue().trim();
if ("数量".equals(value) || StringUtils.isBlank(value)) {
continue;
}
if (!columns.containsKey(value)) {
columns.put(value, new ArrayList<Integer>());
}
columns.get(value).add(colIndex);
}
// for(int rowIndex = 3; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
// Row row = sheet.getRow(rowIndex);
//
// for(int colIndex = 7; colIndex <= 7 + sizeRange - 1; colIndex++) {
// String size = sheet.getRow(2).getCell(colIndex).getStringCellValue().trim();
//
// List<Integer> cols = columns.get(size);
// row.getCell(colIndex).setCellType(Cell.CELL_TYPE_FORMULA);
// row.getCell(colIndex).setCellFormula(this.buildSumFormula(rowIndex, cols));
// }
// }
for (int rowIndex = 4; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
Row row = sheet.getRow(rowIndex);
if (row == null) {
continue;
}
for (int colIndex = 7; colIndex <= row.getLastCellNum(); colIndex++) {
if ("数量".equals(CellUtils.getStringValue(sheet.getRow(2).getCell(colIndex)))) {
Cell cell = row.getCell(colIndex);
cell.setCellType(Cell.CELL_TYPE_FORMULA);
String formula =
String.format(
"SUM(%s%s:%s%s)",
CellUtils.convertToABC(colIndex - sizeInfo.getSizeNumber() + 1),
rowIndex + 1,
CellUtils.convertToABC(colIndex - 1 + 1),
rowIndex + 1);
cell.setCellFormula(formula);
}
}
}
for (int rowIndex = 4; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
Row row = sheet.getRow(rowIndex);
if ("合计".equals(CellUtils.getStringValue(row.getCell(0)))) {
for (int colIndex = 7; colIndex <= sheet.getLastRowNum(); colIndex++) {
Cell cell = row.getCell(colIndex);
if (cell == null) {
continue;
}
cell.setCellType(Cell.CELL_TYPE_FORMULA);
String formula =
String.format(
"SUM(%s%s:%s%s)",
CellUtils.convertToABC(colIndex + 1),
5,
CellUtils.convertToABC(colIndex + 1),
rowIndex);
cell.setCellFormula(formula);
}
}
}
}
void excelCopy() {
FileInputStream file1;
int lastRowUpdated = 0, currentRow = 0;
int i = 0;
try {
file1 =
new FileInputStream(
"C:\\Users\\SharvaP\\Desktop\\Excel Format\\LookUp for Transaction Name.xls");
int columnIndex = 0;
Workbook lookupWorkbook = new HSSFWorkbook(file1);
Sheet lookupSheet = lookupWorkbook.getSheetAt(0);
FileInputStream file2 =
new FileInputStream("C:\\Users\\SharvaP\\Desktop\\Excel Format\\round1_temp.xls");
Workbook round1Workbook = new HSSFWorkbook(file2);
Sheet round1Sheet = round1Workbook.getSheetAt(0);
FileInputStream file3 =
new FileInputStream("C:\\Users\\SharvaP\\Desktop\\Excel Format\\template.xls");
Workbook templateWorkbook = new HSSFWorkbook(file3);
Sheet templateSheet = templateWorkbook.getSheetAt(0);
FileInputStream file4 =
new FileInputStream("C:\\Users\\SharvaP\\Desktop\\Excel Format\\round2_temp.xls");
Workbook round2Workbook = new HSSFWorkbook(file4);
Sheet round2Sheet = round2Workbook.getSheetAt(0);
int averageRow = round1Sheet.getPhysicalNumberOfRows() - 2;
int percentileRow = round1Sheet.getPhysicalNumberOfRows() - 1;
System.out.println("Average row1:" + averageRow);
int averageRow2 = round2Sheet.getPhysicalNumberOfRows() - 2;
int percentileRow2 = round2Sheet.getPhysicalNumberOfRows() - 1;
/*Row round1Row = round1Sheet.getRow(averageRow);
Row round2Row = round2Sheet.getRow(averageRow2);
Cell tempRound1Cell=round1Row.getCell(columnIndex);
Cell tempRound2Cell=round2Row.getCell(columnIndex);
CellStyle cellStyle = templateWorkbook.createCellStyle();
CreationHelper createHelper=templateWorkbook.getCreationHelper();
cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("#"));
*/
Iterator<Row> rowIterator1 = lookupSheet.iterator();
while (rowIterator1.hasNext()) {
Row lookupRow = rowIterator1.next();
if (lookupRow.getRowNum() > 0) {
Cell lookupCell = lookupRow.getCell(1);
String transationNameTemplate = lookupCell.getStringCellValue();
// System.out.println("NAme:"+transationNameTemplate);
// System.out.println(templateSheet.getPhysicalNumberOfRows());
for (int count = lastRowUpdated;
count < templateSheet.getPhysicalNumberOfRows() - 1;
count++) {
Row templateRow = templateSheet.getRow(count);
Cell templateCell = templateRow.getCell(0);
Cell templateCellFormulae1 = templateRow.getCell(5);
Cell templateCellFormulae2 = templateRow.getCell(6);
String templateCellName = templateCell.getStringCellValue();
// System.out.println("template cell name:"+templateCellName);
currentRow = templateRow.getRowNum();
if (templateCellName.equals(transationNameTemplate)) {
// System.out.println("Transac Name:"+templateCellName+", row num:"+currentRow+", last
// updated row:"+lastRowUpdated);
Row round1RowAverage = round1Sheet.getRow(averageRow);
Cell round1CellAverage = round1RowAverage.getCell(columnIndex);
Row round2RowAverage = round2Sheet.getRow(averageRow2);
Cell round2CellAverage = round2RowAverage.getCell(columnIndex);
Cell templateCellRound1Average = templateRow.getCell(1);
Cell templateCellRound2Average = templateRow.getCell(3);
templateCellFormulae1.setCellFormula(
"IF(B"
+ (currentRow + 1)
+ ">D"
+ (currentRow + 1)
+ ",((B"
+ (currentRow + 1)
+ "-D"
+ (currentRow + 1)
+ ")/B"
+ (currentRow + 1)
+ ")*100,((B"
+ (currentRow + 1)
+ "-D"
+ (currentRow + 1)
+ ")/D"
+ (currentRow + 1)
+ ")*100)");
System.out.println(round1CellAverage.getNumericCellValue());
templateCellRound1Average.setCellValue(round1CellAverage.getNumericCellValue());
templateCellRound2Average.setCellValue(round2CellAverage.getNumericCellValue());
Row round1RowPercentile = round1Sheet.getRow(percentileRow);
Cell round1CellPercentile = round1RowPercentile.getCell(columnIndex);
Row round2RowPercentile = round2Sheet.getRow(percentileRow2);
Cell round2CellPercentile = round2RowPercentile.getCell(columnIndex);
Cell templateCellRound1Percentile = templateRow.getCell(2);
Cell templateCellRound2Percentile = templateRow.getCell(4);
templateCellFormulae2.setCellFormula(
"IF(C"
+ (currentRow + 1)
+ ">E"
+ (currentRow + 1)
+ ",((C"
+ (currentRow + 1)
+ "-E"
+ (currentRow + 1)
+ ")/C"
+ (currentRow + 1)
+ ")*100,((C"
+ (currentRow + 1)
+ "-E"
+ (currentRow + 1)
+ ")/E"
+ (currentRow + 1)
+ ")*100)");
templateCellRound1Percentile.setCellValue(round1CellPercentile.getNumericCellValue());
templateCellRound2Percentile.setCellValue(round2CellPercentile.getNumericCellValue());
// System.out.println("round1 avg value:"+round1CellAverage.getNumericCellValue());
// System.out.println("round2 avg value:"+round2CellAverage.getNumericCellValue());
// System.out.println("Match Found"+templateRow.getCell(1).getNumericCellValue());
columnIndex++;
lastRowUpdated = currentRow;
break;
}
}
}
}
FileOutputStream out =
new FileOutputStream("C:\\Users\\SharvaP\\Desktop\\Excel Format\\Template.xls");
templateWorkbook.write(out);
out.close();
System.out.println("Finished....");
file1.close();
file2.close();
file3.close();
file4.close();
} catch (FileNotFoundException e) {
System.out.println(e.getMessage());
e.printStackTrace();
} catch (IOException e) {
System.out.println(e.getMessage());
e.printStackTrace();
}
}
/**
* 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();
}
/** Adds in a Row to the given Sheet */
public Row addRow(
Workbook wb,
SheetToAdd sheetToAdd,
RowToAdd rowToAdd,
int rowIndex,
ReportData reportData,
ReportDesign design,
Map<String, String> repeatSections) {
// Create a new row and copy over style attributes from the row to add
Row newRow = sheetToAdd.getSheet().createRow(rowIndex);
Row rowToClone = rowToAdd.getRowToClone();
try {
CellStyle rowStyle = rowToClone.getRowStyle();
if (rowStyle != null) {
newRow.setRowStyle(rowStyle);
}
} catch (Exception e) {
// No idea why this is necessary, but this has thrown IndexOutOfBounds errors getting the
// rowStyle. Mysteries of POI
}
newRow.setHeight(rowToClone.getHeight());
// Iterate across all of the cells in the row, and configure all those that need to be
// added/cloned
List<CellToAdd> cellsToAdd = new ArrayList<CellToAdd>();
int totalCells = rowToClone.getPhysicalNumberOfCells();
int cellsFound = 0;
for (int cellNum = 0; cellsFound < totalCells; cellNum++) {
Cell currentCell = rowToClone.getCell(cellNum);
log.debug("Handling cell: " + currentCell);
if (currentCell != null) {
cellsFound++;
}
// If we find that the cell that we are on is a repeating cell, then add the appropriate
// number of cells to clone
String repeatingColumnProperty =
getRepeatingColumnProperty(sheetToAdd.getOriginalSheetNum(), cellNum, repeatSections);
if (repeatingColumnProperty != null) {
String[] dataSetSpanSplit = repeatingColumnProperty.split(",");
String dataSetName = dataSetSpanSplit[0];
DataSet dataSet = getDataSet(reportData, dataSetName, rowToAdd.getReplacementData());
int numCellsToRepeat = 1;
if (dataSetSpanSplit.length == 2) {
numCellsToRepeat = Integer.parseInt(dataSetSpanSplit[1]);
}
log.debug(
"Repeating this cell with dataset: " + dataSet + " and repeat of " + numCellsToRepeat);
int repeatNum = 0;
for (DataSetRow dataSetRow : dataSet) {
repeatNum++;
for (int i = 0; i < numCellsToRepeat; i++) {
Cell cell = (i == 0 ? currentCell : rowToClone.getCell(cellNum + i));
if (repeatNum == 1 && cell != null && cell != currentCell) {
cellsFound++;
}
Map<String, Object> newReplacements =
getReplacementData(
rowToAdd.getReplacementData(),
reportData,
design,
dataSetName,
dataSetRow,
repeatNum);
cellsToAdd.add(new CellToAdd(cell, newReplacements));
log.debug("Adding " + cell + " with dataSetRow: " + dataSetRow);
}
}
cellNum += numCellsToRepeat;
} else {
cellsToAdd.add(new CellToAdd(currentCell, rowToAdd.getReplacementData()));
log.debug("Adding " + currentCell);
}
}
// Now, go through all of the collected cells, and add them back in
ExcelStyleHelper styleHelper = new ExcelStyleHelper(wb);
String prefix = getExpressionPrefix(design);
String suffix = getExpressionSuffix(design);
for (int i = 0; i < cellsToAdd.size(); i++) {
CellToAdd cellToAdd = cellsToAdd.get(i);
Cell newCell = newRow.createCell(i);
Cell cellToClone = cellToAdd.getCellToClone();
if (cellToClone != null) {
String contents = ExcelUtil.getCellContentsAsString(cellToClone);
newCell.setCellStyle(cellToClone.getCellStyle());
try {
newCell.setCellFormula(cellToClone.getCellFormula());
} catch (Exception e) {
// Do nothing here. I don't know why POI throw exceptions here when the cell is not a
// formula, but this suppresses them...
}
int numFormattings =
sheetToAdd.getSheet().getSheetConditionalFormatting().getNumConditionalFormattings();
for (int n = 0; n < numFormattings; n++) {
ConditionalFormatting f =
sheetToAdd.getSheet().getSheetConditionalFormatting().getConditionalFormattingAt(n);
for (CellRangeAddress add : f.getFormattingRanges()) {
if (add.getFirstRow() == rowToAdd.getRowToClone().getRowNum()
&& add.getLastRow() == rowToClone.getRowNum()) {
if (add.getFirstColumn() == cellToClone.getColumnIndex()
&& add.getLastColumn() == cellToClone.getColumnIndex()) {
ConditionalFormattingRule[] rules =
new ConditionalFormattingRule[f.getNumberOfRules()];
for (int j = 0; j < f.getNumberOfRules(); j++) {
rules[j] = f.getRule(j);
}
CellRangeAddress[] cellRange = new CellRangeAddress[1];
cellRange[0] = new CellRangeAddress(rowIndex, rowIndex, i, i);
sheetToAdd
.getSheet()
.getSheetConditionalFormatting()
.addConditionalFormatting(cellRange, rules);
}
}
}
}
if (ObjectUtil.notNull(contents)) {
Object newContents =
EvaluationUtil.evaluateExpression(
contents, cellToAdd.getReplacementData(), prefix, suffix);
ExcelUtil.setCellContents(styleHelper, newCell, newContents);
}
}
}
return newRow;
}
