excel导入导出

1，导入到excel：

 Public Function ToExcel(ByVal da As DataGridView)
        Dim MyExcel As New Microsoft.Office.Interop.Excel.Application()
        MyExcel.Application.Workbooks.Add(True)
        MyExcel.Visible = True

        '去除dataGridView1的编号列（这里也可以不要）
        Dim m As Integer
        For m = 0 To da.ColumnCount - 1
            MyExcel.Cells(1, m + 1) = da.Columns(m).HeaderText
        Next m

        '往excel表里添加数据
        Dim i As Integer
        For i = 0 To da.RowCount - 1
            Dim j As Integer
            For j = 0 To da.ColumnCount - 1
                If da(j, i).Value Is System.DBNull.Value Then

                    MyExcel.Cells(i + 2, j + 1) = ""
                Else
                    MyExcel.Cells(i + 2, j + 1) = da(j, i).Value.ToString

                End If
            Next j
        Next i
        Return True
    End Function

 

 

2，从excel中导入数据到数据库

B层

/*******************************************************************************
 *文    件：CreateExcelDataBLL.cs
 *文件说明：基础系统-把excel转化为datatable
 *创建日期：2013年1月23日9:43:16
 *修改作者：
 *修改日期：
 *修改描述：
 *版 本 号：V1.0
 *版本号变更记录：     
********************************************************************************/

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

//引用各命名空间
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.IO;

namespace BLL
{
    public class CreateExcelDataBLL
    {
        //构造方法
        public CreateExcelDataBLL()
        {
           
        }
        /// <summary>
        /// 传入excel路径，转换为datatable
        /// </summary>
        /// <param name="url"></param>
        /// <returns></returns>
        public DataTable CreateExcelDataSource(string url)
        {
            //定义一个DataTable数据表
            DataTable dt = null;

            //获得excel数据
            string connetionStr = "Provider=Microsoft.Jet.OleDb.4.0;" + "data source=" + url + ";Extended Properties='Excel 8.0; HDR=YES; IMEX=1'";
            //从Excel表的Sheet1单元格获取数据
            string strSql = "select * from [Sheet1$]";
            OleDbConnection oleConn = new OleDbConnection(connetionStr);

            OleDbDataAdapter oleAdapter = new OleDbDataAdapter(strSql, connetionStr);
            try
            {
                //把Excel数据填充给DataTable
                dt = new DataTable();
                oleAdapter.Fill(dt);
                //返回数据表
                return removeEmpty(dt);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                oleAdapter.Dispose();
                oleConn.Close();
                oleConn.Dispose();
                //删除上传的Excel文件（因为该文件的存在会占用多余的网站空间）
                if (File.Exists(url))
                {
                    File.Delete(url);
                }
            }
        }
        /// <summary>
        /// 循环去除datatable中的空行
        /// </summary>
        /// <param name="dt"></param>
        protected DataTable removeEmpty(DataTable dt)
        {
            List<DataRow> removelist = new List<DataRow>();
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                bool rowdataisnull = true;
                for (int j = 0; j < dt.Columns.Count; j++)
                {

                    if (!string.IsNullOrEmpty(dt.Rows[i][j].ToString().Trim()))
                    {

                        rowdataisnull = false;
                    }

                }
                if (rowdataisnull)
                {
                    removelist.Add(dt.Rows[i]);
                }

            }
            for (int i = 0; i < removelist.Count; i++)
            {
                dt.Rows.Remove(removelist[i]);
            }
            return dt; 
        }
    }
}

 

D层

 /// <summary>
        /// 批量导入教师
        /// </summary>
        /// <param name="ds">Dataset</param>
        /// <returns>布尔值</returns>
        public Boolean ImportMember(DataSet ds)
        {
            //添加教师信息
            Boolean flagAddMember;
            //调用sqlHelper的"批量导入datatable表"的方法
            try
            {
                flagAddMember = DbHelperSQL.InsertTable(ds.Tables["dt_AddDutyTeacher"], "T_DutyTeacherInfo", ds.Tables["dt_AddDutyTeacher"].Columns);
            }
            catch (Exception e)
            {

                return false;
            }

            return true;

        }

 

界面：

 #region 批量信息
        protected void btnImportTeacher_Click(object sender, EventArgs e)
        {
            //成员业务逻辑层
            BLL.DutyTeacherInfoBLL   teacherInfo = new BLL.DutyTeacherInfoBLL ();
            //BLL层把Excel转化为datatable类
            BLL.CreateExcelDataBLL createExcelData = new BLL.CreateExcelDataBLL();
           
            //将错误信息导出到Excel           
            BLL.DataTableToExcel dataTableToExcel = new BLL.DataTableToExcel();

            //获取上传文件地址
            string url = fupImportTeacher.PostedFile.FileName.ToString();

            if (url == "")
            {
                //数据源为空，弹出提示：请选择Excel文件！
                Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript' defer>alert('请选择Excel文件！');</script>");
                return;
            }

            string urlLocation = url.Substring(url.LastIndexOf("\\") + 1);//获取文件名

            DataTable dtAllMember;
            //在系统中建文件夹up,并将excel文件另存
            this.fupImportTeacher.SaveAs(Server.MapPath("~\\UploadFile") + "\\" + urlLocation);//记录文件名到服务器相对应的文件夹中

            //获得文件路径
            string strpath = Server.MapPath("~\\UploadFile") + "\\" + urlLocation;

            //把上传的Excel转换为datatable
            dtAllMember = createExcelData.CreateExcelDataSource(strpath);

            /*-------------------------------------判断数据源是否合法--------------------------------------*/
            //定义要求的字段数据
            string[] headfields = { "星期", "教师姓名", "教师手机号", "教师QQ号", "备注" };
            //判断dtAllTeacher中是否包含全部要求的字段
            for (int i = 0; i < headfields.Length; i++)
            {
                //只要有一个字段不被包含，则提示"数据源缺少必要的字段"，并退出循环和整个方法
                if (!dtAllMember.Columns.Contains(headfields[i]))
                {
                    Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript' defer>alert('数据源缺少必要的字段,请检查Excel数据源！');</script>");
                    //退出方法
                    return;
                }
            }

            //判断数据源中是否有数据
            if (dtAllMember.Rows.Count == 0)
            {
                Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript' defer>alert('Excel文件中没有任何数据，请填充数据！');</script>");
                //退出方法
                return;
            }

            //判断是否有相同手机号的行
            DataView dvTeacher = new DataView(dtAllMember);
            if (dvTeacher.Count != dvTeacher.ToTable(true, "教师手机号").Rows.Count)
            {
                Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript' defer>alert('Excel中有相同的教师手机号，教师手机号不能相同！');</script>");
                //退出方法
                return;
            }


            DataSet dsMember = new DataSet("dt_DutyTeacher"); //创建一个名为dt_DutyTeacher的DataSet

            //手动创建的新数据表-学生数据表
            DataTable dtAddDutyTeacher = new DataTable("dt_AddDutyTeacher"); //创建一个名为dt_AddDutyTeacher的DataTalbe
            //为dt_AddDutyTeacher表内建立Column（表头）,添加数据列:
            dtAddDutyTeacher.Columns.Add(new DataColumn("week", typeof(string)));
            dtAddDutyTeacher.Columns.Add(new DataColumn("teacherName", typeof(string)));
            dtAddDutyTeacher.Columns.Add(new DataColumn("teacherPhone", typeof(string)));
            dtAddDutyTeacher.Columns.Add(new DataColumn("teacherQQ", typeof(string)));
            dtAddDutyTeacher.Columns.Add(new DataColumn("explian", typeof(string)));
            


            //新建数据表用于存放错误数据
            DataTable dtErrorRow = new DataTable();
            //为dtErrorRow创建列
            dtErrorRow.Columns.Add(new DataColumn("星期", typeof(string)));
            dtErrorRow.Columns.Add(new DataColumn("教师姓名", typeof(string)));
            dtErrorRow.Columns.Add(new DataColumn("教师手机号", typeof(string)));
            dtErrorRow.Columns.Add(new DataColumn("教师QQ号", typeof(string)));
           
            dtErrorRow.Columns.Add(new DataColumn("备注", typeof(string)));
            dtErrorRow.Columns.Add(new DataColumn("错误原因", typeof(string)));

            //从上传的Excel转换为的datatable表中取出数据，放入值班教师信息。
            for (int intRow = 0; intRow < dtAllMember.Rows.Count; intRow++)
            {

                //成员姓名
                strweek  = dtAllMember.Rows[intRow]["星期"].ToString();
                //成员姓名
                strteacherName  = dtAllMember.Rows[intRow]["教师姓名"].ToString();
                strteacherPhone  = dtAllMember.Rows[intRow]["教师手机号"].ToString();
                strQQ  = dtAllMember.Rows[intRow]["教师QQ号"].ToString();
                strExplian = dtAllMember.Rows[intRow]["备注"].ToString();


                
               
                //判断星期格式是否正确
                if(strweek !="星期一" && strweek !="星期二" && strweek !="星期三" && strweek !="星期四" &&strweek !="星期五" &&strweek !="星期六" &&strweek !="星期日" )
                 {
                    //向错误列表中添加当前行
                    AddErrorRow(dtErrorRow, "星期格式错误，只能为大写格式如：星期一");
                    //跳出当前循环
                    continue;
                 }

                if (strweek == "")
                {
                    //向错误列表中添加当前行
                    AddErrorRow(dtErrorRow, "星期不能为空");
                    //跳出当前循环
                    continue;
                }
                ////判断是否包含教师姓名
                if (strteacherName == "")
                {
                    //向错误列表中添加当前行
                    AddErrorRow(dtErrorRow, "教师姓名不能为空");
                    //跳出当前循环
                    continue;
                }
                //判断是否包含教师手机号
                if (strteacherPhone == "")
                {
                    //向错误列表中添加当前行
                    AddErrorRow(dtErrorRow, "教师手机号不能为空");
                    //跳出当前循环
                    continue;
                }
                ////判断是否包含教师QQ号
                //if (strQQ == "")
                //{
                //    //向错误列表中添加当前行
                //    AddErrorRow(dtErrorRow, "教师QQ号不能为空");
                //    //跳出当前循环
                //    continue;
                //}

                //添加成员信息表的新行
                DataRow drAddDutyTeacher = dtAddDutyTeacher.NewRow();//注意这边创建dt的新行的方法。指定类型是DataRow而不是TableRow，然后不用new直接的用创建的DataTable下面的NewRow方法。
                //学生信息表对应的各列值
                drAddDutyTeacher["week"] = strweek;
                drAddDutyTeacher["teacherName"] = strteacherName;
                drAddDutyTeacher["teacherPhone"] = strteacherPhone;
                drAddDutyTeacher["teacherQQ"] = strQQ;
                drAddDutyTeacher["explian"] = strExplian ;

                dtAddDutyTeacher.Rows.Add(drAddDutyTeacher);  //将一整条数据写入表中             
            }

            //将表加入DataSet中:成员信息
            dsMember.Tables.Add(dtAddDutyTeacher);


            //将DataSet中数据表导入数据库
            Boolean flagImportMember = teacherInfo.ImportMember(dsMember);
            //判断成员是否导入成功
            if (true == flagImportMember && dtErrorRow.Rows.Count == 0)
            {
                //导入成功，弹出提示
                Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript' defer>alert('成员信息导入成功！');</script>");
            }
            else if (false == flagImportMember)
            {
                //导入失败，弹出提示
                Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript' defer>alert('成员信息导入失败！');</script>");
            }
            else if (true == flagImportMember && dtErrorRow.Rows.Count != 0)
            {
                //只有部分数据导入成功，弹出提示
                Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript' defer>alert('成员信息只有部分导入成功，有错误的部分已导出为EXCEL，请查看新导出的EXCEL；新导出的Excel修改错误后可作为数据源重新导入');</script>");
                //将错误数据导出到Excel并打开显示
                dataTableToExcel.ToExcel(dtErrorRow);
            }
        }
        #endregion

        #region 向错误列表中添加当前行
        /// <summary>
        /// 向错误列表中添加当前行
        /// </summary>
        /// <param name="dtErrorRow">代表错误列表的datatable</param>
        private void AddErrorRow(DataTable dtErrorRow, string reason)
        {
            //新建数据行
            DataRow drAddErrorRow = dtErrorRow.NewRow();
            //填充数据行
            drAddErrorRow["星期"] = "\'" + strweek;
            drAddErrorRow["教师姓名"] = "\'" + strteacherName ;
            drAddErrorRow["教师手机号"] = strteacherPhone;
            drAddErrorRow["教师QQ号"] = "\'" + strQQ;
            drAddErrorRow["备注"] = strExplian;

            drAddErrorRow["错误原因"] = reason;
            dtErrorRow.Rows.Add(drAddErrorRow);
        }
        #endregion