DataTable 导出到 Excel 类

底层类：

#region DataTable 导出到 Excel
        /// <summary>
        /// DataTable 导出到 Excel
        /// </summary>
        /// <param name="dt">数据表</param>
        /// <param name="captions">要导出的列标题</param>
        /// <param name="fieldNames">要导出的列名</param>
        /// <param name="sheetName">工作簿名称</param>
        /// <param name="isOpen">导出完成是否打开Excel,如果=false会杀死当前Excel进程</param>        
        /// <returns>错误信息，如果为空，说明用户取消导入</returns>
        public static string DtToExcel(DataTable dt, string[] captions, string[] fieldNames, string sheetName, bool isOpen)
        {
            //返回的信息
            string str = "";
            //保存对话框
            SaveFileDialog sfd = new SaveFileDialog();
            sfd.Filter = "(*.xls,*.xlsx)|*.xls;*.xlsx";
            //保存的文件名称
            string fileName = "";
            if (sfd.ShowDialog() != DialogResult.OK)
            {
                return "";
            }
            if (dt == null)
            {
                str = "表不能为空";
                return str;
            }
            if (captions.Length != fieldNames.Length)
            {
                str = "列名数目和列标题数目不匹配";
                return str;
            }
            foreach (string aa in fieldNames)
            {
                if (!dt.Columns.Contains(aa))
                {
                    str = "表中不存在列名为 " + aa + " 的列";
                    return str;
                }
            }
            //如果不是全部列都要导出，改变一下样式
            DataView dv = new DataView(dt);
            dt = dv.ToTable(false, fieldNames);

            //导出路径
            fileName = sfd.FileName;
            //定义Excel程序
            Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
            //判断是否安装Excel
            if (excel == null)
            {
                str = "无法创建Excel对象,可能您的计算机未安装Excel!";
                return str;
            }
            //是否显示导出过程 ，建议关闭，否则在导出过程中鼠标点击Excel文件时会出错
            excel.Visible = false;
            //定义一个Excel文件
            Microsoft.Office.Interop.Excel.Workbook wBook = excel.Workbooks.Add(System.Reflection.Missing.Value);
            //定义工作表
            Microsoft.Office.Interop.Excel.Worksheet wSheet = (Microsoft.Office.Interop.Excel.Worksheet)wBook.Worksheets[1];
            Microsoft.Office.Interop.Excel.Range range;
            //获取行数、列数
            int rowCount = dt.Rows.Count;
            int colCount = captions.Length;

            //设置表格名称
            if (sheetName != null && sheetName.Length > 0)
            {
                wSheet.Name = sheetName;
            }
            else
            {
                wSheet.Name = dt.TableName;
            }

            if (rowCount < 1)
            {
                str = "没有记录可以导出";
                return str;
            }
            try
            {
                //写标题,标题居中
                for (int j = 1; j < captions.Length + 1; j++)
                {
                    excel.Cells[1, j] = captions[j - 1];                   
                }

                //设置对齐方式
                wSheet.Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
                //设置第一行高度，即标题栏
                ((Microsoft.Office.Interop.Excel.Range)wSheet.Rows["1:1", System.Type.Missing]).RowHeight = 20;
                //设置数据行行高度
                ((Microsoft.Office.Interop.Excel.Range)wSheet.Rows["2:" + dt.Rows.Count + 1, System.Type.Missing]).RowHeight = 16;
                //设置字体大小（10号字体）
                wSheet.Range[wSheet.Cells[1, 1], wSheet.Cells[dt.Rows.Count + 1, dt.Columns.Count]].Font.Size = 10;
                //设置单元格边框
                Microsoft.Office.Interop.Excel.Range range1 = wSheet.Range[wSheet.Cells[1, 1], wSheet.Cells[dt.Rows.Count + 1, dt.Columns.Count]];

                //往Excel写数据
                for (int m = 0; m < rowCount; m++)
                {
                    for (int n = 0; n < colCount; n++)
                    {
                        excel.Cells[m + 2, n + 1] = (object)dt.Rows[m][n] is DBNull ? "" : dt.Rows[m][n].ToString();
                    }
                }
                //保存工作表
                wSheet.SaveAs(fileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                wBook.Close(Type.Missing, Type.Missing, Type.Missing);

                ////设置EXLCE最大化
                //excel.WindowState = XlWindowState.xlMaximized;

                //提示信息
                str = "导出成功！ 共导出 " + rowCount + " 条数据";
                return str;
            }
            catch (Exception ex)
            {
                str = "导出异常，请重试";
                return str;
            }
            finally
            {
                if (isOpen)
                {
                    //前台显示给用户
                    excel.Visible = true;
                    wSheet = null;
                    excel = null;
                }
                else
                {
                    excel.Visible = false;
                    excel.Quit();
                    // excel = null;
                    //杀死Excel 进程
                    KillProcess(excel);
                }

                GC.Collect();
            }

        }
        #endregion

调用（举例）：

 string str=Functionjsj.DtToExcel(dt, new string[] { "学号", "姓名", "年龄" },
               new string[] { "Stu_Number", "Stu_Name", "Stu_Age" }, "学生信息",false);
           if (str.Length > 0) {
               MessageBox.Show(str, System.Windows.Forms.Application.ProductName);
               return;
           }

 

版权声明：本文为博主原创文章，未经博主允许不得转载。