Java POI: Excel to CSV Conversion Utility

1. First we need a util class

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellValue;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;

public class ExcelUtil
{

    public static Cell getCell(Row row, int i)
    {
        Cell cell = row.getCell(i);
        if (null == cell)
        {
            cell = row.createCell(i);
        }
        return cell;
    }

    public static Row getRow(Sheet sheet, int i)
    {
        Row row = sheet.getRow(i);
        if (null == row)
        {
            row = sheet.createRow(i);
        }
        return row;
    }

    public static String getValue(Cell cell)
    {
        FormulaEvaluator evaluator = cell.getSheet().getWorkbook()
                .getCreationHelper().createFormulaEvaluator();

        CellValue cellValue = evaluator.evaluate(cell);

        switch (cellValue.getCellType())
        {
        case Cell.CELL_TYPE_BLANK:
            return "";
        case Cell.CELL_TYPE_STRING:
            return cellValue.getStringValue();
        case Cell.CELL_TYPE_NUMERIC:
            return Double.valueOf(cellValue.getNumberValue()).toString();
        case Cell.CELL_TYPE_BOOLEAN:
            return Boolean.valueOf(cellValue.getBooleanValue()).toString();
        case Cell.CELL_TYPE_ERROR:
            return String.valueOf(cellValue.getErrorValue());
        case Cell.CELL_TYPE_FORMULA:
            return null;
        default:
            return null;
        }
    }
}

 

2. Handler

import java.io.BufferedWriter;
import java.io.FileWriter;
import java.io.IOException;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;

public class FlatFileConversionUtil
{
    private static String headers = "aaa~bbb~ccc~ddd";

    private static String rowDelimeter = "~";
    private static String colDelimeter = ",";

    public static void convertToFlatFile(Sheet fromSheet, String csvFileName)
            throws IOException
    {
        StringBuilder fileContent = new StringBuilder();
        fileContent.append(headers);
        fileContent.append(rowDelimeter);
        String contents = readContents(fromSheet);
        fileContent.append(contents);
        writeToFile(csvFileName, fileContent.toString());
    }

    /**
     * Write fileContent to csv file <br/>
     * 
     * @param csvFileName
     * @param fileContent
     *            "111,222,333~111,222,333"
     * @throws IOException
     */
    private static void writeToFile(String csvFileName, String fileContent)
            throws IOException
    {
        BufferedWriter writer = new BufferedWriter(new FileWriter(csvFileName));

        String[] lines = fileContent.split(rowDelimeter);
        for (int i = 0; i < lines.length; i++)
        {
            String line = lines[i];
            writer.write(line);
            if (i != (lines.length - 1))
            {
                writer.newLine();
            }
        }

        writer.flush();
        writer.close();
    }

    /**
     * Output like:
     * "111,222,333,444,555~111,222,333,444,555~111,222,333,444,555" <br/>
     * 
     * @param fromSheet
     * @return
     */
    private static String readContents(Sheet fromSheet)
    {
        StringBuilder contents = new StringBuilder();
        for (int i = 1; i <= fromSheet.getLastRowNum(); i++)
        {
            String line = readRow(fromSheet, i, 109);
            contents.append(line);
            if (i != fromSheet.getLastRowNum())
            {
                contents.append(rowDelimeter);
            }
        }

        return contents.toString();
    }

    /**
     * Output like: "111,222,333,444,555" <br/>
     * 
     * @param fromSheet
     * @param rowNum
     * @param colCount
     * @return rowData: "data" + colDelimeter + "data"
     */
    private static String readRow(Sheet fromSheet, int rowNum, int colCount)
    {
        StringBuilder data = new StringBuilder();

        Row row = ExcelUtil.getRow(fromSheet, rowNum);
        for (int i = 0; i < colCount; i++)
        {
            Cell cell = ExcelUtil.getCell(row, i);
            String cellValue = ExcelUtil.getValue(cell).trim();
            data.append(cellValue);

            if ((colCount - 1) != i)
            {
                data.append(colDelimeter);
            }
        }

        return data.toString();
    }
}

 

3. Test case

import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
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.ss.usermodel.WorkbookFactory;
import org.junit.Test;


public class ExcelFileConversionTest
{
    @Test
    public void testConvertToCsv() throws InvalidFormatException, IOException
    {
        List<String> fileNameList = new ArrayList<String>();
        fileNameList.add("excel_1.xlsx");

        for (String fileName : fileNameList)
        {
            Workbook wb = WorkbookFactory.create(new File("/" + fileName));

            int sheetCount = wb.getNumberOfSheets();
            String fileNamePrefix = "ALM_";
            String fileNameSuffix = ".csv";
            for (int i = 0; i < sheetCount; i++)
            {
                Sheet sheet = wb.getSheetAt(i);
                String csvFileName = getCsvFileName(sheet, fileNamePrefix,
                        fileNameSuffix);
                FlatFileConversionUtil.convertToFlatFile(sheet, csvFileName);
            }
        }
    }

    private String getCsvFileName(Sheet sheet, String fileNamePrefix,
            String fileNameSuffix)
    {
        Row row = ExcelUtil.getRow(sheet, 1);
        Cell cell = ExcelUtil.getCell(row, 0);
        String cobDate = ExcelUtil.getValue(cell);
        return fileNamePrefix + cobDate + fileNameSuffix;
    }
}