How to write csv file on the basis of Excel using java?

Question

I have spreadsheet with certain data. I would like to programmatically dump this to CSV file. How can I do this using java?

probably a copy of http://stackoverflow.com/questions/6451765/how-to-export-and-save-tables-to-csv-from-access-database-in-java/6451852#6451852 — Andreas, Jun 29 '11 at 12:08

score 0 · Answer 1 · answered Jul 31 '13 at 17:59

0

http://techblog.ralph-schuster.eu/csv-utility-package-for-java/ resolves your problem out-of-the-box. There is a CSVUtils class which does the copying for you.

answered Jul 31 '13 at 17:59

Frank

1

Please do not only post links as answers, but rather quote the most important parts so that it is safe against link rot. – pascalhein Jul 31 '13 at 18:17

score 0 · Answer 2 · answered Jun 29 '11 at 12:09

0

You could use one of the APIs that allow you to read Excel files. Personally, I like JExcelApi best. It is really easy to learn and use.

answered Jun 29 '11 at 12:09

nfechner

17,295
7
45
64

score 0 · Accepted Answer · answered Jun 30 '11 at 15:54

Here I am using OpenCsv API and Apache-POI.TO read excel file and writing CSV File.

public void ExcelTOCSV(){
    CSVWriter writer=null;
    int columnLength =0;
    int i =0;String[] rowData;
    HSSFCell cell = null;HSSFSheet sheet=null;
    int SheetIndex=0;
    try
    {
        InputStream  resourceAsStream =ClassLoader.getSystemResourceAsStream(filePath);
        HSSFWorkbook workbook = new HSSFWorkbook(resourceAsStream);
        sheet = workbook.getSheetAt(sheetIndex);

        //Here i am create my custom method to get column row on the basis of Sheet index. 
        columnLength = getNumberOfColumns(sheetIndex);
        writer = new CSVWriter(new FileWriter("c:\\yourfile.csv"));
        Iterator<Row> rows = sheet.rowIterator();
        while (rows.hasNext())
        {
                rowData=new String[columnLength];
                HSSFRow row = (HSSFRow) rows.next();
                Iterator<Cell> cells = row.cellIterator();
                i = 0;
                        while (cells.hasNext())
                        {
                            cell = (HSSFCell) cells.next();
                            //Must do this, you need to get value based on the cell type
                                                switch (cell.getCellType())
                                                {
                                                    case HSSFCell.CELL_TYPE_NUMERIC:
                                                       rowData[i]=Double.toString(cell.getNumericCellValue());
                                                    break;

                                                    case HSSFCell.CELL_TYPE_STRING:
                                                         rowData[i]=cell.getStringCellValue();
                                                       break;

                                                    case HSSFCell.CELL_TYPE_BLANK:

                                                       break;

                                                    default: break;
                                                }//end of switch
                                   i++;
                                }//cell while loop
                 writer.writeNext(rowData);//writing data into file
            }//Row While loop
             writer.close();

    } 
    catch (IOException ex)
    {
        Logger.getLogger(CreateCSVFile.class.getName()).log(Level.SEVERE, null, ex);
    }
}

Method To getNumberofColumns:

 public int getNumberOfColumns(int SheetIndex)
{
    int NO_OF_Column=0;HSSFCell cell = null;
    HSSFSheet sheet=null;
            try {
                loadFile();  //load give Excel
                if(validateIndex(SheetIndex))
                {
                    sheet  = workbook.getSheetAt(SheetIndex);
                    Iterator rowIter = sheet.rowIterator();
                    HSSFRow firstRow = (HSSFRow) rowIter.next();
                    Iterator cellIter = firstRow.cellIterator();
                    while(cellIter.hasNext())
                    {
                          cell = (HSSFCell) cellIter.next();
                          NO_OF_Column++;
                    }
                }
                else
                    throw new InvalidSheetIndexException("Invalid sheet index.");
            } catch (Exception ex) {
                logger.error(ex);

            }

    return NO_OF_Column;
}

How to write csv file on the basis of Excel using java?

3 Answers3