asp.net-mvc – jqgrid EF MVC：如何在excel中导出？你建议哪种

正如我之前所写的那样(例如,参见 here和 here),将网格数据导出为XML的最佳方法是使用 Open XML SDK 2.0.

Stephen Walther博士的post展示了如何创建可以由Excel读取的HTML文件.它不是Excel文件,必须仍然转换为Excel格式. CSV的使用有更多问题.依赖于源表中的内容,自动转换为Excel数据类型绝对是错误的.在我为客户开发的一个项目中,网格包含有关软件产品的信息：产品名称,版本等.软件版本有时会作为日期(例如1.3.1963),并且此类单元格将被错误转换(在德语中使用’.’作为日期中的分隔符).结果一个人真的遇到了困难.使用带有逗号的文本的CSV也经常被错误导入.即使引用带逗号(,)的单元格并转义带有配额的文本,导入仍然是错误的,尤其是在第一列中.我不想在这里解释所有尝试和错误的完整历史,但毕竟我决定放弃使用CSV和HTML并开始使用Open XML SDK 2.0,它允许创建带有扩展XLSX的真实Excel文件.这种方式似乎很完美,因为一个人不需要任何办公室
安装在服务器上的组件,没有其他许可证.

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

可以找到很多关于如何使用Open XML SDK 2.0的信息(参见here,here和here).许多有用的代码示例可直接在MSDN上找到(参见here).然而,Open XML SDK 2.0的实际使用至少在第一时间并不那么容易.所以我从我自己使用的代码部分创建了一个演示.

您可以从here下载演示项目.演示是the answer和this one演示的扩展.

要导出数据,我使用DataForExcel帮助程序类.它在表单中有构造函数

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

或者以一点简化的形式

DataForExcel(string[] headers,string sheetName)

和唯一的公共方法

CreateXlsxAndFillData(Stream stream)

使用类创建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();

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

static readonly string[] HeadersQuestions = {
    "Id","Votes","Title"
};
static readonly DataForExcel.DataType[] ColunmTypesQuestions = {
    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");
}

其中ExcelResult定义为

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

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

    public ExcelResult (string[] headers,DataForExcel.DataType[] colunmTypes,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);

            //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();
    }
}

为了使代码完整,我必须包含类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),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),data[i]) });
            else
                r.Append (new OpenXmlElement[] { CreateSharedTextCell (ConvertIntToColumnHeader (i),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,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,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,string sheetName) {
        _headers = headers;
        _data = data;
        _sheetName = sheetName;
    }

    public DataForExcel(string[] headers,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);
        }
    }
}

上面的代码直接创建了新的XLSX文件.您可以扩展代码以支持更多数据类型,如我在代码中使用的String和Integer.

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

更新：The answer包含更新的代码,允许生成带有更多单元格格式的Excel.