java使用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);
}
}
# 1 楼答案
使用Commons CSV对单元格值进行编码,这样更健壮。不幸的是,仍然需要一些额外的代码来逐行/单元格地遍历工作表,并在每个单元格上调用Commons CSV(XSSF不提供这一点),但至少要保证编写的实际单元格值是标准CSV(即,您不必担心转义字符或添加逗号)
Maven为Commons CSV添加:
一旦Commons CSV可用,这就是将工作簿导出到CSV的代码。此示例写入OutputStream,但文件输出也很容易
# 2 楼答案
这是感谢@Swapnil
以下内容由@Abdullah编辑(添加) 我在上面最初的回答没有那么大的影响,但阿卜杜拉的编辑显示了很大的努力,所以我把它留给那些遇到这个问题的人&;回答
# 3 楼答案
#