/**
* * Transforms the contents of a given cell from the POI-model to a {@link Formula} and sets the
* formula as content of the given cell from the internal model.
*
* @param cell The given cell from the internal model.
* @param poiCell The given cell form the POI-model.
*/
protected void transformFormulaContent(Cell cell, org.apache.poi.ss.usermodel.Cell poiCell) {
// Create formula and add to cell.
Formula formula = new Formula();
cell.setFormula(formula);
formula.setCell(cell);
formula.setFormulaString(poiCell.getCellFormula());
// Set formula result type.
switch (poiCell.getCachedFormulaResultType()) {
case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC:
formula.setResultType(CellContentType.NUMERIC);
cell.setNumericContent(poiCell.getNumericCellValue());
break;
case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_STRING:
formula.setResultType(CellContentType.TEXT);
cell.setTextContent(poiCell.getStringCellValue());
break;
case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_BOOLEAN:
formula.setResultType(CellContentType.BOOLEAN);
cell.setBooleanContent(poiCell.getBooleanCellValue());
break;
case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_ERROR:
formula.setResultType(CellContentType.ERROR);
cell.setErrorContent(FormulaError.forInt(poiCell.getErrorCellValue()).toString());
break;
default:
break;
}
// Set formula content.
Stack<Ptg> ptgs = getPtgStackFor(poiCell);
transform(cell, ptgs, formula, ptgs.size());
}
/**
* 由于Excel当中的单元格Cell存在类型,若获取类型错误就会产生异常, 所以通过此方法将Cell内容全部转换为String类型
*
* @param cell
* @return
*/
public static String getCellValue(Cell cell) {
String str = null;
if (cell != null) {
switch (cell.getCellType()) {
case Cell.CELL_TYPE_BLANK:
str = "";
break;
case Cell.CELL_TYPE_BOOLEAN:
str = String.valueOf(cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_FORMULA:
str = String.valueOf(cell.getCellFormula());
break;
case Cell.CELL_TYPE_NUMERIC:
if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) {
str = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss").format(cell.getDateCellValue());
} else {
str = String.valueOf((long) cell.getNumericCellValue());
}
break;
case Cell.CELL_TYPE_STRING:
str = String.valueOf(cell.getStringCellValue());
break;
default:
str = null;
break;
}
}
return StringUtils.trim(str);
}
public static void copyCell(Cell oldCell, Cell newCell, boolean copyStyle) {
if (copyStyle) {
newCell.setCellStyle(oldCell.getCellStyle());
}
switch (oldCell.getCellType()) {
case Cell.CELL_TYPE_STRING:
newCell.setCellValue(oldCell.getRichStringCellValue());
break;
case Cell.CELL_TYPE_NUMERIC:
newCell.setCellValue(oldCell.getNumericCellValue());
break;
case Cell.CELL_TYPE_BLANK:
newCell.setCellType(Cell.CELL_TYPE_BLANK);
break;
case Cell.CELL_TYPE_BOOLEAN:
newCell.setCellValue(oldCell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_ERROR:
newCell.setCellErrorValue(oldCell.getErrorCellValue());
break;
case Cell.CELL_TYPE_FORMULA:
newCell.setCellFormula(oldCell.getCellFormula());
break;
default:
break;
}
}
/** 把单元格内的类型转换至String类型 */
private String ConvertCellStr(Cell cell, String cellStr) {
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
// 读取String
cellStr = cell.getStringCellValue().toString();
break;
case Cell.CELL_TYPE_BOOLEAN:
// 得到Boolean对象的方法
cellStr = String.valueOf(cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_NUMERIC:
// 先看是否是日期格式
if (DateUtil.isCellDateFormatted(cell)) {
// 读取日期格式
cellStr = cell.getDateCellValue().toString();
} else {
// 读取数字
cellStr = String.valueOf(cell.getNumericCellValue());
}
break;
case Cell.CELL_TYPE_FORMULA:
// 读取公式
cellStr = cell.getCellFormula().toString();
break;
}
return cellStr;
}
/**
* Watch list serve for formula changes. Basically all the rows appeared in the formula in the
* current sheet will be watched. Note if the cell reference is from other sheet or workbooks, it
* will be ignored.
*
* @param wbWrapper XSSFEvaluationWorkbook used for formula parse.
* @param sheet current sheet.
* @return List row number for monitoring.
*/
private List<Integer> buildFormWatchList(
final XSSFEvaluationWorkbook wbWrapper, final Sheet sheet) {
List<Integer> watchList = new ArrayList<Integer>();
ConfigRange cRange = this.getConfigRange();
List<ConfigCommand> commandList = cRange.getCommandList();
if (commandList.size() <= 0) {
// if no command then no dynamic changes. then no need formula shifts.
return watchList;
}
int lastStaticRow = commandList.get(0).getTopRow() - 1;
if (lastStaticRow < 0) {
lastStaticRow = this.getTopRow();
}
Workbook wb = sheet.getWorkbook();
for (int i = this.getTopRow(); i <= this.getLastRow(); i++) {
Row row = sheet.getRow(i);
for (Cell cell : row) {
if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
String formula = cell.getCellFormula();
Ptg[] ptgs =
FormulaParser.parse(formula, wbWrapper, FormulaType.CELL, wb.getSheetIndex(sheet));
for (int k = 0; k < ptgs.length; k++) {
Object ptg = ptgs[k];
// For area formula, only first row is watched.
// Reason is the lastRow must shift same rows with
// firstRow.
// Otherwise it's difficult to calculate.
// In case some situation cannot fit, then should make
// change to the formula.
int areaInt = ShiftFormula.getFirstSupportedRowNumFromPtg(ptg);
if (areaInt >= 0) {
addToWatchList(sheet, areaInt, lastStaticRow, watchList);
}
}
// when insert row, the formula may changed. so here is the workaround.
// change formula to user formula to preserve the row changes.
cell.setCellType(Cell.CELL_TYPE_STRING);
cell.setCellValue(
ConfigurationHelper.USER_FORMULA_PREFIX
+ formula
+ ConfigurationHelper.USER_FORMULA_SUFFIX);
}
}
}
return watchList;
}
public void printWorksheetContent() throws Exception {
FrontDesk desk = FrontDesk.getInstance();
// read policy & rule
DynamicPolicy policy = SifMarshaller.unmarshal(new File(policyPath));
@SuppressWarnings("unchecked")
DynamicInspectionRequest<Workbook> req =
(DynamicInspectionRequest<Workbook>)
desk.requestNewDynamicInspection("fubar", new File(filepath));
FrontDesk.getInstance().scan();
FrontDesk.getInstance().register(policy);
FrontDesk.getInstance().setPolicy(policy);
DynamicPolicyRule rule = (DynamicPolicyRule) policy.getRuleByName("greaterThan0");
assertTrue("No rule with name \"greaterThan0\" was found", rule != null);
// write TestInput in PoiWorkbook
POIWriter writer = new POIWriter();
writer.insertTestInput(rule, req.getExternalSpreadsheet());
Workbook wb = req.getExternalSpreadsheet();
Sheet sheet1 = wb.getSheetAt(0);
for (Row row : sheet1) {
for (Cell cell : row) {
CellReference cellRef = new CellReference(row.getRowNum(), cell.getColumnIndex());
System.out.print(row.getRowNum() + ", " + cell.getColumnIndex());
System.out.print(" - ");
System.out.print(cellRef.formatAsString());
System.out.print(" - ");
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
System.out.println(cell.getRichStringCellValue().getString());
break;
case Cell.CELL_TYPE_NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
System.out.println(cell.getDateCellValue());
} else {
System.out.println(cell.getNumericCellValue());
}
break;
case Cell.CELL_TYPE_BOOLEAN:
System.out.println(cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_FORMULA:
System.out.println(cell.getCellFormula());
break;
default:
System.out.println();
}
}
}
}
/** Verify that FormulaParser handles defined names beginning with underscores, see Bug #49640 */
public void testFormulasWithUnderscore() {
HSSFWorkbook wb = new HSSFWorkbook();
Name nm1 = wb.createName();
nm1.setNameName("_score1");
nm1.setRefersToFormula("A1");
Name nm2 = wb.createName();
nm2.setNameName("_score2");
nm2.setRefersToFormula("A2");
Sheet sheet = wb.createSheet();
Cell cell = sheet.createRow(0).createCell(2);
cell.setCellFormula("_score1*SUM(_score1+_score2)");
assertEquals("_score1*SUM(_score1+_score2)", cell.getCellFormula());
}
/**
* * Gets the formula content of a given cell from the POI-model as a stack of ptgs.The Ptgs are
* in reverse polish notation.
*
* @param poiCell The given cell from the POI-model.
* @return The contents of the given cell as a stack of ptgs.
*/
private Stack<Ptg> getPtgStackFor(org.apache.poi.ss.usermodel.Cell poiCell) {
// Parse POI-Formula to ptgs.
Ptg[] ptgArray =
FormulaParser.parse(
poiCell.getCellFormula(),
poiIO.formulaParsingWorkbook,
poiCell.getCellType(),
poiIO.poiWorkbook.getSheetIndex(poiCell.getSheet()));
// Create stack to keep the reverse polish notation and to ease the
// transformation.
Stack<Ptg> ptgs = new Stack<Ptg>();
for (Ptg ptg : ptgArray) {
ptgs.push(ptg);
}
return ptgs;
}
/**
* 取对应单元格类型的值
*
* @param c 列数
* @return 单元格的值
*/
private String getCellValue(Cell c) {
String o = null;
switch (c.getCellType()) {
case Cell.CELL_TYPE_BLANK:
o = "";
break;
case Cell.CELL_TYPE_BOOLEAN:
o = String.valueOf(c.getBooleanCellValue());
break;
case CELL_TYPE_FORMULA:
o = String.valueOf(c.getCellFormula());
break;
case Cell.CELL_TYPE_NUMERIC:
if (HSSFDateUtil.isCellDateFormatted(c)) { // 处理日期格式、时间格式
SimpleDateFormat sdf;
if (c.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("h:mm")) {
sdf = new SimpleDateFormat("HH:mm");
} else { // 日期
sdf = new SimpleDateFormat("yyyy-MM-dd");
}
Date date = c.getDateCellValue();
o = sdf.format(date).equals("1899-12-31") ? "" : sdf.format(date);
} else if (c.getCellStyle().getDataFormat() == 58) {
// 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
double value = c.getNumericCellValue();
Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value);
o = sdf.format(date);
}
break;
case Cell.CELL_TYPE_STRING:
o = c.getStringCellValue();
break;
default:
o = null;
break;
}
return o;
}
/**
* 获取单元格值
*
* @param row 获取的行
* @param column 获取单元格列号
* @return 单元格值
*/
public Object getCellValue(Row row, int column) {
Object val = "";
try {
Cell cell = row.getCell(column);
if (cell != null) {
if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
val = cell.getNumericCellValue();
} else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
val = cell.getStringCellValue();
} else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
val = cell.getCellFormula();
} else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
val = cell.getBooleanCellValue();
} else if (cell.getCellType() == Cell.CELL_TYPE_ERROR) {
val = cell.getErrorCellValue();
}
}
} catch (Exception e) {
return val;
}
return val;
}
/**
* 过滤 Cell 内容，返回字符串.
*
* @param c
* @return
*/
private static String filter(Cell c) {
int type = c.getCellType();
switch (type) {
case Cell.CELL_TYPE_BLANK:
return "";
case Cell.CELL_TYPE_BOOLEAN:
return String.valueOf(c.getBooleanCellValue());
case Cell.CELL_TYPE_STRING:
{
RichTextString text = c.getRichStringCellValue();
return text.getString();
}
case Cell.CELL_TYPE_FORMULA:
{
return c.getCellFormula();
}
case Cell.CELL_TYPE_NUMERIC:
{
return String.valueOf(c.getNumericCellValue());
}
}
return "no-value";
}
/**
* 获得指定位置的值，返回object，可为数值，字符串，布尔类型，null类型
*
* @param column
* @return
*/
public Object getCellValueObject(int rowNum, int column) {
// 定义返回的数组
Object tempObject = null;
row = sheet.getRow(rowNum);
cell = row.getCell(column);
// 判断值类型
switch (cell.getCellType()) {
// 字符串类型
case Cell.CELL_TYPE_STRING:
tempObject = cell.getRichStringCellValue().getString();
// System.out.println(cell.getRichStringCellValue().getString());
break;
// 数值类型
case Cell.CELL_TYPE_NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
tempObject = cell.getDateCellValue();
// System.out.println(cell.getDateCellValue());
} else {
tempObject = cell.getNumericCellValue();
// System.out.println(cell.getNumericCellValue());
}
break;
// 布尔类型
case Cell.CELL_TYPE_BOOLEAN:
tempObject = cell.getBooleanCellValue();
// System.out.println(cell.getBooleanCellValue());
break;
// 数学公式类型
case Cell.CELL_TYPE_FORMULA:
tempObject = cell.getCellFormula();
// System.out.println(cell.getCellFormula());
break;
default:
System.out.println();
}
return tempObject;
}
public static List<List<List<String>>> readExcel(File file, Rule rule) {
int start = rule.getStart();
int end = rule.getEnd();
List<List<List<String>>> result = Lists.newArrayList();
Workbook wb;
try {
wb = WorkbookFactory.create(file);
} catch (Exception e) {
throw new ExcelException(e);
}
for (int i = 0; i < wb.getNumberOfSheets(); i++) {
Sheet sheet = wb.getSheetAt(i);
List<List<String>> sheetList = Lists.newArrayList();
int rows = sheet.getLastRowNum();
if (start <= sheet.getFirstRowNum()) {
start = sheet.getFirstRowNum();
}
if (end >= rows) {
end = rows;
} else if (end <= 0) {
end = rows + end;
}
for (int rowIndex = start; rowIndex <= end; rowIndex++) {
Row row = sheet.getRow(rowIndex);
List<String> columns = Lists.newArrayList();
int cellNum = row.getLastCellNum();
System.out.println(row.getLastCellNum());
System.out.println(row.getPhysicalNumberOfCells());
for (int cellIndex = row.getFirstCellNum(); cellIndex < cellNum; cellIndex++) {
Cell cell = row.getCell(cellIndex);
int cellType = cell.getCellType();
String column = "";
switch (cellType) {
case Cell.CELL_TYPE_NUMERIC:
// DecimalFormat format = new DecimalFormat();
// format.setGroupingUsed(false);
column = String.valueOf(cell.getDateCellValue());
break;
case Cell.CELL_TYPE_STRING:
column = cell.getStringCellValue();
break;
case Cell.CELL_TYPE_BOOLEAN:
column = cell.getBooleanCellValue() + "";
break;
case Cell.CELL_TYPE_FORMULA:
column = cell.getCellFormula();
break;
case Cell.CELL_TYPE_ERROR:
case Cell.CELL_TYPE_BLANK:
column = " ";
break;
default:
}
columns.add(column.trim());
}
List<Boolean> rowFilterFlagList = Lists.newArrayList();
List<RowFilter> rowFilterList = Lists.newArrayList();
for (int k = 0; k < rowFilterList.size(); k++) {
RowFilter rowFilter = rowFilterList.get(k);
rowFilterFlagList.add(rowFilter.doFilter(rowIndex, columns));
}
if (!rowFilterFlagList.contains(false)) {
sheetList.add(columns);
}
}
result.add(sheetList);
}
return result;
}
/** Adds in a Row to the given Sheet */
public Row addRow(
Workbook wb,
SheetToAdd sheetToAdd,
RowToAdd rowToAdd,
int rowIndex,
ReportData reportData,
ReportDesign design,
Map<String, String> repeatSections) {
// Create a new row and copy over style attributes from the row to add
Row newRow = sheetToAdd.getSheet().createRow(rowIndex);
Row rowToClone = rowToAdd.getRowToClone();
try {
CellStyle rowStyle = rowToClone.getRowStyle();
if (rowStyle != null) {
newRow.setRowStyle(rowStyle);
}
} catch (Exception e) {
// No idea why this is necessary, but this has thrown IndexOutOfBounds errors getting the
// rowStyle. Mysteries of POI
}
newRow.setHeight(rowToClone.getHeight());
// Iterate across all of the cells in the row, and configure all those that need to be
// added/cloned
List<CellToAdd> cellsToAdd = new ArrayList<CellToAdd>();
int totalCells = rowToClone.getPhysicalNumberOfCells();
int cellsFound = 0;
for (int cellNum = 0; cellsFound < totalCells; cellNum++) {
Cell currentCell = rowToClone.getCell(cellNum);
log.debug("Handling cell: " + currentCell);
if (currentCell != null) {
cellsFound++;
}
// If we find that the cell that we are on is a repeating cell, then add the appropriate
// number of cells to clone
String repeatingColumnProperty =
getRepeatingColumnProperty(sheetToAdd.getOriginalSheetNum(), cellNum, repeatSections);
if (repeatingColumnProperty != null) {
String[] dataSetSpanSplit = repeatingColumnProperty.split(",");
String dataSetName = dataSetSpanSplit[0];
DataSet dataSet = getDataSet(reportData, dataSetName, rowToAdd.getReplacementData());
int numCellsToRepeat = 1;
if (dataSetSpanSplit.length == 2) {
numCellsToRepeat = Integer.parseInt(dataSetSpanSplit[1]);
}
log.debug(
"Repeating this cell with dataset: " + dataSet + " and repeat of " + numCellsToRepeat);
int repeatNum = 0;
for (DataSetRow dataSetRow : dataSet) {
repeatNum++;
for (int i = 0; i < numCellsToRepeat; i++) {
Cell cell = (i == 0 ? currentCell : rowToClone.getCell(cellNum + i));
if (repeatNum == 1 && cell != null && cell != currentCell) {
cellsFound++;
}
Map<String, Object> newReplacements =
getReplacementData(
rowToAdd.getReplacementData(),
reportData,
design,
dataSetName,
dataSetRow,
repeatNum);
cellsToAdd.add(new CellToAdd(cell, newReplacements));
log.debug("Adding " + cell + " with dataSetRow: " + dataSetRow);
}
}
cellNum += numCellsToRepeat;
} else {
cellsToAdd.add(new CellToAdd(currentCell, rowToAdd.getReplacementData()));
log.debug("Adding " + currentCell);
}
}
// Now, go through all of the collected cells, and add them back in
ExcelStyleHelper styleHelper = new ExcelStyleHelper(wb);
String prefix = getExpressionPrefix(design);
String suffix = getExpressionSuffix(design);
for (int i = 0; i < cellsToAdd.size(); i++) {
CellToAdd cellToAdd = cellsToAdd.get(i);
Cell newCell = newRow.createCell(i);
Cell cellToClone = cellToAdd.getCellToClone();
if (cellToClone != null) {
String contents = ExcelUtil.getCellContentsAsString(cellToClone);
newCell.setCellStyle(cellToClone.getCellStyle());
try {
newCell.setCellFormula(cellToClone.getCellFormula());
} catch (Exception e) {
// Do nothing here. I don't know why POI throw exceptions here when the cell is not a
// formula, but this suppresses them...
}
int numFormattings =
sheetToAdd.getSheet().getSheetConditionalFormatting().getNumConditionalFormattings();
for (int n = 0; n < numFormattings; n++) {
ConditionalFormatting f =
sheetToAdd.getSheet().getSheetConditionalFormatting().getConditionalFormattingAt(n);
for (CellRangeAddress add : f.getFormattingRanges()) {
if (add.getFirstRow() == rowToAdd.getRowToClone().getRowNum()
&& add.getLastRow() == rowToClone.getRowNum()) {
if (add.getFirstColumn() == cellToClone.getColumnIndex()
&& add.getLastColumn() == cellToClone.getColumnIndex()) {
ConditionalFormattingRule[] rules =
new ConditionalFormattingRule[f.getNumberOfRules()];
for (int j = 0; j < f.getNumberOfRules(); j++) {
rules[j] = f.getRule(j);
}
CellRangeAddress[] cellRange = new CellRangeAddress[1];
cellRange[0] = new CellRangeAddress(rowIndex, rowIndex, i, i);
sheetToAdd
.getSheet()
.getSheetConditionalFormatting()
.addConditionalFormatting(cellRange, rules);
}
}
}
}
if (ObjectUtil.notNull(contents)) {
Object newContents =
EvaluationUtil.evaluateExpression(
contents, cellToAdd.getReplacementData(), prefix, suffix);
ExcelUtil.setCellContents(styleHelper, newCell, newContents);
}
}
}
return newRow;
}
