//string[] headers = new string[2] {"Сервер приложений, узел1;Сервер приложений, узел2;Балансировщик сервера приложений;БД, узел1;БД, узел1;Шина, узел1; Шина, узел2;Балансировшик шины",
// "ra-bx07;ra-bx08;ra-vx71;dbperf1;dbperf2;ra-vx42;ra-vx43;ra-vx44"};
/// <summary>
/// Инициализирует объект с указанием результирующего XLSX-файла.
/// </summary>
/// <param name="excelFile">Имя результирующего файла.</param>
/// <exception cref="ArgumentNullException">Исключение возникает, если результирующий файл не задан.</exception>
public ExcelMaster(FileInfo excelFile,JMeterLogDictionary dict)
{
if (excelFile == null) throw new ArgumentNullException("excelFile", "XLSX-файл не указан!");
dictExcel = dict;
_excelFile = new ExcelPackage(excelFile);
_excelFile.Workbook.Properties.Author = AUTHOR;
_excelFile.Workbook.Properties.Title = TITLE;
_excelFile.Workbook.Properties.Company = COMPANY;
styleHHMMSS = _excelFile.Workbook.Styles.CreateNamedStyle("MyTime");
styleHHMMSS.Style.Numberformat.Format = "dd.mm.yy hh:mm:ss";
styleHHMM = _excelFile.Workbook.Styles.CreateNamedStyle("AbsTime");
styleHHMM.Style.Numberformat.Format = "hh:mm:ss";
stylePercent = _excelFile.Workbook.Styles.CreateNamedStyle("MyPercent");
stylePercent.Style.Numberformat.Format = "0.00%";
styleDouble = _excelFile.Workbook.Styles.CreateNamedStyle("MyDouble");
styleDouble.Style.Numberformat.Format = "#.00";
styleHyperLink = _excelFile.Workbook.Styles.CreateNamedStyle("MyHLink");
styleHyperLink.Style.Font.UnderLine = true;
styleHyperLink.Style.Font.Color.SetColor(Color.Blue);
styleBlock = _excelFile.Workbook.Styles.CreateNamedStyle("MyBlock");
styleBlock.Style.Font.Bold = true;
styleBlock.Style.Fill.PatternType = ExcelFillStyle.Solid;
styleBlock.Style.Fill.BackgroundColor.SetColor(Color.Gray);
styleBlock.Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
}
/// <summary>
/// Loads the style XML to memory
/// </summary>
private void LoadFromDocument()
{
//NumberFormats
ExcelNumberFormatXml.AddBuildIn(NameSpaceManager, NumberFormats);
XmlNode numNode = _styleXml.SelectSingleNode(NumberFormatsPath, _nameSpaceManager);
if (numNode != null)
{
foreach (XmlNode n in numNode)
{
ExcelNumberFormatXml nf = new ExcelNumberFormatXml(_nameSpaceManager, n);
NumberFormats.Add(nf.Id, nf);
if (nf.NumFmtId >= NumberFormats.NextId) NumberFormats.NextId=nf.NumFmtId+1;
}
}
//Fonts
XmlNode fontNode = _styleXml.SelectSingleNode(FontsPath, _nameSpaceManager);
foreach (XmlNode n in fontNode)
{
ExcelFontXml f = new ExcelFontXml(_nameSpaceManager, n);
Fonts.Add(f.Id, f);
}
//Fills
XmlNode fillNode = _styleXml.SelectSingleNode(FillsPath, _nameSpaceManager);
foreach (XmlNode n in fillNode)
{
ExcelFillXml f;
if (n.FirstChild != null && n.FirstChild.LocalName == "gradientFill")
{
f = new ExcelGradientFillXml(_nameSpaceManager, n);
}
else
{
f = new ExcelFillXml(_nameSpaceManager, n);
}
Fills.Add(f.Id, f);
}
//Borders
XmlNode borderNode = _styleXml.SelectSingleNode(BordersPath, _nameSpaceManager);
foreach (XmlNode n in borderNode)
{
ExcelBorderXml b = new ExcelBorderXml(_nameSpaceManager, n);
Borders.Add(b.Id, b);
}
//cellStyleXfs
XmlNode styleXfsNode = _styleXml.SelectSingleNode(CellStyleXfsPath, _nameSpaceManager);
if (styleXfsNode != null)
{
foreach (XmlNode n in styleXfsNode)
{
ExcelXfs item = new ExcelXfs(_nameSpaceManager, n, this);
CellStyleXfs.Add(item.Id, item);
}
}
XmlNode styleNode = _styleXml.SelectSingleNode(CellXfsPath, _nameSpaceManager);
for (int i = 0; i < styleNode.ChildNodes.Count; i++)
{
XmlNode n = styleNode.ChildNodes[i];
ExcelXfs item = new ExcelXfs(_nameSpaceManager, n, this);
CellXfs.Add(item.Id, item);
}
//cellStyle
XmlNode namedStyleNode = _styleXml.SelectSingleNode(CellStylesPath, _nameSpaceManager);
if (namedStyleNode != null)
{
foreach (XmlNode n in namedStyleNode)
{
ExcelNamedStyleXml item = new ExcelNamedStyleXml(_nameSpaceManager, n, this);
NamedStyles.Add(item.Name, item);
}
}
//dxfsPath
XmlNode dxfsNode = _styleXml.SelectSingleNode(dxfsPath, _nameSpaceManager);
if (dxfsNode != null)
{
foreach (XmlNode x in dxfsNode)
{
ExcelDxfStyleConditionalFormatting item = new ExcelDxfStyleConditionalFormatting(_nameSpaceManager, x, this);
Dxfs.Add(item.Id, item);
}
}
}
private void AddNamedStyle(int id, XmlNode styleXfsNode,XmlNode cellXfsNode, ExcelNamedStyleXml style)
{
var styleXfs = CellStyleXfs[style.StyleXfId];
styleXfsNode.AppendChild(styleXfs.CreateXmlNode(_styleXml.CreateElement("xf", ExcelPackage.schemaMain), true));
styleXfs.newID = id;
styleXfs.XfId = style.StyleXfId;
var ix = CellXfs.FindIndexByID(styleXfs.Id);
if (ix < 0)
{
cellXfsNode.AppendChild(styleXfs.CreateXmlNode(_styleXml.CreateElement("xf", ExcelPackage.schemaMain)));
}
else
{
if(id<0) CellXfs[ix].XfId = id;
cellXfsNode.AppendChild(CellXfs[ix].CreateXmlNode(_styleXml.CreateElement("xf", ExcelPackage.schemaMain)));
CellXfs[ix].useCnt = 0;
CellXfs[ix].newID = id;
}
if (style.XfId >= 0)
style.XfId = CellXfs[style.XfId].newID;
else
style.XfId = 0;
}
public ExcelNamedStyleXml CreateNamedStyle(string name, ExcelStyle Template)
{
if (_wb.Styles.NamedStyles.ExistsKey(name))
{
throw new Exception(string.Format("Key {0} already exists in collection", name));
}
ExcelNamedStyleXml style;
style = new ExcelNamedStyleXml(NameSpaceManager, this);
int xfIdCopy, positionID;
ExcelStyles styles;
if (Template == null)
{
// style.Style = new ExcelStyle(this, NamedStylePropertyChange, -1, name, 0);
xfIdCopy = 0;
positionID = -1;
styles = this;
}
else
{
if (Template.PositionID < 0 && Template.Styles==this)
{
xfIdCopy = Template.Index;
positionID=Template.PositionID;
styles = this;
//style.Style = new ExcelStyle(this, NamedStylePropertyChange, Template.PositionID, name, Template.Index);
//style.StyleXfId = Template.Index;
}
else
{
xfIdCopy = Template.XfId;
positionID = -1;
styles = Template.Styles;
}
}
//Clone namedstyle
int styleXfId = CloneStyle(styles, xfIdCopy, true);
//Close cells style
CellStyleXfs[styleXfId].XfId = CellStyleXfs.Count-1;
int xfid = CloneStyle(styles, xfIdCopy, false, true); //Always add a new style (We create a new named style here)
CellXfs[xfid].XfId = styleXfId;
style.Style = new ExcelStyle(this, NamedStylePropertyChange, positionID, name, styleXfId);
style.StyleXfId = styleXfId;
style.Name = name;
int ix =_wb.Styles.NamedStyles.Add(style.Name, style);
style.Style.SetIndex(ix);
//style.Style.XfId = ix;
return style;
}
public ExcelRow InsertRow(int rowPosition, ExcelNamedStyleXml namedStyle)
{
return InsertRow(rowPosition, namedStyle.Name);
}
/// <summary>
/// Loads the style XML to memory
/// </summary>
private void LoadFromDocument()
{
//NumberFormats
ExcelNumberFormatXml.AddBuildIn(NameSpaceManager, NumberFormats);
XmlNode numNode = _styleXml.SelectSingleNode(NumberFormatsPath, _nameSpaceManager);
if (numNode != null)
{
foreach (XmlNode n in numNode)
{
ExcelNumberFormatXml nf = new ExcelNumberFormatXml(_nameSpaceManager, n);
NumberFormats.Add(nf.Id, nf);
if (nf.NumFmtId >= NumberFormats.NextId) NumberFormats.NextId=nf.NumFmtId+1;
}
}
//Fonts
XmlNode fontNode = _styleXml.SelectSingleNode(FontsPath, _nameSpaceManager);
foreach (XmlNode n in fontNode)
{
ExcelFontXml f = new ExcelFontXml(_nameSpaceManager, n);
Fonts.Add(f.Id, f);
}
//Fills
XmlNode fillNode = _styleXml.SelectSingleNode(FillsPath, _nameSpaceManager);
foreach (XmlNode n in fillNode)
{
ExcelFillXml f = new ExcelFillXml(_nameSpaceManager, n);
Fills.Add(f.Id, f);
}
//Borders
XmlNode borderNode = _styleXml.SelectSingleNode(BordersPath, _nameSpaceManager);
foreach (XmlNode n in borderNode)
{
ExcelBorderXml b = new ExcelBorderXml(_nameSpaceManager, n);
Borders.Add(b.Id, b);
}
//cellStyleXfs
XmlNode styleXfsNode = _styleXml.SelectSingleNode(CellStyleXfsPath, _nameSpaceManager);
foreach (XmlNode n in styleXfsNode)
{
ExcelXfs item = new ExcelXfs(_nameSpaceManager, n, this);
CellStyleXfs.Add(item.Id, item);
}
XmlNode styleNode = _styleXml.SelectSingleNode(CellXfsPath, _nameSpaceManager);
for (int i = 0; i < styleNode.ChildNodes.Count; i++)
{
XmlNode n = styleNode.ChildNodes[i];
ExcelXfs item = new ExcelXfs(_nameSpaceManager, n, this);
CellXfs.Add(item.Id, item);
}
//cellStyle
XmlNode namedStyleNode = _styleXml.SelectSingleNode(CellStylesPath, _nameSpaceManager);
foreach (XmlNode n in namedStyleNode)
{
ExcelNamedStyleXml item = new ExcelNamedStyleXml(_nameSpaceManager, n, this);
NamedStyles.Add(item.Name, item);
}
}
public ExcelNamedStyleXml CreateNamedStyle(string name, ExcelStyle Template)
{
if (_wb.Styles.NamedStyles.ExistsKey(name))
{
throw new Exception(string.Format("Key {0} already exist in collection", name));
}
ExcelNamedStyleXml style;
style = new ExcelNamedStyleXml(NameSpaceManager, this);
if (Template == null)
{
style.Style = new ExcelStyle(this, NamedStylePropertyChange, -1, name, 0);
}
else
{
style.Style = new ExcelStyle(this, NamedStylePropertyChange, -1, name, Template.Index);
style.StyleXfId = Template.Index;
}
style.Name = name;
int ix =_wb.Styles.NamedStyles.Add(style.Name, style);
style.Style.SetIndex(ix);
//style.Style.XfId = ix;
return style;
}