List of usage examples for org.apache.poi.xssf.usermodel XSSFSheet setColumnWidth
@Override public void setColumnWidth(int columnIndex, int width)
The maximum column width for an individual cell is 255 characters.
From source file:com.netsteadfast.greenstep.bsc.command.TimeSeriesAnalysisExcelCommand.java
License:Apache License
private void putTables(XSSFWorkbook wb, XSSFSheet sh, Context context) throws Exception { TsaVO tsa = (TsaVO) context.get("tsa"); @SuppressWarnings("unchecked") List<BbTsaMaCoefficients> coefficients = (List<BbTsaMaCoefficients>) context.get("coefficients"); @SuppressWarnings("unchecked") List<TimeSeriesAnalysisResult> tsaResults = (List<TimeSeriesAnalysisResult>) context.get("tsaResults"); XSSFFont cellHeadFont = wb.createFont(); cellHeadFont.setBold(true);/*from w w w. j a v a 2 s . c o m*/ XSSFCellStyle cellHeadStyle = wb.createCellStyle(); cellHeadStyle.setFillForegroundColor(new XSSFColor(SimpleUtils.getColorRGB4POIColor("#f5f5f5"))); cellHeadStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); cellHeadStyle.setBorderBottom(BorderStyle.THIN); cellHeadStyle.setBorderTop(BorderStyle.THIN); cellHeadStyle.setBorderRight(BorderStyle.THIN); cellHeadStyle.setBorderLeft(BorderStyle.THIN); cellHeadStyle.setFont(cellHeadFont); XSSFCellStyle cellHeadStyleBlank = wb.createCellStyle(); cellHeadStyleBlank.setFillForegroundColor(new XSSFColor(SimpleUtils.getColorRGB4POIColor("#ffffff"))); cellHeadStyleBlank.setFont(cellHeadFont); XSSFCellStyle cellHeadStyle2 = wb.createCellStyle(); cellHeadStyle2.setFillForegroundColor(new XSSFColor(SimpleUtils.getColorRGB4POIColor("#ffffff"))); cellHeadStyle2.setFillPattern(FillPatternType.SOLID_FOREGROUND); cellHeadStyle2.setBorderBottom(BorderStyle.THIN); cellHeadStyle2.setBorderTop(BorderStyle.THIN); cellHeadStyle2.setBorderRight(BorderStyle.THIN); cellHeadStyle2.setBorderLeft(BorderStyle.THIN); sh.setColumnWidth(0, 12000); int row = 0; // ============================================================== Row nowRow = sh.createRow(row); Cell cellTitle = nowRow.createCell(0); cellTitle.setCellStyle(cellHeadStyleBlank); cellTitle.setCellValue("Forecast analysis - " + context.get("visionName")); row++; // ============================================================== nowRow = sh.createRow(row); Cell cell0a = nowRow.createCell(0); cell0a.setCellStyle(cellHeadStyleBlank); cell0a.setCellValue("Frequency: " + context.get("frequencyName") + " , Date range: " + context.get("date1") + " - " + context.get("date2") + " , " + "Measure data type for: " + context.get("dataFor") + " , " + context.get("organizationName") + context.get("employeeName")); row++; // ============================================================== nowRow = sh.createRow(row); Cell cell0b = nowRow.createCell(0); cell0b.setCellStyle(cellHeadStyleBlank); cell0b.setCellValue(""); row++; // ============================================================== nowRow = sh.createRow(row); Cell cell1 = nowRow.createCell(0); cell1.setCellStyle(cellHeadStyleBlank); cell1.setCellValue("Param infornation"); row++; // ============================================================== nowRow = sh.createRow(row); Cell cell2_a = nowRow.createCell(0); cell2_a.setCellStyle(cellHeadStyle); cell2_a.setCellValue("Item"); Cell cell2_b = nowRow.createCell(1); cell2_b.setCellStyle(cellHeadStyle); cell2_b.setCellValue("Value"); row++; // ============================================================== nowRow = sh.createRow(row); Cell cell3_a = nowRow.createCell(0); cell3_a.setCellStyle(cellHeadStyle2); cell3_a.setCellValue("Param name"); Cell cell3_b = nowRow.createCell(1); cell3_b.setCellStyle(cellHeadStyle2); cell3_b.setCellValue(tsa.getName()); row++; // ============================================================== nowRow = sh.createRow(row); Cell cell4_a = nowRow.createCell(0); cell4_a.setCellStyle(cellHeadStyle2); cell4_a.setCellValue("Integration order"); Cell cell4_b = nowRow.createCell(1); cell4_b.setCellStyle(cellHeadStyle2); cell4_b.setCellValue(tsa.getIntegrationOrder()); row++; // ============================================================== nowRow = sh.createRow(row); Cell cell5_a = nowRow.createCell(0); cell5_a.setCellStyle(cellHeadStyle2); cell5_a.setCellValue("Forecast next"); Cell cell5_b = nowRow.createCell(1); cell5_b.setCellStyle(cellHeadStyle2); cell5_b.setCellValue(tsa.getForecastNext()); row++; // ============================================================== nowRow = sh.createRow(row); Cell cell6_a = nowRow.createCell(0); cell6_a.setCellStyle(cellHeadStyle2); cell6_a.setCellValue("Description"); Cell cell6_b = nowRow.createCell(1); cell6_b.setCellStyle(cellHeadStyle2); cell6_b.setCellValue(StringUtils.defaultString(tsa.getDescription()).trim()); row++; // ============================================================== for (int i = 0; coefficients != null && i < coefficients.size(); i++) { BbTsaMaCoefficients coefficient = coefficients.get(i); nowRow = sh.createRow(row); Cell cell7x_a = nowRow.createCell(0); cell7x_a.setCellStyle(cellHeadStyle2); cell7x_a.setCellValue("Coefficient (" + (i + 1) + ")"); Cell cell7x_b = nowRow.createCell(1); cell7x_b.setCellStyle(cellHeadStyle2); cell7x_b.setCellValue(String.valueOf(coefficient.getSeqValue())); row++; } // ============================================================== nowRow = sh.createRow(row); Cell cellTitle3a = nowRow.createCell(0); cellTitle3a.setCellStyle(cellHeadStyleBlank); cellTitle3a.setCellValue(""); row++; // nowRow = sh.createRow(row); Cell cellTitle2a = nowRow.createCell(0); cellTitle2a.setCellStyle(cellHeadStyle); cellTitle2a.setCellValue("KPIs"); int j = 1; TimeSeriesAnalysisResult firstResult = tsaResults.get(0); for (int i = 0; i < firstResult.getKpi().getDateRangeScores().size(); i++) { DateRangeScoreVO dateRangeScore = firstResult.getKpi().getDateRangeScores().get(i); Cell cellTitle2a_dateRange = nowRow.createCell(j); j++; cellTitle2a_dateRange.setCellStyle(cellHeadStyle); cellTitle2a_dateRange.setCellValue(dateRangeScore.getDate()); } for (int i = 0; i < firstResult.getForecastNext().size(); i++) { Cell cellTitle2a_dateRange = nowRow.createCell(j); j++; cellTitle2a_dateRange.setCellStyle(cellHeadStyle); cellTitle2a_dateRange.setCellValue("next(" + (i + 1) + ")"); } row++; // Date Range score Forecast next score for (int i = 0; i < tsaResults.size(); i++) { nowRow = sh.createRow(row); j = 0; TimeSeriesAnalysisResult resultModel = tsaResults.get(i); Cell cell_kpi = nowRow.createCell(j); cell_kpi.setCellStyle(cellHeadStyle); cell_kpi.setCellValue(resultModel.getKpi().getName()); j++; for (int n = 0; n < resultModel.getKpi().getDateRangeScores().size(); n++) { DateRangeScoreVO dateRangeScore = resultModel.getKpi().getDateRangeScores().get(n); Cell cell_dateRangeScore = nowRow.createCell(j); cell_dateRangeScore.setCellStyle(cellHeadStyle2); cell_dateRangeScore.setCellValue(dateRangeScore.getScore()); j++; } for (int n = 0; n < resultModel.getForecastNext().size(); n++) { double forecastScore = resultModel.getForecastNext().get(n); Cell cell_forecastScore = nowRow.createCell(j); cell_forecastScore.setCellStyle(cellHeadStyle2); cell_forecastScore.setCellValue(forecastScore); j++; } row++; } }
From source file:com.respam.comniq.models.POIexcelExporter.java
License:Open Source License
public void createFile() throws IOException { String path = System.getProperty("user.home") + File.separator + "comniq" + File.separator + "output"; File file = new File(path + File.separator + "POImovieInfo.xlsx"); // Blank Workbook XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet sheet = workbook.createSheet("Movies"); // Data for Labels Map<String, Object[]> label = new TreeMap<>(); label.put("1", new Object[] { "Poster", "Title", "Release Date", "Metascore", "IMDB Rating", "Plot", "IMDB URL", "Genre", "Director", "Actors", "Rating", "Runtime" }); // Iterate over label and write to sheet Set<String> keyset = label.keySet(); // Setting Style for the Label Row Font font = workbook.createFont(); font.setFontHeight((short) 240); font.setFontName("Courier New"); font.setBold(true);/*from w ww .j av a2s . co m*/ XSSFCellStyle labelStyle = workbook.createCellStyle(); labelStyle.setWrapText(true); labelStyle.setFont(font); // Setting column widths sheet.setColumnWidth(0, 4000); sheet.setColumnWidth(1, 8500); sheet.setColumnWidth(2, 4000); sheet.setColumnWidth(3, 4000); sheet.setColumnWidth(4, 3500); sheet.setColumnWidth(5, 9500); sheet.setColumnWidth(6, 5000); sheet.setColumnWidth(7, 4000); sheet.setColumnWidth(8, 3500); sheet.setColumnWidth(9, 4000); sheet.setColumnWidth(10, 3000); sheet.setColumnWidth(11, 4000); // Freezing the first row sheet.createFreezePane(0, 1); // Filling each cell with Label data for (String key : keyset) { Row row = sheet.createRow(0); Object[] objArr = label.get(key); int cellnum = 0; for (Object obj : objArr) { Cell cell = row.createCell(cellnum++); cell.setCellStyle(labelStyle); cell.setCellValue((String) obj); } } // Writing the excel file try { FileOutputStream out = new FileOutputStream(file); workbook.write(out); out.close(); System.out.println("Excel File Created"); } catch (Exception e) { e.printStackTrace(); } }
From source file:com.saba.CalendarDemo.java
License:Apache License
private static void setupColumnWidthForEachFields(XSSFSheet sheet) { //Product/Service Name & Details Key(s) sheet.setColumnWidth(0, 180 * 33); //UNSPSC & Details value(s) sheet.setColumnWidth(1, 180 * 33);/* w ww .j av a 2 s . c om*/ //Unit of Measure sheet.setColumnWidth(2, 130 * 33); //Quantity of Each sheet.setColumnWidth(3, 130 * 33); //Distributor(s) sheet.setColumnWidth(4, 150 * 33); //Unit Price sheet.setColumnWidth(5, 100 * 33); //Discounted Unit Price sheet.setColumnWidth(6, 200 * 33); //Quantity Needed (per Yr.) sheet.setColumnWidth(7, 200 * 33); //Total Price sheet.setColumnWidth(8, 150 * 33); }
From source file:courtscheduler.persistence.CourtScheduleIO.java
License:Apache License
private XSSFSheet initializePage(XSSFWorkbook workbook, String conference) { // Create a new sheet with titles and headings for each new conference XSSFSheet sheet = workbook.createSheet(conference); rowNumber = 0;/*w w w. ja v a2 s .c o m*/ Row header = sheet.createRow(rowNumber); // Set sheet to Landscape so all columns will fit on one page XSSFPrintSetup printSetup = sheet.getPrintSetup(); printSetup.setOrientation(PrintOrientation.LANDSCAPE); // Column widths determined by specific sizes of heading strings (further down) sheet.setColumnWidth(0, 7424); sheet.setColumnWidth(1, 1024); sheet.setColumnWidth(2, 7424); sheet.setColumnWidth(3, 3072); sheet.setColumnWidth(4, 2816); sheet.setColumnWidth(5, 2816); sheet.setColumnWidth(6, 2403); sheet.setColumnWidth(7, 1792); header.createCell(0).setCellValue("THE COURTS"); header.createCell(2).setCellValue("Game Schedule"); rowNumber = rowNumber + 2; header = sheet.createRow(rowNumber); header.createCell(0).setCellValue("Conference " + conference); rowNumber = rowNumber + 2; header = sheet.createRow(rowNumber); header.createCell(0).setCellValue("TEAM"); header.createCell(1).setCellValue(" "); header.createCell(2).setCellValue("OPPONENT"); header.createCell(3).setCellValue("CONFERENCE"); header.createCell(4).setCellValue("DAY"); header.createCell(5).setCellValue("DATE"); header.createCell(6).setCellValue("TIME"); header.createCell(7).setCellValue("COURT"); return sheet; }
From source file:coverageqc.functions.MyExcelEditor.java
public static void excelFormator(XSSFSheet currentSheet, File variantTsvFile, String tsvHeadingLine) throws IOException { String[] headingsArray = tsvHeadingLine.split("\t"); HashMap<String, Integer> headings = new HashMap<String, Integer>(); for (int x = 0; x < headingsArray.length; x++) { headings.put(headingsArray[x].substring(0, headingsArray[x].indexOf("_")), x); }/*from w w w.ja va 2 s. co m*/ XSSFPrintSetup printSetup = (XSSFPrintSetup) currentSheet.getPrintSetup(); File xslxTempFile = new File(variantTsvFile.getCanonicalPath() + ".coverage_qc.xlsx"); currentSheet.getHeader().setLeft(xslxTempFile.getName()); currentSheet.getHeader().setRight("DO NOT DISCARD!!! Keep with patient folder."); //in Dr. Carter's VBA was set at points 18 which is .25 inches currentSheet.setMargin(Sheet.RightMargin, .25); currentSheet.setMargin(Sheet.LeftMargin, .25); printSetup.setOrientation(PrintOrientation.LANDSCAPE); //NOTE: setFitWidth doesn't work for columns, ie can't setFitToPageColumns, this //is the best workaround I can do, it will only looked cramped for those with a lot of calls printSetup.setFitWidth((short) 1); printSetup.setFitHeight((short) 3); currentSheet.setRepeatingRows(CellRangeAddress.valueOf("1")); currentSheet.setFitToPage(true); //making it by default not print the fellow's interp currentSheet.getWorkbook().setPrintArea(0, 1, currentSheet.getRow(0).getPhysicalNumberOfCells(), 0, currentSheet.getLastRowNum()); for (int x = 0; x < currentSheet.getRow(0).getPhysicalNumberOfCells(); x++) { currentSheet.autoSizeColumn(x); } currentSheet.setColumnWidth(0, 10000); currentSheet.setColumnWidth(1, 10000); currentSheet.setColumnWidth(headings.get("Consequence").intValue() + 2, 3500); currentSheet.setColumnHidden(headings.get("Classification").intValue() + 2, true); currentSheet.setColumnHidden(headings.get("Inherited From").intValue() + 2, true); currentSheet.setColumnHidden(headings.get("Allelic Depths").intValue() + 2, true); currentSheet.setColumnHidden(headings.get("Custom Annotation").intValue() + 2, true); currentSheet.setColumnHidden(headings.get("Custom Gene Annotation").intValue() + 2, true); currentSheet.setColumnHidden(headings.get("Num Transcripts").intValue() + 2, true); currentSheet.setColumnHidden(headings.get("Transcript").intValue() + 2, true); currentSheet.setColumnHidden(headings.get("cDNA Position").intValue() + 2, true); currentSheet.setColumnHidden(headings.get("CDS Position").intValue() + 2, true); currentSheet.setColumnHidden(headings.get("Protein Position").intValue() + 2, true); currentSheet.setColumnHidden(headings.get("Amino Acids").intValue() + 2, true); currentSheet.setColumnHidden(headings.get("Codons").intValue() + 2, true); currentSheet.setColumnHidden(headings.get("HGNC").intValue() + 2, true); currentSheet.setColumnHidden(headings.get("Transcript HGNC").intValue() + 2, true); currentSheet.setColumnHidden(headings.get("Canonical").intValue() + 2, true); currentSheet.setColumnHidden(headings.get("Sift").intValue() + 2, true); currentSheet.setColumnHidden(headings.get("PolyPhen").intValue() + 2, true); currentSheet.setColumnHidden(headings.get("ENSP").intValue() + 2, true); currentSheet.setColumnHidden(headings.get("HGVSc").intValue() + 2, true); currentSheet.setColumnHidden(headings.get("HGVSp").intValue() + 2, true); currentSheet.setColumnHidden(headings.get("dbSNP ID").intValue() + 2, true); currentSheet.setColumnHidden(headings.get("Ancestral Allele").intValue() + 2, true); currentSheet.setColumnHidden(headings.get("Allele Freq").intValue() + 2, true); //everything beyond this is hidden for (int x = headings.get("Global Minor Allele").intValue() + 2; x < currentSheet.getRow(0) .getPhysicalNumberOfCells(); x++) { currentSheet.setColumnHidden(x, true); } }
From source file:coverageqc.functions.MyExcelGenerator.java
public void excelFormator(String sheetName, File variantTsvFile) throws IOException { // String[] headingsArray = tsvHeadingLine.split("\t"); // HashMap<String, Integer> headings = new HashMap<String, Integer>(); // for(int x = 0; x < headingsArray.length; x++) { // headings.put(headingsArray[x].substring(0, headingsArray[x].indexOf("_")), x); // }//from w w w. jav a 2 s .c om XSSFSheet currentSheet = this.workbookcopy.getSheet(sheetName); XSSFPrintSetup printSetup = (XSSFPrintSetup) currentSheet.getPrintSetup(); File xslxTempFile = new File(variantTsvFile.getCanonicalPath() + ".coverage_qc.xlsx"); currentSheet.getHeader().setLeft(xslxTempFile.getName()); currentSheet.getHeader().setRight("DO NOT DISCARD!!! Keep with patient folder."); //in Dr. Carter's VBA was set at points 18 which is .25 inches currentSheet.setMargin(Sheet.RightMargin, .25); currentSheet.setMargin(Sheet.LeftMargin, .25); printSetup.setOrientation(PrintOrientation.LANDSCAPE); //NOTE: setFitWidth doesn't work for columns, ie can't setFitToPageColumns, this //is the best workaround I can do, it will only looked cramped for those with a lot of calls printSetup.setFitWidth((short) 1); printSetup.setFitHeight((short) 3); currentSheet.setRepeatingRows(CellRangeAddress.valueOf("1")); currentSheet.setFitToPage(true); //making it by default not print the fellow's interp currentSheet.getWorkbook().setPrintArea(0, 2, 20, 0, currentSheet.getLastRowNum()); for (int x = 0; x < currentSheet.getRow(0).getPhysicalNumberOfCells(); x++) { currentSheet.autoSizeColumn(x); if (x > 33) { currentSheet.setColumnHidden(x, true); } } currentSheet.setColumnWidth(0, 10000); currentSheet.setColumnWidth(1, 10000); currentSheet.setColumnWidth(2, 10000); // currentSheet.setColumnWidth(this.tsvRearrangeConversion.get(this.originalHeadings.get("Consequence"))+3, 3500); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Gene"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Variant"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Chr"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Coordinate"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Type"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Genotype"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Exonic"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Filters"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Quality"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("GQX"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Alt Variant Freq"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Read Depth"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Alt Read Depth"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Consequence"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Sift"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("PolyPhen"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Allele Freq Global Minor"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Classification"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Inherited From"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Allelic Depths"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Custom Annotation"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Custom Gene Annotation"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Num Transcripts"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Transcript"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("cDNA Position"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("CDS Position"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Protein Position"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Amino Acids"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Codons"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("HGNC"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Transcript HGNC"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Canonical"))+3, true); // //currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Sift"))+3, false); // //currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("PolyPhen"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("ENSP"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("HGVSc"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("HGVSp"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("dbSNP ID"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Ancestral Allele"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Global Minor Allele"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Allele Freq Amr"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Allele Freq Asn"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Allele Freq Af"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Allele Freq Eur"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Allele Freq Evs"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("EVS Coverage"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("EVS Samples"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Conserved Sequence"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("COSMIC ID"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("COSMIC Wildtype"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("COSMIC Allele"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("COSMIC Gene"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("COSMIC Primary Site"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("COSMIC Histology"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("ClinVar Accession"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("ClinVar Ref"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("ClinVar Alleles"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("ClinVar Allele Type"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("ClinVar Significance"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Alternate Alleles"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Google Scholar"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("PubMed"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("UCSC Browser"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("ClinVar RS"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("ClinVar Disease Name"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("ClinVar MedGen"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("ClinVar OMIM"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("ClinVar Orphanet"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("ClinVar GeneReviews"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("ClinVar SnoMedCt ID"))+3, true); }
From source file:de.symeda.sormas.api.doc.DataDictionaryGenerator.java
License:Open Source License
@SuppressWarnings("unchecked") private XSSFSheet createEntitySheet(XSSFWorkbook workbook, Class<? extends EntityDto> entityClass, String i18nPrefix) {/*from ww w. j a v a 2s. c om*/ String name = I18nProperties.getCaption(i18nPrefix); String safeName = WorkbookUtil.createSafeSheetName(name); XSSFSheet sheet = workbook.createSheet(safeName); // Create XSSFTable table = sheet.createTable(); String safeTableName = safeName.replaceAll("\\s", "_"); table.setName(safeTableName); table.setDisplayName(safeTableName); XssfHelper.styleTable(table, 1); int columnCount = EntityColumn.values().length; int rowNumber = 0; // header XSSFRow headerRow = sheet.createRow(rowNumber++); for (EntityColumn column : EntityColumn.values()) { table.addColumn(); String columnCaption = column.toString(); columnCaption = columnCaption.substring(0, 1) + columnCaption.substring(1).toLowerCase(); headerRow.createCell(column.ordinal()).setCellValue(columnCaption); } // column width sheet.setColumnWidth(EntityColumn.FIELD.ordinal(), 256 * 30); sheet.setColumnWidth(EntityColumn.TYPE.ordinal(), 256 * 30); sheet.setColumnWidth(EntityColumn.CAPTION.ordinal(), 256 * 30); sheet.setColumnWidth(EntityColumn.DESCRIPTION.ordinal(), 256 * 60); sheet.setColumnWidth(EntityColumn.REQUIRED.ordinal(), 256 * 10); sheet.setColumnWidth(EntityColumn.DISEASES.ordinal(), 256 * 45); sheet.setColumnWidth(EntityColumn.OUTBREAKS.ordinal(), 256 * 10); CellStyle defaultCellStyle = workbook.createCellStyle(); defaultCellStyle.setWrapText(true); List<Class<Enum<?>>> usedEnums = new ArrayList<Class<Enum<?>>>(); for (Field field : entityClass.getDeclaredFields()) { if (java.lang.reflect.Modifier.isStatic(field.getModifiers())) continue; XSSFRow row = sheet.createRow(rowNumber++); // field name XSSFCell fieldNameCell = row.createCell(EntityColumn.FIELD.ordinal()); fieldNameCell.setCellValue(field.getName()); // value range XSSFCell fieldValueCell = row.createCell(EntityColumn.TYPE.ordinal()); fieldValueCell.setCellStyle(defaultCellStyle); Class<?> fieldType = field.getType(); if (fieldType.isEnum()) { // use enum type name - values are added below // Object[] enumValues = fieldType.getEnumConstants(); // StringBuilder valuesString = new StringBuilder(); // for (Object enumValue : enumValues) { // if (valuesString.length() > 0) // valuesString.append(", "); // valuesString.append(((Enum) enumValue).name()); // } // fieldValueCell.setCellValue(valuesString.toString()); fieldValueCell.setCellValue(fieldType.getSimpleName()); if (!usedEnums.contains(fieldType)) { usedEnums.add((Class<Enum<?>>) fieldType); } } else if (EntityDto.class.isAssignableFrom(fieldType)) { fieldValueCell.setCellValue(fieldType.getSimpleName().replaceAll("Dto", "")); } else if (ReferenceDto.class.isAssignableFrom(fieldType)) { fieldValueCell.setCellValue(fieldType.getSimpleName().replaceAll("Dto", "")); } else if (String.class.isAssignableFrom(fieldType)) { fieldValueCell.setCellValue(I18nProperties.getCaption("text")); } else if (Date.class.isAssignableFrom(fieldType)) { fieldValueCell.setCellValue(I18nProperties.getCaption("date")); } else if (Number.class.isAssignableFrom(fieldType)) { fieldValueCell.setCellValue(I18nProperties.getCaption("number")); } else if (Boolean.class.isAssignableFrom(fieldType) || boolean.class.isAssignableFrom(fieldType)) { fieldValueCell.setCellValue(Boolean.TRUE.toString() + ", " + Boolean.FALSE.toString()); } // caption XSSFCell captionCell = row.createCell(EntityColumn.CAPTION.ordinal()); captionCell.setCellValue(I18nProperties.getPrefixCaption(i18nPrefix, field.getName(), "")); // description XSSFCell descriptionCell = row.createCell(EntityColumn.DESCRIPTION.ordinal()); descriptionCell.setCellStyle(defaultCellStyle); descriptionCell.setCellValue(I18nProperties.getPrefixDescription(i18nPrefix, field.getName(), "")); // required XSSFCell requiredCell = row.createCell(EntityColumn.REQUIRED.ordinal()); if (field.getAnnotation(Required.class) != null) requiredCell.setCellValue(true); // diseases XSSFCell diseasesCell = row.createCell(EntityColumn.DISEASES.ordinal()); diseasesCell.setCellStyle(defaultCellStyle); Diseases diseases = field.getAnnotation(Diseases.class); if (diseases != null) { StringBuilder diseasesString = new StringBuilder(); for (Disease disease : diseases.value()) { if (diseasesString.length() > 0) diseasesString.append(", "); diseasesString.append(disease.toShortString()); } diseasesCell.setCellValue(diseasesString.toString()); } else { diseasesCell.setCellValue("All"); } // outbreak XSSFCell outbreakCell = row.createCell(EntityColumn.OUTBREAKS.ordinal()); if (field.getAnnotation(Outbreaks.class) != null) outbreakCell.setCellValue(true); } AreaReference reference = workbook.getCreationHelper().createAreaReference(new CellReference(0, 0), new CellReference(rowNumber - 1, columnCount - 1)); table.setCellReferences(reference); table.getCTTable().addNewAutoFilter(); for (Class<Enum<?>> usedEnum : usedEnums) { rowNumber = createEnumTable(sheet, rowNumber + 1, usedEnum); } return sheet; }
From source file:de.symeda.sormas.api.doc.UserRightsGenerator.java
License:Open Source License
@Test public void generateUserRights() throws FileNotFoundException, IOException { XSSFWorkbook workbook = new XSSFWorkbook(); // Create User Rights sheet String safeName = WorkbookUtil.createSafeSheetName("User Rights"); XSSFSheet sheet = workbook.createSheet(safeName); // Initialize cell styles // Authorized style XSSFCellStyle authorizedStyle = workbook.createCellStyle(); authorizedStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); authorizedStyle.setFillForegroundColor(new XSSFColor(new Color(0, 153, 0))); authorizedStyle.setBorderBottom(BorderStyle.THIN); authorizedStyle.setBorderLeft(BorderStyle.THIN); authorizedStyle.setBorderTop(BorderStyle.THIN); authorizedStyle.setBorderRight(BorderStyle.THIN); authorizedStyle.setBorderColor(BorderSide.BOTTOM, new XSSFColor(Color.BLACK)); authorizedStyle.setBorderColor(BorderSide.LEFT, new XSSFColor(Color.BLACK)); authorizedStyle.setBorderColor(BorderSide.TOP, new XSSFColor(Color.BLACK)); authorizedStyle.setBorderColor(BorderSide.RIGHT, new XSSFColor(Color.BLACK)); // Unauthorized style XSSFCellStyle unauthorizedStyle = workbook.createCellStyle(); unauthorizedStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); unauthorizedStyle.setFillForegroundColor(new XSSFColor(Color.RED)); unauthorizedStyle.setBorderBottom(BorderStyle.THIN); unauthorizedStyle.setBorderLeft(BorderStyle.THIN); unauthorizedStyle.setBorderTop(BorderStyle.THIN); unauthorizedStyle.setBorderRight(BorderStyle.THIN); unauthorizedStyle.setBorderColor(BorderSide.BOTTOM, new XSSFColor(Color.BLACK)); unauthorizedStyle.setBorderColor(BorderSide.LEFT, new XSSFColor(Color.BLACK)); unauthorizedStyle.setBorderColor(BorderSide.TOP, new XSSFColor(Color.BLACK)); unauthorizedStyle.setBorderColor(BorderSide.RIGHT, new XSSFColor(Color.BLACK)); // Bold style XSSFFont boldFont = workbook.createFont(); boldFont.setBold(true);/* w w w.java 2s .co m*/ XSSFCellStyle boldStyle = workbook.createCellStyle(); boldStyle.setFont(boldFont); int rowCounter = 0; // Header Row headerRow = sheet.createRow(rowCounter++); Cell userRightHeadlineCell = headerRow.createCell(0); userRightHeadlineCell.setCellValue("User Right"); userRightHeadlineCell.setCellStyle(boldStyle); Cell descHeadlineCell = headerRow.createCell(1); descHeadlineCell.setCellValue("Description"); descHeadlineCell.setCellStyle(boldStyle); sheet.setColumnWidth(0, 256 * 35); sheet.setColumnWidth(1, 256 * 50); for (UserRole userRole : UserRole.values()) { String columnCaption = userRole.toString(); Cell headerCell = headerRow.createCell(userRole.ordinal() + 2); headerCell.setCellValue(columnCaption); headerCell.setCellStyle(boldStyle); sheet.setColumnWidth(userRole.ordinal() + 2, 256 * 14); } // User right rows for (UserRight userRight : UserRight.values()) { Row row = sheet.createRow(rowCounter++); // User right name Cell nameCell = row.createCell(0); nameCell.setCellValue(userRight.name()); nameCell.setCellStyle(boldStyle); // User right description Cell descCell = row.createCell(1); descCell.setCellValue(userRight.toString()); // Add styled cells for all user roles for (UserRole userRole : UserRole.values()) { Cell roleRightCell = row.createCell(userRole.ordinal() + 2); if (userRole.hasDefaultRight(userRight)) { roleRightCell.setCellStyle(authorizedStyle); roleRightCell.setCellValue("Yes"); } else { roleRightCell.setCellStyle(unauthorizedStyle); roleRightCell.setCellValue("No"); } } } XssfHelper.addAboutSheet(workbook); String filePath = "src/main/resources/doc/SORMAS_User_Rights.xlsx"; try (OutputStream fileOut = new FileOutputStream(filePath)) { workbook.write(fileOut); } workbook.close(); // Desktop.getDesktop().open(new File(filePath)); }
From source file:DSC.AccountantReport.java
private static void createExcelReport() { XSSFWorkbook workbook = new XSSFWorkbook(); clients.sort(new Comparator<Client>() { @Override/* w w w .j a v a 2 s . c o m*/ public int compare(Client o1, Client o2) { return (o1.getSurname() + " " + o1.getName()).compareTo(o2.getSurname() + " " + o2.getName()); } }); int lastIndex = 0; for (int letter = 0; letter < 26; letter++) { XSSFSheet sheet = workbook.createSheet("AccountReport " + (char) (65 + letter)); Map<String, Object[]> data = new TreeMap<>(); data.put("1", new Object[] { "Doorstep Chef Accountant Sheet", "", "", "", "", "", "", "Week: " + DriverReport.returnWeekInt(), "", "" }); data.put("2", new Object[] { "", "", "", "", "", "", "", "", "", "" }); data.put("3", new Object[] { "Customer", "Contact", "Fam", "4Day", "Mthly", "EFT", "Cash", "Date Paid", "Stay", "Comments" }); int reduction = 0; for (int i = 0; i < clients.size(); i++) { Client client = clients.get(i); if (client.getSurname().toUpperCase().charAt(0) == (char) (65 + letter)) { data.put((i + 4 - reduction) + "", new Object[] { client.getName() + " " + client.getSurname(), client.getContactNumber().substring(0, 3) + " " + client.getContactNumber().substring(3, 6) + " " + client.getContactNumber().substring(6, 10), client.getAdditionalInfo(), "", "", "", "", "", "", "" }); } else { reduction++; } } Set<String> keySet = data.keySet(); int totalSize = 34900; int longestCustomer = 0; for (int key = 1; key < keySet.size() + 1; key++) { Row row = sheet.createRow(key - 1); Object[] arr = data.get(key + ""); for (int i = 0; i < arr.length; i++) { Cell cell = row.createCell(i); cell.setCellValue((String) arr[i]); if (i == 0 && !(key + "").equals("1") && longestCustomer < ((String) arr[i]).length()) { longestCustomer = ((String) arr[i]).length(); } XSSFCellStyle borderStyle = workbook.createCellStyle(); if (!((key + "").equals("1") || (key + "").equals("2"))) { borderStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex()); borderStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex()); borderStyle.setTopBorderColor(IndexedColors.BLACK.getIndex()); borderStyle.setRightBorderColor(IndexedColors.BLACK.getIndex()); if ((key + "").equals("3")) { borderStyle.setBorderBottom(XSSFCellStyle.BORDER_MEDIUM); borderStyle.setBorderLeft(XSSFCellStyle.BORDER_MEDIUM); borderStyle.setBorderTop(XSSFCellStyle.BORDER_MEDIUM); borderStyle.setBorderRight(XSSFCellStyle.BORDER_MEDIUM); borderStyle.setAlignment(HorizontalAlignment.CENTER); borderStyle.setFillPattern(XSSFCellStyle.LESS_DOTS); borderStyle.setFillBackgroundColor(IndexedColors.GREY_50_PERCENT.getIndex()); XSSFFont font = workbook.createFont(); font.setColor(IndexedColors.WHITE.getIndex()); font.setBold(true); borderStyle.setFont(font); } else { if (i != 0) { borderStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN); } else { borderStyle.setBorderLeft(XSSFCellStyle.BORDER_MEDIUM); } if (i != 9) { borderStyle.setBorderRight(XSSFCellStyle.BORDER_THIN); } else { borderStyle.setBorderRight(XSSFCellStyle.BORDER_MEDIUM); } if ((Integer.parseInt((key + ""))) != keySet.size()) { borderStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN); } else { borderStyle.setBorderBottom(XSSFCellStyle.BORDER_MEDIUM); } borderStyle.setBorderTop(XSSFCellStyle.BORDER_THIN); } } else { if (i == 7) { borderStyle.setAlignment(HorizontalAlignment.RIGHT); } XSSFFont font = workbook.createFont(); font.setFontName("Calibri"); font.setFontHeightInPoints((short) 13); font.setBold(true); borderStyle.setFont(font); } cell.setCellStyle(borderStyle); } if (key == 1) { sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 6)); sheet.addMergedRegion(new CellRangeAddress(0, 0, 7, 9)); } } sheet.setColumnWidth(0, (longestCustomer + 1) * 240); sheet.setColumnWidth(1, 11 * 240); sheet.setColumnWidth(2, 5 * 240); sheet.setColumnWidth(3, 5 * 240); sheet.setColumnWidth(4, 5 * 240); sheet.setColumnWidth(5, 5 * 240); sheet.setColumnWidth(6, 5 * 240); sheet.setColumnWidth(7, 10 * 240); sheet.setColumnWidth(8, 5 * 240); for (int i = 0; i < 9; i++) { totalSize -= sheet.getColumnWidth(i); } sheet.setColumnWidth(9, totalSize); Row rowDate = sheet.createRow(keySet.size() + 1); Cell cell = rowDate.createCell(0); SimpleDateFormat sf = new SimpleDateFormat("EEE MMM yyyy HH:mm:ss"); cell.setCellValue(sf.format(Calendar.getInstance().getTime())); XSSFCellStyle cellStyle = workbook.createCellStyle(); cellStyle.setAlignment(XSSFCellStyle.ALIGN_RIGHT); cell.setCellStyle(cellStyle); sheet.addMergedRegion(new CellRangeAddress(keySet.size() + 1, keySet.size() + 1, 0, 9)); } try { workbook.write(excelOut); excelOut.close(); System.out.println("Done - Accountant"); if (!(DSC_Main.generateAllReports)) { accountLoadObj.setVisible(false); accountLoadObj.dispose(); JOptionPane.showMessageDialog(null, "AccountReports Succesfully Generated", "Success", JOptionPane.INFORMATION_MESSAGE); } else { DSC_Main.reportsDone++; if (DSC_Main.reportsDone == DSC_Main.TOTAL_REPORTS) { DSC_Main.reportsDone(); } } } catch (IOException io) { accountLoadObj.setVisible(false); accountLoadObj.dispose(); JOptionPane.showMessageDialog(null, "An error occured\nCould not create AccountReport", "Error", JOptionPane.ERROR_MESSAGE); System.err.println("Error - Could not create new AccountReport: "); io.printStackTrace(); } }
From source file:DSC.ChefReport.java
public static void processChefReport() throws IOException { boolean firstFamEntry = true; String list[] = { "Standard", "Low Carb", "Kiddies" }; int excelNumber = 0; int sheetNumber = 0; for (mealCounter = 0; mealCounter < 3; mealCounter++) { workbook = new XSSFWorkbook(); sheetNumber = 0;//www . ja va 2s .c o m for (String currRoute : allRoutes) { Map<String, Object[]> data = new TreeMap<>(); data.put(0 + "", new String[] { "Doorstep Chef - Chef Report " + DriverReport.returnWeekInt(), "", "", "Meal Type : " + list[mealCounter] + " " + " " + "Route: " + sheetNumber }); data.put(1 + "", new String[] { "", "", "", "", "", "", "" }); data.put(2 + "", new String[] { "Family Size", "Quantity", "Allergies", "Exclusions" }); int counter = 3; XSSFSheet sheet = workbook.createSheet("ChefReports Route - " + sheetNumber); int rowNum = 0; int cellNum = 0; String familysize = ""; ArrayList<Meal> mealList = new ArrayList<>(); boolean hasValue = false; for (Order order : orders) { for (Meal meal : order.getMeals()) { if (meal.getMealType().equals(list[mealCounter]) && order.getRoute().equals(currRoute)) { mealList.add(meal); hasValue = true; } } } mealList.sort(new Comparator<Meal>() { @Override public int compare(Meal o1, Meal o2) { if (o1.getQuantity() < o2.getQuantity()) { return -1; } else if (o1.getQuantity() > o2.getQuantity()) { return 1; } else { return 0; } } }); if (hasValue) { int currQuantity = 0; int bulk = 0; boolean firstIterate = true; for (Meal meal : mealList) { if (meal.getQuantity() != currQuantity) { if (!firstIterate && bulk != 0) { data.put(counter + "", new String[] { familysize + " Normal *", bulk + "", "", "" }); bulk = 0; counter++; } firstIterate = false; switch (meal.getQuantity()) { case 1: familysize = "Single"; break; case 2: familysize = "Couple"; break; case 3: familysize = "Three"; break; case 4: familysize = "Four"; break; case 5: familysize = "Five"; break; case 6: familysize = "Six"; break; default: familysize = "Extra"; } currQuantity = meal.getQuantity(); } if (meal.getAllergies().equals("-") && meal.getExclusions().equals("-")) { bulk++; } else { data.put(counter + "", new String[] { familysize + " Meal", meal.getQuantity() + "", meal.getAllergies(), meal.getExclusions() }); counter++; } } } Set<String> keySet = data.keySet(); Object[] keys = data.keySet().toArray(); Arrays.sort(keys); ArrayList<Object> keyList = new ArrayList(); int longestCustomer = 5; int totalWidth = 50000; boolean isBulk = false; for (Object key : keys) { keyList.add(data.get(key)); } for (int keyIterate = 0; keyIterate < keySet.size(); keyIterate++) { Row row = sheet.createRow(rowNum); Object[] arr = data.get(keyIterate + ""); for (int i = 0; i < arr.length; i++) { XSSFFont font = workbook.createFont(); Cell cell = row.createCell(i); cell.setCellValue((String) arr[i]); XSSFCellStyle borderStyle = workbook.createCellStyle(); if ((keyIterate + "").equals("0") || (keyIterate + "").equals("1")) { font.setFontName("Calibri"); font.setFontHeightInPoints((short) 13); font.setBold(true); borderStyle.setFont(font); borderStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex()); borderStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex()); borderStyle.setTopBorderColor(IndexedColors.BLACK.getIndex()); borderStyle.setRightBorderColor(IndexedColors.BLACK.getIndex()); borderStyle.setAlignment(HorizontalAlignment.LEFT); } else { borderStyle.setBorderBottom(BorderStyle.THIN); borderStyle.setBorderTop(BorderStyle.THIN); borderStyle.setBorderLeft(BorderStyle.THIN); borderStyle.setBorderRight(BorderStyle.THIN); if ((arr[0] + "").contains("*")) { isBulk = true; borderStyle.setBorderBottom(BorderStyle.MEDIUM); } } if ((keyIterate + "").equals("2")) { borderStyle.setBorderBottom(XSSFCellStyle.BORDER_MEDIUM); borderStyle.setBorderLeft(XSSFCellStyle.BORDER_MEDIUM); borderStyle.setBorderTop(XSSFCellStyle.BORDER_MEDIUM); borderStyle.setBorderRight(XSSFCellStyle.BORDER_MEDIUM); borderStyle.setAlignment(HorizontalAlignment.CENTER); borderStyle.setFillPattern(XSSFCellStyle.LESS_DOTS); borderStyle.setFillBackgroundColor(IndexedColors.GREY_50_PERCENT.getIndex()); XSSFFont font2 = workbook.createFont(); font2.setColor(IndexedColors.WHITE.getIndex()); borderStyle.setFont(font2); } cell.setCellStyle(borderStyle); } rowNum++; cellNum++; } sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 2)); for (int i = 0; i < 5; i++) { switch (i) { case 1: sheet.setColumnWidth(i, 3000); break; case 2: sheet.setColumnWidth(i, 8000); break; default: sheet.setColumnWidth(i, 4000); break; } if (i == 3) { sheet.setColumnWidth(i, 8000); } } Row rowDate = sheet.createRow(keySet.size() + 1); Cell cell = rowDate.createCell(0); SimpleDateFormat sf = new SimpleDateFormat("EEE MMM yyyy HH:mm:ss"); cell.setCellValue(sf.format(Calendar.getInstance().getTime())); XSSFCellStyle cellStyle = workbook.createCellStyle(); cellStyle.setAlignment(XSSFCellStyle.ALIGN_RIGHT); cell.setCellStyle(cellStyle); sheet.addMergedRegion(new CellRangeAddress(keySet.size() + 1, keySet.size() + 1, 0, 3)); sheetNumber++; creatSheet(list[mealCounter], workbook); excelNumber++; if (excelNumber == allRoutes.size()) { if (!(DSC_Main.generateAllReports)) { chefLoadingObj.setVisible(false); chefLoadingObj.dispose(); JOptionPane.showMessageDialog(null, "Chef Reports Successfully Generated."); } } } } }