Re: How do you Wrap and set the Background Color of a cell ? [ExcelPackage:66841

Oct 20, 2009 at 6:16 PM
Hi Andy,
I changed Styles/ExcelFills.cs as shown below to get the background color to work

public int Add(string fgColor)
{
XmlNode newNode = (XmlNode)_styleXml.CreateNode(XmlNodeType.Element, "fill", ExcelPackage.schemaMain);
XmlNode patternFillChild = AddNode("patternFill", "patternType", PatternType.solid.ToString());
patternFillChild.AppendChild(AddNode("fgColor", "rgb", fgColor));
newNode.AppendChild(patternFillChild);
string searchString = string.Format("//*[local-name()='fills']", "");
XmlNode node = _styleXml.SelectSingleNode(searchString);
node.AppendChild(newNode);
int count = Convert.ToInt32(node.Attributes["count"].Value);
count++;
node.Attributes["count"].Value = count.ToString();
this.LoadFills();
return (count - 1);
}
/// <summary>
/// Adds a fill to the stylsheet with a solid patern
/// </summary>
/// <param name="fgColor">the colour used in the fill</param>
/// <returns>the index of the fill</returns>
public int Add(PatternType pattern, string bgColor, string fgColor)
{
if ( string.Equals(pattern.ToString(), "solid", StringComparison.InvariantCultureIgnoreCase))
{
throw new InvalidOperationException("A solid pattern cannot be used with a background and foreground colour");
}
XmlNode newNode = (XmlNode)_styleXml.CreateNode(XmlNodeType.Element, "fill", ExcelPackage.schemaMain);
XmlNode patternFillChild = AddNode("patternFill", "patternType", pattern.ToString());
patternFillChild.AppendChild(AddNode("bgColor", "rgb", bgColor));
patternFillChild.AppendChild(AddNode("fgColor", "rgb", fgColor));
newNode.AppendChild(patternFillChild);
string searchString = string.Format("//*[local-name()='fills']", "");
XmlNode node = _styleXml.SelectSingleNode(searchString);
node.AppendChild(newNode);
int count = Convert.ToInt32(node.Attributes["count"].Value);
count++;
node.Attributes["count"].Value = count.ToString();
this.LoadFills();
return (count - 1);
}
To get Wrap (wraptext) to work, I changed Styles/excelCellFormat.cs as shown
/// <summary>
/// Adds a new ExcelCellFormat to the Cell Format (xfs) table.
/// </summary>
/// <param name="numFmtId">The id of the Number Format to use.</param>
/// <param name="fontId">The id of the font to use.</param>
/// <param name="fillId">The id of the fill to use.</param>
/// <param name="borderId">The id of the border to use.</param>
/// <param name="xfId"></param>
/// <param name="applyFont">A value to indicate if the font should be applied.</param>
/// <param name="applyFill">A value to indicate if the fill should be applied.</param>
/// <param name="hAlignment">Horizontal alignment</param>
/// <param name="vAlignment">Vertical alignment</param>
/// <param name="wrapText">Wrap text</param>
/// <returns>the id of the new Cell Format.</returns>
public int Add(int numFmtId, int fontId, int fillId,
int borderId, int xfId, int applyFont,
int applyFill, HorizontalAlignment hAlignment, VerticalAlignment vAlignment, bool wrapText)
{
XmlNode newNode = (XmlNode)_styleXml.CreateNode(XmlNodeType.Element, "xf", ExcelPackage.schemaMain);
newNode.Attributes.Append(_styleXml.CreateAttribute("numFmtId"));
newNode.Attributes["numFmtId"].Value = numFmtId.ToString();
newNode.Attributes.Append(_styleXml.CreateAttribute("fontId"));
newNode.Attributes["fontId"].Value = fontId.ToString();
newNode.Attributes.Append(_styleXml.CreateAttribute("fillId"));
newNode.Attributes["fillId"].Value = fillId.ToString();
newNode.Attributes.Append(_styleXml.CreateAttribute("borderId"));
newNode.Attributes["borderId"].Value = borderId.ToString();
newNode.Attributes.Append(_styleXml.CreateAttribute("xfId"));
newNode.Attributes["xfId"].Value = xfId.ToString();
newNode.Attributes.Append(_styleXml.CreateAttribute("applyFont"));
newNode.Attributes["applyFont"].Value = applyFont.ToString();
newNode.Attributes.Append(_styleXml.CreateAttribute("applyFill"));
newNode.Attributes["applyFill"].Value = applyFill.ToString();
XmlNode newAlignment = (XmlNode)_styleXml.CreateNode(XmlNodeType.Element, "alignment", ExcelPackage.schemaMain);
newAlignment.Attributes.Append(_styleXml.CreateAttribute("horizontal"));
newAlignment.Attributes["horizontal"].Value = hAlignment.ToString();
newAlignment.Attributes.Append(_styleXml.CreateAttribute("vertical"));
newAlignment.Attributes["vertical"].Value = vAlignment.ToString();
newAlignment.Attributes.Append(_styleXml.CreateAttribute("wrapText"));
if (wrapText == true)
newAlignment.Attributes["wrapText"].Value = "true";
else
newAlignment.Attributes["wrapText"].Value = "false";
newNode.AppendChild(newAlignment);
string searchString = string.Format("//*[local-name()='cellXfs']", "");
XmlNode node = _styleXml.SelectSingleNode(searchString);
node.AppendChild(newNode);
int count = Convert.ToInt32(node.Attributes["count"].Value);
count++;
node.Attributes["count"].Value = count.ToString();
this.loadFormats();
return (count - 1);
}
And I think MergeCells code was posted here before . (ExcelWorksheet.cs)
/// <summary>
/// Sets up a region of merged cells.
/// </summary>
/// <param name="startCell">The beginning (top left) of the merged region.</param>
/// <param name="numRows">The number of rows to merge</param>
/// <param name="numCols">The number of columns to merge</param>
public void MergeCells(ExcelCell startCell, int numRows, int numCols)
{
MergeCells(startCell, Cell(startCell.Row + numRows, startCell.Column + numCols));
}
/// <summary>
/// Sets up a region of merged cells.
/// </summary>
/// <param name="startCell">The beginning (top left) cell of the merge</param>
/// <param name="endCell">The end (bottom right) cell of the merge</param>
public void MergeCells(ExcelCell startCell, ExcelCell endCell)
{
XmlNode mergeNode = WorksheetXml.SelectSingleNode("//d:mergeCells", NameSpaceManager);
int mergeCount = 0;
if (mergeNode == null)
{
mergeNode = WorksheetXml.CreateElement("mergeCells", ExcelPackage.schemaMain);
XmlNode prevNode = WorksheetXml.SelectSingleNode("//d:sheetData", NameSpaceManager);
WorksheetXml.DocumentElement.InsertAfter(mergeNode, prevNode);
}
XmlAttribute attr = mergeNode.Attributes["count"];
if (attr == null)
{
attr = WorksheetXml.CreateAttribute("count");
mergeNode.Attributes.Append(attr);
}
else
mergeCount = int.Parse(attr.Value);
// see if we already have this merge section
XmlNode mergeDefNode = mergeNode.SelectSingleNode(string.Format("./d:mergeCell[@ref='{0}:{1}']", startCell.CellAddress, endCell.CellAddress), NameSpaceManager);
if (mergeDefNode == null)
{
mergeDefNode = WorksheetXml.CreateElement("mergeCell", ExcelPackage.schemaMain);
mergeNode.AppendChild(mergeDefNode);
attr = WorksheetXml.CreateAttribute("ref");
mergeDefNode.Attributes.Append(attr);
attr.Value = string.Format("{0}:{1}", startCell.CellAddress, endCell.CellAddress);
mergeCount++;
}
attr = mergeNode.Attributes["count"];
// we already know the attr can't be null since we looked at it earlier
attr.Value = mergeCount.ToString();
}
/// <summary>
/// Sets up a region of merged cells.
/// </summary>
/// <param name="startCell">The beginning (top left) cell of the merge</param>
/// <param name="endCell">The end (bottom right) cell of the merge</param>
/// <param name="style">The name of the style that should be applied to the merged cells</param>
public void MergeCells(ExcelCell startCell, ExcelCell endCell, string style)
{
MergeCells(startCell, endCell);
for (int i = startCell.Row; i <= endCell.Row; i++)
{
for (int j = startCell.Column; j <= endCell.Column; j++)
{
Cell(i, j).Style = style;
}
}
}


-----Original Message-----
From: AndyBrew <notifications@codeplex.com>
To: [email removed]
Sent: Tue, Oct 20, 2009 7:11 am
Subject: Re: How do you Wrap and set the Background Color of a cell ? [ExcelPackage:66841]

From: AndyBrew
Same issue here, just looking at using this for a new project but will definately need to set the background and foreground colours of cells.
So any information on this would be great :)
Thanks in advance
Andy
To add a post to this discussion, reply to this email (ExcelPackage@discussions.codeplex.com)
To start a new discussion for this project, email ExcelPackage@discussions.codeplex.com
You are receiving this email because you subscribed to this discussion on CodePlex. You can unsubscribe on codePlex.com.
Please note: Images and attachments will be removed from emails. Any posts to this discussion will also be available online at codeplex.com