类对象导出excle

最新推荐文章于 2024-04-12 07:27:58 发布

windcoldice

最新推荐文章于 2024-04-12 07:27:58 发布

阅读量398

点赞数

分类专栏：技术分享文章标签： null application string object list excel

本文链接：https://blog.csdn.net/windcoldice/article/details/6020783

版权

技术分享专栏收录该内容

11 篇文章 0 订阅

订阅专栏

using System;
using System.Collections;
using System.Data;
using System.Data.OleDb;
using System.Collections.Generic;
using System.IO;
using System.Reflection;
using System.Web;
using System.Text;
using System.Data.SqlClient;
using System.Drawing;
using System.Data.OleDb;
using Microsoft.Office.Interop.Excel;
using System.Runtime.InteropServices;

            /// <summary>
            /// 类导出excel 调用微软自带Microsoft.Office.Interop.Excel接口
            /// </summary>
            /// <typeparam name="T">类对象</typeparam>
            /// <param name="list">类集合</param>
            /// <param name="notecolumn">类属性</param>
            /// <param name="titlecolumn">表头</param>
            /// <note>备注：表头与相对应属性必须一一对应，类属性必须完全与类中属性相同</note>

public void OutExcel<T>(List<T> list, string[] notecolumn, string[] titlecolumn)
            {
                //-----------初始化-----------------------
                string filename = "";
                Application app = null;
                Workbook wb = null;
                Worksheet sheet = null;
                //-----------------------------------
                try
                {
                    //---------------------------------------------------数据填充-------------------------------------------------------------
                    app = new Application();
                    app.Visible = false;
                    wb = (Workbook)app.Workbooks.Add(Missing.Value);
                    sheet = (Worksheet)wb.ActiveSheet;
                    for (int i = 0; i < titlecolumn.Length; i++)
                    {
                        sheet.Cells[1, 1 + i] = titlecolumn[i];
                        for (int j = 0; j < list.Count; j++)
                        {
                            if (notecolumn[i] != string.Empty)
                            {
                                sheet.Cells[j + 2, i+1] ="'" +list[j].GetType().GetProperty(notecolumn[i]).GetValue(list[j],null);
                            }
                        }
                    }
                    //-------------------------------------------------------------------------------------------------------------------
                    //--------------------------------------------------保存到服务器-----------------------------------------------------------------------
                    wb.Saved = true;
                    string filepath = HttpContext.Current.Server.MapPath("~") + "/Utility/files/TempExcel";
                    if (!Directory.Exists(filepath))
                    {
                        Directory.CreateDirectory(filepath);
                    }
                    filename = String.Format("{0}{1}.xls",filepath+"//", DateTime.Now.ToString("yyyyMMddHHmmssffff"));
                    app.ActiveWorkbook.SaveCopyAs(filename);
                    //------------------------------------------------------------------------------------------------------------------------------------
                }
                finally
                {
                    wb.Close(null, null, null);
                    app.Workbooks.Close();
                    app.Quit();
                    Marshal.ReleaseComObject((object)app);
                    Marshal.ReleaseComObject((object)wb);
                    Marshal.ReleaseComObject((object)sheet);
                    GC.Collect();
                }
                //-----------------------------------输出excel------------------------------------------------------------------------------------------------
                FileInfo fileInfo = new FileInfo(filename);
                HttpContext.Current.Response.Clear();
                HttpContext.Current.Response.ClearContent();
                HttpContext.Current.Response.ClearHeaders();
                HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename="+filename.Substring(filename.LastIndexOf("//")+1));
                HttpContext.Current.Response.AddHeader("Content-Length", fileInfo.Length.ToString());
                HttpContext.Current.Response.AddHeader("Content-Transfer-Encoding", "binary");
                HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
                HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312");
                HttpContext.Current.Response.WriteFile(fileInfo.FullName);
                HttpContext.Current.Response.Flush();
                HttpContext.Current.Response.End();
                //---------------------------------------------------------------------------------------------------------------------------------------------
            }