Set the Excel cell format in asp.net OpenXML

This article briefly introduces how to set the Excel cell format in asp.net OpenXML. For more information, see.

1. Set the cell font

In Stylesheet, the Class for storing the Font set is Fonts, and the class for setting the Font is Font.

First, define a font set with three types of fonts:

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

 

Then, define several Fonts and add them to Fonts:

The Code is as follows:	Copy code
// FontId starts from 0. Here 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 two fonts are omitted here, which can be defined as the above font...

 

2. Set cell borders

The cell Border is defined in the Borders object. Like setting the font, You can first create a Borders object of the specified size and then add the specific Border to the Border set. The borderId starts 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 DiagonalBorder ()); 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 );

3. Set the fill color of cells

Like setting the font and border, you need to set the fill color set first, and then add the specific fill color to the fill color set. However, it should be noted that the positions of fillid = 0 and fillId = 1 in Fills are default. The fill color of fillId = 0 is None, and the fill color of fillId = 1 is Gray125, but when you need to customize the fill color, it must be defined from fillId = 2, that is to say, You need to define these two fill colors first when you need to customize them. (It was found through repeated tests by myself, and it took a long time ). The Code is as follows:

The Code is as follows:

Copy code

// FillId, 0 always None, 1 always gray125, custom 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 };

 

4. Define CellFormats

As described earlier, after the CellFormat is defined, add it to the CellFormats unit format set.

It should be mentioned that, whether Fonts, Borders, Fills or CellFormats objects, they are all attributes of Stylesheet. To make the Set Font and Border valid, you also need to associate CellFormat with Font, Border, and Fill, borderId and FillId (the order of IDS is determined by the order of adding to the set ).

When creating a Cell, you only need to set the Cell's StyleIndex attribute to CellFormat's CellFormatId to apply the Cell format. The Code is as follows:

// Define the format

The 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 for creating a 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 document describes how to use OpenXML to set common cell formats.