java converting .xls to csv

Question

I have converted to .xls file to a csv file using the Apache POI library. I iterate each row and cell, put a comma, and append to the buffered reader. The cell types numeric and string are converted perfectly. If a blank cell comes I put a comma, but blank values are not detected by the code. How to do it? Please help me out.

import java.io.*;
import java.util.Iterator;
import java.text.DateFormat;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.text.DecimalFormat;
import java.text.NumberFormat;
import java.math.BigDecimal;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
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;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.DateUtil;
class convert {

static void convertToXls(File inputFile, File outputFile)
{
StringBuffer cellDData = new StringBuffer();
String cellDDataString=null;
try
{
        FileOutputStream fos = new FileOutputStream(outputFile);

        HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(inputFile));
        HSSFSheet sheet = workbook.getSheetAt(0);
        Cell cell=null;
        Row row;
        int previousCell;
        int currentCell;
        Iterator<Row> rowIterator = sheet.iterator();
        while (rowIterator.hasNext())
        {
        previousCell = -1;
        currentCell = 0;
        row = rowIterator.next();
         System.out.println("ROW:-->");
        Iterator<Cell> cellIterator = row.cellIterator();
        while (cellIterator.hasNext())
{
          // System.out.println("true" +cellIterator.hasNext());
        cell = cellIterator.next();
        currentCell = cell.getColumnIndex();


        System.out.println("CELL:-->" +cell.toString());
        try{
        switch (cell.getCellType())
        {

        case Cell.CELL_TYPE_BOOLEAN:
                cellDData.append(cell.getBooleanCellValue() + ",");
                System.out.println("boo"+ cell.getBooleanCellValue());
                break;

        case Cell.CELL_TYPE_NUMERIC:
                         if (DateUtil.isCellDateFormatted(cell))
                        {

                  //      System.out.println(cell.getDateCellValue());
                        SimpleDateFormat dateFormat = new SimpleDateFormat(
                            "dd/MM/yyyy");
                         String  strCellValue = dateFormat.format(cell.getDateCellValue());
                //      System.out.println("date:"+strCellValue);
                        cellDData.append(strCellValue +",");
                    }
                       else {
                        System.out.println(cell.getNumericCellValue());
                        Double value = cell.getNumericCellValue();
                    Long longValue = value.longValue();
                    String strCellValue1 = new String(longValue.toString());
                //      System.out.println("number:"+strCellValue1);
                         cellDData.append(strCellValue1 +",");
                    }
        //      cellDData.append(cell.getNumericCellValue() + ",");
                //String  i=(new java.text.DecimalFormat("0").format( cell.getNumericCellValue()+"," ));
                //System.out.println("number"+cell.getNumericCellValue());
                break;

        case Cell.CELL_TYPE_STRING:
   String out=cell.getRichStringCellValue().getString();
                cellDData.append(cell.getRichStringCellValue().getString() + ",");
                //System.out.println("string"+cell.getStringCellValue());
                break;

        case Cell.CELL_TYPE_BLANK:
                cellDData.append("" + "THIS IS BLANK");
                System.out.print("THIS IS BLANK");
                break;

        default:
                break;
        }}
catch (NullPointerException e) {
                    //do something clever with the exception
                        System.out.println("nullException"+e.getMessage());
                }

}
        int len=cellDData.length() - 1;
//      System.out.println("length:"+len);
//      System.out.println("length1:"+cellDData.length());
       cellDData.replace(cellDData.length() - 1, cellDData.length() , "");
        cellDData.append("\n");
        }
        //cellDData.append("\n");


//String out=cellDData.toString();
//System.out.println("res"+out);

//String o = out.substring(0, out.lastIndexOf(","));
//System.out.println("final"+o);
fos.write(cellDData.toString().getBytes());
//fos.write(cellDDataString.getBytes());
fos.close();

}
catch (FileNotFoundException e)
{
    System.err.println("Exception" + e.getMessage());
}
catch (IOException e)
{
        System.err.println("Exception" + e.getMessage());
}
}

public static void main(String[] args) throws IOException
{
        File inputFile = new File("/bwdev/kadfeb/xls/Accredo_Kadmon_Monthly_02282014.xls");
        File outputFile = new File("output1.csv");
        convertToXls(inputFile, outputFile);
}

Is there a reason why you're doing this yourself, rather than following the Example XLS to CSV converter that Apache POI provides? — Gagravarr
– Gagravarr, Commented Apr 22, 2014 at 12:51

Reji · Accepted Answer · 2014-04-22 14:50:39Z

5

I assume that the HSSFWorkbook by default skips the blank cells or missing cells. Try setting the MissingCellPolicy for the HSSFWorkbook object.

The possible values to be set for MissingCellPolicy can be found here

Use row index and col index instead of Iterator.

HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(inputFile));
workbook.setMissingCellPolicy(Row.CREATE_NULL_AS_BLANK);

HSSFSheet sheet = workbook.getSheetAt(0);
for(int rowIndex = sheet.getFirstRowNum(); rowIndex < sheet.getLastRowNum(); rowIndex++)
{
       Cell cell=null;
       Row row = null;

       previousCell = -1;
       currentCell = 0;
       row = sheet.getRow(rowIndex);
       for(int colIndex=row.getFirstCellNum(); colIndex < row.getLastCellNum(); colIndex++)
            {
                 cell = row.getCell(colIndex);
                 currentCell = cell.getColumnIndex();

                 /* Cell processing starts here*/
            }
    }

edited Apr 22, 2014 at 14:50

answered Apr 21, 2014 at 17:01

Reji

3,5363 gold badges24 silver badges25 bronze badges

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

4 Comments

sakthi Over a year ago

How to use MissingCellPolicy can you give example

Gagravarr Over a year ago

I know it's a shocking concept, but did you consider checking the Apache POI documentation on iterating cells? It features an example of the very same....

Reji Over a year ago

@Gagravarr The code that I placed there was not a complete one. I was only trying to convey the use of MissingPolicy and didn't verify the validity of the test of the code. I have updated my answer. The code that I pasted here is from sakthi's code itself.

Reji Over a year ago

@sakthi if the answer worked for you, do accept it please. Else share the issue faced.

David Raj · Accepted Answer · 2014-12-19 04:55:21Z

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.util.Iterator;

import org.apache.commons.io.FilenameUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class XlsxtoCSV {

    static void xlsx(File inputFile, File outputFile) {
        // For storing data into CSV files
        StringBuffer data = new StringBuffer();

        try {
            FileOutputStream fos = new FileOutputStream(outputFile);
            // Get the workbook object for XLSX file
            FileInputStream fis = new FileInputStream(inputFile);
            Workbook workbook = null;

            String ext = FilenameUtils.getExtension(inputFile.toString());

            if (ext.equalsIgnoreCase("xlsx")) {
                workbook = new XSSFWorkbook(fis);
            } else if (ext.equalsIgnoreCase("xls")) {
                workbook = new HSSFWorkbook(fis);
            }

            // Get first sheet from the workbook

            int numberOfSheets = workbook.getNumberOfSheets();
            Row row;
            Cell cell;
            // Iterate through each rows from first sheet

            for (int i = 0; i < numberOfSheets; i++) {
                Sheet sheet = workbook.getSheetAt(0);
                Iterator<Row> rowIterator = sheet.iterator();

                while (rowIterator.hasNext()) {
                    row = rowIterator.next();
                    // For each row, iterate through each columns
                    Iterator<Cell> cellIterator = row.cellIterator();
                    while (cellIterator.hasNext()) {

                        cell = cellIterator.next();

                        switch (cell.getCellType()) {
                        case Cell.CELL_TYPE_BOOLEAN:
                            data.append(cell.getBooleanCellValue() + ",");

                            break;
                        case Cell.CELL_TYPE_NUMERIC:
                            data.append(cell.getNumericCellValue() + ",");

                            break;
                        case Cell.CELL_TYPE_STRING:
                            data.append(cell.getStringCellValue() + ",");
                            break;

                        case Cell.CELL_TYPE_BLANK:
                            data.append("" + ",");
                            break;
                        default:
                            data.append(cell + ",");

                        }
                    }
                    data.append('\n'); // appending new line after each row
                }

            }
            fos.write(data.toString().getBytes());
            fos.close();

        } catch (Exception ioe) {
            ioe.printStackTrace();
        }
    }

    // testing the application

    public static void main(String[] args) {
        // int i=0;
        // reading file from desktop
        File inputFile = new File(".//src//test//resources//yourExcel.xls"); //provide your path
        // writing excel data to csv
        File outputFile = new File(".//src//test//resources//yourCSV.csv");  //provide your path
        xlsx(inputFile, outputFile);
        System.out.println("Conversion of " + inputFile + " to flat file: "
                + outputFile + " is completed");
    }
}

This faces exactly the same problem that the OP's code does, which is that it ignores blank/missing cells, you need to use @Reji's code to properly handle them

lanes · Accepted Answer · 2014-08-12 09:19:23Z

0

I replaced

while (cellIterator.hasNext()) {
   Cell cell = cellIterator.next();

with

for (int cn = 0; cn < row.getLastCellNum(); cn++) {
   Cell cell = row.getCell(cn, Row.CREATE_NULL_AS_BLANK);

answered Aug 12, 2014 at 9:19

lanes

1,9572 gold badges19 silver badges20 bronze badges

Comments

anurag · Accepted Answer · 2019-02-07 12:10:16Z

0

Simple way to convert xls/xlsx into csv by using apache POI.

public class XLSXToCSVConverter {

    public InputStream convertxlstoCSV(InputStream inputStream) throws IOException, InvalidFormatException {

        Workbook wb = WorkbookFactory.create(inputStream);

        return  csvConverter(wb.getSheetAt(0));
    }

    private InputStream csvConverter(Sheet sheet) {
        Row row = null;
        String str = new String();
        for (int i = 0; i < sheet.getLastRowNum()+1; i++) {
            row = sheet.getRow(i);
            String rowString = new String();
            for (int j = 0; j < 3; j++) {
                if(row.getCell(j)==null) {
                    rowString = rowString + Utility.BLANK_SPACE + Utility.COMMA;
                }
                else {
                    rowString = rowString + row.getCell(j)+ Utility.COMMA;
                }
            }
            str = str + rowString.substring(0,rowString.length()-1)+ Utility.NEXT_LINE_OPERATOR;
        }
        System.out.println(str);
        return new ByteArrayInputStream(str.getBytes(StandardCharsets.UTF_8));
    }
}

answered Feb 7, 2019 at 12:10

anurag

653 bronze badges

1 Comment

saran3h Over a year ago

Where is this Utility class? I can't find it anywhere.

Zhenya · Accepted Answer · 2022-06-09 07:28:35Z

I suggest this one. It is very similiar to previos ones, but have for loop instead of iterator, whick gives you oportunity not to skip empty (deleted) cells

    public InputStream fromExcelToCsv(Sheet sheet, String delimiter) throws IOException {
        // For storing data into CSV files
        StringBuilder data = new StringBuilder();

        ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
        Row row;
        // Iterate through each rows from first sheet

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

            for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) {
                Cell cell = row.getCell(i);
                if (cell == null) {
                    //cell is deletd => add empty
                    data.append(delimiter);
                } else {
                    switch (cell.getCellTypeEnum()) {
                        case BOOLEAN:
                            data.append(cell.getBooleanCellValue()).append(delimiter);

                            break;
                        case NUMERIC:
                            double numericCellValue = cell.getNumericCellValue();
                            //All numbers in excel is double. example: 1.0
                            //If there is no 
fractional part than delete it
                            if (numericCellValue % 1 == 0) {
                                data.append((int) numericCellValue).append(delimiter);
                            } else {
                                data.append(numericCellValue).append(delimiter);
                            }

                            break;
                        case STRING:
                            data.append(cell.getStringCellValue()).append(delimiter);
                            break;

                        case BLANK:
                            data.append(delimiter);
                            break;
                        default:
                            data.append(cell).append(delimiter);

                    }
                }
            }

            data.append('\n'); // appending new line after each row
        }

        byteArrayOutputStream.write(data.toString().getBytes());
        byteArrayOutputStream.close();

        return new ByteArrayInputStream(byteArrayOutputStream.toByteArray());
    }

Collectives™ on Stack Overflow

java converting .xls to csv

5 Answers 5

4 Comments

1 Comment

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

4 Comments

1 Comment

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related