C#读写 Excel文件类

川长思鸟来 2022-08-25 12:49 225阅读 0赞

最近整理了下前段时间做的东西，关于Excel的读写，在网上也看到不少关于这方面的代码，还是觉得不是很完善，就自己整理了一个Excel文件读写的类。代码如下：

/// <summary>
        /// 标题：Excel文件助手类
        /// 描述：1.读取指定条件的Excel信息到内存中
        ///       2.将内存中的信息导出到Excel文件中
        /// </summary>
    
    
        class Excel
        {
            //类成员
            private string m_filepath;//文件路径
            public OleDbConnection pOleDbconnection; //文件链接
            string strConnection;
    
            /// <summary>
            /// 构造函数
            /// </summary>
            /// <param name="filepath">excel文件路径</param>
            public Excel (string filepath)
            {
                m_filepath = filepath;
    
            }
            public Excel ()
            {
            }
            /// <summary>
            /// 获取数据源
            /// </summary>
            /// <param name="filepath_">Excel文件路径</param>
            /// <returns>Excel表格数据集</returns>
            /// <example>
            /// Provider代表连接驱动4.0版本
            /// Data Source代表Excel的路径
            /// Extended Properties代表连接Excel的版本，对于Excel 97以上版本都用Excel 8.0
            /// HDR代表默认Excel第一行是否列名，Yse代表是可以直接读取，No反之1
            /// </example>
    
            public DataSet Excel2DataSet (string filepath_)
            {
                
                    //获取文件扩展名
                    string fileType = System.IO.Path.GetExtension(filepath_);
                    if(fileType==".xls")
                    {
                        strConnection = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + m_filepath + ";Extended Properties=Excel 8.0";    
                    }
                    else if(fileType == ".xlsx")
                    {
                        strConnection = @"Provider=Microsoft.Jet.OLEDB.12.0;Data Source=" + m_filepath + ";Extended Properties=Excel 12.0";
                    }
                    else if(string.IsNullOrEmpty(fileType))
                    {
                        return null;
                    }
                    DataSet dataSet = new DataSet();
                    System.Data.DataTable dtSheetName = new System.Data.DataTable();
                    OleDbDataAdapter dataAdapter;//数据适配器
                    try
                    {   //初始化链接并打开
                        pOleDbconnection = new OleDbConnection(strConnection);
                        pOleDbconnection.Open();
     
                        //获取数据源的表定义元数据
                        string SheetName = "";
                        dtSheetName = pOleDbconnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,new object[] {null,null,null,"TABLE" });
                       
                        //初始化适配器
                        dataAdapter = new OleDbDataAdapter();
                        for(int i = 0;i < dtSheetName.Rows.Count;i++)
                        {
                            SheetName = (string)dtSheetName.Rows[i]["TABLE_NAME"];
                            string sql_F = @"Select * FROM [" + SheetName + "]";//SQL
                            //筛选有效表
                            if(SheetName.Contains("$") || SheetName.Replace("'","").EndsWith("$"))
                            {
                                dataAdapter.SelectCommand = new OleDbCommand(sql_F,pOleDbconnection);
                                DataSet dsItem = new DataSet();
                                dataAdapter.Fill(dsItem,"[" + SheetName + "]");
                                dataSet.Tables.Add(dsItem.Tables[0].Copy());
                            }
                         
                        }
    
                    }
                    catch(Exception ex)
                    {
    
                        System.Windows.Forms.MessageBox.Show("链接Excel出错！"+ex.Message);
                    }
    
                return dataSet;
            }
             /// <summary>
             /// 将DataTable导出到Excel
             /// </summary>
             /// <param name="excelTable">DataTable</param>
             /// <param name="strFilepath">文件路径</param>
            public void DataTable2Excel (System.Data.DataTable excelTable,string strFilepath)
            {
                Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.ApplicationClass();
    
                try
                {
                    app.Visible = false;
                    Microsoft.Office.Interop.Excel.Workbook wBook = app.Workbooks.Add(true);
                    Microsoft.Office.Interop.Excel.Worksheet wSheet = wBook.Worksheets[1] as Microsoft.Office.Interop.Excel.Worksheet;
                    if(excelTable.Rows.Count > 0)
                    {
                        int row = 0;
                        row = excelTable.Rows.Count;
                        int col = excelTable.Columns.Count;
                        for(int i = 0;i < row;i++)
                        {
                            for(int j = 0;j < col;j++)
                            {
                                string str = excelTable.Rows[i][j].ToString();
                                wSheet.Cells[i + 2,j + 1] = str;
                            }
                        }
                    }
    
                    int size = excelTable.Columns.Count;
                    for(int i = 0;i < size;i++)
                    {
                        wSheet.Cells[1,1 + i] = excelTable.Columns[i].ColumnName;
                    }
                    //设置禁止弹出保存和覆盖的询问提示框   
                    app.DisplayAlerts = false;
                    app.AlertBeforeOverwriting = false;
                    //保存工作簿   
                    wBook.Save();
                    //保存excel文件   
                    app.Save(strFilepath);
                    app.SaveWorkspace(strFilepath);
                    app.Quit();
                    app = null;
                  
                }
                catch(Exception err)
                {
                   System.Windows.Forms.MessageBox.Show("导出Excel出错！错误原因：" + err.Message,"提示信息",
                        System.Windows.Forms.MessageBoxButtons.OK,System.Windows.Forms.MessageBoxIcon.Information);
                   
                }
                finally
                {
                }  
            }
           
        }