Office

Wolfgang Fahl

Introduction[edit]

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[edit]

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[edit]

template structure[edit]

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

@args: arguments[edit]

@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[edit]

@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[edit]

@{
  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[edit]

    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[edit]

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[edit]

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[edit]

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[edit]

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[edit]

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[edit]

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[edit]

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[edit]

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)
}
@("|}")
}

Office

Contents

Introduction[edit]

Supported Document Formats[edit]

Contents

Templates[edit]

Contents

template structure[edit]

@args: arguments[edit]

@import: import statements[edit]

java code and class declaration[edit]

constructor and error handling[edit]

Word[edit]

Word97[edit]

Excel[edit]

Excel97[edit]

Powerpoint[edit]

Powerpoint97[edit]

PDF[edit]

VCard[edit]