java - XSSFWorksheet 代码中未检测到空白

Question

我正在将 XLSX 转换为 CSV 并使用以下代码将数据上传到数据库，

import java.io.*;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
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 XLStoCSVConvert {
    static Connectivity conv;
    static Connection con = null;
    static Statement st = null;
    static String query=null;
     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
                System.out.println("working......");
                XSSFWorkbook wBook = new XSSFWorkbook(new FileInputStream(inputFile));
                // Get first sheet from the workbook
                System.out.println("working......");
                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 + ",");

                        }

                    }
                    data.append("\n");
                }

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


                conv = new Connectivity();
                con = conv.setConnection();
                st = con.createStatement();
                query = "LOAD DATA LOCAL INFILE \"" + outputFile + "\" INTO TABLE xLSXUpload FIELDS TERMINATED BY ',' IGNORE 1 LINES";

                st.executeUpdate(query);



            } catch (Exception ioe) {
                ioe.printStackTrace();
            }
        }

    public static void main(String[] args) {
        try
        {
            //FileOutputStream fin = new FileOutputStream("/home/raptorjd4/Desktop/RaptorTrackingSystem/mani.csv");
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }
        File inputFile = new File("/home/raptorjd4/Desktop/XLS files/ToConsult.xlsx");
        //writing excel data to csv 
        File outputFile = new File("/home/raptorjd4/Desktop/RaptorTrackingSystem/ToConsult.csv");
        xlsx(inputFile, outputFile);

    }

}

以上代码将数据上传到表，但代码未检测到空白值，并且空值存储在表中作为空白值，并且日期格式始终存储在2033-01-02而不是1990- 05-19.

我哪里做错了？

Answer 1

Excel 不知道单元格的日期。该值存储为数字，只有格式使其成为日期。

如果您的单元格包含值 33012 并且您指定了日期格式 yyyy-mm-dd，它将在 Excel 中显示为 1990-05-19 。当您提取数据时，您将获得该单元格的数值。

您需要区分应被解释为日期的数值与正常数值。查找下面的代码片段作为示例。

import org.apache.poi.ss.usermodel.DateUtil;
...
case Cell.CELL_TYPE_NUMERIC:
    if (DateUtil.isCellDateFormatted(cell)) {
        // append as String in the format specified in Excel
        // System.out.println("format: " + cell.getCellStyle().getDataFormatString());
        data.append(cell);
    } else {
        data.append(cell.getNumericCellValue());
    }