List of usage examples for org.apache.poi.ss.usermodel CellStyle setAlignment
void setAlignment(HorizontalAlignment align);
From source file:reports.vmmcexcel.java
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { try {/* w w w. j a v a 2 s . c o m*/ response.setContentType("text/html;charset=UTF-8"); session = request.getSession(); dbConn conn = new dbConn(); //get the existing data for the month, year and facility that is already on session String month = ""; String year = ""; String facil = "361"; String form = "vmmc"; //===================================================================================================== year = "2015"; month = "5"; String county = ""; String header = ""; String reportType = ""; if (request.getParameter("reportType") != null) { reportType = request.getParameter("reportType"); } String reportDuration = ""; if (request.getParameter("reportDuration") != null) { reportDuration = request.getParameter("reportDuration"); } if (request.getParameter("year") != null) { year = request.getParameter("year"); } if (request.getParameter("facility") != null && reportType.equals("2")) { facil = request.getParameter("facility"); String getfacil = "select SubPartnerNom,CentreSanteId as mflcode from subpartnera where SubPartnerID='" + facil + "'"; conn.rs = conn.st.executeQuery(getfacil); while (conn.rs.next()) { header += " FACILITY : " + conn.rs.getString(1).toUpperCase() + " MFL CODE : " + conn.rs.getString(2) + " "; } } if (request.getParameter("county") != null && reportType.equals("2")) { county = request.getParameter("county"); String getcounty = "select County from county where CountyID='" + county + "'"; conn.rs = conn.st.executeQuery(getcounty); while (conn.rs.next()) { header += " COUNTY : " + conn.rs.getString(1).toUpperCase() + " "; } } if (request.getParameter("month") != null && reportDuration.equals("4")) { month = request.getParameter("month"); String getmonth = "select name as monthname from month where id='" + month + "'"; conn.rs = conn.st.executeQuery(getmonth); while (conn.rs.next()) { header += " MONTH : " + conn.rs.getString(1).toUpperCase() + ""; } } header += " YEAR : " + year + ""; String facilitywhere = ""; String yearwhere = ""; String monthwhere = ""; String countywhere = ""; String duration = ""; String semi_annual = ""; String quarter = ""; //================================================================================================== //XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX int yearcopy = Integer.parseInt(year); // reportType="2"; // year=2015; // reportDuration="3"; String yearmonth = "" + year; int prevYear = yearcopy - 1; int maxYearMonth = 0; int monthcopy = 0; // GET REPORT DURATION============================================ if (reportDuration.equals("1")) { yearmonth += "_AnnualReport"; duration = " " + form + ".yearmonth BETWEEN " + prevYear + "10 AND " + year + "09"; } else if (reportDuration.equals("2")) { semi_annual = request.getParameter("semi_annual"); // semi_annual="2"; if (semi_annual.equals("1")) { yearmonth = prevYear + "_Oct_" + year + "_Mar"; duration = " " + form + ".yearmonth BETWEEN " + prevYear + "10 AND " + year + "03"; } else { yearmonth += "_Apr_Sep"; duration = " " + form + ".yearmonth BETWEEN " + year + "04 AND " + year + "09"; } } else if (reportDuration.equals("3")) { String startMonth, endMonth; quarter = request.getParameter("quarter"); // quarter="3"; String getMonths = "SELECT months,name FROM quarter WHERE id='" + quarter + "'"; conn.rs = conn.st.executeQuery(getMonths); if (conn.rs.next() == true) { String months[] = conn.rs.getString(1).split(","); startMonth = months[0]; endMonth = months[2]; if (quarter.equals("1")) { duration = " " + form + ".yearmonth BETWEEN " + prevYear + "" + startMonth + " AND " + prevYear + "" + endMonth; yearmonth = prevYear + "_" + conn.rs.getString(2); } else { yearmonth = year + "_" + conn.rs.getString(2); duration = " " + form + ".yearmonth BETWEEN " + year + "" + startMonth + " AND " + year + "" + endMonth; } } } else if (reportDuration.equals("4")) { monthcopy = Integer.parseInt(request.getParameter("month")); // month=5; if (monthcopy >= 10) { yearmonth = prevYear + "_" + month; duration = " " + form + ".yearmonth=" + prevYear + "" + month; } else { duration = " " + form + ".yearmonth=" + year + "0" + month; yearmonth = year + "_(" + month + ")"; } } else { duration = ""; } //====================================================================== //================================================================================================== //XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX String getexistingdata = ""; if (!county.equals("")) { countywhere = " and countyid = '" + county + "'"; } if (!facil.equals("") && reportType.equalsIgnoreCase("2")) { facilitywhere = " and " + form + ".SubPartnerID = '" + facil + "' "; } String joinedwhwere = " where 1=1 " + facilitywhere + " " + yearwhere + " && " + duration; //===================================================================================================== //===================================================================================================== //______________________________________________________________________________________ // NOW CREATE THE WORKSHEETS //______________________________________________________________________________________ HSSFWorkbook wb = new HSSFWorkbook(); //______________________________________________________________________________________ //______________________________________________________________________________________ HSSFFont font = wb.createFont(); font.setFontHeightInPoints((short) 18); font.setFontName("Cambria"); font.setColor((short) 0000); CellStyle style = wb.createCellStyle(); style.setFont(font); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFFont font2 = wb.createFont(); font2.setFontName("Cambria"); font2.setColor((short) 0000); CellStyle style2 = wb.createCellStyle(); style2.setFont(font2); style2.setBorderTop(HSSFCellStyle.BORDER_THIN); style2.setBorderBottom(HSSFCellStyle.BORDER_THIN); style2.setBorderLeft(HSSFCellStyle.BORDER_THIN); style2.setBorderRight(HSSFCellStyle.BORDER_THIN); style2.setAlignment(HSSFCellStyle.ALIGN_LEFT); style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); HSSFCellStyle stborder = wb.createCellStyle(); stborder.setBorderTop(HSSFCellStyle.BORDER_THIN); stborder.setBorderBottom(HSSFCellStyle.BORDER_THIN); stborder.setBorderLeft(HSSFCellStyle.BORDER_THIN); stborder.setBorderRight(HSSFCellStyle.BORDER_THIN); stborder.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFCellStyle stylex = wb.createCellStyle(); stylex.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); stylex.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); stylex.setBorderTop(HSSFCellStyle.BORDER_THIN); stylex.setBorderBottom(HSSFCellStyle.BORDER_THIN); stylex.setBorderLeft(HSSFCellStyle.BORDER_THIN); stylex.setBorderRight(HSSFCellStyle.BORDER_THIN); stylex.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFFont fontx = wb.createFont(); fontx.setColor(HSSFColor.BLACK.index); fontx.setFontName("Cambria"); stylex.setFont(fontx); stylex.setWrapText(true); HSSFSheet shet = wb.createSheet(form); //create headers for that worksheet HSSFRow rw = shet.createRow(0); rw.setHeightInPoints(25); HSSFCell cl0 = rw.createCell(0); cl0.setCellValue("VOLUNTARY MALE CIRCUMCISION REPORTING FORM"); cl0.setCellStyle(stylex); for (int a = 1; a <= 4; a++) { HSSFCell clx = rw.createCell(a); clx.setCellValue(""); clx.setCellStyle(stylex); } HSSFRow rw1 = shet.createRow(1); rw1.setHeightInPoints(23); HSSFCell cl = rw1.createCell(0); cl.setCellValue(header); cl.setCellStyle(stylex); for (int a = 1; a <= 4; a++) { HSSFCell clx = rw1.createCell(a); clx.setCellValue(""); clx.setCellStyle(stylex); } HSSFRow rw2 = shet.createRow(2); rw2.setHeightInPoints(23); HSSFCell cl3 = rw2.createCell(0); cl3.setCellValue("P5.1.D:"); cl3.setCellStyle(style2); HSSFCell cl31 = rw2.createCell(1); cl31.setCellValue( "Number of Males Circumcised as part of the minimum package of MC for HIV prevention services:"); cl31.setCellStyle(stylex); for (int a = 2; a <= 4; a++) { HSSFCell clx = rw2.createCell(a); clx.setCellValue(""); clx.setCellStyle(stylex); } shet.addMergedRegion(new CellRangeAddress(2, 11, 0, 0)); shet.addMergedRegion(new CellRangeAddress(0, 0, 0, 4)); shet.addMergedRegion(new CellRangeAddress(1, 1, 0, 4)); shet.addMergedRegion(new CellRangeAddress(2, 2, 1, 4)); shet.setColumnWidth(0, 2000); shet.setColumnWidth(1, 9000); shet.setColumnWidth(2, 5000); shet.setColumnWidth(3, 5000); shet.setColumnWidth(4, 5000); getexistingdata = "select sum(P51D1) as P51D1, sum(P51D9) as P51D9, sum(P51D10) as P51D10, sum(P51D19) as P51D19,sum(P51D24) as P51D24, sum(P51D29) as P51D29, sum(P51D49) as P51D49, sum(P51D50) as P51D50, sum(P51DT) as P51DT, sum(P521DM) as P521DM, sum(P521DS) as P521DS, sum(P521DT) as P521DT, sum(P522DM) as P522DM, sum(P522DS) as P522DS, sum(P522DT) as P522DT, sum(P52DM) as P52DM, sum(P52DS) as P52DS, sum(P52DT) as P52DT, sum(P511KP) as P511KP, sum(P511KN) as P511KN, sum(P511KU) as P511KU, sum(P511Surg) as P511Surg, sum(P511Dev) as P511Dev, sum(P53DF) as P53DF, sum(P53DO) as P53DO, sum(P53DM) as P53DM, sum(P53D) as P53D, sum(P54D) as P54D from " + form + " join ( subpartnera join (district join county on county.CountyID=district.CountyID ) on district.DistrictID = subpartnera.DistrictID ) on " + form + ".SubPartnerID = subpartnera.SubPartnerID " + joinedwhwere + " "; System.out.println(getexistingdata); String P51D1 = ""; String P51D9 = ""; String P51D10 = ""; String P51D19 = ""; String P51D24 = ""; String P51D29 = ""; String P51D49 = ""; String P51D50 = ""; String P51DT = ""; String P521DM = ""; String P521DS = ""; String P521DT = ""; String P522DM = ""; String P522DS = ""; String P522DT = ""; String P52DM = ""; String P52DS = ""; String P52DT = ""; String P511KP = ""; String P511KN = ""; String P511KU = ""; String P511Surg = ""; String P511Dev = ""; String P53DF = ""; String P53DO = ""; String P53DM = ""; String P53D = ""; String P54D = ""; String distid = ""; if (session.getAttribute("subcountyid") != null) { distid = session.getAttribute("subcountyid").toString(); } int counter = 0; conn.rs = conn.st.executeQuery(getexistingdata); while (conn.rs.next()) { //now check if form was updated and if its one month after data entry //now load the column values here P51D1 = conn.rs.getString("P51D1"); if (P51D1 == null) { P51D1 = ""; } P51D9 = conn.rs.getString("P51D9"); if (P51D9 == null) { P51D9 = ""; } P51D10 = conn.rs.getString("P51D10"); if (P51D10 == null) { P51D10 = ""; } P51D19 = conn.rs.getString("P51D19"); if (P51D19 == null) { P51D19 = ""; } P51D24 = conn.rs.getString("P51D24"); if (P51D24 == null) { P51D24 = ""; } P51D29 = conn.rs.getString("P51D29"); if (P51D29 == null) { P51D29 = ""; } P51D49 = conn.rs.getString("P51D49"); if (P51D49 == null) { P51D49 = ""; } P51D50 = conn.rs.getString("P51D50"); if (P51D50 == null) { P51D50 = ""; } P51DT = conn.rs.getString("P51DT"); if (P51DT == null) { P51DT = ""; } P521DM = conn.rs.getString("P521DM"); if (P521DM == null) { P521DM = ""; } P521DS = conn.rs.getString("P521DS"); if (P521DS == null) { P521DS = ""; } P521DT = conn.rs.getString("P521DT"); if (P521DT == null) { P521DT = ""; } P522DM = conn.rs.getString("P522DM"); if (P522DM == null) { P522DM = ""; } P522DS = conn.rs.getString("P522DS"); if (P522DS == null) { P522DS = ""; } P522DT = conn.rs.getString("P522DT"); if (P522DT == null) { P522DT = ""; } P52DM = conn.rs.getString("P52DM"); if (P52DM == null) { P52DM = ""; } P52DS = conn.rs.getString("P52DS"); if (P52DS == null) { P52DS = ""; } P52DT = conn.rs.getString("P52DT"); if (P52DT == null) { P52DT = ""; } P511KP = conn.rs.getString("P511KP"); if (P511KP == null) { P511KP = ""; } P511KN = conn.rs.getString("P511KN"); if (P511KN == null) { P511KN = ""; } P511KU = conn.rs.getString("P511KU"); if (P511KU == null) { P511KU = ""; } P511Surg = conn.rs.getString("P511Surg"); if (P511Surg == null) { P511Surg = ""; } P511Dev = conn.rs.getString("P511Dev"); if (P511Dev == null) { P511Dev = ""; } P53DF = conn.rs.getString("P53DF"); if (P53DF == null) { P53DF = ""; } P53DO = conn.rs.getString("P53DO"); if (P53DO == null) { P53DO = ""; } P53DM = conn.rs.getString("P53DM"); if (P53DM == null) { P53DM = ""; } P53D = conn.rs.getString("P53D"); if (P53D == null) { P53D = ""; } P54D = conn.rs.getString("P54D"); if (P54D == null) { P54D = ""; } } String createdtable = ""; if (1 == 1) { if (1 == 1) { int r = 3; HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell clx0 = rwx.createCell(0); clx0.setCellValue(""); clx0.setCellStyle(style2); HSSFCell clx = rwx.createCell(1); clx.setCellValue("< 1"); clx.setCellStyle(style2); HSSFCell clx1 = rwx.createCell(4); clx1.setCellValue(P51D1); clx1.setCellStyle(style2); for (int a = 2; a <= 3; a++) { HSSFCell clx2 = rwx.createCell(a); clx2.setCellValue(""); clx2.setCellStyle(style2); shet.addMergedRegion(new CellRangeAddress(r, r, 1, 3)); } } if (1 == 1) { int r = 4; HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell clx0 = rwx.createCell(0); clx0.setCellValue(""); clx0.setCellStyle(style2); HSSFCell clx = rwx.createCell(1); clx.setCellValue("1-9"); clx.setCellStyle(style2); HSSFCell clx1 = rwx.createCell(4); clx1.setCellValue(P51D9); clx1.setCellStyle(style2); for (int a = 2; a <= 3; a++) { HSSFCell clx2 = rwx.createCell(a); clx2.setCellValue(""); clx2.setCellStyle(style2); } shet.addMergedRegion(new CellRangeAddress(r, r, 1, 3)); } //===================================================================================== if (1 == 1) { int r = 5; HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell clx0 = rwx.createCell(0); clx0.setCellValue(""); clx0.setCellStyle(style2); HSSFCell clx = rwx.createCell(1); clx.setCellValue("10-14"); clx.setCellStyle(style2); HSSFCell clx1 = rwx.createCell(4); clx1.setCellValue(P51D10); clx1.setCellStyle(style2); for (int a = 2; a <= 3; a++) { HSSFCell clx2 = rwx.createCell(a); clx2.setCellValue(""); clx2.setCellStyle(style2); } shet.addMergedRegion(new CellRangeAddress(r, r, 1, 3)); } //===================================================================================== if (1 == 1) { int r = 6; HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell clx0 = rwx.createCell(0); clx0.setCellValue(""); clx0.setCellStyle(style2); HSSFCell clx = rwx.createCell(1); clx.setCellValue("15-19"); clx.setCellStyle(style2); HSSFCell clx1 = rwx.createCell(4); clx1.setCellValue(P51D19); clx1.setCellStyle(style2); for (int a = 2; a <= 3; a++) { HSSFCell clx2 = rwx.createCell(a); clx2.setCellValue(""); clx2.setCellStyle(style2); } shet.addMergedRegion(new CellRangeAddress(r, r, 1, 3)); } //================================================================================== if (1 == 1) { int r = 7; HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell clx0 = rwx.createCell(0); clx0.setCellValue(""); clx0.setCellStyle(style2); HSSFCell clx = rwx.createCell(1); clx.setCellValue("20-24"); clx.setCellStyle(style2); HSSFCell clx1 = rwx.createCell(4); clx1.setCellValue(P51D24); clx1.setCellStyle(style2); for (int a = 2; a <= 3; a++) { HSSFCell clx2 = rwx.createCell(a); clx2.setCellValue(""); clx2.setCellStyle(style2); } shet.addMergedRegion(new CellRangeAddress(r, r, 1, 3)); } //================================================================================== if (1 == 1) { int r = 8; HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell clx0 = rwx.createCell(0); clx0.setCellValue(""); clx0.setCellStyle(style2); HSSFCell clx = rwx.createCell(1); clx.setCellValue("25-29"); clx.setCellStyle(style2); HSSFCell clx1 = rwx.createCell(4); clx1.setCellValue(P51D29); clx1.setCellStyle(style2); for (int a = 2; a <= 3; a++) { HSSFCell clx2 = rwx.createCell(a); clx2.setCellValue(""); clx2.setCellStyle(style2); } shet.addMergedRegion(new CellRangeAddress(r, r, 1, 3)); } //================================================================================== if (1 == 1) { int r = 9; HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell clx0 = rwx.createCell(0); clx0.setCellValue(""); clx0.setCellStyle(style2); HSSFCell clx = rwx.createCell(1); clx.setCellValue("30-49"); clx.setCellStyle(style2); HSSFCell clx1 = rwx.createCell(4); clx1.setCellValue(P51D49); clx1.setCellStyle(style2); for (int a = 2; a <= 3; a++) { HSSFCell clx2 = rwx.createCell(a); clx2.setCellValue(""); clx2.setCellStyle(style2); } shet.addMergedRegion(new CellRangeAddress(r, r, 1, 3)); } //================================================================================== if (1 == 1) { int r = 10; HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell clx0 = rwx.createCell(0); clx0.setCellValue(""); clx0.setCellStyle(style2); HSSFCell clx = rwx.createCell(1); clx.setCellValue("50 +"); clx.setCellStyle(style2); HSSFCell clx1 = rwx.createCell(4); clx1.setCellValue(P51D50); clx1.setCellStyle(style2); for (int a = 2; a <= 3; a++) { HSSFCell clx2 = rwx.createCell(a); clx2.setCellValue(""); clx2.setCellStyle(style2); } shet.addMergedRegion(new CellRangeAddress(r, r, 1, 3)); } //================================================================================== if (1 == 1) { int r = 11; HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell clx0 = rwx.createCell(0); clx0.setCellValue(""); clx0.setCellStyle(style2); HSSFCell clx = rwx.createCell(1); clx.setCellValue("Total"); clx.setCellStyle(style2); HSSFCell clx1 = rwx.createCell(4); clx1.setCellValue(P51DT); clx1.setCellStyle(style2); for (int a = 2; a <= 3; a++) { HSSFCell clx2 = rwx.createCell(a); clx2.setCellValue(""); clx2.setCellStyle(style2); } shet.addMergedRegion(new CellRangeAddress(r, r, 1, 3)); } //================================================================================== if (1 == 1) { int r = 12; HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell clx0 = rwx.createCell(0); clx0.setCellValue("P5.2.D"); clx0.setCellStyle(style2); HSSFCell clx = rwx.createCell(1); clx.setCellValue( "Number of Clients circumcised who experienced one or more moderate or severe adverse events(s)"); clx.setCellStyle(stylex); HSSFCell clx1 = rwx.createCell(4); clx1.setCellValue(""); clx1.setCellStyle(style2); for (int a = 2; a <= 3; a++) { HSSFCell clx2 = rwx.createCell(a); clx2.setCellValue(""); clx2.setCellStyle(style2); } shet.addMergedRegion(new CellRangeAddress(r, r, 1, 4)); shet.addMergedRegion(new CellRangeAddress(r, r + 4, 0, 0)); } //================================================================================== if (1 == 1) { int r = 13; HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell clx0 = rwx.createCell(0); clx0.setCellValue(""); clx0.setCellStyle(style2); HSSFCell clx = rwx.createCell(1); clx.setCellValue(""); clx.setCellStyle(style2); HSSFCell clxm = rwx.createCell(2); clxm.setCellValue("Moderate"); clxm.setCellStyle(stylex); HSSFCell clxs = rwx.createCell(3); clxs.setCellValue("Severe"); clxs.setCellStyle(stylex); HSSFCell clxt = rwx.createCell(4); clxt.setCellValue("Total"); clxt.setCellStyle(stylex); //shet.addMergedRegion(new CellRangeAddress(r,r,1,3)); } //================================================================================== if (1 == 1) { int r = 14; HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell clx0 = rwx.createCell(0); clx0.setCellValue(""); clx0.setCellStyle(style2); HSSFCell clx = rwx.createCell(1); clx.setCellValue("During Circumcission"); clx.setCellStyle(stylex); HSSFCell clxm = rwx.createCell(2); clxm.setCellValue(P521DM); clxm.setCellStyle(style2); HSSFCell clxs = rwx.createCell(3); clxs.setCellValue(P521DS); clxs.setCellStyle(style2); HSSFCell clxt = rwx.createCell(4); clxt.setCellValue(P521DT); clxt.setCellStyle(style2); //shet.addMergedRegion(new CellRangeAddress(r,r,1,3)); } //================================================================================== if (1 == 1) { int r = 15; HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell clx0 = rwx.createCell(0); clx0.setCellValue(""); clx0.setCellStyle(style2); HSSFCell clx = rwx.createCell(1); clx.setCellValue("Post CircumCission"); clx.setCellStyle(stylex); HSSFCell clxm = rwx.createCell(2); clxm.setCellValue(P522DM); clxm.setCellStyle(style2); HSSFCell clxs = rwx.createCell(3); clxs.setCellValue(P522DS); clxs.setCellStyle(style2); HSSFCell clxt = rwx.createCell(4); clxt.setCellValue(P522DT); clxt.setCellStyle(style2); //shet.addMergedRegion(new CellRangeAddress(r,r,1,3)); } //================================================================================== if (1 == 1) { int r = 16; HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell clx0 = rwx.createCell(0); clx0.setCellValue(""); clx0.setCellStyle(style2); HSSFCell clx = rwx.createCell(1); clx.setCellValue("Total Adverse Events"); clx.setCellStyle(stylex); HSSFCell clxm = rwx.createCell(2); clxm.setCellValue(P52DM); clxm.setCellStyle(style2); HSSFCell clxs = rwx.createCell(3); clxs.setCellValue(P52DS); clxs.setCellStyle(style2); HSSFCell clxt = rwx.createCell(4); clxt.setCellValue(P52DT); clxt.setCellStyle(style2); //shet.addMergedRegion(new CellRangeAddress(r,r,1,3)); } //================================================================================== if (1 == 1) { int r = 17; HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell clx0 = rwx.createCell(0); clx0.setCellValue("P5.1.1.K"); clx0.setCellStyle(style2); HSSFCell clx = rwx.createCell(1); clx.setCellValue("HIV Status of MC clients)"); clx.setCellStyle(stylex); HSSFCell clx1 = rwx.createCell(4); clx1.setCellValue(""); clx1.setCellStyle(style2); for (int a = 2; a <= 3; a++) { HSSFCell clx2 = rwx.createCell(a); clx2.setCellValue(""); clx2.setCellStyle(style2); } shet.addMergedRegion(new CellRangeAddress(r, r, 1, 4)); shet.addMergedRegion(new CellRangeAddress(r, r + 3, 0, 0)); } //================================================================================== if (1 == 1) { int r = 18; HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell clx0 = rwx.createCell(0); clx0.setCellValue(""); clx0.setCellStyle(style2); HSSFCell clx = rwx.createCell(1); clx.setCellValue("Tested/self-reported positive"); clx.setCellStyle(style2); HSSFCell clx1 = rwx.createCell(4); clx1.setCellValue(P511KP); clx1.setCellStyle(style2); for (int a = 2; a <= 3; a++) { HSSFCell clx2 = rwx.createCell(a); clx2.setCellValue(""); clx2.setCellStyle(style2); shet.addMergedRegion(new CellRangeAddress(r, r, 1, 3)); } } //================================================================================== if (1 == 1) { int r = 19; HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell clx0 = rwx.createCell(0); clx0.setCellValue(""); clx0.setCellStyle(style2); HSSFCell clx = rwx.createCell(1); clx.setCellValue("Tested negative"); clx.setCellStyle(style2); HSSFCell clx1 = rwx.createCell(4); clx1.setCellValue(P511KN); clx1.setCellStyle(style2); for (int a = 2; a <= 3; a++) { HSSFCell clx2 = rwx.createCell(a); clx2.setCellValue(""); clx2.setCellStyle(style2); shet.addMergedRegion(new CellRangeAddress(r, r, 1, 3)); } } //================================================================================== if (1 == 1) { int r = 20; HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell clx0 = rwx.createCell(0); clx0.setCellValue(""); clx0.setCellStyle(style2); HSSFCell clx = rwx.createCell(1); clx.setCellValue("Unknown/self-reported negative"); clx.setCellStyle(style2); HSSFCell clx1 = rwx.createCell(4); clx1.setCellValue(P511KU); clx1.setCellStyle(style2); for (int a = 2; a <= 3; a++) { HSSFCell clx2 = rwx.createCell(a); clx2.setCellValue(""); clx2.setCellStyle(style2); shet.addMergedRegion(new CellRangeAddress(r, r, 1, 3)); } } //================================================================================== //================================================================================== if (1 == 1) { int r = 21; HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell clx0 = rwx.createCell(0); clx0.setCellValue("P5.1.1.T"); clx0.setCellStyle(style2); HSSFCell clx = rwx.createCell(1); clx.setCellValue("Circumcission Technique)"); clx.setCellStyle(stylex); HSSFCell clx1 = rwx.createCell(4); clx1.setCellValue(""); clx1.setCellStyle(style2); for (int a = 2; a <= 3; a++) { HSSFCell clx2 = rwx.createCell(a); clx2.setCellValue(""); clx2.setCellStyle(style2); } shet.addMergedRegion(new CellRangeAddress(r, r, 1, 4)); shet.addMergedRegion(new CellRangeAddress(r, r + 2, 0, 0)); } //================================================================================== if (1 == 1) { int r = 22; HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell clx0 = rwx.createCell(0); clx0.setCellValue(""); clx0.setCellStyle(style2); HSSFCell clx = rwx.createCell(1); clx.setCellValue("Surgical VMMC"); clx.setCellStyle(style2); HSSFCell clx1 = rwx.createCell(4); clx1.setCellValue(P511Surg); clx1.setCellStyle(style2); for (int a = 2; a <= 3; a++) { HSSFCell clx2 = rwx.createCell(a); clx2.setCellValue(""); clx2.setCellStyle(style2); shet.addMergedRegion(new CellRangeAddress(r, r, 1, 3)); } } //================================================================================== if (1 == 1) { int r = 23; HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell clx0 = rwx.createCell(0); clx0.setCellValue(""); clx0.setCellStyle(style2); HSSFCell clx = rwx.createCell(1); clx.setCellValue("Device-Based VMMC"); clx.setCellStyle(style2); HSSFCell clx1 = rwx.createCell(4); clx1.setCellValue(P511Dev); clx1.setCellStyle(style2); for (int a = 2; a <= 3; a++) { HSSFCell clx2 = rwx.createCell(a); clx2.setCellValue(""); clx2.setCellStyle(style2); shet.addMergedRegion(new CellRangeAddress(r, r, 1, 3)); } } //================================================================================== //================================================================================== if (1 == 1) { int r = 24; HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell clx0 = rwx.createCell(0); clx0.setCellValue("P5.3.D:"); clx0.setCellStyle(style2); HSSFCell clx = rwx.createCell(1); clx.setCellValue( "Number of locations providing MC surgery as part of the minimum package of MC for HIV prevention services within the reporting period "); clx.setCellStyle(stylex); HSSFCell clx1 = rwx.createCell(4); clx1.setCellValue(""); clx1.setCellStyle(style2); for (int a = 2; a <= 3; a++) { HSSFCell clx2 = rwx.createCell(a); clx2.setCellValue(""); clx2.setCellStyle(style2); } shet.addMergedRegion(new CellRangeAddress(r, r, 1, 4)); shet.addMergedRegion(new CellRangeAddress(r, r + 4, 0, 0)); } //================================================================================== if (1 == 1) { int r = 25; HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell clx0 = rwx.createCell(0); clx0.setCellValue(""); clx0.setCellStyle(style2); HSSFCell clx = rwx.createCell(1); clx.setCellValue("Fixed/Static"); clx.setCellStyle(style2); HSSFCell clx1 = rwx.createCell(4); clx1.setCellValue(P53DF); clx1.setCellStyle(style2); for (int a = 2; a <= 3; a++) { HSSFCell clx2 = rwx.createCell(a); clx2.setCellValue(""); clx2.setCellStyle(style2); shet.addMergedRegion(new CellRangeAddress(r, r, 1, 3)); } } //================================================================================== if (1 == 1) { int r = 26; HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell clx0 = rwx.createCell(0); clx0.setCellValue(""); clx0.setCellStyle(style2); HSSFCell clx = rwx.createCell(1); clx.setCellValue("Outreach"); clx.setCellStyle(style2); HSSFCell clx1 = rwx.createCell(4); clx1.setCellValue(P53DO); clx1.setCellStyle(style2); for (int a = 2; a <= 3; a++) { HSSFCell clx2 = rwx.createCell(a); clx2.setCellValue(""); clx2.setCellStyle(style2); shet.addMergedRegion(new CellRangeAddress(r, r, 1, 3)); } } //============================================================================================== //================================================================================== if (1 == 1) { int r = 27; HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell clx0 = rwx.createCell(0); clx0.setCellValue(""); clx0.setCellStyle(style2); HSSFCell clx = rwx.createCell(1); clx.setCellValue("Mobile"); clx.setCellStyle(style2); HSSFCell clx1 = rwx.createCell(4); clx1.setCellValue(P53DM); clx1.setCellStyle(style2); for (int a = 2; a <= 3; a++) { HSSFCell clx2 = rwx.createCell(a); clx2.setCellValue(""); clx2.setCellStyle(style2); shet.addMergedRegion(new CellRangeAddress(r, r, 1, 3)); } } //============================================================================================== //================================================================================== if (1 == 1) { int r = 28; HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell clx0 = rwx.createCell(0); clx0.setCellValue(""); clx0.setCellStyle(style2); HSSFCell clx = rwx.createCell(1); clx.setCellValue("Total"); clx.setCellStyle(style2); HSSFCell clx1 = rwx.createCell(4); clx1.setCellValue(P53D); clx1.setCellStyle(style2); for (int a = 2; a <= 3; a++) { HSSFCell clx2 = rwx.createCell(a); clx2.setCellValue(""); clx2.setCellStyle(style2); shet.addMergedRegion(new CellRangeAddress(r, r, 1, 3)); } } //============================================================================================== //================================================================================== if (1 == 1) { int r = 29; HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(28); HSSFCell clx0 = rwx.createCell(0); clx0.setCellValue("P5.3.D:"); clx0.setCellStyle(style2); HSSFCell clx = rwx.createCell(1); clx.setCellValue( "Number of males circumcised within the reporting period who return at least once for postoperative follow?up care (routine or emergent) within 14 days of surgery"); clx.setCellStyle(stylex); HSSFCell clx1 = rwx.createCell(4); clx1.setCellValue(""); clx1.setCellStyle(style2); for (int a = 2; a <= 3; a++) { HSSFCell clx2 = rwx.createCell(a); clx2.setCellValue(""); clx2.setCellStyle(style2); } shet.addMergedRegion(new CellRangeAddress(r, r, 1, 4)); shet.addMergedRegion(new CellRangeAddress(r, r + 1, 0, 0)); } //================================================================================== if (1 == 1) { int r = 30; HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell clx0 = rwx.createCell(0); clx0.setCellValue(""); clx0.setCellStyle(style2); HSSFCell clx = rwx.createCell(1); clx.setCellValue(""); clx.setCellStyle(style2); HSSFCell clx1 = rwx.createCell(4); clx1.setCellValue(P54D); clx1.setCellStyle(style2); for (int a = 2; a <= 3; a++) { HSSFCell clx2 = rwx.createCell(a); clx2.setCellValue(""); clx2.setCellStyle(style2); shet.addMergedRegion(new CellRangeAddress(r, r, 1, 3)); } } //================================================================================== } //System.out.println(createdtable); if (conn.conn != null) { conn.conn.close(); } if (conn.rs != null) { conn.rs.close(); } if (conn.rs1 != null) { conn.rs1.close(); } if (conn.rs2 != null) { conn.rs2.close(); } if (conn.st != null) { conn.st.close(); } if (conn.st2 != null) { conn.st2.close(); } IdGenerator IG = new IdGenerator(); String createdOn = IG.CreatedOn(); ByteArrayOutputStream outByteStream = new ByteArrayOutputStream(); wb.write(outByteStream); byte[] outArray = outByteStream.toByteArray(); response.setContentType("application/ms-excel"); response.setContentLength(outArray.length); response.setHeader("Expires:", "0"); // eliminates browser caching response.setHeader("Content-Disposition", "attachment; filename=" + form + yearmonth + "_Generatted_On_" + createdOn + ".xls"); OutputStream outStream = response.getOutputStream(); outStream.write(outArray); outStream.flush(); outStream.close(); } catch (SQLException ex) { Logger.getLogger(Vmmcpdf.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:ro.dabuno.office.integration.BusinessPlan.java
License:Apache License
/** * create a library of cell styles/*from w ww. ja v a 2s . c o m*/ */ private static Map<String, CellStyle> createStyles(Workbook wb) { Map<String, CellStyle> styles = new HashMap<String, CellStyle>(); DataFormat df = wb.createDataFormat(); CellStyle style; Font headerFont = wb.createFont(); headerFont.setBold(true); style = createBorderedStyle(wb); style.setAlignment(HorizontalAlignment.CENTER); style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex()); style.setFillPattern(FillPatternType.SOLID_FOREGROUND); style.setFont(headerFont); styles.put("header", style); style = createBorderedStyle(wb); style.setAlignment(HorizontalAlignment.CENTER); style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex()); style.setFillPattern(FillPatternType.SOLID_FOREGROUND); style.setFont(headerFont); style.setDataFormat(df.getFormat("d-mmm")); styles.put("header_date", style); Font font1 = wb.createFont(); font1.setBold(true); style = createBorderedStyle(wb); style.setAlignment(HorizontalAlignment.LEFT); style.setFont(font1); styles.put("cell_b", style); style = createBorderedStyle(wb); style.setAlignment(HorizontalAlignment.CENTER); style.setFont(font1); styles.put("cell_b_centered", style); style = createBorderedStyle(wb); style.setAlignment(HorizontalAlignment.RIGHT); style.setFont(font1); style.setDataFormat(df.getFormat("d-mmm")); styles.put("cell_b_date", style); style = createBorderedStyle(wb); style.setAlignment(HorizontalAlignment.RIGHT); style.setFont(font1); style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); style.setFillPattern(FillPatternType.SOLID_FOREGROUND); style.setDataFormat(df.getFormat("d-mmm")); styles.put("cell_g", style); Font font2 = wb.createFont(); font2.setColor(IndexedColors.BLUE.getIndex()); font2.setBold(true); style = createBorderedStyle(wb); style.setAlignment(HorizontalAlignment.LEFT); style.setFont(font2); styles.put("cell_bb", style); style = createBorderedStyle(wb); style.setAlignment(HorizontalAlignment.RIGHT); style.setFont(font1); style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); style.setFillPattern(FillPatternType.SOLID_FOREGROUND); style.setDataFormat(df.getFormat("d-mmm")); styles.put("cell_bg", style); Font font3 = wb.createFont(); font3.setFontHeightInPoints((short) 14); font3.setColor(IndexedColors.DARK_BLUE.getIndex()); font3.setBold(true); style = createBorderedStyle(wb); style.setAlignment(HorizontalAlignment.LEFT); style.setFont(font3); style.setWrapText(true); styles.put("cell_h", style); style = createBorderedStyle(wb); style.setAlignment(HorizontalAlignment.LEFT); style.setWrapText(true); styles.put("cell_normal", style); style = createBorderedStyle(wb); style.setAlignment(HorizontalAlignment.CENTER); style.setWrapText(true); styles.put("cell_normal_centered", style); style = createBorderedStyle(wb); style.setAlignment(HorizontalAlignment.RIGHT); style.setWrapText(true); style.setDataFormat(df.getFormat("d-mmm")); styles.put("cell_normal_date", style); style = createBorderedStyle(wb); style.setAlignment(HorizontalAlignment.LEFT); style.setIndention((short) 1); style.setWrapText(true); styles.put("cell_indented", style); style = createBorderedStyle(wb); style.setFillForegroundColor(IndexedColors.BLUE.getIndex()); style.setFillPattern(FillPatternType.SOLID_FOREGROUND); styles.put("cell_blue", style); return styles; }
From source file:ro.dabuno.office.integration.LoadCalculator.java
/** * cell styles used for formatting calendar sheets *//* w w w . j av a 2 s. com*/ private static Map<String, CellStyle> createStyles(Workbook wb) { Map<String, CellStyle> styles = new HashMap<String, CellStyle>(); CellStyle style; Font titleFont = wb.createFont(); titleFont.setFontHeightInPoints((short) 14); titleFont.setFontName("Trebuchet MS"); style = wb.createCellStyle(); style.setFont(titleFont); style.setBorderBottom(BorderStyle.DOTTED); style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); styles.put("title", style); Font itemFont = wb.createFont(); itemFont.setFontHeightInPoints((short) 9); itemFont.setFontName("Trebuchet MS"); style = wb.createCellStyle(); style.setAlignment(HorizontalAlignment.LEFT); style.setFont(itemFont); styles.put("item_left", style); style = wb.createCellStyle(); style.setAlignment(HorizontalAlignment.RIGHT); style.setFont(itemFont); styles.put("item_right", style); style = wb.createCellStyle(); style.setAlignment(HorizontalAlignment.RIGHT); style.setFont(itemFont); style.setBorderRight(BorderStyle.DOTTED); style.setRightBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderBottom(BorderStyle.DOTTED); style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderLeft(BorderStyle.DOTTED); style.setLeftBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderTop(BorderStyle.DOTTED); style.setTopBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setDataFormat( wb.createDataFormat().getFormat("_($* #,##0.00_);_($* (#,##0.00);_($* \"-\"??_);_(@_)")); styles.put("input_$", style); style = wb.createCellStyle(); style.setAlignment(HorizontalAlignment.RIGHT); style.setFont(itemFont); style.setBorderRight(BorderStyle.DOTTED); style.setRightBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderBottom(BorderStyle.DOTTED); style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderLeft(BorderStyle.DOTTED); style.setLeftBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderTop(BorderStyle.DOTTED); style.setTopBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setDataFormat(wb.createDataFormat().getFormat("0.000%")); styles.put("input_%", style); style = wb.createCellStyle(); style.setAlignment(HorizontalAlignment.RIGHT); style.setFont(itemFont); style.setBorderRight(BorderStyle.DOTTED); style.setRightBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderBottom(BorderStyle.DOTTED); style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderLeft(BorderStyle.DOTTED); style.setLeftBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderTop(BorderStyle.DOTTED); style.setTopBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setDataFormat(wb.createDataFormat().getFormat("0")); styles.put("input_i", style); style = wb.createCellStyle(); style.setAlignment(HorizontalAlignment.CENTER); style.setFont(itemFont); style.setDataFormat(wb.createDataFormat().getFormat("m/d/yy")); styles.put("input_d", style); style = wb.createCellStyle(); style.setAlignment(HorizontalAlignment.RIGHT); style.setFont(itemFont); style.setBorderRight(BorderStyle.DOTTED); style.setRightBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderBottom(BorderStyle.DOTTED); style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderLeft(BorderStyle.DOTTED); style.setLeftBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderTop(BorderStyle.DOTTED); style.setTopBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setDataFormat(wb.createDataFormat().getFormat("$##,##0.00")); style.setBorderBottom(BorderStyle.DOTTED); style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); style.setFillPattern(FillPatternType.SOLID_FOREGROUND); styles.put("formula_$", style); style = wb.createCellStyle(); style.setAlignment(HorizontalAlignment.RIGHT); style.setFont(itemFont); style.setBorderRight(BorderStyle.DOTTED); style.setRightBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderBottom(BorderStyle.DOTTED); style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderLeft(BorderStyle.DOTTED); style.setLeftBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderTop(BorderStyle.DOTTED); style.setTopBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setDataFormat(wb.createDataFormat().getFormat("0")); style.setBorderBottom(BorderStyle.DOTTED); style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); style.setFillPattern(FillPatternType.SOLID_FOREGROUND); styles.put("formula_i", style); return styles; }
From source file:ru.wmbdiff.ExportIntoExcel.java
License:Apache License
public void export(File file, WMBDiffNoRootTreeTableModel model) { logger.info("export begin"); Workbook workbook = new HSSFWorkbook(); Sheet sheet = workbook.createSheet("WMBDiff"); int rowNum = 0; //Create Header CellStyle style; Font headerFont = workbook.createFont(); headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD); headerFont.setColor(IndexedColors.WHITE.getIndex()); headerFont.setFontHeightInPoints((short) 10); style = workbook.createCellStyle();/* w ww. j a va2 s .co m*/ style.setBorderRight(CellStyle.BORDER_THIN); style.setRightBorderColor(IndexedColors.WHITE.getIndex()); style.setBorderBottom(CellStyle.BORDER_THIN); style.setBottomBorderColor(IndexedColors.WHITE.getIndex()); style.setBorderLeft(CellStyle.BORDER_THIN); style.setLeftBorderColor(IndexedColors.WHITE.getIndex()); style.setBorderTop(CellStyle.BORDER_THIN); style.setTopBorderColor(IndexedColors.WHITE.getIndex()); style.setAlignment(CellStyle.ALIGN_CENTER); style.setFillForegroundColor(IndexedColors.AQUA.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setFont(headerFont); Row row = sheet.createRow(rowNum++); Cell cell; cell = row.createCell(0); cell.setCellValue("Result"); cell.setCellStyle(style); cell = row.createCell(1); cell.setCellValue("Broker"); cell.setCellStyle(style); cell = row.createCell(2); cell.setCellValue("Execution Group"); cell.setCellStyle(style); cell = row.createCell(3); cell.setCellValue("Name"); cell.setCellStyle(style); cell = row.createCell(4); cell.setCellValue("Type"); cell.setCellStyle(style); cell = row.createCell(5); cell.setCellValue("Last Modification"); cell.setCellStyle(style); cell = row.createCell(6); cell.setCellValue("Deployment Date"); cell.setCellStyle(style); cell = row.createCell(7); cell.setCellValue("Bar File"); cell.setCellStyle(style); cell = row.createCell(8); cell.setCellValue("Result Description"); cell.setCellStyle(style); sheet.createFreezePane(0, 1); List<DiffExecutionGroup> dEG = model.getDiffExecutionGroupList(); ListIterator<DiffExecutionGroup> litr = dEG.listIterator(); while (litr.hasNext()) { DiffExecutionGroup element = litr.next(); element.getDiffResultList(); ListIterator<DiffDeployedObjectResult> litr2 = element.getDiffResultList().listIterator(); while (litr2.hasNext()) { DiffDeployedObjectResult res = litr2.next(); switch (res.getResult()) { case ONLY_IN_A: createRow(rowNum++, sheet, res.getAObject(), "A", res.getResultDesc()); break; case ONLY_IN_B: createRow(rowNum++, sheet, res.getBObject(), "B", res.getResultDesc()); break; case EQUAL: createRow(rowNum++, sheet, res.getAObject(), "=", res.getResultDesc()); createRow(rowNum++, sheet, res.getBObject(), "=", res.getResultDesc()); sheet.groupRow(rowNum - 2, rowNum - 2); break; case DIFF: createRow(rowNum++, sheet, res.getAObject(), "!=", res.getResultDesc()); createRow(rowNum++, sheet, res.getBObject(), "!=", res.getResultDesc()); sheet.groupRow(rowNum - 2, rowNum - 2); break; } ; } ; } ; //Adjust column width to fit the contents for (int i = 0; i < 9; i++) sheet.autoSizeColumn(i); //set Filter sheet.setAutoFilter(new CellRangeAddress(0, rowNum - 1, 0, 8)); try { FileOutputStream out = new FileOutputStream(file); workbook.write(out); workbook.close(); out.close(); } catch (Exception e) { logger.error("export", e); } logger.info("export end"); }
From source file:se.mithlond.services.content.impl.ejb.report.ExcelReportServiceBean.java
License:Apache License
/** * {@inheritDoc}/*from www . j a v a2s. c om*/ */ @Override @SuppressWarnings("all") public CellStyle getCellStyle(final ExcelElement el, final Workbook workbook) { // Check sanity Validate.notNull(workbook, "workbook"); Validate.notNull(el, "el"); // Acquire the el and Font as expected final CellStyle toReturn = workbook.createCellStyle(); final Font theFont = workbook.createFont(); switch (el) { case TITLE: theFont.setFontHeightInPoints((short) 18); theFont.setBold(true); theFont.setColor(IndexedColors.BLUE_GREY.getIndex()); toReturn.setAlignment(HorizontalAlignment.CENTER); toReturn.setVerticalAlignment(VerticalAlignment.CENTER); break; case HEADER: theFont.setFontHeightInPoints((short) 11); theFont.setColor(IndexedColors.WHITE.getIndex()); toReturn.setAlignment(HorizontalAlignment.CENTER); toReturn.setVerticalAlignment(VerticalAlignment.CENTER); toReturn.setFillForegroundColor(IndexedColors.BLUE_GREY.getIndex()); toReturn.setFillPattern(FillPatternType.SOLID_FOREGROUND); toReturn.setWrapText(true); break; case CELL: toReturn.setAlignment(HorizontalAlignment.LEFT); toReturn.setWrapText(true); toReturn.setBorderRight(BORDER_THIN); toReturn.setRightBorderColor(GREY_25_PERCENT); toReturn.setBorderLeft(BORDER_THIN); toReturn.setLeftBorderColor(GREY_25_PERCENT); toReturn.setBorderTop(BORDER_THIN); toReturn.setTopBorderColor(GREY_25_PERCENT); toReturn.setBorderBottom(BORDER_THIN); toReturn.setBottomBorderColor(GREY_25_PERCENT); break; case NON_WRAPPING: toReturn.setAlignment(HorizontalAlignment.LEFT); toReturn.setWrapText(false); toReturn.setBorderRight(BORDER_THIN); toReturn.setRightBorderColor(GREY_25_PERCENT); toReturn.setBorderLeft(BORDER_THIN); toReturn.setLeftBorderColor(GREY_25_PERCENT); toReturn.setBorderTop(BORDER_THIN); toReturn.setTopBorderColor(GREY_25_PERCENT); toReturn.setBorderBottom(BORDER_THIN); toReturn.setBottomBorderColor(GREY_25_PERCENT); break; case FORMULA: toReturn.setAlignment(HorizontalAlignment.CENTER); toReturn.setVerticalAlignment(VerticalAlignment.CENTER); toReturn.setFillForegroundColor(GREY_25_PERCENT); toReturn.setFillPattern(FillPatternType.SOLID_FOREGROUND); toReturn.setDataFormat(workbook.createDataFormat().getFormat("0.00")); break; case ALT_FORMULA: toReturn.setAlignment(HorizontalAlignment.CENTER); toReturn.setVerticalAlignment(VerticalAlignment.CENTER); toReturn.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex()); toReturn.setFillPattern(FillPatternType.SOLID_FOREGROUND); toReturn.setDataFormat(workbook.createDataFormat().getFormat("0.00")); break; default: throw new IllegalArgumentException( "Style [" + el.name() + "] was not defined. " + "Blame the programmer."); } // All done. toReturn.setFont(theFont); return toReturn; }
From source file:servlet.exportScoreSheet.java
private static Map<String, CellStyle> createStyles(Workbook wb) { Map<String, CellStyle> styles = new HashMap<String, CellStyle>(); CellStyle style; Font titleFont = wb.createFont(); titleFont.setFontHeightInPoints((short) 18); titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD); style = wb.createCellStyle();// w w w .j av a 2 s . co m style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setFont(titleFont); styles.put("title", style); Font monthFont = wb.createFont(); monthFont.setFontHeightInPoints((short) 11); monthFont.setColor(IndexedColors.WHITE.getIndex()); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setFont(monthFont); style.setWrapText(true); styles.put("header", style); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setWrapText(true); style.setBorderRight(CellStyle.BORDER_THIN); style.setRightBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderLeft(CellStyle.BORDER_THIN); style.setLeftBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderTop(CellStyle.BORDER_THIN); style.setTopBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderBottom(CellStyle.BORDER_THIN); style.setBottomBorderColor(IndexedColors.BLACK.getIndex()); styles.put("cell", style); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setDataFormat(wb.createDataFormat().getFormat("0.00")); styles.put("formula", style); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setDataFormat(wb.createDataFormat().getFormat("0.00")); styles.put("formula_2", style); return styles; }
From source file:sql.fredy.sqltools.XLSExport.java
License:Open Source License
/** * Create the XLS-File named fileName//from w w w. j a v a2 s .co m * * @param fileName is the Name (incl. Path) of the XLS-file to create * * */ public int createXLS(String fileName) { // I need to have a query to process if ((getQuery() == null) && (getPstmt() == null)) { logger.log(Level.WARNING, "Need to have a query to process"); return 0; } // I also need to have a file to write into if (fileName == null) { logger.log(Level.WARNING, "Need to know where to write into"); return 0; } fileName = fixFileName(fileName); checkXlsx(fileName); // I need to have a connection to the RDBMS if (getCon() == null) { logger.log(Level.WARNING, "Need to have a connection to process"); return 0; } //Statement stmt = null; ResultSet resultSet = null; ResultSetMetaData rsmd = null; try { // first we have to create the Statement if (getPstmt() == null) { pstmt = getCon().prepareStatement(getQuery()); } //stmt = getCon().createStatement(); } catch (SQLException sqle1) { setException(sqle1); logger.log(Level.WARNING, "Can not create Statement. Message: " + sqle1.getMessage().toString()); return 0; } logger.log(Level.FINE, "FileName: " + fileName); logger.log(Level.FINE, "Query : " + getQuery()); logger.log(Level.FINE, "Starting export..."); // create an empty sheet Workbook wb; Sheet sheet; Sheet sqlsheet; CreationHelper createHelper = null; //XSSFSheet xsheet; //HSSFSheet sheet; if (isXlsx()) { wb = new SXSSFWorkbook(); createHelper = wb.getCreationHelper(); } else { wb = new HSSFWorkbook(); createHelper = wb.getCreationHelper(); } sheet = wb.createSheet("Data Export"); // create a second sheet just containing the SQL Statement sqlsheet = wb.createSheet("SQL Statement"); Row sqlrow = sqlsheet.createRow(0); Cell sqltext = sqlrow.createCell(0); try { if (getQuery() != null) { sqltext.setCellValue(getQuery()); } else { sqltext.setCellValue(pstmt.toString()); } } catch (Exception lex) { } CellStyle style = wb.createCellStyle(); style.setWrapText(true); sqltext.setCellStyle(style); Row r = null; int row = 0; // row number int col = 0; // column number int columnCount = 0; try { //resultSet = stmt.executeQuery(getQuery()); resultSet = pstmt.executeQuery(); logger.log(Level.FINE, "query executed"); } catch (SQLException sqle2) { setException(sqle2); logger.log(Level.WARNING, "Can not execute query. Message: " + sqle2.getMessage().toString()); return 0; } // create Header in XLS-file ArrayList<String> head = new ArrayList(); try { rsmd = resultSet.getMetaData(); logger.log(Level.FINE, "Got MetaData of the resultset"); columnCount = rsmd.getColumnCount(); logger.log(Level.FINE, Integer.toString(columnCount) + " Columns in this resultset"); r = sheet.createRow(row); // titlerow if ((!isXlsx()) && (columnCount > 255)) { columnCount = 255; } for (int i = 0; i < columnCount; i++) { // we create the cell Cell cell = r.createCell(col); // set the value of the cell cell.setCellValue(rsmd.getColumnName(i + 1)); head.add(rsmd.getColumnName(i + 1)); // then we align center CellStyle cellStyle = wb.createCellStyle(); cellStyle.setAlignment(CellStyle.ALIGN_CENTER); // now we make it bold //HSSFFont f = wb.createFont(); Font headerFont = wb.createFont(); headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD); cellStyle.setFont(headerFont); //cellStyle.setFont(f); // adapt this font to the cell cell.setCellStyle(cellStyle); col++; } } catch (SQLException sqle3) { setException(sqle3); logger.log(Level.WARNING, "Can not create XLS-Header. Message: " + sqle3.getMessage().toString()); return 0; } // looping the resultSet int wbCounter = 0; try { while (resultSet.next()) { // this is the next row col = 0; // put column counter back to 0 to start at the next row row++; // next row // create a new sheet if more then 60'000 Rows and xls file if ((!isXlsx()) && (row % 65530 == 0)) { wbCounter++; row = 0; sheet = wb.createSheet("Data Export " + Integer.toString(wbCounter)); logger.log(Level.INFO, "created a further page because of a huge amount of data"); // create the head r = sheet.createRow(row); // titlerow for (int i = 0; i < head.size(); i++) { // we create the cell Cell cell = r.createCell(col); // set the value of the cell cell.setCellValue((String) head.get(i)); // then we align center CellStyle cellStyle = wb.createCellStyle(); cellStyle.setAlignment(CellStyle.ALIGN_CENTER); // now we make it bold //HSSFFont f = wb.createFont(); Font headerFont = wb.createFont(); headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD); cellStyle.setFont(headerFont); //cellStyle.setFont(f); // adapt this font to the cell cell.setCellStyle(cellStyle); col++; } row++; } try { r = sheet.createRow(row); } catch (Exception e) { logger.log(Level.WARNING, "Error while creating row number " + row + " " + e.getMessage()); wbCounter++; row = 0; sheet = wb.createSheet("Data Export " + Integer.toString(wbCounter)); logger.log(Level.WARNING, "created a further page in the hope it helps..."); // create the head r = sheet.createRow(row); // titlerow for (int i = 0; i < head.size(); i++) { // we create the cell Cell cell = r.createCell(col); // set the value of the cell cell.setCellValue((String) head.get(i)); // then we align center CellStyle cellStyle = wb.createCellStyle(); cellStyle.setAlignment(CellStyle.ALIGN_CENTER); // now we make it bold //HSSFFont f = wb.createFont(); Font headerFont = wb.createFont(); headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD); cellStyle.setFont(headerFont); //cellStyle.setFont(f); // adapt this font to the cell cell.setCellStyle(cellStyle); col++; } row++; } col = 0; // put column counter back to 0 to start at the next row String previousMessage = ""; for (int i = 0; i < columnCount; i++) { try { // depending on the type, create the cell switch (rsmd.getColumnType(i + 1)) { case java.sql.Types.INTEGER: r.createCell(col).setCellValue(resultSet.getInt(i + 1)); break; case java.sql.Types.FLOAT: r.createCell(col).setCellValue(resultSet.getFloat(i + 1)); break; case java.sql.Types.DOUBLE: r.createCell(col).setCellValue(resultSet.getDouble(i + 1)); break; case java.sql.Types.DECIMAL: r.createCell(col).setCellValue(resultSet.getFloat(i + 1)); break; case java.sql.Types.NUMERIC: r.createCell(col).setCellValue(resultSet.getFloat(i + 1)); break; case java.sql.Types.BIGINT: r.createCell(col).setCellValue(resultSet.getInt(i + 1)); break; case java.sql.Types.TINYINT: r.createCell(col).setCellValue(resultSet.getInt(i + 1)); break; case java.sql.Types.SMALLINT: r.createCell(col).setCellValue(resultSet.getInt(i + 1)); break; case java.sql.Types.DATE: // first we get the date java.sql.Date dat = resultSet.getDate(i + 1); java.util.Date date = new java.util.Date(dat.getTime()); r.createCell(col).setCellValue(date); break; case java.sql.Types.TIMESTAMP: // first we get the date java.sql.Timestamp ts = resultSet.getTimestamp(i + 1); Cell c = r.createCell(col); try { c.setCellValue(ts); // r.createCell(col).setCellValue(ts); // Date Format CellStyle cellStyle = wb.createCellStyle(); cellStyle.setDataFormat( createHelper.createDataFormat().getFormat("yyyy/mm/dd hh:mm:ss")); c.setCellStyle(cellStyle); } catch (Exception e) { c.setCellValue(" "); } break; case java.sql.Types.TIME: // first we get the date java.sql.Time time = resultSet.getTime(i + 1); r.createCell(col).setCellValue(time); break; case java.sql.Types.BIT: boolean b1 = resultSet.getBoolean(i + 1); r.createCell(col).setCellValue(b1); break; case java.sql.Types.BOOLEAN: boolean b2 = resultSet.getBoolean(i + 1); r.createCell(col).setCellValue(b2); break; case java.sql.Types.CHAR: r.createCell(col).setCellValue(resultSet.getString(i + 1)); break; case java.sql.Types.NVARCHAR: r.createCell(col).setCellValue(resultSet.getString(i + 1)); break; case java.sql.Types.VARCHAR: try { r.createCell(col).setCellValue(resultSet.getString(i + 1)); } catch (Exception e) { r.createCell(col).setCellValue(" "); logger.log(Level.WARNING, "Exception while writing column {0} row {3} type: {1} Message: {2}", new Object[] { col, rsmd.getColumnType(i + 1), e.getMessage(), row }); } break; default: r.createCell(col).setCellValue(resultSet.getString(i + 1)); break; } } catch (Exception e) { //e.printStackTrace(); if (resultSet.wasNull()) { r.createCell(col).setCellValue(" "); } else { logger.log(Level.WARNING, "Unhandled type at column {0}, row {3} type: {1}. Filling up with blank {2}", new Object[] { col, rsmd.getColumnType(i + 1), e.getMessage(), row }); r.createCell(col).setCellValue(" "); } } col++; } } //pstmt.close(); } catch (SQLException sqle3) { setException(sqle3); logger.log(Level.WARNING, "Exception while writing data into sheet. Message: " + sqle3.getMessage().toString()); } try { // Write the output to a file FileOutputStream fileOut = new FileOutputStream(fileName); wb.write(fileOut); fileOut.close(); logger.log(Level.INFO, "File created"); logger.log(Level.INFO, "Wrote: {0} lines into XLS-File", Integer.toString(row)); } catch (Exception e) { logger.log(Level.WARNING, "Exception while writing xls-File: " + e.getMessage().toString()); } return row; }
From source file:standarapp.algorithm.ReadFileVector.java
public String lectureRegistry(String nameFile, String nameOut, int[] col, double percent, int rowBegin) { String answer = ""; int quantityFound = 0; workbook = Lecture.lectureXLSX(nameFile); sheet = workbook.getSheetAt(0);//from ww w . j a v a2 s .c o m for (Row row : sheet) { if (row.getRowNum() < rowBegin) { continue; } String[] cellsWI = new String[col.length + 1]; for (int i = 0; i < col.length; i++) { cellsWI[i] = ""; try { Cell cell = row.getCell(col[i]); if (cell.getCellType() == Cell.CELL_TYPE_STRING) { cellsWI[i] = deleteTrash(cell.getStringCellValue()); } else { cellsWI[i] = cell.getDateCellValue().toString(); cellsWI[col.length] = String.valueOf(cell.getDateCellValue().getMonth()); } if (i == 2) { cellsWI[i] = cell.getStringCellValue(); } } catch (Exception e) { } } registry.add(cellsWI); } System.out.println("Total: " + registry.size()); /*for (int i = 0; i < registry.size(); i++) { System.out.println(i +" Municipio: " + registry.get(i)[0] + " | Localidad: " + registry.get(i)[1] + " | Especie: " + registry.get(i)[2] + " | Fecha: " + registry.get(i)[3]); }*/ int rowCount = 0; int columnCount = 0; CellStyle cs = workbook.createCellStyle(); //Font font = workbook.createFont(); cs.setAlignment(HorizontalAlignment.CENTER); cs.setVerticalAlignment(VerticalAlignment.CENTER); cs.setBorderRight(BorderStyle.THIN); cs.setBorderLeft(BorderStyle.THIN); cs.setBorderBottom(BorderStyle.THIN); cs.setBorderTop(BorderStyle.THIN); sheet = workbook.createSheet(); sheet.setFitToPage(true); sheet.setHorizontallyCenter(true); sheet.setColumnWidth(0, 20); Row row = sheet.createRow(0); Cell encabezado = row.createCell(rowCount); encabezado.setCellValue("Base de datos coordenadas"); encabezado.setCellStyle(cs); CellRangeAddress region = new CellRangeAddress(0, 0, 0, 8); sheet.addMergedRegion(region); row = sheet.createRow(++rowCount); Cell cell = row.createCell(columnCount); cell.setCellValue("Especie"); cell.setCellStyle(cs); cell = row.createCell(++columnCount); cell.setCellValue("Municipio"); cell.setCellStyle(cs); cell = row.createCell(++columnCount); cell.setCellValue("Codigo Municipio"); cell.setCellStyle(cs); cell = row.createCell(++columnCount); cell.setCellValue("Vereda"); cell.setCellStyle(cs); cell = row.createCell(++columnCount); cell.setCellValue("Codigo Vereda"); cell.setCellStyle(cs); cell = row.createCell(++columnCount); cell.setCellValue("Mes"); cell.setCellStyle(cs); cell = row.createCell(++columnCount); cell.setCellValue("Ao"); cell.setCellStyle(cs); cell = row.createCell(++columnCount); cell.setCellValue("Latitud"); cell.setCellStyle(cs); cell = row.createCell(++columnCount); cell.setCellValue("Longitud"); cell.setCellStyle(cs); cell = row.createCell(++columnCount); cell.setCellValue("Fuente"); cell.setCellStyle(cs); for (int i = 0; i < registry.size(); i++) { try { String[] registro = registry.get(i); columnCount = -1; int cod_Mncp = 0; row = sheet.createRow(++rowCount); double levenstein = 0; double localidad_oficial = 0; double levensteinActual = 0; for (Integer codMunicipio : codigo_Municipio.keySet()) { if (registro[0].equals(codigo_Municipio.get(codMunicipio))) { cod_Mncp = codMunicipio; break; } try { double levenstein_local = FuzzySearch.ratio(registro[0], codigo_Municipio.get(codMunicipio)); if (levenstein_local >= levensteinActual) { cod_Mncp = codMunicipio; levensteinActual = levenstein_local; } if (levensteinActual == 100) { break; } } catch (Exception e) { } } for (Double cod_Loc : mncp_localidad.get(cod_Mncp).keySet()) { String loc = mncp_localidad.get(cod_Mncp).get(cod_Loc); if (registro[1].equals(loc)) { localidad_oficial = cod_Loc; levenstein = 101; } try { double levenstein_local = FuzzySearch.ratio(registro[1], loc); if (levenstein_local >= levenstein) { localidad_oficial = cod_Loc; levenstein = levenstein_local; } if (levenstein == 100) { break; } } catch (Exception e) { } } String mncp_oficial = codigo_Municipio.get(cod_Mncp); String loc_oficial = codigo_localidad.get(localidad_oficial); String especie = registro[2]; double locX = localidad_x.get(localidad_oficial); double locY = localidad_y.get(localidad_oficial); int year = 0; int month = 0; System.out.println(); try { year = Integer.parseInt(registro[3].split(" ")[5]); month = Integer.parseInt(registro[registro.length - 1]) + 1; } catch (Exception e) { year = Integer.parseInt(registro[3].substring(registro[3].length() - 4)); month = Integer .parseInt(registro[3].substring(registro[3].length() - 7, registro[3].length() - 5)); } quantityFound++; cell = row.createCell(++columnCount); cell.setCellValue(especie); cell.setCellStyle(cs); cell = row.createCell(++columnCount); cell.setCellValue(mncp_oficial); cell.setCellStyle(cs); cell = row.createCell(++columnCount); cell.setCellValue(cod_Mncp); cell.setCellStyle(cs); cell = row.createCell(++columnCount); cell.setCellValue(loc_oficial); cell.setCellStyle(cs); cell = row.createCell(++columnCount); cell.setCellValue(localidad_oficial); cell.setCellStyle(cs); cell = row.createCell(++columnCount); cell.setCellValue(month); cell.setCellStyle(cs); cell = row.createCell(++columnCount); cell.setCellValue(year); cell.setCellStyle(cs); cell = row.createCell(++columnCount); cell.setCellValue(locY); cell.setCellStyle(cs); cell = row.createCell(++columnCount); cell.setCellValue(locX); cell.setCellStyle(cs); } catch (Exception e) { continue; } } sheet.setColumnWidth(0, 5800); sheet.setColumnWidth(1, 5800); sheet.setColumnWidth(2, 3000); sheet.setColumnWidth(3, 5800); sheet.setColumnWidth(4, 3000); sheet.setColumnWidth(5, 3000); sheet.setColumnWidth(6, 3000); sheet.setColumnWidth(7, 6400); sheet.setColumnWidth(8, 6400); answer = "Se generaron " + quantityFound + " vector(es)"; try (FileOutputStream outputStream = new FileOutputStream(nameOut)) { workbook.write(outputStream); } catch (IOException ex) { quantityFound = 0; answer = "Cerrar el archivo de entrada "; } return answer; }
From source file:test.poi.MyExcelDemo.java
License:Apache License
/** * Create a library of cell styles/*from ww w. ja va 2 s .c o m*/ */ private static Map<String, CellStyle> createStyles(Workbook wb) { Map<String, CellStyle> styles = new HashMap<String, CellStyle>(); CellStyle style; Font titleFont = wb.createFont(); titleFont.setFontHeightInPoints((short) 18); titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setFont(titleFont); styles.put("title", style); Font headerFont = wb.createFont(); headerFont.setFontHeightInPoints((short) 11); headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); headerFont.setColor(IndexedColors.BLACK.getIndex()); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setFillForegroundColor(IndexedColors.WHITE.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setFont(headerFont); // style.setWrapText(true); style.setBorderRight(CellStyle.BORDER_THIN); style.setRightBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderLeft(CellStyle.BORDER_THIN); style.setLeftBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderTop(CellStyle.BORDER_THIN); style.setTopBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderBottom(CellStyle.BORDER_THIN); style.setBottomBorderColor(IndexedColors.BLACK.getIndex()); styles.put("header", style); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setWrapText(true); style.setBorderRight(CellStyle.BORDER_THIN); style.setRightBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderLeft(CellStyle.BORDER_THIN); style.setLeftBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderTop(CellStyle.BORDER_THIN); style.setTopBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderBottom(CellStyle.BORDER_THIN); style.setBottomBorderColor(IndexedColors.BLACK.getIndex()); styles.put("cell", style); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setDataFormat(wb.createDataFormat().getFormat("0.00")); styles.put("formula", style); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setDataFormat(wb.createDataFormat().getFormat("0.00")); styles.put("formula_2", style); return styles; }
From source file:uk.co.spudsoft.birt.emitters.excel.StyleManager.java
License:Open Source License
/** * Create a new POI CellStyle based upon a BIRT style. * @param birtStyle//w w w . j ava2 s . com * The BIRT style to base the CellStyle upon. * @return * The CellStyle whose attributes are described by the BIRT style. */ private CellStyle createStyle(BirtStyle birtStyle) { CellStyle poiStyle = workbook.createCellStyle(); // Font Font font = fm.getFont(birtStyle); if (font != null) { poiStyle.setFont(font); } // Alignment poiStyle.setAlignment( smu.poiAlignmentFromBirtAlignment(birtStyle.getString(StyleConstants.STYLE_TEXT_ALIGN))); // Background colour smu.addBackgroundColourToStyle(workbook, poiStyle, birtStyle.getString(StyleConstants.STYLE_BACKGROUND_COLOR)); // Top border smu.applyBorderStyle(workbook, poiStyle, BorderSide.TOP, birtStyle.getProperty(StyleConstants.STYLE_BORDER_TOP_COLOR), birtStyle.getProperty(StyleConstants.STYLE_BORDER_TOP_STYLE), birtStyle.getProperty(StyleConstants.STYLE_BORDER_TOP_WIDTH)); // Left border smu.applyBorderStyle(workbook, poiStyle, BorderSide.LEFT, birtStyle.getProperty(StyleConstants.STYLE_BORDER_LEFT_COLOR), birtStyle.getProperty(StyleConstants.STYLE_BORDER_LEFT_STYLE), birtStyle.getProperty(StyleConstants.STYLE_BORDER_LEFT_WIDTH)); // Right border smu.applyBorderStyle(workbook, poiStyle, BorderSide.RIGHT, birtStyle.getProperty(StyleConstants.STYLE_BORDER_RIGHT_COLOR), birtStyle.getProperty(StyleConstants.STYLE_BORDER_RIGHT_STYLE), birtStyle.getProperty(StyleConstants.STYLE_BORDER_RIGHT_WIDTH)); // Bottom border smu.applyBorderStyle(workbook, poiStyle, BorderSide.BOTTOM, birtStyle.getProperty(StyleConstants.STYLE_BORDER_BOTTOM_COLOR), birtStyle.getProperty(StyleConstants.STYLE_BORDER_BOTTOM_STYLE), birtStyle.getProperty(StyleConstants.STYLE_BORDER_BOTTOM_WIDTH)); // Number format smu.applyNumberFormat(workbook, birtStyle, poiStyle, locale); // Whitespace/wrap if (CSSConstants.CSS_PRE_VALUE.equals(birtStyle.getString(StyleConstants.STYLE_WHITE_SPACE))) { poiStyle.setWrapText(true); } // Vertical alignment if (CSSConstants.CSS_TOP_VALUE.equals(birtStyle.getString(StyleConstants.STYLE_VERTICAL_ALIGN))) { poiStyle.setVerticalAlignment(CellStyle.VERTICAL_TOP); } else if (CSSConstants.CSS_MIDDLE_VALUE.equals(birtStyle.getString(StyleConstants.STYLE_VERTICAL_ALIGN))) { poiStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); } else if (CSSConstants.CSS_BOTTOM_VALUE.equals(birtStyle.getString(StyleConstants.STYLE_VERTICAL_ALIGN))) { poiStyle.setVerticalAlignment(CellStyle.VERTICAL_BOTTOM); } // Rotation CSSValue rotation = birtStyle.getProperty(BirtStyle.TEXT_ROTATION); if (rotation instanceof FloatValue) { poiStyle.setRotation((short) ((FloatValue) rotation).getFloatValue()); } styles.add(new StylePair(birtStyle.clone(), poiStyle)); return poiStyle; }