java excel到csv文件的转换

java excel to csv file convert

提问by sakthi

I am trying to convert .xlsx file to .csv, convertion is happening but the data is not formatted properly. Please find code below and suggest changes to the code.

我正在尝试将 .xlsx 文件转换为 .csv，正在转换但数据格式不正确。请在下面找到代码并建议对代码进行更改。

Here I am trying to read an .xlsx file and write it to a csv file i.e. converting xlsx to csv but I am not getting the .csv file in proper format all the data is displayed in a single but it must displayed like rows in Excel.

在这里，我试图读取一个 .xlsx 文件并将其写入一个 csv 文件，即将 xlsx 转换为 csv 但我没有以正确的格式获取 .csv 文件所有数据都显示在一个单一的但它必须像 Excel 中的行一样显示.

import java.io.File;

import java.io.FileInputStream;

import java.io.FileNotFoundException;

import java.io.FileOutputStream;

import java.io.IOException;

import java.util.Iterator;

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;

class XlstoCSV 

{

   static void xls(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 XLS file
    HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(inputFile));
    // Get first sheet from the workbook
    HSSFSheet sheet = workbook.getSheetAt(0);
    Cell cell;
    Row row;

    // Iterate through each rows from first sheet
    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'); 
            }
    }

    fos.write(data.toString().getBytes());
    fos.close();
    }
    catch (FileNotFoundException e) 
    {
            e.printStackTrace();
    }
    catch (IOException e) 
    {
            e.printStackTrace();
    }
    }

    public static void main(String[] args) 
    {
            File in`enter code here`putFile = new File("C:\test.xls");
            File outputFile = new File("C:\output.csv");
            xls(inputFile, outputFile);
    }
}

回答by tom

The problem is you are appending a new line after every cell, here:

问题是您在每个单元格后添加一个新行，这里：

data.append('\n'); 

You need to do it after every row instead.

您需要在每一行之后执行此操作。

Note also that you would be best using a proper CSV library for serialisation as it will handle escaping strings correctly.

另请注意，最好使用适当的 CSV 库进行序列化，因为它将正确处理转义字符串。

回答by tom

Try Changing your code to this your new line char has to be appended after the one row read

尝试将您的代码更改为此您的新行字符必须在读取一行后附加

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

回答by Akilan

Quite a old post but will help someone.. With Multiple Sheets.

相当旧的帖子，但会帮助某人.. 多张纸。

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.io.OutputStreamWriter;
import java.util.Iterator;

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;

class XlStoCSV {
    static void xls(File inputFile) {
        // Get the workbook object for XLS file
        int count = 0;
        try {

            HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(
                    inputFile));
            for (int l = workbook.getNumberOfSheets() - 1; l >= 0; l--) {
                File outputFile = new File(System.getProperty("user.dir")
                        + "/output/"+inputFile.getName()+"-"+workbook.getSheetName(count) +".csv");

                // For storing data into CSV files
                StringBuffer data = new StringBuffer();
                FileOutputStream fos = new FileOutputStream(outputFile);
                HSSFSheet sheet = workbook.getSheetAt(count);
                Cell cell;
                Row row;

                // Iterate through each rows from first sheet
                Iterator<Row> rowIterator = sheet.iterator();
                while (rowIterator.hasNext()) {
                    row = rowIterator.next();
                    // For each row, iterate through each columns
                    Iterator<Cell> cellIterator = row.cellIterator();
                    int columnNumber = 1;
                    while (cellIterator.hasNext()) {
                        cell = cellIterator.next();
                        if (columnNumber > 1)
                        {
                            data.append(",");
                        }

                        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);
                        }
                         ++columnNumber;
                    }
                    data.append('\n');
                }

                fos.write(data.toString().getBytes());
                fos.close();
                count++;
            }
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) {
        File inputFile = new File(System.getProperty("user.dir") + "/assets/"
                + "test.xls");
        xls(inputFile);
    }
}

回答by MK-rou

Try this :

尝试这个 ：

public String convertRowContentToCSV(Row row) {
        Iterator<Cell> cellIterator = row.cellIterator();
        StringBuilder data = new StringBuilder();
        while (cellIterator.hasNext()) {
            Cell cell = cellIterator.next();

            switch (cell.getCellTypeEnum()) {
                case BOOLEAN:
                    data.append(cell.getBooleanCellValue()).append(",");
                    break;

                case NUMERIC:
                    data.append(cell.getNumericCellValue()).append(",");
                    break;

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

                case BLANK:
                    data.append(",");
                    break;

                case FORMULA:
                case _NONE:
                case ERROR:
                    break;

                default:
                    data.append(cell).append(",");
            }

        }
        return data.toString();
    }

回答by Abdullah

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