datasheet/Datasheet.cfc at master · adrianblynch/datasheet · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
component {

	/*
		Version: 1.0
		Author: Adrian Lynch - www.adrianlynch.co.uk
	*/

	function init(path, url) localmode="modern" {

		/*
			@path - The path to an xls(x) file
			@url - The url to an xls(x) file
		*/

		if (!isNull(arguments.path)) {
			// TODO: Look at using File over FileInputStream as it consumes less memory: http://poi.apache.org/spreadsheet/quick-guide.html#FileInputStrea
			inputStream = createObject("java", "java.io.FileInputStream").init(arguments.path);
		} else if (!isNull(arguments.url)) {
			inputStream = createObject("java", "java.net.URL").init(arguments.url).openStream();
		} else {
			throw "No input file specified. Please supply either a path or a URL to an XLS(X) file.";
		}

		variables.workbook = createObject("java", "org.apache.poi.ss.usermodel.WorkbookFactory").create(inputStream);
		variables.cellPolicy = "RETURN_NULL_AND_BLANK";

		return this;

	}

	function asArrays() localmode="modern" {

		arrays = [];

		i = 1;
		for (i = 1; i <= workbook.getNumberOfSheets(); i++) {

			sheet = workbook.getSheetAt(i - 1);
			arrays.append([]);
			highestCellIndex = getHighestCellIndex(sheet);

			for (j = 0; j <= sheet.getLastRowNum(); j++) {

				row = sheet.getRow(j);
				arrays[i].append([]);


				/*
					BUG: https://issues.apache.org/bugzilla/show_bug.cgi?id=30635
					Row.getLastCellNum() can report the wrong number.
					The first thought of checking for null might not work when we
					want to deal with nulls.
				*/

				// Why does this loop run once, putting a null cell in when it shouldn't - Maybe
				for (k = 0; k <= highestCellIndex; k++) {

					if (!isNull(row)) {
						cell = row.getCell(k, row[cellPolicy]);
						arrays[i][j + 1].append(getCellValue(cell));
					} else {
						arrays[i][j + 1].append(null);
					}

				}

			}

		}

		return arrays

	}

	function getHighestCellIndex(sheet) localmode="modern" {

		// To include null data when cells are skipped, get the highest cell index.

		rows = sheet.rowIterator(); // Excludes null rows - Which is OK
		highestIndex = 0;

		while (rows.hasNext()) {
			row = rows.next();
			highestIndex = max(highestIndex, row.getLastCellNum() - 1); // See comment about bug above in asArrays()
		}

		return highestIndex;

	}

	function getCellValue(cell) localmode="modern" {

		if (isNull(cell)) {
			return null;
		}

		cellType = cell.getCellType();

		if (cellType EQ cell.CELL_TYPE_NUMERIC) {
			value = cell.getNumericCellValue();
		} else if (cellType EQ cell.CELL_TYPE_STRING) {
			value = cell.getStringCellValue();
		} else if (cellType EQ cell.CELL_TYPE_BOOLEAN) {
			value = cell.getBooleanCellValue();
		} else if (cellType EQ cell.CELL_TYPE_BLANK) {
			value = "";
		} else if (cellType EQ cell.CELL_TYPE_ERROR) {
			value = "";
		} else if (cellType EQ cell.CELL_TYPE_FORMULA) {
			value = "";
		}

		return value;

	}

}