XLSX转换为csv与Apache POI API [英] Convert xlsx to csv with Apache POI API

问题描述

我想转换文件的的.xlsx 的到的的.csv 的，皈依正在发生的事情，但数据的格式不正确。请在下面找到code和建议改变code。

在这里，我想读的的.xlsx 的文件，并将其写入到的 CSV 的文件即转换的 XLSX 的到的 CSV 的，但我没有得到的的.csv 的以适当的格式文件中的所有数据都显示在一个单一的，但它必须在Excel中显示像行。

 进口的java.io.File;
进口java.io.FileInputStream中;
进口java.io.FileOutputStream中;
进口java.util.Iterator的;进口org.apache.poi.ss.usermodel.Cell;
进口org.apache.poi.ss.usermodel.Row;
进口org.apache.poi.xssf.usermodel.XSSFSheet;
进口org.apache.poi.xssf.usermodel.XSSFWorkbook;公共类XlsxtoCSV {    静态无效XLSX（inputfile文件，文件OUTPUTFILE）{
        //用于存储数据为CSV文件
        StringBuffer的数据=新的StringBuffer（）;        尝试{
            FOS的FileOutputStream =新的FileOutputStream（OUTPUTFILE）;
            //获取XLSX文件工作簿对象
            XSSFWorkbook wBook​​ =新XSSFWorkbook（新的FileInputStream（INPUTFILE））;
            //从工作簿中第一张
            XSSFSheet片= wBook​​.getSheetAt（0）;
            鳞次栉比;
            细胞细胞;
            //通过从第一张各行迭代
            迭代器＆LT;行＆GT; rowIterator = sheet.iterator（）;            而（rowIterator.hasNext（））{
                行= rowIterator.next（）;                //对于每一行，遍历每个列
                迭代＆所述;电池＆gt; cellIterator = row.cellIterator（）;
                而（cellIterator.hasNext（））{                    细胞= cellIterator.next（）;                    开关（cell.getCellType（））{
                        案例Cell.CELL_TYPE_BOOLEAN：
                            data.append（cell.getBooleanCellValue（）+，）;                            打破;
                        案例Cell.CELL_TYPE_NUMERIC：
                            data.append（cell.getNumericCellValue（）+，）;                            打破;
                        案例Cell.CELL_TYPE_STRING：
                            data.append（cell.getStringCellValue（）+，）;
                            打破;                        案例Cell.CELL_TYPE_BLANK：
                            data.append（+，）;
                            打破;
                        默认：
                            data.append（细胞中+，）;                    }
                }
            }            fos.write（data.toString（）的getBytes（））;
            fos.close（）;        }赶上（例外IOE）{
            ioe.printStackTrace（）;
        }
    }
    //测试应用程序    公共静态无效的主要（字串[] args）{
        //读取从桌面文件
        inputfile文件=新的文件（C：\\\\ \\\\用户\\\\ user69桌面\\\\ test.xlsx）;
        //写入Excel数据为csv
        文件OUTPUTFILE =新的文件（C：\\\\ \\\\用户\\\\ user69桌面\\\\ test1.csv）;
        XLSX（INPUTFILE，OUTPUTFILE）;
    }
}
 

解决方案

这是由于@Swapnil！

  data.append（\\ r \\ n）; //后列已追加。
 

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.

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.

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

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;

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
            XSSFWorkbook wBook = new XSSFWorkbook(new FileInputStream(inputFile));
            // Get first sheet from the workbook
            XSSFSheet sheet = wBook.getSheetAt(0);
            Row row;
            Cell cell;
            // 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 + ",");

                    }
                }
            }

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

        } catch (Exception ioe) {
            ioe.printStackTrace();
        }
    }
    //testing the application 

    public static void main(String[] args) {
        //reading file from desktop
        File inputFile = new File("C:\\Users\\user69\\Desktop\\test.xlsx");
        //writing excel data to csv 
        File outputFile = new File("C:\\Users\\user69\\Desktop\\test1.csv");
        xlsx(inputFile, outputFile);
    }
}

解决方案

This is thanks @Swapnil!

data.append("\r\n"); // After the columns have been appended.

这篇关于XLSX转换为csv与Apache POI API的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！