/**
* 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;
}
/**
* 转换excel中的所有sheet
*
* @param xmlFile
* @return
*/
public boolean allSheetToXml(String xmlFile) {
for (int i = 0; i < workBook.getNumberOfSheets(); i++) {
int result = excelToXml(xmlFile + "_" + (i + 1), i);
if (result == 0) {
System.out.println("转换出错,程序退出！");
return false;
}
if (result == 2) {
Util.msgToLog("error.log", xmlFile + "_" + (i + 1) + ".xml" + " 转换出错！");
System.out.println(xmlFile + "_" + (i + 1) + ".xml" + " 转换出错！");
}
}
return true;
}
/**
* 将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;
}