Convert csv to xls/xlsx using Apache poi?

Question

I need to convert csv to xls/xlsx in my project? How can i do that? Can anyone post me some examples? I want to do it with Apache poi. I also need to create a cell from java side.

score 23 · Accepted Answer · answered Aug 06 '13 at 14:37

23

You can try following method to create xlsx file using apache-poi.

public static void csvToXLSX() {
    try {
        String csvFileAddress = "test.csv"; //csv file address
        String xlsxFileAddress = "test.xlsx"; //xlsx file address
        XSSFWorkbook workBook = new XSSFWorkbook();
        XSSFSheet sheet = workBook.createSheet("sheet1");
        String currentLine=null;
        int RowNum=0;
        BufferedReader br = new BufferedReader(new FileReader(csvFileAddress));
        while ((currentLine = br.readLine()) != null) {
            String str[] = currentLine.split(",");
            RowNum++;
            XSSFRow currentRow=sheet.createRow(RowNum);
            for(int i=0;i<str.length;i++){
                currentRow.createCell(i).setCellValue(str[i]);
            }
        }

        FileOutputStream fileOutputStream =  new FileOutputStream(xlsxFileAddress);
        workBook.write(fileOutputStream);
        fileOutputStream.close();
        System.out.println("Done");
    } catch (Exception ex) {
        System.out.println(ex.getMessage()+"Exception in try");
    }
}

answered Aug 06 '13 at 14:37

Sankumarsingh

9,889
11
50
74

thanks but my csv is | delimited. when i replace the comma with pipi i'm getting one character per cell. what should i do?? – v0ld3m0rt Aug 07 '13 at 06:27
Please confirm you have used simply "|" or "\\|". – Sankumarsingh Aug 07 '13 at 15:02
2

got it,, i was using "|" only which meant logical or, i should have used "\\|". thanks – v0ld3m0rt Aug 08 '13 at 04:14
This throws an exception on line: "XSSFWorkbook workBook = new XSSFWorkbook();," saying "NoClassDefFoundError." – Code Doggo Apr 26 '16 at 04:41
You should follow the naming conventions. That means `rowNum` instead of `RowNum`. Also, `String[] str` is preferred rather than `String str[]`, see [JLS 7](http://docs.oracle.com/javase/specs/jls/se7/jls7.pdf). – MC Emperor Sep 16 '16 at 06:08
I got this error "the type org.apache.poi.POIXMLDocument cannot be resolved. It is indirectly referenced from required .class file" I am using apache poi - 3.15 ( ooxml and ooxml-schemes also added in path) – Morez Jan 06 '17 at 19:02
To Dan and Ronnie and others with that problem: add the relevant imports (duh!). And if you don't understand what that means, it is time you read the Java Tutorial. – Stephen C Jun 11 '17 at 05:52
RowNum++ before writing means that you are creating the current row at row 2 rather than row 1 of the excel sheet. rowNum should be incremented after you are done filling the row – gdawgrancid Nov 14 '19 at 18:00
1

Not working when value has "," for example a csv value is "5,200.00" – wherby Sep 24 '20 at 09:41
You are incrementing RowNum too early, resulting in the first row having an index of '1' instead of '0' – beirtipol Jun 11 '21 at 11:24

score 7 · Answer 2 · edited Mar 11 '14 at 05:59

We can use SXSSF Jar in which we can parse a long file as below:

public static void main( String[] args ) {
  try {

    //   String fName = args[ 0 ];

    String csvFileAddress = "C:\\Users\\psingh\\Desktop\\test\\New folder\\GenericDealerReport - version6.txt"; //csv file address
    String xlsxFileAddress = "C:\\Users\\psingh\\Desktop\\trial\\test3.xlsx"; //xlsx file address

    SXSSFWorkbook workBook = new SXSSFWorkbook( 1000 );
    org.apache.poi.ss.usermodel.Sheet sheet = workBook.createSheet( "sheet1" );
    String currentLine = null;
    int RowNum = -1;
    BufferedReader br = new BufferedReader( new FileReader( csvFileAddress ) );
    while ( ( currentLine = br.readLine() ) != null ) {
      String str[] = currentLine.split( "\\|" );
      RowNum++;
      Row currentRow = sheet.createRow( RowNum );
      for ( int i = 0; i < str.length; i++ ) {
        currentRow.createCell( i )
                .setCellValue( str[ i ] );
      }
    }
    DateFormat df = new SimpleDateFormat( "yyyy-mm-dd-HHmmss" );
    Date today = Calendar.getInstance()
                       .getTime();
    String reportDate = df.format( today );
    FileOutputStream fileOutputStream = new FileOutputStream( xlsxFileAddress );
    workBook.write( fileOutputStream );
    fileOutputStream.close();
    //System.out.println( "Done" );
  }
  catch ( Exception ex ) {
    System.out.println( ex.getMessage() + "Exception in try" );
  }
}

Worth to mention you are parsing a | separated CSV (which is not the default). Also, I would suggest creating the readers using Try-with-resources. — Amit Goldstein, May 06 '21 at 07:36

score 5 · Answer 3 · answered Oct 18 '18 at 14:09

public static void convertCsvToXlsx(String xlsLocation, String csvLocation) throws Exception {
    SXSSFWorkbook workbook = new SXSSFWorkbook();
    SXSSFSheet sheet = workbook.createSheet("Sheet");
    AtomicReference<Integer> row = new AtomicReference<>(0);
    Files.readAllLines(Paths.get(csvLocation)).forEach(line -> {
        Row currentRow = sheet.createRow(row.getAndSet(row.get() + 1));
        String[] nextLine = line.split(",");
        Stream.iterate(0, i -> i + 1).limit(nextLine.length).forEach(i -> {
            currentRow.createCell(i).setCellValue(nextLine[i]);
        });
    });
    FileOutputStream fos = new FileOutputStream(new File(xlsLocation));
    workbook.write(fos);
    fos.flush();
}

This is the FASTEST solution and is clearly self documented. In my tests this is 30% faster than the the solution above from Superb Self. — asnyder, Jun 07 '19 at 16:30

score 4 · Answer 4 · answered Oct 20 '16 at 20:49

I have found SXSSFWorkbook really faster then XSSFWorkbook. Here is the modified code:

try {
        String csvFileInput = "inputFile.csv";
        String xlsxFileOutput ="outputFile.xls";

        LOGGER.error(csvFileInput);
        LOGGER.error( xlsxFileOutput);
        SXSSFWorkbook workBook = new SXSSFWorkbook();
        Sheet sheet = workBook.createSheet(transformBean.getOutputFileName());
        String currentLine = null;
        int RowNum = 0;
        BufferedReader br = new BufferedReader(new FileReader(csvFileInput));
        while ((currentLine = br.readLine()) != null) {
            String str[] = currentLine.split(",");
            RowNum++;
            Row currentRow = sheet.createRow(RowNum);
            for (int i = 0; i < str.length; i++) {
                currentRow.createCell(i).setCellValue(str[i]);
            }
        }
        FileOutputStream fileOutputStream = new FileOutputStream(xlsxFileOutput);
        workBook.write(fileOutputStream);
        fileOutputStream.close();
        System.out.println("Done");
    } catch (Exception ex) {
        System.out.println(ex.getMessage() + "Found Exception");
    }

score 0 · Answer 5 · answered Jul 26 '17 at 08:48

if(new File(newFileName).isFile()) return;
    @SuppressWarnings("resource")
    HSSFWorkbook wb = new HSSFWorkbook();
    Row xlsRow;
    Cell xlsCell;
    HSSFSheet sheet = wb.createSheet("sheet1");
    int rowIndex = 0;
    for(CSVRecord record : CSVFormat.EXCEL.parse(new FileReader(fileName))) {
        xlsRow = sheet.createRow(rowIndex);
        for(int i = 0; i < record.size(); i ++){
            xlsCell = xlsRow.createCell(i);
            xlsCell.setCellValue(record.get(i));
        }
        rowIndex ++;
    }
    FileOutputStream out = new FileOutputStream(newFileName);
    wb.write(out);
    out.close();

score 0 · Answer 6 · answered Nov 03 '17 at 12:04

Try this one if you have inputstream public static XSSFWorkbook csvToXLSX(InputStream inputStream) throws IOException { XSSFWorkbook workBook = new XSSFWorkbook();

    try(BufferedReader br = new BufferedReader(new InputStreamReader(inputStream))) {
        Sheet sheet = workBook.createSheet("sheet1");
        String currentLine=null;
        int rowNum=0;

        while ((currentLine = br.readLine()) != null) {
            String[] str = currentLine.split(",");
            rowNum++;
            Row currentRow=sheet.createRow(rowNum);
            for(int i=0;i<str.length;i++){
                currentRow.createCell(i).setCellValue(str[i]);
            }
        }

        log.info("CSV file converted to the workbook");
        return workBook;
    } catch (Exception ex) {
        log.error("Exception while converting csv to xls {}",ex);
    }finally {
        if (Objects.nonNull(workBook)) {
            workBook.close();
        }
    }
    return workBook;
}

Convert csv to xls/xlsx using Apache poi?

6 Answers6

Linked