使用SQL語言查詢多個(gè)Excel表格的操作方法

更新時(shí)間：2025年01月22日 09:00:30 作者：pythonsmilefly

本文介紹了如何使用SQL語言查詢多個(gè)Excel表格,通過將所有Excel表格放入一個(gè).xlsx文件中,并使用pandas和pandasql庫進(jìn)行讀取和查詢,此外,還探討了如何實(shí)現(xiàn)一個(gè)自定義的calcite文件適配器來支持Excel文件的查詢,感興趣的朋友一起看看吧

如何用SQL語言查詢多個(gè)Excel表格

沒錯(cuò)，之前我也不知道SQL語言除了可以查詢（本文只討論查詢語句）數(shù)據(jù)庫，還可以查詢Excel，或者說經(jīng)過一定處理后，可以像查詢數(shù)據(jù)庫一樣查詢Excel。

下面給出一個(gè)場景，假如你有幾個(gè)（個(gè)數(shù)未知）Excel表格，你想在這些表格上實(shí)現(xiàn)SQL多表查詢，該怎么辦？

像這樣：

學(xué)號	姓名
1054	小姜
1055	小王
1061	小李
1081	王哥

課程名稱	任課老師
人工智能	王老師
數(shù)據(jù)庫	李老師
運(yùn)籌學(xué)	張老師
概率論	郝老師

學(xué)號	課程名稱	分?jǐn)?shù)
1054	人工智能	90
1055	數(shù)據(jù)庫	91
1061	運(yùn)籌學(xué)	92
1081	概率論	91
1054	運(yùn)籌學(xué)	89
1055	概率論	91
1061	人工智能	95
1081	數(shù)據(jù)庫	94

大致思路如下：

將所有要導(dǎo)入的Excel表放入一個(gè).xlsx文件中，將各Sheet命名為表名，類似數(shù)據(jù)庫的table名；
利用pandas庫讀取.xlsx文件并創(chuàng)建為一個(gè)ExcelFile類；
利用類中名為sheet_names的property獲取其所有該文件所有的Sheet名；
用locals和read_excel函數(shù)創(chuàng)建名為各sheet名，值為各sheet內(nèi)容的局部變量；
利用pandasql庫中的sqldf來查詢一個(gè)或多個(gè)dataframe，sqldf函數(shù)默認(rèn)查詢所有局部變量中的dataframe。

利用pandasql庫中的sqldf來查詢一個(gè)或多個(gè)dataframe，sqldf函數(shù)默認(rèn)查詢所有局部變量中的dataframe。

代碼如下：

import pandas as pd
from pandasql import sqldf
def dealwith_excel(excel_file,sql_query):
    xls = pd.ExcelFile(excel_file)
    sheet_names = xls.sheet_names #list type
    # print(sheet_names)
    for sheet_name in sheet_names:
        locals()[sheet_name] = pd.read_excel(excel_file, sheet_name=sheet_name)
    df_result = sqldf(sql_query)
    return df_result

最后返回的就是查詢結(jié)果!

擴(kuò)展：

如何使用sql查詢excel內(nèi)容

1. 簡介

我們在前面的文章中提到了calcite支持csv和json文件的數(shù)據(jù)源適配, 其實(shí)就是將文件解析成表然后以文件夾為schema, 然后將生成的schema注冊到RootSehema(RootSchema是所有數(shù)據(jù)源schema的parent，多個(gè)不同數(shù)據(jù)源schema可以掛在同一個(gè)RootSchema下)下, 最終使用calcite的特性進(jìn)行sql的解析查詢返回.

但其實(shí)我們的數(shù)據(jù)文件一般使用excel進(jìn)行存儲,流轉(zhuǎn), 但很可惜, calcite本身沒有excel的適配器, 但其實(shí)我們可以模仿calcite-file, 自己搞一個(gè)calcite-file-excel, 也可以熟悉calcite的工作原理.

2. 實(shí)現(xiàn)思路

因?yàn)閑xcel有sheet的概念, 所以可以將一個(gè)excel解析成schema, 每個(gè)sheet解析成table, 實(shí)現(xiàn)步驟如下:

實(shí)現(xiàn)SchemaFactory重寫create方法: schema工廠用于創(chuàng)建schema
繼承AbstractSchema: schema描述類用于解析excel, 創(chuàng)建table(解析sheet)
繼承AbstractTable, ScannableTable: table描述類提供字段信息和數(shù)據(jù)內(nèi)容等(解析sheet data)

3. Excel樣例

excel有兩個(gè)sheet頁, 分別是user_info 和 role_info如下:

ok, 萬事具備.

4. Maven

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>5.2.3</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>5.2.3</version>
</dependency>
<dependency>
    <groupId>org.apache.calcite</groupId>
    <artifactId>calcite-core</artifactId>
    <version>1.37.0</version>
</dependency>

5. 核心代碼

5.1 SchemaFactory

package com.ldx.calcite.excel;
import com.google.common.collect.Lists;
import org.apache.calcite.schema.Schema;
import org.apache.calcite.schema.SchemaFactory;
import org.apache.calcite.schema.SchemaPlus;
import org.apache.commons.lang3.ObjectUtils;
import org.apache.commons.lang3.StringUtils;
import java.io.File;
import java.util.List;
import java.util.Map;
/**
 * schema factory
 */
public class ExcelSchemaFactory implements SchemaFactory {
    public final static ExcelSchemaFactory INSTANCE = new ExcelSchemaFactory();
    private ExcelSchemaFactory(){}
    @Override
    public Schema create(SchemaPlus parentSchema, String name, Map<String, Object> operand) {
        final Object filePath = operand.get("filePath");
        if (ObjectUtils.isEmpty(filePath)) {
            throw new NullPointerException("can not find excel file");
        }
        return this.create(filePath.toString());
    }
    public Schema create(String excelFilePath) {
        if (StringUtils.isBlank(excelFilePath)) {
            throw new NullPointerException("can not find excel file");
        }
        return this.create(new File(excelFilePath));
    }
    public Schema create(File excelFile) {
        if (ObjectUtils.isEmpty(excelFile) || !excelFile.exists()) {
            throw new NullPointerException("can not find excel file");
        }
        if (!excelFile.isFile() || !isExcelFile(excelFile)) {
            throw new RuntimeException("can not find excel file: " + excelFile.getAbsolutePath());
        }
        return new ExcelSchema(excelFile);
    }
    protected List<String> supportedFileSuffix() {
        return Lists.newArrayList("xls", "xlsx");
    }
    private boolean isExcelFile(File excelFile) {
        if (ObjectUtils.isEmpty(excelFile)) {
            return false;
        }
        final String name = excelFile.getName();
        return StringUtils.endsWithAny(name, this.supportedFileSuffix().toArray(new String[0]));
    }
}

schema中有多個(gè)重載的create方法用于方便的創(chuàng)建schema, 最終將excel file 交給ExcelSchema創(chuàng)建一個(gè)schema對象

5.2 Schema

package com.ldx.calcite.excel;
import org.apache.calcite.schema.Table;
import org.apache.calcite.schema.impl.AbstractSchema;
import org.apache.commons.lang3.ObjectUtils;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.testng.collections.Maps;
import java.io.File;
import java.util.Iterator;
import java.util.Map;
/**
 * schema
 */
public class ExcelSchema extends AbstractSchema {
    private final File excelFile;
    private Map<String, Table> tableMap;
    public ExcelSchema(File excelFile) {
        this.excelFile = excelFile;
    }
    @Override
    protected Map<String, Table> getTableMap() {
        if (ObjectUtils.isEmpty(tableMap)) {
            tableMap = createTableMap();
        }
        return tableMap;
    }
    private Map<String, Table> createTableMap() {
        final Map<String, Table> result = Maps.newHashMap();
        try (Workbook workbook = WorkbookFactory.create(excelFile)) {
            final Iterator<Sheet> sheetIterator = workbook.sheetIterator();
            while (sheetIterator.hasNext()) {
                final Sheet sheet = sheetIterator.next();
                final ExcelScannableTable excelScannableTable = new ExcelScannableTable(sheet, null);
                result.put(sheet.getSheetName(), excelScannableTable);
            }
        }
        catch (Exception ignored) {}
        return result;
    }
}

schema類讀取Excel file, 并循環(huán)讀取sheet, 將每個(gè)sheet解析成ExcelScannableTable并存儲

5.3 Table

package com.ldx.calcite.excel;
import com.google.common.collect.Lists;
import com.ldx.calcite.excel.enums.JavaFileTypeEnum;
import org.apache.calcite.DataContext;
import org.apache.calcite.adapter.java.JavaTypeFactory;
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.rel.type.RelProtoDataType;
import org.apache.calcite.schema.ScannableTable;
import org.apache.calcite.schema.impl.AbstractTable;
import org.apache.calcite.sql.type.SqlTypeName;
import org.apache.calcite.util.Pair;
import org.apache.commons.lang3.ObjectUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.checkerframework.checker.nullness.qual.Nullable;
import java.util.List;
/**
 * table
 */
public class ExcelScannableTable extends AbstractTable implements ScannableTable {
    private final RelProtoDataType protoRowType;
    private final Sheet sheet;
    private RelDataType rowType;
    private List<JavaFileTypeEnum> fieldTypes;
    private List<Object[]> rowDataList;
    public ExcelScannableTable(Sheet sheet, RelProtoDataType protoRowType) {
        this.protoRowType = protoRowType;
        this.sheet = sheet;
    }
    @Override
    public Enumerable<@Nullable Object[]> scan(DataContext root) {
        JavaTypeFactory typeFactory = root.getTypeFactory();
        final List<JavaFileTypeEnum> fieldTypes = this.getFieldTypes(typeFactory);
        if (rowDataList == null) {
            rowDataList = readExcelData(sheet, fieldTypes);
        }
        return Linq4j.asEnumerable(rowDataList);
    }
    @Override
    public RelDataType getRowType(RelDataTypeFactory typeFactory) {
        if (ObjectUtils.isNotEmpty(protoRowType)) {
            return protoRowType.apply(typeFactory);
        }
        if (ObjectUtils.isEmpty(rowType)) {
            rowType = deduceRowType((JavaTypeFactory) typeFactory, sheet, null);
        }
        return rowType;
    }
    public List<JavaFileTypeEnum> getFieldTypes(RelDataTypeFactory typeFactory) {
        if (fieldTypes == null) {
            fieldTypes = Lists.newArrayList();
            deduceRowType((JavaTypeFactory) typeFactory, sheet, fieldTypes);
        }
        return fieldTypes;
    }
    private List<Object[]> readExcelData(Sheet sheet, List<JavaFileTypeEnum> fieldTypes) {
        List<Object[]> rowDataList = Lists.newArrayList();
        for (int rowIndex = 1; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
            Row row = sheet.getRow(rowIndex);
            Object[] rowData = new Object[fieldTypes.size()];
            for (int i = 0; i < row.getLastCellNum(); i++) {
                final JavaFileTypeEnum javaFileTypeEnum = fieldTypes.get(i);
                Cell cell = row.getCell(i, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
                final Object cellValue = javaFileTypeEnum.getCellValue(cell);
                rowData[i] = cellValue;
            }
            rowDataList.add(rowData);
        }
        return rowDataList;
    }
    public static RelDataType deduceRowType(JavaTypeFactory typeFactory, Sheet sheet, List<JavaFileTypeEnum> fieldTypes) {
        final List<String> names = Lists.newArrayList();
        final List<RelDataType> types = Lists.newArrayList();
        if (sheet != null) {
            Row headerRow = sheet.getRow(0);
            if (headerRow != null) {
                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 = null;
                    if (columnInfo.length == 2) {
                        columnType = columnInfo[1].trim();
                    }
                    final JavaFileTypeEnum javaFileType = JavaFileTypeEnum
                            .of(columnType)
                            .orElse(JavaFileTypeEnum.UNKNOWN);
                    final RelDataType sqlType = typeFactory.createSqlType(javaFileType.getSqlTypeName());
                    names.add(columnName);
                    types.add(sqlType);
                    if (fieldTypes != null) {
                        fieldTypes.add(javaFileType);
                    }
                }
            }
        }
        if (names.isEmpty()) {
            names.add("line");
            types.add(typeFactory.createSqlType(SqlTypeName.VARCHAR));
        }
        return typeFactory.createStructType(Pair.zip(names, types));
    }
}

table類中其中有兩個(gè)比較關(guān)鍵的方法

? scan: 掃描表內(nèi)容, 我們這里將sheet頁面的數(shù)據(jù)內(nèi)容解析存儲最后交給calcite

? getRowType: 獲取字段信息, 我們這里默認(rèn)使用第一條記錄作為表頭(row[0]) 并解析為字段信息, 字段規(guī)則跟csv一樣 name:string, 冒號前面的是字段key, 冒號后面的是字段類型, 如果未指定字段類型, 則解析為UNKNOWN, 后續(xù)JavaFileTypeEnum會進(jìn)行類型推斷, 最終在結(jié)果處理時(shí)calcite也會進(jìn)行推斷

deduceRowType: 推斷字段類型, 方法中使用JavaFileTypeEnum枚舉類對java type & sql type & 字段值轉(zhuǎn)化處理方法進(jìn)行管理

5.4 ColumnTypeEnum

package com.ldx.calcite.excel.enums;
import lombok.Getter;
import lombok.extern.slf4j.Slf4j;
import org.apache.calcite.avatica.util.DateTimeUtils;
import org.apache.calcite.sql.type.SqlTypeName;
import org.apache.commons.lang3.ObjectUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.time.FastDateFormat;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.util.CellUtil;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Arrays;
import java.util.Date;
import java.util.Optional;
import java.util.TimeZone;
import java.util.function.Function;
/**
 * type converter
 */
@Slf4j
@Getter
public enum JavaFileTypeEnum {
    STRING("string", SqlTypeName.VARCHAR, Cell::getStringCellValue),
    BOOLEAN("boolean", SqlTypeName.BOOLEAN, Cell::getBooleanCellValue),
    BYTE("byte", SqlTypeName.TINYINT, Cell::getStringCellValue),
    CHAR("char", SqlTypeName.CHAR, Cell::getStringCellValue),
    SHORT("short", SqlTypeName.SMALLINT, Cell::getNumericCellValue),
    INT("int", SqlTypeName.INTEGER, cell -> (Double.valueOf(cell.getNumericCellValue()).intValue())),
    LONG("long", SqlTypeName.BIGINT, cell -> (Double.valueOf(cell.getNumericCellValue()).longValue())),
    FLOAT("float", SqlTypeName.REAL, Cell::getNumericCellValue),
    DOUBLE("double", SqlTypeName.DOUBLE, Cell::getNumericCellValue),
    DATE("date", SqlTypeName.DATE, getValueWithDate()),
    TIMESTAMP("timestamp", SqlTypeName.TIMESTAMP, getValueWithTimestamp()),
    TIME("time", SqlTypeName.TIME, getValueWithTime()),
    UNKNOWN("unknown", SqlTypeName.UNKNOWN, getValueWithUnknown()),;
    // cell type
    private final String typeName;
		// sql type
    private final SqlTypeName sqlTypeName;
    // value convert func
    private final Function<Cell, Object> cellValueFunc;
    private static final FastDateFormat TIME_FORMAT_DATE;
    private static final FastDateFormat TIME_FORMAT_TIME;
    private static final FastDateFormat TIME_FORMAT_TIMESTAMP;
    static {
        final TimeZone gmt = TimeZone.getTimeZone("GMT");
        TIME_FORMAT_DATE = FastDateFormat.getInstance("yyyy-MM-dd", gmt);
        TIME_FORMAT_TIME = FastDateFormat.getInstance("HH:mm:ss", gmt);
        TIME_FORMAT_TIMESTAMP = FastDateFormat.getInstance("yyyy-MM-dd HH:mm:ss", gmt);
    }
    JavaFileTypeEnum(String typeName, SqlTypeName sqlTypeName, Function<Cell, Object> cellValueFunc) {
        this.typeName = typeName;
        this.sqlTypeName = sqlTypeName;
        this.cellValueFunc = cellValueFunc;
    }
    public static Optional<JavaFileTypeEnum> of(String typeName) {
        return Arrays
                .stream(values())
                .filter(type -> StringUtils.equalsIgnoreCase(typeName, type.getTypeName()))
                .findFirst();
    }
    public static SqlTypeName findSqlTypeName(String typeName) {
        final Optional<JavaFileTypeEnum> javaFileTypeOptional = of(typeName);
        if (javaFileTypeOptional.isPresent()) {
            return javaFileTypeOptional
                    .get()
                    .getSqlTypeName();
        }
        return SqlTypeName.UNKNOWN;
    }
    public Object getCellValue(Cell cell) {
        return cellValueFunc.apply(cell);
    }
    public static Function<Cell, Object> getValueWithUnknown() {
        return cell -> {
            if (ObjectUtils.isEmpty(cell)) {
                return null;
            }
            switch (cell.getCellType()) {
                case STRING:
                    return cell.getStringCellValue();
                case NUMERIC:
                    if (DateUtil.isCellDateFormatted(cell)) {
                        // 如果是日期類型，返回日期對象
                        return cell.getDateCellValue();
                    }
                    else {
                        // 否則返回?cái)?shù)值
                        return cell.getNumericCellValue();
                    }
                case BOOLEAN:
                    return cell.getBooleanCellValue();
                case FORMULA:
                    // 對于公式單元格，先計(jì)算公式結(jié)果，再獲取其值
                    try {
                        return cell.getNumericCellValue();
                    }
                    catch (Exception e) {
                        try {
                            return cell.getStringCellValue();
                        }
                        catch (Exception ex) {
                            log.error("parse unknown data error, cellRowIndex:{}, cellColumnIndex:{}", cell.getRowIndex(), cell.getColumnIndex(), e);
                            return null;
                        }
                    }
                case BLANK:
                    return "";
                default:
                    return null;
            }
        };
    }
    public static Function<Cell, Object> getValueWithDate() {
        return cell -> {
            Date date = cell.getDateCellValue();
            if(ObjectUtils.isEmpty(date)) {
                return null;
            }
            try {
                final String formated = new SimpleDateFormat("yyyy-MM-dd").format(date);
                Date newDate = TIME_FORMAT_DATE.parse(formated);
                return (int) (newDate.getTime() / DateTimeUtils.MILLIS_PER_DAY);
            }
            catch (ParseException e) {
                log.error("parse date error, date:{}", date, e);
            }
            return null;
        };
    }
    public static Function<Cell, Object> getValueWithTimestamp() {
        return cell -> {
            Date date = cell.getDateCellValue();
            if(ObjectUtils.isEmpty(date)) {
                return null;
            }
            try {
                final String formated = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(date);
                Date newDate = TIME_FORMAT_TIMESTAMP.parse(formated);
                return (int) newDate.getTime();
            }
            catch (ParseException e) {
                log.error("parse timestamp error, date:{}", date, e);
            }
            return null;
        };
    }
    public static Function<Cell, Object> getValueWithTime() {
        return cell -> {
            Date date = cell.getDateCellValue();
            if(ObjectUtils.isEmpty(date)) {
                return null;
            }
            try {
                final String formated = new SimpleDateFormat("HH:mm:ss").format(date);
                Date newDate = TIME_FORMAT_TIME.parse(formated);
                return newDate.getTime();
            }
            catch (ParseException e) {
                log.error("parse time error, date:{}", date, e);
            }
            return null;
        };
    }
}

該枚舉類主要管理了java type& sql type & cell value convert func, 方便統(tǒng)一管理類型映射及單元格內(nèi)容提取時(shí)的轉(zhuǎn)換方法(這里借用了java8 function函數(shù)特性)

注: 這里的日期轉(zhuǎn)換只能這樣寫, 即使用GMT的時(shí)區(qū)(抄的calcite-file), 要不然輸出的日期時(shí)間一直有時(shí)差...

6. 測試查詢

package com.ldx.calcite;
import com.ldx.calcite.excel.ExcelSchemaFactory;
import lombok.SneakyThrows;
import lombok.extern.slf4j.Slf4j;
import org.apache.calcite.config.CalciteConnectionProperty;
import org.apache.calcite.jdbc.CalciteConnection;
import org.apache.calcite.schema.Schema;
import org.apache.calcite.schema.SchemaPlus;
import org.apache.calcite.util.Sources;
import org.junit.jupiter.api.AfterAll;
import org.junit.jupiter.api.BeforeAll;
import org.junit.jupiter.api.Test;
import org.testng.collections.Maps;
import java.net.URL;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Map;
import java.util.Properties;
@Slf4j
public class CalciteExcelTest {
    private static Connection connection;
    private static SchemaPlus rootSchema;
    private static CalciteConnection calciteConnection;
    @BeforeAll
    @SneakyThrows
    public static void beforeAll() {
        Properties info = new Properties();
        // 不區(qū)分sql大小寫
        info.setProperty(CalciteConnectionProperty.CASE_SENSITIVE.camelName(), "false");
        // 創(chuàng)建Calcite連接
        connection = DriverManager.getConnection("jdbc:calcite:", info);
        calciteConnection = connection.unwrap(CalciteConnection.class);
        // 構(gòu)建RootSchema，在Calcite中，RootSchema是所有數(shù)據(jù)源schema的parent，多個(gè)不同數(shù)據(jù)源schema可以掛在同一個(gè)RootSchema下
        rootSchema = calciteConnection.getRootSchema();
    }
    @Test
    @SneakyThrows
    public void test_execute_query() {
        final Schema schema = ExcelSchemaFactory.INSTANCE.create(resourcePath("file/test.xlsx"));
        rootSchema.add("test", schema);
        // 設(shè)置默認(rèn)的schema
        calciteConnection.setSchema("test");
        final Statement statement = calciteConnection.createStatement();
        ResultSet resultSet = statement.executeQuery("SELECT * FROM user_info");
        printResultSet(resultSet);
        System.out.println("=========");
        ResultSet resultSet2 = statement.executeQuery("SELECT * FROM test.user_info where id > 110 and birthday > '2003-01-01'");
        printResultSet(resultSet2);
        System.out.println("=========");
        ResultSet resultSet3 = statement.executeQuery("SELECT * FROM test.user_info ui inner join  test.role_info ri on ui.role_id = ri.id");
        printResultSet(resultSet3);
    }
    @AfterAll
    @SneakyThrows
    public static void closeResource() {
        connection.close();
    }
    private static String resourcePath(String path) {
        final URL url = CalciteExcelTest.class.getResource("/" + path);
        return Sources.of(url).file().getAbsolutePath();
    }
    public static void printResultSet(ResultSet resultSet) throws SQLException {
        // 獲取 ResultSet 元數(shù)據(jù)
        ResultSetMetaData metaData = resultSet.getMetaData();
        // 獲取列數(shù)
        int columnCount = metaData.getColumnCount();
        log.info("Number of columns: {}",columnCount);
        // 遍歷 ResultSet 并打印結(jié)果
        while (resultSet.next()) {
            final Map<String, String> item = Maps.newHashMap();
            // 遍歷每一列并打印
            for (int i = 1; i <= columnCount; i++) {
                String columnName = metaData.getColumnName(i);
                String columnValue = resultSet.getString(i);
                item.put(columnName, columnValue);
            }
            log.info(item.toString());
        }
    }
}

測試結(jié)果如下: