All geek questions in one place

Reading a column format using cfspreadsheet

Is it possible to get the data type or format of a column or cell in a spreadsheet when reading it with cfspreadsheet ?

I am converting table data from an Excel table to a database table. So far, I just formatted everything as varchars, but it would be nice if I could specify dates as dates and integers as integers.

+2
source share
1 answer

Unfortunately, do not use cfspreadsheet or the built-in spreadsheet functions. They return only what is displayed, not the base values. However, you can topple your own by clicking on the basic POI workbook .

A few things to keep in mind:

  • Unlike database tables, spreadsheet columns can contain a combination of data types. Just because the first cell contains a date does not guarantee that all the cells in this column contain dates either. As with any import, be sure to return all values ​​before inserting them into the database table.
  • These methods include only filled rows and cells. Blank lines and cells are skipped. Column values ​​are not always contiguous.
  • Indexes of sheets, rows and columns are zero (0)

To do the processing, just grab the sheet you want and iterate through the rows and cells. As you go through the columns, check the cell type and retrieve the raw value (e.g. date, row, number, ...)

Source: Busy Developer Guide for HSSF and 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>
+3
source

Source: https://habr.com/ru/post/1240941/

More articles:


All Articles