List of usage examples for org.apache.poi.xssf.usermodel XSSFSheet getWorkbook
@Override
public XSSFWorkbook getWorkbook()
From source file:Business.ExcelReportCreator.java
public static int create(EcoSystem system) { // Steps:- // Create a Workbook. // Create a Sheet. // Repeat the following steps until all data is processed: // Create a Row. // Create Cells in a Row. Apply formatting using CellStyle. // Write to an OutputStream. // Close the output stream. XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet sheet = workbook.createSheet("Customer Details"); //Custom font style for header CellStyle cellStyle = sheet.getWorkbook().createCellStyle(); Font font = sheet.getWorkbook().createFont(); font.setBold(true);/*w w w . j a va 2 s. c o m*/ cellStyle.setFont(font); int rowCount = 0; int columnCount1 = 0; //Creating header row String s[] = { "CUSTOMER ID", "CUSTOMER NAME", "CONTACT NO.", "EMAIL ID", "USAGE(Gallons)", "BILLING DATE", "TOTAL BILL($)" }; Row row1 = sheet.createRow(++rowCount); for (String s1 : s) { Cell header = row1.createCell(++columnCount1); header.setCellValue(s1); header.setCellStyle(cellStyle); } for (Network network : system.getNetworkList()) { for (Enterprise enterprise : network.getEnterpriseDirectory().getEnterpriseList()) { if (enterprise instanceof WaterEnterprise) { for (Organization organization : enterprise.getOrganizationDirectory().getOrganizationList()) { if (organization instanceof CustomerOrganization) { for (Employee employee : organization.getEmployeeDirectory().getEmployeeList()) { Customer customer = (Customer) employee; Row row = sheet.createRow(++rowCount); int columnCount = 0; for (int i = 0; i < 7; i++) { Cell cell = row.createCell(++columnCount); if (i == 0) { cell.setCellValue(customer.getId()); } else if (i == 1) { cell.setCellValue(customer.getName()); } else if (i == 2) { cell.setCellValue(customer.getContactNo()); } else if (i == 3) { cell.setCellValue(customer.getEmailId()); } else if (i == 4) { cell.setCellValue(customer.getTotalUsageVolume()); } else if (i == 5) { if (customer.getBillingDate() != null) cell.setCellValue(String.valueOf(customer.getBillingDate())); else cell.setCellValue("Bill Not yet available"); } else if (i == 6) { if (customer.getTotalBill() != 0) cell.setCellValue(customer.getTotalBill()); else cell.setCellValue("Bill Not yet available"); } } } } } } } } try (FileOutputStream outputStream = new FileOutputStream("Customer_details.xlsx")) { workbook.write(outputStream); } catch (FileNotFoundException ex) { JOptionPane.showMessageDialog(null, "File not found"); return 0; } catch (IOException ex) { JOptionPane.showMessageDialog(null, "IOException"); return 0; } return 1; }
From source file:com.griffinslogistics.document.excel.CMRGenerator.java
private static int generatePoint20Till24(XSSFSheet sheet, Map<String, CellStyle> styles, int currentRow, Pulsiodetails pulsiodetails) {//from w w w .jav a 2 s . c o m currentRow++; String mergeString; for (int i = currentRow; i < currentRow + 2; i++) { Row row = sheet.createRow(i); mergeString = String.format("$B$%s:$I$%s", i + 1, i + 1); sheet.addMergedRegion(CellRangeAddress.valueOf(mergeString)); for (int j = 1; j < 9; j++) { row.createCell(j) .setCellStyle(styles.get(i == currentRow ? LABEL_MIDDLE_STYLE : LABEL_BOTTOM_STYLE)); } } for (int i = currentRow + 2; i < currentRow + 16; i++) { Row row = sheet.createRow(i); mergeString = String.format("$D$%s:$I$%s", i + 1, i + 1); sheet.addMergedRegion(CellRangeAddress.valueOf(mergeString)); for (int j = 1; j < 9; j++) { row.createCell(j).setCellStyle(styles.get(LABEL_MIDDLE_STYLE)); } } currentRow++; Row row45 = sheet.getRow(currentRow); row45.setHeightInPoints(30); row45.getCell(1).setCellValue(LABEL_POINT_20); currentRow++; Row row46 = sheet.getRow(currentRow); Cell establishedInCell = row46.getCell(1); establishedInCell.setCellValue(LABEL_ESTABLISHED_IN); Cell establishedOnCell = row46.getCell(2); establishedOnCell.setCellValue(LABEL_ESTABLISHED_ON); Cell goodsDeliveredCell = row46.getCell(3); goodsDeliveredCell.setCellValue(LABEL_GOODS_RECEIVED); currentRow++; Row row47 = sheet.getRow(currentRow); row47.getCell(3).setCellValue(LABEL_TIME_OF_ARRIVAL); currentRow++; Row row48 = sheet.getRow(currentRow); Cell cityCell = row48.getCell(1); cityCell.setCellStyle(styles.get(CONTENT_MIDDLE_STYLE)); cityCell.setCellValue("Sofia, Bulgaria"); Cell dateCell = row48.getCell(2); dateCell.setCellStyle(styles.get(CONTENT_MIDDLE_STYLE)); dateCell.setCellValue(new SimpleDateFormat("dd.MM.yyyy").format(new Date())); currentRow += 2; sheet.getRow(currentRow).getCell(3).setCellValue(LABEL_PLACE_20); currentRow++; Row row51 = sheet.getRow(currentRow); currentRow++; row51.getCell(1).setCellValue(TWENTY_TWO); row51.getCell(2).setCellValue(TWENTY_THREE); // Insert signature picture Workbook workbook = sheet.getWorkbook(); byte[] imageBytes = pulsiodetails.getSignature(); int pictureIdx = workbook.addPicture(imageBytes, Workbook.PICTURE_TYPE_PNG); CreationHelper helper = workbook.getCreationHelper(); Drawing drawing = sheet.createDrawingPatriarch(); ClientAnchor anchor = helper.createClientAnchor(); //set top-left corner for the image anchor.setCol1(1); anchor.setRow1(currentRow); XSSFPicture pict = (XSSFPicture) drawing.createPicture(anchor, pictureIdx); pict.resize(1.01, 5); currentRow += 4; Row row56 = sheet.getRow(currentRow); row56.getCell(3).setCellValue(LABEL_SIGNATURE_STAMP); currentRow += 2; Row row58 = sheet.getRow(currentRow); Cell signatureLabelCell1 = row58.getCell(1); signatureLabelCell1.setCellValue(LABEL_SENDER_SIGNATURE_BULGARIAN); Cell carrierSignatureCell = row58.getCell(2); carrierSignatureCell.setCellValue(LABEL_CARRIER_SIGNATURE_BULGARIAN); Cell receiverSignatureCell = row58.getCell(3); receiverSignatureCell.setCellValue(LABEL_RECEIVER_SIGNATURE_BULGARIAN); currentRow++; Row row59 = sheet.getRow(currentRow); Cell signatureLabelCell2 = row59.getCell(1); signatureLabelCell2.setCellValue(LABEL_SENDER_SIGNATURE_ENGLISH); Cell carrierSignatureCel2 = row59.getCell(2); carrierSignatureCel2.setCellValue(LABEL_CARRIER_SIGNATURE_BULGARIAN); Cell receiverSignatureCel2 = row59.getCell(3); receiverSignatureCel2.setCellValue(LABEL_RECEIVER_SIGNATURE_ENGLISH); currentRow++; Row row60 = sheet.createRow(currentRow); mergeString = String.format("$B$%s:$I$%s", currentRow + 1, currentRow + 1); sheet.addMergedRegion(CellRangeAddress.valueOf(mergeString)); for (int i = 1; i < 9; i++) { row60.createCell(i).setCellStyle(styles.get(LABEL_WHOLE_STYLE)); } Cell additionalSpaceCell = row60.getCell(1); additionalSpaceCell.setCellValue(LABEL_ADDITIONAL_SPACE); return currentRow; }
From source file:com.griffinslogistics.excel.CMRGenerator.java
private static int generatePoint20Till24(XSSFSheet sheet, Map<String, CellStyle> styles, int currentRow, Pulsiodetails pulsiodetails) {//from w w w . j a v a 2 s .c om currentRow++; String mergeString; for (int i = currentRow; i < currentRow + 2; i++) { Row row = sheet.createRow(i); mergeString = String.format("$B$%s:$I$%s", i + 1, i + 1); sheet.addMergedRegion(CellRangeAddress.valueOf(mergeString)); for (int j = 1; j < 9; j++) { row.createCell(j) .setCellStyle(styles.get(i == currentRow ? LABEL_MIDDLE_STYLE : LABEL_BOTTOM_STYLE)); } } for (int i = currentRow + 2; i < currentRow + 16; i++) { Row row = sheet.createRow(i); mergeString = String.format("$D$%s:$I$%s", i + 1, i + 1); sheet.addMergedRegion(CellRangeAddress.valueOf(mergeString)); for (int j = 1; j < 9; j++) { row.createCell(j).setCellStyle(styles.get(LABEL_MIDDLE_STYLE)); } } currentRow++; Row row45 = sheet.getRow(currentRow); row45.setHeightInPoints(30); row45.getCell(1).setCellValue(LABEL_POINT_20); currentRow++; Row row46 = sheet.getRow(currentRow); Cell establishedInCell = row46.getCell(1); establishedInCell.setCellValue(LABEL_ESTABLISHED_IN); Cell establishedOnCell = row46.getCell(2); establishedOnCell.setCellValue(LABEL_ESTABLISHED_ON); Cell goodsDeliveredCell = row46.getCell(3); goodsDeliveredCell.setCellValue(LABEL_GOODS_RECEIVED); currentRow++; Row row47 = sheet.getRow(currentRow); row47.getCell(3).setCellValue(LABEL_TIME_OF_ARRIVAL); currentRow++; Row row48 = sheet.getRow(currentRow); Cell cityCell = row48.getCell(1); cityCell.setCellStyle(styles.get(CONTENT_MIDDLE_STYLE)); cityCell.setCellValue("Sofia, Bulgaria"); Cell dateCell = row48.getCell(2); dateCell.setCellStyle(styles.get(CONTENT_MIDDLE_STYLE)); dateCell.setCellValue(new SimpleDateFormat("dd.MM.yyyy").format(new Date())); currentRow += 2; sheet.getRow(currentRow).getCell(3).setCellValue(LABEL_PLACE_20); currentRow++; Row row51 = sheet.getRow(currentRow); currentRow++; row51.getCell(1).setCellValue(TWENTY_TWO); row51.getCell(2).setCellValue(TWENTY_THREE); // Insert signature picture Workbook workbook = sheet.getWorkbook(); byte[] imageBytes = pulsiodetails.getSignature(); int pictureIdx = workbook.addPicture(imageBytes, Workbook.PICTURE_TYPE_JPEG); CreationHelper helper = workbook.getCreationHelper(); Drawing drawing = sheet.createDrawingPatriarch(); ClientAnchor anchor = helper.createClientAnchor(); //set top-left corner for the image anchor.setCol1(1); anchor.setRow1(currentRow); Picture pict = drawing.createPicture(anchor, pictureIdx); pict.resize(); currentRow += 4; Row row56 = sheet.getRow(currentRow); row56.getCell(3).setCellValue(LABEL_SIGNATURE_STAMP); currentRow += 2; Row row58 = sheet.getRow(currentRow); Cell signatureLabelCell1 = row58.getCell(1); signatureLabelCell1.setCellValue(LABEL_SENDER_SIGNATURE_BULGARIAN); Cell carrierSignatureCell = row58.getCell(2); carrierSignatureCell.setCellValue(LABEL_CARRIER_SIGNATURE_BULGARIAN); Cell receiverSignatureCell = row58.getCell(3); receiverSignatureCell.setCellValue(LABEL_RECEIVER_SIGNATURE_BULGARIAN); currentRow++; Row row59 = sheet.getRow(currentRow); Cell signatureLabelCell2 = row59.getCell(1); signatureLabelCell2.setCellValue(LABEL_SENDER_SIGNATURE_ENGLISH); Cell carrierSignatureCel2 = row59.getCell(2); carrierSignatureCel2.setCellValue(LABEL_CARRIER_SIGNATURE_BULGARIAN); Cell receiverSignatureCel2 = row59.getCell(3); receiverSignatureCel2.setCellValue(LABEL_RECEIVER_SIGNATURE_ENGLISH); currentRow++; Row row60 = sheet.createRow(currentRow); mergeString = String.format("$B$%s:$I$%s", currentRow + 1, currentRow + 1); sheet.addMergedRegion(CellRangeAddress.valueOf(mergeString)); for (int i = 1; i < 9; i++) { row60.createCell(i).setCellStyle(styles.get(LABEL_WHOLE_STYLE)); } Cell additionalSpaceCell = row60.getCell(1); additionalSpaceCell.setCellValue(LABEL_ADDITIONAL_SPACE); return currentRow; }
From source file:com.vodafone.poms.ii.helpers.ExportManager.java
private static String addFile(XSSFSheet sh, String filePath, double oleId) throws IOException, InvalidFormatException { File file = new File(filePath); FileInputStream fin = new FileInputStream(file); byte[] data;//from w ww . j a va 2s .com data = new byte[fin.available()]; fin.read(data); Ole10Native ole10 = new Ole10Native(file.getAbsolutePath(), file.getAbsolutePath(), file.getAbsolutePath(), data); ByteArrayOutputStream bos = new ByteArrayOutputStream(500); ole10.writeOut(bos); POIFSFileSystem poifs = new POIFSFileSystem(); poifs.getRoot().createDocument(Ole10Native.OLE10_NATIVE, new ByteArrayInputStream(bos.toByteArray())); poifs.getRoot().setStorageClsid(ClassID.OLE10_PACKAGE); final PackagePartName pnOLE = PackagingURIHelper .createPartName("/xl/embeddings/oleObject" + oleId + Math.random() + ".bin"); final PackagePart partOLE = sh.getWorkbook().getPackage().createPart(pnOLE, "application/vnd.openxmlformats-officedocument.oleObject"); PackageRelationship prOLE = sh.getPackagePart().addRelationship(pnOLE, TargetMode.INTERNAL, POIXMLDocument.OLE_OBJECT_REL_TYPE); OutputStream os = partOLE.getOutputStream(); poifs.writeFilesystem(os); os.close(); poifs.close(); return prOLE.getId(); }
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. j a v a2 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 www. j a v a2 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:cv_extractor.DocReader.java
protected static void generateExcel(String directory, String fileName) { XSSFWorkbook workbook = new XSSFWorkbook(); //Create a blank sheet XSSFSheet sheet = workbook.createSheet("Email Address"); //Create CellStyle for header (First row) CellStyle cellStyle = sheet.getWorkbook().createCellStyle(); Font font = sheet.getWorkbook().createFont(); font.setBold(true);//from ww w .ja v a2 s .c om font.setFontHeightInPoints((short) 16); cellStyle.setFont(font); //Create first row (Header) Row header = sheet.createRow(0); //Creating a cell (Only one required, for single attribute E-mail) Cell cellTitle = header.createCell(1); cellTitle.setCellStyle(cellStyle); cellTitle.setCellValue("E-Mail"); //Counter to use while creating further rows, starting from row 1 int rowNum = 1; //Iterate over data and write to sheet Iterator<String> iterator = data.iterator(); while (iterator.hasNext()) { //Create a row for each E-mail string Row row = sheet.createRow(rowNum++); //Create a cell to put the E-mail string Cell cell = row.createCell(1); //Put value in the cell cell.setCellValue(iterator.next()); } try { //Write the workbook in file system if (fileName.equals("")) { fileName = "Email List"; } FileOutputStream out = new FileOutputStream(new File(directory + "/" + fileName + ".xlsx")); workbook.write(out); out.close(); JOptionPane.showMessageDialog(null, "File generated !"); } catch (Exception e) { e.printStackTrace(); } }
From source file:gov.nih.nci.evs.app.neopl.CSVtoExcel.java
License:Open Source License
public void runXSSF(String inputfile) { int size = checkSpecialCharacters(inputfile); int n = inputfile.lastIndexOf("."); //String outputfile = inputfile.substring(0, n) + ".xlsx"; String outputfile = getOutputFile(inputfile, ".xlsx"); try {// ww w . ja v a 2s. c o m XSSFWorkbook wb = new XSSFWorkbook(); XSSFCreationHelper helper = null; XSSFCellStyle cellStyle = wb.createCellStyle(); XSSFFont font = wb.createFont(); font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD); cellStyle.setFont(font); XSSFCellStyle linkCellStyle = wb.createCellStyle(); XSSFFont linkfont = wb.createFont(); //XSSFColor color = new XSSFColor(Color.LIGHT_BLUE); XSSFColor color = new XSSFColor(Color.BLUE); linkfont.setColor(color); //linkfont.setColor(XSSFColor.LIGHT_BLUE.index); linkCellStyle.setFont(linkfont); CSVReader reader = new CSVReader(new FileReader(inputfile));//CSV file String[] line; int r = 0; Cell cell = null; XSSFHyperlink url_link = null; XSSFSheet sheet = null; int page_num = 1; Row row = null; int lcv = 0; int row_count = 0; try { while ((line = reader.readNext()) != null) { if (lcv % PAGE_SIZE == 0) { r = 0; String sheetLabel = SHEET_LABEL; if (size > PAGE_SIZE) { sheetLabel = sheetLabel + " (Page " + page_num + ")"; } //System.out.println("Creating " + sheetLabel); sheet = wb.createSheet(sheetLabel); helper = sheet.getWorkbook().getCreationHelper(); url_link = helper.createHyperlink(XSSFHyperlink.LINK_URL); row = sheet.createRow((short) r); for (int i = 0; i < HEADINGS.length; i++) { String heading = HEADINGS[i]; cell = row.createCell(i); cell.setCellValue(heading); cell.setCellStyle(cellStyle); } page_num++; } else { String s4 = (String) line[4]; s4 = s4.trim(); r++; row = sheet.createRow((short) r); row_count++; cell = row.createCell(0); String ncit_code = line[0]; cell.setCellValue(ncit_code); if (NCIT_LINK) { url_link = helper.createHyperlink(XSSFHyperlink.LINK_URL); url_link.setAddress(getNCItHyperlink(ncit_code)); cell.setHyperlink(url_link); cell.setCellStyle(linkCellStyle); } cell = row.createCell(1); String name = line[1]; cell.setCellValue(line[1]); cell = row.createCell(2); cell.setCellValue(line[2]); if (NCIM_LINK) { String s2 = line[2]; s2 = s2.trim(); if (s2.length() > 0) { url_link = helper.createHyperlink(XSSFHyperlink.LINK_URL); url_link.setAddress(getNCImHyperlink(s2)); cell.setHyperlink(url_link); cell.setCellStyle(linkCellStyle); } } cell = row.createCell(3); String ncim_name = line[3]; cell.setCellValue(line[3]); cell = row.createCell(4); cell.setCellValue(line[4]); cell = row.createCell(5); String atom_name = (String) line[5]; cell.setCellValue(line[5]); cell = row.createCell(6); cell.setCellValue(line[6]); if (SOURCE_LINK) { if (s4.length() > 0) { String s6 = (String) line[6]; if (localNameMap.containsKey(s4)) { url_link = helper.createHyperlink(XSSFHyperlink.LINK_URL); s4 = (String) localNameMap.get(s4); url_link.setAddress(getSourceHyperlink(s4, s6)); cell.setHyperlink(url_link); cell.setCellStyle(linkCellStyle); } } } cell = row.createCell(7); cell.setCellValue(line[7]); } lcv++; } } catch (Exception ex) { ex.printStackTrace(); } // Write the output to a file FileOutputStream fileOut = new FileOutputStream(outputfile); wb.write(fileOut); fileOut.close(); System.out.println("Output file " + outputfile + " generated."); System.out.println("row_count: " + row_count); } catch (Exception ex) { ex.printStackTrace(); } }
From source file:nl.detoren.ijsco.io.ExcelExport.java
License:Open Source License
/** * Sorts (A-Z) rows by String column/* w w w . jav a 2 s . c o m*/ * @param sheet - sheet to sort * @param column - String column to sort by * @param rowStart - sorting from this row down */ private void sortSheet(XSSFSheet sheet, int column, int rowStart, int rowEnd) { try { FormulaEvaluator evaluator = sheet.getWorkbook().getCreationHelper().createFormulaEvaluator(); logger.log(Level.INFO, "sorting sheet: " + sheet.getSheetName()); boolean sorting = true; //int lastRow = sheet.getLastRowNum(); while (sorting == true) { sorting = false; for (Row row : sheet) { // skip if this row is before first to sort if (row.getRowNum() < rowStart) continue; // end if this is last row if (rowEnd == row.getRowNum()) break; Row row2 = sheet.getRow(row.getRowNum() + 1); if (row2 == null) continue; int rownum1 = row.getRowNum(); int rownum2 = row2.getRowNum(); CellValue firstValue; CellValue secondValue; firstValue = evaluator.evaluate(row.getCell(column)); secondValue = evaluator.evaluate(row2.getCell(column)); //compare cell from current row and next row - and switch if secondValue should be before first if (secondValue.toString().compareToIgnoreCase(firstValue.toString()) < 0) { logger.log(Level.INFO, "Shifting rows" + sheet.getSheetName() + rownum1 + " - " + rownum2); sheet.shiftRows(row2.getRowNum(), row2.getRowNum(), -1); logger.log(Level.INFO, "Shifting rows" + sheet.getSheetName() + rownum1 + " - " + rownum2); sheet.shiftRows(row.getRowNum(), row.getRowNum(), 1); sorting = true; } } } } catch (Exception ex) { logger.log(Level.WARNING, "Failing Shifting rows" + sheet.getSheetName() + "Error " + ex.getMessage()); } }
From source file:opn.greenwebs.FXMLDocumentController.java
private File createStockFile(List<ItemDB> list) { int nSize = list.size(); XSSFWorkbook wbs = createStockWorkbook(); XSSFSheet sheetStock = wbs.getSheet("Digital Version"); List<XSSFTable> lTables = sheetStock.getTables(); // Create a FormulaEvaluator to use FormulaEvaluator mainWorkbookEvaluator = sheetStock.getWorkbook().getCreationHelper() .createFormulaEvaluator();//from w w w.ja v a 2 s. c o m File fStock = createFilename("STK", ""); Instant instant = Instant.from(dteOrderDate.getValue().atStartOfDay(ZoneId.systemDefault())); Row rowed = sheetStock.getRow(6); Cell celled = rowed.getCell(10); CellStyle cellStyle = celled.getCellStyle(); XSSFFont font = sheetStock.getWorkbook().createFont(); font.setFontHeight(14); cellStyle.setFont(font); celled.setCellValue(Date.from(instant)); celled.setCellStyle(cellStyle); rowed = sheetStock.getRow(10); celled = rowed.getCell(2); celled.setCellValue(fStock.getName().substring(0, fStock.getName().length() - 5)); if (!lTables.isEmpty()) { XSSFTable table = lTables.get(0); table.getCTTable() .setRef(new CellRangeAddress(table.getStartCellReference().getRow(), table.getEndCellReference().getRow() + nSize, table.getStartCellReference().getCol(), table.getEndCellReference().getCol()).formatAsString()); XSSFRow row; XSSFCell cell; font = sheetStock.getWorkbook().createFont(); font.setFontHeight(14); int nCellRef = table.getStartCellReference().getRow() + 1; for (ItemDB itemdb : list) { row = sheetStock.createRow(nCellRef++); cell = row.createCell(0); cellStyle = cell.getCellStyle(); cell.setCellValue(itemdb.getDblQty()); cellStyle.setFont(font); cell.setCellStyle(cellStyle); cell = row.createCell(1); cell.setCellValue(itemdb.getStrMfr()); cell.setCellStyle(cellStyle); cell = row.createCell(2); cell.setCellValue(itemdb.getStrSKU()); cell.setCellStyle(cellStyle); cell = row.createCell(3); cell.setCellValue(itemdb.getStrDescrip()); cell.setCellStyle(cellStyle); cell = row.createCell(4); cell.setCellValue(itemdb.getStrSupplier()); cell.setCellStyle(cellStyle); cell = row.createCell(5); cell.setCellValue(itemdb.getStrSupPart()); cell.setCellType(HSSFCell.CELL_TYPE_STRING); //cell.setCellStyle(cellStyle); cell = row.createCell(6); cell.setCellValue(itemdb.getDblSalePrice()); cell.setCellStyle(cellStyle); cell = row.createCell(7); cell.setCellValue(itemdb.getDblCost()); cell.setCellStyle(cellStyle); /*cell = row.createCell(8); cell.setCellType(HSSFCell.CELL_TYPE_FORMULA); cell.setCellFormula("IF(A" + nCellRef + ">0,IF(G" + nCellRef + ">0,IF(H" + nCellRef + ">0,A" + nCellRef + "*G" + nCellRef + "-A" + nCellRef + "*H" + nCellRef + ",\"\"),\"\"),\"\")"); mainWorkbookEvaluator.evaluateFormulaCell(cell); cell.setCellStyle(cellStyle); cell = row.createCell(9); cell.setCellFormula("IF(I" + nCellRef + "<>\"\",I" + nCellRef + "/(A" + nCellRef + "*G" + nCellRef + "),\"\")"); mainWorkbookEvaluator.evaluateFormulaCell(cell); CellStyle style = wbs.createCellStyle(); style.setDataFormat(wbs.createDataFormat().getFormat("0%")); cell.setCellStyle(style);*/ mainWorkbookEvaluator.evaluateAll(); } try { try (FileOutputStream fileOut = new FileOutputStream(fStock)) { wbs.write(fileOut); return fStock; } } catch (FileNotFoundException ex) { logger.info(ex.getLocalizedMessage()); } catch (IOException ex) { logger.info(ex.getLocalizedMessage()); } } return null; }