问题使用Apache POI API将xlsx转换为csv

我想转换 .XLSX 归档到 的.csv，转换发生但数据格式不正确。请在下面找到代码，并建议更改代码。

在这里，我想读一个 .XLSX 文件并将其写入 CSV 文件即转换 XLSX 至 CSV 但我没有得到 的.csv 文件格式正确，所有数据都显示在一个文件中，但必须在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);
    }
}

5276

2018-06-27 14:19

起源

你尝试过什么？请给我们一个精确输出和任何错误，或者问题可能会被无限期关闭或暂停。 - Andrey Akhmetov

您缺少新的行字符 - Swapnil

excel中的行必须在csv i中显示类似，如果我在excel中有5行我应该在csv中获得5行但上面的代码是在单个中添加所有5行我希望它在csv中添加到行中 - user2335416

为什么你只是保存为... csv。完成它 - Bhavik Shah

如果你没有有条件地从每一行的末尾删除一个逗号，这不会在每一行上创建一个额外的列吗？ - anon58192932

答案:

这是谢谢@Swapnil！

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

2018-06-27 15:19

public static void convertToXlsx(File inputFile, File outputFile) {
StringBuffer bf = new StringBuffer();
    FileOutputStream fos = null;
    String strGetValue = "";
    try {
        fos = new FileOutputStream(outputFile);
        XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(inputFile));
        XSSFSheet sheet = wb.getSheetAt(0);
        Row row;
        Cell cell;
        int intRowCounter = 0;
        Iterator<Row> rowIterator = sheet.iterator();
        while (rowIterator.hasNext()) {
            StringBuffer cellDData = new StringBuffer();
            row = rowIterator.next();
            int maxNumOfCells = sheet.getRow(0).getLastCellNum();
            int cellCounter = 0;
            while ((cellCounter) < maxNumOfCells) {
                if (sheet.getRow(row.getRowNum()) != null
                        && sheet.getRow(row.getRowNum()).getCell(cellCounter) != null) {
                    cell = sheet.getRow(row.getRowNum()).getCell(cellCounter);
                    switch (cell.getCellType()) {
                    case Cell.CELL_TYPE_BOOLEAN:
                        strGetValue = cell.getBooleanCellValue() + ",";
                        cellDData.append(removeSpace(strGetValue));
                        break;
                    case Cell.CELL_TYPE_NUMERIC:
                        strGetValue = new BigDecimal(cell.getNumericCellValue()).toPlainString();
                        if (DateUtil.isCellDateFormatted(cell)) {
                            strGetValue = new DataFormatter().formatCellValue(cell);
                        } else {
                            strGetValue = new BigDecimal(cell.getNumericCellValue()).toPlainString();
                        }
                        String tempStrGetValue = removeSpace(strGetValue);
                        if (tempStrGetValue.length() == 0) {
                            strGetValue = " ,";
                            cellDData.append(strGetValue);
                        } else {
                            strGetValue = strGetValue + ",";
                            cellDData.append(removeSpace(strGetValue));
                        }
                        break;
                    case Cell.CELL_TYPE_STRING:
                        strGetValue = cell.getStringCellValue();
                        String tempStrGetValue1 = removeSpace(strGetValue);
                        if (tempStrGetValue1.length() == 0) {
                            strGetValue = " ,";
                            cellDData.append(strGetValue);
                        } else {
                            strGetValue = strGetValue + ",";
                            cellDData.append(removeSpace(strGetValue));
                        }
                        break;
                    case Cell.CELL_TYPE_BLANK:
                        strGetValue = "" + ",";
                        cellDData.append(removeSpace(strGetValue));
                        break;
                    default:
                        strGetValue = cell + ",";
                        cellDData.append(removeSpace(strGetValue));
                    }
                } else {
                    strGetValue = " ,";
                    cellDData.append(strGetValue);
                }
                cellCounter++;
            }
            String temp = cellDData.toString();
            if (temp != null && temp.contains(",,,")) {
                temp = temp.replaceFirst(",,,", ", ,");
            }
            if (temp.endsWith(",")) {
                temp = temp.substring(0, temp.lastIndexOf(","));
                cellDData = null;
                bf.append(temp.trim());
            }
            bf.append("\n");
            intRowCounter++;
        }
        fos.write(bf.toString().getBytes());
        fos.close();
    } catch (Exception ex) {
        ex.printStackTrace();
    } finally {
        try {
            if (fos != null)
                fos.close();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }
}
private static String removeSpace(String strString) {
    if (strString != null && !strString.equals("")) {
        return strString.trim();
    }
    return strString;
}

代码示例句柄和单元格中的空格字符
＃

2018-06-09 11:18

问题 使用Apache POI API将xlsx转换为csv

答案:

＃

热门问题

问题使用Apache POI API将xlsx转换为csv