在實際開發中,為了保護内網系統，有些系統的内外網是分開的，會遇到内外網互動的情況，如何實作内外網互動的，很多情況是通過内外網擺渡機、光閘、或者網閘去共享資料，但是我們現在沒有這些工具，需要實作内外網資料互動，現在給出一種通過Excel表的方式去互動資料。
WinForm背景功能代碼：
public partial class DataOperatingInterface : Form
{
private static readonly string strDataSource = "Wwsj-Excel";
public DataOperatingInterface()
{
InitializeComponent();
}
private void btnExport_Click(object sender, EventArgs e)
{
try
{
#region 代碼
DialogResult exportDir = fbdExport.ShowDialog();//是調用檔案浏覽器控件;
if (exportDir == System.Windows.Forms.DialogResult.OK)//是判斷檔案浏覽器控件是否傳回ok，即使用者是否确定選擇。如果确定選擇，則彈出使用者在檔案浏覽器中選擇的路徑：
{
//MessageBox.Show(fbdExport.SelectedPath);//展示視窗
#region ①資料庫的連接配接
GS.DataBase.IDbAccess iDb_WwsjNqzj = GS.DataBase.DbAccessFactory.CreateInstance(ConfigurationManager.AppSettings["Conn_WwsjNqzj"], ConfigurationManager.AppSettings["Dbtype_WwsjNqzj"]);
#endregion
#region ②解析資料庫
EstateMonth em = new EstateMonth();
string strStartTime = string.Empty;
string strEndTime = string.Empty;
Filter exportFilter = new Filter();
if (string.IsNullOrEmpty(dtpStartTime.Text))
{
MessageBox.Show("請選擇開始時間!");
return;
}
if (string.IsNullOrEmpty(dtpEndTime.Text))
{
MessageBox.Show("請選擇截止時間!");
return;
}
strStartTime = dtpStartTime.Text;
strEndTime = dtpEndTime.Text;
exportFilter.StartTime = strStartTime;
exportFilter.EndTime = strEndTime;
exportFilter.Path = fbdExport.SelectedPath;
em.ExportUnitRegData(iDb_WwsjNqzj, exportFilter, strDataSource);
#endregion
MessageBox.Show("資料生成成功!路徑：【" + fbdExport.SelectedPath + "】");
}
else
{
MessageBox.Show("請選擇導出的路徑!");
}
#endregion
}
catch (Exception ex)
{
MessageBox.Show("異常:" + ex.ToString());
}
}
private void btnImport_Click(object sender, EventArgs e)
{
try
{
#region 打開檔案路徑進行操作
DialogResult importDir = fbdImport.ShowDialog();//是調用檔案浏覽器控件;
if (importDir == System.Windows.Forms.DialogResult.OK)//是判斷檔案浏覽器控件是否傳回ok，即使用者是否确定選擇。如果确定選擇，則彈出使用者在檔案浏覽器中選擇的路徑：
{
//MessageBox.Show(fbdImport.SelectedPath);//展示視窗
#region ①資料庫的連接配接
GS.DataBase.IDbAccess iDb_WwsjWqzj = GS.DataBase.DbAccessFactory.CreateInstance(ConfigurationManager.AppSettings["Conn_WwsjWqzj"], ConfigurationManager.AppSettings["Dbtype_WwsjWqzj"]);
#endregion
string ImportPath = fbdImport.SelectedPath;
EstateMonth emImport = new EstateMonth();
Filter importFilter = new Filter();
importFilter.Path = fbdImport.SelectedPath;
emImport.ImportUnitRegData(iDb_WwsjWqzj, importFilter, strDataSource);
MessageBox.Show("資料導入成功!");
}
else
{
MessageBox.Show("請選擇導入的路徑");
}
#endregion
}
catch (Exception ex)
{
MessageBox.Show("異常:" + ex.ToString());
}
#region 打開檔案進行操作
初始化一個OpenFileDialog類
//OpenFileDialog fileDialog = new OpenFileDialog();
如果我們要為彈出的選擇框中過濾檔案類型，可以設定OpenFileDialog的Filter屬性。比如我們隻允許使用者選擇.xls檔案，可以作如下設定：
//fileDialog.Filter = "(*.xls)|*.xls";
判斷使用者是否正确的選擇了檔案
//if (fileDialog.ShowDialog() == DialogResult.OK)
//{
// //擷取使用者選擇檔案的字尾名
// string extension = Path.GetExtension(fileDialog.FileName);
// //聲明允許的字尾名
// string[] str = new string[] { ".gif", ".jpge", ".jpg" };
// if (!((IList)str).Contains(extension))
// {
// MessageBox.Show("僅能上傳gif,jpge,jpg格式的圖檔！");
// }
// else
// {
// //擷取使用者選擇的檔案，并判斷檔案大小不能超過20K，fileInfo.Length是以位元組為機關的
// FileInfo fileInfo = new FileInfo(fileDialog.FileName);
// if (fileInfo.Length > 20480)
// {
// MessageBox.Show("上傳的圖檔不能大于20K");
// }
// else
// {
// //在這裡就可以寫擷取到正确檔案後的代碼了
// }
// }
//}
#endregion
}
}
Common類庫：
Model:
public class Filter
{
/// <summary>
/// 開始時間
/// </summary>
public string StartTime { get; set; }
/// <summary>
/// 截至時間
/// </summary>
public string EndTime { get; set; }
/// <summary>
/// 路徑
/// </summary>
public string Path { get; set; }
}
EstateMonth類：
public class EstateMonth
{
#region 推送資料部分
/// <summary>
/// 内網WWDATA向外網WWSJ推送資料
/// </summary>
/// <param name="iDb">資料庫源(查詢)</param>
/// <param name="strStartTime">開始時間</param>
/// <param name="strEndTime">結束時間</param>
/// <param name="strDataSource">資料來源:标志</param>
public void ExportUnitRegData(GS.DataBase.IDbAccess iDb, Filter modelFilter, string strDataSource)
{
string strLogCaseNo = String.Empty;
string strLogYWH = String.Empty;
try
{
string strSql = String.Empty;
DataSet dsSqlList = new DataSet();
List<string> lstSqlKey = new List<string>();
DataSet dsData = new DataSet();
DataSet dsSendData = new DataSet();
Hashtable ht = new Hashtable();
List<string> lstCompara = new List<string>();
DataSet tempData = new DataSet();//推送查詢資料
#region 1.查詢有哪些表的資料需要推送以及其查詢方式
strSql = string.Format("select SQL,TARGETKEY,TARGETTABLE,DATAIN from ESTATEINTERACTIVE where DATAIN in ('77')");
dsSqlList = iDb.GetDataSet(strSql);
foreach (DataRow drSqlList in dsSqlList.Tables[0].Rows)
{
strSql = drSqlList["SQL"].ToString();
string strTargeKeg = drSqlList["TARGETKEY"].ToString();
string strTargeTABLE = drSqlList["TARGETTABLE"].ToString();
string strDataIn = drSqlList["DATAIN"].ToString();
///SQL TARGETKEYTARGETTABLE
///select * from BDC_BDCDJ where 業務号='#業務号#'業務号 BDC_BDCDJ
lstSqlKey.Add(strSql + "," + strTargeKeg + "," + strTargeTABLE + "," + strDataIn);//
string strColumn = string.Empty;
#region 判斷Where後面的條件strColumn
if (JudgeHelper.HasChinese(strTargeKeg))
{
strColumn = "推送日期";
}
else
{
strColumn = "TSRQ";
}
#endregion
string strExcelSql = "select * from " + strTargeTABLE + " Where 1=1 ";
if (iDb.DataBaseType.ToString() == "ORACLE")
{
if (!string.IsNullOrEmpty(modelFilter.StartTime)) {
strExcelSql += " and " + strColumn + ">=to_date('" + modelFilter.StartTime + "','yyyy-mm-dd hh24:mi:ss') ";
//strExcelSql += " and " + strColumn + ">=to_date('" + modelFilter.StartTime + "','yyyy-mm-dd hh24:mi:ss') ";
}
if (!string.IsNullOrEmpty(modelFilter.EndTime)) {
strExcelSql += " and " + strColumn + "<=to_date('" + modelFilter.EndTime + "','yyyy-mm-dd hh24:mi:ss') ";
//strExcelSql += " and " + strColumn + "<=to_date('" + modelFilter.EndTime + " 23:59:59" + "','yyyy-mm-dd hh24:mi:ss') ";
}
}
else if (iDb.DataBaseType.ToString() == "SQLSERVER")
{
if (!string.IsNullOrEmpty(modelFilter.StartTime)){
strExcelSql += " and " + strColumn + ">='" + modelFilter.StartTime + "'";
}
if (!string.IsNullOrEmpty(modelFilter.EndTime)){
strExcelSql += " and " + strColumn + "<='" + modelFilter.EndTime + "'";
//strExcelSql += " and " + strColumn + "<='" + modelFilter.EndTime + " 23:59:59" + "'";
}
}
dsData = iDb.GetDataSet(strExcelSql);
ExcelHelper.GetExcelByDataSet(iDb, dsData, strTargeKeg, strTargeTABLE, modelFilter.Path, strDataSource);
}
#endregion
}
catch (Exception ee)
{
Log.SaveLog(iDb, ee.ToString(), "SendUnitRegData", "SendUnitRegData", "Wwsj-Excel", 1, strDataSource);
}
}
/// <summary>
/// 從指定檔案夾路徑（Path）選擇Excel表導入制定資料庫（iDb）
/// </summary>
/// <param name="iDb">目标外網中間庫</param>
/// <param name="modelFilter"></param>
/// <param name="strDataSource"></param>
public void ImportUnitRegData(GS.DataBase.IDbAccess iDb, Filter modelFilter, string strDataSource)
{
string strLogCaseNo = String.Empty;
string strLogYWH = String.Empty;
try
{
string strSql = String.Empty;
DataSet dsSqlList = new DataSet();
List<string> lstSqlKey = new List<string>();
DataSet dsData = new DataSet();
DataSet dsSendData = new DataSet();
Hashtable ht = new Hashtable();
DataSet tempData = new DataSet();//推送查詢資料
#region 1.查詢有哪些表的資料需要推送以及其查詢方式
strSql = string.Format("select SQL,TARGETKEY,TARGETTABLE,DATAIN from ESTATEINTERACTIVE where DATAIN in ('77')");
dsSqlList = iDb.GetDataSet(strSql);
foreach (DataRow drSqlList in dsSqlList.Tables[0].Rows)
{
strSql = drSqlList["SQL"].ToString();
string strTargeKeg = drSqlList["TARGETKEY"].ToString();
string strTargeTABLE = drSqlList["TARGETTABLE"].ToString();
string strDataIn = drSqlList["DATAIN"].ToString();
lstSqlKey.Add(strSql + "," + strTargeKeg + "," + strTargeTABLE + "," + strDataIn);//
string strColumn = string.Empty;
ExcelHelper.GetExcelDataCommon(iDb, iDb, strTargeTABLE, modelFilter.Path, strDataSource);
}
#endregion
}
catch (Exception ee)
{
Log.SaveLog(iDb, ee.ToString(), "ImportUnitRegData", "ImportUnitRegData", "Wwsj-Excel", 1, strDataSource);
}
}
/// <summary>
/// WFPROCESSGLOB/WFCASEINF/WFPROCESS 根據受理編号CASENO發送資料 DataIn=14
/// </summary>
/// <param name="iDb">業務庫(源)</param>
/// <param name="iDb_OL">前置機(目标庫)</param>
/// <param name="ds_List">根據受理編号擷取對應表資料集合</param>
/// <param name="strTargeTable">儲存的目标表</param>
/// <param name="strCaseNo">受理編号</param>
public void SendWwsjDataByCaseNo(GS.DataBase.IDbAccess iDb, GS.DataBase.IDbAccess iDb_OL, DataSet ds_List, string strTargeTable, string strTargeKeg, string strCaseNo, string strYwh, string strDataSource, List<string> lstCompare)
{
try
{
#region 表進行循環
foreach (DataRow row_st in ds_List.Tables[0].Rows)//周遊與該受理編号有關的資料資訊
{
Hashtable ht = new Hashtable();
foreach (DataColumn col_st in ds_List.Tables[0].Columns)
{
if (!string.IsNullOrEmpty(row_st[col_st].ToString()))
{
ht.Add(col_st.ColumnName, row_st[col_st]);
}
}
#region 判斷字段是否為中文"推送日期"并作處理,以表的第二列為準
if (JudgeHelper.HasChinese(ds_List.Tables[0].Columns[1].ColumnName))
{
if (ht.ContainsKey("推送日期"))
{
ht.Remove("推送日期");//删除原來的
ht.Add("推送日期", DateTime.Now.ToString());//添加個新的(可能存在像擺渡機一樣,存在兩次推送,中間表已存在"TSRQ")
}
else
{
ht.Add("推送日期", DateTime.Now.ToString());//添加個新的
}
}
else
{
if (ht.ContainsKey("TSRQ"))
{
ht.Remove("TSRQ");//删除原來的
ht.Add("TSRQ", DateTime.Now.ToString());//添加個新的(可能存在像擺渡機一樣,存在兩次推送,中間表已存在"TSRQ")
}
else
{
ht.Add("TSRQ", DateTime.Now.ToString());//添加個新的
}
}
#endregion
#region 儲存字段主鍵做處理--關鍵字段
if (strTargeTable == "WFPROCESSGLOB" || strTargeTable == "WFPROCESS")
{
iDb_OL.SaveData(strTargeTable, ht, "PID");
}
else if (strTargeTable == "WFCASEINF")
{
iDb_OL.SaveData(strTargeTable, ht, "CID");
}
else if (strTargeTable == "BDC_QLR")
{
iDb_OL.SaveData(strTargeTable, ht, "權利人ID");
}
else//DATAIN=12; //BDC_WWSH;
{
iDb_OL.SaveData(strTargeTable, ht, strTargeKeg);
}
#endregion
#region 删除表已推送的資料,但是會有修改的資料,再次推送的時候需要删除以前推送的記錄
if (!lstCompare.Contains(strCaseNo))
{
//DeleteOLData(iDb, iDb_OL, strYwh);
lstCompare.Add(strCaseNo);
}
#endregion
}
#endregion
}
catch (Exception e)
{
Log.SaveLog(iDb, e.ToString(), "SendWwsjDataByCaseNo", "SendWwsjDataByCaseNo", strCaseNo + "|" + strYwh, 1, strDataSource);
}
}
#endregion
}
Excel表類：
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace Common
{
public class ExcelHelper
{
/// <summary>
/// 把DataSet轉為Excel
/// </summary>
/// <param name="ds">資料集合</param>
/// <param name="sheetKeys">腳本關鍵字</param>
/// <param name="sheetName">表名</param>
/// <param name="sPath">生成路徑: 檔案(sheetName$sheetKeys)</param>
public static void GetExcelByDataSet(GS.DataBase.IDbAccess iDb_Log, DataSet ds, string sheetKeys, string sheetName, string sPath, string strDataSource)
{
try
{
IWorkbook fileWorkbook = new HSSFWorkbook();//一個dataset對應一個WookBook
foreach (DataTable dt in ds.Tables)
{
ISheet sheet = fileWorkbook.CreateSheet(sheetName + "$" + sheetKeys);//sheet頁名為 表名+$+關鍵字 如 bdc_djzb$業務号
#region 表頭
IRow row = sheet.CreateRow(0);
for (int i = 0; i < dt.Columns.Count; i++)
{
ICell cell = row.CreateCell(i);
cell.SetCellValue(dt.Columns[i].ColumnName);
}
#endregion
#region 資料
for (int i = 0; i < dt.Rows.Count; i++)
{
IRow row1 = sheet.CreateRow(i + 1);
for (int j = 0; j < dt.Columns.Count; j++)
{
ICell cell = row1.CreateCell(j);
cell.SetCellValue(dt.Rows[i][j].ToString());
}
}
#endregion
}
#region 轉為位元組數組
MemoryStream stream = new MemoryStream();
fileWorkbook.Write(stream);
var buf = stream.ToArray();
if (!System.IO.Directory.Exists(sPath))
{
System.IO.Directory.CreateDirectory(sPath);
}
#endregion
#region 根據檔案版本設定EXCEL名字
//DirectoryInfo DireInfo = new DirectoryInfo(sPath);
string[] fileList = Directory.GetFiles(sPath, string.Format(@"{0}*", sheetName));
int count = Convert.ToInt32(fileList.Length) + 1;
sPath = System.IO.Path.Combine(sPath, string.Format("{0}${1}.xls", sheetName, count));
//儲存為Excel檔案
using (FileStream fs = new FileStream(sPath, FileMode.OpenOrCreate, FileAccess.Write))
{
fs.Write(buf, 0, buf.Length);
fs.Flush();
}
#endregion
}
catch (Exception ex)
{
Log.SaveLog(iDb_Log, ex.ToString(), "GetExcelByDataSet", "GetExcelByDataSet", "Wwsj-Excel", 1, strDataSource);
throw;
}
}
/// <summary>
/// 擷取最新的EXCEL檔案
/// </summary>
/// <param name="direPath"></param>
/// <param name="patten"></param>
/// <returns></returns>
public static FileInfo GetLastExcel(string direPath, string patten)
{
DirectoryInfo direInfo = new DirectoryInfo(direPath);
FileInfo[] fileList = direInfo.GetFiles(patten);
List<FileInfo> list = new List<FileInfo>(fileList);
list.Sort(new Comparison<FileInfo>(delegate(FileInfo a, FileInfo b)
{
return b.CreationTime.CompareTo(a.CreationTime);
}));
if (list.Count > 0)
{
return list[0];
}
return null;
}
//将EXCEL 檔案資料存入資料庫
public static void ExcelToDataBase(GS.DataBase.IDbAccess iDb, GS.DataBase.IDbAccess iDb_OL, FileInfo fileLast, string strDataSource)
{
if (fileLast == null)
{
return;
}
ISheet sheet = null;
IWorkbook workbook = null;
DataTable data = new DataTable();
//int startRow = 0;
try
{
// fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
if (fileLast.Name.IndexOf(".xlsx") > 0) // 2007版本
{
workbook = new XSSFWorkbook(fileLast.FullName);
}
else if (fileLast.Name.IndexOf(".xls") > 0) // 2003版本
{
workbook = new HSSFWorkbook(fileLast.Open(FileMode.OpenOrCreate));
}
//循環sheet 表
sheet = workbook.GetSheetAt(0);
if (sheet != null)
{
string sheetName = sheet.SheetName.Split('$')[0];
string sheetKeys = sheet.SheetName.Split('$')[1];
IRow firstRow = sheet.GetRow(0);//擷取表頭
int cellCount = firstRow.LastCellNum;//sheet頁列數
int rowCount = sheet.LastRowNum;
IRow rowNow = null;
string value;
ArrayList colunmName = new ArrayList();
for (int j = 0; j < cellCount; j++) //周遊列 擷取表頭
{
colunmName.Add(firstRow.GetCell(j).StringCellValue);
}
for (int jj = 1; jj <= rowCount; jj++)//周遊行
{
Hashtable ht = new Hashtable();
rowNow = sheet.GetRow(jj);
for (int k = 0; k < cellCount; k++)//周遊列
{
value = rowNow.GetCell(k).StringCellValue;
ht.Add(colunmName[k], value);
}
#region 判斷Where後面的條件strColumn
//string strTsrq = "";
//if (JudgeHelper.HasChinese(sheetKeys))
//{
// strTsrq = "推送日期";
//}
//else
//{
// strTsrq = "TSRQ";
//}
//iDb_OL.SaveData(sheetName, ht, sheetKeys + "," + strTsrq);
#endregion
iDb_OL.SaveData(sheetName, ht, sheetKeys);
}
}
}
catch (Exception ex)
{
Log.SaveLog(iDb, ex.ToString(), "ExcelToDataBase", "ExcelToDataBase", "Wwsj-Excel", 1, strDataSource);
}
}
/// <summary>
/// 讀取EXCEL資料到資料庫
/// </summary>
/// <param name="iDb">庫A(A->B)</param>
/// <param name="iDb_OL">庫B(A->B)</param>
/// <param name="WwsjTableName">庫B表名</param>
/// <param name="sPath">Escel檔案目錄</param>
/// <param name="strDataSource">資料來源</param>
public static void GetExcelDataCommon(GS.DataBase.IDbAccess iDb, GS.DataBase.IDbAccess iDb_OL,string WwsjTableName, string sPath, string strDataSource)
{
try
{
string[] TableInfo = WwsjTableName.Split(',', '，');
for (int i = 0; i < TableInfo.Length; i++)
{
//sheetName = TableInfo[i].Split(':', '：')[0];//表名
//sheetKeys = TableInfo[i].Split(':', '：')[1];//關鍵字名
string patten = TableInfo[i] + "*";
FileInfo fileLast = GetLastExcel(sPath, patten);
ExcelToDataBase(iDb, iDb_OL, fileLast, strDataSource);
}
}
catch (Exception ex)
{
Log.SaveLog(iDb, ex.ToString(), "GetExcelDataCommon", "GetExcelDataCommon", "Wwsj-Excel", 1, strDataSource);
}
}
}
}
JudgeHelper類：
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace Common
{
public class JudgeHelper
{
#region 判斷字元串是否為中文
/// <summary>
/// 判斷字元串中是否包含中文
/// </summary>
/// <param name="str">需要判斷的字元串</param>
/// <returns>判斷結果</returns>
public static bool HasChinese(string str)
{
return System.Text.RegularExpressions.Regex.IsMatch(str, @"[\u4e00-\u9fa5]");
}
#endregion
}
}
Log類：
using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace Common
{
public class Log
{
#region 日志異常記錄
/// <summary>
/// 記錄異常，供共享監管系統查詢
/// </summary>
/// <param name="iDb"></param>
/// <param name="strMessage">異常資訊</param>
/// <param name="strOperate">操作資訊</param>
/// <param name="strFuctionName">函數名(共享監管系統進行重複調用方法時使用)</param>
/// <param name="strFunctionKey">函數參數值(共享監管系統進行重複調用方法時使用)</param>
/// <param name="iLevel">嚴重程度(1最嚴重)</param>
public static void SaveLog(GS.DataBase.IDbAccess iDb, string strMessage, string strOperate, string strFuctionName, string strFunctionKey, int iLevel, string strDataSource)
{
Hashtable ht = new Hashtable();
ht.Add("MESSAGE", strMessage);
ht.Add("TIME", DateTime.Now.ToString());
ht.Add("FUNCTIONNAME", strFuctionName);
ht.Add("FUNCTIONKEY", strFunctionKey);
ht.Add("OPERATE", strOperate);
ht.Add("FUNCTIONLEVEL", iLevel);
ht.Add("DATASOURCE", strDataSource);
iDb.AddData("ESTATEINTERACTIVELOG", ht);
}
#endregion
}
}
引用Excel的三個DLL:
NPOI.dll、NPOI.OOXML.dll、NPOI.OpenXml4Net.dll
配置說明：
-------------------------------------------------------------------------------
---------------------------------資料導出--------------------------------------
-------------------------------------------------------------------------------
導出資料時需要部署:
--先查詢ESTATEINTERACTIVE表指定DATA in（'77'）的表,然後根據這些表去資料庫
--查找同名的資料，解析資料并在指定檔案夾下生成Excel表;
------------------------建标隻針對一個資料(内網中間庫)--------------------------
一、建表
-- Create table ESTATEINTERACTIVE
create table ESTATEINTERACTIVE
(
sqlname VARCHAR2(50),
sql VARCHAR2(2000),
targetkey VARCHAR2(50),
targettable VARCHAR2(50),
datain VARCHAR2(2),
datasource VARCHAR2(50)
)
--插入要推送資料表資訊
INSERT INTO ESTATEINTERACTIVE(SQLNAME,SQL,TARGETKEY,TARGETTABLE,DATAIN,DATASOURCE)
VALUES('BDC_PROGRESS','select * from BDC_PROGRESS where 受理編号=''#受理編号#''','受理編号','BDC_PROGRESS','77','1')
--建立日志表ESTATEINTERACTIVELOG
create table ESTATEINTERACTIVE
(
sqlname VARCHAR2(50),
sql VARCHAR2(2000),
targetkey VARCHAR2(50),
targettable VARCHAR2(50),
datain VARCHAR2(2),
datasource VARCHAR2(50)
)
-------------------------------------------------------------------------------
---------------------------------資料導入--------------------------------------
-------------------------------------------------------------------------------
說明：
導入資料時需要部署:
先查詢ESTATEINTERACTIVE表指定DATA in（'77'）的表,然後根據這些表去指定檔案夾
查找同名的Excel表，解析Excel表并将資料儲存在資料庫;
------------------------建标隻針對一個資料(外網中間庫)--------------------------
一、建表
-- Create Oracle table ESTATEINTERACTIVE
create table ESTATEINTERACTIVE
(
sqlname VARCHAR2(50),
sql VARCHAR2(2000),
targetkey VARCHAR2(50),
targettable VARCHAR2(50),
datain VARCHAR2(2),
datasource VARCHAR2(50)
)
-- Create Sqlserver table ESTATEINTERACTIVE
CREATE TABLE ESTATEINTERACTIVE(
SQLNAME varchar(50) NULL,
SQL varchar(2000) NULL,
TARGETKEY varchar(50) NULL,
TARGETTABLE varchar(50) NULL,
DATAIN varchar(2) NULL,
DATASOURCE varchar(50) NULL
)
--插入要推送資料表資訊
INSERT INTO ESTATEINTERACTIVE(SQLNAME,SQL,TARGETKEY,TARGETTABLE,DATAIN,DATASOURCE)
VALUES('BDC_PROGRESS','select * from BDC_PROGRESS where 受理編号=''#受理編号#''','受理編号','BDC_PROGRESS','77','1')
--建立要推送表BDC_PROGRESS
-- Create Oracle table
create table BDC_PROGRESS
(
id0 NUMBER,
受理編号 VARCHAR2(32),
登記類型 VARCHAR2(50),
申請人 VARCHAR2(60),
證件号 VARCHAR2(50),
聯系方式 VARCHAR2(30),
收件時間 VARCHAR2(30),
目前環節 VARCHAR2(30),
推送日期 DATE
)
-- Add comments to the columns
comment on column BDC_PROGRESS.id0
is '序列号';
comment on column BDC_PROGRESS.受理編号
is '受理編号';
comment on column BDC_PROGRESS.登記類型
is '登記大類';
comment on column BDC_PROGRESS.申請人
is '業務申請人';
comment on column BDC_PROGRESS.證件号
is '身份證号';
comment on column BDC_PROGRESS.聯系方式
is '電話号碼';
comment on column BDC_PROGRESS.收件時間
is '收件時間';
comment on column BDC_PROGRESS.目前環節
is '目前辦理環節';
comment on column BDC_PROGRESS.推送日期
is '推送日期';
--Create Sqlserver Table
create table BDC_PROGRESS
(
id0 int,
受理編号 VARCHAR(32),
登記類型 VARCHAR(50),
申請人 VARCHAR(120),
證件号 VARCHAR(50),
聯系方式 VARCHAR(30),
收件時間 VARCHAR(30),
目前環節 VARCHAR(30),
推送日期 datetime
)
--建立日志表ESTATEINTERACTIVELOG
--Oracle
CREATE TABLE ESTATEINTERACTIVELOG(
MESSAGE clob NULL,
TIME date NULL,
FUNCTIONKEY varchar(500) NULL,
OPERATE varchar(500) NULL,
FUNCTIONLEVEL Number NULL,
FUNCTIONNAME varchar(500) NULL,
DATASOURCE varchar(50) NULL
)
--SqlServer
CREATE TABLE ESTATEINTERACTIVELOG(
MESSAGE text NULL,
TIME datetime NULL,
FUNCTIONKEY varchar(500) NULL,
OPERATE varchar(500) NULL,
FUNCTIONLEVEL int NULL,
FUNCTIONNAME varchar(500) NULL,
DATASOURCE varchar(50) NULL
)
--資料庫配置： 每個功能隻需要配置對應的一個資料庫連接配接
<!--外網收件内網推送内網前置機-->
<!--<add key="Conn_WwsjNqzj" value="Data Source=.\SQL2008R2;Initial Catalog=NWWDATA;User ID=sa;Password=sa;"/>
<add key="Dbtype_WwsjNqzj" value="SQLSERVER" />-->
<add key="Conn_WwsjNqzj" value="Data Source=orcl;User ID=BDCBZB;Password=BDCBZB;" />
<add key="Dbtype_WwsjNqzj" value="ORACLE" />
<!--外網收件内網推送外網前置機-->
<!--<add key="Conn_WwsjWqzj" value="Data Source=.\SQL2008R2;Initial Catalog=WWWDATA;User ID=sa;Password=sa;"/>
<add key="Dbtype_WwsjWqzj" value="SQLSERVER" />-->
<add key="Conn_WwsjWqzj" value="Data Source=bdcbzb;User ID=bdcbzb;Password=bdcbzb;" />
<add key="Dbtype_WwsjWqzj" value="ORACLE" />
操作說明：
導出：
需要選擇“開始時間”和“結束時間”,并點選“導出”按鈕，選擇Excel表生成路徑，等待執行，操作成功會用提示"資料生成成功!路徑：【Excel生成路徑】"
導入：
導入隻需要點選“導出”按鈕，選擇要導入的Excel表所在的路徑就行;等待執行，執行成功會提示“資料導入成功!”
![Unity遊戲開發背景知識[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)