ASP.NET C# Excell导入导出

来源：互联网发布：淘宝销售客服话术大全编辑：程序博客网时间：2024/06/05 06:43

最近在写WebForm下的Excel的导入导出，在网上查了大量的资料，没有找到自己满意的，不过通过查找资料倒是学了不少东西；网上很多关于这方面的文章都是只说一部分，像技术小白看了就一知半解的，现在这篇文章呢，是我参考了网上的一些资料，再加上自己的倾心研究，写了个简单的但完整实现的Excel导入导出；

下面就直接上代码：

这是新建的一个vs2012空项目，文件列表如图，接下来分别贴出代码：iExcelToInportAndExport.aspx前段代码：

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="iExcelToInportAndExport.aspx.cs" Inherits="WebExcelExportInport.iExcelToInportAndExport" %>

<!DOCTYPE html>

</form>
</body>
</html>

//===========================================

iExcelToInportAndExport.aspx.cs的代码

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Microsoft.Office.Interop.Excel;
using System.IO;
using System.Text;
using System.Diagnostics;
using System.Reflection;
using System.Data.OleDb;

namespace WebExcelExportInport
{
    public partial class iExcelToInportAndExport : System.Web.UI.Page
    {
        string strConn = SiteSetting.ConnectionString;
        string sqlSelectALL = SiteSetting.sqlSelALL;
        string exToDB = SiteSetting.ExToDB;
        string fileName = SiteSetting.FileName;
        string outPutPath = SiteSetting.OutPutPath;
        protected void Page_Load(object sender, EventArgs e)
        {
            Bind();
        }
        //绑定数据,显示到web页面上
        public void Bind()
        {
            DataSet ds = new DataSet();
            using (SqlConnection conn = new SqlConnection())
            {
                SqlDataAdapter sda = new SqlDataAdapter(sqlSelectALL, strConn);
                sda.Fill(ds, "userinfo");
            }
            GridView1.DataSource = ds.Tables["userinfo"];
            GridView1.DataBind();
        }
        //从数据库取出要导出的Detset数据集
        private DataSet getds()
        {
            SqlConnection conns = new SqlConnection(strConn);
            SqlDataAdapter da = new SqlDataAdapter(sqlSelectALL, conns);
            DataSet ds = new DataSet();
            da.Fill(ds);
            conns.Close();
            conns.Dispose();
            return ds;
        }

        //Excel的导出方法
        public void Excel_Export_Button(object sender, EventArgs e)
        {
            try
            {
                System.Data.DataTable dt = new System.Data.DataTable();

                dt = getds().Tables[0];
                ExportExcel(fileName,dt,outPutPath);
                Response.Write("<script>alert('数据导出成功!')</script>");
            }
            catch
            {
                Response.Write("<script>alert('数据导出失败!')</script>");
            }
        }


        //Excel的导出方法
        //====================================================================

        //Excel的导入方法
        public void Excel_Inport_Button(object sender, EventArgs e)
        {
            if (FileUpload1.PostedFile.FileName == "")
            {
                Response.Write("<script language=javascript>alert('请选择要上传的文件！');</script>");
                return;
            }
            //从Excel读取数据
            string filename = FileUpload1.PostedFile.FileName;

            if (string.IsNullOrEmpty(filename))
            {
                return;
            }

string filePath = Server.MapPath("/excel/") + DateTime.Now.Ticks.ToString() + ".xlsx";
FileUpload1.SaveAs(filePath);

System.Data.DataTable dt= ImportExcel(filePath);

            if (dt.Rows.Count >= 1)
            {
                //插入数据库就行了
                SqlConnection conns = new SqlConnection(strConn);
                int n = 0;
                foreach (System.Data.DataRow dr in dt.Rows)
                {
                    if (n == 0)
                    {
                        n++; continue;
                    }

if (conns.State == ConnectionState.Closed) conns.Open();

                    StringBuilder sb = new StringBuilder();
                    sb.Append(" insert into UserInfo(Name,Age,Address ,Phone) values(");
                    sb.Append(string.Format("'{0}',{1},'{2}',{3}",dr[0].ToString(), dr[1].ToString(),dr[2].ToString(),dr[3].ToString()));
                    sb.Append(" )");

                    SqlCommand cmd = new SqlCommand(sb.ToString(), conns);
                    cmd.ExecuteNonQuery();
                }
                conns.Close();

            }

}
//Excel的导入方法

        //==================================实现区==============================
        private int _ReturnStatus;
        private string _ReturnMessage;
        /// <summary>
        /// 执行返回状态
        /// </summary>
        public int ReturnStatus
        {
            get
            {
                return _ReturnStatus;
            }
        }

        /// <summary>
        /// 执行返回信息
        /// </summary>
        public string ReturnMessage
        {
            get
            {
                return _ReturnMessage;
            }
        }

        /// <summary>
        /// 导入EXCEL到DataSet
        /// </summary>
        /// <param name="fileName">Excel全路径文件名</param>
        /// <returns>导入成功的DataSet</returns>
        public System.Data.DataTable ImportExcel(string fileName)
        {
            //判断是否安装EXCEL
            Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
            if (xlApp == null)
            {
                _ReturnStatus = -1;
                _ReturnMessage = "无法创建Excel对象，可能您的计算机未安装Excel";
                return null;
            }

            //判断文件是否被其他进程使用
            Microsoft.Office.Interop.Excel.Workbook workbook;
            try
            {//打开已有Workbook
                workbook = xlApp.Workbooks.Open(fileName, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, 1, 0);
            }
            catch
            {
                _ReturnStatus = -1;
                _ReturnMessage = "Excel文件处于打开状态，请保存关闭";
                return null;
            }

            //获得所有Sheet名称
            int n = workbook.Worksheets.Count;
            string[] SheetSet = new string[n];
            System.Collections.ArrayList al = new System.Collections.ArrayList();
            for (int i = 1; i <= n; i++)
            {
                SheetSet[i - 1] = ((Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[i]).Name;
            }

            //释放Excel相关对象
            workbook.Close(null, null, null);
            xlApp.Quit();
            if (workbook != null)
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
                workbook = null;
            }
            if (xlApp != null)
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
                xlApp = null;
            }
            GC.Collect();

            //把EXCEL导入到DataSet
            System.Data.DataSet ds = new System.Data.DataSet();
            System.Data.DataTable table = new System.Data.DataTable();
            //string connStr = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = " + fileName + ";Extended Properties=Excel 8.0";
            string connStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties='Excel 12.0;HDR=NO;IMEX=1';";
            using (OleDbConnection conn = new OleDbConnection(connStr))
            {
                conn.Open();
                OleDbDataAdapter da;
                string sql = "select * from [" + SheetSet[0] + "$] ";
                da = new OleDbDataAdapter(sql, conn);
                da.Fill(ds, SheetSet[0]);
                da.Dispose();
                table = ds.Tables[0];
                conn.Close();
                conn.Dispose();
            }
            return table;
        }

        /// <summary>
        /// 把DataTable导出到EXCEL
        /// </summary>
        /// <param name="reportName">报表名称</param>
        /// <param name="dt">数据源表</param>
        /// <param name="saveFileName">Excel全路径文件名</param>
        /// <returns>导出是否成功</returns>
        public bool ExportExcel(string reportName, System.Data.DataTable dt, string saveFileName)
        {
            if (dt == null)
            {
                _ReturnStatus = -1;
                _ReturnMessage = "数据集为空！";
                return false;
            }

            bool fileSaved = false;
            Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();//建立一个Excel进程
            //设置进程的界面是否可见
            xlApp.Visible = true;
            if (xlApp == null)
            {
                _ReturnStatus = -1;
                _ReturnMessage = "无法创建Excel对象，可能您的计算机未安装Excel";
                return false;
            }

            Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
            Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
            Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
            worksheet.Cells.Font.Size = 10;
            Microsoft.Office.Interop.Excel.Range range;

            long totalCount = dt.Rows.Count;
            long rowRead = 0;
            float percent = 0;

            worksheet.Cells[1, 1] = reportName;
            ((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, 1]).Font.Size = 12;
            ((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, 1]).Font.Bold = true;

            //写入字段
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                worksheet.Cells[2, i + 1] = dt.Columns[i].ColumnName;
                range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[2, i + 1];
                range.Interior.ColorIndex = 15;
                range.Font.Bold = true;

            }
            //写入数值
            for (int r = 0; r < dt.Rows.Count; r++)
            {
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    worksheet.Cells[r + 3, i + 1] = dt.Rows[r][i].ToString();
                }
                rowRead++;
                percent = ((float)(100 * rowRead)) / totalCount;
            }

            //保存文件
            if (saveFileName != "")
            {
                try
                {
                    workbook.Saved = true;
                    workbook.SaveCopyAs(@saveFileName+"a.xls");
                    fileSaved = true;
                }
                catch (Exception ex)
                {
                    fileSaved = false;
                    _ReturnStatus = -1;
                    _ReturnMessage = "导出文件时出错,文件可能正被打开！\n" + ex.Message;
                }
            }
            else
            {
                fileSaved = false;
            }

            //释放Excel对应的对象

            if (worksheet != null)
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet);
                worksheet = null;
            }
            if (workbook != null)
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
                workbook = null;
            }
            if (workbooks != null)
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(workbooks);
                workbooks = null;
            }
            xlApp.Application.Workbooks.Close();
            xlApp.Quit();
            if (xlApp != null)
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
                xlApp = null;
            }
            GC.Collect();
            return fileSaved;
        }

//==================================实现区===============================

}
}

下面是：SiteSetting.cs的代码：

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Linq;
using System.Web;

namespace WebExcelExportInport
{
    public class SiteSetting
    {
          //数据库连接字符串
         public static string ConnectionString = ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString;

         //导出文件名称
         public static string FileName = "OutPut.xls";

         //导出文件地址
         public static string OutPutPath = "D:\\excleFile\\";
         //sql语句
        // public static string sqlSelALL = "select name,age,address,phone,CONVERT(varchar(100), AddDate, 20) AddDate,ReContents from userinfo";
         public static string sqlSelALL = "select name 姓名,age 年龄,address 地址,phone 电话 from userinfo";
         //从excel读数据
        // public static string ExToDB = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\";Data Source=";
         public static string ExToDB = "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=3\";Data Source=";

//string connStr ="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filepath + ";Extended Properties='Excel 12.0;HDR=NO;IMEX=1';";

}
}

下面是配置文件的内容：web.config

<?xml version="1.0" encoding="utf-8"?>

说明一下：先在D盘根目录下创建excleFile文件夹，接收导出的文件；项目里的excel文件夹是用来存储导入的excel文件的。

下面给出页面效果图：

数据库设计：

把connectionString="server=.;uid=sa;pwd=SQLServer2012;database=WebForms"换成自己机子上的就行了；

全文到此结束，够清楚够完整吧，本人水平有限，还有很多不足。还请各位多多指教。多多支持

0 0