如何使用C＃中的jqGrid数据导出到Excel？ [英] How to export a JQgrid data to Excel using c#?
本文介绍了如何使用C＃中的jqGrid数据导出到Excel？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我已经对此但是大部分的解决方案是MVC ..我只是用Asp.net 3.5我怎么可以在点击按钮实现这个..我应该包括任何图书馆或其他任何东西。请帮助一些研究..
I have made some research regarding this but most of the solutions are for MVC.. I am just using Asp.net 3.5 How could i achieve this on button click.. Should i include any library or anything else.. Please help..
推荐答案
在code，我张贴在答案可以实用而不在其中C＃编写任何ASP.NET code任何修改。 helper类 DataForExcel （请参阅文件 DataForExcel.cs ）具有构造
The code which I posted in the answer can be used practically without any modification in any ASP.NET code which are written in C#. The helper class DataForExcel (see the file DataForExcel.cs) has constructor
public DataForExcel(string[] headers, List<string[]> data, string sheetName)
还是有点更高级的版本
or a little more "advanced" version
public DataForExcel(string[] headers, DataType[] colunmTypes,
List<string[]> data, string sheetName)
允许指定哪些列具有数字数据类型。参数列表＆LT;字符串[]＆GT;数据是需要被导出到Excel中的数据。参数的String []标题指定输出的第一行中的数据。
which allow to specify which columns have numeric datatype. The parameter List<string[]> data is the data which need be exported to Excel. The parameter string[] headers specify the data for the first line of the output.
类 DataForExcel 只有一个公共方法
public void CreateXlsxAndFillData(Stream stream)
填补流与导致.XLSX Excel文件的二进制重新presentation。
which fill stream with the binary representation of resulting .XLSX Excel file.
要返回从ASP.NET方法二进制数据（例如ASHX处理程序），你需要做的只是几乎相同做什么的ExecuteReuslt 从我的答案是：
To return the binary data from your ASP.NET method (for example ASHX handler) you need just do almost the same what do ExecuteResult from my answer:
- 创建使用带有
内存流（VAR流=新的MemoryStream（））{...} - 创建需要导出到Excel
DataForExcel对象VAR dataExcel =新DataForExcel（新[] {ID，投票，标题}，Questions.xlsx，名称或表）; -
response.AddHeader（内容处置，附件;文件名= Questions.xlsx）; -
response.ContentType =应用程序/ vnd.openxmlformats-officedocument.s preadsheetml.sheet; -
response.ContentEncoding = Encoding.UTF8; -
stream.WriteTo（response.OutputStream）; -
response.Flush（）;
- create memory stream with
using (var stream = new MemoryStream()) {...} - create
DataForExcelobject needed for export to Excelvar dataExcel = new DataForExcel (new []{"Id", "Votes", "Title"}, "Questions.xlsx", "Name or Sheet"); response.AddHeader ("content-disposition", "attachment; filename=Questions.xlsx");response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";response.ContentEncoding = Encoding.UTF8;stream.WriteTo (response.OutputStream);response.Flush();
这就是全部。
更新时间：：我修改了code，我前贴中创建非常简单的方法从电网的Excel文件（.XLSX格式）。如果您使用的OpenXML SDK 2.0 那么你可以使用。 .NET 3.5。 的OpenXML SDK 2.5 需要.NET 4.0或更高版本。
UPDATED: I modified the code which I posted before to create in very easy way the Excel file (in .xlsx format) from the grid. If you use OpenXML SDK 2.0 then you can use .NET 3.5. OpenXML SDK 2.5 required .NET 4.0 or higher.
建议的code可让数据的的String [] [] 转换为二进制Excel数据和写入流的结果。您可以使用的MemoryStream 就像我之前所描述从任何ASP.NET应用程序返回Excel中。
The suggested code allows to convert string[][] of data to binary Excel data and write the results in Stream. You can use MemoryStream like I described before to return Excel from any ASP.NET application.
建议的code包含 ExportToExcel 静态类与一个公共静态方法填充preadsheetDocument 这可以通过以下方式使用
The suggested code contain ExportToExcel static class with one public static method FillSpreadsheetDocument which can be used in the following way
var data = new[] {
new [] {"Tom", "30", "x", "", "1974-06-16"},
new [] {"Margarita", "34", "x", "x", "1978-10-02"},
new [] {"Bob", "7", "", "", "2005-06-26"},
new [] {"Oleg", "48", "x", "x", "1964-09-11"},
new [] {"Frank", "29", "", "x", "1983-01-28"}
};
using (var stream = new FileStream("Test.xlsx", FileMode.Create)) {
ExportToExcel.FillSpreadsheetDocument(stream,
new[] {
new ColumnModel { Type = DataType.String, Alignment = HorizontalAlignment.Left, Header = "Name" },
new ColumnModel { Type = DataType.Integer, Header = "Age" },
new ColumnModel { Type = DataType.String, Header = "Is Married", Alignment = HorizontalAlignment.Center, IsRotatedHeader = true },
new ColumnModel { Type = DataType.String, Header = "Has Children", Alignment = HorizontalAlignment.Center, IsRotatedHeader = true },
new ColumnModel { Type = DataType.Date, Header = "Birthday", Alignment = HorizontalAlignment.Left }
},
data,
"Friends");
}
它产生的Test.xlsx用一张纸朋友这看起来
列的宽度将不设置，但在两次点击（选择所有，双击列之间），一个用户可以列的宽度设定为最佳宽度像以上的构建。所有的细胞已格式化的数据（没有通用的格式）。我用了整数，日期和纯字符串。人们可以轻松创建居中对齐或右对齐文本列。
The width of the columns will be not set, but in two clicks (select all and double click on between columns) one the user can set the width of columns to optimal width like on the build above. All cells has formatted data (no "Generic" format). I used integer, date and pure strings. One can easy create columns with center alignment or right alignment texts.
您可以轻松修改code，让更多不同的文本格式将被使用。这只是一个例子，如何产生真正的Excel文档与格式的单元格。
You can easy modify the code so that more different text formats will be used. It's just an example how to produce real Excel document with formatted cells.
工作Visual Studio 2008的项目可以从这里下载 下面，你会发现从演示源$ C $ C：
The working Visual Studio 2008 Project you can download from here. Below you will find the source code from the demo:
using System;
using System.Collections.Generic;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System.Text;
using System.IO;
using System.Globalization;
namespace ExportToExcel {
public enum ExcelCellDataType {
String,
Integer,
Date
}
public enum HorizontalAlignment {
Left,
Center,
Right
}
public class ColumnModel {
public ExcelCellDataType Type { set; get; }
public HorizontalAlignment Alignment { set; get; }
public string Header { set; get; }
public bool IsRotatedHeader { set; get; }
}
public enum OutputCellFormat: uint {
Text,
Integer,
Date,
TextHeader,
TextHeaderRotated,
TextCenter,
TextRight
}
public static class ExportToExcel {
private static StringBuilder ConvertIntToColumnHeader (uint iCol) {
var sb = new StringBuilder();
while (iCol > 0) {
if (iCol <= 'Z' - 'A') // iCol=0 -> 'A', 25 -> 'Z'
break;
sb.Append(ConvertIntToColumnHeader(iCol / ('Z' - 'A' + 1) - 1));
iCol = iCol % ('Z' - 'A' + 1);
}
sb.Append((char)('A' + iCol));
return sb;
}
private static string GetCellReference (uint iRow, uint iCol) {
return ConvertIntToColumnHeader(iCol).Append(iRow).ToString();
}
private static Row CreateColumnHeaderRow (uint iRow, IList<ColumnModel> colunmModels) {
var r = new Row { RowIndex = iRow };
for (var iCol = 0; iCol < colunmModels.Count; iCol++) {
var styleIndex = colunmModels[iCol].IsRotatedHeader
? (UInt32Value)(uint)(OutputCellFormat.TextHeaderRotated + 1)
: (UInt32Value)(uint)(OutputCellFormat.TextHeader + 1);
r.Append(new OpenXmlElement[] {
// create Cell with InlineString as a child, which has Text as a child
new Cell(new InlineString(new Text { Text = colunmModels[iCol].Header })) {
DataType = CellValues.InlineString,
StyleIndex = styleIndex,
CellReference = GetCellReference(iRow, (uint)iCol)
}
});
}
return r;
}
private static UInt32Value GetStyleIndexFromColumnModel (ColumnModel colunmModel) {
switch (colunmModel.Type) {
case ExcelCellDataType.Integer:
return (uint)(OutputCellFormat.Integer) + 1;
case ExcelCellDataType.Date:
return (uint)(OutputCellFormat.Date) + 1;
}
switch (colunmModel.Alignment) {
case HorizontalAlignment.Center:
return (uint)(OutputCellFormat.TextCenter) + 1;
case HorizontalAlignment.Right:
return (uint)(OutputCellFormat.TextRight) + 1;
default:
return (uint)(OutputCellFormat.Text) + 1;
}
}
private static string ConvertDateToString (string date) {
DateTime dt;
string text = date; // default results of conversion
if (DateTime.TryParse(date, out dt))
text = dt.ToOADate().ToString(CultureInfo.InvariantCulture);
return text;
}
private static Row CreateRow (UInt32 iRow, IList<string> data, IList<ColumnModel> colunmModels, IDictionary<string, int> sharedStrings) {
var r = new Row { RowIndex = iRow };
for (var iCol = 0; iCol < data.Count; iCol++) {
var styleIndex = (uint)(OutputCellFormat.Text) + 1;
string text = data[iCol] ?? String.Empty;
if (colunmModels != null && iCol < colunmModels.Count) {
styleIndex = GetStyleIndexFromColumnModel(colunmModels[iCol]);
switch (colunmModels[iCol].Type) {
case ExcelCellDataType.Integer:
r.Append(new OpenXmlElement[] {
// create Cell with CellValue as a child, which has Text as a child
new Cell(new CellValue { Text = text }) {
StyleIndex = styleIndex,
CellReference = GetCellReference(iRow, (uint)iCol)
}
});
continue;
case ExcelCellDataType.Date:
r.Append(new OpenXmlElement[] {
// create Cell with CellValue as a child, which has Text as a child
new Cell(new CellValue { Text = ConvertDateToString(text) }) {
StyleIndex = styleIndex,
CellReference = GetCellReference(iRow, (uint)iCol)
}
});
continue;
}
}
// default format is text
if (String.IsNullOrEmpty(text) || !sharedStrings.ContainsKey(text)) {
// create Cell with InlineString as a child, which has Text as a child
r.Append(new OpenXmlElement[] {
new Cell(new InlineString(new Text { Text = text })) {
DataType = CellValues.InlineString,
StyleIndex = styleIndex,
CellReference = GetCellReference(iRow, (uint)iCol)
}
});
} else {
r.Append(new OpenXmlElement[] {
// create Cell with CellValue as a child, which has Text as a child
new Cell(new CellValue { Text = sharedStrings[text].ToString(CultureInfo.InvariantCulture) }) {
DataType = CellValues.SharedString,
StyleIndex = styleIndex,
CellReference = GetCellReference(iRow, (uint)iCol)
}
});
}
}
return r;
}
private static void FillSpreadsheetDocument (SpreadsheetDocument spreadsheetDocument, IList<ColumnModel> columnModels, IList<string[]> data, string sheetName) {
if (columnModels == null)
throw new ArgumentNullException("columnModels");
if (data == null)
throw new ArgumentNullException("data");
// add empty workbook and worksheet to the SpreadsheetDocument
var workbookPart = spreadsheetDocument.AddWorkbookPart();
var worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
var workbookStylesPart = workbookPart.AddNewPart<WorkbookStylesPart>();
// create styles for the header and columns
workbookStylesPart.Stylesheet = new Stylesheet(
new Fonts(
// Index 0 - The default font.
new Font(
new FontSize { Val = 11 },
new Color { Rgb = new HexBinaryValue { Value = "00000000" } },
new FontName { Val = "Calibri" }
),
// Index 1 - The bold font.
new Font(
new Bold(),
new FontSize { Val = 11 },
new Color { Rgb = new HexBinaryValue { Value = "00000000" } },
new FontName { Val = "Calibri" }
)
),
new Fills(
// Index 0 - required, reserved by Excel - no pattern
new Fill(new PatternFill { PatternType = PatternValues.None }),
// Index 1 - required, reserved by Excel - fill of gray 125
new Fill(new PatternFill { PatternType = PatternValues.Gray125 }),
// Index 2 - no pattern text on gray background
new Fill(new PatternFill {
PatternType = PatternValues.Solid,
BackgroundColor = new BackgroundColor { Indexed = 64U },
ForegroundColor = new ForegroundColor { Rgb = "FFD9D9D9" }
})
),
new Borders(
// Index 0 - The default border.
new Border(
new LeftBorder(),
new RightBorder(),
new TopBorder(),
new BottomBorder(),
new DiagonalBorder()
),
// Index 1 - Applies a Left, Right, Top, Bottom border to a cell
new Border(
new LeftBorder(new Color { Auto = true }) { Style = BorderStyleValues.Thin },
new RightBorder(new Color { Auto = true }) { Style = BorderStyleValues.Thin },
new TopBorder(new Color { Auto = true }) { Style = BorderStyleValues.Thin },
new BottomBorder(new Color { Auto = true }) { Style = BorderStyleValues.Thin },
new DiagonalBorder()
)
),
new CellFormats(
// Index 0 - The default cell style. If a cell does not have a style iCol applied it will use this style combination instead
new CellFormat {
NumberFormatId = (UInt32Value)0U,
FontId = (UInt32Value)0U,
FillId = (UInt32Value)0U,
BorderId = (UInt32Value)0U
},
// Index 1 - Alignment Left, Text
new CellFormat(new Alignment { Horizontal = HorizontalAlignmentValues.Left }) {
NumberFormatId = (UInt32Value)49U, // "@" - text format - see http://msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.numberingformat.aspx
FontId = (UInt32Value)0U,
FillId = (UInt32Value)0U,
BorderId = (UInt32Value)1U,
ApplyNumberFormat = true,
ApplyAlignment = true
},
// Index 2 - Interger Number
new CellFormat {
NumberFormatId = (UInt32Value)1U, // "0" - integer format - see http://msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.numberingformat.aspx
FontId = (UInt32Value)0U,
FillId = (UInt32Value)0U,
BorderId = (UInt32Value)1U,
ApplyNumberFormat = true
},
// Index 3 - Interger Date
new CellFormat {
NumberFormatId = (UInt32Value)14U, // "14" - date format mm-dd-yy - see http://msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.numberingformat.aspx
FontId = (UInt32Value)0U,
FillId = (UInt32Value)0U,
BorderId = (UInt32Value)1U,
ApplyNumberFormat = true
},
// Index 4 - Text for headers
new CellFormat(new Alignment {
Vertical = VerticalAlignmentValues.Center,
Horizontal = HorizontalAlignmentValues.Center
}) {
NumberFormatId = (UInt32Value)49U, // "@" - text format - see http://msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.numberingformat.aspx
FontId = (UInt32Value)1U,
FillId = (UInt32Value)2U,
BorderId = (UInt32Value)1U,
ApplyNumberFormat = true,
ApplyAlignment = true
},
// Index 5 - Text for headers rotated
new CellFormat(new Alignment {
Horizontal = HorizontalAlignmentValues.Center,
TextRotation = (UInt32Value)90U
}) {
NumberFormatId = (UInt32Value)49U, // "@" - text format - see http://msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.numberingformat.aspx
FontId = (UInt32Value)1U,
FillId = (UInt32Value)2U,
BorderId = (UInt32Value)1U,
ApplyNumberFormat = true,
ApplyAlignment = true
},
// Index 6 - Alignment Center, Text
new CellFormat(new Alignment { Horizontal = HorizontalAlignmentValues.Center }) {
NumberFormatId = (UInt32Value)49U, // "@" - text format - see http://msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.numberingformat.aspx
FontId = (UInt32Value)0U,
FillId = (UInt32Value)0U,
BorderId = (UInt32Value)1U,
ApplyNumberFormat = true,
ApplyAlignment = true
},
// Index 7 - Alignment Right, Text
new CellFormat(new Alignment { Horizontal = HorizontalAlignmentValues.Right }) {
NumberFormatId = (UInt32Value)49U, // "@" - text format - see http://msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.numberingformat.aspx
FontId = (UInt32Value)0U,
FillId = (UInt32Value)0U,
BorderId = (UInt32Value)1U,
ApplyNumberFormat = true,
ApplyAlignment = true
}
)
);
workbookStylesPart.Stylesheet.Save();
// create and fill SheetData
var sheetData = new SheetData();
// first row is the header
uint iRow = 1;
sheetData.AppendChild(CreateColumnHeaderRow(iRow++, columnModels));
//iRow++; // skip one row for the filter
// first of all collect all different strings
var sst = new SharedStringTable();
var sharedStrings = new SortedDictionary<string, int>();
foreach (var dataRow in data)
for (var iCol = 0; iCol < dataRow.Length; iCol++)
if (iCol >= columnModels.Count || columnModels[iCol].Type != ExcelCellDataType.Integer) {
string text = (columnModels[iCol].Type == ExcelCellDataType.Date
? dataRow[iCol]
: ConvertDateToString(dataRow[iCol])) ?? String.Empty;
if (!String.IsNullOrEmpty(text) && !sharedStrings.ContainsKey(text)) {
sst.AppendChild(new SharedStringItem(new Text(text)));
sharedStrings.Add(text, sharedStrings.Count);
}
}
var shareStringPart = workbookPart.AddNewPart<SharedStringTablePart>();
shareStringPart.SharedStringTable = sst;
shareStringPart.SharedStringTable.Save();
foreach (var dataRow in data)
sheetData.AppendChild(CreateRow(iRow++, dataRow, columnModels, sharedStrings));
// 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,
Id = workbookPart.GetIdOfPart(worksheetPart) // generate the id for sheet
})
);
spreadsheetDocument.WorkbookPart.Workbook.Save();
spreadsheetDocument.Close();
}
public static void FillSpreadsheetDocument (Stream stream, IList<ColumnModel> columnModels, IList<string[]> data, string sheetName) {
using (var spreadsheetDocument = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook)) {
FillSpreadsheetDocument(spreadsheetDocument, columnModels, data, sheetName);
}
}
}
class Program {
static void Main () {
var data = new[] {
new [] {"Tom", "30", "x", null, "1974-06-16"},
new [] {"Margarita", "34", "x", "x", null},
new [] {"Bob", "7", "", "", "2005-06-26"},
new [] {"Oleg", null, "x", "x", "1964-09-11"},
new [] {"Frank", "29", "", "x", "1983-01-28"}
};
using (var stream = new FileStream("Test.xlsx", FileMode.Create)) {
ExportToExcel.FillSpreadsheetDocument(stream,
new[] {
new ColumnModel { Type = ExcelCellDataType.String, Alignment = HorizontalAlignment.Left, Header = "Name" },
new ColumnModel { Type = ExcelCellDataType.Integer, Header = "Age" },
new ColumnModel { Type = ExcelCellDataType.String, Header = "Is Married", Alignment = HorizontalAlignment.Center, IsRotatedHeader = true },
new ColumnModel { Type = ExcelCellDataType.String, Header = "Has Children", Alignment = HorizontalAlignment.Center, IsRotatedHeader = true },
new ColumnModel { Type = ExcelCellDataType.Date, Header = "Birthday", Alignment = HorizontalAlignment.Left }
},
data,
"Friends");
}
}
}
}
这篇关于如何使用C＃中的jqGrid数据导出到Excel？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
