Excel导入数据到U9系统

使用Excel导入数据到U9系统，示例代码：

using System;

using System.Text;

using System.Collections;

using System.Xml;

using System.Data;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Resources;

using System.Reflection;

using System.Globalization;

using System.Threading;

using Telerik.WebControls;

using UFSoft.UBF.UI.WebControls;

using UFSoft.UBF.UI.Controls;

using UFSoft.UBF.Util.Log;

using UFSoft.UBF.Util.Globalization;

using UFSoft.UBF.UI.IView;

using UFSoft.UBF.UI.Engine;

using UFSoft.UBF.UI.MD.Runtime;

using UFSoft.UBF.UI.ActionProcess;

using UFSoft.UBF.UI.WebControls.ClientCallBack;

using System.Collections.Generic;

using System.IO;

using System.Data.OleDb;

using System.Text.RegularExpressions;

using UFSoft.UBF.UI.FormProcess;

using UFSoft.UBF.UI;

using UFIDA.U9.CS.UI.Controls;

using System.Linq;

using Microsoft.Win32;

/***********************************************************************************************

- Form ID:

- UIFactory Auto Generator

***********************************************************************************************/

namespace UFIDA.U9.Cust.TLJXUI

{

public partial class CustUploadUIFormWebPart

{

#region Custome eventBind private UploadFileCtrl upload;

//BtnClose_Click...

private void BtnClose_Click_Extend(object sender, EventArgs e)

{

//调用模版提供的默认实现.--默认实现可能会调用相应的Action.

this.CloseDialog(true);

this.CurrentSessionState["CreateSOID"] = 0;

BtnClose_Click_DefaultImpl(sender,e);

}

//BtnOk_Click...

private void BtnOk_Click_Extend(object sender, EventArgs e)

{

//调用模版提供的默认实现.--默认实现可能会调用相应的Action.

ViewState["GridInfo"] = "";

//调用模版提供的默认实现.--默认实现可能会调用相应的Action.

this.Model.ClearErrorMessage();

//this.Model.Clear();

OnDataCollect(this); //当前事件先执行数据收集

//调用模版提供的默认实现.--默认实现可能会调用相应的Action.

this.IsDataBinding = true; //当前事件执行后会进行数据绑定

this.IsConsuming = false;

if (upload.HttpPostedFile == null || upload.HttpPostedFile.FileName.Length == 0)

{

return;

}

string path = System.Web.HttpContext.Current.Server.MapPath("~/FileStreamLog");//tmp为服务器上文件夹默认为wwwboot

if (!Directory.Exists(path)) Directory.CreateDirectory(path);

string FileType = upload.HttpPostedFile.FileName;

FileType = FileType.Substring(FileType.LastIndexOf('.') + 1);

string file = "";

file = Path.Combine(path, DateTime.Now.ToString("yyyyMMddhhmmssms") + "File." + FileType.ToLower() + "");

upload.HttpPostedFile.SaveAs(file);

DataSet dataDs = ReadExcel(file);

if (dataDs == null || dataDs.Tables.Count == 0 || dataDs.Tables[0].Rows.Count == 0)

{

ShowAlertMessage(this, "导入数据为空！");

return;

}

}

else

{

ShowAlertMessage(this, "导入数据来源不明确！");

}

}

BtnOk_Click_DefaultImpl(sender,e);

}

///

/// filepath

/// dataset

private DataSet ReadExcel(string _file)

{

//RegistryKey reg_TypeGuessRows = Registry.LocalMachine.CreateSubKey(@"SOFTWARE\Microsoft\Jet\4.0\Engines\Excel");

//reg_TypeGuessRows.SetValue("TypeGuessRows", 0);

DataSet _dst = new DataSet();

Hashtable dsTable = new Hashtable();

string OledbConnectionString = string.Empty;

FileInfo fi = new FileInfo(_file);

if (fi.Extension.Equals(".xls"))

{

// Work for xls files(Execl2000和Execl2003版本)

OledbConnectionString = string.Format("Provider=Microsoft.Ace.OLEDB.12.0;Data Source='" + _file + "';Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'");

}

else if (fi.Extension.Equals(".xlsx"))

{

// Work for xlsx files(Execl2007及以后的版本)

OledbConnectionString = string.Format("Provider=Microsoft.Ace.OLEDB.12.0;Data Source='" + _file + "';Extended Properties='Excel 12.0;HDR=Yes;IMEX=1'");

}

OleDbConnection conn = new OleDbConnection(OledbConnectionString);

if (conn.State == ConnectionState.Closed)

{

try

{

conn.Open();

}

catch (Exception e)

{

//throw new Exception("打开文件报错"+e.Message);

ShowAlertMessage(this, "打开文件报错" + e.Message);

return null;

}

}

DataTable dtExcelSchema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });

// foreach (DataRow dtSheet in dtExcelSchema.Select("TABL