From a9631bf0505ac256d276b060dc05d99ea971d877 Mon Sep 17 00:00:00 2001
From: david-PC\david <kimi42345@gmail.com>
Date: 星期二, 12 六月 2018 17:13:50 +0800
Subject: [PATCH] 修改了Engine的流程（第一次）
---
/dev/null | 277 ----------------------------------------------
source/my/frame/src/frame/file/office/Engine.java | 64 +++++++++-
source/my/frame/src/frame/file/FileIOContainer.java | 7 +
source/my/frame/src/frame/file/office/LoadManager.java | 8
4 files changed, 66 insertions(+), 290 deletions(-)
diff --git a/source/my/frame/src/frame/file/FileIOContainer.java b/source/my/frame/src/frame/file/FileIOContainer.java
index 2069bb9..4478e11 100644
--- a/source/my/frame/src/frame/file/FileIOContainer.java
+++ b/source/my/frame/src/frame/file/FileIOContainer.java
@@ -15,6 +15,7 @@
public class FileIOContainer {
+ public static boolean Debug = true;
private static FileIOContainer instance;
private static Object locker = new Object();
@@ -58,6 +59,12 @@
return instance.fileIOMap.get(ioCode);
}
+ public static void tryLoad() throws Exception {
+ if (Debug) {
+ load();
+ }
+ }
+
public static void load() throws Exception {
getInstance();
NamedSQL namedSQL;
diff --git a/source/my/frame/src/frame/file/LoadError.java b/source/my/frame/src/frame/file/LoadError.java
deleted file mode 100644
index f05e262..0000000
--- a/source/my/frame/src/frame/file/LoadError.java
+++ /dev/null
@@ -1,15 +0,0 @@
-package frame.file;
-
-public enum LoadError {
- SheetNotFound,SQLRunError,Non;
-
- private String message;
-
- public void setMessage(String message) {
- this.message = toString() + ": --" + message;
- }
-
- public String getMessage() {
- return null;
- }
-}
diff --git a/source/my/frame/src/frame/file/office/Engine.java b/source/my/frame/src/frame/file/office/Engine.java
index 2623e37..7eafd16 100644
--- a/source/my/frame/src/frame/file/office/Engine.java
+++ b/source/my/frame/src/frame/file/office/Engine.java
@@ -22,10 +22,16 @@
import frame.call.writer.JsonWriter;
import frame.config.Configer;
import frame.data.DataContext;
+import frame.file.FileIO;
import frame.file.FileIOContainer;
+import frame.file.FileIOErrorType;
+import frame.file.FileIOException;
+import frame.file.FileIOItem;
import frame.file.UploadResult;
+import frame.file.db.DataBaseTranslator;
import frame.file.office.excel.DownloadResult;
-import frame.file.office.excel.ExcelLoader;
+import frame.file.office.excel.ExcelReader;
+import frame.file.office.excel.ExcelReaders;
import frame.file.office.excel.ExcelWriter;
import frame.file.processor.Monitor;
import frame.upload.FileItem;
@@ -39,14 +45,14 @@
private static Engine instance;
private static Object locker = new Object();
private Logger logger;
- private ExcelLoader excelLoader;
+ private ExcelReader excelLoader;
private static int MAX_CACHE_SIZE = 30 * 1024 * 1024;
private static int MAX_FILE_SIZE = 100 * 1024 * 1024;
private Engine() {
logger = LoggerFactory.getLogger(this.getClass());
- excelLoader = new ExcelLoader(logger);
+ excelLoader = new ExcelReader(logger);
}
@@ -102,19 +108,28 @@
}
}
- public static UploadResult upload(String iocode, File file, HttpServletRequest request) throws Exception {
+ public static UploadResult upload(String ioCode, File file, HttpServletRequest request) throws Exception {
UploadResult result = new UploadResult(request);
result.writeBegin();
try {
+ //1. prepare
getInstance();
FileIOContainer.load();
-
- LoadManager manager = new LoadManager(result, iocode);
- manager.setRequest(request);
- instance.excelLoader.loadFiles(result, iocode, loadUploadFileList);
-// instance.excelLoader.loadOneFile(result, iocode, file);
+ //2.
+ if (Util.isEmptyStr(ioCode)) {
+ throw new FileIOException(FileIOErrorType.FileIOCodeIsNull, "file io code is null");
+ }
+
+ FileIO fileIO = FileIOContainer.get(ioCode);
+
+ if (fileIO == null) {
+ throw new FileIOException(FileIOErrorType.FileIONotExists, "fileio not exists: " + ioCode);
+ }
+
+ //3.
+ instance.doUpload(fileIO);
}
catch (Exception e){
result.fail("error", e.getMessage());
@@ -126,6 +141,37 @@
return result;
}
+ private void doUpload(FileIO fileIO) {
+ ExcelReaders excelLoaders = new ExcelReaders(request);
+
+ //1. fire begin event
+ fileIO.execHandlers(result, "begin");
+
+ //2. file to tempt table
+ List<FileIOItem> importList = fileIO.getItemList(FileIOTypeCode.Import);
+
+ for (FileIOItem fileIOItem : importList) {
+ fileIOItem.execHandlers(result, "begin");
+ excelLoaders.loadOneItem(fileIOItem);
+ fileIOItem.execHandlers(result, "end");
+ }
+
+ //3. fire after tempt tables
+ fileIO.execHandlers(result, "after-temp");
+
+ //4. tempt table to standard table
+ List<FileIOItem> dbList = fileIO.getItemList(FileIOTypeCode.Db);
+
+ for (FileIOItem fileIOItem : dbList) {
+ fileIOItem.execHandlers(result, "begin");
+ DataBaseTranslator.translate(result, fileIOItem);
+ fileIOItem.execHandlers(result, "end");
+ }
+
+ //5. fire end event
+ fileIO.execHandlers(result, "end");
+ }
+
private static List<FileItem> loadUploadFile(HttpServletRequest request) throws FileUploadException {
DiskFileItemFactory diskFileFactory = new DiskFileItemFactory();
diff --git a/source/my/frame/src/frame/file/office/LoadManager.java b/source/my/frame/src/frame/file/office/LoadManager.java
index a23077a..dbac984 100644
--- a/source/my/frame/src/frame/file/office/LoadManager.java
+++ b/source/my/frame/src/frame/file/office/LoadManager.java
@@ -8,17 +8,17 @@
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
-
import frame.file.FileIO;
import frame.file.FileIOContainer;
import frame.file.FileIOItem;
import frame.file.UploadResult;
import frame.file.db.DataBaseTranslator;
-import frame.file.office.excel.ExcelHandler;
-import frame.file.office.excel.ExcelLoaders;
+import frame.file.office.excel.ExcelReaders;
import frame.util.Util;
+
public class LoadManager {
+
private boolean isSuccess;
private Logger logger;
private String ioCode;
@@ -71,7 +71,7 @@
throw new Exception("excelLoaders request is null");
}
- ExcelLoaders excelLoaders = new ExcelLoaders(request);
+ ExcelReaders excelLoaders = new ExcelReaders(request);
isSuccess = excelLoaders.load(result, fileIO);
diff --git a/source/my/frame/src/frame/file/office/excel/ExcelLoader.java b/source/my/frame/src/frame/file/office/excel/ExcelLoader.java
deleted file mode 100644
index b1c0e69..0000000
--- a/source/my/frame/src/frame/file/office/excel/ExcelLoader.java
+++ /dev/null
@@ -1,480 +0,0 @@
-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 sun.awt.geom.AreaOp.AddOp;
-
-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 澶歴heet 鎴栬�呰 澶歠ile 缁勬垚涓�娆ask
- 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) {
- 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];
-
- if(cloumnList == null) {
- cloumnList = new ArrayList<String>();
- }
-
- cloumnList.set(i, oneCloumn);
- }
- }
- else {
- //add one row
- }
-
- }
- };
- 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();
- //TODO 澶歴heet 闂 1.澶歴heet 鍚屼竴寮犺〃 涓嶅悓瀛楁 涓�鏉℃暟鎹� 2.澶歴heet 涓嶅悓琛� 鐩稿悓涓存椂琛� 锛堜笉鍚屽瓧娈� 涓嶅悓鏁版嵁锛�3.澶歴heet涓嶅悓琛ㄤ笉鍚屼复鏃惰〃 1瀵�1
- if (fromName.equalsIgnoreCase(sheetName)) {
-
- InputSource inputSource = new InputSource(sheetData);
- parser.parse(inputSource);
- sheetData.close();
- }
- }
-
- }
-
-
- //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();
- }
- }
-
-}
diff --git a/source/my/frame/src/frame/file/office/excel/ExcelLoaders.java b/source/my/frame/src/frame/file/office/excel/ExcelLoaders.java
deleted file mode 100644
index 4946c3c..0000000
--- a/source/my/frame/src/frame/file/office/excel/ExcelLoaders.java
+++ /dev/null
@@ -1,277 +0,0 @@
-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.HashMap;
-import java.util.HashSet;
-import java.util.List;
-import java.util.Map;
-
-import javax.servlet.http.HttpServletRequest;
-import javax.transaction.Transaction;
-
-import org.apache.commons.collections4.map.HashedMap;
-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 com.sun.mail.handlers.image_gif;
-import com.sun.org.apache.bcel.internal.generic.NEW;
-
-import frame.config.Configer;
-import frame.data.Entity;
-import frame.data.meta.EntityMeta;
-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.file.office.IOMappingItemRuntime;
-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 List<Entity> dataList;
- private static int MAX_CACHE_SIZE = 30 * 1024 * 1024;
- private static int MAX_FILE_SIZE = 100 * 1024 * 1024;
- private static int DEFAULT_UPLOAD_SIZE = 10000;
-
- public ExcelLoaders(HttpServletRequest request) {
- this.logger = LoggerFactory.getLogger(this.getClass());
- this.request = request;
- dataList = new ArrayList<Entity>();
- }
-
- 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());
- } 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 = FileIOItem.extractFileName(fromName) + FileIOItem.extractSheetName(fromName);
- sheetInputStream = xssfReader.getSheet(sheetName);
-
- if (sheetInputStream.available() == 0) {
- //TODO 娌℃壘鍒版湰fileiotime瀵瑰簲鐨剆heet 涓や釜鏂瑰悜 澶歠ile涓� 鍚庨潰file 鎵惧埌浜� 杩樻槸璇撮渶瑕佸涓猣ile閮藉瓨鍦�
- loadError = LoadError.SheetNotFound;
- loadError.setMessage("sheet :" + sheetName + " not match");
- return loadError;
- }
-
- 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) {
- Map<String, Integer> cloumnMap = null;
- if (rowIndex == 0) {
- cloumnMap = new HashedMap<String, Integer>();
- }
- else if (fileIOItem.getFromRowNo() > (rowIndex - 1)) {
- for(int i = 0; i < datas.length; i++) {
- String oneCloumn = datas[i];
-
- if(cloumnMap == null) {
- cloumnMap = new HashMap<String, Integer>();
- }
-
- cloumnMap.put(oneCloumn, i);
- }
- }
- else {
- //add one row
- if (fileIOItem.getFromRowNo() + DEFAULT_UPLOAD_SIZE == rowIndex) {
- commit2TeslmpDb(dataList);
- }
- List<IOMappingItemRuntime> insertMappingRuntime = fileIOItem.getInsertMappingRuntime();
- EntityMeta toMeta = fileIOItem.getToMeta();
- Entity entity = new Entity(toMeta);
-
- for (IOMappingItemRuntime ioMappingItemRuntime : insertMappingRuntime) {
- String fromField = ioMappingItemRuntime.getFromField();
- String toField = ioMappingItemRuntime.getToField();
- if (cloumnMap == null) {
- throw new NoCloumnException("");
- }
-
- if(!cloumnMap.containsKey(fromField)) {
- throw new NoMatchFieldException(fromField);
- }
-
- Integer valueIdx = cloumnMap.get(fromField);
- String fieldValue = datas[valueIdx];
- entity.set(toField, fieldValue);
- }
- dataList.add(entity);
-
- }
-
- }
- };
- 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);
- }
-
-}
--
Gitblit v1.8.0