C#导入Excel表格功能aspx.cs，代码
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.OleDb;
using System.IO;
using jxl;
using jxl.write;
using jxl.format;
using java.io;
using java.util;
using java.net;
using java.text;
public partial class Modules_ClassHR_UplRecordList : System.Web.UI.Page
{
const string ROLEADMIN = "TechClass_ADM" ; --const定义常量字符串
const string TechClass_HR = "TechClass_HR" ;
#region Page_Load
protected void Page_Load(object sender , EventArgs e)
{
this .Page.Title=System.Configuration.ConfigurationManager.AppSettings[ "WebSiteTitle" ].ToString();
if (!User.Identity.IsAuthenticated)
{
Response.Redirect(System.Configuration.ConfigurationManager.AppSettings[ "urlPermissionDenied" ].ToString());
return;
}
string v_domin= " " ;
string v_domainaccount = " " ;
string v_empID = " " ;
string systemid = ConfigurationManager.AppSettings[ "SystemID" ].ToString( );
if (!IsPostBack)
{
if ((Page.User.Identity.AuthenticationType.ToString() == "Negotiate" ) || (Page.User.Identity.AuthenticationType.ToString() == "NTLM" ))
{
if (Page.User.Identity.Name.IndexOf( '\\' ) < 1)
{
Response.Redirect(System.Configuration.ConfigurationManager.AppSettings[ "urlMappingErrorPage" ].ToString(), true);
return;
}
v_domain = Page.User.Identity.Name.Split( '\\' )[0].ToString();
v_domainaccount = Page.User.Identity.Name.Split( '\\' )[1].ToString();
v_empID = Coeno.Account. Users .GetUserEmpID(v_domain.ToUpper(), v_domainaccount.ToUpper());
}
else
{
v_empID = Page.User.Identity.Name;
}
if (v_empID == " " )
{
Response.Redirect(System.Configuration. ConfigurationManager .AppSettings[ "urlMappingErrorPage" ].ToString(), true);
return;
}
if (!Coeno.Main.Roles.IsUserInRole(systemid, v_empID, ROLEADMIN) && !Coeno.Main.Roles.IsUserInRole(systemid, v_empID, TechClass_HR))
{
Response.Redirect(System.Configuration.ConfigurationManager.AppSettings[ "urlPermissionDenied" ].ToString(), true);
return;
}
txtCurrentEmpID.Text = v_empID;
}
}
#endregion
protected void BtnUpload_Click(object sender, EventArgs e)
{
HttpPostedFile file = uploadFile.PostedFile;
if (file.FileName.Length==0)
{
lblMsg.Text= " 请选择一个要上传的文件！" ;
lblMsg.ForeColor=System.Drawing.Color.Red;
return ;
}
if (Path.GetExtension(file.FileName) != ".xls" )
{
lblMsg.Text= " 请选择一个.xls为后缀的文件" ;
lblMsg.ForeColor=System.Drawing.Color.Red;
return;
}
string filename=txtCurrentEmpID.Text+System.DateTime.NowToString( "yyyyMMddHHmmss" )+Path.GetExtension(fileName);
string filePath = "~/UploadFiles/HRUplEmpQuery/" + filename;
file.SaveAs(Server.MapPath(filePath));
int count = 0;
int m = 0;
OleDbConnection OleConn = new OleDbConnection( "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath(filePath) + ";Extended Properties=\"Excel 8.0;IMEX=1;\";" );
try
{
OleDbCommand OleDbCmd = new OleDbCommand( "SELECT * FROM [Sheet1$]", OleConn );
OleDbCommand OleDbCmdc = new OleDbCommand( "SELECT count(*) FROM [Sheet1$]" , OleConn);
OleConn.Open();
OleDbDataReader odr = OleDbCmd.ExecuteReader();
count = Convert.ToInt32(OleDbCmdc.ExecuteScalar());
lblGUID.Text = " " ;
lblGUID.Text = Guid.NewGuid().ToString();
//上傳資料
DataTable dt = new DataTable();
dt.Columns.Add( "PeriodID" , typeof(string));//1
dt.Columns.Add( "EmpID" , typeof(string));//2
dt.Columns.Add( "EmpName" , typeof(string));//3
dt.Columns.Add( "RecDayID" , typeof(string));//3
dt.Columns.Add( "RecHourID" , typeof(string));//4
dt.Columns.Add( "RecMinID" , typeof(string));//5
dt.Columns.Add( "DeviceID" , typeof(string));
dt.Columns.Add( "IOFlag" , typeof(string));
dt.Columns.Add( "RecTime" , typeof(string));
dt.Columns.Add( "CUser" , typeof(string));
DataRow dr;
while (odr.Read())
{
dr = dt.NewRow();
dr[0] = lblGUID.Text;
dr[1] = odr[0].ToString();
dr[2] = odr[1].ToString();
dr[3] = odr[2].ToString();
dr[4] = odr[3].ToString().Substring(0,2);
dr[5] = odr[3].ToString().Substring(3, 2);
dr[6] = odr[4].ToString();
dr[7] = odr[5].ToString();
dr[8] = odr[2].ToString() + ' ' + odr[3].ToString();
dr[9] = Coeno.Utility.String.CleanUpInput(txtCurrentEmpID.Text);
dt.Rows.Add(dr);
}
odr.Close();
//修改
if (dt.Rows.Count > 0)
{
string returnstatus = " " ;
string returnid = " " ;
string returnmsg = " " ;
string Num = " " ;
string NumOK = " " ;
string NumErr = " " ;
int ecode = Coeno.TechClass.ClassHR.RecordInsByExcel(dt);
if (ecode == 0)
{
Coeno.TechClass.ClassHR.RecordInsExcelCheck(dt, out returnstatus, out returnid, out returnmsg, out Num, out NumOK, out NumErr);
}
else
{
lblMsg.Text = "資料有誤!" ;
lblMsg.ForeColor = System.Drawing.Color.Red;
return;
}
gvDataBind(lblGUID.Text);
if (returnstatus == "1" )
{
pnlRecordList.Visible = true ;
lblMsg.Text = "共上傳：" + Num + "筆，成功：" + NumOK + "筆，失敗：" + NumErr + "筆" ;
lblMsg.ForeColor = System.Drawing.Color.Red;
}
else
{
pnlRecordList.Visible = false ;
lblMsg.Text =returnmsg;
}
}
}
catch(Exception ex)
{
pnlRecordList.Visible = false ;
lblMsg.Text = ex.Message;
}
pnlRecordList.Visible = true ;
}
protected void gvDataBind(string v_GUID)
{
gvRecordList.DataSource = null ;
gvRecordList.DataBind();
DataTable dt = Coeno.TechClass.ClassHR.QueryRecordProcList(lblGUID.Text);
if (dt.Rows.Count > 0)
{
gvRecordList.DataSource = dt;
gvRecordList.DataBind();
}
}
}