Creating and Editing Excel Documents (.csv) with Java

For this project I'm working on, I want to take multiple excel sheets and then merge them into one, manipulating the data as I please to make everything a little more reada开发者_Go百科ble.

What would be the best way to open files, read their contents, store that content, create a new file (.csv), then paste the information in the organization of my choosing?

I definitely need to stick to java, as this will be part of a pre-existing automated process and I don't want to have to change everything to another language.

Is there a useful package out there that I should know about?

Many thanks

Justian

I think any serious work in Excel should consider Joel's solution of letting Office do it for you on a Windows machine you call remotely if necessary. If, however, your needs are simple enough or you really need a pure Java solution, Apache's POI library does a good enough job.

As far as I know, csv is not excel-specific, but rather just a "comma-separated values"-file.

So this might help you.

Writing CSV files is usually very simple, for obvious reasons. You can write your own helper class to do it. The caveat is to ensure that you do not have your delimeter in any of the outputs.

Reading CSV is trickier. There isn't a standard library like there is in Python (a much better language, IMHO, for doing CSV processing), but if you search for it there are a lot of decent free implementations around.

The biggest question is the internal representation in your program: Depending on the size of your inputs and outputs, keeping everything in memory may be out of the question. Can you do everything in one pass? (I mean, read some, write some, etc.)

You may also want to use sparse representations rather than just represent all the spreadsheets in an array.

Maybe you should try this one: Jxcell,it is a java spreadsheet component,and can read/write/edit all xls/xlsx/csv files.

Try this code

    import java.util.*;
    import java.util.Map.Entry;
    import java.util.concurrent.TimeoutException;
    import java.util.logging.Logger;
    import java.util.logging.Level;
    import java.util.logging.Logger;
    import org.apache.poi.ss.usermodel.*;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.apache.poi.xssf.usermodel.XSSFWorkbookFactory;

    public class App {

        public void convertExcelToCSV(Sheet sheet, String sheetName) {
            StringBuffer data = new StringBuffer();
            try {
                FileOutputStream fos = new FileOutputStream("C:\\Users\\" + sheetName + ".csv");
                Cell cell;
                Row row;

                Iterator<Row> rowIterator = sheet.iterator();
                while (rowIterator.hasNext()) {
                    row = rowIterator.next();
                    Iterator<Cell> cellIterator = row.cellIterator();
                    while (cellIterator.hasNext()) {
                        cell = cellIterator.next();

                        CellType type = cell.getCellTypeEnum();
                        if (type == CellType.BOOLEAN) {
                            data.append(cell.getBooleanCellValue() + ",");
                        } else if (type == CellType.NUMERIC) {
                            data.append(cell.getNumericCellValue() + ",");
                        } else if (type == CellType.STRING) {
                            data.append(cell.getStringCellValue() + ",");
                        } else if (type == CellType.BLANK) {
                            data.append("" + ",");
                        } else {
                            data.append(cell + ",");
                        }
                    }
                    data.append('\n');
                }
                fos.write(data.toString().getBytes());
                fos.close();
            }
            catch (FileNotFoundException e)
            {
                e.printStackTrace();
            }
            catch (IOException e)
            {
                e.printStackTrace();
            }
        }

        public static void main(String [] args)
        {
            App app = new App();
            String path =  "C:\\Users\\myFile.xlsx";
            InputStream inp = null;
            try {
                inp = new FileInputStream(path);
                Workbook wb = WorkbookFactory.create(inp);

                for(int i=0;i<wb.getNumberOfSheets();i++) {
                    System.out.println(wb.getSheetAt(i).getSheetName());
                    app.convertExcelToCSV(wb.getSheetAt(i),wb.getSheetAt(i).getSheetName());
                }
            } catch (Exception ex) {
                System.out.println(ex.getMessage());
            } 
            finally {
                try {
                    inp.close();
                } catch (Exception ex) {
                    System.out.println(ex.getMessage());
                }
            }
        }
    }