DataTable写入Excel中用Excel标准格式

最新推荐文章于 2025-09-12 16:32:54 发布

转载最新推荐文章于 2025-09-12 16:32:54 发布 · 103 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/kingkongv/archive/2012/09/03/2668573.html

文章标签：

#runtime #数据库

本文介绍了一种将DataTable数据导出到Excel的方法，并提供了详细的代码实现。该方法支持将数据以Excel标准格式导出，便于再次导入到DataTable或数据库中。

        /// <summary>
        /// DataTable数据导入到Excel中，这里用Excel标准格式，导出的Excel文件可以再导入到DataTable或者数据库中 
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="strPath"></param>
        /// <returns></returns>
        private bool TableIntoExcel(DataTable dt, string strPath)
        {
            strPath = Request.PhysicalApplicationPath;
            bool IsSuccess = false;
            Excel.Application xlApp = null;
            Excel.Workbooks workbooks = null;
            Excel.Workbook workbook = null;
            Excel.Worksheet worksheet = null;
            try
            {
                xlApp = new Excel.Application();
                if (xlApp == null)
                {
                    ScriptManager.RegisterStartupScript(this, GetType(), "msg", "alert('创建Excel失败')", true);
                    return IsSuccess;
                }
                workbooks = xlApp.Workbooks;
                workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
                worksheet = (Excel.Worksheet)workbook.Worksheets[1];//取得sheet1 
                //写入字段 
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    worksheet.Cells[1, i + 1] = dt.Columns[i].ColumnName;
                }
                //写入数值 
                try
                {
                    for (int r = 0; r < dt.Rows.Count; r++)
                    {
                        for (int i = 0; i < dt.Columns.Count; i++)
                        {
                            worksheet.Cells[r + 2, i + 1] = dt.Rows[r][i];
                        }

                        // System.Windows.Forms.Application.DoEvents();
                    }
                    worksheet.Columns.EntireColumn.AutoFit();//列宽自适应。
                }
                catch (Exception ee)
                {
                    ScriptManager.RegisterStartupScript(this, GetType(), "msg", "alert('写入Excel数据时出错！')", true);
                    return IsSuccess;

                }

                strPath = strPath + "导出的Excel.xls";
                workbook.Saved = true;
                workbook.SaveCopyAs(strPath);
                IsSuccess = true;
                workbook.Close(false, null, null);
                xlApp.Quit();
            }
            finally
            {
                //释放对象 
                System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(workbooks);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
                workbooks = null;
                worksheet = null;
                xlApp = null;
                GC.Collect();//强行销毁
            }
            return IsSuccess;
        }