How to style Excel cell using Open XMLHow do I format a Microsoft JSON date?How to Deserialize XML documentHow do you create a dropdownlist from an enum in ASP.NET MVC?How do you handle multiple submit buttons in ASP.NET MVC Framework?How to add reference to System.Web.Optimization for MVC-3-converted-to-4 appHow to add Web API to an existing ASP.NET MVC 4 Web Application project?How to make custom error pages work in ASP.NET MVC 4Download report in excel format using SSRS in ASP.NET MVCvs2012 MVC4 download excel file works on desktop but not mobile “could not read the document”Export data with images to Excel
"Correct me if I'm wrong"
How can I restore a master database from its bak file?
How do I find which software is doing an SSH connection?
Name for a function whose effect is canceled by another function?
Scaling an object to change its key
Examples of protocols that are insecure when run concurrently
Math symbols in math operators
What does this Swiss black on yellow rectangular traffic sign with a symbol looking like a dart mean?
Why is it easier to balance a non-moving bike standing up than sitting down?
Is using legacy mode instead of UEFI mode a bad thing to do?
Synaptic Static - when to roll the d6?
I just entered the USA without passport control at Atlanta airport
Why do you need to heat the pan before heating the olive oil?
In the US, can a former president run again?
How to compute the inverse of an operation in Q#?
Is Newton's third law really correct?
How do you transpose samples in cents?
What could be the physiological mechanism for a biological Geiger counter?
How can I prevent a user from copying files on another hard drive?
Why is it 出差去 and not 去出差?
Parse JSON in LWC
In a list with unique pairs A, B, how can I sort them so that the last B is the first A in the next pair?
Implementation of the Jacobi Symbol in C
In Street Fighter, what does the M stand for in M Bison?
How to style Excel cell using Open XML
How do I format a Microsoft JSON date?How to Deserialize XML documentHow do you create a dropdownlist from an enum in ASP.NET MVC?How do you handle multiple submit buttons in ASP.NET MVC Framework?How to add reference to System.Web.Optimization for MVC-3-converted-to-4 appHow to add Web API to an existing ASP.NET MVC 4 Web Application project?How to make custom error pages work in ASP.NET MVC 4Download report in excel format using SSRS in ASP.NET MVCvs2012 MVC4 download excel file works on desktop but not mobile “could not read the document”Export data with images to Excel
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty height:90px;width:728px;box-sizing:border-box;
1
I have a requirement for export data to excel using open Xml plug in. And I want to style excel head boarder colour. I tried different method to achieve my requirement. Its done, But I couldn't not style particular cells (or columns). I have to add boarder and background to my excel cell.
My Code as following
public ActionResult exxx()
MemoryStream ms = new MemoryStream();
SpreadsheetDocument xl = SpreadsheetDocument.Create(ms, SpreadsheetDocumentType.Workbook);
WorkbookPart wbp = xl.AddWorkbookPart();
WorksheetPart wsp = wbp.AddNewPart<WorksheetPart>();
Workbook wb = new Workbook();
FileVersion fv = new FileVersion();
fv.ApplicationName = "Microsoft Office Excel";
Worksheet ws = new Worksheet();
//First cell
SheetData sd = new SheetData();
Row r1 = new Row() RowIndex = (UInt32Value)1u ;
Cell c1 = new Cell();
c1.DataType = CellValues.String;
c1.CellValue = new CellValue("some value");
r1.Append(c1);
// Second cell
Cell c2 = new Cell();
c2.CellReference = "C1";
c2.DataType = CellValues.String;
c2.CellValue = new CellValue("other value");
r1.Append(c2);
sd.Append(r1);
//third cell
Row r2 = new Row() RowIndex = (UInt32Value)2u ;
Cell c3 = new Cell();
c3.DataType = CellValues.String;
c3.CellValue = new CellValue("some string");
Cell c4 = new Cell();
c4.DataType = CellValues.String;
c4.CellValue = new CellValue("some car");
r2.Append(c3);
r2.Append(c4);
sd.Append(r2);
ws.Append(sd);
wsp.Worksheet = ws;
wsp.Worksheet.Save();
Sheets sheets = new Sheets();
Sheet sheet = new Sheet();
sheet.Name = "first sheet";
sheet.SheetId = 1;
sheet.Id = wbp.GetIdOfPart(wsp);
sheets.Append(sheet);
wb.Append(fv);
wb.Append(sheets);
xl.WorkbookPart.Workbook = wb;
xl.WorkbookPart.Workbook.Save();
xl.Close();
string fileName = "getdata.xlsx";
Response.Clear();
byte[] dt = ms.ToArray();
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("Content-Disposition", string.Format("attachment; filename=0", fileName));
Response.BinaryWrite(dt);
Response.End();
return File(dt, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "getdata.xlsx");
My expected result:
enter image description here
asp.net asp.net-mvc asp.net-mvc-4 asp.net-core
asked Mar 23 at 8:02
Brian lukeBrian luke
2227
add a comment |
1
I have a requirement for export data to excel using open Xml plug in. And I want to style excel head boarder colour. I tried different method to achieve my requirement. Its done, But I couldn't not style particular cells (or columns). I have to add boarder and background to my excel cell.
My Code as following
public ActionResult exxx()
MemoryStream ms = new MemoryStream();
SpreadsheetDocument xl = SpreadsheetDocument.Create(ms, SpreadsheetDocumentType.Workbook);
WorkbookPart wbp = xl.AddWorkbookPart();
WorksheetPart wsp = wbp.AddNewPart<WorksheetPart>();
Workbook wb = new Workbook();
FileVersion fv = new FileVersion();
fv.ApplicationName = "Microsoft Office Excel";
Worksheet ws = new Worksheet();
//First cell
SheetData sd = new SheetData();
Row r1 = new Row() RowIndex = (UInt32Value)1u ;
Cell c1 = new Cell();
c1.DataType = CellValues.String;
c1.CellValue = new CellValue("some value");
r1.Append(c1);
// Second cell
Cell c2 = new Cell();
c2.CellReference = "C1";
c2.DataType = CellValues.String;
c2.CellValue = new CellValue("other value");
r1.Append(c2);
sd.Append(r1);
//third cell
Row r2 = new Row() RowIndex = (UInt32Value)2u ;
Cell c3 = new Cell();
c3.DataType = CellValues.String;
c3.CellValue = new CellValue("some string");
Cell c4 = new Cell();
c4.DataType = CellValues.String;
c4.CellValue = new CellValue("some car");
r2.Append(c3);
r2.Append(c4);
sd.Append(r2);
ws.Append(sd);
wsp.Worksheet = ws;
wsp.Worksheet.Save();
Sheets sheets = new Sheets();
Sheet sheet = new Sheet();
sheet.Name = "first sheet";
sheet.SheetId = 1;
sheet.Id = wbp.GetIdOfPart(wsp);
sheets.Append(sheet);
wb.Append(fv);
wb.Append(sheets);
xl.WorkbookPart.Workbook = wb;
xl.WorkbookPart.Workbook.Save();
xl.Close();
string fileName = "getdata.xlsx";
Response.Clear();
byte[] dt = ms.ToArray();
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("Content-Disposition", string.Format("attachment; filename=0", fileName));
Response.BinaryWrite(dt);
Response.End();
return File(dt, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "getdata.xlsx");
My expected result:
enter image description here
asp.net asp.net-mvc asp.net-mvc-4 asp.net-core
asked Mar 23 at 8:02
Brian lukeBrian luke
2227
I need background colour and boarder for each column header.
– Brian luke
Mar 25 at 6:15
add a comment |
1
1
1
I have a requirement for export data to excel using open Xml plug in. And I want to style excel head boarder colour. I tried different method to achieve my requirement. Its done, But I couldn't not style particular cells (or columns). I have to add boarder and background to my excel cell.
My Code as following
public ActionResult exxx()
MemoryStream ms = new MemoryStream();
SpreadsheetDocument xl = SpreadsheetDocument.Create(ms, SpreadsheetDocumentType.Workbook);
WorkbookPart wbp = xl.AddWorkbookPart();
WorksheetPart wsp = wbp.AddNewPart<WorksheetPart>();
Workbook wb = new Workbook();
FileVersion fv = new FileVersion();
fv.ApplicationName = "Microsoft Office Excel";
Worksheet ws = new Worksheet();
//First cell
SheetData sd = new SheetData();
Row r1 = new Row() RowIndex = (UInt32Value)1u ;
Cell c1 = new Cell();
c1.DataType = CellValues.String;
c1.CellValue = new CellValue("some value");
r1.Append(c1);
// Second cell
Cell c2 = new Cell();
c2.CellReference = "C1";
c2.DataType = CellValues.String;
c2.CellValue = new CellValue("other value");
r1.Append(c2);
sd.Append(r1);
//third cell
Row r2 = new Row() RowIndex = (UInt32Value)2u ;
Cell c3 = new Cell();
c3.DataType = CellValues.String;
c3.CellValue = new CellValue("some string");
Cell c4 = new Cell();
c4.DataType = CellValues.String;
c4.CellValue = new CellValue("some car");
r2.Append(c3);
r2.Append(c4);
sd.Append(r2);
ws.Append(sd);
wsp.Worksheet = ws;
wsp.Worksheet.Save();
Sheets sheets = new Sheets();
Sheet sheet = new Sheet();
sheet.Name = "first sheet";
sheet.SheetId = 1;
sheet.Id = wbp.GetIdOfPart(wsp);
sheets.Append(sheet);
wb.Append(fv);
wb.Append(sheets);
xl.WorkbookPart.Workbook = wb;
xl.WorkbookPart.Workbook.Save();
xl.Close();
string fileName = "getdata.xlsx";
Response.Clear();
byte[] dt = ms.ToArray();
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("Content-Disposition", string.Format("attachment; filename=0", fileName));
Response.BinaryWrite(dt);
Response.End();
return File(dt, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "getdata.xlsx");
My expected result:
enter image description here
asp.net asp.net-mvc asp.net-mvc-4 asp.net-core
asked Mar 23 at 8:02
Brian lukeBrian luke
2227
I have a requirement for export data to excel using open Xml plug in. And I want to style excel head boarder colour. I tried different method to achieve my requirement. Its done, But I couldn't not style particular cells (or columns). I have to add boarder and background to my excel cell.
My Code as following
public ActionResult exxx()
MemoryStream ms = new MemoryStream();
SpreadsheetDocument xl = SpreadsheetDocument.Create(ms, SpreadsheetDocumentType.Workbook);
WorkbookPart wbp = xl.AddWorkbookPart();
WorksheetPart wsp = wbp.AddNewPart<WorksheetPart>();
Workbook wb = new Workbook();
FileVersion fv = new FileVersion();
fv.ApplicationName = "Microsoft Office Excel";
Worksheet ws = new Worksheet();
//First cell
SheetData sd = new SheetData();
Row r1 = new Row() RowIndex = (UInt32Value)1u ;
Cell c1 = new Cell();
c1.DataType = CellValues.String;
c1.CellValue = new CellValue("some value");
r1.Append(c1);
// Second cell
Cell c2 = new Cell();
c2.CellReference = "C1";
c2.DataType = CellValues.String;
c2.CellValue = new CellValue("other value");
r1.Append(c2);
sd.Append(r1);
//third cell
Row r2 = new Row() RowIndex = (UInt32Value)2u ;
Cell c3 = new Cell();
c3.DataType = CellValues.String;
c3.CellValue = new CellValue("some string");
Cell c4 = new Cell();
c4.DataType = CellValues.String;
c4.CellValue = new CellValue("some car");
r2.Append(c3);
r2.Append(c4);
sd.Append(r2);
ws.Append(sd);
wsp.Worksheet = ws;
wsp.Worksheet.Save();
Sheets sheets = new Sheets();
Sheet sheet = new Sheet();
sheet.Name = "first sheet";
sheet.SheetId = 1;
sheet.Id = wbp.GetIdOfPart(wsp);
sheets.Append(sheet);
wb.Append(fv);
wb.Append(sheets);
xl.WorkbookPart.Workbook = wb;
xl.WorkbookPart.Workbook.Save();
xl.Close();
string fileName = "getdata.xlsx";
Response.Clear();
byte[] dt = ms.ToArray();
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("Content-Disposition", string.Format("attachment; filename=0", fileName));
Response.BinaryWrite(dt);
Response.End();
return File(dt, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "getdata.xlsx");
My expected result:
enter image description here
asp.net asp.net-mvc asp.net-mvc-4 asp.net-core
asp.net asp.net-mvc asp.net-mvc-4 asp.net-core
asked Mar 23 at 8:02
Brian lukeBrian luke
2227
asked Mar 23 at 8:02
Brian lukeBrian luke
2227
edited Mar 25 at 6:14
Brian luke
asked Mar 23 at 8:02
Brian lukeBrian luke
2227
asked Mar 23 at 8:02
Brian lukeBrian luke
2227
asked Mar 23 at 8:02
Brian lukeBrian luke
2227
2227
I need background colour and boarder for each column header.
– Brian luke
Mar 25 at 6:15
add a comment |
I need background colour and boarder for each column header.
– Brian luke
Mar 25 at 6:15
I need background colour and boarder for each column header.
– Brian luke
Mar 25 at 6:15
I need background colour and boarder for each column header.
– Brian luke
Mar 25 at 6:15
add a comment |
1 Answer
1
active
oldest
votes
0
You need to create the style part and reference it by Cell c1 = new Cell() StyleIndex = (UInt32Value)1U ;.
Check
public ActionResult CreateExcel()
MemoryStream ms = new MemoryStream();
SpreadsheetDocument xl = SpreadsheetDocument.Create(ms, SpreadsheetDocumentType.Workbook);
WorkbookPart wbp = xl.AddWorkbookPart();
WorkbookStylesPart workbookStylesPart1 = wbp.AddNewPart<WorkbookStylesPart>("rId3");
GenerateWorkbookStylesPart1Content(workbookStylesPart1);
WorksheetPart wsp = wbp.AddNewPart<WorksheetPart>();
Workbook wb = new Workbook();
FileVersion fv = new FileVersion();
fv.ApplicationName = "Microsoft Office Excel";
Worksheet ws = new Worksheet();
//First cell
SheetData sd = new SheetData();
Row r1 = new Row() RowIndex = (UInt32Value)1u ;
Cell c1 = new Cell() StyleIndex = (UInt32Value)1U ;
c1.DataType = CellValues.String;
c1.CellValue = new CellValue("some value");
r1.Append(c1);
// Second cell
Cell c2 = new Cell() StyleIndex = (UInt32Value)1U ;
c2.CellReference = "C1";
c2.DataType = CellValues.String;
c2.CellValue = new CellValue("other value");
r1.Append(c2);
sd.Append(r1);
//third cell
Row r2 = new Row() RowIndex = (UInt32Value)2u ;
Cell c3 = new Cell();
c3.DataType = CellValues.String;
c3.CellValue = new CellValue("some string");
Cell c4 = new Cell();
c4.DataType = CellValues.String;
c4.CellValue = new CellValue("some car");
r2.Append(c3);
r2.Append(c4);
sd.Append(r2);
ws.Append(sd);
wsp.Worksheet = ws;
wsp.Worksheet.Save();
Sheets sheets = new Sheets();
Sheet sheet = new Sheet();
sheet.Name = "first sheet";
sheet.SheetId = 1;
sheet.Id = wbp.GetIdOfPart(wsp);
sheets.Append(sheet);
wb.Append(fv);
wb.Append(sheets);
xl.WorkbookPart.Workbook = wb;
xl.WorkbookPart.Workbook.Save();
xl.Close();
string fileName = "getdata.xlsx";
Response.Clear();
byte[] dt = ms.ToArray();
//Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
//Response.AddHeader("Content-Disposition", string.Format("attachment; filename=0", fileName));
//Response.BinaryWrite(dt);
//Response.End();
return File(dt, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "getdata.xlsx");
private void GenerateWorkbookStylesPart1Content(WorkbookStylesPart workbookStylesPart1)
Stylesheet stylesheet1 = new Stylesheet() MCAttributes = new MarkupCompatibilityAttributes() Ignorable = "x14ac x16r2" ;
stylesheet1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
stylesheet1.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
stylesheet1.AddNamespaceDeclaration("x16r2", "http://schemas.microsoft.com/office/spreadsheetml/2015/02/main");
Fonts fonts1 = new Fonts() Count = (UInt32Value)2U, KnownFonts = true ;
Font font1 = new Font();
FontSize fontSize1 = new FontSize() Val = 11D ;
Color color1 = new Color() Theme = (UInt32Value)1U ;
FontName fontName1 = new FontName() Val = "Calibri" ;
FontFamilyNumbering fontFamilyNumbering1 = new FontFamilyNumbering() Val = 2 ;
FontScheme fontScheme1 = new FontScheme() Val = FontSchemeValues.Minor ;
font1.Append(fontSize1);
font1.Append(color1);
font1.Append(fontName1);
font1.Append(fontFamilyNumbering1);
font1.Append(fontScheme1);
Font font2 = new Font();
FontSize fontSize2 = new FontSize() Val = 11D ;
Color color2 = new Color() Theme = (UInt32Value)0U ;
FontName fontName2 = new FontName() Val = "Calibri" ;
FontFamilyNumbering fontFamilyNumbering2 = new FontFamilyNumbering() Val = 2 ;
FontScheme fontScheme2 = new FontScheme() Val = FontSchemeValues.Minor ;
font2.Append(fontSize2);
font2.Append(color2);
font2.Append(fontName2);
font2.Append(fontFamilyNumbering2);
font2.Append(fontScheme2);
fonts1.Append(font1);
fonts1.Append(font2);
Fills fills1 = new Fills() Count = (UInt32Value)3U ;
Fill fill1 = new Fill();
PatternFill patternFill1 = new PatternFill() PatternType = PatternValues.None ;
fill1.Append(patternFill1);
Fill fill2 = new Fill();
PatternFill patternFill2 = new PatternFill() PatternType = PatternValues.Gray125 ;
fill2.Append(patternFill2);
Fill fill3 = new Fill();
PatternFill patternFill3 = new PatternFill() PatternType = PatternValues.Solid ;
ForegroundColor foregroundColor1 = new ForegroundColor() Rgb = "FF0070C0" ;
BackgroundColor backgroundColor1 = new BackgroundColor() Indexed = (UInt32Value)64U ;
patternFill3.Append(foregroundColor1);
patternFill3.Append(backgroundColor1);
fill3.Append(patternFill3);
fills1.Append(fill1);
fills1.Append(fill2);
fills1.Append(fill3);
Borders borders1 = new Borders() Count = (UInt32Value)2U ;
Border border1 = new Border();
LeftBorder leftBorder1 = new LeftBorder();
RightBorder rightBorder1 = new RightBorder();
TopBorder topBorder1 = new TopBorder();
BottomBorder bottomBorder1 = new BottomBorder();
DiagonalBorder diagonalBorder1 = new DiagonalBorder();
border1.Append(leftBorder1);
border1.Append(rightBorder1);
border1.Append(topBorder1);
border1.Append(bottomBorder1);
border1.Append(diagonalBorder1);
Border border2 = new Border();
LeftBorder leftBorder2 = new LeftBorder() Style = BorderStyleValues.Double ;
Color color3 = new Color() Auto = true ;
leftBorder2.Append(color3);
RightBorder rightBorder2 = new RightBorder() Style = BorderStyleValues.Double ;
Color color4 = new Color() Auto = true ;
rightBorder2.Append(color4);
TopBorder topBorder2 = new TopBorder() Style = BorderStyleValues.Double ;
Color color5 = new Color() Auto = true ;
topBorder2.Append(color5);
BottomBorder bottomBorder2 = new BottomBorder() Style = BorderStyleValues.Double ;
Color color6 = new Color() Auto = true ;
bottomBorder2.Append(color6);
DiagonalBorder diagonalBorder2 = new DiagonalBorder();
border2.Append(leftBorder2);
border2.Append(rightBorder2);
border2.Append(topBorder2);
border2.Append(bottomBorder2);
border2.Append(diagonalBorder2);
borders1.Append(border1);
borders1.Append(border2);
CellStyleFormats cellStyleFormats1 = new CellStyleFormats() Count = (UInt32Value)1U ;
CellFormat cellFormat1 = new CellFormat() NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U ;
cellStyleFormats1.Append(cellFormat1);
CellFormats cellFormats1 = new CellFormats() Count = (UInt32Value)2U ;
CellFormat cellFormat2 = new CellFormat() NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U ;
CellFormat cellFormat3 = new CellFormat() NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)1U, FillId = (UInt32Value)2U, BorderId = (UInt32Value)1U, FormatId = (UInt32Value)0U, ApplyFont = true, ApplyFill = true, ApplyBorder = true ;
cellFormats1.Append(cellFormat2);
cellFormats1.Append(cellFormat3);
CellStyles cellStyles1 = new CellStyles() Count = (UInt32Value)1U ;
CellStyle cellStyle1 = new CellStyle() Name = "Normal", FormatId = (UInt32Value)0U, BuiltinId = (UInt32Value)0U ;
cellStyles1.Append(cellStyle1);
DifferentialFormats differentialFormats1 = new DifferentialFormats() Count = (UInt32Value)0U ;
TableStyles tableStyles1 = new TableStyles() Count = (UInt32Value)0U, DefaultTableStyle = "TableStyleMedium2", DefaultPivotStyle = "PivotStyleLight16" ;
StylesheetExtensionList stylesheetExtensionList1 = new StylesheetExtensionList();
StylesheetExtension stylesheetExtension1 = new StylesheetExtension() Uri = "EB79DEF2-80B8-43e5-95BD-54CBDDF9020C" ;
stylesheetExtension1.AddNamespaceDeclaration("x14", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/main");
X14.SlicerStyles slicerStyles1 = new X14.SlicerStyles() DefaultSlicerStyle = "SlicerStyleLight1" ;
stylesheetExtension1.Append(slicerStyles1);
StylesheetExtension stylesheetExtension2 = new StylesheetExtension() Uri = "9260A510-F301-46a8-8635-F512D64BE5F5" ;
stylesheetExtension2.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");
X15.TimelineStyles timelineStyles1 = new X15.TimelineStyles() DefaultTimelineStyle = "TimeSlicerStyleLight1" ;
stylesheetExtension2.Append(timelineStyles1);
stylesheetExtensionList1.Append(stylesheetExtension1);
stylesheetExtensionList1.Append(stylesheetExtension2);
stylesheet1.Append(fonts1);
stylesheet1.Append(fills1);
stylesheet1.Append(borders1);
stylesheet1.Append(cellStyleFormats1);
stylesheet1.Append(cellFormats1);
stylesheet1.Append(cellStyles1);
stylesheet1.Append(differentialFormats1);
stylesheet1.Append(tableStyles1);
stylesheet1.Append(stylesheetExtensionList1);
workbookStylesPart1.Stylesheet = stylesheet1;
answered Mar 25 at 6:47
Tao ZhouTao Zhou
8,90331634
1
Thank You Tao Zhou, How can I create another style index in the same page for border with out fill colour. I couldn't understand your style format. And I just flag your comment . Simply notify you(I will undo)
– Brian luke
Mar 25 at 10:27
add a comment |
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55311829%2fhow-to-style-excel-cell-using-open-xml%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
You need to create the style part and reference it by Cell c1 = new Cell() StyleIndex = (UInt32Value)1U ;.
Check
public ActionResult CreateExcel()
MemoryStream ms = new MemoryStream();
SpreadsheetDocument xl = SpreadsheetDocument.Create(ms, SpreadsheetDocumentType.Workbook);
WorkbookPart wbp = xl.AddWorkbookPart();
WorkbookStylesPart workbookStylesPart1 = wbp.AddNewPart<WorkbookStylesPart>("rId3");
GenerateWorkbookStylesPart1Content(workbookStylesPart1);
WorksheetPart wsp = wbp.AddNewPart<WorksheetPart>();
Workbook wb = new Workbook();
FileVersion fv = new FileVersion();
fv.ApplicationName = "Microsoft Office Excel";
Worksheet ws = new Worksheet();
//First cell
SheetData sd = new SheetData();
Row r1 = new Row() RowIndex = (UInt32Value)1u ;
Cell c1 = new Cell() StyleIndex = (UInt32Value)1U ;
c1.DataType = CellValues.String;
c1.CellValue = new CellValue("some value");
r1.Append(c1);
// Second cell
Cell c2 = new Cell() StyleIndex = (UInt32Value)1U ;
c2.CellReference = "C1";
c2.DataType = CellValues.String;
c2.CellValue = new CellValue("other value");
r1.Append(c2);
sd.Append(r1);
//third cell
Row r2 = new Row() RowIndex = (UInt32Value)2u ;
Cell c3 = new Cell();
c3.DataType = CellValues.String;
c3.CellValue = new CellValue("some string");
Cell c4 = new Cell();
c4.DataType = CellValues.String;
c4.CellValue = new CellValue("some car");
r2.Append(c3);
r2.Append(c4);
sd.Append(r2);
ws.Append(sd);
wsp.Worksheet = ws;
wsp.Worksheet.Save();
Sheets sheets = new Sheets();
Sheet sheet = new Sheet();
sheet.Name = "first sheet";
sheet.SheetId = 1;
sheet.Id = wbp.GetIdOfPart(wsp);
sheets.Append(sheet);
wb.Append(fv);
wb.Append(sheets);
xl.WorkbookPart.Workbook = wb;
xl.WorkbookPart.Workbook.Save();
xl.Close();
string fileName = "getdata.xlsx";
Response.Clear();
byte[] dt = ms.ToArray();
//Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
//Response.AddHeader("Content-Disposition", string.Format("attachment; filename=0", fileName));
//Response.BinaryWrite(dt);
//Response.End();
return File(dt, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "getdata.xlsx");
private void GenerateWorkbookStylesPart1Content(WorkbookStylesPart workbookStylesPart1)
Stylesheet stylesheet1 = new Stylesheet() MCAttributes = new MarkupCompatibilityAttributes() Ignorable = "x14ac x16r2" ;
stylesheet1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
stylesheet1.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
stylesheet1.AddNamespaceDeclaration("x16r2", "http://schemas.microsoft.com/office/spreadsheetml/2015/02/main");
Fonts fonts1 = new Fonts() Count = (UInt32Value)2U, KnownFonts = true ;
Font font1 = new Font();
FontSize fontSize1 = new FontSize() Val = 11D ;
Color color1 = new Color() Theme = (UInt32Value)1U ;
FontName fontName1 = new FontName() Val = "Calibri" ;
FontFamilyNumbering fontFamilyNumbering1 = new FontFamilyNumbering() Val = 2 ;
FontScheme fontScheme1 = new FontScheme() Val = FontSchemeValues.Minor ;
font1.Append(fontSize1);
font1.Append(color1);
font1.Append(fontName1);
font1.Append(fontFamilyNumbering1);
font1.Append(fontScheme1);
Font font2 = new Font();
FontSize fontSize2 = new FontSize() Val = 11D ;
Color color2 = new Color() Theme = (UInt32Value)0U ;
FontName fontName2 = new FontName() Val = "Calibri" ;
FontFamilyNumbering fontFamilyNumbering2 = new FontFamilyNumbering() Val = 2 ;
FontScheme fontScheme2 = new FontScheme() Val = FontSchemeValues.Minor ;
font2.Append(fontSize2);
font2.Append(color2);
font2.Append(fontName2);
font2.Append(fontFamilyNumbering2);
font2.Append(fontScheme2);
fonts1.Append(font1);
fonts1.Append(font2);
Fills fills1 = new Fills() Count = (UInt32Value)3U ;
Fill fill1 = new Fill();
PatternFill patternFill1 = new PatternFill() PatternType = PatternValues.None ;
fill1.Append(patternFill1);
Fill fill2 = new Fill();
PatternFill patternFill2 = new PatternFill() PatternType = PatternValues.Gray125 ;
fill2.Append(patternFill2);
Fill fill3 = new Fill();
PatternFill patternFill3 = new PatternFill() PatternType = PatternValues.Solid ;
ForegroundColor foregroundColor1 = new ForegroundColor() Rgb = "FF0070C0" ;
BackgroundColor backgroundColor1 = new BackgroundColor() Indexed = (UInt32Value)64U ;
patternFill3.Append(foregroundColor1);
patternFill3.Append(backgroundColor1);
fill3.Append(patternFill3);
fills1.Append(fill1);
fills1.Append(fill2);
fills1.Append(fill3);
Borders borders1 = new Borders() Count = (UInt32Value)2U ;
Border border1 = new Border();
LeftBorder leftBorder1 = new LeftBorder();
RightBorder rightBorder1 = new RightBorder();
TopBorder topBorder1 = new TopBorder();
BottomBorder bottomBorder1 = new BottomBorder();
DiagonalBorder diagonalBorder1 = new DiagonalBorder();
border1.Append(leftBorder1);
border1.Append(rightBorder1);
border1.Append(topBorder1);
border1.Append(bottomBorder1);
border1.Append(diagonalBorder1);
Border border2 = new Border();
LeftBorder leftBorder2 = new LeftBorder() Style = BorderStyleValues.Double ;
Color color3 = new Color() Auto = true ;
leftBorder2.Append(color3);
RightBorder rightBorder2 = new RightBorder() Style = BorderStyleValues.Double ;
Color color4 = new Color() Auto = true ;
rightBorder2.Append(color4);
TopBorder topBorder2 = new TopBorder() Style = BorderStyleValues.Double ;
Color color5 = new Color() Auto = true ;
topBorder2.Append(color5);
BottomBorder bottomBorder2 = new BottomBorder() Style = BorderStyleValues.Double ;
Color color6 = new Color() Auto = true ;
bottomBorder2.Append(color6);
DiagonalBorder diagonalBorder2 = new DiagonalBorder();
border2.Append(leftBorder2);
border2.Append(rightBorder2);
border2.Append(topBorder2);
border2.Append(bottomBorder2);
border2.Append(diagonalBorder2);
borders1.Append(border1);
borders1.Append(border2);
CellStyleFormats cellStyleFormats1 = new CellStyleFormats() Count = (UInt32Value)1U ;
CellFormat cellFormat1 = new CellFormat() NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U ;
cellStyleFormats1.Append(cellFormat1);
CellFormats cellFormats1 = new CellFormats() Count = (UInt32Value)2U ;
CellFormat cellFormat2 = new CellFormat() NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U ;
CellFormat cellFormat3 = new CellFormat() NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)1U, FillId = (UInt32Value)2U, BorderId = (UInt32Value)1U, FormatId = (UInt32Value)0U, ApplyFont = true, ApplyFill = true, ApplyBorder = true ;
cellFormats1.Append(cellFormat2);
cellFormats1.Append(cellFormat3);
CellStyles cellStyles1 = new CellStyles() Count = (UInt32Value)1U ;
CellStyle cellStyle1 = new CellStyle() Name = "Normal", FormatId = (UInt32Value)0U, BuiltinId = (UInt32Value)0U ;
cellStyles1.Append(cellStyle1);
DifferentialFormats differentialFormats1 = new DifferentialFormats() Count = (UInt32Value)0U ;
TableStyles tableStyles1 = new TableStyles() Count = (UInt32Value)0U, DefaultTableStyle = "TableStyleMedium2", DefaultPivotStyle = "PivotStyleLight16" ;
StylesheetExtensionList stylesheetExtensionList1 = new StylesheetExtensionList();
StylesheetExtension stylesheetExtension1 = new StylesheetExtension() Uri = "EB79DEF2-80B8-43e5-95BD-54CBDDF9020C" ;
stylesheetExtension1.AddNamespaceDeclaration("x14", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/main");
X14.SlicerStyles slicerStyles1 = new X14.SlicerStyles() DefaultSlicerStyle = "SlicerStyleLight1" ;
stylesheetExtension1.Append(slicerStyles1);
StylesheetExtension stylesheetExtension2 = new StylesheetExtension() Uri = "9260A510-F301-46a8-8635-F512D64BE5F5" ;
stylesheetExtension2.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");
X15.TimelineStyles timelineStyles1 = new X15.TimelineStyles() DefaultTimelineStyle = "TimeSlicerStyleLight1" ;
stylesheetExtension2.Append(timelineStyles1);
stylesheetExtensionList1.Append(stylesheetExtension1);
stylesheetExtensionList1.Append(stylesheetExtension2);
stylesheet1.Append(fonts1);
stylesheet1.Append(fills1);
stylesheet1.Append(borders1);
stylesheet1.Append(cellStyleFormats1);
stylesheet1.Append(cellFormats1);
stylesheet1.Append(cellStyles1);
stylesheet1.Append(differentialFormats1);
stylesheet1.Append(tableStyles1);
stylesheet1.Append(stylesheetExtensionList1);
workbookStylesPart1.Stylesheet = stylesheet1;
answered Mar 25 at 6:47
Tao ZhouTao Zhou
8,90331634
1
Thank You Tao Zhou, How can I create another style index in the same page for border with out fill colour. I couldn't understand your style format. And I just flag your comment . Simply notify you(I will undo)
– Brian luke
Mar 25 at 10:27
add a comment |
0
You need to create the style part and reference it by Cell c1 = new Cell() StyleIndex = (UInt32Value)1U ;.
Check
public ActionResult CreateExcel()
MemoryStream ms = new MemoryStream();
SpreadsheetDocument xl = SpreadsheetDocument.Create(ms, SpreadsheetDocumentType.Workbook);
WorkbookPart wbp = xl.AddWorkbookPart();
WorkbookStylesPart workbookStylesPart1 = wbp.AddNewPart<WorkbookStylesPart>("rId3");
GenerateWorkbookStylesPart1Content(workbookStylesPart1);
WorksheetPart wsp = wbp.AddNewPart<WorksheetPart>();
Workbook wb = new Workbook();
FileVersion fv = new FileVersion();
fv.ApplicationName = "Microsoft Office Excel";
Worksheet ws = new Worksheet();
//First cell
SheetData sd = new SheetData();
Row r1 = new Row() RowIndex = (UInt32Value)1u ;
Cell c1 = new Cell() StyleIndex = (UInt32Value)1U ;
c1.DataType = CellValues.String;
c1.CellValue = new CellValue("some value");
r1.Append(c1);
// Second cell
Cell c2 = new Cell() StyleIndex = (UInt32Value)1U ;
c2.CellReference = "C1";
c2.DataType = CellValues.String;
c2.CellValue = new CellValue("other value");
r1.Append(c2);
sd.Append(r1);
//third cell
Row r2 = new Row() RowIndex = (UInt32Value)2u ;
Cell c3 = new Cell();
c3.DataType = CellValues.String;
c3.CellValue = new CellValue("some string");
Cell c4 = new Cell();
c4.DataType = CellValues.String;
c4.CellValue = new CellValue("some car");
r2.Append(c3);
r2.Append(c4);
sd.Append(r2);
ws.Append(sd);
wsp.Worksheet = ws;
wsp.Worksheet.Save();
Sheets sheets = new Sheets();
Sheet sheet = new Sheet();
sheet.Name = "first sheet";
sheet.SheetId = 1;
sheet.Id = wbp.GetIdOfPart(wsp);
sheets.Append(sheet);
wb.Append(fv);
wb.Append(sheets);
xl.WorkbookPart.Workbook = wb;
xl.WorkbookPart.Workbook.Save();
xl.Close();
string fileName = "getdata.xlsx";
Response.Clear();
byte[] dt = ms.ToArray();
//Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
//Response.AddHeader("Content-Disposition", string.Format("attachment; filename=0", fileName));
//Response.BinaryWrite(dt);
//Response.End();
return File(dt, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "getdata.xlsx");
private void GenerateWorkbookStylesPart1Content(WorkbookStylesPart workbookStylesPart1)
Stylesheet stylesheet1 = new Stylesheet() MCAttributes = new MarkupCompatibilityAttributes() Ignorable = "x14ac x16r2" ;
stylesheet1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
stylesheet1.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
stylesheet1.AddNamespaceDeclaration("x16r2", "http://schemas.microsoft.com/office/spreadsheetml/2015/02/main");
Fonts fonts1 = new Fonts() Count = (UInt32Value)2U, KnownFonts = true ;
Font font1 = new Font();
FontSize fontSize1 = new FontSize() Val = 11D ;
Color color1 = new Color() Theme = (UInt32Value)1U ;
FontName fontName1 = new FontName() Val = "Calibri" ;
FontFamilyNumbering fontFamilyNumbering1 = new FontFamilyNumbering() Val = 2 ;
FontScheme fontScheme1 = new FontScheme() Val = FontSchemeValues.Minor ;
font1.Append(fontSize1);
font1.Append(color1);
font1.Append(fontName1);
font1.Append(fontFamilyNumbering1);
font1.Append(fontScheme1);
Font font2 = new Font();
FontSize fontSize2 = new FontSize() Val = 11D ;
Color color2 = new Color() Theme = (UInt32Value)0U ;
FontName fontName2 = new FontName() Val = "Calibri" ;
FontFamilyNumbering fontFamilyNumbering2 = new FontFamilyNumbering() Val = 2 ;
FontScheme fontScheme2 = new FontScheme() Val = FontSchemeValues.Minor ;
font2.Append(fontSize2);
font2.Append(color2);
font2.Append(fontName2);
font2.Append(fontFamilyNumbering2);
font2.Append(fontScheme2);
fonts1.Append(font1);
fonts1.Append(font2);
Fills fills1 = new Fills() Count = (UInt32Value)3U ;
Fill fill1 = new Fill();
PatternFill patternFill1 = new PatternFill() PatternType = PatternValues.None ;
fill1.Append(patternFill1);
Fill fill2 = new Fill();
PatternFill patternFill2 = new PatternFill() PatternType = PatternValues.Gray125 ;
fill2.Append(patternFill2);
Fill fill3 = new Fill();
PatternFill patternFill3 = new PatternFill() PatternType = PatternValues.Solid ;
ForegroundColor foregroundColor1 = new ForegroundColor() Rgb = "FF0070C0" ;
BackgroundColor backgroundColor1 = new BackgroundColor() Indexed = (UInt32Value)64U ;
patternFill3.Append(foregroundColor1);
patternFill3.Append(backgroundColor1);
fill3.Append(patternFill3);
fills1.Append(fill1);
fills1.Append(fill2);
fills1.Append(fill3);
Borders borders1 = new Borders() Count = (UInt32Value)2U ;
Border border1 = new Border();
LeftBorder leftBorder1 = new LeftBorder();
RightBorder rightBorder1 = new RightBorder();
TopBorder topBorder1 = new TopBorder();
BottomBorder bottomBorder1 = new BottomBorder();
DiagonalBorder diagonalBorder1 = new DiagonalBorder();
border1.Append(leftBorder1);
border1.Append(rightBorder1);
border1.Append(topBorder1);
border1.Append(bottomBorder1);
border1.Append(diagonalBorder1);
Border border2 = new Border();
LeftBorder leftBorder2 = new LeftBorder() Style = BorderStyleValues.Double ;
Color color3 = new Color() Auto = true ;
leftBorder2.Append(color3);
RightBorder rightBorder2 = new RightBorder() Style = BorderStyleValues.Double ;
Color color4 = new Color() Auto = true ;
rightBorder2.Append(color4);
TopBorder topBorder2 = new TopBorder() Style = BorderStyleValues.Double ;
Color color5 = new Color() Auto = true ;
topBorder2.Append(color5);
BottomBorder bottomBorder2 = new BottomBorder() Style = BorderStyleValues.Double ;
Color color6 = new Color() Auto = true ;
bottomBorder2.Append(color6);
DiagonalBorder diagonalBorder2 = new DiagonalBorder();
border2.Append(leftBorder2);
border2.Append(rightBorder2);
border2.Append(topBorder2);
border2.Append(bottomBorder2);
border2.Append(diagonalBorder2);
borders1.Append(border1);
borders1.Append(border2);
CellStyleFormats cellStyleFormats1 = new CellStyleFormats() Count = (UInt32Value)1U ;
CellFormat cellFormat1 = new CellFormat() NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U ;
cellStyleFormats1.Append(cellFormat1);
CellFormats cellFormats1 = new CellFormats() Count = (UInt32Value)2U ;
CellFormat cellFormat2 = new CellFormat() NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U ;
CellFormat cellFormat3 = new CellFormat() NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)1U, FillId = (UInt32Value)2U, BorderId = (UInt32Value)1U, FormatId = (UInt32Value)0U, ApplyFont = true, ApplyFill = true, ApplyBorder = true ;
cellFormats1.Append(cellFormat2);
cellFormats1.Append(cellFormat3);
CellStyles cellStyles1 = new CellStyles() Count = (UInt32Value)1U ;
CellStyle cellStyle1 = new CellStyle() Name = "Normal", FormatId = (UInt32Value)0U, BuiltinId = (UInt32Value)0U ;
cellStyles1.Append(cellStyle1);
DifferentialFormats differentialFormats1 = new DifferentialFormats() Count = (UInt32Value)0U ;
TableStyles tableStyles1 = new TableStyles() Count = (UInt32Value)0U, DefaultTableStyle = "TableStyleMedium2", DefaultPivotStyle = "PivotStyleLight16" ;
StylesheetExtensionList stylesheetExtensionList1 = new StylesheetExtensionList();
StylesheetExtension stylesheetExtension1 = new StylesheetExtension() Uri = "EB79DEF2-80B8-43e5-95BD-54CBDDF9020C" ;
stylesheetExtension1.AddNamespaceDeclaration("x14", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/main");
X14.SlicerStyles slicerStyles1 = new X14.SlicerStyles() DefaultSlicerStyle = "SlicerStyleLight1" ;
stylesheetExtension1.Append(slicerStyles1);
StylesheetExtension stylesheetExtension2 = new StylesheetExtension() Uri = "9260A510-F301-46a8-8635-F512D64BE5F5" ;
stylesheetExtension2.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");
X15.TimelineStyles timelineStyles1 = new X15.TimelineStyles() DefaultTimelineStyle = "TimeSlicerStyleLight1" ;
stylesheetExtension2.Append(timelineStyles1);
stylesheetExtensionList1.Append(stylesheetExtension1);
stylesheetExtensionList1.Append(stylesheetExtension2);
stylesheet1.Append(fonts1);
stylesheet1.Append(fills1);
stylesheet1.Append(borders1);
stylesheet1.Append(cellStyleFormats1);
stylesheet1.Append(cellFormats1);
stylesheet1.Append(cellStyles1);
stylesheet1.Append(differentialFormats1);
stylesheet1.Append(tableStyles1);
stylesheet1.Append(stylesheetExtensionList1);
workbookStylesPart1.Stylesheet = stylesheet1;
answered Mar 25 at 6:47
Tao ZhouTao Zhou
8,90331634
1
Thank You Tao Zhou, How can I create another style index in the same page for border with out fill colour. I couldn't understand your style format. And I just flag your comment . Simply notify you(I will undo)
– Brian luke
Mar 25 at 10:27
add a comment |
0
0
0
You need to create the style part and reference it by Cell c1 = new Cell() StyleIndex = (UInt32Value)1U ;.
Check
public ActionResult CreateExcel()
MemoryStream ms = new MemoryStream();
SpreadsheetDocument xl = SpreadsheetDocument.Create(ms, SpreadsheetDocumentType.Workbook);
WorkbookPart wbp = xl.AddWorkbookPart();
WorkbookStylesPart workbookStylesPart1 = wbp.AddNewPart<WorkbookStylesPart>("rId3");
GenerateWorkbookStylesPart1Content(workbookStylesPart1);
WorksheetPart wsp = wbp.AddNewPart<WorksheetPart>();
Workbook wb = new Workbook();
FileVersion fv = new FileVersion();
fv.ApplicationName = "Microsoft Office Excel";
Worksheet ws = new Worksheet();
//First cell
SheetData sd = new SheetData();
Row r1 = new Row() RowIndex = (UInt32Value)1u ;
Cell c1 = new Cell() StyleIndex = (UInt32Value)1U ;
c1.DataType = CellValues.String;
c1.CellValue = new CellValue("some value");
r1.Append(c1);
// Second cell
Cell c2 = new Cell() StyleIndex = (UInt32Value)1U ;
c2.CellReference = "C1";
c2.DataType = CellValues.String;
c2.CellValue = new CellValue("other value");
r1.Append(c2);
sd.Append(r1);
//third cell
Row r2 = new Row() RowIndex = (UInt32Value)2u ;
Cell c3 = new Cell();
c3.DataType = CellValues.String;
c3.CellValue = new CellValue("some string");
Cell c4 = new Cell();
c4.DataType = CellValues.String;
c4.CellValue = new CellValue("some car");
r2.Append(c3);
r2.Append(c4);
sd.Append(r2);
ws.Append(sd);
wsp.Worksheet = ws;
wsp.Worksheet.Save();
Sheets sheets = new Sheets();
Sheet sheet = new Sheet();
sheet.Name = "first sheet";
sheet.SheetId = 1;
sheet.Id = wbp.GetIdOfPart(wsp);
sheets.Append(sheet);
wb.Append(fv);
wb.Append(sheets);
xl.WorkbookPart.Workbook = wb;
xl.WorkbookPart.Workbook.Save();
xl.Close();
string fileName = "getdata.xlsx";
Response.Clear();
byte[] dt = ms.ToArray();
//Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
//Response.AddHeader("Content-Disposition", string.Format("attachment; filename=0", fileName));
//Response.BinaryWrite(dt);
//Response.End();
return File(dt, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "getdata.xlsx");
private void GenerateWorkbookStylesPart1Content(WorkbookStylesPart workbookStylesPart1)
Stylesheet stylesheet1 = new Stylesheet() MCAttributes = new MarkupCompatibilityAttributes() Ignorable = "x14ac x16r2" ;
stylesheet1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
stylesheet1.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
stylesheet1.AddNamespaceDeclaration("x16r2", "http://schemas.microsoft.com/office/spreadsheetml/2015/02/main");
Fonts fonts1 = new Fonts() Count = (UInt32Value)2U, KnownFonts = true ;
Font font1 = new Font();
FontSize fontSize1 = new FontSize() Val = 11D ;
Color color1 = new Color() Theme = (UInt32Value)1U ;
FontName fontName1 = new FontName() Val = "Calibri" ;
FontFamilyNumbering fontFamilyNumbering1 = new FontFamilyNumbering() Val = 2 ;
FontScheme fontScheme1 = new FontScheme() Val = FontSchemeValues.Minor ;
font1.Append(fontSize1);
font1.Append(color1);
font1.Append(fontName1);
font1.Append(fontFamilyNumbering1);
font1.Append(fontScheme1);
Font font2 = new Font();
FontSize fontSize2 = new FontSize() Val = 11D ;
Color color2 = new Color() Theme = (UInt32Value)0U ;
FontName fontName2 = new FontName() Val = "Calibri" ;
FontFamilyNumbering fontFamilyNumbering2 = new FontFamilyNumbering() Val = 2 ;
FontScheme fontScheme2 = new FontScheme() Val = FontSchemeValues.Minor ;
font2.Append(fontSize2);
font2.Append(color2);
font2.Append(fontName2);
font2.Append(fontFamilyNumbering2);
font2.Append(fontScheme2);
fonts1.Append(font1);
fonts1.Append(font2);
Fills fills1 = new Fills() Count = (UInt32Value)3U ;
Fill fill1 = new Fill();
PatternFill patternFill1 = new PatternFill() PatternType = PatternValues.None ;
fill1.Append(patternFill1);
Fill fill2 = new Fill();
PatternFill patternFill2 = new PatternFill() PatternType = PatternValues.Gray125 ;
fill2.Append(patternFill2);
Fill fill3 = new Fill();
PatternFill patternFill3 = new PatternFill() PatternType = PatternValues.Solid ;
ForegroundColor foregroundColor1 = new ForegroundColor() Rgb = "FF0070C0" ;
BackgroundColor backgroundColor1 = new BackgroundColor() Indexed = (UInt32Value)64U ;
patternFill3.Append(foregroundColor1);
patternFill3.Append(backgroundColor1);
fill3.Append(patternFill3);
fills1.Append(fill1);
fills1.Append(fill2);
fills1.Append(fill3);
Borders borders1 = new Borders() Count = (UInt32Value)2U ;
Border border1 = new Border();
LeftBorder leftBorder1 = new LeftBorder();
RightBorder rightBorder1 = new RightBorder();
TopBorder topBorder1 = new TopBorder();
BottomBorder bottomBorder1 = new BottomBorder();
DiagonalBorder diagonalBorder1 = new DiagonalBorder();
border1.Append(leftBorder1);
border1.Append(rightBorder1);
border1.Append(topBorder1);
border1.Append(bottomBorder1);
border1.Append(diagonalBorder1);
Border border2 = new Border();
LeftBorder leftBorder2 = new LeftBorder() Style = BorderStyleValues.Double ;
Color color3 = new Color() Auto = true ;
leftBorder2.Append(color3);
RightBorder rightBorder2 = new RightBorder() Style = BorderStyleValues.Double ;
Color color4 = new Color() Auto = true ;
rightBorder2.Append(color4);
TopBorder topBorder2 = new TopBorder() Style = BorderStyleValues.Double ;
Color color5 = new Color() Auto = true ;
topBorder2.Append(color5);
BottomBorder bottomBorder2 = new BottomBorder() Style = BorderStyleValues.Double ;
Color color6 = new Color() Auto = true ;
bottomBorder2.Append(color6);
DiagonalBorder diagonalBorder2 = new DiagonalBorder();
border2.Append(leftBorder2);
border2.Append(rightBorder2);
border2.Append(topBorder2);
border2.Append(bottomBorder2);
border2.Append(diagonalBorder2);
borders1.Append(border1);
borders1.Append(border2);
CellStyleFormats cellStyleFormats1 = new CellStyleFormats() Count = (UInt32Value)1U ;
CellFormat cellFormat1 = new CellFormat() NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U ;
cellStyleFormats1.Append(cellFormat1);
CellFormats cellFormats1 = new CellFormats() Count = (UInt32Value)2U ;
CellFormat cellFormat2 = new CellFormat() NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U ;
CellFormat cellFormat3 = new CellFormat() NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)1U, FillId = (UInt32Value)2U, BorderId = (UInt32Value)1U, FormatId = (UInt32Value)0U, ApplyFont = true, ApplyFill = true, ApplyBorder = true ;
cellFormats1.Append(cellFormat2);
cellFormats1.Append(cellFormat3);
CellStyles cellStyles1 = new CellStyles() Count = (UInt32Value)1U ;
CellStyle cellStyle1 = new CellStyle() Name = "Normal", FormatId = (UInt32Value)0U, BuiltinId = (UInt32Value)0U ;
cellStyles1.Append(cellStyle1);
DifferentialFormats differentialFormats1 = new DifferentialFormats() Count = (UInt32Value)0U ;
TableStyles tableStyles1 = new TableStyles() Count = (UInt32Value)0U, DefaultTableStyle = "TableStyleMedium2", DefaultPivotStyle = "PivotStyleLight16" ;
StylesheetExtensionList stylesheetExtensionList1 = new StylesheetExtensionList();
StylesheetExtension stylesheetExtension1 = new StylesheetExtension() Uri = "EB79DEF2-80B8-43e5-95BD-54CBDDF9020C" ;
stylesheetExtension1.AddNamespaceDeclaration("x14", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/main");
X14.SlicerStyles slicerStyles1 = new X14.SlicerStyles() DefaultSlicerStyle = "SlicerStyleLight1" ;
stylesheetExtension1.Append(slicerStyles1);
StylesheetExtension stylesheetExtension2 = new StylesheetExtension() Uri = "9260A510-F301-46a8-8635-F512D64BE5F5" ;
stylesheetExtension2.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");
X15.TimelineStyles timelineStyles1 = new X15.TimelineStyles() DefaultTimelineStyle = "TimeSlicerStyleLight1" ;
stylesheetExtension2.Append(timelineStyles1);
stylesheetExtensionList1.Append(stylesheetExtension1);
stylesheetExtensionList1.Append(stylesheetExtension2);
stylesheet1.Append(fonts1);
stylesheet1.Append(fills1);
stylesheet1.Append(borders1);
stylesheet1.Append(cellStyleFormats1);
stylesheet1.Append(cellFormats1);
stylesheet1.Append(cellStyles1);
stylesheet1.Append(differentialFormats1);
stylesheet1.Append(tableStyles1);
stylesheet1.Append(stylesheetExtensionList1);
workbookStylesPart1.Stylesheet = stylesheet1;
answered Mar 25 at 6:47
Tao ZhouTao Zhou
8,90331634
You need to create the style part and reference it by Cell c1 = new Cell() StyleIndex = (UInt32Value)1U ;.
Check
public ActionResult CreateExcel()
MemoryStream ms = new MemoryStream();
SpreadsheetDocument xl = SpreadsheetDocument.Create(ms, SpreadsheetDocumentType.Workbook);
WorkbookPart wbp = xl.AddWorkbookPart();
WorkbookStylesPart workbookStylesPart1 = wbp.AddNewPart<WorkbookStylesPart>("rId3");
GenerateWorkbookStylesPart1Content(workbookStylesPart1);
WorksheetPart wsp = wbp.AddNewPart<WorksheetPart>();
Workbook wb = new Workbook();
FileVersion fv = new FileVersion();
fv.ApplicationName = "Microsoft Office Excel";
Worksheet ws = new Worksheet();
//First cell
SheetData sd = new SheetData();
Row r1 = new Row() RowIndex = (UInt32Value)1u ;
Cell c1 = new Cell() StyleIndex = (UInt32Value)1U ;
c1.DataType = CellValues.String;
c1.CellValue = new CellValue("some value");
r1.Append(c1);
// Second cell
Cell c2 = new Cell() StyleIndex = (UInt32Value)1U ;
c2.CellReference = "C1";
c2.DataType = CellValues.String;
c2.CellValue = new CellValue("other value");
r1.Append(c2);
sd.Append(r1);
//third cell
Row r2 = new Row() RowIndex = (UInt32Value)2u ;
Cell c3 = new Cell();
c3.DataType = CellValues.String;
c3.CellValue = new CellValue("some string");
Cell c4 = new Cell();
c4.DataType = CellValues.String;
c4.CellValue = new CellValue("some car");
r2.Append(c3);
r2.Append(c4);
sd.Append(r2);
ws.Append(sd);
wsp.Worksheet = ws;
wsp.Worksheet.Save();
Sheets sheets = new Sheets();
Sheet sheet = new Sheet();
sheet.Name = "first sheet";
sheet.SheetId = 1;
sheet.Id = wbp.GetIdOfPart(wsp);
sheets.Append(sheet);
wb.Append(fv);
wb.Append(sheets);
xl.WorkbookPart.Workbook = wb;
xl.WorkbookPart.Workbook.Save();
xl.Close();
string fileName = "getdata.xlsx";
Response.Clear();
byte[] dt = ms.ToArray();
//Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
//Response.AddHeader("Content-Disposition", string.Format("attachment; filename=0", fileName));
//Response.BinaryWrite(dt);
//Response.End();
return File(dt, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "getdata.xlsx");
private void GenerateWorkbookStylesPart1Content(WorkbookStylesPart workbookStylesPart1)
Stylesheet stylesheet1 = new Stylesheet() MCAttributes = new MarkupCompatibilityAttributes() Ignorable = "x14ac x16r2" ;
stylesheet1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
stylesheet1.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
stylesheet1.AddNamespaceDeclaration("x16r2", "http://schemas.microsoft.com/office/spreadsheetml/2015/02/main");
Fonts fonts1 = new Fonts() Count = (UInt32Value)2U, KnownFonts = true ;
Font font1 = new Font();
FontSize fontSize1 = new FontSize() Val = 11D ;
Color color1 = new Color() Theme = (UInt32Value)1U ;
FontName fontName1 = new FontName() Val = "Calibri" ;
FontFamilyNumbering fontFamilyNumbering1 = new FontFamilyNumbering() Val = 2 ;
FontScheme fontScheme1 = new FontScheme() Val = FontSchemeValues.Minor ;
font1.Append(fontSize1);
font1.Append(color1);
font1.Append(fontName1);
font1.Append(fontFamilyNumbering1);
font1.Append(fontScheme1);
Font font2 = new Font();
FontSize fontSize2 = new FontSize() Val = 11D ;
Color color2 = new Color() Theme = (UInt32Value)0U ;
FontName fontName2 = new FontName() Val = "Calibri" ;
FontFamilyNumbering fontFamilyNumbering2 = new FontFamilyNumbering() Val = 2 ;
FontScheme fontScheme2 = new FontScheme() Val = FontSchemeValues.Minor ;
font2.Append(fontSize2);
font2.Append(color2);
font2.Append(fontName2);
font2.Append(fontFamilyNumbering2);
font2.Append(fontScheme2);
fonts1.Append(font1);
fonts1.Append(font2);
Fills fills1 = new Fills() Count = (UInt32Value)3U ;
Fill fill1 = new Fill();
PatternFill patternFill1 = new PatternFill() PatternType = PatternValues.None ;
fill1.Append(patternFill1);
Fill fill2 = new Fill();
PatternFill patternFill2 = new PatternFill() PatternType = PatternValues.Gray125 ;
fill2.Append(patternFill2);
Fill fill3 = new Fill();
PatternFill patternFill3 = new PatternFill() PatternType = PatternValues.Solid ;
ForegroundColor foregroundColor1 = new ForegroundColor() Rgb = "FF0070C0" ;
BackgroundColor backgroundColor1 = new BackgroundColor() Indexed = (UInt32Value)64U ;
patternFill3.Append(foregroundColor1);
patternFill3.Append(backgroundColor1);
fill3.Append(patternFill3);
fills1.Append(fill1);
fills1.Append(fill2);
fills1.Append(fill3);
Borders borders1 = new Borders() Count = (UInt32Value)2U ;
Border border1 = new Border();
LeftBorder leftBorder1 = new LeftBorder();
RightBorder rightBorder1 = new RightBorder();
TopBorder topBorder1 = new TopBorder();
BottomBorder bottomBorder1 = new BottomBorder();
DiagonalBorder diagonalBorder1 = new DiagonalBorder();
border1.Append(leftBorder1);
border1.Append(rightBorder1);
border1.Append(topBorder1);
border1.Append(bottomBorder1);
border1.Append(diagonalBorder1);
Border border2 = new Border();
LeftBorder leftBorder2 = new LeftBorder() Style = BorderStyleValues.Double ;
Color color3 = new Color() Auto = true ;
leftBorder2.Append(color3);
RightBorder rightBorder2 = new RightBorder() Style = BorderStyleValues.Double ;
Color color4 = new Color() Auto = true ;
rightBorder2.Append(color4);
TopBorder topBorder2 = new TopBorder() Style = BorderStyleValues.Double ;
Color color5 = new Color() Auto = true ;
topBorder2.Append(color5);
BottomBorder bottomBorder2 = new BottomBorder() Style = BorderStyleValues.Double ;
Color color6 = new Color() Auto = true ;
bottomBorder2.Append(color6);
DiagonalBorder diagonalBorder2 = new DiagonalBorder();
border2.Append(leftBorder2);
border2.Append(rightBorder2);
border2.Append(topBorder2);
border2.Append(bottomBorder2);
border2.Append(diagonalBorder2);
borders1.Append(border1);
borders1.Append(border2);
CellStyleFormats cellStyleFormats1 = new CellStyleFormats() Count = (UInt32Value)1U ;
CellFormat cellFormat1 = new CellFormat() NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U ;
cellStyleFormats1.Append(cellFormat1);
CellFormats cellFormats1 = new CellFormats() Count = (UInt32Value)2U ;
CellFormat cellFormat2 = new CellFormat() NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U ;
CellFormat cellFormat3 = new CellFormat() NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)1U, FillId = (UInt32Value)2U, BorderId = (UInt32Value)1U, FormatId = (UInt32Value)0U, ApplyFont = true, ApplyFill = true, ApplyBorder = true ;
cellFormats1.Append(cellFormat2);
cellFormats1.Append(cellFormat3);
CellStyles cellStyles1 = new CellStyles() Count = (UInt32Value)1U ;
CellStyle cellStyle1 = new CellStyle() Name = "Normal", FormatId = (UInt32Value)0U, BuiltinId = (UInt32Value)0U ;
cellStyles1.Append(cellStyle1);
DifferentialFormats differentialFormats1 = new DifferentialFormats() Count = (UInt32Value)0U ;
TableStyles tableStyles1 = new TableStyles() Count = (UInt32Value)0U, DefaultTableStyle = "TableStyleMedium2", DefaultPivotStyle = "PivotStyleLight16" ;
StylesheetExtensionList stylesheetExtensionList1 = new StylesheetExtensionList();
StylesheetExtension stylesheetExtension1 = new StylesheetExtension() Uri = "EB79DEF2-80B8-43e5-95BD-54CBDDF9020C" ;
stylesheetExtension1.AddNamespaceDeclaration("x14", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/main");
X14.SlicerStyles slicerStyles1 = new X14.SlicerStyles() DefaultSlicerStyle = "SlicerStyleLight1" ;
stylesheetExtension1.Append(slicerStyles1);
StylesheetExtension stylesheetExtension2 = new StylesheetExtension() Uri = "9260A510-F301-46a8-8635-F512D64BE5F5" ;
stylesheetExtension2.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");
X15.TimelineStyles timelineStyles1 = new X15.TimelineStyles() DefaultTimelineStyle = "TimeSlicerStyleLight1" ;
stylesheetExtension2.Append(timelineStyles1);
stylesheetExtensionList1.Append(stylesheetExtension1);
stylesheetExtensionList1.Append(stylesheetExtension2);
stylesheet1.Append(fonts1);
stylesheet1.Append(fills1);
stylesheet1.Append(borders1);
stylesheet1.Append(cellStyleFormats1);
stylesheet1.Append(cellFormats1);
stylesheet1.Append(cellStyles1);
stylesheet1.Append(differentialFormats1);
stylesheet1.Append(tableStyles1);
stylesheet1.Append(stylesheetExtensionList1);
workbookStylesPart1.Stylesheet = stylesheet1;
answered Mar 25 at 6:47
Tao ZhouTao Zhou
8,90331634
answered Mar 25 at 6:47
Tao ZhouTao Zhou
8,90331634
answered Mar 25 at 6:47
Tao ZhouTao Zhou
8,90331634
answered Mar 25 at 6:47
Tao ZhouTao Zhou
8,90331634
8,90331634
1
Thank You Tao Zhou, How can I create another style index in the same page for border with out fill colour. I couldn't understand your style format. And I just flag your comment . Simply notify you(I will undo)
– Brian luke
Mar 25 at 10:27
add a comment |
1
Thank You Tao Zhou, How can I create another style index in the same page for border with out fill colour. I couldn't understand your style format. And I just flag your comment . Simply notify you(I will undo)
– Brian luke
Mar 25 at 10:27
1
1
Thank You Tao Zhou, How can I create another style index in the same page for border with out fill colour. I couldn't understand your style format. And I just flag your comment . Simply notify you(I will undo)
– Brian luke
Mar 25 at 10:27
Thank You Tao Zhou, How can I create another style index in the same page for border with out fill colour. I couldn't understand your style format. And I just flag your comment . Simply notify you(I will undo)
– Brian luke
Mar 25 at 10:27
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55311829%2fhow-to-style-excel-cell-using-open-xml%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
I need background colour and boarder for each column header.
– Brian luke
Mar 25 at 6:15