[C#.NET]将DataTable中的数据导出到Excel中

　　首先添加对Microsoft.Office.Interop.Excel的引用：

using Microsoft.Office.Interop.Excel;

 

        /// <summary>
        /// 将DataTable的数据导出到Excel中。
        /// </summary>
        /// <param name="dt">DataTable</param>
        /// <param name="xlsFileDir">导出的Excel文件存放目录（绝对路径，最后带“\”）</param>
        /// <param name="nameList">DataTable中列名的中文对应表</param>
        /// <param name="strTitle">Excel表的标题</param>
        /// <returns>Excel文件名</returns>
        public static string ExportDataToExcel(System.Data.DataTable dt, string xlsFileDir, Hashtable nameList, string strTitle)
        {
            if (dt == null) return "";

            Microsoft.Office.Interop.Excel.ApplicationClass excel = new Microsoft.Office.Interop.Excel.ApplicationClass();
            Microsoft.Office.Interop.Excel.Workbooks workBooks = excel.Workbooks;
            Microsoft.Office.Interop.Excel.Workbook workBook = workBooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
            Microsoft.Office.Interop.Excel.Worksheet workSheet = (Microsoft.Office.Interop.Excel.Worksheet)workBook.Worksheets[1];

            int titleRowsCount = 0;
            if (strTitle != null && strTitle.Trim() != "")
            {
                titleRowsCount = 1;
                excel.get_Range(excel.Cells[1, 1], excel.Cells[1, dt.Columns.Count]).Font.Bold = true;
                excel.get_Range(excel.Cells[1, 1], excel.Cells[1, dt.Columns.Count]).Font.Size = 16;
                excel.get_Range(excel.Cells[1, 1], excel.Cells[1, dt.Columns.Count]).MergeCells = true;
                workSheet.Cells[1, 1] = strTitle;
            }
            if (!System.IO.Directory.Exists(xlsFileDir))
            {
                System.IO.Directory.CreateDirectory(xlsFileDir);
            }
            string strFileName = DateTime.Now.ToString("yyyyMMddHHmmssff") + ".xls";

            string tempColumnName = "";

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    if (i == 0)
                    {
                        tempColumnName = dt.Columns[j].ColumnName.Trim();
                        if (nameList != null)
                        {
                            IDictionaryEnumerator Enum = nameList.GetEnumerator();
                            while (Enum.MoveNext())
                            {
                                if (Enum.Key.ToString().Trim() == tempColumnName)
                                {
                                    tempColumnName = Enum.Value.ToString();
                                }
                            }
                        }
                        workSheet.Cells[titleRowsCount + 1, j + 1] = tempColumnName;
                    }
                    workSheet.Cells[i + titleRowsCount + 2, j + 1] = dt.Rows[i][j].ToString();
                }
            }
            excel.get_Range(excel.Cells[titleRowsCount + 1, 1], excel.Cells[titleRowsCount + 1, dt.Columns.Count]).Font.Bold = true;
            excel.get_Range(excel.Cells[1, 1], excel.Cells[titleRowsCount + 1 + dt.Rows.Count, dt.Columns.Count]).HorizontalAlignment = XlVAlign.xlVAlignCenter;
            excel.get_Range(excel.Cells[1, 1], excel.Cells[titleRowsCount + 1 + dt.Rows.Count, dt.Columns.Count]).EntireColumn.AutoFit();

            workBook.Saved = true;
            workBook.SaveCopyAs(xlsFileDir + strFileName);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(workSheet);
            workSheet = null;
            System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook);
            workBook = null;
            workBooks.Close();
            System.Runtime.InteropServices.Marshal.ReleaseComObject(workBooks);
            workBooks = null;
            excel.Quit();
            System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
            excel = null;
            return strFileName;
        }

转载于:https://www.cnblogs.com/hechaoyang/archive/2011/01/17/1937668.html