Writing to Excel using java code

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

Post Tagged with ,

24 Responses so far.

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

  2. zet ucu says:

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

  3. Kiran says:

    Nitin,

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

  4. Ankur Jindal says:

    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?

  5. Ankur Jindal says:

    Hi Nitin,

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

    regards,
    Ankur

  6. Nitin says:

    Nitin rocking all the world

  7. […] 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. […]

  8. Pallavi says:

    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?

  9. sujata says:

    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

  10. nishil says:

    Hi Nitin
    I want to write into already existing excel file,with out replacing the current data.

    • Nits says:

      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

  11. Sharvan says:

    Hi,

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

  12. Gaurav Deswal says:

    thanks to all…. 🙂

  13. kannaaa says:

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

  14. abc says:

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

  15. Rahul says:

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

  16. nagarjuna says:

    can u give me the code for reading only name of excel file?

    • Nits says:

      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.

  17. oracle excel says:

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

  18. pankaj says:

    Hi
    Can i typecast workbook to writableworkbook in jxl?

  19. Chirag says:

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

  20. Eleni Bouzou says:

    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!

  21. zerminey says:

    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

Your email address will not be published. Required fields are marked *