List of usage examples for org.apache.poi.poifs.filesystem POIFSFileSystem POIFSFileSystem
public POIFSFileSystem(InputStream stream) throws IOException
From source file:org.opf_labs.aqua.OfficeAnalyser.java
License:Apache License
public static void main(String[] args) throws Exception { //import org.apache.poi.poifs.dev.POIFSDump; //POIFSDump.main(args); SMOutputDocument xmldoc = SMOutputFactory.createOutputDocument( SMOutputFactory.getGlobalXMLOutputFactory().createXMLStreamWriter(System.out, "UTF-8"), "1.1", "UTF-8", true); xmldoc.setIndentation("\n ", 1, 2); // for unix linefeed, 2 spaces per level SMOutputElement xmlroot = xmldoc.addElement("properties"); // Loop through arguments: for (int i = 0; i < args.length; i++) { SMOutputElement xd = xmlroot.addElement("document"); xd.addAttribute("href", args[i]); HWPFDocument doc = new HWPFDocument(new FileInputStream(args[i])); // SummaryInformation SMOutputElement sie = xd.addElement("SummaryInformation"); sie.addElement("ApplicationName").addCharacters(doc.getSummaryInformation().getApplicationName()); sie.addElement("OSVersion").addCharacters("" + doc.getSummaryInformation().getOSVersion()); sie.addElement("Author").addCharacters("" + doc.getSummaryInformation().getAuthor()); sie.addElement("CharCount").addCharacters("" + doc.getSummaryInformation().getCharCount()); sie.addElement("Comments").addCharacters("" + doc.getSummaryInformation().getComments()); sie.addElement("EditTime").addCharacters("" + doc.getSummaryInformation().getEditTime()); sie.addElement("Format").addCharacters("" + doc.getSummaryInformation().getFormat()); sie.addElement("Keywords").addCharacters("" + doc.getSummaryInformation().getKeywords()); sie.addElement("LastAuthor").addCharacters("" + doc.getSummaryInformation().getLastAuthor()); sie.addElement("PageCount").addCharacters("" + doc.getSummaryInformation().getPageCount()); sie.addElement("RevNumber").addCharacters("" + doc.getSummaryInformation().getRevNumber()); sie.addElement("SectionCount").addCharacters("" + doc.getSummaryInformation().getSectionCount()); sie.addElement("Security").addCharacters("" + doc.getSummaryInformation().getSecurity()); sie.addElement("Subject").addCharacters("" + doc.getSummaryInformation().getSubject()); sie.addElement("Template").addCharacters("" + doc.getSummaryInformation().getTemplate()); sie.addElement("Title").addCharacters("" + doc.getSummaryInformation().getTitle()); sie.addElement("WordCount").addCharacters("" + doc.getSummaryInformation().getWordCount()); sie.addElement("CreatedDateTime").addCharacters("" + doc.getSummaryInformation().getCreateDateTime()); sie.addElement("LastPrinted").addCharacters("" + doc.getSummaryInformation().getLastPrinted()); sie.addElement("LastSaveDateTime") .addCharacters("" + doc.getSummaryInformation().getLastSaveDateTime()); sie.addElement("Thumbnail").addCharacters("" + doc.getSummaryInformation().getThumbnail()); // TextTable SMOutputElement tte = xd.addElement("TextTable"); for (TextPiece tp : doc.getTextTable().getTextPieces()) { SMOutputElement tpe = tte.addElement("TextPiece"); tpe.addAttribute("isUnicode", "" + tp.getPieceDescriptor().isUnicode()); tpe.addCharacters(tp.getStringBuilder().toString()); }//from ww w . jav a 2s . c o m // DocumentSummaryInformation SMOutputElement dsie = xd.addElement("DocumentSummaryInformation"); dsie.addElement("ParCount").addCharacters("" + doc.getDocumentSummaryInformation().getParCount()); dsie.addElement("ByteCount").addCharacters("" + doc.getDocumentSummaryInformation().getByteCount()); dsie.addElement("HiddenCount").addCharacters("" + doc.getDocumentSummaryInformation().getHiddenCount()); dsie.addElement("LineCount").addCharacters("" + doc.getDocumentSummaryInformation().getLineCount()); dsie.addElement("MMClipCount").addCharacters("" + doc.getDocumentSummaryInformation().getMMClipCount()); dsie.addElement("NoteCount").addCharacters("" + doc.getDocumentSummaryInformation().getNoteCount()); dsie.addElement("SectionCount") .addCharacters("" + doc.getDocumentSummaryInformation().getSectionCount()); dsie.addElement("SlideCount").addCharacters("" + doc.getDocumentSummaryInformation().getSlideCount()); dsie.addElement("Format").addCharacters("" + doc.getDocumentSummaryInformation().getFormat()); dsie.addElement("PresentationFormat") .addCharacters("" + doc.getDocumentSummaryInformation().getPresentationFormat()); dsie.addElement("Company").addCharacters("" + doc.getDocumentSummaryInformation().getCompany()); dsie.addElement("Category").addCharacters("" + doc.getDocumentSummaryInformation().getCategory()); // Sections for (Object os : doc.getDocumentSummaryInformation().getSections()) { Section s = (Section) os; SMOutputElement se = dsie.addElement("Section"); se.addElement("FormatID").addCharacters("" + s.getFormatID()); se.addElement("CodePage").addCharacters("" + s.getCodepage()); se.addElement("PropertyCount").addCharacters("" + s.getPropertyCount()); for (Property sp : s.getProperties()) { SMOutputElement pe = se.addElement("Property"); pe.addAttribute("class", sp.getValue().getClass().getCanonicalName()); pe.addCharacters(sp.getValue().toString()); } } SMOutputElement fte = xd.addElement("FontTable"); for (Ffn f : doc.getFontTable().getFontNames()) { SMOutputElement fe = fte.addElement("Font"); fe.addElement("MainFontName").addCharacters(f.getMainFontName()); try { fe.addElement("AltFontName").addCharacters(f.getAltFontName()); } catch (Exception e) { // Seems to fail, and no safe test found as yet. } fe.addElement("Size").addCharacters("" + f.getSize()); fe.addElement("Weight").addCharacters("" + f.getWeight()); } SMOutputElement pte = xd.addElement("PicturesTable"); for (Picture p : doc.getPicturesTable().getAllPictures()) { SMOutputElement pe = pte.addElement("Picture"); pe.addElement("MimeType").addCharacters(p.getMimeType()); pe.addElement("Width").addCharacters("" + p.getWidth()); pe.addElement("Height").addCharacters("" + p.getHeight()); pe.addElement("HorizontalScalingFactor").addCharacters("" + p.getHorizontalScalingFactor()); pe.addElement("VerticalScalingFactor").addCharacters("" + p.getVerticalScalingFactor()); pe.addElement("Content").addCharacters("" + p.getContent()); } //parseCompObj( new File(args[i]) ); // This //System.out.println("Dumping " + args[i]); FileInputStream is = new FileInputStream(args[i]); POIFSFileSystem fs = new POIFSFileSystem(is); is.close(); DirectoryEntry root = fs.getRoot(); //dump(root); xmldoc.closeRoot(); // important, flushes, closes output } }
From source file:org.orbeon.oxf.processor.converter.FromXLSConverter.java
License:Open Source License
public ProcessorOutput createOutput(String name) { ProcessorOutput output = new ProcessorOutputImpl(FromXLSConverter.this, name) { public void readImpl(PipelineContext context, XMLReceiver xmlReceiver) { try { // Read binary content of Excel file ByteArrayOutputStream os = new ByteArrayOutputStream(); Base64XMLReceiver base64ContentHandler = new Base64XMLReceiver(os); readInputAsSAX(context, INPUT_DATA, base64ContentHandler); final byte[] fileContent = os.toByteArray(); // 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, "from xls output", DOMGenerator.ZeroValidity, DOMGenerator.DefaultContext); domGenerator.createOutput(OUTPUT_DATA).read(context, xmlReceiver); } catch (IOException e) { throw new OXFException(e); }//ww w .j a v a 2 s. c om } 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 NonLazyUserDataDocument 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.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; {/*from w ww. j a v a 2 s. c om*/ 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 {/*from ww w .j av a2 s . c o m*/ 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.pathwayeditor.metabolic.excelexport.ExcelGenerator.java
License:Apache License
protected HSSFWorkbook loadTemplateFromPath() throws IOException { InputStream inputStream = this.getClass().getClassLoader().getResourceAsStream(templatePath); try {//from w ww. ja va2 s. c om POIFSFileSystem fs; fs = new POIFSFileSystem(inputStream); return new HSSFWorkbook(fs); } finally { inputStream.close(); } }
From source file:org.pathwayeditor.metabolic.excelexport.ExcelGeneratorTestStub.java
License:Apache License
@Override protected HSSFWorkbook loadTemplateFromPath() throws IOException { InputStream inputStream = new FileInputStream(templatePath); try {/*from w w w. j a v a2s. c o m*/ POIFSFileSystem fs; fs = new POIFSFileSystem(inputStream); return new HSSFWorkbook(fs); } finally { inputStream.close(); } }
From source file:org.paxle.parser.msoffice.impl.AMsOfficeParser.java
License:Open Source License
public IParserDocument parse(URI location, String charset, InputStream is) throws ParserException, UnsupportedEncodingException, IOException { IParserDocument parserDoc = null;/* w w w.jav a 2 s.c o m*/ try { // create an empty document parserDoc = this.contextLocal.getCurrentContext().createDocument(); // open the POI filesystem final POIFSFileSystem fs = new POIFSFileSystem(is); // extract metadata this.extractMetadata(fs, parserDoc); // extract plain text this.extractText(fs, parserDoc); parserDoc.setStatus(IParserDocument.Status.OK); return parserDoc; } catch (Throwable e) { throw new ParserException(String.format("Error parsing ms-%s document. %s: %s", docType, e.getClass().getName(), e.getMessage()), e); } }
From source file:org.redpill.alfresco.module.metadatawriter.services.msoffice.impl.POIFSFacadeImpl.java
License:Open Source License
public POIFSFacadeImpl(final InputStream in, final OutputStream out) throws IOException { if (in == null) { throw new IOException("Could not create POIFSFileSystem from null InputStream!"); }/* w ww .j a v a2 s . c o m*/ this.out = out; this.in = in; this.fileSystem = new POIFSFileSystem(in); }
From source file:org.rti.zcore.dar.report.CombinedReportAction.java
License:Apache License
/** * Process the specified HTTP request, and create the corresponding HTTP * response (or forward to another web component that will create it). * Return an <code>ActionForward</code> instance describing where and how * control should be forwarded, or <code>null</code> if the response has * already been completed./* w ww. j a v a2 s. com*/ * * @param mapping The ActionMapping used to select this instance * @param form The optional ActionForm bean for this request (if any) * @param request The HTTP request we are processing * @param response The HTTP response we are creating * @return Action to forward to * @throws Exception if an input/output error or servlet exception occurs */ protected ActionForward doExecute(ActionMapping mapping, ActionForm form, HttpServletRequest request, HttpServletResponse response) throws Exception { ParameterActionForward fwd = null; Principal user = request.getUserPrincipal(); String username = user.getName(); Connection conn = null; HttpSession session = request.getSession(); /*boolean dynamicReport = false; boolean isFacilityReport = false; if (request.getParameter("dynamicReport") != null) { dynamicReport = true; } if (request.getParameter("isFacilityReport") != null) { isFacilityReport = true; }*/ SessionUtil zeprs_session = null; Site site = null; String siteAbbrev = null; String reportName = null; Register report = null; Class clazz = null; String className = null; try { zeprs_session = (SessionUtil) session.getAttribute("zeprs_session"); } catch (Exception e) { // unit testing - it's ok... } try { ClientSettings clientSettings = zeprs_session.getClientSettings(); site = clientSettings.getSite(); siteAbbrev = site.getAbbreviation(); } catch (SessionUtil.AttributeNotFoundException e) { log.error(e); } catch (NullPointerException e) { // it's ok - unit testing siteAbbrev = "test"; } DailyActivityReport aReport = null; MonthlyArtReport mReport = null; String template = DarConstants.COMBINED_REPORT_FILENAME; /*if (isFacilityReport) { template = "Monthly Facility Reports for ART and OI"; } else { template = "ART_&_PMTCT_LMIS_Data_Aggregation_Tool"; }*/ int i = 1; //String reportFileName = "Monthly Reports for ARV and OI" + "-" + siteAbbrev + "-" + username + "-" + DateUtils.getNowPretty() + "-" + i; String reportFileName = template + "-" + siteAbbrev + "-" + username + "-" + DateUtils.getNowPretty() + "-" + i; String path = Constants.ARCHIVE_PATH + site.getAbbreviation() + Constants.pathSep + "reports" + Constants.pathSep + reportFileName + ".xls"; // check if file exists File f = new File(path); while (f.exists()) { i++; //reportFileName = "Monthly Reports for ARV and OI" + "-" + siteAbbrev + "-" + username + "-" + DateUtils.getNowPretty() + "-" + i; reportFileName = template + "-" + siteAbbrev + "-" + username + "-" + DateUtils.getNowPretty() + "-" + i; path = Constants.ARCHIVE_PATH + site.getAbbreviation() + Constants.pathSep + "reports" + Constants.pathSep + reportFileName + ".xls"; f = new File(path); } //String reportFileName = report.getReportFileName(); //String pathXml = Constants.ARCHIVE_PATH + site.getAbbreviation() + Constants.pathSep + "reports" + Constants.pathSep + reportFileName + ".xml"; //String pathExcel = Constants.ARCHIVE_PATH + site.getAbbreviation() + Constants.pathSep + "reports" + Constants.pathSep + reportFileName + ".xls"; //String combinedReport = Constants.ARCHIVE_PATH + site.getAbbreviation() + Constants.pathSep + "reports" + Constants.pathSep + "Monthly Reports for ARV and OI.xls"; String pathExcelMaster = null; /*if (dynamicReport) { pathExcelMaster = Constants.ARCHIVE_PATH + Constants.pathSep + "Monthly Reports for ARV and OI-dynamic.xls"; } else {*/ //pathExcelMaster = Constants.ARCHIVE_PATH + Constants.pathSep + "Monthly Reports for ARV and OI.xls"; pathExcelMaster = Constants.ARCHIVE_PATH + Constants.pathSep + template; //} String[] args = new String[] { pathExcelMaster }; java.text.SimpleDateFormat sdf = new java.text.SimpleDateFormat( org.rti.zcore.Constants.DATE_FORMAT_EXCEL_LONG); sdf.setTimeZone(TimeZone.getDefault()); try { POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(pathExcelMaster)); HSSFWorkbook wb = new HSSFWorkbook(fs); // CDRR-ART reportName = "CDRRArtReport"; // may use CDRRArtReport, CDRROIReport, or MonthlyArtReport className = "org.rti.zcore.dar.report." + StringManipulation.fixClassname(reportName); try { clazz = Class.forName(className); } catch (ClassNotFoundException e) { log.error(e); } try { report = (Register) clazz.newInstance(); } catch (InstantiationException e) { log.error(e); } catch (IllegalAccessException e) { log.error(e); } report = SessionUtil.getInstance(session).getReports().get(reportName); if (report != null) { aReport = (DailyActivityReport) report; HSSFSheet aSsheet = null; int sheetPos = 0; String rowNumStr = null; int rowNum = 0; String cellNumStr = null; int cellNum = 0; String districtName = null; String provinceName = null; // Must first pre-fill the header info in the Facilities spreadsheet - Office 2010 compatibility issue. String filename = org.rti.zcore.Constants.getPathToCatalinaHome() + "databases" + File.separator + "facilities.txt"; for (String line : new TextFile(filename)) { //System.out.println(line); String[] lineArray = line.split("\\|"); String itemName = lineArray[0]; if (itemName.equals("sheet")) { String sheetPosStr = lineArray[1]; sheetPos = Integer.valueOf(sheetPosStr) - 1; aSsheet = wb.getSheetAt(sheetPos); } else if (itemName.equals("siteName")) { rowNumStr = lineArray[1]; rowNum = Integer.valueOf(rowNumStr) - 1; cellNumStr = lineArray[2]; cellNum = Integer.valueOf(cellNumStr) - 1; HSSFRichTextString hssfRichTextString = new HSSFRichTextString(report.getSiteName()); HSSFCell cell = aSsheet.getRow(rowNum).getCell(cellNum); cell.setCellValue(hssfRichTextString); } else if (itemName.equals("district")) { rowNumStr = lineArray[1]; rowNum = Integer.valueOf(rowNumStr) - 1; cellNumStr = lineArray[2]; cellNum = Integer.valueOf(cellNumStr) - 1; districtName = lineArray[3]; aSsheet.getRow(rowNum).getCell(cellNum).setCellValue(new HSSFRichTextString(districtName)); } else if (itemName.equals("province")) { rowNumStr = lineArray[1]; rowNum = Integer.valueOf(rowNumStr) - 1; cellNumStr = lineArray[2]; cellNum = Integer.valueOf(cellNumStr) - 1; provinceName = lineArray[3]; aSsheet.getRow(rowNum).getCell(cellNum).setCellValue(new HSSFRichTextString(provinceName)); } else if (itemName.equals("beginDate")) { rowNumStr = lineArray[1]; rowNum = Integer.valueOf(rowNumStr) - 1; cellNumStr = lineArray[2]; cellNum = Integer.valueOf(cellNumStr) - 1; aSsheet.getRow(rowNum).getCell(cellNum) .setCellValue(new HSSFRichTextString(sdf.format(report.getBeginDate().getTime()))); } else if (itemName.equals("endDate")) { rowNumStr = lineArray[1]; rowNum = Integer.valueOf(rowNumStr) - 1; cellNumStr = lineArray[2]; cellNum = Integer.valueOf(cellNumStr) - 1; aSsheet.getRow(rowNum).getCell(cellNum) .setCellValue(new HSSFRichTextString(sdf.format(report.getEndDate().getTime()))); } } filename = org.rti.zcore.Constants.getPathToCatalinaHome() + "databases" + File.separator + "cdrr.txt"; for (String line : new TextFile(filename)) { //System.out.println(line); String[] lineArray = line.split("\\|"); String itemName = lineArray[0]; if (itemName.equals("sheet")) { String sheetPosStr = lineArray[1]; sheetPos = Integer.valueOf(sheetPosStr) - 1; aSsheet = wb.getSheetAt(sheetPos); } else if (itemName.equals("siteName")) { rowNumStr = lineArray[1]; rowNum = Integer.valueOf(rowNumStr) - 1; cellNumStr = lineArray[2]; cellNum = Integer.valueOf(cellNumStr) - 1; HSSFRichTextString hssfRichTextString = new HSSFRichTextString(report.getSiteName()); HSSFCell cell = aSsheet.getRow(rowNum).getCell(cellNum); cell.setCellValue(hssfRichTextString); } else if (itemName.equals("district")) { rowNumStr = lineArray[1]; rowNum = Integer.valueOf(rowNumStr) - 1; cellNumStr = lineArray[2]; cellNum = Integer.valueOf(cellNumStr) - 1; districtName = lineArray[3]; aSsheet.getRow(rowNum).getCell(cellNum).setCellValue(new HSSFRichTextString(districtName)); } else if (itemName.equals("province")) { rowNumStr = lineArray[1]; rowNum = Integer.valueOf(rowNumStr) - 1; cellNumStr = lineArray[2]; cellNum = Integer.valueOf(cellNumStr) - 1; provinceName = lineArray[3]; aSsheet.getRow(rowNum).getCell(cellNum).setCellValue(new HSSFRichTextString(provinceName)); } else if (itemName.equals("beginDate")) { rowNumStr = lineArray[1]; rowNum = Integer.valueOf(rowNumStr) - 1; cellNumStr = lineArray[2]; cellNum = Integer.valueOf(cellNumStr) - 1; aSsheet.getRow(rowNum).getCell(cellNum) .setCellValue(new HSSFRichTextString(sdf.format(report.getBeginDate().getTime()))); } else if (itemName.equals("endDate")) { rowNumStr = lineArray[1]; rowNum = Integer.valueOf(rowNumStr) - 1; cellNumStr = lineArray[2]; cellNum = Integer.valueOf(cellNumStr) - 1; aSsheet.getRow(rowNum).getCell(cellNum) .setCellValue(new HSSFRichTextString(sdf.format(report.getEndDate().getTime()))); } } /*aSsheet.getRow(3).getCell(1).setCellValue(new HSSFRichTextString(report.getSiteName())); aSsheet.getRow(3).getCell(7).setCellValue(new HSSFRichTextString("Nairobi")); aSsheet.getRow(3).getCell(13).setCellValue(new HSSFRichTextString("Nairobi")); aSsheet.getRow(4).getCell(1).setCellValue(new HSSFRichTextString(sdf.format(report.getBeginDate().getTime()))); aSsheet.getRow(4).getCell(7).setCellValue(new HSSFRichTextString(sdf.format(report.getEndDate().getTime())));*/ CDRRArtSheetPopulater.populateCDRRArtSheet(aReport, aSsheet, 12); //CDRRArtSheetPopulater.populateDynamicCDRRArtSheet(aReport, aSsheet, 43, wb, false); } // CDRR-OI no longer used. /*reportName = "CDRROIReport"; // may use CDRRArtReport, CDRROIReport, or MonthlyArtReport className = "org.rti.zcore.dar.report." + StringManipulation.fixClassname(reportName); report = null; clazz = null; try { clazz = Class.forName(className); } catch (ClassNotFoundException e) { log.error(e); } try { report = (Register) clazz.newInstance(); } catch (InstantiationException e) { log.error(e); } catch (IllegalAccessException e) { log.error(e); } report = SessionUtil.getInstance(session).getReports().get(reportName); if (report != null) { CDRROIReport oReport = (CDRROIReport) report; HSSFSheet oSsheet = null; if (isFacilityReport) { oSsheet = wb.getSheetAt(1); } else { oSsheet = wb.getSheetAt(2); //} oSsheet.getRow(3).getCell(1).setCellValue(new HSSFRichTextString("KEMSA")); oSsheet.getRow(4).getCell(1).setCellValue(new HSSFRichTextString(report.getSiteName())); oSsheet.getRow(5).getCell(1).setCellValue(new HSSFRichTextString("Nairobi")); oSsheet.getRow(6).getCell(1).setCellValue(new HSSFRichTextString(sdf.format(report.getBeginDate().getTime()))); oSsheet.getRow(6).getCell(11).setCellValue(new HSSFRichTextString(sdf.format(report.getEndDate().getTime()))); // now fill in the report data if (dynamicReport) { CDRROiSheetPopulater.populateDynamicCDRRPOiSheet(oReport, oSsheet); } else { //CDRROiSheetPopulater.populateCDRRPOiSheet(oReport, oSsheet); //} }*/ reportName = "MonthlyArtReport"; // may use CDRRArtReport, CDRROIReport, or MonthlyArtReport className = "org.rti.zcore.dar.report." + StringManipulation.fixClassname(reportName); report = null; clazz = null; try { clazz = Class.forName(className); } catch (ClassNotFoundException e) { log.error(e); } try { report = (Register) clazz.newInstance(); } catch (InstantiationException e) { log.error(e); } catch (IllegalAccessException e) { log.error(e); } report = SessionUtil.getInstance(session).getReports().get(reportName); if (report != null) { mReport = (MonthlyArtReport) report; // Monthly ART Report HSSFSheet mSsheet = null; // populate the statistics //SiteStatisticsReport register = new SiteStatisticsReport(); /*int siteId = report.getSiteId(); SiteStatistics stats = new SiteStatistics(); try { conn = DatabaseUtils.getZEPRSConnection(org.rti.zcore.Constants.DATABASE_ADMIN_USERNAME); Integer newClients = SiteStatisticsDAO.getNewClients(conn, beginDate, endDate, siteId); stats.setNewClients(newClients); Integer femaleAdults = SiteStatisticsDAO.getFemaleAdults(conn, siteId); stats.setFemaleAdults(femaleAdults); Integer femaleChildren = SiteStatisticsDAO.getFemaleChildren(conn, siteId); stats.setFemaleChildren(femaleChildren); stats.setFemales(femaleAdults + femaleChildren); Integer maleAdults = SiteStatisticsDAO.getMaleAdults(conn, siteId); stats.setMaleAdults(maleAdults); Integer maleChildren = SiteStatisticsDAO.getMaleChildren(conn, siteId); stats.setMaleChildren(maleChildren); stats.setMales(maleAdults + maleChildren); stats.setAdults(maleAdults + femaleAdults); stats.setChildren(maleChildren + femaleChildren); stats.setAllClients(maleAdults + femaleAdults +maleChildren + femaleChildren); Integer statusDied = SiteStatisticsDAO.getStatusDied(conn, beginDate, endDate, siteId); stats.setStatusDied(statusDied); Integer statusTransferred = SiteStatisticsDAO.getStatusTransferred(conn, beginDate, endDate, siteId); stats.setStatusTransferred(statusTransferred); Integer statusDefaulters = SiteStatisticsDAO.getStatusDefaulters(conn, beginDate, endDate, siteId); stats.setStatusDefaulters(statusDefaulters); Integer statusOther = SiteStatisticsDAO.getStatusOther(conn, beginDate, endDate, siteId); stats.setStatusOther(statusOther); Integer activeArvClients = SiteStatisticsDAO.getActiveArvClients(conn, beginDate, endDate, siteId); stats.setActiveArvClients(activeArvClients); ArrayList<RegimenReport> regimens = SiteStatisticsDAO.getRegimens(conn, beginDate, endDate, siteId); stats.setRegimens(regimens); mSsheet.getRow(8).getCell(2).setCellValue(new HSSFRichTextString("Client totals include all clients in the database.")); mSsheet.getRow(9).getCell(2).setCellValue(new HSSFRichTextString(stats.getAdults().toString())); mSsheet.getRow(9).getCell(6).setCellValue(new HSSFRichTextString(stats.getChildren().toString())); mSsheet.getRow(11).getCell(2).setCellValue(new HSSFRichTextString(stats.getMales().toString())); mSsheet.getRow(11).getCell(4).setCellValue(new HSSFRichTextString(stats.getFemales().toString())); } catch (Exception e) { e.printStackTrace(); } finally { try { conn.close(); } catch (SQLException e) { log.error(e); } }*/ String filename = org.rti.zcore.Constants.getPathToCatalinaHome() + "databases" + File.separator + "regimens.txt"; int sheetPos = 0; String rowNumStr = null; int rowNum = 0; String cellNumStr = null; int cellNum = 0; String districtName = null; String provinceName = null; for (String line : new TextFile(filename)) { //System.out.println(line); String[] lineArray = line.split("\\|"); String itemName = lineArray[0]; if (itemName.equals("sheet")) { String sheetPosStr = lineArray[1]; sheetPos = Integer.valueOf(sheetPosStr) - 1; mSsheet = wb.getSheetAt(sheetPos); } else if (itemName.equals("siteName")) { rowNumStr = lineArray[1]; rowNum = Integer.valueOf(rowNumStr) - 1; cellNumStr = lineArray[2]; cellNum = Integer.valueOf(cellNumStr) - 1; mSsheet.getRow(rowNum).getCell(cellNum) .setCellValue(new HSSFRichTextString(report.getSiteName())); } else if (itemName.equals("district")) { rowNumStr = lineArray[1]; rowNum = Integer.valueOf(rowNumStr) - 1; cellNumStr = lineArray[2]; cellNum = Integer.valueOf(cellNumStr) - 1; districtName = lineArray[3]; mSsheet.getRow(rowNum).getCell(cellNum).setCellValue(new HSSFRichTextString(districtName)); } else if (itemName.equals("province")) { rowNumStr = lineArray[1]; rowNum = Integer.valueOf(rowNumStr) - 1; cellNumStr = lineArray[2]; cellNum = Integer.valueOf(cellNumStr) - 1; provinceName = lineArray[3]; mSsheet.getRow(rowNum).getCell(cellNum).setCellValue(new HSSFRichTextString(provinceName)); } else if (itemName.equals("beginDate")) { rowNumStr = lineArray[1]; rowNum = Integer.valueOf(rowNumStr) - 1; cellNumStr = lineArray[2]; cellNum = Integer.valueOf(cellNumStr) - 1; mSsheet.getRow(rowNum).getCell(cellNum) .setCellValue(new HSSFRichTextString(sdf.format(report.getBeginDate().getTime()))); } else if (itemName.equals("endDate")) { rowNumStr = lineArray[1]; rowNum = Integer.valueOf(rowNumStr) - 1; cellNumStr = lineArray[2]; cellNum = Integer.valueOf(cellNumStr) - 1; mSsheet.getRow(rowNum).getCell(cellNum) .setCellValue(new HSSFRichTextString(sdf.format(report.getEndDate().getTime()))); } else if (itemName.equals("adultsTotal")) { rowNumStr = lineArray[1]; rowNum = Integer.valueOf(rowNumStr) - 1; cellNumStr = lineArray[2]; cellNum = Integer.valueOf(cellNumStr) - 1; //${register.artRegimenReport.totalAdultsDispensed} HSSFCell cell = mSsheet.getRow(rowNum).getCell(cellNum); cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(aReport.getArtRegimenReport().getTotalAdultsDispensed()); } else if (itemName.equals("childrenTotal")) { rowNumStr = lineArray[1]; rowNum = Integer.valueOf(rowNumStr) - 1; cellNumStr = lineArray[2]; cellNum = Integer.valueOf(cellNumStr) - 1; //${register.artRegimenReport.totalAdultsDispensed} HSSFCell cell = mSsheet.getRow(rowNum).getCell(cellNum); cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(aReport.getArtRegimenReport().getTotalChildrenDispensed()); } /*else if (itemName.equals("totalMalesNew")) { rowNumStr = lineArray[1]; rowNum = Integer.valueOf(rowNumStr)-1; cellNumStr = lineArray[2]; cellNum = Integer.valueOf(cellNumStr)-1; //${register.artRegimenReport.totalAdultsDispensed} mSsheet.getRow(rowNum).getCell(cellNum).setCellValue(aReport.getArtRegimenReport().getTotalChildrenDispensed()); } else if (itemName.equals("totalMalesRevisit")) { rowNumStr = lineArray[1]; rowNum = Integer.valueOf(rowNumStr)-1; cellNumStr = lineArray[2]; cellNum = Integer.valueOf(cellNumStr)-1; //${register.artRegimenReport.totalAdultsDispensed} mSsheet.getRow(rowNum).getCell(cellNum).setCellValue(aReport.getArtRegimenReport().getTotalChildrenDispensed()); } else if (itemName.equals("totalFemalesNew")) { rowNumStr = lineArray[1]; rowNum = Integer.valueOf(rowNumStr)-1; cellNumStr = lineArray[2]; cellNum = Integer.valueOf(cellNumStr)-1; //${register.artRegimenReport.totalAdultsDispensed} mSsheet.getRow(rowNum).getCell(cellNum).setCellValue(aReport.getArtRegimenReport().getTotalChildrenDispensed()); } else if (itemName.equals("totalFemalesRevisit")) { rowNumStr = lineArray[1]; rowNum = Integer.valueOf(rowNumStr)-1; cellNumStr = lineArray[2]; cellNum = Integer.valueOf(cellNumStr)-1; //${register.artRegimenReport.totalAdultsDispensed} mSsheet.getRow(rowNum).getCell(cellNum).setCellValue(aReport.getArtRegimenReport().getTotalChildrenDispensed()); }*/ } MonthlyArtSheetPopulater.populateMonthlyArtSheet(mReport, mSsheet, 8); //MonthlyArtSheetPopulater.populateDynamicMonthlyArtSheet(mReport, mSsheet, 100); } FileOutputStream stream = new FileOutputStream(path); wb.write(stream); stream.close(); } catch (Exception e) { e.printStackTrace(); } fwd = new ParameterActionForward(mapping.findForward(SUCCESS_FORWARD)); String url = path.replace("&", "%26"); fwd.addParameter("path", url); //return mapping.findForward("success"); return fwd; }
From source file:org.rti.zcore.dar.utils.PoiUtils.java
License:Apache License
/** * This utility is a version of HSSF.main that does not use deprecated methods. * It is helpful in figuring out what row a filed is on when outputting Excel files via POI. * @param pathExcelMaster/*from w w w .j a va 2s . c om*/ */ public static void testExcelOutput(String pathExcelMaster) { try { //HSSF hssf = new HSSF(args[ 0 ]); System.out.println("Data dump:\n"); //HSSFWorkbook wb = hssf.hssfworkbook; POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(pathExcelMaster)); HSSFWorkbook wb = new HSSFWorkbook(fs); for (int k = 0; k < wb.getNumberOfSheets(); k++) { System.out.println("Sheet " + k); HSSFSheet sheet = wb.getSheetAt(k); int rows = sheet.getPhysicalNumberOfRows(); for (int r = 0; r < rows; r++) { //HSSFRow row = sheet.getPhysicalRowAt(r); HSSFRow row = sheet.getRow(r); if (row != null) { int cells = row.getPhysicalNumberOfCells(); System.out.println("ROW " + row.getRowNum()); for (int c = 0; c < cells; c++) { //HSSFCell cell = row.getPhysicalCellAt(c); HSSFCell cell = row.getCell(c); String value = null; if (cell != null) { switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_FORMULA: value = "FORMULA "; value = "FORMULA " + cell.getCellFormula(); break; case HSSFCell.CELL_TYPE_NUMERIC: value = "NUMERIC value=" + cell.getNumericCellValue(); break; case HSSFCell.CELL_TYPE_STRING: //value = "STRING value=" + cell.getStringCellValue(); HSSFRichTextString str = cell.getRichStringCellValue(); value = "STRING value=" + str; break; default: } //System.out.println("CELL col=" + cell.getCellNum() + " VALUE=" + value); System.out.println("CELL col=" + cell.getColumnIndex() + " VALUE=" + value); } } } } } } catch (Exception e) { e.printStackTrace(); } }