@Override
public String getPriceDate(String fileName) {
try {
HSSFSheet sheet = getSheet(fileName, 0);
String marker = "Дата";
int numOfRows = sheet.getPhysicalNumberOfRows() > 10 ? 10 : sheet.getPhysicalNumberOfRows();
String dirtyDate = null;
for (int i = priceConfig.getRowToStart(Brand.STELS); i < numOfRows; i++) {
Row row = sheet.getRow(i);
if (row.getPhysicalNumberOfCells() > 1) {
Cell cell = row.getCell(0);
if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
String stringCellValue = cell.getStringCellValue();
if (stringCellValue.contains(marker)) {
dirtyDate = stringCellValue;
break;
}
}
}
}
if (dirtyDate != null) {
return dirtyDate.trim();
}
} catch (Exception ex) {
LOGGER.error("FAIL to get price date - return today");
return new Date().toString();
}
return new Date().toString();
}
/**
* get the specified excel sheet and put its value string to list.
*
* @param sheetName excel sheet name
* @param ignoreRows first several rows not to read.
* @param ignoreCols first several cols not to read.
* @param readRows specified row count to read.
* @param readColumns specified column count to read.
* @throws RuntimeException
*/
public List<String> excelToList(
String sheetName, int ignoreRows, int ignoreCols, int readRows, int readColumns) {
FileInputStream fso = null;
List<String> paraList = new ArrayList<String>();
try {
fso = new FileInputStream(fileName);
Workbook workBook = getWorkBook(fso, true);
xlSheet = workBook.getSheet(sheetName);
if (xlSheet == null) {
LOG.error("sheet [" + sheetName + "] does not exist!");
throw new RuntimeException("sheet [" + sheetName + "] does not exist!");
}
readRows = (readRows == 0) ? xlSheet.getPhysicalNumberOfRows() : readRows;
for (int i = ignoreRows; i < ignoreRows + readRows; i++) {
xlRow = xlSheet.getRow(i);
readColumns = (readColumns == 0) ? xlRow.getPhysicalNumberOfCells() : readColumns;
if (xlRow != null) {
for (int j = ignoreCols; j < ignoreCols + readColumns; j++) {
xlCell = xlRow.getCell(j);
if (xlCell == null) {
paraList.add("");
} else {
paraList.add(xlCell.toString());
}
}
}
}
fso.close();
} catch (Exception e) {
LOG.error(e);
throw new RuntimeException("read excel failed:" + e.getMessage());
}
return paraList;
}
public static void generateTrainingDataFromFile(
String
fileLocation) // Requires that the original file had the metadata and requires that this
// file is formated the same in first sheet
{
testDataLL = (LinkedList<String[]>) dataLL.clone();
actualClassifications = (LinkedList<String>) classificationsLL.clone();
FileInputStream file;
try {
file = new FileInputStream(new File(fileLocation));
Workbook excelFile = new HSSFWorkbook(file);
Sheet sheet1 = excelFile.getSheetAt(0); // Data sheet
for (Row row : sheet1) {
String data[] = new String[row.getPhysicalNumberOfCells() - 1];
String classification = "";
int offset =
0; // Used so that we can declare an array of the size of the attributes without the
// classification
for (Cell cell : row) {
int index = cell.getColumnIndex();
if (classificationLocation != index) {
data[index - offset] = cell.toString();
} else {
classification = cell.toString();
offset++;
}
}
// Even though data and classifications are not really used add it onto the end so it is
// still complete for in the event they end up being used in a later version
dataLL.add(data);
classificationsLL.add(classification);
trainingDataLL.add(data);
knownClassifications.add(classification);
// Check to see if we have seen that classification yet
int occurrences = 0;
for (int i = 0; i < classificationTypes.size() && occurrences == 0; i++) {
if (classificationTypes.get(i).compareTo(classification) == 0) {
occurrences = 1;
}
}
if (occurrences == 0) {
classificationTypes.add(classification);
}
}
excelFile.close();
} catch (FileNotFoundException e) {
System.out.println("Error file not found");
System.exit(0);
} catch (IOException e) {
System.out.println("Unable to read file, disk drive may be failing");
e.printStackTrace();
System.exit(0);
}
}
/**
* This method checks the row to see if it is a title row. It returns true if it is.
*
* @param oRow The row to be checked.
* @return TRUE if this is a title row.
*/
private static boolean isTitleRow(Row oRow) {
boolean bReturnValue = false;
if ((oRow != null) && (oRow.getPhysicalNumberOfCells() > 0)) {
Cell oCell = oRow.getCell(oRow.getFirstCellNum());
if ((oCell != null) && (oCell.getCellType() == Cell.CELL_TYPE_STRING)) {
if (TITLE_ROW_CELL_1_TEXT.equals(oCell.getStringCellValue())) {
bReturnValue = true;
}
}
}
return bReturnValue;
}
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;
}
/**
* 将excel内的内容读取到xml文件中，并添加dtd验证
*
* @param xmlFile
* @param sheetNum
* @return 1代表成功，0失败，-1超过最大sheet,2跳过当前失败的xml
*/
public int excelToXml(String xmlFile, int sheetNum) {
if (sheetNum >= workBook.getNumberOfSheets()) return -1;
else sheet = workBook.getSheetAt(sheetNum);
xmlFile = xmlFile + ".xml";
try {
Document document = DocumentHelper.createDocument();
// 使用sheet名称命名跟节点
String rootName = sheet.getSheetName().replaceAll(" ", "");
Element root = document.addElement(rootName);
// 添加dtd文件说明
DocumentType documentType = new DOMDocumentType();
documentType.setElementName(rootName);
List<ElementDecl> declList = new ArrayList<>();
declList.add(new ElementDecl(rootName, "(row*)"));
// 判断sheet是否为空,为空则不执行任何操作
if (sheet.getRow(0) == null) return 1;
// 遍历sheet第一行,获取元素名称
row = sheet.getRow(0);
String rowString = null;
List<String> pcdataList = new ArrayList<>();
for (int y = 0; y < row.getPhysicalNumberOfCells(); y++) {
Object object = this.getCellValueObject(0, y);
// 判断是否有合并单元格，有的话跳过
if (object == null) return 2;
// 去除表头字符串中的空格
String objectStr = object.toString().replaceAll(" ", "");
if (rowString != null) rowString += "|" + objectStr;
else rowString = objectStr;
pcdataList.add(objectStr);
}
// 设置行节点
declList.add(new ElementDecl("row", "(" + rowString + ")*"));
// 遍历list设置行的下级节点
for (String tmp : pcdataList) {
declList.add(new ElementDecl(tmp, "(#PCDATA)"));
}
documentType.setInternalDeclarations(declList);
// 遍历读写excel数据到xml中
for (int x = 1; x < sheet.getLastRowNum(); x++) {
row = sheet.getRow(x);
Element rowElement = root.addElement("row");
for (int y = 0; y < row.getPhysicalNumberOfCells(); y++) {
// cell = row.getCell(y);
Object object = this.getCellValueObject(x, y);
if (object != null) {
// 将sheet第一行的行首元素当作元素名称
String pcdataString = pcdataList.get(y);
Element element = rowElement.addElement(pcdataString);
// Element element = rowElement.addElement("name");
element.setText(object.toString());
}
}
}
// 写入文件和dtd
document.setDocType(documentType);
this.docToXmlFile(document, xmlFile);
} catch (Exception e) {
e.printStackTrace();
}
return 1;
}
public static void readExcelFile(String fileName) {
FileInputStream file;
try {
file = new FileInputStream(new File(fileName));
Workbook excelFile = new HSSFWorkbook(file);
Sheet sheet1 = excelFile.getSheetAt(0); // Data sheet
// Set just in case metadata is incomplete or malformed
classificationLocation =
sheet1.getRow(0).getPhysicalNumberOfCells()
- 1; // Minus one since classificationLocation includes 0 and getPhysicalNumberOfCells
// does not
Sheet sheet2 = excelFile.getSheetAt(1); // Metadata sheet
// Loop based on number of attribute names
for (int i = 0; i < sheet2.getRow(0).getPhysicalNumberOfCells(); i++) {
String[] metadata = new String[METADATASIZE];
// Construct metadata
Row currRow = sheet2.getRow(0); // This should be a row of names
metadata[0] = currRow.getCell(i).toString();
currRow = sheet2.getRow(1); // This should be a row of data types (discrete or continuous)
metadata[1] = currRow.getCell(i).toString();
currRow = sheet2.getRow(2); // This should say which one is the classifier
if (currRow.getCell(i) == null
|| currRow.getCell(i).getCellType() == Cell.CELL_TYPE_BLANK) {
metadata[2] = "attribute";
} else {
metadata[2] = "classifier";
classificationLocation = i;
}
metadataLL.add(metadata);
}
for (Row row : sheet1) {
String data[] = new String[row.getPhysicalNumberOfCells() - 1];
int offset =
0; // Used so that we can declare an array of the size of the attributes without the
// classification
for (Cell cell : row) {
int index = cell.getColumnIndex();
if (classificationLocation != index) {
data[index - offset] = cell.toString();
} else {
classificationsLL.add(cell.toString());
// Moved to generate training data so that we do not get possible classifications from
// unknown data since some denote unknown by saying ?
// //Check to see if we have seen it yet
//
// int occurrences = 0;
// for(int i = 0; i < classificationTypes.size(); i++)
// {
// if(classificationTypes.get(i).compareTo(cell.toString()) == 0)
// {
// occurrences++;
// }
// }
// if(occurrences == 0)
// {
// classificationTypes.add(cell.toString());
// }
offset++;
}
}
dataLL.add(data);
// classCount = temp.length;
}
excelFile.close();
} catch (FileNotFoundException e) {
System.out.println("Error file not found");
System.exit(0);
} catch (IOException e) {
System.out.println("Unable to read file, disk drive may be failing");
e.printStackTrace();
System.exit(0);
}
}
/** 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;
}
