jqgrid + EF + MVC：如何在excel中导出？你建议哪种方法？ [英] jqgrid + EF + MVC: How to export in excel? Which method you suggest?

问题描述

我正在使用具有EF 4 + MVC3 的 jqgrid（标准）。我想实现excel导出。你会建议我的哪种方法？要生成excel，我希望使用这个库由Stephen Walther博士，它有三种类型的输出，并允许定义标题。请告诉我，如果你觉得我的目的是有效的。

我提出这个问题，因为我仍然要实现excel导出，我发现了几种技术。有些建议使用csv导出，其他人则表示应该返回一个JSON输出，而且这个功能是否存在于jqgrid的免费版本中是不清楚的。无论如何，我想将数据传递给Walther的对象。

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

不幸的是，现在我只找到了使用EF MVC进行excel导出的解决方案，但没有解决方案或完整的例子...

关于MVC逻辑，我将要实现和开发此代码，由@Tommy推荐。

如果问题可能是愚蠢的话，请谅解，我只是一个（爱好者）的初学者。

感谢您的宝贵帮助！ b $ b 最好的问候

解决方案

正如我之前写的（见这里和此处 r示例）将网格数据导出到XML的最佳方式是使用打开XML SDK 2.0 。

post 显示如何创建可以通过Excel读取的 HTML文件。它不是Excel文件，必须仍然转换为Excel格式。 CSV的用法还有更多的问题。依赖于源表中的内容，自动转换为Excel数据类型可能是绝对错误的。在我为客户开发的一个项目中，网格包含有关软件产品的信息：产品名称，版本等。该软件版本看起来像日期（例如，1.3.1963），并且这样的单元格将被错误转换（在德语中使用'。'作为日期中的分隔符）。结果是有一个很难的问题。使用带有逗号的文本的CSV也会导入错误。即使当引号的单元格有逗号（，）并转义具有配额的文本时，导入仍然是错误的，特别是在第一列中。我不想在这里解释所有尝试和错误的整个历史，但毕竟我决定放弃使用CSV和HTML，并开始使用Open XML SDK 2.0，它允许创建真正的Excel文件扩展名为XLSX。方式似乎我完美，因为一个人不需要任何Office
组件安装在服务器上，没有额外的许可证。

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

可以找到很多信息，如何使用Open XML SDK 2.0（请参阅这里， here 和此处）。一个cam可以直接在MSDN上找到许多有用的代码示例（请参阅 here ）。然而，Open XML SDK 2.0的实际用法至少在第一时间并不容易。所以我从我自己使用的代码部分创建了一个演示。

您可以从这里 。演示版是答案和这个。

要导出数据，我使用 DataForExcel helper类。它的形式为

  DataForExcel（string [] headers，DataType [] colunmTypes，List< string []> ，
 string sheetName）
  

或一点简化形式

  DataForExcel（string [] headers，List< string []> data，string sheetName）
  

和唯一的公共方法

  CreateXlsxAndFillData（Stream stream）
  

创建Excel文件的类的用法可以像以下

  var excelData = new DataForExcel（
 //列标题
 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} 
}，
测试网格 ）; 
 Stream stream = new FileStream（Test.xlsx，FileMode.Create）; 
 excelData.CreateXlsxAndFillData（stream）; 
 stream.Close（）; 
  

ASP.NET MVC演示中的用法如下：

  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; //我们不想更新数据
 
 //能够使用ToString（），其中不存在于LINQ to Entity中
 //我们应该包含在查询中只有我们将使用的属性
 var query = questions.ToList（）; 
 if（query.Count == 0）
返回新的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 
}））; 
 
返回新的ExcelResult（HeadersQuestions，ColunmTypesQuestions，data，
Questions.xlsx，Questions）; 
} 
  

其中 ExcelResult 定义为

  public class ExcelResult：ActionResult {
 private readonly DataForExcel _data; 
私有readonly字符串_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 =新的DataForExcel（头，colunmTypes，数据，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）; 
 
 //将其返回给客户端 -  strFile已更新，因此返回。 
 response.AddHeader（content-disposition，attachment; filename =+ _fileName）; 
 
 //参见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（）; 
} 
} 
  

为了使代码完整，我必须包含代码的类 DataForExcel ：

  public class DataForExcel {
 public enum DataType {
 String，
 Integer 
} 
 private readonly string [] _headers; 
 private readonly DataType [] _colunmTypes; 
 private readonly List< string []> _数据; 
 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}; （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; 
} 
 
私有静态单元格CreateTextCell（字符串头，UInt32索引，字符串文本）{
 //创建使用InlineString作为孩子的单元格，其中以文本为小孩
 return new Cell（new InlineString（new Text {Text = text}））{
 //单元格属性
 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）}）{
 //单元格属性
 DataType = CellValues.SharedString，
 CellReference = header + index 
}; 
} 
} 
 //使用InlineString作为孩子创建单元格，其中以文本为单位
抛出新的InstanceNotFoundException（）; 
} 
 
私有静态单元格CreateNumberCell（字符串头，UInt32索引，字符串数字AsString）{
 //创建Cell与CellValue作为一个孩子，其中有文本作为一个孩子
 return new Cell（new CellValue {Text = numberAsString}）{
 //单元格属性
 CellReference = header + index 
}; 
} 
 
 private void FillSharedStringTable（IEnumerable< string> data）{
 foreach（数据中的var项目）
 _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）{
 //创建并填充SheetData 
 var sheetData = new SheetData（）; 
 
 //第一行是标题
 sheetData.AppendChild（CreateRow（1，_headers））; 
 
 // const UInt32 iAutoFilter = 2; 
 //跳过AutoFilter的下一行（数字2）
 // var i = iAutoFilter + 1; 
 UInt32 i = 2; 
 
 //首先在OrderedSet< string>中收集所有不同的字符串_os 
 foreach（_data中的var dataRow）
如果（_colunmTypes！= null）
 FillSharedStringTable（dataRow，_colunmTypes）; 
 else 
 FillSharedStringTable（dataRow）; 
 _sharedStrings = _os.ToArray（）; 
 
 foreach（_data中的var dataRow）
 sheetData.AppendChild（_colunmTypes！= null 
？CreateRowWithSharedStrings（i ++，dataRow，_colunmTypes）
：CreateRowWithSharedStrings（i ++，dataRow ））; 
 
 var sst = new SharedStringTable（）; 
 foreach（_os中的var文本）
 sst.AppendChild（new SharedStringItem（new Text（text）））; 
 
 //将空工作簿和工作表添加到SpreadsheetDocument 
 var workbookPart = spreadsheetDocument.AddWorkbookPart（）; 
 var worksheetPart = workbookPart.AddNewPart< WorksheetPart> （）; 
 
 var shareStringPart = workbookPart.AddNewPart< SharedStringTablePart> （）; 
 shareStringPart.SharedStringTable = sst; 
 
 shareStringPart.SharedStringTable.Save（）; 
 
 //将工作表数据添加到工作表
 worksheetPart.Worksheet = new Worksheet（sheetData）; 
 worksheetPart.Worksheet.Save（）; 
 
 //使用工作表填写工作簿
 spreadsheetDocument.WorkbookPart.Workbook = new Workbook（
 new FileVersion {ApplicationName =Microsoft Office Excel}，
 new Sheets （
 new Sheet {
 Name = _sheetName，
 SheetId =（UInt32Value）1U，
 
 //生成表单
 Id = workbookPart。 GetIdOfPart（worksheetPart）
} 
）
）; 
 spreadsheetDocument.WorkbookPart.Workbook.Save（）; 
 spreadsheetDocument.Close（）; 
} 
 
 public void CreateXlsxAndFillData（Stream stream）{
 //使用（var spreadsheetDocument = SpreadsheetDocument.Create（stream，SpreadsheetDocumentType.Workbook））创建工作簿文档
） {
 FillSpreadsheetDocument（spreadsheetDocument）; 
} 
} 
} 
  

上述代码创建新的XLSX文件直接。您可以扩展代码以支持更多的数据类型，例如我在代码中使用的 String 和 Integer 。 >

在更专业的应用程序版本中，您可以创建一些用于导出不同表的XLSX模板。在代码中，您可以将数据放在单元格中，因此修改电子表格而不是创建。您可以创建完美格式的XLSX文件。来自MSDN的示例（请参阅此处）将帮助您实施需要的方式。

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

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

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.

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.

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

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

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

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

Thanks for your precious help! Best Regards

解决方案

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.

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.

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.

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.

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

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

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

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

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:

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.

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.

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

这篇关于jqgrid + EF + MVC：如何在excel中导出？你建议哪种方法？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！