2

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

2

3 Answers 3

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.

Sign up to request clarification or add additional context in comments.

Comments

0

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;
}

Comments

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.

1 Comment

Please do not only post links as answers, but rather quote the most important parts so that it is safe against link rot.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.