Java tutorial
/* * Copyright (C) 2000 - 2011 TagServlet Ltd * * This file is part of Open BlueDragon (OpenBD) CFML Server Engine. * * OpenBD is free software: you can redistribute it and/or modify * it under the terms of the GNU General Public License as published by * Free Software Foundation,version 3. * * OpenBD is distributed in the hope that it will be useful, * but WITHOUT ANY WARRANTY; without even the implied warranty of * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the * GNU General Public License for more details. * * You should have received a copy of the GNU General Public License * along with OpenBD. If not, see http://www.gnu.org/licenses/ * * Additional permission under GNU GPL version 3 section 7 * * If you modify this Program, or any covered work, by linking or combining * it with any of the JARS listed in the README.txt (or a modified version of * (that library), containing parts covered by the terms of that JAR, the * licensors of this Program grant you additional permission to convey the * resulting work. * README.txt @ http://www.openbluedragon.org/license/README.txt * * http://openbd.org/ * $Id: SpreadsheetQueryRead.java 2108 2012-06-07 00:32:22Z alan $ */ package org.alanwilliamson.openbd.plugin.spreadsheet.functions; import java.util.ArrayList; import java.util.Iterator; import java.util.List; import java.util.Map; import org.alanwilliamson.openbd.plugin.spreadsheet.SheetUtility; import org.alanwilliamson.openbd.plugin.spreadsheet.cfSpreadSheetData; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellValue; import org.apache.poi.ss.usermodel.FormulaEvaluator; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import com.nary.util.FastMap; import com.naryx.tagfusion.cfm.engine.cfArgStructData; import com.naryx.tagfusion.cfm.engine.cfBooleanData; import com.naryx.tagfusion.cfm.engine.cfData; import com.naryx.tagfusion.cfm.engine.cfNumberData; import com.naryx.tagfusion.cfm.engine.cfQueryResultData; import com.naryx.tagfusion.cfm.engine.cfSession; import com.naryx.tagfusion.cfm.engine.cfStringData; import com.naryx.tagfusion.cfm.engine.cfmRunTimeException; import com.naryx.tagfusion.cfm.tag.tagUtils; import com.naryx.tagfusion.expression.function.functionBase; public class SpreadsheetQueryRead extends functionBase { private static final long serialVersionUID = 1L; public SpreadsheetQueryRead() { min = 2; max = 6; setNamedParams(new String[] { "spreadsheet", "sheet", "headerrow", "columnnames", "columns", "rows" }); } public String[] getParamInfo() { return new String[] { "spreadsheet object", "the sheet number to be active; the first sheet starts at 0", "the row in the spreadsheet that is to be considered the header for the query (only HEADERROW or COLUMNNAMES must be present)", "the comma-separated names for the column names (only HEADERROW or COLUMNNAMES must be present)", "the columns to be used in the format (1,2,3,5-6); defaults to all", "the rows to be used in the format (1,2,3,5-6); defaults to all" }; } public java.util.Map getInfo() { return makeInfo("spreadsheet-plugin", "Reads the given sheet from the spreadsheet object as a query. If there are formulae in the cells they will be evaluated", ReturnType.QUERY); } public cfData execute(cfSession _session, cfArgStructData argStruct) throws cfmRunTimeException { cfSpreadSheetData spreadsheet = (cfSpreadSheetData) getNamedParam(argStruct, "spreadsheet", null); if (spreadsheet == null) throwException(_session, "spreadsheet object not given"); //How many columns are we using int[] columnsToUse; String columns = getNamedStringParam(argStruct, "columns", null); if (columns != null) { int x = 0; List<Integer> numbers = tagUtils.getNumberListSorted(columns); columnsToUse = new int[numbers.size()]; Iterator<Integer> numbersIT = numbers.iterator(); while (numbersIT.hasNext()) columnsToUse[x++] = (numbersIT.next() - 1); } else { int maxColumns = SheetUtility.getMaxColumn(spreadsheet.getActiveSheet()); columnsToUse = new int[maxColumns]; for (int x = 0; x < maxColumns; x++) columnsToUse[x] = x; } //Figure out the columns String columnLabels[] = null; int startRow = 0; String columnnames = getNamedStringParam(argStruct, "columnnames", null); int headerRow = getNamedIntParam(argStruct, "headerrow", -1); FormulaEvaluator evaluator = spreadsheet.getWorkBook().getCreationHelper().createFormulaEvaluator(); if (columnnames != null) { columnLabels = (columnnames.split("\\s*,\\s*")); if (columnLabels.length != columnsToUse.length) throwException(_session, "The COLUMNNAMES does not match the number of columns"); } else if (headerRow > -1) { headerRow = headerRow - 1; Row row = spreadsheet.getActiveSheet().getRow(headerRow); if (row == null) throwException(_session, "The HEADERROW does not exist"); columnLabels = new String[columnsToUse.length]; for (int c = 0; c < columnsToUse.length; c++) { Cell cell = row.getCell(columnsToUse[c]); if (cell == null) columnLabels[c] = ""; else columnLabels[c] = cell.toString(); } startRow = headerRow + 1; } else { columnLabels = new String[columnsToUse.length]; for (int x = 0; x < columnLabels.length; x++) columnLabels[x] = "Column " + (columnsToUse[x] + 1); } //Create the query cfQueryResultData queryData = new cfQueryResultData(columnLabels, "SpreadSheet"); List<Map<String, cfData>> vResults = new ArrayList<Map<String, cfData>>(); int sheetNo = getNamedIntParam(argStruct, "sheet", -1); if (sheetNo != -1) spreadsheet.setActiveSheet(sheetNo); Sheet sheet = spreadsheet.getActiveSheet(); Row row; Cell cell; cfData cfdata; String rowsSt = getNamedStringParam(argStruct, "rows", null); if (rowsSt != null) { List<Integer> rows = tagUtils.getNumberListSorted(rowsSt); Iterator<Integer> rowsIT = rows.iterator(); while (rowsIT.hasNext()) { int r = rowsIT.next() - 1; Map<String, cfData> hm = new FastMap<String, cfData>(); if ((row = sheet.getRow(r)) == null) continue; for (int c = 0; c < columnsToUse.length; c++) { cell = row.getCell(columnsToUse[c]); if (cell == null) cfdata = new cfStringData(""); else { if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) cfdata = cfBooleanData.getcfBooleanData(cell.getBooleanCellValue()); else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) cfdata = new cfNumberData(cell.getNumericCellValue()); else if (cell.getCellType() == Cell.CELL_TYPE_BLANK) cfdata = new cfStringData(""); else if (cell.getCellType() == Cell.CELL_TYPE_STRING) cfdata = new cfStringData(cell.getStringCellValue()); else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) { CellValue cellValue = evaluator.evaluate(cell); switch (cellValue.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: cfdata = cfBooleanData.getcfBooleanData(cellValue.getBooleanValue()); break; case Cell.CELL_TYPE_NUMERIC: cfdata = new cfNumberData(cellValue.getNumberValue()); break; case Cell.CELL_TYPE_STRING: cfdata = new cfStringData(cellValue.getStringValue()); break; default: cfdata = cfStringData.EMPTY_STRING; break; } } else cfdata = new cfStringData(""); } hm.put(columnLabels[c], cfdata); } vResults.add(hm); } } else { /* * Read __ALL__ the rows associated with this spreadsheet */ for (int r = startRow; r < sheet.getLastRowNum() + 1; r++) { Map<String, cfData> hm = new FastMap<String, cfData>(); if ((row = sheet.getRow(r)) == null) continue; for (int c = 0; c < columnsToUse.length; c++) { cell = row.getCell(columnsToUse[c]); if (cell == null) cfdata = new cfStringData(""); else { if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) cfdata = cfBooleanData.getcfBooleanData(cell.getBooleanCellValue()); else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) cfdata = new cfNumberData(cell.getNumericCellValue()); else if (cell.getCellType() == Cell.CELL_TYPE_BLANK) cfdata = new cfStringData(""); else if (cell.getCellType() == Cell.CELL_TYPE_STRING) cfdata = new cfStringData(cell.getStringCellValue()); else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) { CellValue cellValue = evaluator.evaluate(cell); switch (cellValue.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: cfdata = cfBooleanData.getcfBooleanData(cellValue.getBooleanValue()); break; case Cell.CELL_TYPE_NUMERIC: cfdata = new cfNumberData(cellValue.getNumberValue()); break; case Cell.CELL_TYPE_STRING: cfdata = new cfStringData(cellValue.getStringValue()); break; default: cfdata = cfStringData.EMPTY_STRING; break; } } else cfdata = new cfStringData(""); } hm.put(columnLabels[c], cfdata); } vResults.add(hm); } } queryData.populateQuery(vResults); return queryData; } }