List of usage examples for org.apache.poi.xssf.usermodel XSSFWorkbook createSheet
@Override
public XSSFSheet createSheet(String sheetname)
From source file:reports.moh731_subSection.java
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { try {// w ww.j av a2 s . c om response.setContentType("text/html;charset=UTF-8"); //a page to get Report of all the servlets String year = "2015"; String month = "4"; String county = "1"; String form = "moh731"; 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 facilitywhere = ""; String countywhere = ""; String districtwhere = ""; String reporttype = ""; 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 ArrayList worksheets = new ArrayList(); //An arralist to store distinct worksheets. This will be derived from the the sections column ArrayList distinctsheets = new ArrayList(); String selectdistinctworksheet = "select distinct(section) from pivottable where form='" + form + "' and active='1'"; conn.rs = conn.st.executeQuery(selectdistinctworksheet); while (conn.rs.next()) { //add the name of distinct sections distinctsheets.add(conn.rs.getString(1).replace("/", "_")); } String getattribs = "select indicator,label,section,cumulative,percentage,active ,shortlabel from pivottable where form='" + form + "' order by tableid, 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 worksheet //if String perfacilselect = "select Upper(County) as County , Upper(DistrictNom) as District , UPPER(SubPartnerNom) as facility ,CentreSanteId as mflcode , "; //-------------------------------------------------------------------------------------------- // 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 += " from " + form + " join ( subpartnera join (district join county on county.CountyID=district.CountyID ) on district.DistrictID = subpartnera.DistrictID ) on " + form + ".SubPartnerID = subpartnera.SubPartnerID "; //------------------------------------------------------------------------------------------ // WHERE //------------------------------------------------------------------------------------------ //----------------------------------------------------------------------------------------- //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 } conn.rs = conn.st.executeQuery(perfacilselect); String sectioncopy = ""; int sheetpos = 0; int rowpos = 2; while (conn.rs.next()) { //-----------------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)){} for (int g = 0; g < distinctsheets.size(); g++) { shet = wb.getSheetAt(g); int colpos = 0; //the fourth cell should XSSFRow rw = shet.createRow(rowpos); for (int e = 0; e < Headerorgunits.size(); e++) { XSSFCell cell0 = rw.createCell(colpos); 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.getString(dbcolumns.get(c).toString())); cell0.setCellStyle(stborder); colpos++; } //end of comparing if } //end of for loop } rowpos++; } 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() + "_REPORT.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.new711report.java
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { try {//from ww w .ja va2s. com 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:rpt.GUI.ProgramStrategist.CyclePlans.CompareDialogController.java
public void okButtonClicked() throws IOException { // create two new maps, one for currentCycleplan and one for the comparison plan Map<String, TableVariant> currentCyclePlan = new HashMap<String, TableVariant>(); Map<String, TableVariant> oldCyclePlan = new HashMap<String, TableVariant>(); Map<String, TableVariant> movedVariants = new HashMap<String, TableVariant>(); Map<String, TableVariant> changedVariants = new HashMap<String, TableVariant>(); Map<String, Map<String, String>> changedInfo = new HashMap<String, Map<String, String>>(); Map<String, String> diffValues = new HashMap<String, String>(); Statement statement;//from w w w . j ava 2s. c o m try { // Set current YYwWW and use to ignore variants that are no longer in production Calendar cal = Calendar.getInstance(); String currentWeek = cal.get(Calendar.YEAR) % 100 + "w" + cal.get(Calendar.WEEK_OF_YEAR); // Extract all variants in the current cycleplan and put them in an map System.out.println("Extracting current variants"); statement = RPT.conn.createStatement(); statement.setQueryTimeout(30); String query = "SELECT * FROM VARIANTS, VariantBelongsToCyclePlan WHERE " + "VariantBelongsToCyclePlan.CyclePlanID= \'" + CyclePlansController.selectedCyclePlan + "\' " + "AND VARIANTS.VariantID = VariantBelongsToCyclePlan.VariantID " + "AND EndOfProd > '" + currentWeek + "'"; ResultSet rs = statement.executeQuery(query); while (rs.next()) { TableVariant entry = new TableVariant(rs.getString("Plant"), rs.getString("Platform"), rs.getString("Vehicle"), rs.getString("Propulsion"), rs.getString("Denomination"), rs.getString("Fuel"), rs.getString("EngineFamily"), rs.getString("Generation"), "EngineName not used", rs.getString("EngineCode"), rs.getString("Displacement"), rs.getString("EnginePower"), rs.getString("ElMotorPower"), rs.getString("Torque"), rs.getString("TorqueOverBoost"), rs.getString("GearboxType"), rs.getString("Gears"), rs.getString("Gearbox"), rs.getString("Driveline"), rs.getString("TransmissionCode"), rs.getString("CertGroup"), rs.getString("EmissionClass"), rs.getString("StartOfProd"), rs.getString("EndOfProd")); currentCyclePlan.put(entry.getVariantID(), entry); } //Now extract all variants in the cycleplan to compare with System.out.println("Extracting comparison variants"); query = "SELECT * FROM VARIANTS, VariantBelongsToCyclePlan WHERE " + "VariantBelongsToCyclePlan.CyclePlanID= \'" + cyclePlanSelector.getSelectionModel().getSelectedItem().toString() + "\' " + "AND VARIANTS.VariantID = VariantBelongsToCyclePlan.VariantID " + "AND EndOfProd > '" + currentWeek + "'"; rs = statement.executeQuery(query); while (rs.next()) { TableVariant entry = new TableVariant(rs.getString("Plant"), rs.getString("Platform"), rs.getString("Vehicle"), rs.getString("Propulsion"), rs.getString("Denomination"), rs.getString("Fuel"), rs.getString("EngineFamily"), rs.getString("Generation"), "EngineName not used", rs.getString("EngineCode"), rs.getString("Displacement"), rs.getString("EnginePower"), rs.getString("ElMotorPower"), rs.getString("Torque"), rs.getString("TorqueOverBoost"), rs.getString("GearboxType"), rs.getString("Gears"), rs.getString("Gearbox"), rs.getString("Driveline"), rs.getString("TransmissionCode"), rs.getString("CertGroup"), rs.getString("EmissionClass"), rs.getString("StartOfProd"), rs.getString("EndOfProd")); oldCyclePlan.put(entry.getVariantID(), entry); } } catch (Exception e) { System.err.println(e.getMessage()); } //for each variant in current plan, remove from both if it exists in old for (Iterator<Map.Entry<String, TableVariant>> entries = currentCyclePlan.entrySet().iterator(); entries .hasNext();) { Map.Entry<String, TableVariant> entry = entries.next(); if (oldCyclePlan.containsKey(entry.getKey())) { entries.remove(); // remove from currentCyclePlan oldCyclePlan.remove(entry.getKey()); } } // Now need to check if some entries were only moved in time for (Iterator<Map.Entry<String, TableVariant>> entries = currentCyclePlan.entrySet().iterator(); entries .hasNext();) { Map.Entry<String, TableVariant> entry = entries.next(); try { statement = RPT.conn.createStatement(); statement.setQueryTimeout(30); //TODO //Add all columns except Start of production, as all will be important to find it correctly String query = "SELECT VARIANTS.VariantID, VARIANTS.StartOfProd, VARIANTS.EndOfProd FROM VARIANTS, VariantBelongsToCyclePlan WHERE " + "VARIANTS.VariantID = VariantBelongsToCyclePlan.VariantID AND " + "VariantBelongsToCyclePlan.CyclePlanID= \'" + cyclePlanSelector.getSelectionModel().getSelectedItem().toString() + "\' AND " + "VARIANTS.Plant = \'" + entry.getValue().getPlant() + "\' AND " + "VARIANTS.Platform = \'" + entry.getValue().getPlatform() + "\' AND " + "VARIANTS.Vehicle = \'" + entry.getValue().getVehicle() + "\' AND " + "VARIANTS.Propulsion = \'" + entry.getValue().getPropulsion() + "\' AND " + "VARIANTS.Denomination = \'" + entry.getValue().getDenomination() + "\' AND " + "VARIANTS.Fuel = \'" + entry.getValue().getFuel() + "\' AND " + "VARIANTS.EngineFamily = \'" + entry.getValue().getEngineFamily() + "\' AND " + "VARIANTS.Generation = \'" + entry.getValue().getGeneration() + "\' AND " + "VARIANTS.EngineCode = \'" + entry.getValue().getEngineCode() + "\' AND " + "VARIANTS.Displacement = \'" + entry.getValue().getDisplacement() + "\' AND " + "VARIANTS.EnginePower = \'" + entry.getValue().getEnginePower() + "\' AND " + "VARIANTS.ElMotorPower = \'" + entry.getValue().getElMotorPower() + "\' AND " + "VARIANTS.TorqueOverBoost = \'" + entry.getValue().getTorqueOverBoost() + "\' AND " + "VARIANTS.GearboxType = \'" + entry.getValue().getGearboxType() + "\' AND " + "VARIANTS.Gears = \'" + entry.getValue().getGears() + "\' AND " + "VARIANTS.Gearbox = \'" + entry.getValue().getGearbox() + "\' AND " + "VARIANTS.Driveline = \'" + entry.getValue().getDriveline() + "\' AND " + "VARIANTS.TransmissionCode = \'" + entry.getValue().getTransmissionCode() + "\' AND " + "VARIANTS.CertGroup = \'" + entry.getValue().getCertGroup() + "\' AND " // may remove once + "VARIANTS.EmissionClass = \'" + entry.getValue().getEmissionClass() + "\'"; ResultSet rs = statement.executeQuery(query); if (rs.next()) { entry.getValue().setOldSOP(rs.getString("StartOfProd")); entry.getValue().setOldEOP(rs.getString("EndOfProd")); movedVariants.put(entry.getKey(), entry.getValue()); //Save variant to moved map entries.remove(); //remove variant from current map oldCyclePlan.remove(rs.getString("VariantID")); //remove variant from old map } } catch (Exception e) { System.err.println(e.getMessage()); } } // Now check for variants that have been slightly changed only. // Show a dialog window allowing the user to define what a minor change is majorChanges = new ArrayList(); Stage stage; Parent root; stage = new Stage(); root = FXMLLoader .load(getClass().getResource("/rpt/GUI/ProgramStrategist/CyclePlans/dialogDefineChanged.fxml")); stage.setScene(new Scene(root)); stage.setTitle("Set change definition"); stage.initModality(Modality.APPLICATION_MODAL); stage.showAndWait(); // pause until the user has selected minor changes // Now loop through the remaining Added items and check if they are to be moved to "Modified" //for (String s : majorChanges) { // System.out.println(s); //} // Create string for extracting data which has been judged as minor //String dataString = ""; // Data which will be used for difference check //for (String s : majorChanges) { // dataString = dataString + ", VARIANTS." + s; //} // Build list of parameters to extract and compare with the new variant ArrayList<String> infoArray = new ArrayList(); String query = "PRAGMA table_info(VARIANTS)"; //Get all column names String extractionData = ""; try { statement = RPT.conn.createStatement(); statement.setQueryTimeout(30); ResultSet rsColumns = statement.executeQuery(query); //traverser through list of columns and add those not pointed out as MAJOR boolean first = true; while (rsColumns.next()) { if (!(majorChanges.contains(rsColumns.getString("name")))) { infoArray.add(rsColumns.getString("name")); if (first) { extractionData = extractionData + "VARIANTS." + rsColumns.getString("name"); first = false; } else { extractionData = extractionData + ", VARIANTS." + rsColumns.getString("name"); } } } } catch (Exception e) { System.err.println("CompareDialogController error when building extraction data: " + e.getMessage()); } for (Iterator<Map.Entry<String, TableVariant>> entries = currentCyclePlan.entrySet().iterator(); entries .hasNext();) { Map.Entry<String, TableVariant> entry = entries.next(); try { statement = RPT.conn.createStatement(); statement.setQueryTimeout(30); query = "SELECT "; query = query + extractionData; query = query + " FROM VARIANTS, VariantBelongsToCyclePlan WHERE " + "VARIANTS.VariantID = VariantBelongsToCyclePlan.VariantID AND " + "VariantBelongsToCyclePlan.CyclePlanID= \'" + cyclePlanSelector.getSelectionModel().getSelectedItem().toString() + "\'"; for (String s : majorChanges) { query = query + " AND VARIANTS." + s + " = \'" + entry.getValue().getValue(s) + "\'"; } //System.out.println(query); ResultSet rs = statement.executeQuery(query); if (rs.next()) { // Found "similar enough" changedVariants.put(entry.getKey(), entry.getValue()); //Save variant to moved map entries.remove(); //remove variant from current map oldCyclePlan.remove(rs.getString("VariantID")); //remove variant from old map // now loop through all non major columns and check for difference between variant in new and old cycle plan diffValues = new HashMap<String, String>(); for (String s : infoArray) { if (!rs.getString(s).equals(entry.getValue().getValue(s))) { diffValues.put(s, rs.getString(s)); } } changedInfo.put(entry.getKey(), diffValues); //Add information about differences between new and old variant } } catch (Exception e) { System.err.println(e.getMessage()); } } // Open file selector and let user specify report file XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet sheet = workbook.createSheet("Information"); //turn off gridlines sheet.setDisplayGridlines(false); sheet.setPrintGridlines(false); sheet.setFitToPage(true); sheet.setHorizontallyCenter(true); PrintSetup printSetup = sheet.getPrintSetup(); //the following three statements are required only for HSSF sheet.setAutobreaks(true); printSetup.setFitHeight((short) 1); printSetup.setFitWidth((short) 1); // print out information about baseline cycle plan Row row = sheet.createRow(0); Cell cell = row.createCell(0); Font headerFont = workbook.createFont(); headerFont.setBold(true); CellStyle style = workbook.createCellStyle(); style.setFont(headerFont); cell.setCellStyle(style); cell.setCellValue("Cycle plan:"); cell = row.createCell(1); cell.setCellValue(CyclePlansController.selectedCyclePlan); // print out information about comaparison cycle plan row = sheet.createRow(1); cell = row.createCell(0); headerFont = workbook.createFont(); headerFont.setBold(true); style.setFont(headerFont); cell.setCellStyle(style); cell.setCellValue("Compared to:"); cell = row.createCell(1); cell.setCellValue(cyclePlanSelector.getSelectionModel().getSelectedItem().toString()); sheet.autoSizeColumn(0); sheet.autoSizeColumn(1); // Write Added variant information sheet = workbook.createSheet("Added"); //freeze the first row sheet.createFreezePane(0, 1); row = sheet.createRow(0); writeHeaders(workbook, row, false); int rowNum = 1; int amountOfColumns = 0; // loop through added for (Iterator<Map.Entry<String, TableVariant>> entries = currentCyclePlan.entrySet().iterator(); entries .hasNext();) { Map.Entry<String, TableVariant> entry = entries.next(); row = sheet.createRow(rowNum); amountOfColumns = writeRow(workbook, sheet, row, entry.getValue(), null, false, false); rowNum++; } //autosize all columns for (int i = 0; i < amountOfColumns; i++) { sheet.autoSizeColumn(i); } amountOfColumns = 0; // Write Removed variant information sheet = workbook.createSheet("Removed"); //freeze the first row sheet.createFreezePane(0, 1); row = sheet.createRow(0); writeHeaders(workbook, row, false); rowNum = 1; // loop through removed for (Iterator<Map.Entry<String, TableVariant>> entries = oldCyclePlan.entrySet().iterator(); entries .hasNext();) { Map.Entry<String, TableVariant> entry = entries.next(); row = sheet.createRow(rowNum); amountOfColumns = writeRow(workbook, sheet, row, entry.getValue(), null, false, false); rowNum++; } //autosize all columns for (int i = 0; i < amountOfColumns; i++) { sheet.autoSizeColumn(i); } amountOfColumns = 0; // Write Changed variant information sheet = workbook.createSheet("Changed"); //freeze the first row sheet.createFreezePane(0, 1); row = sheet.createRow(0); writeHeaders(workbook, row, false); rowNum = 1; // loop through changed for (Iterator<Map.Entry<String, TableVariant>> entries = changedVariants.entrySet().iterator(); entries .hasNext();) { Map.Entry<String, TableVariant> entry = entries.next(); row = sheet.createRow(rowNum); amountOfColumns = writeRow(workbook, sheet, row, entry.getValue(), changedInfo, true, false); rowNum++; } //autosize all columns for (int i = 0; i < amountOfColumns; i++) { sheet.autoSizeColumn(i); } amountOfColumns = 0; // Write Moved variant information sheet = workbook.createSheet("Moved"); //freeze the first row sheet.createFreezePane(0, 1); row = sheet.createRow(0); writeHeaders(workbook, row, true); rowNum = 1; for (Iterator<Map.Entry<String, TableVariant>> entries = movedVariants.entrySet().iterator(); entries .hasNext();) { Map.Entry<String, TableVariant> entry = entries.next(); row = sheet.createRow(rowNum); amountOfColumns = writeRow(workbook, sheet, row, entry.getValue(), null, false, true); rowNum++; } //autosize all columns for (int i = 0; i < amountOfColumns; i++) { sheet.autoSizeColumn(i); } amountOfColumns = 0; FileChooser fileChooser = new FileChooser(); fileChooser.setTitle("Save Comparison Result File"); File selectedFile = fileChooser.showSaveDialog(null); if (selectedFile != null) { try { FileOutputStream out = new FileOutputStream(selectedFile); workbook.write(out); out.close(); System.out.println("Excel written successfully.."); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } } closeDialog(); }
From source file:se.inera.intyg.rehabstod.service.export.xlsx.XlsxExportServiceImpl.java
License:Open Source License
@Override public byte[] export(List<SjukfallEnhet> sjukfallList, PrintSjukfallRequest req, RehabstodUser user, int total) throws IOException { headers = initHeaders(req, user.getUrval(), isSrsFeatureActive(user)); XSSFWorkbook wb = new XSSFWorkbook(); setupFonts(wb);//w w w . j a va 2 s. co m XSSFSheet sheet = wb.createSheet(SHEET_TITLE_SJUKFALL); int rowNumber = 0; // CHECKSTYLE:OFF MagicNumber // Start with 2 empty rows to make space for filter for (; rowNumber < 2; rowNumber++) { sheet.createRow(rowNumber); } addFilterMainHeader(sheet, rowNumber++, VALDA_FILTER); addFilterHeader(sheet, rowNumber++, FILTER_TITLE_FRITEXTFILTER, notEmpty(req) ? req.getFritext() : "-"); addFilterHeader(sheet, rowNumber++, FILTER_TITLE_VISAPATIENTUPPGIFTER, req.isShowPatientId() ? " Ja" : " Nej"); addFilterHeader(sheet, rowNumber++, FILTER_TITLE_VALD_ALDER, req.getAldersIntervall().getMin() + " - " + req.getAldersIntervall().getMax() + " r"); rowNumber = addDiagnosKapitel(sheet, rowNumber++, FILTER_TITLE_VALDA_DIAGNOSER, req.getDiagnosGrupper()); // NOSONAR addFilterHeader(sheet, rowNumber++, FILTER_TITLE_VALD_SLUTDATUM, getFilterDate(req.getSlutdatumIntervall())); addFilterHeader(sheet, rowNumber++, FILTER_TITLE_VALD_SJUKSKRIVNINGSLANGD, req.getLangdIntervall().getMin() + " - " + req.getLangdIntervall().getMax() + " dagar"); rowNumber = addLakareList(sheet, rowNumber++, FILTER_TITLE_VALDA_LAKARE, req.getLakare(), user); // NOSONAR // Instllningar addFilterMainHeader(sheet, rowNumber++, H2_SJUKFALLSINSTALLNING); addFilterHeader(sheet, rowNumber++, MAXANTAL_DAGAR_UPPEHALL_MELLAN_INTYG, req.getMaxIntygsGlapp() + " dagar"); rowNumber += FILTER_SPACING; // Sortering addFilterMainHeader(sheet, rowNumber++, VALD_SORTERING_PA_TABELLEN); addFilterHeader(sheet, rowNumber++, SORTERING_KOLUMN, req.getSortering().getKolumn()); addFilterHeader(sheet, rowNumber++, SORTERING_RIKTNING, req.getSortering().getOrder()); rowNumber += FILTER_SPACING; addFilterMainHeader(sheet, rowNumber++, ANTAL_VISAR_ANTAL_PAGAENDE_SJUKFALL); addFilterHeader(sheet, rowNumber++, ANTAL_EXPORTEN_VISAR, String.valueOf(sjukfallList.size())); addFilterHeader(sheet, rowNumber++, user.getUrval() == Urval.ISSUED_BY_ME ? ANTAL_TOTALT_MINA : ANTAL_TOTALT_PA_ENHETEN, String.valueOf(total)); rowNumber += 3; addTableHeaderRows(sheet, rowNumber++); addDataRows(sheet, rowNumber, sjukfallList, user.getUrval(), req.isShowPatientId(), isSrsFeatureActive(user)); ByteArrayOutputStream baos = new ByteArrayOutputStream(); wb.write(baos); return baos.toByteArray(); }
From source file:se.vgregion.alfresco.repo.scripts.ExportJsonToExcel.java
public File csvToXLSX(File csv) { File xlsx = new File(filename + ".xlsx"); try {//from ww w .jav a 2 s. com XSSFWorkbook workBook = new XSSFWorkbook(); XSSFSheet sheet = workBook.createSheet("sheet1"); String currentLine = null; int RowNum = 0; BufferedReader br = new BufferedReader(new FileReader(csv)); while ((currentLine = br.readLine()) != null) { String str[] = currentLine.split(";"); RowNum++; XSSFRow currentRow = sheet.createRow(RowNum); for (int i = 0; i < str.length; i++) { currentRow.createCell(i).setCellValue(str[i]); } } FileOutputStream fileOutputStream = new FileOutputStream(xlsx); workBook.write(fileOutputStream); fileOutputStream.close(); } catch (Exception ex) { System.out.println(ex.getMessage() + "Exception in try"); } return xlsx; }
From source file:Servelt.ExcelWriter.java
private XSSFSheet createSheet(XSSFWorkbook workbook, ComponentSet comSet) { XSSFSheet sheet = workbook.createSheet(comSet.getComponentName()); XSSFRow row = sheet.createRow(0);/*from w w w. ja v a 2 s. com*/ mapToCell(row, "main", comSet.getMainColumn()); mapToCell(row, "attr", comSet.getForeignColumn()); mapToCell(row, "des", comSet.getGeneraColumn()); return sheet; }
From source file:sistemas.Utils.java
public static void writeXLSXFile(JTable table, String path) throws IOException { TableModel model = table.getModel(); //Table model String excelFileName = path;//name of excel file String sheetName = "Sheet1";//name of sheet XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet sheet = wb.createSheet(sheetName); XSSFRow rowH = sheet.createRow(0);/*w ww .j a v a2s . co m*/ for (int headings = 0; headings < model.getColumnCount(); headings++) { //For each column rowH.createCell(headings).setCellValue(model.getColumnName(headings));//Write column name } //iterating r number of rows for (int r = 1; r <= model.getRowCount(); r++) { XSSFRow row = sheet.createRow(r); //iterating c number of columns for (int c = 0; c < model.getColumnCount(); c++) { XSSFCell cell = row.createCell(c); cell.setCellValue("" + model.getValueAt(r - 1, c)); } } FileOutputStream fileOut = new FileOutputStream(excelFileName); //write this workbook to an Outputstream. wb.write(fileOut); fileOut.flush(); fileOut.close(); }
From source file:testpoi.FlatFilesInFolderToExcel.java
License:Open Source License
public static void main(String args[]) { XSSFWorkbook workbook = new XSSFWorkbook(); sheet = workbook.createSheet("Read files"); //Create a new row in current sheet Row row = sheet.createRow(0);/*from ww w . jav a2 s .c o m*/ //Create a new cell in current row Cell cell = row.createCell(0); //Set value to new value cell.setCellValue("File Contents"); rowCount = 1; processAllFiles(folder); try { FileOutputStream out = new FileOutputStream(new File("D:\\new.xlsx")); workbook.write(out); out.close(); System.out.println("Excel written successfully.."); } catch (Exception e) { e.printStackTrace(); } }
From source file:testpoi.GenerateDailyExcel.java
License:Open Source License
public static void main(String args[]) { //For Reading FileInputStream file1 = null, file2 = null; try {/*from w w w . ja v a 2 s. c om*/ file1 = new FileInputStream(new File(path + "all.xlsx")); XSSFWorkbook workbook1 = new XSSFWorkbook(file1); //Get first sheet from the workbook1 sheetAll = workbook1.getSheetAt(0); //Get second sheet from the workbook1 sheetFemale = workbook1.getSheetAt(1); file2 = new FileInputStream(new File(path + "children.xlsx")); XSSFWorkbook workbook2 = new XSSFWorkbook(file2); //Get first sheet from the workbook2 sheetChildren = workbook2.getSheetAt(0); } catch (Exception e) { System.err.println("Error opening files for reading."); e.printStackTrace(); } //For writing XSSFWorkbook workbook = new XSSFWorkbook(); sheetNew = workbook.createSheet("Generated File - Do not edit"); //Create a new row in current sheet for heading. Row row = sheetNew.createRow(0); //Create a new cell in current row Cell cell = row.createCell(0); //Set value to new value cell.setCellValue("Department"); cell = row.createCell(1); cell.setCellValue("Name"); cell = row.createCell(2); cell.setCellValue("Guardian's Name"); cell = row.createCell(3); cell.setCellValue("Relation"); cell = row.createCell(4); cell.setCellValue("AgeYrs"); cell = row.createCell(5); cell.setCellValue("Gender"); cell = row.createCell(6); cell.setCellValue("Address"); cell = row.createCell(7); cell.setCellValue("City"); cell = row.createCell(8); cell.setCellValue("State"); rowCnt = 1; deptts = new ArrayList<>(); deptts.add(new Department("Medicine", 118, true)); deptts.add(new Department("Surgery", 89, true)); deptts.add(new Department("Obs & Gynae", 67, true)); deptts.add(new Department("Paediatrics", 38, true)); deptts.add(new Department("Orthopaedics", 54, true)); deptts.add(new Department("Ophthalmology", 33, true)); deptts.add(new Department("ENT", 28, true)); deptts.add(new Department("Dental", 27, true)); deptts.add(new Department("Casualty", 11, true)); generateRows(); try { // FileOutputStream out = new FileOutputStream(new File(path+"\\"+date+".xlsx")); FileOutputStream out = new FileOutputStream(new File(path + date + ".xlsx")); workbook.write(out); out.close(); if (file1 != null) file1.close(); if (file2 != null) file2.close(); System.out.println("Excel written successfully.."); } catch (Exception e) { e.printStackTrace(); } }
From source file:testpoi.GenerateDailyExcelPickingRowsSequentially.java
License:Open Source License
public static void main(String args[]) { //For Reading FileInputStream file1 = null, file2 = null; try {/*from w w w.j ava 2 s.com*/ file1 = new FileInputStream(new File(path + "all.xlsx")); XSSFWorkbook workbook1 = new XSSFWorkbook(file1); //Get first sheet from the workbook1 sheetAll = workbook1.getSheetAt(0); //Get second sheet from the workbook1 sheetFemale = workbook1.getSheetAt(1); file2 = new FileInputStream(new File(path + "children.xlsx")); XSSFWorkbook workbook2 = new XSSFWorkbook(file2); //Get first sheet from the workbook2 sheetChildren = workbook2.getSheetAt(0); } catch (Exception e) { System.err.println("Error opening files for reading."); e.printStackTrace(); } //For writing XSSFWorkbook workbook = new XSSFWorkbook(); sheetNew = workbook.createSheet("Generated File - Do not edit"); //Create a new row in current sheet for heading. Row row = sheetNew.createRow(0); //Create a new cell in current row Cell cell = row.createCell(0); //Set value to new value cell.setCellValue("Department"); cell = row.createCell(1); cell.setCellValue("Name"); cell = row.createCell(2); cell.setCellValue("Guardian's Name"); cell = row.createCell(3); cell.setCellValue("Relation"); cell = row.createCell(4); cell.setCellValue("AgeYrs"); cell = row.createCell(5); cell.setCellValue("Gender"); cell = row.createCell(6); cell.setCellValue("Address"); cell = row.createCell(7); cell.setCellValue("City"); cell = row.createCell(8); cell.setCellValue("State"); rowCnt = 1; femaleRowNum = 1; childRowNum = 1; allRowNum = 1; deptts = new ArrayList<>(); deptts.add(new Department("Medicine", 118, true)); deptts.add(new Department("Surgery", 89, true)); deptts.add(new Department("Obs & Gynae", 67, true)); deptts.add(new Department("Paediatrics", 38, true)); deptts.add(new Department("Orthopaedics", 54, true)); deptts.add(new Department("Ophthalmology", 33, true)); deptts.add(new Department("ENT", 28, true)); deptts.add(new Department("Dental", 27, true)); deptts.add(new Department("Casualty", 11, true)); generateRows(); try { // FileOutputStream out = new FileOutputStream(new File(path+"\\"+date+".xlsx")); FileOutputStream out = new FileOutputStream(new File(path + date + ".xlsx")); workbook.write(out); out.close(); if (file1 != null) file1.close(); if (file2 != null) file2.close(); System.out.println("Excel written successfully.."); } catch (Exception e) { e.printStackTrace(); } }