上传Excel并将指定数据导入到数据库_传数据上去然后指定的数据导出来，软件-优快云博客

本文介绍了一个使用C#实现的Excel文件导入数据库的具体案例。通过上传Excel文件，利用ADO.NET技术读取Excel数据，并将其导入到SQL Server数据库中，同时处理了可能遇到的错误情况。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

excel.aspx

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

<!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>
        <title>Excel</title>
        <meta content="Microsoft Visual Studio .NET 7.1" name="GENERATOR">
        <meta content="C#" name="CODE_LANGUAGE">
        <meta content="JavaScript" name="vs_defaultClientScript">
        <meta content="http://schemas.microsoft.com/intellisense/ie5" name="vs_targetSchema">
    </HEAD>
    <body >
        <form id="Form1" method="post" runat="server">
            <TABLE id="Table2" style="Z-INDEX: 101; LEFT: 0px; POSITION: absolute; TOP: 0px" height="100%"
                cellSpacing="1" cellPadding="1" width="100%" border="0">
                <TR>
                    <TD style="FONT-SIZE: 12px; COLOR: appworkspace" align="center" colSpan="3"><FONT face="宋体"> </FONT>
                        <TABLE id="Table4" style="FONT-SIZE: 12px" cellSpacing="1" cellPadding="1" width="300"
                            border="0">
                            <TR>
                                <TD style="FONT-WEIGHT: bolder; FONT-SIZE: 12pt" align="center" colSpan="2">
                                    EXCEL导入数据库</TD>
                            </TR>
                            <TR>
                                <TD align="right">位置：</TD>
                                <TD><INPUT id="file1" style="FONT-SIZE: 12px; HEIGHT: 22px" type="file" runat="server"></TD>
                            </TR>
                            <TR>
                                <TD style="height: 26px"></TD>
                                <TD align="center" style="height: 26px"><asp:button id="Button2" runat="server" Text="上　　传" BorderStyle="None" OnClick="Button2_Click"></asp:button></TD>
                            </TR>
                            <TR>
                                <TD style="COLOR: gray; height: 18px;" align="right" colSpan="2">
                                    >>Excel表名默认为Sheet1</TD>
                            </TR>
                            <tr>
                                <td align="right" colspan="2" style="color: gray; height: 18px">
                                    <asp:Label ID="lblMessage" runat="server" Text="Label"></asp:Label></td>
                            </tr>
                        </TABLE>
                    </TD>
                </TR>
            </TABLE>
        </form>
    </body>
</HTML>

excel.aspx.cs

using System;

using System.Collections;

using System.ComponentModel;

using System.Data;

using System.Data.OleDb;

using System.Data.SqlClient;

using System.Drawing;

using System.Web;

using System.Web.SessionState;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.HtmlControls;

using System.Web.Security;

using System.Configuration;

public partial class _Excel : System.Web.UI.Page

{

/**/

/// <summary>

/// Excel 的摘要说明。

/// </summary>

public class Excel : System.Web.UI.Page

{

}

protected void Button2_Click(object sender, EventArgs e)

{

if (!file1.Value.ToLower().EndsWith("xls"))

{

lblMessage.Text = "只能导入Excel文件";

return;

}

//获取上传文件的路径

string myFile = file1.PostedFile.FileName;

string fileName = myFile.Substring(myFile.LastIndexOf("/") + 1);

string Path = Server.MapPath("xls") + "/" + fileName;

file1.PostedFile.SaveAs(Path);

string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Path + ";" + "Extended Properties=Excel 8.0;";

string strExcel = "select [商号],[日期],[数量] from [sheet1$]";

DataSet myDataSet = new DataSet();

OleDbConnection conn = new OleDbConnection(strConn);

OleDbDataAdapter myCommand = new OleDbDataAdapter(strExcel, strConn);

try

{

myCommand.Fill(myDataSet);

}

catch (Exception ex)

{

string aa = ex.Message;

lblMessage.Text = "表单名不正确,只能识别Sheet1的表单";

return;

}

//复制

string connsr = ConfigurationManager.ConnectionStrings["statConnectionString"].ConnectionString.ToString();

SqlConnection con = new SqlConnection(connsr);

SqlDataAdapter da = new SqlDataAdapter("select top 1 businessid,gaintime,number from incomeinfo", con);

DataSet ds1 = new DataSet();

da.Fill(ds1);//原有

try

{

for (int i = 0; i < myDataSet.Tables[0].Rows.Count; i++)

{

DataRow dr = ds1.Tables[0].NewRow();

for (int j = 0; j < ds1.Tables[0].Columns.Count; j++)

{

dr[j] = myDataSet.Tables[0].Rows[i][j];

}

ds1.Tables[0].Rows.Add(dr);

}

try

{

SqlCommandBuilder cb = new SqlCommandBuilder(da);

cb.QuotePrefix = "[";

cb.QuoteSuffix = "]";

da.Update(ds1);

lblMessage.Text = "添加成功!";

}

catch (Exception ex2)

{

lblMessage.Text = ex2.Message.ToString();

}

catch (Exception ex1)

{

lblMessage.Text = ex1.Message.ToString() + "请清除多余数据再试";

}

数据库

create table incomeinfo --每日数据

(

infoid int primary key identity(1,1), --信息ID

businessid varchar(20) not null foreign key references income(businessid), --商号

gaintime smalldatetime not null, --日期

number int default(0) --数量

)

Excel

商号日期数量

skype12 2007-11-19 15