.net怎么讲excel表的一列导入到数据库某张表的一列?
追问:貌似挺复杂的哦。。有一张学生信息表,成绩那一列没有数据,我想先导出,然后添加每个考生的成绩,然后再倒入到数据库中。。。我只会导出所有的数据,嘿嘿。。。
追问:貌似挺复杂的哦。。有一张学生信息表,成绩那一列没有数据,我想先导出,然后添加每个考生的成绩,然后再倒入到数据库中。。。我只会导出所有的数据,嘿嘿。。。
答案:using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.IO;
using System.Data.OleDb;
using System.Data;
using System.Web.Services;
public partial class admin_Att_ExcelImporting : System.Web.UI.Page
{
static string puth = "";
protected void Page_Load(object sender, EventArgs e)
{
}
[WebMethod]
public static List<string> getExcelSheetNames()
{
OleDbConnection conn = null;
try
{
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + puth + ";" + "Extended Properties=Excel 8.0;";
conn = new OleDbConnection(strConn);
conn.Open();
DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
List<string> strli = new List<string>();
foreach (DataRow dr in dt.Rows)
{
strli.Add((String)dr["TABLE_NAME"]);
}
conn.Close();
return strli;
}
catch (Exception)
{
if (conn != null)
conn.Close();
return null;
}
}
public DataSet ExcelToDS(string sheetName)
{
OleDbConnection conn = null;
try
{
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + puth + ";" + "Extended Properties=Excel 8.0;";
conn = new OleDbConnection(strConn);
conn.Open();
string strExcel = "";
OleDbDataAdapter myCommand = null;
DataSet ds = null;
strExcel = "select * from ["+sheetName+"]";
myCommand = new OleDbDataAdapter(strExcel, strConn);
ds = new DataSet();
myCommand.Fill(ds, "sheetName");
return ds;
}
catch (Exception ex)
{
if (conn != null)
{
conn.Close();
}
Tool.LogWrite.WriteLog(ex);
return null;
}
}
protected void AsyncFileUpload1_UploadedComplete(object sender, AjaxControlToolkit.AsyncFileUploadEventArgs e)
{
if (System.IO.Path.GetExtension(e.FileName) == ".xls")
{
AjaxControlToolkit.AsyncFileUpload au = sender as AjaxControlToolkit.AsyncFileUpload;
string nowputh = Server.MapPath(".");
DirectoryInfo dir = new DirectoryInfo(nowputh);
string upputh = dir.Parent.FullName + "\\TemporaryExcel\\";
if (!Directory.Exists(upputh))
Directory.CreateDirectory(upputh);
Random random = new Random();
string randomNum = random.Next(100000, 999999).ToString();
upputh += randomNum.ToString();
upputh += System.IO.Path.GetExtension(e.FileName);
puth = upputh;
if (au != null && au.HasFile)
au.SaveAs(upputh);
}
else
{
}
}
[WebMethod]
public static string Emp_ExcelImporting(string sheet,string type)
{
int rowcount = 0;
int rowerr = 0;
string message = "";
//TR.BLL.Org_PositionBll orgpbll = new TR.BLL.Org_PositionBll();
//TR.BLL.Org_OrganizationBll orgbll=new TR.BLL.Org_OrganizationBll();
TR.BLL.Emp_EmployeesBasicBLL empbll=new TR.BLL.Emp_EmployeesBasicBLL();
TR.BLL.Sys_TypeBll sysbll=new TR.BLL.Sys_TypeBll();
try
{
admin_Att_ExcelImporting ff = new admin_Att_ExcelImporting();
DataSet ds = ff.ExcelToDS(sheet);
if (ds != null && ds.Tables.Count > 0)
{
DataTable dt = ds.Tables[0];
if (type=="2")
{
foreach (DataRow r in dt.Rows)
{
try
{
rowcount++;
int empid = empbll.GetEmpIdByJobNumber(r[0].ToString());
int systypeId = sysbll.GetTypeIdByTypeName(r[2].ToString());
if (empid == 0)
{
message += "插入第" + rowcount + "条数据时,社番号不正确,插入失败,已跳过!</br>";
rowerr++;
}
else if (systypeId == 0)
{
message += "插入第" + rowcount + "条数据时,检测到'" + r[2] + "'请假类型不存在,插入失败,已跳过!</br>";
rowerr++;
}
else
{
Object obj = Tool.SQLHelper.ExecuteScalar(CommandType.Text, "select id from Att_Leave where empId=" + empid + " and typeId="+systypeId+" and DATEDIFF(day,'" + DateTime.Now.ToString("yyyy-MM-dd") + "',addTime)=0", null);
string str = "";
if (obj==null)
{
str = "INSERT INTO [dbo].[Att_Leave]([empId],[typeId],[leaveTime1],[leaveTime2],[days],[leaveReason],[isEnd],[added],[status])VALUES(" + empid + " ," + systypeId + ",'" + r[3] + "','" + r[4] + "'," + r[5] + ",'" + r[6] + "',1,'" + ff.Session["User_Nickname"] + "',1)";
}
else
{
str = "update Att_Leave set typeId=" + systypeId + ",leaveTime1='"+r[3]+"',leaveTime2='"+r[4]+"',days="+r[5]+",leaveReason='"+r[6]+"',isEnd=1,added='"+ff.Session["User_Nickname"]+"',status=1 where id="+Convert.ToInt32(obj);
}
Tool.SQLHelper.ExecuteNonQuery(CommandType.Text, str, null);
}
}
catch (Exception)
{
message += "插入第" + rowcount + "条数据时发生错误(可能是数据格式存在问题),已跳过!</br>";
rowerr++;
}
}
}
else
{
foreach (DataRow r in dt.Rows)
{
try
{
rowcount++;
int empid = empbll.GetEmpIdByJobNumber(r[0].ToString());
int systypeId = sysbll.GetTypeIdByTypeName(r[2].ToString());
if (empid == 0)
{
message += "插入第" + rowcount + "条数据时,社番号不正确,插入失败,已跳过!</br>";
rowerr++;
}
else if (systypeId == 0)
{
message += "插入第" + rowcount + "条数据时,检测到'" + r[2] + "'加班类型不存在,插入失败,已跳过!</br>";
rowerr++;
}
else
{
Object obj =Tool.SQLHelper.ExecuteScalar(CommandType.Text,"select id from Att_Overtime where empId="+empid+" and typeId="+systypeId+" and datediff(day,'"+DateTime.Now+"',addTimes)=0",null);
string str = "";
if (obj == null)
{
str = "INSERT INTO [dbo].[Att_Overtime]([empId],[typeId],[overtime],[addTime],[added],[status]) VALUES (" + empid + "," + systypeId + "," + r[3] + ",'" + r[4] + "','" + ff.Session["User_Nickname"] + "',1)";
}
其他:看你不会的是哪一步?
1.读出excel的一列;
2.连接数据库;
3.执行sql语句
问题再细化一点吧
上一个:请SQL或EXCEL高手帮忙:利用SQL建立一个图库,没个图片加一个标签,以后输入标签就能查出相应的图片
下一个:在一个aspx页面中打开了一个excel表,想把表上的数据写到后台数据库中