package frame.file.office.excel;
|
|
import java.io.File;
|
import java.io.IOException;
|
import java.io.InputStream;
|
import java.util.ArrayList;
|
import java.util.Iterator;
|
import java.util.List;
|
|
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.xml.sax.ContentHandler;
|
import org.xml.sax.InputSource;
|
import org.xml.sax.SAXException;
|
import org.xml.sax.XMLReader;
|
import org.xml.sax.helpers.XMLReaderFactory;
|
|
import frame.file.AppendMode;
|
import frame.file.DeleteMode;
|
import frame.file.FileIO;
|
import frame.file.FileIOContainer;
|
import frame.file.FileIOContext;
|
import frame.file.FileIOItem;
|
import frame.file.UploadResult;
|
import frame.file.office.FileRecord;
|
import frame.file.office.IOMappingItemRuntime;
|
import frame.persist.NamedSQL;
|
import frame.persist.SQLRunner;
|
import frame.upload.FileItem;
|
import frame.util.Util;
|
|
public class ExcelLoader {
|
|
private Logger logger;
|
private static int Count_Batch = 5000;
|
private UploadResult result;
|
private long count;
|
|
private XMLReader parser;
|
private InputSource sheetSource;
|
private int index = 0;
|
private SheetHandler handler;
|
ArrayList<String> cloumnList;
|
public ExcelLoader(Logger logger) {
|
this.logger = logger;
|
}
|
|
public boolean loadFiles(UploadResult result,String ioCode, List<FileItem> fiList) throws Exception {
|
//TODO 多sheet 或者说 多file 组成一次task
|
for (FileItem fileItem : fiList) {
|
loadOneFile(result,ioCode, fileItem);
|
InputStream inputStream = fileItem.getInputStream();
|
OPCPackage pkg = OPCPackage.open(inputStream);
|
XSSFReader xssfReader = new XSSFReader(pkg);
|
SharedStringsTable sharedStringsTable = xssfReader.getSharedStringsTable();
|
StylesTable stylesTable = xssfReader.getStylesTable();
|
sheet = xssfReader.getSheet("rId1");
|
parser = fetchSheetParser(sharedStringsTable, stylesTable);
|
sheetSource = new InputSource(sheet);
|
}
|
return true;
|
}
|
|
private XMLReader fetchSheetParser(SharedStringsTable sharedStringsTable, StylesTable stylesTable) throws SAXException {
|
XMLReader parser = XMLReaderFactory.createXMLReader("org.apache.xerces.parsers.SAXParser");
|
handler = new SheetHandler(sharedStringsTable, stylesTable){
|
|
@Override
|
protected void outputRow(String[] datas, int[] rowTypes, int rowIndex, FileIOItem fileIOItem) {
|
//TODO 读取到每一行数据的时候 多行 表头 怎么取
|
if (rowIndex == 0) {
|
cloumnList = new ArrayList<String>();
|
}
|
else if (fileIOItem.getFromRowNo() > (rowIndex - 1)) {
|
|
for(int i = 0; i < datas.length; i++) {
|
String oneCloumn = datas[i];
|
cloumnList.add(oneCloumn);
|
}
|
}
|
|
}
|
};
|
parser.setContentHandler(handler);
|
|
return parser;
|
}
|
|
public void loadOneFile(UploadResult result, String ioCode, FileItem fileItem) throws Exception {
|
this.result = result;
|
|
//FileRecord fileRecord = new FileRecord(fileItem);
|
//1. repository
|
//2
|
FileIO fileIO = FileIOContainer.get(ioCode);
|
|
if (fileIO == null) {
|
logger.error("can not find fileIO: " + ioCode);
|
result.fail("error_IONotExist", "上载定义没有配置");
|
return;
|
}
|
|
//1. fire before process
|
fileIO.execHandlers(result, "begin");
|
|
//2. import file
|
doLoadOneFile(result, fileItem, fileIO);
|
|
//3. move to standard table
|
processData(result, fileIO);
|
|
//4. after save
|
fileIO.execHandlers(result, "end");
|
|
//5. backup
|
//fileRecord.moveToBackup();
|
}
|
|
public void testDataProcess(UploadResult result, String ioCode) {
|
FileIO fileIO = FileIOContainer.get(ioCode);
|
|
if (fileIO == null) {
|
logger.error("can not find fileIO: " + ioCode);
|
result.fail("error_IONotExist", "上载定义没有配置");
|
return;
|
}
|
|
processData(result, fileIO);
|
}
|
|
private void doLoadOneFile(UploadResult result, FileItem fileItem, FileIO fileIO) {
|
logger.info("正在导入文件: " + fileItem.getName() + "...");
|
|
List<FileIOItem> ioItemList = fileIO.getItemList(FileIOItem.TypeCode_Import);
|
|
//1. delete temp table data
|
for (FileIOItem ioItem : ioItemList) {
|
String toName = ioItem.getToName();
|
|
NamedSQL namedSql = NamedSQL.getInstance("emptyTable");
|
namedSql.setParam("tablename", toName);
|
SQLRunner.execSQL(namedSql);
|
}
|
|
//2.
|
|
try {
|
|
for (FileIOItem ioItem : ioItemList) {
|
readOneSheet(fileItem, ioItemList);
|
ioItem.execHandlers(result);
|
}
|
}
|
catch (Exception e) {
|
logger.error(e.getMessage());
|
result.fail("error_parseData", "文件解析错误： " + e.getMessage());
|
}
|
finally {
|
excelConn.Close();
|
}
|
}
|
|
private void readOneSheet(FileItem fileItem, List<FileIOItem> ioItemList) {
|
InputStream inputStream = fileItem.getInputStream();
|
OPCPackage pkg = OPCPackage.open(inputStream);
|
XSSFReader xssfReader = new XSSFReader(pkg);
|
SharedStringsTable sharedStringsTable = xssfReader.getSharedStringsTable();
|
StylesTable stylesTable = xssfReader.getStylesTable();
|
parser = fetchSheetParser(sharedStringsTable, stylesTable);
|
XSSFReader.SheetIterator sheetsDataIterator = (SheetIterator) xssfReader.getSheetsData();
|
|
while (sheetsDataIterator.hasNext()) {
|
InputStream sheetData = sheetsDataIterator.next();
|
String sheetName = sheetsDataIterator.getSheetName();
|
|
for (FileIOItem fileIOItem : ioItemList) {
|
String fromName = fileIOItem.getFromName();
|
if (fromName.equalsIgnoreCase(sheetName)) {
|
//TODO
|
}
|
}
|
|
InputSource inputSource = new InputSource(sheetData);
|
parser.parse(inputSource);
|
sheetData.close();
|
}
|
|
//1. get sheet
|
String sheetname = getSheetName(excelConn, ioItemList);
|
|
//2. create mapping runtime
|
List<String> excelColumns = getExcelFields(sheetname);
|
ioItemList.createMappingRuntime(excelColumns, new FileIOContext(result, ioItemList));
|
|
//3. create select sql
|
String excelDataString = createSelectSql(sheetname, ioItemList);
|
OleDbCommand command = new OleDbCommand(excelDataString, excelConn);
|
|
//4. read data
|
OleDbDataReader dataReader = command.ExecuteReader();
|
try {
|
SqlBulkCopy bulkCopy = null;
|
try {
|
bulkCopy = new SqlBulkCopy(SQLRunner.connStr);
|
|
bulkCopy.DestinationTableName = ioItemList.getToName();
|
|
List<IOMappingItemRuntime> insertMappingRuntime = ioItemList.getInsertMappingRuntime();
|
for (IOMappingItemRuntime itemRuntime : insertMappingRuntime) {
|
bulkCopy.ColumnMappings.Add(itemRuntime.getFromField(), itemRuntime.getToField());
|
}
|
|
bulkCopy.BatchSize = Count_Batch;
|
bulkCopy.NotifyAfter = Count_Batch;
|
bulkCopy.SqlRowsCopied += new SqlRowsCopiedEventHandler(onBatchCopyedEvent);
|
|
bulkCopy.WriteToServer(dataReader);
|
}
|
finally {
|
if (bulkCopy != null) {
|
bulkCopy.Close();
|
}
|
}
|
}
|
finally {
|
dataReader.Close();
|
}
|
}
|
|
private void processData(UploadResult result, FileIO fileIO) {
|
try {
|
List<FileIOItem> itemList = fileIO.getItemList(FileIOItem.TypeCode_DB);
|
|
//0. init
|
for (FileIOItem item : itemList) {
|
FileIOContext context = (FileIOContext)(item.createContext(result));
|
item.createMappingRuntime(null, context);
|
}
|
|
//1. process temple data
|
for (FileIOItem ioItem : itemList) {
|
ioItem.execHandlers(result, "beforemove");
|
}
|
|
if (!result.isSuccess()) {
|
return;
|
}
|
|
//2. move data to standard table
|
for (FileIOItem ioItem : itemList) {
|
if (ioItem.isStandardMove()) {
|
transferData(result, ioItem);
|
continue;
|
}
|
else {
|
ioItem.execHandlers(result, "move");
|
}
|
}
|
|
if (!result.isSuccess()) {
|
return;
|
}
|
|
//3. process standard table
|
for (FileIOItem ioItem : itemList) {
|
ioItem.execHandlers(result, "aftermove");
|
}
|
|
if (!result.isSuccess()) {
|
return;
|
}
|
}
|
catch (Exception e) {
|
logger.error(result.workingSQL.getSQLString());
|
|
e.printStackTrace();
|
throw e;
|
}
|
}
|
|
private void transferData(UploadResult result, FileIOItem ioItem) throws Exception {
|
FileIOContext context = (FileIOContext) (ioItem.getContext());
|
|
AppendMode appendMode = ioItem.getAppendMode();
|
DeleteMode deleteMode = ioItem.getDeleteMode();
|
int cnt = 0;
|
|
if (AppendMode.Append == appendMode) {
|
cnt = execute("transferAll", context);
|
result.addMessage("插入数据：" + cnt);
|
}
|
else if (AppendMode.ClearAndAppend == appendMode) {
|
//1. clear data
|
if (ioItem.existsFilterFieldValues()) {
|
cnt = execute("deleteByFilter", context);
|
}
|
else {
|
cnt = execute("deleteAll", context);
|
}
|
result.addMessage("删除数据：" + cnt);
|
|
//2. transfer data
|
cnt = execute("transferAll", context);
|
result.addMessage("插入数据：" + cnt);
|
}
|
else if (AppendMode.UpdateChanged == appendMode) {
|
//1. delete not exists data
|
if (DeleteMode.HardDelete == deleteMode) {
|
cnt = execute("hardDeleteNotExists", context);
|
result.addMessage("删除数据：" + cnt);
|
}
|
else if (DeleteMode.SoftDelete == deleteMode) {
|
cnt = execute("softDeleteNotExists", context);
|
result.addMessage("软删除数据：" + cnt);
|
}
|
|
//2. update exists data
|
cnt = execute("updateExists", context);
|
result.addMessage("更新数据：" + cnt);
|
|
//3. insert append data
|
cnt = execute("insertAppend", context);
|
result.addMessage("插入数据：" + cnt);
|
}
|
else if (AppendMode.InsertChanged == appendMode) {
|
//1. update exists data
|
cnt = execute("updateExists", context);
|
result.addMessage("更新数据：" + cnt);
|
|
//2. insert append data
|
cnt = execute("insertAppend", context);
|
result.addMessage("插入数据：" + cnt);
|
}
|
}
|
|
private int execute(String sqlName, FileIOContext context) throws Exception {
|
NamedSQL namedSQL = NamedSQL.getInstance(sqlName);
|
context.setParametersTo(namedSQL);
|
return SQLRunner.execSQL(namedSQL);
|
}
|
|
private void onBatchCopyedEvent(object sender, SqlRowsCopiedEventArgs e) {
|
count = count + e.RowsCopied;
|
logger.info("已导入" + count + "条...");
|
}
|
|
private String getSheetName(OleDbConnection excelConn, FileIOItem ioItem) {
|
DataTable sheetNames = excelConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new String[] { null, null, null, "Table" });
|
|
String designname = FileIOItem.extractSheetName(ioItem.getFromName()).toLowerCase();
|
List<String> sheetNameList = new ArrayList<String>();
|
|
//1.
|
for (int i = 0; i < sheetNames.Rows.Count; i++) {
|
String sheetname = sheetNames.Rows[i][2].ToString();
|
|
String lower = sheetname.trim().toLowerCase();
|
if (designname.equals(lower)) {
|
return sheetname;
|
}
|
|
sheetNameList.add(lower);
|
}
|
|
//2.
|
if (designname.indexOf("$") >= 0) {
|
designname = designname.replace("$", "sheet");
|
String lower = designname.trim().toLowerCase();
|
|
if (!"$".equals(lower.substring(lower.length() - 1))) {
|
lower = lower + "$";
|
}
|
|
for (int i = sheetNameList.size() - 1; i >= 0; i--) {
|
String sheetname = sheetNameList.get(i);
|
|
|
|
if (sheetname.equals(lower)) {
|
return sheetname;
|
}
|
}
|
}
|
|
//3.
|
for (int i = sheetNameList.size() - 1; i >= 0; i--) {
|
String sheetname = sheetNameList.get(i);
|
|
if ("$".equals(sheetname.substring(sheetname.length() - 1))) {
|
return sheetname;
|
}
|
}
|
|
return null;
|
}
|
|
private List<String> getExcelFields(String sheetname) {
|
List<String> result = new ArrayList<String>();
|
|
DataTable columns = excelConn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new Object[] { null, null, sheetname, null });
|
|
for (int i = 0; i < columns.Rows.Count; i++) {
|
String columnname = columns.Rows[i].ItemArray[3].ToString();
|
result.add(columnname);
|
}
|
|
return result;
|
}
|
|
private String createSelectSql(String sheetname, FileIOItem ioItem) {
|
StringBuilder result = new StringBuilder();
|
|
List<IOMappingItemRuntime> itemRuntimes = ioItem.getInsertMappingRuntime();
|
|
result.append("Select ");
|
|
Boolean empty = true;
|
|
for (IOMappingItemRuntime itemRuntime : itemRuntimes) {
|
if (!empty) {
|
result.append(", ");
|
}
|
|
result.append("[");
|
result.append(itemRuntime.getFromField());
|
result.append("]");
|
|
empty = false;
|
}
|
|
sheetname = Util.deleteQuoted(sheetname);
|
result.append(" FROM [").append(sheetname).append("]");
|
|
return result.toString();
|
}
|
|
public void getExcelMeta() {
|
String excelConnString = String.Format(Configer.readExcelConnString, "D:/工作/root/local/NPT sales YTD 2014-2_A.xlsx");
|
logger.write("正在导入文件...");
|
|
OleDbConnection excelConn = new OleDbConnection(excelConnString);
|
try {
|
excelConn.Open();
|
logger.write("正在打开文件，读取数据...");
|
DataTable datatable = excelConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new String[] { null, null, null, "Table" });
|
|
int cnt = datatable.Rows.Count;
|
int fieldCnt = datatable.Columns.Count;
|
|
for (int i = 0; i < cnt; i++) {
|
DataRow row = datatable.Rows[i];
|
|
String value = "";
|
for (int j = 0; j < fieldCnt; j++) {
|
value = value + ", " + row[j].ToString();
|
}
|
|
logger.write(value);
|
}
|
}
|
finally {
|
excelConn.Close();
|
}
|
}
|
|
}
|
