List of usage examples for org.apache.poi.xssf.usermodel XSSFCellStyle setFillPattern
@Override public void setFillPattern(FillPatternType pattern)
From source file:reports.new711report.java
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { try {//from w ww . j a va 2 s .c o m response.setContentType("text/html;charset=UTF-8"); //a page to get Report of all the servlets String year = "2016"; String month = "4"; String county = ""; String form = "moh711_new"; if (request.getParameter("year") != null) { year = request.getParameter("year"); } if (request.getParameter("county") != null) { county = request.getParameter("county"); } if (request.getParameter("month") != null) { month = request.getParameter("month"); } if (request.getParameter("form") != null) { form = request.getParameter("form"); } String pivotform = form; if (form.equalsIgnoreCase("MOH 731")) { form = "MOH731"; } if (form.equalsIgnoreCase("MOH 711 (New)")) { form = "moh711_new"; } String facilitywhere = ""; String yearwhere = ""; String monthwhere = ""; String countywhere = ""; String districtwhere = ""; String reporttype = ""; if (!year.equals("")) { yearwhere = " and Annee = '" + year + "'"; } if (!county.equals("")) { countywhere = " and countyid = '" + county + "'"; } if (!month.equals("")) { monthwhere = " and Mois = '" + month + "'"; } dbConn conn = new dbConn(); //an array to store haeder information. //the header information should appear only if a certain parameters are met //The parameters listed in here can be removed if the report type doesnt require certain parameters ArrayList Headerorgunits = new ArrayList(); Headerorgunits.add("COUNTY"); Headerorgunits.add("SUB-COUNTY"); Headerorgunits.add("FACILITY"); Headerorgunits.add("MFL CODE"); //An arralist to store a list of columns that will be selected from the database ArrayList dbcolumns = new ArrayList(); ArrayList labels = new ArrayList(); ArrayList tablename = new ArrayList(); ArrayList iscumulative = new ArrayList(); ArrayList ispercent = new ArrayList(); // ArrayList isactive=new ArrayList(); //An arralist to store a list of worksheets that will be selected from the sections and the respective service area to determine the facilities whose data will appear in that sheet ArrayList worksheets = new ArrayList(); //An arralist to store distinct worksheets. This will be derived from the the sections column ArrayList distinctsheets = new ArrayList(); ArrayList distinctservicearea = new ArrayList(); String selectdistinctworksheet = "select section,servicearea from pivottable where form='" + form.replace("_", "") + "' and active='1' group by section order by order_per_form"; conn.rs = conn.st.executeQuery(selectdistinctworksheet); while (conn.rs.next()) { //add the name of distinct sections distinctsheets.add(conn.rs.getString(1).replace("/", "_")); String servicearea = " 2=2 "; if (conn.rs.getString(2) != null) { servicearea = " " + conn.rs.getString(2) + "=1"; } distinctservicearea.add(servicearea); } String getattribs = "select indicator,label,section,cumulative,percentage,active ,shortlabel from pivottable where form='" + form.replace("_", "") + "' order by order_per_form, section"; conn.rs = conn.st.executeQuery(getattribs); while (conn.rs.next()) { //add active indicators only if (conn.rs.getString("active").equals("1")) { System.out.println(conn.rs.getString("indicator") + ""); //add indicator dbcolumns.add(conn.rs.getString("indicator")); //add label if (form.equals("moh731")) { labels.add(conn.rs.getString("shortlabel") + " \n " + conn.rs.getString("label")); } else { labels.add(conn.rs.getString("label")); } //add worksheets worksheets.add(conn.rs.getString("section").replace("/", "_")); String perc = "0"; String cum = "0"; if (conn.rs.getString("cumulative") != null) { iscumulative.add(conn.rs.getString("cumulative")); } else { iscumulative.add(cum); } if (conn.rs.getString("percentage") != null) { ispercent.add(conn.rs.getString("percentage")); } else { ispercent.add(perc); } } //end of active } //end of pivot table active //if String perfacilselect = "select Upper(County) as County , Upper(DistrictNom) as District , UPPER(SubPartnerNom) as facility ,CentreSanteId as mflcode , district.CountyID as countyid , "; //-------------------------------------------------------------------------------------------- // PREPARE SELECT //-------------------------------------------------------------------------------------------- //prepare selects for (int a = 0; a < dbcolumns.size(); a++) { //if the indicator is a percent, get an avaerage if (ispercent.get(a).equals("1")) { perfacilselect += " AVG(" + dbcolumns.get(a) + ") as " + dbcolumns.get(a); } else if (iscumulative.get(a).equals("1")) { perfacilselect += " " + dbcolumns.get(a) + " as " + dbcolumns.get(a); } else { perfacilselect += " SUM(" + dbcolumns.get(a) + ") as " + dbcolumns.get(a); } //if the item is not the last, append a comma //if(a<dbcolumns.size()-1){ perfacilselect += " ,"; // } } //------------------------------------------------------------------------------------ // FROM //------------------------------------------------------------------------------------ perfacilselect += " isValidated as Form_Validated from " + form + " join ( subpartnera join (district join county on county.CountyID=district.CountyID ) on district.DistrictID = subpartnera.DistrictID ) on " + form + ".SubPartnerID = subpartnera.SubPartnerID "; //------------------------------------------------------------------------------------------ // WHERE //------------------------------------------------------------------------------------------ perfacilselect += " where 1=1 " + monthwhere + yearwhere; //----------------------------------------------------------------------------------------- //GROUP BY //---------------------------------------------------------------------------------------- perfacilselect += " group by subpartnera.SubPartnerID"; //System.out.println(perfacilselect); //______________________________________________________________________________________ // NOW CREATE THE WORKSHEETS //______________________________________________________________________________________ XSSFWorkbook wb = new XSSFWorkbook(); //______________________________________________________________________________________ //______________________________________________________________________________________ XSSFFont 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); XSSFFont 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); XSSFCellStyle 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); XSSFCellStyle stylex = wb.createCellStyle(); stylex.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); stylex.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); stylex.setBorderTop(HSSFCellStyle.BORDER_THIN); stylex.setBorderBottom(HSSFCellStyle.BORDER_THIN); stylex.setBorderLeft(HSSFCellStyle.BORDER_THIN); stylex.setBorderRight(HSSFCellStyle.BORDER_THIN); stylex.setAlignment(HSSFCellStyle.ALIGN_LEFT); XSSFFont fontx = wb.createFont(); fontx.setColor(HSSFColor.BLACK.index); fontx.setFontName("Cambria"); stylex.setFont(fontx); stylex.setWrapText(true); for (int b = 0; b < distinctsheets.size(); b++) { XSSFSheet shet = wb.createSheet(distinctsheets.get(b).toString().toUpperCase()); //create headers for that worksheet XSSFRow rw = shet.createRow(1); int headercellpos = 0; //create the orgunit header eg COUNTY | SUBCOUNTY | FACILITY for (int e = 0; e < Headerorgunits.size(); e++) { XSSFCell cell0 = rw.createCell(headercellpos); cell0.setCellValue(Headerorgunits.get(e).toString()); cell0.setCellStyle(stylex); headercellpos++; shet.setColumnWidth(e, 6000); } //create the indicators header eg HV0101 | HIV 09676 | TOTAL for (int c = 0; c < dbcolumns.size(); c++) { //compare if the indicator belongs to the specified section and hence worksheet //recall, each indicator has got an associated section / worksheet //An indicator should be put as an header in the respective worksheet if (worksheets.get(c).equals(distinctsheets.get(b))) { shet.setColumnWidth(headercellpos, 6000); XSSFCell cell0 = rw.createCell(headercellpos); cell0.setCellValue(labels.get(c).toString()); cell0.setCellStyle(stylex); headercellpos++; } //end of comparing if } //end of for loop //create is validated header shet.setColumnWidth(headercellpos, 6000); XSSFCell cell0 = rw.createCell(headercellpos); cell0.setCellValue("Form Validated ?"); cell0.setCellStyle(stylex); headercellpos++; } String sectioncopy = ""; int sheetpos = 0; int rowpos = 2; //-----------------INSIDE THE DATA FORM--------------------------------- //if the section changes, change the position of the worksheet too //also, reset the position counter to begin from 2 again. XSSFSheet shet = null; // if(--!sectioncopy.equals(shet)){} //create the org unit data values e.g BARINGO | BARINGO CENTRAL |KABARNET DISTRICT HOSPITAL | MFL CODE for (int g = 0; g < distinctsheets.size(); g++) { shet = wb.getSheetAt(g); int colpos = 0; String finalquery = perfacilselect.replace("1=1", distinctservicearea.get(g).toString()); System.out.println("" + finalquery); conn.rs = conn.st.executeQuery(finalquery); while (conn.rs.next()) { //the fourth cell should XSSFRow rw = shet.createRow(rowpos); for (int e = 0; e < Headerorgunits.size(); e++) { XSSFCell cell0 = rw.createCell(colpos); //for mfl code, last header, print integers if (Headerorgunits.get(e).toString().equals("MFL CODE")) { cell0.setCellValue(conn.rs.getInt(e + 1)); } else { cell0.setCellValue(conn.rs.getString(e + 1)); } cell0.setCellStyle(style2); colpos++; } //_________________________________________________________________ //VALUES //_________________________________________________________________ //create the indicators values eg 90 | 45 | 356 for (int c = 0; c < dbcolumns.size(); c++) { //get the section of the current dbcolumn //compare if the indicator belongs to the specified section and hence worksheet //recall, each indicator has got an associated section / worksheet //An indicator should be put as an header in the respective worksheet if (worksheets.get(c).equals(distinctsheets.get(g))) { XSSFCell cell0 = rw.createCell(colpos); cell0.setCellValue(conn.rs.getInt(dbcolumns.get(c).toString())); cell0.setCellStyle(stborder); colpos++; } //end of comparing if } //end of for loop String isvalidated = "Yes"; if (conn.rs.getString("Form_Validated").equals("0")) { isvalidated = "No"; } XSSFCell cell0 = rw.createCell(colpos); cell0.setCellValue(isvalidated); cell0.setCellStyle(stborder); colpos++; rowpos++; colpos = 0; } rowpos = 2; } IdGenerator IG = new IdGenerator(); String createdOn = IG.CreatedOn(); System.out.println("" + form.toUpperCase().trim() + "_REPORT_FOR_" + year.trim() + "(" + month.trim() + ")_CREATED_" + createdOn.trim() + ".xlsx"); 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.toUpperCase().trim() + "_REPORT_FOR_" + year.trim() + "(" + month.trim() + ")_CREATED_" + createdOn.trim() + ".xlsx"); OutputStream outStream = response.getOutputStream(); outStream.write(outArray); outStream.flush(); } catch (SQLException ex) { Logger.getLogger(allStaticReportsMonthly.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:reports.Reached_OthersMessages.java
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException, SQLException, InvalidFormatException { session = request.getSession();/*from w w w. j av a 2 s . co m*/ dbConn conn = new dbConn(); ouputyear = outputMonth = ""; if (session.getAttribute("PepfarYear") != null) { year = Integer.parseInt(session.getAttribute("PepfarYear").toString()); quarter = Integer.parseInt(session.getAttribute("period").toString()); // year=2015; // quarter=2; System.out.println("dates are : " + year + " quarter : " + quarter); if (quarter == 4) { reportYear = year - 1; startmonth = "10"; endMonth = "12"; prevReportYear = reportYear; prevReportMonth = "09"; } else { reportYear = year; if (quarter == 1) { startmonth = "01"; endMonth = "03"; prevReportYear = reportYear - 1; prevReportMonth = "12"; } if (quarter == 2) { startmonth = "04"; endMonth = "06"; prevReportYear = reportYear; prevReportMonth = "03"; } if (quarter == 3) { startmonth = "07"; endMonth = "09"; prevReportYear = reportYear; prevReportMonth = "06"; } } startDate = "" + startmonth + "/01/" + reportYear + ""; endDate = "" + endMonth + "/31/" + reportYear + ""; startDate1 = "" + reportYear + "-" + startmonth + "-01"; endDate1 = "" + reportYear + "-" + endMonth + "-31"; endYearMonth = prevReportYear + "" + prevReportMonth; position = 1; // ouputyear=""+reportYear; if (quarter == 1) { outputMonth = "Jan_March_" + reportYear; } if (quarter == 2) { outputMonth = "Apr_Jun_" + reportYear; } if (quarter == 3) { outputMonth = "July_Sept_" + reportYear; } if (quarter == 4) { outputMonth = "Oct_Dec_" + reportYear; } String reportHeader[] = ("COUNTY NAME ,PARTNER NAME,DISTRICT NAME,DIC NAME, GROUP NAME,CLIENT NAME , AGE BRACKET, GENDER,YEAR,MONTH") .split(","); // COPY FILE TO BE WRITTEN TO Path original = Paths.get(getServletContext().getRealPath("/OTHER_MESSAGES.xlsm")); //original file Path destination = Paths.get(getServletContext().getRealPath("/OTHER_MESSAGES_1.xlsm")); //new file System.out.println("origin : " + original + " destination : " + destination); try { Files.copy(original, destination, StandardCopyOption.REPLACE_EXISTING); System.out.println("file copied----------------"); } catch (IOException x) { //catch all for IO problems System.out.println("fine not copied"); } String allpath = getServletContext().getRealPath("/OTHER_MESSAGES_1.xlsm"); // ^^^^^^^^^^^^^CREATE STATIC AND WRITE STATIC DATA TO THE EXCELL^^^^^^^^^^^^ XSSFWorkbook wb; OPCPackage pkg = OPCPackage.open(allpath); wb = new XSSFWorkbook(pkg); // ^^^^^^^^^^^^^CREATE STATIC AND WRITE STATIC DATA TO THE EXCELL^^^^^^^^^^^^ // HSSFWorkbook wb=new HSSFWorkbook(); XSSFSheet shet1 = wb.getSheet("Sheet1"); XSSFFont font = wb.createFont(); font.setFontHeightInPoints((short) 18); font.setFontName("Arial Black"); font.setColor((short) 0000); CellStyle style = wb.createCellStyle(); style.setFont(font); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); XSSFFont font2 = wb.createFont(); font2.setFontName("Arial Black"); font2.setColor((short) 0000); CellStyle style2 = wb.createCellStyle(); style2.setFont(font2); XSSFCellStyle 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); for (int i = 0; i <= reportHeader.length; i++) { shet1.setColumnWidth(i, 4000); } XSSFCellStyle styleBorder = wb.createCellStyle(); styleBorder.setBorderTop(HSSFCellStyle.BORDER_THIN); styleBorder.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleBorder.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleBorder.setBorderRight(HSSFCellStyle.BORDER_THIN); styleBorder.setAlignment(HSSFCellStyle.ALIGN_CENTER); XSSFCellStyle stylex = wb.createCellStyle(); stylex.setFillForegroundColor(HSSFColor.LIME.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); XSSFFont fontx = wb.createFont(); fontx.setColor(HSSFColor.DARK_BLUE.index); stylex.setFont(fontx); stylex.setWrapText(true); XSSFCell cell; XSSFRow rw0 = shet1.createRow(0); rw0.setHeightInPoints(30); rw0.setRowStyle(style2); for (int i = 0; i <= (reportHeader.length - 1); i++) { cell = rw0.createCell(i); cell.setCellValue(reportHeader[i]); cell.setCellStyle(stylex); } String query = "SELECT CLIENT, " + "" + "if( (dateRegister BETWEEN '" + startDate1 + "' AND '" + endDate1 + "'),dateRegister,dateAdherence) REPORTDATE," + " " + "dateRegister,dateAdherence, countyName, partnerName, districtName, AGEBRACKET, SEX,fname,mname,lname,GROUPNAME,DIC FROM (" + "SELECT DISTINCT(tempData.clientID) as CLIENT,if( (reg2Date BETWEEN '" + startDate1 + "' AND '" + endDate1 + "'),reg2Date,'0') dateRegister,if( (AdherenceDate BETWEEN '" + startDate1 + "' AND '" + endDate1 + "'),AdherenceDate,'0') dateAdherence, countyName, partnerName, districtName, AGEBRACKET, SEX,fname,mname,lname,GROUPNAME,DIC FROM (" + "SELECT DISTINCT(personal_information.client_id) as clientID,STR_TO_DATE(register2.date,'%m/%d/%Y') as reg2Date,STR_TO_DATE(adherence.date_of_session,'%m/%d/%Y') as AdherenceDate," + " CONCAT(personal_information.completionyear,personal_information.completionmonth) AS YEARMONTH," + "county.county_name AS countyName,partner.partner_name AS partnerName,district.district_name AS districtName," + "CASE " + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 0 AND 9 THEN '0-9' " + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 10 AND 14 THEN '10-14' " + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 15 AND 19 THEN '15-19' " + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 20 AND 24 THEN '20-24' " + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 25 AND 49 THEN '25-49' " + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) >49 THEN '50 and above' " + " ELSE 'NO DATE OF BIRTH'" + " END AS AGEBRACKET," + " CASE " + "when personal_information.gender LIKE 'Female' THEN 'F' " + "when personal_information.gender LIKE 'Male' THEN 'M' " + "ELSE 'NO SEX' " + "END AS SEX,personal_information.fname as fname,personal_information.mname as mname ,personal_information.lname as lname," + "groups.group_name as GROUPNAME,dic.dic_name as DIC " + " FROM personal_information " + "LEFT JOIN register2 ON personal_information.client_id=register2.client_id " + "LEFT JOIN adherence ON personal_information.client_id=adherence.client_id " + "LEFT JOIN groups ON personal_information.group_id=groups.group_id " + "LEFT JOIN dic ON personal_information.dic_id=dic.dic_id " + "LEFT JOIN district ON personal_information.district_id=district.district_id " + "LEFT JOIN partner ON personal_information.partner_id=partner.partner_id " + "LEFT JOIN county ON district.county_id=county.county_id " + "WHERE personal_information.completionyear>0 " + "&& (register2.datekey BETWEEN '" + startDate1.replace("-", "") + "' AND '" + endDate1.replace("-", "") + "' || STR_TO_DATE(adherence.date_of_session,'%m/%d/%Y') BETWEEN STR_TO_DATE('" + startDate + "','%m/%d/%Y') AND STR_TO_DATE('" + endDate + "','%m/%d/%Y'))" + " HAVING YEARMONTH<=" + endYearMonth + ") AS tempData GROUP BY tempData.clientID) AS finalTable "; System.out.println(query); conn.rs = conn.st.executeQuery(query); while (conn.rs.next()) { countyName = conn.rs.getString(5); partnerName = conn.rs.getString(6); districtName = conn.rs.getString(7); reportDate = conn.rs.getString(2); ageBracket = conn.rs.getString(8); gender = conn.rs.getString(9); fname = conn.rs.getString(10); mname = conn.rs.getString(11); lname = conn.rs.getString(12); if (conn.rs.getString(13) != null) { groupName = conn.rs.getString(13); } else { groupName = "Individual"; } if (conn.rs.getString(14) != null) { groupName = conn.rs.getString(14); } else { dic_name = "NO DIC"; } if (mname.equals(lname)) { mname = ""; } fullName = fname + " " + mname + " " + lname; String dateSplit[] = reportDate.split("-"); if (dateSplit[1].equals("01")) { month = "Jan"; } if (dateSplit[1].equals("02")) { month = "Feb"; } if (dateSplit[1].equals("03")) { month = "Mar"; } if (dateSplit[1].equals("04")) { month = "Apr"; } if (dateSplit[1].equals("05")) { month = "May"; } if (dateSplit[1].equals("06")) { month = "Jun"; } if (dateSplit[1].equals("07")) { month = "Jul"; } if (dateSplit[1].equals("08")) { month = "Aug"; } if (dateSplit[1].equals("09")) { month = "Sept"; } if (dateSplit[1].equals("10")) { month = "Oct"; } if (dateSplit[1].equals("11")) { month = "Nov"; } if (dateSplit[1].equals("12")) { month = "Dec"; } year2 = dateSplit[0]; String data[] = (countyName + "," + partnerName + "," + districtName + "," + dic_name + "," + groupName + "," + fullName + "," + ageBracket + "," + gender + "," + year2 + "," + month) .split(","); XSSFRow rw1 = shet1.createRow(position); rw1.setHeightInPoints(25); rw1.setRowStyle(style2); for (int i = 0; i <= (data.length - 1); i++) { cell = rw1.createCell(i); cell.setCellValue(data[i]); cell.setCellStyle(styleBorder); } position++; } IdGenerator CRT = new IdGenerator(); 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=PWP_REACHED_WITH_OTHER_MESSAGES_REPORT_FOR_" + outputMonth + "_CREATED_ON_" + CRT.timestamp() + ".xlsm"); OutputStream outStream = response.getOutputStream(); outStream.write(outArray); outStream.flush(); pkg.close(); } else { session.setAttribute("kePMSError", "<font color=\"red\">Error : Please try again.</font>"); response.sendRedirect("kePMS.jsp"); } }
From source file:rpt.GUI.ProgramStrategist.CyclePlans.CompareDialogController.java
private void writeHeaders(Workbook wb, Row row, Boolean addOldSOP) { Cell cell = row.createCell(0);// w w w . j a v a2 s .c o m XSSFCellStyle style = (XSSFCellStyle) wb.createCellStyle(); Font headerFont = wb.createFont(); headerFont.setBold(true); style.setFillForegroundColor(new XSSFColor(new java.awt.Color(220, 220, 220))); style.setFillPattern(FillPatternType.SOLID_FOREGROUND); style.setFont(headerFont); cell.setCellStyle(style); cell.setCellValue("Plant"); cell = row.createCell(1); cell.setCellStyle(style); cell.setCellValue("Platform"); cell = row.createCell(2); cell.setCellStyle(style); cell.setCellValue("Vehicle"); cell = row.createCell(3); cell.setCellStyle(style); cell.setCellValue("Propulsion"); cell = row.createCell(4); cell.setCellStyle(style); cell.setCellValue("Denomination"); cell = row.createCell(5); cell.setCellStyle(style); cell.setCellValue("Fuel"); cell = row.createCell(6); cell.setCellStyle(style); cell.setCellValue("Engine Family"); cell = row.createCell(7); cell.setCellStyle(style); cell.setCellValue("Generation"); cell = row.createCell(8); cell.setCellStyle(style); cell.setCellValue("Engine Code"); cell = row.createCell(9); cell.setCellStyle(style); cell.setCellValue("Displacement"); cell = row.createCell(10); cell.setCellStyle(style); cell.setCellValue("Engine power (PS)"); cell = row.createCell(11); cell.setCellStyle(style); cell.setCellValue("Electric motor power (PS)"); cell = row.createCell(12); cell.setCellStyle(style); cell.setCellValue("Torque"); cell = row.createCell(13); cell.setCellStyle(style); cell.setCellValue("Torque overboost"); cell = row.createCell(14); cell.setCellStyle(style); cell.setCellValue("Gearbox Type"); cell = row.createCell(15); cell.setCellStyle(style); cell.setCellValue("Gears"); cell = row.createCell(16); cell.setCellStyle(style); cell.setCellValue("Gearbox"); cell = row.createCell(17); cell.setCellStyle(style); cell.setCellValue("Driveline"); cell = row.createCell(18); cell.setCellStyle(style); cell.setCellValue("Transmission Code"); cell = row.createCell(19); cell.setCellStyle(style); cell.setCellValue("Cert Group"); cell = row.createCell(20); cell.setCellStyle(style); cell.setCellValue("Emission Class"); cell = row.createCell(21); cell.setCellStyle(style); cell.setCellValue("SOP"); cell = row.createCell(22); cell.setCellStyle(style); cell.setCellValue("EOP"); if (addOldSOP) { cell = row.createCell(23); cell.setCellStyle(style); cell.setCellValue("Old SOP"); } if (addOldSOP) { //Same boolean flag cell = row.createCell(24); cell.setCellStyle(style); cell.setCellValue("Old EOP"); } }
From source file:se.minstrel.tools.xssfbuilder.style.impl.StyleBuilderImpl.java
License:Open Source License
private XSSFCellStyle buildStyle() { XSSFWorkbook wb = support.getWorkbook(); XSSFFont font = wb.createFont();/*from w ww .j av a2s . co m*/ font.setFontName(style.getFont()); font.setFontHeightInPoints(style.getFontSize()); font.setBold(style.isBold()); font.setItalic(style.isItalics()); if (style.getFgColor() != null) { font.setColor(new XSSFColor(style.getFgColor())); } XSSFCellStyle cs = support.getWorkbook().createCellStyle(); cs.setFont(font); if (style.getBgColor() != null) { cs.setFillForegroundColor(new XSSFColor(style.getBgColor())); cs.setFillPattern(FillPatternType.SOLID_FOREGROUND); } if (style.getFormat() != null) { cs.setDataFormat(support.getDataFormat().getFormat(style.getFormat())); } // style.getBgColor(); // style.getFgColor(); // style.isBold(); // style.isItalics(); return cs; }
From source file:Servelt.ExcelWriter.java
private void setMainCell(XSSFCell cell, String name) { XSSFCellStyle style = workbook.createCellStyle(); style.setFillForegroundColor(HSSFColor.ORANGE.index); style.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND); style.setAlignment(XSSFCellStyle.ALIGN_CENTER); XSSFFont font = workbook.createFont(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); style.setFont(font);// w ww . jav a 2s .c o m cell.setCellValue(name); cell.setCellStyle(style); }
From source file:Servelt.ExcelWriter.java
private void setAttrCell(XSSFCell cell, String name) { XSSFCellStyle style = workbook.createCellStyle(); style.setFillForegroundColor(HSSFColor.YELLOW.index); style.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND); style.setAlignment(XSSFCellStyle.ALIGN_CENTER); XSSFFont font = workbook.createFont(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); style.setFont(font);/*from w w w.j av a 2 s. c o m*/ cell.setCellValue(name); cell.setCellStyle(style); }
From source file:Servelt.ExcelWriter.java
private void setDesCell(XSSFCell cell, String name) { XSSFCellStyle style = workbook.createCellStyle(); style.setFillForegroundColor(HSSFColor.GREEN.index); style.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND); style.setAlignment(XSSFCellStyle.ALIGN_CENTER); XSSFFont font = workbook.createFont(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); style.setFont(font);//from w w w .jav a2s.c o m cell.setCellValue(name); cell.setCellStyle(style); }
From source file:Sheets.GlobalSheet.java
@Override protected void createDateRows() throws SQLException { int currentColN = 11; Row row1 = sheet.getRow(21); //row com os "X" dos dias letivos Row row2 = sheet.getRow(23); //row com os dias da semana Row row3 = sheet.getRow(24); //row com as datas XSSFCellStyle style; for (CustomDate cDate : dates) { XSSFCell cell1 = (XSSFCell) row1.getCell(currentColN); XSSFCell cell2 = (XSSFCell) row2.getCell(currentColN); XSSFCell cell3 = (XSSFCell) row3.getCell(currentColN); boolean classRegistered = false; for (StudentClassDiscipline studentClassDiscipline : studentClassDisciplines) { ArrayList<Class> classes = ClassDAO .getClassesByStudentClassDisciplineAndDate(studentClassDiscipline, cDate.getDate()); if (!classes.isEmpty()) //se pelo menos uma aula ocorreu {// www. ja v a 2s .co m classRegistered = true; studentClassDiscipline.getWeekDays().addDate(cDate); //essa turma/disciplina tem aula no dia da semana de cDate for (Class classs : classes) { this.totalHours += classs.getHoursPerDay(); } } } if (classRegistered) { this.nClassDays++; cell1.setCellValue("X"); } cell2.setCellValue(" " + cDate.getWeekDay()); cell3.setCellValue(new java.util.Date(cDate.getDate().getTime())); style = (XSSFCellStyle) cell1.getCellStyle().clone(); XSSFColor fillBackgroundColorColor = style.getFillBackgroundColorColor(); style.setFillPattern(FillPatternType.SOLID_FOREGROUND); style.setFillForegroundColor(fillBackgroundColorColor); cell1.setCellStyle(style); style = (XSSFCellStyle) cell2.getCellStyle().clone(); style.setFillPattern(FillPatternType.NO_FILL); cell2.setCellStyle(style); style = (XSSFCellStyle) cell3.getCellStyle().clone(); style.setFillPattern(FillPatternType.NO_FILL); cell3.setCellStyle(style); currentColN++;//vai pra prxima coluna } }
From source file:Sheets.PartialSheet.java
/** * Cria as linhas das datas/*from w w w.j a va2 s .c o m*/ * @throws java.sql.SQLException */ @Override protected void createDateRows() throws SQLException { int currentColN = 11; Row row1 = sheet.getRow(10); //row com os "X" dos dias letivos Row row2 = sheet.getRow(12); //row com os dias da semana Row row3 = sheet.getRow(13); //row com as datas XSSFCellStyle style; for (CustomDate cDate : dates) { ArrayList<Class> classes = ClassDAO.getClassesByStudentClassDisciplineAndDate(studentClassDiscipline, cDate.getDate()); XSSFCell cell1 = (XSSFCell) row1.getCell(currentColN); XSSFCell cell2 = (XSSFCell) row2.getCell(currentColN); XSSFCell cell3 = (XSSFCell) row3.getCell(currentColN); if (!classes.isEmpty()) { Class classs = classes.get(0);//pega somente a primeira aula (no deveria existir mais de uma aula de uma displiplina em um mesmo dia) this.totalHours += classs.getHoursPerDay(); this.nClassDays++; cell1.setCellValue("X"); } cell2.setCellValue(" " + cDate.getWeekDay()); cell3.setCellValue(new java.util.Date(cDate.getDate().getTime())); style = (XSSFCellStyle) cell1.getCellStyle().clone(); XSSFColor fillBackgroundColorColor = style.getFillBackgroundColorColor(); style.setFillPattern(FillPatternType.SOLID_FOREGROUND); style.setFillForegroundColor(fillBackgroundColorColor); cell1.setCellStyle(style); style = (XSSFCellStyle) cell2.getCellStyle().clone(); style.setFillPattern(FillPatternType.NO_FILL); cell2.setCellStyle(style); style = (XSSFCellStyle) cell3.getCellStyle().clone(); style.setFillPattern(FillPatternType.NO_FILL); cell3.setCellStyle(style); currentColN++;//vai pra prxima coluna } }
From source file:step.datapool.excel.StyleSyntax.java
License:Open Source License
/** * Analysiert den angegebenen Stil und setzt diesen excel-konform. * //w ww. ja v a 2 s . c o m * @param strStyle strStyle * @param _wb _wb * @return XSSFCellStyle */ public static XSSFCellStyle composeStyle(String strStyle, Workbook _wb) { if (_wb instanceof XSSFWorkbook) { XSSFWorkbook wb = (XSSFWorkbook) _wb; if (strStyle == null || strStyle.isEmpty()) return null; XSSFCellStyle style = wb.createCellStyle(); XSSFFont font = wb.createFont(); strStyle = replaceColors(strStyle); /* Unterteilung der verschiedenen Stilangaben: Schriftschnitt, Vordergrund/Hintergrundfarbe, Schriftgroesse, Schriftsatz */ String[] arr = strStyle.split(","); for (String str : arr) { /* Abhandlung des Schriftschnitts. Diese koennen alle zusammen auftreten und schliessen sich nicht aus */ if (str.matches("( *bold *| *italic *| *underline *| *strikeout *)*")) { if (str.contains("bold")) { font.setBoldweight(Font.BOLDWEIGHT_BOLD); } if (str.contains("italic")) { font.setItalic(true); } if (str.contains("underline")) { font.setUnderline(Font.U_SINGLE); } if (str.contains("strikeout")) { font.setStrikeout(true); } continue; } /* Abhandlung der Farben */ if (str.contains("/") && !str.contains(":")) { continue; } if (str.contains(":")) { String[] fgBg; /* Vordergrund / Hintergrund */ if (str.contains("/")) { fgBg = str.split("/"); } else { /* Verkuerzte Angabe ohne / (nur Vordergrund) */ fgBg = new String[1]; fgBg[0] = str; } for (int i = 0; i < fgBg.length; i++) { fgBg[i] = fgBg[i].trim(); if (!fgBg[i].isEmpty()) { String[] clr = fgBg[i].split(":"); if (clr.length != 3) { return null; } int red = Integer.parseInt(clr[0].trim()); int green = Integer.parseInt(clr[1].trim()); int blue = Integer.parseInt(clr[2].trim()); if (i == 0) { /* Schriftfarbe * ------------------------------------------------------------- * | ACHTUNG: poi hat einen Fehler beim Setzen der Schriftfarbe! | * ------------------------------------------------------------- * Weiss und Schwarz sind verwechselt worden! Gilt NUR fuer * font.setColor! * Deshalb wird hier einfach Weiss auf Schwarz korrigiert und * umgekehrt. */ if (red == 0 && green == 0 && blue == 0) { red = 255; green = 255; blue = 255; } else if (red == 255 && green == 255 && blue == 255) { red = 0; green = 0; blue = 0; } XSSFColor xssfColor = new XSSFColor(new Color(red, green, blue)); font.setColor(xssfColor); } else { // Vordergrund/Hintergrundfarbe der Zelle XSSFColor xssfColor = new XSSFColor(new Color(red, green, blue)); style.setFillForegroundColor(xssfColor); style.setFillBackgroundColor(xssfColor); style.setFillPattern(CellStyle.SOLID_FOREGROUND); } } } continue; } /* Abhandlung der Schriftgroesse */ if (str.matches(" *[1-9][0-9]* *")) { short fontHeightInPoints = Short.parseShort(str.trim()); font.setFontHeightInPoints(fontHeightInPoints); continue; } /* Abhandlung der Schriftart */ if (!str.isEmpty()) { font.setFontName(str); continue; } } style.setFont(font); return style; } else { return null; } }