List of usage examples for org.apache.poi.ss.usermodel Row setRowStyle
void setRowStyle(CellStyle style);
From source file:opisiame.controller.gestion_resultat.Choix_exportController.java
@FXML public void excel_export() { File excel_file = choix_chemin_enregistrement("Excel files (*.xls)", "*.xls"); if (onglet_actif.equals("questions")) { if (excel_file != null) { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet("Resultat par question"); sheet.autoSizeColumn(5);//from www. j av a 2 s.c o m create_data1(sheet, 0, "Question", "Pourcentage reponse A", "Pourcentage reponse B", "Pourcentage reponse C", "Pourcentage reponse D", "Pourcentage bonne rponse"); Row row = sheet.getRow(0); HSSFCellStyle cellStyle = null; HSSFFont font = wb.createFont(); font.setBold(true); cellStyle = wb.createCellStyle(); cellStyle.setFont(font); row.setRowStyle(cellStyle); for (int i = 0; i < reponse_questions.size(); i++) { Reponse_question rq = reponse_questions.get(i); create_data1(sheet, i + 1, rq.getQuestion(), rq.getStr_pourcentage_rep_a(), rq.getStr_pourcentage_rep_b(), rq.getStr_pourcentage_rep_c(), rq.getStr_pourcentage_rep_d(), rq.getStr_pourcentage()); } FileOutputStream fileOut; try { fileOut = new FileOutputStream(excel_file); wb.write(fileOut); fileOut.close(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } } } else if (onglet_actif.equals("eleves")) { if (excel_file != null) { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet("Resultat des tudiants"); sheet.autoSizeColumn(5); create_data2(sheet, 0, "Nom", "Prnom", "N tudiant", "Note", "Pourcentage"); Row row = sheet.getRow(0); HSSFCellStyle cellStyle = null; HSSFFont font = wb.createFont(); font.setBold(true); cellStyle = wb.createCellStyle(); cellStyle.setFont(font); row.setRowStyle(cellStyle); for (int i = 0; i < resultats_eleves.size(); i++) { Rep_eleves_quiz re = resultats_eleves.get(i); create_data2(sheet, i + 1, re.getNom_eleve(), re.getPrenom_eleve(), re.getNum_eleve().toString(), re.getNote_eleve().toString(), re.getPourcent_eleve().toString()); } FileOutputStream fileOut; try { fileOut = new FileOutputStream(excel_file); wb.write(fileOut); fileOut.close(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } } } else if (onglet_actif.equals("eleves_pas_num")) { if (excel_file != null) { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet("Resultat des tudiants"); sheet.autoSizeColumn(4); create_data3(sheet, 0, "Nom", "Prnom", "Note", "Pourcentage"); Row row = sheet.getRow(0); HSSFCellStyle cellStyle = null; HSSFFont font = wb.createFont(); font.setBold(true); cellStyle = wb.createCellStyle(); cellStyle.setFont(font); row.setRowStyle(cellStyle); for (int i = 0; i < resultats_eleves.size(); i++) { Rep_eleves_quiz re = resultats_eleves.get(i); create_data3(sheet, i + 1, re.getNom_eleve(), re.getPrenom_eleve(), re.getNote_eleve().toString(), re.getPourcent_eleve().toString()); } FileOutputStream fileOut; try { fileOut = new FileOutputStream(excel_file); wb.write(fileOut); fileOut.close(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } } } close_window(); }
From source file:org.haplo.jsinterface.generate.KGenerateXLS.java
License:Mozilla Public License
@Override protected void writeRow(int rowNumber, ArrayList<Object> row, ArrayList<Object> rowOptions, boolean isHeaderRow, boolean pageBreakBefore) { Row r = this.sheet.createRow(rowNumber); if (pageBreakBefore && rowNumber > 0) { this.sheet.setRowBreak(rowNumber - 1); }/*from www.j av a 2 s. c om*/ int rowSize = row.size(); for (int i = 0; i < rowSize; ++i) { Object value = row.get(i); // ConsString is checked if (value != null) { Cell c = r.createCell(i); if (value instanceof Number) { c.setCellValue(((Number) value).doubleValue()); } else if (value instanceof CharSequence) { c.setCellValue(((CharSequence) value).toString()); } else if (value instanceof Date) { c.setCellValue((Date) value); // Check to see if option is for dates only boolean dateAndTimeStyle = true; String options = (String) getOptionsFromArrayList(rowOptions, i, String.class); // ConsString is checked by getOptionsFromArrayList() if (options != null && options.equals("date")) { dateAndTimeStyle = false; } if (dateCellStyle == null) { // Only create one each of the date cell styles per workbook to save space. dateCellStyle = workbook.createCellStyle(); dateCellStyle.setDataFormat(workbook.createDataFormat().getFormat("yyyy-mm-dd hh:mm")); dateOnlyCellStyle = workbook.createCellStyle(); dateOnlyCellStyle.setDataFormat(workbook.createDataFormat().getFormat("yyyy-mm-dd")); } c.setCellStyle(dateAndTimeStyle ? dateCellStyle : dateOnlyCellStyle); // Set column width so the dates don't come out as ########## on causal viewing setMinimumWidth(i, dateAndTimeStyle ? DATE_AND_TIME_COLUMN_WIDTH : DATE_COLUMN_WIDTH); } } } if (isHeaderRow) { // Make sure the row is always on screen this.sheet.createFreezePane(0, 1, 0, 1); // Style the row CellStyle style = this.workbook.createCellStyle(); style.setBorderBottom(BorderStyle.THIN); Font font = this.workbook.createFont(); font.setBold(true); style.setFont(font); r.setRowStyle(style); // Style the cells for (int s = 0; s < rowSize; ++s) { Cell c = r.getCell(s); if (c == null) { c = r.createCell(s); } c.setCellStyle(style); } } }
From source file:org.hellojavaer.poi.excel.utils.ExcelUtils.java
License:Apache License
@SuppressWarnings("unchecked") private static void write(boolean useTemplate, Workbook workbook, OutputStream outputStream, ExcelWriteSheetProcessor<?>... sheetProcessors) { for (@SuppressWarnings("rawtypes") ExcelWriteSheetProcessor sheetProcessor : sheetProcessors) { @SuppressWarnings("rawtypes") ExcelWriteContext context = new ExcelWriteContext(); try {/*from ww w. j a v a 2 s . co m*/ if (sheetProcessor == null) { continue; } String sheetName = sheetProcessor.getSheetName(); Integer sheetIndex = sheetProcessor.getSheetIndex(); Sheet sheet = null; if (sheetProcessor.getTemplateStartRowIndex() == null && sheetProcessor.getTemplateEndRowIndex() == null) { sheetProcessor.setTemplateRows(sheetProcessor.getStartRowIndex(), sheetProcessor.getStartRowIndex()); } // sheetName priority, if (useTemplate) { if (sheetName != null) { try { sheet = workbook.getSheet(sheetName); } catch (IllegalArgumentException e) { // ignore } if (sheet != null && sheetIndex != null && !sheetIndex.equals(workbook.getSheetIndex(sheet))) { throw new IllegalArgumentException( "sheetName[" + sheetName + "] and sheetIndex[" + sheetIndex + "] not match."); } } else if (sheetIndex != null) { try { sheet = workbook.getSheetAt(sheetIndex); } catch (IllegalArgumentException e) { // ignore } } else { throw new IllegalArgumentException("sheetName or sheetIndex can't be null"); } if (sheet == null) { ExcelWriteException e = new ExcelWriteException( "Sheet Not Found Exception. for sheet name:" + sheetName); e.setCode(ExcelWriteException.CODE_OF_SHEET_NOT_EXSIT); throw e; } } else { if (sheetName != null) { sheet = workbook.getSheet(sheetName); if (sheet != null) { if (sheetIndex != null && !sheetIndex.equals(workbook.getSheetIndex(sheet))) { throw new IllegalArgumentException("sheetName[" + sheetName + "] and sheetIndex[" + sheetIndex + "] not match."); } } else { sheet = workbook.createSheet(sheetName); if (sheetIndex != null) { workbook.setSheetOrder(sheetName, sheetIndex); } } } else if (sheetIndex != null) { sheet = workbook.createSheet(); workbook.setSheetOrder(sheet.getSheetName(), sheetIndex); } else { throw new IllegalArgumentException("sheetName or sheetIndex can't be null"); } } if (sheetIndex == null) { sheetIndex = workbook.getSheetIndex(sheet); } if (sheetName == null) { sheetName = sheet.getSheetName(); } // proc sheet context.setCurSheet(sheet); context.setCurSheetIndex(sheetIndex); context.setCurSheetName(sheet.getSheetName()); context.setCurRow(null); context.setCurRowIndex(null); context.setCurCell(null); context.setCurColIndex(null); // beforeProcess sheetProcessor.beforeProcess(context); // write head writeHead(useTemplate, sheet, sheetProcessor); // sheet ExcelProcessControllerImpl controller = new ExcelProcessControllerImpl(); int writeRowIndex = sheetProcessor.getStartRowIndex(); boolean isBreak = false; Map<Integer, InnerRow> cacheForTemplateRow = new HashMap<Integer, InnerRow>(); List<?> dataList = sheetProcessor.getDataList(); // if (dataList != null && !dataList.isEmpty()) { for (Object rowData : dataList) { // proc row Row row = sheet.getRow(writeRowIndex); if (row == null) { row = sheet.createRow(writeRowIndex); } InnerRow templateRow = getTemplateRow(cacheForTemplateRow, sheet, sheetProcessor, writeRowIndex); if (templateRow != null) { row.setHeight(templateRow.getHeight()); row.setHeightInPoints(templateRow.getHeightInPoints()); row.setRowStyle(templateRow.getRowStyle()); row.setZeroHeight(templateRow.isZeroHeight()); } context.setCurRow(row); context.setCurRowIndex(writeRowIndex); context.setCurColIndex(null); context.setCurCell(null); // try { controller.reset(); if (sheetProcessor.getRowProcessor() != null) { sheetProcessor.getRowProcessor().process(controller, context, rowData, row); } if (!controller.isDoSkip()) { writeRow(context, templateRow, row, rowData, sheetProcessor); writeRowIndex++; } if (controller.isDoBreak()) { isBreak = true; break; } } catch (RuntimeException e) { if (e instanceof ExcelWriteException) { ExcelWriteException ewe = (ExcelWriteException) e; // ef.setColIndex(null); user may want to set this value, ewe.setRowIndex(writeRowIndex); throw ewe; } else { ExcelWriteException ewe = new ExcelWriteException(e); ewe.setColIndex(null); ewe.setCode(ExcelWriteException.CODE_OF_PROCESS_EXCEPTION); ewe.setRowIndex(writeRowIndex); throw ewe; } } } if (isBreak) { break; } } if (sheetProcessor.getTemplateStartRowIndex() != null && sheetProcessor.getTemplateEndRowIndex() != null) { writeDataValidations(sheet, sheetProcessor); writeStyleAfterFinish(useTemplate, sheet, sheetProcessor); } } catch (RuntimeException e) { sheetProcessor.onException(context, e); } finally { sheetProcessor.afterProcess(context); } } try { workbook.write(outputStream); } catch (IOException e) { throw new RuntimeException(e); } }
From source file:org.openmrs.module.mksreports.renderer.PatientHistoryExcelTemplateRenderer.java
License:Open Source License
/** * Adds in a Row to the given Sheet//from ww w .j a v a 2s. c om */ public Row addRow(Workbook wb, SheetToAdd sheetToAdd, RowToAdd rowToAdd, int rowIndex, ReportData reportData, ReportDesign design, Map<String, String> repeatSections) { // Create a new row and copy over style attributes from the row to add Row newRow = sheetToAdd.getSheet().createRow(rowIndex); Row rowToClone = rowToAdd.getRowToClone(); try { CellStyle rowStyle = rowToClone.getRowStyle(); if (rowStyle != null) { newRow.setRowStyle(rowStyle); } } catch (Exception e) { // No idea why this is necessary, but this has thrown IndexOutOfBounds errors getting the rowStyle. Mysteries of POI } newRow.setHeight(rowToClone.getHeight()); // Iterate across all of the cells in the row, and configure all those that need to be added/cloned List<CellToAdd> cellsToAdd = new ArrayList<CellToAdd>(); int totalCells = rowToClone.getPhysicalNumberOfCells(); int cellsFound = 0; for (int cellNum = 0; cellsFound < totalCells; cellNum++) { Cell currentCell = rowToClone.getCell(cellNum); log.debug("Handling cell: " + currentCell); if (currentCell != null) { cellsFound++; } // If we find that the cell that we are on is a repeating cell, then add the appropriate number of cells to clone String repeatingColumnProperty = getRepeatingColumnProperty(sheetToAdd.getOriginalSheetNum(), cellNum, repeatSections); if (repeatingColumnProperty != null) { String[] dataSetSpanSplit = repeatingColumnProperty.split(","); String dataSetName = dataSetSpanSplit[0]; DataSet dataSet = getDataSet(reportData, dataSetName, rowToAdd.getReplacementData()); int numCellsToRepeat = 1; if (dataSetSpanSplit.length == 2) { numCellsToRepeat = Integer.parseInt(dataSetSpanSplit[1]); } log.debug("Repeating this cell with dataset: " + dataSet + " and repeat of " + numCellsToRepeat); int repeatNum = 0; for (DataSetRow dataSetRow : dataSet) { repeatNum++; for (int i = 0; i < numCellsToRepeat; i++) { Cell cell = (i == 0 ? currentCell : rowToClone.getCell(cellNum + i)); if (repeatNum == 1 && cell != null && cell != currentCell) { cellsFound++; } Map<String, Object> newReplacements = getReplacementData(rowToAdd.getReplacementData(), reportData, design, dataSetName, dataSetRow, repeatNum); cellsToAdd.add(new CellToAdd(cell, newReplacements)); log.debug("Adding " + cell + " with dataSetRow: " + dataSetRow); } } cellNum += numCellsToRepeat; } else { cellsToAdd.add(new CellToAdd(currentCell, rowToAdd.getReplacementData())); log.debug("Adding " + currentCell); } } // Now, go through all of the collected cells, and add them back in String prefix = getExpressionPrefix(design); String suffix = getExpressionSuffix(design); List<CellRangeAddress> newMergedRegions = new ArrayList<CellRangeAddress>(); for (int i = 0; i < cellsToAdd.size(); i++) { CellToAdd cellToAdd = cellsToAdd.get(i); Cell newCell = newRow.createCell(i); Cell cellToClone = cellToAdd.getCellToClone(); if (cellToClone != null) { Object contents = ExcelUtil.getCellContents(cellToClone); newCell.setCellStyle(cellToClone.getCellStyle()); int numFormattings = sheetToAdd.getSheet().getSheetConditionalFormatting() .getNumConditionalFormattings(); for (int n = 0; n < numFormattings; n++) { ConditionalFormatting f = sheetToAdd.getSheet().getSheetConditionalFormatting() .getConditionalFormattingAt(n); for (CellRangeAddress add : f.getFormattingRanges()) { if (add.getFirstRow() == rowToAdd.getRowToClone().getRowNum() && add.getLastRow() == rowToClone.getRowNum()) { if (add.getFirstColumn() == cellToClone.getColumnIndex() && add.getLastColumn() == cellToClone.getColumnIndex()) { ConditionalFormattingRule[] rules = new ConditionalFormattingRule[f .getNumberOfRules()]; for (int j = 0; j < f.getNumberOfRules(); j++) { rules[j] = f.getRule(j); } CellRangeAddress[] cellRange = new CellRangeAddress[1]; cellRange[0] = new CellRangeAddress(rowIndex, rowIndex, i, i); sheetToAdd.getSheet().getSheetConditionalFormatting() .addConditionalFormatting(cellRange, rules); } } } } int numMergedRegions = sheetToAdd.getSheet().getNumMergedRegions(); for (int n = 0; n < numMergedRegions; n++) { CellRangeAddress add = sheetToAdd.getSheet().getMergedRegion(n); int rowNum = rowToClone.getRowNum(); if (add.getFirstRow() == rowNum && add.getLastRow() == rowNum) { if (add.getFirstColumn() == cellToClone.getColumnIndex()) { newMergedRegions .add(new CellRangeAddress(rowNum, rowNum, i, i + add.getNumberOfCells() - 1)); } } } if (ObjectUtil.notNull(contents)) { if (contents instanceof String) { contents = EvaluationUtil.evaluateExpression(contents.toString(), cellToAdd.getReplacementData(), prefix, suffix); } ExcelUtil.setCellContents(newCell, contents); } ExcelUtil.copyFormula(cellToClone, newCell); } } for (CellRangeAddress mergedRegion : newMergedRegions) { sheetToAdd.getSheet().addMergedRegion(mergedRegion); } return newRow; }
From source file:org.sleuthkit.autopsy.report.ReportExcel.java
License:Open Source License
/** * Start a new set for the current data type. * @param setName name of the set//from w w w. j ava 2s .co m */ @Override public void startSet(String setName) { setName = escapeForExcel(setName); Row row = sheet.createRow(rowIndex); row.setRowStyle(setStyle); row.createCell(0).setCellValue(setName); ++rowIndex; }
From source file:org.sleuthkit.autopsy.report.ReportExcel.java
License:Open Source License
/** * Add an element to the set//from w w w. java2s . c o m * @param elementName element name */ @Override public void addSetElement(String elementName) { elementName = escapeForExcel(elementName); Row row = sheet.createRow(rowIndex); row.setRowStyle(elementStyle); row.createCell(0).setCellValue(elementName); ++rowIndex; }
From source file:org.sleuthkit.autopsy.report.ReportExcel.java
License:Open Source License
/** * Label the top of this sheet with the table column names. * @param titles column names// w w w. jav a2s. c o m */ @Override public void startTable(List<String> titles) { int tableColCount = 0; Row row = sheet.createRow(rowIndex); row.setRowStyle(titleStyle); for (int i = 0; i < titles.size(); i++) { row.createCell(i).setCellValue(titles.get(i)); ++tableColCount; } ++rowIndex; // Keep track of the number of columns with data in them for later column auto-sizing. if (tableColCount > sheetColCount) { sheetColCount = tableColCount; } }
From source file:org.sleuthkit.autopsy.report.ReportExcel.java
License:Open Source License
private void writeSummaryWorksheet() { sheet = wb.createSheet(NbBundle.getMessage(this.getClass(), "ReportExcel.sheetName.text")); rowIndex = 0;/*w w w . j av a2 s.c o m*/ Row row = sheet.createRow(rowIndex); row.setRowStyle(setStyle); row.createCell(0).setCellValue(NbBundle.getMessage(this.getClass(), "ReportExcel.cellVal.summary")); ++rowIndex; sheet.createRow(rowIndex); ++rowIndex; Case currentCase = Case.getCurrentCase(); row = sheet.createRow(rowIndex); row.setRowStyle(setStyle); row.createCell(0).setCellValue(NbBundle.getMessage(this.getClass(), "ReportExcel.cellVal.caseName")); row.createCell(1).setCellValue(currentCase.getName()); ++rowIndex; row = sheet.createRow(rowIndex); row.setRowStyle(setStyle); row.createCell(0).setCellValue(NbBundle.getMessage(this.getClass(), "ReportExcel.cellVal.caseNum")); row.createCell(1).setCellValue(currentCase.getNumber()); ++rowIndex; row = sheet.createRow(rowIndex); row.setRowStyle(setStyle); row.createCell(0).setCellValue(NbBundle.getMessage(this.getClass(), "ReportExcel.cellVal.examiner")); row.createCell(1).setCellValue(currentCase.getExaminer()); ++rowIndex; row = sheet.createRow(rowIndex); row.setRowStyle(setStyle); row.createCell(0).setCellValue(NbBundle.getMessage(this.getClass(), "ReportExcel.cellVal.numImages")); int numImages; try { numImages = currentCase.getDataSources().size(); } catch (TskCoreException ex) { numImages = 0; } row.createCell(1).setCellValue(numImages); ++rowIndex; sheet.autoSizeColumn(0); sheet.autoSizeColumn(1); }
From source file:reports.notReached.java
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException, SQLException, InvalidFormatException { session = request.getSession();//from ww w .ja v a2 s . co m dbConn conn = new dbConn(); position = 1; String reportHeader[] = ("COUNTY NAME ,PARTNER NAME,DISTRICT NAME, DIC NAME, GROUP NAME,CLIENT FULL NAME ," + " CCC NO. , MOBILE NUMBER , GENDER , DATE OF BIRTH , MARITAL STATUS , EMPLOYMENT STATUS ," + "EDUCATION LEVEL , ART STATUS , SERVICE PROVIDER NAME , HEALTH FACILITY, LESSONS ATTENDED,AGE BRACKET, Knowledge of HIV Status," + "Partner HIV Testing,Child HIV Testing,Discordance,HIV Disclosure,Risk Factor/Reduction,Condom Use," + "Alcohol and Substance Abuse,Adherence,STIs,Family Planning,PMTCT,TB").split(","); // COPY FILE TO BE WRITTEN TO Path original = Paths.get(getServletContext().getRealPath("/NOT_ACHIEVED_TEMPLATE.xlsm")); //original file Path destination = Paths.get(getServletContext().getRealPath("/NOT_ACHIEVED_TEMPLATE_1.xlsm")); //new file System.out.println("origin : " + original + " destination : " + destination); try { Files.copy(original, destination, StandardCopyOption.REPLACE_EXISTING); System.out.println("file copied----------------"); } catch (IOException x) { //catch all for IO problems System.out.println("fine not copied"); } String allpath = getServletContext().getRealPath("/NOT_ACHIEVED_TEMPLATE_1.xlsm"); // ^^^^^^^^^^^^^CREATE STATIC AND WRITE STATIC DATA TO THE EXCELL^^^^^^^^^^^^ XSSFWorkbook wb1; OPCPackage pkg = OPCPackage.open(allpath); wb1 = new XSSFWorkbook(pkg); SXSSFWorkbook wb = new SXSSFWorkbook(wb1, 100); // ^^^^^^^^^^^^^CREATE STATIC AND WRITE STATIC DATA TO THE EXCELL^^^^^^^^^^^^ // HSSFWorkbook wb=new HSSFWorkbook(); Sheet shet1 = wb.getSheet("Sheet1"); Font font = wb.createFont(); font.setFontHeightInPoints((short) 18); font.setFontName("Arial Black"); font.setColor((short) 0000); CellStyle style = wb.createCellStyle(); style.setFont(font); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); Font font2 = wb.createFont(); font2.setFontName("Arial Black"); font2.setColor((short) 0000); CellStyle style2 = wb.createCellStyle(); style2.setFont(font2); CellStyle stborder = wb.createCellStyle(); stborder.setBorderTop(HSSFCellStyle.BORDER_THIN); stborder.setBorderBottom(HSSFCellStyle.BORDER_THIN); stborder.setBorderLeft(HSSFCellStyle.BORDER_THIN); stborder.setBorderRight(HSSFCellStyle.BORDER_THIN); stborder.setAlignment(HSSFCellStyle.ALIGN_CENTER); for (int i = 0; i <= reportHeader.length; i++) { shet1.setColumnWidth(i, 4000); } CellStyle styleBorder = wb.createCellStyle(); styleBorder.setBorderTop(HSSFCellStyle.BORDER_THIN); styleBorder.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleBorder.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleBorder.setBorderRight(HSSFCellStyle.BORDER_THIN); styleBorder.setAlignment(HSSFCellStyle.ALIGN_CENTER); CellStyle stylex = wb.createCellStyle(); stylex.setFillForegroundColor(HSSFColor.LIME.index); stylex.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); stylex.setBorderTop(HSSFCellStyle.BORDER_THIN); stylex.setBorderBottom(HSSFCellStyle.BORDER_THIN); stylex.setBorderLeft(HSSFCellStyle.BORDER_THIN); stylex.setBorderRight(HSSFCellStyle.BORDER_THIN); stylex.setAlignment(HSSFCellStyle.ALIGN_CENTER); Font fontx = wb.createFont(); fontx.setColor(HSSFColor.DARK_BLUE.index); stylex.setFont(fontx); stylex.setWrapText(true); Cell cell; Row rw0 = shet1.createRow(0); rw0.setHeightInPoints(30); rw0.setRowStyle(style2); for (int i = 0; i <= (reportHeader.length - 1); i++) { cell = rw0.createCell(i); cell.setCellValue(reportHeader[i]); cell.setCellStyle(stylex); } String getClients = "SELECT county.county_name,partner.partner_name,district.district_name,dic.dic_name," + "groups.group_name,personal_information.fname,personal_information.mname,personal_information.lname," + "personal_information.ccc_no,personal_information.mobile_no, personal_information.gender," + "personal_information.dob,marital_status.name,employment_status.name,education_levels.name," + "art_status.name," + "service_provider.fname,service_provider.mname,service_provider.lname,health_facility.hf_name, " + "personal_information.lessons_attended," + "CASE" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 0 AND 9 THEN '0-9'" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 10 AND 14 THEN '10-14'" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 15 AND 19 THEN '15-19'" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 20 AND 24 THEN '20-24'" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 25 AND 49 THEN '25-49'" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) >49 THEN '50 and above'" + " ELSE 'NO DATE OF BIRTH'" + " END AS AGEBRACKET," + "CASE " + "when personal_information.gender LIKE 'Female' THEN 'F' " + "when personal_information.gender LIKE 'Male' THEN 'M' " + "ELSE 'NO SEX' " + "END AS SEX,s1,s2,s3,s4,s5,s6,s7,s8,s9,s10,s11,s12,s13 " + " FROM personal_information " + " LEFT JOIN groups ON personal_information.group_id=groups.group_id " + " LEFT JOIN dic ON personal_information.dic_id=dic.dic_id " + " LEFT JOIN service_provider ON personal_information.provider_id=service_provider.provider_id " + " LEFT JOIN health_facility ON personal_information.hf_id=health_facility.hf_id " + " LEFT JOIN district ON personal_information.district_id=district.district_id " + " LEFT JOIN marital_status ON personal_information.marital_status=marital_status.id " + " LEFT JOIN employment_status ON personal_information.employment_status=employment_status.id " + " LEFT JOIN education_levels ON personal_information.education_level=education_levels.id " + " LEFT JOIN art_status ON personal_information.art_status=art_status.id " + " LEFT JOIN register ON personal_information.client_id=register.client_id " + " LEFT JOIN partner ON personal_information.partner_id=partner.partner_id " + " LEFT JOIN county ON district.county_id=county.county_id " + " WHERE (personal_information.completionmonth=0 || personal_information.completionyear=0) " + " ORDER BY partner.partner_name,county.county_name,district.district_name,dic.dic_name," + "groups.group_name"; System.out.println("query is : " + getClients); conn.rs = conn.st.executeQuery(getClients); while (conn.rs.next()) { // ADD THE DATA TO EXCEL HERE groupName = DICName = districtName = partnerName = countyName = agebracket = lessons_attended = year = ""; clientFname = clientMname = clientLname = ccc_no = mobile_no = gender = dob = marital_status = ""; location = employment_status = education_level = under_18 = ovc_children = hiv_year = art_status = ""; registration_date = approved_by = designation = approval_date = ""; SPFname = SPMname = SPLname = SPFullName = healthFacility = ""; s1 = s2 = s3 = s4 = s5 = s6 = s7 = s8 = s9 = s10 = s11 = s12 = s13 = ""; if (conn.rs.getString(1) != null) { countyName = conn.rs.getString(1); } if (conn.rs.getString(2) != null) { partnerName = conn.rs.getString(2); } if (conn.rs.getString(3) != null) { districtName = conn.rs.getString(3); } if (conn.rs.getString(4) != null) { DICName = conn.rs.getString(4); } else { DICName = "NO DIC"; } if (conn.rs.getString(5) != null) { groupName = conn.rs.getString(5); } else { groupName = "Individual"; } if (conn.rs.getString(6) != null) { clientFname = conn.rs.getString(6); } if (conn.rs.getString(7) != null) { clientMname = conn.rs.getString(7); } if (conn.rs.getString(8) != null) { clientLname = conn.rs.getString(8); } if (conn.rs.getString(9) != null) { ccc_no = conn.rs.getString(9); } if (conn.rs.getString(10) != null) { mobile_no = conn.rs.getString(10); } if (conn.rs.getString(11) != null) { gender = conn.rs.getString(11); } if (conn.rs.getString(12) != null) { dob = conn.rs.getString(12); } if (conn.rs.getString(13) != null) { marital_status = conn.rs.getString(13); } if (conn.rs.getString(14) != null) { employment_status = conn.rs.getString(14); } if (conn.rs.getString(15) != null) { education_level = conn.rs.getString(15); } if (conn.rs.getString(16) != null) { art_status = conn.rs.getString(16); } if (conn.rs.getString(17) != null) { SPFname = conn.rs.getString(17); } if (conn.rs.getString(18) != null) { SPMname = conn.rs.getString(18); } if (conn.rs.getString(19) != null) { SPLname = conn.rs.getString(19); } if (conn.rs.getString(20) != null) { healthFacility = conn.rs.getString(20); } if (conn.rs.getString(21) != null) { lessons_attended = conn.rs.getString(21); } if (conn.rs.getString(22) != null) { agebracket = conn.rs.getString(22); } if (conn.rs.getString(23) != null) { gender = conn.rs.getString(23); } if (conn.rs.getString(24) != null) { s1 = conn.rs.getString(24); } if (conn.rs.getString(25) != null) { s2 = conn.rs.getString(25); } if (conn.rs.getString(26) != null) { s3 = conn.rs.getString(26); } if (conn.rs.getString(27) != null) { s4 = conn.rs.getString(27); } if (conn.rs.getString(28) != null) { s5 = conn.rs.getString(28); } if (conn.rs.getString(29) != null) { s6 = conn.rs.getString(29); } if (conn.rs.getString(30) != null) { s7 = conn.rs.getString(30); } if (conn.rs.getString(31) != null) { s8 = conn.rs.getString(31); } if (conn.rs.getString(32) != null) { s9 = conn.rs.getString(32); } if (conn.rs.getString(33) != null) { s10 = conn.rs.getString(33); } if (conn.rs.getString(34) != null) { s11 = conn.rs.getString(34); } if (conn.rs.getString(35) != null) { s12 = conn.rs.getString(35); } if (conn.rs.getString(36) != null) { s13 = conn.rs.getString(36); } if (s1.equals("5")) { s1 = ""; } if (s1.equals("2")) { s1 = "0"; } if (s2.equals("5")) { s2 = ""; } if (s2.equals("2")) { s2 = "0"; } if (s3.equals("5")) { s3 = ""; } if (s3.equals("2")) { s3 = "0"; } if (s4.equals("5")) { s4 = ""; } if (s4.equals("2")) { s4 = "0"; } if (s5.equals("5")) { s5 = ""; } if (s5.equals("2")) { s5 = "0"; } if (s6.equals("5")) { s6 = ""; } if (s6.equals("2")) { s6 = "0"; } if (s7.equals("5")) { s7 = ""; } if (s7.equals("2")) { s7 = "0"; } if (s8.equals("5")) { s8 = ""; } if (s8.equals("2")) { s8 = "0"; } if (s9.equals("5")) { s9 = ""; } if (s9.equals("2")) { s9 = "0"; } if (s10.equals("5")) { s10 = ""; } if (s10.equals("2")) { s10 = "0"; } if (s11.equals("5")) { s11 = ""; } if (s11.equals("2")) { s11 = "0"; } if (s12.equals("5")) { s12 = ""; } if (s12.equals("2")) { s12 = "0"; } if (s13.equals("5")) { s13 = ""; } if (s13.equals("2")) { s13 = "0"; } if (clientMname.equals(clientLname)) { clientMname = ""; } if (SPMname.equals(SPLname)) { SPMname = ""; } SPFullName = SPFname + " " + SPMname + " " + SPLname; clientFullName = clientFname + " " + clientMname + " " + clientLname; String rawData[] = (countyName + "," + partnerName + "," + districtName + "," + DICName + "," + groupName + "," + clientFullName + "," + ccc_no + "," + mobile_no + "," + gender + "," + dob + "," + marital_status + "," + employment_status + "," + education_level + "," + art_status + "," + SPFullName + "," + healthFacility + "," + lessons_attended + "," + agebracket + "," + s1 + "," + s2 + "," + s3 + "," + s4 + "," + s5 + "," + s6 + "," + s7 + "," + s8 + "," + s9 + "," + s10 + "," + s11 + "," + s12 + "," + s13).split(","); Row rw1 = shet1.createRow(position); rw1.setHeightInPoints(25); rw1.setRowStyle(style2); for (int i = 0; i <= (reportHeader.length - 1); i++) { cell = rw1.createCell(i); cell.setCellStyle(styleBorder); } for (int i = 0; i <= (rawData.length - 1); i++) { cell = rw1.getCell(i); cell.setCellValue(rawData[i]); } position++; System.out.println("at position : " + position); } IdGenerator CRT = new IdGenerator(); ByteArrayOutputStream outByteStream = new ByteArrayOutputStream(); wb.write(outByteStream); byte[] outArray = outByteStream.toByteArray(); response.setContentType("application/ms-excel"); response.setContentLength(outArray.length); response.setHeader("Expires:", "0"); // eliminates browser caching response.setHeader("Content-Disposition", "attachment; filename=PWP_CLIENTS_NOT_REACHED_REPORT_CREATED_ON_" + CRT.timestamp() + ".xlsm"); OutputStream outStream = response.getOutputStream(); outStream.write(outArray); outStream.flush(); pkg.close(); }
From source file:se.mithlond.services.backend.war.resources.food.FoodResource.java
License:Apache License
/** * @return The Allergies and Preferences food report. *//*from w w w . ja v a2 s . c o m*/ @GET @Path("/report/excel") @Consumes({ MediaType.APPLICATION_JSON, MediaType.APPLICATION_XML }) @Produces(ExcelReportService.EXCEL_CONTENT_TYPE) public Response getAllergyAndPreferencesReport( @QueryParam(RestfulParameters.INCLUDE_LOGIN_NOT_PERMITTED) final Boolean includeLoginNotPermitted) { // Create the workbook to return final Workbook workbook = excelReportService.createDocument(getActiveMembership(), "Allergier och Matpreferenser"); final String fileName = "allergyReport_" + TimeFormat.COMPACT_LOCALDATETIME.print(LocalDateTime.now()) + ".xls"; final boolean withLoginOnly = includeLoginNotPermitted != null && !includeLoginNotPermitted; final List<String> allergyColumns = Arrays.asList("Alias", "Allergigrad", "Allerginiv", "Fdomne", "Grupp", "Undergrupp"); final Sheet allergySheet = excelReportService.createStandardExcelSheet(workbook, "Allergier", "Allergier", allergyColumns); if (log.isDebugEnabled()) { log.debug("Got non-null Workbook: " + (workbook != null)); } // #1) Find the allergies and food prefs per membership alias. final SortedMap<String, SortedSet<Allergy>> alias2AllergiesMap = new TreeMap<>(); final SortedMap<String, SortedSet<FoodPreference>> alias2PreferencesMap = new TreeMap<>(); final FoodAndAllergySearchParameters searchParameters = FoodAndAllergySearchParameters.builder() .withOrganisationIDs(getActiveMembership().getOrganisation().getId()).withLoginOnly(withLoginOnly) .build(); foodAndAllergyService.getAllergiesFor(searchParameters).forEach((k, v) -> { alias2AllergiesMap.put(k.getAlias(), v); }); foodAndAllergyService.getPreferencesFor(searchParameters).forEach((k, v) -> { alias2PreferencesMap.put(k.getAlias(), v); }); // #2) Create an Excel Sheet holding the Allergies. int currentRowIndex = 2; final CellStyle standardCellStyle = excelReportService .getCellStyle(ExcelReportService.ExcelElement.NON_WRAPPING, workbook); final CellStyle rowCellStyle = excelReportService.getCellStyle(ExcelReportService.ExcelElement.CELL, workbook); for (Map.Entry<String, SortedSet<Allergy>> current : alias2AllergiesMap.entrySet()) { final SortedSet<Allergy> allergies = current.getValue(); if (allergies.size() > 0) { for (Allergy currentAllergy : allergies) { final Row currentRow = allergySheet.createRow(currentRowIndex++); currentRow.setRowStyle(rowCellStyle); final Food currentFood = currentAllergy.getFood(); final String categoryID = currentFood.getCategory().getCategoryID(); final String subcategoryID = currentFood.getSubCategory().getCategoryID(); final String foodName = currentFood.getLocalizedFoodName().getText(); final String allergySeverityDesc = currentAllergy.getSeverity().getShortDescription().getText(); final String allergySortOrder = "" + currentAllergy.getSeverity().getSeveritySortOrder(); // Populate the row with Cells. excelReportService.addCell(0, currentRow, current.getKey(), standardCellStyle); excelReportService.addCell(1, currentRow, allergySeverityDesc, standardCellStyle); excelReportService.addCell(2, currentRow, allergySortOrder, standardCellStyle); excelReportService.addCell(3, currentRow, foodName, standardCellStyle); excelReportService.addCell(4, currentRow, categoryID, standardCellStyle); excelReportService.addCell(5, currentRow, subcategoryID, standardCellStyle); } } } // #3) Create an Excel Sheet holding the Food preferences. currentRowIndex = 2; final List<String> preferenceColumnNames = Arrays.asList("Alias", "Preferens", "Beskrivning"); final Sheet preferenceSheet = excelReportService.createStandardExcelSheet(workbook, "Preferenser", "Matpreferenser", preferenceColumnNames); // Insert relevant data into the Excel Sheet. currentRowIndex = 2; for (Map.Entry<String, SortedSet<FoodPreference>> current : alias2PreferencesMap.entrySet()) { final SortedSet<FoodPreference> preferences = current.getValue(); if (preferences.size() > 0) { for (FoodPreference currentPreference : preferences) { final Row currentRow = preferenceSheet.createRow(currentRowIndex++); final String categoryID = currentPreference.getCategory().getCategoryID(); final String desc = currentPreference.getCategory().getDescription(); // Populate the row with Cells. excelReportService.addCell(0, currentRow, current.getKey(), standardCellStyle); excelReportService.addCell(1, currentRow, categoryID, standardCellStyle); excelReportService.addCell(2, currentRow, desc, standardCellStyle); } } } // Convert the workbook to a byte[], and send it back to the client. final Response.ResponseBuilder builder = Response .ok(excelReportService.convertToByteArray(workbook), ExcelReportService.EXCEL_CONTENT_TYPE) .header(ReportService.SUGGESTED_FILENAME_HEADER, fileName); builder.header("Content-Disposition", "attachment; filename=" + fileName); // All Done. return builder.build(); }