WinForm通过Excel作为中间介质实现导入导出小工具

最新推荐文章于 2023-12-08 12:01:43 发布

fengzhilu000

最新推荐文章于 2023-12-08 12:01:43 发布

阅读量319

点赞数

分类专栏： WinForm C# 文章标签： Excel导入导出 WinForm

本文链接：https://blog.csdn.net/u012761373/article/details/79012999

版权

C# 同时被 2 个专栏收录

31 篇文章 0 订阅

订阅专栏

WinForm

5 篇文章 0 订阅

订阅专栏

在实际开发中,为了保护内网系统，有些系统的内外网是分开的，会遇到内外网交互的情况，如何实现内外网交互的，很多情况是通过内外网摆渡机、光闸、或者网闸去共享数据，但是我们现在没有这些工具，需要实现内外网数据交互，现在给出一种通过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=orcl;User ID=BDCBZB;Password=BDCBZB;" />
<add key="Dbtype_WwsjNqzj" value="ORACLE" />


<add key="Conn_WwsjWqzj" value="Data Source=bdcbzb;User ID=bdcbzb;Password=bdcbzb;" />
<add key="Dbtype_WwsjWqzj" value="ORACLE" />

操作说明：
导出：
需要选择“开始时间”和“结束时间”,并点击“导出”按钮，选择Excel表生成路径，等待执行，操作成功会用提示"数据生成成功!路径：【Excel生成路径】"
导入：
导入只需要点击“导出”按钮，选择要导入的Excel表所在的路径就行;等待执行，执行成功会提示“数据导入成功!”