如何使用 c# 将 JQgrid 数据导出到 Excel? [英] How to export a JQgrid data to Excel using c#?
本文介绍了如何使用 c# 将 JQgrid 数据导出到 Excel?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我对此进行了一些研究，但大多数解决方案都是针对 MVC 的.我只是使用 Asp.net 3.5 我怎么能在按钮点击时实现这一点......我应该包括任何库或其他任何东西......请帮忙..
解决方案
我在. 下面是演示的源代码:
使用系统；使用 System.Collections.Generic;使用 DocumentFormat.OpenXml；使用 DocumentFormat.OpenXml.Packaging;使用 DocumentFormat.OpenXml.Spreadsheet；使用 System.Text;使用 System.IO；使用 System.Globalization；命名空间 ExportToExcel {公共枚举 ExcelCellDataType {细绳，整数，日期}公共枚举水平对齐{剩下，中心，对}公共类 ColumnModel {公共 ExcelCellDataType 类型 { 设置；得到;}公共水平对齐对齐{设置；得到;}公共字符串标题{设置；得到;}public bool IsRotatedHeader { set;得到;}}公共枚举 OutputCellFormat: uint {文本，整数，日期，文本标题，文本标题旋转，文本中心，右文字}公共静态类 ExportToExcel {私有静态 StringBuilder ConvertIntToColumnHeader (uint iCol) {var sb = new StringBuilder();而 (iCol > 0) {if (iCol <= 'Z' - 'A')//iCol=0 ->'A', 25 ->'Z'休息;sb.Append(ConvertIntToColumnHeader(iCol/('Z' - 'A' + 1) - 1));iCol = iCol % ('Z' - 'A' + 1);}sb.Append((char)('A' + iCol));返回某人;}私有静态字符串 GetCellReference (uint iRow, uint iCol) {返回 ConvertIntToColumnHeader(iCol).Append(iRow).ToString();}私有静态行 CreateColumnHeaderRow (uint iRow, IList colunmModels) {var r = 新行 { 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(新的 OpenXmlElement[] {//创建以 InlineString 为子元素的 Cell，其子元素为 Textnew Cell(new InlineString(new Text { Text = colunmModels[iCol].Header })) {数据类型 = CellValues.InlineString,样式索引 = 样式索引，CellReference = GetCellReference(iRow, (uint)iCol)}});}返回 r;}私有静态 UInt32Value GetStyleIndexFromColumnModel (ColumnModel colunmModel) {开关(colunmModel.Type){案例 ExcelCellDataType.Integer:返回 (uint)(OutputCellFormat.Integer) + 1;案例 ExcelCellDataType.Date:返回 (uint)(OutputCellFormat.Date) + 1;}开关(colunmModel.Alignment){案例 HorizontalAlignment.Center:返回 (uint)(OutputCellFormat.TextCenter) + 1;案例 HorizontalAlignment.Right:返回 (uint)(OutputCellFormat.TextRight) + 1;默认:返回 (uint)(OutputCellFormat.Text) + 1;}}私有静态字符串 ConvertDateToString(字符串日期){日期时间 dt;字符串文本 = 日期；//默认转换结果if (DateTime.TryParse(date, out dt))文本 = dt.ToOADate().ToString(CultureInfo.InvariantCulture);返回文本；}私有静态行 CreateRow (UInt32 iRow, IList data, IList colunmModels, IDictionary sharedStrings) {var r = 新行 { RowIndex = iRow };for (var iCol = 0; iCol < data.Count; iCol++) {var styleIndex = (uint)(OutputCellFormat.Text) + 1;字符串文本 = 数据 [iCol] ??String.Empty;if (colunmModels != null && iCol < colunmModels.Count) {styleIndex = GetStyleIndexFromColumnModel(colunmModels[iCol]);开关 (colunmModels[iCol].Type) {案例 ExcelCellDataType.Integer:r.Append(新的 OpenXmlElement[] {//以 CellValue 作为子节点创建 Cell，以 Text 作为子节点新单元格(新单元格值 { 文本 = 文本 }){样式索引 = 样式索引，CellReference = GetCellReference(iRow, (uint)iCol)}});继续;案例 ExcelCellDataType.Date:r.Append(新的 OpenXmlElement[] {//以 CellValue 作为子节点创建 Cell，以 Text 作为子节点new Cell(new CellValue { Text = ConvertDateToString(text) }) {样式索引 = 样式索引，CellReference = GetCellReference(iRow, (uint)iCol)}});继续;}}//默认格式为文本if (String.IsNullOrEmpty(text) || !sharedStrings.ContainsKey(text)) {//创建以 InlineString 为子元素的 Cell，其子元素为 Textr.Append(新的 OpenXmlElement[] {new Cell(new InlineString(new Text { Text = text })) {数据类型 = CellValues.InlineString,样式索引 = 样式索引，CellReference = GetCellReference(iRow, (uint)iCol)}});} 别的 {r.Append(新的 OpenXmlElement[] {//以 CellValue 作为子节点创建 Cell，以 Text 作为子节点new Cell(new CellValue { Text = sharedStrings[text].ToString(CultureInfo.InvariantCulture) }) {数据类型 = CellValues.SharedString,样式索引 = 样式索引，CellReference = GetCellReference(iRow, (uint)iCol)}});}}返回 r;}private static void FillSpreadsheetDocument(SpreadsheetDocument 电子表格文档，IList columnModels，IList 数据，字符串 sheetName){if (columnModels == null)throw new ArgumentNullException("columnModels");如果(数据==空)throw new ArgumentNullException("data");//将空工作簿和工作表添加到电子表格文档var workbookPart = 电子表格Document.AddWorkbookPart();var worksheetPart = workbookPart.AddNewPart();var workbookStylesPart = workbookPart.AddNewPart();//为标题和列创建样式workbookStylesPart.Stylesheet = new Stylesheet(新字体(//索引 0 - 默认字体.新字体(新字体大小 { Val = 11 },new Color { Rgb = new HexBinaryValue { Value = "00000000" } },新字体名称 { Val = "Calibri" }),//索引 1 - 粗体字体.新字体(新加粗()，新字体大小 { Val = 11 },new Color { Rgb = new HexBinaryValue { Value = "00000000" } },新字体名称 { Val = "Calibri" })),新填充(//索引 0 - 必需，Excel 保留 - 无模式新填充(新 PatternFill { PatternType = PatternValues.None })，//索引 1 - 必需，由 Excel 保留 - 填充灰色 125新填充(新 PatternFill { PatternType = PatternValues.Gray125 })，//索引 2 - 灰色背景上没有图案文本新填充(新模式填充{PatternType = PatternValues.Solid,背景颜色 = 新背景颜色 { 索引 = 64U },ForegroundColor = 新的 ForegroundColor { Rgb = "FFD9D9D9" }})),新边界(//索引 0 - 默认边框.新边框(新左边框()，新的右边框()，新的 TopBorder(),新的底部边框()，新的对角线边框()),//索引 1 - 将左、右、上、下边框应用到单元格新边框(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 },新的对角线边框())),新的单元格格式(//索引 0 - 默认单元格样式.如果单元格没有应用样式 iCol，它将使用此样式组合代替新的单元格格式 {NumberFormatId = (UInt32Value)0U,FontId = (UInt32Value)0U,FillId = (UInt32Value)0U,BorderId = (UInt32Value)0U},//索引 1 - 左对齐，文本新的 CellFormat(新的对齐方式 { Horizontal = HorizontalAlignmentValues.Left }){NumberFormatId = (UInt32Value)49U,//"@" - 文本格式 - 参见 http://msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.numberingformat.aspxFontId = (UInt32Value)0U,FillId = (UInt32Value)0U,BorderId = (UInt32Value)1U,ApplyNumberFormat = true,应用对齐 = 真},//索引 2 - 整数新的单元格格式 {NumberFormatId = (UInt32Value)1U,//"0" - 整数格式 - 参见 http://msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.numberingformat.aspxFontId = (UInt32Value)0U,FillId = (UInt32Value)0U,BorderId = (UInt32Value)1U,ApplyNumberFormat = true},//索引 3 - 整数日期新的单元格格式 {NumberFormatId = (UInt32Value)14U,//"14" - 日期格式 mm-dd-yy - 请参阅 http://msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.numberingformat.aspxFontId = (UInt32Value)0U,FillId = (UInt32Value)0U,BorderId = (UInt32Value)1U,ApplyNumberFormat = true},//索引 4 - 标题文本新单元格格式(新对齐方式{垂直 = VerticalAlignmentValues.Center，水平 = HorizontalAlignmentValues.Center}) {NumberFormatId = (UInt32Value)49U,//"@" - 文本格式 - 参见 http://msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.numberingformat.aspxFontId = (UInt32Value)1U,FillId = (UInt32Value)2U,BorderId = (UInt32Value)1U,ApplyNumberFormat = true,应用对齐 = 真},//索引 5 - 旋转标题的文本新单元格格式(新对齐方式{水平 = HorizontalAlignmentValues.Center，TextRotation = (UInt32Value)90U}) {NumberFormatId = (UInt32Value)49U,//"@" - 文本格式 - 参见 http://msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.numberingformat.aspxFontId = (UInt32Value)1U,FillId = (UInt32Value)2U,BorderId = (UInt32Value)1U,ApplyNumberFormat = true,应用对齐 = 真},//索引 6 - 对齐中心，文本新单元格格式(新对齐方式 { Horizontal = HorizontalAlignmentValues.Center }){NumberFormatId = (UInt32Value)49U,//"@" - 文本格式 - 参见 http://msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.numberingformat.aspxFontId = (UInt32Value)0U,FillId = (UInt32Value)0U,BorderId = (UInt32Value)1U,ApplyNumberFormat = true,应用对齐 = 真},//索引 7 - 右对齐，文本新的 CellFormat(新的对齐方式 { Horizontal = HorizontalAlignmentValues.Right }){NumberFormatId = (UInt32Value)49U,//"@" - 文本格式 - 参见 http://msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.numberingformat.aspxFontId = (UInt32Value)0U,FillId = (UInt32Value)0U,BorderId = (UInt32Value)1U,ApplyNumberFormat = true,应用对齐 = 真}));workbookStylesPart.Stylesheet.Save();//创建并填充 SheetDatavar sheetData = new SheetData();//第一行是标题uint iRow = 1;sheetData.AppendChild(CreateColumnHeaderRow(iRow++, columnModels));//iRow++;//跳过过滤器的一行//首先收集所有不同的字符串var sst = new SharedStringTable();var sharedStrings = new SortedDictionary();foreach(数据中的var dataRow)for (var iCol = 0; iCol < dataRow.Length; iCol++)if (iCol >= columnModels.Count || columnModels[iCol].Type != ExcelCellDataType.Integer) {字符串文本 = (columnModels[iCol].Type == ExcelCellDataType.Date?数据行[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();shareStringPart.SharedStringTable = sst;shareStringPart.SharedStringTable.Save();foreach(数据中的var dataRow)sheetData.AppendChild(CreateRow(iRow++, dataRow, columnModels, sharedStrings));//将工作表数据添加到工作表worksheetPart.Worksheet = new Worksheet(sheetData);worksheetPart.Worksheet.Save();//用工作表填充工作簿电子表格Document.WorkbookPart.Workbook = 新工作簿(new FileVersion { ApplicationName = "Microsoft Office Excel" },新表(新表{名称 = 工作表名称，SheetId = (UInt32Value)1U,Id = workbookPart.GetIdOfPart(worksheetPart)//生成工作表的 id}));电子表格Document.WorkbookPart.Workbook.Save();电子表格Document.Close();}public static void FillSpreadsheetDocument (Stream stream, IList columnModels, IList data, string sheetName) {使用(var 电子表格文档 = 电子表格文档.创建(流，电子表格文档类型.工作簿)){FillSpreadsheetDocument(spreadsheetDocument, columnModels, data, sheetName);}}}课程计划{静态无效主(){var 数据 = 新 [] {新 [] {汤姆"，30"，x"，空，1974-06-16"}，新 [] {玛格丽塔"，34"，x"，x"，空}，新 [] {"鲍勃", "7", "", "", "2005-06-26"},新 [] {奥列格"，空，x"，x"，1964-09-11"}，新 [] {弗兰克"，29"，"，x"，1983-01-28"}};使用 (var stream = new FileStream("Test.xlsx", FileMode.Create)) {ExportToExcel.FillSpreadsheetDocument(stream,新的[] {新 ColumnModel { Type = ExcelCellDataType.String, Alignment = HorizontalAlignment.Left, Header = "Name" },新的 ColumnModel { Type = ExcelCellDataType.Integer, Header = "Age" },新 ColumnModel { Type = ExcelCellDataType.String, Header = "Is Married", Alignment = HorizontalAlignment.Center, IsRotatedHeader = true },新 ColumnModel { Type = ExcelCellDataType.String, Header = "Has Children", Alignment = HorizontalAlignment.Center, IsRotatedHeader = true },新 ColumnModel { Type = ExcelCellDataType.Date, Header = "Birthday", Alignment = HorizontalAlignment.Left }},数据，朋友们");}}}} 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..
解决方案
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)
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.
The class DataForExcel has only one public method
public void CreateXlsxAndFillData(Stream stream)
which fill stream with the binary representation of resulting .XLSX Excel file.
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:
- 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();
It's all.
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.
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.
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");
}
It produces the "Test.xlsx" with one sheet "Friends" which looks
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.
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.
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屋！
查看全文
