实现思路：Excel的内容并不可以直接上传到数据库，所以先将Excel内容读取出来转化为DataSet，然后可以得到DataTable，遍历每一行，存到数据库里就好了。
前台只放了两个控件：
<asp:Button ID="btnUpload" class="btnStyle" runat="server" Text="上传" OnClick="btnUpload_Click" />
<asp:FileUpload ID="FileUpload1" runat="server" />
后台如下：
namespace *********不显示了无关紧要************
{
public partial class UploadExcelFile : System.Web.UI.Page
{
UploadExcelFileBiz uploadExcelFileBiz = new UploadExcelFileBiz();
protected void Page_Load(object sender, EventArgs e)
{
}
/// <summary>
/// 上传至数据库
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnUpload_Click(object sender, EventArgs e)
{
//bool style = false;
string road = "";
#region 文件上传
try
{
//全名
string excelFile = this.FileUpload1.PostedFile.FileName;
//获取文件名（不包括扩展名）
string fileName = Path.GetFileNameWithoutExtension(FileUpload1.PostedFile.FileName);
//扩展名
string extentionName = excelFile.Substring(excelFile.LastIndexOf(".") + 1);
if (fileName == "" || fileName == null)
{
Response.Write("<script>alert('请先选择Excel文件！')</script>");
return;
}
if (extentionName != "xls" && extentionName != "xlsx")
{
Response.Write("<script>alert('您上传的不是Excel文件！')</script>");
return;
}
//浏览器安全性限制 无法直接获取客户端文件的真实路径，将文件上传到服务器端 然后获取文件源路径
#region 设置上传路径将文件保存到服务器
string dateTime = DateTime.Now.Date.ToString("yyyyMMdd");
string time = DateTime.Now.ToShortTimeString().Replace(":", "");
string newFileName = dateTime + time + DateTime.Now.Millisecond.ToString() + ".xls"; ;
//Annex是自己创建的文件夹 位置随意 合理即可
road = Server.MapPath("..\\..\\Annex") + "\\" + newFileName;
this.FileUpload1.PostedFile.SaveAs(road);
//Response.Write("<script>alert('已经上传到服务器文件夹')</script>");
#endregion
}
catch
{
Response.Write("<script>alert('数据上传失败，请重新导入')</script>");
return;
}
#endregion
#region 数据导入
try
{
#region 读取成DataSet 然后转化为Table
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + road + "';" +
"Extended Properties='Excel 8.0;IMEX=1'";
DataSet dsMin = new DataSet();
OleDbDataAdapter oada = new OleDbDataAdapter("select * from [模板(5分钟)$]", strConn);
oada.Fill(dsMin);
DataSet dsHour = new DataSet();
OleDbDataAdapter oada2 = new OleDbDataAdapter("select * from [模板(小时)$]", strConn);
oada2.Fill(dsHour);
DataTable dtMin = dsMin.Tables[0];
DataRow[] dr = dtMin.Select();
int rowsNum = dtMin.Rows.Count;
string a, b, c, d, f, g, h;
//遍历获取的Excel内容 dr[行][列] 下标索引从0开始
for (int i = 2; i < rowsNum; i++)
{
//序号
a = dr[i][0].ToString();
//时间
b = dr[i][1].ToString();
//本人上传的Excel第三列有空格，所以轮空
c = dr[i][3].ToString();
d = dr[i][4].ToString();
f = dr[i][5].ToString();
g = dr[i][6].ToString();
h = dr[i][7].ToString();
//插入数据库 Add是自定义的方法
uploadExcelFileBiz.Add(Convert.ToDateTime(b), c, d, f, g, h);
}
#region SqlBulkCopy批量写入数据库
DataTable dtHour = dsHour.Tables[0];
dtHour .Columns[0].ColumnName = "UploadTime";
dtHour.Columns[1].ColumnName = "Data1";
dtHour.Columns[2].ColumnName = "Data2";
dtHour.Columns[3].ColumnName = "Data3";
dtHour.Columns[4].ColumnName = "Data4";
dtHour.Columns[5].ColumnName = "Data5";
uploadExcelFileBiz.WriteIntoDataBase(dtHour);
#endregion
#endregion
}
catch
{
Response.Write("<script>alert('导入数据失败！')</script>");
return;
}
#endregion
}
}
}
附上SqlBulkCopy批量写入数据库的方法如下：
DAL层：
/// <summary>
/// 利用SqlBulkCopy批量写入数据库
/// </summary>
/// <param name="dt"></param>
public void WriteIntoDataBase(DataTable dt)
{
SqlConnection myConn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["dbBaseDatabase"].ConnectionString);
myConn.Open();
SqlBulkCopy bulkCopy = new SqlBulkCopy(myConn);
bulkCopy.BatchSize = 10000;
bulkCopy.BulkCopyTimeout = 60;
bulkCopy.DestinationTableName = "T_Sys_UploadTest";
for (int i = 0; i < dt.Columns.Count; i++)
{
string columnName = dt.Columns[i].ColumnName;
bulkCopy.ColumnMappings.Add(columnName, columnName);
}
bulkCopy.WriteToServer(dt);
//myConn.Close();
myConn.Dispose();
}
注意事项：
dataTable的表头得和所要插入的数据库表中的字段名称一一对应，注意重命名：如：dataTable.Columns[0].ColumnName = "内容";
备注：
SqlBulkCopy这种批量写入方式，可以大大的节约数据库导入数据的时间，因为字符串的拼接的方式写入数据库需要不停的打开和关闭连接。
上述内容则提供了这两种方法，方便对比
-----------------------分割线----2017.06.26更新-------附上UploadExcelFileBiz.cs--------------------
using ApplyForNetwork.DAL.ApplyForNetwork.Sys;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
namespace ApplyForNetwork.BLL.ApplyForNetwork.Sys
{
public class UploadExcelFileBiz
{
UploadExcelFileDA uploadExcelFileDA = new UploadExcelFileDA();
public UploadExcelFileBiz()
{
}
/// <summary>
/// 利用SqlBulkCopy批量写入数据库
/// </summary>
/// <param name="dt"></param>
public void WriteIntoDataBase(DataTable dt,string tableName)
{
uploadExcelFileDA.WriteIntoDataBase(dt, tableName);
}
/// <summary>
/// 创建临时表
/// </summary>
/// <returns></returns>
public DataTable tmpTable()
{
DataTable dt = new DataTable();
dt.Columns.Add("ID", typeof(int));
dt.Columns.Add("PortId",typeof(string));
dt.Columns.Add("Tstamp", typeof(DateTime));
dt.Columns.Add("ZAD_DATA1", typeof(Decimal));
dt.Columns.Add("MinusData1", typeof(Decimal));
dt.Columns.Add("ZAD_DATA3", typeof(Decimal));
dt.Columns.Add("MinusData3", typeof(Decimal));
dt.Columns.Add("ZAD_DATA4", typeof(Decimal));
dt.Columns.Add("MinusData4", typeof(Decimal));
dt.Columns.Add("ZAD_DATA6", typeof(Decimal));
dt.Columns.Add("MinusData6", typeof(Decimal));
dt.Columns.Add("ZAD_DATA9", typeof(Decimal));
dt.Columns.Add("MinusData9", typeof(Decimal));
dt.Columns.Add("CheckBatch", typeof(string));
return dt;
}
}
}
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)
