C#导出数据到excel

来源：互联网发布：ubuntu find命令编辑：程序博客网时间：2024/06/06 02:11

格式一：直接导入一个新的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;            }        }

0 1