/**
* Import the model according to reversed dependency order among model objects: book, sheet,
* defined name, cells, chart, pictures, validation.
*/
@Override
public SBook imports(InputStream is, String bookName) throws IOException {
// clear cache for reuse
importedStyle.clear();
importedFont.clear();
workbook = createPoiBook(is);
book = SBooks.createBook(bookName);
// book.setDefaultCellStyle(importCellStyle(workbook.getCellStyleAt((short) 0), false));
// //ZSS-780
// ZSS-854
importDefaultCellStyles();
importNamedStyles();
// ZSS-1140
importExtraStyles();
setBookType(book);
// ZSS-715: Enforce internal Locale.US Locale so formula is in consistent internal format
Locale old = Locales.setThreadLocal(Locale.US);
SBookSeries bookSeries = book.getBookSeries();
boolean isCacheClean = bookSeries.isAutoFormulaCacheClean();
try {
bookSeries.setAutoFormulaCacheClean(false); // disable it to avoid
// unnecessary clean up
// during importing
importExternalBookLinks();
int numberOfSheet = workbook.getNumberOfSheets();
for (int i = 0; i < numberOfSheet; i++) {
Sheet poiSheet = workbook.getSheetAt(i);
importSheet(poiSheet, i);
SSheet sheet = book.getSheet(i);
importTables(poiSheet, sheet); // ZSS-855, ZSS-1011
}
importNamedRange();
for (int i = 0; i < numberOfSheet; i++) {
SSheet sheet = book.getSheet(i);
Sheet poiSheet = workbook.getSheetAt(i);
for (Row poiRow : poiSheet) {
importRow(poiRow, sheet);
}
importColumn(poiSheet, sheet);
importMergedRegions(poiSheet, sheet);
importDrawings(poiSheet, sheet);
importValidation(poiSheet, sheet);
importAutoFilter(poiSheet, sheet);
importSheetProtection(poiSheet, sheet); // ZSS-576
}
} finally {
book.getBookSeries().setAutoFormulaCacheClean(isCacheClean);
Locales.setThreadLocal(old);
}
return book;
}
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);
}
}
public List<Object> readExcel(Workbook wb, Class clz, int readLine, int tailLine) {
Sheet sheet = wb.getSheetAt(0); // 取第一张表
List<Object> objs = null;
try {
Row row = sheet.getRow(readLine); // 开始行，主题栏
objs = new ArrayList<Object>();
Map<Integer, String> maps = getHeaderMap(row, clz); // 设定对应的字段顺序与方法名
if (maps == null || maps.size() <= 0)
throw new RuntimeException("要读取的Excel的格式不正确，检查是否设定了合适的行"); // 与order顺序不符
for (int i = readLine + 1; i <= sheet.getLastRowNum() - tailLine; i++) { // 取数据
row = sheet.getRow(i);
Object obj = clz.newInstance(); // 调用无参结构
for (Cell c : row) {
int ci = c.getColumnIndex();
String mn = maps.get(ci).substring(3); // 消除get
mn = mn.substring(0, 1).toLowerCase() + mn.substring(1);
Map<String, Object> params = new HashMap<String, Object>();
if (!"enterDate".equals(mn)) c.setCellType(Cell.CELL_TYPE_STRING); // 设置单元格格式
else c.setCellType(Cell.CELL_TYPE_NUMERIC);
if (this.getCellValue(c).trim().equals("是")) {
BeanUtils.copyProperty(obj, mn, 1);
} else if (this.getCellValue(c).trim().equals("否")) {
BeanUtils.copyProperty(obj, mn, 0);
} else BeanUtils.copyProperty(obj, mn, this.getCellValue(c));
}
objs.add(obj);
}
} catch (InstantiationException e) {
e.printStackTrace();
logger.error(e);
} catch (IllegalAccessException e) {
e.printStackTrace();
logger.error(e);
} catch (InvocationTargetException e) {
e.printStackTrace();
logger.error(e);
} catch (NumberFormatException e) {
e.printStackTrace();
logger.error(e);
}
return objs;
}
/**
* 将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);
}
}
