Friday, April 16, 2010

Excel Manipulations with Java (POI 3.6)

This post consists of a code fragment that can be used to perform various operations like, creating excel files, updating them, copying images in the excel files and pushing them into an applet using Java.

My experiments in this post can be summarized into the following
  • createWorkBook()
  • readFromWorkBookDemo()
  • writeToWorkBookDemo()
  • getPictureData()

In order to work with these functions you may have to download the following jars, get them into the resources of your project and proceed with my code.
I have used the POI 3.6 API to accomplish the same .

Click here to download the necessary files

Source Code
**************************************************
import java.awt.Color;
import java.awt.Image;
import java.awt.Toolkit;
import java.awt.datatransfer.Clipboard;
import java.awt.datatransfer.Transferable;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.Enumeration;
import java.util.List;

import javax.swing.Icon;
import javax.swing.ImageIcon;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JPanel;
import javax.swing.TransferHandler;

import org.apache.poi.hslf.model.Sheet;
import org.apache.poi.hssf.model.Workbook;
import org.apache.poi.hssf.record.chart.ChartRecord;
import org.apache.poi.hssf.record.formula.functions.Row;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFChart;
import org.apache.poi.hssf.usermodel.HSSFPictureData;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellReference;

public class NewWorkbook {

public void createWorkBook() throws IOException {
HSSFWorkbook wb = new HSSFWorkbook();
FileOutputStream fileOut = new FileOutputStream("c:\\workbook.xls");
wb.write(fileOut);
fileOut.close();
}

public void readFromWorkBookDemo() throws IOException {
InputStream is=new FileInputStream("c:\\workbook.xls");
HSSFWorkbook wb = new HSSFWorkbook(is);
HSSFSheet sheet1 = wb.getSheetAt(0);
HSSFRow row = sheet1.getRow((short) 0);
HSSFCell cell=row.getCell(0);
System.out.print(cell.getStringCellValue());

}

public void writeToWorkBookDemo() throws IOException {
InputStream is=new FileInputStream("c:\\speedometer.xls");
HSSFWorkbook wb = new HSSFWorkbook(is);
HSSFSheet sheet1 = wb.getSheetAt(0);
HSSFRow row = sheet1.getRow((short) 2);
HSSFCell cell=row.getCell(5);
double d=80;
cell.setCellValue(d);
is.close();
FileOutputStream fileOut = new FileOutputStream("c:\\speedometer.xls");
wb.write(fileOut);
fileOut.close();
}

public void getPictureData() throws IOException {
InputStream is=new FileInputStream("c:\\workbook.xls");
HSSFWorkbook wb = new HSSFWorkbook(is);
List pics=(List) wb.getAllPictures();
System.out.println(pics.size());
System.out.println(pics.get(0).suggestFileExtension());
HSSFChart.getSheetCharts(wb.getSheetAt(0));
ImageIcon imageIcon = new ImageIcon(pics.get(0).getData());
Image image = imageIcon.getImage();
JLabel label=new JLabel(imageIcon);
final Clipboard clipboard =Toolkit.getDefaultToolkit().getSystemClipboard();
TransferHandler handler = label.getTransferHandler();
System.out.println(label.toString());
System.out.println(clipboard.toString());
JFrame myFrame=new JFrame();
myFrame.setSize(500,500);
JPanel panel = new JPanel();
panel.setBackground(Color.CYAN);
panel.add(label);
myFrame.getContentPane().add(panel);
myFrame.setVisible(true);
}

public static void main (String args[]) {
NewWorkbook nwb=new NewWorkbook();
try {
//nwb.createWorkBook();
//nwb.readFromWorkBookDemo();
//nwb.getPictureData();
nwb.writeToWorkBookDemo();
} catch (Exception e) {
e.printStackTrace();
}
}
}

6 comments:

  1. hi,

    have you a similar code but using to generate a word/docx code?

    Thanks in advance

    Good stuff/code you have here

    Filipe

    ReplyDelete
  2. Thanks Filipe. Nope I haven't yet developed any code for that yet. Will post them when time permits :)

    ReplyDelete
  3. Hi, Any news/developments to "Word manipulations with java poi3.6"?
    I tried to do it but i get some java errors i cant understand!

    Best regards

    Filipe

    ReplyDelete
  4. Have n't spent time with that yet.... Will try and find some :)

    ReplyDelete
  5. I found this but uses athor than POI its docx4j:

    package org.docx4j.samples;
    import java.io.File;
    import javax.xml.bind.JAXBContext;
    import javax.xml.bind.Marshaller;
    import org.docx4j.convert.out.flatOpcXml.FlatOpcXmlCreator;
    import org.docx4j.jaxb.Context;
    import org.docx4j.jaxb.NamespacePrefixMapperUtils;
    import org.docx4j.model.table.TblFactory;
    import org.docx4j.openpackaging.packages.WordprocessingMLPackage;
    import org.docx4j.openpackaging.parts.PartName;
    import org.docx4j.openpackaging.parts.WordprocessingML.AlternativeFormatInputPart;
    import org.docx4j.openpackaging.contenttype.CTDefault;
    import org.docx4j.openpackaging.contenttype.ContentType;
    import org.docx4j.openpackaging.contenttype.ContentTypes;
    import org.docx4j.openpackaging.contenttype.ObjectFactory;
    import org.docx4j.openpackaging.exceptions.InvalidFormatException;
    import org.docx4j.openpackaging.io.SaveToZipFile;
    import org.docx4j.relationships.Relationship;
    import org.docx4j.wml.CTAltChunk;
    import org.docx4j.wml.Tbl;

    /**
    47 * Creates a WordprocessingML document from scratch.
    48 *
    49 * @author Jason Harrop
    50 * @version 1.0
    51 */
    public class CreateWordprocessingMLDocument {

    public static void main(String[] args) throws Exception {

    boolean save = true;
    System.out.println( "Creating package..");
    WordprocessingMLPackage wordMLPackage = WordprocessingMLPackage.createPackage(); wordMLPackage.getMainDocumentPart()
    .addStyledParagraphOfText("Title", "Hello world");
    wordMLPackage.getMainDocumentPart().addParagraphOfText("from docx4j!");
    // To get bold text, you must set the run's rPr@w:b,
    // so you can't use the createParagraphOfText convenience method
    //org.docx4j.wml.P p = wordMLPackage.getMainDocumentPart().createParagraphOfText("text");

    org.docx4j.wml.ObjectFactory factory = new org.docx4j.wml.ObjectFactory();
    org.docx4j.wml.P p = factory.createP();

    org.docx4j.wml.Text t = factory.createText();
    t.setValue("text");

    org.docx4j.wml.R run = factory.createR();
    run.getRunContent().add(t); p.getParagraphContent().add(run);


    org.docx4j.wml.RPr rpr = factory.createRPr();
    org.docx4j.wml.BooleanDefaultTrue b = new org.docx4j.wml.BooleanDefaultTrue();
    b.setVal(true);
    rpr.setB(b);

    run.setRPr(rpr);

    // Optionally, set pPr/rPr@w:b
    org.docx4j.wml.PPr ppr = factory.createPPr();
    p.setPPr( ppr );
    org.docx4j.wml.ParaRPr paraRpr = factory.createParaRPr();
    ppr.setRPr(paraRpr);
    rpr.setB(b);

    wordMLPackage.getMainDocumentPart().addObject(p);

    continues...

    ReplyDelete
  6. its better to send you the full file ok?
    Can you implement this?

    Regards

    ReplyDelete