package frame.file.office.excel;
|
|
import java.io.File;
|
import java.io.FileNotFoundException;
|
import java.io.IOException;
|
import java.io.InputStream;
|
import java.util.ArrayList;
|
import java.util.HashSet;
|
import java.util.List;
|
|
import javax.servlet.http.HttpServletRequest;
|
import javax.transaction.Transaction;
|
|
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
|
import org.apache.poi.openxml4j.exceptions.OpenXML4JException;
|
import org.apache.poi.openxml4j.opc.OPCPackage;
|
import org.apache.poi.xssf.eventusermodel.XSSFReader;
|
import org.apache.poi.xssf.eventusermodel.XSSFReader.SheetIterator;
|
import org.apache.poi.xssf.model.SharedStringsTable;
|
import org.apache.poi.xssf.model.StylesTable;
|
import org.slf4j.Logger;
|
import org.slf4j.LoggerFactory;
|
import org.xml.sax.InputSource;
|
import org.xml.sax.SAXException;
|
import org.xml.sax.XMLReader;
|
import org.xml.sax.helpers.XMLReaderFactory;
|
|
import frame.config.Configer;
|
import frame.file.AppendMode;
|
import frame.file.DeleteMode;
|
import frame.file.FileIO;
|
import frame.file.FileIOContext;
|
import frame.file.FileIOItem;
|
import frame.file.LoadError;
|
import frame.file.UploadResult;
|
import frame.file.office.FileIOTypeCode;
|
import frame.persist.NamedSQL;
|
import frame.persist.SQLRunner;
|
import frame.upload.FileItem;
|
import frame.upload.FileUploadException;
|
import frame.upload.disk.DiskFileItemFactory;
|
import frame.upload.servlet.ServletFileUpload;
|
|
public class ExcelLoaders {
|
|
private Logger logger;
|
private UploadResult result;
|
private HttpServletRequest request;
|
|
private static int MAX_CACHE_SIZE = 30 * 1024 * 1024;
|
private static int MAX_FILE_SIZE = 100 * 1024 * 1024;
|
|
public ExcelLoaders(HttpServletRequest request) {
|
this.logger = LoggerFactory.getLogger(this.getClass());
|
this.request = request;
|
}
|
|
public boolean load(UploadResult result, FileIO fileIO) throws Exception {
|
this.result = result;
|
boolean isSuccess = false;
|
List<FileIOItem> importList = fileIO.getItemList(FileIOTypeCode.Import);
|
|
for (FileIOItem fileIOItem : importList) {
|
fileIO.execHandlers(result, "XXbefore");
|
isSuccess = oneload(fileIOItem);
|
}
|
|
return isSuccess;
|
}
|
|
private boolean oneload(FileIOItem fileIOItem) throws Exception {
|
//1. delete temp table data
|
String toName = fileIOItem.getToName();
|
NamedSQL namedSql = NamedSQL.getInstance("emptyTable");
|
namedSql.setParam("tablename", toName);
|
SQLRunner.execSQL(namedSql);
|
|
List<FileItem> loadUploadFile = loadUploadFile(request);
|
//2. import file
|
boolean isEndFound = false;
|
for (int i = 0; i < loadUploadFile.size(); i++) {
|
FileItem fileItem = loadUploadFile.get(i);
|
LoadError loadOneError = doLoadOneFile(result, fileItem, fileIOItem);
|
|
if (loadOneError.equals(LoadError.SheetNotFound) && i < loadUploadFile.size() - 1) {
|
isEndFound = false;
|
}
|
|
if ((i == loadUploadFile.size() - 1) && !isEndFound) {
|
throw SheetNotFoundException(loadOneError.getMessage());
|
}
|
|
if (loadOneError.equals(LoadError.SQLRunError)) {
|
throw SQLrunnerException(loadOneError.getMessage());
|
}
|
|
}
|
|
return false;
|
}
|
|
private String transactionSheetName(String fromName) {
|
String sheetName = fromName;
|
int leftIndex = fromName.indexOf("[");
|
String sheetString = fromName.substring(0, leftIndex);
|
|
if (sheetString.equalsIgnoreCase("*")) {
|
sheetName = "rId";
|
}
|
|
int rightIndex = fromName.indexOf("]");
|
String noString = fromName.substring(leftIndex + 1, rightIndex);
|
|
try {
|
Integer no = Integer.valueOf(noString);
|
sheetName += no;
|
} catch (Exception e) {
|
e.printStackTrace();
|
throw new FileIOException("fileio 数据错误");
|
}
|
|
return sheetName;
|
}
|
|
private LoadError doLoadOneFile(UploadResult result, FileItem file, FileIOItem fileIOItem) {
|
logger.info("正在导入文件: " + file.getName() + "...");
|
LoadError loadError = null;
|
//1. delete temp table data
|
String toName = fileIOItem.getToName();
|
NamedSQL namedSql = null;
|
try {
|
namedSql = NamedSQL.getInstance("emptyTable");
|
namedSql.setParam("tablename", toName);
|
SQLRunner.execSQL(namedSql);
|
} catch (Exception e) {
|
e.printStackTrace();
|
loadError = LoadError.SQLRunError;
|
try {
|
loadError.setMessage("SQL:" + namedSql.getSQLString() + "-- " +loadError.name());
|
} catch (Exception e1) {
|
loadError.setMessage("SQL:" + namedSql.getName() + "-- not found");
|
e1.printStackTrace();
|
}
|
return loadError;
|
}
|
|
//2. load file
|
InputStream fileInputStream = null;
|
InputStream sheetInputStream = null;
|
try {
|
fileInputStream = file.getInputStream();
|
OPCPackage pkg = OPCPackage.open(fileInputStream);
|
|
XSSFReader xssfReader = new XSSFReader(pkg);
|
SharedStringsTable sharedStringsTable = xssfReader.getSharedStringsTable();
|
StylesTable stylesTable = xssfReader.getStylesTable();
|
|
String fromName = fileIOItem.getFromName();
|
String sheetName = transactionSheetName(fromName);
|
sheetInputStream = xssfReader.getSheet(sheetName);
|
|
if (sheetInputStream.available() == 0) {
|
//TODO 没找到本fileiotime对应的sheet 两个方向 多file中 后面file 找到了 还是说需要多个file都存在
|
|
return LoadError.SheetNotFound;
|
}
|
|
XMLReader parser = fetchSheetParser(sharedStringsTable, stylesTable);
|
InputSource sheetSource = new InputSource(sheetInputStream);
|
parser.parse(sheetSource);
|
|
fileIOItem.execHandlers(result);
|
}
|
catch (Exception e) {
|
logger.error(e.getMessage());
|
result.fail("error_parseData", "文件解析错误： " + e.getMessage());
|
}
|
finally {
|
if (fileInputStream != null) {
|
try {
|
fileInputStream.close();
|
} catch (IOException e) {
|
e.printStackTrace();
|
}
|
}
|
if (sheetInputStream != null) {
|
try {
|
sheetInputStream.close();
|
} catch (IOException e) {
|
e.printStackTrace();
|
}
|
}
|
}
|
return LoadError.Non;
|
}
|
|
private XMLReader fetchSheetParser(SharedStringsTable sharedStringsTable, StylesTable stylesTable) throws SAXException {
|
XMLReader parser = XMLReaderFactory.createXMLReader("org.apache.xerces.parsers.SAXParser");
|
SheetHandler handler = new SheetHandler(sharedStringsTable, stylesTable){
|
|
@Override
|
protected void outputRow(String[] datas, int[] rowTypes, int rowIndex, FileIOItem fileIOItem) {
|
HashSet<String> cloumnList = null;
|
if (rowIndex == 0) {
|
cloumnList = new HashSet<String>();
|
}
|
else if (fileIOItem.getFromRowNo() > (rowIndex - 1)) {
|
for(int i = 0; i < datas.length; i++) {
|
String oneCloumn = datas[i];
|
|
if(cloumnList == null) {
|
cloumnList = new HashSet<String>();
|
}
|
|
cloumnList.add(oneCloumn);
|
}
|
}
|
else {
|
//add one row
|
}
|
|
}
|
};
|
parser.setContentHandler(handler);
|
|
return parser;
|
}
|
|
@SuppressWarnings("unchecked")
|
private static List<FileItem> loadUploadFile(HttpServletRequest request) throws FileUploadException {
|
DiskFileItemFactory diskFileFactory = new DiskFileItemFactory();
|
diskFileFactory.setSizeThreshold(MAX_CACHE_SIZE);
|
diskFileFactory.setRepository(new File(Configer.getParam("repository")));
|
|
ServletFileUpload fileUpload = new ServletFileUpload(diskFileFactory);
|
fileUpload.setSizeMax(MAX_FILE_SIZE);
|
return fileUpload.parseRequest(request);
|
}
|
|
}
|
