List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet setDisplayGridlines
@Override public void setDisplayGridlines(boolean show)
From source file:PPMP.ppmpreport_2.java
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException, SQLException { response.setContentType("text/html;charset=UTF-8"); try {//from w w w . j ava 2s.com /* TODO output your page here. You may use following sample code. */ //create ppmp report here //define some variables for keeping number of columns. // this should be dynamic because of the annual cumulatives depending on the selected year //the minimum year is 2011 // int selectedyear = 2016; int projectstartyear = 2011; int minimumcolumns = 11;//this is if the year is 2011 int currentcolumns = minimumcolumns + (selectedyear - projectstartyear); String selectedQTR = "Q2"; if (request.getParameter("year") != null) { selectedyear = new Integer(request.getParameter("year")); } if (request.getParameter("year") != null) { selectedQTR = request.getParameter("quarter"); } HSSFWorkbook wb = new HSSFWorkbook(); Calendar cal = Calendar.getInstance(); int year = cal.get(Calendar.YEAR); String month = String.format("%02d", cal.get(Calendar.MONTH) + 1); String date = String.format("%02d", cal.get(Calendar.DATE)); String hour = String.format("%02d", cal.get(Calendar.HOUR_OF_DAY)); String min = String.format("%02d", cal.get(Calendar.MINUTE)); String sec = String.format("%02d", cal.get(Calendar.SECOND)); String generationtime = "(" + year + "_" + month + "_" + date + ")_" + hour + "-" + min + "-" + sec; //______________________________________________________________________________________ //______________________________________________________________________________________ HSSFFont font = wb.createFont(); font.setFontHeightInPoints((short) 12); font.setFontName("Arial Narrow"); font.setColor((short) 0000); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); CellStyle style = wb.createCellStyle(); style.setFont(font); style.setBorderTop(HSSFCellStyle.BORDER_THICK); style.setBorderBottom(HSSFCellStyle.BORDER_THICK); style.setBorderLeft(HSSFCellStyle.BORDER_THICK); style.setBorderRight(HSSFCellStyle.BORDER_THICK); style.setAlignment(HSSFCellStyle.ALIGN_LEFT); style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); style.setWrapText(true); CellStyle spstyle = wb.createCellStyle(); spstyle.setFont(font); spstyle.setBorderTop(HSSFCellStyle.BORDER_THICK); spstyle.setBorderBottom(HSSFCellStyle.BORDER_THICK); spstyle.setBorderLeft(HSSFCellStyle.BORDER_THICK); spstyle.setBorderRight(HSSFCellStyle.BORDER_THICK); spstyle.setAlignment(HSSFCellStyle.ALIGN_LEFT); spstyle.setFillForegroundColor(HSSFColor.LIGHT_BLUE.index); spstyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); spstyle.setWrapText(true); System.out.println("Blue index:" + HSSFColor.BLUE.index); HSSFFont font2 = wb.createFont(); font2.setFontName("Cambria"); font2.setColor((short) 0000); CellStyle style2 = wb.createCellStyle(); style2.setFont(font2); style2.setBorderTop(HSSFCellStyle.BORDER_THICK); style2.setBorderBottom(HSSFCellStyle.BORDER_THICK); style2.setBorderLeft(HSSFCellStyle.BORDER_THICK); style2.setBorderRight(HSSFCellStyle.BORDER_THICK); style2.setAlignment(HSSFCellStyle.ALIGN_LEFT); style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); style2.setWrapText(true); HSSFCellStyle stborder = wb.createCellStyle(); stborder.setBorderTop(HSSFCellStyle.BORDER_THICK); stborder.setBorderBottom(HSSFCellStyle.BORDER_THICK); stborder.setBorderLeft(HSSFCellStyle.BORDER_THICK); stborder.setBorderRight(HSSFCellStyle.BORDER_THICK); stborder.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFCellStyle stylex = wb.createCellStyle(); stylex.setFillForegroundColor(HSSFColor.WHITE.index); stylex.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); stylex.setBorderTop(HSSFCellStyle.BORDER_THICK); stylex.setBorderBottom(HSSFCellStyle.BORDER_THICK); stylex.setBorderLeft(HSSFCellStyle.BORDER_THICK); stylex.setBorderRight(HSSFCellStyle.BORDER_THICK); stylex.setAlignment(HSSFCellStyle.ALIGN_LEFT); HSSFFont fontx = wb.createFont(); fontx.setColor(HSSFColor.BLACK.index); fontx.setFontName("Arial Narrow"); fontx.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); fontx.setFontHeightInPoints((short) 16); stylex.setFont(fontx); stylex.setWrapText(true); HSSFSheet shet = wb.createSheet("PPMP " + selectedyear + " Report "); //create headers for that worksheet HSSFRow rw = shet.createRow(0); rw.setHeightInPoints(25); HSSFCell cl0 = rw.createCell(0); cl0.setCellValue("PROJECT PERFORMANCE MONITORING PLAN (PPMP)"); cl0.setCellStyle(stylex); for (int a = 1; a < currentcolumns; a++) { HSSFCell clx = rw.createCell(a); clx.setCellValue(""); clx.setCellStyle(stylex); } //merge row one shet.addMergedRegion(new CellRangeAddress(0, 0, 0, currentcolumns - 1)); //firt row ArrayList headerone = new ArrayList(); //headerone.add("Sub Purpose"); headerone.add("Output"); headerone.add("Indicators"); headerone.add("Baseline"); headerone.add("Year " + selectedyear + " Target"); headerone.add(selectedyear + " Quarterly Achievements "); headerone.add(""); headerone.add(""); headerone.add(""); headerone.add("Cumulative Year Achievements"); //the header Cumulative Year Achievements could be in the report depending on the selected year //for 2011, its not expected to appear in the report for (int a = 0; a <= (selectedyear - projectstartyear); a++) { if (a == 0) { //do nothing } else { headerone.add(""); } } headerone.add("Percentage (%) Achieved vs Year " + selectedyear); //header two which contains quartersa dn yearly achievement ArrayList headertwo = new ArrayList(); //headertwo.add("Sub Purpose"); headertwo.add("Output"); headertwo.add("Indicators"); headertwo.add("Baseline"); headertwo.add(""); headertwo.add("Oct-Dec " + (selectedyear - 1)); headertwo.add("Jan-Mar"); headertwo.add("Apr-Jun"); headertwo.add("Jul-Sep"); headertwo.add(selectedyear); //the header Cumulative Year Achievements could be in the report depending on the selected year //for 2011, its not expected to appear in the report for (int a = 0; a <= (selectedyear - projectstartyear); a++) { if (a == 0) { //do nothing } else { headertwo.add(selectedyear - a); //eg 2016, 2015, 2014 ... } } headertwo.add(""); //================================================================================= //DISPLAY HEADER DISPLAY HEADER DISPLAY HEADER DISPLAY HEADER DISPLAY HEADER //================================================================================= //display the header values for row one and two HSSFRow rw1 = shet.createRow(1); for (int a = 0; a < headerone.size(); a++) { HSSFCell cellx = rw1.createCell(a); cellx.setCellValue(headerone.get(a).toString()); cellx.setCellStyle(style); shet.setColumnWidth(a, 3500); } //===================row 2======================= //display the header values for row one and two HSSFRow rw2 = shet.createRow(2); rw2.setHeightInPoints(35); for (int a = 0; a < headertwo.size(); a++) { HSSFCell cellx = rw2.createCell(a); if (headertwo.get(a).toString().startsWith("20")) { cellx.setCellValue(new Integer(headertwo.get(a).toString())); } else { cellx.setCellValue(headertwo.get(a).toString()); } cellx.setCellStyle(style); } // shet.addMergedRegion(new CellRangeAddress(start row, end row, start column ,end column)); String mergingarray[] = { "1_2_0_0", "1_2_1_1", "1_2_2_2", "1_2_3_3", "1_1_4_7", "1_1_8_" + (8 + (selectedyear - projectstartyear)), "1_2_" + (8 + (selectedyear - projectstartyear) + 1) + "_" + (8 + (selectedyear - projectstartyear) + 1) }; for (int a = 0; a < mergingarray.length; a++) { String content[] = mergingarray[a].split("_"); shet.addMergedRegion(new CellRangeAddress(new Integer(content[0]), new Integer(content[1]), new Integer(content[2]), new Integer(content[3]))); } //=================================================================================== //DISPLAY VALUES DISPLAY VALUES DISPLAY VALUES DISPLAY VALUES DISPLAY VALUES //=================================================================================== String getindicators = "select * from indicatortitles where active='yes' order by subpurpose, output , title "; dbConnect conn = new dbConnect(); conn.rs = conn.state.executeQuery(getindicators); int rownumber = 3; shet.setColumnWidth(1, 14000); shet.setColumnWidth(2, 2300); shet.setColumnWidth(0, 7000); shet.setColumnWidth(3, 2300); shet.setColumnWidth(4, 2300); shet.setColumnWidth(5, 2300); shet.setColumnWidth(6, 2300); shet.setColumnWidth(7, 2300); shet.setColumnWidth(8, 2300); shet.setColumnWidth(9, 2300); shet.setColumnWidth(10, 2300); shet.setColumnWidth(11, 2300); shet.setColumnWidth(12, 2300); shet.setColumnWidth(13, 2300); shet.setColumnWidth(14, 2300); ArrayList sp = new ArrayList(); int subpurposerow = 3; int subpurposerowcopy = 3; ArrayList op = new ArrayList(); int outputrow = 3; int outputrowcopy = 4; int colpos = 0; int count = 0; while (conn.rs.next()) { System.out.println("SP__" + conn.rs.getString("subpurpose")); //merge subpartner row sp.add(conn.rs.getString("subpurpose")); if (sp.size() > 1) { //check if subporpose has changed if (!sp.get(count).toString().equals(sp.get(count - 1).toString())) { subpurposerow = rownumber; //===================================================subpurpose======================== HSSFRow rwxa = shet.createRow(rownumber); HSSFCell cl01 = rwxa.createCell(colpos); cl01.setCellValue(conn.rs.getString("subpurpose")); cl01.setCellStyle(spstyle); subpurposerowcopy = subpurposerow; //should merge entire row shet.addMergedRegion(new CellRangeAddress(subpurposerow, subpurposerow, 0, 15)); rownumber++; } } else { subpurposerow = rownumber; //===================================================subpurpose======================== HSSFRow rwxa = shet.createRow(rownumber); HSSFCell cl01 = rwxa.createCell(colpos); cl01.setCellValue(conn.rs.getString("subpurpose")); cl01.setCellStyle(spstyle); subpurposerowcopy = subpurposerow; //should merge entire row shet.addMergedRegion(new CellRangeAddress(subpurposerow, subpurposerow, 0, 15)); rownumber++; } String outputval = ""; if (conn.rs.getString("output") != null) { outputval = conn.rs.getString("output"); } //merge ouput rows op.add(outputval); if (op.size() > 1) { //check if out has changed if (!op.get(count).toString().equals(op.get(count - 1).toString())) { outputrow = rownumber; //should merge shet.addMergedRegion(new CellRangeAddress(outputrowcopy, outputrow - 1, 0, 0)); outputrowcopy = outputrow; System.out.println(" Comparison :" + op.get(count).toString() + " %%% " + op.get(count - 1).toString()); } } //now output the first part of the report HSSFRow rwx = shet.createRow(rownumber); //====================================================output============================= HSSFCell cl02 = rwx.createCell(colpos); cl02.setCellValue(conn.rs.getString("output")); cl02.setCellStyle(style2); colpos++; //===================================================indicators=========================== HSSFCell cl03 = rwx.createCell(colpos); cl03.setCellValue(conn.rs.getString("title")); cl03.setCellStyle(style2); colpos++; //====================================================baseline============================= HSSFCell cl04 = rwx.createCell(colpos); cl04.setCellValue(conn.rs.getString("totalbaseline")); cl04.setCellStyle(style2); colpos++; //====================================================targets================================= String gettargets = " select sum(target_combined) as target from yearly_targets where indicator_id='" + conn.rs.getString("titleID") + "' and year='" + selectedyear + "' "; //for percent indicators, get avg if (conn.rs.getInt("percentage") == 1) { gettargets = "select avg(target_combined) as target from yearly_targets where indicator_id='" + conn.rs.getString("titleID") + "' and year='" + selectedyear + "' "; } HSSFCell cl05 = rwx.createCell(colpos); colpos++; int annualtarget = 1; conn.rs1 = conn.state1.executeQuery(gettargets); if (conn.rs1.next()) { //set the target cl05.setCellValue(conn.rs1.getInt("target")); if (conn.rs.getInt("percentage") == 1) { if (conn.rs1.getInt("target") < 200) { cl05.setCellValue(conn.rs1.getInt("target") + "%"); } else { cl05.setCellValue(conn.rs1.getInt("target")); } } else { cl05.setCellValue(conn.rs1.getInt("target")); } cl05.setCellStyle(style2); if (conn.rs1.getString("target") != null) { if (!conn.rs1.getString("target").equals("")) { annualtarget = conn.rs1.getInt("target"); } } } //===========================================current year values==================================== String ispercent = ""; int highestvalue = 0; String getdata = " select sum(case when reportingPeriod='Q1' then totalAchieved end) as Q1, sum(case when reportingPeriod='Q2' then totalAchieved end) as Q2, sum(case when reportingPeriod='Q3' then totalAchieved end) as Q3, sum(case when reportingPeriod='Q4' then totalAchieved end) as Q4 where titleID='" + conn.rs.getString("titleID") + "' and financialyear='" + selectedyear + "' group by titleID "; //for percent indicators, get avg if (conn.rs.getInt("percentage") == 1) { ispercent = "%"; if (conn.rs.getString("tableIdentifier").equals("1")) { //by gender and thus separate columns getdata = " select ROUND(AVG(case when reportingPeriod='Q1' then ((menAchieved + womenAchieved)/2) end)) as Q1, ROUND(AVG(case when reportingPeriod='Q2' then ((menAchieved + womenAchieved)/2) end)) as Q2, ROUND(AVG(case when reportingPeriod='Q3' then ((menAchieved + womenAchieved)/2) end)) as Q3, ROUND(AVG(case when reportingPeriod='Q4' then ((menAchieved + womenAchieved)/2) end)) as Q4 from indicatorachieved where titleID='" + conn.rs.getString("titleID") + "' and financialyear='" + selectedyear + "' group by titleID "; System.out.println("@@" + getdata); } else { //combined i.e male and female getdata = " select ROUND(AVG(case when reportingPeriod='Q1' then totalAchieved end)) as Q1,ROUND(AVG(case when reportingPeriod='Q2' then totalAchieved end)) as Q2, ROUND(AVG(case when reportingPeriod='Q3' then totalAchieved end)) as Q3, ROUND(AVG(case when reportingPeriod='Q4' then totalAchieved end)) as Q4 from indicatorachievedcombined where titleID='" + conn.rs.getString("titleID") + "' and financialyear='" + selectedyear + "' group by titleID "; System.out.println("@@" + getdata); } } //non percentages else { //if if (conn.rs.getString("tableIdentifier").equals("1")) { //by gender and thus separate columns getdata = " select sum(case when reportingPeriod='Q1' then (menAchieved + womenAchieved) end) as Q1,sum(case when reportingPeriod='Q2' then (menAchieved + womenAchieved) end) as Q2, sum(case when reportingPeriod='Q3' then (menAchieved + womenAchieved) end) as Q3, sum(case when reportingPeriod='Q4' then (menAchieved + womenAchieved) end) as Q4 from indicatorachieved where titleID='" + conn.rs.getString("titleID") + "' and financialyear='" + selectedyear + "' group by titleID "; } else { getdata = " select sum(case when reportingPeriod='Q1' then totalAchieved end) as Q1, sum(case when reportingPeriod='Q2' then totalAchieved end) as Q2, sum(case when reportingPeriod='Q3' then totalAchieved end) as Q3, sum(case when reportingPeriod='Q4' then totalAchieved end) as Q4 from indicatorachievedcombined where titleID='" + conn.rs.getString("titleID") + "' and financialyear='" + selectedyear + "' group by titleID "; } } String Q1 = ""; String Q2 = ""; String Q3 = ""; String Q4 = ""; conn.rs1 = conn.state1.executeQuery(getdata); //Q1 HSSFCell clQ1 = rwx.createCell(colpos); colpos++; HSSFCell clQ2 = rwx.createCell(colpos); colpos++; HSSFCell clQ3 = rwx.createCell(colpos); colpos++; HSSFCell clQ4 = rwx.createCell(colpos); colpos++; if (conn.rs1.next()) { if (conn.rs1.getString("Q1") != null) { if (!conn.rs1.getString("Q1").equals("")) { highestvalue = conn.rs1.getInt("Q1"); if (!ispercent.equals("")) { clQ1.setCellValue(conn.rs1.getInt("Q1") + ispercent); } else { clQ1.setCellValue(conn.rs1.getInt("Q1")); } } } if (conn.rs1.getString("Q2") != null) { if (!conn.rs1.getString("Q2").equals("")) { if (conn.rs1.getInt("Q2") > highestvalue) { highestvalue = conn.rs1.getInt("Q2"); } if (!ispercent.equals("")) { clQ2.setCellValue(conn.rs1.getInt("Q2") + ispercent); } else { clQ2.setCellValue(conn.rs1.getInt("Q2")); } } } if (conn.rs1.getString("Q3") != null) { if (!conn.rs1.getString("Q3").equals("")) { if (conn.rs1.getInt("Q3") > highestvalue) { highestvalue = conn.rs1.getInt("Q3"); } if (!ispercent.equals("")) { clQ3.setCellValue(conn.rs1.getInt("Q3") + ispercent); } else { clQ3.setCellValue(conn.rs1.getInt("Q3")); } } } if (conn.rs1.getString("Q4") != null) { if (!conn.rs1.getString("Q4").equals("")) { if (conn.rs1.getInt("Q4") > highestvalue) { highestvalue = conn.rs1.getInt("Q4"); } if (!ispercent.equals("")) { clQ4.setCellValue(conn.rs1.getInt("Q4") + ispercent); } else { clQ4.setCellValue(conn.rs1.getInt("Q4")); } } } } clQ2.setCellStyle(style2); clQ1.setCellStyle(style2); clQ3.setCellStyle(style2); clQ4.setCellStyle(style2); //====================================Annual figures======================= String percentageachievement = ""; String achievednonpercent = "No target / achieved value"; int curcol = colpos; String annualispercent = ""; int currentyearvalue = 0; int currentyearhighestqtr = 0; for (int yearval = selectedyear; yearval >= projectstartyear; yearval--) { HSSFCell clx = rwx.createCell(curcol); // System.out.println("******"+curcol); //separate cumulates + average with the rest if (conn.rs.getString("cumulative_chooser").equalsIgnoreCase("Cumulative") || conn.rs.getString("cumulative_chooser").equalsIgnoreCase("Average")) { String qry = ""; if (conn.rs.getString("percentage").equals("1")) { annualispercent = "%"; if (conn.rs.getString("tableidentifier").equals("2")) { //no gender thus its combined qry = " select ROUND(AVG(totalAchieved)) as y" + yearval + " from indicatorachievedcombined where financialyear='" + yearval + "' and titleID='" + conn.rs.getString("titleID") + "' group by titleID"; } else { qry = " select ROUND(AVG((menAchieved + womenAchieved)/2)) as y" + yearval + " from indicatorachieved where financialyear='" + yearval + "' and titleID='" + conn.rs.getString("titleID") + "' group by titleID"; } } //non percents else { //for cumulatives //check tabletype //1 is by gender //_____________CUMULATIVES______________ if (conn.rs.getString("cumulative_chooser").equalsIgnoreCase("Cumulative")) { if (conn.rs.getString("tableIdentifier").equals("1")) { qry = " select sum((menAchieved + womenAchieved)) as y" + yearval + " from indicatorachieved where financialyear='" + yearval + "' and titleID='" + conn.rs.getString("titleID") + "' group by titleID"; } else { qry = " select sum(totalAchieved) as y" + yearval + " from indicatorachievedcombined where financialyear='" + yearval + "' and titleID='" + conn.rs.getString("titleID") + "' group by titleID"; } //end of else of table identifier } //end of sum/cumulatives } //end of else of non percents System.out.println("@" + qry); conn.rs1 = conn.state1.executeQuery(qry); if (conn.rs1.next()) { // System.out.println("__"+conn.rs1.getString(1)); if (conn.rs1.getString(1) != null) { if (!conn.rs1.getString(1).equals("")) { if (annualispercent.equals("")) { clx.setCellValue(conn.rs1.getInt(1)); //achieved nonpercentage if (yearval == selectedyear) { if (annualtarget > 1) { achievednonpercent = "" + (int) conn.rs1.getInt(1) * 100 / (int) annualtarget + "%"; } } } else { clx.setCellValue(conn.rs1.getInt(1) + annualispercent); //do this for the cureent year if (yearval == selectedyear) { percentageachievement = conn.rs1.getInt(1) + annualispercent; } } } } } clx.setCellStyle(style2); } //end of cumulatives and percentages else if (conn.rs.getString("cumulative_chooser").equalsIgnoreCase("Highest")) { String qry = ""; if (conn.rs.getString("tableIdentifier").equals("1")) { //by gender and thus separate columns qry = " select sum(case when reportingPeriod='Q1' then (menAchieved + womenAchieved) end) as Q1,sum(case when reportingPeriod='Q2' then (menAchieved + womenAchieved) end) as Q2, sum(case when reportingPeriod='Q3' then (menAchieved + womenAchieved) end) as Q3, sum(case when reportingPeriod='Q4' then (menAchieved + womenAchieved) end) as Q4 from indicatorachieved where titleID='" + conn.rs.getString("titleID") + "' and financialyear='" + yearval + "' group by titleID "; } else { qry = " select sum(case when reportingPeriod='Q1' then totalAchieved end) as Q1, sum(case when reportingPeriod='Q2' then totalAchieved end) as Q2, sum(case when reportingPeriod='Q3' then totalAchieved end) as Q3, sum(case when reportingPeriod='Q4' then totalAchieved end) as Q4 from indicatorachievedcombined where titleID='" + conn.rs.getString("titleID") + "' and financialyear='" + yearval + "' group by titleID "; } int highestqtr = 0; //excecute query conn.rs1 = conn.state1.executeQuery(qry); while (conn.rs1.next()) { // if (conn.rs1.getString("Q1") != null) { if (!conn.rs1.getString("Q1").equals("")) { if (conn.rs1.getInt("Q1") > highestqtr) { highestqtr = conn.rs1.getInt("Q1"); } } } else if (conn.rs1.getString("Q2") != null) { if (!conn.rs1.getString("Q2").equals("")) { if (conn.rs1.getInt("Q2") > highestqtr) { highestqtr = conn.rs1.getInt("Q2"); } } } else if (conn.rs1.getString("Q3") != null) { if (!conn.rs1.getString("Q3").equals("")) { if (conn.rs1.getInt("Q3") > highestqtr) { highestqtr = conn.rs1.getInt("Q3"); } } } else if (conn.rs1.getString("Q4") != null) { if (!conn.rs1.getString("Q4").equals("")) { if (conn.rs1.getInt("Q4") > highestqtr) { highestqtr = conn.rs1.getInt("Q4"); } } } } if (highestqtr > 0) { clx.setCellValue(highestqtr); } else { clx.setCellValue(""); } //achieved nonpercentage if (yearval == selectedyear) { if (annualtarget != 1) { achievednonpercent = "" + (int) highestqtr * 100 / (int) annualtarget + "%"; System.out.println(highestqtr + " / " + annualtarget + "___" + (int) highestqtr * 100 / (int) (annualtarget) + "%"); } } clx.setCellStyle(style2); } // end of highest else if (conn.rs.getString("cumulative_chooser").equalsIgnoreCase("OLMIS") || conn.rs.getString("cumulative_chooser").equalsIgnoreCase("Last Reported")) { String qry = ""; //get data for the last input quarter if (yearval == selectedyear) { //get data for that quarter if (conn.rs.getString("tableidentifier").equals("2")) { //no gender qry = " select sum((totalAchieved)) as y" + yearval + " from indicatorachievedcombined where financialyear='" + yearval + "' and reportingPeriod='" + selectedQTR + "' and titleID='" + conn.rs.getString("titleID") + "' group by titleID"; } else { qry = " select sum((menAchieved + womenAchieved)) as y" + yearval + " from indicatorachieved where financialyear='" + yearval + "' and reportingPeriod='" + selectedQTR + "' and titleID='" + conn.rs.getString("titleID") + "' group by titleID"; } } else { if (conn.rs.getString("tableidentifier").equals("2")) { //no gender qry = " select sum((totalAchieved)) as y" + yearval + " from indicatorachievedcombined where financialyear='" + yearval + "' and reportingPeriod='Q4' and titleID='" + conn.rs.getString("titleID") + "' group by titleID"; } else { qry = " select sum((menAchieved + womenAchieved)) as y" + yearval + " from indicatorachieved where financialyear='" + yearval + "' and reportingPeriod='Q4' and titleID='" + conn.rs.getString("titleID") + "' group by titleID"; } } //execute the query conn.rs1 = conn.state1.executeQuery(qry); if (conn.rs1.next()) { // System.out.println("__"+conn.rs1.getString(1)); if (conn.rs1.getString(1) != null) { if (!conn.rs1.getString(1).equals("")) { clx.setCellValue(conn.rs1.getInt(1)); //this is for percentage purpose if (selectedyear == yearval) { currentyearvalue = conn.rs1.getInt(1); } } } } //achieved nonpercentage if (yearval == selectedyear) { if (annualtarget != 1) { achievednonpercent = "" + (int) currentyearvalue * 100 / (int) annualtarget + "%"; } } clx.setCellStyle(style2); } //end of olmis and last reported indicators curcol++; } //end of for loop HSSFCell clx = rwx.createCell(curcol); if (conn.rs.getInt("percentage") == 1) { clx.setCellValue(percentageachievement); } else { clx.setCellValue(achievednonpercent); } clx.setCellStyle(style2); rwx.setHeightInPoints(42); rownumber++; count++; colpos = 0; } for (int e = 0; e < 13; e++) { //shet.autoSizeColumn(e); } //Made my life veery simple... shet.setDisplayGridlines(false); shet.createFreezePane(2, 3); if (conn.rs != null) { conn.rs.close(); } if (conn.rs1 != null) { conn.rs1.close(); } if (conn.state1 != null) { conn.state1.close(); } if (conn.state2 != null) { conn.state2.close(); } // write it as an excel attachment 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=PPMPREPORT_" + selectedyear + "_" + selectedQTR + "_gen_on_" + generationtime + ".xls"); OutputStream outStream = response.getOutputStream(); outStream.write(outArray); outStream.flush(); } finally { } }
From source file:quickreports.masterlist.java
/** * Processes requests for both HTTP <code>GET</code> and <code>POST</code> * methods.//www . j a v a 2 s . co m * * @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, SQLException { response.setContentType("text/html;charset=UTF-8"); //PrintWriter out = response.getWriter(); /* TODO output your page here. You may use following sample code. */ //______________________________________________________________________________________ // CREATE THE WORKSHEETS //______________________________________________________________________________________ HSSFWorkbook wb = new HSSFWorkbook(); HSSFFont 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); HSSFFont 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); HSSFCellStyle 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); HSSFCellStyle 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_LEFT); HSSFCellStyle stylesum = wb.createCellStyle(); stylesum.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); stylesum.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); stylesum.setBorderTop(HSSFCellStyle.BORDER_THIN); stylesum.setBorderBottom(HSSFCellStyle.BORDER_THIN); stylesum.setBorderLeft(HSSFCellStyle.BORDER_THIN); stylesum.setBorderRight(HSSFCellStyle.BORDER_THIN); stylesum.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFFont fontx = wb.createFont(); fontx.setColor(HSSFColor.BLACK.index); fontx.setFontName("Cambria"); stylex.setFont(fontx); stylex.setWrapText(true); stylesum.setFont(fontx); stylesum.setWrapText(true); HSSFSheet shet = wb.createSheet("Masterlist"); String year = ""; if (request.getParameter("year") != null) { year = request.getParameter("year"); } dbConn conn = new dbConn(); //========Query 1================= HSSFRow rw0 = shet.createRow(1); HSSFCell cell = rw0.createCell(0); cell.setCellValue("APHIAPLUS Nuru Ya Bonde Supported Sites Summary " + year); cell.setCellStyle(style); shet.addMergedRegion(new CellRangeAddress(1, 1, 0, 3)); int count1 = 3; String qry1 = "call rpt_masterlist_summary('2015-10-01','2016-09-30','" + year + "')"; conn.rs = conn.st.executeQuery(qry1); ResultSetMetaData metaData = conn.rs.getMetaData(); int columnCount = metaData.getColumnCount(); ArrayList mycolumns1 = new ArrayList(); while (conn.rs.next()) { if (count1 == 3) { //header rows HSSFRow rw = shet.createRow(count1); rw.setHeightInPoints(26); for (int i = 1; i <= columnCount; i++) { mycolumns1.add(metaData.getColumnLabel(i)); HSSFCell cell0 = rw.createCell(i - 1); cell0.setCellValue(metaData.getColumnLabel(i)); cell0.setCellStyle(stylex); //create row header } //end of for loop count1++; } //end of if //data rows HSSFRow rw = shet.createRow(count1); for (int a = 0; a < columnCount; a++) { // System.out.print(mycolumns1.get(a) + ":" + conn.rs.getString("" + mycolumns1.get(a))); HSSFCell cell0 = rw.createCell(a); if (a > 0) { cell0.setCellValue(conn.rs.getInt(mycolumns1.get(a).toString())); } else { cell0.setCellValue(conn.rs.getString("" + mycolumns1.get(a))); } cell0.setCellStyle(style2); } // System.out.println(""); count1++; } HSSFRow rw01 = shet.createRow(count1 + 1); HSSFCell cell1 = rw01.createCell(0); cell1.setCellValue("APHIAPLUS Nuru Ya Bonde supported sites details " + year); cell1.setCellStyle(style); shet.addMergedRegion(new CellRangeAddress(count1 + 1, count1 + 1, 0, 3)); //========Query two====Facility Details============== String qry = "call rpt_masterlist('2015-10-01','2016-09-30','" + year + "')"; conn.rs = conn.st.executeQuery(qry); metaData = conn.rs.getMetaData(); columnCount = metaData.getColumnCount(); int count = count1 + 3; ArrayList mycolumns = new ArrayList(); while (conn.rs.next()) { if (count == (count1 + 3)) { //header rows HSSFRow rw = shet.createRow(count); rw.setHeightInPoints(26); for (int i = 1; i <= columnCount; i++) { mycolumns.add(metaData.getColumnLabel(i)); HSSFCell cell0 = rw.createCell(i - 1); cell0.setCellValue(metaData.getColumnLabel(i)); cell0.setCellStyle(stylex); //create row header } //end of for loop count++; } //end of if //data rows HSSFRow rw = shet.createRow(count); for (int a = 0; a < columnCount; a++) { //System.out.print(mycolumns.get(a) + ":" + conn.rs.getString("" + mycolumns.get(a))); HSSFCell cell0 = rw.createCell(a); if ((a >= 3 && a <= 14) || (a >= 18 && a <= 22) || (a >= 27 && a <= 29)) { cell0.setCellValue(conn.rs.getInt(mycolumns.get(a).toString())); } else { cell0.setCellValue(conn.rs.getString("" + mycolumns.get(a))); } cell0.setCellStyle(style2); } // System.out.println(""); count++; } //Autofreeze || Autofilter || Remove Gridlines || shet.setAutoFilter(new CellRangeAddress(count1 + 3, count - 1, 0, columnCount - 1)); //System.out.println("1,"+rowpos+",0,"+colposcopy); for (int i = 0; i <= columnCount; i++) { shet.autoSizeColumn(i); } shet.setDisplayGridlines(false); shet.createFreezePane(4, 14); if (conn.rs != null) { conn.rs.close(); } if (conn.rs1 != null) { conn.rs1.close(); } if (conn.st != null) { conn.st.close(); } if (conn.st1 != null) { conn.st1.close(); } IdGenerator IG = new IdGenerator(); String createdOn = IG.CreatedOn(); System.out.println("" + "MasterList_Gen_" + createdOn.trim() + ".xls"); 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=" + "MasterList_Gen_" + createdOn.trim() + ".xls"); OutputStream outStream = response.getOutputStream(); outStream.write(outArray); outStream.flush(); }
From source file:Sales.MainMenu.java
private void submitToPublishingPDFButtonActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_submitToPublishingPDFButtonActionPerformed String validityFrom = validityFromDatePicker.getJFormattedTextField().getText(); String validityTo = validityToDatePicker.getJFormattedTextField().getText(); String kkluNumber = kkluNumberTextField.getText(); String pol = pPolTextField.getText(); String pod = pPodTextField.getText(); String commClass = pCommodityClassComboBox.getSelectedItem().toString(); String handlingInstructions = pHandlingInstructions.getSelectedItem().toString(); String commDesc = pCommodityDescriptionTextField.getText(); String oft = pOftTextField.getText(); String oftUnit = pOftComboBox.getSelectedItem().toString(); String baf = null;/* w ww.java 2s. c o m*/ String bafText = pBafTextField.getText(); Boolean bafIncluded = pBafIncludedCheckBox.isSelected(); String eca = null; String ecaText = pEcaTextField.getText(); String ecaUnit = pEcaComboBox.getSelectedItem().toString(); Boolean ecaIncluded = pEcaIncludedCheckBox.isSelected(); String thc = null; String thcText = "$" + pThcTextField.getText(); String thcUnit = pThcComboBox.getSelectedItem().toString(); Boolean thcIncluded = pThcIncludedCheckBox.isSelected(); Boolean thcSubjectToTariff = thcSubjectToTariffCheckBox.isSelected(); String wfg = null; String wfgText = pWfgTextField.getText(); String wfgUnit = pWfgComboBox.getSelectedItem().toString(); Boolean wfgIncluded = pWfgIncludedCheckBox.isSelected(); Boolean wfgSubjectToTariff = wfgSubjectToTariffCheckBox.isSelected(); String docFee = pDocFeeComboBox.getSelectedItem().toString(); Boolean docFeeIncluded = pDocFeeIncludedCheckBox.isSelected(); String comments = pCommentsTextArea.getText(); Boolean warRisk = pWarRiskCheckBox.isSelected(); String quoteID = pQuoteNumberTextField.getText(); String bookingNumber = pBookingNumberTextField.getText(); String storage = null; String storageText = storageTextField.getText(); String storageUnit = storageUnitComboBox.getSelectedItem().toString(); Boolean storageIncluded = storageIncludedCheckBox.isSelected(); Boolean storageSubjectToTariff = storageSubjectToTariffCheckBox.isSelected(); Boolean bafSubjectToTariff = bafSubjectToTariffCheckBox.isSelected(); Boolean ecaSubjectToTariff = ecaSubjectToTariffCheckBox.isSelected(); String TIME_STAMP = new SimpleDateFormat("MM/dd/yyyy HH:mm").format(Calendar.getInstance().getTime()); if (bafIncluded != true && bafSubjectToTariff != true) { baf = bafText; } else if (bafIncluded == true) { baf = "Included"; } else if (bafSubjectToTariff == true) { baf = "Subject to Tariff"; } if (ecaIncluded != true && ecaSubjectToTariff != true) { eca = ecaText; } else if (ecaIncluded == true) { eca = "Included"; } else if (ecaSubjectToTariff == true) { eca = "Subject to Tariff"; } else if (ecaSubjectToTariff == true) { eca = "Subject to Tariff"; } if (thcSubjectToTariff == true) { thc = "Subject to Tariff"; } else if (thcSubjectToTariff != true && thcIncluded != true) { thc = thcText; } else if (thcSubjectToTariff != true && thcIncluded == true) { thc = "Included"; } else if (thcIncluded == true && thcSubjectToTariff == true) { JOptionPane.showMessageDialog(null, "You can only select one"); } if (wfgSubjectToTariff == true) { wfg = "Subject to Tariff"; wfgUnit = "N/A"; } else if (wfgSubjectToTariff != true && wfgIncluded != true) { wfg = wfgText; } else if (wfgSubjectToTariff != true && wfgIncluded == true) { wfg = "Included"; } else if (wfgIncluded == true && wfgSubjectToTariff == true) { JOptionPane.showMessageDialog(null, "You can only select one"); } if (storageSubjectToTariff == true) { storage = "Subject to Tariff"; storageUnit = "N/A"; } else if (storageSubjectToTariff != true && storageIncluded != true) { storage = storageText; } else if (storageIncluded == true && storageSubjectToTariff != true) { storage = "Included"; } else if (storageIncluded == true && storageSubjectToTariff == true) { JOptionPane.showMessageDialog(null, "You can only select one"); } String sql = "INSERT INTO spotrates (validityFrom, validityTo, tariffNumber, pol, pod, bookingNumber, commClass, handlingInstructions, commDesc, oft, oftUnit, baf, bafIncluded, bafPerTariff, ecaBaf, ecaBafUnit, ecaIncluded, ecaPerTariff, thc, thcUnit,thcIncluded, thcPerTariff, wfg, wfgUnit, wfgIncluded, wfgPerTariff, storage, storageUnit, storageIncluded, storagePerTariff, docFee, docFeeIncluded, comments, quoteID, warRisk) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"; try { PreparedStatement ps = CONN.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS); ps.setString(1, validityFrom); ps.setString(2, validityTo); ps.setString(3, kkluNumber); ps.setString(4, pol); ps.setString(5, pod); ps.setString(6, bookingNumber); ps.setString(7, commClass); ps.setString(8, handlingInstructions); ps.setString(9, commDesc); ps.setString(10, oft); ps.setString(11, oftUnit); ps.setString(12, baf); ps.setBoolean(13, bafIncluded); ps.setBoolean(14, bafSubjectToTariff); ps.setString(15, eca); ps.setString(16, ecaUnit); ps.setBoolean(17, ecaIncluded); ps.setBoolean(18, ecaSubjectToTariff); ps.setString(19, thc); ps.setString(20, thcUnit); ps.setBoolean(21, thcIncluded); ps.setBoolean(22, thcSubjectToTariff); ps.setString(23, wfg); ps.setString(24, wfgUnit); ps.setBoolean(25, wfgIncluded); ps.setBoolean(26, wfgSubjectToTariff); ps.setString(27, storage); ps.setString(28, storageUnit); ps.setBoolean(29, storageIncluded); ps.setBoolean(30, storageSubjectToTariff); ps.setString(31, docFee); ps.setBoolean(32, docFeeIncluded); ps.setString(33, comments); ps.setString(34, quoteID); ps.setBoolean(35, warRisk); // Execute the update ps.executeUpdate(); //Return the auto-generated key ResultSet keys = ps.getGeneratedKeys(); int lastKey = 1; while (keys.next()) { lastKey = keys.getInt(1); } int pid = lastKey; Double oft1 = (Double) (Double.parseDouble(oft)); String eca1 = ""; switch (eca) { case "Included": eca1 = "Included"; break; case "Subject to Tariff": eca1 = "Subject to Tariff"; break; default: eca1 = eca; break; } String baf1; switch (baf) { case "Included": baf1 = "Included"; break; case "Subject To Tariff": baf1 = "Subject to Tariff"; break; default: baf1 = baf; break; } String thc1; switch (thc) { case "Included": thc1 = thc; break; case "Subject to Tariff": thc1 = "Subject to Tariff"; break; default: thc1 = "$" + thc + " per " + thcUnit; break; } switch (thcUnit) { case "FAS": thc1 = thcUnit; break; case "Subject to local charges": thc1 = thcUnit; default: break; } String wfg1; switch (wfg) { case "Included": wfg1 = wfg; break; case "Subject to Tariff": wfg1 = "Subject to Tariff"; break; default: wfg1 = "$" + wfg + " per " + wfgUnit; break; } switch (wfgUnit) { case "FAS": wfg1 = wfgUnit; break; case "Subject to local charges": wfg1 = wfgUnit; default: break; } /* *************************************** *************************************** *************************************** */ String filename = USER_HOME_FOLDER + "\\Desktop\\Publishing\\" + pol + " To " + pod + "; " + commDesc + " PID" + lastKey + ".xls"; HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet("KKLU" + kkluNumber); sheet.setColumnWidth(0, 650); sheet.setColumnWidth(1, 5742); sheet.setColumnWidth(2, 5920); sheet.setColumnWidth(3, 3668); sheet.setColumnWidth(4, 5711); //Bold Font HSSFFont font = workbook.createFont(); font.setBold(true); CellStyle style = workbook.createCellStyle(); style.setFont(font); //Currency cell type CellStyle currency = workbook.createCellStyle(); currency.setDataFormat((short) 7); //Percentage cell type CellStyle percentage = workbook.createCellStyle(); percentage.setDataFormat((short) 0xa); sheet.setDisplayGridlines(false); //Black medium sized border around cell CellStyle blackBorder = workbook.createCellStyle(); blackBorder.setBorderBottom(CellStyle.BORDER_MEDIUM); blackBorder.setBottomBorderColor(IndexedColors.BLACK.getIndex()); blackBorder.setBorderLeft(CellStyle.BORDER_MEDIUM); blackBorder.setLeftBorderColor(IndexedColors.BLACK.getIndex()); blackBorder.setBorderRight(CellStyle.BORDER_MEDIUM); blackBorder.setRightBorderColor(IndexedColors.BLACK.getIndex()); blackBorder.setBorderTop(CellStyle.BORDER_MEDIUM); blackBorder.setTopBorderColor(IndexedColors.BLACK.getIndex()); //Red font CellStyle redFontStyle = workbook.createCellStyle(); HSSFFont redFont = workbook.createFont(); redFont.setColor(HSSFColor.RED.index); redFontStyle.setFont(redFont); HSSFRow rowhead = sheet.createRow((short) 0); rowhead.createCell(3).setCellValue(validityFrom); HSSFRow row1 = sheet.createRow((short) 1); Cell cell = row1.createCell(1); cell.setCellValue("FILING REQUEST TO RICLFILE"); cell.setCellStyle(style); sheet.addMergedRegion(new CellRangeAddress(1, 1, 1, 3)); sheet.setPrintGridlines(false); HSSFRow row2 = sheet.createRow((short) 2); row2.createCell(0).setCellValue("A)"); row2.createCell(1).setCellValue("Tariff Number(KKLU):"); row2.createCell(2).setCellValue(kkluNumber); HSSFRow space0 = sheet.createRow((short) 3); HSSFRow row4 = sheet.createRow((short) 4); row4.createCell(0).setCellValue("B)"); row4.createCell(1).setCellValue("Commodity:"); row4.createCell(2).setCellValue(commDesc); HSSFRow space2 = sheet.createRow((short) 5); HSSFRow row5 = sheet.createRow((short) 6); row5.createCell(0).setCellValue("C)"); row5.createCell(1).setCellValue("POL:"); row5.createCell(2).setCellValue(pol); HSSFRow space3 = sheet.createRow((short) 7); HSSFRow row6 = sheet.createRow((short) 8); row6.createCell(0).setCellValue("D)"); row6.createCell(1).setCellValue("POD:"); row6.createCell(2).setCellValue(pod); HSSFRow space4 = sheet.createRow((short) 9); HSSFRow row7 = sheet.createRow((short) 10); row7.createCell(0).setCellValue("E)"); row7.createCell(1).setCellValue("Rate:"); Cell rate = row7.createCell(2); rate.setCellValue(oft1); rate.setCellStyle(currency); HSSFRow space5 = sheet.createRow((short) 11); HSSFRow row8 = sheet.createRow((short) 12); row8.createCell(0).setCellValue("F)"); row8.createCell(1).setCellValue("Rate Basis:"); row8.createCell(2).setCellValue(oftUnit); HSSFRow space6 = sheet.createRow((short) 13); HSSFRow row9 = sheet.createRow((short) 14); row9.createCell(0).setCellValue("G)"); row9.createCell(1).setCellValue("BAF:"); Cell Baf = row9.createCell(2); switch (baf1) { case "Included": Baf.setCellValue("Included"); break; case "Subject to Tariff": Baf.setCellValue("Subject to Tariff"); break; default: Baf.setCellValue(baf1); Baf.setCellStyle(percentage); break; } HSSFRow space7 = sheet.createRow((short) 15); HSSFRow row10 = sheet.createRow((short) 16); row10.createCell(0).setCellValue("H)"); row10.createCell(1).setCellValue("ECA BAF:"); Cell ecaBaf = row10.createCell(2); switch (eca) { case "Included": ecaBaf.setCellValue("Included"); break; case "Subject to Tariff": ecaBaf.setCellValue("Subject to Tariff"); break; default: ecaBaf.setCellValue(eca1 + " per " + ecaUnit); ecaBaf.setCellStyle(currency); break; } HSSFRow space8 = sheet.createRow((short) 17); HSSFRow row11 = sheet.createRow((short) 18); row11.createCell(0).setCellValue("I)"); row11.createCell(1).setCellValue("THC/WFG:"); row11.createCell(2).setCellValue(thc1 + " / " + wfg1); HSSFRow space = sheet.createRow((short) 19); HSSFRow row12 = sheet.createRow((short) 20); row12.createCell(0).setCellValue("J)"); row12.createCell(1).setCellValue("Storage:"); row12.createCell(2).setCellValue(storage); HSSFRow space10 = sheet.createRow((short) 21); HSSFRow row13 = sheet.createRow((short) 22); row13.createCell(0).setCellValue("K)"); row13.createCell(1).setCellValue("Doc Fee:"); row13.createCell(2).setCellValue(docFee); HSSFRow space11 = sheet.createRow((short) 23); HSSFRow row14 = sheet.createRow((short) 24); row14.createCell(0).setCellValue("L)"); row14.createCell(1).setCellValue("War Risk:"); HSSFRow space12 = sheet.createRow((short) 25); if (warRisk == true) { String warRiskPercentage = "3%"; row14.createCell(2).setCellValue(warRiskPercentage); } else if (warRisk != true) { String warRiskPercentage = "N/A"; row14.createCell(2).setCellValue(warRiskPercentage); } HSSFRow row15 = sheet.createRow((short) 26); row15.createCell(0).setCellValue("M)"); row15.createCell(1).setCellValue("Validity"); row15.createCell(2).setCellValue("Effective: " + validityFrom); HSSFRow row16 = sheet.createRow((short) 27); row16.createCell(2).setCellValue("Expiration: " + validityTo); HSSFRow space13 = sheet.createRow((short) 28); HSSFRow row17 = sheet.createRow((short) 29); row17.createCell(0).setCellValue("N)"); row17.createCell(1).setCellValue("Remarks"); row17.createCell(2).setCellValue(comments); HSSFRow space14 = sheet.createRow((short) 30); HSSFRow row18 = sheet.createRow((short) 31); row18.createCell(0).setCellValue("O)"); row18.createCell(1).setCellValue("Booking #:"); row18.createCell(2).setCellValue(bookingNumber); HSSFRow space15 = sheet.createRow((short) 32); HSSFRow row19 = sheet.createRow((short) 33); row19.createCell(0).setCellValue("P)"); row19.createCell(1).setCellValue("RQS #:"); row19.createCell(2).setCellValue(quoteID); HSSFRow space16 = sheet.createRow((short) 34); HSSFRow row20 = sheet.createRow((short) 35); row20.createCell(0).setCellValue("Q)"); row20.createCell(1).setCellValue("PID #:"); row20.createCell(2).setCellValue(pid); HSSFRow space17 = sheet.createRow((short) 36); HSSFRow space18 = sheet.createRow((short) 37); HSSFRow row21 = sheet.createRow((short) 38); row21.createCell(0).setCellValue(""); row21.createCell(1).setCellValue("For RICLFILE Use Only"); HSSFRow space19 = sheet.createRow((short) 39); HSSFRow row22 = sheet.createRow((short) 40); Cell comm = row22.createCell(1); comm.setCellValue("Commodity #:"); comm.setCellStyle(redFontStyle); row22.createCell(2).setCellValue(""); Cell desc = row22.createCell(3); desc.setCellValue("Description:"); desc.setCellStyle(redFontStyle); row22.createCell(4).setCellValue(""); HSSFRow space20 = sheet.createRow((short) 41); HSSFRow row24 = sheet.createRow((short) 42); Cell TLI = row24.createCell(1); TLI.setCellValue("TLI #:"); TLI.setCellStyle(redFontStyle); row24.createCell(2).setCellValue(""); HSSFRow space21 = sheet.createRow((short) 43); HSSFRow row26 = sheet.createRow((short) 44); Cell exp = row26.createCell(1); exp.setCellValue("Expiration: "); exp.setCellStyle(redFontStyle); row26.createCell(2).setCellValue(""); try (FileOutputStream fileOut = new FileOutputStream(filename)) { workbook.write(fileOut); } System.out.print("Your excel file has been generate"); String spotRateId = String.valueOf(lastKey); JOptionPane.showMessageDialog(null, "The spot filing (PID" + spotRateId + ") for " + quoteID + " has been succsefully generated."); pQuoteIDTextField.setText(""); validityFromDatePicker.getJFormattedTextField().setText(""); validityToDatePicker.getJFormattedTextField().setText(""); kkluNumberTextField.setText(""); pPolTextField.setText(""); pPodTextField.setText(""); pCommodityClassComboBox.setSelectedIndex(0); pHandlingInstructions.setSelectedIndex(0); pCommodityDescriptionTextField.setText(""); pOftTextField.setText(""); pOftComboBox.setSelectedItem(""); bafSubjectToTariffCheckBox.setSelected(false); pBafTextField.setText(""); pBafIncludedCheckBox.setSelected(false); pEcaTextField.setText(""); pEcaComboBox.setSelectedIndex(0); pEcaIncludedCheckBox.setSelected(false); ecaSubjectToTariffCheckBox.setSelected(false); pThcTextField.setText(""); pThcComboBox.setSelectedIndex(0); pThcIncludedCheckBox.setSelected(false); thcSubjectToTariffCheckBox.setSelected(false); pWfgTextField.setText(""); pWfgComboBox.setSelectedIndex(0); pWfgIncludedCheckBox.setSelected(false); wfgSubjectToTariffCheckBox.setSelected(false); pDocFeeComboBox.setSelectedIndex(0); pWarRiskCheckBox.setSelected(false); storageSubjectToTariffCheckBox.setSelected(false); pCommentsTextArea.setText(""); pBookingNumberTextField.setText(""); pQuoteNumberTextField.setText(""); pIDTextField.setText(""); } catch (SQLException | IOException e) { JOptionPane.showMessageDialog(null, "Error: " + e.getMessage()); System.out.println(e.getMessage()); } }
From source file:Sales.MainMenu.java
private void saveChangesPublishingPDFButtonActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_saveChangesPublishingPDFButtonActionPerformed // Save spot rate changes String validityFrom = validityFromDatePicker.getJFormattedTextField().getText(); String validityTo = validityToDatePicker.getJFormattedTextField().getText(); String kkluNumber = kkluNumberTextField.getText(); String pol = pPolTextField.getText(); String pod = pPodTextField.getText(); String commClass = pCommodityClassComboBox.getSelectedItem().toString(); String handlingInstructions = pHandlingInstructions.getSelectedItem().toString(); String commDesc = pCommodityDescriptionTextField.getText(); String oft = pOftTextField.getText(); String oftUnit = pOftComboBox.getSelectedItem().toString(); String baf = null;// www . ja va 2 s .co m String bafText = pBafTextField.getText(); Boolean bafIncluded = pBafIncludedCheckBox.isSelected(); String eca = null; String ecaText = pEcaTextField.getText(); String ecaUnit = pEcaComboBox.getSelectedItem().toString(); Boolean ecaIncluded = pEcaIncludedCheckBox.isSelected(); String thc = null; String thcText = "$" + pThcTextField.getText(); String thcUnit = pThcComboBox.getSelectedItem().toString(); Boolean thcIncluded = pThcIncludedCheckBox.isSelected(); Boolean thcSubjectToTariff = thcSubjectToTariffCheckBox.isSelected(); String wfg = null; String wfgText = pWfgTextField.getText(); String wfgUnit = pWfgComboBox.getSelectedItem().toString(); Boolean wfgIncluded = pWfgIncludedCheckBox.isSelected(); Boolean wfgSubjectToTariff = wfgSubjectToTariffCheckBox.isSelected(); String docFee = pDocFeeComboBox.getSelectedItem().toString(); Boolean docFeeIncluded = pDocFeeIncludedCheckBox.isSelected(); String comments = pCommentsTextArea.getText(); Boolean warRisk = pWarRiskCheckBox.isSelected(); String quoteID = pQuoteNumberTextField.getText(); String bookingNumber = pBookingNumberTextField.getText(); String storage = null; String storageText = storageTextField.getText(); String storageUnit = storageUnitComboBox.getSelectedItem().toString(); Boolean storageIncluded = storageIncludedCheckBox.isSelected(); Boolean storageSubjectToTariff = storageSubjectToTariffCheckBox.isSelected(); Boolean bafSubjectToTariff = bafSubjectToTariffCheckBox.isSelected(); Boolean ecaSubjectToTariff = ecaSubjectToTariffCheckBox.isSelected(); String ID = pQuoteIDTextField.getText(); String timeStamp = new SimpleDateFormat("MM/dd/yyyy HH:mm").format(Calendar.getInstance().getTime()); if (bafIncluded != true && bafSubjectToTariff != true) { baf = bafText; } else if (bafIncluded == true) { baf = "Included"; } else if (bafSubjectToTariff == true) { baf = "Subject to Tariff"; } if (ecaIncluded != true && ecaSubjectToTariff != true) { eca = ecaText; } else if (ecaIncluded == true) { eca = "Included"; } else if (ecaSubjectToTariff == true) { eca = "Subject to Tariff"; } else if (ecaSubjectToTariff == true) { eca = "Subject to Tariff"; } if (thcSubjectToTariff == true) { thc = "Subject to Tariff"; } else if (thcSubjectToTariff != true && thcIncluded != true) { thc = thcText; } else if (thcSubjectToTariff != true && thcIncluded == true) { thc = "Included"; } else if (thcIncluded == true && thcSubjectToTariff == true) { JOptionPane.showMessageDialog(null, "You can only select one"); } if (wfgSubjectToTariff == true) { wfg = "Subject to Tariff"; wfgUnit = "N/A"; } else if (wfgSubjectToTariff != true && wfgIncluded != true) { wfg = wfgText; } else if (wfgSubjectToTariff != true && wfgIncluded == true) { wfg = "Included"; } else if (wfgIncluded == true && wfgSubjectToTariff == true) { JOptionPane.showMessageDialog(null, "You can only select one"); } if (storageSubjectToTariff == true) { storage = "Subject to Tariff"; storageUnit = "N/A"; } else if (storageSubjectToTariff != true && storageIncluded != true) { storage = storageText; } else if (storageIncluded == true && storageSubjectToTariff != true) { storage = "Included"; } else if (storageIncluded == true && storageSubjectToTariff == true) { JOptionPane.showMessageDialog(null, "You can only select one"); } String sql = "UPDATE spotrates SET validityFrom=?, validityTo=?, tariffNumber=?, pol=?, pod=?, bookingNumber=?, commClass=?, handlingInstructions=?, commDesc=?, oft=?, oftUnit=?, baf=?,bafIncluded=?, bafPerTariff=?, ecaBaf=?, ecaBafUnit=?, ecaIncluded=?, ecaPerTariff=?, thc=?, thcUnit=?, thcIncluded=?, thcPerTariff=?, wfg=?, wfgUnit=?, wfgIncluded=?, wfgPerTariff=?, storage=?, storageUnit=?, storageIncluded=?, storagePerTariff=?, docFee=?, docFeeIncluded=?, comments=?, quoteID=?, warRisk=? WHERE ID=?"; try { PreparedStatement ps = CONN.prepareStatement(sql); ps.setString(1, validityFrom); ps.setString(2, validityTo); ps.setString(3, kkluNumber); ps.setString(4, pol); ps.setString(5, pod); ps.setString(6, bookingNumber); ps.setString(7, commClass); ps.setString(8, handlingInstructions); ps.setString(9, commDesc); ps.setString(10, oft); ps.setString(11, oftUnit); ps.setString(12, baf); ps.setBoolean(13, bafIncluded); ps.setBoolean(14, bafSubjectToTariff); ps.setString(15, eca); ps.setString(16, ecaUnit); ps.setBoolean(17, ecaIncluded); ps.setBoolean(18, ecaSubjectToTariff); ps.setString(19, thc); ps.setString(20, thcUnit); ps.setBoolean(21, thcIncluded); ps.setBoolean(22, thcSubjectToTariff); ps.setString(23, wfg); ps.setString(24, wfgUnit); ps.setBoolean(25, wfgIncluded); ps.setBoolean(26, wfgSubjectToTariff); ps.setString(27, storage); ps.setString(28, storageUnit); ps.setBoolean(29, storageIncluded); ps.setBoolean(30, storageSubjectToTariff); ps.setString(31, docFee); ps.setBoolean(32, docFeeIncluded); ps.setString(33, comments); ps.setString(34, quoteID); ps.setBoolean(35, warRisk); ps.setString(40, ID); ps.executeUpdate(); String addBookingNumber = "UPDATE allquotes SET bookingNumber='" + bookingNumber + "', publishingID='" + ID + "' WHERE ID='" + quoteID + "';"; PreparedStatement psAddBookingNumber = CONN.prepareStatement(addBookingNumber); psAddBookingNumber.executeUpdate(addBookingNumber); Double oft1 = (Double) (Double.parseDouble(oft)); String eca1 = ""; switch (eca) { case "Included": eca1 = "Included"; break; case "Subject to Tariff": eca1 = "Subject to Tariff"; break; default: eca1 = "$" + eca + " " + ecaUnit; break; } String baf1; switch (baf) { case "Included": baf1 = "Included"; break; case "Subject To Tariff": baf1 = "Subject to Tariff"; break; default: baf1 = baf + "%"; break; } String thc1; switch (thc) { case "Included": thc1 = thc; break; case "Subject to Tariff": thc1 = "Subject to Tariff"; break; default: thc1 = "$" + thc + " per " + thcUnit; break; } switch (thcUnit) { case "FAS": thc1 = thcUnit; break; case "Subject to local charges": thc1 = thcUnit; default: break; } String wfg1; switch (wfg) { case "Included": wfg1 = wfg; break; case "Subject to Tariff": wfg1 = "Subject to Tariff"; break; default: wfg1 = "$" + wfg + " per " + wfgUnit; break; } switch (wfgUnit) { case "FAS": wfg1 = wfgUnit; break; case "Subject to local charges": wfg1 = wfgUnit; default: break; } /* *************************************** *************************************** *************************************** */ String filename = USER_HOME_FOLDER + "\\Desktop\\Publishing\\" + pol + " To " + pod + "; " + commDesc + " PID" + ID + ".xls"; HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet("KKLU" + kkluNumber); sheet.setColumnWidth(0, 650); sheet.setColumnWidth(1, 5742); sheet.setColumnWidth(2, 5920); sheet.setColumnWidth(3, 3668); sheet.setColumnWidth(4, 5711); //Bold Font HSSFFont font = workbook.createFont(); font.setBold(true); CellStyle style = workbook.createCellStyle(); style.setFont(font); //Currency cell type CellStyle currency = workbook.createCellStyle(); currency.setDataFormat((short) 7); //Percentage cell type CellStyle percentage = workbook.createCellStyle(); percentage.setDataFormat((short) 0xa); sheet.setDisplayGridlines(false); //Black medium sized border around cell CellStyle blackBorder = workbook.createCellStyle(); blackBorder.setBorderBottom(CellStyle.BORDER_MEDIUM); blackBorder.setBottomBorderColor(IndexedColors.BLACK.getIndex()); blackBorder.setBorderLeft(CellStyle.BORDER_MEDIUM); blackBorder.setLeftBorderColor(IndexedColors.BLACK.getIndex()); blackBorder.setBorderRight(CellStyle.BORDER_MEDIUM); blackBorder.setRightBorderColor(IndexedColors.BLACK.getIndex()); blackBorder.setBorderTop(CellStyle.BORDER_MEDIUM); blackBorder.setTopBorderColor(IndexedColors.BLACK.getIndex()); //Red font CellStyle redFontStyle = workbook.createCellStyle(); HSSFFont redFont = workbook.createFont(); redFont.setColor(HSSFColor.RED.index); redFontStyle.setFont(redFont); HSSFRow rowhead = sheet.createRow((short) 0); rowhead.createCell(3).setCellValue(validityFrom); HSSFRow row1 = sheet.createRow((short) 1); Cell cell = row1.createCell(1); cell.setCellValue("FILING REQUEST TO RICLFILE"); cell.setCellStyle(style); sheet.addMergedRegion(new CellRangeAddress(1, 1, 1, 3)); sheet.setPrintGridlines(false); HSSFRow row2 = sheet.createRow((short) 2); row2.createCell(0).setCellValue("A)"); row2.createCell(1).setCellValue("Tariff Number(KKLU):"); row2.createCell(2).setCellValue(kkluNumber); HSSFRow space0 = sheet.createRow((short) 3); HSSFRow row4 = sheet.createRow((short) 4); row4.createCell(0).setCellValue("B)"); row4.createCell(1).setCellValue("Commodity:"); row4.createCell(2).setCellValue(commDesc); HSSFRow space2 = sheet.createRow((short) 5); HSSFRow row5 = sheet.createRow((short) 6); row5.createCell(0).setCellValue("C)"); row5.createCell(1).setCellValue("POL:"); row5.createCell(2).setCellValue(pol); HSSFRow space3 = sheet.createRow((short) 7); HSSFRow row6 = sheet.createRow((short) 8); row6.createCell(0).setCellValue("D)"); row6.createCell(1).setCellValue("POD:"); row6.createCell(2).setCellValue(pod); HSSFRow space4 = sheet.createRow((short) 9); HSSFRow row7 = sheet.createRow((short) 10); row7.createCell(0).setCellValue("E)"); row7.createCell(1).setCellValue("Rate:"); Cell rate = row7.createCell(2); rate.setCellValue(oft1); rate.setCellStyle(currency); HSSFRow space5 = sheet.createRow((short) 11); HSSFRow row8 = sheet.createRow((short) 12); row8.createCell(0).setCellValue("F)"); row8.createCell(1).setCellValue("Rate Basis:"); row8.createCell(2).setCellValue(oftUnit); HSSFRow space6 = sheet.createRow((short) 13); HSSFRow row9 = sheet.createRow((short) 14); row9.createCell(0).setCellValue("G)"); row9.createCell(1).setCellValue("BAF:"); Cell Baf = row9.createCell(2); switch (baf1) { case "Included": Baf.setCellValue("Included"); break; case "Subject to Tariff": Baf.setCellValue("Subject to Tariff"); break; default: Baf.setCellValue(baf1); break; } HSSFRow space7 = sheet.createRow((short) 15); HSSFRow row10 = sheet.createRow((short) 16); row10.createCell(0).setCellValue("H)"); row10.createCell(1).setCellValue("ECA BAF:"); Cell ecaBaf = row10.createCell(2); switch (eca) { case "Included": ecaBaf.setCellValue("Included"); break; case "Subject to Tariff": ecaBaf.setCellValue("Subject to Tariff"); break; default: ecaBaf.setCellValue("$" + eca1 + " per " + ecaUnit); ecaBaf.setCellStyle(currency); break; } HSSFRow space8 = sheet.createRow((short) 17); HSSFRow row11 = sheet.createRow((short) 18); row11.createCell(0).setCellValue("I)"); row11.createCell(1).setCellValue("THC/WFG:"); row11.createCell(2).setCellValue(thc1 + " / " + wfg1); HSSFRow space = sheet.createRow((short) 19); HSSFRow row12 = sheet.createRow((short) 20); row12.createCell(0).setCellValue("J)"); row12.createCell(1).setCellValue("Storage:"); row12.createCell(2).setCellValue(storage); HSSFRow space10 = sheet.createRow((short) 21); HSSFRow row13 = sheet.createRow((short) 22); row13.createCell(0).setCellValue("K)"); row13.createCell(1).setCellValue("Doc Fee:"); row13.createCell(2).setCellValue(docFee); HSSFRow space11 = sheet.createRow((short) 23); HSSFRow row14 = sheet.createRow((short) 24); row14.createCell(0).setCellValue("L)"); row14.createCell(1).setCellValue("War Risk:"); HSSFRow space12 = sheet.createRow((short) 25); if (warRisk == true) { String warRiskPercentage = "3%"; row14.createCell(2).setCellValue(warRiskPercentage); } else if (warRisk != true) { String warRiskPercentage = "N/A"; row14.createCell(2).setCellValue(warRiskPercentage); } HSSFRow row15 = sheet.createRow((short) 26); row15.createCell(0).setCellValue("M)"); row15.createCell(1).setCellValue("Validity"); row15.createCell(2).setCellValue("Effective: " + validityFrom); HSSFRow row16 = sheet.createRow((short) 27); row16.createCell(2).setCellValue("Expiration: " + validityTo); HSSFRow space13 = sheet.createRow((short) 28); HSSFRow row17 = sheet.createRow((short) 29); row17.createCell(0).setCellValue("N)"); row17.createCell(1).setCellValue("Remarks"); row17.createCell(2).setCellValue(comments); HSSFRow space14 = sheet.createRow((short) 30); HSSFRow row18 = sheet.createRow((short) 31); row18.createCell(0).setCellValue("O)"); row18.createCell(1).setCellValue("Booking #:"); row18.createCell(2).setCellValue(bookingNumber); HSSFRow space15 = sheet.createRow((short) 32); HSSFRow row19 = sheet.createRow((short) 33); row19.createCell(0).setCellValue("P)"); row19.createCell(1).setCellValue("RQS #:"); row19.createCell(2).setCellValue(quoteID); HSSFRow space16 = sheet.createRow((short) 34); HSSFRow row20 = sheet.createRow((short) 35); row20.createCell(0).setCellValue("Q)"); row20.createCell(1).setCellValue("PID #:"); row20.createCell(2).setCellValue(ID); HSSFRow space17 = sheet.createRow((short) 36); HSSFRow space18 = sheet.createRow((short) 37); HSSFRow row21 = sheet.createRow((short) 38); row21.createCell(0).setCellValue(""); row21.createCell(1).setCellValue("For RICLFILE Use Only"); HSSFRow space19 = sheet.createRow((short) 39); HSSFRow row22 = sheet.createRow((short) 40); Cell comm = row22.createCell(1); comm.setCellValue("Commodity #:"); comm.setCellStyle(redFontStyle); row22.createCell(2).setCellValue(""); Cell desc = row22.createCell(3); desc.setCellValue("Description:"); desc.setCellStyle(redFontStyle); row22.createCell(4).setCellValue(""); HSSFRow space20 = sheet.createRow((short) 41); HSSFRow row24 = sheet.createRow((short) 42); Cell TLI = row24.createCell(1); TLI.setCellValue("TLI #:"); TLI.setCellStyle(redFontStyle); row24.createCell(2).setCellValue(""); HSSFRow space21 = sheet.createRow((short) 43); HSSFRow row26 = sheet.createRow((short) 44); Cell exp = row26.createCell(1); exp.setCellValue("Expiration: "); exp.setCellStyle(redFontStyle); row26.createCell(2).setCellValue(""); try (FileOutputStream fileOut = new FileOutputStream(filename)) { workbook.write(fileOut); } System.out.print("Your excel file has been generate"); String spotRateId = String.valueOf(ID); JOptionPane.showMessageDialog(null, "PID" + ID + " has been successfully update."); pQuoteIDTextField.setText(""); validityFromDatePicker.getJFormattedTextField().setText(""); validityToDatePicker.getJFormattedTextField().setText(""); kkluNumberTextField.setText(""); pPolTextField.setText(""); pPodTextField.setText(""); pCommodityClassComboBox.setSelectedIndex(0); pHandlingInstructions.setSelectedIndex(0); pCommodityDescriptionTextField.setText(""); pOftTextField.setText(""); pOftComboBox.setSelectedItem(""); bafSubjectToTariffCheckBox.setSelected(false); pBafTextField.setText(""); pBafIncludedCheckBox.setSelected(false); pEcaTextField.setText(""); pEcaComboBox.setSelectedItem(""); pEcaIncludedCheckBox.setSelected(false); ecaSubjectToTariffCheckBox.setSelected(false); pThcTextField.setText(""); pThcComboBox.setSelectedIndex(0); pThcIncludedCheckBox.setSelected(false); thcSubjectToTariffCheckBox.setSelected(false); pWfgTextField.setText(""); pWfgComboBox.setSelectedIndex(0); pWfgIncludedCheckBox.setSelected(false); wfgSubjectToTariffCheckBox.setSelected(false); pDocFeeComboBox.setSelectedIndex(0); pWarRiskCheckBox.setSelected(false); storageSubjectToTariffCheckBox.setSelected(false); pCommentsTextArea.setText(""); pBookingNumberTextField.setText(""); storageTextField.setText(""); storageUnitComboBox.setSelectedIndex(0); storageIncludedCheckBox.setSelected(false); pQuoteNumberTextField.setText(""); pIDTextField.setText(""); } catch (SQLException | IOException e) { JOptionPane.showMessageDialog(null, "Error: " + e.getMessage()); System.out.println(e.getMessage()); } }
From source file:senselogic.excelbundle.ExcelExporter.java
License:Apache License
/** * Create a sheet and fill it with stuff. */// w w w .j a v a2 s . c o m private void createSheet(String sheetName, LanguagePack referencePack) { HSSFSheet sheet = wb.createSheet(sheetName); sheet.protectSheet("excelbundle"); sheet.setDisplayGridlines(false); sheet.createFreezePane(1, 0, 1, 0); //Set some column widths: int numCols = langPacks.size() + 2; for (short i = 0; i <= numCols; i++) sheet.setColumnWidth(i, 10000); //Now lets fill it with stuff HSSFRow r; HSSFCell c; int rowIndex = 0; int colIndex; for (LanguageFile refLangFile : referencePack.getLanguageFiles()) { //Bundle path r = sheet.createRow(rowIndex); c = r.createCell(0); c.setCellStyle(bundlePathStyle); c.setCellValue(new HSSFRichTextString(refLangFile.getPath())); rowIndex++; //"Table header" r = sheet.createRow(rowIndex); colIndex = 1; for (LanguagePack pack : langPacks.values()) { c = r.createCell(colIndex); c.setCellStyle(languageStyle); c.setCellValue(new HSSFRichTextString(pack.getLanguage())); colIndex++; } rowIndex++; //Keys and values for (LanguageFile.KeyValuePair pair : refLangFile.getPairs()) { r = sheet.createRow(rowIndex); r.setHeightInPoints(16); c = r.createCell(0); c.setCellStyle(keyStyle); c.setCellValue(new HSSFRichTextString(pair.getKey())); colIndex = 1; for (LanguagePack pack : langPacks.values()) { c = r.createCell(colIndex); String value = null; LanguageFile langFile = pack.getLanguageFile(refLangFile.getPath()); if (langFile != null) value = langFile.getValue(pair.getKey()); if (value == null) { if (redmark) c.setCellStyle(missingStyle); else c.setCellStyle(valueStyle); } else { c.setCellStyle(valueStyle); c.setCellValue(new HSSFRichTextString(value)); } colIndex++; } rowIndex++; } rowIndex++; } //End of sheet creation }