Office

From BITPlan Wiki
Jump to navigation Jump to search

Introduction

The templates on this page are Wikitask helper templates to work with Office Documents. The Java library being used for Microsoft Office Documents is Apache POI. For the Access to Adobe Portable Document Files Apache PDFBox is used.

Supported Document Formats

Logo Format Documentation Stackoverflow Tutorial example
Word docx XWPF xwpf WikiTaskTutorial/Word
Word97 doc HWPF hwpf WikiTaskTutorial/Word97
Excel xlsx HSSF+XSSF xssf WikiTaskTutorial/Excel
Excel 97 xls HSSF+XSSF HSSF UML diagram hssf WikiTaskTutorial/Excel97
Powerpoint pptx XSLF xslf WikiTaskTutorial/Powerpoint
Powerpoint97 ppt HSLF hslf WikiTaskTutorial/Powerpoint97
Portable Document Format pdf PDF pdfbox WikiTaskTutorial/Portable Document Format
VCard vcf VCard vcard WikiTaskTutorial/VCard

The above table shows the supported Document Formats and has links to the Apache POI documentation, corressponding Stackoverflow questions and the WikiTask tutorial example for each document format.

Templates

template structure

The following templates have a common structure. First the arguments are declared:

@args: arguments

@args() {
  String title 
  String logo
  org.sidif.wiki.WikiTask wikiTask
}

The argument/parameter wikiTask holds the information how the template is called. The input can e.g. be fetched from wikiTask.getInput(). It should contain the URL of a Microsoft Office Document

@import: import statements

@import java.net.URL
@import org.apache.poi.xwpf.usermodel.XWPFDocument;
@import org.apache.poi.xwpf.usermodel.XWPFParagraph

The import statements are specific to the Office document format being used. See Office#Supported_Microsoft_Office_Document_Formats.

java code and class declaration

@{
  class WordDocx {
   ...

    public WordDocx(String url) {
      ...
    } 
  } // WordDocx
  WordDocx docx=new WordDocx(wikiTask.getInput());
 
}

In a Java Code section @{...} a class corresponding to the Microsoft Office Document Format is declared. The constructor takes a url as a parameter. From such a url e.g. http://somedomain/somedocument.doc the Document is fetched.

constructor and error handling

    public XWPFDocument doc;
    public Throwable error;
 
    public WordDocx(String url) {
      try {
        InputStream is = new URL(url).openStream();
        doc = new XWPFDocument(is);
      }  catch (Throwable th) {
        error=th;
      }
    }

The constructor tries to open the document as the corresponding Apache POI document type. E.g. a docx document is openend as XWPFDocument. If this fails the Exception/Throwable caught will be available in the member variable error.

Word

Logo Format Apache POI documentation Stackoverflow Tutorial example
Word docx XWPF xwpf WikiTaskTutorial/Word 
@args() {
  String title 
  String logo
  org.sidif.wiki.WikiTask wikiTask
}
@import java.net.URL
@import org.apache.poi.xwpf.usermodel.XWPFDocument;
@import org.apache.poi.xwpf.usermodel.XWPFParagraph;
@{
  class WordDocx {
    public XWPFDocument doc;
    public Throwable error;
    
    public WordDocx(String url) {
      try {
        InputStream is = new URL(url).openStream();
        doc = new XWPFDocument(is);
      }  catch (Throwable th) {
        error=th;
      }
    } 
  } // WordDocx
  WordDocx docx=new WordDocx(wikiTask.getInput());

}

Word97

Logo Format Apache POI documentation Stackoverflow Tutorial example
Word97 doc HWPF hwpf WikiTaskTutorial/Word97 
@args() {
  String title 
  String logo
  org.sidif.wiki.WikiTask wikiTask
}
@import java.net.URL
@import org.apache.poi.hwpf.HWPFDocument;
@import org.apache.poi.hwpf.extractor.WordExtractor;
@import org.apache.poi.hwpf.usermodel.Range;
@import org.apache.poi.poifs.filesystem.POIFSFileSystem;
@{
  class WordDoc {
    public HWPFDocument doc;
    public WordExtractor we;
    public Range range;
    public Throwable error;

    public WordDoc(String url) {
      try {
        InputStream is = new URL(url).openStream();
        POIFSFileSystem fs = new POIFSFileSystem(is);
        doc = new HWPFDocument(fs);
        we = new WordExtractor(doc);
        range = doc.getRange();
      } catch (Throwable th) {
        error=th;
      }
    } 
  } // WordDoc
  WordDoc doc=new WordDoc(wikiTask.getInput());

}

Excel

Logo Format Apache POI documentation Stackoverflow Tutorial example
Excel xlsx HSSF+XSSF xssf WikiTaskTutorial/Excel 
@args() {
  String title 
  String logo
  org.sidif.wiki.WikiTask wikiTask
}
@import java.net.URL
@import org.apache.poi.ss.usermodel.Cell
@import org.apache.poi.ss.usermodel.Row
@import org.apache.poi.xssf.usermodel.XSSFSheet
@import org.apache.poi.xssf.usermodel.XSSFWorkbook
@import org.apache.poi.xssf.usermodel.XSSFCell
@import org.apache.poi.xssf.usermodel.XSSFRow
@{
  class Excel {
    public XSSFWorkbook workbook =null;
    public Throwable error;

    public List<List<String>> getSheetContent(XSSFSheet sheet) {
      List<List<String>> result=new ArrayList<List<String>>();
      Iterator<Row> rows = sheet.rowIterator();
      while (rows.hasNext()) {
        XSSFRow row = (XSSFRow) rows.next();
        Iterator<Cell> cells = row.cellIterator();
        List<String> rowList=new ArrayList<String>();
        while (cells.hasNext()) {
          XSSFCell cell = (XSSFCell) cells.next();
          String cellValue = cell.toString();
          if (!"".equals(cellValue))
            rowList.add(cellValue);
        }
        if (rowList.size()>0)
          result.add(rowList);
      }
      return result;
    }

    public Excel(String url) {
      // http://stackoverflow.com/questions/5836965/how-to-open-xlsx-files-with-poi-ss
      try {
        InputStream is = new URL(url).openStream();
        workbook = new XSSFWorkbook(is);
      } catch (Throwable th) {
        error=th;
      }
    } 
  } // Excel
  Excel excel=new Excel(wikiTask.getInput());

}

Excel97

Logo Format Apache POI documentation Stackoverflow Tutorial example
Excel97 xls HSSF+XSSF HSSF UML diagram hssf WikiTaskTutorial/Excel97 
@args() {
  String title 
  String logo
  org.sidif.wiki.WikiTask wikiTask
}
@import java.net.URL
@import org.apache.poi.ss.usermodel.Row
@import org.apache.poi.ss.usermodel.Cell
@import org.apache.poi.hssf.usermodel.HSSFSheet;
@import org.apache.poi.hssf.usermodel.HSSFWorkbook;
@import org.apache.poi.hssf.usermodel.HSSFCell;
@import org.apache.poi.hssf.usermodel.HSSFRow;
@{
  class Excel {
    public HSSFWorkbook workbook =null;
    public Throwable error;

    public List<List<String>> getSheetContent(HSSFSheet sheet) {
      List<List<String>> result=new ArrayList<List<String>>();
      Iterator<Row> rows = sheet.rowIterator();
      while (rows.hasNext()) {
        HSSFRow row = (HSSFRow) rows.next();
        Iterator<Cell> cells = row.cellIterator();
        List<String> rowList=new ArrayList<String>();
        int nonempty=0;
        while (cells.hasNext()) {
          HSSFCell cell = (HSSFCell) cells.next();
          String cellValue = cell.toString();
          if (!"".equals(cellValue)) {
            nonempty++;
            rowList.add(cellValue);
          } else {
            rowList.add("");
          }
        }
        if (nonempty>0)
          result.add(rowList);
      }
      return result;
    }
 

    public Excel(String url) {
      // http://stackoverflow.com/questions/5836965/how-to-open-xlsx-files-with-poi-ss
      try {
        InputStream is = new URL(url).openStream();
        workbook = new HSSFWorkbook(is);
      }  catch (Throwable th) {
        error=th;
      }
    } 
  } // Excel
  Excel excel=new Excel(wikiTask.getInput());

}

Powerpoint

Logo Format Apache POI documentation Stackoverflow Tutorial example
Powerpoint pptx XSLF xslf WikiTaskTutorial/Powerpoint 
@args() {
  String title 
  String logo
  org.sidif.wiki.WikiTask wikiTask
}
@import java.net.URL
@import org.apache.poi.ss.usermodel.Cell
@import org.apache.poi.xslf.usermodel.XMLSlideShow;
@import org.apache.poi.xslf.usermodel.XSLFCommentAuthors
@import org.apache.poi.xslf.usermodel.XSLFComments
@import org.apache.poi.xslf.usermodel.XSLFCommonSlideData
@import org.apache.poi.xslf.usermodel.XSLFNotes
@import org.apache.poi.xslf.usermodel.XSLFRelation
@import org.apache.poi.xslf.usermodel.XSLFSlide
@import org.apache.poi.xslf.usermodel.XSLFSlideLayout
@import org.apache.poi.xslf.usermodel.XSLFSlideMaster
@{
  class Powerpoint {
    public XMLSlideShow slideshow=null;
    public List<XSLFSlide> slides=null;
    public Throwable error;
 
    public Powerpoint(String url) {
      try {
        InputStream is = new URL(url).openStream();
        slideshow = new XMLSlideShow(is);
        slides = slideshow.getSlides();
      } catch (Throwable th) {
        error=th;
      }
    } 
  } // Powerpoint
  Powerpoint powerpoint=new Powerpoint(wikiTask.getInput());
}

Powerpoint97

Logo Format Apache POI documentation Stackoverflow Tutorial example
Powerpoint97 ppt HSLF hslf WikiTaskTutorial/Powerpoint97 
@args() {
  String title 
  String logo
  org.sidif.wiki.WikiTask wikiTask
}
@import java.net.URL
@import org.apache.poi.hslf.usermodel.SlideShow
@import org.apache.poi.hslf.model.Slide
@{
  class Powerpoint {
    public SlideShow slideshow=null;
    public Slide[] slides=null;
    public Throwable error;
 
    public Powerpoint(String url) {
      try {
        InputStream is = new URL(url).openStream();
        slideshow = new SlideShow(is);
        is.close();
        slides = slideshow.getSlides();
      } catch (Throwable th) {
        error=th;
      }
    } 
  } // Powerpoint
  Powerpoint powerpoint=new Powerpoint(wikiTask.getInput());
}

PDF

Logo Format Apache documentation Stackoverflow Tutorial example
Portable Document Format pdf PDF pdfbox WikiTaskTutorial/Portable Document Format 
@args() {
  String title 
  String logo
  org.sidif.wiki.WikiTask wikiTask
}
@import java.net.URL
@import org.apache.pdfbox.pdmodel.PDDocument
@import org.apache.pdfbox.pdmodel.PDPage
@import org.apache.pdfbox.util.PDFTextStripper
@{
 /**
  * Portable Document File extractor help
  */
 class PDF {
   public Throwable error;
   PDDocument doc;
   PDFTextStripper pdfStripper;
   
   /**
    * construct this PDF from the given url
    */
   public PDF(String url) {
     try {
       // might want to switch off logging here to improve performance
        String[] loggers = { "org.apache.pdfbox.util.PDFStreamEngine",
            "org.apache.pdfbox.util", "org.apache.pdfbox.util.PDFStreamEngine",
            "org.apache.pdfbox.pdfparser.PDFObjectStreamParser",
            "org.apache.pdfbox.cos.COSDocument",
            "org.apache.pdfbox.pdmodel.font.PDSimpleFont",
            "org.apache.pdfbox.pdmodel.graphics.xobject.PDPixelMap",
            "org.apache.pdfbox.pdmodel.graphics.color.PDSeparation",
            "org.apache.pdfbox.pdmodel.graphics.color.PDColorState",
            "org.apache.pdfbox.pdmodel.graphics.color.PDICCBased",
            "org.apache.pdfbox.pdfparser.PDFObjectStreamParser" };
       for (String logger : loggers) {
          org.apache.log4j.Logger logpdfengine = org.apache.log4j.Logger
              .getLogger(logger);
          logpdfengine.setLevel(org.apache.log4j.Level.OFF);
       }
       InputStream is = new URL(url).openStream();
       doc=PDDocument.load(is);
       pdfStripper = new PDFTextStripper();
     } catch(Throwable th) {
       error=th;
     }
   }
   public String getPageText(int page) {
     String result="?";
     try {
       pdfStripper.setStartPage(page);
       pdfStripper.setEndPage(page); 
       result=pdfStripper.getText(doc);
     } catch(Throwable th) {
       error=th;
       result="Error: "+th.getMessage();
     }
     return result;
   }
 }
 // don't create this here ... but might make tutorial examples fail
 // PDF pdf=new PDF(wikiTask.getInput());
}

VCard

Logo Format Documentation Stackoverflow Tutorial example
VCard vcf VCard vcard WikiTaskTutorial/VCard 
@args() {
  org.sidif.wiki.WikiTask wikiTask
}
@import java.net.URL
@import ezvcard.Ezvcard
@import ezvcard.VCard
@import ezvcard.property.Email
@import ezvcard.property.Address
@import ezvcard.property.Title
@import ezvcard.property.Telephone
@import ezvcard.property.Note
@{ 
  /**
   * holder for a VCard
   */
  class VCardHolder {
    String text;
    Throwable error;
    VCard vcard;

    /**
     * construct a VCard from the given url
     */
    public VCardHolder(String url) {
      try {
        InputStream is = new URL(url).openStream();
        text=org.apache.commons.io.IOUtils.toString(is, "UTF-8");
        vcard = Ezvcard.parse(text).first();
      } catch (Throwable th) { 
        error=th;
      }
    }
  }
  VCardHolder vcardHolder=new VCardHolder(wikiTask.getInput());

}
@def vcardAddressAsTableRow(Address address) {
!@(address.getTypes().toArray()[0]) address
|@address.getStreetAddress()<br>@address.getPostalCode() @address.getLocality()<br>@address.getCountry()
|-
}
@def vcardPhoneAsTableRow(Telephone phone) {
!@if (phone.getTypes().size()>0) { @phone.getTypes().toArray()[0] } phone
|<nowiki>@phone.getText()</nowiki>
|-
}
@def vcardNoteAsTableRow(Note note) {
!note
|<nowiki>@note.getValue()</nowiki>
|-
}
@def vcardTitleAsTableRow(Title title) {
! @if (title.getType()) { @title.getType() } title
|@title.getValue()
|-
}
@def vcardAsTable(VCard vcard) {
@("{|") class='wikitable sortable'
!fullname:
|@vcard.getFormattedName().getValue()
|-
@for (Title title:vcard.getTitles()) {
@vcardTitleAsTableRow(title);
}	
!firstname:
|@vcard.getStructuredName().getGiven()
|-
!lastname:
|@vcard.getStructuredName().getFamily()
|-
!organization:
|@if (vcard.getOrganization()) { @vcard.getOrganization().getValues().get(0) }
|-
@for (Address address:vcard.getAddresses()) {
@vcardAddressAsTableRow(address)
}		
@for (Email email : vcard.getEmails()){
!email:
|@email.getValue()
|-
}
@for (Telephone phone : vcard.getTelephoneNumbers()) {
@vcardPhoneAsTableRow(phone)
}
@for (Note note : vcard.getNotes()) {
@vcardNoteAsTableRow(note)
}
@("|}")
}
Retrieved from "https://wiki.bitplan.com/index.php?title=Office&oldid=173"