复杂的对象导出到Excel的IQueryable [英] Export iQueryable of complex objects to Excel

问题描述

我们有一些code，从数据库到Excel出口数据和它工作得很好。

We have some code that exports data from a database to Excel and it works well.

主要的问题是，我们使用一个数据库，以便我们收集的所有数据。这将出现，因为我们有多种类型，其中我们出口对象的一个​​相当大的观点的问题。

The main problem is we use a DB view to gather all our data. This creates the issue of having a sizable view as we have multiple types of objects of which we export.

class Event
  int id
  string title
  List<EventDate> Dates
  string desc

class EventDate
  DateTime start
  DateTime end
  List<EventLocation> Locations

class EventLocation
  string address
  string city
  string zip

class Birthday : Event
  int balloonsOrdered
  string cakeText

class Meeting : Event
  string Organizer
  string Topic

所以，上面是模型。 生日和会议从事件继承和所有事件对象有 EVENTDATE 对象的列表。每个 EVENTDATE 对象有一个开始日期，结束日期和位置的列表的对象。

So, above is the model. Birthday and Meeting inherit from Event and all Event objects have a list of EventDate objects. Each EventDate object has a start date, end date and a list of Location objects.

我们的目标是找到一个动态的方式从DB数据为Excel文档。我们宁愿不维护数据库中的一个巨大的视图（如我们最终会增加更多的事件类型）。

Our goal is to find a dynamic way to get data from the DB to an Excel doc. We'd rather not maintain a massive view in the database (as we'll add more event types eventually).

我不是那么熟悉.NET的XML功能，但是我们使用的解决方案，现在使用的OpenXML和code是有道理的。

I'm not all that familiar with .NET's XML capabilities, but the solution we are using now uses OpenXML and the code makes sense.

您输入和可能的解决方案是大大pciated AP $ P $

Your input and possible solutions are greatly appreciated

推荐答案

您可以创建使用 CSV 文件列表＆LT; T＆GT; 键，并按照code：

You could create a CSV file using List<T> and and following code:

using System;
using System.Collections.Generic;
using System.IO;
using System.Reflection;
using System.Web;

public static void CreateCSV<T>(List<T> list, string csvNameWithExt)
{
    if (list == null || list.Count == 0) return;

    HttpContext.Current.Response.Clear();
    HttpContext.Current.Response.AddHeader(
        "content-disposition", string.Format("attachment; filename={0}", csvNameWithExt));
    HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";

    //get type from 0th member
    Type t = list[0].GetType();
    string newLine = Environment.NewLine;

    using (StringWriter sw = new StringWriter())
    {
        //gets all properties
        PropertyInfo[] props = t.GetProperties();

        //this is the header row
        //foreach of the properties in class above, write out properties
        foreach (PropertyInfo pi in props)
        {
            sw.Write(pi.Name.ToUpper() + ",");
        }
        sw.Write(newLine);

        //this acts as datarow
        foreach (T item in list)
        {
            //this acts as datacolumn
            foreach (PropertyInfo Column in props)
            {
                //this is the row+col intersection (the value)
                string value = item.GetType().GetProperty(Column.Name).GetValue(item, null).ToString();
                if (value.Contains(","))
                {
                    value = "\"" + value + "\"";
                }
                sw.Write(value + ",");
            }
            sw.Write(newLine);
        }

        //  render the htmlwriter into the response
        HttpContext.Current.Response.Write(sw.ToString());
        HttpContext.Current.Response.End();
    }
}

这篇关于复杂的对象导出到Excel的IQueryable的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！