3

I am actually bit confused to say. I googled for applying styles in my spreadsheet i got some functions in which they mention about the font, borders etc which i need but i dont know where should i use or how should i implement. When i tried to implement like cell.StyleIndex=8 // Which is modified as per my need but there is no effect in the cells

can any one help me what can be the issue and where would i done the mistake

Darin Dimitrov
  • 1,023,142
  • 271
  • 3,287
  • 2,928
hkv
  • 163
  • 1
  • 6
  • 15

1 Answers1

4

OpenXml is one of those really complex framework that could use a framework to make certain common tasks easier. I would suggest starting with the OpenXml Productivity Tool (available in the SDK). Create a spreadsheet that has the styles you want, save it, then open it in the tool to view the code necessary to create the style you would like.

Basically, there is a stylesheet section within the workbook that contains the various formats that are available to your document. These formats are sequential and may be accessed via their index (the StyleIndex you mentioned above.

So, it's Friday. Enough talk, let's look at some code: 

// Obtain a handle to the stylesheet
WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();
Stylesheet stylesheet = workbookPart.WorkbookStylesPart.Stylesheet;

// Highlight format
CellFormat highlightPriceFormat = new CellFormat { NumberFormatId = (UInt32Value) 164U, FontId = (UInt32Value) 1U, FillId = (UInt32Value) 2U, BorderId = (UInt32Value) 0U, FormatId = (UInt32Value) 0U, ApplyNumberFormat = true, ApplyFont = true, ApplyProtection = true };
highlightPriceFormat.AppendChild(new Protection { Locked = false });
stylesheet.CellFormats.AppendChild(highlightPriceFormat);

The code above first obtains a handle to the workbook, then the worksheet, and finally the stylesheet for the worksheet. Once obtained, we create a new cell format that is based on the Currency format and highlighted in yellow.

I hope this is enough to get you started. There is information out there, but it is in bits-and-pieces all over the internet. This related question has another great example.

Community
  • 1
  • 1
Bobby D
  • 2,129
  • 14
  • 21
  • thanks for th introductory part now got some idea to do the same thanks a ton. If possible can u tell me how to include & symbol in cell text i tried. cellText="Student && Parents"; and cellText="Student && Parents"; for both these case i did not got the output which i needed. Can u please tell me how to include "&" in the cell – hkv Apr 04 '11 at 07:42
  • When you create the text cell, are you inserting text into the shared strings table? Escaping the ampersand `("&")` should work when you do that.. – Bobby D Apr 04 '11 at 19:51
  • Thanks for your reply what i exactly do is i store the value in the string and if the string has some specified value then i change the text based on my needs and pass the same. Lets say I have something like this switch (cellText) { case "Welcome": cellText = "Welcome one " + " & amp; " + "all"; break;} I try to add as you said but in the spreadsheet i get the output like welcome one & amp; all – hkv Apr 05 '11 at 06:46