Writing to Excel using java code


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 : https://nitinaggarwal.wordpress.com/2009/02/21/reading-contents-of-excel-using-java-code/

24 comments

  1. // 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”);

  2. 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?

  3. Hi Nitin,

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

    regards,
    Ankur

  4. 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?

  5. 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

  6. 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

  7. Hi,

    Is it possible to modify the cells of an existing excel sheet with out changing the content.

  8. 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..

  9. 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.

  10. 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???

  11. 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!

  12. 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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s