int GetBorderIndex(StyleInfo si)
{
StringBuilder sb = new StringBuilder(150);
sb.Append("<border>");
if (si.BStyleLeft == BorderStyleEnum.None &&
si.BStyleRight == BorderStyleEnum.None &&
si.BStyleBottom == BorderStyleEnum.None &&
si.BStyleTop == BorderStyleEnum.None)
{
sb.Append("<left /> <right /> <top /> <bottom /> <diagonal/>");
}
else
{
sb.AppendFormat("<left style=\"{0}\">{1}</left>\r\n<right style=\"{2}\">{3}</right>" +
"\r\n<top style=\"{4}\">{5}</top>\r\n<bottom style=\"{6}\">{7}</bottom>\r\n<diagonal/>",
GetBorderName(si.BStyleLeft, si.BWidthLeft), GetColor(si.BColorLeft),
GetBorderName(si.BStyleRight, si.BWidthRight), GetColor(si.BColorRight),
GetBorderName(si.BStyleTop, si.BWidthTop), GetColor(si.BColorTop),
GetBorderName(si.BStyleBottom, si.BWidthBottom), GetColor(si.BColorBottom));
}
sb.Append("</border>");
//<w:top w:val="dashed" w:sz="24" …/>
int i = _BorderCache.GetIndex(sb.ToString());
return(i);
}
internal ExcelValet()
{
_Sheets = new List <SheetInfo>();
_Grid = null;
_StringCache = new StringCache();
_BorderCache = new StringCache();
_FontCache = new StringCache();
_FillCache = new StringCache();
// work around so that the first 2 fill caches matches what Excel chooses for them
_FillCache.GetIndex("<fill><patternFill patternType=\"none\"/></fill>"); //index 0
_FillCache.GetIndex("<fill><patternFill patternType=\"gray125\"/></fill>"); //index 1
_StyleCache = new StringCache();
_StyleXfsCache = new StringCache();
_DefaultStyle = new StyleInfo(); // use this when no style is specified
GetStyleIndex(_DefaultStyle); // populates the default style entries as 0
ZipWrap.Init(); // intialize the zip utility (doesn't hurt to do if already done)
}
/// <summary>
/// Set the value of a cell
/// </summary>
/// <param name="row"></param>
/// <param name="col"></param>
/// <param name="val"></param>
/// <param name="si"></param>
/// Row 0 is reserved to hold the column width
/// Col 0 is reserved to hold the row height
internal void SetCell(int row, int col, string val, StyleInfo si)
{
if (_Grid == null) // if no sheet yet, create one
{
AddSheet("Sheet1");
}
int gval = _StringCache.GetIndex(val);
if (si == null)
{
si = _DefaultStyle;
}
_Grid[row + 1, col + 1] = new CellData(gval, GetStyleIndex(si));
}
int GetFillIndex(StyleInfo si)
{
string s;
if (si.BackgroundColor.IsEmpty)
{
s = "<fill><patternFill patternType=\"none\"/></fill>";
}
else
{//<fill><patternFill patternType="solid"><fgColor rgb="FFFFFF00"/><bgColor rgb="FFFFFF00"/></patternFill></fill>
s = string.Format("<fill><patternFill patternType=\"solid\">{0}{1}</patternFill></fill>",
GetColor("fgColor", si.BackgroundColor), GetColor("bgColor", si.BackgroundColor));
}
int i = _FillCache.GetIndex(s);
return(i);
}
int GetFontIndex(StyleInfo si)
{
StringBuilder sb = new StringBuilder(150);
sb.Append("<font>");
sb.Append(GetColor(si.Color));
sb.AppendFormat("<sz val=\"{0}\"/> ", si.FontSize);
sb.AppendFormat("<name val=\"{0}\"/> ", si.FontFamily);
if (si.IsFontBold())
{
sb.Append("<b /> ");
}
if (si.FontStyle == FontStyleEnum.Italic)
{
sb.Append("<i /> ");
}
sb.Append("</font>");
int i = _FontCache.GetIndex(sb.ToString());
return(i);
}
int GetStyleIndex(StyleInfo si)
{
int fi = GetFontIndex(si);
int filli = GetFillIndex(si);
int bi = GetBorderIndex(si);
// Do the cell style xfs first (because the xfid is needed in the cell style
StringBuilder sb = new StringBuilder();
sb.AppendFormat("<xf numFmtId=\"0\" fontId=\"{0}\" fillId=\"{1}\" borderId=\"{2}\"" +
" applyFont=\"1\" applyAlignment=\"1\" applyFill=\"1\">", fi, filli, bi);
sb.AppendFormat("<alignment horizontal=\"{0}\" wrapText=\"1\"/>", GetAlignment(si.TextAlign));
sb.Append("</xf>");
int cfxi = _StyleXfsCache.GetIndex(sb.ToString());
sb.Length = 0; // reset and build another one
sb.AppendFormat("<xf numFmtId=\"0\" fontId=\"{0}\" fillId=\"{1}\" borderId=\"{2}\" xfId=\"{3}\"" +
" applyFont=\"1\" applyAlignment=\"1\" applyFill=\"1\">", fi, filli, bi, cfxi);
sb.AppendFormat("<alignment horizontal=\"{0}\" wrapText=\"1\"/>", GetAlignment(si.TextAlign));
sb.Append("</xf>");
int i = _StyleCache.GetIndex(sb.ToString());
return(i);
}
// AJM GJL 130608 Cell Merge
public void SetMerge(String merge, string x)
{
_MergeCells.GetIndex(merge);
}