C#-文件读写（将DataSet数据的保存为EXCEL文件，将Excel文件读取到DataSet中）

最新推荐文章于 2024-07-26 16:48:19 发布
yellow_s
最新推荐文章于 2024-07-26 16:48:19 发布
阅读量3.7k
点赞数 8
本文链接：https://blog.csdn.net/jkhmf/article/details/82946965
版权
主要是用来保存应用程序的参数与数据。
/// <summary>
/// 将DataSet数据保存为EXCEL文件
/// </summary>
/// <param name="Path">将要保存的文件路径</param>
/// <param name="ds">将要保存的DataSet数据表可多页面</param>
        public void DataSetToExcel(string Path,System.Data.DataSet ds)
        {
            string strCon = string.Empty;
            FileInfo file = new FileInfo(Path);
            string extension = file.Extension;
            switch (extension)
            {
                case ".xls":
                    strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Path + ";Extended Properties=Excel 8.0;";
                    break;
                case ".xlsx":
                    strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Path + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=0;'";
                    break;
                default:
                    strCon = "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 con = new System.Data.OleDb.OleDbConnection(strCon))
                {
                    con.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), con);    //覆盖文件时可能会出现Table 'Sheet1' already exists.所以这里先删除了一下
                            cmd.ExecuteNonQuery();
                        }
                       
                    }
                    catch { }
                    //创建表格字段
                    for(int i = 0; i < ds.Tables.Count; i++)
                    {
                        strSQL.Clear();
                        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(), con);
                        cmd.ExecuteNonQuery();

                        //添加数据
                        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++)
                            {
                                strvalue.Append("'" + ds.Tables[i].Rows[i][j].ToString() + "'");
                                if (j != ds.Tables[i].Columns.Count - 1)
                                {
                                    strvalue.Append(",");
                                }
                                else
                                {
                                }
                            }
                            cmd.CommandText = strSQL.Append(" insert into [" + ds.Tables[i].TableName + "] values (").Append(strvalue).Append(")").ToString();
                            cmd.ExecuteNonQuery();
                        }
                    }
                     con.Close();
                }
            }
            catch { }
        }


 /// <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();
            }

}