List of usage examples for org.apache.poi.ss.usermodel Row setHeightInPoints
void setHeightInPoints(float height);
From source file:demo.poi.LoanCalculator.java
License:Apache License
public static void main(String[] args) throws Exception { Workbook wb;/* www. j a v a 2 s .c o m*/ if (args.length > 0 && args[0].equals("-xls")) wb = new HSSFWorkbook(); else wb = new HSSFWorkbook(); Map<String, CellStyle> 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, 3 * 256); sheet.setColumnWidth(1, 3 * 256); sheet.setColumnWidth(2, 11 * 256); sheet.setColumnWidth(3, 14 * 256); sheet.setColumnWidth(4, 14 * 256); sheet.setColumnWidth(5, 14 * 256); sheet.setColumnWidth(6, 14 * 256); createNames(wb); Row titleRow = sheet.createRow(0); titleRow.setHeightInPoints(35); for (int i = 1; i <= 7; i++) { titleRow.createCell(i).setCellStyle(styles.get("title")); } Cell titleCell = titleRow.getCell(2); titleCell.setCellValue("Simple Loan Calculator"); sheet.addMergedRegion(CellRangeAddress.valueOf("$C$1:$H$1")); Row row = sheet.createRow(2); Cell cell = row.createCell(4); cell.setCellValue("Enter values"); cell.setCellStyle(styles.get("item_right")); row = sheet.createRow(3); cell = row.createCell(2); cell.setCellValue("Loan amount"); cell.setCellStyle(styles.get("item_left")); cell = row.createCell(4); cell.setCellStyle(styles.get("input_$")); cell.setAsActiveCell(); row = sheet.createRow(4); cell = row.createCell(2); cell.setCellValue("Annual interest rate"); cell.setCellStyle(styles.get("item_left")); cell = row.createCell(4); cell.setCellStyle(styles.get("input_%")); row = sheet.createRow(5); cell = row.createCell(2); cell.setCellValue("Loan period in years"); cell.setCellStyle(styles.get("item_left")); cell = row.createCell(4); cell.setCellStyle(styles.get("input_i")); row = sheet.createRow(6); cell = row.createCell(2); cell.setCellValue("Start date of loan"); cell.setCellStyle(styles.get("item_left")); cell = row.createCell(4); cell.setCellStyle(styles.get("input_d")); row = sheet.createRow(8); cell = row.createCell(2); cell.setCellValue("Monthly payment"); cell.setCellStyle(styles.get("item_left")); cell = row.createCell(4); cell.setCellFormula("IF(Values_Entered,Monthly_Payment,\"\")"); cell.setCellStyle(styles.get("formula_$")); row = sheet.createRow(9); cell = row.createCell(2); cell.setCellValue("Number of payments"); cell.setCellStyle(styles.get("item_left")); cell = row.createCell(4); cell.setCellFormula("IF(Values_Entered,Loan_Years*12,\"\")"); cell.setCellStyle(styles.get("formula_i")); row = sheet.createRow(10); cell = row.createCell(2); cell.setCellValue("Total interest"); cell.setCellStyle(styles.get("item_left")); cell = row.createCell(4); cell.setCellFormula("IF(Values_Entered,Total_Cost-Loan_Amount,\"\")"); cell.setCellStyle(styles.get("formula_$")); row = sheet.createRow(11); cell = row.createCell(2); cell.setCellValue("Total cost of loan"); cell.setCellStyle(styles.get("item_left")); cell = row.createCell(4); cell.setCellFormula("IF(Values_Entered,Monthly_Payment*Number_of_Payments,\"\")"); cell.setCellStyle(styles.get("formula_$")); // Write the output to a file String file = "target/loan-calculator.xls"; FileOutputStream out = new FileOutputStream(file); wb.write(out); out.close(); }
From source file:demo.poi.TimesheetDemo.java
License:Apache License
public static void main(String[] args) throws Exception { Workbook wb;//from w w w . j a v a2s .c om if (args.length > 0 && args[0].equals("-xls")) wb = new HSSFWorkbook(); else wb = new HSSFWorkbook(); 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 = "target/timesheet.xls"; FileOutputStream out = new FileOutputStream(file); wb.write(out); out.close(); }
From source file:Demos.CalendarDemo.java
License:Apache License
public static void main(String[] args) throws Exception { Calendar calendar = Calendar.getInstance(); boolean xlsx = true; for (int i = 0; i < args.length; i++) { if (args[i].charAt(0) == '-') { xlsx = args[i].equals("-xlsx"); } else {// w w w. j ava2 s .c o m calendar.set(Calendar.YEAR, Integer.parseInt(args[i])); } } int year = calendar.get(Calendar.YEAR); Workbook wb = xlsx ? new XSSFWorkbook() : new HSSFWorkbook(); Map<String, CellStyle> styles = createStyles(wb); for (int month = 0; month < 12; month++) { calendar.set(Calendar.MONTH, month); calendar.set(Calendar.DAY_OF_MONTH, 1); // create a sheet for each month Sheet sheet = wb.createSheet(months[month]); // turn off gridlines sheet.setDisplayGridlines(false); sheet.setPrintGridlines(false); sheet.setFitToPage(true); sheet.setHorizontallyCenter(true); PrintSetup printSetup = sheet.getPrintSetup(); printSetup.setLandscape(true); // the following three statements are required only for HSSF sheet.setAutobreaks(true); printSetup.setFitHeight((short) 1); printSetup.setFitWidth((short) 1); // the header row: centered text in 48pt font Row headerRow = sheet.createRow(0); headerRow.setHeightInPoints(80); Cell titleCell = headerRow.createCell(0); titleCell.setCellValue(months[month] + " " + year); titleCell.setCellStyle(styles.get("title")); sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$N$1")); // header with month titles Row monthRow = sheet.createRow(1); for (int i = 0; i < days.length; i++) { // set column widths, the width is measured in units of 1/256th // of a character width sheet.setColumnWidth(i * 2, 5 * 256); // the column is 5 // characters wide sheet.setColumnWidth(i * 2 + 1, 13 * 256); // the column is 13 // characters wide sheet.addMergedRegion(new CellRangeAddress(1, 1, i * 2, i * 2 + 1)); Cell monthCell = monthRow.createCell(i * 2); monthCell.setCellValue(days[i]); monthCell.setCellStyle(styles.get("month")); } int cnt = 1, day = 1; int rownum = 2; for (int j = 0; j < 6; j++) { Row row = sheet.createRow(rownum++); row.setHeightInPoints(100); for (int i = 0; i < days.length; i++) { Cell dayCell_1 = row.createCell(i * 2); Cell dayCell_2 = row.createCell(i * 2 + 1); int day_of_week = calendar.get(Calendar.DAY_OF_WEEK); if (cnt >= day_of_week && calendar.get(Calendar.MONTH) == month) { dayCell_1.setCellValue(day); calendar.set(Calendar.DAY_OF_MONTH, ++day); if (i == 0 || i == days.length - 1) { dayCell_1.setCellStyle(styles.get("weekend_left")); dayCell_2.setCellStyle(styles.get("weekend_right")); } else { dayCell_1.setCellStyle(styles.get("workday_left")); dayCell_2.setCellStyle(styles.get("workday_right")); } } else { dayCell_1.setCellStyle(styles.get("grey_left")); dayCell_2.setCellStyle(styles.get("grey_right")); } cnt++; } if (calendar.get(Calendar.MONTH) > month) break; } } // Write the output to a file String file = "C:\\Users\\BaldiniHP\\Desktop\\calendar.xls"; if (wb instanceof XSSFWorkbook) file += "x"; FileOutputStream out = new FileOutputStream(file); wb.write(out); out.close(); }
From source file:GapAnalysis.gapAnalysis.java
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { response.setContentType("text/html;charset=UTF-8"); //PrintWriter out = response.getWriter(); try {//from w w w.j a v a 2 s . c o m ArrayList keyal = new ArrayList(); ArrayList countyal = new ArrayList(); ArrayList scountyal = new ArrayList(); ArrayList facilal = new ArrayList(); ArrayList yearmonthal = new ArrayList(); ArrayList monthal = new ArrayList(); ArrayList sectional = new ArrayList(); XSSFWorkbook wb; String periodname = ""; String allpath = getServletContext().getRealPath("/Gapanalysis.xlsm"); System.out.println(allpath); XSSFWorkbook workbook; String mydrive = allpath.substring(0, 1); // wb = new XSSFWorkbook( OPCPackage.open(allpath) ); Date da = new Date(); String dat2 = da.toString().replace(" ", "_"); dat2 = dat2.toString().replace(":", "_"); String np = mydrive + ":\\APHIAPLUS\\InternalSystem\\Gapanalysis" + dat2 + ".xlsm"; System.out.println("path:: " + np); // String desteepath1 = getServletContext().getRealPath("/Females 15to24.xlsm"); String sr = getServletContext().getRealPath("/Gapanalysis.xlsm"); //check if file exists //first time , it should create those folders that host the macro file File f = new File(np); if (!f.exists() && !f.isDirectory()) { /* do something */ copytemplates ct = new copytemplates(); ct.transfermacros(sr, np); //rem np is the destination file name System.out.println("Copying macro template first time .."); } else //copy the file alone { copytemplates ct = new copytemplates(); //copy the agebased file only ct.copymacros(sr, np); } String filepth = np; File allpathfile = new File(filepth); OPCPackage pkg = OPCPackage.open(allpathfile); pathtodelete = filepth; wb = new XSSFWorkbook(pkg); dbConn conn = new dbConn(); HashMap<String, String> rawdatahashmap = new HashMap<String, String>(); int year = 0; String yearval = ""; int prevyear = 0; String quarter = ""; String yearmonth = ""; String startyearmonth = ""; String endyearmonth = ""; yearval = request.getParameter("year").toString(); System.out.println("YEARVAL" + yearval); year = Integer.parseInt(yearval); prevyear = year - 1; quarter = request.getParameter("quarter"); periodname += yearval + "_"; if (quarter.equals("1")) { startyearmonth = prevyear + "10"; endyearmonth = prevyear + "12"; periodname = prevyear + "_(Oct_Dec)"; } else if (quarter.equals("2")) { startyearmonth = year + "01"; endyearmonth = year + "03"; periodname = yearval + "_(Jan-Mar)"; } else if (quarter.equals("3")) { startyearmonth = year + "04"; endyearmonth = year + "06"; periodname = yearval + "_(Apr_Jun)"; } else if (quarter.equals("4")) { startyearmonth = year + "07"; endyearmonth = year + "09"; periodname = yearval + "_(Jul_Sep)"; } int colsmerging = 6; String Sections[] = { "ART", "HTC", "PMTCT" }; String headers[] = { "County", "Sub-County", "Facility", "Year", "Month" }; String headergsn[] = { "County", "Sub-County", "Facility" }; //if one wants gaps for one service area if (request.getParameterValues("gapsection") != null) { Sections = request.getParameterValues("gapsection"); } //This is the loop that well use to create worksheets for each String period = " 1=1 and Annee=" + yearval + " and yearmonth between " + startyearmonth + " and " + endyearmonth + " "; String gsnperiod = " 1=1 "; //______________________________________________________________________________________ //______________________________________________________________________________________ Font font = wb.createFont(); font.setFontHeightInPoints((short) 18); font.setFontName("Cambria"); font.setColor((short) 0000); CellStyle style = wb.createCellStyle(); style.setFont(font); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); Font font2 = wb.createFont(); font2.setFontName("Cambria"); font2.setColor((short) 0000); CellStyle style2 = wb.createCellStyle(); style2.setFont(font2); style2.setBorderTop(HSSFCellStyle.BORDER_THIN); style2.setBorderBottom(HSSFCellStyle.BORDER_THIN); style2.setBorderLeft(HSSFCellStyle.BORDER_THIN); style2.setBorderRight(HSSFCellStyle.BORDER_THIN); style2.setAlignment(HSSFCellStyle.ALIGN_LEFT); style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); CellStyle stborder = wb.createCellStyle(); stborder.setBorderTop(HSSFCellStyle.BORDER_THIN); stborder.setBorderBottom(HSSFCellStyle.BORDER_THIN); stborder.setBorderLeft(HSSFCellStyle.BORDER_THIN); stborder.setBorderRight(HSSFCellStyle.BORDER_THIN); stborder.setAlignment(HSSFCellStyle.ALIGN_CENTER); CellStyle stylex = wb.createCellStyle(); stylex.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); stylex.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); stylex.setBorderTop(HSSFCellStyle.BORDER_THIN); stylex.setBorderBottom(HSSFCellStyle.BORDER_THIN); stylex.setBorderLeft(HSSFCellStyle.BORDER_THIN); stylex.setBorderRight(HSSFCellStyle.BORDER_THIN); stylex.setAlignment(HSSFCellStyle.ALIGN_CENTER); CellStyle stylex1 = wb.createCellStyle(); stylex1.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); stylex1.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); stylex1.setBorderTop(HSSFCellStyle.BORDER_THIN); stylex1.setBorderBottom(HSSFCellStyle.BORDER_THIN); stylex1.setBorderLeft(HSSFCellStyle.BORDER_THIN); stylex1.setBorderRight(HSSFCellStyle.BORDER_THIN); stylex1.setAlignment(HSSFCellStyle.ALIGN_LEFT); CellStyle stylex2 = wb.createCellStyle(); stylex2.setFillForegroundColor(HSSFColor.SKY_BLUE.index); stylex2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); stylex2.setBorderTop(HSSFCellStyle.BORDER_THIN); stylex2.setBorderBottom(HSSFCellStyle.BORDER_THIN); stylex2.setBorderLeft(HSSFCellStyle.BORDER_THIN); stylex2.setBorderRight(HSSFCellStyle.BORDER_THIN); stylex2.setAlignment(HSSFCellStyle.ALIGN_CENTER); CellStyle stylex3 = wb.createCellStyle(); stylex3.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index); stylex3.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); stylex3.setBorderTop(HSSFCellStyle.BORDER_THIN); stylex3.setBorderBottom(HSSFCellStyle.BORDER_THIN); stylex3.setBorderLeft(HSSFCellStyle.BORDER_THIN); stylex3.setBorderRight(HSSFCellStyle.BORDER_THIN); stylex3.setAlignment(HSSFCellStyle.ALIGN_CENTER); Font fontx = wb.createFont(); fontx.setColor(HSSFColor.BLACK.index); fontx.setFontName("Cambria"); stylex.setFont(fontx); stylex.setWrapText(true); stylex1.setFont(fontx); stylex1.setWrapText(true); stylex2.setFont(fontx); stylex2.setWrapText(true); //================================================== for (int a = 0; a < Sections.length; a++) { int column = 0; int Row = 3; Sheet shet = wb.createSheet(Sections[a]); Row rwx = shet.createRow(2); Row rw1 = null; Row rw2 = null; Row rw = shet.createRow(0); rw.setHeightInPoints(25); Cell cl0 = rw.createCell(0); cl0.setCellValue(Sections[a] + " GAP ANALYSIS"); cl0.setCellStyle(stylex1); //this will depend on the length of the number of elements being checked for (int b = 1; b <= colsmerging; b++) { Cell clx = rw.createCell(b); clx.setCellValue(""); clx.setCellStyle(stylex); } //now go to the database and do a query for each section int determinant = 2; String getqueries = " Select * from gap_analysis where active=1 and section='" + Sections[a] + "' "; conn.rs = conn.st.executeQuery(getqueries); while (conn.rs.next()) { //if an excel sheet exists, then get the row number 1 if (shet.getRow(1) != null) { rw1 = shet.getRow(1); } else { rw1 = shet.createRow(1); rw1.setHeightInPoints(25); } //print blanks before printing real header //for gsns, we only print three columns and no period if (conn.rs.getString("id").equals("1")) { for (int p = 0; p < headergsn.length; p++) { Cell cl2 = rw1.createCell(column + p); cl2.setCellValue(""); cl2.setCellStyle(stylex); shet.setColumnWidth(column + p, 5000); } } else { for (int p = 0; p < headers.length; p++) { Cell cl2 = rw1.createCell(column + p); cl2.setCellValue(""); cl2.setCellStyle(stylex); shet.setColumnWidth(column + p, 5000); } } determinant++; if (determinant % 2 == 0) { Cell cl1 = rw1.createCell(column); cl1.setCellValue(conn.rs.getString("rule")); cl1.setCellStyle(stylex3); } else { Cell cl1 = rw1.createCell(column); cl1.setCellValue(conn.rs.getString("rule")); cl1.setCellStyle(stylex2); } //Create the column header if (shet.getRow(2) != null) { rw2 = shet.getRow(2); } else { rw2 = shet.createRow(2); rw2.setHeightInPoints(25); } if (conn.rs.getString("id").equals("1")) { for (int p = 0; p < headergsn.length; p++) { Cell cl2 = rw2.createCell(column + p); cl2.setCellValue(headergsn[p]); cl2.setCellStyle(stylex); } } else { for (int p = 0; p < headers.length; p++) { Cell cl2 = rw2.createCell(column + p); cl2.setCellValue(headers[p]); cl2.setCellStyle(stylex); } } String currentqry = conn.rs.getString("query"); //process each query as you //pass the necessary period parameters from the interface //rem each query ends with a 'and' if (conn.rs.getString("id").equals("1")) { currentqry += gsnperiod; } else { currentqry += period + " and subpartnera." + Sections[a] + "= 1 "; } System.out.println("" + currentqry); Row = 3; conn.rs1 = conn.st1.executeQuery(currentqry); while (conn.rs1.next()) { if (shet.getRow(Row) != null) { rwx = shet.getRow(Row); } else { rwx = shet.createRow(Row); rwx.setHeightInPoints(25); } Cell cly = rwx.createCell(column); cly.setCellValue(conn.rs1.getString("County")); cly.setCellStyle(style2); Cell cly2 = rwx.createCell(column + 1); cly2.setCellValue(conn.rs1.getString("DistrictNom")); cly2.setCellStyle(style2);//gsn sites do not have a yearmonth Cell cly1 = rwx.createCell(column + 2); cly1.setCellValue(conn.rs1.getString("SubPartnerNom")); cly1.setCellStyle(style2); //if the current list is not inclusive of GSNs if (!conn.rs.getString(1).equals("1")) { Cell cly3 = rwx.createCell(column + 3); cly3.setCellValue(new Integer(conn.rs1.getString("yearmonth").substring(0, 4))); cly3.setCellStyle(style2); //the month section Cell cly3x = rwx.createCell(column + 4); cly3x.setCellValue(new Integer(conn.rs1.getString("yearmonth").substring(4))); cly3x.setCellStyle(style2); //my key is a String mykey = Sections[a] + conn.rs1.getString("SubPartnerNom") + "_" + conn.rs1.getString("yearmonth") + "_"; //add all the facilities at this point //ignore the sites in ART since they are static if (!keyal.contains(mykey)) { keyal.add(mykey); countyal.add(conn.rs1.getString("County")); scountyal.add(conn.rs1.getString("DistrictNom")); facilal.add(conn.rs1.getString("SubPartnerNom")); sectional.add(Sections[a]); yearmonthal.add(conn.rs1.getString("yearmonth")); monthal.add(conn.rs1.getString("yearmonth").substring(4)); } } Row++; } if (conn.rs.getString(1).equals("1")) { column += 3; } else { column += 5; } if (conn.rs.getString("id").equals("1")) { shet.addMergedRegion(new CellRangeAddress(1, 1, 0, column - 1)); } else { shet.addMergedRegion(new CellRangeAddress(1, 1, column - 5, column - 1)); } } //end of all queries per section shet.addMergedRegion(new CellRangeAddress(0, 0, 0, column - 1)); } // end of sheets loop //create a new sheet //county subcounty facility yearmonth section Sheet shet = wb.getSheet("Sheet1"); Row rw = shet.createRow(0); Cell cl0 = rw.createCell(0); cl0.setCellValue("county"); cl0.setCellStyle(stylex1); Cell cl1 = rw.createCell(1); cl1.setCellValue("subcounty"); cl1.setCellStyle(stylex1); Cell cl2 = rw.createCell(2); cl2.setCellValue("facility"); cl2.setCellStyle(stylex1); Cell cl3 = rw.createCell(3); cl3.setCellValue("year"); cl3.setCellStyle(stylex1); Cell cl4 = rw.createCell(4); cl4.setCellValue("month"); cl4.setCellStyle(stylex1); Cell cl5 = rw.createCell(5); cl5.setCellValue("section"); cl5.setCellStyle(stylex1); for (int q = 0; q < keyal.size(); q++) { Row rwx = shet.createRow(q + 1); Cell cl01 = rwx.createCell(0); cl01.setCellValue(countyal.get(q).toString()); cl01.setCellStyle(style2); Cell cl11 = rwx.createCell(1); cl11.setCellValue(scountyal.get(q).toString()); cl11.setCellStyle(style2); Cell cl21 = rwx.createCell(2); cl21.setCellValue(facilal.get(q).toString()); cl21.setCellStyle(style2); Cell cl31 = rwx.createCell(3); cl31.setCellValue(new Integer(yearmonthal.get(q).toString().substring(0, 4))); cl31.setCellStyle(style2); Cell cl41 = rwx.createCell(4); cl41.setCellValue(new Integer(monthal.get(q).toString())); cl41.setCellStyle(style2); Cell cl51 = rwx.createCell(5); cl51.setCellValue(sectional.get(q).toString()); cl51.setCellStyle(style2); } IdGenerator IG = new IdGenerator(); String createdOn = IG.CreatedOn(); ByteArrayOutputStream outByteStream = new ByteArrayOutputStream(); wb.write(outByteStream); byte[] outArray = outByteStream.toByteArray(); response.setContentType("application/ms-excel"); response.setContentLength(outArray.length); response.setHeader("Expires:", "0"); // eliminates browser caching response.setHeader("Content-Disposition", "attachment; filename=GapAnalysis_For" + periodname + "_Generatted_On_" + createdOn + ".xlsm"); OutputStream outStream = response.getOutputStream(); outStream.write(outArray); outStream.flush(); outStream.close(); pkg.close(); if (conn.rs != null) { conn.rs.close(); } if (conn.rs1 != null) { conn.rs1.close(); } if (conn.st1 != null) { conn.st1.close(); } if (conn.st != null) { conn.st.close(); } File file = new File(pathtodelete); System.out.println("path: 2" + pathtodelete); if (file.delete()) { System.out.println(file.getName() + " is deleted!"); } else { System.out.println("Delete operation failed."); } } catch (SQLException ex) { Logger.getLogger(gapAnalysis.class.getName()).log(Level.SEVERE, null, ex); } catch (InvalidFormatException ex) { Logger.getLogger(gapAnalysis.class.getName()).log(Level.SEVERE, null, ex); } finally { } }
From source file:gov.nih.nci.cadsr.cdecurate.test.TestSpreadsheetDownload.java
License:BSD License
private void createDownloadColumns(ArrayList<String[]> allRows) { final int MAX_ROWS = 65000; String sheetName = "Custom Download"; int sheetNum = 1; String fillIn = "false";// set true to fill in all values. String[] columns = null;// ww w . j av a 2 s . c o m ArrayList<String> defaultHeaders = new ArrayList<String>(); for (String cName : allExpandedColumnHeaders) { if (cName.endsWith("IDSEQ") || cName.startsWith("CD ") || cName.startsWith("Conceptual Domain")) { /* skip */ } else { System.out.println("cName = " + cName); defaultHeaders.add(cName); } } columns = defaultHeaders.toArray(new String[defaultHeaders.size()]); int[] colIndices = new int[columns.length]; for (int i = 0; i < columns.length; i++) { String colName = columns[i]; if (columnHeaders.indexOf(colName) < 0) { String tempType = arrayColumnTypes.get(colName); int temp = columnTypes.indexOf(tempType); colIndices[i] = temp; } else { int temp = columnHeaders.indexOf(colName); colIndices[i] = temp; } } Workbook wb = new HSSFWorkbook(); Sheet sheet = wb.createSheet(sheetName); Font font = wb.createFont(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); CellStyle boldCellStyle = wb.createCellStyle(); boldCellStyle.setFont(font); boldCellStyle.setAlignment(CellStyle.ALIGN_GENERAL); Row headerRow = sheet.createRow(0); headerRow.setHeightInPoints(12.75f); String temp; for (int i = 0; i < columns.length; i++) { Cell cell = headerRow.createCell(i); temp = columns[i]; cell.setCellValue(temp); cell.setCellStyle(boldCellStyle); } // freeze the first row sheet.createFreezePane(0, 1); Row row = null; Cell cell; int rownum = 1; int bump = 0; int i = 0; try { System.out.println("Total CDEs to download [" + allRows.size() + "]"); for (i = 0; i < allRows.size(); i++, rownum++) { // Check if row already exists int maxBump = 0; if (sheet.getRow(rownum + bump) == null) { row = sheet.createRow(rownum + bump); } if (allRows.get(i) == null) continue; for (int j = 0; j < colIndices.length; j++) { cell = row.createCell(j); String currentType = columnTypes.get(colIndices[j]); if (currentType.endsWith("_T")) { // Deal with CS/CSI String[] originalArrColNames = typeMap.get(currentType).get(0); // Find current column in original data int originalColumnIndex = -1; for (int a = 0; a < originalArrColNames.length; a++) { if (columns[j].equals(originalArrColNames[a])) { originalColumnIndex = a; break; } } // ArrayList<HashMap<String,ArrayList<String[]>>> // arrayData1 = // (ArrayList<HashMap<String,ArrayList<String[]>>>)arrayData; HashMap<String, List<String[]>> typeArrayData = arrayData.get(i); ArrayList<String[]> rowArrayData = (ArrayList<String[]>) typeArrayData.get(currentType); if (rowArrayData != null) { int tempBump = 0; for (int nestedRowIndex = 0; nestedRowIndex < rowArrayData.size(); nestedRowIndex++) { String[] nestedData = rowArrayData.get(nestedRowIndex); String data = ""; if (currentType.contains("DERIVED")) { // Derived data element is special double // nested, needs to be modified to be more // general. // General DDE information is in the first 4 // columns, but contained in the first row // of the Row Array Data if (originalColumnIndex < 5) { if (nestedRowIndex == 0) data = (originalColumnIndex > 0) ? nestedData[originalColumnIndex] : nestedData[originalColumnIndex + 1]; } else { if (nestedRowIndex + 1 < rowArrayData.size()) { data = rowArrayData.get(nestedRowIndex + 1)[originalColumnIndex - 5]; } } } else data = nestedData[originalColumnIndex]; logger.debug( "at line 828 of TestSpreadsheetDownload.java*****" + data + currentType); if (currentType.contains("VALID_VALUE")) { data = AdministeredItemUtil.truncateTime(data); } cell.setCellValue(data); tempBump++; if (nestedRowIndex < rowArrayData.size() - 1) { row = sheet.getRow(rownum + bump + tempBump); if (row == null) { if (rownum + bump + tempBump >= MAX_ROWS) { sheet = wb.createSheet(sheetName + "_" + sheetNum); sheetNum++; rownum = 1; bump = 0; tempBump = 0; } row = sheet.createRow(rownum + bump + tempBump); } cell = row.createCell(j); } else { // Go back to top row row = sheet.getRow(rownum + bump); if (tempBump > maxBump) maxBump = tempBump; } } } } else { temp = allRows.get(i)[colIndices[j]]; logger.debug("at line 866 of TestSpreadsheetDownload.java*****" + temp + currentType); if (currentType.equalsIgnoreCase("Date")) { temp = AdministeredItemUtil.truncateTime(temp); } cell.setCellValue(temp); } } bump = bump + maxBump; if (fillIn != null && (fillIn.equals("true") || fillIn.equals("yes") && bump > 0)) { sheet = fillInBump(sheet, i, rownum, bump, allRows, columnTypes, colIndices); rownum = rownum + bump; bump = 0; } } } catch (Exception e) { e.printStackTrace(); } try { // Please specify the path below if needed, otherwise it will create in the root/dir where this test class is run fileOutputStream = new FileOutputStream("Test_Excel.xls"); wb.write(fileOutputStream); } catch (Exception e) { e.printStackTrace(); } finally { /** * Close the fileOutputStream. */ try { if (fileOutputStream != null) { fileOutputStream.close(); } } catch (IOException ex) { ex.printStackTrace(); } } }
From source file:gov.nih.nci.cadsr.cdecurate.tool.CustomDownloadServlet.java
License:BSD License
private void createDownloadColumns(ArrayList<String[]> allRows) { final int MAX_ROWS = 65000; String sheetName = "Custom Download"; int sheetNum = 1; String colString = (String) this.m_classReq.getParameter("cdlColumns"); String fillIn = (String) this.m_classReq.getParameter("fillIn"); ArrayList<String> allHeaders = (ArrayList<String>) m_classReq.getSession().getAttribute("headers"); ArrayList<String> allExpandedHeaders = (ArrayList<String>) m_classReq.getSession() .getAttribute("allExpandedHeaders"); ArrayList<String> allTypes = (ArrayList<String>) m_classReq.getSession().getAttribute("types"); HashMap<String, ArrayList<String[]>> typeMap = (HashMap<String, ArrayList<String[]>>) m_classReq .getSession().getAttribute("typeMap"); ArrayList<HashMap<String, ArrayList<String[]>>> arrayData = (ArrayList<HashMap<String, ArrayList<String[]>>>) m_classReq .getSession().getAttribute("arrayData"); HashMap<String, String> arrayColumnTypes = (HashMap<String, String>) m_classReq.getSession() .getAttribute("arrayColumnTypes"); String[] columns = null;/* w ww. j av a2 s .c o m*/ if (colString != null && !colString.trim().equals("")) { columns = colString.split(","); } else { ArrayList<String> defaultHeaders = new ArrayList<String>(); for (String cName : allExpandedHeaders) { if (cName.endsWith("IDSEQ") || cName.startsWith("CD ") || cName.startsWith("Conceptual Domain")) { /*skip*/ } else { System.out.println("cName = " + cName); defaultHeaders.add(cName); } } columns = defaultHeaders.toArray(new String[defaultHeaders.size()]); } int[] colIndices = new int[columns.length]; for (int i = 0; i < columns.length; i++) { String colName = columns[i]; if (allHeaders.indexOf(colName) < 0) { String tempType = arrayColumnTypes.get(colName); int temp = allTypes.indexOf(tempType); colIndices[i] = temp; } else { int temp = allHeaders.indexOf(colName); colIndices[i] = temp; } } Workbook wb = new HSSFWorkbook(); Sheet sheet = wb.createSheet(sheetName); Font font = wb.createFont(); //GF30779 font.setBoldweight(Font.BOLDWEIGHT_BOLD); //GF30779 CellStyle boldCellStyle = wb.createCellStyle(); //GF30779 boldCellStyle.setFont(font); //GF30779 boldCellStyle.setAlignment(CellStyle.ALIGN_GENERAL); //GF30779 Row headerRow = sheet.createRow(0); headerRow.setHeightInPoints(12.75f); String temp; for (int i = 0; i < columns.length; i++) { Cell cell = headerRow.createCell(i); temp = columns[i]; cell.setCellValue(temp); cell.setCellStyle(boldCellStyle); //GF30779 } //freeze the first row sheet.createFreezePane(0, 1); Row row = null; Cell cell; int rownum = 1; int bump = 0; boolean fillRow = false; int i = 0; long startTime = System.currentTimeMillis(); try { System.out.println("Total CDEs to download [" + allRows.size() + "]"); for (i = 0; i < allRows.size(); i++, rownum++) { //Check if row already exists int maxBump = 0; if (sheet.getRow(rownum + bump) == null) { row = sheet.createRow(rownum + bump); } if (allRows.get(i) == null) continue; for (int j = 0; j < colIndices.length; j++) { cell = row.createCell(j); String currentType = allTypes.get(colIndices[j]); if (currentType.endsWith("_T")) { //Deal with CS/CSI String[] originalArrColNames = typeMap.get(currentType).get(0); //Find current column in original data int originalColumnIndex = -1; for (int a = 0; a < originalArrColNames.length; a++) { if (columns[j].equals(originalArrColNames[a])) { originalColumnIndex = a; break; } } HashMap<String, ArrayList<String[]>> typeArrayData = arrayData.get(i); ArrayList<String[]> rowArrayData = typeArrayData.get(currentType); if (rowArrayData != null) { int tempBump = 0; for (int nestedRowIndex = 0; nestedRowIndex < rowArrayData.size(); nestedRowIndex++) { String[] nestedData = rowArrayData.get(nestedRowIndex); String data = ""; if (currentType.contains("DERIVED")) { //Derived data element is special double nested, needs to be modified to be more general. //General DDE information is in the first 4 columns, but contained in the first row of the Row Array Data if (originalColumnIndex < 5) { if (nestedRowIndex == 0) data = (originalColumnIndex > 0) ? nestedData[originalColumnIndex] : nestedData[originalColumnIndex + 1]; //This skips the 2nd entry, description, which is not to be shown. } else { if (nestedRowIndex + 1 < rowArrayData.size()) { data = rowArrayData.get(nestedRowIndex + 1)[originalColumnIndex - 5]; } } } else data = nestedData[originalColumnIndex]; logger.debug("at line 960 of CustomDownloadServlet.java*****" + data + currentType); if (currentType.contains("VALID_VALUE")) { //GF30779 data = AdministeredItemUtil.truncateTime(data); } cell.setCellValue(data); tempBump++; if (nestedRowIndex < rowArrayData.size() - 1) { row = sheet.getRow(rownum + bump + tempBump); if (row == null) { if (rownum + bump + tempBump >= MAX_ROWS) { sheet = wb.createSheet(sheetName + "_" + sheetNum); sheetNum++; rownum = 1; bump = 0; tempBump = 0; } row = sheet.createRow(rownum + bump + tempBump); } cell = row.createCell(j); } else { //Go back to top row row = sheet.getRow(rownum + bump); if (tempBump > maxBump) maxBump = tempBump; } } } } else { temp = allRows.get(i)[colIndices[j]]; logger.debug("at line 993 of CustomDownloadServlet.java*****" + temp + currentType); if (currentType.equalsIgnoreCase("Date")) { //GF30779 temp = AdministeredItemUtil.truncateTime(temp); } cell.setCellValue(temp); } } bump = bump + maxBump; if (fillIn != null && (fillIn.equals("true") || fillIn.equals("yes") && bump > 0)) { sheet = fillInBump(sheet, i, rownum, bump, allRows, allTypes, colIndices); rownum = rownum + bump; bump = 0; } } } catch (Exception e) { e.printStackTrace(); } // sheet.setZoom(3, 4); //GF30779 // Write the output to response stream. try { m_classRes.setContentType("application/vnd.ms-excel"); m_classRes.setHeader("Content-Disposition", "attachment; filename=\"customDownload.xls\""); OutputStream out = m_classRes.getOutputStream(); wb.write(out); out.close(); } catch (Exception e) { e.printStackTrace(); } }
From source file:itpreneurs.itp.report.archive.BusinessPlan.java
License:Apache License
public static void main(String[] args) throws Exception { Workbook wb;//ww w .j ava 2s . co m // if(args.length > 0 && args[0].equals("-xls")) wb = new // HSSFWorkbook(); // else wb = new XSSFWorkbook(); wb = new XSSFWorkbook(); Map<String, CellStyle> styles = createStyles(wb); Sheet sheet = wb.createSheet("Business Plan"); // turn off gridlines sheet.setDisplayGridlines(false); sheet.setPrintGridlines(false); sheet.setFitToPage(true); sheet.setHorizontallyCenter(true); PrintSetup printSetup = sheet.getPrintSetup(); printSetup.setLandscape(true); // the following three statements are required only for HSSF sheet.setAutobreaks(true); printSetup.setFitHeight((short) 1); printSetup.setFitWidth((short) 1); // the header row: centered text in 48pt font Row headerRow = sheet.createRow(0); headerRow.setHeightInPoints(12.75f); for (int i = 0; i < titles.length; i++) { Cell cell = headerRow.createCell(i); cell.setCellValue(titles[i]); cell.setCellStyle(styles.get("header")); } // columns for 11 weeks starting from 9-Jul Calendar calendar = Calendar.getInstance(); int year = calendar.get(Calendar.YEAR); calendar.setTime(fmt.parse("9-Jul")); calendar.set(Calendar.YEAR, year); for (int i = 0; i < 11; i++) { Cell cell = headerRow.createCell(titles.length + i); cell.setCellValue(calendar); cell.setCellStyle(styles.get("header_date")); calendar.roll(Calendar.WEEK_OF_YEAR, true); } // freeze the first row sheet.createFreezePane(0, 1); Row row; Cell cell; int rownum = 1; for (int i = 0; i < data.length; i++, rownum++) { row = sheet.createRow(rownum); if (data[i] == null) continue; for (int j = 0; j < data[i].length; j++) { cell = row.createCell(j); String styleName; boolean isHeader = i == 0 || data[i - 1] == null; switch (j) { case 0: if (isHeader) { styleName = "cell_b"; cell.setCellValue(Double.parseDouble(data[i][j])); } else { styleName = "cell_normal"; cell.setCellValue(data[i][j]); } break; case 1: if (isHeader) { styleName = i == 0 ? "cell_h" : "cell_bb"; } else { styleName = "cell_indented"; } cell.setCellValue(data[i][j]); break; case 2: styleName = isHeader ? "cell_b" : "cell_normal"; cell.setCellValue(data[i][j]); break; case 3: styleName = isHeader ? "cell_b_centered" : "cell_normal_centered"; cell.setCellValue(Integer.parseInt(data[i][j])); break; case 4: { calendar.setTime(fmt.parse(data[i][j])); calendar.set(Calendar.YEAR, year); cell.setCellValue(calendar); styleName = isHeader ? "cell_b_date" : "cell_normal_date"; break; } case 5: { int r = rownum + 1; String fmla = "IF(AND(D" + r + ",E" + r + "),E" + r + "+D" + r + ",\"\")"; cell.setCellFormula(fmla); styleName = isHeader ? "cell_bg" : "cell_g"; break; } default: styleName = data[i][j] != null ? "cell_blue" : "cell_normal"; } cell.setCellStyle(styles.get(styleName)); } } // group rows for each phase, row numbers are 0-based sheet.groupRow(4, 6); sheet.groupRow(9, 13); sheet.groupRow(16, 18); // set column widths, the width is measured in units of 1/256th of a // character width sheet.setColumnWidth(0, 256 * 6); sheet.setColumnWidth(1, 256 * 33); sheet.setColumnWidth(2, 256 * 20); sheet.setZoom(3, 4); // Write the output to a file String file = "/Users/vincentgong/Documents/workspaces/Resource/itpreneurs/report/businessplan.xls"; if (wb instanceof XSSFWorkbook) file += "x"; FileOutputStream out = new FileOutputStream(file); wb.write(out); out.close(); }
From source file:itpreneurs.itp.report.archive.LoanCalculator.java
License:Apache License
public static void main(String[] args) throws Exception { Workbook wb;/*from w w w . j a v a 2 s .co m*/ 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("Loan Calculator"); sheet.setPrintGridlines(false); sheet.setDisplayGridlines(false); PrintSetup printSetup = sheet.getPrintSetup(); printSetup.setLandscape(true); sheet.setFitToPage(true); sheet.setHorizontallyCenter(true); sheet.setColumnWidth(0, 3 * 256); sheet.setColumnWidth(1, 3 * 256); sheet.setColumnWidth(2, 11 * 256); sheet.setColumnWidth(3, 14 * 256); sheet.setColumnWidth(4, 14 * 256); sheet.setColumnWidth(5, 14 * 256); sheet.setColumnWidth(6, 14 * 256); createNames(wb); Row titleRow = sheet.createRow(0); titleRow.setHeightInPoints(35); for (int i = 1; i <= 7; i++) { titleRow.createCell(i).setCellStyle(styles.get("title")); } Cell titleCell = titleRow.getCell(2); titleCell.setCellValue("Simple Loan Calculator"); sheet.addMergedRegion(CellRangeAddress.valueOf("$C$1:$H$1")); Row row = sheet.createRow(2); Cell cell = row.createCell(4); cell.setCellValue("Enter values"); cell.setCellStyle(styles.get("item_right")); row = sheet.createRow(3); cell = row.createCell(2); cell.setCellValue("Loan amount"); cell.setCellStyle(styles.get("item_left")); cell = row.createCell(4); cell.setCellStyle(styles.get("input_$")); cell.setAsActiveCell(); row = sheet.createRow(4); cell = row.createCell(2); cell.setCellValue("Annual interest rate"); cell.setCellStyle(styles.get("item_left")); cell = row.createCell(4); cell.setCellStyle(styles.get("input_%")); row = sheet.createRow(5); cell = row.createCell(2); cell.setCellValue("Loan period in years"); cell.setCellStyle(styles.get("item_left")); cell = row.createCell(4); cell.setCellStyle(styles.get("input_i")); row = sheet.createRow(6); cell = row.createCell(2); cell.setCellValue("Start date of loan"); cell.setCellStyle(styles.get("item_left")); cell = row.createCell(4); cell.setCellStyle(styles.get("input_d")); row = sheet.createRow(8); cell = row.createCell(2); cell.setCellValue("Monthly payment"); cell.setCellStyle(styles.get("item_left")); cell = row.createCell(4); cell.setCellFormula("IF(Values_Entered,Monthly_Payment,\"\")"); cell.setCellStyle(styles.get("formula_$")); row = sheet.createRow(9); cell = row.createCell(2); cell.setCellValue("Number of payments"); cell.setCellStyle(styles.get("item_left")); cell = row.createCell(4); cell.setCellFormula("IF(Values_Entered,Loan_Years*12,\"\")"); cell.setCellStyle(styles.get("formula_i")); row = sheet.createRow(10); cell = row.createCell(2); cell.setCellValue("Total interest"); cell.setCellStyle(styles.get("item_left")); cell = row.createCell(4); cell.setCellFormula("IF(Values_Entered,Total_Cost-Loan_Amount,\"\")"); cell.setCellStyle(styles.get("formula_$")); row = sheet.createRow(11); cell = row.createCell(2); cell.setCellValue("Total cost of loan"); cell.setCellStyle(styles.get("item_left")); cell = row.createCell(4); cell.setCellFormula("IF(Values_Entered,Monthly_Payment*Number_of_Payments,\"\")"); cell.setCellStyle(styles.get("formula_$")); // Write the output to a file String file = "/Users/vincentgong/Documents/workspaces/Resource/itpreneurs/report/loan-calculator.xls"; if (wb instanceof XSSFWorkbook) file += "x"; FileOutputStream out = new FileOutputStream(file); wb.write(out); out.close(); }
From source file:jdbreport.model.io.xls.poi.Excel2003Writer.java
License:Apache License
private void createRows(ReportModel model, Sheet sheet) { for (int row = 0; row < model.getRowCount(); row++) { TableRow tableRow = model.getRowModel().getRow(row); Row sheetRow = sheet.getRow(row); if (sheetRow == null) { sheetRow = sheet.createRow(row); }// w w w.j a va2 s .c o m sheetRow.setHeightInPoints((tableRow).getNativeHeight()); if (model.isLastRowInPage(row)) { sheet.setRowBreak(row); } } }
From source file:ke.co.mspace.nonsmppmanager.service.SMSOutServiceImpl.java
@Override public void generateXSL(String user, String startDate, String endDate) { try {// w w w.ja va2s .com HSSFWorkbook wb = new HSSFWorkbook(); Map<String, CellStyle> styles = createStyles(wb); HSSFSheet sheet = wb.createSheet("Users_Sheet1"); 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("SMS OUT REPORT"); titleCell.setCellStyle(styles.get("title")); sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$H$1")); String[] titles = { "Mobile", "Source Address", "Message", "Time Spent", "Last Update", "User", "Status", "No. of SMS" }; HSSFRow row = sheet.createRow(1); row.setHeightInPoints(40); Cell headerCell; for (int i = 0; i < titles.length; i++) { headerCell = row.createCell(i); headerCell.setCellValue(titles[i]); headerCell.setCellStyle(styles.get("header")); } List<SMSOut> exportSMSOutReport = (List) userSMSOutReport(user, startDate, endDate).get("result"); int rowNum = 2; for (SMSOut anSMS : exportSMSOutReport) { row = sheet.createRow(rowNum); row.createCell(0).setCellValue(anSMS.getDestinationAddr()); row.createCell(1).setCellValue(anSMS.getSourceAddr()); row.createCell(2).setCellValue(anSMS.getMessagePayload()); row.createCell(3).setCellValue(anSMS.getTimeSubmitted()); row.createCell(4).setCellValue(anSMS.getTimeProcessed()); row.createCell(5).setCellValue(anSMS.getUser()); row.createCell(6).setCellValue(anSMS.getRealStatus()); row.createCell(7).setCellValue(anSMS.getSmsCount()); rowNum++; } sheet.setColumnWidth(0, 20 * 256); //30 characters wide sheet.setColumnWidth(1, 15 * 256); for (int i = 2; i < 5; i++) { sheet.setColumnWidth(i, 20 * 256); //6 characters wide } sheet.setColumnWidth(5, 10 * 256); sheet.setColumnWidth(6, 20 * 256); sheet.setColumnWidth(7, 10 * 256); //10 characters wide FacesContext context = FacesContext.getCurrentInstance(); HttpServletResponse res = (HttpServletResponse) context.getExternalContext().getResponse(); res.setContentType("application/vnd.ms-excel"); res.setHeader("Content-disposition", "attachment;filename=mydata.xlsx"); ServletOutputStream out = res.getOutputStream(); wb.write(out); out.flush(); out.close(); FacesContext.getCurrentInstance().responseComplete(); } catch (Exception e) { e.printStackTrace(); } }