jqgrid + EF + MVC:如何在excel中导出?你认为哪一种方法呢?

I am using jqgrid (standard) with EF 4 + MVC3. I'd like to implement excel export. Which method you would suggest me?

我正在使用jqgrid(标准)和EF 4 + MVC3。我想实现excel导出。你建议我用什么方法?

To generate excel, I'd like to use this library by Dr Stephen Walther, which has three types of output and allows to define headers too. Please tell me if you find it valid for my purpose.

要生成excel，我想使用Stephen Walther博士的这个库，它有三种类型的输出，并且允许定义header。如果你认为它对我的目的有效，请告诉我。

I ask this question because I am still approaching to implement excel export and I found several techniques. Some suggest making a csv export, others indicate that it should return a JSON output and it is not clear to me whether this capability is present in the free version of jqgrid. In any case, I would like to pass the data to Walther's object.

我问这个问题是因为我还在尝试实现excel导出，我发现了一些技术。有些人建议导出csv，有些人则建议返回JSON输出，我不清楚jqgrid的免费版本中是否包含此功能。无论如何，我希望将数据传递给Walther的对象。

About the jqgrid code, I found this interesting answer by Oleg, but I do not understand if could be applied to my needs.

关于jqgrid代码，我在Oleg中找到了这个有趣的答案，但是我不明白是否可以应用于我的需要。

Unfortunately, by now I only found parts of solutions for excel export with EF MVC, but no solution or complete examples...

不幸的是，到目前为止，我只找到了部分使用EF MVC的excel导出解决方案，但没有解决方案或完整的示例……

About the MVC logic, I am going to implement and develop this code as kindly suggested by @Tommy.

关于MVC逻辑，我将按照@Tommy的建议来实现和开发这段代码。

Please sorry if the question could be silly, I am just a (enthusiast) beginner.

如果这个问题很愚蠢，请见谅，我只是一个初学者。

Thanks for your precious help! Best Regards

谢谢你的宝贵帮助!致以最亲切的问候

2 个解决方案

#1

As I wrote before (see here and here for example) the best way to export grid data to XML is the usage of Open XML SDK 2.0.

正如我之前所写的(参见这里和这里)，将网格数据导出为XML的最佳方式是使用Open XML SDK 2.0。

The post of Dr Stephen Walther shows how to create HTML file which can be read by Excel. It's not Excel file and have to be still converted to Excel format. The usage of CSV has even more problems. Depend on the content in the source table the automatic conversion to Excel data types can be absolutely wrong. In one project which I developed for a customer the grid contained information about software products: product name, version, and so on. The software version looks sometime as the date (1.3.1963 for example) and such cells will be wrong converted (in German one use '.' as the separator in the date). As the result one had really hard problems. The usage of CSV with texts having commas inside will be also frequently wrong imported. Even when one quotes the cells having commas (,) and escaped the texts having quotas the import still be wrong especially in the first column. I don't want to explain here the whole history of all attempts and errors, but after all I decide to give up with the usage of CSV and HTML and started to use Open XML SDK 2.0 which allows to create real Excel files with extension XLSX. The way seems me perfect because one don't need any Office components installed on the server, no additional licenses.

Stephen Walther博士的文章展示了如何创建可以被Excel读取的HTML文件。它不是Excel文件，仍然需要转换成Excel格式。CSV的使用还有更多的问题。根据源表中的内容，自动转换到Excel数据类型可能是完全错误的。在我为客户开发的一个项目中，网格包含有关软件产品的信息:产品名称、版本等。软件版本有时看起来像日期(例如1.3.1963)，这样的单元格将被错误地转换(在德语中是“one use”)。作为日期的分隔符)。结果，一个人遇到了非常困难的问题。使用带有逗号的文本的CSV也会经常被导入错误。即使引用具有逗号(，)的单元格并转义具有配额的文本，导入仍然是错误的，特别是在第一列中。我不想在这里解释所有尝试和错误的全部历史，但毕竟我决定放弃使用CSV和HTML，并开始使用Open XML SDK 2.0，它允许使用扩展XLSX创建真正的Excel文件。这种方法看起来很完美，因为它不需要在服务器上安装任何办公组件，也不需要附加许可证。

The only restriction is that one should be able to use DocumentFormat.OpenXml.dll, so your server program should run on any Windows operation system. As it's well known, XLSX file is ZIP file which contains some XML files inside. If you still don't know that I recommend you to rename the XLSX file to ZIP file and extract it. The Open XML SDK 2.0 is the library which works with XLSX file like with XML files. So no additional Office components are required.

唯一的限制是应该能够使用DocumentFormat.OpenXml。dll，所以您的服务器程序应该在任何Windows操作系统上运行。众所周知，XLSX文件是ZIP文件，其中包含一些XML文件。如果您还不知道，我建议您将XLSX文件重命名为ZIP文件并提取它。Open XML SDK 2.0是与XLSX文件一起工作的库，就像与XML文件一起工作一样。所以不需要额外的办公组件。

One can find a lot of information how to use Open XML SDK 2.0 (see here, here and here). Many helpful code examples one cam find directly on the MSDN (see here). Nevertheless the practical usage of Open XML SDK 2.0 is not so easy at least at the first time. So I created a demo from the parts of the code which I used myself.

您可以找到许多关于如何使用Open XML SDK 2.0的信息(请参见这里、这里和这里)。一个cam直接在MSDN上找到的许多有用的代码示例(参见这里)。然而，开放XML SDK 2.0的实际使用并不容易，至少在第一次使用时是如此。所以我用我自己的代码部分创建了一个演示。

You can download the demo project from here. The demo is an extension of the demos from the answer and this one.

您可以从这里下载演示项目。这个演示是这个答案和这个答案的演示的扩展。

To export data I use the DataForExcel helper class. It has constructor in the form

要导出数据，我使用dataforexcelhelper类。它在表单中有构造函数

DataForExcel(string[] headers, DataType[] colunmTypes, List<string[]> data,
             string sheetName)

or in a little simplified form

或者用简化的形式。

DataForExcel(string[] headers, List<string[]> data, string sheetName)

and the only public method

这是唯一的公共方法

CreateXlsxAndFillData(Stream stream)

The usage of the class to create Excel file can be like the following

类创建Excel文件的用法如下所示

var excelData = new DataForExcel (
    // column Header
    new[]{"Col1", "Col2", "Col3"},
    new[]{DataForExcel.DataType.String, DataForExcel.DataType.Integer,
          DataForExcel.DataType.String},
    new List<string[]> {
        new[] {"a", "1", "c1"},
        new[] {"a", "2", "c2"}
    },
    "Test Grid");
Stream stream = new FileStream ("Test.xlsx", FileMode.Create);
excelData.CreateXlsxAndFillData (stream);
stream.Close();

The usage in the demo from ASP.NET MVC is the following

在演示中使用的ASP。netmvc如下

static readonly string[] HeadersQuestions = {
    "Id", "Votes", "Title"
};
static readonly DataForExcel.DataType[] ColunmTypesQuestions = {
    DataForExcel.DataType.Integer,
    DataForExcel.DataType.Integer,
    DataForExcel.DataType.String
};

public ActionResult ExportAllQuestionsToExcel () {
    var context = new HaackOverflowEntities ();
    var questions = context.Questions;
    questions.MergeOption = MergeOption.NoTracking; // we don't want to update the data

    // to be able to use ToString() below which is NOT exist in the LINQ to Entity
    // we should include in query only the properies which we will use below
    var query = questions.ToList ();
    if (query.Count == 0)
        return new EmptyResult ();
    var data = new List<string[]> (query.Count);
    data.AddRange (query.Select (item => new[] {
        item.Id.ToString(CultureInfo.InvariantCulture),
        item.Votes.ToString(CultureInfo.InvariantCulture),
        item.Title
    }));

    return new ExcelResult (HeadersQuestions, ColunmTypesQuestions, data,
                            "Questions.xlsx", "Questions");
}

where ExcelResult are defined as

ExcelResult在哪里定义为

public class ExcelResult : ActionResult {
    private readonly DataForExcel _data;
    private readonly string _fileName;

    public ExcelResult (string[] headers, List<string[]> data, string fileName, string sheetName) {
        _data = new DataForExcel (headers, data, sheetName);
        _fileName = fileName;
    }

    public ExcelResult (string[] headers, DataForExcel.DataType[] colunmTypes, List<string[]> data, string fileName, string sheetName) {
        _data = new DataForExcel (headers, colunmTypes, data, sheetName);
        _fileName = fileName;
    }

    public override void ExecuteResult (ControllerContext context) {
        var response = context.HttpContext.Response;
        response.ClearContent();
        response.ClearHeaders();
        response.Cache.SetMaxAge (new TimeSpan (0));

        using (var stream = new MemoryStream()) {
            _data.CreateXlsxAndFillData (stream);

            //Return it to the client - strFile has been updated, so return it. 
            response.AddHeader ("content-disposition", "attachment; filename=" + _fileName);

            // see http://filext.com/faq/office_mime_types.php
            response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            response.ContentEncoding = Encoding.UTF8;
            stream.WriteTo (response.OutputStream);
        }
        response.Flush();
        response.Close();
    }
}

To make the code full I have to include the code of the class DataForExcel:

要使代码完整，我必须包含DataForExcel类的代码:

public class DataForExcel {
    public enum DataType {
        String,
        Integer
    }
    private readonly string[] _headers;
    private readonly DataType[] _colunmTypes;
    private readonly List<string[]> _data;
    private readonly string _sheetName = "Grid1";
    private readonly SortedSet<string> _os = new SortedSet<string> ();
    private string[] _sharedStrings;

    private static string ConvertIntToColumnHeader(int index) {
        var sb = new StringBuilder ();
        while (index > 0) {
            if (index <= 'Z' - 'A') // index=0 -> 'A', 25 -> 'Z'
                break;
            sb.Append (ConvertIntToColumnHeader (index / ('Z' - 'A' + 1) - 1));
            index = index % ('Z' - 'A' + 1);
        }
        sb.Append ((char)('A' + index));
        return sb.ToString ();
    }

    private static Row CreateRow(UInt32 index, IList<string> data) {
        var r = new Row { RowIndex = index };
        for (var i = 0; i < data.Count; i++)
            r.Append (new OpenXmlElement[] { CreateTextCell (ConvertIntToColumnHeader (i), index, data[i]) });

        return r;
    }

    private Row CreateRowWithSharedStrings(UInt32 index, IList<string> data) {
        var r = new Row { RowIndex = index };
        for (var i = 0; i < data.Count; i++)
            r.Append (new OpenXmlElement[] { CreateSharedTextCell (ConvertIntToColumnHeader (i), index, data[i]) });

        return r;
    }

    private Row CreateRowWithSharedStrings(UInt32 index, IList<string> data, IList<DataType> colunmTypes) {
        var r = new Row { RowIndex = index };
        for (var i = 0; i < data.Count; i++)
            if (colunmTypes != null && i < colunmTypes.Count && colunmTypes[i] == DataType.Integer)
                r.Append (new OpenXmlElement[] { CreateNumberCell (ConvertIntToColumnHeader (i), index, data[i]) });
            else
                r.Append (new OpenXmlElement[] { CreateSharedTextCell (ConvertIntToColumnHeader (i), index, data[i]) });

        return r;
    }

    private static Cell CreateTextCell(string header, UInt32 index, string text) {
        // create Cell with InlineString as a child, which has Text as a child
        return new Cell (new InlineString (new Text { Text = text })) {
            // Cell properties
            DataType = CellValues.InlineString,
            CellReference = header + index
        };
    }

    private Cell CreateSharedTextCell(string header, UInt32 index, string text) {
        for (var i=0; i<_sharedStrings.Length; i++) {
            if (String.Compare (_sharedStrings[i], text, StringComparison.Ordinal) == 0) {
                return new Cell (new CellValue { Text = i.ToString (CultureInfo.InvariantCulture) }) {
                    // Cell properties
                    DataType = CellValues.SharedString,
                    CellReference = header + index
                };
            }
        }
        // create Cell with InlineString as a child, which has Text as a child
        throw new InstanceNotFoundException();
    }

    private static Cell CreateNumberCell(string header, UInt32 index, string numberAsString) {
        // create Cell with CellValue as a child, which has Text as a child
        return new Cell (new CellValue { Text = numberAsString }) {
            // Cell properties
            CellReference = header + index
        };
    }

    private void FillSharedStringTable(IEnumerable<string> data) {
        foreach (var item in data)
            _os.Add (item);
    }

    private void FillSharedStringTable(IList<string> data, IList<DataType> colunmTypes) {
        for (var i = 0; i < data.Count; i++)
            if (colunmTypes == null || i >= colunmTypes.Count || colunmTypes[i] == DataType.String)
                _os.Add (data[i]);
    }

    public DataForExcel(string[] headers, List<string[]> data, string sheetName) {
        _headers = headers;
        _data = data;
        _sheetName = sheetName;
    }

    public DataForExcel(string[] headers, DataType[] colunmTypes, List<string[]> data, string sheetName) {
        _headers = headers;
        _colunmTypes = colunmTypes;
        _data = data;
        _sheetName = sheetName;
    }

    private void FillSpreadsheetDocument(SpreadsheetDocument spreadsheetDocument) {
        // create and fill SheetData
        var sheetData = new SheetData ();

        // first row is the header
        sheetData.AppendChild (CreateRow (1, _headers));

        //const UInt32 iAutoFilter = 2;
        // skip next row (number 2) for the AutoFilter
        //var i = iAutoFilter + 1;
        UInt32 i = 2;

        // first of all collect all different strings in OrderedSet<string> _os
        foreach (var dataRow in _data)
            if (_colunmTypes != null)
                FillSharedStringTable (dataRow, _colunmTypes);
            else
                FillSharedStringTable (dataRow);
        _sharedStrings = _os.ToArray ();

        foreach (var dataRow in _data)
            sheetData.AppendChild (_colunmTypes != null
                                      ? CreateRowWithSharedStrings (i++, dataRow, _colunmTypes)
                                      : CreateRowWithSharedStrings (i++, dataRow));

        var sst = new SharedStringTable ();
        foreach (var text in _os)
            sst.AppendChild (new SharedStringItem (new Text (text)));

        // add empty workbook and worksheet to the SpreadsheetDocument
        var workbookPart = spreadsheetDocument.AddWorkbookPart ();
        var worksheetPart = workbookPart.AddNewPart<WorksheetPart> ();

        var shareStringPart = workbookPart.AddNewPart<SharedStringTablePart> ();
        shareStringPart.SharedStringTable = sst;

        shareStringPart.SharedStringTable.Save ();

        // add sheet data to Worksheet
        worksheetPart.Worksheet = new Worksheet (sheetData);
        worksheetPart.Worksheet.Save ();

        // fill workbook with the Worksheet
        spreadsheetDocument.WorkbookPart.Workbook = new Workbook (
                new FileVersion { ApplicationName = "Microsoft Office Excel" },
                new Sheets (
                    new Sheet {
                        Name = _sheetName,
                        SheetId = (UInt32Value)1U,

                        // generate the id for sheet
                        Id = workbookPart.GetIdOfPart (worksheetPart)
                    }
                )
            );
        spreadsheetDocument.WorkbookPart.Workbook.Save ();
        spreadsheetDocument.Close ();
    }

    public void CreateXlsxAndFillData(Stream stream) {
        // Create workbook document
        using (var spreadsheetDocument = SpreadsheetDocument.Create (stream, SpreadsheetDocumentType.Workbook)) {
            FillSpreadsheetDocument (spreadsheetDocument);
        }
    }
}

The above code create new XLSX file directly. You can extend the code to support more data types as String and Integer which I used in the code.

上面的代码直接创建新的XLSX文件。您可以扩展代码以支持更多的数据类型，如我在代码中使用的字符串和整数。

In more professional version of your application you can create some XLSX templates for exporting different tables. In the code you can place the data in the cells instead, so modify the spreadsheet instead of creating. In the way you can create perfect formatted XLSX files. The examples from the MSDN (see here) will help you to implement the way when it will be required.

在更专业的应用程序版本中，您可以为导出不同表创建一些XLSX模板。在代码中，您可以将数据放在单元格中，因此修改电子表格而不是创建。用这种方式，您可以创建完美的格式化的XLSX文件。MSDN上的示例将帮助您实现所需的方法。

UPDATED: The answer contains updated code which allows generate Excel documented with more cell formatting.

更新:答案包含更新的代码，允许生成包含更多单元格格式文档的Excel。

#2

I looked at Stephen's post and it's old as hell, which btw doesn't make it wrong. If you don't need custom formatting, headers and styles, then I think use CSV as it's very simple.
More importantly, don't think that excel export from MVC site that internally uses EF for data access is harder than, say, Ruby on Rails site that uses ActiveRecord. For me it's independent concerns, export shouldn't new anything about underlying technologies (at least not directly), just the structure of your data, that's all.
Search for codeplex libraries that allows to do Excel reading/writing and export, there are plenty of them these days, many really good solutions that's regularly maintained and tested by thousand of developers all over the globe. If I were you I won't use Stephen solution because it looks like he occasionally typed it in a notepad and then pasted to the post - no unit tests, no extensibility points + it's in VB so it even harder to understand, but may be that's just me. Hope this help and good luck

我看了看斯蒂芬的帖子，发现它老得像地狱一样，顺便说一句，这并没有错。如果您不需要自定义格式、标题和样式，那么我认为应该使用CSV，因为它非常简单。更重要的是，不要认为从内部使用EF进行数据访问的MVC站点的excel导出比使用ActiveRecord的Ruby on Rails站点更加困难。对我来说，这是独立的问题，导出不应该有任何关于底层技术的新东西(至少不是直接的)，只是数据的结构，仅此而已。搜索codeplex库，可以进行Excel阅读/写作和导出，现在有很多这样的库，很多很好的解决方案都是由全球上千个开发人员定期维护和测试的。如果我是你，我不会使用Stephen解决方案，因为看起来他偶尔会在记事本中输入，然后粘贴到post -没有单元测试，没有可扩展性点+它在VB中，所以更难理解，但可能就是我。希望这些帮助和好运。