ASP.NET 从Excel文件导入数据到数据库(笔记)

页面ＨＴＭＬ代码：

代码

<% @ Page Language = " C# " AutoEventWireup = " true " CodeFile = " Default.aspx.cs " Inherits = " _Default " %>

<! 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 数据导入 数据库 </ title >

< script type ="text/javascript" src ="js/jquery-1.4.2.min.js" ></ script >

< script type ="text/javascript" >
$( function (){
$( " # " + " <%=btnImport.ClientID %> " ).click( function (){
var fileName = $( " # " + " <%=excelFile.ClientID %> " ).val();
if (fileName == "" ){
alert( " 请选择Excel文件！ " );
return false ;
}
else {
var extension = fileName.substring(fileName.lastIndexOf( ' . ' ) + 1 );
if (extension != " xlsx " && extension != " xls " ){
alert( " 上传的文件不是Excel文件，请重试！ " );
return false ;
}
}
return true ;
});
});
</ script >

</ head >
< body >
< form id ="form1" runat ="server" >
< div >
< fieldset >
< legend > 数据导入： </ legend >
< table >
< tr >
< td style ="width: 182px" >
数据Excel： </ td >
< td >
< asp:FileUpload ID ="excelFile" runat ="server" /></ td >
</ tr >
< tr >
< td colspan ="2" >
< asp:Button ID ="btnImport" runat ="server" Text ="导入" OnClick ="btnImport_Click" />
< input id ="btnCancel" type ="button" value ="取消" onclick ='window.location.href="Default.aspx"' /></ td >
</ tr >
</ table >
</ fieldset >
</ div >
< div id ="errorDiv" runat ="server" >
< fieldset >
< legend > 错误信息: </ legend >
< textarea id ="errorArea" runat ="server" style ="width: 722px; height: 88px" ></ textarea >
</ fieldset >
</ div >
< div id ="confrimDiv" runat ="server" >
< fieldset >
< legend > 导入确认: </ legend >
< asp:GridView ID ="GVConfirm" runat ="server" CssClass ="grid" >
</ asp:GridView >
< div id ="buttonDiv" runat ="server" visible ="false" >
< asp:Button ID ="btnConfirm" runat ="server" Text ="确定" OnClick ="btnConfirm_Click" />
< input id ="btnNotConfirm" type ="button" value ="取消" onclick ='window.location.href="Default.aspx"' />
</ div >
</ fieldset >
</ div >
</ form >
</ body >
</ html >

Ｃ# 代码：

代码

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.Text;
using System.Data.OleDb;
using System.IO;

public partial class _Default : System.Web.UI.Page
{
protected void Page_Load( object sender, EventArgs e)
{
this .initPage();
}
/// <summary>
/// 通过ViewState保存一个DataTable.用来在现实之后添加到数据库
/// </summary>
public DataTable SamplDataTable
{
get
{
if (ViewState[ " SamplDataTable " ] == null )
return new DataTable();
return (DataTable)ViewState[ " SamplDataTable " ];
}
set
{
ViewState[ " SamplDataTable " ] = value;
}
}
private void initPage()
{
this .errorDiv.Visible = false ;
this .confrimDiv.Visible = false ;
}
protected void btnImport_Click( object sender, EventArgs e)
{
/// 取得文件名
string fileName = this .excelFile.FileName;
// 绝对路径
string path = " ~/Excel/ " + fileName;
path = Server.MapPath(path);
try
{
// 暂时保存在服务上
this .excelFile.SaveAs(path);
/// 读取指定路径 Excel 文件中的内容转换成DataTable
DataTable excelDT = ImportToDataSet(path);

// 声明错误信息字符串
StringBuilder errorBuiler = new StringBuilder();
/// 检查 存储在内存中的 Excel DataTable
if (Validate(excelDT, errorBuiler))
{
// 如果检查通过,使用ViewState保存DataSet中数据,在保存到数据库的时候用到
SamplDataTable = excelDT;
// 页面GridView数据绑定,用来显示从Excel读取出来的数据供用户确认
this .GVConfirm.DataSource = SamplDataTable;
this .GVConfirm.DataBind();
this .confrimDiv.Visible = true ;
this .buttonDiv.Visible = true ;
}
else
{ // 如果检测没有通过,输出相关错误信息
this .errorDiv.Visible = true ;
this .errorArea.Value = errorBuiler.ToString();
}
}
catch (Exception ex)
{
this .errorDiv.Visible = true ;
this .errorArea.Value = ex.Message;
}
finally
{
// 关闭,删除 文件
if (File.Exists(path))
File.Delete(path);
}
}
/// <summary>
/// 验证指定的Excel规则(列数)
/// </summary>
/// <param name="excelDT"></param>
/// <param name="errorBuiler"></param>
/// <returns></returns>
public bool Validate(DataTable excelDT, StringBuilder errorBuiler)
{
bool result = true ;
if (excelDT.Columns.Count != 5 ) // 假设是5列
{
result = false ;
int difference = excelDT.Columns.Count - 5 ;
if (difference > 0 )
errorBuiler.AppendLine( " 要导入的Excel多 " + difference.ToString() + " 列 " );
else
errorBuiler.AppendLine( " 要导入的Excel少 " + ( - difference).ToString() + " 列 " );
}
else
{
// foreach (DataRow row in excelDT.Rows)
// {
// if (Exists(row[0].ToString()))
// {
// result = false;
// errorBuiler.AppendLine("内容 " + "'" + row[2].ToString() + "'" + "已存在！");
// }
// }
}
return result;
}

protected void btnConfirm_Click( object sender, EventArgs e)
{
/// 将GridView中显示的数据(其实是保存在VIewState中)写入到数据库中
if (AddDataTable(SamplDataTable, 0 ))
{
Page.ClientScript.RegisterStartupScript(Page.GetType(), " insertSuccess " , " <script>alert('导入成功！');window.location.href='Default.aspx';</script> " );
}
else
{
Page.ClientScript.RegisterStartupScript(Page.GetType(), " insertFailure " , " <script>alert('导入失败！请重试');</script> " );
}
}
private bool AddDataTable(DataTable dt, int p)
{
bool result = true ;
string sql = BuilderInsertSql(dt, p);
try
{
// 执行sql语句 DbHelper.ExecuteSql();
return true ;
}
catch
{
result = false ;
}
return result; ;
}

private string BuilderInsertSql(DataTable dt, int p)
{
// 遍历DataTable拼接添加字符串
return "" ;
}

private bool AddDataTable(DataTable SamplDataTable, object p, int p_3)
{
throw new Exception( " The method or operation is not implemented. " );
}
/// <summary>
/// 读取指定路径的Excel内容到DataTable中
/// </summary>
/// <param name="path"></param>
/// <returns></returns>
public DataTable ImportToDataSet( string path)
{
string strConn = " Provider=Microsoft.Ace.OleDb.12.0; " + " Data Source= " + path + " ; " + " Extended Properties='Excel 12.0;HDR=Yes;IMEX=1'; " ;
OleDbConnection conn = new OleDbConnection(strConn);
try
{
DataTable dt = new DataTable();
if (conn.State != ConnectionState.Open)
conn.Open();
string strExcel = " select * from [Sheet1$] " ;
OleDbDataAdapter adapter = new OleDbDataAdapter(strExcel, conn);
adapter.Fill(dt);
return dt;
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
if (conn.State != ConnectionState.Closed)
conn.Close();
}
}
}

 

转载于:https://www.cnblogs.com/xiexiaoliang/archive/2011/01/10/1932400.html