List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet createFreezePane
@Override public void createFreezePane(int colSplit, int rowSplit, int leftmostColumn, int topRow)
From source file:com.sevenorcas.openstyle.app.service.spreadsheet.SpreadSheet.java
/** * Freeze pane (if configured)<p>/*w w w. java 2 s.c o m*/ * * Thanks to https://poi.apache.org/spreadsheet/quick-guide.html#Splits * * @param sheet */ public void createFreezePane(HSSFSheet sheet) { if (freezePane1 != null) { sheet.createFreezePane(freezePane1, freezePane2, freezePane3, freezePane4); //sheet.createSplitPane(2000,2000,0,0,Sheet.PANE_LOWER_LEFT); } }
From source file:gov.nih.nci.caintegrator.application.report.LevelOfExpressionIHCReport.java
License:BSD License
public static HSSFWorkbook getReportExcel(Finding finding, HashMap map) { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet(finding.getTaskId()); ArrayList dFindings = new ArrayList(finding.getDomainFindings()); ArrayList<LevelOfExpressionIHCFinding> domainFindings = new ArrayList<LevelOfExpressionIHCFinding>( dFindings);/*from ww w . ja va 2 s . c om*/ ArrayList<LevelOfExpressionIHCFindingReportBean> results = new ArrayList<LevelOfExpressionIHCFindingReportBean>(); for (LevelOfExpressionIHCFinding loef : domainFindings) { LevelOfExpressionIHCFindingReportBean reportBean = new LevelOfExpressionIHCFindingReportBean(loef); results.add(reportBean); } LevelOfExpressionIHCFindingCriteria criteria = (LevelOfExpressionIHCFindingCriteria) finding.getQueryDTO(); if (!results.isEmpty()) { //SORT THE ARRAYLIST(RESULTS) BY PATIENT DID PatientComparator p = new PatientComparator(); Collections.sort(results, p); //CREATE A HASHMAP SORTED BY PATIENT DID AS THE KEY AND THE ARRAYLIST OF REPORTBEANS AS THE VALUE Map<String, ArrayList<LevelOfExpressionIHCFindingReportBean>> reportBeanMap = new HashMap<String, ArrayList<LevelOfExpressionIHCFindingReportBean>>(); for (int i = 0; i < results.size(); i++) { if (i == 0) { reportBeanMap.put( results.get(i).getSpecimenIdentifier() + "_" + results.get(i).getBiomarkerName(), new ArrayList<LevelOfExpressionIHCFindingReportBean>()); reportBeanMap .get(results.get(i).getSpecimenIdentifier() + "_" + results.get(i).getBiomarkerName()) .add(results.get(i)); } else if (!results.get(i).getSpecimenIdentifier() .equalsIgnoreCase(results.get(i - 1).getSpecimenIdentifier())) { reportBeanMap.put( results.get(i).getSpecimenIdentifier() + "_" + results.get(i).getBiomarkerName(), new ArrayList<LevelOfExpressionIHCFindingReportBean>()); reportBeanMap .get(results.get(i).getSpecimenIdentifier() + "_" + results.get(i).getBiomarkerName()) .add(results.get(i)); } else if (results.get(i).getSpecimenIdentifier() .equalsIgnoreCase(results.get(i - 1).getSpecimenIdentifier()) && !results.get(i).getBiomarkerName() .equalsIgnoreCase(results.get(i - 1).getBiomarkerName())) { reportBeanMap.put( results.get(i).getSpecimenIdentifier() + "_" + results.get(i).getBiomarkerName(), new ArrayList<LevelOfExpressionIHCFindingReportBean>()); reportBeanMap .get(results.get(i).getSpecimenIdentifier() + "_" + results.get(i).getBiomarkerName()) .add(results.get(i)); } else { reportBeanMap.get(results.get(i - 1).getSpecimenIdentifier() + "_" + results.get(i - 1).getBiomarkerName()).add(results.get(i)); } } //IF THE USER SELECTED TIMEPOINTS FOR WHICH THAT PATIENT DID DID NOT HAVE DATA, CREATE NULL BEANS SO AS TO RENDER A READABLE REPORT Set<String> b = reportBeanMap.keySet(); for (String g : b) { while (reportBeanMap.get(g) .size() < (reportBeanMap.get(g).get(0).getTimepointHeaders(criteria).size())) { reportBeanMap.get(g) .add(new LevelOfExpressionIHCFindingReportBean(new LevelOfExpressionIHCFinding())); } } ArrayList<String> ntpHeaders = results.get(0).getNonTimepointHeaders(); HSSFRow row = sheet.createRow((short) 0); //ADD HEADERS THAT ARE TIMEPOINT DEPENDENT ArrayList<String> headers = results.get(0).getHeaders(); ArrayList<String> tpHeaders = results.get(0).getTimepointHeaders(criteria); TimepointStringComparator ts = new TimepointStringComparator(); Collections.sort(tpHeaders, ts); ArrayList<String> combinedHeaders = new ArrayList<String>(); for (int i = 0; i < headers.size(); i++) { for (int j = 0; j < tpHeaders.size(); j++) { combinedHeaders.add(headers.get(i) + tpHeaders.get(j)); } } ntpHeaders.addAll(combinedHeaders); for (int i = 0; i < ntpHeaders.size(); i++) { HSSFCell cell = row.createCell((short) i); cell.setCellValue(ntpHeaders.get(i)); } row = null; HSSFCell dataCell = null; Set<String> keysSet = reportBeanMap.keySet(); ArrayList<String> keys = new ArrayList<String>(keysSet); int u = 0; // ADD DATA ROWS for (int i = 0; i < keys.size(); i++) { String tp = reportBeanMap.get(keys.get(i)).get(0).getTimepoint(); Collection<String> localizationCollection = criteria.getStainLocalizationCollection(); Collection<String> intensityCollection = criteria.getStainIntensityCollection(); String localization = reportBeanMap.get(keys.get(i)).get(0).getStainLocalization(); String intensity = reportBeanMap.get(keys.get(i)).get(0).getStainIntensity(); if (tpHeaders.contains(tp) && (localizationCollection == null || (localizationCollection != null && localizationCollection.contains(localization))) && (intensityCollection == null || (intensityCollection != null && intensityCollection.contains(intensity)))) { sheet.createFreezePane(0, 1, 0, 1); row = sheet.createRow((short) u + 1); dataCell = row.createCell((short) 0); dataCell.setCellValue(reportBeanMap.get(keys.get(i)).get(0).getPatientDID()); dataCell = row.createCell((short) 1); dataCell.setCellValue(reportBeanMap.get(keys.get(i)).get(0).getBiomarkerName()); //GRAB EACH REPORT BEAN IN EACH ARRAYLIST AND MATCH UP TO THE APPROPRIATE TIMEPOINT AS A MAP WITH THE TIMEPOINT AS KEY AND REPORTBEAN THE VALUE ArrayList<LevelOfExpressionIHCFindingReportBean> myList = reportBeanMap.get(keys.get(i)); Map<String, LevelOfExpressionIHCFindingReportBean> myMap = new HashMap<String, LevelOfExpressionIHCFindingReportBean>(); ArrayList<LevelOfExpressionIHCFindingReportBean> mySortedMap = new ArrayList<LevelOfExpressionIHCFindingReportBean>(); for (LevelOfExpressionIHCFindingReportBean ggg : myList) { for (int j = 0; j < tpHeaders.size(); j++) { if (ggg.getTimepoint().equalsIgnoreCase(tpHeaders.get(j))) { myMap.put(tpHeaders.get(j), ggg); break; } else if (ggg.getTimepoint().equals("--")) { if (!myMap.containsKey(tpHeaders.get(j))) { myMap.put(tpHeaders.get(j), ggg); break; } } } } //SORT MAP BY TIMEPOINT SO THAT THE REPORT BEAN DATA CAN EASILY BE DISPLAYED UNDER THE APPROPRIATE TIEMPOINT for (int t = 0; t < tpHeaders.size(); t++) { for (String k : myMap.keySet()) { if (k.equalsIgnoreCase(tpHeaders.get(t))) { mySortedMap.add(myMap.get(k)); } } } int counter = 2; //ITERATE OVER THE MAP FOR EACH DATA FIELD WITH ITS CORRESPONDING TIMEPOINT AND BUILD DATA ROWS for (LevelOfExpressionIHCFindingReportBean reportBean : mySortedMap) { dataCell = row.createCell((short) counter++); dataCell.setCellValue(reportBean.getPercentPositive()); } for (LevelOfExpressionIHCFindingReportBean reportBean : mySortedMap) { dataCell = row.createCell((short) counter++); dataCell.setCellValue(reportBean.getStainIntensity()); } for (LevelOfExpressionIHCFindingReportBean reportBean : mySortedMap) { dataCell = row.createCell((short) counter++); dataCell.setCellValue(reportBean.getStainLocalization()); } for (LevelOfExpressionIHCFindingReportBean reportBean : mySortedMap) { dataCell = row.createCell((short) counter++); dataCell.setCellValue(reportBean.getInvasivePresentation()); } for (LevelOfExpressionIHCFindingReportBean reportBean : mySortedMap) { dataCell = row.createCell((short) counter++); dataCell.setCellValue(reportBean.getOverallExpression()); } u++; } } } else { //TODO: handle this error } return wb; }
From source file:gov.nih.nci.caintegrator.application.report.LossOfExpressionIHCReport.java
License:BSD License
public static HSSFWorkbook getReportExcel(Finding finding, HashMap map) { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet(finding.getTaskId()); ArrayList dFindings = new ArrayList(finding.getDomainFindings()); ArrayList<LossOfExpressionIHCFinding> domainFindings = new ArrayList<LossOfExpressionIHCFinding>(dFindings); ArrayList<LossOfExpressionIHCFindingReportBean> results = new ArrayList<LossOfExpressionIHCFindingReportBean>(); for (LossOfExpressionIHCFinding loef : domainFindings) { LossOfExpressionIHCFindingReportBean reportBean = new LossOfExpressionIHCFindingReportBean(loef); results.add(reportBean);//from w w w . j ava 2 s . com } LossOfExpressionIHCFindingCriteria criteria = (LossOfExpressionIHCFindingCriteria) finding.getQueryDTO(); if (!results.isEmpty()) { //SORT THE ARRAYLIST(RESULTS) BY PATIENT DID PatientComparator p = new PatientComparator(); Collections.sort(results, p); //CREATE A HASHMAP SORTED BY PATIENT DID AS THE KEY AND THE ARRAYLIST OF REPORTBEANS AS THE VALUE Map<String, ArrayList<LossOfExpressionIHCFindingReportBean>> reportBeanMap = new HashMap<String, ArrayList<LossOfExpressionIHCFindingReportBean>>(); for (int i = 0; i < results.size(); i++) { if (i == 0) { reportBeanMap.put( results.get(i).getSpecimenIdentifier() + "_" + results.get(i).getBiomarkerName(), new ArrayList<LossOfExpressionIHCFindingReportBean>()); reportBeanMap .get(results.get(i).getSpecimenIdentifier() + "_" + results.get(i).getBiomarkerName()) .add(results.get(i)); } else if (!results.get(i).getSpecimenIdentifier() .equalsIgnoreCase(results.get(i - 1).getSpecimenIdentifier())) { reportBeanMap.put( results.get(i).getSpecimenIdentifier() + "_" + results.get(i).getBiomarkerName(), new ArrayList<LossOfExpressionIHCFindingReportBean>()); reportBeanMap .get(results.get(i).getSpecimenIdentifier() + "_" + results.get(i).getBiomarkerName()) .add(results.get(i)); } else if (results.get(i).getSpecimenIdentifier() .equalsIgnoreCase(results.get(i - 1).getSpecimenIdentifier()) && !results.get(i).getBiomarkerName() .equalsIgnoreCase(results.get(i - 1).getBiomarkerName())) { reportBeanMap.put( results.get(i).getSpecimenIdentifier() + "_" + results.get(i).getBiomarkerName(), new ArrayList<LossOfExpressionIHCFindingReportBean>()); reportBeanMap .get(results.get(i).getSpecimenIdentifier() + "_" + results.get(i).getBiomarkerName()) .add(results.get(i)); } else { reportBeanMap.get(results.get(i - 1).getSpecimenIdentifier() + "_" + results.get(i - 1).getBiomarkerName()).add(results.get(i)); } } //IF THE USER SELECTED TIMEPOINTS FOR WHICH THAT PATIENT DID DID NOT HAVE DATA, CREATE NULL BEANS SO AS TO RENDER A READABLE REPORT Set<String> b = reportBeanMap.keySet(); for (String g : b) { while (reportBeanMap.get(g) .size() < (reportBeanMap.get(g).get(0).getTimepointHeaders(criteria).size())) { reportBeanMap.get(g) .add(new LossOfExpressionIHCFindingReportBean(new LossOfExpressionIHCFinding())); } } ArrayList<String> ntpHeaders = results.get(0).getNonTimepointHeaders(); HSSFRow row = sheet.createRow((short) 0); //ADD HEADERS THAT ARE TIMEPOINT DEPENDENT ArrayList<String> headers = results.get(0).getHeaders(); ArrayList<String> tpHeaders = results.get(0).getTimepointHeaders(criteria); TimepointStringComparator ts = new TimepointStringComparator(); Collections.sort(tpHeaders, ts); ArrayList<String> combinedHeaders = new ArrayList<String>(); for (int i = 0; i < headers.size(); i++) { for (int j = 0; j < tpHeaders.size(); j++) { combinedHeaders.add(headers.get(i) + tpHeaders.get(j)); } } ntpHeaders.addAll(combinedHeaders); for (int i = 0; i < ntpHeaders.size(); i++) { HSSFCell cell = row.createCell((short) i); cell.setCellValue(ntpHeaders.get(i)); } row = null; HSSFCell dataCell = null; Set<String> keysSet = reportBeanMap.keySet(); ArrayList<String> keys = new ArrayList<String>(keysSet); int u = 0; // ADD DATA ROWS for (int i = 0; i < keys.size(); i++) { String tp = reportBeanMap.get(keys.get(i)).get(0).getTimepoint(); Integer invasiveSumCrit = criteria.getInvasiveSum(); String invasiveSumOpCrit = criteria.getInvasiveSumOperator(); String invasiveSum = reportBeanMap.get(keys.get(i)).get(0).getInvasiveSum(); String invasiveSumOp = reportBeanMap.get(keys.get(i)).get(0).getInvasiveSumOperator(); int q = 0; if (invasiveSumCrit != null && invasiveSumOpCrit != null) { q = new Integer(invasiveSum).compareTo(invasiveSumCrit); } Integer benignSumCrit = criteria.getBenignSum(); String benignSumOpCrit = criteria.getBenignSumOperator(); String benignSum = reportBeanMap.get(keys.get(i)).get(0).getBenignSum(); String benignSumOp = reportBeanMap.get(keys.get(i)).get(0).getBenignSumOperator(); int h = 0; if (benignSumCrit != null && benignSumOpCrit != null) { h = new Integer(benignSum).compareTo(benignSumCrit); } Collection<String> resultCodeCollection = criteria.getResultCodeCollection(); String resultCode = reportBeanMap.get(keys.get(i)).get(0).getLossResult(); // if(tpHeaders.contains(tp)) { if (tpHeaders.contains(tp) && (resultCodeCollection == null || (resultCodeCollection != null && resultCodeCollection.contains(resultCode))) && ((invasiveSumCrit == null && invasiveSumOpCrit == null) || (invasiveSumCrit != null && (invasiveSumOpCrit != null && invasiveSumOpCrit.equals("=")) && invasiveSumCrit.toString().equals(invasiveSum)) || (invasiveSumCrit != null && (invasiveSumOpCrit != null && invasiveSumOpCrit.equals(">=")) && q >= 0) || (invasiveSumCrit != null && (invasiveSumOpCrit != null && invasiveSumOpCrit.equals("<=")) && q <= 0)) && ((benignSumCrit == null && benignSumOpCrit == null) || (benignSumCrit != null && (benignSumOpCrit != null && benignSumOpCrit.equals("=")) && benignSumCrit.toString().equals(benignSum)) || (benignSumCrit != null && (benignSumOpCrit != null && benignSumOpCrit.equals(">=")) && h >= 0) || (benignSumCrit != null && (benignSumOpCrit != null && benignSumOpCrit.equals("<=")) && h <= 0))) { sheet.createFreezePane(0, 1, 0, 1); row = sheet.createRow((short) u + 1); dataCell = row.createCell((short) 0); dataCell.setCellValue(reportBeanMap.get(keys.get(i)).get(0).getPatientDID()); dataCell = row.createCell((short) 1); dataCell.setCellValue(reportBeanMap.get(keys.get(i)).get(0).getBiomarkerName()); //GRAB EACH REPORT BEAN IN EACH ARRAYLIST AND MATCH UP TO THE APPROPRIATE TIMEPOINT AS A MAP WITH THE TIMEPOINT AS KEY AND REPORTBEAN THE VALUE ArrayList<LossOfExpressionIHCFindingReportBean> myList = reportBeanMap.get(keys.get(i)); Map<String, LossOfExpressionIHCFindingReportBean> myMap = new HashMap<String, LossOfExpressionIHCFindingReportBean>(); ArrayList<LossOfExpressionIHCFindingReportBean> mySortedMap = new ArrayList<LossOfExpressionIHCFindingReportBean>(); for (LossOfExpressionIHCFindingReportBean ggg : myList) { for (int j = 0; j < tpHeaders.size(); j++) { if (ggg.getTimepoint().equalsIgnoreCase(tpHeaders.get(j))) { myMap.put(tpHeaders.get(j), ggg); break; } else if (ggg.getTimepoint().equals("--")) { if (!myMap.containsKey(tpHeaders.get(j))) { myMap.put(tpHeaders.get(j), ggg); break; } } } } //SORT MAP BY TIMEPOINT SO THAT THE REPORT BEAN DATA CAN EASILY BE DISPLAYED UNDER THE APPROPRIATE TIEMPOINT for (int t = 0; t < tpHeaders.size(); t++) { for (String k : myMap.keySet()) { if (k.equalsIgnoreCase(tpHeaders.get(t))) { mySortedMap.add(myMap.get(k)); } } } int counter = 2; //ITERATE OVER THE MAP FOR EACH DATA FIELD WITH ITS CORRESPONDING TIMEPOINT AND BUILD DATA ROWS for (LossOfExpressionIHCFindingReportBean reportBean : mySortedMap) { dataCell = row.createCell((short) counter++); dataCell.setCellValue(reportBean.getBenignPresentValue()); } for (LossOfExpressionIHCFindingReportBean reportBean : mySortedMap) { dataCell = row.createCell((short) counter++); if (!reportBean.getInvasiveSum().equals("--")) { dataCell.setCellValue(Short.parseShort(reportBean.getInvasiveSum())); } else { dataCell.setCellValue(reportBean.getInvasiveSum()); } } for (LossOfExpressionIHCFindingReportBean reportBean : mySortedMap) { dataCell = row.createCell((short) counter++); if (!reportBean.getBenignSum().equals("--")) { dataCell.setCellValue(Short.parseShort(reportBean.getBenignSum())); } else { dataCell.setCellValue(reportBean.getBenignSum()); } } for (LossOfExpressionIHCFindingReportBean reportBean : mySortedMap) { dataCell = row.createCell((short) counter++); if (!reportBean.getInvasiveBenignDiff().equals("--")) { dataCell.setCellValue(Short.parseShort(reportBean.getInvasiveBenignDiff())); } else { dataCell.setCellValue(reportBean.getInvasiveBenignDiff()); } } for (LossOfExpressionIHCFindingReportBean reportBean : mySortedMap) { dataCell = row.createCell((short) counter++); dataCell.setCellValue(reportBean.getComments()); } for (LossOfExpressionIHCFindingReportBean reportBean : mySortedMap) { dataCell = row.createCell((short) counter++); dataCell.setCellValue(reportBean.getLossResult()); } u++; } } } else { //TODO: handle this error } return wb; }
From source file:gov.nih.nci.caintegrator.application.report.P53Report.java
License:BSD License
public static HSSFWorkbook getReportExcel(Finding finding, HashMap map) { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet(finding.getTaskId()); ArrayList dFindings = new ArrayList(finding.getDomainFindings()); ArrayList<P53MutationFinding> domainFindings = new ArrayList<P53MutationFinding>(dFindings); ArrayList<P53FindingReportBean> results = new ArrayList<P53FindingReportBean>(); for (P53MutationFinding p53f : domainFindings) { P53FindingReportBean reportBean = new P53FindingReportBean(p53f); results.add(reportBean);/*from w w w.j a va2s . c o m*/ } P53FindingCriteria criteria = (P53FindingCriteria) finding.getQueryDTO(); if (!results.isEmpty()) { //SORT THE ARRAYLIST(RESULTS) BY PATIENT DID PatientComparator p = new PatientComparator(); Collections.sort(results, p); //CREATE A HASHMAP SORTED BY PATIENT DID AS THE KEY AND THE ARRAYLIST OF REPORTBEANS AS THE VALUE Map<String, ArrayList<P53FindingReportBean>> reportBeanMap = new HashMap<String, ArrayList<P53FindingReportBean>>(); for (int i = 0; i < results.size(); i++) { if (i == 0) { reportBeanMap.put(results.get(i).getSpecimenIdentifier(), new ArrayList<P53FindingReportBean>()); reportBeanMap.get(results.get(i).getSpecimenIdentifier()).add(results.get(i)); } else if (!results.get(i).getSpecimenIdentifier() .equalsIgnoreCase(results.get(i - 1).getSpecimenIdentifier())) { reportBeanMap.put(results.get(i).getSpecimenIdentifier(), new ArrayList<P53FindingReportBean>()); reportBeanMap.get(results.get(i).getSpecimenIdentifier()).add(results.get(i)); } else if (results.get(i).getSpecimenIdentifier() .equalsIgnoreCase(results.get(i - 1).getSpecimenIdentifier())) { reportBeanMap.put(results.get(i).getSpecimenIdentifier(), new ArrayList<P53FindingReportBean>()); reportBeanMap.get(results.get(i).getSpecimenIdentifier()).add(results.get(i)); } else { reportBeanMap.get(results.get(i - 1).getSpecimenIdentifier()).add(results.get(i)); } } //IF THE USER SELECTED TIMEPOINTS FOR WHICH THAT PATIENT DID DID NOT HAVE DATA, CREATE NULL BEANS SO AS TO RENDER A READABLE REPORT Set<String> b = reportBeanMap.keySet(); for (String g : b) { while (reportBeanMap.get(g) .size() < (reportBeanMap.get(g).get(0).getTimepointHeaders(criteria).size())) { reportBeanMap.get(g).add(new P53FindingReportBean(new P53MutationFinding())); } } ArrayList<String> ntpHeaders = results.get(0).getNonTimepointHeaders(); HSSFRow row = sheet.createRow((short) 0); //ADD HEADERS THAT ARE TIMEPOINT DEPENDENT ArrayList<String> headers = results.get(0).getHeaders(); ArrayList<String> tpHeaders = results.get(0).getTimepointHeaders(criteria); TimepointStringComparator ts = new TimepointStringComparator(); Collections.sort(tpHeaders, ts); ArrayList<String> combinedHeaders = new ArrayList<String>(); for (int i = 0; i < headers.size(); i++) { for (int j = 0; j < tpHeaders.size(); j++) { combinedHeaders.add(headers.get(i) + tpHeaders.get(j)); } } ntpHeaders.addAll(combinedHeaders); for (int i = 0; i < ntpHeaders.size(); i++) { HSSFCell cell = row.createCell((short) i); cell.setCellValue(ntpHeaders.get(i)); } row = null; HSSFCell dataCell = null; Set<String> keysSet = reportBeanMap.keySet(); ArrayList<String> keys = new ArrayList<String>(keysSet); int u = 0; // ADD DATA ROWS for (int i = 0; i < keys.size(); i++) { String tp = reportBeanMap.get(keys.get(i)).get(0).getTimepoint(); Collection<String> mutationStatusCollection = criteria.getMutationStatusCollection(); Collection<String> mutationTypeCollection = criteria.getMutationTypeCollection(); String mutationStatus = reportBeanMap.get(keys.get(i)).get(0).getMutationStatus(); String mutationType = reportBeanMap.get(keys.get(i)).get(0).getMutationType(); if (tpHeaders.contains(tp) && (mutationStatusCollection == null || (mutationStatusCollection != null && mutationStatusCollection.contains(mutationStatus))) && (mutationTypeCollection == null || (mutationTypeCollection != null && mutationTypeCollection.contains(mutationType)))) { sheet.createFreezePane(0, 1, 0, 1); row = sheet.createRow((short) u + 1); dataCell = row.createCell((short) 0); dataCell.setCellValue(reportBeanMap.get(keys.get(i)).get(0).getPatientDID()); dataCell = row.createCell((short) 1); dataCell.setCellValue(reportBeanMap.get(keys.get(i)).get(0).getSpecimenIdentifier()); //GRAB EACH REPORT BEAN IN EACH ARRAYLIST AND MATCH UP TO THE APPROPRIATE TIMEPOINT AS A MAP WITH THE TIMEPOINT AS KEY AND REPORTBEAN THE VALUE ArrayList<P53FindingReportBean> myList = reportBeanMap.get(keys.get(i)); Map<String, P53FindingReportBean> myMap = new HashMap<String, P53FindingReportBean>(); ArrayList<P53FindingReportBean> mySortedMap = new ArrayList<P53FindingReportBean>(); for (P53FindingReportBean ggg : myList) { for (int j = 0; j < tpHeaders.size(); j++) { if (ggg.getTimepoint().equalsIgnoreCase(tpHeaders.get(j))) { myMap.put(tpHeaders.get(j), ggg); break; } else if (ggg.getTimepoint().equals("--")) { if (!myMap.containsKey(tpHeaders.get(j))) { myMap.put(tpHeaders.get(j), ggg); break; } } } } //SORT MAP BY TIMEPOINT SO THAT THE REPORT BEAN DATA CAN EASILY BE DISPLAYED UNDER THE APPROPRIATE TIEMPOINT for (int t = 0; t < tpHeaders.size(); t++) { for (String k : myMap.keySet()) { if (k.equalsIgnoreCase(tpHeaders.get(t))) { mySortedMap.add(myMap.get(k)); } } } int counter = 2; //ITERATE OVER THE MAP FOR EACH DATA FIELD WITH ITS CORRESPONDING TIMEPOINT AND BUILD DATA ROWS for (P53FindingReportBean reportBean : mySortedMap) { dataCell = row.createCell((short) counter++); dataCell.setCellValue(reportBean.getMutationStatus()); } for (P53FindingReportBean reportBean : mySortedMap) { dataCell = row.createCell((short) counter++); dataCell.setCellValue(reportBean.getMutationType()); } for (P53FindingReportBean reportBean : mySortedMap) { dataCell = row.createCell((short) counter++); dataCell.setCellValue(reportBean.getExonOrIntronLocation()); } for (P53FindingReportBean reportBean : mySortedMap) { dataCell = row.createCell((short) counter++); dataCell.setCellValue(reportBean.getBaseChange()); } for (P53FindingReportBean reportBean : mySortedMap) { dataCell = row.createCell((short) counter++); dataCell.setCellValue(reportBean.getCodonAminoacidChange()); } for (P53FindingReportBean reportBean : mySortedMap) { dataCell = row.createCell((short) counter++); dataCell.setCellValue(reportBean.getProteinStructuralDomain()); } u++; } } } else { //TODO: handle this error } return wb; }
From source file:gov.nih.nci.evs.reportwriter.formatter.AsciiToExcelFormatter.java
License:BSD License
public Boolean convert(String textfile, String delimiter, String outfile) throws Exception { Vector<String> headings = getColumnHeadings(textfile, delimiter); Vector<Integer> maxChars = getColumnMaxChars(textfile, delimiter); // Note: Special Case for CDISC STDM Terminology report. int extensible_col = -1; if (_specialCases_CDISC) extensible_col = findColumnIndicator(headings, "Extensible"); int heading_height_multiplier = 1; for (int i = 0; i < maxChars.size(); i++) { String heading = (String) headings.elementAt(i); int maxCellLen = maxChars.elementAt(i); int maxTokenLen = getMaxTokenLength(heading); if (maxTokenLen > maxCellLen) { maxCellLen = maxTokenLen;//from w w w. ja v a2 s.co m maxChars.setElementAt(new Integer(maxCellLen), i); } if (maxCellLen < MAX_CODE_WIDTH) { Vector<String> tokens = parseData(heading, " "); if (tokens.size() > heading_height_multiplier) heading_height_multiplier = tokens.size(); } } Boolean[] a = findWrappedColumns(textfile, delimiter, MAX_WIDTH); // Note: The max column number allowed in an Excel spreadsheet is 256 int[] b = new int[255]; for (int i = 0; i < 255; i++) { b[i] = 0; } File file = new File(textfile); String absolutePath = file.getAbsolutePath(); _logger.debug("Absolute Path: " + absolutePath); String filename = file.getName(); _logger.debug("filename: " + filename); int m = filename.indexOf("."); String workSheetLabel = filename.substring(0, m); int n = workSheetLabel.indexOf("__"); workSheetLabel = workSheetLabel.substring(0, n); _logger.debug("workSheetLabel: " + workSheetLabel); if (workSheetLabel.compareTo("") == 0) return Boolean.FALSE; String pathName = file.getPath(); _logger.debug("Path: " + pathName); BufferedReader br = getBufferReader(textfile); FileOutputStream fout = new FileOutputStream(outfile); HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet ws = wb.createSheet(workSheetLabel); HSSFCellStyle toprow = wb.createCellStyle(); HSSFCellStyle highlightedrow = wb.createCellStyle(); HSSFCellStyle cs = wb.createCellStyle(); // Note: GF20673 shade top row HSSFFont font = wb.createFont(); font.setColor(HSSFColor.BLACK.index); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); toprow.setFont(font); if (extensible_col == -1) { toprow.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); } else { //toprow.setFillForegroundColor(HSSFColor.YELLOW.index); toprow.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index); } toprow.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); toprow.setAlignment(HSSFCellStyle.VERTICAL_CENTER); toprow.setWrapText(true); highlightedrow.setFont(font); //highlightedrow.setFillForegroundColor(HSSFColor.SKY_BLUE.index); //highlightedrow.setFillForegroundColor(HSSFColor.LIGHT_BLUE.index); highlightedrow.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index); highlightedrow.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); highlightedrow.setAlignment(HSSFCellStyle.VERTICAL_CENTER); // highlightedrow.setWrapText(true); cs.setWrapText(true); // cs.setAlignment(HSSFCellStyle.ALIGN_JUSTIFY); cs.setAlignment(HSSFCellStyle.VERTICAL_TOP); HSSFRow wr = null; int rownum = 0; // int baseline_height = 15; int baseline_height = 12; while (true) { String line = br.readLine(); if (line == null) break; // line = line.trim(); Note: 090512 first value could be empty if (line.length() <= 0) continue; Vector<String> v = parseData(line, delimiter); wr = ws.createRow(rownum); // wr.setHeightInPoints(60); if (rownum == 0) { wr.setHeightInPoints(baseline_height * heading_height_multiplier); } else { wr.setHeightInPoints(baseline_height); if (ADJUST_HEIGHT) { int num_lines = getHeightInPoints(v, ADJUST_HEIGHT, MAX_CELL_WIDTH); wr.setHeightInPoints(baseline_height * num_lines); } } // Note: Special Case for CDISC STDM Terminology report. boolean highlight_row = false; if (_specialCases_CDISC) highlight_row = extensible_col != -1 && v.elementAt(extensible_col).trim().length() > 0; for (int i = 0; i < v.size(); i++) { HSSFCell wc = wr.createCell(i); if (rownum == 0) { wc.setCellStyle(toprow); } else if (a[i].equals(Boolean.TRUE)) { wc.setCellStyle(cs); wc.setCellType(HSSFCell.CELL_TYPE_STRING); if (highlight_row) wc.setCellStyle(highlightedrow); } else { if (highlight_row) wc.setCellStyle(highlightedrow); } String s = (String) v.elementAt(i); s = s.trim(); if (s.length() > b[i]) { b[i] = s.length(); } if (s.equals("")) { s = null; } wc.setCellValue(s); if (_ncitCodeColumns.contains(i) && rownum > 0 && s != null && s.length() > 0) { try { wc.setCellFormula("HYPERLINK(\"" + getNCItCodeUrl(s) + "\", \"" + s + "\")"); } catch (Exception e) { ExceptionUtils.print(_logger, e, "The following string is too large to be a " + "valid NCIt code (" + filename + "): " + s); } } } rownum++; } br.close(); for (int i = 0; i < 255; i++) { if (b[i] != 0) { int multiplier = b[i]; if (i < headings.size()) { Integer int_obj = (Integer) maxChars.elementAt(i); multiplier = int_obj.intValue(); } // Note(GF20673): 315 is the magic number for this font and size int colWidth = multiplier * 315; // Fields like definition run long, some sanity required if (colWidth > 20000) { colWidth = 20000; } // _logger.debug("Calculated column width " + i + ": " + // colWidth); ws.setColumnWidth(i, colWidth); } } // Note(GF20673): Freeze top row ws.createFreezePane(0, 1, 0, 1); wb.write(fout); fout.close(); return Boolean.TRUE; }
From source file:net.sourceforge.fenixedu.presentationTier.Action.academicAdministration.executionCourseManagement.CourseLoadOverviewBean.java
License:Open Source License
public StyledExcelSpreadsheet getInconsistencySpreadsheet() { final StyledExcelSpreadsheet spreadsheet = new StyledExcelSpreadsheet( BundleUtil.getString(Bundle.ACADEMIC, "label.course.load.inconsistency.filename") + "_" + executionSemester.getExecutionYear().getYear().replace('/', '_') + "_" + executionSemester.getSemester()); HSSFCellStyle normalStyle = spreadsheet.getExcelStyle().getValueStyle(); normalStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFWorkbook wb = spreadsheet.getWorkbook(); HSSFFont font = wb.createFont();//from w ww.j a va 2 s.c om font.setColor(HSSFColor.BLACK.index); font.setFontHeightInPoints((short) 8); HSSFCellStyle redStyle = wb.createCellStyle(); redStyle.setFont(font); redStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); redStyle.setFillForegroundColor(HSSFColor.ORANGE.index); redStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); HSSFCellStyle yellowStyle = wb.createCellStyle(); yellowStyle.setFont(font); yellowStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); yellowStyle.setFillForegroundColor(HSSFColor.YELLOW.index); yellowStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); spreadsheet.newHeaderRow(); spreadsheet.addHeader(BundleUtil.getString(Bundle.ACADEMIC, "label.department")); spreadsheet.addHeader(BundleUtil.getString(Bundle.ACADEMIC, "label.degree")); spreadsheet.addHeader(BundleUtil.getString(Bundle.ACADEMIC, "label.executionCourse")); spreadsheet.addHeader(BundleUtil.getString(Bundle.ACADEMIC, "label.shift")); spreadsheet.addHeader(BundleUtil.getString(Bundle.ACADEMIC, "label.shiftType")); spreadsheet.addHeader(BundleUtil.getString(Bundle.ACADEMIC, "label.load.competenceCourse")); spreadsheet.addHeader(BundleUtil.getString(Bundle.ACADEMIC, "label.load.curricularCourse")); spreadsheet.addHeader(BundleUtil.getString(Bundle.ACADEMIC, "label.load.executionCourse")); spreadsheet.addHeader(BundleUtil.getString(Bundle.ACADEMIC, "label.load.lessonInstances")); spreadsheet.addHeader(BundleUtil.getString(Bundle.ACADEMIC, "label.load.lesson.count")); spreadsheet.addHeader(BundleUtil.getString(Bundle.ACADEMIC, "label.load.lessonInstances.count")); for (final ExecutionCourse executionCourse : executionSemester.getAssociatedExecutionCoursesSet()) { for (final CourseLoad courseLoad : executionCourse.getCourseLoadsSet()) { for (final Shift shift : courseLoad.getShiftsSet()) { spreadsheet.newRow(); spreadsheet.addCell(getDepartmentString(executionCourse)); spreadsheet.addCell(executionCourse.getDegreePresentationString()); spreadsheet.addCell(executionCourse.getName()); spreadsheet.addCell(shift.getNome()); spreadsheet.addCell(courseLoad.getType().getFullNameTipoAula()); final BigDecimal competenceCourseLoad = new BigDecimal(getCompetenceCourseLoad(courseLoad)) .setScale(2, RoundingMode.HALF_EVEN); final BigDecimal curricularCourseLoad = new BigDecimal(getCurricularCourseLoad(courseLoad)) .setScale(2, RoundingMode.HALF_EVEN); final BigDecimal executionLoad = courseLoad.getTotalQuantity().setScale(2, RoundingMode.HALF_EVEN); final BigDecimal shiftCourseLoad = getShiftCourseLoad(shift).setScale(2, RoundingMode.HALF_EVEN); if (competenceCourseLoad.signum() < 0) { spreadsheet.addCell(getCompetenceCourseLoadStrings(courseLoad), redStyle); } else { spreadsheet.addCell(competenceCourseLoad); } if (!competenceCourseLoad.equals(curricularCourseLoad) || curricularCourseLoad.signum() < 0) { spreadsheet.addCell(getCurricularCourseLoadString(courseLoad), redStyle); } else { spreadsheet.addCell(curricularCourseLoad); } if (!executionLoad.equals(curricularCourseLoad)) { spreadsheet.addCell(executionLoad, redStyle); } else { spreadsheet.addCell(executionLoad); } if (!shiftCourseLoad.equals(executionLoad)) { if (isLargeDifference(shiftCourseLoad, executionLoad, competenceCourseLoad.divide(new BigDecimal(14), 2, RoundingMode.HALF_EVEN))) { spreadsheet.addCell(shiftCourseLoad, redStyle); } else { spreadsheet.addCell(shiftCourseLoad, yellowStyle); } } else { spreadsheet.addCell(shiftCourseLoad); } spreadsheet.addCell(shift.getAssociatedLessonsSet().size()); spreadsheet.addCell(getLessonInstanceCount(shift)); } } } final HSSFSheet sheet = wb.getSheetAt(0); sheet.createFreezePane(0, 1, 0, 1); sheet.autoSizeColumn(1, true); sheet.autoSizeColumn(2, true); sheet.autoSizeColumn(3, true); sheet.autoSizeColumn(4, true); sheet.autoSizeColumn(5, true); sheet.autoSizeColumn(6, true); sheet.autoSizeColumn(7, true); sheet.autoSizeColumn(8, true); sheet.autoSizeColumn(9, true); return spreadsheet; }
From source file:org.cyberoam.iview.servlets.ExcelFileGenerator.java
License:Open Source License
/** * This method is used to handle HTTP POST requests. *//*from w ww. j a v a2 s. c o m*/ public void doPost(HttpServletRequest request, HttpServletResponse response) throws IOException, ServletException { String startDate = null, endDate = null, limit = "1000", offset = null, applianceList = null, mode = null; int reportGroupID = -1; int reportID = -1; SqlReader sqlReader = new SqlReader(); ResultSetWrapper rsw = null; String query = null; IndexManager indexManager = null; HttpSession session = request.getSession(); ServletOutputStream out = response.getOutputStream(); response.setContentType("application/octet-stream"); try { // Archive Logs Exportation if (request.getParameter("archive") != null) { try { HashMap<String, String> criteriaList = new HashMap<String, String>(); String categoryID = (String) session.getAttribute("categoryid"); query = " where"; Date startDateDt = DateDifference.stringToDate((String) session.getAttribute("startdate"), "yyyy-MM-dd hh:mm:ss"); Date endDateDt = DateDifference.stringToDate((String) session.getAttribute("enddate"), "yyyy-MM-dd hh:mm:ss"); if (PrepareQuery.calculateDifference(startDateDt, endDateDt) > 0) { query += " upload_datetime >= '" + (String) (String) session.getAttribute("startdate") + "' and upload_datetime <= ' " + (new SimpleDateFormat("yyyy-MM-dd")).format(DateDifference .stringToDate((String) session.getAttribute("startdate"), "yyyy-MM-dd")) + " 23:59:59'"; criteriaList.put("upload_datetimeStart", ">=," + (String) session.getAttribute("startdate")); criteriaList.put("upload_datetimeEnd", "<=," + (new SimpleDateFormat("yyyy-MM-dd")).format(DateDifference .stringToDate((String) session.getAttribute("startdate"), "yyyy-MM-dd")) + " 23:59:59"); } else { query += " upload_datetime >= '" + (String) (String) session.getAttribute("startdate") + "' and upload_datetime <= ' " + (String) session.getAttribute("enddate") + "'"; criteriaList.put("upload_datetimeStart", ">=," + (String) session.getAttribute("startdate")); criteriaList.put("upload_datetimeEnd", "<=," + (String) session.getAttribute("enddate")); } query += " and device_name in (" + (String) session.getAttribute("appliancelist") + ") "; criteriaList.put("device_name", "=," + (String) session.getAttribute("appliancelist")); criteriaList.put("indexCriteria", request.getParameter("indexCriteria")); CyberoamLogger.appLog .info("ExcelGeneration : indexCriteria = " + request.getParameter("indexCriteria")); String intLotSize = iViewConfigBean.getValueByKey("Limit"); long longCurrentRecordPosition = Long.parseLong(request.getParameter("offset")); String tblName = request.getParameter("tblname"); HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet formattedLogs = wb.createSheet("Formatted Logs"); HSSFSheet rawLogs = wb.createSheet("Raw Logs"); HSSFRow row; HSSFCell cell; //Formatted logs Column Headings row = formattedLogs.createRow(0); ArrayList<IndexFieldsBean> indexFileList = IndexFieldsBean .getIndexFieldBeanListByCategoryID(categoryID); for (int n = 0; n < indexFileList.size() - 2; n++) { row.createCell(n).setCellValue(((IndexFieldsBean) indexFileList.get(n)).getGuiIndexName()); } // Raw Logs Column Heading. row = rawLogs.createRow(0); row.createCell(0).setCellValue("Raw Logs"); // For freezing first row in both sheets formattedLogs.createFreezePane(0, 1, 0, 1); rawLogs.createFreezePane(0, 1, 0, 1); criteriaList.put("limit", "=," + Integer.parseInt(intLotSize)); criteriaList.put("offset", "=," + longCurrentRecordPosition); criteriaList.put("categoryID", "=," + categoryID); ArrayList recordArray = null; if (request.getParameter("indexCriteria") == null || request.getParameter("indexCriteria").equals("null")) recordArray = SearchIndexBean.getDateRangeData(criteriaList); else recordArray = SearchIndexBean.getSearchData(criteriaList); if (recordArray.size() == Integer.parseInt(intLotSize) + 1) { recordArray.remove(recordArray.size() - 1); } //Formatted Logs data ArrayList columnArray = null; String dataValue; if (recordArray != null) { for (int recordcount = 0; recordcount < recordArray.size(); recordcount++) { columnArray = (ArrayList) recordArray.get(recordcount); row = formattedLogs.createRow(recordcount + 1); for (int columncount = 0; columncount < columnArray.size() - 1; columncount++) { dataValue = (String) columnArray.get(columncount); formattedLogs.autoSizeColumn((short) columncount); cell = row.createCell(columncount); if (dataValue != null && dataValue.length() > 0) { // 1 is for numeric data type if (Integer.parseInt((indexFileList.get(columncount)).getDataType()) == 1) { cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(Integer.parseInt(dataValue)); } else cell.setCellValue(dataValue); } else { cell.setCellValue("-"); } } } } // Raw Logs if (recordArray != null) { for (int recordcount = 0; recordcount < recordArray.size(); recordcount++) { row = rawLogs.createRow(recordcount + 1); columnArray = (ArrayList) recordArray.get(recordcount); dataValue = (String) columnArray.get(columnArray.size() - 1); if (dataValue != null && dataValue.length() > 0) { row.createCell(0).setCellValue(dataValue); } else { row.createCell(0).setCellValue("-"); } } } wb.write(out); out.close(); } catch (Exception e) { CyberoamLogger.appLog.error("Error in exporting archive data to excel"); } } //Report data Export else { if (request.getParameter("xlsdata") != null) mode = (String) request.getParameter("xlsdata"); if (mode.equalsIgnoreCase("group")) { limit = "5"; } else { reportID = Integer.parseInt(request.getParameter("reportid")); } PrepareQuery prepareQuery = new PrepareQuery(); if (request.getParameter("reportgroupid") != null) reportGroupID = Integer.parseInt(request.getParameter("reportgroupid")); // For report group if (reportID == -1) { try { CyberoamLogger.appLog.info("Report Group id : " + reportGroupID); ReportGroupBean reportGroupBean = ReportGroupBean.getRecordbyPrimarykey(reportGroupID); ReportGroupRelationBean reportGroupRelationBean; ArrayList reportList = reportGroupBean.getReportIdByReportGroupId(reportGroupID); //For getting workbook of Excel HSSFWorkbook wb = new HSSFWorkbook(); int noReports = reportList.size(); for (int ctr = 0; ctr < noReports; ctr++) { reportGroupRelationBean = (ReportGroupRelationBean) reportList.get(ctr); reportID = reportGroupRelationBean.getReportId(); CyberoamLogger.appLog.info("ReportGroup : Report id : " + reportID); ReportBean reportBean = ReportBean.getRecordbyPrimarykey(reportID); query = prepareQuery.getQuery(reportBean, request); // Getting result set try { if (query.indexOf("select") == -1 && query.indexOf("SELECT") == -1) { indexManager = new IndexManager(); rsw = indexManager.getSearch(query); //rsw=indexManager.getResutSetFromArrayList(searchRecord); } else { rsw = sqlReader.getInstanceResultSetWrapper(query); } } catch (org.postgresql.util.PSQLException e) { if (query.indexOf("5min_ts_20") > -1) { query = query.substring(0, query.indexOf("5min_ts_20")) + "4hr" + query.substring(query.indexOf("5min_ts_20") + 16, query.length()); rsw = sqlReader.getInstanceResultSetWrapper(query); } else { CyberoamLogger.appLog.error("Exeption in AjaxController.java " + e, e); } } catch (Exception e) { CyberoamLogger.appLog.error("Exeption in AjaxController.java " + e, e); rsw.close(); } // Getting workbook with sheets & all data & chart getWorkBook(rsw, reportBean, wb); } wb.write(out); out.close(); } catch (Exception e) { CyberoamLogger.appLog.error("***Exeption in report group Excel file generation***" + e); } finally { sqlReader.close(); rsw.close(); } } else {// For single report try { ReportBean reportBean = ReportBean.getRecordbyPrimarykey(reportID); //Getting query for report query = prepareQuery.getQuery(reportBean, request); String searchQuery = request.getParameter("searchquery"); if (searchQuery != null && !"".equalsIgnoreCase(searchQuery)) { query = query.replaceAll("where", "where " + searchQuery + " and"); } try { if (query.indexOf("select") == -1 && query.indexOf("SELECT") == -1) { indexManager = new IndexManager(); rsw = indexManager.getSearch(query); //rsw=indexManager.getResutSetFromArrayList(searchRecord); } else { rsw = sqlReader.getInstanceResultSetWrapper(query); } } catch (org.postgresql.util.PSQLException e) { if (query.indexOf("5min_ts_20") > -1) { query = query.substring(0, query.indexOf("5min_ts_20")) + "4hr" + query.substring(query.indexOf("5min_ts_20") + 16, query.length()); rsw = sqlReader.getInstanceResultSetWrapper(query); } else { CyberoamLogger.appLog.error("Exeption in AjaxController.java " + e, e); } } catch (Exception e) { CyberoamLogger.appLog.error("Exeption in AjaxController.java " + e, e); rsw.close(); } HSSFWorkbook wb = new HSSFWorkbook(); getWorkBook(rsw, reportBean, wb); wb.write(out); out.close(); } catch (Exception e) { CyberoamLogger.appLog .info("***Exception during Excel Single report generation***" + e.getMessage()); } finally { sqlReader.close(); rsw.close(); } } } } catch (Exception ex) { CyberoamLogger.appLog.info("***Excel Report Generartion Exception***" + ex, ex); } }
From source file:org.cyberoam.iview.servlets.ExcelFileGenerator.java
License:Open Source License
void getWorkBook(ResultSetWrapper rsw, ReportBean reportBean, HSSFWorkbook wb) { try {/* w w w.j av a 2s .c o m*/ ReportColumnBean[] reportColumns = (ReportColumnBean[]) ReportColumnBean .getReportColumnsByReportID(reportBean.getReportId()).toArray(new ReportColumnBean[0]); HSSFSheet newSheet = wb.createSheet(reportBean.getTitle()); HSSFRow row; HSSFCell cell; HSSFCellStyle cellStyle = wb.createCellStyle(); HSSFFont fontStyle = wb.createFont(); fontStyle.setFontHeightInPoints((short) 10); fontStyle.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); cellStyle.setFont(fontStyle); // getting number of records & fields for report rsw.last(); int rowCount = rsw.getRow(); int colCount = reportColumns.length; //For Freezing the first row newSheet.createFreezePane(0, 1, 0, 1); // Adding column headings to Excel row = newSheet.createRow((short) 0); for (int cCount = 0; cCount < colCount; cCount++) { cell = row.createCell(cCount); cell.setCellStyle(cellStyle); cell.setCellValue(reportColumns[cCount].getColumnName()); } //Adding data for each record to Excel rsw.first(); for (int rcount = 1; rcount <= rowCount; rcount++) { row = newSheet.createRow(rcount); for (int cCount = 0; cCount < colCount; cCount++) { String data = rsw.getString(reportColumns[cCount].getDbColumnName()); cell = row.createCell(cCount); newSheet.autoSizeColumn((short) cCount); if (reportColumns[cCount].getColumnFormat() == TabularReportConstants.BYTE_FORMATTING) { data = ByteInUnit.getBytesInUnit(rsw.getLong(reportColumns[cCount].getDbColumnName())); } else if (reportColumns[cCount].getColumnFormat() == TabularReportConstants.PROTOCOL_FORMATTING && data.indexOf(':') != -1) { String xdata = ProtocolBean.getProtocolNameById( Integer.parseInt(rsw.getString(reportColumns[cCount].getDbColumnName()).substring(0, data.indexOf(':')))); data = xdata + rsw.getString(reportColumns[cCount].getDbColumnName()) .substring(data.indexOf(':'), data.length()); } // Setting value to the cell if (cCount == 0 && (data == null || "".equalsIgnoreCase(data))) data = "N/A"; if (reportColumns[cCount].getColumnFormat() == TabularReportConstants.PERCENTAGE_FORMATTING) { try { cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(Double.parseDouble(data)); } catch (NumberFormatException e) { cell.setCellValue(data); } } else if (rsw.getMetaData().getColumnTypeName(cCount + 1).equalsIgnoreCase("numeric") && reportColumns[cCount].getColumnFormat() != TabularReportConstants.BYTE_FORMATTING) { try { cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(Integer.parseInt(data)); } catch (NumberFormatException e) { cell.setCellValue(data); } } else { cell.setCellValue(data); } } rsw.next(); } } catch (Exception e) { CyberoamLogger.appLog.error("***Error in getWorkbook function***" + e, e); } }
From source file:org.emmanet.controllers.statsSpreadsheetController.java
License:Apache License
public boolean populateDataCells(HSSFSheet arg1, List results, int startRow, boolean freezeFrame) { System.out.println("results size" + results.size()); int irow = startRow;//0 row set to header already for (Iterator it = results.listIterator(); it.hasNext();) { Object[] o = (Object[]) it.next(); HSSFRow row = arg1.createRow(irow); //System.out.println("O length is::" + o.length); for (int i = 0; i < o.length; i++) { if (o[i] == null) { o[i] = ""; }// w ww .java 2s. c o m HSSFRichTextString data = new HSSFRichTextString(o[i].toString()); Pattern pattern = Pattern.compile(PATTERN); Matcher matcher = pattern.matcher(o[i].toString()); boolean matchFound = matcher.matches(); if (matchFound) { //this is a numeric data field format as such double value = Double.parseDouble(o[i].toString()); row.createCell((short) i, 0).setCellValue(value); } else { row.createCell((short) i).setCellValue(data); } //System.out.println(i + ". result value= " + o.length + " ---- " +o[i].toString()); } irow++; // System.out.println(); } // AUTOSIZE COLUMNS TO DATA for (int i = 0; i <= headerTitle.length; i++) { arg1.autoSizeColumn((short) i); } if (freezeFrame) { arg1.createFreezePane(0, 1, 0, 1); } return true; }
From source file:org.fenixedu.academic.ui.struts.action.academicAdministration.executionCourseManagement.CourseLoadOverviewBean.java
License:Open Source License
public StyledExcelSpreadsheet getInconsistencySpreadsheet() { final StyledExcelSpreadsheet spreadsheet = new StyledExcelSpreadsheet( BundleUtil.getString(Bundle.ACADEMIC, "label.course.load.inconsistency.filename") + "_" + executionSemester.getExecutionYear().getYear().replace('/', '_') + "_" + executionSemester.getSemester()); CellStyle normalStyle = spreadsheet.getExcelStyle().getValueStyle(); normalStyle.setAlignment(HorizontalAlignment.CENTER); HSSFWorkbook wb = spreadsheet.getWorkbook(); HSSFFont font = wb.createFont();//from www. j av a 2 s . co m font.setColor(HSSFColor.BLACK.index); font.setFontHeightInPoints((short) 8); HSSFCellStyle redStyle = wb.createCellStyle(); redStyle.setFont(font); redStyle.setAlignment(HorizontalAlignment.CENTER); redStyle.setFillForegroundColor(HSSFColor.ORANGE.index); redStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); HSSFCellStyle yellowStyle = wb.createCellStyle(); yellowStyle.setFont(font); yellowStyle.setAlignment(HorizontalAlignment.CENTER); yellowStyle.setFillForegroundColor(HSSFColor.YELLOW.index); yellowStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); spreadsheet.newHeaderRow(); spreadsheet.addHeader(BundleUtil.getString(Bundle.ACADEMIC, "label.department")); spreadsheet.addHeader(BundleUtil.getString(Bundle.ACADEMIC, "label.degree")); spreadsheet.addHeader(BundleUtil.getString(Bundle.ACADEMIC, "label.executionCourse")); spreadsheet.addHeader(BundleUtil.getString(Bundle.ACADEMIC, "label.shift")); spreadsheet.addHeader(BundleUtil.getString(Bundle.ACADEMIC, "label.shiftType")); spreadsheet.addHeader(BundleUtil.getString(Bundle.ACADEMIC, "label.load.competenceCourse")); spreadsheet.addHeader(BundleUtil.getString(Bundle.ACADEMIC, "label.load.curricularCourse")); spreadsheet.addHeader(BundleUtil.getString(Bundle.ACADEMIC, "label.load.executionCourse")); spreadsheet.addHeader(BundleUtil.getString(Bundle.ACADEMIC, "label.load.lessonInstances")); spreadsheet.addHeader(BundleUtil.getString(Bundle.ACADEMIC, "label.load.lesson.count")); spreadsheet.addHeader(BundleUtil.getString(Bundle.ACADEMIC, "label.load.lessonInstances.count")); for (final ExecutionCourse executionCourse : executionSemester.getAssociatedExecutionCoursesSet()) { for (final CourseLoad courseLoad : executionCourse.getCourseLoadsSet()) { for (final Shift shift : courseLoad.getShiftsSet()) { spreadsheet.newRow(); spreadsheet.addCell(getDepartmentString(executionCourse)); spreadsheet.addCell(executionCourse.getDegreePresentationString()); spreadsheet.addCell(executionCourse.getName()); spreadsheet.addCell(shift.getNome()); spreadsheet.addCell(courseLoad.getType().getFullNameTipoAula()); final BigDecimal competenceCourseLoad = new BigDecimal(getCompetenceCourseLoad(courseLoad)) .setScale(2, RoundingMode.HALF_EVEN); final BigDecimal curricularCourseLoad = new BigDecimal(getCurricularCourseLoad(courseLoad)) .setScale(2, RoundingMode.HALF_EVEN); final BigDecimal executionLoad = courseLoad.getTotalQuantity().setScale(2, RoundingMode.HALF_EVEN); final BigDecimal shiftCourseLoad = getShiftCourseLoad(shift).setScale(2, RoundingMode.HALF_EVEN); if (competenceCourseLoad.signum() < 0) { spreadsheet.addCell(getCompetenceCourseLoadStrings(courseLoad), redStyle); } else { spreadsheet.addCell(competenceCourseLoad); } if (!competenceCourseLoad.equals(curricularCourseLoad) || curricularCourseLoad.signum() < 0) { spreadsheet.addCell(getCurricularCourseLoadString(courseLoad), redStyle); } else { spreadsheet.addCell(curricularCourseLoad); } if (!executionLoad.equals(curricularCourseLoad)) { spreadsheet.addCell(executionLoad, redStyle); } else { spreadsheet.addCell(executionLoad); } if (!shiftCourseLoad.equals(executionLoad)) { if (isLargeDifference(shiftCourseLoad, executionLoad, competenceCourseLoad.divide(new BigDecimal(14), 2, RoundingMode.HALF_EVEN))) { spreadsheet.addCell(shiftCourseLoad, redStyle); } else { spreadsheet.addCell(shiftCourseLoad, yellowStyle); } } else { spreadsheet.addCell(shiftCourseLoad); } spreadsheet.addCell(shift.getAssociatedLessonsSet().size()); spreadsheet.addCell(getLessonInstanceCount(shift)); } } } final HSSFSheet sheet = wb.getSheetAt(0); sheet.createFreezePane(0, 1, 0, 1); sheet.autoSizeColumn(1, true); sheet.autoSizeColumn(2, true); sheet.autoSizeColumn(3, true); sheet.autoSizeColumn(4, true); sheet.autoSizeColumn(5, true); sheet.autoSizeColumn(6, true); sheet.autoSizeColumn(7, true); sheet.autoSizeColumn(8, true); sheet.autoSizeColumn(9, true); return spreadsheet; }