List of usage examples for org.apache.poi.ss.usermodel Workbook getNumberOfSheets
int getNumberOfSheets();
From source file:org.rhq.helpers.perftest.support.reporting.ExcelExporter.java
License:Open Source License
@Override public void export(Map<String, Long> timings, ITestResult result) { Workbook wb; InputStream inp = null;//w w w . j a v a2 s . c o m String fileName = getFileName(); // Check if Workbook is present - otherwise create it try { inp = new FileInputStream(fileName); wb = new HSSFWorkbook(inp); } catch (Exception e) { wb = new HSSFWorkbook(); } finally { if (inp != null) try { inp.close(); } catch (IOException e) { e.printStackTrace(); // TODO: Customise this generated block } } // Now write to it FileOutputStream fileOut = null; try { // Check if we have our sheet, otherwise create if (wb.getNumberOfSheets() == 0) { wb.createSheet("Overview"); } Sheet sheet = wb.getSheetAt(0); DataFormat df = wb.createDataFormat(); integerStyle = wb.createCellStyle(); integerStyle.setDataFormat(df.getFormat("#######0")); Font boldFont = wb.createFont(); boldFont.setBoldweight(Font.BOLDWEIGHT_BOLD); boldText = wb.createCellStyle(); boldText.setFont(boldFont); createOverviewHeaderIfNeeded(sheet); long time = getTotalTime(timings); createOverviewEntry(sheet, time, result); createDetailsSheet(wb, timings, result); // Write the output to a file File outFile = new File(fileName); System.out.println("ExcelExporter, writing to " + outFile.getAbsolutePath()); fileOut = new FileOutputStream(outFile); wb.write(fileOut); fileOut.flush(); } catch (Exception e) { e.printStackTrace(); } finally { try { if (fileOut != null) fileOut.close(); } catch (IOException e) { System.err.println("Failed to close the workbook: " + e.getMessage()); } } }
From source file:org.rhq.helpers.perftest.test.ExcelExporterTest.java
License:Open Source License
/** * Test writing a workbook twice.//w ww . java 2s. c om * Should contain one Overview tab and one 'test' tab * with two times the same result * @throws Exception If anything goes wrong */ public void testRewriteSheets() throws Exception { /* * Run a dummy test. */ TestNG testNG = new TestNG(); testNG.setTestClasses(new Class[] { DummyTest.class }); TestListenerAdapter adapter = new TestListenerAdapter(); testNG.addListener(adapter); testNG.run(); // RHQ additional timing data Map<String, Long> timings = new HashMap<String, Long>(); timings.put("test", 123L); /* * Set up the reporter */ PerformanceReportExporter rep = new ExcelExporter(); rep.setBaseFile("test1"); rep.setRolling(PerformanceReporting.Rolling.NONE); /* * Write to .xls file twice */ rep.export(timings, adapter.getPassedTests().iterator().next()); rep.export(timings, adapter.getPassedTests().iterator().next()); /* * Now check the workbook written * But first delete an existing file */ File file = new File("test1.xls"); FileInputStream fis = new FileInputStream(file); Workbook wb = new HSSFWorkbook(fis); assert wb.getNumberOfSheets() == 2 : "Workbook does not have 2 sheets"; Sheet overview = wb.getSheetAt(0); assert overview.getSheetName().equals("Overview") : "Sheet 0 is not the Overview"; assert wb.getSheetAt(1).getSheetName().equals("DummyTest.one") : "Sheet 1 is not DummyTest.one"; // 0 based as opposed to xls where it rows are 1 based. assert overview.getLastRowNum() == 2 : "Last row of overview is not 4, but " + overview.getLastRowNum(); fis.close(); if (file.exists()) { assert file.delete(); } }
From source file:org.seasar.fisshplate.wrapper.WorkbookWrapper.java
License:Apache License
public WorkbookWrapper(Workbook workbook) { this.hssfWorkbook = workbook; for (int i = 0; i < workbook.getNumberOfSheets(); i++) { sheetList.add(new SheetWrapper(workbook.getSheetAt(i), this, i)); }/*ww w .j a va 2s. c om*/ }
From source file:org.sleuthkit.autopsy.report.ReportXLS.java
License:Apache License
@Override public String generateReport(ReportConfiguration reportconfig) throws ReportModuleException { config = reportconfig;//from w w w. j ava 2s.c o m ReportGen reportobj = new ReportGen(); reportobj.populateReport(reportconfig); HashMap<BlackboardArtifact, ArrayList<BlackboardAttribute>> report = reportobj.Results; Workbook wbtemp = new XSSFWorkbook(); int countGen = 0; int countBookmark = 0; int countCookie = 0; int countHistory = 0; int countDownload = 0; int countRecentObjects = 0; int countTrackPoint = 0; int countInstalled = 0; int countKeyword = 0; int countHash = 0; int countDevice = 0; int countEmail = 0; for (Entry<BlackboardArtifact, ArrayList<BlackboardAttribute>> entry : report.entrySet()) { if (entry.getKey().getArtifactTypeID() == BlackboardArtifact.ARTIFACT_TYPE.TSK_GEN_INFO.getTypeID()) { countGen++; } if (entry.getKey().getArtifactTypeID() == BlackboardArtifact.ARTIFACT_TYPE.TSK_WEB_BOOKMARK .getTypeID()) { countBookmark++; } if (entry.getKey().getArtifactTypeID() == BlackboardArtifact.ARTIFACT_TYPE.TSK_WEB_COOKIE.getTypeID()) { countCookie++; } if (entry.getKey().getArtifactTypeID() == BlackboardArtifact.ARTIFACT_TYPE.TSK_WEB_HISTORY .getTypeID()) { countHistory++; } if (entry.getKey().getArtifactTypeID() == BlackboardArtifact.ARTIFACT_TYPE.TSK_WEB_DOWNLOAD .getTypeID()) { countDownload++; } if (entry.getKey().getArtifactTypeID() == BlackboardArtifact.ARTIFACT_TYPE.TSK_RECENT_OBJECT .getTypeID()) { countRecentObjects++; } if (entry.getKey().getArtifactTypeID() == BlackboardArtifact.ARTIFACT_TYPE.TSK_TRACKPOINT.getTypeID()) { countTrackPoint++; } if (entry.getKey().getArtifactTypeID() == BlackboardArtifact.ARTIFACT_TYPE.TSK_INSTALLED_PROG .getTypeID()) { countInstalled++; } if (entry.getKey().getArtifactTypeID() == BlackboardArtifact.ARTIFACT_TYPE.TSK_KEYWORD_HIT .getTypeID()) { countKeyword++; } if (entry.getKey().getArtifactTypeID() == BlackboardArtifact.ARTIFACT_TYPE.TSK_HASHSET_HIT .getTypeID()) { countHash++; } if (entry.getKey().getArtifactTypeID() == BlackboardArtifact.ARTIFACT_TYPE.TSK_DEVICE_ATTACHED .getTypeID()) { countDevice++; } if (entry.getKey().getArtifactTypeID() == BlackboardArtifact.ARTIFACT_TYPE.TSK_EMAIL_MSG.getTypeID()) { countEmail++; } } try { Case currentCase = Case.getCurrentCase(); // get the most updated case SleuthkitCase skCase = currentCase.getSleuthkitCase(); String caseName = currentCase.getName(); Integer imagecount = currentCase.getImageIDs().length; Integer filesystemcount = currentCase.getRootObjectsCount(); Integer totalfiles = skCase.countFsContentType(TskData.TSK_FS_META_TYPE_ENUM.TSK_FS_META_TYPE_REG); Integer totaldirs = skCase.countFsContentType(TskData.TSK_FS_META_TYPE_ENUM.TSK_FS_META_TYPE_DIR); DateFormat datetimeFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss"); DateFormat dateFormat = new SimpleDateFormat("MM-dd-yyyy-HH-mm-ss"); Date date = new Date(); String datetime = datetimeFormat.format(date); String datenotime = dateFormat.format(date); //The first summary report page Sheet sheetSummary = wbtemp.createSheet("Summary"); //Generate a sheet per artifact type // Sheet sheetGen = wbtemp.createSheet(BlackboardArtifact.ARTIFACT_TYPE.TSK_GEN_INFO.getDisplayName()); Sheet sheetHash = wbtemp.createSheet(BlackboardArtifact.ARTIFACT_TYPE.TSK_HASHSET_HIT.getDisplayName()); Sheet sheetDevice = wbtemp .createSheet(BlackboardArtifact.ARTIFACT_TYPE.TSK_DEVICE_ATTACHED.getDisplayName()); Sheet sheetInstalled = wbtemp .createSheet(BlackboardArtifact.ARTIFACT_TYPE.TSK_INSTALLED_PROG.getDisplayName()); Sheet sheetKeyword = wbtemp .createSheet(BlackboardArtifact.ARTIFACT_TYPE.TSK_KEYWORD_HIT.getDisplayName()); // Sheet sheetTrackpoint = wbtemp.createSheet(BlackboardArtifact.ARTIFACT_TYPE.TSK_TRACKPOINT.getDisplayName()); Sheet sheetRecent = wbtemp .createSheet(BlackboardArtifact.ARTIFACT_TYPE.TSK_RECENT_OBJECT.getDisplayName()); Sheet sheetCookie = wbtemp .createSheet(BlackboardArtifact.ARTIFACT_TYPE.TSK_WEB_COOKIE.getDisplayName()); Sheet sheetBookmark = wbtemp .createSheet(BlackboardArtifact.ARTIFACT_TYPE.TSK_WEB_BOOKMARK.getDisplayName()); Sheet sheetDownload = wbtemp .createSheet(BlackboardArtifact.ARTIFACT_TYPE.TSK_WEB_DOWNLOAD.getDisplayName()); Sheet sheetHistory = wbtemp .createSheet(BlackboardArtifact.ARTIFACT_TYPE.TSK_WEB_HISTORY.getDisplayName()); Sheet sheetEmail = wbtemp.createSheet(BlackboardArtifact.ARTIFACT_TYPE.TSK_EMAIL_MSG.getDisplayName()); //Bold/underline cell style for the top header rows CellStyle style = wbtemp.createCellStyle(); style.setBorderBottom((short) 2); Font font = wbtemp.createFont(); font.setFontHeightInPoints((short) 14); font.setFontName("Arial"); font.setBoldweight((short) 2); style.setFont(font); //create 'default' style CellStyle defaultstyle = wbtemp.createCellStyle(); defaultstyle.setBorderBottom((short) 2); Font defaultfont = wbtemp.createFont(); defaultfont.setFontHeightInPoints((short) 14); defaultfont.setFontName("Arial"); defaultfont.setBoldweight((short) 2); defaultstyle.setFont(defaultfont); //create the rows in the worksheet for our records //Create first row and header // sheetGen.createRow(0); // sheetGen.getRow(0).createCell(0).setCellValue("Name"); // sheetGen.getRow(0).createCell(1).setCellValue("Value"); // sheetGen.getRow(0).createCell(2).setCellValue("Date/Time"); sheetSummary.setDefaultColumnStyle(1, defaultstyle); sheetSummary.createRow(0).setRowStyle(style); sheetSummary.getRow(0).createCell(0).setCellValue("Summary Information"); sheetSummary.getRow(0).createCell(1).setCellValue(caseName); //add some basic information sheetSummary.createRow(1).setRowStyle(defaultstyle); sheetSummary.getRow(1).createCell(0).setCellValue("# of Images"); sheetSummary.getRow(1).createCell(1).setCellValue(imagecount); sheetSummary.createRow(2); sheetSummary.getRow(2).createCell(0).setCellValue("Filesystems found"); sheetSummary.getRow(2).createCell(1).setCellValue(imagecount); sheetSummary.createRow(3); sheetSummary.getRow(3).createCell(0).setCellValue("# of Files"); sheetSummary.getRow(3).createCell(1).setCellValue(totalfiles); sheetSummary.createRow(4); sheetSummary.getRow(4).createCell(0).setCellValue("# of Directories"); sheetSummary.getRow(4).createCell(1).setCellValue(totaldirs); sheetSummary.createRow(5); sheetSummary.getRow(5).createCell(0).setCellValue("Date/Time"); sheetSummary.getRow(5).createCell(1).setCellValue(datetime); sheetHash.setDefaultColumnStyle(1, defaultstyle); sheetHash.createRow(0).setRowStyle(style); sheetHash.getRow(0).createCell(0).setCellValue("Name"); sheetHash.getRow(0).createCell(1).setCellValue("Size"); sheetHash.getRow(0).createCell(2).setCellValue("Hashset Name"); sheetDevice.setDefaultColumnStyle(1, defaultstyle); sheetDevice.createRow(0).setRowStyle(style); sheetDevice.getRow(0).createCell(0).setCellValue("Name"); sheetDevice.getRow(0).createCell(1).setCellValue("Serial #"); sheetDevice.getRow(0).createCell(2).setCellValue("Time"); sheetInstalled.setDefaultColumnStyle(1, defaultstyle); sheetInstalled.createRow(0).setRowStyle(style); sheetInstalled.getRow(0).createCell(0).setCellValue("Program Name"); sheetInstalled.getRow(0).createCell(1).setCellValue("Install Date/Time"); sheetKeyword.setDefaultColumnStyle(1, defaultstyle); sheetKeyword.createRow(0).setRowStyle(style); sheetKeyword.getRow(0).createCell(0).setCellValue("Keyword"); sheetKeyword.getRow(0).createCell(1).setCellValue("File Name"); sheetKeyword.getRow(0).createCell(2).setCellValue("Preview"); sheetKeyword.getRow(0).createCell(3).setCellValue("Keyword List"); sheetRecent.setDefaultColumnStyle(1, defaultstyle); sheetRecent.createRow(0).setRowStyle(style); sheetRecent.getRow(0).createCell(0).setCellValue("Name"); sheetRecent.getRow(0).createCell(1).setCellValue("Path"); sheetRecent.getRow(0).createCell(2).setCellValue("Related Shortcut"); sheetCookie.setDefaultColumnStyle(1, defaultstyle); sheetCookie.createRow(0).setRowStyle(style); sheetCookie.getRow(0).createCell(0).setCellValue("URL"); sheetCookie.getRow(0).createCell(1).setCellValue("Date"); sheetCookie.getRow(0).createCell(2).setCellValue("Name"); sheetCookie.getRow(0).createCell(3).setCellValue("Value"); sheetCookie.getRow(0).createCell(4).setCellValue("Program"); sheetBookmark.setDefaultColumnStyle(1, defaultstyle); sheetBookmark.createRow(0).setRowStyle(style); sheetBookmark.getRow(0).createCell(0).setCellValue("URL"); sheetBookmark.getRow(0).createCell(1).setCellValue("Title"); sheetBookmark.getRow(0).createCell(2).setCellValue("Program"); sheetDownload.setDefaultColumnStyle(1, defaultstyle); sheetDownload.createRow(0).setRowStyle(style); sheetDownload.getRow(0).createCell(0).setCellValue("File"); sheetDownload.getRow(0).createCell(1).setCellValue("Source"); sheetDownload.getRow(0).createCell(2).setCellValue("Time"); sheetDownload.getRow(0).createCell(3).setCellValue("Program"); sheetHistory.setDefaultColumnStyle(1, defaultstyle); sheetHistory.createRow(0).setRowStyle(style); sheetHistory.getRow(0).createCell(0).setCellValue("URL"); sheetHistory.getRow(0).createCell(1).setCellValue("Date"); sheetHistory.getRow(0).createCell(2).setCellValue("Referrer"); sheetHistory.getRow(0).createCell(3).setCellValue("Title"); sheetHistory.getRow(0).createCell(4).setCellValue("Program"); sheetEmail.setDefaultColumnStyle(1, defaultstyle); sheetEmail.createRow(0).setRowStyle(style); sheetEmail.getRow(0).createCell(0).setCellValue("From"); sheetEmail.getRow(0).createCell(1).setCellValue("To"); sheetEmail.getRow(0).createCell(2).setCellValue("Subject"); sheetEmail.getRow(0).createCell(3).setCellValue("Date/Time"); sheetEmail.getRow(0).createCell(4).setCellValue("Content"); sheetEmail.getRow(0).createCell(5).setCellValue("CC"); sheetEmail.getRow(0).createCell(6).setCellValue("BCC"); sheetEmail.getRow(0).createCell(7).setCellValue("Path"); for (int i = 0; i < wbtemp.getNumberOfSheets(); i++) { Sheet tempsheet = wbtemp.getSheetAt(i); tempsheet.setAutobreaks(true); for (Row temprow : tempsheet) { for (Cell cell : temprow) { cell.setCellStyle(style); tempsheet.autoSizeColumn(cell.getColumnIndex()); } } } int countedGen = 0; int countedBookmark = 0; int countedCookie = 0; int countedHistory = 0; int countedDownload = 0; int countedRecentObjects = 0; int countedTrackPoint = 0; int countedInstalled = 0; int countedKeyword = 0; int countedHash = 0; int countedDevice = 0; int countedEmail = 0; //start populating the sheets in the workbook for (Entry<BlackboardArtifact, ArrayList<BlackboardAttribute>> entry : report.entrySet()) { if (ReportFilter.cancel == true) { break; } int cc = 0; Long objId = entry.getKey().getObjectID(); AbstractFile file = skCase.getAbstractFileById(objId); String filename = file.getName(); Long filesize = file.getSize(); TreeMap<Integer, String> attributes = new TreeMap<Integer, String>(); // Get all the attributes, line them up to be added. Place empty string placeholders for each attribute type int n; for (n = 1; n <= 36; n++) { attributes.put(n, ""); } for (BlackboardAttribute tempatt : entry.getValue()) { if (ReportFilter.cancel == true) { break; } String value = ""; int type = tempatt.getAttributeTypeID(); if (tempatt.getAttributeTypeID() == BlackboardAttribute.ATTRIBUTE_TYPE.TSK_DATETIME_RCVD .getTypeID() || tempatt.getAttributeTypeID() == BlackboardAttribute.ATTRIBUTE_TYPE.TSK_DATETIME .getTypeID() || tempatt.getAttributeTypeID() == BlackboardAttribute.ATTRIBUTE_TYPE.TSK_LAST_ACCESSED .getTypeID()) { value = new java.text.SimpleDateFormat("MM/dd/yyyy HH:mm:ss") .format(new java.util.Date((tempatt.getValueLong()) * 1000)).toString(); } else { value = tempatt.getValueString(); } attributes.put(type, StringEscapeUtils.escapeXml(value)); cc++; } if (entry.getKey().getArtifactTypeID() == BlackboardArtifact.ARTIFACT_TYPE.TSK_GEN_INFO .getTypeID()) { countedGen++; // Row temp = sheetGen.getRow(countedGen); } if (entry.getKey().getArtifactTypeID() == BlackboardArtifact.ARTIFACT_TYPE.TSK_WEB_BOOKMARK .getTypeID()) { countedBookmark++; Row temp = sheetBookmark.createRow(countedBookmark); temp.createCell(0) .setCellValue(attributes.get(BlackboardAttribute.ATTRIBUTE_TYPE.TSK_URL.getTypeID())); temp.createCell(1) .setCellValue(attributes.get(BlackboardAttribute.ATTRIBUTE_TYPE.TSK_NAME.getTypeID())); temp.createCell(2).setCellValue( attributes.get(BlackboardAttribute.ATTRIBUTE_TYPE.TSK_PROG_NAME.getTypeID())); } if (entry.getKey().getArtifactTypeID() == BlackboardArtifact.ARTIFACT_TYPE.TSK_WEB_COOKIE .getTypeID()) { countedCookie++; Row temp = sheetCookie.createRow(countedCookie); temp.createCell(0) .setCellValue(attributes.get(BlackboardAttribute.ATTRIBUTE_TYPE.TSK_URL.getTypeID())); temp.createCell(1).setCellValue( attributes.get(BlackboardAttribute.ATTRIBUTE_TYPE.TSK_DATETIME.getTypeID())); temp.createCell(2) .setCellValue(attributes.get(BlackboardAttribute.ATTRIBUTE_TYPE.TSK_NAME.getTypeID())); temp.createCell(3) .setCellValue(attributes.get(BlackboardAttribute.ATTRIBUTE_TYPE.TSK_VALUE.getTypeID())); temp.createCell(4).setCellValue( attributes.get(BlackboardAttribute.ATTRIBUTE_TYPE.TSK_PROG_NAME.getTypeID())); } if (entry.getKey().getArtifactTypeID() == BlackboardArtifact.ARTIFACT_TYPE.TSK_WEB_HISTORY .getTypeID()) { countedHistory++; Row temp = sheetHistory.createRow(countedHistory); temp.createCell(0) .setCellValue(attributes.get(BlackboardAttribute.ATTRIBUTE_TYPE.TSK_URL.getTypeID())); temp.createCell(1).setCellValue( attributes.get(BlackboardAttribute.ATTRIBUTE_TYPE.TSK_LAST_ACCESSED.getTypeID())); temp.createCell(2).setCellValue( attributes.get(BlackboardAttribute.ATTRIBUTE_TYPE.TSK_REFERRER.getTypeID())); temp.createCell(3) .setCellValue(attributes.get(BlackboardAttribute.ATTRIBUTE_TYPE.TSK_NAME.getTypeID())); temp.createCell(4).setCellValue( attributes.get(BlackboardAttribute.ATTRIBUTE_TYPE.TSK_PROG_NAME.getTypeID())); } if (entry.getKey().getArtifactTypeID() == BlackboardArtifact.ARTIFACT_TYPE.TSK_WEB_DOWNLOAD .getTypeID()) { countedDownload++; Row temp = sheetDownload.createRow(countedDownload); temp.createCell(0) .setCellValue(attributes.get(BlackboardAttribute.ATTRIBUTE_TYPE.TSK_PATH.getTypeID())); temp.createCell(1) .setCellValue(attributes.get(BlackboardAttribute.ATTRIBUTE_TYPE.TSK_URL.getTypeID())); temp.createCell(2).setCellValue( attributes.get(BlackboardAttribute.ATTRIBUTE_TYPE.TSK_LAST_ACCESSED.getTypeID())); temp.createCell(3).setCellValue( attributes.get(BlackboardAttribute.ATTRIBUTE_TYPE.TSK_PROG_NAME.getTypeID())); } if (entry.getKey().getArtifactTypeID() == BlackboardArtifact.ARTIFACT_TYPE.TSK_RECENT_OBJECT .getTypeID()) { countedRecentObjects++; Row temp = sheetRecent.createRow(countedRecentObjects); temp.createCell(0) .setCellValue(attributes.get(BlackboardAttribute.ATTRIBUTE_TYPE.TSK_NAME.getTypeID())); temp.createCell(1) .setCellValue(attributes.get(BlackboardAttribute.ATTRIBUTE_TYPE.TSK_PATH.getTypeID())); temp.createCell(2).setCellValue(file.getName()); temp.createCell(3).setCellValue( attributes.get(BlackboardAttribute.ATTRIBUTE_TYPE.TSK_PROG_NAME.getTypeID())); } if (entry.getKey().getArtifactTypeID() == BlackboardArtifact.ARTIFACT_TYPE.TSK_TRACKPOINT .getTypeID()) { // sheetTrackpoint.addContent(artifact); } if (entry.getKey().getArtifactTypeID() == BlackboardArtifact.ARTIFACT_TYPE.TSK_INSTALLED_PROG .getTypeID()) { countedInstalled++; Row temp = sheetInstalled.createRow(countedInstalled); temp.createCell(0).setCellValue( attributes.get(BlackboardAttribute.ATTRIBUTE_TYPE.TSK_PROG_NAME.getTypeID())); temp.createCell(1).setCellValue( attributes.get(BlackboardAttribute.ATTRIBUTE_TYPE.TSK_DATETIME.getTypeID())); } if (entry.getKey().getArtifactTypeID() == BlackboardArtifact.ARTIFACT_TYPE.TSK_KEYWORD_HIT .getTypeID()) { countedKeyword++; Row temp = sheetKeyword.createRow(countedKeyword); temp.createCell(0).setCellValue( attributes.get(BlackboardAttribute.ATTRIBUTE_TYPE.TSK_KEYWORD.getTypeID())); temp.createCell(1).setCellValue(filename); temp.createCell(2).setCellValue( attributes.get(BlackboardAttribute.ATTRIBUTE_TYPE.TSK_KEYWORD_PREVIEW.getTypeID())); temp.createCell(3).setCellValue( attributes.get(BlackboardAttribute.ATTRIBUTE_TYPE.TSK_SET_NAME.getTypeID())); } if (entry.getKey().getArtifactTypeID() == BlackboardArtifact.ARTIFACT_TYPE.TSK_HASHSET_HIT .getTypeID()) { countedHash++; Row temp = sheetHash.createRow(countedHash); temp.createCell(0).setCellValue(file.getName().toString()); temp.createCell(1).setCellValue(filesize.toString()); temp.createCell(2).setCellValue( attributes.get(BlackboardAttribute.ATTRIBUTE_TYPE.TSK_SET_NAME.getTypeID())); } if (entry.getKey().getArtifactTypeID() == BlackboardArtifact.ARTIFACT_TYPE.TSK_DEVICE_ATTACHED .getTypeID()) { countedDevice++; Row temp = sheetDevice.createRow(countedDevice); temp.createCell(0).setCellValue( attributes.get(BlackboardAttribute.ATTRIBUTE_TYPE.TSK_DEVICE_MODEL.getTypeID())); temp.createCell(1).setCellValue( attributes.get(BlackboardAttribute.ATTRIBUTE_TYPE.TSK_DEVICE_ID.getTypeID())); temp.createCell(2).setCellValue( attributes.get(BlackboardAttribute.ATTRIBUTE_TYPE.TSK_DATETIME.getTypeID())); } if (entry.getKey().getArtifactTypeID() == BlackboardArtifact.ARTIFACT_TYPE.TSK_EMAIL_MSG .getTypeID()) { countedEmail++; Row temp = sheetEmail.createRow(countedEmail); temp.createCell(0).setCellValue( attributes.get(BlackboardAttribute.ATTRIBUTE_TYPE.TSK_EMAIL_FROM.getTypeID())); temp.createCell(1).setCellValue( attributes.get(BlackboardAttribute.ATTRIBUTE_TYPE.TSK_EMAIL_TO.getTypeID())); temp.createCell(2).setCellValue( attributes.get(BlackboardAttribute.ATTRIBUTE_TYPE.TSK_SUBJECT.getTypeID())); temp.createCell(3).setCellValue( attributes.get(BlackboardAttribute.ATTRIBUTE_TYPE.TSK_DATETIME_RCVD.getTypeID())); temp.createCell(4).setCellValue( attributes.get(BlackboardAttribute.ATTRIBUTE_TYPE.TSK_EMAIL_CONTENT_PLAIN.getTypeID())); temp.createCell(5).setCellValue( attributes.get(BlackboardAttribute.ATTRIBUTE_TYPE.TSK_EMAIL_CC.getTypeID())); temp.createCell(6).setCellValue( attributes.get(BlackboardAttribute.ATTRIBUTE_TYPE.TSK_EMAIL_BCC.getTypeID())); temp.createCell(7) .setCellValue(attributes.get(BlackboardAttribute.ATTRIBUTE_TYPE.TSK_PATH.getTypeID())); } } //write out the report to the reports folder, set the wbtemp to the primary wb object wb = wbtemp; xlsPath = currentCase.getCaseDirectory() + File.separator + "Reports" + File.separator + caseName + "-" + datenotime + ".xlsx"; this.save(xlsPath); } catch (Exception E) { String test = E.toString(); } return xlsPath; }
From source file:org.smart.migrate.dao.impl.MetaExcelDao.java
@Override public List<String> getTables(Connection connection) { List<String> tables = new ArrayList<String>(); try {/* w w w . jav a 2s.c o m*/ InputStream inp = new FileInputStream(dbfile); Workbook wb = WorkbookFactory.create(inp); for (int i = 0; i < wb.getNumberOfSheets(); i++) { Sheet sheet = wb.getSheetAt(i); if (sheet.getPhysicalNumberOfRows() > 0) { tables.add(sheet.getSheetName()); } } } catch (IOException ex) { Logger.getLogger(MetaExcelDao.class.getName()).log(Level.SEVERE, null, ex); } catch (InvalidFormatException ex) { Logger.getLogger(MetaExcelDao.class.getName()).log(Level.SEVERE, null, ex); } return tables; }
From source file:org.specrunner.source.excel.SourceFactoryExcel.java
License:Open Source License
/** * Load a document from a target.// w w w . j a v a 2 s . c o m * * @param uri * The target corresponding uri (if any). * @param target * The target. * @param encoding * The encoding. * @return The document, if exists, null, otherwise. * @throws SourceException * On load error. */ @Override protected Document fromTarget(URI uri, String target, String encoding) throws SourceException { Element html = new Element("html"); Document result = new Document(html); OPCPackage pkg = null; InputStream in = null; POIFSFileSystem fsys = null; try { Workbook wb = null; if (isFile(uri, target)) { if (UtilLog.LOG.isDebugEnabled()) { UtilLog.LOG.debug("Source from file:" + target); } in = new FileInputStream(new File(target)); } else { if (UtilLog.LOG.isDebugEnabled()) { UtilLog.LOG.debug("Source from URI:" + uri); } in = uri.toURL().openStream(); } if (target.trim().toLowerCase().endsWith(XLSX)) { pkg = OPCPackage.open(in); wb = new XSSFWorkbook(pkg); } else { fsys = new POIFSFileSystem(in); wb = new HSSFWorkbook(fsys); } for (int i = 0; i < wb.getNumberOfSheets(); i++) { Sheet sheet = wb.getSheetAt(i); Map<String, Dimension> spanMap = new HashMap<String, Dimension>(); Set<String> ignoreMap = new HashSet<String>(); for (int j = 0; j < sheet.getNumMergedRegions(); j++) { CellRangeAddress region = sheet.getMergedRegion(j); for (int x = region.getFirstRow(); x <= region.getLastRow(); x++) { for (int y = region.getFirstColumn(); y <= region.getLastColumn(); y++) { if (x == region.getFirstRow() && y == region.getFirstColumn()) { spanMap.put(x + "," + y, new Dimension(region.getLastRow() - x + 1, region.getLastColumn() - y + 1)); } else { ignoreMap.add(x + "," + y); } } } } Element table = new Element("table"); table.addAttribute(new Attribute("border", "1")); html.appendChild(table); Element caption = readCaption(table, sheet); Iterator<Row> ite = sheet.iterator(); readBody(table, caption, spanMap, ignoreMap, ite, headers(table, caption, spanMap, ignoreMap, ite)); } } catch (Exception e) { if (UtilLog.LOG.isDebugEnabled()) { UtilLog.LOG.debug(e.getMessage(), e); } throw new SourceException(e); } finally { if (pkg != null) { try { pkg.close(); } catch (IOException e) { if (UtilLog.LOG.isDebugEnabled()) { UtilLog.LOG.debug(e.getMessage(), e); } throw new SourceException(e); } } if (in != null) { try { in.close(); } catch (IOException e) { if (UtilLog.LOG.isDebugEnabled()) { UtilLog.LOG.debug(e.getMessage(), e); } throw new SourceException(e); } } } return result; }
From source file:org.spionen.james.jamesfile.ExcelJamesFile.java
License:Open Source License
public void writeFile(Map<Long, Subscriber> subscribers, File file) throws IOException { // Create the workbook from file first Workbook wb; try {/*from w w w . ja v a 2 s . com*/ wb = WorkbookFactory.create(file); } catch (InvalidFormatException | IOException ife) { // If it fails, just create a blank one if (file.getName().endsWith(".xlsx")) { wb = new XSSFWorkbook(); } else { wb = new HSSFWorkbook(); } } // First, remove all existing sheets int sheets = wb.getNumberOfSheets(); for (int i = 0; i < sheets; i++) { wb.removeSheetAt(i); } Sheet s = wb.createSheet(); // Create top row of sheet with header data Row top = s.createRow(0); FieldType[] fields = FieldType.standardOrder(); for (int i = 0; i < fields.length; i++) { Cell c = top.createCell(i); c.setCellValue(fields[i].getDesc()); } // And then, iterate through all subscribers int j = 1; for (long abNr : subscribers.keySet()) { Subscriber sub = subscribers.get(abNr); Row r = s.createRow(j); for (int k = 0; k < fields.length; k++) { Cell c = r.createCell(k, Cell.CELL_TYPE_STRING); String str = sub.getByField(fields[k]); if (str != null) { c.setCellValue(str); } else { c.setCellType(Cell.CELL_TYPE_BLANK); } } j++; } wb.write(new FileOutputStream(file)); }
From source file:org.squashtest.tm.service.internal.batchimport.testcase.excel.ExcelWorkbookParserBuilder.java
License:Open Source License
/** * Reads the workbook's sheets and append {@link WorksheetDef}s to the * {@link WorkbookMetaData} accordingly. * * @param wb//from w ww.ja v a 2 s.c o m * @param wmd */ private void processSheets(Workbook wb, WorkbookMetaData wmd) { List<WorksheetFormatStatus> worksheetKOStatuses = new ArrayList<>(); for (int iSheet = 0; iSheet < wb.getNumberOfSheets(); iSheet++) { processSheet(wb, wmd, worksheetKOStatuses, iSheet); } if (!worksheetKOStatuses.isEmpty()) { throw new TemplateMismatchException(worksheetKOStatuses); } }
From source file:org.talend.dataprep.schema.xls.XlsSchemaParser.java
License:Open Source License
/** * Parse all xls sheets for old excel document type * * @param request the xls request./*from www. j ava2s. c o m*/ * @return The parsed sheets request. */ private List<Schema.SheetContent> parseAllSheetsOldFormat(Request request) { final Marker marker = Markers.dataset(request.getMetadata().getId()); try { InputStream inputStream = request.getContent(); if (!inputStream.markSupported()) { inputStream = new PushbackInputStream(inputStream, 8); } Workbook hssfWorkbook = WorkbookFactory.create(inputStream); List<Schema.SheetContent> schemas; try { if (hssfWorkbook == null) { throw new IOException("could not open " + request.getMetadata().getId() + " as an excel file"); } int sheetNumber = hssfWorkbook.getNumberOfSheets(); if (sheetNumber < 1) { LOGGER.debug(marker, "has not sheet to read"); return Collections.emptyList(); } schemas = new ArrayList<>(); for (int i = 0; i < sheetNumber; i++) { Sheet sheet = hssfWorkbook.getSheetAt(i); if (sheet.getLastRowNum() < 1) { LOGGER.debug(marker, "sheet '{}' do not have rows skip ip", sheet.getSheetName()); continue; } List<ColumnMetadata> columnsMetadata = parsePerSheet(sheet, // request.getMetadata().getId(), // hssfWorkbook.getCreationHelper().createFormulaEvaluator()); String sheetName = sheet.getSheetName(); // update XlsSerializer if this default sheet naming change!!! schemas.add( new Schema.SheetContent(sheetName == null ? "sheet-" + i : sheetName, columnsMetadata)); } } finally { hssfWorkbook.close(); } return schemas; } catch (Exception e) { LOGGER.debug(marker, "Exception during parsing xls request :" + e.getMessage(), e); throw new TDPException(CommonErrorCodes.UNEXPECTED_EXCEPTION, e); } }
From source file:org.talend.dataprep.transformation.format.XlsWriterTest.java
License:Open Source License
@Test public void write_simple_xls_file() throws Exception { // given//from w w w . j ava2s. c o m SchemaParser.Request request = createSchemaParser("export_dataset.json"); Workbook workbook = WorkbookFactory.create(request.getContent()); assertThat(workbook).isNotNull(); assertThat(workbook.getNumberOfSheets()).isEqualTo(1); Sheet sheet = workbook.getSheetAt(0); assertThat(sheet).isNotNull().isNotEmpty(); assertThat(sheet.getFirstRowNum()).isEqualTo(0); assertThat(sheet.getLastRowNum()).isEqualTo(6); // assert header content Row row = sheet.getRow(0); /* * "columns": [ { "id": "id", "type": "string" }, { "id": "firstname", "type": "string" }, { "id": "lastname", * "type": "string" }, { "id": "age", "type": "integer" }, { "id": "date-of-birth", "type": "date" }, { "id": * "alive", "type": "boolean" }, { "id": "city", "type": "string" } ] */ assertThat(row.getCell(0).getRichStringCellValue().getString()).isEqualTo("id"); assertThat(row.getCell(1).getRichStringCellValue().getString()).isEqualTo("firstname"); assertThat(row.getCell(2).getRichStringCellValue().getString()).isEqualTo("lastname"); assertThat(row.getCell(3).getRichStringCellValue().getString()).isEqualTo("age"); assertThat(row.getCell(4).getRichStringCellValue().getString()).isEqualTo("date-of-birth"); assertThat(row.getCell(5).getRichStringCellValue().getString()).isEqualTo("alive"); assertThat(row.getCell(6).getRichStringCellValue().getString()).isEqualTo("city"); // assert first content row = sheet.getRow(1); /* * { "id" : "1", "firstname" : "Clark", "lastname" : "Kent", "age" : "42", "date-of-birth" : "10/09/1940", * "alive" : "false", "city" : "Smallville" } */ assertThat(row.getCell(0).getNumericCellValue()).isEqualTo(1); assertThat(row.getCell(1).getStringCellValue()).isEqualTo("Clark"); assertThat(row.getCell(2).getStringCellValue()).isEqualTo("Kent"); assertThat(row.getCell(3).getNumericCellValue()).isEqualTo((double) 42); assertThat(row.getCell(4).getStringCellValue()).isEqualTo("10/09/1940"); assertThat(row.getCell(5).getBooleanCellValue()).isFalse(); assertThat(row.getCell(6).getStringCellValue()).isEqualTo("Smallville"); // assert last content row = sheet.getRow(sheet.getLastRowNum()); /* * { "id" : "6", "firstname" : "Ray", "lastname" : "Palmer", "age" : "93", "date-of-birth" : "01/05/1951", * "alive" : "true", "city" : "Star city" } */ assertThat(row.getCell(0).getNumericCellValue()).isEqualTo(6); assertThat(row.getCell(1).getStringCellValue()).isEqualTo("Ray"); assertThat(row.getCell(2).getStringCellValue()).isEqualTo("Palmer"); assertThat(row.getCell(3).getNumericCellValue()).isEqualTo((double) 93); assertThat(row.getCell(4).getStringCellValue()).isEqualTo("01/05/1951"); assertThat(row.getCell(5).getBooleanCellValue()).isTrue(); assertThat(row.getCell(6).getStringCellValue()).isEqualTo("Star city"); }