asp.net OpenXML formatting Excel cells

One, set cell font

The class that stores the font set in stylesheet is fonts, and the class that sets the font is font.

First, define a font set with three fonts:

The code is as follows	Copy Code
Stylesheet. Fonts = new Fonts () { Count = (uint32value) 3U };

Then, define several fonts and add the font to the fonts:

code is as follows	copy code
Fontid starting from 0, here's the fontid=0, Font Fontcalibri = new Font (new FontSize () {Val = 11D}, New FontName () {Val = "Calibri"}, New FontFamily () {Val = 2}, New Fontscheme () {Val = Fontschemevalues.minor}); Stylesheet. Fonts.append (Fontcalibri);

The other 2 fonts are omitted here, and can be modeled after the above font definition ...

Second, set the cell border

The border of a cell is defined in the object borders, the same as setting a font, first creating a Borders object of the specified size, and then adding a specific border to the border set, Borderid starting from 0. The code is as follows:

The code is as follows

Copy Code

Stylesheet. Borders = new Borders () { Count = (uint32value) 2U }; Borderid=0 Border Borderdefault = new Border (new Leftborder (), New Rightborder (), new Topborder () {}, New Bottomborder (), New Diagon Alborder ()); Stylesheet. Borders.append (Borderdefault); Borderid=1 Border bordercontent = 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 () ); Stylesheet. Borders.append (bordercontent);

Three, set the cell fill color

As with the font and border settings above, setting the fill color also requires that you set the fill color first, and then add the specific fill color to the Fill collection. But notice here that the location of the fillid=0 and fillid=1 in fills is the default for the system. The Fillid=0 fill color is the none,fillid=1 fill color is Gray125, but when you need to customize the fill color, you must start with the fillid=2 definition, which means that you need to define the two fill colors when you need to customize them. (It was found through repeated tests, and it was a long toss). The code is as follows:

The code is as follows

Copy Code

fillid,0 always none,1 always gray125, custom starting from fillid = 2 Stylesheet. Fills = new Fills () { Count = (uint32value) 3U }; Fillid=0 Fill Filldefault = new Fill (new Patternfill () {patterntype = Patternvalues.none}); Stylesheet. Fills.append (Filldefault); Fillid=1 Fill Fillgray = new fill (); Patternfill Patternfillgray = new Patternfill () { Patterntype = patternvalues.gray125 }; Fillgray.append (Patternfillgray); Stylesheet. Fills.append (Fillgray); fillid=2 Fill Fillyellow = new fill (); Patternfill Patternfillyellow = new Patternfill (new Foregroundcolor () {RGB = new Hexbinaryvalue () {Value = "FFFFFF00"} }) { Patterntype = Patternvalues.solid }; Fillyellow.append (Patternfillyellow); Stylesheet. Fills.append (Fillyellow); Stylesheet. Borders = new Borders () { Count = (uint32value) 2U };

Iv. definition of Cellformats

As described earlier, after defining CellFormat, add it to the cell format set cellformats.

It is necessary to mention that, whether fonts,borders,fills or Cellformats objects, they are the attributes of stylesheet. If you want to make the fonts, borders, and so on valid, you also need to associate CellFormat with Font,border,fill, which requires the Fontid,borderid and fillid of the above mentioned (the order of IDs is determined by adding to the set).

When you create cells (cell), you can apply cell formatting as long as you set the cell's Styleindex property to CellFormat's Cellformatid. The code is as follows:

Define format

code is as follows

copy code

Stylesheet. Cellformats = new Cellformats (); Stylesheet. Cellformats.count = 2; Styleindex =0u CellFormat Cfdefault = new CellFormat (); cfdefault.alignment = new alignment (); Cfdefault.numberformatid = 0; Cfdefault.fontid = 0; Cfdefault.borderid = 0; Cfdefault.fillid = 0; Cfdefault.applyalignment = true; Cfdefault.applyborder = true; Stylesheet. Cellformats.append (Cfdefault); Styleindex =1u CellFormat cfcontent = new CellFormat (); cfcontent.alignment = new alignment (); Cfcontent.numberformatid = 0; Cfcontent.fontid = 0; Cfcontent.borderid = 1; Cfcontent.fillid = 2; Cfcontent.applyalignment = true; Cfcontent.applyborder = true; Stylesheet. Cellformats.append (cfcontent);

The code to create the cell is as follows:

The code is as follows

Copy Code

Private Cell Createtextcell (object Cellvalue, nullable<uint> styleindex) { Cell cell = new cell (); Cell. DataType = cellvalues.inlinestring; Cell. cellreference = "A1"; if (Styleindex.hasvalue) Cell. Styleindex = Styleindex.value; inlinestring inlinestring = new inlinestring (); Text T = new text (); T.text = Cellvalue.tostring (); Inlinestring.appendchild (t); Cell. AppendChild (inlinestring); return cell; }

Note: This article is mainly a simple introduction to use OPENXML to set common cell formats