使用 cfspreadsheet 读取列格式 [英] Reading column format with cfspreadsheet

问题描述

当您使用 cfspreadsheet 读取电子表格中的列或单元格时，是否可以获得数据类型或格式?

Is it possible to get the datatype or format of a column or cell in a spreadsheet when you read it using cfspreadsheet?

我正在将电子表格数据从 Excel 电子表格转换为数据库表.到目前为止，我只是将所有内容都格式化为 varchars，但如果我可以将日期指定为日期，将整数指定为整数，那就更好了.

I am converting spreadsheet data from an excel spreadsheet to a database table. Thus far I just format everything as varchars, but it would be good if I could specify dates as dates and integers as integers.

推荐答案

很遗憾，没有使用 cfspreadsheet 或内置的电子表格函数.它们只返回显示的内容，而不是基础值.但是，您可以通过利用底层 POI 工作簿来创建自己的.

Unfortunately, not using cfspreadsheet or the built in spreadsheet functions. They only return what is displayed, not the underlying values. However you could roll your own by tapping into the underlying POI workbook.

需要注意的几点:

• 与数据库表不同，电子表格列可以包含多种数据类型.仅仅因为第一个单元格包含日期并不能保证该列中的 所有 单元格也包含日期.因此，与任何导入一样，请务必在将所有值插入数据库表之前对其进行验证.
• 这些方法仅包含填充行和单元格.跳过空白行和单元格.所以列值并不总是连续的.
• 工作表、行和列索引从零 (0) 开始

• Unlike database tables, spreadsheet columns can contain a mix of data types. Just because the first cell contains a date is no guarantee all cells in that column contain dates too. So as with any import, be sure to validate all values before inserting them into your database table.
• These methods only include populated rows and cells. Blank rows and cells are skipped. So the column values are not always contiguous.
• Sheet, row and column indexes are zero (0) based

要进行处理，只需抓取所需的工作表并遍历行和单元格.当您遍历列时，检查单元格类型并提取原始值(即日期、字符串、数字......)

To do the processing, just grab the desired sheet and iterate through the rows and cells. As you loop through the columns, check the cell type and extract the raw value (ie date, string, number, ...)

来源:繁忙的 HSSF 和 XSSF 功能开发人员指南

<cfscript>
// get the sheet you want to read
cfSheet = SpreadSheetRead("c:/path/to/somefile.xls"); 
workbook = cfSheet.getWorkBook();
sheetIndex = workbook.getActiveSheetIndex();
sheet = workbook.getSheetAt( sheetIndex );

// utility used to distinguish between dates and numbers
dateUtil = createObject("java", "org.apache.poi.ss.usermodel.DateUtil");

// process the rows and columns
rows = sheet.rowIterator();
while (rows.hasNext()) {
    currentRow = rows.next();
    data = {}; 

    cells = currentRow.cellIterator();
    while (cells.hasNext()) { 
        currentCell = cells.next();

        col = {};
        col.value  = "";
        col.type   = "";
        col.column = currentCell.getColumnIndex()+ 1;
        col.row    = currentCell.getRowIndex()+ 1;

        if (currentCell.getCellType() EQ currentCell.CELL_TYPE_STRING) {
               col.value = currentCell.getRichStringCellValue().getString();
            col.type = "string";
        }
        else if (currentCell.getCellType() EQ currentCell.CELL_TYPE_NUMERIC) {
            if (DateUtil.isCellDateFormatted(currentCell)) {
                 col.value = currentCell.getDateCellValue();
                 col.type = "date";
            } 
            else {
                 col.value = currentCell.getNumericCellValue();
                 col.type = "number";
            }
        }
        else if (currentCell.getCellType() EQ currentCell.CELL_TYPE_BOOLEAN) {
            col.value = currentCell.getBooleanCellValue();
            col.type = "boolean";
        }
        // ... handle other types CELL_TYPE_BLANK, CELL_TYPE_ERROR, CELL_TYPE_FORMULA

        data["COL"& col.column] = col;
    }

    // this row is finished. display all values
    WriteDump(data);
}
</cfscript>

这篇关于使用 cfspreadsheet 读取列格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！