关闭

Datatable 导出Excel 到指定路径

标签: excelC#
1959人阅读 评论(0) 收藏 举报
分类:
/// <summary>
        /// 将DataTable中的数据导出到Excel(支持Excel2003和Excel2007)
        /// </summary>
        /// <param name="dt"> DataTable</param>
        /// <param name="url">Excel保存的路径DataTable</param>
        /// <returns>导出成功返回True,否则返回false</ returns >
        public bool ExportExcel(SysData.DataTable dt, string url)
        {
            bool falge = false;
            Microsoft.Office.Interop.Excel.Application objExcel = null;
            Workbook objWorkbook = null;
            Worksheet objsheet = null;

            try
            {
                //申明对象
                objExcel = new Microsoft.Office.Interop.Excel.Application();
                objWorkbook = objExcel.Workbooks.Add(Missing.Value);
                objsheet = (Worksheet)objWorkbook.ActiveSheet;

                //设置Excel不可见
                objExcel.Visible = false;
                objExcel.DisplayAlerts = false;

                //设置Excel字段类型全部为字符串
                objsheet.Cells.NumberFormat = "@";

                //向Excel中写入表格的标头
                int displayColumnsCount = 1;
                for (int i = 0; i < dt.Columns.Count; i++) {
                    objExcel.Cells[1, displayColumnsCount] = dt.Columns[i].ColumnName.Trim();
                    displayColumnsCount++;
                }
                //向Excel中逐步逐列写入表格中的数据
                for (int row = 0; row < dt.Rows.Count; row++) {
                    displayColumnsCount = 1;
                    for (int col = 0; col < dt.Columns.Count; col++) {
                        try
                        {
                            objExcel.Cells[row + 2, displayColumnsCount] = dt.Rows[row][col].ToString().Trim();
                            displayColumnsCount++;
                        }
                        catch (Exception) { }
                    }
                }

                //保存文件 SaveAs解释可查看http://msdn.microsoft.com/zh-cn/library/microsoft.office.tools.excel.workbook.saveas(v=vs.80).aspx
                objWorkbook.SaveAs(url, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlShared, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
                falge = true;
            }
            catch (Exception ex){
                falge=false;
                MessageBox.Show(ex.Message,"温馨提示",MessageBoxButtons.OK,MessageBoxIcon.Information);
            }
            finally{
                //关闭Excel应用
                if (objWorkbook != null) objWorkbook.Close(Missing.Value, Missing.Value, Missing.Value);
                if (objExcel.Workbooks != null) objExcel.Workbooks.Close();
                if (objExcel != null) objExcel.Quit();

                //杀死进程
                KillProcess("Excel");
                objsheet = null;
                objWorkbook = null;
                objExcel = null;
            }
            return falge;            
        }


        public void KillProcess(string ProcessName) {
            System.Diagnostics.Process myproc = new System.Diagnostics.Process();
            try
            {
                foreach (System.Diagnostics.Process thisproc in System.Diagnostics.Process.GetProcessesByName(ProcessName))
                {
                    if (!thisproc.CloseMainWindow())
                    {
                        thisproc.Kill();
                    }
                }
            }
            catch (Exception ex) {
                throw new Exception("", ex);
            }
        }

0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:94094次
    • 积分:1456
    • 等级:
    • 排名:千里之外
    • 原创:51篇
    • 转载:29篇
    • 译文:0篇
    • 评论:7条
    最新评论