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");
}
private void printSetup(Sheet oldSheet, Sheet newSheet) {
// print setup
PrintSetup oldPS = oldSheet.getPrintSetup();
PrintSetup newPS = newSheet.getPrintSetup();
newPS.setPaperSize(oldPS.getPaperSize());
newPS.setScale(oldPS.getScale());
newPS.setPageStart(oldPS.getPageStart());
newPS.setFitWidth(oldPS.getFitWidth());
newPS.setFitHeight(oldPS.getFitHeight());
newPS.setLeftToRight(oldPS.getLeftToRight());
newPS.setLandscape(oldPS.getLandscape());
newPS.setValidSettings(oldPS.getValidSettings());
newPS.setNoColor(oldPS.getNoColor());
newPS.setDraft(oldPS.getDraft());
newPS.setNotes(oldPS.getNotes());
newPS.setNoOrientation(oldPS.getNoOrientation());
newPS.setUsePage(oldPS.getUsePage());
newPS.setHResolution(oldPS.getHResolution());
newPS.setVResolution(oldPS.getVResolution());
newPS.setHeaderMargin(oldPS.getHeaderMargin());
newPS.setFooterMargin(oldPS.getFooterMargin());
newPS.setCopies(oldPS.getCopies());
newSheet.setRightToLeft(oldSheet.isRightToLeft());
newSheet.setHorizontallyCenter(oldSheet.getHorizontallyCenter());
newSheet.setVerticallyCenter(oldSheet.getVerticallyCenter());
newSheet.setAutobreaks(oldSheet.getAutobreaks());
newSheet.setDisplayZeros(oldSheet.isDisplayZeros());
newSheet.setFitToPage(oldSheet.getFitToPage());
newSheet.setAutobreaks(oldSheet.getAutobreaks());
newSheet.setPrintGridlines(oldSheet.isPrintGridlines());
newSheet.getHeader().setCenter(oldSheet.getHeader().getCenter());
newSheet.getHeader().setLeft(oldSheet.getHeader().getLeft());
newSheet.getHeader().setRight(oldSheet.getHeader().getRight());
newSheet.getFooter().setCenter(oldSheet.getFooter().getCenter());
newSheet.getFooter().setLeft(oldSheet.getFooter().getLeft());
newSheet.getFooter().setRight(oldSheet.getFooter().getRight());
newSheet.setMargin(Sheet.LeftMargin, oldSheet.getMargin(Sheet.LeftMargin));
newSheet.setMargin(Sheet.RightMargin, oldSheet.getMargin(Sheet.RightMargin));
newSheet.setMargin(Sheet.HeaderMargin, oldSheet.getMargin(Sheet.HeaderMargin));
newSheet.setMargin(Sheet.FooterMargin, oldSheet.getMargin(Sheet.FooterMargin));
newSheet.setMargin(Sheet.BottomMargin, oldSheet.getMargin(Sheet.BottomMargin));
newSheet.setMargin(Sheet.TopMargin, oldSheet.getMargin(Sheet.TopMargin));
}
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();
}
// Define A method for Creating Excel File
public String createExcel(
String titleString,
String sheetString,
String subTitleString,
List data,
String[] headerTitles,
String titleKey,
String filePath) {
String file = null;
Workbook wb;
int col = 0;
try {
// check Header Title
if (headerTitles != null && headerTitles.length > 0) col = headerTitles.length;
wb = (Workbook) new HSSFWorkbook();
// Hear we are getting whole property
List<ConfigurationUtilBean> titleMap = new CustomerCommonPropertyMap().getTitles(titleKey);
Map<String, CellStyle> styles = createStyles(wb);
Sheet sheet = wb.createSheet(sheetString);
PrintSetup printSetup = sheet.getPrintSetup();
printSetup.setLandscape(true);
sheet.setFitToPage(true);
sheet.setHorizontallyCenter(true);
Header header = sheet.getHeader();
header.setCenter("Center Header");
header.setLeft("Left Header");
header.setRight("Right Footer");
Footer footer = sheet.getFooter();
footer.setCenter("center footer");
footer.setLeft("left footer");
footer.setRight("right footer");
// Title Row....
Row titleRow = sheet.createRow(0);
titleRow.setHeightInPoints(20);
Cell titleCell = titleRow.createCell(0);
titleCell.setCellValue(titleString);
titleCell.setCellStyle(styles.get("title"));
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, col - 1));
// Sub Title Row.....
// System.out.println("Sub Title String >>>>>>"+subTitleString);
Row headerRow = null;
if (subTitleString != "") {
Row subTitleRow = sheet.createRow(1);
subTitleRow.setHeightInPoints(18);
Cell subTitleCell = subTitleRow.createCell(0);
subTitleCell.setCellValue(subTitleString);
subTitleCell.setCellStyle(styles.get("subTitle"));
sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, col - 1));
headerRow = sheet.createRow(2);
headerRow.setHeightInPoints(15);
//
} else {
headerRow = sheet.createRow(1);
headerRow.setHeightInPoints(15);
}
Cell headerCell = null;
if (headerTitles != null) {
for (ConfigurationUtilBean cell : titleMap) {
int titleIndex = 0;
for (int i = 0; i < headerTitles.length; i++) {
if (cell.getKey().equalsIgnoreCase(headerTitles[titleIndex].trim())) {
headerCell = headerRow.createCell(titleIndex);
headerCell.setCellValue(cell.getValue());
headerCell.setCellStyle(styles.get("header"));
}
titleIndex++;
}
}
}
Row dataRow = null;
Cell dataCell = null;
int rowIndex = 2;
/* List Iteration text */
try {
if (data != null && data.size() > 0) {
for (Iterator it = data.iterator(); it.hasNext(); ) {
Object[] obdata = (Object[]) it.next();
dataRow = sheet.createRow(rowIndex);
for (int cellIndex = 0; cellIndex < headerTitles.length; cellIndex++) {
dataCell = dataRow.createCell(cellIndex);
if (obdata[cellIndex] != null && !obdata[cellIndex].toString().equalsIgnoreCase("")) {
dataCell.setCellValue(obdata[cellIndex].toString());
} else {
dataCell.setCellValue("NA");
}
}
rowIndex++;
}
}
} catch (Exception e) {
// TODO: handle exception
}
for (int titleIndex = 0; titleIndex < headerTitles.length; titleIndex++)
sheet.autoSizeColumn(titleIndex); // adjust width of the column
file =
filePath
+ File.separator
+ "OpportunityReportDetail_"
+ DateUtil.getCurrentDateIndianFormat()
+ (DateUtil.getCurrentTimeHourMin()).replaceAll(":", "-")
+ ".xls";
if (wb instanceof XSSFWorkbook) file += "x";
FileOutputStream out = new FileOutputStream(file);
wb.write(out);
out.close();
} catch (Exception e) {
e.printStackTrace();
} finally {
}
return file;
}
// Define A method for Creating Excel File
public String createExcelformate(
String titleString,
String sheetString,
String[] headerTitles,
String titleKey,
String filePath) {
String file = null;
Workbook wb;
int col = 0;
try {
// check Header Title
if (headerTitles != null && headerTitles.length > 0) col = headerTitles.length;
wb = (Workbook) new HSSFWorkbook();
// Hear we are getting whole property
List<ConfigurationUtilBean> titleMap = new CustomerCommonPropertyMap().getTitles(titleKey);
Map<String, CellStyle> styles = createStyles(wb);
Sheet sheet = wb.createSheet(sheetString);
PrintSetup printSetup = sheet.getPrintSetup();
printSetup.setLandscape(true);
sheet.setFitToPage(true);
sheet.setHorizontallyCenter(true);
Header header = sheet.getHeader();
header.setCenter("Center Header");
header.setLeft("Left Header");
header.setRight("Right Footer");
Footer footer = sheet.getFooter();
footer.setCenter("center footer");
footer.setLeft("left footer");
footer.setRight("right footer");
// Title Row....
Row titleRow = sheet.createRow(0);
titleRow.setHeightInPoints(20);
Cell titleCell = titleRow.createCell(0);
titleCell.setCellValue(titleString);
titleCell.setCellStyle(styles.get("title"));
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, col - 1));
//
Row headerRow = sheet.createRow(1);
headerRow.setHeightInPoints(15);
Cell headerCell = null;
if (headerTitles != null) {
for (ConfigurationUtilBean cell : titleMap) {
int titleIndex = 0;
for (int i = 0; i < headerTitles.length; i++) {
if (cell.getKey().equalsIgnoreCase(headerTitles[titleIndex].trim())) {
headerCell = headerRow.createCell(titleIndex);
headerCell.setCellValue(cell.getValue());
headerCell.setCellStyle(styles.get("header"));
}
titleIndex++;
}
}
}
for (int titleIndex = 0; titleIndex < headerTitles.length; titleIndex++)
sheet.autoSizeColumn(titleIndex); // adjust width of the column
file =
filePath
+ File.separator
+ "ContactReport"
+ DateUtil.getCurrentDateIndianFormat()
+ (DateUtil.getCurrentTime()).replaceAll(":", "-")
+ ".xls";
if (wb instanceof XSSFWorkbook) file += "x";
FileOutputStream out = new FileOutputStream(file);
wb.write(out);
out.close();
} catch (Exception e) {
e.printStackTrace();
} finally {
}
return file;
}
/**
* 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();
}