Excel导入数据集.docx

using System;using System.Data;using System.Data.OleDb;using System.IO;using System.Configuration;using System.Text;using System.Web;using System.Xml;using System.Web.Security;using System.Threading;using System.Web.UI;using System.Text.RegularExpressions;using System.Runtime.InteropServices;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.Web.UI.HtmlControls;using System.Collections;/ / BaseFunction 的摘要说明/ public class BaseFunction private static BaseFunction obj = new BaseFunction(); public BaseFunction() / / TODO: 在此处添加构造函数逻辑 / #region 配置文件操作方法 /调用API DllImport(kernel32) private static extern int GetPrivateProfileInt( string lpApplicationName, string lpKeyName, int nDefault, string lpFileName); DllImport(kernel32) private static extern bool GetPrivateProfileString( string lpApplicationName, string lpKeyName, string lpDefault, StringBuilder lpReturnedString, int nSize, string lpFileName); DllImport(kernel32) private static extern bool WritePrivateProfileString( string lpApplicationName, string lpKeyName, string lpString, string lpFileName); DllImport(kernel32) private static extern bool GetPrivateProfileSection( string lpAppName, StringBuilder lpReturnedString, int nSize, string lpFileName); DllImport(kernel32) private static extern bool WritePrivateProfileSection( string lpAppName, string lpString, string lpFileName); / / 写配置文件 / / 段落名 / 节点名 / 节点值 / 要读取的文件名(不包括.ini) public static bool WriteIni(string Section, string Key, string KeyValue, string strName) return WritePrivateProfileString(Section, Key, KeyValue, strName); / / 读配置文件 / / 段落名 / 节点名 / 要读取的文件名(不包括.ini) / public static string ReadIni(string Section, string Key, string strName) StringBuilder Temp = new StringBuilder(1000); GetPrivateProfileString(Section, Key, , Temp, 1000, strName); string rtn = Temp.ToString(); Temp = null; return rtn; / / 读取一段SQL语句 / / / / public static string ReadSectionIni(string Section, string strName) StringBuilder Temp = new StringBuilder(1000); GetPrivateProfileSection(Section, Temp, 1000, strName); string rtn = Temp.ToString(); Temp = null; return rtn; #endregion #region 上传数据操作方法 / / 取得上传文件的数据集 / / 上传文件扩展名 / 上传文件名 / 上传的路径 / 拆分字符或Excel表名 / 要导入的数据字段数组 / public static DataSet GetOleDbData(string strUpFileExtName, string strUpFileName, string strUpPath, string strSplitChar, string aryField, bool preview) string strDBDatabaseName, strDBTableName; string DBSQL; if (preview) DBSQL = select top 10 * from 0; else DBSQL = select * from 0; string oleConnStr = Provider=Microsoft.Jet.OLEDB.4.0;Data Source=0;Extended Properties=1; switch (strUpFileExtName) case .txt: /创建配置文件 BaseFunction.WriteIni(strUpFileName, ColNameHeader, False, strUpPath + Schema.ini); BaseFunction.WriteIni(strUpFileName, Format, Delimited( + strSplitChar + ), strUpPath + Schema.ini); BaseFunction.WriteIni(strUpFileName, MaxScanRows, 0, strUpPath + Schema.ini); BaseFunction.WriteIni(strUpFileName, CharacterSet, OEM, strUpPath + Schema.ini); for (int i = 0; i aryField.Length) /上传的字段不合法 throw new Exception(上传的文件字段不符合规则!列数为 + upDs.TablesUpTable.Columns.Count.ToString(); catch (Exception ex) throw new Exception(查询上传数据时出错！ + ex.Message); finally if (dbdat != null) dbdat.Dispose(); dbdat = null; if (oleConnection != null) oleConnection.Dispose(); oleConnection = null; return upDs; #endregion #region 程序常量保存表 private static Hashtable hsUpNum = new Hashtable(); /保存上传用数据 public static string GetHsUpNum(string strID) if (hsUpNumstrID = null) return -1,0,没有处理数据; else return hsUpNumstrID.ToString(); public static void SetHsUpNum(string strID, string strValue) if (hsUpNumstrID = null) hsUpNum.Add(strID, strValue); else hsUpNumstrID = strValue; #endregion #region 常用函数 / / 判断字串是否为数值 / / 要判断的字串 / public static bool IsNumeric(string str) return Regex.IsMatch(str, +-?d*.?d*$); / / 判断字串是否是整数 / / 要判断的字串 / public static bool IsInt(string str) return Regex.IsMatch(str, +-?d*$); / / 判断字串是否是无符号整数 / / 要判断的字串 / public static bool IsUnsign(string str) return Regex.IsMatch(str, d*.?d*$); public static bool IsTelno(ref string str) str = str.Trim(); str = str.Replace(n, ).Replace(t, ).Replace(r, ); if (str.Length = 0) return false; if (IsUnsign(str) return true; return false; / / 改变当前日期对象为无符号字符串 / / 要改变的日期对象 / private static string ChangeDateToStr(DateTime dChangeDate) string strFDate = dChangeDate.Year.ToString(); strFDate += dChangeDate.Month.ToString().PadLeft(2, 0); strFDate += dChangeDate.Day.ToString().PadLeft(2, 0); strFDate += dChangeDate.Hour.ToString().PadLeft(2, 0); strFDate += dChangeDate.Minute.ToString().PadLeft(2, 0); strFDate += dChangeDate.Second.ToString().PadLeft(2, 0); return strFDate; private static string ChangeDateToDateStr(DateTime dt) string strFDate = dt.Year.ToString(); strFDate += dt.Month.ToString().PadLeft(2, 0); strFDate += dt.Day.ToString().PadLeft(2, 0); return strFDate; / / 取得固定格式的日期(当前系统时间) / 如:20030102152312 / 表示:2003-1-2 15:23:12 / / public static string GetFormatDate() return ChangeDateToStr(DateTime.Now); public static string GetDateString() return ChangeDateToDateStr(DateTime.Now); / / 取得固定格式的日期 / 所给定的字符串日期 / / 要取得的日期字符串 / public static string GetFormatDate(string strDate) DateTime tTmpDate = DateTime.Parse(strDate); return ChangeDateToStr(tTmpDate); / / 取得指定长度的随机字串 / / 要取得的字串长度 / public static string GetRandom(int len) char ary = new char1,2,3,4,5,6,7,8,9,0,/a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z,A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z; Random rd = new Random(); string strRandom = ; for (int i = 0; i len; i+) strRandom += aryrd.Next(ary.Length).ToString(); return strRandom; / / 清除页面上控件的值 / / 包含控件的容器 public static void ClearnControls(ControlCollection CtlList) foreach (Control ctl in CtlList) if (ctl is TextBox) (TextBox)ctl).Text = ; else if (ctl is DropDownList) (DropDownList)ctl).ClearSelection(); else if (ctl is WebCalendar.Calendar) (WebCalendar.Calendar)ctl).Text = ; else if (ctl is RadioButtonList) (RadioButtonList)ctl).ClearSelection(); else if (ctl is CheckBox) (CheckBox)ctl).Checked = false; else if (ctl is HtmlTableRow) ClearnControls(HtmlTableRow)ctl).Controls); else if (ctl is HtmlTableCell) ClearnControls(HtmlTableCell)ctl).Controls); else if (ctl is Table) ClearnControls(Table)ctl).Controls); else if (ctl is Panel) ClearnControls(Panel)ctl).Controls); / / 设置页面上控件的状态 / / 包含控件的容器 / 要设置的状态,true,bool public static void SetControlsState(ControlCollection CtlList, bool bState) foreach (Control ctl in CtlList) if (ctl is TextBox) (TextBox)ctl).Enabled = bState; else if (ctl is DropDownList) (DropDownList)ctl).Enabled = bState; else if (ctl is WebCalendar.Calendar) (WebCalendar.Calendar)ctl).Enabled = bState; else if (ctl is RadioButtonList) (RadioButtonList)ctl).Enabled = bState; else if (ctl is ImageButton) (ImageButton)ctl).Visible = bState; else if (ctl is CheckBox) (CheckBox)ctl).Enabled = bState; else if (ctl is HtmlTableRow) SetControlsState(HtmlTableRow)ctl).Controls, bState); else if (ctl is HtmlTableCell) SetControlsState(HtmlTableCell)ctl).Controls, bState); else if (ctl is Table) SetControlsState(Table)ctl).Controls, bState); else if (ctl is Panel) SetControlsState(Panel)ctl).Controls, bState); / / 取得英文字段的中文名称,或中文字段的英文名称 / / 要取得的英文字段 / public static string GetFiledChsName(string strFileName) if (strFileName.IndexOf(_) = 0) return strFileName; Hashtable hs = new Hashtable(); string strRtn = strFileName; try strRtn = hsstrFileName.ToLower().ToString(); catch strRtn = strFileName; hs = null; return strRtn; / / 绑定下拉框 / / 要绑定的数据集 / 要绑定的文本字段 / 要绑定的值 / 要绑定的下拉框 public static void BindDropList(DataView myDv, string strText, string strValue, DropDownList dw) dw.DataSource = myDv; dw.DataTextField = strText; dw.DataValueField = strValue; dw.DataBind(); / / 绑定选择框 / / 要绑定的数据集 / 要绑定的文本字段 / 要绑定的值 / 要绑定的选择框 public static void BindListBox(DataView myDv, string strText, string strValue, ListBox lb) lb.DataSource = myDv; lb.DataTextField = strText; lb.DataValueField = strValue; lb.DataBind(); / / 绑定时间和分钟下拉框 / / 要绑定的时间下拉框 / 要绑定的分钟下拉框 public static void BindTime(DropDownList ddHour, DropDownList ddMinute) for (int i = 0; i 60; i+) ddMinute.Items.Add(new ListItem(i.ToString().PadLeft(2, 0), i.ToString().PadLeft(2, 0); if (i length) byte b2 = new bytelength; for (int i = 0; i length; i+) b2i = b1i; return System.Text.Encoding.Default.GetString(b2); return str; #endregion #region 写文件操作 / / 添加日志文件 / / 要添加的日志内容 public static void AddDayLog(string strAddText) lock (obj) string strFileName = DateTime.Now.Year.ToString() + DateTime.Now.Month.ToString().PadLeft(2, 0) + DateTime.Now.Day.ToString().PadLeft(2, 0) + .txt; string strPathName = System.Web.HttpContext.Current.Request.ApplicationPath; strPathName = System.Web.HttpContext.Current.Server.MapPath(strPathName); string strPath = strPathName + /SysLogFile/ + strFileName; if (!File.Exists(strPath) try FileStream fs = new FileStream(strPath, FileMode.OpenOrCreate); fs.Close(); catch return; string strErrInfo = ; StreamWriter sw = File.AppendText(strPath); try strErrInfo += -n; strErrInfo += 时间: + DateTime.Now.ToShortDateString() + + DateTime.Now.ToLongTimeString() + n; string aryTmp = strAddText.Split(