public void Addresses()
{
var a1 = new ExcelAddress("SalesData!$K$445");
var a2 = new ExcelAddress("SalesData!$K$445:$M$449,SalesData!$N$448:$Q$454,SalesData!$L$458:$O$464");
var a3 = new ExcelAddress("SalesData!$K$445:$L$448");
//var a4 = new ExcelAddress("'[1]Risk]TatTWRForm_TWRWEEKLY20130926090'!$N$527");
var a5 = new ExcelAddress("Table1[[#All],[Title]]");
var a6 = new ExcelAddress("Table1[#All]");
var a7 = new ExcelAddress("Table1[[#Headers],[FirstName]:[LastName]]");
var a8 = new ExcelAddress("Table1[#Headers]");
var a9 = new ExcelAddress("Table2[[#All],[SubTotal]]");
var a10 = new ExcelAddress("Table2[#All]");
var a11 = new ExcelAddress("Table1[[#All],[Freight]]");
var a12 = new ExcelAddress("[1]!Table1[[LastName]:[Name]]");
var a13 = new ExcelAddress("Table1[[#All],[Freight]]");
var a14 = new ExcelAddress("SalesData!$N$5+'test''1'!$J$33");
}
internal static string GetFullAddress(string worksheetName, string address, bool fullRowCol)
{
if (!string.IsNullOrEmpty(worksheetName))
{
worksheetName = worksheetName.Replace("'", "''"); //Makesure addresses handle single qoutes
}
if (address.IndexOf("!") == -1 || address == "#REF!")
{
if (fullRowCol)
{
string[] cells = address.Split(':');
if (cells.Length > 0)
{
address = string.IsNullOrEmpty(worksheetName) ? cells[0] : string.Format("'{0}'!{1}", worksheetName, cells[0]);
if (cells.Length > 1)
{
address += string.Format(":{0}", cells[1]);
}
}
}
else
{
var a = new ExcelAddressBase(address);
if ((a._fromRow == 1 && a._toRow == ExcelPackage.MaxRows) || (a._fromCol == 1 && a._toCol == ExcelPackage.MaxColumns))
{
if (string.IsNullOrEmpty(worksheetName))
{
address = $"'{worksheetName}'!";
}
address += string.Format("{0}{1}:{2}{3}", ExcelAddress.GetColumnLetter(a._fromCol), a._fromRow, ExcelAddress.GetColumnLetter(a._toCol), a._toRow);
}
else
{
address = GetFullAddress(worksheetName, address, true);
}
}
}
return(address);
}
/// <summary>
/// Removes the comment
/// </summary>
/// <param name="comment">The comment to remove</param>
public void Remove(ExcelComment comment)
{
ulong id = ExcelAddress.GetCellID(Worksheet.SheetID, comment.Range._fromRow, comment.Range._fromCol);
//int ix=_comments.IndexOf(id);
int i = -1;
ExcelComment c = null;
if (Worksheet._commentsStore.Exists(comment.Range._fromRow, comment.Range._fromCol, ref i))
{
c = _list[i];
}
if (comment == c)
{
//
comment.TopNode.ParentNode?.RemoveChild(comment.TopNode); //Remove VML
comment._commentHelper.TopNode.ParentNode?.RemoveChild(comment._commentHelper.TopNode); //Remove Comment
if (Worksheet.VmlDrawingsComments._drawings.ContainsKey(id))
{
Worksheet.VmlDrawingsComments._drawings.Delete(id);
}
_list.RemoveAt(i);
Worksheet._commentsStore.Delete(comment.Range._fromRow, comment.Range._fromCol, 1, 1, false); //Issue 15549, Comments should not be shifted
var ci = new CellsStoreEnumerator <int>(Worksheet._commentsStore);
while (ci.Next())
{
if (ci.Value > i)
{
ci.Value -= 1;
}
}
}
else
{
throw (new ArgumentException("Comment does not exist in the worksheet"));
}
}
private bool IsActiveCellInSelection(ExcelAddress ac, ExcelAddress sd)
{
var c = sd.Collide(ac);
if (c == ExcelAddress.eAddressCollition.Equal || c == ExcelAddress.eAddressCollition.Inside)
{
return(true);
}
else
{
if (sd.Addresses != null)
{
foreach (var sds in sd.Addresses)
{
c = sds.Collide(ac);
if (c == ExcelAddress.eAddressCollition.Equal || c == ExcelAddress.eAddressCollition.Inside)
{
return(true);
}
}
}
}
return(false);
}
public void InsertDeleteTest()
{
ExcelWorksheet ws = _pck.Workbook.Worksheets.Add("InsertDelete");
//ws.Cells.Value = 0;
ws.Cells["A1:C5"].Value = 1;
Assert.AreEqual(((object[,])ws.Cells["A1:C5"].Value)[1, 1], 1);
ws.Cells["A1:B3"].Merge = true;
ws.Cells["D3"].Formula = "A2+C5";
ws.InsertRow(2, 1);
ws.Cells["A10:C15"].Value = 1;
ws.Cells["A11:B13"].Merge = true;
ws.DeleteRow(12, 1,true);
ws.Cells["a1:B100"].Style.Locked = false;
ws.Cells["a1:B12"].Style.Hidden = true;
ws.Protection.IsProtected=true;
ws.Protection.SetPassword("Password");
var range = ws.Cells["B2:D100"];
ws.PrinterSettings.PrintArea=null;
ws.PrinterSettings.PrintArea=ws.Cells["B2:D99"];
ws.PrinterSettings.PrintArea = null;
ws.Row(15).PageBreak = true;
ws.Column(3).PageBreak = true;
ws.View.ShowHeaders = false;
ws.View.PageBreakView = true;
ws.Row(200).Height = 50;
ws.Workbook.CalcMode = ExcelCalcMode.Automatic;
Assert.AreEqual(range.Start.Column, 2);
Assert.AreEqual(range.Start.Row, 2);
Assert.AreEqual(range.Start.Address, "B2");
Assert.AreEqual(range.End.Column, 4);
Assert.AreEqual(range.End.Row, 100);
Assert.AreEqual(range.End.Address, "D100");
ExcelAddress addr = new ExcelAddress("B1:D3");
Assert.AreEqual(addr.Start.Column, 2);
Assert.AreEqual(addr.Start.Row, 1);
Assert.AreEqual(addr.End.Column, 4);
Assert.AreEqual(addr.End.Row, 3);
}
/// <summary>
/// Set chart series lengths
/// We implicitly assume they're all the same
/// </summary>
private void SetChartSeriesLengths(ExcelWorksheet ws, int headerRow, int rowCount)
{
foreach (var excelDrawing in ws.Drawings.Where(x => x.GetType().IsSubclassOf(typeof(ExcelChart))))
{
var chart = (ExcelChart)excelDrawing;
foreach (var chartType in chart.PlotArea.ChartTypes)
{
foreach (ExcelChartSerie serie in chartType.Series)
{
var yReferenceAddress = new ExcelAddress(serie.Series);
if (yReferenceAddress.Address == "") continue;
var yAddress = new ExcelAddress(headerRow + 1, yReferenceAddress.Start.Column, headerRow + 1 + rowCount, yReferenceAddress.End.Column);
serie.Series = yAddress.Address;
var xReferenceAddress = new ExcelAddress(serie.XSeries);
if (xReferenceAddress.Address == "") continue;
var xAddress = new ExcelAddress(headerRow + 1, xReferenceAddress.Start.Column, headerRow + 1 + rowCount, xReferenceAddress.End.Column);
serie.XSeries = xAddress.Address;
}
}
}
}
internal static string GetFullAddress(string worksheetName, string address, bool fullRowCol)
{
if (address.IndexOf("!") == -1 || address == "#REF!")
{
if (fullRowCol)
{
string[] cells = address.Split(':');
if (cells.Length > 0)
{
address = string.Format("'{0}'!{1}", worksheetName, cells[0]);
if (cells.Length > 1)
{
address += string.Format(":{0}", cells[1]);
}
}
}
else
{
var a = new ExcelAddressBase(address);
if ((a._fromRow == 1 && a._toRow == ExcelPackage.MaxRows) || (a._fromCol == 1 && a._toCol == ExcelPackage.MaxColumns))
{
address = string.Format("'{0}'!{1}{2}:{3}{4}", worksheetName, ExcelAddress.GetColumnLetter(a._fromCol), a._fromRow, ExcelAddress.GetColumnLetter(a._toCol), a._toRow);
}
else
{
address = GetFullAddress(worksheetName, address, true);
}
}
}
return(address);
}
private static async Task loadScoresSummarySheet(List<ScoresSummary> scoresSummaryList, ExcelWorksheet scoresSummarySheet)
{
Task t = new Task
(
() =>
{
var dataRange = scoresSummarySheet.Cells["A1"].LoadFromCollection(
from s in scoresSummaryList orderby s.AbsoluteDiffCount descending
select s, true, OfficeOpenXml.Table.TableStyles.Medium2);
dataRange.AutoFitColumns();
ExcelAddress percentChange = new ExcelAddress(2, 6, dataRange.End.Row, 6);
var percentChangeRule = scoresSummarySheet.ConditionalFormatting.AddDatabar(percentChange, System.Drawing.Color.Green);
var wsScoresSummPivot = scoresSummarySheet.Workbook.Worksheets.Add("ScoresSummaryPivotTable");
var ptScoresSumm = wsScoresSummPivot.PivotTables.Add(wsScoresSummPivot.Cells["A3"], dataRange, "tblScoresSummary");
var reportFilter = ptScoresSumm.PageFields.Add(ptScoresSumm.Fields["ModelName"]);
var dfPrevCount = ptScoresSumm.DataFields.Add(ptScoresSumm.Fields["PrevCount"]);
var dfCurrentCount = ptScoresSumm.DataFields.Add(ptScoresSumm.Fields["CurrentCount"]);
dfPrevCount.Function = OfficeOpenXml.Table.PivotTable.DataFieldFunctions.Sum;
dfPrevCount.Name = "Sum of PrevCount";
dfCurrentCount.Function = OfficeOpenXml.Table.PivotTable.DataFieldFunctions.Sum;
dfCurrentCount.Name = "Sum of CurrentCount";
var fldScoreRange = ptScoresSumm.Fields["ScoreRange"];
fldScoreRange.Sort = OfficeOpenXml.Table.PivotTable.eSortType.Ascending;
ptScoresSumm.RowFields.Add(fldScoreRange);
ptScoresSumm.DataOnRows = false;
var chrtScoresSumm = wsScoresSummPivot.Drawings.AddChart("chrtScoresSummary", OfficeOpenXml.Drawing.Chart.eChartType.ColumnClustered, ptScoresSumm);
//chrtScoresSumm.PlotArea.Fill.Style = eFillStyle.GradientFill;
chrtScoresSumm.SetPosition(3, 0, 4, 0);
chrtScoresSumm.SetSize(1280, 760);
}
);
t.Start();
await t;
}
private static async Task loadRCSummarySheet(List<RCSummary> rcSummaryList, ExcelWorksheet rcSummarySheet)
{
Task t = new Task
(
() =>
{
var dataRange = rcSummarySheet.Cells["A1"].LoadFromCollection(
from s in rcSummaryList orderby s.AbsoluteDiff descending
select s, true, OfficeOpenXml.Table.TableStyles.Medium2);
rcSummarySheet.Cells.AutoFitColumns();
ExcelAddress percentChange = new ExcelAddress(2, 6, dataRange.End.Row, 6);
var percentChangeRule = rcSummarySheet.ConditionalFormatting.AddDatabar(percentChange, System.Drawing.Color.Green);
}
);
t.Start();
await t;
}
internal void GetDefinedNames()
{
XmlNodeList nl = WorkbookXml.SelectNodes("//d:definedNames/d:definedName", NameSpaceManager);
if (nl != null)
{
foreach (XmlElement elem in nl)
{
try
{
string fullAddress = elem.InnerText;
int localSheetID;
ExcelWorksheet nameWorksheet;
if (!int.TryParse(elem.GetAttribute("localSheetId"), NumberStyles.Number, CultureInfo.InvariantCulture, out localSheetID))
{
localSheetID = -1;
nameWorksheet = null;
}
else
{
nameWorksheet = Worksheets[localSheetID + _package._worksheetAdd];
}
var addressType = ExcelAddressBase.IsValid(fullAddress);
ExcelRangeBase range;
ExcelNamedRange namedRange;
if (fullAddress.IndexOf("[") == 0)
{
int start = fullAddress.IndexOf("[");
int end = fullAddress.IndexOf("]", start);
if (start >= 0 && end >= 0)
{
string externalIndex = fullAddress.Substring(start + 1, end - start - 1);
int index;
if (int.TryParse(externalIndex, NumberStyles.Any, CultureInfo.InvariantCulture, out index))
{
if (index > 0 && index <= _externalReferences.Count)
{
fullAddress = fullAddress.Substring(0, start) + "[" + _externalReferences[index - 1] + "]" + fullAddress.Substring(end + 1);
}
}
}
}
if (addressType == ExcelAddressBase.AddressType.Invalid || addressType == ExcelAddressBase.AddressType.InternalName || addressType == ExcelAddressBase.AddressType.ExternalName || addressType == ExcelAddressBase.AddressType.Formula || addressType == ExcelAddressBase.AddressType.CellAddress) //A value or a formula
{
double value;
range = new ExcelRangeBase(this, nameWorksheet, elem.GetAttribute("name"), true);
if (nameWorksheet == null)
{
namedRange = _names.Add(elem.GetAttribute("name"), range);
}
else
{
namedRange = nameWorksheet.Names.Add(elem.GetAttribute("name"), range);
}
if (ConvertUtil._invariantCompareInfo.IsPrefix(fullAddress, "\"")) //String value
{
namedRange.NameValue = fullAddress.Substring(1, fullAddress.Length - 2);
}
else if (double.TryParse(fullAddress, NumberStyles.Number, CultureInfo.InvariantCulture, out value))
{
namedRange.NameValue = value;
}
else
{
//if (addressType == ExcelAddressBase.AddressType.ExternalAddress || addressType == ExcelAddressBase.AddressType.ExternalName)
//{
// var r = new ExcelAddress(fullAddress);
// namedRange.NameFormula = '\'[' + r._wb
//}
//else
//{
namedRange.NameFormula = fullAddress;
//}
}
}
else
{
ExcelAddress addr = new ExcelAddress(fullAddress, _package, null);
if (localSheetID > -1)
{
if (string.IsNullOrEmpty(addr._ws))
{
namedRange = Worksheets[localSheetID + _package._worksheetAdd].Names.Add(elem.GetAttribute("name"), new ExcelRangeBase(this, Worksheets[localSheetID + _package._worksheetAdd], fullAddress, false));
}
else
{
namedRange = Worksheets[localSheetID + _package._worksheetAdd].Names.Add(elem.GetAttribute("name"), new ExcelRangeBase(this, Worksheets[addr._ws], fullAddress, false));
}
}
else
{
var ws = Worksheets[addr._ws];
namedRange = _names.Add(elem.GetAttribute("name"), new ExcelRangeBase(this, ws, fullAddress, false));
}
}
if (namedRange == null)
{
continue;
}
namedRange.RawFormula = elem.InnerText;
if (elem.GetAttribute("hidden") == "1")
{
namedRange.IsNameHidden = true;
}
if (!string.IsNullOrEmpty(elem.GetAttribute("comment")))
{
namedRange.NameComment = elem.GetAttribute("comment");
}
}
catch (Exception)
{
// Quickfix: Hidden named ranges that references worksheets which do not exist will not be added to all defined names.
}
}
}
}
/// <summary>
/// Removes a shared formula
/// </summary>
private void RemoveFormuls(ExcelAddress address)
{
List<int> removed = new List<int>();
int fFromRow, fFromCol, fToRow, fToCol;
foreach (int index in _worksheet._sharedFormulas.Keys)
{
ExcelWorksheet.Formulas f = _worksheet._sharedFormulas[index];
ExcelCell.GetRowColFromAddress(f.Address, out fFromRow, out fFromCol, out fToRow, out fToCol);
if (((fFromCol >= address.Start.Column && fFromCol <= address.End.Column) ||
(fToCol >= address.Start.Column && fToCol <= address.End.Column)) &&
((fFromRow >= address.Start.Row && fFromRow <= address.End.Row) ||
(fToRow >= address.Start.Row && fToRow <= address.End.Row)))
{
for (int col = fFromCol; col <= fToCol; col++)
{
for (int row = fFromRow; row <= fToRow; row++)
{
_worksheet.Cell(row, col).SharedFormulaID = int.MinValue;
}
}
removed.Add(index);
}
}
foreach (int index in removed)
{
_worksheet._sharedFormulas.Remove(index);
}
}
internal void GetDefinedNames()
{
XmlNodeList nl = WorkbookXml.SelectNodes("//d:definedNames/d:definedName", NameSpaceManager);
if (nl != null)
{
foreach (XmlElement elem in nl)
{
string fullAddress = elem.InnerText;
int localSheetID;
ExcelWorksheet nameWorksheet;
if (!int.TryParse(elem.GetAttribute("localSheetId"), out localSheetID))
{
localSheetID = -1;
nameWorksheet = null;
}
else
{
nameWorksheet = Worksheets[localSheetID + 1];
}
var addressType = ExcelAddressBase.IsValid(fullAddress);
ExcelRangeBase range;
ExcelNamedRange namedRange;
if (fullAddress.IndexOf("[") > -1)
{
int start = fullAddress.IndexOf("[");
int end = fullAddress.IndexOf("]", start);
if (start >= 0 && end >= 0)
{
string externalIndex = fullAddress.Substring(start + 1, end - start - 1);
int index;
if (int.TryParse(externalIndex, out index))
{
if (index > 0 && index <= _externalReferences.Count)
{
fullAddress = fullAddress.Substring(0, start) + "[" + _externalReferences[index - 1] + "]" + fullAddress.Substring(end + 1);
}
}
}
}
if (addressType == ExcelAddressBase.AddressType.Invalid || addressType == ExcelAddressBase.AddressType.InternalName || addressType == ExcelAddressBase.AddressType.ExternalName) //A value or a formula
{
double value;
range = new ExcelRangeBase(this, nameWorksheet, elem.GetAttribute("name"), true);
if (nameWorksheet == null)
{
namedRange = _names.Add(elem.GetAttribute("name"), range);
}
else
{
namedRange = nameWorksheet.Names.Add(elem.GetAttribute("name"), range);
}
if (fullAddress.StartsWith("\"")) //String value
{
namedRange.NameValue = fullAddress.Substring(1, fullAddress.Length - 2);
}
else if (double.TryParse(fullAddress, NumberStyles.Any, CultureInfo.InvariantCulture, out value))
{
namedRange.NameValue = value;
}
else
{
namedRange.NameFormula = fullAddress;
}
}
else
{
ExcelAddress addr = new ExcelAddress(fullAddress);
if (localSheetID > -1)
{
if (string.IsNullOrEmpty(addr._ws))
{
namedRange = Worksheets[localSheetID + 1].Names.Add(elem.GetAttribute("name"), new ExcelRangeBase(this, Worksheets[localSheetID + 1], fullAddress, false));
}
else
{
namedRange = Worksheets[localSheetID + 1].Names.Add(elem.GetAttribute("name"), new ExcelRangeBase(this, Worksheets[addr._ws], fullAddress, false));
}
}
else
{
var ws = Worksheets[addr._ws];
namedRange = _names.Add(elem.GetAttribute("name"), new ExcelRangeBase(this, ws, fullAddress, false));
}
}
if (elem.GetAttribute("hidden") == "1" && namedRange != null)
{
namedRange.IsNameHidden = true;
}
if (!string.IsNullOrEmpty(elem.GetAttribute("comment")))
{
namedRange.NameComment = elem.GetAttribute("comment");
}
}
}
}
/// <summary>
/// Updates the Excel formula so that all the cellAddresses are incremented by the row and column increments
/// if they fall after the afterRow and afterColumn.
/// Supports inserting rows and columns into existing templates.
/// </summary>
/// <param name="originalFormula">The Excel formula</param>
/// <param name="rowIncrement">The amount to increment the cell reference by</param>
/// <param name="colIncrement">The amount to increment the cell reference by</param>
/// <param name="afterRow">Only change rows after this row</param>
/// <param name="afterColumn">Only change columns after this column</param>
/// <param name="currentSheet">The sheet that contains the formula currently being processed.</param>
/// <param name="modifiedSheet">The sheet where cells are being inserted or deleted.</param>
/// <param name="setFixed">Fixed address</param>
/// <param name="updateOnlyFixed">Indicates whether or not to only update fixed (absolute) references. Used for named range formula references.</param>
/// <returns>The updated version of the <paramref name="originalFormula"/>.</returns>
public string UpdateFormulaReferences(string originalFormula, int rowIncrement, int colIncrement, int afterRow, int afterColumn, string currentSheet, string modifiedSheet, bool setFixed = false, bool updateOnlyFixed = false)
{
try
{
var sct = new SourceCodeTokenizer(FunctionNameProvider.Empty, NameValueProvider.Empty);
var tokens = sct.Tokenize(originalFormula);
string formula = string.Empty;
foreach (var t in tokens)
{
if (t.TokenType == TokenType.ExcelAddress)
{
var address = new ExcelAddress(t.Value);
var referencesModifiedWorksheet = (string.IsNullOrEmpty(address._ws) && currentSheet.IsEquivalentTo(modifiedSheet)) || modifiedSheet.IsEquivalentTo(address._ws);
if (!setFixed && (!string.IsNullOrEmpty(address._wb) || !referencesModifiedWorksheet))
{
// This address is in a different worksheet or workbook; no update is required.
formula += address.Address;
continue;
}
// Persist fully-qualified worksheet references.
if (!string.IsNullOrEmpty(address._ws))
{
formula += $"'{address._ws}'!";
}
if (rowIncrement > 0)
{
address = address?.AddRow(afterRow, rowIncrement, setFixed, updateOnlyFixed);
}
else if (rowIncrement < 0)
{
address = address?.DeleteRow(afterRow, -rowIncrement, setFixed, updateOnlyFixed);
}
if (colIncrement > 0)
{
address = address?.AddColumn(afterColumn, colIncrement, setFixed, updateOnlyFixed);
}
else if (colIncrement < 0)
{
address = address?.DeleteColumn(afterColumn, -colIncrement, setFixed, updateOnlyFixed);
}
if (address == null || !address.IsValidRowCol())
{
formula += "#REF!";
}
else
{
// If the address was not shifted, then a.Address will still have the sheet name.
var splitAddress = address.Address.Split('!');
if (splitAddress.Length > 1)
{
formula += splitAddress[1];
}
else
{
formula += address.Address;
}
}
}
else
{
if (t.TokenType == TokenType.StringContent)
{
formula += t.Value.Replace("\"", "\"\"");
}
else
{
formula += t.Value;
}
}
}
return(formula);
}
catch //Invalid formula, skip updating addresses
{
return(originalFormula);
}
}
/// <summary>
/// Parses the style field and applies any settings within it.
/// Style selections should be separated by the | character.
/// </summary>
/// <param name="ws"></param>
/// <param name="styleSettings"></param>
/// <param name="address"></param>
private void ApplyColumnStyleSettings(ExcelWorksheet ws, string styleSettings, ExcelAddress address)
{
if (string.IsNullOrEmpty(styleSettings)) return;
string[] settings = styleSettings.Split("|".ToCharArray());
if(settings.Contains("bold"))
{
ws.Cells[address.Address].Style.Font.Bold = true;
}
}
internal void GetDefinedNames()
{
XmlNodeList nl = WorkbookXml.SelectNodes("//d:definedNames/d:definedName", NameSpaceManager);
if (nl != null)
{
foreach (XmlElement elem in nl)
{
string fullAddress = elem.InnerText;
//int splitPos = fullAddress.LastIndexOf('!');
//string sheet = fullAddress.Substring(0, splitPos);
//string address = fullAddress.Substring(splitPos + 1, fullAddress.Length - splitPos - 1);
//if(sheet[0]=='\'') sheet = sheet.Substring(1, sheet.Length-2); //remove single quotes from sheet
int localSheetID;
if(!int.TryParse(elem.GetAttribute("localSheetId"), out localSheetID))
{
localSheetID = -1;
}
ExcelAddress addr = new ExcelAddress(fullAddress);
ExcelNamedRange namedRange;
if (localSheetID > -1)
{
namedRange = Worksheets[localSheetID + 1].Names.Add(elem.GetAttribute("name"), new ExcelRangeBase(Worksheets[addr._ws], fullAddress));
}
else
{
namedRange = _names.Add(elem.GetAttribute("name"), new ExcelRangeBase(Worksheets[addr._ws], fullAddress));
}
if (elem.GetAttribute("hidden") == "1") namedRange.IsNameHidden = true;
}
}
}
/// <summary>
/// Set the property for an address
/// </summary>
/// <param name="address"></param>
/// <param name="valueMethod"></param>
/// <param name="value"></param>
private static void SetValueAddress(ExcelAddress address, _setValue valueMethod, object value)
{
for (int col = address.Start.Column; col <= address.End.Column; col++)
{
for (int row = address.Start.Row; row <= address.End.Row; row++)
{
valueMethod(value, row, col);
}
}
}
private int appendnetworkCompanyToSheet(ExcelWorksheet worksheet, NetworkCompany networkCompany, int row)
{
string companyName;
if (networkCompany.Company.Name.Length > 30)
{
companyName = networkCompany.Company.Name.Remove(27) + "...";
}
else
{
companyName = networkCompany.Company.Name;
}
var headlinesColor = Color.MediumSeaGreen;
var inprogress = 0;
var finished = 0;
var total = 0;
var saved = 0;
var denied = 0;
worksheet.Cells[++row,
2].Value = "Kontaktinformationen";
worksheet.Cells[row,
2].Style.Font.Bold = true;
worksheet.Cells[row,
2].Style.Fill.PatternType = ExcelFillStyle.Solid;
worksheet.Cells[row++,
2].Style.Fill.BackgroundColor.SetColor(headlinesColor);
//worksheet.Row(row++).Height = 20;
//worksheet.Cells[row, 6].Style.Numberformat.Format = "#,##0 [$kWh];[RED] -#,##0 [$kWh]";
//worksheet.Cells[row, 6].Style.Numberformat.Format ="#0\\.00%";
worksheet.Cells[row++,
2].Value = networkCompany.Company.Address.Street;
worksheet.Cells[row++,
2].Value = networkCompany.Company.Address.PostalCode + " " + networkCompany.Company.Address.City;
worksheet.Cells[row++,
2].Value = networkCompany.Company.Address.Website;
worksheet.Cells[++row,
2].Value = "Ansprechpartner";
worksheet.Cells[row,
2].Style.Font.Bold = true;
worksheet.Cells[row,
2].Style.Fill.PatternType = ExcelFillStyle.Solid;
worksheet.Cells[row,
2].Style.Fill.BackgroundColor.SetColor(headlinesColor);
foreach (var employee in networkCompany.Company.Employees)
{
row++;
worksheet.Cells[row++,
2].Value = employee.FirstName + " " + employee.LastName;
worksheet.Cells[row++,
2].Value = employee.Email;
worksheet.Cells[row++,
2].Value = employee.PhoneNumber;
}
if (networkCompany.Measures.Any())
{
foreach (var measure in networkCompany.Measures)
{
total = measure.EnergySavings.Aggregate(total,
(current, saving) => (int) (current + saving.Value));
if (measure.State.Title == "abgeschlossen")
{
finished++;
saved = measure.EnergySavings.Aggregate(saved,
(current, saving) => (int) (current + saving.Value));
}
if (measure.State.Title == "abgelehnt")
{
denied = measure.EnergySavings.Aggregate(denied,
(current, saving) => (int) (current + saving.Value));
}
if (measure.State.Title == "in Bearbeitung")
{
inprogress++;
}
}
total -= denied;
worksheet.Cells[++row,
2].Value = "Maßnahmen";
worksheet.Cells[row,
2].Style.Font.Bold = true;
worksheet.Cells[row,
2,
row,
4].Style.Fill.PatternType = ExcelFillStyle.Solid;
worksheet.Cells[row,
2,
row,
4].Style.Fill.BackgroundColor.SetColor(headlinesColor);
worksheet.Cells[++row,
2].Value = "Maßnahmen gesamt";
worksheet.Cells[row++,
4].Value = networkCompany.Measures.Count;
worksheet.Cells[row,
2].Value = "in Bearbeitung";
worksheet.Cells[row++,
4].Value = inprogress;
worksheet.Cells[row,
2].Value = "abgeschlossene Maßnahmen";
worksheet.Cells[row++,
4].Value = finished;
worksheet.Cells[row,
2].Value = "eingesparte Energie";
worksheet.Cells[row,
3].Value = "kWh";
worksheet.Cells[row,
4].Value = saved;
worksheet.Cells[row++,
4].Style.Numberformat.Format = "#,##0;[RED] -#,##0";
worksheet.Cells[row,
2].Value = "Potiential abgelehnter Maßnahmen";
worksheet.Cells[row,
3].Value = "kWh";
worksheet.Cells[row,
4].Value = denied;
worksheet.Cells[row++,
4].Style.Numberformat.Format = "#,##0;[RED] -#,##0";
worksheet.Cells[row,
2].Value = "gesamt Einsparungspotiential";
worksheet.Cells[row,
3].Value = "kWh";
worksheet.Cells[row,
4].Value = total;
worksheet.Cells[row++,
4].Style.Numberformat.Format = "#,##0;[RED] -#,##0";
worksheet.Cells[row - 1,
6].Value = "verbleibendes Potential";
worksheet.Cells[row - 1,
8].Value = "kWh";
worksheet.Cells[row - 1,
9].Formula = new ExcelCellAddress(row - 1,
4).Address + "-" + new ExcelCellAddress(row - 3,
4).Address;
int chartrowStart;
if ((row - 15) <= 0)
{
chartrowStart = 1;
}
else
{
chartrowStart = row - 15;
}
ExcelChart chartMeasures = worksheet.Drawings.AddChart("Maßnahmen",
eChartType.Pie);
chartMeasures.Title.Text = "Maßnahmenpotential";
chartMeasures.SetPosition(chartrowStart,
0,
4,
20);
chartMeasures.SetSize(350,
280);
var data1Address = "'" + companyName + "'!" + new ExcelCellAddress(row - 1,
9).Address;
var data2Address = "'" + companyName + "'!" + new ExcelCellAddress(row - 2,
4).Address;
var data3Address = "'" + companyName + "'!" + new ExcelCellAddress(row - 3,
4).Address;
var xAddress = "'" + companyName + "'!" + new ExcelCellAddress(row - 1,
6).Address + "," + "'" + companyName + "'!" + new ExcelCellAddress(row - 2,
2).Address + "," + "'" + companyName + "'!" + new ExcelCellAddress(row - 3,
2).Address;
var legendAddress = new ExcelAddress("'" + companyName + "'!" + new ExcelCellAddress(row - 1,
3).Address);
var seriesMeasures = chartMeasures.Series.Add(data1Address + "," + data2Address + "," + data3Address,
xAddress);
seriesMeasures.HeaderAddress = legendAddress;
chartMeasures.Style = eChartStyle.Style2;
}
{
var effizienzstrom = new double[3000];
var effizienzerdgas = new double[3000];
var effizienzheizöl = new double[3000];
var effizienzfernwärme = new double[3000];
var effizienz = new double[3000];
var effiemissstrom = new double[3000];
var effiemisserdgas = new double[3000];
var effiemissheizöl = new double[3000];
var effiemissfernwärme = new double[3000];
var effiemiss = new double[3000];
var minYear = 3000;
var maxYear = 0;
var isEmpty = networkCompany.Surveys.Count(s => s.Readings.Count > 0) == 0;
if (networkCompany.Surveys.Count(s => s.Readings.Count > 0) == 0)
{
return row;
}
foreach (var survey in networkCompany.Surveys)
{
var relevantYear = true;
foreach (var reading in survey.Readings)
{
var value = reading.Value / 1000;
effizienz[survey.Date.Year] += value;
effiemiss[survey.Date.Year] += value * reading.EnergySource.CO2Equivalent / 1000;
if (reading.EnergySource.Name == "electricity")
{
effizienzstrom[survey.Date.Year] += value;
effiemissstrom[survey.Date.Year] += value * reading.EnergySource.CO2Equivalent / 1000;
}
if (reading.EnergySource.Name == "naturalGas")
{
effizienzerdgas[survey.Date.Year] += value;
effiemisserdgas[survey.Date.Year] += value * reading.EnergySource.CO2Equivalent / 1000;
}
if (reading.EnergySource.Name == "fuelOil")
{
effizienzheizöl[survey.Date.Year] += value;
effiemissheizöl[survey.Date.Year] += value * reading.EnergySource.CO2Equivalent / 1000;
}
if (reading.EnergySource.Name == "districtHeating")
{
effizienzfernwärme[survey.Date.Year] += value;
effiemissfernwärme[survey.Date.Year] += value * reading.EnergySource.CO2Equivalent / 1000;
}
if (minYear > survey.Date.Year)
{
minYear = survey.Date.Year;
}
if (maxYear < survey.Date.Year)
{
maxYear = survey.Date.Year;
}
relevantYear = effizienz[survey.Date.Year] != 0;
}
if ((!relevantYear) && (minYear == survey.Date.Year))
{
minYear++;
}
if ((!relevantYear) && (maxYear == survey.Date.Year))
{
maxYear--;
}
}
row++;
var co2Offset = maxYear - minYear + 6;
var rowOffset = 0;
for (int index = minYear; index <= maxYear; index++)
{
worksheet.Cells[row + 0,
2].Value = "Energieverbrauch";
worksheet.Cells[row + 0,
2].Style.Font.Bold = true;
worksheet.Cells[row + 0,
4 + (index - minYear)].Value = index;
worksheet.Cells[row + 0,
4 + (index - minYear)].Style.Font.Bold = true;
worksheet.Cells[row + 1,
2].Value = "Strom";
worksheet.Cells[row + 1,
3].Value = "MWh";
worksheet.Cells[row + 1,
4 + (index - minYear)].Value = effizienzstrom[index];
worksheet.Cells[row + 1,
4 + (index - minYear)].Style.Numberformat.Format = "#,##0;[RED] -#,##0";
worksheet.Cells[row + 2,
2].Value = "Erdgas";
worksheet.Cells[row + 2,
3].Value = "MWh";
worksheet.Cells[row + 2,
4 + (index - minYear)].Value = effizienzerdgas[index];
worksheet.Cells[row + 2,
4 + (index - minYear)].Style.Numberformat.Format = "#,##0;[RED] -#,##0";
worksheet.Cells[row + 3,
2].Value = "Heizöl";
worksheet.Cells[row + 3,
3].Value = "MWh";
worksheet.Cells[row + 3,
4 + (index - minYear)].Value = effizienzheizöl[index];
worksheet.Cells[row + 3,
4 + (index - minYear)].Style.Numberformat.Format = "#,##0;[RED] -#,##0";
worksheet.Cells[row + 4,
2].Value = "Fernwärme";
worksheet.Cells[row + 4,
3].Value = "MWh";
worksheet.Cells[row + 4,
4 + (index - minYear)].Value = effizienzfernwärme[index];
worksheet.Cells[row + 4,
4 + (index - minYear)].Style.Numberformat.Format = "#,##0;[RED] -#,##0";
worksheet.Cells[row + 0,
(co2Offset + 2)].Value = "CO2-Emissionen";
worksheet.Cells[row + 0,
(co2Offset + 2)].Style.Font.Bold = true;
worksheet.Cells[row + 0,
(co2Offset + 4) + (index - minYear)].Value = index;
worksheet.Cells[row + 0,
(co2Offset + 4) + (index - minYear)].Style.Font.Bold = true;
worksheet.Cells[row + 1,
(co2Offset + 2)].Value = "CO2 Strom";
worksheet.Cells[row + 1,
(co2Offset + 3)].Value = "t";
worksheet.Cells[row + 1,
(co2Offset + 4) + (index - minYear)].Value = effiemissstrom[index];
worksheet.Cells[row + 1,
(co2Offset + 4) + (index - minYear)].Style.Numberformat.Format = "#,##0.00;[RED] -#,##0.00";
worksheet.Cells[row + 2,
(co2Offset + 2)].Value = "CO2 Erdgas";
worksheet.Cells[row + 2,
(co2Offset + 3)].Value = "t";
worksheet.Cells[row + 2,
(co2Offset + 4) + (index - minYear)].Value = effiemisserdgas[index];
worksheet.Cells[row + 2,
(co2Offset + 4) + (index - minYear)].Style.Numberformat.Format = "#,##0.00;[RED] -#,##0.00";
worksheet.Cells[row + 3,
(co2Offset + 2)].Value = "CO2 Heizöl";
worksheet.Cells[row + 3,
(co2Offset + 3)].Value = "t";
worksheet.Cells[row + 3,
(co2Offset + 4) + (index - minYear)].Value = effiemissheizöl[index];
worksheet.Cells[row + 3,
(co2Offset + 4) + (index - minYear)].Style.Numberformat.Format = "#,##0.00;[RED] -#,##0.00";
worksheet.Cells[row + 4,
(co2Offset + 2)].Value = "CO2 Fernwärme";
worksheet.Cells[row + 4,
(co2Offset + 3)].Value = "t";
worksheet.Cells[row + 4,
(co2Offset + 4) + (index - minYear)].Value = effiemissfernwärme[index];
worksheet.Cells[row + 4,
(co2Offset + 4) + (index - minYear)].Style.Numberformat.Format = "#,##0.00;[RED] -#,##0.00";
}
worksheet.Cells[row + 0,
6 + maxYear - minYear].Value = "Summe";
worksheet.Cells[row + 0,
6 + maxYear - minYear].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
worksheet.Cells[row + 0,
6 + maxYear - minYear].Style.Font.Bold = true;
worksheet.Cells[row + 0,
2,
row,
6 + maxYear - minYear].Style.Fill.PatternType = ExcelFillStyle.Solid;
worksheet.Cells[row + 0,
2,
row,
6 + maxYear - minYear].Style.Fill.BackgroundColor.SetColor(headlinesColor);
worksheet.Cells[row + 1,
6 + maxYear - minYear].Formula = string.Format("Sum({0})",
new ExcelAddress(row + 1,
4,
row + 1,
4 + maxYear - minYear).Address);
worksheet.Cells[row + 1,
6 + maxYear - minYear].Style.Font.Bold = true;
worksheet.Cells[row + 1,
6 + maxYear - minYear].Style.Numberformat.Format = "#,##0;[RED] -#,##0";
worksheet.Cells[row + 2,
6 + maxYear - minYear].Formula = string.Format("Sum({0})",
new ExcelAddress(row + 2,
4,
row + 2,
4 + maxYear - minYear).Address);
worksheet.Cells[row + 2,
6 + maxYear - minYear].Style.Font.Bold = true;
worksheet.Cells[row + 2,
6 + maxYear - minYear].Style.Numberformat.Format = "#,##0;[RED] -#,##0";
worksheet.Cells[row + 3,
6 + maxYear - minYear].Formula = string.Format("Sum({0})",
new ExcelAddress(row + 3,
4,
row + 3,
4 + maxYear - minYear).Address);
worksheet.Cells[row + 3,
6 + maxYear - minYear].Style.Font.Bold = true;
worksheet.Cells[row + 3,
6 + maxYear - minYear].Style.Numberformat.Format = "#,##0;[RED] -#,##0";
worksheet.Cells[row + 4,
6 + maxYear - minYear].Formula = string.Format("Sum({0})",
new ExcelAddress(row + 4,
4,
row + 4,
4 + maxYear - minYear).Address);
worksheet.Cells[row + 4,
6 + maxYear - minYear].Style.Font.Bold = true;
worksheet.Cells[row + 4,
6 + maxYear - minYear].Style.Numberformat.Format = "#,##0;[RED] -#,##0";
worksheet.Cells[row + 0,
co2Offset + 6 + maxYear - minYear].Value = "Summe";
worksheet.Cells[row + 0,
co2Offset + 6 + maxYear - minYear].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
worksheet.Cells[row + 0,
co2Offset + 6 + maxYear - minYear].Style.Font.Bold = true;
worksheet.Cells[row + 0,
co2Offset + 2,
row,
co2Offset + 6 + maxYear - minYear].Style.Fill.PatternType = ExcelFillStyle.Solid;
worksheet.Cells[row + 0,
co2Offset + 2,
row,
co2Offset + 6 + maxYear - minYear].Style.Fill.BackgroundColor.SetColor(headlinesColor);
worksheet.Cells[row + 1,
co2Offset + 6 + maxYear - minYear].Formula = string.Format("Sum({0})",
new ExcelAddress(row + 1,
co2Offset + 4,
row + 1,
co2Offset + 4 + maxYear - minYear).Address);
worksheet.Cells[row + 1,
co2Offset + 6 + maxYear - minYear].Style.Font.Bold = true;
worksheet.Cells[row + 1,
co2Offset + 6 + maxYear - minYear].Style.Numberformat.Format = "#,##0.00;[RED] -#,##0.00";
worksheet.Cells[row + 2,
co2Offset + 6 + maxYear - minYear].Formula = string.Format("Sum({0})",
new ExcelAddress(row + 2,
co2Offset + 4,
row + 2,
co2Offset + 4 + maxYear - minYear).Address);
worksheet.Cells[row + 2,
co2Offset + 6 + maxYear - minYear].Style.Font.Bold = true;
worksheet.Cells[row + 2,
co2Offset + 6 + maxYear - minYear].Style.Numberformat.Format = "#,##0.00;[RED] -#,##0.00";
worksheet.Cells[row + 3,
co2Offset + 6 + maxYear - minYear].Formula = string.Format("Sum({0})",
new ExcelAddress(row + 3,
co2Offset + 4,
row + 3,
co2Offset + 4 + maxYear - minYear).Address);
worksheet.Cells[row + 3,
co2Offset + 6 + maxYear - minYear].Style.Font.Bold = true;
worksheet.Cells[row + 3,
co2Offset + 6 + maxYear - minYear].Style.Numberformat.Format = "#,##0.00;[RED] -#,##0.00";
worksheet.Cells[row + 4,
co2Offset + 6 + maxYear - minYear].Formula = string.Format("Sum({0})",
new ExcelAddress(row + 4,
co2Offset + 4,
row + 4,
co2Offset + 4 + maxYear - minYear).Address);
worksheet.Cells[row + 4,
co2Offset + 6 + maxYear - minYear].Style.Font.Bold = true;
worksheet.Cells[row + 4,
co2Offset + 6 + maxYear - minYear].Style.Numberformat.Format = "#,##0.00;[RED] -#,##0.00";
row += 5;
worksheet.Cells[row,
2].Value = "Summe";
worksheet.Cells[row,
2].Style.Font.Bold = true;
worksheet.Cells[row,
3].Value = "MWh";
worksheet.Cells[row,
3].Style.Font.Bold = true;
worksheet.Cells[row,
co2Offset + 2].Value = "Summe";
worksheet.Cells[row,
co2Offset + 2].Style.Font.Bold = true;
worksheet.Cells[row,
co2Offset + 3].Value = "t";
worksheet.Cells[row,
co2Offset + 3].Style.Font.Bold = true;
rowOffset = 18;
worksheet.Cells[++row + rowOffset + 0,
2].Value = "Entwicklung des Energieverbrauches";
worksheet.Cells[row + rowOffset + 0,
2].Style.Font.Bold = true;
worksheet.Cells[row + rowOffset + 0,
2,
row + 18,
4 + maxYear - minYear].Style.Fill.PatternType = ExcelFillStyle.Solid;
worksheet.Cells[row + rowOffset + 0,
2,
row + 18,
4 + maxYear - minYear].Style.Fill.BackgroundColor.SetColor(headlinesColor);
worksheet.Cells[row + rowOffset + 1,
2].Value = "Strom";
worksheet.Cells[row + rowOffset + 2,
2].Value = "Erdgas";
worksheet.Cells[row + rowOffset + 3,
2].Value = "Heizöl";
worksheet.Cells[row + rowOffset + 4,
2].Value = "Fernwärme";
worksheet.Cells[row + rowOffset + 5,
2].Value = "Gesamt";
worksheet.Cells[row + rowOffset + 5,
2].Style.Font.Bold = true;
worksheet.Cells[row + rowOffset + 0,
co2Offset + 2].Value = "Entwicklung der CO2-Emissionen";
worksheet.Cells[row + rowOffset + 0,
co2Offset + 2].Style.Font.Bold = true;
worksheet.Cells[row + rowOffset + 0,
co2Offset + 2,
row + rowOffset + 0,
co2Offset + 4 + maxYear - minYear].Style.Fill.PatternType = ExcelFillStyle.Solid;
worksheet.Cells[row + rowOffset + 0,
co2Offset + 2,
row + rowOffset + 0,
co2Offset + 4 + maxYear - minYear].Style.Fill.BackgroundColor.SetColor(headlinesColor);
worksheet.Cells[row + rowOffset + 1,
co2Offset + 2].Value = "CO2 Strom";
worksheet.Cells[row + rowOffset + 2,
co2Offset + 2].Value = "CO2 Erdgas";
worksheet.Cells[row + rowOffset + 3,
co2Offset + 2].Value = "CO2 Heizöl";
worksheet.Cells[row + rowOffset + 4,
co2Offset + 2].Value = "CO2 Fernwärme";
worksheet.Cells[row + rowOffset + 5,
co2Offset + 2].Value = "CO2 Gesamt";
worksheet.Cells[row + rowOffset + 5,
co2Offset + 2].Style.Font.Bold = true;
rowOffset = 42;
worksheet.Cells[row + rowOffset + 0,
2].Value = "Anteile am Gesamtenergieverbrauch";
worksheet.Cells[row + rowOffset + 0,
2].Style.Font.Bold = true;
worksheet.Cells[row + rowOffset + 0,
2,
row + rowOffset + 0,
4 + maxYear - minYear].Style.Fill.PatternType = ExcelFillStyle.Solid;
worksheet.Cells[row + rowOffset + 0,
2,
row + rowOffset + 0,
4 + maxYear - minYear].Style.Fill.BackgroundColor.SetColor(headlinesColor);
worksheet.Cells[row + rowOffset + 1,
2].Value = "Stromanteil";
worksheet.Cells[row + rowOffset + 2,
2].Value = "Erdgasanteil";
worksheet.Cells[row + rowOffset + 3,
2].Value = "Heizölanteil";
worksheet.Cells[row + rowOffset + 4,
2].Value = "Fernwärmeanteil";
worksheet.Cells[row + rowOffset + 0,
co2Offset + 2].Value = "Anteile an den Gesamtemissionen";
worksheet.Cells[row + rowOffset + 0,
co2Offset + 2].Style.Font.Bold = true;
worksheet.Cells[row + rowOffset + 0,
co2Offset + 2,
row + rowOffset + 0,
co2Offset + 4 + maxYear - minYear].Style.Fill.PatternType = ExcelFillStyle.Solid;
worksheet.Cells[row + rowOffset + 0,
co2Offset + 2,
row + rowOffset + 0,
co2Offset + 4 + maxYear - minYear].Style.Fill.BackgroundColor.SetColor(headlinesColor);
worksheet.Cells[row + rowOffset + 1,
co2Offset + 2].Value = "CO2 Stromanteil";
worksheet.Cells[row + rowOffset + 2,
co2Offset + 2].Value = "CO2 Erdgasanteil";
worksheet.Cells[row + rowOffset + 3,
co2Offset + 2].Value = "CO2 Heizölanteil";
worksheet.Cells[row + rowOffset + 4,
co2Offset + 2].Value = "CO2 Fernwärmeanteil";
for (int index = minYear; index <= maxYear; index++)
{
worksheet.Cells[row - 1,
4 + index - minYear].Formula = string.Format("Sum({0})",
new ExcelAddress(row - 5,
4 + index - minYear,
row - 2,
4 + index - minYear).Address);
worksheet.Cells[row - 1,
4 + index - minYear].Style.Font.Bold = true;
worksheet.Cells[row - 1,
4 + index - minYear].Style.Numberformat.Format = "#,##0;[RED] -#,##0";
worksheet.Cells[row - 1,
co2Offset + 4 + index - minYear].Formula = string.Format("Sum({0})",
new ExcelAddress(row - 5,
co2Offset + 4 + index - minYear,
row - 2,
co2Offset + 4 + index - minYear).Address);
worksheet.Cells[row - 1,
co2Offset + 4 + index - minYear].Style.Font.Bold = true;
worksheet.Cells[row - 1,
co2Offset + 4 + index - minYear].Style.Numberformat.Format = "#,##0.00;[RED] -#,##0.00";
rowOffset = 18;
worksheet.Cells[row + rowOffset + 1,
4 + index - minYear].Formula = string.Format("IF({0}<>0,Sum({1}/{0}),0)",
new ExcelCellAddress(row - 5,
4).Address,
new ExcelCellAddress(row - 5,
4 + index - minYear).Address);
worksheet.Cells[row + rowOffset + 1,
4 + index - minYear].Style.Numberformat.Format = "0.00%";
worksheet.Cells[row + rowOffset + 2,
4 + index - minYear].Formula = string.Format("IF({0}<>0,Sum({1}/{0}),0)",
new ExcelCellAddress(row - 4,
4).Address,
new ExcelCellAddress(row - 4,
4 + index - minYear).Address);
worksheet.Cells[row + rowOffset + 2,
4 + index - minYear].Style.Numberformat.Format = "0.00%";
worksheet.Cells[row + rowOffset + 3,
4 + index - minYear].Formula = string.Format("IF({0}<>0,Sum({1}/{0}),0)",
new ExcelCellAddress(row - 3,
4).Address,
new ExcelCellAddress(row - 3,
4 + index - minYear).Address);
worksheet.Cells[row + rowOffset + 3,
4 + index - minYear].Style.Numberformat.Format = "0.00%";
worksheet.Cells[row + rowOffset + 4,
4 + index - minYear].Formula = string.Format("IF({0}<>0,Sum({1}/{0}),0)",
new ExcelCellAddress(row - 2,
4).Address,
new ExcelCellAddress(row - 2,
4 + index - minYear).Address);
worksheet.Cells[row + rowOffset + 4,
4 + index - minYear].Style.Numberformat.Format = "0.00%";
worksheet.Cells[row + rowOffset + 5,
4 + index - minYear].Formula = string.Format("IF({0}<>0,Sum({1}/{0}),0)",
new ExcelCellAddress(row - 1,
4).Address,
new ExcelCellAddress(row - 1,
4 + index - minYear).Address);
worksheet.Cells[row + rowOffset + 5,
4 + index - minYear].Style.Numberformat.Format = "0.00%";
worksheet.Cells[row + rowOffset + 5,
4 + index - minYear].Style.Font.Bold = true;
worksheet.Cells[row + rowOffset + 1,
co2Offset + 4 + index - minYear].Formula = string.Format("IF({0}<>0,Sum({1}/{0}),0)",
new ExcelCellAddress(row - 5,
co2Offset + 4).Address,
new ExcelCellAddress(row - 5,
co2Offset + 4 + index - minYear).Address);
worksheet.Cells[row + rowOffset + 1,
co2Offset + 4 + index - minYear].Style.Numberformat.Format = "0.00%";
worksheet.Cells[row + rowOffset + 2,
co2Offset + 4 + index - minYear].Formula = string.Format("IF({0}<>0,Sum({1}/{0}),0)",
new ExcelCellAddress(row - 4,
co2Offset + 4).Address,
new ExcelCellAddress(row - 4,
co2Offset + 4 + index - minYear).Address);
worksheet.Cells[row + rowOffset + 2,
co2Offset + 4 + index - minYear].Style.Numberformat.Format = "0.00%";
worksheet.Cells[row + rowOffset + 3,
co2Offset + 4 + index - minYear].Formula = string.Format("IF({0}<>0,Sum({1}/{0}),0)",
new ExcelCellAddress(row - 3,
co2Offset + 4).Address,
new ExcelCellAddress(row - 3,
co2Offset + 4 + index - minYear).Address);
worksheet.Cells[row + rowOffset + 3,
co2Offset + 4 + index - minYear].Style.Numberformat.Format = "0.00%";
worksheet.Cells[row + rowOffset + 4,
co2Offset + 4 + index - minYear].Formula = string.Format("IF({0}<>0,Sum({1}/{0}),0)",
new ExcelCellAddress(row - 2,
co2Offset + 4).Address,
new ExcelCellAddress(row - 2,
co2Offset + 4 + index - minYear).Address);
worksheet.Cells[row + rowOffset + 4,
co2Offset + 4 + index - minYear].Style.Numberformat.Format = "0.00%";
worksheet.Cells[row + rowOffset + 5,
co2Offset + 4 + index - minYear].Formula = string.Format("IF({0}<>0,Sum({1}/{0}),0)",
new ExcelCellAddress(row - 1,
co2Offset + 4).Address,
new ExcelCellAddress(row - 1,
co2Offset + 4 + index - minYear).Address);
worksheet.Cells[row + rowOffset + 5,
co2Offset + 4 + index - minYear].Style.Numberformat.Format = "0.00%";
rowOffset = 42;
worksheet.Cells[row + rowOffset + 1,
4 + index - minYear].Formula = string.Format("IF({1}<>0,Sum({0}/{1}),0)",
new ExcelCellAddress(row - 5,
4 + index - minYear).Address,
new ExcelCellAddress(row - 1,
4 + index - minYear).Address);
worksheet.Cells[row + rowOffset + 1,
4 + index - minYear].Style.Numberformat.Format = "0.00%";
worksheet.Cells[row + rowOffset + 2,
4 + index - minYear].Formula = string.Format("IF({1}<>0,Sum({0}/{1}),0)",
new ExcelCellAddress(row - 4,
4 + index - minYear).Address,
new ExcelCellAddress(row - 1,
4 + index - minYear).Address);
worksheet.Cells[row + rowOffset + 2,
4 + index - minYear].Style.Numberformat.Format = "0.00%";
worksheet.Cells[row + rowOffset + 3,
4 + index - minYear].Formula = string.Format("IF({1}<>0,Sum({0}/{1}),0)",
new ExcelCellAddress(row - 3,
4 + index - minYear).Address,
new ExcelCellAddress(row - 1,
4 + index - minYear).Address);
worksheet.Cells[row + rowOffset + 3,
4 + index - minYear].Style.Numberformat.Format = "0.00%";
worksheet.Cells[row + rowOffset + 4,
4 + index - minYear].Formula = string.Format("IF({1}<>0,Sum({0}/{1}),0)",
new ExcelCellAddress(row - 2,
4 + index - minYear).Address,
new ExcelCellAddress(row - 1,
4 + index - minYear).Address);
worksheet.Cells[row + rowOffset + 4,
4 + index - minYear].Style.Numberformat.Format = "0.00%";
worksheet.Cells[row + rowOffset + 1,
co2Offset + 4 + index - minYear].Formula = string.Format("IF({1}<>0,Sum({0}/{1}),0)",
new ExcelCellAddress(row - 5,
co2Offset + 4 + index - minYear).Address,
new ExcelCellAddress(row - 1,
co2Offset + 4 + index - minYear).Address);
worksheet.Cells[row + rowOffset + 1,
co2Offset + 4 + index - minYear].Style.Numberformat.Format = "0.00%";
worksheet.Cells[row + rowOffset + 2,
co2Offset + 4 + index - minYear].Formula = string.Format("IF({1}<>0,Sum({0}/{1}),0)",
new ExcelCellAddress(row - 4,
co2Offset + 4 + index - minYear).Address,
new ExcelCellAddress(row - 1,
co2Offset + 4 + index - minYear).Address);
worksheet.Cells[row + rowOffset + 2,
co2Offset + 4 + index - minYear].Style.Numberformat.Format = "0.00%";
worksheet.Cells[row + rowOffset + 3,
co2Offset + 4 + index - minYear].Formula = string.Format("IF({1}<>0,Sum({0}/{1}),0)",
new ExcelCellAddress(row - 3,
co2Offset + 4 + index - minYear).Address,
new ExcelCellAddress(row - 1,
co2Offset + 4 + index - minYear).Address);
worksheet.Cells[row + rowOffset + 3,
co2Offset + 4 + index - minYear].Style.Numberformat.Format = "0.00%";
worksheet.Cells[row + rowOffset + 4,
co2Offset + 4 + index - minYear].Formula = string.Format("IF({1}<>0,Sum({0}/{1}),0)",
new ExcelCellAddress(row - 2,
co2Offset + 4 + index - minYear).Address,
new ExcelCellAddress(row - 1,
co2Offset + 4 + index - minYear).Address);
worksheet.Cells[row + rowOffset + 4,
co2Offset + 4 + index - minYear].Style.Numberformat.Format = "0.00%";
}
ExcelChart chartOverview = worksheet.Drawings.AddChart("Entwicklung der Energieeffizienz",
eChartType.Line);
chartOverview.Title.Text = "Entwicklung der Energieeffizienz";
int chartrowStart;
int chartrowOffset;
int chartpixelOffset;
int chartsizeDelta;
if (co2Offset < 9)
{
chartrowOffset = co2Offset + 1;
chartpixelOffset = 80;
chartsizeDelta = 355 + (40 * (maxYear - minYear));
}
else
{
chartrowOffset = co2Offset + 1;
chartpixelOffset = 10;
chartsizeDelta = 315 + (40 * (maxYear - minYear));
}
if ((row - 27) <= 0)
{
chartrowStart = 1;
}
else
{
chartrowStart = row - 27;
}
chartOverview.SetPosition(chartrowStart,
0,
chartrowOffset,
chartpixelOffset);
chartOverview.SetSize(chartsizeDelta,
380);
{
var data1Address = new ExcelAddress((row + 23),
4,
(row + 23),
(4 + maxYear - minYear)).Address;
var data2Address = new ExcelAddress((row + 23),
4 + co2Offset,
(row + 23),
(4 + co2Offset + maxYear - minYear)).Address;
var xAddress = new ExcelAddress(row - 6,
4,
row - 6,
(4 + maxYear - minYear)).Address;
var series1Overview = chartOverview.Series.Add(data1Address,
xAddress);
series1Overview.HeaderAddress = new ExcelAddress("'" + companyName + "'!" + new ExcelCellAddress((row + 18),
2).Address);
var series2Overview = chartOverview.Series.Add(data2Address,
xAddress);
series2Overview.HeaderAddress = new ExcelAddress("'" + companyName + "'!" + new ExcelCellAddress((row + 18),
co2Offset + 2).Address);
}
chartOverview.Style = eChartStyle.Style2;
ExcelChart chartEnergieVerbrauch = worksheet.Drawings.AddChart("Energieverbrauch",
eChartType.ColumnClustered);
chartEnergieVerbrauch.Title.Text = "Energieverbrauch";
chartEnergieVerbrauch.SetPosition(row,
0,
1,
0);
chartEnergieVerbrauch.SetSize(305 + (40 * (maxYear - minYear)),
315);
for (int index = 0; index < 4; index++)
{
var dataAddress = new ExcelAddress((row - 5 + index),
4,
(row - 5 + index),
(4 + maxYear - minYear)).Address;
var xAddress = new ExcelAddress(row - 6,
4,
row - 6,
(4 + maxYear - minYear)).Address;
var legendAddress = new ExcelAddress("'" + companyName + "'!" + new ExcelCellAddress((row - 5 + index),
2).Address);
var seriesEnergieVerbrauch = chartEnergieVerbrauch.Series.Add(dataAddress,
xAddress);
seriesEnergieVerbrauch.HeaderAddress = legendAddress;
}
chartEnergieVerbrauch.Style = eChartStyle.Style2;
chartEnergieVerbrauch.YAxis.Format = "#,##0 [$kWh];[RED] -#,##0 [$kWh]";
ExcelChart chartEmission = worksheet.Drawings.AddChart("CO2-Emissionen",
eChartType.ColumnClustered);
chartEmission.Title.Text = "CO2-Emissionen";
chartEmission.SetPosition(row,
0,
1 + co2Offset,
0);
chartEmission.SetSize(305 + (40 * (maxYear - minYear)),
315);
for (int index = 0; index < 4; index++)
{
var dataAddress = new ExcelAddress((row - 5 + index),
4 + co2Offset,
(row - 5 + index),
(4 + co2Offset + maxYear - minYear)).Address;
var xAddress = new ExcelAddress(row - 6,
4 + co2Offset,
row - 6,
(4 + co2Offset + maxYear - minYear)).Address;
var legendAddress = new ExcelAddress("'" + companyName + "'!" + new ExcelCellAddress((row - 5 + index),
2 + co2Offset).Address);
var seriesEmission = chartEmission.Series.Add(dataAddress,
xAddress);
seriesEmission.HeaderAddress = legendAddress;
}
chartEmission.Style = eChartStyle.Style2;
chartEmission.YAxis.Format = "#,##0 [$t];[RED] -#,##0 [$t]";
ExcelChart chartEnergieEntwicklung = worksheet.Drawings.AddChart("Energieffizienzentwicklung",
eChartType.Line);
chartEnergieEntwicklung.Title.Text = "Energieentwicklung";
chartEnergieEntwicklung.SetPosition(row + 24,
0,
1,
0);
chartEnergieEntwicklung.SetSize(305 + (40 * (maxYear - minYear)),
315);
for (int index = 0; index < 4; index++)
{
var dataAddress = new ExcelAddress((row + 24 - 5 + index),
4,
(row + 24 - 5 + index),
(4 + maxYear - minYear)).Address;
var xAddress = new ExcelAddress(row - 6,
4,
row - 6,
(4 + maxYear - minYear)).Address;
var legendAddress = new ExcelAddress("'" + companyName + "'!" + new ExcelCellAddress((row + 24 - 5 + index),
2).Address);
var seriesEnergieEntwicklung = chartEnergieEntwicklung.Series.Add(dataAddress,
xAddress);
seriesEnergieEntwicklung.HeaderAddress = legendAddress;
}
chartEnergieEntwicklung.Style = eChartStyle.Style2;
ExcelChart chartCO2entwicklung = worksheet.Drawings.AddChart("CO2-Emissionenentwicklung",
eChartType.Line);
chartCO2entwicklung.Title.Text = "CO2-Entwicklung";
chartCO2entwicklung.SetPosition(row + 24,
0,
1 + co2Offset,
0);
chartCO2entwicklung.SetSize(305 + (40 * (maxYear - minYear)),
315);
for (int index = 0; index < 4; index++)
{
var dataAddress = new ExcelAddress((row + 24 - 5 + index),
4 + co2Offset,
(row + 24 - 5 + index),
(4 + co2Offset + maxYear - minYear)).Address;
var xAddress = new ExcelAddress(row - 6,
4 + co2Offset,
row - 6,
(4 + co2Offset + maxYear - minYear)).Address;
var legendAddress = new ExcelAddress("'" + companyName + "'!" + new ExcelCellAddress((row + 24 - 5 + index),
2 + co2Offset).Address);
var seriesCO2entwicklung = chartCO2entwicklung.Series.Add(dataAddress,
xAddress);
seriesCO2entwicklung.HeaderAddress = legendAddress;
}
chartCO2entwicklung.Style = eChartStyle.Style2;
row += 47;
ExcelChart chartAnteilGesamt = worksheet.Drawings.AddChart("Anteile am Gesamtenergieverbrauch",
eChartType.ColumnStacked100);
chartAnteilGesamt.Title.Text = "Anteile am Gesamtenergieverbrauch";
//chartAnteilGesamt.Legend.Position = eLegendPosition.Right;
//chartAnteilGesamt.Legend.Add();
chartAnteilGesamt.SetPosition(row,
0,
1,
0);
chartAnteilGesamt.SetSize(305 + (40 * (maxYear - minYear)),
315);
for (int index = 0; index < 4; index++)
{
var dataAddress = new ExcelAddress((row - 4 + index),
4,
(row - 4 + index),
(4 + maxYear - minYear)).Address;
var xAddress = new ExcelAddress(row - 53,
4,
row - 53,
(4 + maxYear - minYear)).Address;
var legendAddress = new ExcelAddress("'" + companyName + "'!" + new ExcelCellAddress((row - 4 + index),
2).Address);
var seriesAnteilGesamt = chartAnteilGesamt.Series.Add(dataAddress,
xAddress);
seriesAnteilGesamt.HeaderAddress = legendAddress;
}
//chart.DataLabel.ShowCategory = true;
//chart.DataLabel.ShowPercent = true;
//chart.DataLabel.ShowLeaderLines = true;
//chartAnteilGesamt.Legend.Remove();
chartAnteilGesamt.Style = eChartStyle.Style2;
//chartAnteilGesamt.YAxis.Format = "#,##0 [$kWh];[RED] -#,##0 [$kWh]";
ExcelChart chartAnteilGesEmis = worksheet.Drawings.AddChart("Anteile an den CO2-Emissionen",
eChartType.ColumnStacked100);
chartAnteilGesEmis.Title.Text = "Anteile an den CO2-Emissionen";
chartAnteilGesEmis.SetPosition(row,
0,
1 + co2Offset,
0);
chartAnteilGesEmis.SetSize(305 + (40 * (maxYear - minYear)),
315);
for (int index = 0; index < 4; index++)
{
var dataAddress = new ExcelAddress((row - 4 + index),
4 + co2Offset,
(row - 4 + index),
(4 + co2Offset + maxYear - minYear)).Address;
var xAddress = new ExcelAddress(row - 53,
4 + co2Offset,
row - 53,
(4 + co2Offset + maxYear - minYear)).Address;
var legendAddress = new ExcelAddress("'" + companyName + "'!" + new ExcelCellAddress((row - 4 + index),
2 + co2Offset).Address);
var seriesAnteilGesEmis = chartAnteilGesEmis.Series.Add(dataAddress,
xAddress);
seriesAnteilGesEmis.HeaderAddress = legendAddress;
}
chartAnteilGesEmis.Style = eChartStyle.Style2;
var pieChartsEnergyperYear = new ExcelChart[maxYear - minYear + 1];
var pieChartsCO2perYear = new ExcelChart[maxYear - minYear + 1];
for (int index = 0; index <= (maxYear - minYear); index++)
{
{
pieChartsEnergyperYear[index] = worksheet.Drawings.AddChart(("Energieverbrauchsanteile in " + (minYear + index)),
eChartType.Pie);
pieChartsEnergyperYear[index].Title.Text = "Energieverbrauchsanteile in " + (minYear + index);
pieChartsEnergyperYear[index].SetPosition(row + 17 + (17 * index),
0,
1,
0);
pieChartsEnergyperYear[index].SetSize(305 + (40 * (maxYear - minYear)),
315);
var dataAddress = new ExcelAddress(row - 4,
4 + index,
row - 1,
4 + index).Address;
var xAddress = new ExcelAddress(row - 4,
2,
row - 1,
2).Address;
var legendAddress = new ExcelAddress("'" + companyName + "'!" + new ExcelCellAddress(row - 5,
2).Address);
var seriespieChartEnergy = pieChartsEnergyperYear[index].Series.Add(dataAddress,
xAddress);
seriespieChartEnergy.HeaderAddress = legendAddress;
}
{
pieChartsCO2perYear[index] = worksheet.Drawings.AddChart(("CO2-Emissionsanteile in " + (minYear + index)),
eChartType.Pie);
pieChartsCO2perYear[index].Title.Text = "CO2-Emissionsanteile in " + (minYear + index);
pieChartsCO2perYear[index].SetPosition(row + 17 + (17 * index),
0,
co2Offset + 1,
0);
pieChartsCO2perYear[index].SetSize(305 + (40 * (maxYear - minYear)),
315);
var dataAddress = new ExcelAddress(row - 4,
co2Offset + 4 + index,
row - 1,
co2Offset + 4 + index).Address;
var xAddress = new ExcelAddress(row - 4,
co2Offset + 2,
row - 1,
co2Offset + 2).Address;
var legendAddress = new ExcelAddress("'" + companyName + "'!" + new ExcelCellAddress(row - 5,
co2Offset + 2).Address);
var seriespieChartCO2 = pieChartsCO2perYear[index].Series.Add(dataAddress,
xAddress);
seriespieChartCO2.HeaderAddress = legendAddress;
}
}
worksheet.Column(1).
Width = 8.43;
worksheet.Column(2).
Width = 33.57;
worksheet.Column(3).
Width = 7.43;
for (int index = 0; index <= (maxYear - minYear); index++)
{
worksheet.Column(4 + index).
Width = 10;
}
worksheet.Column(5 + maxYear - minYear).
Width = 0.83;
worksheet.Column(6 + maxYear - minYear).
Width = 9.29;
worksheet.Column(co2Offset + 1).
Width = 8.43;
worksheet.Column(co2Offset + 2).
Width = 16.43;
worksheet.Column(co2Offset + 3).
Width = 6.43;
for (int index = 0; index <= (maxYear - minYear); index++)
{
worksheet.Column(co2Offset + 4 + index).
Width = 10;
}
worksheet.Column(co2Offset + 5 + maxYear - minYear).
Width = 0.83;
worksheet.Column(co2Offset + 6 + maxYear - minYear).
Width = 10;
row += 17;
return (row);
}
}
/// <summary>
/// Handles shared formulas
/// </summary>
/// <param name="value"></param>
/// <param name="address"></param>
private void Set_SharedFormula(string value, ExcelAddress address)
{
if (address.Start.Row == address.End.Row && address.Start.Column == address.End.Column) //is it really a shared formula?
{
//Nope, single cell. Set the formula
Set_Formula(value, address.Start.Row, address.Start.Column);
return;
}
RemoveFormuls(address);
ExcelWorksheet.Formulas f = new ExcelWorksheet.Formulas();
f.Formula = value;
f.Index = _worksheet.GetMaxShareFunctionIndex();
f.Address = address.FirstAddress;
f.StartCol = address.Start.Column;
f.StartRow = address.Start.Row;
_worksheet._sharedFormulas.Add(f.Index, f);
_worksheet.Cell(address.Start.Row, address.Start.Column).SharedFormulaID = f.Index;
_worksheet.Cell(address.Start.Row, address.Start.Column).Formula = value;
for (int col = address.Start.Column; col <= address.End.Column; col++)
{
for (int row = address.Start.Row; row <= address.End.Row; row++)
{
_worksheet.Cell(row, col).SharedFormulaID = f.Index;
}
}
}
/// <summary>
/// Sample 5 - open Sample 1 and add 2 new rows and a Piechart
/// </summary>
public static string RunSample5(DirectoryInfo outputDir)
{
FileInfo templateFile = new FileInfo(outputDir.FullName + @"\sample1.xlsx");
FileInfo newFile = new FileInfo(outputDir.FullName + @"\sample5.xlsx");
if (newFile.Exists)
{
newFile.Delete(); // ensures we create a new workbook
newFile = new FileInfo(outputDir.FullName + @"\sample5.xlsx");
}
using (ExcelPackage package = new ExcelPackage(newFile, templateFile))
{
//Open worksheet 1
ExcelWorksheet worksheet = package.Workbook.Worksheets[1];
worksheet.InsertRow(5, 2);
worksheet.Cells["A5"].Value = "12010";
worksheet.Cells["B5"].Value = "Drill";
worksheet.Cells["C5"].Value = 20;
worksheet.Cells["D5"].Value = 8;
worksheet.Cells["A6"].Value = "12011";
worksheet.Cells["B6"].Value = "Crowbar";
worksheet.Cells["C6"].Value = 7;
worksheet.Cells["D6"].Value = 23.48;
worksheet.Cells["E2:E6"].FormulaR1C1 = "RC[-2]*RC[-1]";
var name = worksheet.Names.Add("SubTotalName", worksheet.Cells["C7:E7"]);
name.Style.Font.Italic = true;
name.Formula = "SUBTOTAL(9,C2:C6)";
//Format the new rows
worksheet.Cells["C5:C6"].Style.Numberformat.Format = "#,##0";
worksheet.Cells["D5:E6"].Style.Numberformat.Format = "#,##0.00";
var chart = (worksheet.Drawings.AddChart("PieChart", eChartType.Pie3D) as ExcelPieChart);
chart.Title.Text = "Total";
//From row 1 colum 5 with five pixels offset
chart.SetPosition(0, 0, 5, 5);
chart.SetSize(600, 300);
ExcelAddress valueAddress = new ExcelAddress(2, 5, 6, 5);
var ser = (chart.Series.Add(valueAddress.Address, "B2:B6") as ExcelPieChartSerie);
chart.DataLabel.ShowCategory = true;
chart.DataLabel.ShowPercent = true;
chart.Legend.Border.LineStyle = eLineStyle.Solid;
chart.Legend.Border.Fill.Style = eFillStyle.SolidFill;
chart.Legend.Border.Fill.Color = Color.DarkBlue;
//Switch the PageLayoutView back to normal
worksheet.View.PageLayoutView = false;
// save our new workbook and we are done!
package.Save();
}
return newFile.FullName;
}
/// <summary>
/// Sample 14 - Conditional formatting example
/// </summary>
public static string RunSample14(DirectoryInfo outputDir)
{
FileInfo newFile = new FileInfo(outputDir.FullName + @"\sample14.xlsx");
if (newFile.Exists)
{
newFile.Delete(); // ensures we create a new workbook
newFile = new FileInfo(outputDir.FullName + @"\sample14.xlsx");
}
using (ExcelPackage package = new ExcelPackage(newFile))
{
// add a new worksheet to the empty workbook
ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Conditional Formatting");
// Create 4 columns of samples data
for (int col = 1; col < 5; col++)
{
// Add the headers
worksheet.Cells[1, col].Value = "Sample " + col;
for (int row = 2; row < 21; row++)
{
// Add some items...
worksheet.Cells[row, col].Value = row;
}
}
// -------------------------------------------------------------------
// TwoColorScale Conditional Formatting example
// -------------------------------------------------------------------
ExcelAddress cfAddress1 = new ExcelAddress("A2:A10");
var cfRule1 = worksheet.ConditionalFormatting.AddTwoColorScale(cfAddress1);
// Now, lets change some properties:
cfRule1.LowValue.Type = eExcelConditionalFormattingValueObjectType.Num;
cfRule1.LowValue.Value = 4;
cfRule1.LowValue.Color = ColorTranslator.FromHtml("#FFFFEB84");
cfRule1.HighValue.Type = eExcelConditionalFormattingValueObjectType.Formula;
cfRule1.HighValue.Formula = "IF($G$1=\"A</x:&'cfRule>\",1,5)";
cfRule1.StopIfTrue = true;
cfRule1.Style.Font.Bold = true;
// But others you can't (readonly)
// cfRule1.Type = eExcelConditionalFormattingRuleType.ThreeColorScale;
// -------------------------------------------------------------------
// ThreeColorScale Conditional Formatting example
// -------------------------------------------------------------------
ExcelAddress cfAddress2 = new ExcelAddress(2, 2, 10, 2); //="B2:B10"
var cfRule2 = worksheet.ConditionalFormatting.AddThreeColorScale(cfAddress2);
// Changing some properties again
cfRule2.Priority = 1;
cfRule2.MiddleValue.Type = eExcelConditionalFormattingValueObjectType.Percentile;
cfRule2.MiddleValue.Value = 30;
cfRule2.StopIfTrue = true;
// You can access a rule by its Priority
var cfRule2Priority = cfRule2.Priority;
var cfRule2_1 = worksheet.ConditionalFormatting.RulesByPriority(cfRule2Priority);
// And you can even change the rule's Address
cfRule2_1.Address = new ExcelAddress("Z1:Z3");
// -------------------------------------------------------------------
// Adding another ThreeColorScale in a different way (observe that we are
// pointing to the same range as the first rule we entered. Excel allows it to
// happen and group the rules in one <conditionalFormatting> node)
// -------------------------------------------------------------------
var cfRule3 = worksheet.Cells[cfAddress1.Address].ConditionalFormatting.AddThreeColorScale();
cfRule3.LowValue.Color = Color.LemonChiffon;
// -------------------------------------------------------------------
// Change the rules priorities to change their execution order
// -------------------------------------------------------------------
cfRule3.Priority = 1;
cfRule1.Priority = 2;
cfRule2.Priority = 3;
// -------------------------------------------------------------------
// Create an Above Average rule
// -------------------------------------------------------------------
var cfRule5 = worksheet.ConditionalFormatting.AddAboveAverage(
new ExcelAddress("B11:B20"));
cfRule5.Style.Font.Bold = true;
cfRule5.Style.Font.Color.Color = Color.Red;
cfRule5.Style.Font.Strike = true;
// -------------------------------------------------------------------
// Create an Above Or Equal Average rule
// -------------------------------------------------------------------
var cfRule6 = worksheet.ConditionalFormatting.AddAboveOrEqualAverage(
new ExcelAddress("B11:B20"));
// -------------------------------------------------------------------
// Create a Below Average rule
// -------------------------------------------------------------------
var cfRule7 = worksheet.ConditionalFormatting.AddBelowAverage(
new ExcelAddress("B11:B20"));
// -------------------------------------------------------------------
// Create a Below Or Equal Average rule
// -------------------------------------------------------------------
var cfRule8 = worksheet.ConditionalFormatting.AddBelowOrEqualAverage(
new ExcelAddress("B11:B20"));
// -------------------------------------------------------------------
// Create a Above StdDev rule
// -------------------------------------------------------------------
var cfRule9 = worksheet.ConditionalFormatting.AddAboveStdDev(
new ExcelAddress("B11:B20"));
cfRule9.StdDev = 0;
// -------------------------------------------------------------------
// Create a Below StdDev rule
// -------------------------------------------------------------------
var cfRule10 = worksheet.ConditionalFormatting.AddBelowStdDev(
new ExcelAddress("B11:B20"));
cfRule10.StdDev = 2;
// -------------------------------------------------------------------
// Create a Bottom rule
// -------------------------------------------------------------------
var cfRule11 = worksheet.ConditionalFormatting.AddBottom(
new ExcelAddress("B11:B20"));
cfRule11.Rank = 4;
// -------------------------------------------------------------------
// Create a Bottom Percent rule
// -------------------------------------------------------------------
var cfRule12 = worksheet.ConditionalFormatting.AddBottomPercent(
new ExcelAddress("B11:B20"));
cfRule12.Rank = 15;
// -------------------------------------------------------------------
// Create a Top rule
// -------------------------------------------------------------------
var cfRule13 = worksheet.ConditionalFormatting.AddTop(
new ExcelAddress("B11:B20"));
// -------------------------------------------------------------------
// Create a Top Percent rule
// -------------------------------------------------------------------
var cfRule14 = worksheet.ConditionalFormatting.AddTopPercent(
new ExcelAddress("B11:B20"));
cfRule14.Style.Border.Left.Style = ExcelBorderStyle.Thin;
cfRule14.Style.Border.Left.Color.Theme = 3;
cfRule14.Style.Border.Bottom.Style = ExcelBorderStyle.DashDot;
cfRule14.Style.Border.Bottom.Color.Index=8;
cfRule14.Style.Border.Right.Style = ExcelBorderStyle.Thin;
cfRule14.Style.Border.Right.Color.Color=Color.Blue;
cfRule14.Style.Border.Top.Style = ExcelBorderStyle.Hair;
cfRule14.Style.Border.Top.Color.Auto=true;
// -------------------------------------------------------------------
// Create a Last 7 Days rule
// -------------------------------------------------------------------
ExcelAddress timePeriodAddress = new ExcelAddress("D21:G34 C11:C20");
var cfRule15 = worksheet.ConditionalFormatting.AddLast7Days(
timePeriodAddress);
cfRule15.Style.Fill.PatternType = ExcelFillStyle.LightTrellis;
cfRule15.Style.Fill.PatternColor.Color = Color.BurlyWood;
cfRule15.Style.Fill.BackgroundColor.Color = Color.LightCyan;
// -------------------------------------------------------------------
// Create a Last Month rule
// -------------------------------------------------------------------
var cfRule16 = worksheet.ConditionalFormatting.AddLastMonth(
timePeriodAddress);
cfRule16.Style.NumberFormat.Format = "YYYY";
// -------------------------------------------------------------------
// Create a Last Week rule
// -------------------------------------------------------------------
var cfRule17 = worksheet.ConditionalFormatting.AddLastWeek(
timePeriodAddress);
cfRule17.Style.NumberFormat.Format = "YYYY";
// -------------------------------------------------------------------
// Create a Next Month rule
// -------------------------------------------------------------------
var cfRule18 = worksheet.ConditionalFormatting.AddNextMonth(
timePeriodAddress);
// -------------------------------------------------------------------
// Create a Next Week rule
// -------------------------------------------------------------------
var cfRule19 = worksheet.ConditionalFormatting.AddNextWeek(
timePeriodAddress);
// -------------------------------------------------------------------
// Create a This Month rule
// -------------------------------------------------------------------
var cfRule20 = worksheet.ConditionalFormatting.AddThisMonth(
timePeriodAddress);
// -------------------------------------------------------------------
// Create a This Week rule
// -------------------------------------------------------------------
var cfRule21 = worksheet.ConditionalFormatting.AddThisWeek(
timePeriodAddress);
// -------------------------------------------------------------------
// Create a Today rule
// -------------------------------------------------------------------
var cfRule22 = worksheet.ConditionalFormatting.AddToday(
timePeriodAddress);
// -------------------------------------------------------------------
// Create a Tomorrow rule
// -------------------------------------------------------------------
var cfRule23 = worksheet.ConditionalFormatting.AddTomorrow(
timePeriodAddress);
// -------------------------------------------------------------------
// Create a Yesterday rule
// -------------------------------------------------------------------
var cfRule24 = worksheet.ConditionalFormatting.AddYesterday(
timePeriodAddress);
// -------------------------------------------------------------------
// Create a BeginsWith rule
// -------------------------------------------------------------------
ExcelAddress cellIsAddress = new ExcelAddress("E11:E20");
var cfRule25 = worksheet.ConditionalFormatting.AddBeginsWith(
cellIsAddress);
cfRule25.Text = "SearchMe";
// -------------------------------------------------------------------
// Create a Between rule
// -------------------------------------------------------------------
var cfRule26 = worksheet.ConditionalFormatting.AddBetween(
cellIsAddress);
cfRule26.Formula = "IF(E11>5,10,20)";
cfRule26.Formula2 = "IF(E11>5,30,50)";
// -------------------------------------------------------------------
// Create a ContainsBlanks rule
// -------------------------------------------------------------------
var cfRule27 = worksheet.ConditionalFormatting.AddContainsBlanks(
cellIsAddress);
// -------------------------------------------------------------------
// Create a ContainsErrors rule
// -------------------------------------------------------------------
var cfRule28 = worksheet.ConditionalFormatting.AddContainsErrors(
cellIsAddress);
// -------------------------------------------------------------------
// Create a ContainsText rule
// -------------------------------------------------------------------
var cfRule29 = worksheet.ConditionalFormatting.AddContainsText(
cellIsAddress);
cfRule29.Text = "Me";
// -------------------------------------------------------------------
// Create a DuplicateValues rule
// -------------------------------------------------------------------
var cfRule30 = worksheet.ConditionalFormatting.AddDuplicateValues(
cellIsAddress);
// -------------------------------------------------------------------
// Create an EndsWith rule
// -------------------------------------------------------------------
var cfRule31 = worksheet.ConditionalFormatting.AddEndsWith(
cellIsAddress);
cfRule31.Text = "EndText";
// -------------------------------------------------------------------
// Create an Equal rule
// -------------------------------------------------------------------
var cfRule32 = worksheet.ConditionalFormatting.AddEqual(
cellIsAddress);
cfRule32.Formula = "6";
// -------------------------------------------------------------------
// Create an Expression rule
// -------------------------------------------------------------------
var cfRule33 = worksheet.ConditionalFormatting.AddExpression(
cellIsAddress);
cfRule33.Formula = "E11=E12";
// -------------------------------------------------------------------
// Create a GreaterThan rule
// -------------------------------------------------------------------
var cfRule34 = worksheet.ConditionalFormatting.AddGreaterThan(
cellIsAddress);
cfRule34.Formula = "SE(E11<10,10,65)";
// -------------------------------------------------------------------
// Create a GreaterThanOrEqual rule
// -------------------------------------------------------------------
var cfRule35 = worksheet.ConditionalFormatting.AddGreaterThanOrEqual(
cellIsAddress);
cfRule35.Formula = "35";
// -------------------------------------------------------------------
// Create a LessThan rule
// -------------------------------------------------------------------
var cfRule36 = worksheet.ConditionalFormatting.AddLessThan(
cellIsAddress);
cfRule36.Formula = "36";
// -------------------------------------------------------------------
// Create a LessThanOrEqual rule
// -------------------------------------------------------------------
var cfRule37 = worksheet.ConditionalFormatting.AddLessThanOrEqual(
cellIsAddress);
cfRule37.Formula = "37";
// -------------------------------------------------------------------
// Create a NotBetween rule
// -------------------------------------------------------------------
var cfRule38 = worksheet.ConditionalFormatting.AddNotBetween(
cellIsAddress);
cfRule38.Formula = "333";
cfRule38.Formula2 = "999";
// -------------------------------------------------------------------
// Create a NotContainsBlanks rule
// -------------------------------------------------------------------
var cfRule39 = worksheet.ConditionalFormatting.AddNotContainsBlanks(
cellIsAddress);
// -------------------------------------------------------------------
// Create a NotContainsErrors rule
// -------------------------------------------------------------------
var cfRule40 = worksheet.ConditionalFormatting.AddNotContainsErrors(
cellIsAddress);
// -------------------------------------------------------------------
// Create a NotContainsText rule
// -------------------------------------------------------------------
var cfRule41 = worksheet.ConditionalFormatting.AddNotContainsText(
cellIsAddress);
cfRule41.Text = "NotMe";
// -------------------------------------------------------------------
// Create an NotEqual rule
// -------------------------------------------------------------------
var cfRule42 = worksheet.ConditionalFormatting.AddNotEqual(
cellIsAddress);
cfRule42.Formula = "14";
// -----------------------------------------------------------
// Removing Conditional Formatting rules
// -----------------------------------------------------------
// Remove one Rule by its object
//worksheet.ConditionalFormatting.Remove(cfRule1);
// Remove one Rule by index
//worksheet.ConditionalFormatting.RemoveAt(1);
// Remove one Rule by its Priority
//worksheet.ConditionalFormatting.RemoveByPriority(2);
// Remove all the Rules
//worksheet.ConditionalFormatting.RemoveAll();
// set some document properties
package.Workbook.Properties.Title = "Conditional Formatting";
package.Workbook.Properties.Author = "Eyal Seagull";
package.Workbook.Properties.Comments = "This sample demonstrates how to add Conditional Formatting to an Excel 2007 worksheet using EPPlus";
// set some custom property values
package.Workbook.Properties.SetCustomPropertyValue("Checked by", "Eyal Seagull");
package.Workbook.Properties.SetCustomPropertyValue("AssemblyName", "EPPlus");
// save our new workbook and we are done!
package.Save();
}
return newFile.FullName;
}
private void SetStyleAddress(StyleBase sender, Style.StyleChangeEventArgs e, ExcelAddress address, ExcelWorksheet ws, ref Dictionary <int, int> styleCashe)
{
if (address.Start.Column == 0 || address.Start.Row == 0)
{
throw (new Exception("error address"));
}
//Columns
else if (address.Start.Row == 1 && address.End.Row == ExcelPackage.MaxRows)
{
ExcelColumn column;
int col = address.Start.Column, row = 0;
bool isNew;
//Get the startcolumn
object o = null;
if (!ws.ExistsValueInner(0, address.Start.Column, ref o))
{
column = ws.Column(address.Start.Column);
isNew = true;
}
else
{
//column = (ExcelColumn)ws.GetValueInner(0, address.Start.Column);
column = (ExcelColumn)o;
isNew = false;
}
var prevColumMax = column.ColumnMax;
while (column.ColumnMin <= address.End.Column)
{
if (column.ColumnMin > prevColumMax + 1)
{
var newColumn = ws.Column(prevColumMax + 1);
newColumn.ColumnMax = column.ColumnMin - 1;
AddNewStyleColumn(sender, e, ws, styleCashe, newColumn, newColumn.StyleID);
}
if (column.ColumnMax > address.End.Column)
{
var newCol = ws.CopyColumn(column, address.End.Column + 1, column.ColumnMax);
column.ColumnMax = address.End.Column;
}
var s = ws.GetStyleInner(0, column.ColumnMin);
AddNewStyleColumn(sender, e, ws, styleCashe, column, s);
//index++;
prevColumMax = column.ColumnMax;
if (!ws._values.NextCell(ref row, ref col) || row > 0)
{
if (column._columnMax == address.End.Column)
{
break;
}
if (isNew)
{
column._columnMax = address.End.Column;
}
else
{
var newColumn = ws.Column(column._columnMax + 1);
newColumn.ColumnMax = address.End.Column;
AddNewStyleColumn(sender, e, ws, styleCashe, newColumn, newColumn.StyleID);
}
break;
}
else
{
column = (ws.GetValueInner(0, col) as ExcelColumn);
}
}
if (column._columnMax < address.End.Column)
{
var newCol = ws.Column(column._columnMax + 1) as ExcelColumn;
newCol._columnMax = address.End.Column;
var s = ws.GetStyleInner(0, column.ColumnMin);
if (styleCashe.ContainsKey(s))
{
ws.SetStyleInner(0, column.ColumnMin, styleCashe[s]);
}
else
{
ExcelXfs st = CellXfs[s];
int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(s, newId);
ws.SetStyleInner(0, column.ColumnMin, newId);
}
column._columnMax = address.End.Column;
}
//Set for individual cells in the span. We loop all cells here since the cells are sorted with columns first.
var cse = ws._values.GetEnumerator(1, address._fromCol, address._toRow, address._toCol);
while (cse.MoveNext())
{
if (cse.Column >= address.Start.Column &&
cse.Column <= address.End.Column &&
cse.Value._styleId != 0)
{
if (styleCashe.ContainsKey(cse.Value._styleId))
{
ws.SetStyleInner(cse.Row, cse.Column, styleCashe[cse.Value._styleId]);
}
else
{
ExcelXfs st = CellXfs[cse.Value._styleId];
int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(cse.Value._styleId, newId);
ws.SetStyleInner(cse.Row, cse.Column, newId);
}
}
}
if (!(address._fromCol == 1 && address._toCol == ExcelPackage.MaxColumns))
{
//Update cells with styled columns
cse = ws._values.GetEnumerator(1, 0, address._toRow, 0);
while (cse.MoveNext())
{
if (cse.Value._styleId == 0)
{
continue;
}
for (int c = address._fromRow; c <= address._toCol; c++)
{
if (!ws.ExistsStyleInner(cse.Row, c))
{
if (styleCashe.ContainsKey(cse.Value._styleId))
{
ws.SetStyleInner(cse.Row, c, styleCashe[cse.Value._styleId]);
}
else
{
ExcelXfs st = CellXfs[cse.Value._styleId];
int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(cse.Value._styleId, newId);
ws.SetStyleInner(cse.Row, c, newId);
}
}
}
}
}
}
//Rows
else if (address.Start.Column == 1 && address.End.Column == ExcelPackage.MaxColumns)
{
for (int rowNum = address.Start.Row; rowNum <= address.End.Row; rowNum++)
{
var s = ws.GetStyleInner(rowNum, 0);
if (s == 0)
{
//iterate all columns and set the row to the style of the last column
var cse = ws._values.GetEnumerator(0, 1, 0, ExcelPackage.MaxColumns);
while (cse.MoveNext())
{
s = cse.Value._styleId;
if (s == 0)
{
continue;
}
var c = ws.GetValueInner(cse.Row, cse.Column) as ExcelColumn;
if (c != null && c.ColumnMax < ExcelPackage.MaxColumns)
{
for (int col = c.ColumnMin; col < c.ColumnMax; col++)
{
if (!ws.ExistsStyleInner(rowNum, col))
{
ws.SetStyleInner(rowNum, col, s);
}
}
}
}
ws.SetStyleInner(rowNum, 0, s);
cse.Dispose();
}
if (styleCashe.ContainsKey(s))
{
ws.SetStyleInner(rowNum, 0, styleCashe[s]);
}
else
{
ExcelXfs st = CellXfs[s];
int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(s, newId);
ws.SetStyleInner(rowNum, 0, newId);
}
}
//Update individual cells
var cse2 = ws._values.GetEnumerator(address._fromRow, address._fromCol, address._toRow, address._toCol);
while (cse2.MoveNext())
{
var s = cse2.Value._styleId;
if (s == 0)
{
continue;
}
if (styleCashe.ContainsKey(s))
{
ws.SetStyleInner(cse2.Row, cse2.Column, styleCashe[s]);
}
else
{
ExcelXfs st = CellXfs[s];
int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(s, newId);
ws.SetStyleInner(cse2.Row, cse2.Column, newId);
}
}
//Update cells with styled rows
cse2 = ws._values.GetEnumerator(0, 1, 0, address._toCol);
while (cse2.MoveNext())
{
if (cse2.Value._styleId == 0)
{
continue;
}
for (int r = address._fromRow; r <= address._toRow; r++)
{
if (!ws.ExistsStyleInner(r, cse2.Column))
{
var s = cse2.Value._styleId;
if (styleCashe.ContainsKey(s))
{
ws.SetStyleInner(r, cse2.Column, styleCashe[s]);
}
else
{
ExcelXfs st = CellXfs[s];
int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(s, newId);
ws.SetStyleInner(r, cse2.Column, newId);
}
}
}
}
}
else //Cellrange
{
var tmpCache = styleCashe;
int rowCount = address.End.Row - address.Start.Row + 1;
var rowCache = new Dictionary <int, int>(rowCount);
int columnCount = address.End.Column - address.Start.Column + 1;
var colCache = new Dictionary <int, ExcelCoreValue>(columnCount);
for (int row = address.Start.Row; row < rowCount + address.Start.Row; row++)
{
for (int column = address.Start.Column; column < columnCount + address.Start.Column; column++)
{
// Optimized GetStyleID
var s = ws.GetStyleInner(row, column);
if (s == 0 && !ws.ExistsStyleInner(row, 0, ref s))
{
// get row styleId with cache
if (!rowCache.ContainsKey(row))
{
rowCache.Add(row, ws._values.GetValue(row, 0)._styleId);
}
s = rowCache[row];
if (s == 0)
{
// get column styleId with cache
if (!colCache.ContainsKey(column))
{
colCache.Add(column, ws._values.GetValue(0, column));
}
s = colCache[column]._styleId;
if (s == 0)
{
int r = 0, c = column;
if (ws._values.PrevCell(ref r, ref c))
{
//var val = ws._values.GetValue(0, c);
if (!colCache.ContainsKey(c))
{
colCache.Add(c, ws._values.GetValue(0, c));
}
var val = colCache[c];
var colObj = (ExcelColumn)(val._value);
if (colObj != null && colObj.ColumnMax >= column) //Fixes issue 15174
{
s = val._styleId;
}
}
}
}
}
if (tmpCache.ContainsKey(s))
{
ws.SetStyleInner(row, column, tmpCache[s]);
}
else
{
ExcelXfs st = CellXfs[s];
int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
tmpCache.Add(s, newId);
ws.SetStyleInner(row, column, newId);
}
}
}
}
}
private static async Task loadScoresAttributesSummarySheet(List<ScoresAttributesSummary> scoresAttrList, ExcelWorksheet scoresAttrSheet)
{
Task t = new Task
(
() =>
{
var dataRange = scoresAttrSheet.Cells["A1"].LoadFromCollection(
from s in scoresAttrList orderby s.AttrChangeCount descending
select s, true, OfficeOpenXml.Table.TableStyles.Medium2);
scoresAttrSheet.Cells.AutoFitColumns();
ExcelAddress percentChange = new ExcelAddress(2, 12,dataRange.End.Row,12);
var percentChangeRule = scoresAttrSheet.ConditionalFormatting.AddDatabar(percentChange, System.Drawing.Color.Green);
}
);
t.Start();
await t;
}
internal ExcelProtectedRange(string name, ExcelAddress address, XmlNamespaceManager ns, XmlNode topNode) :
base(ns,topNode)
{
Name = name;
Address = address;
}
internal ExcelProtectedRange(string name, ExcelAddress address, XmlNamespaceManager ns, XmlNode topNode) :
base(ns, topNode)
{
Name = name;
Address = address;
}
public void InsertDeleteTest()
{
ExcelWorksheet ws = _pck.Workbook.Worksheets.Add("InsertDelete");
ws.Cells["A1:C5"].Value = 1;
ws.Cells["A1:B3"].Merge = true;
ws.Cells["D3"].Formula = "A2+C5";
ws.InsertRow(2, 1);
ws.Cells["A10:C15"].Value = 1;
ws.Cells["A11:B13"].Merge = true;
ws.DeleteRow(12, 1,true);
ws.Cells["a1:B100"].Style.Locked = false;
ws.Cells["a1:B12"].Style.Hidden = true;
ws.Protection.IsProtected=true;
ws.Protection.SetPassword("Password");
var range = ws.Cells["B2:D100"];
Assert.AreEqual(range.Start.Column, 2);
Assert.AreEqual(range.Start.Row, 2);
Assert.AreEqual(range.Start.Address, "B2");
Assert.AreEqual(range.End.Column, 4);
Assert.AreEqual(range.End.Row, 100);
Assert.AreEqual(range.End.Address, "D100");
ExcelAddress addr = new ExcelAddress("B1:D3");
Assert.AreEqual(addr.Start.Column, 2);
Assert.AreEqual(addr.Start.Row, 1);
Assert.AreEqual(addr.End.Column, 4);
Assert.AreEqual(addr.End.Row, 3);
}
internal void GetDefinedNames()
{
XmlNodeList nl = WorkbookXml.SelectNodes("//d:definedNames/d:definedName", NameSpaceManager);
if (nl != null)
{
foreach (XmlElement elem in nl)
{
string fullAddress = elem.InnerText;
int localSheetID;
ExcelWorksheet nameWorksheet;
if(!int.TryParse(elem.GetAttribute("localSheetId"), out localSheetID))
{
localSheetID = -1;
nameWorksheet=null;
}
else
{
nameWorksheet=Worksheets[localSheetID + 1];
}
var addressType = ExcelAddressBase.IsValid(fullAddress);
ExcelRangeBase range;
ExcelNamedRange namedRange;
if (fullAddress.IndexOf("[") == 0)
{
int start = fullAddress.IndexOf("[");
int end = fullAddress.IndexOf("]", start);
if (start >= 0 && end >= 0)
{
string externalIndex = fullAddress.Substring(start + 1, end - start - 1);
int index;
if (int.TryParse(externalIndex, out index))
{
if (index > 0 && index <= _externalReferences.Count)
{
fullAddress = fullAddress.Substring(0, start) + "[" + _externalReferences[index - 1] + "]" + fullAddress.Substring(end + 1);
}
}
}
}
if (addressType == ExcelAddressBase.AddressType.Invalid || addressType == ExcelAddressBase.AddressType.InternalName || addressType == ExcelAddressBase.AddressType.ExternalName || addressType==ExcelAddressBase.AddressType.Formula || addressType==ExcelAddressBase.AddressType.ExternalAddress) //A value or a formula
{
double value;
range = new ExcelRangeBase(this, nameWorksheet, elem.GetAttribute("name"), true);
if (nameWorksheet == null)
{
namedRange = _names.Add(elem.GetAttribute("name"), range);
}
else
{
namedRange = nameWorksheet.Names.Add(elem.GetAttribute("name"), range);
}
if (fullAddress.StartsWith("\"")) //String value
{
namedRange.NameValue = fullAddress.Substring(1,fullAddress.Length-2);
}
else if (double.TryParse(fullAddress, NumberStyles.Any, CultureInfo.InvariantCulture, out value))
{
namedRange.NameValue = value;
}
else
{
//if (addressType == ExcelAddressBase.AddressType.ExternalAddress || addressType == ExcelAddressBase.AddressType.ExternalName)
//{
// var r = new ExcelAddress(fullAddress);
// namedRange.NameFormula = '\'[' + r._wb
//}
//else
//{
namedRange.NameFormula = fullAddress;
//}
}
}
else
{
ExcelAddress addr = new ExcelAddress(fullAddress, _package, null);
if (localSheetID > -1)
{
if (string.IsNullOrEmpty(addr._ws))
{
namedRange = Worksheets[localSheetID + 1].Names.Add(elem.GetAttribute("name"), new ExcelRangeBase(this, Worksheets[localSheetID + 1], fullAddress, false));
}
else
{
namedRange = Worksheets[localSheetID + 1].Names.Add(elem.GetAttribute("name"), new ExcelRangeBase(this, Worksheets[addr._ws], fullAddress, false));
}
}
else
{
var ws = Worksheets[addr._ws];
namedRange = _names.Add(elem.GetAttribute("name"), new ExcelRangeBase(this, ws, fullAddress, false));
}
}
if (elem.GetAttribute("hidden") == "1" && namedRange != null) namedRange.IsNameHidden = true;
if(!string.IsNullOrEmpty(elem.GetAttribute("comment"))) namedRange.NameComment=elem.GetAttribute("comment");
}
}
}
public void ShouldHandleWorksheetSpec()
{
var address = "Sheet1!A1:Sheet1!A2";
var excelAddress = new ExcelAddress(address);
Assert.AreEqual("Sheet1", excelAddress.WorkSheet);
Assert.AreEqual(1, excelAddress._fromRow);
Assert.AreEqual(2, excelAddress._toRow);
}