By user177785


2009-10-04 10:54:38 8 Comments

I want to read and write an Excel file from Java with 3 columns and N rows, printing one string in each cell. Can anyone give me simple code snippet for this? Do I need to use any external lib or does Java have built-in support for it?

I want to do the following:

for(i=0; i <rows; i++)
     //read [i,col1] ,[i,col2], [i,col3]

for(i=0; i<rows; i++)
    //write [i,col1], [i,col2], [i,col3]

20 comments

@Abderrahmen 2019-03-30 16:48:19

You need Apache POI library and this code below should help you

    import java.io.File;
    import java.io.FileInputStream;
    import java.io.FileNotFoundException;
    import java.io.FileOutputStream;
    import java.io.IOException;
    import java.util.ArrayList;
    import java.util.List;
    import java.util.Iterator;
    //*************************************************************
    import org.apache.poi.ss.usermodel.Sheet;
    import org.apache.poi.ss.usermodel.Cell;
    import org.apache.poi.ss.usermodel.Row;
    import org.apache.poi.ss.usermodel.Workbook;
    import org.apache.poi.xssf.usermodel.XSSFSheet;
    import org.apache.poi.xssf.usermodel.XSSFWorkbook;

    //*************************************************************
   public class AdvUse {

    private static Workbook wb ; 
    private static Sheet sh ; 
    private static FileInputStream fis ; 
    private static FileOutputStream fos  ; 
    private static Row row  ; 
    private static Cell cell  ;
    private static String ExcelPath ; 

    //*************************************************************
    public static void setEcxelFile(String ExcelPath, String SheetName) throws Exception {
    try {
   File f= new File(ExcelPath); 
   if(!f.exists()){
       f.createNewFile();
       System.out.println("File not Found so created");
   }

    fis = new FileInputStream("./testData.xlsx");
    wb = WorkbookFactory.create(fis); 
    sh = wb.getSheet("SheetName");
    if(sh == null){
        sh = wb.getSheet(SheetName); 
    }
    }catch(Exception e)
    {System.out.println(e.getMessage());
    }
    }

      //*************************************************************
      public static void setCellData(String text , int rowno , int colno){
    try{
        row = sh.getRow(rowno);
        if(row == null){
            row = sh.createRow(rowno);
        }
        cell = row.getCell(colno);
        if(cell!=null){
            cell.setCellValue(text);

        }
        else{
            cell = row.createCell(colno);
            cell.setCellValue(text);

        }
        fos = new FileOutputStream(ExcelPath);
        wb.write(fos);
        fos.flush();
        fos.close();
    }catch(Exception e){
        System.out.println(e.getMessage());
    }
    }

      //*************************************************************
      public static String getCellData(int rowno , int colno){
        try{

            cell = sh.getRow(rowno).getCell(colno); 
            String CellData = null ;
            switch(cell.getCellType()){
            case  STRING :
                CellData = cell.getStringCellValue();
               break ; 
            case NUMERIC : 
                CellData = Double.toString(cell.getNumericCellValue());
                if(CellData.contains(".o")){
                    CellData = CellData.substring(0,CellData.length()-2);

                }
            break ; 
            case BLANK : 
            CellData = ""; break ; 

            }
            return CellData;
        }catch(Exception e){return ""; }
    }

       //*************************************************************
      public static int getLastRow(){
        return sh.getLastRowNum();
    }

@Brian Agnew 2009-10-04 10:57:12

Apache POI can do this for you. Specifically the HSSF module. The quick guide is most useful. Here's how to do what you want - specifically create a sheet and write it out.

Workbook wb = new HSSFWorkbook();
//Workbook wb = new XSSFWorkbook();
CreationHelper createHelper = wb.getCreationHelper();
Sheet sheet = wb.createSheet("new sheet");

// Create a row and put some cells in it. Rows are 0 based.
Row row = sheet.createRow((short)0);
// Create a cell and put a value in it.
Cell cell = row.createCell(0);
cell.setCellValue(1);

// Or do it on one line.
row.createCell(1).setCellValue(1.2);
row.createCell(2).setCellValue(
createHelper.createRichTextString("This is a string"));
row.createCell(3).setCellValue(true);

// Write the output to a file
FileOutputStream fileOut = new FileOutputStream("workbook.xls");
wb.write(fileOut);
fileOut.close();

@Ran Adler 2014-12-23 08:21:18

There is a new easy and very cool tool (10x to Kfir): xcelite

Write:

public class User { 

  @Column (name="Firstname")
  private String firstName;

  @Column (name="Lastname")
  private String lastName;

  @Column
  private long id; 

  @Column
  private Date birthDate; 
}

Xcelite xcelite = new Xcelite();    
XceliteSheet sheet = xcelite.createSheet("users");
SheetWriter<User> writer = sheet.getBeanWriter(User.class);
List<User> users = new ArrayList<User>();
// ...fill up users
writer.write(users); 
xcelite.write(new File("users_doc.xlsx"));

Read:

Xcelite xcelite = new Xcelite(new File("users_doc.xlsx"));
XceliteSheet sheet = xcelite.getSheet("users");
SheetReader<User> reader = sheet.getBeanReader(User.class);
Collection<User> users = reader.read();

@rogeriopvl 2009-10-04 10:59:35

Try the Apache POI HSSF. Here's an example on how to read an excel file:

try {
    POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(file));
    HSSFWorkbook wb = new HSSFWorkbook(fs);
    HSSFSheet sheet = wb.getSheetAt(0);
    HSSFRow row;
    HSSFCell cell;

    int rows; // No of rows
    rows = sheet.getPhysicalNumberOfRows();

    int cols = 0; // No of columns
    int tmp = 0;

    // This trick ensures that we get the data properly even if it doesn't start from first few rows
    for(int i = 0; i < 10 || i < rows; i++) {
        row = sheet.getRow(i);
        if(row != null) {
            tmp = sheet.getRow(i).getPhysicalNumberOfCells();
            if(tmp > cols) cols = tmp;
        }
    }

    for(int r = 0; r < rows; r++) {
        row = sheet.getRow(r);
        if(row != null) {
            for(int c = 0; c < cols; c++) {
                cell = row.getCell((short)c);
                if(cell != null) {
                    // Your code here
                }
            }
        }
    }
} catch(Exception ioe) {
    ioe.printStackTrace();
}

On the documentation page you also have examples of how to write to excel files.

@DavidVdd 2012-11-07 16:21:27

row.getCell((short)c); is deprecated I think I have to use int instead.

@nicolallias 2014-12-23 14:18:01

in fact, just remove the (short) cast poi.apache.org/apidocs/org/apache/poi/hssf/usermodel/…

@pearcemerritt 2016-03-04 19:23:36

In my attempts to use this code, I found that HSSFSheet's getPhysicalNumberOfRows() method seemed to return the number of non-empty rows (it was not obvious to me that's what "physical" meant). So, if your excel file has a lot of empty rows (i.e. for formatting reasons), you might have more luck using rows = sheet.getLastRowNum();. This should also mean you can remove the trick (see comment) from the first loop: for(int i = 0; i <= rows; i++) { (note the change from < to <=).

@Karen 2018-03-29 08:04:59

Sorry to dig this again but i has the same problem as this, Let say you have 3 column of Name, BirthDate, Gender. How do you add that 3 to separate Vector by reading with this code?

@Mahender Reddy Yasa 2018-06-14 07:24:32

why nobody have given option to use JXI

@Ring 2019-01-07 18:17:42

Where is the file encoding specified?

@Raju 2018-07-20 21:46:10

You can not read & write same file in parallel(Read-write lock). But, we can do parallel operations on temporary data(i.e. Input/output stream). Write the data to file only after closing the input stream. Below steps should be followed.

  • Open the file to Input stream
  • Open the same file to an Output Stream
  • Read and do the processing
  • Write contents to output stream.
  • Close the read/input stream, close file
  • Close output stream, close file.

Apache POI - read/write same excel example

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;
import java.util.Set;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;


public class XLSXReaderWriter {

    public static void main(String[] args) {

        try {
            File excel = new File("D://raju.xlsx");
            FileInputStream fis = new FileInputStream(excel);
            XSSFWorkbook book = new XSSFWorkbook(fis);
            XSSFSheet sheet = book.getSheetAt(0);

            Iterator<Row> itr = sheet.iterator();

            // Iterating over Excel file in Java
            while (itr.hasNext()) {
                Row row = itr.next();

                // Iterating over each column of Excel file
                Iterator<Cell> cellIterator = row.cellIterator();
                while (cellIterator.hasNext()) {

                    Cell cell = cellIterator.next();

                    switch (cell.getCellType()) {
                    case Cell.CELL_TYPE_STRING:
                        System.out.print(cell.getStringCellValue() + "\t");
                        break;
                    case Cell.CELL_TYPE_NUMERIC:
                        System.out.print(cell.getNumericCellValue() + "\t");
                        break;
                    case Cell.CELL_TYPE_BOOLEAN:
                        System.out.print(cell.getBooleanCellValue() + "\t");
                        break;
                    default:

                    }
                }
                System.out.println("");
            }

            // writing data into XLSX file
            Map<String, Object[]> newData = new HashMap<String, Object[]>();
            newData.put("1", new Object[] { 1d, "Raju", "75K", "dev",
                    "SGD" });
            newData.put("2", new Object[] { 2d, "Ramesh", "58K", "test",
                    "USD" });
            newData.put("3", new Object[] { 3d, "Ravi", "90K", "PMO",
                    "INR" });

            Set<String> newRows = newData.keySet();
            int rownum = sheet.getLastRowNum();

            for (String key : newRows) {
                Row row = sheet.createRow(rownum++);
                Object[] objArr = newData.get(key);
                int cellnum = 0;
                for (Object obj : objArr) {
                    Cell cell = row.createCell(cellnum++);
                    if (obj instanceof String) {
                        cell.setCellValue((String) obj);
                    } else if (obj instanceof Boolean) {
                        cell.setCellValue((Boolean) obj);
                    } else if (obj instanceof Date) {
                        cell.setCellValue((Date) obj);
                    } else if (obj instanceof Double) {
                        cell.setCellValue((Double) obj);
                    }
                }
            }

            // open an OutputStream to save written data into Excel file
            FileOutputStream os = new FileOutputStream(excel);
            book.write(os);
            System.out.println("Writing on Excel file Finished ...");

            // Close workbook, OutputStream and Excel file to prevent leak
            os.close();
            book.close();
            fis.close();

        } catch (FileNotFoundException fe) {
            fe.printStackTrace();
        } catch (IOException ie) {
            ie.printStackTrace();
        }
    }
}

@KIBOU Hassan 2017-04-13 08:16:34

For reading a xlsx file we can use Apache POI libs Try this:

public static void readXLSXFile() throws IOException
    {
        InputStream ExcelFileToRead = new FileInputStream("C:/Test.xlsx");
        XSSFWorkbook  wb = new XSSFWorkbook(ExcelFileToRead);

        XSSFWorkbook test = new XSSFWorkbook(); 

        XSSFSheet sheet = wb.getSheetAt(0);
        XSSFRow row; 
        XSSFCell cell;

        Iterator rows = sheet.rowIterator();

        while (rows.hasNext())
        {
            row=(XSSFRow) rows.next();
            Iterator cells = row.cellIterator();
            while (cells.hasNext())
            {
                cell=(XSSFCell) cells.next();

                if (cell.getCellType() == XSSFCell.CELL_TYPE_STRING)
                {
                    System.out.print(cell.getStringCellValue()+" ");
                }
                else if(cell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC)
                {
                    System.out.print(cell.getNumericCellValue()+" ");
                }
                else
                {
                    //U Can Handel Boolean, Formula, Errors
                }
            }
            System.out.println();
        }

    }

@amanteaux 2017-09-30 15:07:45

Another way to read/write Excel files is to use Windmill. It provides a fluent API to process Excel and CSV files.

Import data

try (Stream<Row> rowStream = Windmill.parse(FileSource.of(new FileInputStream("myFile.xlsx")))) {
  rowStream
    // skip the header row that contains the column names
    .skip(1)
    .forEach(row -> {
      System.out.println(
        "row n°" + row.rowIndex()
        + " column 'User login' value : " + row.cell("User login").asString()
        + " column n°3 number value : " + row.cell(2).asDouble().value() // index is zero-based
      );
    });
}

Export data

Windmill
  .export(Arrays.asList(bean1, bean2, bean3))
  .withHeaderMapping(
    new ExportHeaderMapping<Bean>()
      .add("Name", Bean::getName)
      .add("User login", bean -> bean.getUser().getLogin())
  )
  .asExcel()
  .writeTo(new FileOutputStream("Export.xlsx"));

@Rahul.P 2017-02-22 09:53:16

using spring apache poi repo

if (fileName.endsWith(".xls")) {



File myFile = new File("file location" + fileName);
                FileInputStream fis = new FileInputStream(myFile);

                org.apache.poi.ss.usermodel.Workbook workbook = null;
                try {
                    workbook = WorkbookFactory.create(fis);
                } catch (InvalidFormatException e) {

                    e.printStackTrace();
                }


                org.apache.poi.ss.usermodel.Sheet sheet = workbook.getSheetAt(0);


                Iterator<Row> rowIterator = sheet.iterator();


                while (rowIterator.hasNext()) {
                    Row row = rowIterator.next();

                    Iterator<Cell> cellIterator = row.cellIterator();
                    while (cellIterator.hasNext()) {

                        Cell cell = cellIterator.next();
                        switch (cell.getCellType()) {
                        case Cell.CELL_TYPE_STRING:
                            System.out.print(cell.getStringCellValue());
                            break;
                        case Cell.CELL_TYPE_BOOLEAN:
                            System.out.print(cell.getBooleanCellValue());
                            break;
                        case Cell.CELL_TYPE_NUMERIC:
                            System.out.print(cell.getNumericCellValue());
                            break;
                        }
                        System.out.print(" - ");
                    }
                    System.out.println();
                }
            }

@Senthil Kumar Natarajan 2016-08-26 06:06:23

If column number are varing you can use this

package com.org.tests;
import org.apache.poi.xssf.usermodel.*;
import java.io.FileInputStream;
import java.io.IOException;

public class ExcelSimpleTest 
{   
    String path;
    public FileInputStream fis = null;
    private XSSFWorkbook workbook = null;
    private XSSFSheet sheet = null;
    private XSSFRow row   =null;
    private XSSFCell cell = null;

    public ExcelSimpleTest() throws IOException
    {
        path = System.getProperty("user.dir")+"\\resources\\Book1.xlsx";
        fis = new FileInputStream(path); 
        workbook = new XSSFWorkbook(fis);
        sheet = workbook.getSheetAt(0);
    }
    public void ExelWorks()
    {
        int index = workbook.getSheetIndex("Sheet1");
        sheet = workbook.getSheetAt(index);
        int rownumber=sheet.getLastRowNum()+1;  

        for (int i=1; i<rownumber; i++ )
        {
            row = sheet.getRow(i);
            int colnumber = row.getLastCellNum();
            for (int j=0; j<colnumber; j++ )
            {
                cell = row.getCell(j);
                System.out.println(cell.getStringCellValue());
            }
        }
    }   
    public static void main(String[] args) throws IOException 
    {
        ExcelSimpleTest excelwork = new ExcelSimpleTest();
        excelwork.ExelWorks();
    }
}

The corresponding mavendependency can be found here

@Roel 2017-08-21 15:18:51

varing, varing over the baring

@Prashant Tiwari 2013-08-02 09:15:54

First add all these jar files in your project class path:

  1. poi-scratchpad-3.7-20101029
  2. poi-3.2-FINAL-20081019
  3. poi-3.7-20101029
  4. poi-examples-3.7-20101029
  5. poi-ooxml-3.7-20101029
  6. poi-ooxml-schemas-3.7-20101029
  7. xmlbeans-2.3.0
  8. dom4j-1.6.1

Code for writing in a excel file:

public static void main(String[] args) {
    //Blank workbook
    XSSFWorkbook workbook = new XSSFWorkbook();

    //Create a blank sheet
    XSSFSheet sheet = workbook.createSheet("Employee Data");

    //This data needs to be written (Object[])
    Map<String, Object[]> data = new TreeMap<String, Object[]>();
    data.put("1", new Object[]{"ID", "NAME", "LASTNAME"});
    data.put("2", new Object[]{1, "Amit", "Shukla"});
    data.put("3", new Object[]{2, "Lokesh", "Gupta"});
    data.put("4", new Object[]{3, "John", "Adwards"});
    data.put("5", new Object[]{4, "Brian", "Schultz"});

    //Iterate over data and write to sheet
    Set<String> keyset = data.keySet();

    int rownum = 0;
    for (String key : keyset) 
    {
        //create a row of excelsheet
        Row row = sheet.createRow(rownum++);

        //get object array of prerticuler key
        Object[] objArr = data.get(key);

        int cellnum = 0;

        for (Object obj : objArr) 
        {
            Cell cell = row.createCell(cellnum++);
            if (obj instanceof String) 
            {
                cell.setCellValue((String) obj);
            }
            else if (obj instanceof Integer) 
            {
                cell.setCellValue((Integer) obj);
            }
        }
    }
    try 
    {
        //Write the workbook in file system
        FileOutputStream out = new FileOutputStream(new File("C:\\Documents and Settings\\admin\\Desktop\\imp data\\howtodoinjava_demo.xlsx"));
        workbook.write(out);
        out.close();
        System.out.println("howtodoinjava_demo.xlsx written successfully on disk.");
    } 
    catch (Exception e)
    {
        e.printStackTrace();
    }
}

Code for reading from excel file

/*
* To change this template, choose Tools | Templates
* and open the template in the editor.
*/

public static void main(String[] args) {
    try {
        FileInputStream file = new FileInputStream(new File("C:\\Documents and Settings\\admin\\Desktop\\imp data\\howtodoinjava_demo.xlsx"));

        //Create Workbook instance holding reference to .xlsx file
        XSSFWorkbook workbook = new XSSFWorkbook(file);

        //Get first/desired sheet from the workbook
        XSSFSheet sheet = workbook.getSheetAt(0);

        //Iterate through each rows one by one
        Iterator<Row> rowIterator = sheet.iterator();
        while (rowIterator.hasNext())
        {
            Row row = rowIterator.next();
            //For each row, iterate through all the columns
            Iterator<Cell> cellIterator = row.cellIterator();

            while (cellIterator.hasNext()) 
            {
                Cell cell = cellIterator.next();
                //Check the cell type and format accordingly
                switch (cell.getCellType()) 
                {
                    case Cell.CELL_TYPE_NUMERIC:
                        System.out.print(cell.getNumericCellValue() + "\t");
                        break;
                    case Cell.CELL_TYPE_STRING:
                        System.out.print(cell.getStringCellValue() + "\t");
                        break;
                }
            }
            System.out.println("");
        }
        file.close();
    } catch (Exception e) {
        e.printStackTrace();
    }
}

@Toumi 2014-04-11 10:24:35

String path="C:\\Book2.xlsx";
try {

        File f = new File( path );
        Workbook wb = WorkbookFactory.create(f);
        Sheet mySheet = wb.getSheetAt(0);
        Iterator<Row> rowIter = mySheet.rowIterator();
        for ( Iterator<Row> rowIterator = mySheet.rowIterator() ;rowIterator.hasNext(); )
        {
            for (  Iterator<Cell> cellIterator = ((Row)rowIterator.next()).cellIterator() ; cellIterator.hasNext() ;  ) 
            {
                System.out.println ( ( (Cell)cellIterator.next() ).toString() );
            }
            System.out.println( " **************************************************************** ");
        }
    } catch ( Exception e )
    {
        System.out.println( "exception" );
        e.printStackTrace();
    }

and make sure to have added the jars poi and poi-ooxml (org.apache.poi) to your project

@Nahuel Illescas 2015-10-04 20:16:02

I edited the most voted one a little cuz it didn't count blanks columns or rows well not totally, so here is my code i tested it and now can get any cell in any part of an excel file. also now u can have blanks columns between filled column and it will read them

  try {
POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(Dir));
HSSFWorkbook wb = new HSSFWorkbook(fs);
HSSFSheet sheet = wb.getSheetAt(0);
HSSFRow row;
HSSFCell cell;

int rows; // No of rows
rows = sheet.getPhysicalNumberOfRows();

int cols = 0; // No of columns
int tmp = 0;
int cblacks=0;

// This trick ensures that we get the data properly even if it doesn't start from first few rows
for(int i = 0; i <= 10 || i <= rows; i++) {
    row = sheet.getRow(i);
    if(row != null) {
        tmp = sheet.getRow(i).getPhysicalNumberOfCells();
        if(tmp >= cols) cols = tmp;else{rows++;cblacks++;}
    }

    cols++;
}
cols=cols+cblacks;
for(int r = 0; r < rows; r++) {
    row = sheet.getRow(r);
    if(row != null) {
        for(int c = 0; c < cols; c++) {
            cell = row.getCell(c);
            if(cell != null) {
                System.out.print(cell+"\n");//Your Code here
            }
        }
    }
}} catch(Exception ioe) {
ioe.printStackTrace();}

@satender 2015-05-06 14:58:15

Sure , you will find the code below useful and easy to read and write. This is a util class which you can use in your main method and then you are good to use all methods below.

     public class ExcelUtils {
     private static XSSFSheet ExcelWSheet;
     private static XSSFWorkbook ExcelWBook;
     private static XSSFCell Cell;
     private static XSSFRow Row;
     File fileName = new File("C:\\Users\\satekuma\\Pro\\Fund.xlsx");
     public void setExcelFile(File Path, String SheetName) throws Exception                

    try {
        FileInputStream ExcelFile = new FileInputStream(Path);
        ExcelWBook = new XSSFWorkbook(ExcelFile);
        ExcelWSheet = ExcelWBook.getSheet(SheetName);
    } catch (Exception e) {
        throw (e);
    }

}


      public static String getCellData(int RowNum, int ColNum) throws Exception {

    try {
        Cell = ExcelWSheet.getRow(RowNum).getCell(ColNum);
        String CellData = Cell.getStringCellValue();
        return CellData;
    } catch (Exception e) {

        return "";

    }

}
public static void setCellData(String Result, int RowNum, int ColNum, File Path) throws Exception {

    try {
        Row = ExcelWSheet.createRow(RowNum - 1);
        Cell = Row.createCell(ColNum - 1);
        Cell.setCellValue(Result);
        FileOutputStream fileOut = new FileOutputStream(Path);
        ExcelWBook.write(fileOut);
        fileOut.flush();
        fileOut.close();
    } catch (Exception e) {

        throw (e);

    }

}

}

@Rajesh Chaudhary 2014-11-28 11:41:01

Please use Apache POI libs and try this.

public class TakingDataFromExcel {
   public static void main(String[] args) {
    try
    {
        FileInputStream x = new FileInputStream(new File("/Users/rajesh/Documents/rajesh.xls"));

        //Create Workbook instance holding reference to .xlsx file
        Workbook workbook = new HSSFWorkbook(x);

        //Get first/desired sheet from the workbook
        Sheet sheet = workbook.getSheetAt(0);

        //Iterate through each rows one by one
        for (Iterator<Row> iterator = sheet.iterator(); iterator.hasNext();) {
            Row row = (Row) iterator.next();
            for (Iterator<Cell> iterator2 = row.iterator(); iterator2
                    .hasNext();) {
                Cell cell = (Cell) iterator2.next();
                System.out.println(cell.getStringCellValue());              
            }               
        }         
        x.close();
    }
    catch (Exception e)
    {
        e.printStackTrace();
    }
   }
}

@Doug Hauf 2014-02-26 22:58:35

This will write a JTable to a tab separated file that can be easily imported into Excel. This works.

If you save an Excel worksheet as an XML document you could also build the XML file for EXCEL with code. I have done this with word so you do not have to use third-party packages.

This could code have the JTable taken out and then just write a tab separated to any text file and then import into Excel. I hope this helps.

Code:

import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import javax.swing.JTable;
import javax.swing.table.TableModel;

public class excel {
    String columnNames[] = { "Column 1", "Column 2", "Column 3" };

    // Create some data
    String dataValues[][] =
    {
        { "12", "234", "67" },
        { "-123", "43", "853" },
        { "93", "89.2", "109" },
        { "279", "9033", "3092" }
    };

    JTable table;

    excel() {
        table = new JTable( dataValues, columnNames );
    }


    public void toExcel(JTable table, File file){
        try{
            TableModel model = table.getModel();
            FileWriter excel = new FileWriter(file);

            for(int i = 0; i < model.getColumnCount(); i++){
                excel.write(model.getColumnName(i) + "\t");
            }

            excel.write("\n");

            for(int i=0; i< model.getRowCount(); i++) {
                for(int j=0; j < model.getColumnCount(); j++) {
                    excel.write(model.getValueAt(i,j).toString()+"\t");
                }
                excel.write("\n");
            }

            excel.close();

        }catch(IOException e){ System.out.println(e); }
    }

    public static void main(String[] o) {
        excel cv = new excel();
        cv.toExcel(cv.table,new File("C:\\Users\\itpr13266\\Desktop\\cs.tbv"));
    }
}

@MGM 2015-07-03 12:58:18

(You are simply store the String arrays in the JTable, and get back the Strings for wrinting a tab-separated "CSV" file. JTable is superfluous in this algorithm...)

@Satish Reddy 2013-06-18 01:47:24

For reading data from .xlsx workbooks we need to use XSSFworkbook classes.

XSSFWorkbook xlsxBook = new XSSFWorkbook(fis);

XSSFSheet sheet = xlsxBook.getSheetAt(0); etc.

We need to use Apache-poi 3.9 @ http://poi.apache.org/

For detailed info with example visit : http://java-recent.blogspot.in

@NixonUposseen 2018-11-09 08:31:48

With Apache POI you need HSSF module for XLS and XSSF module for XLSX format, with GemBox.Spreadsheet for Java both formats are unified into same module and are represented with same ExcelFile type.

@javashlook 2009-10-04 13:06:16

You can also consider JExcelApi. I find it better designed than POI. There's a tutorial here.

@Vikram 2015-01-07 22:11:11

I tried to visit the tutorial page. It seems to be removed.

@scravy 2015-03-13 14:12:32

no it is there and it works

@MGM 2015-07-03 12:51:33

JExcelApi is for old (up to Excel 2003) binary .xls files. Apache POI-HSSF is addressing the same old .xls, while Apache POI-XSSF works with the new (Excel 2007-) .xlsx

@duffymo 2009-10-04 12:50:09

.csv or POI will certainly do it, but you should be aware of Andy Khan's JExcel. I think it's by far the best Java library for working with Excel there is.

@Adriaan Koster 2009-10-04 12:02:02

If you need to do anything more with office documents in Java, go for POI as mentioned.

For simple reading/writing an excel document like you requested, you can use the CSV format (also as mentioned):

import java.io.BufferedReader;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.IOException;
import java.io.PrintWriter;
import java.util.Scanner;

public class CsvWriter {
 public static void main(String args[]) throws IOException {

  String fileName = "test.xls";

  PrintWriter out = new PrintWriter(new FileWriter(fileName));
  out.println("a,b,c,d");
  out.println("e,f,g,h");
  out.println("i,j,k,l");
  out.close();

  BufferedReader in = new BufferedReader(new FileReader(fileName));
  String line = null;
  while ((line = in.readLine()) != null) {

   Scanner scanner = new Scanner(line);
   String sep = "";
   while (scanner.hasNext()) {
    System.out.println(sep + scanner.next());
    sep = ",";
   }
  }
  in.close();
 }
}

@John La Rooy 2009-10-04 11:01:49

A simple CSV file should suffice

@Brian Agnew 2009-10-04 11:22:42

Beware that you will have to escape commas within the strings you write to the CSV.

@Yuval F 2009-10-04 11:31:52

Indeed CSV is enough. If your fields have no commas, it is probably easiest just to print the fields and commas. Otherwise, you may want to use commons.apache.org/sandbox/csv

@Brian Agnew 2009-10-04 11:35:42

I'm not sure a CSV is enough, since the question specifically says 'read' and 'write' an Excel file. He might be able to get away with CSV, but that's not what he's asking.

@Roalt 2010-09-09 13:30:39

Some international versions of Excel use semicolons instead of commas as separators. This also increases complexity

@Thorbjørn Ravn Andersen 2014-10-08 08:51:15

If the output contains dates CSV may be too simple.

@Ben 2018-04-02 08:05:51

Question clearly states spreadsheet, and not csv, which is a huge difference.

Related Questions

Sponsored Content

60 Answered Questions

[SOLVED] How do I read / convert an InputStream into a String in Java?

64 Answered Questions

[SOLVED] How do I generate random integers within a specific range in Java?

  • 2008-12-12 18:20:57
  • user42155
  • 3788430 View
  • 3206 Score
  • 64 Answer
  • Tags:   java random integer

45 Answered Questions

[SOLVED] How do I convert a String to an int in Java?

38 Answered Questions

[SOLVED] How do I efficiently iterate over each entry in a Java Map?

30 Answered Questions

[SOLVED] How do I create a file and write to it in Java?

  • 2010-05-21 19:58:55
  • Drew Johnson
  • 2669743 View
  • 1269 Score
  • 30 Answer
  • Tags:   java file-io

42 Answered Questions

[SOLVED] How to create Excel (.XLS and .XLSX) file in C# without installing Ms Office?

  • 2008-09-29 22:30:28
  • mistrmark
  • 997859 View
  • 1732 Score
  • 42 Answer
  • Tags:   c# .net excel file-io

30 Answered Questions

[SOLVED] How to avoid Java code in JSP files?

  • 2010-07-05 07:24:06
  • former
  • 267742 View
  • 1608 Score
  • 30 Answer
  • Tags:   java jsp scriptlet

4 Answered Questions

1 Answered Questions

[SOLVED] Read Excel Files and Only Use Specific Files

Sponsored Content