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

Solution 1

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*/
            }
    }

Solution 2

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