C#操作Excel文件(读取Excel，写入Excel)

来源：互联网发布：koka方便面知乎编辑：程序博客网时间：2024/04/19 19:59

2009-01-10 09:40

看到论坛里面不断有人提问关于读取excel和导入excel的相关问题。闲暇时间将我所知道的对excel的操作加以总结，现在共享大家，希望给大家能够给大家带了一定的帮助。
另外我们还要注意一些简单的问题1.excel文件只能存储65535行数据，如果你的数据大于65535行，那么就需要将excel分割存放了。2.关于乱码，这主要是字符设置问题。

1.加载Excel（读取excel内容）返回值是一个DataSet

        //加载Excel 
        public static DataSet LoadDataFromExcel(string filePath) 
         { 
            try 
             { 
                string strConn; 
                 strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR=False;IMEX=1'"; 
                 OleDbConnection OleConn = new OleDbConnection(strConn); 
                 OleConn.Open(); 
                 String sql = "SELECT * FROM   [Sheet1$]";//可是更改Sheet名称，比如sheet2，等等 
                 OleDbDataAdapter OleDaExcel = new OleDbDataAdapter(sql, OleConn); 
                 DataSet OleDsExcle = new DataSet(); 
                 OleDaExcel.Fill(OleDsExcle, "Sheet1"); 
                 OleConn.Close(); 
                return OleDsExcle; 
             } 
            catch (Exception err) 
             { 
                 MessageBox.Show("数据绑定Excel失败!失败原因：" + err.Message, "提示信息", 
                     MessageBoxButtons.OK, MessageBoxIcon.Information); 
                return null; 
             } 
         } 

2.写入Excel内容，参数：excelTable是要导入excel的一个table表

        public static bool SaveDataTableToExcel(System.Data.DataTable excelTable, string filePath) 
         { 
             Microsoft.Office.Interop.Excel.Application app = 
                new Microsoft.Office.Interop.Excel.ApplicationClass(); 
            try 
             { 
                 app.Visible = false; 
                 Workbook wBook = app.Workbooks.Add(true); 
                 Worksheet wSheet = wBook.Worksheets[1] as 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(filePath); 
                 app.SaveWorkspace(filePath); 
                 app.Quit(); 
                 app = null; 
                return true; 
             } 
            catch (Exception err) 
             { 
                 MessageBox.Show("导出Excel出错！错误原因：" + err.Message, "提示信息", 
                     MessageBoxButtons.OK, MessageBoxIcon.Information); 
                return false; 
             } 
            finally 
             { 
             } 
         } 