protected ExcelRow PopulateLine(IReportable item, int row, ExcelNamedStyleXml namedStyle)
{
ExcelRow resultRow = InsertRow(row, namedStyle);
PopulateLineGeneric(item, row);
return(resultRow);
}
protected ExcelRow CreateTableHeader(IReportable item, int row, ExcelNamedStyleXml namedStyle)
{
ExcelRow resultRow = InsertRow(row, namedStyle);
CreateHeaderGeneric(item, row);
return(resultRow);
}
/// <summary>
/// Creates a named style if the given name is not exists on the Excel workbook
/// </summary>
/// <param name="workbook">The workbook</param>
/// <param name="styleName">The name of style</param>
/// <param name="style">The style actions will be applied</param>
/// <returns></returns>
public static ExcelWorkbook CreateNamedStyleIfNotExists(this ExcelWorkbook workbook, string styleName, Action <ExcelStyle> style)
{
if (workbook.Styles.NamedStyles.All(x => x.Name != styleName))
{
ExcelNamedStyleXml errorStyle = workbook.Styles.CreateNamedStyle(styleName);
style.Invoke(errorStyle.Style);
}
return(workbook);
}
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, true, 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);
}
private void CreateStyleHyperLink(ExcelWorksheet sheet, string stylename)
{
try
{
ExcelNamedStyleXml styleHyperlink = sheet.Workbook.Styles.CreateNamedStyle(stylename);
styleHyperlink.Style.Font.UnderLine = true;
styleHyperlink.Style.Font.Color.SetColor(Color.Blue);
}
catch { }
}
/// <summary>
/// Creates a named style on the Excel workbook. If the named style is already exists then throws the <see cref="ArgumentException"/>
/// </summary>
/// <param name="workbook">The workbook</param>
/// <param name="styleName">The name of style</param>
/// <param name="styleAction">The style actions which will be applied</param>
/// <returns></returns>
public static ExcelWorkbook CreateNamedStyle(this ExcelWorkbook workbook, string styleName, Action <ExcelStyle> styleAction)
{
NotNull(styleAction, nameof(styleAction));
ThrowIfConditionMet(workbook.Styles.NamedStyles.Any(x => x.Name == styleName), "The Excel package already has a style with the name of '{0}'", styleName);
ExcelNamedStyleXml errorStyle = workbook.Styles.CreateNamedStyle(styleName);
styleAction.Invoke(errorStyle.Style);
return(workbook);
}
/// <summary>
/// Creates a named style on the Excel workbook. If the named style is already exists then throws the <see cref="ArgumentException"/>
/// </summary>
/// <param name="workbook">The workbook</param>
/// <param name="styleName">The name of style</param>
/// <param name="style">The style actions will be applied</param>
/// <returns></returns>
public static ExcelWorkbook CreateNamedStyle(this ExcelWorkbook workbook, string styleName, Action <ExcelStyle> style)
{
if (workbook.Styles.NamedStyles.Any(x => x.Name == styleName))
{
throw new ArgumentException($"The Excel package already has a style with the name of '{styleName}'");
}
ExcelNamedStyleXml errorStyle = workbook.Styles.CreateNamedStyle(styleName);
style.Invoke(errorStyle.Style);
return(workbook);
}
private void SetStyleName(ExcelRange range, string styleName)
{
// Check to see if this style has been added to the spreadsheet.
if (!m_stylesAddedToSpreadSheet.Contains(styleName))
{
// If the style exists in the defined collection then add it to the spreadsheet.
if (m_styleDictionary.ContainsKey(styleName))
{
ExcelNamedStyleXml newStyle = m_excelPackage.Workbook.Styles.CreateNamedStyle(styleName);
SetStyleFromInformation(newStyle.Style, m_styleDictionary[styleName]);
}
// Record the we have seen the style even if it is not added to the spreadsheet.
m_stylesAddedToSpreadSheet.Add(styleName);
}
range.StyleName = styleName;
}
public ExcelRow InsertRow(int rowPosition, ExcelNamedStyleXml namedStyle)
{
return(InsertRow(rowPosition, namedStyle.Name));
}
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;
}
}
/// <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 ExportToExcel_OnClick(object sender, RoutedEventArgs e)
{
SaveFileDialog saveFileDialog = new SaveFileDialog {
FileName = "Trello.xlsx", Filter = "Excel|*.xlsx"
};
if (saveFileDialog.ShowDialog() == true)
{
using (ExcelPackage package = new ExcelPackage())
{
ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Trello");
ExcelNamedStyleXml hyperlinkStyle = worksheet.Workbook.Styles.CreateNamedStyle("Hyperlink");
hyperlinkStyle.Style.Font.UnderLine = true;
hyperlinkStyle.Style.Font.Color.SetColor(System.Drawing.Color.Blue);
ExcelRange headerCells = worksheet.Cells[1, 1, 1, 6];
headerCells.Style.Font.Bold = true;
worksheet.Cells[1, 1].Value = "Mine";
worksheet.Cells[1, 2].Value = "Title";
worksheet.Cells[1, 3].Value = "Url";
worksheet.Cells[1, 4].Value = "Created";
worksheet.Cells[1, 5].Value = "List";
worksheet.Cells[1, 6].Value = "Assigned To";
int index = 2;
foreach (CardViewModel card in MainWindowViewModel.Sort(MainWindowViewModel.Cards))
{
worksheet.Cells[index, 1].Value = card.AssignedToMe ? "Yes" : "No";
worksheet.Cells[index, 2].Value = card.Title;
worksheet.Cells[index, 3].Formula = string.Format("=hyperlink(\"{0}\")", card.Url);
worksheet.Cells[index, 3].StyleName = hyperlinkStyle.Name;
worksheet.Cells[index, 4].Value = card.CreationDate;
worksheet.Cells[index, 5].Value = card.List;
worksheet.Cells[index, 6].Value = card.AssignedTo;
index++;
}
worksheet.Column(4).Style.Numberformat.Format = "mm/dd/yyyy";
worksheet.Cells.AutoFitColumns(5, 115);
worksheet.Column(3).Width = 28.5;
Stream stream;
try
{
stream = File.Create(saveFileDialog.FileName);
}
catch (IOException)
{
stream = null;
}
if (stream == null)
{
try
{
string suffix = Guid.NewGuid().ToString("N").Substring(0, 5);
string fileName = Path.Combine(Path.GetDirectoryName(saveFileDialog.FileName), string.Format("{0}-{1}{2}",
Path.GetFileNameWithoutExtension(saveFileDialog.FileName),
suffix,
Path.GetExtension(saveFileDialog.FileName)));
stream = File.Create(Path.GetDirectoryName(fileName));
}
catch (IOException)
{
stream = null;
}
}
if (stream != null)
{
package.SaveAs(stream);
stream.Close();
}
}
}
}
internal ExcelNamedStyle(ExcelNamedStyleXml xml)
{
_xml = xml;
}
internal int CloneStyle(ExcelStyles style, int styleId, bool isNamedStyle, bool alwaysAddCellXfs)
{
ExcelXfs xfs;
lock (style)
{
if (isNamedStyle)
{
xfs = style.CellStyleXfs[styleId];
}
else
{
xfs = style.CellXfs[styleId];
}
ExcelXfs newXfs = xfs.Copy(this);
//Number format
if (xfs.NumberFormatId > 0)
{
//rake36: Two problems here...
//rake36: 1. the first time through when format stays equal to String.Empty, it adds a string.empty to the list of Number Formats
//rake36: 2. when adding a second sheet, if the numberformatid == 164, it finds the 164 added by previous sheets but was using the array index
//rake36: for the numberformatid
var format = string.Empty;
foreach (ExcelNumberFormatXml fmt in style.NumberFormats)
{
if (fmt.NumFmtId == xfs.NumberFormatId)
{
format = fmt.Format;
break;
}
}
//rake36: Don't add another format if it's blank
if (!string.IsNullOrEmpty(format))
{
var ix = NumberFormats.FindIndexById(format);
if (ix < 0)
{
var item = new ExcelNumberFormatXml(NameSpaceManager)
{
Format = format, NumFmtId = NumberFormats.NextId++
};
NumberFormats.Add(format, item);
//rake36: Use the just added format id
newXfs.NumberFormatId = item.NumFmtId;
}
else
{
//rake36: Use the format id defined by the index... not the index itself
newXfs.NumberFormatId = NumberFormats[ix].NumFmtId;
}
}
}
//Font
if (xfs.FontId > -1)
{
var ix = Fonts.FindIndexById(xfs.Font.Id);
if (ix < 0)
{
ExcelFontXml item = style.Fonts[xfs.FontId].Copy();
ix = Fonts.Add(xfs.Font.Id, item);
}
newXfs.FontId = ix;
}
//Border
if (xfs.BorderId > -1)
{
var ix = Borders.FindIndexById(xfs.Border.Id);
if (ix < 0)
{
ExcelBorderXml item = style.Borders[xfs.BorderId].Copy();
ix = Borders.Add(xfs.Border.Id, item);
}
newXfs.BorderId = ix;
}
//Fill
if (xfs.FillId > -1)
{
var ix = Fills.FindIndexById(xfs.Fill.Id);
if (ix < 0)
{
ExcelFillXml item = style.Fills[xfs.FillId].Copy();
ix = Fills.Add(xfs.Fill.Id, item);
}
newXfs.FillId = ix;
}
//Named style reference
if (xfs.XfId > 0)
{
var id = style.CellStyleXfs[xfs.XfId].Id;
var newId = CellStyleXfs.FindIndexById(id);
if (newId >= 0)
{
newXfs.XfId = newId;
}
else if (style._wb != _wb && alwaysAddCellXfs == false) //Not the same workbook, copy the namedstyle to the workbook or match the id
{
var nsFind = style.NamedStyles.ToDictionary(d => d.StyleXfId);
if (nsFind.ContainsKey(xfs.XfId))
{
ExcelNamedStyleXml st = nsFind[xfs.XfId];
if (NamedStyles.ExistsKey(st.Name))
{
newXfs.XfId = NamedStyles.FindIndexById(st.Name);
}
else
{
ExcelNamedStyle ns = CreateNamedStyle(st.Name, st.Style);
newXfs.XfId = NamedStyles.Count - 1;
}
}
}
}
int index;
if (isNamedStyle && alwaysAddCellXfs == false)
{
index = CellStyleXfs.Add(newXfs.Id, newXfs);
}
else
{
if (alwaysAddCellXfs)
{
index = CellXfs.Add(newXfs.Id, newXfs);
}
else
{
index = CellXfs.FindIndexById(newXfs.Id);
if (index < 0)
{
index = CellXfs.Add(newXfs.Id, newXfs);
}
}
}
return(index);
}
}
public static void setTestStepFinish(String status, Exception e)
{
try
{
// Test Step Error & ScreenShot Style
testStepWorkSheet.Cells[ts_RowIndex, ts_testStepError_ColIndex].Style.WrapText = true;
testStepWorkSheet.Cells[ts_RowIndex, ts_testStepScreenShot_ColIndex].Style.WrapText = true;
if (status.Equals("Pass"))
{
//Set Test Step Result to Pass
testStepWorkSheet.Cells[ts_RowIndex, ts_testStepResult_ColIndex].Value = status;
testStepWorkSheet.Cells[ts_RowIndex, ts_testStepResult_ColIndex].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
testStepWorkSheet.Cells[ts_RowIndex, ts_testStepResult_ColIndex].Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.Green);
//set Test Step Error to NA
testStepWorkSheet.Cells[ts_RowIndex, ts_testStepError_ColIndex].Value = "NA";
//set Test Step Screenshot location to NA
testStepWorkSheet.Cells[ts_RowIndex, ts_testStepScreenShot_ColIndex].Value = "NA";
if (tc_Hierachy_Counter > 0)
{
subTestFailure = false;
}
}
else
{
// Check for sub test case failure
if (!subTestFailure)
{
//Set Test Step Result to Fail
testStepWorkSheet.Cells[ts_RowIndex, ts_testStepResult_ColIndex].Value = status;
//Set the color of the status
testStepWorkSheet.Cells[ts_RowIndex, ts_testStepResult_ColIndex].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
testStepWorkSheet.Cells[ts_RowIndex, ts_testStepResult_ColIndex].Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.Red);
//Write Error Message in Excel
testStepWorkSheet.Cells[ts_RowIndex, ts_testStepError_ColIndex].Value = e.Message;
//set the width of the error column
testStepWorkSheet.Column(ts_testStepError_ColIndex).Width = 50;
testStepWorkSheet.Cells[ts_RowIndex, ts_testStepError_ColIndex].Style.WrapText = true;
testStepWorkSheet.Row(ts_RowIndex).Height = 50;
//testStepWorkSheet.Column(ts_testStepError_ColIndex).AutoFit();
CommonUtilities.CreateAndSaveScreenShot(resultFolderPath + "\\" + currentTestname + ".png");
Thread.Sleep(3 * 1000);
var uri = new Uri(resultFolderPath + "\\" + currentTestname + ".png");
errorTestResultFilePath = resultFolderPath + "\\" + currentTestname + ".png";
var cell = testStepWorkSheet.Cells[ts_RowIndex, ts_testStepScreenShot_ColIndex];
//cell.Hyperlink = new Uri(Environment.CurrentDirectory + "\\" + currentTestname + ".png");
// cell.Hyperlink = new Uri(Environment.CurrentDirectory + "\\" + currentTestname + ".png");
if (!(namedStyle != null))
{
namedStyle = testStepWorkSheet.Workbook.Styles.CreateNamedStyle("HyperLink");
namedStyle.Style.Font.UnderLine = true;
namedStyle.Style.Font.Color.SetColor(Color.Blue);
}
cell.Hyperlink = new ExcelHyperLink(uri.AbsoluteUri.ToString());
cell.StyleName = "HyperLink";
cell.Value = currentTestname + ".png";
if (tc_Hierachy_Counter > 0)
{
subTestFailure = true;
}
}
}
//Increment the Row Counter
ts_RowIndex++;
}
catch (Exception ex)
{
SAFINCALog.Info("Error Writing data to excel " + ex);
}
}
public void Excel_Create(string SheetName, int col1, int col2, string col3, string URL)
{
//ExcelPackage ExcelPkg = new ExcelPackage();
//ExcelWorksheet wsSheet1 = ExcelPkg.Workbook.Worksheets.Add(SheetName);
//using (ExcelRange Rng = wsSheet1.Cells[row, col])
//{
// Rng.Value = val;
// Rng.Style.Font.Size = 16;
// Rng.Style.Font.Bold = true;
// Rng.Style.Font.Italic = true;
//}
var datatable = new DataTable("tblData");
//Generate titles of datatable
datatable.Columns.AddRange(new[] { new DataColumn("ID", typeof(int)), new DataColumn("Num", typeof(int)), new DataColumn("String", typeof(object)), new DataColumn("Screenshot", typeof(string)) });
bTitle = true;
var row = datatable.NewRow();
row[0] = col1;
row[1] = col2;
row[2] = col3 + " " + Path.GetRandomFileName();
datatable.Rows.Add(row);
//Create a test file
var existingFile = new FileInfo(@"D:\TestFinal.xlsx");
if (existingFile.Exists)
{
existingFile.Delete();
}
using (var pck = new ExcelPackage(existingFile))
{
var worksheet = pck.Workbook.Worksheets.Add(SheetName);
worksheet.Cells.LoadFromDataTable(datatable, true);
Encoding.RegisterProvider(CodePagesEncodingProvider.Instance);
pck.Save();
}
using (var pck = new ExcelPackage(existingFile))
{
var worksheet = pck.Workbook.Worksheets[SheetName];
//Cells only contains references to cells with actual data
var cells = worksheet.Cells;
var dictionary = cells.GroupBy(c => new { c.Start.Row, c.Start.Column })
.ToDictionary(
rcg => new KeyValuePair <int, int>(rcg.Key.Row, rcg.Key.Column),
rcg => cells[rcg.Key.Row, rcg.Key.Column].Value);
foreach (var kvp in dictionary)
{
Console.WriteLine("{{ Row: {0}, Column: {1}, Value: \"{2}\" }}", kvp.Key.Key, kvp.Key.Value, kvp.Value);
}
Console.ReadLine();
string StyleName = "HyperStyle";
ExcelNamedStyleXml HyperStyle = worksheet.Workbook.Styles.CreateNamedStyle(StyleName);
HyperStyle.Style.Font.UnderLine = true;
HyperStyle.Style.Font.Size = 12;
HyperStyle.Style.Font.Color.SetColor(Color.Blue);
//------HYPERLINK to a website.
using (ExcelRange Rng = worksheet.Cells[2, 4, 2, 4])
{
Rng.Hyperlink = new Uri("http://" + URL, UriKind.Absolute);
Rng.Value = "Screenshot";
Rng.StyleName = StyleName;
}
worksheet.Protection.IsProtected = false;
worksheet.Protection.AllowSelectLockedCells = false;
Encoding.RegisterProvider(CodePagesEncodingProvider.Instance);
pck.SaveAs(new FileInfo(@"D:\TestFinal.xlsx"));
}
}
