List of usage examples for org.apache.poi.hssf.usermodel HSSFFont BOLDWEIGHT_BOLD
short BOLDWEIGHT_BOLD
To view the source code for org.apache.poi.hssf.usermodel HSSFFont BOLDWEIGHT_BOLD.
Click Source Link
From source file:pe.gob.mef.gescon.web.ui.WikiMB.java
public void postProcessXLS(Object document) { HSSFWorkbook wb = (HSSFWorkbook) document; HSSFSheet sheet = wb.getSheetAt(0);/* w ww . j a v a 2 s. c o m*/ // Para la cabecera HSSFRow header = sheet.getRow(0); HSSFCellStyle headerStyle = wb.createCellStyle(); HSSFFont font = wb.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); headerStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); headerStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); headerStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); headerStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); headerStyle.setFont(font); for (int i = 0; i < header.getPhysicalNumberOfCells(); i++) { HSSFCell cell = header.getCell(i); cell.setCellStyle(headerStyle); sheet.autoSizeColumn(i); } HSSFCellStyle centerStyle = wb.createCellStyle(); centerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFCellStyle centerGrayStyle = wb.createCellStyle(); centerGrayStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); centerGrayStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); centerGrayStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); HSSFCellStyle grayBG = wb.createCellStyle(); grayBG.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); grayBG.setFillPattern(CellStyle.SOLID_FOREGROUND); int i = 1; for (Conocimiento c : this.getListaWiki()) { HSSFRow row = sheet.getRow(i); for (int j = 0; j < row.getPhysicalNumberOfCells(); j++) { HSSFCell cell = row.getCell(j); if (i % 2 == 0) { if (j > 0) { cell.setCellStyle(centerGrayStyle); } else { cell.setCellStyle(grayBG); } } else { if (j > 0) { cell.setCellStyle(centerStyle); } } } i++; } }
From source file:poi.hssf.usermodel.examples.CellComments.java
License:Apache License
public static void main(String[] args) throws IOException { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet("Cell comments in POI HSSF"); // Create the drawing patriarch. This is the top level container for all shapes including cell comments. HSSFPatriarch patr = sheet.createDrawingPatriarch(); //create a cell in row 3 HSSFCell cell1 = sheet.createRow(3).createCell(1); cell1.setCellValue(new HSSFRichTextString("Hello, World")); //anchor defines size and position of the comment in worksheet HSSFComment comment1 = patr.createComment(new HSSFClientAnchor(0, 0, 0, 0, (short) 4, 2, (short) 6, 5)); // set text in the comment comment1.setString(new HSSFRichTextString("We can set comments in POI")); //set comment author. //you can see it in the status bar when moving mouse over the commented cell comment1.setAuthor("Apache Software Foundation"); // The first way to assign comment to a cell is via HSSFCell.setCellComment method cell1.setCellComment(comment1);/*ww w .j a va 2 s . c o m*/ //create another cell in row 6 HSSFCell cell2 = sheet.createRow(6).createCell(1); cell2.setCellValue(36.6); HSSFComment comment2 = patr.createComment(new HSSFClientAnchor(0, 0, 0, 0, (short) 4, 8, (short) 6, 11)); //modify background color of the comment comment2.setFillColor(204, 236, 255); HSSFRichTextString string = new HSSFRichTextString("Normal body temperature"); //apply custom font to the text in the comment HSSFFont font = wb.createFont(); font.setFontName("Arial"); font.setFontHeightInPoints((short) 10); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); font.setColor(HSSFColor.RED.index); string.applyFont(font); comment2.setString(string); comment2.setVisible(true); //by default comments are hidden. This one is always visible. comment2.setAuthor("Bill Gates"); /** * The second way to assign comment to a cell is to implicitly specify its row and column. * Note, it is possible to set row and column of a non-existing cell. * It works, the comment is visible. */ comment2.setRow(6); comment2.setColumn(1); FileOutputStream out = new FileOutputStream("poi_comment.xls"); wb.write(out); out.close(); }
From source file:poi.hssf.usermodel.examples.RepeatingRowsAndColumns.java
License:Apache License
public static void main(String[] args) throws IOException { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet1 = wb.createSheet("first sheet"); wb.createSheet("second sheet"); wb.createSheet("third sheet"); HSSFFont boldFont = wb.createFont(); boldFont.setFontHeightInPoints((short) 22); boldFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); HSSFCellStyle boldStyle = wb.createCellStyle(); boldStyle.setFont(boldFont);//from ww w . j a va2 s . c o m HSSFRow row = sheet1.createRow(1); HSSFCell cell = row.createCell(0); cell.setCellValue("This quick brown fox"); cell.setCellStyle(boldStyle); // Set the columns to repeat from column 0 to 2 on the first sheet wb.setRepeatingRowsAndColumns(0, 0, 2, -1, -1); // Set the rows to repeat from row 0 to 2 on the second sheet. wb.setRepeatingRowsAndColumns(1, -1, -1, 0, 2); // Set the the repeating rows and columns on the third sheet. wb.setRepeatingRowsAndColumns(2, 4, 5, 1, 2); FileOutputStream fileOut = new FileOutputStream("workbook.xls"); wb.write(fileOut); fileOut.close(); }
From source file:poi.HSSFReadWrite.java
License:Apache License
/** * given a filename this outputs a sample sheet with just a set of * rows/cells.// w w w .j av a2s . c o m */ private static void testCreateSampleSheet(String outputFilename) throws IOException { int rownum; HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet s = wb.createSheet(); HSSFCellStyle cs = wb.createCellStyle(); HSSFCellStyle cs2 = wb.createCellStyle(); HSSFCellStyle cs3 = wb.createCellStyle(); HSSFFont f = wb.createFont(); HSSFFont f2 = wb.createFont(); f.setFontHeightInPoints((short) 12); f.setColor((short) 0xA); f.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); f2.setFontHeightInPoints((short) 10); f2.setColor((short) 0xf); f2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); cs.setFont(f); cs.setDataFormat(HSSFDataFormat.getBuiltinFormat("($#,##0_);[Red]($#,##0)")); cs2.setBorderBottom(HSSFCellStyle.BORDER_THIN); cs2.setFillPattern((short) 1); // fill w fg cs2.setFillForegroundColor((short) 0xA); cs2.setFont(f2); wb.setSheetName(0, "HSSF Test"); for (rownum = 0; rownum < 300; rownum++) { HSSFRow r = s.createRow(rownum); if ((rownum % 2) == 0) { r.setHeight((short) 0x249); } for (int cellnum = 0; cellnum < 50; cellnum += 2) { HSSFCell c = r.createCell(cellnum); c.setCellValue(rownum * 10000 + cellnum + (((double) rownum / 1000) + ((double) cellnum / 10000))); if ((rownum % 2) == 0) { c.setCellStyle(cs); } c = r.createCell(cellnum + 1); c.setCellValue(new HSSFRichTextString("TEST")); // 50 characters divided by 1/20th of a point s.setColumnWidth(cellnum + 1, (int) (50 * 8 / 0.05)); if ((rownum % 2) == 0) { c.setCellStyle(cs2); } } } // draw a thick black border on the row at the bottom using BLANKS rownum++; rownum++; HSSFRow r = s.createRow(rownum); cs3.setBorderBottom(HSSFCellStyle.BORDER_THICK); for (int cellnum = 0; cellnum < 50; cellnum++) { HSSFCell c = r.createCell(cellnum); c.setCellStyle(cs3); } s.addMergedRegion(new CellRangeAddress(0, 3, 0, 3)); s.addMergedRegion(new CellRangeAddress(100, 110, 100, 110)); // end draw thick black border // create a sheet, set its title then delete it wb.createSheet(); wb.setSheetName(1, "DeletedSheet"); wb.removeSheetAt(1); // end deleted sheet FileOutputStream out = new FileOutputStream(outputFilename); wb.write(out); out.close(); // wb.close(); }
From source file:PPMP.ppmpreport.java
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException, SQLException { response.setContentType("text/html;charset=UTF-8"); try {/*w w w .j av a2s . co m*/ /* 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 = 2018; int projectstartyear = 2018; int minimumcolumns = 9;//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("Times New Roman"); font.setColor((short) 0000); font.setBoldweight(HSSFFont.COLOR_NORMAL); CellStyle style = wb.createCellStyle(); style.setFont(font); style.setBorderTop(HSSFCellStyle.BORDER_THIN); style.setBorderBottom(HSSFCellStyle.BORDER_THIN); style.setBorderLeft(HSSFCellStyle.BORDER_THIN); style.setBorderRight(HSSFCellStyle.BORDER_THIN); style.setAlignment(HSSFCellStyle.ALIGN_LEFT); style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); style.setWrapText(true); CellStyle spstyle = wb.createCellStyle(); spstyle.setFont(font); spstyle.setBorderTop(HSSFCellStyle.BORDER_THIN); spstyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); spstyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); spstyle.setBorderRight(HSSFCellStyle.BORDER_THIN); spstyle.setAlignment(HSSFCellStyle.ALIGN_LEFT); spstyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); spstyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); spstyle.setWrapText(true); spstyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); System.out.println("Blue index:" + HSSFColor.BLUE.index); HSSFFont font2 = wb.createFont(); font2.setFontHeightInPoints((short) 12); font2.setFontName("Times New Roman"); 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); style2.setWrapText(true); 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.WHITE.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); HSSFFont fontx = wb.createFont(); fontx.setFontHeightInPoints((short) 12); fontx.setColor(HSSFColor.BLACK.index); fontx.setFontName("Times New Roman"); 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("Code"); headerone.add("Indicator"); 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("Code"); headertwo.add("Indicator"); 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' and inppmp='yes' order by tableNo,output "; dbConnect conn = new dbConnect(); conn.rs = conn.state.executeQuery(getindicators); int rownumber = 3; shet.setColumnWidth(1, 20000); shet.setColumnWidth(2, 2300); shet.setColumnWidth(0, 2100); shet.setColumnWidth(3, 2500); shet.setColumnWidth(4, 2500); shet.setColumnWidth(5, 2500); shet.setColumnWidth(6, 2500); shet.setColumnWidth(7, 2500); shet.setColumnWidth(8, 2500); shet.setColumnWidth(9, 2500); shet.setColumnWidth(10, 2500); shet.setColumnWidth(11, 2500); shet.setColumnWidth(12, 2500); shet.setColumnWidth(13, 2500); shet.setColumnWidth(14, 2500); 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; int rowgani = 1; while (conn.rs.next()) { System.out.println("SP__" + conn.rs.getString("output")); //merge subpartner row sp.add(conn.rs.getString("output")); 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); rwxa.setHeightInPoints(25); HSSFCell cl01 = rwxa.createCell(colpos); cl01.setCellValue(conn.rs.getString("output")); cl01.setCellStyle(spstyle); subpurposerowcopy = subpurposerow; //should merge entire row shet.addMergedRegion( new CellRangeAddress(subpurposerow, subpurposerow, 0, selectedyear - 2009)); rownumber++; } } else { subpurposerow = rownumber; //===================================================subpurpose======================== HSSFRow rwxa = shet.createRow(rownumber); rwxa.setHeightInPoints(25); HSSFCell cl01 = rwxa.createCell(colpos); cl01.setCellValue(conn.rs.getString("output")); cl01.setCellStyle(spstyle); subpurposerowcopy = subpurposerow; //should merge entire row shet.addMergedRegion( new CellRangeAddress(subpurposerow, subpurposerow, 0, selectedyear - 2009)); 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")); String tn = conn.rs.getString("tableno"); if (!tn.contains(".")) { if (isNumeric(tn)) { cl02.setCellValue(new Integer(tn)); } else { cl02.setCellValue(tn); } } else { cl02.setCellValue(tn); } 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 if (selectedyear >= 2018) { getdata = " select ROUND((sum(case when reportingPeriod='Q1' then ((men_numerator + women_numerator)) end)/sum(case when reportingPeriod='Q1' then ((men_denominator + women_denominator)) end))*100 ) as Q1, ROUND((sum(case when reportingPeriod='Q2' then ((men_numerator + women_numerator)) end)/sum(case when reportingPeriod='Q2' then ((men_denominator + women_denominator)) end))*100 ) as Q2, ROUND((sum(case when reportingPeriod='Q3' then ((men_numerator + women_numerator)) end)/sum(case when reportingPeriod='Q3' then ((men_denominator + women_denominator)) end))*100 ) as Q3, ROUND((sum(case when reportingPeriod='Q4' then ((men_numerator + women_numerator)) end)/sum(case when reportingPeriod='Q4' then ((men_denominator + women_denominator)) end))*100 ) as Q4 from indicatorachieved where titleID='" + conn.rs.getString("titleID") + "' and financialyear='" + selectedyear + "' group by titleID "; System.out.println("@@" + getdata); } else { 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 "; } } else { //combined i.e male and female if (selectedyear >= 2018) { getdata = "select ROUND((SUM(case when reportingPeriod='Q1' then numerator end)/SUM(case when reportingPeriod='Q1' then denominator end))*100) as Q1 , ROUND((SUM(case when reportingPeriod='Q2' then numerator end)/SUM(case when reportingPeriod='Q2' then denominator end))*100) as Q2, ROUND((SUM(case when reportingPeriod='Q3' then numerator end)/SUM(case when reportingPeriod='Q3' then denominator end))*100) as Q3, ROUND((SUM(case when reportingPeriod='Q4' then numerator end)/SUM(case when reportingPeriod='Q4' then denominator end))*100) as Q4 from indicatorachievedcombined where titleID='" + conn.rs.getString("titleID") + "' and financialyear='" + selectedyear + "' group by titleID "; } else { //use old way of averages 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 if (yearval >= 2018) { qry = " select ROUND((SUM(case when financialYear='" + yearval + "' then numerator end)/SUM(case when financialYear='" + yearval + "' then denominator end))*100) as y" + yearval + " from indicatorachievedcombined where financialyear='" + yearval + "' and titleID='" + conn.rs.getString("titleID") + "' group by titleID"; } else { qry = " select ROUND(AVG(totalAchieved)) as y" + yearval + " from indicatorachievedcombined where financialyear='" + yearval + "' and titleID='" + conn.rs.getString("titleID") + "' group by titleID"; } } else { if (yearval >= 2018) { qry = " select ROUND((sum(case when financialYear='" + yearval + "' then ((men_numerator + women_numerator)) end)/sum(case when financialYear='" + yearval + "' then ((men_denominator + women_denominator)) end))*100) as y" + yearval + " from indicatorachieved 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 else { System.out.println("failed:" + conn.rs.getString("cumulative_chooser") + " *** " + conn.rs.getString("title")); } } //end of else of non percents System.out.println("@ annual query" + 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) (((double) conn.rs1.getDouble(1) * 100) / (double) annualtarget) + "%"; //System.out.println("For id "+conn.rs.getString("titleID")+" "+achievednonpercent); } } } 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 && annualtarget != 0) { 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.getRow(rowgani).autoSizeColumn(e,true); } //shet.autoSizeColumn(1,false); //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:PPMP.ppmpreport_2.java
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException, SQLException { response.setContentType("text/html;charset=UTF-8"); try {//w w w. ja va 2s. c o m /* 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:ro.nextreports.engine.exporter.XlsExporter.java
License:Apache License
private HSSFCellStyle buildBandElementStyle(BandElement bandElement, Object value, int gridRow, int gridColumn, int colSpan) { Map<String, Object> style = buildCellStyleMap(bandElement, value, gridRow, gridColumn, colSpan); HSSFCellStyle cellStyle;/*from ww w . j ava2s . c o m*/ HSSFFont cellFont = null; int fontKey = -1; // we have to create new fonts and styles if some formatting conditions are met // also for subreports we may have a subreportCellStyle passed by ReportBandElement boolean cacheFont = false; boolean cacheAllFont = false; if ((modifiedStyle[gridRow][gridColumn]) || bean.isSubreport()) { fontKey = getFontKey(style); if (fontKey != -1) { cellFont = condFonts.get(fontKey); } cellStyle = wb.createCellStyle(); if (cellFont == null) { cellFont = wb.createFont(); cacheFont = true; } modifiedStyle[gridRow][gridColumn] = false; } else { cellStyle = styles[gridRow][gridColumn]; fontKey = getFontKey(style); if (fontKey != -1) { cellFont = fonts.get(fontKey); } if ((cellFont == null) && (bandElement != null)) { cellFont = wb.createFont(); cacheAllFont = true; } } // HSSFPalette cellPal = wb.getCustomPalette(); if (style.containsKey(StyleFormatConstants.FONT_FAMILY_KEY)) { String val = (String) style.get(StyleFormatConstants.FONT_FAMILY_KEY); cellFont.setFontName(val); } if (style.containsKey(StyleFormatConstants.FONT_SIZE)) { Float val = (Float) style.get(StyleFormatConstants.FONT_SIZE); cellFont.setFontHeightInPoints(val.shortValue()); } if (style.containsKey(StyleFormatConstants.FONT_COLOR)) { Color val = (Color) style.get(StyleFormatConstants.FONT_COLOR); cellFont.setColor(ExcelColorSupport.getNearestColor(val)); } if (style.containsKey(StyleFormatConstants.FONT_STYLE_KEY)) { if (StyleFormatConstants.FONT_STYLE_NORMAL.equals(style.get(StyleFormatConstants.FONT_STYLE_KEY))) { cellFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL); } if (StyleFormatConstants.FONT_STYLE_BOLD.equals(style.get(StyleFormatConstants.FONT_STYLE_KEY))) { cellFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); } if (StyleFormatConstants.FONT_STYLE_ITALIC.equals(style.get(StyleFormatConstants.FONT_STYLE_KEY))) { cellFont.setItalic(true); } if (StyleFormatConstants.FONT_STYLE_BOLDITALIC.equals(style.get(StyleFormatConstants.FONT_STYLE_KEY))) { cellFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); cellFont.setItalic(true); } } if (cacheFont && (fontKey != -1)) { condFonts.put(fontKey, cellFont); } if (cacheAllFont && (fontKey != -1)) { fonts.put(fontKey, cellFont); } if (style.containsKey(StyleFormatConstants.BACKGROUND_COLOR)) { Color val = (Color) style.get(StyleFormatConstants.BACKGROUND_COLOR); cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); cellStyle.setFillForegroundColor(ExcelColorSupport.getNearestColor(val)); } if (style.containsKey(StyleFormatConstants.HORIZONTAL_ALIGN_KEY)) { if (StyleFormatConstants.HORIZONTAL_ALIGN_LEFT .equals(style.get(StyleFormatConstants.HORIZONTAL_ALIGN_KEY))) { cellStyle.setAlignment((short) 1); } if (StyleFormatConstants.HORIZONTAL_ALIGN_RIGHT .equals(style.get(StyleFormatConstants.HORIZONTAL_ALIGN_KEY))) { cellStyle.setAlignment((short) 3); } if (StyleFormatConstants.HORIZONTAL_ALIGN_CENTER .equals(style.get(StyleFormatConstants.HORIZONTAL_ALIGN_KEY))) { cellStyle.setAlignment((short) 2); } } if (style.containsKey(StyleFormatConstants.VERTICAL_ALIGN_KEY)) { if (StyleFormatConstants.VERTICAL_ALIGN_TOP .equals(style.get(StyleFormatConstants.VERTICAL_ALIGN_KEY))) { cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP); } if (StyleFormatConstants.VERTICAL_ALIGN_MIDDLE .equals(style.get(StyleFormatConstants.VERTICAL_ALIGN_KEY))) { cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); } if (StyleFormatConstants.VERTICAL_ALIGN_BOTTOM .equals(style.get(StyleFormatConstants.VERTICAL_ALIGN_KEY))) { cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_BOTTOM); } } else { cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); } short left = 0, right = 0, top = 0, bottom = 0; Color leftColor = Color.BLACK, rightColor = Color.BLACK, topColor = Color.BLACK, bottomColor = Color.BLACK; if (style.containsKey(StyleFormatConstants.BORDER_LEFT)) { Float val = (Float) style.get(StyleFormatConstants.BORDER_LEFT); // left = val.shortValue(); if (left == BORDER_THIN_VALUE) { cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); } if (left == BORDER_MEDIUM_VALUE) { cellStyle.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM); } if (left == BORDER_THICK_VALUE) { cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THICK); } Color color = (Color) style.get(StyleFormatConstants.BORDER_LEFT_COLOR); leftColor = color; cellStyle.setLeftBorderColor(ExcelColorSupport.getNearestColor(color)); } if (style.containsKey(StyleFormatConstants.BORDER_RIGHT)) { Float val = (Float) style.get(StyleFormatConstants.BORDER_RIGHT); // right = val.shortValue(); if (right == BORDER_THIN_VALUE) { cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); } if (right == BORDER_MEDIUM_VALUE) { cellStyle.setBorderRight(HSSFCellStyle.BORDER_MEDIUM); } if (right == BORDER_THICK_VALUE) { cellStyle.setBorderRight(HSSFCellStyle.BORDER_THICK); } Color color = (Color) style.get(StyleFormatConstants.BORDER_RIGHT_COLOR); rightColor = color; cellStyle.setRightBorderColor(ExcelColorSupport.getNearestColor(color)); } if (style.containsKey(StyleFormatConstants.BORDER_TOP)) { Float val = (Float) style.get(StyleFormatConstants.BORDER_TOP); // top = val.shortValue(); if (top == BORDER_THIN_VALUE) { cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); } if (top == BORDER_MEDIUM_VALUE) { cellStyle.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); } if (top == BORDER_THICK_VALUE) { cellStyle.setBorderTop(HSSFCellStyle.BORDER_THICK); } Color color = (Color) style.get(StyleFormatConstants.BORDER_TOP_COLOR); topColor = color; cellStyle.setTopBorderColor(ExcelColorSupport.getNearestColor(color)); } if (style.containsKey(StyleFormatConstants.BORDER_BOTTOM)) { Float val = (Float) style.get(StyleFormatConstants.BORDER_BOTTOM); // bottom = val.shortValue(); if (bottom == BORDER_THIN_VALUE) { cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); } if (bottom == BORDER_MEDIUM_VALUE) { cellStyle.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM); } if (bottom == BORDER_THICK_VALUE) { cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THICK); } Color color = (Color) style.get(StyleFormatConstants.BORDER_BOTTOM_COLOR); bottomColor = color; cellStyle.setBottomBorderColor(ExcelColorSupport.getNearestColor(color)); } border = new Border(left, right, top, bottom); border.setLeftColor(leftColor); border.setRightColor(rightColor); border.setTopColor(topColor); border.setBottomColor(bottomColor); if (cellFont != null) { cellStyle.setFont(cellFont); } if (style.containsKey(StyleFormatConstants.PATTERN)) { String pattern = (String) style.get(StyleFormatConstants.PATTERN); HSSFDataFormat format = wb.createDataFormat(); cellStyle.setDataFormat(format.getFormat(pattern)); } if (bandElement != null) { cellStyle.setWrapText(bandElement.isWrapText()); } cellStyle = updateSubreportBandElementStyle(cellStyle, bandElement, value, gridRow, gridColumn, colSpan); return cellStyle; }
From source file:ru.spb.nicetu.tableviewer.server.XlsToHtml.java
License:Apache License
private void fontStyle(CellStyle style, Formatter out, boolean isBuiltIn) { Font font = wb.getFontAt(style.getFontIndex()); if (font.getBoldweight() >= HSSFFont.BOLDWEIGHT_BOLD) styleOut("font-weight", "bold", out, isBuiltIn); if (font.getItalic()) styleOut("font-style", "italic", out, isBuiltIn); if (font.getFontName() != null && !"".equals(font.getFontName())) styleOut("font-family", font.getFontName(), out, isBuiltIn); int fontheight = font.getFontHeightInPoints(); if (fontheight == 9) { //fix for stupid ol Windows fontheight = 10;//from w w w . j ava 2 s . c o m } styleOut("font-size", "" + fontheight + "pt", out, isBuiltIn); // Font color is handled with the other colors }
From source file:senselogic.excelbundle.ExcelExporter.java
License:Apache License
/** * Do stuff like creating the workbook and creating the styles. */// w w w.j a v a 2 s . c o m private void initialize() { wb = new HSSFWorkbook(); //Create style for bundle path cells bundlePathStyle = wb.createCellStyle(); HSSFFont font = wb.createFont(); font.setFontHeightInPoints((short) 14); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); bundlePathStyle.setFont(font); bundlePathStyle.setLocked(true); //Create style for language column languageStyle = wb.createCellStyle(); font = wb.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); languageStyle.setFont(font); languageStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); languageStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); languageStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); languageStyle.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM); languageStyle.setLocked(true); //Create style for key cells keyStyle = wb.createCellStyle(); font = wb.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); keyStyle.setFont(font); keyStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); keyStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); keyStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); keyStyle.setBorderRight(HSSFCellStyle.BORDER_MEDIUM); keyStyle.setLocked(true); //Create style for value cells valueStyle = wb.createCellStyle(); valueStyle.setWrapText(true); valueStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); valueStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); valueStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); valueStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); valueStyle.setLocked(false); if (redmark) { //Create style for cells where values are missing missingStyle = wb.createCellStyle(); missingStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); missingStyle.setFillForegroundColor(HSSFColor.ORANGE.index); missingStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); missingStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); missingStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); missingStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); missingStyle.setLocked(false); } }
From source file:servlets.ReportesCSV.java
@Override public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { Connection con = null;/*from ww w. j a va 2s . c o m*/ PreparedStatement ps = null; ResultSet rs = null; String fechaInicial = request.getParameter("fechaInicial"); String fechaFinal = request.getParameter("fechaFinal"); String url = getServletContext().getRealPath("/"); String identificacion = request.getParameter("identificacion"); String producto = request.getParameter("producto"); String cotizacion = request.getParameter("cotizacion"); int sede = Integer.parseInt(request.getParameter("sede")); String tipo = request.getParameter("tipo"); String query = null; try { con = DBConnector.getInstance().getConnection(); switch (tipo) { case "rotacion": query = "SELECT p.codigoInterno,p.nomProducto,sum(cantidad),mim.nombreMovimiento,p.costo " + "FROM kalamarypos.inv_movimiento m " + "inner join inv_movimiento_detalle md on md.inv_movimiento_numDoc = m.numDoc " + "inner join cfg_mov_inventario_detalle mi on mi.idMovInventarioDetalle = m.cfg_mov_inventario_detalle_idMovInventarioDetalle " + "inner join cfg_mov_inventario_maestro mim on mim.idMovInventarioMaestro = mi.cfg_mov_inventario_maestro_idMovInventarioMaestro " + "inner join cfg_producto p on p.idProducto = md.cfg_producto_IdProducto " + "where fecha between ? and ADDDATE(?, INTERVAL 13 DAY) " + "and cfg_empresasede_idSede = ? " + "group by p.codProducto,p.nomProducto,mim.nombreMovimiento " + "order by 3 desc"; break; case "vc": query = "SELECT f.fecCrea,d.prefijoDoc, fc.fac_documentosmaster_numDocumento,ifnull(di.valorImpuesto,0) iva,f.subtotal-f.descuento as subtotal,f.total, " + "concat(ti.abreviatura,' ',c.numDoc,' ',c.nom1Cliente,' ',c.nom2Cliente,' ',c.apellido1,' ',c.apellido2) as nombre " + "FROM kalamarypos.fac_cartera_cliente fc " + "inner join fac_documentosmaster f on fc.fac_documentosmaster_numDocumento = f.numDocumento " + "inner join cfg_cliente c on c.idCliente = fc.cfg_cliente_idCliente " + "inner join cfg_documento d on d.cfg_empresasede_idSede = f.cfg_empresasede_idSede " + "inner join cfg_tipoidentificacion ti on ti.id = c.cfg_tipoidentificacion_id " + "left join fac_documentoimpuesto di on di.fac_documentosmaster_numDocumento = fc.fac_documentosmaster_numDocumento and di.porcentajeImpuesto=16 and di.fac_documentosmaster_cfg_documento_idDoc=f.cfg_documento_idDoc " + "where f.estado!='CANCELADA' and f.estado!='ANULADA' " + "and f.cfg_empresasede_idSede=? " + "and d.codDocumento !=6 " + "and f.cfg_documento_idDoc =d.idDoc "; if (!identificacion.equals("0")) { query = query + " and c.numDoc = ? "; } query = query + " order by 1 desc"; break; case "productosVendidos": query = "SELECT c.nombreCategoria, " + " r.nombreReferencia, " + " m.nombreMarca, " + " p.codigoInterno, " + " p.nomProducto, " + " sum(fd.cantidad) as total, " + " max(f.fecCrea) as maxFeCrea " + "FROM kalamarypos.fac_cartera_cliente fc " + "inner join fac_documentosmaster f on fc.fac_documentosmaster_numDocumento = f.numDocumento " + "inner join cfg_documento d on d.cfg_empresasede_idSede = f.cfg_empresasede_idSede " + "inner join fac_documentodetalle fd on fd.fac_documentosmaster_cfg_documento_idDoc=f.cfg_documento_idDoc " + "inner join cfg_producto p on p.idProducto = fd.cfg_producto_idProducto " + "inner join cfg_categoriaproducto c on c.idCategoria = p.cfg_categoriaproducto_idCategoria " + "inner join cfg_referenciaproducto r on r.idReferencia = p.cfg_referenciaproducto_idReferencia " + "inner join cfg_marcaproducto m on m.idMarca = p.cfg_marcaproducto_idMarca " + "where f.estado!='CANCELADA' and f.estado!='ANULADA' " + "and f.cfg_empresasede_idSede=? " + "and fd.fac_documentosmaster_numDocumento=f.numDocumento " + "and f.cfg_documento_idDoc =d.idDoc " + "and f.fecCrea between ? and ADDDATE(?, INTERVAL 1 DAY) " + "and fd.fac_documentosmaster_numDocumento = f.numDocumento " + "group by fd.cfg_producto_idProducto " + "order by 6 desc "; break;//productosVendidos case "ventasVendedores": query = "SELECT f.fecCrea,d.prefijoDoc, fc.fac_documentosmaster_numDocumento,ifnull(di.valorImpuesto,0) iva,f.subtotal-f.descuento as subtotal,f.total, " + "concat('CC ',s.numDoc,' ',s.nom1Usuario,' ',s.nom2Usuario,' ',s.apellido1,' ',s.apellido2) as nombre " + "FROM kalamarypos.fac_cartera_cliente fc " + "inner join fac_documentosmaster f on fc.fac_documentosmaster_numDocumento = f.numDocumento " + "inner join cfg_documento d on d.cfg_empresasede_idSede = f.cfg_empresasede_idSede " + "inner join seg_usuario s on s.idUsuario = f.seg_usuario_idUsuario1 " + " left join fac_documentoimpuesto di on di.fac_documentosmaster_numDocumento = fc.fac_documentosmaster_numDocumento and di.porcentajeImpuesto=16 and di.fac_documentosmaster_cfg_documento_idDoc=f.cfg_documento_idDoc " + "where f.estado!='CANCELADA' and f.estado!='ANULADA' " + "and f.cfg_empresasede_idSede=? " + "and f.fecCrea between ? and ADDDATE(?, INTERVAL 13 DAY) " + "and f.cfg_documento_idDoc =d.idDoc " + "and d.codDocumento !=6 " + "and s.cfg_rol_idrol=3 "; if (!identificacion.equals("0")) { if (!identificacion.equals("")) { query = query + "and s.numDoc = ? "; } } query = query + " order by 1 desc"; break;//Ventas vendedores case "productosA": query = "select c.nombreCategoria, " + " r.nombreReferencia, " + " m.nombreMarca, " + " p.codigoInterno, " + " p.nomProducto, " + " p.precio " + "from cfg_producto p " + "inner join cfg_empresasede s on s.cfg_empresa_idEmpresa = p.cfg_empresa_idEmpresa " + "inner join cfg_categoriaproducto c on c.idCategoria = p.cfg_categoriaproducto_idCategoria " + "inner join cfg_referenciaproducto r on r.idReferencia = p.cfg_referenciaproducto_idReferencia " + "inner join cfg_marcaproducto m on m.idMarca = p.cfg_marcaproducto_idMarca " + "where s.idSede =? "; if (producto != null) { if (!producto.equals("")) { query = query + "and p.codigoInterno= ? "; } } break;//Productos case "servicios": query = "SELECT c.nombreCategoria, " + " r.nombreReferencia, " + " m.nombreMarca, " + " p.codigoInterno, " + " p.codBarProducto , " + " p.nomProducto, " + " p.costo, " + " p.utilidad, " + " p.precio, " + " p.fecCrea " + "FROM " + " cfg_producto p " + "inner join cfg_empresasede s on s.cfg_empresa_idEmpresa = p.cfg_empresa_idEmpresa " + "inner join cfg_categoriaproducto c on c.idCategoria = p.cfg_categoriaproducto_idCategoria " + "inner join cfg_referenciaproducto r on r.idReferencia = p.cfg_referenciaproducto_idReferencia " + "inner join cfg_marcaproducto m on m.idMarca = p.cfg_marcaproducto_idMarca " + "where p.esServicio=1 " + "and s.idSede =? "; if (producto != null) { if (!producto.equals("")) { query = query + "and p.codigoInterno= ? "; } } query = query + "order by 1 desc"; break;//Servicios case "stock": query = "SELECT c.nombreCategoria, " + " r.nombreReferencia, " + " m.nombreMarca, " + " p.codigoInterno, " + " p.nomProducto, " + " co.existencia " + "FROM kalamarypos.cfg_producto p " + "inner join inv_consolidado co on co.cfg_producto_idProducto = p.idProducto " + "inner join cfg_categoriaproducto c on c.idCategoria = p.cfg_categoriaproducto_idCategoria " + "inner join cfg_referenciaproducto r on r.idReferencia = p.cfg_referenciaproducto_idReferencia " + "inner join cfg_marcaproducto m on m.idMarca = p.cfg_marcaproducto_idMarca " + "where co.cfg_empresasede_idSede=? "; if (producto != null) { if (!producto.equals("")) { query = query + "and p.codigoInterno= ? "; } } query = query + " order by co.existencia desc"; break;//stock case "productoClientes": query = "SELECT concat(ti.abreviatura,' ',c.numDoc,' ',c.nom1Cliente,' ',c.nom2Cliente,' ',c.apellido1,' ',c.apellido2) as nombre, " + "fc.cfg_cliente_idCliente,fc.fac_documentosmaster_numDocumento,sum(cantidad) as total,fd.cfg_producto_idProducto, " + "max(f.fecCrea),concat(ca.nombreCategoria,' ',r.nombreReferencia,' ',m.nombreMarca,' ',p.codigoInterno,' ',p.nomProducto) as producto " + "FROM kalamarypos.fac_cartera_cliente fc " + "inner join fac_documentosmaster f on fc.fac_documentosmaster_numDocumento = f.numDocumento " + "inner join fac_documentodetalle fd on fd.fac_documentosmaster_cfg_documento_idDoc=f.cfg_documento_idDoc " + "inner join cfg_cliente c on c.idCliente = fc.cfg_cliente_idCliente " + "inner join cfg_documento d on d.cfg_empresasede_idSede = f.cfg_empresasede_idSede " + "inner join cfg_tipoidentificacion ti on ti.id = c.cfg_tipoidentificacion_id " + "inner join cfg_producto p on p.idProducto = fd.cfg_producto_idProducto " + "inner join cfg_categoriaproducto ca on ca.idCategoria = p.cfg_categoriaproducto_idCategoria " + "inner join cfg_referenciaproducto r on r.idReferencia = p.cfg_referenciaproducto_idReferencia " + "inner join cfg_marcaproducto m on m.idMarca = p.cfg_marcaproducto_idMarca " + "where f.estado!='CANCELADA' and f.estado!='ANULADA' " + "and f.cfg_empresasede_idSede=? " + "and fd.fac_documentosmaster_numDocumento = f.numDocumento " + "and d.codDocumento =1 "; if (producto != null) { if (!producto.equals("")) { query = query + "and p.codigoInterno= ? "; } } query = query + " group by fd.cfg_producto_idProducto " + "order by 4 desc"; break;//productosClientes case "cotizacion": query = "SELECT p.codigoInterno, " + "p.nomProducto, " + "p.precio, " + "ifnull(di.valorImpuesto,0) as iva, " + "f.total, " + "concat(ti.abreviatura,' ',cl.numDoc,' ',cl.nom1Cliente,' ',cl.nom2Cliente,' ',cl.apellido1,' TELEFONO ',cl.tel1) as nombre, " + "concat('No Cotizacin:',' ',d.prefijoDoc,' ', fc.fac_documentosmaster_numDocumento) cotizacion, " + "f.fecCrea, concat('Vendedor:',s.nom1Usuario,' ',s.nom2Usuario,' ',s.apellido1,' ',s.apellido2) as vendedor " + " " + "FROM kalamarypos.fac_documentosmaster fc " + "inner join fac_documentosmaster f on fc.fac_documentosmaster_numDocumento = f.numDocumento " + "inner join fac_documentodetalle fd on fd.fac_documentosmaster_cfg_documento_idDoc=f.cfg_documento_idDoc " + "inner join cfg_cliente cl on cl.idCliente = fc.cfg_cliente_idCliente " + "inner join cfg_documento d on d.cfg_empresasede_idSede = f.cfg_empresasede_idSede " + "inner join cfg_producto p on p.idProducto = fd.cfg_producto_idProducto " + "inner join cfg_categoriaproducto c on c.idCategoria = p.cfg_categoriaproducto_idCategoria " + "inner join cfg_referenciaproducto r on r.idReferencia = p.cfg_referenciaproducto_idReferencia " + "inner join cfg_marcaproducto m on m.idMarca = p.cfg_marcaproducto_idMarca " + "inner join cfg_tipoidentificacion ti on ti.id = cl.cfg_tipoidentificacion_id " + "inner join seg_usuario s on s.idUsuario = f.seg_usuario_idUsuario1 " + "left join fac_documentoimpuesto di on di.fac_documentosmaster_numDocumento = fc.fac_documentosmaster_numDocumento and di.porcentajeImpuesto=16 and di.fac_documentosmaster_cfg_documento_idDoc=f.cfg_documento_idDoc " + "where f.estado!='CANCELADA' and f.estado!='ANULADA' " + "and f.cfg_empresasede_idSede=? "; if (identificacion != null) { if (!identificacion.equals("")) query = query + "and cl.numDoc = ? "; } if (cotizacion != null) { if (!cotizacion.equals("")) query = query + "and fc.fac_documentosmaster_numDocumento = ? "; } query = query + " and d.codDocumento =6 " + "and fd.fac_documentosmaster_numDocumento=f.numDocumento " + "and f.cfg_documento_idDoc = d.idDoc " + "and s.cfg_rol_idrol=3"; break;//cotizacion case "vencimientosFacturas": query = "SELECT cc.numDoc, " + " concat(cc.nom1Cliente,' ',cc.nom2Cliente,' ',cc.apellido1,' ',cc.apellido2) cliente, " + " cc.tel1, " + " fc.valor, " + " fc.saldo, " + " fc.fecha_limite, " + " concat(d.prefijoDoc,' ',fd.numDocumento) documento " + "FROM kalamarypos.fac_cartera_cliente fc " + "inner join fac_documentosmaster fd on fc.fac_documentosmaster_numDocumento = fd.numDocumento " + "inner join cfg_cliente cc on cc.idCliente = fc.cfg_cliente_idCliente " + "inner join cfg_documento d on d.cfg_empresasede_idSede = fd.cfg_empresasede_idSede " + " where fc.estado='PENDIENTE' " + "and fecha_limite<=ADDDATE(now(), INTERVAL 15 DAY) " + "and fd.cfg_documento_idDoc = fc.fac_documentosmaster_cfg_documento_idDoc " + "and d.codDocumento =1 " + "and fd.cfg_documento_idDoc =d.idDoc " + "and fd.cfg_empresasede_idSede=? " + "and fd.cfg_documento_idDoc = fc.fac_documentosmaster_cfg_documento_idDoc " + "order by fecha_limite"; break;//vencimiento factruas case "vencimientosSeparados": query = "SELECT cc.numDoc, " + " concat(cc.nom1Cliente,' ',cc.nom2Cliente,' ',cc.apellido1,' ',cc.apellido2) cliente, " + " cc.tel1, " + " fc.valor, " + " fc.saldo, " + " fc.fecha_limite, " + " concat(d.prefijoDoc,' ',fd.numDocumento) documento " + "FROM kalamarypos.fac_cartera_cliente fc " + "inner join fac_documentosmaster fd on fc.fac_documentosmaster_numDocumento = fd.numDocumento " + "inner join cfg_cliente cc on cc.idCliente = fc.cfg_cliente_idCliente " + "inner join cfg_documento d on d.cfg_empresasede_idSede = fd.cfg_empresasede_idSede " + " where fc.estado='PENDIENTE' " + "and fecha_limite<=ADDDATE(now(), INTERVAL 15 DAY) " + "and fd.cfg_documento_idDoc = fc.fac_documentosmaster_cfg_documento_idDoc " + "and d.codDocumento =7 " + "and fd.cfg_empresasede_idSede=? " + "and fd.cfg_documento_idDoc =d.idDoc " + "and fd.cfg_documento_idDoc = fc.fac_documentosmaster_cfg_documento_idDoc " + "order by fecha_limite"; break;//vencimientoSeparados case "productosSinRotacion": query = "SELECT c.nombreCategoria, " + " r.nombreReferencia, " + " ma.nombreMarca, " + " p.codigoInterno, " + " p.nomProducto, " + " max(m.fecha) fechaultimo " + " " + "FROM kalamarypos.inv_movimiento_detalle im " + "inner join inv_movimiento m on im.inv_movimiento_numDoc = m.numDoc " + "left join cfg_producto p on p.idProducto = im.cfg_producto_idProducto " + "inner join cfg_categoriaproducto c on c.idCategoria = p.cfg_categoriaproducto_idCategoria " + "inner join cfg_referenciaproducto r on r.idReferencia = p.cfg_referenciaproducto_idReferencia " + "inner join cfg_marcaproducto ma on ma.idMarca = p.cfg_marcaproducto_idMarca " + "where cfg_empresasede_idSede=? " + "group by im.cfg_producto_idProducto " + "having DATEDIFF(now(),max(m.fecha))>=90 " + "order by 6"; break;//productosSinRotacion } ps = con.prepareStatement(query); ps.clearParameters(); int indice = 1; switch (tipo) { case "rotacion": ps.setString(indice++, fechaInicial); ps.setString(indice++, fechaFinal); ps.setInt(indice++, sede); break; case "vc": ps.setInt(indice++, sede); if (!identificacion.equals("0")) { ps.setString(indice++, identificacion); } break; case "productosVendidos": ps.setInt(indice++, sede); ps.setString(indice++, fechaInicial); ps.setString(indice++, fechaFinal); break; case "ventasVendedores": ps.setInt(indice++, sede); ps.setString(indice++, fechaInicial); ps.setString(indice++, fechaFinal); if (!identificacion.equals("0")) { if (!identificacion.equals("")) ps.setString(indice++, identificacion); } break; case "productosA": ps.setInt(indice++, sede); if (producto != null) { if (!producto.equals("")) { ps.setString(indice++, producto); } } break;//Producros case "servicios": ps.setInt(indice++, sede); if (producto != null) { if (!producto.equals("")) { ps.setString(indice++, producto); } } break;//servicios case "stock": ps.setInt(indice++, sede); if (producto != null) { if (!producto.equals("")) { ps.setString(indice++, producto); } } break;//stock case "productoClientes": ps.setInt(indice++, sede); if (producto != null) { if (!producto.equals("")) { ps.setString(indice++, producto); } } break;//productoClientes case "cotizacion": ps.setInt(indice++, sede); if (identificacion != null) { if (!identificacion.equals("")) ps.setString(indice++, identificacion); } if (cotizacion != null) { if (!cotizacion.equals("")) ps.setString(indice++, cotizacion); } break; case "vencimientosFacturas": ps.setInt(indice++, sede); break; case "vencimientosSeparados": ps.setInt(indice++, sede); break; case "productosSinRotacion": ps.setInt(indice++, sede); break; } rs = ps.executeQuery(); Date fechaActual = new Date(); SimpleDateFormat sd = new SimpleDateFormat("yyyy-MM-dd"); SimpleDateFormat sd2 = new SimpleDateFormat("dd-MMM-yyyy"); String rutaArchivo = url + "/informes/reportes/"; String nombreArchivo = null; switch (tipo) { case "rotacion": nombreArchivo = "rotacionProducto_" + sd.format(fechaActual) + "_" + sede + ".xls"; break; case "vc": nombreArchivo = "ventasPorCliente_" + sd.format(fechaActual) + "_" + sede + ".xls"; break; case "productosVendidos": nombreArchivo = "productosVendidos" + sd.format(fechaActual) + "_" + sede + ".xls"; break; case "ventasVendedores": nombreArchivo = "ventasVendedores" + sd.format(fechaActual) + "_" + sede + ".xls"; break; case "productosA": nombreArchivo = "productos" + sd.format(fechaActual) + "_" + sede + ".xls"; break; case "servicios": nombreArchivo = "servicios" + sd.format(fechaActual) + "_" + sede + ".xls"; break; case "stock": nombreArchivo = "stock" + sd.format(fechaActual) + "_" + sede + ".xls"; break; case "productoClientes": nombreArchivo = "ventasProductosClientes" + sd.format(fechaActual) + "_" + sede + ".xls"; break; case "cotizacion": nombreArchivo = "cotizacion" + sd.format(fechaActual) + "_" + sede + ".xls"; break; case "vencimientosFacturas": nombreArchivo = "vencimientoFacturas" + sd.format(fechaActual) + "_" + sede + ".xls"; break; case "vencimientosSeparados": nombreArchivo = "vencimientoSeparados" + sd.format(fechaActual) + "_" + sede + ".xls"; break; case "productosSinRotacion": nombreArchivo = "productosSinRotacion" + sd.format(fechaActual) + "_" + sede + ".xls"; break; } rutaArchivo = rutaArchivo + nombreArchivo; File archivo1 = new File(rutaArchivo); if (archivo1.exists()) { archivo1.delete(); } archivo1.createNewFile(); HSSFWorkbook libro = new HSSFWorkbook(); HSSFCellStyle cellStyle = libro.createCellStyle(); HSSFFont font = libro.createFont(); font.setFontName(HSSFFont.FONT_ARIAL); font.setFontHeightInPoints((short) 10); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); font.setColor(HSSFColor.BLACK.index); font.setCharSet(HSSFFont.ANSI_CHARSET); cellStyle.setFont(font); /*Se inicializa el flujo de datos con el archivo xls*/ FileOutputStream archi = new FileOutputStream(rutaArchivo); Sheet hoja; Cell celda; Row fila; int i = 0; switch (tipo) { case "rotacion": hoja = libro.createSheet("Rotacin de Producto"); fila = hoja.createRow(i); celda = fila.createCell(0); celda.setCellValue("CODIGO PRODUCTO"); celda.setCellStyle(cellStyle); celda = fila.createCell(1); celda.setCellValue("PRODUCTO"); celda.setCellStyle(cellStyle); celda = fila.createCell(2); celda.setCellValue("CANTIDAD MOVIMIENTO"); celda.setCellStyle(cellStyle); celda = fila.createCell(3); celda.setCellValue("TIPO MOVIMIENTO"); celda.setCellStyle(cellStyle); celda = fila.createCell(4); celda.setCellValue("COSTO UNITARIO"); celda.setCellStyle(cellStyle); celda = fila.createCell(5); celda.setCellValue("COSTO TOTAL"); celda.setCellStyle(cellStyle); i = i + 1; for (int j = 0; j <= 12; j++) { hoja.autoSizeColumn(j); } while (rs.next()) { fila = hoja.createRow(i); celda = fila.createCell(0); celda.setCellValue(rs.getString(1)); celda = fila.createCell(1); celda.setCellValue(rs.getString(2)); celda = fila.createCell(2); celda.setCellValue(rs.getInt(3)); celda = fila.createCell(3); celda.setCellValue(rs.getString(4)); celda = fila.createCell(4); celda.setCellValue(rs.getDouble(5)); celda = fila.createCell(5); celda.setCellValue((rs.getDouble(5) * rs.getInt(3))); ////celda.getStringCellValue().getBytes(Charset.forName("UTF-8")); i = i + 1; } break; //En rotacin case "vc": hoja = libro.createSheet("Ventas por clientes"); fila = hoja.createRow(i); celda = fila.createCell(0); celda.setCellValue("FECHA"); celda.setCellStyle(cellStyle); celda = fila.createCell(1); celda.setCellValue("DOCUMENTO"); celda.setCellStyle(cellStyle); celda = fila.createCell(2); celda.setCellValue("CLIENTE"); celda.setCellStyle(cellStyle); celda = fila.createCell(3); celda.setCellValue("SUBTOTAL"); celda.setCellStyle(cellStyle); celda = fila.createCell(4); celda.setCellValue("IVA"); celda.setCellStyle(cellStyle); celda = fila.createCell(5); celda.setCellValue("COSTO TOTAL"); celda.setCellStyle(cellStyle); i = i + 1; for (int j = 0; j <= 12; j++) { hoja.autoSizeColumn(j); } while (rs.next()) { fila = hoja.createRow(i); celda = fila.createCell(0); celda.setCellValue(sd2.format(rs.getDate(1))); celda = fila.createCell(1); celda.setCellValue(rs.getString(2) + " " + rs.getString(3)); celda = fila.createCell(2); celda.setCellValue(rs.getString(7)); celda = fila.createCell(3); celda.setCellValue(rs.getDouble(5)); celda = fila.createCell(4); celda.setCellValue(rs.getDouble(4)); celda = fila.createCell(5); celda.setCellValue(rs.getDouble(6)); i = i + 1; } break;//Ventas por cliente case "productosVendidos": hoja = libro.createSheet("Productos Vendidos"); fila = hoja.createRow(i); celda = fila.createCell(0); celda.setCellValue("CATEGORIA"); celda.setCellStyle(cellStyle); celda = fila.createCell(1); celda.setCellValue("REFERENCIA"); celda.setCellStyle(cellStyle); celda = fila.createCell(2); celda.setCellValue("MARCA"); celda.setCellStyle(cellStyle); celda = fila.createCell(3); celda.setCellValue("CODIGO INTERNO"); celda.setCellStyle(cellStyle); celda = fila.createCell(4); celda.setCellValue("PRODUCTO"); celda.setCellStyle(cellStyle); celda = fila.createCell(5); celda.setCellValue("CANTIDAD VENDIDA"); celda.setCellStyle(cellStyle); celda = fila.createCell(6); celda.setCellValue("FECHA ULTIMA VENTA"); celda.setCellStyle(cellStyle); i = i + 1; for (int j = 0; j <= 12; j++) { hoja.autoSizeColumn(j); } while (rs.next()) { fila = hoja.createRow(i); celda = fila.createCell(0); celda.setCellValue(rs.getString(1)); celda = fila.createCell(1); celda.setCellValue(rs.getString(2)); celda = fila.createCell(2); celda.setCellValue(rs.getString(3)); celda = fila.createCell(3); celda.setCellValue(rs.getString(4)); celda = fila.createCell(4); celda.setCellValue(rs.getString(5)); celda = fila.createCell(5); celda.setCellValue(rs.getDouble(6)); celda = fila.createCell(6); celda.setCellValue(sd2.format(rs.getDate(7))); i = i + 1; } break;//Productos Vendidos case "ventasVendedores": hoja = libro.createSheet("Ventas Vendedores"); fila = hoja.createRow(i); celda = fila.createCell(0); celda.setCellValue("FECHA"); celda.setCellStyle(cellStyle); celda = fila.createCell(1); celda.setCellValue("VENDEDOR"); celda.setCellStyle(cellStyle); celda = fila.createCell(2); celda.setCellValue("DOCUMENTO"); celda.setCellStyle(cellStyle); celda = fila.createCell(3); celda.setCellValue("SUBTOTAL"); celda.setCellStyle(cellStyle); celda = fila.createCell(4); celda.setCellValue("IVA"); celda.setCellStyle(cellStyle); celda = fila.createCell(5); celda.setCellValue("TOTAL"); celda.setCellStyle(cellStyle); i = i + 1; for (int j = 0; j <= 12; j++) { hoja.autoSizeColumn(j); } while (rs.next()) { fila = hoja.createRow(i); celda = fila.createCell(0); celda.setCellValue(sd2.format(rs.getDate(1))); celda = fila.createCell(1); celda.setCellValue(rs.getString("nombre")); celda = fila.createCell(2); celda.setCellValue(rs.getString(2) + " " + rs.getString(3)); celda = fila.createCell(3); celda.setCellValue(rs.getDouble("subtotal")); celda = fila.createCell(4); celda.setCellValue(rs.getDouble("IVA")); celda = fila.createCell(5); celda.setCellValue(rs.getDouble("TOTAL")); i = i + 1; } break;//ventas vendedores case "productosA": hoja = libro.createSheet("Informe Productos"); fila = hoja.createRow(i); celda = fila.createCell(0); celda.setCellValue("CATEGORIA"); celda.setCellStyle(cellStyle); celda = fila.createCell(1); celda.setCellValue("REFERENCIA"); celda.setCellStyle(cellStyle); celda = fila.createCell(2); celda.setCellValue("MARCA"); celda.setCellStyle(cellStyle); celda = fila.createCell(3); celda.setCellValue("CODIGO INTERNO"); celda.setCellStyle(cellStyle); celda = fila.createCell(4); celda.setCellValue("PRODUCTO"); celda.setCellStyle(cellStyle); celda = fila.createCell(5); celda.setCellValue("VALOR"); celda.setCellStyle(cellStyle); i = i + 1; for (int j = 0; j <= 12; j++) { hoja.autoSizeColumn(j); } while (rs.next()) { fila = hoja.createRow(i); celda = fila.createCell(0); celda.setCellValue(rs.getString("nombreCategoria")); celda = fila.createCell(1); celda.setCellValue(rs.getString("nombreReferencia")); celda = fila.createCell(2); celda.setCellValue(rs.getString("nombreMarca")); celda = fila.createCell(3); celda.setCellValue(rs.getString("codigoInterno")); celda = fila.createCell(4); celda.setCellValue(rs.getString("nomProducto")); celda = fila.createCell(5); celda.setCellValue(rs.getDouble("PRECIO")); i = i + 1; } break;//productos case "servicios": hoja = libro.createSheet("Servicios"); fila = hoja.createRow(i); celda = fila.createCell(0); celda.setCellValue("CATEGORIA"); celda.setCellStyle(cellStyle); celda = fila.createCell(1); celda.setCellValue("REFERENCIA"); celda.setCellStyle(cellStyle); celda = fila.createCell(2); celda.setCellValue("MARCA"); celda.setCellStyle(cellStyle); celda = fila.createCell(3); celda.setCellValue("CODIGO INTERNO"); celda.setCellStyle(cellStyle); celda = fila.createCell(4); celda.setCellValue("CODIGO DE BARRAS"); celda.setCellStyle(cellStyle); celda = fila.createCell(5); celda.setCellValue("PRODUCTO"); celda.setCellStyle(cellStyle); celda = fila.createCell(6); celda.setCellValue("COSTO"); celda.setCellStyle(cellStyle); celda = fila.createCell(7); celda.setCellValue("UTILIDAD"); celda.setCellStyle(cellStyle); celda = fila.createCell(8); celda.setCellValue("PRECIO"); celda.setCellStyle(cellStyle); celda = fila.createCell(9); celda.setCellValue("FECHA CREACION"); celda.setCellStyle(cellStyle); i = i + 1; for (int j = 0; j <= 12; j++) { hoja.autoSizeColumn(j); } while (rs.next()) { fila = hoja.createRow(i); celda = fila.createCell(0); celda.setCellValue(rs.getString("nombreCategoria")); celda = fila.createCell(1); celda.setCellValue(rs.getString("nombreReferencia")); celda = fila.createCell(2); celda.setCellValue(rs.getString("nombreMarca")); celda = fila.createCell(3); celda.setCellValue(rs.getString("codigoInterno")); celda = fila.createCell(4); celda.setCellValue(rs.getString("codBarProducto")); celda = fila.createCell(5); celda.setCellValue(rs.getString("nomProducto")); celda = fila.createCell(6); celda.setCellValue(rs.getDouble("costo")); celda = fila.createCell(7); celda.setCellValue(rs.getDouble("utilidad")); celda = fila.createCell(8); celda.setCellValue(rs.getDouble("precio")); celda = fila.createCell(9); celda.setCellValue(sd2.format(rs.getDate("fecCrea"))); i = i + 1; } break;//Servicios case "stock": hoja = libro.createSheet("Stock"); fila = hoja.createRow(i); celda = fila.createCell(0); celda.setCellValue("CATEGORIA"); celda.setCellStyle(cellStyle); celda = fila.createCell(1); celda.setCellValue("REFERENCIA"); celda.setCellStyle(cellStyle); celda = fila.createCell(2); celda.setCellValue("MARCA"); celda.setCellStyle(cellStyle); celda = fila.createCell(3); celda.setCellValue("CODIGO INTERNO"); celda.setCellStyle(cellStyle); celda = fila.createCell(4); celda.setCellValue("PRODUCTO"); celda.setCellStyle(cellStyle); celda = fila.createCell(5); celda.setCellValue("EXISTENCIA"); celda.setCellStyle(cellStyle); i = i + 1; for (int j = 0; j <= 12; j++) { hoja.autoSizeColumn(j); } while (rs.next()) { fila = hoja.createRow(i); celda = fila.createCell(0); celda.setCellValue(rs.getString("nombreCategoria")); celda = fila.createCell(1); celda.setCellValue(rs.getString("nombreReferencia")); celda = fila.createCell(2); celda.setCellValue(rs.getString("nombreMarca")); celda = fila.createCell(3); celda.setCellValue(rs.getString("codigoInterno")); celda = fila.createCell(4); celda.setCellValue(rs.getString("nomProducto")); celda = fila.createCell(5); celda.setCellValue(rs.getLong("existencia")); i = i + 1; } break;//stock case "productoClientes": hoja = libro.createSheet("Ventas productos Clientes"); fila = hoja.createRow(i); celda = fila.createCell(0); celda.setCellValue("CLIENTE"); celda.setCellStyle(cellStyle); celda = fila.createCell(1); celda.setCellValue("PRODUCTO"); celda.setCellStyle(cellStyle); celda = fila.createCell(2); celda.setCellValue("CANTIDAD VENDIDA"); celda.setCellStyle(cellStyle); celda = fila.createCell(3); celda.setCellValue("FECHA ULTIMA VENTA"); celda.setCellStyle(cellStyle); i = i + 1; for (int j = 0; j <= 12; j++) { hoja.autoSizeColumn(j); } while (rs.next()) { fila = hoja.createRow(i); celda = fila.createCell(0); celda.setCellValue(rs.getString("nombre")); celda = fila.createCell(1); celda.setCellValue(rs.getString("producto")); celda = fila.createCell(2); celda.setCellValue(rs.getDouble("total")); celda = fila.createCell(3); celda.setCellValue(sd2.format(rs.getDate(6))); i = i + 1; } break;//productosClientes case "cotizacion": hoja = libro.createSheet("Ventas Cotizaciones"); fila = hoja.createRow(i); celda = fila.createCell(0); celda.setCellValue("CLIENTE"); celda.setCellStyle(cellStyle); celda = fila.createCell(1); celda.setCellValue("COTIZACION"); celda.setCellStyle(cellStyle); celda = fila.createCell(2); celda.setCellValue("FECHA"); celda.setCellStyle(cellStyle); celda = fila.createCell(3); celda.setCellValue("CODIGO PRODUCTO"); celda.setCellStyle(cellStyle); celda = fila.createCell(4); celda.setCellValue("PRODUCTO"); celda.setCellStyle(cellStyle); celda = fila.createCell(5); celda.setCellValue("PRECIO"); celda.setCellStyle(cellStyle); celda = fila.createCell(6); celda.setCellValue("IVA"); celda.setCellStyle(cellStyle); celda = fila.createCell(7); celda.setCellValue("TOTAL"); celda.setCellStyle(cellStyle); i = i + 1; for (int j = 0; j <= 12; j++) { hoja.autoSizeColumn(j); } while (rs.next()) { fila = hoja.createRow(i); celda = fila.createCell(0); celda.setCellValue(rs.getString("nombre")); celda = fila.createCell(1); celda.setCellValue(rs.getString("cotizacion")); celda = fila.createCell(2); celda.setCellValue(sd2.format(rs.getDate("fecCrea"))); celda = fila.createCell(3); celda.setCellValue(rs.getString("codigoInterno")); celda = fila.createCell(4); celda.setCellValue(rs.getString("nomProducto")); celda = fila.createCell(5); celda.setCellValue(rs.getDouble("precio")); celda = fila.createCell(6); celda.setCellValue(rs.getString("iva")); celda = fila.createCell(7); celda.setCellValue(rs.getString("total")); i = i + 1; } break;//cotizacion case "vencimientosFacturas": hoja = libro.createSheet("Vencimiento Facturas"); fila = hoja.createRow(i); celda = fila.createCell(0); celda.setCellValue("DOCUMENTO"); celda.setCellStyle(cellStyle); celda = fila.createCell(1); celda.setCellValue("NUMERO IDENTIFICACION CLIENTE"); celda.setCellStyle(cellStyle); celda = fila.createCell(2); celda.setCellValue("CLIENTE"); celda.setCellStyle(cellStyle); celda = fila.createCell(3); celda.setCellValue("TELEFONO"); celda.setCellStyle(cellStyle); celda = fila.createCell(4); celda.setCellValue("VALOR"); celda.setCellStyle(cellStyle); celda = fila.createCell(5); celda.setCellValue("SALDO"); celda.setCellStyle(cellStyle); celda = fila.createCell(6); celda.setCellValue("FECHA VENCIMIENTO"); celda.setCellStyle(cellStyle); i = i + 1; for (int j = 0; j <= 12; j++) { hoja.autoSizeColumn(j); } while (rs.next()) { fila = hoja.createRow(i); celda = fila.createCell(0); celda.setCellValue(rs.getString("documento")); celda = fila.createCell(1); celda.setCellValue(rs.getString("numDoc")); celda = fila.createCell(2); celda.setCellValue(rs.getString("cliente")); celda = fila.createCell(3); celda.setCellValue(rs.getString("tel1")); celda = fila.createCell(4); celda.setCellValue(rs.getDouble("valor")); celda = fila.createCell(5); celda.setCellValue(rs.getDouble("saldo")); celda = fila.createCell(6); celda.setCellValue(sd2.format(rs.getDate("fecha_limite"))); i = i + 1; } break;//vencimientofactuas case "vencimientosSeparados": hoja = libro.createSheet("Vencimiento Separados"); fila = hoja.createRow(i); celda = fila.createCell(0); celda.setCellValue("DOCUMENTO"); celda.setCellStyle(cellStyle); celda = fila.createCell(1); celda.setCellValue("NUMERO IDENTIFICACION CLIENTE"); celda.setCellStyle(cellStyle); celda = fila.createCell(2); celda.setCellValue("CLIENTE"); celda.setCellStyle(cellStyle); celda = fila.createCell(3); celda.setCellValue("TELEFONO"); celda.setCellStyle(cellStyle); celda = fila.createCell(4); celda.setCellValue("VALOR"); celda.setCellStyle(cellStyle); celda = fila.createCell(5); celda.setCellValue("SALDO"); celda.setCellStyle(cellStyle); celda = fila.createCell(6); celda.setCellValue("FECHA VENCIMIENTO"); celda.setCellStyle(cellStyle); i = i + 1; for (int j = 0; j <= 12; j++) { hoja.autoSizeColumn(j); } while (rs.next()) { fila = hoja.createRow(i); celda = fila.createCell(0); celda.setCellValue(rs.getString("documento")); celda = fila.createCell(1); celda.setCellValue(rs.getString("numDoc")); celda = fila.createCell(2); celda.setCellValue(rs.getString("cliente")); celda = fila.createCell(3); celda.setCellValue(rs.getString("tel1")); celda = fila.createCell(4); celda.setCellValue(rs.getDouble("valor")); celda = fila.createCell(5); celda.setCellValue(rs.getDouble("saldo")); celda = fila.createCell(6); celda.setCellValue(sd2.format(rs.getDate("fecha_limite"))); i = i + 1; } break;//vencimientosSeprados case "productosSinRotacion": hoja = libro.createSheet("productos sin rotacin"); fila = hoja.createRow(i); celda = fila.createCell(0); celda.setCellValue("CATEGORIA"); celda.setCellStyle(cellStyle); celda = fila.createCell(1); celda.setCellValue("REFERENCIA"); celda.setCellStyle(cellStyle); celda = fila.createCell(2); celda.setCellValue("MARCA"); celda.setCellStyle(cellStyle); celda = fila.createCell(3); celda.setCellValue("CODIGO INTERNO"); celda.setCellStyle(cellStyle); celda = fila.createCell(4); celda.setCellValue("PRODUCTO"); celda.setCellStyle(cellStyle); celda = fila.createCell(5); celda.setCellValue("FECHA ULTIMO MOVIMIENTO"); celda.setCellStyle(cellStyle); i = i + 1; for (int j = 0; j <= 12; j++) { hoja.autoSizeColumn(j); } while (rs.next()) { fila = hoja.createRow(i); celda = fila.createCell(0); celda.setCellValue(rs.getString("nombreCategoria")); celda = fila.createCell(1); celda.setCellValue(rs.getString("nombreReferencia")); celda = fila.createCell(2); celda.setCellValue(rs.getString("nombreMarca")); celda = fila.createCell(3); celda.setCellValue(rs.getString("codigoInterno")); celda = fila.createCell(4); celda.setCellValue(rs.getString("nomProducto")); celda = fila.createCell(5); celda.setCellValue(sd2.format(rs.getDate("fechaultimo"))); i = i + 1; } break;//ProductosSinRotacion } /*Escribimos en el libro*/ libro.write(archi); /*Cerramos el flujo de datos*/ archi.close(); /*DESCARGAMOS EL ARCHIVO */ File f; f = new File(rutaArchivo); int bit; InputStream in; ServletOutputStream out; response.setContentType("application/vnd.ms-excel"); //Tipo de fichero. response.setHeader("Content-Disposition", "attachment;filename=\"" + nombreArchivo + "\""); //Configurar cabecera http in = new FileInputStream(f); out = response.getOutputStream(); bit = 256; while ((bit) >= 0) { bit = in.read(); out.write(bit); } out.flush(); out.close(); in.close(); } catch (Exception e) { e.printStackTrace(); } finally { try { if (rs != null) { rs.close(); } if (ps != null) { ps.close(); } if (con != null) { con.close(); } DBConnector.getInstance().closeConnection(); } catch (Exception e) { } } }