/// <summary>
/// 获取结构
/// </summary>
/// <param name="context"></param>
/// <returns></returns>
public JObject GetSchema(HttpContext context)
{
JObject obj = new JObject();
try
{
YZRequest request = new YZRequest(context);
int connectId = request.GetInt32("connectId");
string sheetName = request.GetString("sheetName");
string titleIndex = request.GetString("titleIndex");
string query = string.Format("{0}|{1}", sheetName, titleIndex);
ConnectInfo connectInfo = ConnectInfoManager.GetConnectInfo(connectId);
ExcelLogin login = JObject.Parse(connectInfo.connectStr).ToObject <ExcelLogin>();
ISourceHandle handle = new ExcelHandle(login);
QueryDepictInfo info = handle.GetQueryInfo(sheetName);
info.query = query;
info = handle.GetQueryInfo(info);
obj["schema"] = GetColumnJson(info);
obj[YZJsonProperty.success] = true;
return(obj);
}
catch (Exception)
{
obj[YZJsonProperty.success] = false;
obj[YZJsonProperty.errorMessage] = "没有可供查询的表或视图！";
return(obj);
}
}
//modify:gaofeng 2021 / 01 / 18 < 2021 - 0001 QC System Enhancement from sales > --begin
/// <summary>
/// 下载文件
/// </summary>
/// <param name="dt"></param>
/// <param name="fileName"></param>
/// <param name="sheetName"></param>
protected void DownLoadExcel(System.Data.DataTable dt, string fileName, int sheetIndex = 0)
{
try
{
if (fileName == "NextTwoMonthsScrappedCSV")
{
string sourceFile = Server.MapPath("~/TemparyFile/ExcelTemplateCVS.xls");
string destFileName = "ExcelTemplate" + Guid.NewGuid() + ".xls";
string destFilePath = Server.MapPath("~/TemparyFile/") + destFileName;
File.Copy(sourceFile, destFilePath);
ExcelHandle.DataTableToExcel(dt, destFilePath);
ScriptManager.RegisterStartupScript(this, this.GetType(), "", "downloadFile('" + destFileName + "','" + fileName + "');", true);
}
else
{
string sourceFile = Server.MapPath("~/TemparyFile/ExcelTemplateTwo.xls");
string destFileName = "ExcelTemplate" + Guid.NewGuid() + ".xls";
string destFilePath = Server.MapPath("~/TemparyFile/") + destFileName;
File.Copy(sourceFile, destFilePath);
ExcelHandle.DataTableToExcel(dt, destFilePath);
ScriptManager.RegisterStartupScript(this, this.GetType(), "", "downloadFile('" + destFileName + "','" + fileName + "');", true);
}
}
catch (Exception ex)
{
throw ex;
}
}
private void button3_Click(object sender, EventArgs e)
{
if (textBox2.Text.Trim() == "")
{
MessageBox.Show("请选择已缴Excel存储路径！");
return;
}
else
{
DirectoryInfo folder = new DirectoryInfo(textBox2.Text);
FileInfo[] files = folder.GetFiles("*.*", SearchOption.AllDirectories);
foreach (FileInfo file in files)
{
if (file.Extension.ToUpper() == ".XLS" || file.Extension.ToUpper() == ".XLSX")
{
DataSet ds = ExcelHandle.ReadExcel(file.FullName);
if (ds != null)
{
ds.DataSetName = file.FullName;
//Save2DBYIJDF_ZZ(ds);
Save2DBYIJDF_ZZ_2(ds);
}
}
}
}
MessageBox.Show("入库完成！");
}
public SetDateTypeParameterForm(TransmitData transmitData)
{
this.transmitData = transmitData;
InitializeComponent();
loadGlobalChineseCharacters = LoadGlobalChineseCharacters.GetInstance();
this.selectColumnList = transmitData.selectedColumn;
this.columnTypeDict = transmitData.columnTypeDict;
this.selectedColumnVariableDict = transmitData.selectedColumnVariableDict;
tmpTwoDateColumnRelationContainer = new TwoDateColumnRelationContainer <string>();
//relationContainerList = new List<TwoDateColumnRelationContainer<string>>();
excelHandle = new ExcelHandle();
relationContainerList = excelHandle.getExistRelationFormExistExcel();
initUI();
}
private void SaveDataTable2Excel(DataTable dtable, string excelPath)
{
string xltPath = System.Windows.Forms.Application.StartupPath + "\\需补缴党费统计表.xlt";
object missing = Missing.Value;
if (!File.Exists(excelPath))
{
Application app = new ApplicationClass();
try
{
//让后台执行设置为不可见
app.Visible = false;
app.AlertBeforeOverwriting = false;
//新建一个工作簿
//Workbook wBooknew = app.Application.Workbooks.Add(missing);
////通过模板新建一个结果工作簿
Workbook wBooknew = app.Workbooks.Open(xltPath,
missing, missing, missing, missing, missing, missing, missing,
missing, missing, missing, missing, missing, missing, missing);
Worksheet wSheetnew = (Worksheet)wBooknew.ActiveSheet;
wBooknew.SaveAs(excelPath, missing, missing, missing, missing, missing,
XlSaveAsAccessMode.xlExclusive, XlSaveConflictResolution.xlOtherSessionChanges, missing, missing,
missing, missing);
wBooknew.Close();
}
catch (Exception ex)
{
}
finally
{
app.Quit();
app = null;
Process[] procs = Process.GetProcessesByName("Excel");
foreach (Process pro in procs)
{
pro.Kill();//没有更好的方法,只有杀掉进程
}
GC.Collect();
}
}
ExcelHandle.SaveDataTable2Excel(dtable, excelPath);
}
/// <summary>
/// 返回访问结果
/// </summary>
/// <param name="info"></param>
/// <param name="connect"></param>
/// <returns></returns>
private DataTable GetResult(QueryDepictInfo info, ConnectInfo connect)
{
ISourceHandle handle = null;
switch (connect.connectType)
{
case SourceTypeEnum.Excel:
handle = new ExcelHandle(JObject.Parse(connect.connectStr).ToObject <ExcelLogin>());
break;
case SourceTypeEnum.Oracle:
handle = new OracleHandle(JObject.Parse(connect.connectStr).ToObject <OracleLogin>());
break;
case SourceTypeEnum.SAP:
handle = new SapHandle(JObject.Parse(connect.connectStr).ToObject <SapLogin>());
break;
case SourceTypeEnum.SqlServer:
handle = new SqlServerHandle(JObject.Parse(connect.connectStr).ToObject <SqlServerLogin>());
break;
case SourceTypeEnum.WebService:
handle = new WebServiceHandle(JObject.Parse(connect.connectStr).ToObject <WebServiceLogin>());
break;
default:
break;
}
DataTable dt = new DataTable();
if (handle != null)
{
dt = handle.GetResult(info);
}
return(dt);
}
/// <summary>
/// 连接服务
/// </summary>
/// <param name="context"></param>
/// <returns></returns>
public JObject JoinService(HttpContext context)
{
JObject obj = new JObject();
try
{
List <ElementInfo> structList;
ExcelLogin login;
ISourceHandle handle;
QueryDepictInfo info;
YZRequest request = new YZRequest(context);
string joinType = request.GetString("jointype");
if (joinType.ToUpper().Equals("THEADD"))
{
//新增模式
int connectId = request.GetInt32("connectId");
ConnectInfo connectInfo = ConnectInfoManager.GetConnectInfo(connectId);
//生成连接对象
login = JObject.Parse(connectInfo.connectStr).ToObject <ExcelLogin>();
//实例化连接对象
handle = new ExcelHandle(login);
//获取接口集合
structList = handle.GetElementList();
info = new QueryDepictInfo();
}
else
{
//编辑模式
int sourceId = request.GetInt32("sourceId");
//模糊查询表或视图的名称
string sheetName = request.GetString("sheetName", "");
SourceInfo sourceInfo = SourceInfoManager.GetSourceInfo(sourceId);
//生成连接对象
login = JObject.Parse(sourceInfo.connectInfo.connectStr).ToObject <ExcelLogin>();
//实例化连接对象
handle = new ExcelHandle(login);
//获取接口集合
structList = handle.GetElementList(sheetName);
info = JObject.Parse(sourceInfo.sourceStr).ToObject <QueryDepictInfo>();
}
//获取接口集合
if (structList != null && structList.Count > 0)
{
JArray sheetArray = new JArray();
foreach (var item in structList)
{
JObject jo = new JObject();
jo["name"] = item.elementName;
sheetArray.Add(jo);
}
obj["sheetArray"] = sheetArray;
obj["schema"] = GetColumnJson(info);
obj[YZJsonProperty.success] = true;
}
else
{
obj[YZJsonProperty.success] = false;
obj[YZJsonProperty.errorMessage] = "没有可供查询的sheet表！";
return(obj);
}
return(obj);
}
catch (Exception ex)
{
obj[YZJsonProperty.success] = false;
obj[YZJsonProperty.errorMessage] = ex.ToString();
return(obj);
}
}
/// <summary>
/// 保存数据源
/// </summary>
/// <param name="context"></param>
/// <returns></returns>
public JObject SaveSource(HttpContext context)
{
JObject obj = new JObject();
try
{
YZRequest request = new YZRequest(context);
string joinType = request.GetString("joinType");
string sourceName = request.GetString("sourceName");
string caption = request.GetString("caption", "");
int connectId = request.GetInt32("connectId");
int sourceId = request.GetInt32("sourceId", 0);
string sheetName = request.GetString("sheetName");
string titleIndex = request.GetString("titleIndex");
string query = string.Format("{0}|{1}", sheetName, titleIndex);
//判断是否重名
bool isNameOnly = SourceInfoManager.IsSourceNameRepeat(sourceName, SourceTypeEnum.Excel, sourceId);
if (isNameOnly)
{
obj[YZJsonProperty.success] = false;
obj[YZJsonProperty.errorMessage] = "已存在同名的数据源，请重命名数据源名称！";
return(obj);
}
ConnectInfo connectInfo = ConnectInfoManager.GetConnectInfo(connectId);
ExcelLogin login = JObject.Parse(connectInfo.connectStr).ToObject <ExcelLogin>();
ISourceHandle handle = new ExcelHandle(login);
QueryDepictInfo info = handle.GetQueryInfo(sheetName);
info.query = query;
info = handle.GetQueryInfo(info);
StreamReader reader = new StreamReader(context.Request.InputStream);
using (JsonTextReader streamReader = new JsonTextReader(reader))
{
//获取参数配置
JsonSerializer serializer = new JsonSerializer();
JArray array = serializer.Deserialize(streamReader) as JArray;
List <ColumnInfo> param = new List <ColumnInfo>();
foreach (var item in array)
{
if (item["inputIsShow"].ToString().ToUpper().Equals("TRUE"))
{
ColumnInfo column = new ColumnInfo();
column.columnName = item["columnName"].ToString();
column.columnType = item["columnType"].ToString();
column.defaultValue = item["defaultValue"].ToString();
column.isShow = 1;
column.rename = item["inputRename"].ToString();
param.Add(column);
}
ColumnInfo schemaColumn = info.schema.First(n => n.columnName == item["columnName"].ToString());
if (item["outputIsShow"].ToString().ToUpper().Equals("TRUE"))
{
schemaColumn.rename = item["outputRename"].ToString();
schemaColumn.isShow = 1;
}
else
{
schemaColumn.isShow = 0;
}
}
info.parameter = param;
}
if (joinType.ToUpper().Equals("THEEDIT"))
{
SourceInfo sourceInfo = SourceInfoManager.GetSourceInfo(sourceId);
sourceInfo.sourceName = sourceName;
sourceInfo.caption = caption;
sourceInfo.updateTime = DateTime.Now;
sourceInfo.sourceStr = JObject.FromObject(info).ToString();
SourceInfoManager.EditSource(sourceInfo);
}
else
{
SourceInfo sourceInfo = new SourceInfo();
sourceInfo.sourceName = sourceName;
sourceInfo.sourceType = SourceTypeEnum.Excel;
sourceInfo.connectInfo = ConnectInfoManager.GetConnectInfo(connectId);
sourceInfo.caption = caption;
sourceInfo.isvalid = true;
sourceInfo.createTime = DateTime.Now;
sourceInfo.updateTime = DateTime.Now;
sourceInfo.sourceStr = JObject.FromObject(info).ToString();
SourceInfoManager.AddSource(sourceInfo);
}
obj[YZJsonProperty.success] = true;
obj[YZJsonProperty.errorMessage] = "保存成功";
return(obj);
}
catch (Exception ex)
{
obj[YZJsonProperty.success] = false;
obj[YZJsonProperty.errorMessage] = ex.Message.ToString();
return(obj);
}
}
/// <summary>
/// 返回结果
/// </summary>
/// <param name="context"></param>
/// <returns></returns>
public JObject GetResult(HttpContext context)
{
JObject obj = new JObject();
try
{
YZRequest request = new YZRequest(context);
int connectId = request.GetInt32("connectId");
string sheetName = request.GetString("sheetName");
string titleIndex = request.GetString("titleIndex");
string query = string.Format("{0}|{1}", sheetName, titleIndex);
ConnectInfo connectInfo = ConnectInfoManager.GetConnectInfo(connectId);
ExcelLogin login = JObject.Parse(connectInfo.connectStr).ToObject <ExcelLogin>();
ISourceHandle handle = new ExcelHandle(login);
QueryDepictInfo info = handle.GetQueryInfo(sheetName);
info.query = query;
info = handle.GetQueryInfo(info);
StreamReader reader = new StreamReader(context.Request.InputStream);
using (JsonTextReader streamReader = new JsonTextReader(reader))
{
//获取参数配置
JsonSerializer serializer = new JsonSerializer();
JArray array = serializer.Deserialize(streamReader) as JArray;
List <ColumnInfo> param = new List <ColumnInfo>();
foreach (var item in array)
{
if (item["inputIsShow"].ToString().ToUpper().Equals("TRUE"))
{
ColumnInfo column = new ColumnInfo();
column.columnName = item["columnName"].ToString();
column.columnType = item["columnType"].ToString();
column.defaultValue = item["defaultValue"].ToString();
column.isShow = 1;
column.rename = item["inputRename"].ToString();
param.Add(column);
}
ColumnInfo schemaColumn = info.schema.First(n => n.columnName == item["columnName"].ToString());
if (item["outputIsShow"].ToString().ToUpper().Equals("TRUE"))
{
schemaColumn.rename = item["outputRename"].ToString();
schemaColumn.isShow = 1;
}
else
{
schemaColumn.isShow = 0;
}
}
info.parameter = param;
}
//访问
DataTable dt = handle.GetResult(info);
JArray results = new JArray();
if (dt.Rows.Count > 0)
{
foreach (DataRow row in dt.Rows)
{
JObject item = new JObject();
foreach (DataColumn column in dt.Columns)
{
item[column.ColumnName] = row[column.ColumnName].ToString();
}
results.Add(item);
}
}
else
{
JObject item = new JObject();
foreach (DataColumn column in dt.Columns)
{
item[column.ColumnName] = "";
}
results.Add(item);
}
obj["children"] = results;
//执行数据
obj[YZJsonProperty.success] = true;
return(obj);
}
catch (Exception ex)
{
obj[YZJsonProperty.success] = false;
obj[YZJsonProperty.errorMessage] = ex.ToString();
return(obj);
}
}
