使用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屋！