List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet setColumnWidth
@Override public void setColumnWidth(int columnIndex, int width)
The maximum column width for an individual cell is 255 characters.
From source file:devcup.search4u.xlsview.ConvertResultsToXLS.java
public void createXLSTable() throws IOException { HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet("Result sheet"); //create headerRow Row headerRow = sheet.createRow(0);/*from w ww . j a v a 2 s. co m*/ HSSFCellStyle headerStyle = workbook.createCellStyle(); HSSFCellStyle style = workbook.createCellStyle(); HSSFFont partFont = workbook.createFont(); //partFont.setItalic(true); partFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); partFont.setUnderline(HSSFFont.U_DOUBLE); HSSFFont defaultFont = workbook.createFont(); defaultFont.setFontHeightInPoints((short) 10); defaultFont.setFontName("Arial"); defaultFont.setColor(HSSFColor.BLACK.index); defaultFont.setItalic(false); HSSFFont font = workbook.createFont(); font.setFontHeightInPoints((short) 11); font.setFontName("Arial"); font.setColor(HSSFColor.BLUE_GREY.index); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); font.setItalic(true); //style=(HSSFCellStyle) headerRow.getRowStyle(); //style.setFillPattern(CellStyle.SOLID_FOREGROUND); headerStyle.setWrapText(true); headerStyle.setAlignment(CellStyle.ALIGN_CENTER); //style.setFillBackgroundColor(HSSFColor.ROYAL_BLUE.index); //style.setFillForegroundColor(HSSFColor.ROYAL_BLUE.index); //style.setHidden(false); //style.setIndention(indent); headerStyle.setFont(font); //create a new cell in headerRow Cell headerCell; headerCell = headerRow.createCell(0); headerCell.setCellValue("?"); headerCell.setCellStyle(headerStyle); //sheet.autoSizeColumn(0); sheet.setColumnWidth(0, 7000); headerCell = headerRow.createCell(1); headerCell.setCellValue(" "); headerCell.setCellStyle(headerStyle); //sheet.autoSizeColumn(1); sheet.setColumnWidth(1, 12000); /*headerCell = headerRow.createCell(2); headerCell.setCellValue("? "); headerCell.setCellStyle(style); sheet.autoSizeColumn(2); //sheet.setColumnWidth(2, 7000); */ headerCell = headerRow.createCell(2); headerCell.setCellValue(" ?"); headerCell.setCellStyle(headerStyle); //sheet.autoSizeColumn(3); sheet.setColumnWidth(2, 18000); style.setWrapText(true); style.setFont(defaultFont); //create another Rows int rowNum = 1; for (SearchResult searchResult : searchResultsList) { for (String key : searchResult.getRelevDocsPath()) { //for (String key : searchResult.getDocumentsFragments().keySet()) { for (ObjectPair<Integer, String> pair : searchResult.getDocumentsFragments().get(key)) { Row row = sheet.createRow(rowNum++); Cell cell; cell = row.createCell(0); cell.setCellValue(searchResult.getQuery()); cell.setCellStyle(style); cell = row.createCell(1); cell.setCellValue(key); cell.setCellStyle(style); //cell = row.createCell(2); //cell.setCellValue(pair.getFirst()); //cell.setCellStyle(style); cell = row.createCell(2); cell.setCellValue(labeledStringToRichTextString(pair.getSecond(), partFont)); cell.setCellStyle(style); } } } try { FileOutputStream out = new FileOutputStream(new File(resultXMLPath)); workbook.write(out); out.close(); System.out.println("Excel written successfully.."); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } }
From source file:DomainToolCore.Report.XLSSubDomains.java
License:Open Source License
private void WriteSubDomain(HSSFSheet firstSheet) { firstSheet.createRow(5).createCell(0).setCellValue("SubDomain"); firstSheet.getRow(5).getCell(0).setCellStyle(setBold()); firstSheet.setColumnWidth(0, 10000); for (int i = 1, j = 6; i < subdxls.size(); i++) { firstSheet.createRow(j).createCell(0).setCellValue((String) subdxls.get(i)); j++;//from w ww. j ava2 s. c o m } }
From source file:DomainToolCore.Report.XLSSubDomains.java
License:Open Source License
private void WriteIp(HSSFSheet sheet) { sheet.getRow(5).createCell(1).setCellValue("IP Address"); sheet.getRow(5).getCell(1).setCellStyle(setBold()); sheet.setColumnWidth(1, 10000); for (int i = 1, j = 6; i < subdxls.size(); i++) { sheet.getRow(j).createCell(1).setCellValue((String) ipxls.get(i)); j++;/*from w w w .j a v a2 s . c o m*/ } }
From source file:edu.duke.cabig.c3pr.service.Summary3ReportServiceTest.java
License:BSD License
public void testGenerateEXCEL() throws Exception { HealthcareSite hcs = healthcareSiteDao.getById(1100); DateFormat format = new SimpleDateFormat("MM/dd/yyyy"); Date startDate = (Date) format.parse("01/11/1990"); Date endDate = (Date) format.parse("01/01/2007"); String grantNumber = "GRANT-NO 1232"; Summary3Report summary3Report = new Summary3Report(hcs, grantNumber, startDate, endDate); String reportingSource = healthcareSiteDao.getById(1000).getName(); summary3Report.setReportingSource(reportingSource); summaryReportService.buildSummary3Report(summary3Report); String xmlString = summaryReportService.generateXML(summary3Report); File file = new File("testReport.xml"); FileWriter fileWriter = new FileWriter(file); fileWriter.write(xmlString);/*from ww w . ja va2s.c o m*/ fileWriter.flush(); fileWriter.close(); System.out.println(xmlString); // creating the workbook and the spreadsheet try { DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance(); DocumentBuilder builder = factory.newDocumentBuilder(); InputStream inputStream = new FileInputStream(file); Document document = builder.parse(inputStream); HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet spreadSheet = wb.createSheet("Sumary 3 Report"); PrintSetup printSetup = spreadSheet.getPrintSetup(); printSetup.setLandscape(true); spreadSheet.setFitToPage(true); spreadSheet.setHorizontallyCenter(true); spreadSheet.setColumnWidth((short) 0, (short) (60 * 256)); spreadSheet.setColumnWidth((short) 1, (short) (15 * 256)); spreadSheet.setColumnWidth((short) 2, (short) (30 * 256)); HSSFRow titleRow = spreadSheet.createRow(0); HSSFCell titleCell = titleRow.createCell((short) 0); titleRow.setHeightInPoints(40); HSSFCellStyle titleCellStyle = wb.createCellStyle(); titleCellStyle.setAlignment(titleCellStyle.ALIGN_CENTER_SELECTION); titleCellStyle.setVerticalAlignment(titleCellStyle.VERTICAL_CENTER); titleCell.setCellStyle(titleCellStyle); String nullSafeGrantNumber = (document.getElementsByTagName("grantNumber").item(0) != null && document.getElementsByTagName("grantNumber").item(0).getFirstChild() != null) ? (document.getElementsByTagName("grantNumber")).item(0).getFirstChild().getNodeValue() : ""; titleCell.setCellValue("Summary 3: Reportable Patients/Participation " + "in Therapeutic Protocols" + " " + nullSafeGrantNumber); HSSFRow orgRow = spreadSheet.createRow(1); orgRow.setHeightInPoints(30); HSSFCell organizationCell = orgRow.createCell((short) 0); HSSFCellStyle orgCellStyle = wb.createCellStyle(); orgCellStyle.setAlignment(titleCellStyle.ALIGN_CENTER_SELECTION); HSSFFont font = wb.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); orgCellStyle.setFont(font); organizationCell.setCellStyle(orgCellStyle); organizationCell .setCellValue(((Element) (document.getElementsByTagName("reportingOrganization").item(0))) .getElementsByTagName("name").item(0).getFirstChild().getNodeValue()); HSSFRow reportingPeriodRow = spreadSheet.createRow(2); HSSFFont reportingPeriodFont = wb.createFont(); reportingPeriodFont.setFontHeightInPoints((short) 9); HSSFCellStyle reportingPeriodStyle = wb.createCellStyle(); reportingPeriodStyle.setFont(reportingPeriodFont); reportingPeriodRow.setHeightInPoints(20); HSSFCell reportingPeriodCell = reportingPeriodRow.createCell((short) 0); reportingPeriodCell.setCellStyle(titleCellStyle); reportingPeriodCell.setCellValue("Reporting Period " + (document.getElementsByTagName("startDate").item(0).getFirstChild().getNodeValue()) + " - " + (document.getElementsByTagName("endDate").item(0).getFirstChild().getNodeValue())); // creating the first row of table the table header HSSFRow row = spreadSheet.createRow(3); HSSFCell tableHeaderCell1 = row.createCell((short) 0); HSSFCellStyle tableHeaderCellStyle1 = wb.createCellStyle(); tableHeaderCellStyle1.setWrapText(true); tableHeaderCellStyle1.setBorderRight(HSSFCellStyle.BORDER_THIN); tableHeaderCellStyle1.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); tableHeaderCellStyle1.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM); tableHeaderCellStyle1.setBorderBottom(HSSFCellStyle.BORDER_THIN); tableHeaderCellStyle1.setFont(font); tableHeaderCell1.setCellStyle(tableHeaderCellStyle1); tableHeaderCell1.setCellValue("Disease Site"); // creating table header 2nd & 3rd cells HSSFCell tableHeaderCell2 = row.createCell((short) 1); HSSFCellStyle tableHeaderCellStyle2 = wb.createCellStyle(); tableHeaderCellStyle2.setBorderRight(HSSFCellStyle.BORDER_THIN); tableHeaderCellStyle2.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); tableHeaderCellStyle2.setBorderLeft(HSSFCellStyle.BORDER_THIN); tableHeaderCellStyle2.setBorderBottom(HSSFCellStyle.BORDER_THIN); tableHeaderCellStyle2.setWrapText(true); tableHeaderCell2.setCellStyle(tableHeaderCellStyle2); tableHeaderCell2.setCellValue("Newly Registered Patients"); HSSFCell tableHeaderCell3 = row.createCell((short) 2); HSSFCellStyle tableHeaderCellStyle3 = wb.createCellStyle(); tableHeaderCellStyle3.setBorderRight(HSSFCellStyle.BORDER_MEDIUM); tableHeaderCellStyle3.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); tableHeaderCellStyle3.setBorderLeft(HSSFCellStyle.BORDER_THIN); tableHeaderCellStyle3.setBorderBottom(HSSFCellStyle.BORDER_THIN); tableHeaderCellStyle3.setWrapText(true); tableHeaderCell3.setCellStyle(tableHeaderCellStyle3); tableHeaderCell3.setCellValue("Total patients newly enrolled in therapeutic protocols"); NodeList nodeList = document.getElementsByTagName("reportData"); HSSFCellStyle tableCellStyle = wb.createCellStyle(); tableCellStyle.setBorderRight(HSSFCellStyle.BORDER_MEDIUM); tableCellStyle.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); tableCellStyle.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM); tableCellStyle.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM); spreadSheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$C$1")); spreadSheet.addMergedRegion(CellRangeAddress.valueOf("$A$2:$C$2")); spreadSheet.addMergedRegion(CellRangeAddress.valueOf("$A$3:$C$3")); for (int i = 4; i < nodeList.getLength() + 4; i++) { row = spreadSheet.createRow(i); HSSFCell cell = row.createCell((short) 0); if (i == (4 + nodeList.getLength() - 1)) { HSSFCellStyle totalCellStyle = wb.createCellStyle(); totalCellStyle.setFont(font); totalCellStyle.setRightBorderColor((short) 10); totalCellStyle.setBorderRight(HSSFCellStyle.BORDER_MEDIUM); tableCellStyle.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); totalCellStyle.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM); totalCellStyle.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM); cell.setCellStyle(totalCellStyle); } else { cell.setCellStyle(tableCellStyle); } cell.setCellValue(((Element) ((Element) (nodeList.item(i - 4))).getElementsByTagName("key").item(0)) .getAttribute("name")); cell = row.createCell((short) 1); cell.setCellStyle(tableCellStyle); cell.setCellValue(""); cell = row.createCell((short) 2); cell.setCellStyle(tableCellStyle); cell.setCellValue( ((Element) (((Element) (nodeList.item(i - 4))).getElementsByTagName("value").item(3))) .getFirstChild().getNodeValue()); } File outputFile = new File(System.getProperty("user.home") + File.separator + "Summary3Report.xls"); FileOutputStream output = new FileOutputStream(outputFile); wb.write(output); output.flush(); output.close(); } catch (IOException e) { e.printStackTrace(); } catch (ParserConfigurationException e) { e.printStackTrace(); } catch (SAXException e) { e.printStackTrace(); } }
From source file:edu.duke.cabig.c3pr.xml.Summary3ReportGenerator.java
License:BSD License
public void generateExcel(String summary3ReportXml, String file) throws Exception { // creating the workbook and the spreadsheet try {//from w ww . j a v a2s .com DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance(); DocumentBuilder builder = factory.newDocumentBuilder(); String fileName = ""; fileName = file; InputStream inputStream = new FileInputStream((fileName + ".xml")); Document document = builder.parse(inputStream); HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet spreadSheet = wb.createSheet("Sumary 3 Report"); PrintSetup printSetup = spreadSheet.getPrintSetup(); printSetup.setLandscape(true); spreadSheet.setFitToPage(true); spreadSheet.setHorizontallyCenter(true); spreadSheet.setColumnWidth((short) 0, (short) (60 * 256)); spreadSheet.setColumnWidth((short) 1, (short) (15 * 256)); spreadSheet.setColumnWidth((short) 2, (short) (30 * 256)); HSSFRow titleRow = spreadSheet.createRow(0); HSSFCell titleCell = titleRow.createCell((short) 0); titleRow.setHeightInPoints(40); HSSFCellStyle titleCellStyle = wb.createCellStyle(); titleCellStyle.setAlignment(titleCellStyle.ALIGN_CENTER_SELECTION); titleCellStyle.setVerticalAlignment(titleCellStyle.VERTICAL_CENTER); titleCell.setCellStyle(titleCellStyle); String nullSafeGrantNumber = (document.getElementsByTagName("grantNumber").item(0) != null && document.getElementsByTagName("grantNumber").item(0).getFirstChild() != null) ? (document.getElementsByTagName("grantNumber")).item(0).getFirstChild().getNodeValue() : ""; titleCell.setCellValue("Summary 3: Reportable Patients/Participation " + "in Therapeutic Protocols" + " " + nullSafeGrantNumber); HSSFRow orgRow = spreadSheet.createRow(1); orgRow.setHeightInPoints(30); HSSFCell organizationCell = orgRow.createCell((short) 0); HSSFCellStyle orgCellStyle = wb.createCellStyle(); orgCellStyle.setAlignment(titleCellStyle.ALIGN_CENTER_SELECTION); HSSFFont font = wb.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); orgCellStyle.setFont(font); organizationCell.setCellStyle(orgCellStyle); organizationCell .setCellValue(((Element) (document.getElementsByTagName("reportingOrganization").item(0))) .getElementsByTagName("name").item(0).getFirstChild().getNodeValue()); HSSFRow reportingPeriodRow = spreadSheet.createRow(2); HSSFFont reportingPeriodFont = wb.createFont(); reportingPeriodFont.setFontHeightInPoints((short) 9); HSSFCellStyle reportingPeriodStyle = wb.createCellStyle(); reportingPeriodStyle.setFont(reportingPeriodFont); reportingPeriodRow.setHeightInPoints(20); HSSFCell reportingPeriodCell = reportingPeriodRow.createCell((short) 0); reportingPeriodCell.setCellStyle(titleCellStyle); reportingPeriodCell.setCellValue("Reporting Period " + (document.getElementsByTagName("startDate").item(0).getFirstChild().getNodeValue()) + " - " + (document.getElementsByTagName("endDate").item(0).getFirstChild().getNodeValue())); // creating the first row of table the table header HSSFRow row = spreadSheet.createRow(3); HSSFCell tableHeaderCell1 = row.createCell((short) 0); HSSFCellStyle tableHeaderCellStyle1 = wb.createCellStyle(); tableHeaderCellStyle1.setWrapText(true); tableHeaderCellStyle1.setBorderRight(HSSFCellStyle.BORDER_THIN); tableHeaderCellStyle1.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); tableHeaderCellStyle1.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM); tableHeaderCellStyle1.setBorderBottom(HSSFCellStyle.BORDER_THIN); tableHeaderCellStyle1.setFont(font); tableHeaderCell1.setCellStyle(tableHeaderCellStyle1); tableHeaderCell1.setCellValue("Disease Site"); // creating table header 2nd & 3rd cells HSSFCell tableHeaderCell2 = row.createCell((short) 1); HSSFCellStyle tableHeaderCellStyle2 = wb.createCellStyle(); tableHeaderCellStyle2.setBorderRight(HSSFCellStyle.BORDER_THIN); tableHeaderCellStyle2.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); tableHeaderCellStyle2.setBorderLeft(HSSFCellStyle.BORDER_THIN); tableHeaderCellStyle2.setBorderBottom(HSSFCellStyle.BORDER_THIN); tableHeaderCellStyle2.setWrapText(true); tableHeaderCell2.setCellStyle(tableHeaderCellStyle2); tableHeaderCell2.setCellValue("Newly Registered Patients"); HSSFCell tableHeaderCell3 = row.createCell((short) 2); HSSFCellStyle tableHeaderCellStyle3 = wb.createCellStyle(); tableHeaderCellStyle3.setBorderRight(HSSFCellStyle.BORDER_MEDIUM); tableHeaderCellStyle3.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); tableHeaderCellStyle3.setBorderLeft(HSSFCellStyle.BORDER_THIN); tableHeaderCellStyle3.setBorderBottom(HSSFCellStyle.BORDER_THIN); tableHeaderCellStyle3.setWrapText(true); tableHeaderCell3.setCellStyle(tableHeaderCellStyle3); tableHeaderCell3.setCellValue("Total patients newly enrolled in therapeutic protocols"); NodeList nodeList = document.getElementsByTagName("reportData"); HSSFCellStyle tableCellStyle = wb.createCellStyle(); tableCellStyle.setBorderRight(HSSFCellStyle.BORDER_MEDIUM); tableCellStyle.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); tableCellStyle.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM); tableCellStyle.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM); spreadSheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$C$1")); spreadSheet.addMergedRegion(CellRangeAddress.valueOf("$A$2:$C$2")); spreadSheet.addMergedRegion(CellRangeAddress.valueOf("$A$3:$C$3")); for (int i = 4; i < nodeList.getLength() + 4; i++) { row = spreadSheet.createRow(i); HSSFCell cell = row.createCell((short) 0); if (i == (4 + nodeList.getLength() - 1)) { HSSFCellStyle totalCellStyle = wb.createCellStyle(); totalCellStyle.setFont(font); totalCellStyle.setRightBorderColor((short) 10); totalCellStyle.setBorderRight(HSSFCellStyle.BORDER_MEDIUM); tableCellStyle.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); totalCellStyle.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM); totalCellStyle.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM); cell.setCellStyle(totalCellStyle); } else { cell.setCellStyle(tableCellStyle); } cell.setCellValue(((Element) ((Element) (nodeList.item(i - 4))).getElementsByTagName("key").item(0)) .getAttribute("name")); cell = row.createCell((short) 1); cell.setCellStyle(tableCellStyle); cell.setCellValue(""); cell = row.createCell((short) 2); cell.setCellStyle(tableCellStyle); cell.setCellValue( ((Element) (((Element) (nodeList.item(i - 4))).getElementsByTagName("value").item(3))) .getFirstChild().getNodeValue()); } FileOutputStream output = new FileOutputStream(new File(addExtension(file, "Excel"))); wb.write(output); output.flush(); output.close(); } catch (IOException e) { e.printStackTrace(); } catch (ParserConfigurationException e) { e.printStackTrace(); } catch (SAXException e) { e.printStackTrace(); } }
From source file:edu.jhu.cvrg.services.nodeDataService.DataStaging.java
License:Open Source License
private HSSFSheet consolidateAlgorithmFiles(int algorithmCode, HSSFSheet sheet, String subjectIds, String userId, String fileNames, boolean isPublic, ApacheCommonsFtpWrapper ftpClient) { boolean firstToken = true; StringTokenizer tokenizer = new StringTokenizer(subjectIds, "^"); StringTokenizer fileTokenizer = new StringTokenizer(fileNames, "^"); String currentSubject = null; int lineNumber = 0; String headerIndicator = null; switch (algorithmCode) { case 0:/*from w w w. j a v a 2 s .co m*/ headerIndicator = CHESNOKOV_HEADER_INDICATOR; break; case 1: headerIndicator = BERGER_HEADER_INDICATOR; break; } String directory = localFtpRoot + sep + userId + utils.generateTimeStamp(); while (tokenizer.hasMoreTokens() && fileTokenizer.hasMoreTokens()) { currentSubject = tokenizer.nextToken(); System.out.println(currentSubject); String file = fileTokenizer.nextToken(); String bareFile = file.substring(file.lastIndexOf("/") + 1); try { ftpClient.downloadFile(file, directory + bareFile); FileReader fi = new FileReader(directory + bareFile); BufferedReader br = new BufferedReader(fi); String thisLine = null, value = null; int inputLineNumber = 0; while ((thisLine = br.readLine()) != null) { boolean isHeader = false; isHeader = thisLine.contains(headerIndicator); if (thisLine != null && thisLine.length() > 0 && (firstToken || !isHeader)) { HSSFRow rowOut = sheet.createRow(lineNumber); StringTokenizer rowTokenizer = new StringTokenizer(thisLine, ","); HSSFCell cellOut; int colNumber = 0; while (rowTokenizer.hasMoreTokens()) { cellOut = rowOut.createCell(colNumber); String replacePipes = rowTokenizer.nextToken(); if (isHeader) { replacePipes = replacePipes.replaceAll("\\|", ","); value = replacePipes; StringTokenizer temp = new StringTokenizer(replacePipes, ","); if (temp.countTokens() > 1) { HSSFHyperlink link = new HSSFHyperlink(HSSFHyperlink.LINK_URL); link.setAddress(temp.nextToken()); cellOut.setHyperlink(link); value = temp.nextToken().trim(); } cellOut.setCellValue(value); sheet.setColumnWidth(colNumber, (value.length() + 3) * 256); } else { cellOut.setCellValue(replacePipes); if (sheet.getColumnWidth(colNumber) / 256 < replacePipes.length()) sheet.setColumnWidth(colNumber, replacePipes.length() * 256); } colNumber++; } lineNumber++; } inputLineNumber++; } fi.close(); br.close(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } //} firstToken = false; } return sheet; }
From source file:edu.ku.brc.dbsupport.TableModel2Excel.java
License:Open Source License
/** * Converts a tableModel to an Excel Spreadsheet. * @param toFile the file object to write it to. * @param title the title of the spreadsheet. * @param tableModel the table model//from ww w .ja v a 2 s. c o m * @return a file to a spreadsheet */ public static File convertToExcel(final File toFile, final String title, final TableModel tableModel) { if (toFile == null) { UIRegistry.showLocalizedMsg("WARNING", "FILE_NO_EXISTS", toFile != null ? toFile.getAbsolutePath() : ""); return null; } if (tableModel != null && tableModel.getRowCount() > 0) { try { // create a new file FileOutputStream out; try { out = new FileOutputStream(toFile); } catch (FileNotFoundException ex) { UIRegistry.showLocalizedMsg("WARNING", "FILE_NO_WRITE", toFile != null ? toFile.getAbsolutePath() : ""); return null; } // create a new workbook HSSFWorkbook wb = new HSSFWorkbook(); // create a new sheet HSSFSheet sheet = wb.createSheet(); // declare a row object reference // Header Captions HSSFFont headerFont = wb.createFont(); headerFont.setFontHeightInPoints((short) 12); headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // create a style for the header cell HSSFCellStyle headerStyle = wb.createCellStyle(); headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); headerStyle.setFont(headerFont); setBordersOnStyle(headerStyle, HSSFColor.GREY_25_PERCENT.index, HSSFCellStyle.BORDER_THIN); short numColumns = (short) tableModel.getColumnCount(); HSSFRow headerRow = sheet.createRow(0); for (int i = 0; i < numColumns; i++) { HSSFCell headerCell = headerRow.createCell((short) i); headerCell.setCellStyle(headerStyle); //add the date to the header cell headerCell.setCellValue(tableModel.getColumnName(i)); sheet.setColumnWidth((short) i, (short) (30 * 256)); } //-------------------------- // done header //-------------------------- // create 3 cell styles HSSFCellStyle oddCellStyle = wb.createCellStyle(); HSSFCellStyle evenCellStyle = wb.createCellStyle(); setBordersOnStyle(oddCellStyle, HSSFColor.GREY_25_PERCENT.index, HSSFCellStyle.BORDER_THIN); setBordersOnStyle(evenCellStyle, HSSFColor.GREY_25_PERCENT.index, HSSFCellStyle.BORDER_THIN); // create 2 fonts objects HSSFFont cellFont = wb.createFont(); //set font 1 to 12 point type cellFont.setFontHeightInPoints((short) 11); oddCellStyle.setFont(cellFont); evenCellStyle.setFont(cellFont); evenCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); oddCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); oddCellStyle.setFillForegroundColor(HSSFColor.WHITE.index); evenCellStyle.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index); oddCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); evenCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // set the sheet name to HSSF Test wb.setSheetName(0, title); for (short rownum = 0; rownum < (short) tableModel.getRowCount(); rownum++) { // create a row HSSFRow row = sheet.createRow(rownum + 1); for (short cellnum = (short) 0; cellnum < numColumns; cellnum++) { // create a numeric cell HSSFCell cell = row.createCell(cellnum); Object dataVal = tableModel.getValueAt(rownum, cellnum); cell.setCellValue(dataVal != null ? dataVal.toString() : ""); // on every other row cell.setCellStyle((rownum % 2) == 0 ? evenCellStyle : oddCellStyle); } } // write the workbook to the output stream // close our file (don't blow out our file handles wb.write(out); out.close(); } catch (Exception ex) { edu.ku.brc.af.core.UsageTracker.incrHandledUsageCount(); edu.ku.brc.exceptions.ExceptionTracker.getInstance().capture(TableModel2Excel.class, ex); log.error("convertToExcel", ex); //$NON-NLS-1$ } } return toFile; }
From source file:edu.ku.brc.specify.tasks.subpane.wb.XLSExport.java
License:Open Source License
public void writeData(final List<?> data) throws Exception { HSSFWorkbook workBook = new HSSFWorkbook(); HSSFSheet workSheet = workBook.createSheet(); DocumentSummaryInformation mappings = null; int rowNum = 0; if (config.getFirstRowHasHeaders() && !config.getAppendData()) { writeHeaders(workSheet);//from w ww . ja va2s . c o m rowNum++; String[] headers = config.getHeaders(); for (int i = 0; i < headers.length; i++) { workSheet.setColumnWidth(i, StringUtils.isNotEmpty(headers[i]) ? (256 * headers[i].length()) : 2560); } WorkbenchTemplate wbTemplate = null; if (data.get(0) instanceof WorkbenchTemplate) { wbTemplate = (WorkbenchTemplate) data.get(0); } else { wbTemplate = ((WorkbenchRow) data.get(0)).getWorkbench().getWorkbenchTemplate(); } mappings = writeMappings(wbTemplate); } //assuming data is never empty. boolean hasTemplate = data.get(0) instanceof WorkbenchTemplate; boolean hasRows = hasTemplate ? data.size() > 1 : data.size() > 0; if (hasRows) { int[] disciplinees; WorkbenchRow wbRow = (WorkbenchRow) data.get(hasTemplate ? 1 : 0); Workbench workBench = wbRow.getWorkbench(); WorkbenchTemplate template = workBench.getWorkbenchTemplate(); int numCols = template.getWorkbenchTemplateMappingItems().size(); int geoDataCol = -1; Vector<Integer> imgCols = new Vector<Integer>(); disciplinees = bldColTypes(template); for (Object rowObj : data) { if (rowObj instanceof WorkbenchTemplate) { continue; } WorkbenchRow row = (WorkbenchRow) rowObj; HSSFRow hssfRow = workSheet.createRow(rowNum++); int colNum; boolean rowHasGeoData = false; for (colNum = 0; colNum < numCols; colNum++) { HSSFCell cell = hssfRow.createCell(colNum); cell.setCellType(disciplinees[colNum]); setCellValue(cell, row.getData(colNum)); } if (row.getBioGeomancerResults() != null && !row.getBioGeomancerResults().equals("")) { geoDataCol = colNum; rowHasGeoData = true; HSSFCell cell = hssfRow.createCell(colNum++); cell.setCellType(HSSFCell.CELL_TYPE_STRING); setCellValue(cell, row.getBioGeomancerResults()); } // if (row.getCardImage() != null) if (row.getRowImage(0) != null) { if (!rowHasGeoData) { colNum++; } int imgIdx = 0; WorkbenchRowImage img = row.getRowImage(imgIdx++); while (img != null) { if (imgCols.indexOf(colNum) < 0) { imgCols.add(colNum); } HSSFCell cell = hssfRow.createCell(colNum++); cell.setCellType(HSSFCell.CELL_TYPE_STRING); String cellValue = img.getCardImageFullPath(); String attachToTbl = img.getAttachToTableName(); if (attachToTbl != null) { cellValue += "\t" + attachToTbl; } setCellValue(cell, cellValue); img = row.getRowImage(imgIdx++); } } } if (imgCols.size() > 0 || geoDataCol != -1) { writeExtraHeaders(workSheet, imgCols, geoDataCol); } } try { // Write the workbook File file = new File(getConfig().getFileName()); if (file.canWrite() || (!file.exists() && file.createNewFile())) { FileOutputStream fos = new FileOutputStream(file); workBook.write(fos); fos.close(); //Now write the mappings. //NOT (hopefully) the best way to write the mappings, but (sadly) the easiest way. //May need to do this another way if this slows performance for big wbs. if (mappings != null) { InputStream is = new FileInputStream(file); POIFSFileSystem poifs = new POIFSFileSystem(is); is.close(); mappings.write(poifs.getRoot(), DocumentSummaryInformation.DEFAULT_STREAM_NAME); fos = new FileOutputStream(file); poifs.writeFilesystem(fos); fos.close(); } } else { UIRegistry.displayErrorDlgLocalized("WB_EXPORT_PERM_ERR"); } } catch (Exception e) { edu.ku.brc.af.core.UsageTracker.incrHandledUsageCount(); edu.ku.brc.exceptions.ExceptionTracker.getInstance().capture(XLSExport.class, e); throw (e); } }
From source file:eionet.gdem.conversion.excel.writer.ExcelConversionHandler.java
License:Mozilla Public License
@Override public void addCell(String type, String str_value, String style_name) { HSSFSheet _sheet = wb.getSheetAt(currentSheet); HSSFRow _row = _sheet.getRow(currentRow); HSSFCell _cell = _row.createCell((currentCell)); Double number_value = null;/*from ww w . j av a2 s. co m*/ Boolean boolean_value = null; boolean isNumber = false; boolean isBoolean = false; if (type == null) { type = (String) getDefaultParams("data_type"); } if (type != null) { if (type.equals("float") || type.equals("number")) { if (str_value != null) { try { number_value = new Double(str_value); isNumber = true; } catch (Exception e) { // the value is not number, it will be inserted as a string // System.out.println(e.toString()); } } else { isNumber = true; } } else if (type.equals("boolean")) { if (str_value != null) { try { boolean_value = new Boolean(str_value); isBoolean = true; } catch (Exception e) { // the value is not boolean, it will be inserted as a string // System.out.println(e.toString()); } } else { isBoolean = true; } } else if (type.equals("date")) { if (str_value != null) { try { // cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("yyyymmdd")); /* * * The way how to handle user defined formats not supported right now HSSFDataFormat format = * wb.createDataFormat(); HSSFCellStyle style = wb.createCellStyle(); * style.setDataFormat(format.getFormat("yyyymmdd")); _cell.setCellStyle(style); */ // cellStyle.setDataFormat(new HSSFDataFormat("yyyymmdd")); /* * try{ l_value=Long.parseLong(value); System.out.println(String.valueOf(l_value)); isLong=true; } * catch(Exception e){ System.out.println(e.toString()); } */ /* * if (isLong){ Date d = new Date(); _cell.setCellStyle(cellStyle); //_cell.setCellValue(d); * _cell.setCellValue(value); //System.out.println(d.toString()); isDate=true; } else * _cell.setCellValue(value); */ // System.out.println("hh"); } catch (Exception e) { System.out.println(e.toString()); } } } } if (isNumber) { if (number_value != null) { _cell.setCellValue(number_value.doubleValue()); } _cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); } else if (isBoolean) { if (boolean_value != null) { _cell.setCellValue(boolean_value.booleanValue()); } _cell.setCellType(HSSFCell.CELL_TYPE_BOOLEAN); } else { _cell.setCellType(HSSFCell.CELL_TYPE_STRING); // _cell.setEncoding(HSSFCell.ENCODING_UTF_16 );// _cell.setCellValue(str_value); } short idx = -1; if (style_name != null) { idx = getStyleIdxByName(style_name, ExcelStyleIF.STYLE_FAMILY_TABLE_CELL); } if (idx < 0) { Short short_idx = (Short) getDefaultParams("style"); if (short_idx != null) { idx = short_idx.shortValue(); } } if (idx > -1) { _cell.setCellStyle(wb.getCellStyleAt(idx)); } // calculates the col with according to the first row if (currentRow == 0 && idx > -1) { short colStyleWidth = 0; HSSFCellStyle style = wb.getCellStyleAt(idx); int f_i = style.getFontIndex(); HSSFFont font = wb.getFontAt((short) f_i); // character size short size = font.getFontHeightInPoints(); if (columns.size() > currentCell) { RowColumnDefinition column = columns.get(currentCell); String column_style_name = column.getStyleName() == null ? "" : column.getStyleName(); ExcelStyleIF definedStyle = getStyleByName(column_style_name, ExcelStyleIF.STYLE_FAMILY_TABLE_CELL); if (definedStyle != null) { colStyleWidth = definedStyle.getColumnWidth(); } } short width = (short) (_sheet.getDefaultColumnWidth() * size * 25); if (colStyleWidth > 0) { width = colStyleWidth; } else if (str_value.length() > 0) { width = (short) (str_value.length() * size * 50); } _sheet.setColumnWidth(currentCell, width); } currentCell = _cell.getColumnIndex() + 1; // System.out.println("Cell" + currentCell+ "-" + value); }
From source file:esd.common.PoiCreateExcel.java
License:Open Source License
/** * ???/*from ww w . jav a 2 s . co m*/ * * @param FilePath * @param jobList * @return */ public static boolean createJobExcel(String FilePath, List<Job> jobList) { // Excel Workbook,excel HSSFWorkbook wb = new HSSFWorkbook(); // Excelsheet,exceltab HSSFSheet sheet = wb.createSheet("sheet1"); // excel? sheet.setColumnWidth(0, 4000); sheet.setColumnWidth(1, 3500); // Excel? HSSFRow headRow = sheet.createRow(0); HSSFCell headell = headRow.createCell(0); // ??? headell = headRow.createCell(0); headell.setCellValue("????"); headell = headRow.createCell(1); headell.setCellValue("?"); headell = headRow.createCell(2); headell.setCellValue(""); headell = headRow.createCell(3); headell.setCellValue(""); headell = headRow.createCell(4); headell.setCellValue("???"); headell = headRow.createCell(5); headell.setCellValue(""); headell = headRow.createCell(6); headell.setCellValue("?"); headell = headRow.createCell(7); headell.setCellValue("???"); headell = headRow.createCell(8); headell.setCellValue("?"); headell = headRow.createCell(9); headell.setCellValue("?"); headell = headRow.createCell(10); headell.setCellValue("??"); headell = headRow.createCell(11); headell.setCellValue("?"); headell = headRow.createCell(12); headell.setCellValue("?"); headell = headRow.createCell(13); headell.setCellValue("??"); headell = headRow.createCell(14); headell.setCellValue(""); headell = headRow.createCell(15); headell.setCellValue("? "); headell = headRow.createCell(16); headell.setCellValue("?"); headell = headRow.createCell(17); headell.setCellValue(""); headell = headRow.createCell(18); headell.setCellValue("????"); headell = headRow.createCell(19); headell.setCellValue("????"); headell = headRow.createCell(20); headell.setCellValue("?/?"); headell = headRow.createCell(21); headell.setCellValue(""); headell = headRow.createCell(22); headell.setCellValue("?"); headell = headRow.createCell(23); headell.setCellValue("??"); headell = headRow.createCell(24); headell.setCellValue("??"); for (int i = 1; i <= jobList.size(); i++) { Job job = jobList.get(i - 1); // Excel? HSSFRow row = sheet.createRow(i); HSSFCell cell = row.createCell(0); // ??? cell = row.createCell(0); cell.setCellValue(job.getName()); cell = row.createCell(1); cell.setCellValue(job.getHireNumber()); cell = row.createCell(2); cell.setCellValue(job.getSalary()); cell = row.createCell(3); cell.setCellValue(job.getEducation()); cell = row.createCell(4); cell.setCellValue(job.getExperience()); cell = row.createCell(5); cell.setCellValue(job.getGender()); cell = row.createCell(6); cell.setCellValue(job.getAge()); cell = row.createCell(7); cell.setCellValue(job.getDescription()); cell = row.createCell(8); cell.setCellValue(job.getProvideBenefit()); cell = row.createCell(9); cell.setCellValue(job.getContactPerson()); cell = row.createCell(10); cell.setCellValue(job.getContactTel()); cell = row.createCell(11); cell.setCellValue(job.getContactEmail()); if (job.getViewCount() != null) { cell = row.createCell(12); cell.setCellValue(job.getViewCount()); } cell = row.createCell(13); cell.setCellValue(job.getNature()); cell = row.createCell(14); cell.setCellValue(job.getEffectiveDays()); cell = row.createCell(15); // cell.setCellValue(job.getIsActiveEffectiveTime()); if (job.getEffectiveTime() != null) { SimpleDateFormat dateFm = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); // ?? String effectiveTimeString = dateFm.format(job.getEffectiveTime()); cell = row.createCell(16); cell.setCellValue(effectiveTimeString); } if (job.getWorkPlace() != null) { if (job.getWorkPlace().getCode() != null && !"".equals(job.getWorkPlace().getCode())) { cell = row.createCell(17); cell.setCellValue(job.getWorkPlace().getName()); } } if (job.isBed() == true) { cell = row.createCell(18); cell.setCellValue(""); } else if (job.isBed() == false) { cell = row.createCell(18); cell.setCellValue("?"); } if (job.isLunch() == true) { cell = row.createCell(19); cell.setCellValue(""); } else if (job.isLunch() == false) { cell = row.createCell(19); cell.setCellValue("?"); } cell = row.createCell(20); cell.setCellValue(job.getCheckStatus()); cell = row.createCell(21); cell.setCellValue(job.getMark()); if (job.getCompany() != null) { cell = row.createCell(22); cell.setCellValue(job.getCompany().getName()); } if (job.getArea() != null) { if (job.getArea().getCode() != null && !"".equals(job.getArea().getCode())) { cell = row.createCell(23); cell.setCellValue(job.getArea().getName()); } } if (job.getJobCategory() != null) { if (job.getJobCategory().getCode() != null && !"".equals(job.getJobCategory().getCode())) { cell = row.createCell(24); cell.setCellValue(job.getJobCategory().getName()); } } } try { FileOutputStream os = new FileOutputStream(FilePath); wb.write(os); os.flush(); os.close(); jobList.clear(); jobList = null; os = null; wb = null; System.gc(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } return true; }