Calcite读取Excel数据

前言

        学习Calcite官方教程中的读取csv文件样例的源码，简单写个读取Excel文件的小demo来学习一下。将一个Excel文件当作一个数据库 ，每个sheet页当作一张表，实现CalCite查询Excel的步骤如下 ：

1、实现SchemaFactory接口类，重写create方法

2、通过create方法创建schema对象

3、Schema对象获取Table

4、从Table对象中获取字段类型

5、从Table中读取数据

Excel文件样例

标题行所在字段由两部分组成，英文冒号左侧为字段名，右侧为字段类型

代码

import org.apache.calcite.schema.Schema;
import org.apache.calcite.schema.SchemaFactory;
import org.apache.calcite.schema.SchemaPlus;

import java.util.Map;


public class ExcelSchemaFactory implements SchemaFactory {

    @Override
    public Schema create(SchemaPlus schemaPlus, String s, Map<String, Object> operand) {
        String filePath = (String) operand.get("filePath");
        return new ExcelSchema(filePath);
    }
}

import org.apache.calcite.schema.Table;
import org.apache.calcite.schema.impl.AbstractSchema;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

import java.io.File;
import java.util.HashMap;
import java.util.Map;


public class ExcelSchema extends AbstractSchema {
    private final String filePath;

    public ExcelSchema(String filePath) {
        this.filePath = filePath;
    }

    @Override
    protected Map<String, Table> getTableMap() {
        Map<String, Table> tables = new HashMap<>();
        try (Workbook workbook = WorkbookFactory.create(new File(filePath))) {
            for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
                Sheet sheet = workbook.getSheetAt(i);
                String tableName = sheet.getSheetName();
                ExcelTable table = new ExcelTable(filePath, tableName);
                tables.put(tableName, table);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return tables;
    }
}

import org.apache.calcite.DataContext;
import org.apache.calcite.linq4j.Enumerable;
import org.apache.calcite.linq4j.Linq4j;
import org.apache.calcite.rel.type.RelDataType;
import org.apache.calcite.rel.type.RelDataTypeFactory;
import org.apache.calcite.schema.ScannableTable;
import org.apache.calcite.schema.impl.AbstractTable;
import org.apache.poi.ss.usermodel.*;

import java.io.FileInputStream;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

public class ExcelTable extends AbstractTable implements ScannableTable {
    private final String filePath;
    private final String sheetName;

    public ExcelTable(String filePath, String sheetName) {
        this.filePath = filePath;
        this.sheetName = sheetName;
    }

    @Override
    public RelDataType getRowType(RelDataTypeFactory typeFactory) {
        try (InputStream inp = new FileInputStream(filePath);
             Workbook workbook = WorkbookFactory.create(inp)) {
            Sheet sheet = workbook.getSheet(sheetName);
            if (sheet != null) {
                Row headerRow = sheet.getRow(0);
                if (headerRow != null) {
                    RelDataTypeFactory.Builder builder = typeFactory.builder();
                    for (int i = 0; i < headerRow.getLastCellNum(); i++) {
                        Cell cell = headerRow.getCell(i, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
                        String[] columnInfo = cell.getStringCellValue().split(":");
                        String columnName = columnInfo[0].trim();
                        String columnType = columnInfo[1].trim();
                        RelDataType dataType = getColumnType(columnType, typeFactory);
                        builder.add(columnName, dataType);
                    }
                    return builder.build();
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    private RelDataType getColumnType(String columnType, RelDataTypeFactory typeFactory) {
        if ("varchar".equalsIgnoreCase(columnType)) {
            return typeFactory.createJavaType(String.class);
        }else if ("double".equalsIgnoreCase(columnType)) {
            return typeFactory.createJavaType(Double.class);
        } else {
            return typeFactory.createJavaType(String.class);
        }
    }

    @Override
    public Enumerable<Object[]> scan(DataContext root) {
        try (InputStream inp = new FileInputStream(filePath);
             Workbook workbook = WorkbookFactory.create(inp)) {

            Sheet sheet = workbook.getSheet(sheetName);
            if (sheet != null) {
                return Linq4j.asEnumerable(readExcelData(sheet));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return Linq4j.emptyEnumerable();
    }

    private List<Object[]> readExcelData(Sheet sheet) {
        List<Object[]> rows = new ArrayList<>();
        for (int rowIndex = 1; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
            Row row = sheet.getRow(rowIndex);
            Object[] rowData = new Object[row.getLastCellNum()];
            for (int i = 0; i < row.getLastCellNum(); i++) {
                Cell cell = row.getCell(i, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
                switch (cell.getCellType()) {
                    case STRING:
                        rowData[i] = cell.getStringCellValue();
                        break;
                    case NUMERIC:
                        if (DateUtil.isCellDateFormatted(cell)) {
                            rowData[i] = cell.getDateCellValue();
                        } else {
                            rowData[i] = cell.getNumericCellValue();
                        }
                        break;
                    default:
                        rowData[i] = null;
                }
            }
            rows.add(rowData);
        }
        return rows;
    }
}

model.json配置文件

{
  "version": "1.0",
  "defaultSchema": "TEST",
  "schemas": [
    {
      "name": "TEST",
      "type": "custom",
      "factory": "com.example.demo.calcite.demo.excel.ExcelSchemaFactory",
      "operand": {
        "filePath": "C:/Users/DELL/Desktop/test.xlsx"
      }
    }
  ]
}

import java.io.File;
import java.net.URL;
import java.net.URLDecoder;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.Map;
import java.util.Properties;

import com.google.common.collect.Maps;
import org.apache.calcite.jdbc.CalciteConnection;
import org.apache.calcite.schema.SchemaPlus;


public class TestExcel {
    public static void main(String[] args) throws Exception {
        String jsonPath = "F:/EditorSoftware/IDAEA/geovis/demo/src/main/java/com/example/demo/calcite/demo/excel/model.json";
        URL url = new File(jsonPath).toURI().toURL();
        String config = URLDecoder.decode(url.toString(), "UTF-8");
        Properties info = new Properties();
        info.setProperty("charset", "UTF-8");
        info.setProperty("model", config.substring(config.indexOf("file:") + "file:".length()));
        Connection connection = DriverManager.getConnection("jdbc:calcite:", info);
        CalciteConnection calciteConnection = connection.unwrap(CalciteConnection.class);
        SchemaPlus rootSchema = calciteConnection.getRootSchema();

        String sql = "SELECT * FROM TEST.A_STU";
        ResultSet resultSet = calciteConnection.createStatement().executeQuery(sql);
        ResultSetMetaData metaData = resultSet.getMetaData();
        int columnSize = metaData.getColumnCount();
        while (resultSet.next()) {
            Map<String, Object> map = Maps.newLinkedHashMap();
            for (int i = 1; i < columnSize + 1; i++) {
                map.put(metaData.getColumnLabel(i), resultSet.getObject(i).toString());
            }
            System.out.println(map);
        }
    }
}

总结

感觉写的demo还是不太好的，看一下源码了解一些类的作用，能够更好的学习Calcite，如RelDataType等。

持续学习中。。。。。。