C# 数据写入Excel表

引用Microsoft.Office.Interop.Excel.dll才能编译。 

/// <summary>

        /// 写入xls
        /// </summary>
        /// <param name="filename"></param>
        public bool WriteXls(string filename,int count,string code,string name)
        {
            //启动Excel应用程序
            Microsoft.Office.Interop.Excel.Application xls = new Microsoft.Office.Interop.Excel.Application();
            _Workbook book = xls.Workbooks.Add(Missing.Value); //创建一张表，一张表可以包含多个sheet


            _Worksheet sheet;//定义sheet变量
            xls.Visible = false;//设置Excel后台运行
            xls.DisplayAlerts = false;//设置不显示确认修改提示
            for (int i = 1; i <= 1; i++)//循环创建并写入数据到sheet
            {
                try
                {
                    sheet = (_Worksheet)book.Worksheets.get_Item(i);//获得第i个sheet，准备写入
                }
                catch (Exception ex)//不存在就增加一个sheet
                {
                    sheet = (_Worksheet)book.Worksheets.Add(Missing.Value, book.Worksheets[book.Sheets.Count], 1, Missing.Value);
                }
                sheet.Name = "第" + i.ToString() + "页";//设置当前sheet的Name


                string[] codes = code.Split(';');
                string[] names = name.Split(';');
                for (int row = 1; row <= count; row++)//循环设置每个单元格的值
                {
                    sheet.Cells[row, 1] = codes[row-1];
                    sheet.Cells[row, 2] = names[row-1];
                }
            }
            //将表另存为
            book.SaveAs(filename, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);


            //如果表已经存在，直接用下面的命令保存即可
            //book.Save();


            book.Close(false, Missing.Value, Missing.Value);//关闭打开的表
            xls.Quit();//Excel程序退出
            //sheet,book,xls设置为null，防止内存泄露
            sheet = null;
            book = null;
            xls = null;
            GC.Collect();//系统回收资源
            return true;

        }

//读取

public static Array ReadXls( string filename, int index) //读取第index个sheet的数据

         {

             //启动Excel应用程序

             Microsoft.Office.Interop.Excel.Application xls = new Microsoft.Office.Interop.Excel.Application();

             //打开filename表

             _Workbook book = xls.Workbooks.Open(filename, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);

  

             _Worksheet sheet; //定义sheet变量

             xls.Visible = false ; //设置Excel后台运行

             xls.DisplayAlerts = false ; //设置不显示确认修改提示

  

             try

             {

                 sheet = (_Worksheet)book.Worksheets.get_Item(index); //获得第index个sheet，准备读取

             }

             catch (Exception ex) //不存在就退出

             {

                 Console.WriteLine(ex.Message);

                 return null ;

             }

             Console.WriteLine(sheet.Name);

             int row = sheet.UsedRange.Rows.Count; //获取不为空的行数

             int col = sheet.UsedRange.Columns.Count; //获取不为空的列数

             Array value = (Array)sheet.get_Range(sheet.Cells[1, 1], sheet.Cells[row, col]).Cells.Value2; //获得区域数据赋值给Array数组，方便读取

  

             book.Save(); //保存

             book.Close( false , Missing.Value, Missing.Value); //关闭打开的表

             xls.Quit(); //Excel程序退出

             //sheet,book,xls设置为null，防止内存泄露

             sheet = null ;

             book = null ;

             xls = null ;

             GC.Collect(); //系统回收资源

             return value;

         }