ASP.NET数据列表导出为Excel

最新推荐文章于 2024-04-03 16:55:09 发布

QQzyb

最新推荐文章于 2024-04-03 16:55:09 发布

阅读量1k

点赞数

分类专栏：闲杂收录

闲杂收录专栏收录该内容

10 篇文章

订阅专栏

本文介绍如何利用C#实现Excel数据的自动化导出，包括Excel对象的创建、数据填充及格式设置，并实现了文件的保存与下载。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

#region Excel定义
                DateTime beforeTime = DateTime.Now;
                //实例化Excel全局(本地服务器)
                Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
                if (xlApp == null)
                {
                    ResponseScript("无法创建Excel对象，可能您的电脑未安装Excel");
                    return;
                }
                DateTime afterTime = DateTime.Now;
                Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.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];//取得sheet1
                #endregion

                //构建数据
                long totalCount = _nRecordCount;//行总数
                int rindex = 0;
                AppCode.Excel.setRangHead(++rindex, "序号", 5, ref worksheet);
                AppCode.Excel.setRangHead(++rindex, "所属事项", 50, ref worksheet);
                AppCode.Excel.setRangHead(++rindex, "类别名称", 25, ref worksheet);
                //写入内容
                List<M.VbsCategoryBook> exportlist = new B.VbsCategoryBook().GetList(name, treeid, departCode, order, 1, _nRecordCount, ref _nRecordCount);
                for (int r = 0; r < exportlist.Count; r++)
                {
                    rindex = 0;
                    M.VbsCategoryBook obj = exportlist[r] as M.VbsCategoryBook;
                    AppCode.Excel.setRangCell(r + 2, ++rindex, (r + 1).ToString(), ref worksheet);
                    AppCode.Excel.setRangCell(r + 2, ++rindex, returnCategory(obj.InfoItemid), ref worksheet);
                    AppCode.Excel.setRangCell(r + 2, ++rindex, obj.Name, ref worksheet);
                }

                #region 保存下载
                object missing = System.Reflection.Missing.Value;//定义一个missing
                string ExcelFileName = this.Page.Title + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx";
                //保存
                workbook.SaveAs(Server.MapPath("http://www.cnblogs.com/Resources/DownFile/" + ExcelFileName), missing, missing, missing, missing, missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing);
                workbook.Close(null, null, null);
                workbooks.Close();
                xlApp.Application.Quit();
                xlApp.Quit();

                //释放进程及资源
                System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
                worksheet = null;
                workbook = null;
                xlApp = null;
                GC.Collect();
                DateTime startTime;
                try
                {
                    //强行释放最近打开的Excel进程
                    System.Diagnostics.Process[] myProcesses = System.Diagnostics.Process.GetProcessesByName("EXCEL");
                    foreach (System.Diagnostics.Process myProcess in myProcesses)
                    {
                        startTime = myProcess.StartTime;
                        if (startTime >= beforeTime && startTime <= afterTime)
                        {
                            myProcess.Kill();
                        }
                    }
                }
                catch
                {

                }
                //将生成的文件提供给客户端
                AppCode.Excel.ShowFileInClient(this.Page, Server.MapPath("http://www.cnblogs.com/Resources/DownFile/" + ExcelFileName));
                #endregion