C#实现对Excel文件的读取和写入操作

      最近公司要求对添加的外链进行批量检查。需要从Excel中读取源数据，检查后把结果再导出为Excel。在网上搜寻了一番，发现对Excel的操作大致为两种：

      1、使用ado.net将Excel文件当做数据源来读取；

      2、使用com组件（Excel组件）来实现对Excel的操作。

      我在自己的项目中是使用的ado.net来读取数据，然后使用Excel组件来导出数据。

#region 导入Excel文件内的数据
        /// <summary>
        /// 导入Excel文件内的数据
        /// </summary>
        /// <param name="filename">Excel文件路径</param>
        /// <param name="sheetname">工作表名</param>
        /// <returns>返回DataTable</returns>
        public DataTable ReadExcelData(string filename, string sheetname) {
            OleDbConnection conn = null;
            try
            {
                string constr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filename + ";Extended Properties=/"Excel 8.0;HDR=NO;IMEX=1/"";
                using (conn = new OleDbConnection(constr))
                {
                    string strExcel = "SELECT * FROM ["+sheetname+"$]";
                    conn.Open();
                    OleDbCommand cmd = new OleDbCommand(strExcel, conn);
                    OleDbDataAdapter oda = new OleDbDataAdapter(cmd);
                    DataSet ds = new DataSet();
                    oda.Fill(ds, "ExcelSource");
                    DataTable dt = new DataTable();
                    dt = ds.Tables["ExcelSource"];
                    return dt;
                }
            }
            catch (Exception ex) {
                conn.Close();
                conn.Dispose();
                return null;
            }
        }
        #endregion
 

      这里有必要提一下关于Excel的链接字符串：Extended Properties=/"Excel 8.0; HDR=N0; IMEX=1/"

Office 2003使用的是Excel 8.0，HDR有两个值，HDR=YES或者HDR=NO。HDR=YES表示第一行是标题栏，不作为数据读取。HDR=NO表示第一行是数据行，要正常读取。IMEX=1，这样当单元格内有纯数字时也会当做字符串输出，不然纯数字的单元格会显示空。

 

     下面再贴一段导出数据到Excel的代码：

    

      首先在顶部引用Excel的组件dll

 

using Excel = Microsoft.Office.Interop.Excel;

 

#region 导出数据生成EXCEL文件
        /// <summary>
        /// 导出数据生成Excel
        /// </summary>
        /// <param name="success">成功的对象集合</param>
        /// <param name="failure">失败的对象集合</param>
        /// <param name="adddate">添加外链的日期</param>
        /// <param name="filename">保存路径</param>
        /// <param name="name">推广员姓名</param>
        /// <param name="totallink">外链总条数</param>
        /// <param name="s_link">成功的外链条数</param>
        public void Export2Excel(List<OutLink2> success, List<OutLink2> failure,string adddate, string filename, string name,int totallink,int s_link)
        {
            object missing = System.Reflection.Missing.Value;
            Excel.ApplicationClass myexcel = new Excel.ApplicationClass();
            Excel._Workbook xbk;   //工作簿
            Excel.Worksheet xst;   //工作Sheet
            Excel.Worksheet xst2;  //工作表Sheet2
            xbk = myexcel.Workbooks.Add(true);
            float s_c = success.Count;  //成功条数
            float s_t = success.Count + failure.Count;     //总条数
            double s_per = Math.Ceiling(s_c / s_t * 100);  //成功率

            float f_c = failure.Count;                     //失败条数
            double f_per = Math.Ceiling(f_c / s_t * 100);  //失败率

            float f_link = totallink - s_link;             //失败外链数
            double slink_per = Math.Ceiling((float)s_link / totallink * 100);   //外链数成功率
            double flink_per = Math.Ceiling((float)f_link / totallink * 100);   //外链数失败率

            xst = (Excel.Worksheet)xbk.ActiveSheet;
            xst.Name = name + "成功列表";
            myexcel.Cells[1, 1] = "添加日期( " + adddate + " )";
            myexcel.Cells[1, 2] = "检查日期( " + DateTime.Now + " )";
            myexcel.Cells[2, 1] = "本批次成功网址数( " + s_c.ToString() + "条 )";
            myexcel.Cells[2, 2] = "本批次总网址数( " + s_t.ToString() + "条 )";
            myexcel.Cells[2, 3] = "本批次网址成功率( " + s_per.ToString() + "% )";
            myexcel.Cells[3, 1] = "本批次成功外链数( " + s_link.ToString() + "条 )";
            myexcel.Cells[3, 2] = "本批次外链总数( " + totallink.ToString() + "条 )";
            myexcel.Cells[3, 3] = "本批次外链数成功率( " + slink_per.ToString() + "% )";
           
            for (int i = 0; i < success.Count; i++)
            {
                myexcel.Cells[i + 4, 1] = success[i]._uri;   //设置第一行的发布网址
                for (int j = 0; j < success[i]._targetlinks.Count; j++)
                {
                    myexcel.Cells[i + 4, j + 2] = success[i]._targetlinks[j].ToString();
                }
            }
            myexcel.Sheets.Add(missing, missing, 1, Excel.XlSheetType.xlWorksheet);

            xst2 = (Excel.Worksheet)xbk.ActiveSheet;
            xst2.Name = name + "失败列表";
            myexcel.Cells[1, 1] = "添加日期( " + adddate + " )";
            myexcel.Cells[1, 2] = "检查日期( " + DateTime.Now + " )";
            myexcel.Cells[2, 1] = "本批次失败网址数( " + f_c.ToString() + "条 )";
            myexcel.Cells[2, 2] = "本批次总网址数( " + s_t.ToString() + "条 )";
            myexcel.Cells[2, 3] = "本批次网址失败率( " + f_per.ToString() + "% )";
            myexcel.Cells[3, 1] = "本批次失败外链数( " + f_link.ToString() + "条 )";
            myexcel.Cells[3, 2] = "本批次外链总数( " + totallink.ToString() + "条 )";
            myexcel.Cells[3, 3] = "本批次外链数失败率( " + flink_per.ToString() + "% )";

            for (int i = 0; i < failure.Count; i++)
            {
                myexcel.Cells[i + 4, 1] = failure[i]._uri;   //设置第一行的发布网址
                for (int j = 0; j < failure[i]._targetlinks.Count; j++)
                {
                    myexcel.Cells[i + 4, j + 2] = failure[i]._targetlinks[j].ToString();
                }
            }
            myexcel.Sheets.Add(missing, missing, 1, Excel.XlSheetType.xlWorksheet);

            myexcel.Visible = true;
            xbk.SaveAs(filename, missing, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlShared, missing, missing, missing, missing, missing);
            myexcel.Quit();  //释放对象
        }
        #endregion

写入Excel就相当于是一个二维数组，[*,*]，行和列就可以确定数据的位置，所以只需要给相应的位置赋值就可以了！