java - 尝试使用 Java 和 MetaModel API 查询 .CSV 文件

Question

我正在尝试使用 Java 和 MetaModel API 查询 .CSV 文件并且没有成功。这个 API 对于 .XLS 文件很容易，但对于 .CSV 文件我很困惑，因为 SQL 查询没有表名。任何人都可以提供有关如何执行此操作的任何提示或见解吗？

public static Object[][] getCsvData( File csvFile ) 
{   
    CsvConfiguration conf = new CsvConfiguration( 1, true, false );
    DataContext ctx = DataContextFactory.createCsvDataContext( csvFile, conf );

// CsvDataSet dataSet = csvContext.query().
//      .selectAll()
//      .where("run").eq("Y")
//      .execute();
// List<Row> rows = dataSet.toRows();

如果您想尝试我的代码，请参阅我的 GitHub project这里是正在进行的概念验证(带有一个有效的 Excel 示例)。

Answer 1

对于 CSV 你可以这样做

public static Object[][] getCsvData(File csvFile) {
        CsvConfiguration conf = new CsvConfiguration(1);
        DataContext csvContext = DataContextFactory.createCsvDataContext(
                csvFile, conf);
        Schema schema = csvContext.getDefaultSchema();
        Table[] tables = schema.getTables();
        Table table = tables[0];
        DataSet dataSet = csvContext.query().from(table).selectAll().where("run").eq("Y").execute();
        List<Row> rows = dataSet.toRows();
        Object[][] myArray = new Object[rows.size()][2];
        int i = 0;
        SelectItem[] cols = rows.get(0).getSelectItems();
        for (Row r : rows) {
            Object[] data = r.getValues();
            for (int j = 0; j < cols.length; j++) {
                if (data[j] == null)
                    data[j] = ""; // force empty string where there are NULL
                                    // values
            }
            myArray[i][0] = cols;
            myArray[i][1] = data;
            i++;
        }
        logger.info("Row count: " + rows.size());
        logger.info("Column names: " + Arrays.toString(cols));
        return myArray;
    }

我想出了对于XmlDataProvider你可以这样做

@Test
    public void testXML() {

        XmlSaxTableDef employeeTableDef = new XmlSaxTableDef(
                "/root/organization/employees/employee", new String[] {
                        "/root/organization/employees/employee/name",
                        "/root/organization/employees/employee/gender",
                        "index(/root/organization)"});

        XmlSaxTableDef organizationTableDef = new XmlSaxTableDef(
                "/root/organization", new String[] { 
                        "/root/organization/name",
                        "/root/organization@type" });

        DataContext dc = new XmlSaxDataContext(xmlFile, employeeTableDef,
                organizationTableDef);

        Table employeeTable = dc.getTableByQualifiedLabel("/employee");
        Column fk = employeeTable.getColumnByName("index(/root/organization)");
        Column empName = employeeTable.getColumnByName("/name");

        Table organizationTable = dc.getTableByQualifiedLabel("/organization");
        Column orgId = organizationTable.getColumnByName("row_id");
        Column orgName = organizationTable.getColumnByName("/name");
        Query q = dc.query().from(employeeTable)
                .innerJoin(organizationTable).on( fk, orgId )
                .select(empName).as("employeename")
                .select(orgName).as("companyname").toQuery();
        DataSet ds = dc.executeQuery(q);

        List<Row> rows = ds.toRows();
        for (Row r : rows) {
            System.out.println(Arrays.deepToString(r.getValues()));
        }

    }

如果您遇到任何问题，请告诉我:)