C#中将数据导入、导出到Excel

将DataSet中的数据导入到Excel（导出）

下面的Demo演示的功能是：点击按钮获取保存的位置，并且从数据库中查询用户信息表，然后调用 DataSetToExcel方法，传入保存的位置和一个DataSet对象，就可以在该的位置产生一个Excel文件

		 //按钮事件，获取保存位置
            private void button1_Click(object sender, EventArgs e)
        {
            SaveFileDialog saveFile = new SaveFileDialog();
            saveFile.Title = "请选择保存位置";
            string time = DateTime.Now.ToLongDateString();
            saveFile.FileName = time + " 住户信息";
            saveFile.Filter = "xlsx文件|*.xlsx";
            string path = "";
            if (saveFile.ShowDialog() == DialogResult.OK)
            {
                path = saveFile.FileName;
            }
            else
            {
                return;
            }
            string sql = "select * from UserInfo";
            DataSet ds = DBHelper.GetDataSet(sql);
            DataSetToExcel(path, ds);
            MessageBox.Show("保存成功！", "备份提示");
        }
        
        //调用该方法可将DataSet中的数据导出到Excel
         public void DataSetToExcel(string Path, DataSet ds)
        {
             string strConn = string.Empty;//创建连接字符串
            FileInfo file = new FileInfo(Path);//文件路径
            string extension = file.Extension;
            switch (extension)
            {
                case ".xls":
                    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Path + ";Extended Properties=Excel 8.0;";
                    break;
                case ".xlsx":
                    strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Path + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=0;'";
                    break;
                default:
                    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Path + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=0;'";
                    break;
            }
            try
            {
                //创建连接对象
                using (System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(strConn))
                {
                    conn.Open();//打开连接
                    StringBuilder strSQL = new StringBuilder();
                    System.Data.OleDb.OleDbCommand cmd;//创建命令对象
                    try
                    {
                        for (int i = 0; i < ds.Tables.Count; i++)//删除所有表
                        {
                            cmd = new System.Data.OleDb.OleDbCommand(string.Format("drop table {0}", ds.Tables[i].TableName), conn);    //覆盖文件时可能会出现Table 'Sheet1' already exists.所以这里先删除了一下
                            cmd.ExecuteNonQuery();
                        }

                    }
                    catch { }
                    //创建表格字段
                    for (int i = 0; i < ds.Tables.Count; i++)
                    {
                        strSQL.Clear();//移除strSQL中的所有字符串
                        //编写创建一张表的SQL语句
                        //创建所有列标题
                        strSQL.Append("CREATE TABLE ").Append("[" + ds.Tables[i].TableName + "]");
                        strSQL.Append("(");

                        for (int j = 0; j < ds.Tables[i].Columns.Count; j++)
                        {
                            strSQL.Append("[" + ds.Tables[i].Columns[j].ColumnName + "] text,");
                        }
                        strSQL = strSQL.Remove(strSQL.Length - 1, 1);
                        strSQL.Append(")");

                        cmd = new System.Data.OleDb.OleDbCommand(strSQL.ToString(), conn);//实例化命令对象
                        cmd.ExecuteNonQuery();//执行命令
                        //end创建列标题
                        //添加数据,一行一行的添加，用SQL语句只能一行一行的添加
                        for (int k = 0; k < ds.Tables[i].Rows.Count; k++)//遍历行数
                        {
                            strSQL.Clear();
                            StringBuilder strvalue = new StringBuilder();
                            for (int j = 0; j < ds.Tables[i].Columns.Count; j++)//遍历列数
                            {
                                //如果是加密数据则进行解密后再备份
                                if (ds.Tables[i].Columns[j].ColumnName.Trim() == "住户姓名" || ds.Tables[i].Columns[j].ColumnName.Trim() == "联系电话" || ds.Tables[i].Columns[j].ColumnName.Trim() == "身份证号码" || ds.Tables[i].Columns[j].ColumnName.Trim() == "登录账号" || ds.Tables[i].Columns[j].ColumnName.Trim() == "登录密码")
                                {
                                    strvalue.Append("'" + StringSecurity.DESDecrypt(ds.Tables[i].Rows[i][j].ToString()) + "'");
                                    if (j != ds.Tables[i].Columns.Count - 1)//如果不是最后一行，就加 , 用于分隔每个值
                                    {
                                        strvalue.Append(",");
                                    }
                                }
                                else
                                {
                                    strvalue.Append("'" + ds.Tables[i].Rows[i][j].ToString() + "'");
                                    if (j != ds.Tables[i].Columns.Count - 1)//如果不是最后一行，就加 , 用于分隔每个值
                                    {
                                        strvalue.Append(",");
                                    }
                                }
                            }
                            //用CommandText 属性将Command中的文字改变
                            cmd.CommandText = strSQL.Append(" insert into [" + ds.Tables[i].TableName + "] values (").Append(strvalue).Append(")").ToString();
                            cmd.ExecuteNonQuery();
                        }
                    }
                    conn.Close();//关闭连接字符串
                }
            }
            catch { }
        }

将Excel中的数据导入到DataTable（导入）

将EXCLE文件读取到DataSet表中。支持多工作表读取

/// <summary>
 /// 将EXCLE文件读取到DataSet表中。支持多工作表读取
 /// </summary>
 /// <param name="pathName">要读取的文件路径</param>
 /// <returns>返回DataSet表</returns>
        public System.Data.DataSet ExcelToDataSet(string pathName,string sheetName="")
        {
            System.Data.DataSet ds = new System.Data.DataSet();
            string ConnectionString = string.Empty;
            FileInfo file = new FileInfo(pathName);
            if (!file.Exists)
            {
                throw new Exception("文件不存在");
            }
            string extension = file.Extension;
            switch (extension)                          // 连接字符串          
            {
                case ".xls":
                    ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathName + ";Extended Properties='Excel 8.0;HDR=no;IMEX=1;'";
                    break;
                case ".xlsx":
                    ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + pathName + ";Extended Properties='Excel 12.0;HDR=no;IMEX=1;'";
                    break;
                default:
                    ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathName + ";Extended Properties='Excel 8.0;HDR=no;IMEX=1;'";
                    break;
            }
            System.Data.OleDb.OleDbConnection con = new System.Data.OleDb.OleDbConnection(ConnectionString);
            try
            {
                con.Open();
                if (sheetName != "")                      //若指定了工作表名              
                {
                    //读Excel的过程中，发现dt末尾有些行是空的，所以在sql语句中加了Where 条件筛选符合要求的数据。OLEDB会自动生成列名F1,F2……Fn                     
                    System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand("select * from [" + sheetName + "$] where F1 is not null ", con);
                    System.Data.OleDb.OleDbDataAdapter apt = new System.Data.OleDb.OleDbDataAdapter(cmd);
                    try
                    {
                        apt.Fill(ds);
                    }
                    catch (Exception ex)
                    {
                        throw new Exception("该Excel文件中未找到指定工作表名," + ex.Message);//抛出异常接收
                    }
                    ds.Tables.Clear();
                    ds.Tables.Add(sheetName);
                }
                else
                {                   //默认读取第一个有数据的工作表                   
                    var tables = con.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[]
                    { });
                    if (tables.Rows.Count == 0)
                    {
                        throw new Exception("Excel必须包含一个表");
                    }
                    ds.Tables.Clear();
                    foreach (System.Data.DataRow row in tables.Rows)
                    {
                        string strSheetTableName = row["TABLE_NAME"].ToString();                       //过滤无效SheetName     
                        
                        if (strSheetTableName.Contains("$") && strSheetTableName.Replace("'", "").EndsWith("$"))//分析文件名Contains 是否有$有为真，Replace字符替换，EndsWith看字串尾是否有指定字符
                        {
                            System.Data.DataTable tableColumns = con.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Columns,
                                new object[] { null, null, strSheetTableName, null });
                            if (tableColumns.Rows.Count < 2)                     //工作表列数                             
                                continue;
                            System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand("select * from [" + strSheetTableName + "] where F1 is not null", con);
                            System.Data.OleDb.OleDbDataAdapter apt = new System.Data.OleDb.OleDbDataAdapter(cmd);
                            System.Data.DataTable dt = new System.Data.DataTable();
                            apt.Fill(dt);
                            dt.TableName = strSheetTableName.Replace("'", "").Replace("$", "");
                            ds.Tables.Add(dt);
                        }
                    }
                }
                for(int i = 0; i < ds.Tables.Count; i++)
                {
                    if (ds.Tables[i].Rows.Count < 0)
                        throw new Exception("表必须包含数据");                       //重构字段名      
                    System.Data.DataRow headRow = ds.Tables[i].Rows[0];
                    foreach (System.Data.DataColumn c in ds.Tables[i].Columns)
                    {
                        string headValue = (headRow[c.ColumnName] == DBNull.Value || headRow[c.ColumnName] == null) ? "" : headRow[c.ColumnName].ToString().Trim();
                        if (headValue.Length == 0)
                        {
                            throw new Exception("必须输入列标题");
                        }
                        if (ds.Tables[i].Columns.Contains(headValue))
                        {
                            throw new Exception("不能用重复的列标题：" + headValue);
                        }
                        c.ColumnName = headValue;
                    }
                    ds.Tables[i].Rows.RemoveAt(0);
                }
                
                return ds;
            }
            catch (Exception ee)
            {
                throw ee;
            }
            finally
            {
                con.Close();
            }
 
}