使用vs2008建立小网站,实现将excel文件中的内容一次性全部导入到SQL中,希望能有人帮助我。。 使用vs2008建立小网站,实现将excel文件中的内容一次性全部导入到SQL中,希望能有人帮助我。 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 如果数据格式标准的话,直接用oledbconnection来打开excel,将数据将数据读出来,然后再导入到sql数据库中。提供思路,代码要自己研究。 http://blog.csdn.net/fangxinggood/archive/2005/06/18/397315.aspx 环境:c#.2005+Access+ Sql 一、把DataTable插入数据库public static void DataTableToDB(){ string _strExcelFileName = @"D:\example.xls"; DataTable dtExcel = ExcelToDataTable(_strExcelFileName,"Sheet1"); for (int i = 0; i < dtExcel.Rows.Count; i++) { InsertDataToAccess(dtExcel.Rows[i][0].ToString(), float.Parse(dtExcel.Rows[i][1].ToString())); }}二、把Excel数据读入DataTablepublic static DataTable ExcelToDataTable(string strExcelFileName, string strSheetName){ string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + strExcelFileName + ";" +"Extended Properties=Excel 5.0;"; string strExcel = string.Format("select * from [{0}$]", strSheetName); DataSet ds = new DataSet(); using (OleDbConnection conn = new OleDbConnection(strConn)) { conn.Open(); OleDbDataAdapter adapter = new OleDbDataAdapter(strExcel, strConn); adapter.Fill(ds, strSheetName); conn.Close(); } return ds.Tables[strSheetName];}三、向Access数据库表插入数据public static void InsertDataToAccess(string _strPara,float _fPara){ OleDbConnection oleDbConn = new OleDbConnection(); oleDbConn.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\ExcelData.mdb;User Id=admin;Password=;"; oleDbConn.Open(); string strInsertString = "INSERT INTO tb_excelData (strCollumn1,fCollumn2) VALUES (@strCollumn1,@fCollumn2)"; OleDbCommand oComm = new OleDbCommand(strInsertString, oleDbConn); oComm.Parameters.Add("@strCollumn1", OleDbType.Char , 50); oComm.Parameters["@strCollumn1"].Value = _strPara; oComm.Parameters.Add("@fCollumn2", OleDbType.Double); oComm.Parameters["@fCollumn2"].Value = _fPara; ocomm.ExecuteNonQuery(); oleDbConn.Close();} <%@ Page Language="C#" AutoEventWireup="true" CodeFile="ExcleToSql.aspx.cs" Inherits="ExcleToSql" %><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head runat="server"> <title>excel导入sql</title></head><body> <form id="form1" runat="server"> <div> <asp:FileUpload ID="FileUpload1" runat="server" Height="21px" Width="304px" /> <asp:Button ID="Button1" runat="server" Text="导入" onclick="Button1_Click" /> </div> </form></body></html>using System;using System.Collections;using System.Configuration;using System.Data;using System.Data.SqlClient;using System.Linq;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.HtmlControls;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.Xml.Linq;public partial class ExcleToSql : System.Web.UI.Page{ string connectionstr = ConfigurationManager.ConnectionStrings["testConnectionString"].ConnectionString; SqlConnection conn; protected void Page_Load(object sender, EventArgs e) { } protected void Button1_Click(object sender, EventArgs e) { conn = new SqlConnection(connectionstr); conn.Open(); DataTable dt = getExcelDate(); for (int i = 0; i < dt.Rows.Count; i++) { string sqlstr = "insert into Student values ('" + dt.Rows[i]["StuNumber"].ToString() + "','" + dt.Rows[i]["StuName"].ToString() + "')"; SqlCommand cmd = new SqlCommand(sqlstr, conn); cmd.ExecuteNonQuery(); } conn.Close(); Response.Write("<mce:script type="text/javascript"><!--alert('导入成功!');// --></mce:script>"); } public DataTable getExcelDate() { string strExcelFileName =Server.MapPath("Temp")+"\\"+FileUpload1.FileName.ToString(); string sql ="select * from OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel 5.0;HDR=YES;DATABASE="+strExcelFileName+"',sheet1$)"; SqlDataAdapter da = new SqlDataAdapter(sql, conn); DataSet ds = new DataSet(); da.Fill(ds); return ds.Tables[0]; }}参考 #region 导入按钮事件 protected void BtnUpload_Click(object sender, EventArgs e) { string filename = string.Empty; try { filename = UploadXml(FileExcel); ImportXlsToData(filename);//将XLS文件的数据导入数据库 if (filename != string.Empty && System.IO.File.Exists(filename)) { System.IO.File.Delete(filename);//删除上传的XLS文件 } LblMessage.Text = "数据导入成功!"; System.IO.File.Delete(filename);//删除上传的XLS文件 } catch (Exception ex) { LblMessage.Text = ex.Message; } } #endregion #region 上传Excel文件 /// <summary> /// 上传Excel文件 /// </summary> /// <param name="inputfile">上传的控件名</param> protected string UploadXml(System.Web.UI.HtmlControls.HtmlInputFile inputfile) { string orifilename = string.Empty; string uploadfilepath = string.Empty; string modifyfilename = string.Empty; string fileExtend = "";//文件扩展名 int fileSize = 0;//文件大小 try { if (inputfile.Value != string.Empty) { //得到文件的大小 fileSize = inputfile.PostedFile.ContentLength; if (fileSize == 0) { throw new Exception("导入的Excel文件大小为0,请检查是否正确!"); } //得到扩展名 fileExtend = inputfile.Value.Substring(inputfile.Value.LastIndexOf(".") + 1); if (fileExtend.ToLower() != "xls") { throw new Exception("你选择的文件格式不正确,只能导入EXCEL文件!"); } //路径 uploadfilepath = Server.MapPath("~/Service/GraduateChannel/GraduateApply/ImgUpLoads"); //新文件名 modifyfilename = System.Guid.NewGuid().ToString(); modifyfilename += "." + inputfile.Value.Substring(inputfile.Value.LastIndexOf(".") + 1); //判断是否有该目录 System.IO.DirectoryInfo dir = new System.IO.DirectoryInfo(uploadfilepath); if (!dir.Exists) { dir.Create(); } orifilename = uploadfilepath + "\\" + modifyfilename; //如果存在,删除文件 if (File.Exists(orifilename)) { File.Delete(orifilename); } //上传文件 inputfile.PostedFile.SaveAs(orifilename); } else { throw new Exception("请选择要导入的Excel文件!"); } } catch (Exception ex) { throw ex; } return orifilename; } #endregion #region 插入数据 public void Add(DataSet ds) { col = this.RadioList.SelectedValue; int ii = ds.Tables[0].Columns.Count; string sql = "select top "+ii+" name from dbo.syscolumns where id = object_id(N'" + col + "')"; DataTable dt = dbc.GetDataTableBySql(sql); StringBuilder strSql = new StringBuilder(); for (int i = 0; i < ds.Tables[0].Rows.Count; i++) { strSql.Append("insert into " + col + "("); for (int j = 0; j < dt.Rows.Count; j++) { if (j == dt.Rows.Count - 1) { strSql.Append("" + dt.Rows[j][0].ToString() + ""); } else { strSql.Append("" + dt.Rows[j][0].ToString() + ","); } } strSql.Append(")"); strSql.Append("values"); strSql.Append("("); for (int a = 0; a < ii; a++) { if (a == ii - 1) { strSql.Append("'" + ds.Tables[0].Rows[i][a].ToString() + "'"); } else { strSql.Append("'" + ds.Tables[0].Rows[i][a].ToString() + "',"); } } strSql.Append(")"); } dbc.ExecuteSql(strSql.ToString()); } #endregion #region 将Dataset的数据导入数据库 /// <summary> /// 将Dataset的数据导入数据库 /// </summary> /// <param name="pds">数据集</param> /// <param name="Cols">数据集列数</param> /// <returns></returns> private bool AddDatasetToSQL(DataSet pds, int Cols) { int ic, ir; ic = pds.Tables[0].Columns.Count; if (pds.Tables[0].Columns.Count < Cols) { throw new Exception("导入Excel格式错误!Excel只有" + ic.ToString() + "列"); } ir = pds.Tables[0].Rows.Count; if (pds != null && pds.Tables[0].Rows.Count > 0) { Add(pds); } else { throw new Exception("导入数据为空!"); } return true; } #endregion #region 从excel中提取数据——dataset中 /// <summary> /// 从excel中提取数据——dataset中 /// </summary> /// <param name="fileName"></param> private void ImportXlsToData(string fileName) { try { col = this.RadioList.SelectedValue; if (fileName == string.Empty) { throw new ArgumentNullException("Excel文件上传失败!"); } string oleDBConnString = string.Empty; oleDBConnString = "Provider=Microsoft.Jet.OLEDB.4.0;"; oleDBConnString += "Data Source="; oleDBConnString += fileName; oleDBConnString += ";Extended Properties=Excel 8.0;"; OleDbConnection oleDBConn = null; OleDbDataAdapter oleAdMaster = null; DataTable m_tableName = new DataTable(); DataSet ds = new DataSet(); oleDBConn = new OleDbConnection(oleDBConnString); oleDBConn.Open(); m_tableName = oleDBConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); if (m_tableName != null && m_tableName.Rows.Count > 0) { m_tableName.TableName = m_tableName.Rows[0]["TABLE_NAME"].ToString(); } string sqlMaster; sqlMaster = " SELECT * FROM [" + m_tableName.TableName + "]"; oleAdMaster = new OleDbDataAdapter(sqlMaster, oleDBConn); oleAdMaster.Fill(ds, "m_tableName"); oleAdMaster.Dispose(); oleDBConn.Close(); oleDBConn.Dispose(); string sql = "select count(*) from syscolumns where id = object_id( '"+col+"' )"; int i = dbc.ExecuteSql(sql); AddDatasetToSQL(ds, i); } catch (Exception ex) { throw ex; } } #endregion #region 导出按钮事件,查询条件可以添加 protected void BtnDownload_Click(object sender, EventArgs e) { string sql = "select * from "+col+""; //if (this.keyword.Text.Trim() != "") //{ // this.keyword.Text = this.keyword.Text.Trim().Replace("'", ""); // sql += " where patindex('%" + this.keyword.Text + "%',username)>0"; //} //if (pubfun.IsBlank(dgInfo.Sort)) //{ // sql += " Order By addTime DESC"; //} //else //{ // sql += " Order By " + dgInfo.Sort + " " + DataGridSortType; //} DataTable dt = this.dbc.GetDataTableBySql(sql); //this.TotalRecords = dt.Rows.Count; this.DataGrid1.DataSource = dt; this.DataGrid1.DataBind(); dt.Dispose(); this.DataGrid1.Visible = true; if (this.DataGrid1.Items.Count == 0) { pubfun.Alert("对不起,没有查询到任何记录,不能导出数据!", -1); return; } else { Response.Clear(); Response.Buffer = true; Response.Charset = "GB2312"; Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode("Temp.xls")); Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");//设置输出流为简体中文 // Response.ContentEncoding = System.Text.Encoding.UTF8; Response.ContentType = "application/ms-excel";//设置输出文件类型为excel文件。 this.EnableViewState = false; System.Globalization.CultureInfo myCItrad = new System.Globalization.CultureInfo("ZH-CN", true); System.IO.StringWriter oStringWriter = new System.IO.StringWriter(myCItrad); System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter); this.DataGrid1.RenderControl(oHtmlTextWriter); Response.Write(oStringWriter.ToString()); Response.End(); } } #endregion 1、把客户端的excel上传至web服务器上某文件夹,注意要给该文件夹设置网络用户的“写入”权限; 2、使用OleDb+sql语句读取web服务器上的excel文件 3、将读取的数据写入到sql server 数据库 4、将web服务器上的临时excel删除 部分代码如下: string filename = FileUpload1.PostedFile.FileName.Substring(FileUpload1.PostedFile.FileName.LastIndexOf("\\")); FileUpload1.PostedFile.SaveAs(Server.MapPath("fileupload\\") + filename);//上传文件 string conn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("fileupload\\") + filename + ";Extended Properties=Excel 8.0"; string sqlin = "SELECT * FROM [Sheet2$]"; OleDbCommand olecommand = new OleDbCommand(sqlin, new OleDbConnection(conn)); OleDbDataAdapter adapterin = new OleDbDataAdapter(olecommand); DataSet dsin = new DataSet(); adapterin.Fill(dsin); DataTable dtin = dsin.Tables[0];//连接并读取excel数据 for (i = 3; i <= totalrow; i++)//将Excel文件中第一个工作表的数据导入到sql数据库scjd_youliaoxiaohaojihua表中 { string sql = "insert into scjd_youliaoxiaohaojihua(yuexuhao,danwei,youpin,cheliangmingchengxinghao,zichanxingzhi,chepaihao,qiyou,chaiyou,beizhu) values('"; int j; // //此处开始拼接sql语句,将所有的值写出来。 // sql += "')"; try { DoSql(sql); } catch (Exception eeeeeee) { Response.Write("<script>alert('数据导入错误,请检查Excel文件')</script>"); return; } } FileInfo file = new FileInfo(Server.MapPath("fileupload/") + filename); if (file.Exists) {//删除文件 file.Delete(); } protected void DoSql(string sql)//执行sql语句的函数 { SqlConnection conn = new SqlConnection();//创建连接对象 conn.ConnectionString = ConfigurationManager.AppSettings["conn"].ToString();//给连接字符串赋值 conn.Open();//打开数据库 SqlCommand cmd = new SqlCommand(sql, conn); cmd.ExecuteNonQuery();// conn.Close();//关闭数据库 } C#.net网站的繁简切换应该怎么做,救命啊~~~ 使用VS2010出现的几个问题? 求解,高手请进。。。。 SqlDataAdapter执行数据批量插入后数据库中物理顺序与原输入顺序不一致 asp.net生成带有坐标系的统计图 如何去掉IFrame右侧的滚动条位置处出现空白区域????急,解决了加分 在ASP.NET中,怎样将EXCEL另存为HTML文件? jquery 获取焦点失去焦点问题 诚邀各位 .NET爱好者, 网页里显示*.doc 怎么让word的工具栏也显示出来 急救!!如何在.NET项目中把用户注册时的用户名及密码保存到系统的域中 急啊!.net关于弹出alert的问题
提供思路,代码要自己研究。
一、把DataTable插入数据库
public static void DataTableToDB()
{
string _strExcelFileName = @"D:\example.xls";
DataTable dtExcel = ExcelToDataTable(_strExcelFileName,"Sheet1");
for (int i = 0; i < dtExcel.Rows.Count; i++)
{
InsertDataToAccess(dtExcel.Rows[i][0].ToString(), float.Parse(dtExcel.Rows[i][1].ToString()));
}
}二、把Excel数据读入DataTable
public static DataTable ExcelToDataTable(string strExcelFileName, string strSheetName)
{
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + strExcelFileName + ";" +"Extended Properties=Excel 5.0;";
string strExcel = string.Format("select * from [{0}$]", strSheetName);
DataSet ds = new DataSet(); using (OleDbConnection conn = new OleDbConnection(strConn))
{
conn.Open();
OleDbDataAdapter adapter = new OleDbDataAdapter(strExcel, strConn);
adapter.Fill(ds, strSheetName);
conn.Close();
} return ds.Tables[strSheetName];
}三、向Access数据库表插入数据
public static void InsertDataToAccess(string _strPara,float _fPara)
{
OleDbConnection oleDbConn = new OleDbConnection();
oleDbConn.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\ExcelData.mdb;User Id=admin;Password=;";
oleDbConn.Open(); string strInsertString = "INSERT INTO tb_excelData (strCollumn1,fCollumn2) VALUES (@strCollumn1,@fCollumn2)";
OleDbCommand oComm = new OleDbCommand(strInsertString, oleDbConn);
oComm.Parameters.Add("@strCollumn1", OleDbType.Char , 50);
oComm.Parameters["@strCollumn1"].Value = _strPara;
oComm.Parameters.Add("@fCollumn2", OleDbType.Double);
oComm.Parameters["@fCollumn2"].Value = _fPara;
ocomm.ExecuteNonQuery();
oleDbConn.Close();
}
<head runat="server">
<title>excel导入sql</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:FileUpload ID="FileUpload1" runat="server" Height="21px" Width="304px" />
<asp:Button ID="Button1" runat="server" Text="导入" onclick="Button1_Click" />
</div>
</form>
</body>
</html>using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
public partial class ExcleToSql : System.Web.UI.Page
{
string connectionstr = ConfigurationManager.ConnectionStrings["testConnectionString"].ConnectionString;
SqlConnection conn;
protected void Page_Load(object sender, EventArgs e)
{ }
protected void Button1_Click(object sender, EventArgs e)
{
conn = new SqlConnection(connectionstr);
conn.Open();
DataTable dt = getExcelDate();
for (int i = 0; i < dt.Rows.Count; i++)
{
string sqlstr = "insert into Student values ('" + dt.Rows[i]["StuNumber"].ToString() + "','" + dt.Rows[i]["StuName"].ToString() + "')";
SqlCommand cmd = new SqlCommand(sqlstr, conn);
cmd.ExecuteNonQuery();
}
conn.Close();
Response.Write("<mce:script type="text/javascript"><!--
alert('导入成功!');
// --></mce:script>");
}
public DataTable getExcelDate()
{
string strExcelFileName =Server.MapPath("Temp")+"\\"+FileUpload1.FileName.ToString();
string sql ="select * from OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel 5.0;HDR=YES;DATABASE="+strExcelFileName+"',sheet1$)";
SqlDataAdapter da = new SqlDataAdapter(sql, conn);
DataSet ds = new DataSet();
da.Fill(ds);
return ds.Tables[0];
}
}参考
protected void BtnUpload_Click(object sender, EventArgs e)
{
string filename = string.Empty;
try
{
filename = UploadXml(FileExcel);
ImportXlsToData(filename);//将XLS文件的数据导入数据库
if (filename != string.Empty && System.IO.File.Exists(filename))
{
System.IO.File.Delete(filename);//删除上传的XLS文件
}
LblMessage.Text = "数据导入成功!";
System.IO.File.Delete(filename);//删除上传的XLS文件
}
catch (Exception ex)
{
LblMessage.Text = ex.Message;
}
}
#endregion #region 上传Excel文件
/// <summary>
/// 上传Excel文件
/// </summary>
/// <param name="inputfile">上传的控件名</param>
protected string UploadXml(System.Web.UI.HtmlControls.HtmlInputFile inputfile)
{
string orifilename = string.Empty;
string uploadfilepath = string.Empty;
string modifyfilename = string.Empty;
string fileExtend = "";//文件扩展名
int fileSize = 0;//文件大小
try
{
if (inputfile.Value != string.Empty)
{
//得到文件的大小
fileSize = inputfile.PostedFile.ContentLength;
if (fileSize == 0)
{
throw new Exception("导入的Excel文件大小为0,请检查是否正确!");
}
//得到扩展名
fileExtend = inputfile.Value.Substring(inputfile.Value.LastIndexOf(".") + 1);
if (fileExtend.ToLower() != "xls")
{
throw new Exception("你选择的文件格式不正确,只能导入EXCEL文件!");
}
//路径
uploadfilepath = Server.MapPath("~/Service/GraduateChannel/GraduateApply/ImgUpLoads");
//新文件名
modifyfilename = System.Guid.NewGuid().ToString();
modifyfilename += "." + inputfile.Value.Substring(inputfile.Value.LastIndexOf(".") + 1);
//判断是否有该目录
System.IO.DirectoryInfo dir = new System.IO.DirectoryInfo(uploadfilepath);
if (!dir.Exists)
{
dir.Create();
}
orifilename = uploadfilepath + "\\" + modifyfilename;
//如果存在,删除文件
if (File.Exists(orifilename))
{
File.Delete(orifilename);
}
//上传文件
inputfile.PostedFile.SaveAs(orifilename);
}
else
{
throw new Exception("请选择要导入的Excel文件!");
}
}
catch (Exception ex)
{
throw ex;
}
return orifilename;
}
#endregion #region 插入数据
public void Add(DataSet ds)
{
col = this.RadioList.SelectedValue;
int ii = ds.Tables[0].Columns.Count;
string sql = "select top "+ii+" name from dbo.syscolumns where id = object_id(N'" + col + "')";
DataTable dt = dbc.GetDataTableBySql(sql);
StringBuilder strSql = new StringBuilder();
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
strSql.Append("insert into " + col + "(");
for (int j = 0; j < dt.Rows.Count; j++)
{
if (j == dt.Rows.Count - 1)
{
strSql.Append("" + dt.Rows[j][0].ToString() + "");
}
else
{
strSql.Append("" + dt.Rows[j][0].ToString() + ",");
}
}
strSql.Append(")");
strSql.Append("values");
strSql.Append("(");
for (int a = 0; a < ii; a++)
{
if (a == ii - 1)
{
strSql.Append("'" + ds.Tables[0].Rows[i][a].ToString() + "'");
}
else
{
strSql.Append("'" + ds.Tables[0].Rows[i][a].ToString() + "',");
}
}
strSql.Append(")");
}
dbc.ExecuteSql(strSql.ToString());
}
#endregion #region 将Dataset的数据导入数据库
/// <summary>
/// 将Dataset的数据导入数据库
/// </summary>
/// <param name="pds">数据集</param>
/// <param name="Cols">数据集列数</param>
/// <returns></returns>
private bool AddDatasetToSQL(DataSet pds, int Cols)
{
int ic, ir;
ic = pds.Tables[0].Columns.Count;
if (pds.Tables[0].Columns.Count < Cols)
{
throw new Exception("导入Excel格式错误!Excel只有" + ic.ToString() + "列");
}
ir = pds.Tables[0].Rows.Count;
if (pds != null && pds.Tables[0].Rows.Count > 0)
{
Add(pds);
}
else
{
throw new Exception("导入数据为空!");
}
return true;
}
#endregion #region 从excel中提取数据——dataset中
/// <summary>
/// 从excel中提取数据——dataset中
/// </summary>
/// <param name="fileName"></param>
private void ImportXlsToData(string fileName)
{
try
{
col = this.RadioList.SelectedValue;
if (fileName == string.Empty)
{
throw new ArgumentNullException("Excel文件上传失败!");
}
string oleDBConnString = string.Empty;
oleDBConnString = "Provider=Microsoft.Jet.OLEDB.4.0;";
oleDBConnString += "Data Source=";
oleDBConnString += fileName;
oleDBConnString += ";Extended Properties=Excel 8.0;";
OleDbConnection oleDBConn = null;
OleDbDataAdapter oleAdMaster = null;
DataTable m_tableName = new DataTable();
DataSet ds = new DataSet();
oleDBConn = new OleDbConnection(oleDBConnString);
oleDBConn.Open();
m_tableName = oleDBConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); if (m_tableName != null && m_tableName.Rows.Count > 0)
{ m_tableName.TableName = m_tableName.Rows[0]["TABLE_NAME"].ToString(); }
string sqlMaster;
sqlMaster = " SELECT * FROM [" + m_tableName.TableName + "]";
oleAdMaster = new OleDbDataAdapter(sqlMaster, oleDBConn);
oleAdMaster.Fill(ds, "m_tableName");
oleAdMaster.Dispose();
oleDBConn.Close();
oleDBConn.Dispose();
string sql = "select count(*) from syscolumns where id = object_id( '"+col+"' )";
int i = dbc.ExecuteSql(sql);
AddDatasetToSQL(ds, i); }
catch (Exception ex)
{
throw ex;
}
}
#endregion
protected void BtnDownload_Click(object sender, EventArgs e)
{
string sql = "select * from "+col+"";
//if (this.keyword.Text.Trim() != "")
//{
// this.keyword.Text = this.keyword.Text.Trim().Replace("'", "");
// sql += " where patindex('%" + this.keyword.Text + "%',username)>0";
//}
//if (pubfun.IsBlank(dgInfo.Sort))
//{
// sql += " Order By addTime DESC";
//}
//else
//{
// sql += " Order By " + dgInfo.Sort + " " + DataGridSortType;
//}
DataTable dt = this.dbc.GetDataTableBySql(sql);
//this.TotalRecords = dt.Rows.Count;
this.DataGrid1.DataSource = dt;
this.DataGrid1.DataBind();
dt.Dispose(); this.DataGrid1.Visible = true;
if (this.DataGrid1.Items.Count == 0)
{
pubfun.Alert("对不起,没有查询到任何记录,不能导出数据!", -1);
return;
}
else
{
Response.Clear();
Response.Buffer = true;
Response.Charset = "GB2312";
Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode("Temp.xls"));
Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");//设置输出流为简体中文
// Response.ContentEncoding = System.Text.Encoding.UTF8;
Response.ContentType = "application/ms-excel";//设置输出文件类型为excel文件。
this.EnableViewState = false;
System.Globalization.CultureInfo myCItrad = new System.Globalization.CultureInfo("ZH-CN", true);
System.IO.StringWriter oStringWriter = new System.IO.StringWriter(myCItrad);
System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
this.DataGrid1.RenderControl(oHtmlTextWriter);
Response.Write(oStringWriter.ToString());
Response.End();
}
}
#endregion
2、使用OleDb+sql语句读取web服务器上的excel文件
3、将读取的数据写入到sql server 数据库
4、将web服务器上的临时excel删除
部分代码如下:
string filename = FileUpload1.PostedFile.FileName.Substring(FileUpload1.PostedFile.FileName.LastIndexOf("\\"));
FileUpload1.PostedFile.SaveAs(Server.MapPath("fileupload\\") + filename);//上传文件
string conn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("fileupload\\") + filename + ";Extended Properties=Excel 8.0";
string sqlin = "SELECT * FROM [Sheet2$]";
OleDbCommand olecommand = new OleDbCommand(sqlin, new OleDbConnection(conn));
OleDbDataAdapter adapterin = new OleDbDataAdapter(olecommand);
DataSet dsin = new DataSet();
adapterin.Fill(dsin);
DataTable dtin = dsin.Tables[0];//连接并读取excel数据
for (i = 3; i <= totalrow; i++)//将Excel文件中第一个工作表的数据导入到sql数据库scjd_youliaoxiaohaojihua表中
{
string sql = "insert into scjd_youliaoxiaohaojihua(yuexuhao,danwei,youpin,cheliangmingchengxinghao,zichanxingzhi,chepaihao,qiyou,chaiyou,beizhu) values('";
int j;
//
//此处开始拼接sql语句,将所有的值写出来。
//
sql += "')";
try
{
DoSql(sql);
}
catch (Exception eeeeeee)
{
Response.Write("<script>alert('数据导入错误,请检查Excel文件')</script>");
return;
}
}
FileInfo file = new FileInfo(Server.MapPath("fileupload/") + filename);
if (file.Exists)
{//删除文件
file.Delete();
}
protected void DoSql(string sql)//执行sql语句的函数
{
SqlConnection conn = new SqlConnection();//创建连接对象
conn.ConnectionString = ConfigurationManager.AppSettings["conn"].ToString();//给连接字符串赋值
conn.Open();//打开数据库
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.ExecuteNonQuery();//
conn.Close();//关闭数据库
}