jqgrid + EF + MVC:如何在 excel 中导出?你建议哪种方法? [英] jqgrid + EF + MVC: How to export in excel? Which method you suggest?
问题描述
我正在使用 jqgrid(标准)和 EF 4 + MVC3.我想实现excel导出.你会建议我哪种方法?
I am using jqgrid (standard) with EF 4 + MVC3. I'd like to implement excel export. Which method you would suggest me?
要生成 excel，我想使用 这个库 由 Stephen Walther 博士提供，它具有三种类型的输出和也允许定义标题.请告诉我您是否认为它对我的目的有效.
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 导出，我发现了几种技术.有些人建议进行 csv 导出，其他人表示它应该返回一个 JSON 输出，我不清楚这个功能是否存在于 jqgrid 的免费版本中.无论如何，我想将数据传递给 Walther 的对象.
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.
关于 jqgrid 代码，我找到了 Oleg 的这个有趣的答案，但我不明白是否可以应用于我的需求.
About the jqgrid code, I found this interesting answer by Oleg, but I do not understand if could be applied to my needs.
不幸的是，我现在只找到了部分使用 EF MVC 导出 excel 的解决方案，但没有解决方案或完整示例...
Unfortunately, by now I only found parts of solutions for excel export with EF MVC, but no solution or complete examples...
关于MVC逻辑，我将按照@Tommy的建议实现和开发这段代码.
如果问题可能很愚蠢，请见谅，我只是一个(爱好者)初学者.
Please sorry if the question could be silly, I am just a (enthusiast) beginner.
感谢您的宝贵帮助！此致
推荐答案
正如我之前写的(参见 此处 和 <以a href="https://stackoverflow.com/a/5759889/315935">此处为例)将网格数据导出为XML的最佳方式是使用Open XML SDK 2.0.
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.
post 的 Stephen Walther 博士展示了如何创建可以被 Excel 读取的 HTML 文件.它不是 Excel 文件，仍必须转换为 Excel 格式.CSV 的使用有更多的问题.根据源表中的内容，自动转换为 Excel 数据类型可能是完全错误的.在我为客户开发的一个项目中，网格包含有关软件产品的信息:产品名称、版本等.软件版本有时看起来像日期(例如 1.3.1963)，这样的单元格将被错误转换(在德语中，使用."作为日期中的分隔符).结果，一个人遇到了非常棘手的问题.CSV 中包含逗号的文本的使用也经常会被错误导入.即使引用带有逗号 (,) 的单元格并转义具有配额的文本，导入仍然是错误的，尤其是在第一列中.我不想在这里解释所有尝试和错误的整个历史，但毕竟我决定放弃使用 CSV 和 HTML 并开始使用 Open XML SDK 2.0，它允许创建 真正的 Excel 文件 扩展名为 XLSX.这种方式在我看来很完美，因为不需要任何办公室组件安装在服务器上，无需额外许可.
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.
唯一的限制是应该能够使用DocumentFormat.OpenXml.dll，因此您的服务器程序应该可以在任何Windows 操作系统上运行.众所周知，XLSX 文件是 ZIP 文件，其中包含一些 XML 文件.如果您仍然不知道，我建议您将 XLSX 文件重命名为 ZIP 文件并解压缩.Open XML SDK 2.0 是一个可以像处理 XML 文件一样处理 XLSX 文件的库.因此不需要额外的 Office 组件.
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.
可以找到很多关于如何使用 Open XML SDK 2.0 的信息(参见 此处、此处 和此处).许多有用的代码示例可以直接在 MSDN 上找到(参见 此处).然而，Open XML SDK 2.0 的实际使用至少在第一次时并不那么容易.所以我从我自己使用的代码部分创建了一个演示.
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.
为了导出数据，我使用了 DataForExcel 帮助器类.它具有形式为
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)
或者稍微简化的形式
DataForExcel(string[] headers, List<string[]> data, string sheetName)
和唯一的公共方法
CreateXlsxAndFillData(Stream stream)
创建Excel文件的类的用法如下
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();
在 ASP.NET MVC 演示中的用法如下
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");
}
其中 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();
}
}
为了使代码完整，我必须包含 DataForExcel 类的代码:
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);
}
}
}
以上代码直接创建新的 XLSX 文件.您可以扩展代码以支持更多数据类型，例如我在代码中使用的 String 和 Integer.
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 模板.在代码中，您可以将数据放在单元格中，因此修改电子表格而不是创建.通过这种方式，您可以创建完美格式化的 XLSX 文件.MSDN 中的示例(请参阅此处)将帮助您实施何时需要的方式.
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.
更新:答案包含更新的代码，允许生成带有更多单元格格式的 Excel.
UPDATED: The answer contains updated code which allows generate Excel documented with more cell formatting.
这篇关于jqgrid + EF + MVC:如何在 excel 中导出?你建议哪种方法?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
