Writing to Excel using java code

21
February
2009

import java.io.FileOutputStream;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

/**
* This is a sample to create an Excel Sheet using Jakarta POI API
*
* @author Nitin Aggarwal
* @version 1.0
*/
public class CreateXL {
/** A place for the output Excel file to go */
public static String outputFile = “C:/Work/JPOI/excel_in_java.xls”;

public static void main(String argv[]) {
try {
// Create a New XL Document
HSSFWorkbook wb = new HSSFWorkbook();
// Make a worksheet in the XL document created
HSSFSheet sheet = wb.createSheet();
// Create row at index zero ( Top Row)
HSSFRow row = sheet.createRow((short) 0);
// Create a cell at index zero ( Top Left)
HSSFCell cell = row.createCell((short) 0);
// Lets make the cell a string type
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
// Type some content
cell.setCellValue(1234567.0);
// cell.setCellValue(“This is Nitin’s Sample Code”);
// The Output file is where the xls will be created
FileOutputStream fOut = new FileOutputStream(outputFile);
// Write the XL sheet
wb.write(fOut);
fOut.flush();
// Done Deal..
fOut.close();
System.out.println(“File Created ..”);

} catch (Exception e) {
System.out.println(“!!BANG!! xlCreate() : ” + e);
}

}

Let’s concentrate on just the interesting steps of Jakarta POI usage:

Create a new Excel document: workbook = new HSSFWorkbook();
Make a worksheet in that document and give the worksheet a name: sheet = workbook.createSheet("Nitin's Sample sheet");
Set the first three columns’ widths: sheet.setColumnWidth((short)0,(short)10000 );
Create the header line: HSSFRow row = sheet.createRow((short)0);

Create and set font and cell style:

   HSSFFont font = workbook.createFont();
   font.setColor(HSSFFont.COLOR_RED);
   font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
   // Create the style
      HSSFCellStyle cellStyle= workbook.createCellStyle();
      cellStyle.setFont(font);

Use the cell style:

      HSSFCell cell = row.createCell((short) 0);
      cell.setCellStyle(cellStyle);
      cell.setCellType(HSSFCell.CELL_TYPE_STRING);
      cell.setCellValue("Class Name ");

Write the output file:

      FileOutputStream fOut = new FileOutputStream(outputFile);
      // Write the Excel sheet
      workbook.write(fOut);
      fOut.flush();
      // Done deal. Close it.
      fOut.close();

So Now we know how to write to an excel file using java.

In order to read from an excel file Click Here : http://nitinaggarwal.wordpress.com/2009/02/21/reading-contents-of-excel-using-java-code/

Post Tagged with Java, Uncategorized

24 Responses so far.

zerminey says:

24th October 2013 at 7:39 am

Check out this Java Code Library from Aspose.Cells for java where you can find many sample codes in java related to excel spreadsheet including, reading, writing, modifying and even converting excel files to many other formats.
Eleni Bouzou says:

1st August 2012 at 3:04 pm

Hey man, nice blog! I need some help. How could I write in an existing file, I know the cell and the row? Any ideas? Thanks!
Chirag says:

19th July 2012 at 6:40 am

HI,
my excel sheet is having around 1lakh rows and few colums.
when i am writing this excel to a file using FileOutputStream,
its taking a long time..

Does anybody have an idea to speedup this???
pankaj says:

14th May 2012 at 10:28 am

Hi
Can i typecast workbook to writableworkbook in jxl?
oracle excel says:

16th December 2011 at 5:30 pm

If you are using Oracle database, you can write content to Excel file with ORA_EXCEL PL/SQL package (www.oraexcel.com)
nagarjuna says:

15th November 2011 at 6:32 am

can u give me the code for reading only name of excel file?
- Nits says:
  
  17th November 2011 at 11:26 am
  
  Hi Mate, Unfortunately I no longer have my workspace saved, I will have to redo the whole thing which I am planning to @ some point but not sure when… You should get help from the API.
Rahul says:

14th October 2011 at 9:49 am

i have a small requirement to append data to the already existing excel file???????….please help me
abc says:

14th October 2011 at 9:48 am

how to append data to already existing excel file???….plz reply
kannaaa says:

29th August 2011 at 10:17 am

is it possible to create a directory manually??????by using the directory i want to save file??is it possible???if so means,pls give some example also…

Thanks..
Gaurav Deswal says:

12th August 2011 at 9:57 am

thanks to all…. 🙂
Sharvan says:

30th May 2011 at 1:00 pm

Hi,

Is it possible to modify the cells of an existing excel sheet with out changing the content.
- Nits says:
  
  31st May 2011 at 8:26 am
  
  It is Sharvan, try to explore the API for the relevant operation.
nishil says:

15th April 2011 at 10:51 am

Hi Nitin
I want to write into already existing excel file,with out replacing the current data.
- Nits says:
  
  5th May 2011 at 11:06 am
  
  Hi Nishil,
  
  I have been very busy over the last few months… And would not be able to look at your issue anytime sooner…
  
  Will surely try to give it a shot ASAP.
  Apologies for that…
  Cheers Nitin
sujata says:

6th April 2011 at 9:05 am

posted Today 3:47:55 PM 0

Thanks Nitin.Its very useful.

I am working on Export to Excel functionality using jxls,where I will search the records,store in a bean,dispaly it in dispaly tag and when click on
the button ‘Export to Excel’ it will go to another jsp where I can select the fileds to export.
While exporting I am hiding the columns that has not been selected using

transformer.setColumnPropertyNamesToHide(hideCols);
and then
transformer.transformXLS(“c:Test.xls”, beans, “C:genereatedsheet.xls”);

But now I dont want to use hide functionality.

Is there any way where I can create excel only with selected fileds.
I dont want to use setColumnPropertyNamesToHide,only selected columns should be created in excel.

Thanks

Sujata
Pallavi says:

17th March 2011 at 10:33 am

Hi Nitin,

I want to retrieve multiple records from database and write them columwise in each cell of excelsheet.
eg: there are 27 rows with 3 columns. How will we write the code for this using poi?
2010 in review « Nitin's JAVA and SOA BLOG says:

2nd January 2011 at 8:37 am

[…] The busiest day of the year was December 22nd with 145 views. The most popular post that day was Writing to Excel using java code. […]
Nitin says:

7th August 2010 at 10:03 am

Nitin rocking all the world
Ankur Jindal says:

11th June 2009 at 5:41 am

Hi Nitin,

Can you give me any idea how to append to an existing excel file?

regards,
Ankur
Ankur Jindal says:

11th June 2009 at 5:40 am

Hi Nitin,

Thanks for the code. I am trying to append to the excel file and have modified the statement :
FileOutputStream fOut = new FileOutputStream(outputFile); to
FileOutputStream fOut = new FileOutputStream(outputFile, true);
means i have opened the file in append mode. But the file still remains the same. Can you give me any idea how to append?
Kiran says:

21st May 2009 at 3:46 pm

Nitin,

can u give any idea if i want to write multiple sheets in one excel file.
zet ucu says:

2nd March 2009 at 2:24 pm

// officetools.jar available at http://www.dancrintea.ro/xls-to-pdf/

import officetools.OfficeFile;

FileInputStream fis=new FileInputStream(new File(”test.xls”));

OfficeFile f=new OfficeFile(fis,”localhost”,”8100″, false);

// write 495 to a column
for(int i=0;i<=99;i++)
f.setCell(1,i, “495”);
Reading contents of Excel using java code « Nitinaggarwal’s Blog says:

21st February 2009 at 12:32 am

[…] by Writing to Excel using java code « Nitinaggarwal’s Blog — February 21, 2009 […]