List of usage examples for org.apache.poi.ss.util CellRangeAddress valueOf
public static CellRangeAddress valueOf(String ref)
From source file:com.wantdo.stat.excel.poi_src.ConditionalFormats.java
License:Apache License
/** * You can use Excel conditional formatting to shade bands of rows on the worksheet. * In this example, 3 rows are shaded light grey, and 3 are left with no shading. * In the MOD function, the total number of rows in the set of banded rows (6) is entered. *///ww w. j a v a 2 s . c om static void shadeBands(Sheet sheet) { SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting(); ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("MOD(ROW(),6)<3"); PatternFormatting fill1 = rule1.createPatternFormatting(); fill1.setFillBackgroundColor(IndexedColors.GREY_25_PERCENT.index); fill1.setFillPattern(PatternFormatting.SOLID_FOREGROUND); CellRangeAddress[] regions = { CellRangeAddress.valueOf("A1:Z100") }; sheetCF.addConditionalFormatting(regions, rule1); sheet.createRow(0).createCell(1).setCellValue("Shade Bands of Rows"); sheet.createRow(1).createCell(1).setCellValue("Condition: Formula Is =MOD(ROW(),6)<2 (Light Grey Fill)"); }
From source file:com.wantdo.stat.excel.poi_src.SSPerformanceTest.java
License:Apache License
public static void main(String[] args) { if (args.length != 4) usage("need four command arguments"); String type = args[0];//from w w w . j a va 2 s. c om long timeStarted = System.currentTimeMillis(); Workbook workBook = createWorkbook(type); boolean isHType = workBook instanceof HSSFWorkbook; int rows = parseInt(args[1], "Failed to parse rows value as integer"); int cols = parseInt(args[2], "Failed to parse cols value as integer"); boolean saveFile = parseInt(args[3], "Failed to parse saveFile value as integer") != 0; Map<String, CellStyle> styles = createStyles(workBook); Sheet sheet = workBook.createSheet("Main Sheet"); Cell headerCell = sheet.createRow(0).createCell(0); headerCell.setCellValue("Header text is spanned across multiple cells"); headerCell.setCellStyle(styles.get("header")); sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$F$1")); int sheetNo = 0; int rowIndexInSheet = 1; double value = 0; Calendar calendar = Calendar.getInstance(); for (int rowIndex = 0; rowIndex < rows; rowIndex++) { if (isHType && sheetNo != rowIndex / 0x10000) { sheet = workBook.createSheet("Spillover from sheet " + (++sheetNo)); headerCell.setCellValue("Header text is spanned across multiple cells"); headerCell.setCellStyle(styles.get("header")); sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$F$1")); rowIndexInSheet = 1; } Row row = sheet.createRow(rowIndexInSheet); for (int colIndex = 0; colIndex < cols; colIndex++) { Cell cell = row.createCell(colIndex); String address = new CellReference(cell).formatAsString(); switch (colIndex) { case 0: // column A: default number format cell.setCellValue(value++); break; case 1: // column B: #,##0 cell.setCellValue(value++); cell.setCellStyle(styles.get("#,##0.00")); break; case 2: // column C: $#,##0.00 cell.setCellValue(value++); cell.setCellStyle(styles.get("$#,##0.00")); break; case 3: // column D: red bold text on yellow background cell.setCellValue(address); cell.setCellStyle(styles.get("red-bold")); break; case 4: // column E: boolean // TODO booleans are shown as 1/0 instead of TRUE/FALSE cell.setCellValue(rowIndex % 2 == 0); break; case 5: // column F: date / time cell.setCellValue(calendar); cell.setCellStyle(styles.get("m/d/yyyy")); calendar.roll(Calendar.DAY_OF_YEAR, -1); break; case 6: // column F: formula // TODO formulas are not yet supported in SXSSF //cell.setCellFormula("SUM(A" + (rowIndex+1) + ":E" + (rowIndex+1)+ ")"); //break; default: cell.setCellValue(value++); break; } } rowIndexInSheet++; } if (saveFile) { String fileName = type + "_" + rows + "_" + cols + "." + getFileSuffix(args[0]); try { FileOutputStream out = new FileOutputStream(fileName); workBook.write(out); out.close(); } catch (IOException ioe) { System.err .println("Error: failed to write to file \"" + fileName + "\", reason=" + ioe.getMessage()); } } long timeFinished = System.currentTimeMillis(); System.out.println("Elapsed " + (timeFinished - timeStarted) / 1000 + " seconds"); }
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 ww w . ja va 2 s. c o 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); // }//w ww . ja v a 2 s .c o m 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:das.pf.io.IOExcel.java
License:Open Source License
public boolean processFile(Path input, boolean openFile) { boolean result = false; int endRow = 0; try {/*from w ww .j a v a 2s . co m*/ updateMessages(String.format("Inicializando el documento: %s", input.toString())); Path copy = createCopy(input); if (copy != null && Files.exists(copy, LinkOption.NOFOLLOW_LINKS)) { Workbook workbook = WorkbookFactory.create(copy.toFile()); Sheet sheet = workbook.getSheetAt(0); Sheet newSheet = workbook.createSheet("Procesado"); workbook.setSheetName(0, "Crudo"); endRow = getLasRow(sheet); // seccion para la creacion de los encabezados updateMessages("Creando la cabecera de los datos"); createHeaderData(newSheet, getCellStyleHeaderData(workbook)); // seccion para los values USD updateMessages( String.format("Creando la cabecera para los: 'values USD' %s", TypeUnits.MTH.name())); createHeaderValues(newSheet, sheet, getCellStyleValues(workbook, TypeValues.VALUES, TypeUnits.MTH), 11, 35, 14); updateMessages( String.format("Creando la cabecera para los: 'values USD' %s", TypeUnits.QRT.name())); createHeaderValues(newSheet, sheet, getCellStyleValues(workbook, TypeValues.VALUES, TypeUnits.QRT), 35, 49, 38); updateMessages( String.format("Creando la cabecera para los: 'values USD' %s", TypeUnits.YTD.name())); createHeaderValues(newSheet, sheet, getCellStyleValues(workbook, TypeValues.VALUES, TypeUnits.YTD), 49, 54, 52); updateMessages( String.format("Creando la cabecera para los: 'values USD' %s", TypeUnits.MAT.name())); createHeaderValues(newSheet, sheet, getCellStyleValues(workbook, TypeValues.VALUES, TypeUnits.MAT), 54, 59, 57); // seccion para los values units updateMessages( String.format("Creando la cabecera para los: 'values Units' %s", TypeUnits.MTH.name())); createHeaderValues(newSheet, sheet, getCellStyleValues(workbook, TypeValues.UNITS, TypeUnits.MTH), 59, 83, 63); updateMessages( String.format("Creando la cabecera para los: 'values Units' %s", TypeUnits.QRT.name())); createHeaderValues(newSheet, sheet, getCellStyleValues(workbook, TypeValues.UNITS, TypeUnits.QRT), 83, 97, 87); updateMessages( String.format("Creando la cabecera para los: 'values Units' %s", TypeUnits.YTD.name())); createHeaderValues(newSheet, sheet, getCellStyleValues(workbook, TypeValues.UNITS, TypeUnits.YTD), 97, 102, 101); updateMessages( String.format("Creando la cabecera para los: 'values Units' %s", TypeUnits.MAT.name())); createHeaderValues(newSheet, sheet, getCellStyleValues(workbook, TypeValues.UNITS, TypeUnits.MAT), 102, 107, 106); // // // seccion para los values units standars updateMessages(String.format("Creando la cabecera para los: 'values Standard Units' %s", TypeUnits.MTH.name())); createHeaderValues(newSheet, sheet, getCellStyleValues(workbook, TypeValues.U_E, TypeUnits.MTH), 107, 131, 112); updateMessages(String.format("Creando la cabecera para los: 'values Standard Units' %s", TypeUnits.QRT.name())); createHeaderValues(newSheet, sheet, getCellStyleValues(workbook, TypeValues.U_E, TypeUnits.QRT), 131, 145, 136); updateMessages(String.format("Creando la cabecera para los: 'values Standard Units' %s", TypeUnits.YTD.name())); createHeaderValues(newSheet, sheet, getCellStyleValues(workbook, TypeValues.U_E, TypeUnits.YTD), 145, 150, 150); updateMessages(String.format("Creando la cabecera para los: 'values Standard Units' %s", TypeUnits.MAT.name())); createHeaderValues(newSheet, sheet, getCellStyleValues(workbook, TypeValues.U_E, TypeUnits.MAT), 150, 155, 155); // fin de la seccion para la creacion de los encabezados // seccion para escribir los CT updateMessages("Escribiendo las clases terampeuticas..."); writeCT(newSheet, sheet, 13, endRow); // seccion para escribir los productos updateMessages("Escribiendo los productos..."); writeProducts(newSheet, sheet, 14); // seccion para escribir los otros valores updateMessages("Escribiendo datos en general..."); writerOthersValues(newSheet, sheet, 15); // seccion para escribir los key competitors updateMessages("Escribiendo los Key Competitors..."); writeKeyCompetitors(newSheet, 3, endRow, 9, 5); // seccion para escribir el pais XmlContry contry = writeContries(newSheet, 3, 0, input); // seccion para escribir la region writeRegions(contry, newSheet, 3, 1); for (int i = 0; i < 155; i++) newSheet.autoSizeColumn(i); newSheet.setAutoFilter(CellRangeAddress.valueOf(String.format("A3:K%d", newSheet.getLastRowNum()))); String pathOutput = "DAS PF - " + input.getFileName().toString(); try (FileOutputStream fos = new FileOutputStream( Paths.get(this.out.toString(), pathOutput).toFile())) { updateMessages(String.format("Guadando el trabajo en la ruta: '%s'", Paths.get(this.out.toString(), pathOutput))); workbook.write(fos); } catch (IOException ex) { Logger.getLogger(IOExcel.class.getName()).log(Level.SEVERE, null, ex); } finally { workbook.close(); } if (openFile && Desktop.isDesktopSupported() && Desktop.getDesktop().isSupported(Desktop.Action.OPEN)) Desktop.getDesktop().open(Paths.get(this.out.toString(), pathOutput).toFile()); result = true; newSheet = null; sheet = null; workbook = null; Files.delete(copy); } } catch (IOException | InvalidFormatException ex) { Logger.getLogger(IOExcel.class.getName()).log(Level.SEVERE, null, ex); Util.showException("No se pudo guardar el archivo", ex); } return result; }
From source file:das.pf.io.IOExcel.java
License:Open Source License
public boolean consolidateFiles() { boolean result = false; AtomicInteger rowIndex = new AtomicInteger(3); String outputName = "DAS - " + this.out.getFileName().toString() + " consolidate.xlsx"; Workbook consolidateWb = new XSSFWorkbook(); try {/*w w w .j a va2s .com*/ Sheet sheetConsolidate = consolidateWb.createSheet("Consolidado"); Files.list(this.out).filter((p) -> { String name = p.toString(); return (name.endsWith(".xlsx") || name.endsWith(".xls")) && !p.getFileName().toString().equals(outputName); }).sorted((p1, p2) -> { String acronym = getAcromynName(p1); String acronym2 = getAcromynName(p2); return acronym.compareToIgnoreCase(acronym2); }).forEach(p -> { try { Workbook wb = WorkbookFactory.create(p.toFile()); Sheet sheet = wb.getSheet("Procesado"); updateMessages(String.format("Copiando los datos del archivo: %s dentro del archivo: %s", p.toString(), outputName)); for (int index = 3; index < sheet.getLastRowNum(); index++) { Row row = sheet.getRow(index); Row r = sheetConsolidate.createRow(rowIndex.getAndIncrement()); row.forEach(c -> { if (c != null && c.getCellType() != Cell.CELL_TYPE_BLANK) { final Cell cell = r.createCell(c.getColumnIndex(), c.getCellType()); updateMessages( String.format("Copiando los datos de la fila: #%d", c.getRowIndex())); switch (c.getCellType()) { case Cell.CELL_TYPE_NUMERIC: cell.setCellValue(c.getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: cell.setCellValue(c.getRichStringCellValue()); break; } } }); row = null; } sheet = null; wb.close(); wb = null; } catch (IOException | InvalidFormatException | EncryptedDocumentException ex) { Logger.getLogger(IOExcel.class.getName()).log(Level.SEVERE, null, ex); } }); Path path = Files.list(this.out).filter((p) -> { String name = p.toString(); return (name.endsWith(".xlsx") || name.endsWith(".xls")) && !p.getFileName().toString().equals(outputName); }).findFirst().get(); createHeadersConsolidateFile(consolidateWb, path); for (int i = 0; i < 155; i++) sheetConsolidate.autoSizeColumn(i); sheetConsolidate.setAutoFilter( CellRangeAddress.valueOf(String.format("A3:K%d", sheetConsolidate.getLastRowNum()))); try (FileOutputStream fos = new FileOutputStream(Paths.get(out.toString(), outputName).toFile())) { updateMessages(String.format("Guadando el trabajo en la ruta: '%s'", Paths.get(this.out.toString(), outputName))); consolidateWb.write(fos); result = true; } catch (IOException ex) { Logger.getLogger(IOExcel.class.getName()).log(Level.SEVERE, "Ocurrio un error al intenatr guardar el archivo consolidado", ex); } finally { consolidateWb.close(); } } catch (IOException ex) { Logger.getLogger(IOExcel.class.getName()).log(Level.SEVERE, null, ex); } consolidateWb = null; return result; }
From source file:de.iteratec.iteraplan.businesslogic.exchange.elasticExcel.export.template.AbstractSheetGenerator.java
License:Open Source License
/** * Creates the header for the specified sheet. *//* w w w . jav a 2s . c o m*/ private void createSheetHeadline() { Sheet sheet = sheetContext.getSheet(); sheet.createFreezePane(0, FIRST_DATA_ROW_NO); Cell sheetHeaderCell = sheet.createRow(0).createCell(0); sheetHeaderCell.setCellStyle(wbContext.getStyles().get(IteraExcelStyle.HEADER)); sheetHeaderCell.setCellValue(createCompleteSheetName()); sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$D$1")); // headline }
From source file:de.iteratec.iteraplan.businesslogic.exchange.elasticExcel.export.template.AbstractSheetGenerator.java
License:Open Source License
/** * add type info to the sheet: description (visible) and type definition string (hidden) *///from w ww . j a v a 2 s.c om private void createSheetHeader() { Cell descrCell = descriptionRow.createCell(0); // make one larger cell for description, and set line wrapping. // problem: Cell height is not set automatically. // sheetContext.getSheet().addMergedRegion(CellRangeAddress.valueOf("$A$2:$D$2")); // description // CellStyle style = descrCell.getCellStyle(); // style.setWrapText(true); // descrCell.setCellStyle(style); descrCell.setCellValue(typeExpression.getDescription()); Cell typeCell = sheetTypeRow.createCell(0); typeCell.setCellValue(createTypeString()); typeCell.setCellStyle(wbContext.getStyles().get(IteraExcelStyle.DATA_HIDDEN)); String cellRange = String.format("$A$%d:$D$%d", Integer.valueOf((sheetTypeRow.getRowNum() + 1)), Integer.valueOf(sheetTypeRow.getRowNum() + 1)); sheetContext.getSheet().addMergedRegion(CellRangeAddress.valueOf(cellRange)); }
From source file:de.iteratec.iteraplan.businesslogic.exchange.elasticExcel.export.template.TypeSheetGenerator.java
License:Open Source License
private void createNameListFormula() { ColumnContext nameColumn = sheetContext.getColumnByPersistentName(SheetContext.NAME_COLUMN); if (nameColumn != null) { Name rangeName = wbContext.getWb().createName(); rangeName.setNameName(ExcelGeneratorUtils.createNameForFormulaAllNames(getTypeExpression())); CellRangeAddress nameRange = CellRangeAddress.valueOf(nameColumn.getHeaderCellReference()); nameRange.setFirstRow(sheetContext.getFirstDataRowNumber()); nameRange.setLastRow(60000); // high constant last row number, so that manually added row entries will be considered as well, not only generated rows String nameRangeFormulaBase = "OFFSET({0},0,0,SUMPRODUCT(--({0}<>\"\")),1)"; // shows only non-empty entries in the name list String nameRangeFormula = MessageFormat.format(nameRangeFormulaBase, nameRange.formatAsString(sheetContext.getSheetName(), true)); rangeName.setRefersToFormula(nameRangeFormula); }//from ww w .jav a 2s .c o m }
From source file:demo.poi.LoanCalculator.java
License:Apache License
public static void main(String[] args) throws Exception { Workbook wb;//ww w .j a v a 2s . c o m if (args.length > 0 && args[0].equals("-xls")) wb = new HSSFWorkbook(); else wb = new HSSFWorkbook(); Map<String, CellStyle> styles = createStyles(wb); Sheet sheet = wb.createSheet("Loan Calculator"); sheet.setPrintGridlines(false); sheet.setDisplayGridlines(false); PrintSetup printSetup = sheet.getPrintSetup(); printSetup.setLandscape(true); sheet.setFitToPage(true); sheet.setHorizontallyCenter(true); sheet.setColumnWidth(0, 3 * 256); sheet.setColumnWidth(1, 3 * 256); sheet.setColumnWidth(2, 11 * 256); sheet.setColumnWidth(3, 14 * 256); sheet.setColumnWidth(4, 14 * 256); sheet.setColumnWidth(5, 14 * 256); sheet.setColumnWidth(6, 14 * 256); createNames(wb); Row titleRow = sheet.createRow(0); titleRow.setHeightInPoints(35); for (int i = 1; i <= 7; i++) { titleRow.createCell(i).setCellStyle(styles.get("title")); } Cell titleCell = titleRow.getCell(2); titleCell.setCellValue("Simple Loan Calculator"); sheet.addMergedRegion(CellRangeAddress.valueOf("$C$1:$H$1")); Row row = sheet.createRow(2); Cell cell = row.createCell(4); cell.setCellValue("Enter values"); cell.setCellStyle(styles.get("item_right")); row = sheet.createRow(3); cell = row.createCell(2); cell.setCellValue("Loan amount"); cell.setCellStyle(styles.get("item_left")); cell = row.createCell(4); cell.setCellStyle(styles.get("input_$")); cell.setAsActiveCell(); row = sheet.createRow(4); cell = row.createCell(2); cell.setCellValue("Annual interest rate"); cell.setCellStyle(styles.get("item_left")); cell = row.createCell(4); cell.setCellStyle(styles.get("input_%")); row = sheet.createRow(5); cell = row.createCell(2); cell.setCellValue("Loan period in years"); cell.setCellStyle(styles.get("item_left")); cell = row.createCell(4); cell.setCellStyle(styles.get("input_i")); row = sheet.createRow(6); cell = row.createCell(2); cell.setCellValue("Start date of loan"); cell.setCellStyle(styles.get("item_left")); cell = row.createCell(4); cell.setCellStyle(styles.get("input_d")); row = sheet.createRow(8); cell = row.createCell(2); cell.setCellValue("Monthly payment"); cell.setCellStyle(styles.get("item_left")); cell = row.createCell(4); cell.setCellFormula("IF(Values_Entered,Monthly_Payment,\"\")"); cell.setCellStyle(styles.get("formula_$")); row = sheet.createRow(9); cell = row.createCell(2); cell.setCellValue("Number of payments"); cell.setCellStyle(styles.get("item_left")); cell = row.createCell(4); cell.setCellFormula("IF(Values_Entered,Loan_Years*12,\"\")"); cell.setCellStyle(styles.get("formula_i")); row = sheet.createRow(10); cell = row.createCell(2); cell.setCellValue("Total interest"); cell.setCellStyle(styles.get("item_left")); cell = row.createCell(4); cell.setCellFormula("IF(Values_Entered,Total_Cost-Loan_Amount,\"\")"); cell.setCellStyle(styles.get("formula_$")); row = sheet.createRow(11); cell = row.createCell(2); cell.setCellValue("Total cost of loan"); cell.setCellStyle(styles.get("item_left")); cell = row.createCell(4); cell.setCellFormula("IF(Values_Entered,Monthly_Payment*Number_of_Payments,\"\")"); cell.setCellStyle(styles.get("formula_$")); // Write the output to a file String file = "target/loan-calculator.xls"; FileOutputStream out = new FileOutputStream(file); wb.write(out); out.close(); }