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();
}
}
}