List of usage examples for org.apache.poi.ss.usermodel Workbook setSheetName
void setSheetName(int sheet, String name);
From source file:com.evidon.areweprivateyet.Aggregator.java
License:Open Source License
public void createSpreadSheet() throws Exception { int row = 2, cell = 0, sheet = 0; FileOutputStream file = new FileOutputStream(path + "analysis.xls"); Workbook wb = new HSSFWorkbook(); // content: total content length sheet. Sheet s = wb.createSheet();/*from ww w . ja v a 2 s . c om*/ wb.setSheetName(sheet, "Content Length"); this.createHeader(wb, s, "Total Content Length in MB", 0); Row r = s.createRow(row); for (String database : results.keySet()) { Cell c = r.createCell(cell); c.setCellValue(results.get(database).totalContentLength / 1024 / 1024); cell++; } row++; cell = 0; r = s.createRow(row); for (String database : results.keySet()) { Cell c = r.createCell(cell); if (database.equals("baseline")) { c.setCellValue("Decrease:"); Map<String, String> contents = new LinkedHashMap<String, String>(); contents.put(s.getSheetName(), "0"); decrease.put(database, contents); } else { c = r.createCell(cell); c.setCellType(Cell.CELL_TYPE_FORMULA); c.setCellFormula("ROUND((100-(" + getCellLetter(cell - 1) + "3*100/A3)),0)"); FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator(); evaluator.evaluateFormulaCell(c); Map<String, String> contents = new LinkedHashMap<String, String>(); contents.put(s.getSheetName(), c.getNumericCellValue() + ""); decrease.put(database, contents); } cell++; } sheet++; // When content is created, baseline is used as a base for every entry. For example, // if baseline contained doubleclick.com, this will be output and each other analyzer's // map, like ghosterys analyzer is then asked for the content's mapping for doubleclick. // So, if baseline does not contain blah.com, yet ghostery map does, this entry is never // shown in the spreadsheet or any other results. // so this means if we have tracker/whatever URLs in a non-baseline profile // and these URLs are NOT in the baseline profile, // we wouldn't see those trackers/whatever in the final comparison. // content: HTTP Requests s = wb.createSheet(); wb.setSheetName(sheet, "HTTP Requests"); this.createHeader(wb, s, "Pages with One or More HTTP Requests to the Public Suffix", 1); this.createContent(wb, s, "requestCountPerDomain"); sheet++; // content: HTTP Set-Cookie Responses s = wb.createSheet(); wb.setSheetName(sheet, "HTTP Set-Cookie Responses"); this.createHeader(wb, s, "Pages with One or More HTTP Responses from the Public Suffix That Include a Set-Cookie Header", 1); this.createContent(wb, s, "setCookieResponses"); sheet++; // content: Cookie Added - Cookie Deleted s = wb.createSheet(); wb.setSheetName(sheet, "Cookies Added-Deleted"); this.createHeader(wb, s, "Cookies Added - Cookies Deleted Per Domain", 1); this.createContent(wb, s, "cookieTotals"); sheet++; // content: Local Storage counts per domain s = wb.createSheet(); wb.setSheetName(sheet, "Local Storage"); this.createHeader(wb, s, "Local Storage counts per domain", 1); this.createContent(wb, s, "localStorageContents"); sheet++; // content: Pretty Chart s = wb.createSheet(); wb.setSheetName(sheet, "Overall"); int rownum = 0, cellnum = 0; // Header r = s.createRow(rownum); Cell c = r.createCell(0); s.setColumnWidth(0, 8000); c.setCellValue( "Overall effectiveness measured by percentage of decrease vs baseline (0 for any negative effect)"); rownum++; r = s.createRow(rownum); cellnum++; for (String database : decrease.keySet()) { if (database.equals("baseline")) { continue; } c = r.createCell(cellnum); c.setCellValue(database); CellStyle cs = wb.createCellStyle(); Font f = wb.createFont(); f.setBoldweight(Font.BOLDWEIGHT_BOLD); cs.setFont(f); c.setCellStyle(cs); cellnum++; } CellStyle numberStyle = wb.createCellStyle(); numberStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("number")); // Content for (String type : decrease.get("baseline").keySet()) { cellnum = 0; rownum++; r = s.createRow(rownum); c = r.createCell(cellnum); c.setCellValue(type); cellnum++; for (String database : decrease.keySet()) { if (database.equals("baseline")) { continue; } c = r.createCell(cellnum); c.setCellStyle(numberStyle); double decreaseValue = Double.parseDouble(decrease.get(database).get(type)); if (decreaseValue < 0) decreaseValue = 0; c.setCellValue(decreaseValue); cellnum++; } } /* for (String database : decrease.keySet()) { for (String type : decrease.get(database).keySet()) { System.out.println(database + "|" + type + "|" + decrease.get(database).get(type)); } } */ wb.write(file); file.close(); }
From source file:com.github.autoprimer3.Primer3ResultViewController.java
License:Open Source License
private void writePrimersToExcel(final File f) throws IOException { final Service<Void> service = new Service<Void>() { @Override/*from w w w. j av a 2s . co m*/ protected Task<Void> createTask() { return new Task<Void>() { @Override protected Void call() throws IOException { BufferedOutputStream bo = new BufferedOutputStream(new FileOutputStream(f)); Workbook wb = new XSSFWorkbook(); CellStyle hlink_style = wb.createCellStyle(); Font hlink_font = wb.createFont(); hlink_font.setUnderline(Font.U_SINGLE); hlink_font.setColor(IndexedColors.BLUE.getIndex()); hlink_style.setFont(hlink_font); CreationHelper createHelper = wb.getCreationHelper(); Sheet listSheet = wb.createSheet(); Sheet detailsSheet = wb.createSheet(); Row row = null; int rowNo = 0; int sheetNo = 0; wb.setSheetName(sheetNo++, "List"); wb.setSheetName(sheetNo++, "Details"); row = listSheet.createRow(rowNo++); String header[] = { "Primer", "Sequence", "Product Size (bp)" }; for (int col = 0; col < header.length; col++) { Cell cell = row.createCell(col); cell.setCellValue(header[col]); } updateMessage("Writing primers . . ."); updateProgress(0, data.size() * 3); int n = 0; for (Primer3Result r : data) { n++; updateMessage("Writing primer list " + n + " . . ."); row = listSheet.createRow(rowNo++); int col = 0; Cell cell = row.createCell(col++); cell.setCellValue(r.getName() + "F"); cell = row.createCell(col++); cell.setCellValue(r.getLeftPrimer()); cell = row.createCell(col++); cell.setCellValue(r.getProductSize()); updateProgress(n, data.size() * 3); updateMessage("Writing primer list " + n + " . . ."); row = listSheet.createRow(rowNo++); col = 0; cell = row.createCell(col++); cell.setCellValue(r.getName() + "R"); cell = row.createCell(col++); cell.setCellValue(r.getRightPrimer()); cell = row.createCell(col++); cell.setCellValue(r.getProductSize()); n++; updateProgress(n, data.size() * 3); } rowNo = 0; row = detailsSheet.createRow(rowNo++); ArrayList<String> detailsHeader = new ArrayList<>(Arrays.asList("Name", "Other IDs", "Left Primer", "Right Primer", "Product Size (bp)", "Region", "in-silico PCR")); if (ispcrResCol.isVisible()) { detailsHeader.add("in-silico PCR Results"); } for (int col = 0; col < detailsHeader.size(); col++) { Cell cell = row.createCell(col); cell.setCellValue(detailsHeader.get(col)); } int m = 0; for (Primer3Result r : data) { m++; updateMessage("Writing details for pair " + m + " . . ."); row = detailsSheet.createRow(rowNo++); int col = 0; Cell cell = row.createCell(col++); cell.setCellValue(r.getName()); cell = row.createCell(col++); cell.setCellValue(r.getTranscripts()); cell = row.createCell(col++); cell.setCellValue(r.getLeftPrimer()); cell = row.createCell(col++); cell.setCellValue(r.getRightPrimer()); cell = row.createCell(col++); cell.setCellValue(r.getProductSize()); cell = row.createCell(col++); cell.setCellValue(r.getRegion()); cell = row.createCell(col++); if (r.getIsPcrUrl() != null) { cell.setCellValue("isPCR"); org.apache.poi.ss.usermodel.Hyperlink hl = createHelper .createHyperlink(org.apache.poi.ss.usermodel.Hyperlink.LINK_URL); hl.setAddress(r.getIsPcrUrl()); cell.setHyperlink(hl); cell.setCellStyle(hlink_style); } else { cell.setCellValue(""); } if (ispcrResCol.isVisible()) { cell = row.createCell(col++); if (r.getIsPcrResults() != null) { cell.setCellValue(r.getIsPcrResults()); } else { cell.setCellValue(""); } } updateProgress(n + m, data.size() * 3); } updateMessage("Wrote " + data.size() + " primer pairs to file."); wb.write(bo); bo.close(); return null; } }; } }; service.setOnSucceeded(new EventHandler<WorkerStateEvent>() { @Override public void handle(WorkerStateEvent e) { Action response = Dialogs.create().title("Done").masthead("Finished writing") .message("Primers successfully written to " + f.getAbsolutePath() + "\n\nDo you want to open " + "this file now?") .actions(Dialog.ACTION_YES, Dialog.ACTION_NO).styleClass(Dialog.STYLE_CLASS_NATIVE) .showConfirm(); if (response == Dialog.ACTION_YES) { try { openFile(f); } catch (IOException ex) { Action openFailed = Dialogs.create().title("Open failed") .masthead("Could not open output file") .message("Exception encountered when attempting to open " + "the saved file. See below:") .styleClass(Dialog.STYLE_CLASS_NATIVE).showException(ex); } } progressBar.progressProperty().unbind(); progressBar.setVisible(false); summaryLabel.textProperty().unbind(); summaryLabel.setText(summary); closeButton.setDisable(false); closeMenuItem.setDisable(false); setCheckIsPcrButton(); } }); service.setOnCancelled(new EventHandler<WorkerStateEvent>() { @Override public void handle(WorkerStateEvent e) { Dialogs writeCancelled = Dialogs.create().title("Writing Cancelled") .masthead("Cancelled writing to file").message("User cancelled writing primers to file.") .styleClass(Dialog.STYLE_CLASS_NATIVE); writeCancelled.showInformation(); progressBar.progressProperty().unbind(); progressBar.setVisible(false); summaryLabel.textProperty().unbind(); summaryLabel.setText(summary); closeButton.setDisable(false); closeMenuItem.setDisable(false); setCheckIsPcrButton(); } }); service.setOnFailed(new EventHandler<WorkerStateEvent>() { @Override public void handle(WorkerStateEvent e) { Action writeFailed = Dialogs.create().title("Writing failed") .masthead("Could not write primers to file") .message("Exception encountered when attempting to write " + "primers to file. See below:") .styleClass(Dialog.STYLE_CLASS_NATIVE).showException(e.getSource().getException()); progressBar.progressProperty().unbind(); progressBar.setVisible(false); summaryLabel.textProperty().unbind(); summaryLabel.setText(summary); closeButton.setDisable(false); closeMenuItem.setDisable(false); setCheckIsPcrButton(); } }); progressBar.setVisible(true); progressBar.progressProperty().bind(service.progressProperty()); summaryLabel.textProperty().bind(service.messageProperty()); closeButton.setDisable(true); closeMenuItem.setDisable(true); checkIsPcrButton.setText("Cancel"); checkIsPcrButton.setOnAction(new EventHandler<ActionEvent>() { @Override public void handle(ActionEvent actionEvent) { service.cancel(); } }); service.start(); }
From source file:com.hp.action.UserAction.java
private void export(HttpServletResponse response, List<?> dataList, String dataKey, String tempFilePath, String desFilePath) throws ParsePropertyException, org.apache.poi.openxml4j.exceptions.InvalidFormatException, FileNotFoundException, IOException { Map<String, Object> beans = new HashMap<String, Object>(); beans.put(dataKey, dataList);/* w ww .j ava 2s. c o m*/ XLSTransformer transformer = new XLSTransformer(); File tempFile = new File(tempFilePath); if (!tempFile.exists()) { System.out.println("Template file not found!"); return; } Workbook workbook = transformer.transformXLS(new FileInputStream(tempFile), beans); // String fileName = desFilePath.substring(desFilePath.lastIndexOf("/") + 1); // Nu download file excel // response.setContentType("application/vnd.ms-excel"); // response.setHeader("Content-Disposition", "attachment; filename="+fileName); // OutputStream outputStream = response.getOutputStream(); OutputStream outputStream = new FileOutputStream(desFilePath); workbook.setSheetName(0, fileName.substring(0, fileName.lastIndexOf("."))); workbook.write(outputStream); outputStream.flush(); outputStream.close(); System.out.println("Export is OK!"); }
From source file:com.hp.demo.ExportExcelFile.java
private static void export(List<?> dataList, String dataKey, String tempFilePath, String desFilePath, HttpServletRequest request, HttpServletResponse response) throws ParsePropertyException, IOException, InvalidFormatException { Map<String, Object> beans = new HashMap<String, Object>(); beans.put(dataKey, dataList);//from w w w . j a va2 s . co m XLSTransformer transformer = new XLSTransformer(); File tempFile = new File(tempFilePath); if (!tempFile.exists()) { System.out.println("Template file not found!"); return; } Workbook workbook = transformer.transformXLS(new FileInputStream(tempFile), beans); // String fileName = "Bao Cao " + ".xls"; //Download file response.setContentType("application/vnd.ms-excel"); response.setHeader("Content-Disposition", "attachment; filename=" + fileName); //OutputStream outputStream = response.getOutputStream(); //Write to hardware OutputStream outputStream = new FileOutputStream(desFilePath); workbook.setSheetName(0, fileName.substring(0, fileName.lastIndexOf("."))); workbook.write(outputStream); outputStream.flush(); outputStream.close(); System.out.println("Export is OK!"); }
From source file:com.perceptive.epm.perkolcentral.bl.ExcelReportBL.java
public FileInputStream generateAllEmployeeTeamWiseReport(File file, Boolean isScrumTeam) throws ExceptionWrapper { try {/*from w w w . j a v a2s . c om*/ FileInputStream inp = new FileInputStream(file); //InputStream inp = new FileInputStream("workbook.xlsx"); Workbook wb = WorkbookFactory.create(inp); int iSheetCounter = 1; for (Integer groupID : employeeBL.getAllEmployeesKeyedByGroupId().keySet()) { GroupBO groupBO = groupsBL.getAllGroups().get(groupID); if (isScrumTeam && !groupBO.getRallyGroup()) continue; if (!isScrumTeam && groupBO.getRallyGroup()) continue; Sheet sheet = wb.cloneSheet(0); wb.setSheetName(wb.getSheetIndex(sheet), groupBO.getGroupName()); //wb.setSheetName(iSheetCounter,groupBO.getGroupName()); int iRowCounter = 1; for (EmployeeBO employeeBO : employeeBL.getAllEmployeesKeyedByGroupId().get(groupID)) { Row row = sheet.getRow(iRowCounter); if (row == null) row = sheet.createRow(iRowCounter); Cell cell = row.getCell(0); if (cell == null) cell = row.createCell(0); setCellBorder(wb, cell); cell.setCellValue(employeeBO.getEmployeeId()); cell = row.getCell(1); if (cell == null) cell = row.createCell(1); setCellBorder(wb, cell); cell.setCellValue(employeeBO.getEmployeeUid()); cell = row.getCell(2); if (cell == null) cell = row.createCell(2); setCellBorder(wb, cell); cell.setCellValue(employeeBO.getEmployeeName()); cell = row.getCell(3); if (cell == null) cell = row.createCell(3); setCellBorder(wb, cell); cell.setCellValue(employeeBO.getEmail()); cell = row.getCell(4); if (cell == null) cell = row.createCell(4); setCellBorder(wb, cell); cell.setCellValue(employeeBO.getManager()); cell = row.getCell(5); if (cell == null) cell = row.createCell(5); setCellBorder(wb, cell); cell.setCellValue(employeeBO.getMobileNumber()); cell = row.getCell(6); if (cell == null) cell = row.createCell(6); setCellBorder(wb, cell); cell.setCellValue(employeeBO.getExtensionNum()); cell = row.getCell(7); if (cell == null) cell = row.createCell(7); setCellBorder(wb, cell); cell.setCellValue(employeeBO.getWorkspace()); if (isScrumTeam) { cell = row.getCell(8); if (cell == null) cell = row.createCell(8); setCellBorder(wb, cell); cell.setCellValue(employeeBO.getSpecificRoleInScrumTeam()); } iRowCounter = iRowCounter + 1; } } iSheetCounter = iSheetCounter + 1; wb.removeSheetAt(0); FileOutputStream fileOut = new FileOutputStream(file); wb.write(fileOut); fileOut.close(); return new FileInputStream(file); } catch (Exception ex) { throw new ExceptionWrapper(ex); } }
From source file:com.perceptive.epm.perkolcentral.bl.ExcelReportBL.java
public FileInputStream generateLicenseReport(File file) throws ExceptionWrapper { try {/* w ww . j a va2 s . c om*/ FileInputStream inp = new FileInputStream(file); //InputStream inp = new FileInputStream("workbook.xlsx"); Workbook wb = WorkbookFactory.create(inp); HashMap<String, ArrayList<String>> licenseInfoKeyedByLicenseName = licensesBL.getLicenseRelatedInfo(); //Create The Summary Sheet Sheet sheetSummary = wb.getSheetAt(1); int iSummaryRowCounter = 1; for (String licenseType : licenseInfoKeyedByLicenseName.keySet()) { Row row = sheetSummary.getRow(iSummaryRowCounter); if (row == null) row = sheetSummary.createRow(iSummaryRowCounter); Cell cell = row.getCell(0); if (cell == null) cell = row.createCell(0); setCellBorder(wb, cell); cell.setCellValue(licenseType); row = sheetSummary.getRow(iSummaryRowCounter); if (row == null) row = sheetSummary.createRow(iSummaryRowCounter); cell = row.getCell(1); if (cell == null) cell = row.createCell(1); setCellBorder(wb, cell); cell.setCellValue(Double.parseDouble(licenseInfoKeyedByLicenseName.get(licenseType).get(0))); cell.setCellType(Cell.CELL_TYPE_NUMERIC); row = sheetSummary.getRow(iSummaryRowCounter); if (row == null) row = sheetSummary.createRow(iSummaryRowCounter); cell = row.getCell(2); if (cell == null) cell = row.createCell(2); setCellBorder(wb, cell); cell.setCellValue(Double.parseDouble(licenseInfoKeyedByLicenseName.get(licenseType).get(1))); cell.setCellType(Cell.CELL_TYPE_NUMERIC); row = sheetSummary.getRow(iSummaryRowCounter); if (row == null) row = sheetSummary.createRow(iSummaryRowCounter); cell = row.getCell(3); if (cell == null) cell = row.createCell(3); setCellBorder(wb, cell); cell.setCellValue(Double.parseDouble(licenseInfoKeyedByLicenseName.get(licenseType).get(0)) - Double.parseDouble(licenseInfoKeyedByLicenseName.get(licenseType).get(1))); cell.setCellType(Cell.CELL_TYPE_NUMERIC); iSummaryRowCounter = iSummaryRowCounter + 1; } int iSheetCounter = 1; for (String licenseType : licenseInfoKeyedByLicenseName.keySet()) { Sheet sheet = wb.cloneSheet(0); wb.setSheetName(wb.getSheetIndex(sheet), licenseType); CellReference cellReference = new CellReference("B1"); Row row = sheet.getRow(cellReference.getRow()); Cell cell = row.getCell(cellReference.getCol()); if (cell == null) cell = row.createCell(cellReference.getCol()); setCellBorder(wb, cell); cell.setCellValue(Double.parseDouble(licenseInfoKeyedByLicenseName.get(licenseType).get(0))); cell.setCellType(Cell.CELL_TYPE_NUMERIC); cellReference = new CellReference("B2"); row = sheet.getRow(cellReference.getRow()); cell = row.getCell(cellReference.getCol()); if (cell == null) cell = row.createCell(cellReference.getCol()); setCellBorder(wb, cell); cell.setCellValue(Double.parseDouble(licenseInfoKeyedByLicenseName.get(licenseType).get(1))); cell.setCellType(Cell.CELL_TYPE_NUMERIC); cellReference = new CellReference("B3"); row = sheet.getRow(cellReference.getRow()); cell = row.getCell(cellReference.getCol()); if (cell == null) cell = row.createCell(cellReference.getCol()); setCellBorder(wb, cell); cell.setCellValue(Double.parseDouble(licenseInfoKeyedByLicenseName.get(licenseType).get(0)) - Double.parseDouble(licenseInfoKeyedByLicenseName.get(licenseType).get(1))); cell.setCellType(Cell.CELL_TYPE_NUMERIC); ArrayList<EmployeeBO> allEmployees = new ArrayList<EmployeeBO>( employeeBL.getAllEmployees().values()); final String selectedLicenseTypeName = licenseType; CollectionUtils.filter(allEmployees, new Predicate() { @Override public boolean evaluate(Object o) { EmployeeBO emp = (EmployeeBO) o; if (CollectionUtils.exists(emp.getLicenses(), new Predicate() { @Override public boolean evaluate(Object o) { return ((LicenseBO) o).getLicenseTypeName() .equalsIgnoreCase(selectedLicenseTypeName); //To change body of implemented methods use File | Settings | File Templates. } })) return true; else return false; } }); int iRowCounter = 5; for (EmployeeBO employeeBO : allEmployees) { row = sheet.getRow(iRowCounter); if (row == null) row = sheet.createRow(iRowCounter); cell = row.getCell(0); if (cell == null) cell = row.createCell(0); setCellBorder(wb, cell); cell.setCellValue(employeeBO.getEmployeeId()); cell = row.getCell(1); if (cell == null) cell = row.createCell(1); setCellBorder(wb, cell); cell.setCellValue(employeeBO.getEmployeeUid()); cell = row.getCell(2); if (cell == null) cell = row.createCell(2); setCellBorder(wb, cell); cell.setCellValue(employeeBO.getEmployeeName()); cell = row.getCell(3); if (cell == null) cell = row.createCell(3); setCellBorder(wb, cell); cell.setCellValue(employeeBO.getEmail()); cell = row.getCell(4); if (cell == null) cell = row.createCell(4); setCellBorder(wb, cell); cell.setCellValue(employeeBO.getManager()); cell = row.getCell(5); if (cell == null) cell = row.createCell(5); setCellBorder(wb, cell); cell.setCellValue(employeeBO.getMobileNumber()); cell = row.getCell(6); if (cell == null) cell = row.createCell(6); setCellBorder(wb, cell); cell.setCellValue(employeeBO.getExtensionNum()); cell = row.getCell(7); if (cell == null) cell = row.createCell(7); setCellBorder(wb, cell); cell.setCellValue(employeeBO.getWorkspace()); iRowCounter = iRowCounter + 1; } } iSheetCounter = iSheetCounter + 1; wb.removeSheetAt(0); FileOutputStream fileOut = new FileOutputStream(file); wb.write(fileOut); fileOut.close(); return new FileInputStream(file); } catch (Exception ex) { throw new ExceptionWrapper(ex); } }
From source file:com.qihang.winter.poi.excel.export.template.ExcelExportOfTemplateUtil.java
License:Apache License
public Workbook createExcleByTemplate(TemplateExportParams params, Class<?> pojoClass, Collection<?> dataSet, Map<String, Object> map) { // step 1. ?? if (params == null || map == null || StringUtils.isEmpty(params.getTemplateUrl())) { throw new ExcelExportException(ExcelExportEnum.PARAMETER_ERROR); }/*from ww w . j av a 2 s .com*/ Workbook wb = null; // step 2. ?Excel,?? try { this.teplateParams = params; wb = getCloneWorkBook(); // ? setExcelExportStyler( (IExcelExportStyler) teplateParams.getStyle().getConstructor(Workbook.class).newInstance(wb)); // step 3. ?? for (int i = 0, le = params.isScanAllsheet() ? wb.getNumberOfSheets() : params.getSheetNum().length; i < le; i++) { if (params.getSheetName() != null && params.getSheetName().length > i && StringUtils.isNotEmpty(params.getSheetName()[i])) { wb.setSheetName(i, params.getSheetName()[i]); } tempCreateCellSet.clear(); parseTemplate(wb.getSheetAt(i), map); } if (dataSet != null) { // step 4. ? dataHanlder = params.getDataHanlder(); if (dataHanlder != null) { needHanlderList = Arrays.asList(dataHanlder.getNeedHandlerFields()); } addDataToSheet(pojoClass, dataSet, wb.getSheetAt(params.getDataSheetNum()), wb); } } catch (Exception e) { LOGGER.error(e.getMessage(), e); return null; } return wb; }
From source file:com.validation.manager.core.tool.requirement.importer.RequirementImporter.java
License:Apache License
public static File exportTemplate() throws FileNotFoundException, IOException, InvalidFormatException { File template = new File("Template.xls"); template.createNewFile();/*from w ww .ja v a 2 s .c o m*/ org.apache.poi.ss.usermodel.Workbook wb = new HSSFWorkbook(); org.apache.poi.ss.usermodel.Sheet sheet = wb.createSheet(); wb.setSheetName(0, "Requirements"); int column = 0; CellStyle cs = wb.createCellStyle(); cs.setDataFormat(getBuiltinFormat("text")); Font f = wb.createFont(); f.setFontHeightInPoints((short) 12); f.setBold(true); f.setColor((short) Font.COLOR_NORMAL); cs.setFont(f); Row newRow = sheet.createRow(0); for (String label : COLUMNS) { Cell newCell = newRow.createCell(column); newCell.setCellStyle(cs); newCell.setCellValue(label); column++; } try (FileOutputStream out = new FileOutputStream(template)) { wb.write(out); out.close(); } catch (FileNotFoundException e) { LOG.log(Level.SEVERE, null, e); } catch (IOException e) { LOG.log(Level.SEVERE, null, e); } return template; }
From source file:com.validation.manager.core.tool.step.importer.StepImporter.java
License:Apache License
public static File exportTemplate() throws FileNotFoundException, IOException, InvalidFormatException { File template = new File("Template.xls"); template.createNewFile();/*w ww.j ava 2 s. co m*/ org.apache.poi.ss.usermodel.Workbook wb = new HSSFWorkbook(); org.apache.poi.ss.usermodel.Sheet sheet = wb.createSheet(); wb.setSheetName(0, "Steps"); int column = 0; CellStyle cs = wb.createCellStyle(); cs.setDataFormat(getBuiltinFormat("text")); Font f = wb.createFont(); f.setFontHeightInPoints((short) 12); f.setBold(true); f.setColor((short) Font.COLOR_NORMAL); cs.setFont(f); Row newRow = sheet.createRow(0); for (String label : COLUMNS) { Cell newCell = newRow.createCell(column); newCell.setCellStyle(cs); newCell.setCellValue(label); column++; } try (FileOutputStream out = new FileOutputStream(template)) { wb.write(out); out.close(); } catch (FileNotFoundException e) { LOG.log(Level.SEVERE, null, e); } catch (IOException e) { LOG.log(Level.SEVERE, null, e); } return template; }
From source file:das.pf.io.IOExcel.java
License:Open Source License
public boolean processFile(Path input, boolean openFile) { boolean result = false; int endRow = 0; try {/*from w ww . j a v a 2s. co m*/ updateMessages(String.format("Inicializando el documento: %s", input.toString())); Path copy = createCopy(input); if (copy != null && Files.exists(copy, LinkOption.NOFOLLOW_LINKS)) { Workbook workbook = WorkbookFactory.create(copy.toFile()); Sheet sheet = workbook.getSheetAt(0); Sheet newSheet = workbook.createSheet("Procesado"); workbook.setSheetName(0, "Crudo"); endRow = getLasRow(sheet); // seccion para la creacion de los encabezados updateMessages("Creando la cabecera de los datos"); createHeaderData(newSheet, getCellStyleHeaderData(workbook)); // seccion para los values USD updateMessages( String.format("Creando la cabecera para los: 'values USD' %s", TypeUnits.MTH.name())); createHeaderValues(newSheet, sheet, getCellStyleValues(workbook, TypeValues.VALUES, TypeUnits.MTH), 11, 35, 14); updateMessages( String.format("Creando la cabecera para los: 'values USD' %s", TypeUnits.QRT.name())); createHeaderValues(newSheet, sheet, getCellStyleValues(workbook, TypeValues.VALUES, TypeUnits.QRT), 35, 49, 38); updateMessages( String.format("Creando la cabecera para los: 'values USD' %s", TypeUnits.YTD.name())); createHeaderValues(newSheet, sheet, getCellStyleValues(workbook, TypeValues.VALUES, TypeUnits.YTD), 49, 54, 52); updateMessages( String.format("Creando la cabecera para los: 'values USD' %s", TypeUnits.MAT.name())); createHeaderValues(newSheet, sheet, getCellStyleValues(workbook, TypeValues.VALUES, TypeUnits.MAT), 54, 59, 57); // seccion para los values units updateMessages( String.format("Creando la cabecera para los: 'values Units' %s", TypeUnits.MTH.name())); createHeaderValues(newSheet, sheet, getCellStyleValues(workbook, TypeValues.UNITS, TypeUnits.MTH), 59, 83, 63); updateMessages( String.format("Creando la cabecera para los: 'values Units' %s", TypeUnits.QRT.name())); createHeaderValues(newSheet, sheet, getCellStyleValues(workbook, TypeValues.UNITS, TypeUnits.QRT), 83, 97, 87); updateMessages( String.format("Creando la cabecera para los: 'values Units' %s", TypeUnits.YTD.name())); createHeaderValues(newSheet, sheet, getCellStyleValues(workbook, TypeValues.UNITS, TypeUnits.YTD), 97, 102, 101); updateMessages( String.format("Creando la cabecera para los: 'values Units' %s", TypeUnits.MAT.name())); createHeaderValues(newSheet, sheet, getCellStyleValues(workbook, TypeValues.UNITS, TypeUnits.MAT), 102, 107, 106); // // // seccion para los values units standars updateMessages(String.format("Creando la cabecera para los: 'values Standard Units' %s", TypeUnits.MTH.name())); createHeaderValues(newSheet, sheet, getCellStyleValues(workbook, TypeValues.U_E, TypeUnits.MTH), 107, 131, 112); updateMessages(String.format("Creando la cabecera para los: 'values Standard Units' %s", TypeUnits.QRT.name())); createHeaderValues(newSheet, sheet, getCellStyleValues(workbook, TypeValues.U_E, TypeUnits.QRT), 131, 145, 136); updateMessages(String.format("Creando la cabecera para los: 'values Standard Units' %s", TypeUnits.YTD.name())); createHeaderValues(newSheet, sheet, getCellStyleValues(workbook, TypeValues.U_E, TypeUnits.YTD), 145, 150, 150); updateMessages(String.format("Creando la cabecera para los: 'values Standard Units' %s", TypeUnits.MAT.name())); createHeaderValues(newSheet, sheet, getCellStyleValues(workbook, TypeValues.U_E, TypeUnits.MAT), 150, 155, 155); // fin de la seccion para la creacion de los encabezados // seccion para escribir los CT updateMessages("Escribiendo las clases terampeuticas..."); writeCT(newSheet, sheet, 13, endRow); // seccion para escribir los productos updateMessages("Escribiendo los productos..."); writeProducts(newSheet, sheet, 14); // seccion para escribir los otros valores updateMessages("Escribiendo datos en general..."); writerOthersValues(newSheet, sheet, 15); // seccion para escribir los key competitors updateMessages("Escribiendo los Key Competitors..."); writeKeyCompetitors(newSheet, 3, endRow, 9, 5); // seccion para escribir el pais XmlContry contry = writeContries(newSheet, 3, 0, input); // seccion para escribir la region writeRegions(contry, newSheet, 3, 1); for (int i = 0; i < 155; i++) newSheet.autoSizeColumn(i); newSheet.setAutoFilter(CellRangeAddress.valueOf(String.format("A3:K%d", newSheet.getLastRowNum()))); String pathOutput = "DAS PF - " + input.getFileName().toString(); try (FileOutputStream fos = new FileOutputStream( Paths.get(this.out.toString(), pathOutput).toFile())) { updateMessages(String.format("Guadando el trabajo en la ruta: '%s'", Paths.get(this.out.toString(), pathOutput))); workbook.write(fos); } catch (IOException ex) { Logger.getLogger(IOExcel.class.getName()).log(Level.SEVERE, null, ex); } finally { workbook.close(); } if (openFile && Desktop.isDesktopSupported() && Desktop.getDesktop().isSupported(Desktop.Action.OPEN)) Desktop.getDesktop().open(Paths.get(this.out.toString(), pathOutput).toFile()); result = true; newSheet = null; sheet = null; workbook = null; Files.delete(copy); } } catch (IOException | InvalidFormatException ex) { Logger.getLogger(IOExcel.class.getName()).log(Level.SEVERE, null, ex); Util.showException("No se pudo guardar el archivo", ex); } return result; }