Many resources are needed to download a project. Please understand that we have to compensate our server costs. Thank you in advance. Project price only 1 $
You can buy this project and download/modify it how often you want.
/* ====================================================================
Licensed to the Apache Software Foundation (ASF) under one or more
contributor license agreements. See the NOTICE file distributed with
this work for additional information regarding copyright ownership.
The ASF licenses this file to You under the Apache License, Version 2.0
(the "License"); you may not use this file except in compliance with
the License. You may obtain a copy of the License at
http://www.apache.org/licenses/LICENSE-2.0
Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.
==================================================================== */
package org.apache.poi.ss.excelant.util;
import java.io.File;
import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.formula.functions.FreeRefFunction;
import org.apache.poi.ss.formula.udf.AggregatingUDFFinder;
import org.apache.poi.ss.formula.udf.DefaultUDFFinder;
import org.apache.poi.ss.formula.udf.UDFFinder;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellValue;
import org.apache.poi.ss.usermodel.FormulaError;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.tools.ant.BuildException;
import org.apache.tools.ant.Project;
import org.apache.tools.ant.taskdefs.Typedef;
/**
* A general utility class that abstracts the POI details of loading the
* workbook, accessing and updating cells.
*
* @author Jon Svede (jon [at] loquatic [dot] com)
* @author Brian Bush (brian [dot] bush [at] nrel [dot] gov)
*
*/
public class ExcelAntWorkbookUtil extends Typedef {
private String excelFileName;
private Workbook workbook;
private final Map xlsMacroList = new HashMap();
/**
* Constructs an instance using a String that contains the fully qualified
* path of the Excel file. This constructor initializes a Workbook instance
* based on that file name.
*
* @param fName The fully qualified path of the Excel file.
* @throws BuildException If the workbook cannot be loaded.
*/
protected ExcelAntWorkbookUtil(String fName) {
excelFileName = fName;
loadWorkbook();
}
/**
* Constructs an instance based on a Workbook instance.
*
* @param wb The Workbook to use for this instance.
*/
protected ExcelAntWorkbookUtil(Workbook wb) {
workbook = wb;
}
/**
* Loads the member variable workbook based on the fileName variable.
* @return The opened Workbook-instance
* @throws BuildException If the workbook cannot be loaded.
*/
private Workbook loadWorkbook() {
if (excelFileName == null) {
throw new BuildException("fileName attribute must be set!", getLocation());
}
try {
FileInputStream fis = new FileInputStream(excelFileName);
try {
workbook = WorkbookFactory.create(fis);
} finally {
fis.close();
}
} catch(Exception e) {
throw new BuildException("Cannot load file " + excelFileName
+ ". Make sure the path and file permissions are correct.", e);
}
return workbook;
}
/**
* Used to add a UDF to the evaluator.
* @param name
* @param clazzName
* @throws ClassNotFoundException
* @throws InstantiationException
* @throws IllegalAccessException
*/
public void addFunction(String name, String clazzName) throws ClassNotFoundException, InstantiationException, IllegalAccessException {
Class clazzInst = Class.forName(clazzName);
Object newInst = clazzInst.newInstance();
if(newInst instanceof FreeRefFunction) {
addFunction(name, (FreeRefFunction)newInst);
}
}
/**
* Updates the internal HashMap of functions with instance and alias passed
* in.
*
* @param name
* @param func
*/
protected void addFunction(String name, FreeRefFunction func) {
xlsMacroList.put(name, func);
}
/**
* returns a UDFFinder that contains all of the functions added.
*
* @return
*/
protected UDFFinder getFunctions() {
String[] names = new String[xlsMacroList.size()];
FreeRefFunction[] functions = new FreeRefFunction[xlsMacroList.size()];
int x = 0;
for(Map.Entry entry : xlsMacroList.entrySet()) {
names[x] = entry.getKey();
functions[x] = entry.getValue();
}
UDFFinder udff1 = new DefaultUDFFinder(names, functions);
UDFFinder udff = new AggregatingUDFFinder(udff1);
return udff;
}
/**
* Returns a formula evaluator that is loaded with the functions that
* have been supplied.
*
* @param fileName
* @return
*/
protected FormulaEvaluator getEvaluator(String fileName) {
FormulaEvaluator evaluator;
if (fileName.endsWith(".xlsx")) {
if(xlsMacroList.size() > 0) {
evaluator = XSSFFormulaEvaluator.create((XSSFWorkbook) workbook,
null,
getFunctions());
}
evaluator = new XSSFFormulaEvaluator((XSSFWorkbook) workbook);
} else {
if(xlsMacroList.size() > 0) {
evaluator = HSSFFormulaEvaluator.create((HSSFWorkbook)workbook,
null,
getFunctions());
}
evaluator = new HSSFFormulaEvaluator((HSSFWorkbook) workbook);
}
return evaluator;
}
/**
* Returns the Workbook instance associated with this WorkbookUtil.
*
* @return
*/
public Workbook getWorkbook() {
return workbook;
}
/**
* Returns the fileName that was used to initialize this instance. May
* return null if the instance was constructed from a Workbook object.
*
* @return
*/
public String getFileName() {
return excelFileName;
}
/**
* Returns the list of sheet names.
*
* @return
*/
public List getSheets() {
ArrayList sheets = new ArrayList();
int sheetCount = workbook.getNumberOfSheets();
for(int x=0; x precision) {
evalResults = new ExcelAntEvaluationResult(false, false,
resultOfEval.getNumberValue(),
"Results was out of range based on precision " + " of "
+ precision + ". Delta was actually " + delta, delta, cellName);
} else {
evalResults = new ExcelAntEvaluationResult(false, true,
resultOfEval.getNumberValue(),
"Evaluation passed without error within in range.", delta, cellName);
}
} else {
String errorMeaning = null;
try {
errorMeaning = FormulaError.forInt(resultOfEval.getErrorValue()).getString();
} catch(IllegalArgumentException iae) {
errorMeaning = "unknown error code: " +
Byte.toString(resultOfEval.getErrorValue());
}
evalResults = new ExcelAntEvaluationResult(true, false,
resultOfEval.getNumberValue(),
"Evaluation failed due to an evaluation error of "
+ resultOfEval.getErrorValue()
+ " which is "
+ errorMeaning, 0, cellName);
}
return evalResults;
}
/**
* Returns a Cell as a String value.
*
* @param cellName
* @return
*/
public String getCellAsString(String cellName) {
Cell cell = getCell(cellName);
return cell.getStringCellValue();
}
/**
* Returns the value of the Cell as a double.
*
* @param cellName
* @return
*/
public double getCellAsDouble(String cellName) {
Cell cell = getCell(cellName);
return cell.getNumericCellValue();
}
/**
* Returns a cell reference based on a String in standard Excel format
* (SheetName!CellId). This method will create a new cell if the
* requested cell isn't initialized yet.
*
* @param cellName
* @return
*/
private Cell getCell(String cellName) {
CellReference cellRef = new CellReference(cellName);
String sheetName = cellRef.getSheetName();
Sheet sheet = workbook.getSheet(sheetName);
if(sheet == null) {
throw new BuildException("Sheet not found: " + sheetName);
}
int rowIdx = cellRef.getRow();
int colIdx = cellRef.getCol();
Row row = sheet.getRow(rowIdx);
if(row == null) {
row = sheet.createRow(rowIdx);
}
Cell cell = row.getCell(colIdx);
if(cell == null) {
cell = row.createCell(colIdx);
}
return cell;
}
}
