C#导出数据到excel

格式一：直接导入一个新的excel

 /// <summary>
        /// 直接导出到指定excel
        /// </summary>
        /// <param name="topath"></param>
        /// <param name="dt"></param>
        private void SaveAsExcel(string topath, DataTable dt)
        {
            object objMissing = System.Reflection.Missing.Value;//缺省默认值

            Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
            app.Visible = false;
            Microsoft.Office.Interop.Excel.Workbook workBook = null;
            Microsoft.Office.Interop.Excel.Worksheet workSheet = null;
            
            Microsoft.Office.Interop.Excel.Range ranges = null;
            Microsoft.Office.Interop.Excel.Range range = null;
            Microsoft.Office.Interop.Excel.Range head = null;

            /*创建新excel对象*/
            workBook = app.Workbooks.Add(objMissing);
            workSheet = (Microsoft.Office.Interop.Excel.Worksheet)workBook.Worksheets[1];
            /*循环设置第一行名称*/
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                head = (Microsoft.Office.Interop.Excel.Range)workSheet.Cells[1, i + 1];
                head.Value2 = dt.Columns[i].ColumnName;
                head = null;
            }

            try
            {
                //A2用于指定起始行的位置
                range = workSheet.get_Range("A2", objMissing);
                int rows = dt.Rows.Count, cols = dt.Columns.Count;
                ranges = range.get_Resize(rows, cols);
                object[,] datas = new object[rows, cols];

                for (int i = 0; i < rows; i++)
                {
                    for (int j = 0; j < cols; j++)
                    {
                        datas[i, j] = dt.Rows[i][j].ToString();
                    }
                }
                ranges.Value2 = datas;
                workBook.SaveAs(topath, objMissing, objMissing, objMissing, objMissing, objMissing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, objMissing,
                                objMissing, objMissing, objMissing, objMissing);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                workBook.Close(false, objMissing, objMissing);
                app.Quit();
                range = null;
                ranges = null;
                workBook = null;
                workSheet = null;
                app = null;
            }
        }

格式二：根据特定模板，填充数据之后，再导出Excel

/// <summary>
        /// 根据特定模板，填充数据后，导出到excel
        /// </summary>
        /// <param name="frompath"></param>
        /// <param name="topath"></param>
        /// <param name="dt"></param>
        private void SaveAsExcel(string frompath, string ExcelName, DataTable dt)
        {
            object objMissing = System.Reflection.Missing.Value;//缺省值，相当于为空

            Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
            app.Visible = false;
            Microsoft.Office.Interop.Excel.Workbook workBook = null;
            Microsoft.Office.Interop.Excel.Worksheet workSheet = null;
            //以下3个对象在前面定义，为了能在异常中可以释放
            Microsoft.Office.Interop.Excel.Range ranges = null;
            Microsoft.Office.Interop.Excel.Range range = null;
            Microsoft.Office.Interop.Excel.Range head = null;
            /*读取现有excel*/
            workBook = app.Workbooks.Add(frompath);
            workSheet = (Microsoft.Office.Interop.Excel.Worksheet)workBook.Worksheets[1];

            head = (Microsoft.Office.Interop.Excel.Range)workSheet.Cells[1,1];
            head.Value2 = ExcelName;//文件及标题名称
            head = null;

            try
            {
                //A4用于指定起始行的位置(根据自己的需要设置)
                range = workSheet.get_Range("A4", objMissing);
                int rows = dt.Rows.Count, cols = dt.Columns.Count;
                ranges = range.get_Resize(rows, cols);
                object[,] datas = new object[rows, cols];

                for (int i = 0; i < rows; i++)
                {
                    for (int j = 2; j < cols - 3; j++)
                    {
                        datas[i, j] = dt.Rows[i][j].ToString();
                    }
                }
                ranges.Value2 = datas;
                string path = @"C:\Documents and Settings\Administrator\桌面\" + ExcelName + ".xls";
                workBook.SaveAs(path, objMissing, objMissing, objMissing, objMissing, objMissing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, objMissing,
                                objMissing, objMissing, objMissing, objMissing);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                workBook.Close(false, objMissing, objMissing);
                app.Quit();
                range = null;
                ranges = null;
                workBook = null;
                workSheet = null;
                app = null;
            }
        }