List of usage examples for org.apache.poi.hssf.usermodel HSSFWorkbook createDataFormat
@Override
public HSSFDataFormat createDataFormat()
From source file:org.orbeon.oxf.processor.generator.XLSGenerator.java
License:Open Source License
@Override public ProcessorOutput createOutput(String name) { ProcessorOutput output = new ProcessorOutputImpl(XLSGenerator.this, name) { public void readImpl(PipelineContext context, XMLReceiver xmlReceiver) { try { // Read binary content of uploaded Excel file final byte[] fileContent; {// www.j a v a 2 s . c o m final String NO_FILE = "No file was uploaded"; final DocumentInfo requestDocument = readInputAsTinyTree(context, getInputByName(INPUT_REQUEST), XPathCache.getGlobalConfiguration()); final PooledXPathExpression expr = XPathCache.getXPathExpression( requestDocument.getConfiguration(), requestDocument, "/request/parameters/parameter[1]/value", getLocationData()); final Element valueElement = (Element) expr.evaluateSingleToJavaReturnToPoolOrNull(); if (valueElement == null) throw new OXFException(NO_FILE); String type = valueElement.attributeValue(XMLConstants.XSI_TYPE_QNAME); if (type == null) throw new OXFException(NO_FILE); if (type.endsWith("anyURI")) { // Read file from disk String url = valueElement.getStringValue(); InputStream urlInputStream = new URL(url).openStream(); byte[] buffer = new byte[1024]; ByteArrayOutputStream fileByteArray = new ByteArrayOutputStream(); int size; while ((size = urlInputStream.read(buffer)) != -1) fileByteArray.write(buffer, 0, size); urlInputStream.close(); fileContent = fileByteArray.toByteArray(); } else { // Decode base64 fileContent = Base64.decode(valueElement.getStringValue()); } } // Generate XML from Excel file final java.io.ByteArrayInputStream bais = new ByteArrayInputStream(fileContent); final org.dom4j.Document d = extractFromXLS(bais); final DOMGenerator domGenerator = new DOMGenerator(d, "xls generator output", DOMGenerator.ZeroValidity, DOMGenerator.DefaultContext); domGenerator.createOutput(OUTPUT_DATA).read(context, xmlReceiver); } catch (IOException e) { throw new OXFException(e); } } private Document extractFromXLS(InputStream inputStream) throws IOException { // Create workbook HSSFWorkbook workbook = new HSSFWorkbook(new POIFSFileSystem(inputStream)); // Create document final NonLazyUserDataElement root = new NonLazyUserDataElement("workbook"); final Document resultDocument = new NonLazyUserDataDocument(root); // Add elements for each sheet for (int i = 0; i < workbook.getNumberOfSheets(); i++) { HSSFSheet sheet = workbook.getSheetAt(i); final Element element = new NonLazyUserDataElement("sheet"); resultDocument.getRootElement().add(element); // Go though each cell XLSUtils.walk(workbook.createDataFormat(), sheet, new XLSUtils.Handler() { public void cell(HSSFCell cell, String sourceXPath, String targetXPath) { if (targetXPath != null) { int cellType = cell.getCellType(); String value = null; switch (cellType) { case HSSFCell.CELL_TYPE_STRING: case HSSFCell.CELL_TYPE_BLANK: value = cell.getStringCellValue(); break; case HSSFCell.CELL_TYPE_NUMERIC: double doubleValue = cell.getNumericCellValue(); if (((double) ((int) doubleValue)) == doubleValue) { // This is an integer value = Integer.toString((int) doubleValue); } else { // This is a floating point number value = XMLUtils.removeScientificNotation(doubleValue); } break; } if (value == null) throw new OXFException("Unkown cell type " + cellType + " for XPath expression '" + targetXPath + "'"); addToElement(element, targetXPath, value); } } }); } return resultDocument; } private void addToElement(Element element, String xpath, String value) { StringTokenizer elements = new StringTokenizer(xpath, "/"); while (elements.hasMoreTokens()) { String name = elements.nextToken(); if (elements.hasMoreTokens()) { // Not the last: try to find sub element, otherwise create Element child = element.element(name); if (child == null) { child = new NonLazyUserDataElement(name); element.add(child); } element = child; } else { // Last: add element, set content to value Element child = new NonLazyUserDataElement(name); child.add(Dom4jUtils.createText(value)); element.add(child); } } } }; addOutput(name, output); return output; }
From source file:org.orbeon.oxf.processor.serializer.legacy.XLSSerializer.java
License:Open Source License
protected void readInput(final PipelineContext pipelineContext, ProcessorInput input, Config config, OutputStream outputStream) { try {// w w w . j av a 2 s. c om Document dataDocument = readInputAsDOM4J(pipelineContext, INPUT_DATA); final DocumentWrapper wrapper = new DocumentWrapper(dataDocument, null, XPathCache.getGlobalConfiguration()); Document configDocument = readInputAsDOM4J(pipelineContext, INPUT_CONFIG); // Read template sheet String templateName = configDocument.getRootElement().attributeValue("template"); //String fileName = configDocument.getRootElement().attributeValue("filename"); InputStream templateInputStream = URLFactory.createURL(templateName).openStream(); final HSSFWorkbook workbook = new HSSFWorkbook(new POIFSFileSystem(templateInputStream)); final HSSFDataFormat dataFormat = workbook.createDataFormat(); templateInputStream.close(); int sheetIndex = 0; PooledXPathExpression expr = XPathCache.getXPathExpression(wrapper.getConfiguration(), wrapper, "/workbook/sheet", getLocationData()); List<Object> nodes = expr.evaluateToJavaReturnToPool(); for (Iterator i = nodes.iterator(); i.hasNext();) { final Element sheetElement = (Element) i.next(); HSSFSheet sheet = workbook.cloneSheet(0); workbook.setSheetName(sheetIndex + 1, sheetElement.attributeValue("name")); // Duplicate rows if we find a "repeat-row" in the config for (Iterator j = configDocument.selectNodes("/config/repeat-row").iterator(); j.hasNext();) { // Get info about row to repeat Element repeatRowElement = (Element) j.next(); final int rowNum = Integer.parseInt(repeatRowElement.attributeValue("row-num")); final String forEach = repeatRowElement.attributeValue("for-each"); HSSFRow templateRow = sheet.getRow(rowNum); int repeatCount = ((Double) sheetElement.selectObject("count(" + forEach + ")")).intValue(); // Move existing rows lower int lastRowNum = sheet.getLastRowNum(); for (int k = lastRowNum; k > rowNum; k--) { HSSFRow sourceRow = sheet.getRow(k); HSSFRow newRow = sheet.createRow(k + repeatCount - 1); XLSUtils.copyRow(workbook, newRow, sourceRow); } // Create rows, copying the template row for (int k = rowNum + 1; k < rowNum + repeatCount; k++) { HSSFRow newRow = sheet.createRow(k); XLSUtils.copyRow(workbook, newRow, templateRow); } // Modify the XPath expression on each row for (int k = rowNum; k < rowNum + repeatCount; k++) { HSSFRow newRow = sheet.getRow(k); for (short m = 0; m <= newRow.getLastCellNum(); m++) { HSSFCell cell = newRow.getCell(m); if (cell != null) { String currentFormat = dataFormat.getFormat(cell.getCellStyle().getDataFormat()); final Matcher matcher = FORMAT_XPATH.matcher(currentFormat); if (matcher.find()) { String newFormat = matcher.group(1) + "\"" + forEach + "[" + (k - rowNum + 1) + "]/" + matcher.group(2) + "\""; cell.getCellStyle().setDataFormat(dataFormat.getFormat(newFormat)); } } } } } // Set values in cells with an XPath expression XLSUtils.walk(dataFormat, sheet, new XLSUtils.Handler() { public void cell(HSSFCell cell, String sourceXPath, String targetXPath) { if (sourceXPath.charAt(0) == '/') sourceXPath = sourceXPath.substring(1); // Set cell value PooledXPathExpression expr = XPathCache.getXPathExpression(wrapper.getConfiguration(), wrapper.wrap(sheetElement), "string(" + sourceXPath + ")", getLocationData()); String newValue = (String) expr.evaluateSingleToJavaReturnToPoolOrNull(); if (newValue == null) { throw new OXFException("Nothing matches the XPath expression '" + sourceXPath + "' in the input document"); } try { cell.setCellValue(Double.parseDouble(newValue)); } catch (NumberFormatException e) { cell.setCellValue(newValue); } // Set cell format Object element = sheetElement.selectObject(sourceXPath); if (element instanceof Element) { // NOTE: We might want to support other properties here String bold = ((Element) element).attributeValue("bold"); if (bold != null) { HSSFFont originalFont = workbook.getFontAt(cell.getCellStyle().getFontIndex()); HSSFFont newFont = workbook.createFont(); XLSUtils.copyFont(newFont, originalFont); if ("true".equals(bold)) newFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); cell.getCellStyle().setFont(newFont); } } } }); sheetIndex++; } workbook.removeSheetAt(0); // Write out the workbook workbook.write(outputStream); } catch (IOException e) { throw new OXFException(e); } }
From source file:org.sakaiproject.assignment.impl.BaseAssignmentService.java
License:Educational Community License
/** * Access and output the grades spreadsheet for the reference, either for an assignment or all assignments in a context. * * @param out/* w w w. j av a2s . com*/ * The outputStream to stream the grades spreadsheet into. * @param ref * The reference, either to a specific assignment, or just to an assignment context. * @return Whether the grades spreadsheet is successfully output. * @throws IdUnusedException * if there is no object with this id. * @throws PermissionException * if the current user is not allowed to access this. */ public boolean getGradesSpreadsheet(final OutputStream out, final String ref) throws IdUnusedException, PermissionException { boolean retVal = false; String typeGradesString = REF_TYPE_GRADES + Entity.SEPARATOR; String context = ref.substring(ref.indexOf(typeGradesString) + typeGradesString.length()); // get site title for display purpose String siteTitle = ""; try { Site s = SiteService.getSite(context); siteTitle = s.getTitle(); } catch (Exception e) { // ignore exception M_log.debug(this + ":getGradesSpreadsheet cannot get site context=" + context + e.getMessage()); } // does current user allowed to grade any assignment? boolean allowGradeAny = false; List assignmentsList = getListAssignmentsForContext(context); for (int iAssignment = 0; !allowGradeAny && iAssignment < assignmentsList.size(); iAssignment++) { if (allowGradeSubmission(((Assignment) assignmentsList.get(iAssignment)).getReference())) { allowGradeAny = true; } } if (!allowGradeAny) { // not permitted to download the spreadsheet return false; } else { int rowNum = 0; HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet(WorkbookUtil.createSafeSheetName(siteTitle)); // Create a row and put some cells in it. Rows are 0 based. HSSFRow row = sheet.createRow(rowNum++); row.createCell(0).setCellValue(rb.getString("download.spreadsheet.title")); // empty line row = sheet.createRow(rowNum++); row.createCell(0).setCellValue(""); // site title row = sheet.createRow(rowNum++); row.createCell(0).setCellValue(rb.getString("download.spreadsheet.site") + siteTitle); // download time row = sheet.createRow(rowNum++); row.createCell(0).setCellValue( rb.getString("download.spreadsheet.date") + TimeService.newTime().toStringLocalFull()); // empty line row = sheet.createRow(rowNum++); row.createCell(0).setCellValue(""); HSSFCellStyle style = wb.createCellStyle(); // this is the header row number int headerRowNumber = rowNum; // set up the header cells row = sheet.createRow(rowNum++); int cellNum = 0; // user enterprise id column HSSFCell cell = row.createCell(cellNum++); cell.setCellStyle(style); cell.setCellValue(rb.getString("download.spreadsheet.column.name")); // user name column cell = row.createCell(cellNum++); cell.setCellStyle(style); cell.setCellValue(rb.getString("download.spreadsheet.column.userid")); // starting from this row, going to input user data Iterator assignments = new SortedIterator(assignmentsList.iterator(), new AssignmentComparator("duedate", "true")); // site members excluding those who can add assignments List members = new ArrayList(); // hashmap which stores the Excel row number for particular user HashMap user_row = new HashMap(); List allowAddAnySubmissionUsers = allowAddAnySubmissionUsers(context); for (Iterator iUserIds = new SortedIterator(allowAddAnySubmissionUsers.iterator(), new AssignmentComparator("sortname", "true")); iUserIds.hasNext();) { String userId = (String) iUserIds.next(); try { User u = UserDirectoryService.getUser(userId); members.add(u); // create the column for user first row = sheet.createRow(rowNum); // update user_row Hashtable user_row.put(u.getId(), Integer.valueOf(rowNum)); // increase row rowNum++; // put user displayid and sortname in the first two cells cellNum = 0; row.createCell(cellNum++).setCellValue(u.getSortName()); row.createCell(cellNum).setCellValue(u.getDisplayId()); } catch (Exception e) { M_log.warn(" getGradesSpreadSheet " + e.getMessage() + " userId = " + userId); } } int index = 0; // the grade data portion starts from the third column, since the first two are used for user's display id and sort name while (assignments.hasNext()) { Assignment a = (Assignment) assignments.next(); int assignmentType = a.getContent().getTypeOfGrade(); // for column header, check allow grade permission based on each assignment if (!a.getDraft() && allowGradeSubmission(a.getReference())) { // put in assignment title as the column header rowNum = headerRowNumber; row = sheet.getRow(rowNum++); cellNum = (index + 2); cell = row.createCell(cellNum); // since the first two column is taken by student id and name cell.setCellStyle(style); cell.setCellValue(a.getTitle()); for (int loopNum = 0; loopNum < members.size(); loopNum++) { // prepopulate the column with the "no submission" string row = sheet.getRow(rowNum++); cell = row.createCell(cellNum); cell.setCellType(1); cell.setCellValue(rb.getString("listsub.nosub")); } // begin to populate the column for this assignment, iterating through student list for (Iterator sIterator = getSubmissions(a).iterator(); sIterator.hasNext();) { AssignmentSubmission submission = (AssignmentSubmission) sIterator.next(); String userId = submission.getSubmitterId(); if (a.isGroup()) { User[] _users = submission.getSubmitters(); for (int i = 0; _users != null && i < _users.length; i++) { userId = _users[i].getId(); if (user_row.containsKey(userId)) { // find right row row = sheet.getRow(((Integer) user_row.get(userId)).intValue()); if (submission.getGraded() && submission.getGrade() != null) { // graded and released if (assignmentType == 3) { try { // numeric cell type? String grade = submission.getGradeForUser(userId) == null ? submission.getGradeDisplay() : submission.getGradeForUser(userId); //We get float number no matter the locale it was managed with. NumberFormat nbFormat = FormattedText.getNumberFormat(1, 1, null); float f = nbFormat.parse(grade).floatValue(); // remove the String-based cell first cell = row.getCell(cellNum); row.removeCell(cell); // add number based cell cell = row.createCell(cellNum); cell.setCellType(0); cell.setCellValue(f); style = wb.createCellStyle(); style.setDataFormat(wb.createDataFormat().getFormat("#,##0.0")); cell.setCellStyle(style); } catch (Exception e) { // if the grade is not numeric, let's make it as String type // No need to remove the cell and create a new one, as the existing one is String type. cell = row.getCell(cellNum); cell.setCellType(1); cell.setCellValue(submission.getGradeForUser(userId) == null ? submission.getGradeDisplay() : submission.getGradeForUser(userId)); } } else { // String cell type cell = row.getCell(cellNum); cell.setCellValue(submission.getGradeForUser(userId) == null ? submission.getGradeDisplay() : submission.getGradeForUser(userId)); } } else if (submission.getSubmitted() && submission.getTimeSubmitted() != null) { // submitted, but no grade available yet cell = row.getCell(cellNum); cell.setCellValue(rb.getString("gen.nograd")); } } // if } } else { if (user_row.containsKey(userId)) { // find right row row = sheet.getRow(((Integer) user_row.get(userId)).intValue()); if (submission.getGraded() && submission.getGrade() != null) { // graded and released if (assignmentType == 3) { try { // numeric cell type? String grade = submission.getGradeDisplay(); //We get float number no matter the locale it was managed with. NumberFormat nbFormat = FormattedText.getNumberFormat(1, 1, null); float f = nbFormat.parse(grade).floatValue(); // remove the String-based cell first cell = row.getCell(cellNum); row.removeCell(cell); // add number based cell cell = row.createCell(cellNum); cell.setCellType(0); cell.setCellValue(f); style = wb.createCellStyle(); style.setDataFormat(wb.createDataFormat().getFormat("#,##0.0")); cell.setCellStyle(style); } catch (Exception e) { // if the grade is not numeric, let's make it as String type // No need to remove the cell and create a new one, as the existing one is String type. cell = row.getCell(cellNum); cell.setCellType(1); // Setting grade display instead grade. cell.setCellValue(submission.getGradeDisplay()); } } else { // String cell type cell = row.getCell(cellNum); cell.setCellValue(submission.getGradeDisplay()); } } else if (submission.getSubmitted() && submission.getTimeSubmitted() != null) { // submitted, but no grade available yet cell = row.getCell(cellNum); cell.setCellValue(rb.getString("gen.nograd")); } } // if } } } index++; } // output try { wb.write(out); retVal = true; } catch (IOException e) { M_log.warn(" getGradesSpreadsheet Can not output the grade spread sheet for reference= " + ref); } return retVal; } }
From source file:org.sevenorcas.style.app.mod.ss.SpreadsheetCell.java
/** * Get cell format//from w ww . j a v a 2 s . c om * @param wb * @return */ public HSSFCellStyle getCellStyle(HSSFWorkbook wb) { //EX1 if (sheet.containsStyleId(styleId)) { return sheet.getStyle(styleId); } HSSFCellStyle style = wb.createCellStyle(); Integer clazzX = clazz != null ? clazz : (headerCell != null ? headerCell.clazz : null); switch (clazzX != null ? clazzX : CLASS_STRING) { case CLASS_DATE: if (!isHeader()) { CreationHelper createHelper = wb.getCreationHelper(); style = wb.createCellStyle(); style.setDataFormat(createHelper.createDataFormat() .getFormat(sheet.getDateFormat() != null ? sheet.getDateFormat() : "m/d/yy")); } style.setAlignment(HSSFCellStyle.ALIGN_RIGHT); break; case CLASS_PERCENTAGE: style = wb.createCellStyle(); style.setDataFormat(wb.createDataFormat().getFormat("0.00%")); style.setAlignment(HSSFCellStyle.ALIGN_RIGHT); break; case CLASS_INTEGER: case CLASS_LONG: style.setAlignment(HSSFCellStyle.ALIGN_RIGHT); break; case CLASS_DOUBLE: case CLASS_UKURS: //Needs work // if (numberFormat != null){ // createHelper = wb.getCreationHelper(); // style = wb.createCellStyle(); // style.setDataFormat(createHelper.createDataFormat().getFormat(numberFormat)); // } style.setAlignment(HSSFCellStyle.ALIGN_RIGHT); break; case CLASS_INTEGER_LEFT: case CLASS_STRING: case CLASS_BOOLEAN: case CLASS_CHARACTER: default: style.setAlignment(HSSFCellStyle.ALIGN_LEFT); } sheet.setCellStyle(wb, style, this, styleId); return style; }
From source file:org.sigmah.server.endpoint.export.sigmah.spreadsheet.ExcelUtils.java
License:Open Source License
public ExcelUtils(final HSSFWorkbook wb) { this.wb = wb; createHelper = wb.getCreationHelper(); numberFormat = wb.createDataFormat(); }
From source file:org.tentackle.ui.FormTableUtilityPopup.java
License:Open Source License
/** * Converts the table to an excel spreadsheet. * @param file the output file//from w ww . j av a 2 s.co m * @param onlySelected true if export only selected rows * @throws IOException if export failed */ public void excel(File file, boolean onlySelected) throws IOException { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet(); TableModel model = table.getModel(); TableColumnModel columnModel = table.getColumnModel(); int[] selectedRows = onlySelected ? table.getSelectedRows() : new int[] {}; int rows = onlySelected ? selectedRows.length : model.getRowCount(); // number of data rows int cols = columnModel.getColumnCount(); // number of data columns short srow = 0; // current spreadsheet row // local copies cause might be changed String xTitle = this.title; String xIntro = this.intro; if (xTitle == null) { // get default from window title Window parent = FormHelper.getParentWindow(table); try { // paint page-title xTitle = ((FormWindow) parent).getTitle(); } catch (Exception e) { xTitle = null; } } if (xTitle != null) { HSSFRow row = sheet.createRow(srow); HSSFFont font = wb.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); HSSFCellStyle cs = wb.createCellStyle(); cs.setAlignment(HSSFCellStyle.ALIGN_CENTER); cs.setFont(font); HSSFCell cell = row.createCell(0); cell.setCellStyle(cs); cell.setCellValue(new HSSFRichTextString(xTitle)); // region rowFrom, colFrom, rowTo, colTo sheet.addMergedRegion(new CellRangeAddress(0, srow, 0, cols - 1)); srow++; } if (xIntro != null || onlySelected) { HSSFRow row = sheet.createRow(srow); HSSFCell cell = row.createCell(0); HSSFCellStyle cs = wb.createCellStyle(); cs.setAlignment(HSSFCellStyle.ALIGN_LEFT); cs.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); cs.setWrapText(true); cell.setCellStyle(cs); if (onlySelected) { if (xIntro == null) { xIntro = ""; } else { xIntro += ", "; } xIntro += Locales.bundle.getString("<nur_selektierte_Zeilen>"); } cell.setCellValue(new HSSFRichTextString(xIntro)); sheet.addMergedRegion(new CellRangeAddress(srow, srow + 2, 0, cols - 1)); srow += 3; } // column headers boolean isAbstractFormTableModel = model instanceof AbstractFormTableModel; srow++; // always skip one line HSSFRow row = sheet.createRow(srow); HSSFFont font = wb.createFont(); font.setItalic(true); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); HSSFCellStyle cs = wb.createCellStyle(); cs.setAlignment(HSSFCellStyle.ALIGN_CENTER); cs.setFont(font); for (int c = 0; c < cols; c++) { HSSFCell cell = row.createCell(c); cell.setCellValue(new HSSFRichTextString(isAbstractFormTableModel ? ((AbstractFormTableModel) model) .getDisplayedColumnName(columnModel.getColumn(c).getModelIndex()) : model.getColumnName(columnModel.getColumn(c).getModelIndex()))); cell.setCellStyle(cs); } srow++; // default cell-style for date HSSFCellStyle dateStyle = wb.createCellStyle(); dateStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy")); // cellstyles for numbers List<HSSFCellStyle> numberStyles = new ArrayList<HSSFCellStyle>(); HSSFDataFormat format = wb.createDataFormat(); for (int r = 0; r < rows; r++) { int modelRow = onlySelected ? selectedRows[r] : r; row = sheet.createRow(srow + (short) r); for (int i = 0; i < cols; i++) { int c = columnModel.getColumn(i).getModelIndex(); Object value = model.getValueAt(modelRow, c); HSSFCell cell = row.createCell(i); if (value instanceof Boolean) { cell.setCellValue(((Boolean) value).booleanValue()); } else if (value instanceof BMoney) { BMoney money = (BMoney) value; cell.setCellValue(money.doubleValue()); String fmt = "#,##0"; if (money.scale() > 0) { fmt += "."; for (int j = 0; j < money.scale(); j++) { fmt += "0"; } } // create format short fmtIndex = format.getFormat(fmt); // check if there is already a cellstyle with this scale Iterator<HSSFCellStyle> iter = numberStyles.iterator(); boolean found = false; while (iter.hasNext()) { cs = iter.next(); if (cs.getDataFormat() == fmtIndex) { // reuse that found = true; break; } } if (!found) { // create a new style cs = wb.createCellStyle(); cs.setDataFormat(fmtIndex); numberStyles.add(cs); } cell.setCellStyle(cs); } else if (value instanceof Number) { cell.setCellValue(((Number) value).doubleValue()); } else if (value instanceof Date) { cell.setCellValue((Date) value); cell.setCellStyle(dateStyle); } else if (value instanceof GregorianCalendar) { cell.setCellValue((GregorianCalendar) value); cell.setCellStyle(dateStyle); } else if (value != null) { cell.setCellValue(new HSSFRichTextString(value.toString())); } } } // set the width for each column for (int c = 0; c < cols; c++) { short width = (short) (columnModel.getColumn(c).getWidth() * 45); // is a reasonable value sheet.setColumnWidth(c, width); } // Write the output to a file FileOutputStream fileOut = new FileOutputStream(file); wb.write(fileOut); fileOut.close(); // open Excel URLHelper.openURL(file.getPath()); }
From source file:poi.hssf.usermodel.examples.InCellLists.java
License:Apache License
/** * Inserts a single bulleted item into a cell. * * @param workbook A reference to the HSSFWorkbook that 'contains' the * cell.// ww w. j a v a 2 s .c o m * @param listItem An instance of the String class encapsulating the * items text. * @param cell An instance of the HSSFCell class that encapsulates a * reference to the spreadsheet cell into which the list item * will be written. */ public void bulletedItemInCell(HSSFWorkbook workbook, String listItem, HSSFCell cell) { // A format String must be built to ensure that the contents of the // cell appear as a bulleted item. HSSFDataFormat format = workbook.createDataFormat(); String formatString = InCellLists.BULLET_CHARACTER + " @"; int formatIndex = format.getFormat(formatString); // Construct an HSSFCellStyle and set it's data formt to use the // object created above. HSSFCellStyle bulletStyle = workbook.createCellStyle(); bulletStyle.setDataFormat((short) formatIndex); // Set the cells contents and style. cell.setCellValue(new HSSFRichTextString(listItem)); cell.setCellStyle(bulletStyle); }
From source file:swift.selenium.WebHelper.java
License:Open Source License
public static void GetCellInfo(String FilePath, HSSFRow rowValues, int valuesRowIndex, int valuesRowCount) throws IOException // newly Added two Variables for Action Loop { try {//from w w w . java 2s . c om frmDate = new Date(); isDynamicNumFound = true; List<WebElement> controlList = null; //String testCase = null; String ctrlValue = null; //HSSFRow structureRow=null; InputStream myXls = new FileInputStream(FilePath); HSSFWorkbook workBook = new HSSFWorkbook(myXls); format = workBook.createDataFormat(); HSSFSheet sheetStructure = workBook.getSheet("Structure"); //HSSFCell controlValue=null; int rowCount = sheetStructure.getLastRowNum() + 1; HSSFSheet headerValues = ExcelUtility.GetSheet(FilePath, "Values"); //HSSFRow headerRow = headerValues.getRow(0); System.out.println(Automation.dtFormat.format(frmDate)); String fromDate = Automation.dtFormat.format(frmDate); TransactionMapping.report.setFromDate(fromDate); structureHeader = getValueFromHashMap(sheetStructure); columnName = null; int dynamicIndexNumber;//Added for Action Loop String imageType, indexVal, controlName, executeFlag, action, logicalName, controltype, controlID, dynamicIndex, newDynamicIndex, rowNo, colNo;//newly Added for Action Loop //Setting of default reporting values before executing a transaction TransactionMapping.report.setStrMessage(""); TransactionMapping.report.setStrStatus("PASS"); for (int rowIndex = 1; rowIndex < rowCount; rowIndex++) { //structureRow = sheetStructure.getRow(rowIndex); controlName = getCellData("ControlName", sheetStructure, rowIndex, structureHeader);//structureRow.getCell(3); executeFlag = getCellData("ExecuteFlag", sheetStructure, rowIndex, structureHeader);//structureRow.getCell(0); if (executeFlag.toString().equals("Y")) { WebElement webElement = null; imageType = getCellData("ImageType", sheetStructure, rowIndex, structureHeader); action = getCellData("Action", sheetStructure, rowIndex, structureHeader);//structureRow.getCell(1); logicalName = getCellData("LogicalName", sheetStructure, rowIndex, structureHeader);//structureRow.getCell(2); controltype = getCellData("ControlType", sheetStructure, rowIndex, structureHeader);//structureRow.getCell(4); controlID = getCellData("ControlID", sheetStructure, rowIndex, structureHeader);//structureRow.getCell(6); indexVal = getCellData("Index", sheetStructure, rowIndex, structureHeader);//structureRow.getCell(7); columnName = getCellData("ColumnName", sheetStructure, rowIndex, structureHeader); rowNo = getCellData("RowNo", sheetStructure, rowIndex, structureHeader); colNo = getCellData("ColumnNo", sheetStructure, rowIndex, structureHeader); dynamicIndex = getCellData("DynamicIndex", sheetStructure, rowIndex, structureHeader);//Added code for Loop if (action.equalsIgnoreCase("LOOP")) { loopRow = rowIndex + 1; } // if rownum != 1 , then do below steps if ((valuesRowIndex != ExcelUtility.firstRow) && (dynamicIndex.length() > 0)) //valuesRowIndex { dynamicIndexNumber = Integer .parseInt(dynamicIndex.substring(dynamicIndex.length() - 1, dynamicIndex.length())); if (ExcelUtility.dynamicNum == 0) { ExcelUtility.dynamicNum = dynamicIndexNumber + 1; isDynamicNumFound = false; } else if (ExcelUtility.dynamicNum != 0 && isDynamicNumFound) { ExcelUtility.dynamicNum = ExcelUtility.dynamicNum + 1; isDynamicNumFound = false; } newDynamicIndex = dynamicIndex.replace(String.valueOf(dynamicIndexNumber), String.valueOf(ExcelUtility.dynamicNum)); controlName = controlName.replace(dynamicIndex, newDynamicIndex); } /**Stop the execution of the current test case unexpected alert**/ control = controltype.toString(); if (!action.equalsIgnoreCase("LOOP") && !action.equalsIgnoreCase("END_LOOP")) { //boolean isControlValueFound =false; if (valuesHeader.isEmpty() == true) { valuesHeader = getValueFromHashMap(headerValues); } Object actualValue = null; if (logicalName != null) { actualValue = valuesHeader.get(logicalName.toString()); } //headerRow.getCell(colIndex); if (actualValue == null) { System.out.println("Null"); } else { //int colIndex = Integer.parseInt(actualValue.toString()); //controlValue = rowValues.getCell(colIndex); ctrlValue = getCellData(logicalName, headerValues, valuesRowIndex, valuesHeader); testcaseID = rowValues .getCell(Integer.parseInt(valuesHeader.get("TestCaseID").toString())); if (testcaseID == null) { testCase = ""; } else { testCase = testcaseID.toString(); } transactionType = rowValues .getCell(Integer.parseInt(valuesHeader.get("TransactionType").toString())); } if ((action.equals("I") && !ctrlValue.isEmpty()) || (action.equals("V") && !ctrlValue.isEmpty()) || !action.equals("I")) { if (!controltype.startsWith("Sikuli")) { if (!action.equalsIgnoreCase("LOOP") && !controltype.equalsIgnoreCase("Wait") && !action.equalsIgnoreCase("END_LOOP") && !controltype.equalsIgnoreCase("Browser") && !controltype.equalsIgnoreCase("Window") && !controltype.equalsIgnoreCase("Alert") && !controltype.equalsIgnoreCase("URL") && !controltype.equalsIgnoreCase("WaitForJS") && !controltype.contains("Robot") && !controltype.equalsIgnoreCase("BrowserType") && !controltype.equalsIgnoreCase("BrowserAuth") && !controltype.equalsIgnoreCase("Calendar") && !controltype.equalsIgnoreCase("CalendarNew") && !controltype.equalsIgnoreCase("CalendarIPF") && !controltype.equalsIgnoreCase("CalendarEBP") && (!action.equalsIgnoreCase("Read") || ((action.equalsIgnoreCase("Read") && !controlName.isEmpty()))) && !controltype.equalsIgnoreCase("JSScript") && !controltype.equalsIgnoreCase("DB") && !controlID.equalsIgnoreCase("XML") && !controltype.startsWith("Process") && !controltype.startsWith("Destroy") && !controltype.startsWith("ReadSikuli") && !controltype.equalsIgnoreCase("WebService") && !controltype.equalsIgnoreCase("WebService_REST") && !controltype.equalsIgnoreCase("JSONResp") && !action.equalsIgnoreCase("VA") && !action.equalsIgnoreCase("VA") && !controltype.equalsIgnoreCase("CaptureScreen") && !controltype.equalsIgnoreCase("CloseWindow") && !controlID.equalsIgnoreCase("DonutChart") && !controlID.equalsIgnoreCase("BarChart") && !action.equalsIgnoreCase("GB") && !controltype.equalsIgnoreCase("SwitchContext") && !controltype.equalsIgnoreCase("SwipeDown")) { if ((indexVal.equalsIgnoreCase("") || indexVal.equalsIgnoreCase("0")) && !controlID.equalsIgnoreCase("TagValue") && !controlID.equalsIgnoreCase("TagText")) { webElement = getElementByType(controlID, controlName, control, imageType, ctrlValue); } else { controlList = getElementsByType(controlID, controlName, control, imageType, ctrlValue); if (controlList != null && controlList.size() > 1) { webElement = GetControlByIndex(indexVal, controlList, controlID, controlName, control, ctrlValue); //, ISelenium selenium) } else { break; } } } } else { sikuliScreen = new Screen(); } } /*** Perform action on the identified control ***/ doAction(imageType, controltype, controlID, controlName, ctrlValue, logicalName, action, webElement, true, sheetStructure, headerValues, rowIndex, rowCount, rowNo, colNo); } if (action == "END_LOOP" && (valuesRowCount != valuesRowIndex)) { loopRow = 1; break; } } else { System.out.println("ExecuteFlag is N"); } } //Setting of reporting values after execution in case of no exception Date toDate = new Date(); TransactionMapping.report.setFromDate(Automation.dtFormat.format(frmDate)); TransactionMapping.report.setStrIteration(Automation.configHashMap.get("CYCLENUMBER").toString()); TransactionMapping.report.setStrTestcaseId(TransactionMapping.testCaseID.toString()); //TransactionMapping.report.setStrGroupName(MainController.controllerGroupName.toString()); TransactionMapping.report.setStrTrasactionType(TransactionMapping.transactionType.toString()); //TransactionMapping.report.setStrTestDescription(MainController.testDesciption); TransactionMapping.report.setToDate(Automation.dtFormat.format(toDate)); //Setting status for field verification failures if (fieldVerFailCount > 0) { TransactionMapping.report.setStrMessage("Check Detailed Results"); TransactionMapping.report.setStrStatus("FAIL"); } } catch (Exception e) { TransactionMapping.pauseFun(e.getMessage()); } finally { structureHeader.clear(); valuesHeader.clear(); ExcelUtility.writeReport(TransactionMapping.report); fieldVerFailCount = 0; } }
From source file:SwiftSeleniumWeb.WebHelper.java
License:Open Source License
public static void GetCellInfo(String FilePath, HSSFRow rowValues, int valuesRowIndex, int valuesRowCount) throws IOException // newly Added two Variables for Action Loop { try {//from w w w .j av a 2s. c om frmDate = new Date(); isDynamicNumFound = true; List<WebElement> controlList = null; String ctrlValue = null; InputStream myXls = new FileInputStream(FilePath); HSSFWorkbook workBook = new HSSFWorkbook(myXls); format = workBook.createDataFormat(); HSSFSheet sheetStructure = workBook.getSheet("Structure"); int rowCount = sheetStructure.getLastRowNum() + 1; HSSFSheet headerValues = ExcelUtility.GetSheet(FilePath, "Values"); System.out.println(Automation.dtFormat.format(frmDate)); String fromDate = Automation.dtFormat.format(frmDate); SwiftSeleniumWeb.WebDriver.report.setFromDate(fromDate); structureHeader = getValueFromHashMap(sheetStructure); columnName = null; int dynamicIndexNumber;//Added for Action Loop String imageType, indexVal, controlName, executeFlag, action, logicalName, controltype, controlID, dynamicIndex, newDynamicIndex, rowNo, colNo;//newly Added for Action Loop //Setting of default reporting values before executing a transaction SwiftSeleniumWeb.WebDriver.report.setStrMessage(""); SwiftSeleniumWeb.WebDriver.report.setStrStatus("PASS"); for (int rowIndex = 1; rowIndex < rowCount && !MainController.pauseExecution; rowIndex++) { controlName = getCellData("ControlName", sheetStructure, rowIndex, structureHeader); executeFlag = getCellData("ExecuteFlag", sheetStructure, rowIndex, structureHeader); if (executeFlag.toString().equals("Y")) { WebElement webElement = null; imageType = getCellData("ImageType", sheetStructure, rowIndex, structureHeader); action = getCellData("Action", sheetStructure, rowIndex, structureHeader); logicalName = getCellData("LogicalName", sheetStructure, rowIndex, structureHeader); controltype = getCellData("ControlType", sheetStructure, rowIndex, structureHeader); controlID = getCellData("ControlID", sheetStructure, rowIndex, structureHeader); indexVal = getCellData("Index", sheetStructure, rowIndex, structureHeader); columnName = getCellData("ColumnName", sheetStructure, rowIndex, structureHeader); rowNo = getCellData("RowNo", sheetStructure, rowIndex, structureHeader); colNo = getCellData("ColumnNo", sheetStructure, rowIndex, structureHeader); dynamicIndex = getCellData("DynamicIndex", sheetStructure, rowIndex, structureHeader); if (action.equalsIgnoreCase("LOOP")) { loopRow = rowIndex + 1; } if ((valuesRowIndex != ExcelUtility.firstRow) && (dynamicIndex.length() > 0)) //valuesRowIndex { dynamicIndexNumber = Integer .parseInt(dynamicIndex.substring(dynamicIndex.length() - 1, dynamicIndex.length())); if (ExcelUtility.dynamicNum == 0) { ExcelUtility.dynamicNum = dynamicIndexNumber + 1; isDynamicNumFound = false; } else if (ExcelUtility.dynamicNum != 0 && isDynamicNumFound) { ExcelUtility.dynamicNum = ExcelUtility.dynamicNum + 1; isDynamicNumFound = false; } newDynamicIndex = dynamicIndex.replace(String.valueOf(dynamicIndexNumber), String.valueOf(ExcelUtility.dynamicNum)); controlName = controlName.replace(dynamicIndex, newDynamicIndex); } /**Stop the execution of the current test case unexpected alert**/ control = controltype.toString(); if (isAlertPresent(control) == true) { break; } if (!action.equalsIgnoreCase("LOOP") && !action.equalsIgnoreCase("END_LOOP")) { if (valuesHeader.isEmpty() == true) { valuesHeader = getValueFromHashMap(headerValues); } Object actualValue = null; if (logicalName != null) { actualValue = valuesHeader.get(logicalName.toString()); } //headerRow.getCell(colIndex); if (actualValue == null) { System.out.println("Null"); } else { ctrlValue = getCellData(logicalName, headerValues, valuesRowIndex, valuesHeader); testcaseID = rowValues .getCell(Integer.parseInt(valuesHeader.get("TestCaseID").toString())); if (testcaseID == null) { testCase = ""; } else { testCase = testcaseID.toString(); } transactionType = rowValues .getCell(Integer.parseInt(valuesHeader.get("TransactionType").toString())); } if ((action.equals("I") && !ctrlValue.isEmpty()) || (action.equals("V") && !ctrlValue.isEmpty()) || !action.equals("I")) { if (!controltype.startsWith("Sikuli")) { if (!action.equalsIgnoreCase("LOOP") && !controltype.equalsIgnoreCase("Wait") && !action.equalsIgnoreCase("END_LOOP") && !controltype.equalsIgnoreCase("Browser") && !controltype.equalsIgnoreCase("Window") && !controltype.equalsIgnoreCase("Alert") && !controltype.equalsIgnoreCase("URL") && !controltype.equalsIgnoreCase("WaitForJS") && !controltype.contains("Robot") && !controltype.equalsIgnoreCase("Calendar") && !controltype.equalsIgnoreCase("CalendarNew") && !controltype.equalsIgnoreCase("CalendarIPF") && !controltype.equalsIgnoreCase("CalendarEBP") && (!action.equalsIgnoreCase("Read") || ((action.equalsIgnoreCase("Read") && !controlName.isEmpty()))) && !controltype.equalsIgnoreCase("JSScript") && !controltype.equalsIgnoreCase("DB") && !controlID.equalsIgnoreCase("XML") && !controltype.startsWith("Process") && !controltype.startsWith("Destroy") && !controltype.startsWith("ReadSikuli") && !controltype.equalsIgnoreCase("WebService")) { if ((indexVal.equalsIgnoreCase("") || indexVal.equalsIgnoreCase("0")) && !controlID.equalsIgnoreCase("TagValue") && !controlID.equalsIgnoreCase("TagText")) { webElement = getElementByType(controlID, controlName, control, imageType, ctrlValue); } else { controlList = getElementsByType(controlID, controlName, control, imageType, ctrlValue); if (controlList != null && controlList.size() > 1) { webElement = GetControlByIndex(indexVal, controlList, controlID, controlName, control, ctrlValue); //, ISelenium selenium) } else { break; } } } } else { sikuliScreen = new Screen(); } } /*** Perform action on the identified control ***/ doAction(imageType, controltype, controlID, controlName, ctrlValue, logicalName, action, webElement, true, sheetStructure, headerValues, rowIndex, rowCount, rowNo, colNo); } if (action == "END_LOOP" && (valuesRowCount != valuesRowIndex)) { loopRow = 1; break; } } else { System.out.println("ExecuteFlag is N"); } } //Setting of reporting values after execution in case of no exception Date toDate = new Date(); SwiftSeleniumWeb.WebDriver.report.setFromDate(Automation.dtFormat.format(frmDate)); SwiftSeleniumWeb.WebDriver.report .setStrIteration(Automation.configHashMap.get("CYCLENUMBER").toString()); SwiftSeleniumWeb.WebDriver.report.setStrTestcaseId(MainController.controllerTestCaseID.toString()); SwiftSeleniumWeb.WebDriver.report.setStrGroupName(MainController.controllerGroupName.toString()); SwiftSeleniumWeb.WebDriver.report .setStrTrasactionType(MainController.controllerTransactionType.toString()); SwiftSeleniumWeb.WebDriver.report.setStrTestDescription(MainController.testDesciption); SwiftSeleniumWeb.WebDriver.report.setToDate(Automation.dtFormat.format(toDate)); //Setting status for field verification failures if (fieldVerFailCount > 0) { SwiftSeleniumWeb.WebDriver.report.setStrMessage("Check Detailed Results"); SwiftSeleniumWeb.WebDriver.report.setStrStatus("FAIL"); } } catch (Exception e) { MainController.pauseFun(e.getMessage()); } finally { structureHeader.clear(); valuesHeader.clear(); ExcelUtility.writeReport(SwiftSeleniumWeb.WebDriver.report); fieldVerFailCount = 0; } }
From source file:ua.com.ecotep.unianalysis.export.XLSDataExport.java
@Override public void exportData(String selectedFile, AnProperties props, ObservableList<ObservableList<Object>> exportData, List<String> columnTitles) throws Exception { if (selectedFile == null) { return;/*w w w. j a v a 2s . com*/ } System.setProperty("java.awt.headless", "true"); HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet("Sheet1"); HSSFPrintSetup ps = sheet.getPrintSetup(); ps.setPaperSize(HSSFPrintSetup.A4_PAPERSIZE); ps.setLandscape(true); HSSFCellStyle cellStyleT = wb.createCellStyle(); HSSFFont font1 = wb.createFont(); font1.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); font1.setFontHeightInPoints((short) 8); cellStyleT.setFont(font1); int rnumber = 0; HSSFRow row = sheet.createRow(0); sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 10)); HSSFCell cell = row.createCell(0); cell.setCellValue( new HSSFRichTextString(" ??? ??? - " + DateConverters.getDateToStr(LocalDate.now()))); cell.setCellStyle(cellStyleT); rnumber++; if (props != null) { String val = " : " + DateConverters.getDateToStr(props.getDateFrom()) + " " + DateConverters.getDateToStr(props.getDateTo()); createHeaderCell(sheet, rnumber, val, cellStyleT); rnumber++; //---------- if (props.isSalMode()) { String type = props.getSalSalerType() == AnProperties.SALER_TYPES.PROFILE ? " " : " "; val = "? " + type + "| " + props.getSaler().getNameSaler(); createHeaderCell(sheet, rnumber, val, cellStyleT); rnumber++; if (props.getSalSalerType() == AnProperties.SALER_TYPES.SALER) { type = props.isSalDirectSales() ? "? " : " "; val = " : " + type; createHeaderCell(sheet, rnumber, val, cellStyleT); rnumber++; if (props.isSalFixedDepartment()) { val = " : "; createHeaderCell(sheet, rnumber, val, cellStyleT); rnumber++; } } } if (props.isGoodMode()) { if (props.getGoodClassLev0() != null) { val = "? 1: " + props.getGoodClassLev0().getName(); createHeaderCell(sheet, rnumber, val, cellStyleT); rnumber++; if (props.getGoodClassLev1() != null) { val = "? 2: " + props.getGoodClassLev1().getName(); createHeaderCell(sheet, rnumber, val, cellStyleT); rnumber++; if (props.getGoodClassLev2() != null) { val = "? 3: " + props.getGoodClassLev2().getName(); createHeaderCell(sheet, rnumber, val, cellStyleT); rnumber++; if (props.getGoodClassLev3() != null) { val = "? 4: " + props.getGoodClassLev3().getName(); createHeaderCell(sheet, rnumber, val, cellStyleT); rnumber++; } } } if (props.getGoodCustomSearch() != null && !props.getGoodCustomSearch().isEmpty()) { val = " : " + props.getGoodCustomSearch(); createHeaderCell(sheet, rnumber, val, cellStyleT); rnumber++; } if (props.isGoodPeriodAnalysis() && props.getGoodsIndateLst().size() == 2) { val = " : " + DateConverters.getDateToStr(props.getGoodsIndateLst().get(0)); createHeaderCell(sheet, rnumber, val, cellStyleT); rnumber++; val = " : " + DateConverters.getDateToStr(props.getGoodsIndateLst().get(1)); createHeaderCell(sheet, rnumber, val, cellStyleT); rnumber++; } if (!props.isGoodPeriodAnalysis() && props.getGoodsIndateLst().size() > 0) { val = "+: "; for (LocalDate ld : props.getGoodsIndateLst()) { val += DateConverters.getDateToStr(ld) + "; "; } createHeaderCell(sheet, rnumber, val, cellStyleT); rnumber++; } } } if (!props.getClLst().isEmpty()) { if (props.isClIncluded()) { val = " : "; createHeaderCell(sheet, rnumber, val, cellStyleT); rnumber++; } else { val = " : "; createHeaderCell(sheet, rnumber, val, cellStyleT); rnumber++; } val = "+: "; for (ClientBean cb : props.getClLst()) { val += cb.getClientCl() + "; "; } createHeaderCell(sheet, rnumber, val, cellStyleT); rnumber++; } if (!props.getVLst().isEmpty()) { if (props.isVIncluded()) { val = " : "; createHeaderCell(sheet, rnumber, val, cellStyleT); rnumber++; } else { val = " : "; createHeaderCell(sheet, rnumber, val, cellStyleT); rnumber++; } val = ""; for (String v : props.getVLst()) { val += v + "; "; } createHeaderCell(sheet, rnumber, val, cellStyleT); rnumber++; } } //---------- HSSFCellStyle cellStyleH = wb.createCellStyle(); HSSFFont font = wb.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); cellStyleH.setFont(font); cellStyleH.setWrapText(true); cellStyleH.setAlignment(HSSFCellStyle.ALIGN_CENTER); cellStyleH.setVerticalAlignment(HSSFCellStyle.ALIGN_CENTER); cellStyleH.setBorderLeft((short) 1); cellStyleH.setBorderRight((short) 1); cellStyleH.setBorderTop((short) 1); cellStyleH.setBorderBottom((short) 1); HSSFCellStyle cellStyleHh = wb.createCellStyle(); font = wb.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); cellStyleHh.setFont(font); cellStyleHh.setWrapText(true); cellStyleHh.setAlignment(HSSFCellStyle.ALIGN_RIGHT); cellStyleHh.setVerticalAlignment(HSSFCellStyle.ALIGN_CENTER); cellStyleHh.setBorderLeft((short) 1); cellStyleHh.setBorderRight((short) 1); cellStyleHh.setBorderTop((short) 1); cellStyleHh.setBorderBottom((short) 1); //filling table HSSFCellStyle cellStyleN = wb.createCellStyle(); cellStyleN.setAlignment(HSSFCellStyle.ALIGN_LEFT); cellStyleN.setVerticalAlignment(HSSFCellStyle.ALIGN_CENTER); cellStyleN.setBorderLeft((short) 1); cellStyleN.setBorderRight((short) 1); cellStyleN.setBorderTop((short) 1); cellStyleN.setBorderBottom((short) 1); HSSFCellStyle cellStyleI = wb.createCellStyle(); cellStyleI.setAlignment(HSSFCellStyle.ALIGN_CENTER); cellStyleI.setVerticalAlignment(HSSFCellStyle.ALIGN_CENTER); cellStyleI.setBorderLeft((short) 1); cellStyleI.setBorderRight((short) 1); cellStyleI.setBorderTop((short) 1); cellStyleI.setBorderBottom((short) 1); HSSFCellStyle cellStyleD = wb.createCellStyle(); cellStyleD.setAlignment(HSSFCellStyle.ALIGN_RIGHT); cellStyleD.setVerticalAlignment(HSSFCellStyle.ALIGN_RIGHT); HSSFDataFormat df = wb.createDataFormat(); cellStyleD.setDataFormat(df.getFormat("#,##0.0")); cellStyleD.setBorderLeft((short) 1); cellStyleD.setBorderRight((short) 1); cellStyleD.setBorderTop((short) 1); cellStyleD.setBorderBottom((short) 1); HSSFCellStyle cellStyleP = wb.createCellStyle(); cellStyleP.setAlignment(HSSFCellStyle.ALIGN_RIGHT); cellStyleP.setVerticalAlignment(HSSFCellStyle.ALIGN_RIGHT); cellStyleP.setDataFormat(df.getFormat("0.0\\%")); cellStyleP.setBorderLeft((short) 1); cellStyleP.setBorderRight((short) 1); cellStyleP.setBorderTop((short) 1); cellStyleP.setBorderBottom((short) 1); // filling column headers row = sheet.createRow(rnumber); String rowTitle = null; row = sheet.createRow(rnumber); row.setHeightInPoints(40); cell = row.createCell(0); cell.setCellValue(new HSSFRichTextString("?")); cell.setCellStyle(cellStyleH); for (int t = 0; t < columnTitles.size(); t++) { cell = row.createCell(t + 1); cell.setCellValue(new HSSFRichTextString(columnTitles.get(t))); cell.setCellStyle(cellStyleH); } // filling table with data rnumber++; for (ObservableList<Object> line : exportData) { row = sheet.createRow(rnumber); cell = row.createCell(0); cell.setCellValue(new HSSFRichTextString((String) line.get(0))); cell.setCellStyle(cellStyleN); for (int i = 1; i < line.size(); i++) { Double val = (Double) line.get(i); cell = row.createCell(i); cell.setCellStyle(cellStyleD); cell.setCellValue(val); } rnumber++; } for (int t = 0; t < columnTitles.size(); t++) { sheet.autoSizeColumn((short) t); } saveWorkBook(wb, selectedFile); execute(selectedFile); }