/// <summary>
/// Loads the default configuration.
/// </summary>
/// <param name="Grid">The grid.</param>
private void LoadDefaultConfiguration(JDHSoftware.Krypton.Toolkit.KryptonOutlookGrid.KryptonOutlookGrid Grid)
{
Grid.ClearEverything();
Grid.GroupBox.Visible = true;
Grid.HideColumnOnGrouping = false;
Grid.FillMode = FillMode.GroupsAndNodes; //treemode enabled;
Grid.ShowLines = true;
activeColumns = new SandBoxGridColumn[] {
SandBoxGridColumn.ColumnCustomerID,
SandBoxGridColumn.ColumnCustomerName,
SandBoxGridColumn.ColumnAddress,
SandBoxGridColumn.ColumnCity,
SandBoxGridColumn.ColumnCountry,
SandBoxGridColumn.ColumnOrderDate,
SandBoxGridColumn.ColumnProduct,
SandBoxGridColumn.ColumnPrice,
SandBoxGridColumn.SatisfactionColumn,
SandBoxGridColumn.ColumnToken
};
DataGridViewColumn[] columnsToAdd = new DataGridViewColumn[10]
{
SetupColumn(SandBoxGridColumn.ColumnCustomerID),
SetupColumn(SandBoxGridColumn.ColumnCustomerName),
SetupColumn(SandBoxGridColumn.ColumnAddress),
SetupColumn(SandBoxGridColumn.ColumnCity),
SetupColumn(SandBoxGridColumn.ColumnCountry),
SetupColumn(SandBoxGridColumn.ColumnOrderDate),
SetupColumn(SandBoxGridColumn.ColumnProduct),
SetupColumn(SandBoxGridColumn.ColumnPrice),
SetupColumn(SandBoxGridColumn.SatisfactionColumn),
SetupColumn(SandBoxGridColumn.ColumnToken)
};
Grid.Columns.AddRange(columnsToAdd);
//Define the columns for a possible grouping
Grid.AddInternalColumn(columnsToAdd[0], new OutlookGridDefaultGroup(null), SortOrder.None, -1, -1);
Grid.AddInternalColumn(columnsToAdd[1], new OutlookGridAlphabeticGroup(null), SortOrder.None, -1, -1);
Grid.AddInternalColumn(columnsToAdd[2], new OutlookGridDefaultGroup(null), SortOrder.None, -1, -1);
Grid.AddInternalColumn(columnsToAdd[3], new OutlookGridDefaultGroup(null), SortOrder.None, -1, -1);
Grid.AddInternalColumn(columnsToAdd[4], new OutlookGridDefaultGroup(null), SortOrder.None, -1, -1);
Grid.AddInternalColumn(columnsToAdd[5], new OutlookGridDateTimeGroup(null), SortOrder.None, -1, -1);
Grid.AddInternalColumn(columnsToAdd[6], new OutlookGridDefaultGroup(null)
{
OneItemText = "1 product", XXXItemsText = " products"
}, SortOrder.None, -1, -1);
Grid.AddInternalColumn(columnsToAdd[7], new OutlookGridPriceGroup(null), SortOrder.None, -1, -1);
Grid.AddInternalColumn(columnsToAdd[8], new OutlookGridDefaultGroup(null), SortOrder.None, -1, -1);
Grid.AddInternalColumn(columnsToAdd[9], new OutlookGridDefaultGroup(null), SortOrder.None, -1, -1);
//Add a default conditionnal formatting
ConditionalFormatting cond = new ConditionalFormatting();
cond.ColumnName = SandBoxGridColumn.ColumnPrice.ToString();
cond.FormatType = EnumConditionalFormatType.TwoColorsRange;
cond.FormatParams = new TwoColorsParams(Color.White, Color.FromArgb(255, 255, 58, 61));
Grid.ConditionalFormatting.Add(cond);
}
public static bool SetConditionalFormatting(this Worksheet sheet, WorkbookPart book, string columnName,
uint startRow,
Func <ConditionalFormatting, bool> isConditionalFormatting,
Func <WorkbookPart, uint, ConditionalFormatting> CreateConditionalFormatting)
{
var conditionalFormattings = sheet.Elements <ConditionalFormatting>();
ConditionalFormatting conditionalFormatting = null;
for (int x = 0; x < conditionalFormattings.Count(); x++)
{
var currentConditionalFormatting = conditionalFormattings.ElementAt(x);
var cfRules = currentConditionalFormatting.Elements <ConditionalFormattingRule>();
if (isConditionalFormatting(currentConditionalFormatting))
{
conditionalFormatting = currentConditionalFormatting;
break;
}
}
if (conditionalFormatting == null)
{
conditionalFormatting = sheet.InsertAfter(CreateConditionalFormatting(book, startRow), conditionalFormattings.Count() > 0 ? (OpenXmlElement)conditionalFormattings.Last() : sheet.Elements <MergeCells>().First());
}
var sqref = conditionalFormatting.SequenceOfReferences;
var cellReference = columnName + startRow;
if (!sqref.Items.Any(s => s.Value == cellReference))
{
sqref.Items.Add(cellReference);
}
return(true);
}
public static void CreateConditionalFormatting()
{
var workbook = new Workbook(); //creating workbook and adding sheet
var companiesSheet = new Sheet("CompaniesByRevenue");
workbook.Sheets.AddSheet(companiesSheet);
companiesSheet.Columns[0].Width = 5;
companiesSheet.Columns[1].Width = 30;
companiesSheet.Columns[2].Width = 25;
companiesSheet.Columns[3].Width = 25;
companiesSheet.Columns[4].Width = 15;
var headingStyle = new CellStyle(); //define bold and centered header style
headingStyle.Font.Bold = true;
headingStyle.Alignment.Horizontal = HorizontalAlignment.Center;
var thousandsFormatStyle = new CellStyle(); //adding thousands separator format on Employees column
thousandsFormatStyle.Alignment.Horizontal = HorizontalAlignment.Center;
thousandsFormatStyle.Format = "#,#0";
var currencyFormatStyle = new CellStyle(); //adding $ (dollar) sign on Revenue and Capitalization columns
currencyFormatStyle.Format = "$#,#0";
currencyFormatStyle.Alignment.Horizontal = HorizontalAlignment.Center;
var table = companiesSheet["A2", "E6"];
table.GetRow(-1).SetValues("Rank", "Company", "Revenue (USD billions)", "Capitalization (USD billions)", "Employees").SetStyle(headingStyle);
table.GetColumn(0).SetValues(1, 2, 3, 4, 5);
table.GetColumn(1).SetValues("Wal-Mart Stores, Inc. (USA)", "Royal Dutch Shell (NLD)", "Exxon Mobil Corporation (USA)", "China National Petroleum (CHN)", "Sinopec Group (CHN)");
table.GetColumn(2).SetValues(469, 467, 453, 425, 411).SetStyle(currencyFormatStyle);
table.GetColumn(3).SetValues(248, 132, 406, null, 81).SetStyle(currencyFormatStyle);
table.GetColumn(4).SetValues(2200000, 90000, 76900, 1668072, 401000).SetStyle(thousandsFormatStyle);
var originFormating = new ConditionalFormatting(Range.Parse("B2:B6")); //creating ConditionalFormatting for specified range
originFormating.AddContainsText("USA", new CellStyle { Fill = CellFill.BackColor(Color.Red) });
originFormating.AddEndsWithText("(NLD)", new CellStyle { Fill = CellFill.BackColor(new Color("#0096FF")) });
originFormating.AddContainsText("CHN", new CellStyle { Fill = CellFill.BackColor(new Color("#FFFF96")) });
var revenueFormatting = new ConditionalFormatting();
revenueFormatting.AddRange("C2:C6"); //multiple ranges or cells can be added
revenueFormatting.AddDefaultIconSet(IconSetType.Item5Arrows); //add IconSet with 5 arrows
revenueFormatting.AddGreaterThan("460", new CellStyle { Font = new CellFont { Underline = FontUnderline.Single } });
var capitalizationFormatting = new ConditionalFormatting(Range.Parse("D1:D6")); //specifying range in constructor
capitalizationFormatting.AddColorScale(Color.Yellow, Color.Orange);
capitalizationFormatting.AddDefaultIconSet();
capitalizationFormatting.AddIsBlank(new CellStyle { Fill = CellFill.BackColor(Color.Red) }); //add style applied on blank cell
ConditionalFormatting employeesNumberFormating = new ConditionalFormatting(Range.Parse("E1:E6"));
employeesNumberFormating.AddDefaultDataBar(Color.Blue); //adding blue data bar conditional formatting rule
//adding different conditional formattings to sheet
companiesSheet.ConditionalFormatting.Add(originFormating);
companiesSheet.ConditionalFormatting.Add(revenueFormatting);
companiesSheet.ConditionalFormatting.Add(capitalizationFormatting);
companiesSheet.ConditionalFormatting.Add(employeesNumberFormating);
workbook.Save(@"ConditionalFormatting.xlsx");
}
public static void AddConditionalFormatting(this Worksheet worksheet, int rowStart, int rowCount, int columnStart, int columnCount)
{
string range = ComputeRange(rowStart, rowCount, columnStart, columnCount);
ConditionalFormatting conditionalFormatting1 = new ConditionalFormatting()
{
SequenceOfReferences = new ListValue <StringValue>()
{
InnerText = range
}
};
ConditionalFormattingRule conditionalFormattingRule1 =
new ConditionalFormattingRule()
{
Type = ConditionalFormatValues.ColorScale, Priority = 1
};
ColorScale colorScale1 = new ColorScale();
ConditionalFormatValueObject conditionalFormatValueObject1 = new ConditionalFormatValueObject()
{
Type = ConditionalFormatValueObjectValues.Number, Val = "0"
};
ConditionalFormatValueObject conditionalFormatValueObject2 = new ConditionalFormatValueObject()
{
Type = ConditionalFormatValueObjectValues.Percentile, Val = "50"
};
ConditionalFormatValueObject conditionalFormatValueObject3 = new ConditionalFormatValueObject()
{
Type = ConditionalFormatValueObjectValues.Number, Val = "100"
};
Color color1 = new Color()
{
Rgb = "FFF8696B"
};
Color color2 = new Color()
{
Rgb = "FFFFEB84"
};
Color color3 = new Color()
{
Rgb = "FF63BE7B"
};
colorScale1.Append(conditionalFormatValueObject1);
colorScale1.Append(conditionalFormatValueObject2);
colorScale1.Append(conditionalFormatValueObject3);
colorScale1.Append(color1);
colorScale1.Append(color2);
colorScale1.Append(color3);
conditionalFormattingRule1.Append(colorScale1);
conditionalFormatting1.Append(conditionalFormattingRule1);
// If we don't have this after SheetData, it corrupts the file if we have added hyperlinks before
worksheet.InsertAfter(conditionalFormatting1, worksheet.Descendants <SheetData>().First());
}
public void ComputeAggregates()
{
if (computeAggregateExecuted || GroupValues != null || TotalGroupValues != null)
{
return;
}
computeAggregateExecuted = true;
Column.ComputeAggregates(this, ColumnHierarchy.AggregateType);
ConditionalFormatting?.AddValue(GetValue());
}
public void SetCondition(string range, string opr, string formula, int priority, int style)
{
ConditionalFormatting conditionalFormatting = this.part.Worksheet.Elements <ConditionalFormatting>().FirstOrDefault();
if (conditionalFormatting == null)
{
conditionalFormatting = new ConditionalFormatting();
conditionalFormatting.SequenceOfReferences = new ListValue <StringValue>();
this.part.Worksheet.Append(conditionalFormatting);
}
conditionalFormatting.SequenceOfReferences.Items.Add(new StringValue()
{
InnerText = range
});
ConditionalFormattingOperatorValues mopr = ConditionalFormattingOperatorValues.Equal;
if (opr == "<")
{
mopr = ConditionalFormattingOperatorValues.LessThan;
}
if (opr == "<=")
{
mopr = ConditionalFormattingOperatorValues.LessThanOrEqual;
}
if (opr == ">")
{
mopr = ConditionalFormattingOperatorValues.GreaterThan;
}
if (opr == ">=")
{
mopr = ConditionalFormattingOperatorValues.GreaterThanOrEqual;
}
ConditionalFormattingRule rule = new ConditionalFormattingRule()
{
Type = ConditionalFormatValues.CellIs,
FormatId = (UInt32)style,
Priority = priority,
Operator = mopr
};
rule.Append(new Formula(formula));
conditionalFormatting.Append(rule);
}
/// <summary>
/// Добавить условное форматирование
/// </summary>
/// <param name="worksheet">Лист в который добавляется форматирование</param>
/// <param name="formattingExpression">Выражение которое определяет ячейки для форматирования</param>
/// <param name="style">Стиль условного форматирования</param>
/// <param name="targetCellAddresses">Области ячеек для которых будет задействовано данное условие</param>
public static void AddFormattingRule(this Worksheet worksheet, string formattingExpression, DifferentialFormat style, params string[] targetCellAddresses)
{
if (targetCellAddresses == null || targetCellAddresses.Count() == 0)
{
targetCellAddresses = new string[] { "1:1048576" };
}
var styleSheet = worksheet.GetWorkbookPart().GetStylesheet();
if (styleSheet.DifferentialFormats == null)
{
styleSheet.DifferentialFormats = new DifferentialFormats()
{
Count = 0
};
}
var dfList = styleSheet.DifferentialFormats;
dfList.AddDFormat(style);
var formattingRule = Fabric.MakeFormattingRule(formattingExpression);
formattingRule.FormatId = (uint)style.Index();
IEnumerable <StringValue> stringValues = targetCellAddresses.Select(rng => new StringValue(rng));
var sqref = new ListValue <StringValue>(stringValues);
var condFormatting = new ConditionalFormatting(formattingRule)
{
SequenceOfReferences = sqref
};
worksheet.Insert(condFormatting).AfterOneOf(
typeof(MergeCells)
, typeof(CustomSheetView)
, typeof(DataConsolidate)
, typeof(SortState)
, typeof(AutoFilter)
, typeof(Scenarios)
, typeof(ProtectedRanges)
, typeof(SheetProtection)
, typeof(SheetCalculationProperties)
, typeof(SheetData)
);
}
private void AddConditionalFormatting(Worksheet ws, int index)
{
var conditionalFormatting1 =
new ConditionalFormatting {
SequenceOfReferences = new ListValue <StringValue> {
InnerText = formatReference
}
};
var conditionalFormattingRule1 =
new ConditionalFormattingRule {
Type = ConditionalFormatValues.Expression, FormatId = (uint)index, Priority = 1
};
var formula1 = new Formula {
Text = formatFormula
};
conditionalFormattingRule1.Append(formula1);
conditionalFormatting1.Append(conditionalFormattingRule1);
var pp = ws.Descendants <PhoneticProperties>()
.FirstOrDefault();
ws.InsertAfter(conditionalFormatting1, pp);
}
public void AppendConditionalFormatting(String[] sequenceOfReferences, UInt32 formatId, String formula)
{
ConditionalFormatting conditionalFormatting = new ConditionalFormatting()
{
SequenceOfReferences = new ListValue <StringValue>()
{
InnerText = sequenceOfReferences[0] // пока только первая стока
}
};
ConditionalFormattingRule conditionalFormattingRule = new ConditionalFormattingRule()
{
Type = ConditionalFormatValues.Expression,
FormatId = formatId,
Priority = 1
};
conditionalFormattingRule.Append(new Formula()
{
Text = formula
});
conditionalFormatting.Append(conditionalFormattingRule);
worksheet.Append(conditionalFormatting);
}
private PageSetup GetPageSetup()
{
PageSetup pageSetup = worksheet.GetFirstChild <PageSetup>();
if (pageSetup == null)
{
pageSetup = new PageSetup();
// ищем куда вставить
ConditionalFormatting conditionalFormatting = null;
var conditionalFormattings = worksheet.Elements <ConditionalFormatting>();
if (conditionalFormattings != null && conditionalFormattings.Count <ConditionalFormatting>() > 0)
{
conditionalFormatting = conditionalFormattings.Last <ConditionalFormatting>();
}
if (conditionalFormatting != null)
{
conditionalFormatting.InsertAfterSelf <PageSetup>(pageSetup);
}
else
{
MergeCells mergeCells = worksheet.GetFirstChild <MergeCells>();
if (mergeCells != null)
{
mergeCells.InsertAfterSelf <PageSetup>(pageSetup);
}
else
{
SheetData sheetData = worksheet.GetFirstChild <SheetData>();
if (sheetData != null)
{
sheetData.InsertAfterSelf <PageSetup>(pageSetup);
}
}
}
}
return(pageSetup);
}
static void Main(string[] args)
{
var workbookClosed = new XLWorkbook();
var sheet = workbookClosed.Worksheets.Add("Sheet1");
sheet.Cell(1, 1).Value = 1;
sheet.Cell(2, 1).Value = -1;
workbookClosed.SaveAs("closed.xlsx");
using (SpreadsheetDocument report = SpreadsheetDocument.Open("closed.xlsx", true))
{
Workbook workbook = report.WorkbookPart.Workbook;
WorksheetPart worksheetPart = workbook.WorkbookPart.WorksheetParts.First();
DifferentialFormats differentialFormats = new DifferentialFormats() { Count = (UInt32Value)2U };
DifferentialFormat lessThanFormat = new DifferentialFormat();
Font lessThanFont = new Font();
lessThanFont.Append(new Condense() { Val = false });
lessThanFont.Append(new Extend() { Val = false });
lessThanFont.Append(new Color() { Rgb = "FF9C0006" });
Fill lessThanFill = new Fill();
PatternFill lessThanPatternFill = new PatternFill();
lessThanPatternFill.Append(new BackgroundColor() { Rgb = "FFFFC7CE" });
lessThanFill.Append(lessThanPatternFill);
lessThanFormat.Append(lessThanFont);
lessThanFormat.Append(lessThanFill);
DifferentialFormat greaterThanFormat = new DifferentialFormat();
Font greaterThanFont = new Font();
greaterThanFont.Append(new Condense() { Val = false });
greaterThanFont.Append(new Extend() { Val = false });
greaterThanFont.Append(new Color() { Rgb = "FF006100" });
Fill greatherThanFill = new Fill();
PatternFill greaterThanPatternFill = new PatternFill();
greaterThanPatternFill.Append(new BackgroundColor() { Rgb = "FFC6EFCE" });
greatherThanFill.Append(greaterThanPatternFill);
greaterThanFormat.Append(greaterThanFont);
greaterThanFormat.Append(greatherThanFill);
differentialFormats.Append(lessThanFormat);
differentialFormats.Append(greaterThanFormat);
workbook.WorkbookPart.WorkbookStylesPart.Stylesheet.Append(differentialFormats);
ConditionalFormatting conditionalFormatting = new ConditionalFormatting()
{
SequenceOfReferences = new ListValue<StringValue>() { InnerText = "D2:D10" }
};
ConditionalFormattingRule greaterThanRule = new ConditionalFormattingRule()
{
Type = ConditionalFormatValues.CellIs,
FormatId = (UInt32Value)1U,
Priority = 2,
Operator = ConditionalFormattingOperatorValues.GreaterThan
};
Formula greaterThanFormula = new Formula();
greaterThanFormula.Text = "0";
greaterThanRule.Append(greaterThanFormula);
ConditionalFormattingRule lessThanRule = new ConditionalFormattingRule()
{
Type = ConditionalFormatValues.CellIs,
FormatId = (UInt32Value)0U,
Priority = 1,
Operator = ConditionalFormattingOperatorValues.LessThan
};
Formula lessThanFormula = new Formula();
lessThanFormula.Text = "0";
lessThanRule.Append(lessThanFormula);
conditionalFormatting.Append(greaterThanRule);
conditionalFormatting.Append(lessThanRule);
worksheetPart.Worksheet.PrependChild<ConditionalFormatting>(conditionalFormatting);
report.WorkbookPart.Workbook.Save();
report.Close();
}
}
internal void FromConditionalFormatting(ConditionalFormatting cf)
{
this.SetAllNull();
if (cf.Pivot != null) this.Pivot = cf.Pivot.Value;
SLCellPointRange pt;
int index;
int iStartRowIndex = -1;
int iStartColumnIndex = -1;
int iEndRowIndex = -1;
int iEndColumnIndex = -1;
foreach (var s in cf.SequenceOfReferences.Items)
{
index = s.Value.IndexOf(":");
if (index > -1)
{
if (SLTool.FormatCellReferenceRangeToRowColumnIndex(s.Value, out iStartRowIndex, out iStartColumnIndex, out iEndRowIndex, out iEndColumnIndex))
{
pt = new SLCellPointRange(iStartRowIndex, iStartColumnIndex, iEndRowIndex, iEndColumnIndex);
this.SequenceOfReferences.Add(pt);
}
}
else
{
if (SLTool.FormatCellReferenceToRowColumnIndex(s.Value, out iStartRowIndex, out iStartColumnIndex))
{
pt = new SLCellPointRange(iStartRowIndex, iStartColumnIndex, iStartRowIndex, iStartColumnIndex);
this.SequenceOfReferences.Add(pt);
}
}
}
foreach (var rule in cf.Elements<ConditionalFormattingRule>())
{
var cfr = new SLConditionalFormattingRule();
cfr.FromConditionalFormattingRule(rule);
this.Rules.Add(cfr);
}
}
private static void GenerateWorksheetPartContent(WorksheetPart worksheetPart, XLWorksheet xlWorksheet,
SaveContext context)
{
#region Worksheet
if (worksheetPart.Worksheet == null)
worksheetPart.Worksheet = new Worksheet();
GenerateTables(xlWorksheet, worksheetPart, context);
if (
!worksheetPart.Worksheet.NamespaceDeclarations.Contains(new KeyValuePair<String, String>("r",
"http://schemas.openxmlformats.org/officeDocument/2006/relationships")))
{
worksheetPart.Worksheet.AddNamespaceDeclaration("r",
"http://schemas.openxmlformats.org/officeDocument/2006/relationships");
}
#endregion
var cm = new XLWSContentManager(worksheetPart.Worksheet);
#region SheetProperties
if (worksheetPart.Worksheet.SheetProperties == null)
worksheetPart.Worksheet.SheetProperties = new SheetProperties();
worksheetPart.Worksheet.SheetProperties.TabColor = xlWorksheet.TabColor.HasValue
? GetTabColor(xlWorksheet.TabColor)
: null;
cm.SetElement(XLWSContentManager.XLWSContents.SheetProperties, worksheetPart.Worksheet.SheetProperties);
if (worksheetPart.Worksheet.SheetProperties.OutlineProperties == null)
worksheetPart.Worksheet.SheetProperties.OutlineProperties = new OutlineProperties();
worksheetPart.Worksheet.SheetProperties.OutlineProperties.SummaryBelow =
(xlWorksheet.Outline.SummaryVLocation ==
XLOutlineSummaryVLocation.Bottom);
worksheetPart.Worksheet.SheetProperties.OutlineProperties.SummaryRight =
(xlWorksheet.Outline.SummaryHLocation ==
XLOutlineSummaryHLocation.Right);
if (worksheetPart.Worksheet.SheetProperties.PageSetupProperties == null
&& (xlWorksheet.PageSetup.PagesTall > 0 || xlWorksheet.PageSetup.PagesWide > 0))
worksheetPart.Worksheet.SheetProperties.PageSetupProperties = new PageSetupProperties {FitToPage = true};
#endregion
var maxColumn = 0;
var sheetDimensionReference = "A1";
if (xlWorksheet.Internals.CellsCollection.Count > 0)
{
maxColumn = xlWorksheet.Internals.CellsCollection.MaxColumnUsed;
var maxRow = xlWorksheet.Internals.CellsCollection.MaxRowUsed;
sheetDimensionReference = "A1:" + XLHelper.GetColumnLetterFromNumber(maxColumn) +
maxRow.ToStringLookup();
}
if (xlWorksheet.Internals.ColumnsCollection.Count > 0)
{
var maxColCollection = xlWorksheet.Internals.ColumnsCollection.Keys.Max();
if (maxColCollection > maxColumn)
maxColumn = maxColCollection;
}
#region SheetViews
if (worksheetPart.Worksheet.SheetDimension == null)
worksheetPart.Worksheet.SheetDimension = new SheetDimension {Reference = sheetDimensionReference};
cm.SetElement(XLWSContentManager.XLWSContents.SheetDimension, worksheetPart.Worksheet.SheetDimension);
if (worksheetPart.Worksheet.SheetViews == null)
worksheetPart.Worksheet.SheetViews = new SheetViews();
cm.SetElement(XLWSContentManager.XLWSContents.SheetViews, worksheetPart.Worksheet.SheetViews);
var sheetView = (SheetView)worksheetPart.Worksheet.SheetViews.FirstOrDefault();
if (sheetView == null)
{
sheetView = new SheetView {WorkbookViewId = 0U};
worksheetPart.Worksheet.SheetViews.AppendChild(sheetView);
}
if (xlWorksheet.TabSelected)
sheetView.TabSelected = true;
else
sheetView.TabSelected = null;
if (xlWorksheet.ShowFormulas)
sheetView.ShowFormulas = true;
else
sheetView.ShowFormulas = null;
if (xlWorksheet.ShowGridLines)
sheetView.ShowGridLines = null;
else
sheetView.ShowGridLines = false;
if (xlWorksheet.ShowOutlineSymbols)
sheetView.ShowOutlineSymbols = null;
else
sheetView.ShowOutlineSymbols = false;
if (xlWorksheet.ShowRowColHeaders)
sheetView.ShowRowColHeaders = null;
else
sheetView.ShowRowColHeaders = false;
if (xlWorksheet.ShowRuler)
sheetView.ShowRuler = null;
else
sheetView.ShowRuler = false;
if (xlWorksheet.ShowWhiteSpace)
sheetView.ShowWhiteSpace = null;
else
sheetView.ShowWhiteSpace = false;
if (xlWorksheet.ShowZeros)
sheetView.ShowZeros = null;
else
sheetView.ShowZeros = false;
if (xlWorksheet.RightToLeft)
sheetView.RightToLeft = true;
else
sheetView.RightToLeft = null;
if (xlWorksheet.SheetView.View == XLSheetViewOptions.Normal)
sheetView.View = null;
else
sheetView.View = xlWorksheet.SheetView.View.ToOpenXml();
var pane = sheetView.Elements<Pane>().FirstOrDefault();
if (pane == null)
{
pane = new Pane();
sheetView.AppendChild(pane);
}
pane.State = PaneStateValues.FrozenSplit;
Double hSplit = xlWorksheet.SheetView.SplitColumn;
Double ySplit = xlWorksheet.SheetView.SplitRow;
pane.HorizontalSplit = hSplit;
pane.VerticalSplit = ySplit;
pane.TopLeftCell = XLHelper.GetColumnLetterFromNumber(xlWorksheet.SheetView.SplitColumn + 1)
+ (xlWorksheet.SheetView.SplitRow + 1);
if (hSplit == 0 && ySplit == 0)
sheetView.RemoveAllChildren<Pane>();
if (xlWorksheet.SelectedRanges.Any() || xlWorksheet.ActiveCell != null)
{
sheetView.RemoveAllChildren<Selection>();
var firstSelection = xlWorksheet.SelectedRanges.FirstOrDefault();
var selection = new Selection();
if (xlWorksheet.ActiveCell != null)
selection.ActiveCell = xlWorksheet.ActiveCell.Address.ToStringRelative(false);
else if (firstSelection != null)
selection.ActiveCell = firstSelection.RangeAddress.FirstAddress.ToStringRelative(false);
var seqRef = new List<String> {selection.ActiveCell.Value};
seqRef.AddRange(xlWorksheet.SelectedRanges
.Select(range => range.RangeAddress.ToStringRelative(false)));
selection.SequenceOfReferences = new ListValue<StringValue> {InnerText = String.Join(" ", seqRef.Distinct().ToArray())};
sheetView.Append(selection);
}
#endregion
var maxOutlineColumn = 0;
if (xlWorksheet.ColumnCount() > 0)
maxOutlineColumn = xlWorksheet.GetMaxColumnOutline();
var maxOutlineRow = 0;
if (xlWorksheet.RowCount() > 0)
maxOutlineRow = xlWorksheet.GetMaxRowOutline();
#region SheetFormatProperties
if (worksheetPart.Worksheet.SheetFormatProperties == null)
worksheetPart.Worksheet.SheetFormatProperties = new SheetFormatProperties();
cm.SetElement(XLWSContentManager.XLWSContents.SheetFormatProperties,
worksheetPart.Worksheet.SheetFormatProperties);
worksheetPart.Worksheet.SheetFormatProperties.DefaultRowHeight = xlWorksheet.RowHeight;
if (xlWorksheet.RowHeightChanged)
worksheetPart.Worksheet.SheetFormatProperties.CustomHeight = true;
else
worksheetPart.Worksheet.SheetFormatProperties.CustomHeight = null;
var worksheetColumnWidth = GetColumnWidth(xlWorksheet.ColumnWidth);
if (xlWorksheet.ColumnWidthChanged)
worksheetPart.Worksheet.SheetFormatProperties.DefaultColumnWidth = worksheetColumnWidth;
else
worksheetPart.Worksheet.SheetFormatProperties.DefaultColumnWidth = null;
if (maxOutlineColumn > 0)
worksheetPart.Worksheet.SheetFormatProperties.OutlineLevelColumn = (byte)maxOutlineColumn;
else
worksheetPart.Worksheet.SheetFormatProperties.OutlineLevelColumn = null;
if (maxOutlineRow > 0)
worksheetPart.Worksheet.SheetFormatProperties.OutlineLevelRow = (byte)maxOutlineRow;
else
worksheetPart.Worksheet.SheetFormatProperties.OutlineLevelRow = null;
#endregion
#region Columns
if (xlWorksheet.Internals.CellsCollection.Count == 0 &&
xlWorksheet.Internals.ColumnsCollection.Count == 0
&& xlWorksheet.Style.Equals(DefaultStyle))
worksheetPart.Worksheet.RemoveAllChildren<Columns>();
else
{
if (!worksheetPart.Worksheet.Elements<Columns>().Any())
{
var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.Columns);
worksheetPart.Worksheet.InsertAfter(new Columns(), previousElement);
}
var columns = worksheetPart.Worksheet.Elements<Columns>().First();
cm.SetElement(XLWSContentManager.XLWSContents.Columns, columns);
var sheetColumnsByMin = columns.Elements<Column>().ToDictionary(c => c.Min.Value, c => c);
//Dictionary<UInt32, Column> sheetColumnsByMax = columns.Elements<Column>().ToDictionary(c => c.Max.Value, c => c);
Int32 minInColumnsCollection;
Int32 maxInColumnsCollection;
if (xlWorksheet.Internals.ColumnsCollection.Count > 0)
{
minInColumnsCollection = xlWorksheet.Internals.ColumnsCollection.Keys.Min();
maxInColumnsCollection = xlWorksheet.Internals.ColumnsCollection.Keys.Max();
}
else
{
minInColumnsCollection = 1;
maxInColumnsCollection = 0;
}
var worksheetStyleId = context.SharedStyles[xlWorksheet.GetStyleId()].StyleId;
if (minInColumnsCollection > 1)
{
UInt32Value min = 1;
UInt32Value max = (UInt32)(minInColumnsCollection - 1);
for (var co = min; co <= max; co++)
{
var column = new Column
{
Min = co,
Max = co,
Style = worksheetStyleId,
Width = worksheetColumnWidth,
CustomWidth = true
};
UpdateColumn(column, columns, sheetColumnsByMin); //, sheetColumnsByMax);
}
}
for (var co = minInColumnsCollection; co <= maxInColumnsCollection; co++)
{
UInt32 styleId;
Double columnWidth;
var isHidden = false;
var collapsed = false;
var outlineLevel = 0;
if (xlWorksheet.Internals.ColumnsCollection.ContainsKey(co))
{
styleId = context.SharedStyles[xlWorksheet.Internals.ColumnsCollection[co].GetStyleId()].StyleId;
columnWidth = GetColumnWidth(xlWorksheet.Internals.ColumnsCollection[co].Width);
isHidden = xlWorksheet.Internals.ColumnsCollection[co].IsHidden;
collapsed = xlWorksheet.Internals.ColumnsCollection[co].Collapsed;
outlineLevel = xlWorksheet.Internals.ColumnsCollection[co].OutlineLevel;
}
else
{
styleId = context.SharedStyles[xlWorksheet.GetStyleId()].StyleId;
columnWidth = worksheetColumnWidth;
}
var column = new Column
{
Min = (UInt32)co,
Max = (UInt32)co,
Style = styleId,
Width = columnWidth,
CustomWidth = true
};
if (isHidden)
column.Hidden = true;
if (collapsed)
column.Collapsed = true;
if (outlineLevel > 0)
column.OutlineLevel = (byte)outlineLevel;
UpdateColumn(column, columns, sheetColumnsByMin); //, sheetColumnsByMax);
}
var collection = maxInColumnsCollection;
foreach (
var col in
columns.Elements<Column>().Where(c => c.Min > (UInt32)(collection)).OrderBy(
c => c.Min.Value))
{
col.Style = worksheetStyleId;
col.Width = worksheetColumnWidth;
col.CustomWidth = true;
if ((Int32)col.Max.Value > maxInColumnsCollection)
maxInColumnsCollection = (Int32)col.Max.Value;
}
if (maxInColumnsCollection < XLHelper.MaxColumnNumber && !xlWorksheet.Style.Equals(DefaultStyle))
{
var column = new Column
{
Min = (UInt32)(maxInColumnsCollection + 1),
Max = (UInt32)(XLHelper.MaxColumnNumber),
Style = worksheetStyleId,
Width = worksheetColumnWidth,
CustomWidth = true
};
columns.AppendChild(column);
}
CollapseColumns(columns, sheetColumnsByMin);
if (!columns.Any())
{
worksheetPart.Worksheet.RemoveAllChildren<Columns>();
cm.SetElement(XLWSContentManager.XLWSContents.Columns, null);
}
}
#endregion
#region SheetData
if (!worksheetPart.Worksheet.Elements<SheetData>().Any())
{
var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.SheetData);
worksheetPart.Worksheet.InsertAfter(new SheetData(), previousElement);
}
var sheetData = worksheetPart.Worksheet.Elements<SheetData>().First();
cm.SetElement(XLWSContentManager.XLWSContents.SheetData, sheetData);
var cellsByRow = new Dictionary<Int32, List<IXLCell>>();
foreach (var c in xlWorksheet.Internals.CellsCollection.GetCells())
{
var rowNum = c.Address.RowNumber;
if (!cellsByRow.ContainsKey(rowNum))
cellsByRow.Add(rowNum, new List<IXLCell>());
cellsByRow[rowNum].Add(c);
}
var lastRow = 0;
var sheetDataRows =
sheetData.Elements<Row>().ToDictionary(r => r.RowIndex == null ? ++lastRow : (Int32)r.RowIndex.Value,
r => r);
foreach (
var r in
xlWorksheet.Internals.RowsCollection.Deleted.Where(r => sheetDataRows.ContainsKey(r.Key)))
{
sheetData.RemoveChild(sheetDataRows[r.Key]);
sheetDataRows.Remove(r.Key);
xlWorksheet.Internals.CellsCollection.Deleted.RemoveWhere(d => d.Row == r.Key);
}
var distinctRows = cellsByRow.Keys.Union(xlWorksheet.Internals.RowsCollection.Keys);
var noRows = (sheetData.Elements<Row>().FirstOrDefault() == null);
foreach (var distinctRow in distinctRows.OrderBy(r => r))
{
Row row; // = sheetData.Elements<Row>().FirstOrDefault(r => r.RowIndex.Value == (UInt32)distinctRow);
if (sheetDataRows.ContainsKey(distinctRow))
row = sheetDataRows[distinctRow];
else
{
row = new Row {RowIndex = (UInt32)distinctRow};
if (noRows)
{
sheetData.AppendChild(row);
noRows = false;
}
else
{
if (sheetDataRows.Any(r => r.Key > row.RowIndex.Value))
{
var minRow = sheetDataRows.Where(r => r.Key > (Int32)row.RowIndex.Value).Min(r => r.Key);
var rowBeforeInsert = sheetDataRows[minRow];
sheetData.InsertBefore(row, rowBeforeInsert);
}
else
sheetData.AppendChild(row);
}
}
if (maxColumn > 0)
row.Spans = new ListValue<StringValue> {InnerText = "1:" + maxColumn.ToStringLookup()};
row.Height = null;
row.CustomHeight = null;
row.Hidden = null;
row.StyleIndex = null;
row.CustomFormat = null;
row.Collapsed = null;
if (xlWorksheet.Internals.RowsCollection.ContainsKey(distinctRow))
{
var thisRow = xlWorksheet.Internals.RowsCollection[distinctRow];
if (thisRow.HeightChanged)
{
row.Height = thisRow.Height;
row.CustomHeight = true;
row.CustomFormat = true;
}
if (thisRow.GetStyleId() != xlWorksheet.GetStyleId())
{
row.StyleIndex = context.SharedStyles[thisRow.GetStyleId()].StyleId;
row.CustomFormat = true;
}
if (thisRow.IsHidden)
row.Hidden = true;
if (thisRow.Collapsed)
row.Collapsed = true;
if (thisRow.OutlineLevel > 0)
row.OutlineLevel = (byte)thisRow.OutlineLevel;
}
var lastCell = 0;
var cellsByReference = row.Elements<Cell>().ToDictionary(c => c.CellReference == null
? XLHelper.GetColumnLetterFromNumber(
++lastCell) + distinctRow
: c.CellReference.Value, c => c);
foreach (var c in xlWorksheet.Internals.CellsCollection.Deleted.ToList())
{
var key = XLHelper.GetColumnLetterFromNumber(c.Column) + c.Row.ToStringLookup();
if (!cellsByReference.ContainsKey(key)) continue;
row.RemoveChild(cellsByReference[key]);
xlWorksheet.Internals.CellsCollection.Deleted.Remove(c);
}
if (!cellsByRow.ContainsKey(distinctRow)) continue;
var isNewRow = !row.Elements<Cell>().Any();
var mRows = row.Elements<Cell>().ToDictionary(c => XLHelper.GetColumnNumberFromAddress(c.CellReference.Value), c => c);
foreach (var opCell in cellsByRow[distinctRow]
.OrderBy(c => c.Address.ColumnNumber)
.Select(c => (XLCell)c))
{
var styleId = context.SharedStyles[opCell.GetStyleId()].StyleId;
var dataType = opCell.DataType;
var cellReference = (opCell.Address).GetTrimmedAddress();
Cell cell;
if (cellsByReference.ContainsKey(cellReference))
cell = cellsByReference[cellReference];
else
{
cell = new Cell {CellReference = new StringValue(cellReference)};
if (isNewRow)
row.AppendChild(cell);
else
{
var newColumn = XLHelper.GetColumnNumberFromAddress(cellReference);
Cell cellBeforeInsert = null;
int[] lastCo = {Int32.MaxValue};
foreach (var c in mRows.Where(kp => kp.Key > newColumn).Where(c => lastCo[0] > c.Key))
{
cellBeforeInsert = c.Value;
lastCo[0] = c.Key;
}
if (cellBeforeInsert == null)
row.AppendChild(cell);
else
row.InsertBefore(cell, cellBeforeInsert);
}
}
cell.StyleIndex = styleId;
var formula = opCell.FormulaA1;
if (opCell.HasFormula)
{
if (formula.StartsWith("{"))
{
formula = formula.Substring(1, formula.Length - 2);
var f = new CellFormula {FormulaType = CellFormulaValues.Array};
if (opCell.FormulaReference.FirstAddress.Equals(opCell.Address))
{
f.Text = formula;
f.Reference = opCell.FormulaReference.ToStringRelative();
}
cell.CellFormula = f;
}
else
cell.CellFormula = new CellFormula(formula);
cell.CellValue = null;
}
else
{
cell.CellFormula = null;
cell.DataType = opCell.DataType == XLCellValues.DateTime ? null : GetCellValue(opCell);
var cellValue = new CellValue();
if (dataType == XLCellValues.Text)
{
if (opCell.InnerText.Length == 0)
cell.CellValue = null;
else
{
if (opCell.ShareString)
{
cellValue.Text = opCell.SharedStringId.ToString();
cell.CellValue = cellValue;
}
else
{
var text = opCell.GetString();
var t = new Text(text);
if (text.PreserveSpaces())
t.Space = SpaceProcessingModeValues.Preserve;
cell.InlineString = new InlineString {Text = t};
}
}
}
else if (dataType == XLCellValues.TimeSpan)
{
var timeSpan = opCell.GetTimeSpan();
cellValue.Text =
XLCell.BaseDate.Add(timeSpan).ToOADate().ToString(CultureInfo.InvariantCulture);
cell.CellValue = cellValue;
}
else if (dataType == XLCellValues.DateTime || dataType == XLCellValues.Number)
{
if (!XLHelper.IsNullOrWhiteSpace(opCell.InnerText))
cellValue.Text = Double.Parse(opCell.InnerText).ToString(CultureInfo.InvariantCulture);
cell.CellValue = cellValue;
}
else
{
cellValue.Text = opCell.InnerText;
cell.CellValue = cellValue;
}
}
}
xlWorksheet.Internals.CellsCollection.Deleted.RemoveWhere(d => d.Row == distinctRow);
}
foreach (
var r in
xlWorksheet.Internals.CellsCollection.Deleted.Select(c => c.Row).Distinct().Where(
sheetDataRows.ContainsKey))
{
sheetData.RemoveChild(sheetDataRows[r]);
sheetDataRows.Remove(r);
}
#endregion
#region SheetProtection
if (xlWorksheet.Protection.Protected)
{
if (!worksheetPart.Worksheet.Elements<SheetProtection>().Any())
{
var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.SheetProtection);
worksheetPart.Worksheet.InsertAfter(new SheetProtection(), previousElement);
}
var sheetProtection = worksheetPart.Worksheet.Elements<SheetProtection>().First();
cm.SetElement(XLWSContentManager.XLWSContents.SheetProtection, sheetProtection);
var protection = xlWorksheet.Protection;
sheetProtection.Sheet = protection.Protected;
if (!XLHelper.IsNullOrWhiteSpace(protection.PasswordHash))
sheetProtection.Password = protection.PasswordHash;
sheetProtection.FormatCells = GetBooleanValue(!protection.FormatCells, true);
sheetProtection.FormatColumns = GetBooleanValue(!protection.FormatColumns, true);
sheetProtection.FormatRows = GetBooleanValue(!protection.FormatRows, true);
sheetProtection.InsertColumns = GetBooleanValue(!protection.InsertColumns, true);
sheetProtection.InsertHyperlinks = GetBooleanValue(!protection.InsertHyperlinks, true);
sheetProtection.InsertRows = GetBooleanValue(!protection.InsertRows, true);
sheetProtection.DeleteColumns = GetBooleanValue(!protection.DeleteColumns, true);
sheetProtection.DeleteRows = GetBooleanValue(!protection.DeleteRows, true);
sheetProtection.AutoFilter = GetBooleanValue(!protection.AutoFilter, true);
sheetProtection.PivotTables = GetBooleanValue(!protection.PivotTables, true);
sheetProtection.Sort = GetBooleanValue(!protection.Sort, true);
sheetProtection.SelectLockedCells = GetBooleanValue(!protection.SelectLockedCells, false);
sheetProtection.SelectUnlockedCells = GetBooleanValue(!protection.SelectUnlockedCells, false);
}
else
{
worksheetPart.Worksheet.RemoveAllChildren<SheetProtection>();
cm.SetElement(XLWSContentManager.XLWSContents.SheetProtection, null);
}
#endregion
#region AutoFilter
worksheetPart.Worksheet.RemoveAllChildren<AutoFilter>();
if (xlWorksheet.AutoFilter.Enabled)
{
var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.AutoFilter);
worksheetPart.Worksheet.InsertAfter(new AutoFilter(), previousElement);
var autoFilter = worksheetPart.Worksheet.Elements<AutoFilter>().First();
cm.SetElement(XLWSContentManager.XLWSContents.AutoFilter, autoFilter);
PopulateAutoFilter(xlWorksheet.AutoFilter, autoFilter);
}
else
{
cm.SetElement(XLWSContentManager.XLWSContents.AutoFilter, null);
}
#endregion
#region MergeCells
if ((xlWorksheet).Internals.MergedRanges.Any())
{
if (!worksheetPart.Worksheet.Elements<MergeCells>().Any())
{
var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.MergeCells);
worksheetPart.Worksheet.InsertAfter(new MergeCells(), previousElement);
}
var mergeCells = worksheetPart.Worksheet.Elements<MergeCells>().First();
cm.SetElement(XLWSContentManager.XLWSContents.MergeCells, mergeCells);
mergeCells.RemoveAllChildren<MergeCell>();
foreach (var mergeCell in (xlWorksheet).Internals.MergedRanges.Select(
m => m.RangeAddress.FirstAddress.ToString() + ":" + m.RangeAddress.LastAddress.ToString()).Select(
merged => new MergeCell {Reference = merged}))
mergeCells.AppendChild(mergeCell);
mergeCells.Count = (UInt32)mergeCells.Count();
}
else
{
worksheetPart.Worksheet.RemoveAllChildren<MergeCells>();
cm.SetElement(XLWSContentManager.XLWSContents.MergeCells, null);
}
#endregion
#region Conditional Formatting
if (!xlWorksheet.ConditionalFormats.Any())
{
worksheetPart.Worksheet.RemoveAllChildren<ConditionalFormatting>();
cm.SetElement(XLWSContentManager.XLWSContents.ConditionalFormatting, null);
}
else
{
worksheetPart.Worksheet.RemoveAllChildren<ConditionalFormatting>();
var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.ConditionalFormatting);
//if (!worksheetPart.Worksheet.Elements<ConditionalFormatting>().Any())
//{
// var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.ConditionalFormatting);
// worksheetPart.Worksheet.InsertAfter(new ConditionalFormatting(), previousElement);
//}
//var conditionalFormats = worksheetPart.Worksheet.Elements<ConditionalFormatting>().First();
//cm.SetElement(XLWSContentManager.XLWSContents.ConditionalFormatting, conditionalFormats);
////conditionalFormats.RemoveAllChildren<ConditionalFormat>();
var priority = 0;
foreach (var cf in xlWorksheet.ConditionalFormats)
{
priority++;
var conditionalFormatting = new ConditionalFormatting
{
SequenceOfReferences =
new ListValue<StringValue> {InnerText = cf.Range.RangeAddress.ToStringRelative(false)}
};
conditionalFormatting.Append(XLCFConverters.Convert(cf, priority, context));
worksheetPart.Worksheet.InsertAfter(conditionalFormatting, previousElement);
previousElement = conditionalFormatting;
cm.SetElement(XLWSContentManager.XLWSContents.ConditionalFormatting, conditionalFormatting);
}
}
#endregion
#region DataValidations
if (!xlWorksheet.DataValidations.Any(d => d.IsDirty()))
{
worksheetPart.Worksheet.RemoveAllChildren<DataValidations>();
cm.SetElement(XLWSContentManager.XLWSContents.DataValidations, null);
}
else
{
if (!worksheetPart.Worksheet.Elements<DataValidations>().Any())
{
var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.DataValidations);
worksheetPart.Worksheet.InsertAfter(new DataValidations(), previousElement);
}
var dataValidations = worksheetPart.Worksheet.Elements<DataValidations>().First();
cm.SetElement(XLWSContentManager.XLWSContents.DataValidations, dataValidations);
dataValidations.RemoveAllChildren<DataValidation>();
foreach (var dv in xlWorksheet.DataValidations)
{
var sequence = dv.Ranges.Aggregate(String.Empty, (current, r) => current + (r.RangeAddress + " "));
if (sequence.Length > 0)
sequence = sequence.Substring(0, sequence.Length - 1);
var dataValidation = new DataValidation
{
AllowBlank = dv.IgnoreBlanks,
Formula1 = new Formula1(dv.MinValue),
Formula2 = new Formula2(dv.MaxValue),
Type = dv.AllowedValues.ToOpenXml(),
ShowErrorMessage = dv.ShowErrorMessage,
Prompt = dv.InputMessage,
PromptTitle = dv.InputTitle,
ErrorTitle = dv.ErrorTitle,
Error = dv.ErrorMessage,
ShowDropDown = !dv.InCellDropdown,
ShowInputMessage = dv.ShowInputMessage,
ErrorStyle = dv.ErrorStyle.ToOpenXml(),
Operator = dv.Operator.ToOpenXml(),
SequenceOfReferences =
new ListValue<StringValue> {InnerText = sequence}
};
dataValidations.AppendChild(dataValidation);
}
dataValidations.Count = (UInt32)xlWorksheet.DataValidations.Count();
}
#endregion
#region Hyperlinks
var relToRemove = worksheetPart.HyperlinkRelationships.ToList();
relToRemove.ForEach(worksheetPart.DeleteReferenceRelationship);
if (!xlWorksheet.Hyperlinks.Any())
{
worksheetPart.Worksheet.RemoveAllChildren<Hyperlinks>();
cm.SetElement(XLWSContentManager.XLWSContents.Hyperlinks, null);
}
else
{
if (!worksheetPart.Worksheet.Elements<Hyperlinks>().Any())
{
var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.Hyperlinks);
worksheetPart.Worksheet.InsertAfter(new Hyperlinks(), previousElement);
}
var hyperlinks = worksheetPart.Worksheet.Elements<Hyperlinks>().First();
cm.SetElement(XLWSContentManager.XLWSContents.Hyperlinks, hyperlinks);
hyperlinks.RemoveAllChildren<Hyperlink>();
foreach (var hl in xlWorksheet.Hyperlinks)
{
Hyperlink hyperlink;
if (hl.IsExternal)
{
var rId = context.RelIdGenerator.GetNext(RelType.Workbook);
hyperlink = new Hyperlink {Reference = hl.Cell.Address.ToString(), Id = rId};
worksheetPart.AddHyperlinkRelationship(hl.ExternalAddress, true, rId);
}
else
{
hyperlink = new Hyperlink
{
Reference = hl.Cell.Address.ToString(),
Location = hl.InternalAddress,
Display = hl.Cell.GetFormattedString()
};
}
if (!XLHelper.IsNullOrWhiteSpace(hl.Tooltip))
hyperlink.Tooltip = hl.Tooltip;
hyperlinks.AppendChild(hyperlink);
}
}
#endregion
#region PrintOptions
if (!worksheetPart.Worksheet.Elements<PrintOptions>().Any())
{
var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.PrintOptions);
worksheetPart.Worksheet.InsertAfter(new PrintOptions(), previousElement);
}
var printOptions = worksheetPart.Worksheet.Elements<PrintOptions>().First();
cm.SetElement(XLWSContentManager.XLWSContents.PrintOptions, printOptions);
printOptions.HorizontalCentered = xlWorksheet.PageSetup.CenterHorizontally;
printOptions.VerticalCentered = xlWorksheet.PageSetup.CenterVertically;
printOptions.Headings = xlWorksheet.PageSetup.ShowRowAndColumnHeadings;
printOptions.GridLines = xlWorksheet.PageSetup.ShowGridlines;
#endregion
#region PageMargins
if (!worksheetPart.Worksheet.Elements<PageMargins>().Any())
{
var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.PageMargins);
worksheetPart.Worksheet.InsertAfter(new PageMargins(), previousElement);
}
var pageMargins = worksheetPart.Worksheet.Elements<PageMargins>().First();
cm.SetElement(XLWSContentManager.XLWSContents.PageMargins, pageMargins);
pageMargins.Left = xlWorksheet.PageSetup.Margins.Left;
pageMargins.Right = xlWorksheet.PageSetup.Margins.Right;
pageMargins.Top = xlWorksheet.PageSetup.Margins.Top;
pageMargins.Bottom = xlWorksheet.PageSetup.Margins.Bottom;
pageMargins.Header = xlWorksheet.PageSetup.Margins.Header;
pageMargins.Footer = xlWorksheet.PageSetup.Margins.Footer;
#endregion
#region PageSetup
if (!worksheetPart.Worksheet.Elements<PageSetup>().Any())
{
var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.PageSetup);
worksheetPart.Worksheet.InsertAfter(new PageSetup(), previousElement);
}
var pageSetup = worksheetPart.Worksheet.Elements<PageSetup>().First();
cm.SetElement(XLWSContentManager.XLWSContents.PageSetup, pageSetup);
pageSetup.Orientation = xlWorksheet.PageSetup.PageOrientation.ToOpenXml();
pageSetup.PaperSize = (UInt32)xlWorksheet.PageSetup.PaperSize;
pageSetup.BlackAndWhite = xlWorksheet.PageSetup.BlackAndWhite;
pageSetup.Draft = xlWorksheet.PageSetup.DraftQuality;
pageSetup.PageOrder = xlWorksheet.PageSetup.PageOrder.ToOpenXml();
pageSetup.CellComments = xlWorksheet.PageSetup.ShowComments.ToOpenXml();
pageSetup.Errors = xlWorksheet.PageSetup.PrintErrorValue.ToOpenXml();
if (xlWorksheet.PageSetup.FirstPageNumber > 0)
{
pageSetup.FirstPageNumber = (UInt32)xlWorksheet.PageSetup.FirstPageNumber;
pageSetup.UseFirstPageNumber = true;
}
else
{
pageSetup.FirstPageNumber = null;
pageSetup.UseFirstPageNumber = null;
}
if (xlWorksheet.PageSetup.HorizontalDpi > 0)
pageSetup.HorizontalDpi = (UInt32)xlWorksheet.PageSetup.HorizontalDpi;
else
pageSetup.HorizontalDpi = null;
if (xlWorksheet.PageSetup.VerticalDpi > 0)
pageSetup.VerticalDpi = (UInt32)xlWorksheet.PageSetup.VerticalDpi;
else
pageSetup.VerticalDpi = null;
if (xlWorksheet.PageSetup.Scale > 0)
{
pageSetup.Scale = (UInt32)xlWorksheet.PageSetup.Scale;
pageSetup.FitToWidth = null;
pageSetup.FitToHeight = null;
}
else
{
pageSetup.Scale = null;
if (xlWorksheet.PageSetup.PagesWide > 0)
pageSetup.FitToWidth = (UInt32)xlWorksheet.PageSetup.PagesWide;
else
pageSetup.FitToWidth = 0;
if (xlWorksheet.PageSetup.PagesTall > 0)
pageSetup.FitToHeight = (UInt32)xlWorksheet.PageSetup.PagesTall;
else
pageSetup.FitToHeight = 0;
}
#endregion
#region HeaderFooter
var headerFooter = worksheetPart.Worksheet.Elements<HeaderFooter>().FirstOrDefault();
if (headerFooter == null)
headerFooter = new HeaderFooter();
else
worksheetPart.Worksheet.RemoveAllChildren<HeaderFooter>();
{
var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.HeaderFooter);
worksheetPart.Worksheet.InsertAfter(headerFooter, previousElement);
cm.SetElement(XLWSContentManager.XLWSContents.HeaderFooter, headerFooter);
}
if (((XLHeaderFooter)xlWorksheet.PageSetup.Header).Changed
|| ((XLHeaderFooter)xlWorksheet.PageSetup.Footer).Changed)
{
//var headerFooter = worksheetPart.Worksheet.Elements<HeaderFooter>().First();
headerFooter.RemoveAllChildren();
headerFooter.ScaleWithDoc = xlWorksheet.PageSetup.ScaleHFWithDocument;
headerFooter.AlignWithMargins = xlWorksheet.PageSetup.AlignHFWithMargins;
headerFooter.DifferentFirst = xlWorksheet.PageSetup.DifferentFirstPageOnHF;
headerFooter.DifferentOddEven = xlWorksheet.PageSetup.DifferentOddEvenPagesOnHF;
var oddHeader = new OddHeader(xlWorksheet.PageSetup.Header.GetText(XLHFOccurrence.OddPages));
headerFooter.AppendChild(oddHeader);
var oddFooter = new OddFooter(xlWorksheet.PageSetup.Footer.GetText(XLHFOccurrence.OddPages));
headerFooter.AppendChild(oddFooter);
var evenHeader = new EvenHeader(xlWorksheet.PageSetup.Header.GetText(XLHFOccurrence.EvenPages));
headerFooter.AppendChild(evenHeader);
var evenFooter = new EvenFooter(xlWorksheet.PageSetup.Footer.GetText(XLHFOccurrence.EvenPages));
headerFooter.AppendChild(evenFooter);
var firstHeader = new FirstHeader(xlWorksheet.PageSetup.Header.GetText(XLHFOccurrence.FirstPage));
headerFooter.AppendChild(firstHeader);
var firstFooter = new FirstFooter(xlWorksheet.PageSetup.Footer.GetText(XLHFOccurrence.FirstPage));
headerFooter.AppendChild(firstFooter);
}
#endregion
#region RowBreaks
if (!worksheetPart.Worksheet.Elements<RowBreaks>().Any())
{
var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.RowBreaks);
worksheetPart.Worksheet.InsertAfter(new RowBreaks(), previousElement);
}
var rowBreaks = worksheetPart.Worksheet.Elements<RowBreaks>().First();
var rowBreakCount = xlWorksheet.PageSetup.RowBreaks.Count;
if (rowBreakCount > 0)
{
rowBreaks.Count = (UInt32)rowBreakCount;
rowBreaks.ManualBreakCount = (UInt32)rowBreakCount;
var lastRowNum = (UInt32)xlWorksheet.RangeAddress.LastAddress.RowNumber;
foreach (var break1 in xlWorksheet.PageSetup.RowBreaks.Select(rb => new Break
{
Id = (UInt32)rb,
Max = lastRowNum,
ManualPageBreak = true
}))
rowBreaks.AppendChild(break1);
cm.SetElement(XLWSContentManager.XLWSContents.RowBreaks, rowBreaks);
}
else
{
worksheetPart.Worksheet.RemoveAllChildren<RowBreaks>();
cm.SetElement(XLWSContentManager.XLWSContents.RowBreaks, null);
}
#endregion
#region ColumnBreaks
if (!worksheetPart.Worksheet.Elements<ColumnBreaks>().Any())
{
var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.ColumnBreaks);
worksheetPart.Worksheet.InsertAfter(new ColumnBreaks(), previousElement);
}
var columnBreaks = worksheetPart.Worksheet.Elements<ColumnBreaks>().First();
var columnBreakCount = xlWorksheet.PageSetup.ColumnBreaks.Count;
if (columnBreakCount > 0)
{
columnBreaks.Count = (UInt32)columnBreakCount;
columnBreaks.ManualBreakCount = (UInt32)columnBreakCount;
var maxColumnNumber = (UInt32)xlWorksheet.RangeAddress.LastAddress.ColumnNumber;
foreach (var break1 in xlWorksheet.PageSetup.ColumnBreaks.Select(cb => new Break
{
Id = (UInt32)cb,
Max = maxColumnNumber,
ManualPageBreak = true
}))
columnBreaks.AppendChild(break1);
cm.SetElement(XLWSContentManager.XLWSContents.ColumnBreaks, columnBreaks);
}
else
{
worksheetPart.Worksheet.RemoveAllChildren<ColumnBreaks>();
cm.SetElement(XLWSContentManager.XLWSContents.ColumnBreaks, null);
}
#endregion
#region Drawings
//worksheetPart.Worksheet.RemoveAllChildren<Drawing>();
//{
// OpenXmlElement previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.Drawing);
// worksheetPart.Worksheet.InsertAfter(new Drawing() { Id = String.Format("rId{0}", 1) }, previousElement);
//}
//Drawing drawing = worksheetPart.Worksheet.Elements<Drawing>().First();
//cm.SetElement(XLWSContentManager.XLWSContents.Drawing, drawing);
#endregion
#region Tables
worksheetPart.Worksheet.RemoveAllChildren<TableParts>();
{
var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.TableParts);
worksheetPart.Worksheet.InsertAfter(new TableParts(), previousElement);
}
var tableParts = worksheetPart.Worksheet.Elements<TableParts>().First();
cm.SetElement(XLWSContentManager.XLWSContents.TableParts, tableParts);
tableParts.Count = (UInt32)xlWorksheet.Tables.Count();
foreach (
var tablePart in
from XLTable xlTable in xlWorksheet.Tables select new TablePart {Id = xlTable.RelId})
tableParts.AppendChild(tablePart);
#endregion
#region LegacyDrawing
if (xlWorksheet.LegacyDrawingIsNew)
{
worksheetPart.Worksheet.RemoveAllChildren<LegacyDrawing>();
{
if (!XLHelper.IsNullOrWhiteSpace(xlWorksheet.LegacyDrawingId))
{
var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.LegacyDrawing);
worksheetPart.Worksheet.InsertAfter(new LegacyDrawing {Id = xlWorksheet.LegacyDrawingId},
previousElement);
}
}
}
#endregion
#region LegacyDrawingHeaderFooter
//LegacyDrawingHeaderFooter legacyHeaderFooter = worksheetPart.Worksheet.Elements<LegacyDrawingHeaderFooter>().FirstOrDefault();
//if (legacyHeaderFooter != null)
//{
// worksheetPart.Worksheet.RemoveAllChildren<LegacyDrawingHeaderFooter>();
// {
// var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.LegacyDrawingHeaderFooter);
// worksheetPart.Worksheet.InsertAfter(new LegacyDrawingHeaderFooter { Id = xlWorksheet.LegacyDrawingId },
// previousElement);
// }
//}
#endregion
}
// Generates content of worksheetPart1.
private void GenerateWorksheetPart1Content(WorksheetPart worksheetPart1)
{
Worksheet worksheet1 = new Worksheet();
worksheet1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
SheetDimension sheetDimension1 = new SheetDimension() { Reference = "A1:A2" };
SheetViews sheetViews1 = new SheetViews();
SheetView sheetView1 = new SheetView() { TabSelected = true, WorkbookViewId = (UInt32Value)0U };
Selection selection1 = new Selection() { ActiveCell = "A2", SequenceOfReferences = new ListValue<StringValue>() { InnerText = "A2" } };
sheetView1.Append(selection1);
sheetViews1.Append(sheetView1);
SheetFormatProperties sheetFormatProperties1 = new SheetFormatProperties() { DefaultRowHeight = 15D };
SheetData sheetData1 = new SheetData();
Row row1 = new Row() { RowIndex = (UInt32Value)1U, Spans = new ListValue<StringValue>() { InnerText = "1:1" } };
Cell cell1 = new Cell() { CellReference = "A1" };
CellValue cellValue1 = new CellValue();
cellValue1.Text = "1";
cell1.Append(cellValue1);
row1.Append(cell1);
Row row2 = new Row() { RowIndex = (UInt32Value)2U, Spans = new ListValue<StringValue>() { InnerText = "1:1" } };
Cell cell2 = new Cell() { CellReference = "A2" };
CellValue cellValue2 = new CellValue();
cellValue2.Text = "-1";
cell2.Append(cellValue2);
row2.Append(cell2);
sheetData1.Append(row1);
sheetData1.Append(row2);
ConditionalFormatting conditionalFormatting1 = new ConditionalFormatting() { SequenceOfReferences = new ListValue<StringValue>() { InnerText = "A1" } };
ConditionalFormattingRule conditionalFormattingRule1 = new ConditionalFormattingRule() { Type = ConditionalFormatValues.CellIs, FormatId = (UInt32Value)2U, Priority = 2, Operator = ConditionalFormattingOperatorValues.GreaterThan };
Formula formula1 = new Formula();
formula1.Text = "0";
conditionalFormattingRule1.Append(formula1);
conditionalFormatting1.Append(conditionalFormattingRule1);
ConditionalFormatting conditionalFormatting2 = new ConditionalFormatting() { SequenceOfReferences = new ListValue<StringValue>() { InnerText = "A2" } };
ConditionalFormattingRule conditionalFormattingRule2 = new ConditionalFormattingRule() { Type = ConditionalFormatValues.CellIs, FormatId = (UInt32Value)0U, Priority = 1, Operator = ConditionalFormattingOperatorValues.LessThan };
Formula formula2 = new Formula();
formula2.Text = "0";
conditionalFormattingRule2.Append(formula2);
conditionalFormatting2.Append(conditionalFormattingRule2);
PageMargins pageMargins1 = new PageMargins() { Left = 0.7D, Right = 0.7D, Top = 0.75D, Bottom = 0.75D, Header = 0.3D, Footer = 0.3D };
worksheet1.Append(sheetDimension1);
worksheet1.Append(sheetViews1);
worksheet1.Append(sheetFormatProperties1);
worksheet1.Append(sheetData1);
worksheet1.Append(conditionalFormatting1);
worksheet1.Append(conditionalFormatting2);
worksheet1.Append(pageMargins1);
worksheetPart1.Worksheet = worksheet1;
}
public static void CreateConditionalFormatting()
{
var workbook = new Workbook(); //creating workbook and adding sheet
var companiesSheet = new Sheet("CompaniesByRevenue");
workbook.Sheets.AddSheet(companiesSheet);
companiesSheet.Columns[0].Width = 5;
companiesSheet.Columns[1].Width = 30;
companiesSheet.Columns[2].Width = 25;
companiesSheet.Columns[3].Width = 25;
companiesSheet.Columns[4].Width = 15;
var headingStyle = new CellStyle(); //define bold and centered header style
headingStyle.Font.Bold = true;
headingStyle.Alignment.Horizontal = HorizontalAlignment.Center;
var thousandsFormatStyle = new CellStyle(); //adding thousands separator format on Employees column
thousandsFormatStyle.Alignment.Horizontal = HorizontalAlignment.Center;
thousandsFormatStyle.Format = "#,#0";
var currencyFormatStyle = new CellStyle(); //adding $ (dollar) sign on Revenue and Capitalization columns
currencyFormatStyle.Format = "$#,#0";
currencyFormatStyle.Alignment.Horizontal = HorizontalAlignment.Center;
var table = companiesSheet["A2", "E6"];
table.GetRow(-1).SetValues("Rank", "Company", "Revenue (USD billions)", "Capitalization (USD billions)", "Employees").SetStyle(headingStyle);
table.GetColumn(0).SetValues(1, 2, 3, 4, 5);
table.GetColumn(1).SetValues("Wal-Mart Stores, Inc. (USA)", "Royal Dutch Shell (NLD)", "Exxon Mobil Corporation (USA)", "China National Petroleum (CHN)", "Sinopec Group (CHN)");
table.GetColumn(2).SetValues(469, 467, 453, 425, 411).SetStyle(currencyFormatStyle);
table.GetColumn(3).SetValues(248, 132, 406, null, 81).SetStyle(currencyFormatStyle);
table.GetColumn(4).SetValues(2200000, 90000, 76900, 1668072, 401000).SetStyle(thousandsFormatStyle);
var originFormating = new ConditionalFormatting(Range.Parse("B2:B6")); //creating ConditionalFormatting for specified range
originFormating.AddContainsText("USA", new CellStyle {
Fill = CellFill.BackColor(Color.Red)
});
originFormating.AddEndsWithText("(NLD)", new CellStyle {
Fill = CellFill.BackColor(new Color("#0096FF"))
});
originFormating.AddContainsText("CHN", new CellStyle {
Fill = CellFill.BackColor(new Color("#FFFF96"))
});
var revenueFormatting = new ConditionalFormatting();
revenueFormatting.AddRange("C2:C6"); //multiple ranges or cells can be added
revenueFormatting.AddDefaultIconSet(IconSetType.Item5Arrows); //add IconSet with 5 arrows
revenueFormatting.AddGreaterThan("460", new CellStyle {
Font = new CellFont {
Underline = FontUnderline.Single
}
});
var capitalizationFormatting = new ConditionalFormatting(Range.Parse("D1:D6")); //specifying range in constructor
capitalizationFormatting.AddColorScale(Color.Yellow, Color.Orange);
capitalizationFormatting.AddDefaultIconSet();
capitalizationFormatting.AddIsBlank(new CellStyle {
Fill = CellFill.BackColor(Color.Red)
}); //add style applied on blank cell
ConditionalFormatting employeesNumberFormating = new ConditionalFormatting(Range.Parse("E1:E6"));
employeesNumberFormating.AddDefaultDataBar(Color.Blue); //adding blue data bar conditional formatting rule
//adding different conditional formattings to sheet
companiesSheet.ConditionalFormatting.Add(originFormating);
companiesSheet.ConditionalFormatting.Add(revenueFormatting);
companiesSheet.ConditionalFormatting.Add(capitalizationFormatting);
companiesSheet.ConditionalFormatting.Add(employeesNumberFormating);
workbook.Save(@"ConditionalFormatting.xlsx");
}
internal ConditionalFormatting ToConditionalFormatting()
{
ConditionalFormatting cf = new ConditionalFormatting();
if (this.Pivot) cf.Pivot = this.Pivot;
if (this.SequenceOfReferences.Count > 0)
{
string sRef = string.Empty;
cf.SequenceOfReferences = new ListValue<StringValue>();
foreach (SLCellPointRange pt in this.SequenceOfReferences)
{
if (pt.StartRowIndex == pt.EndRowIndex && pt.StartColumnIndex == pt.EndColumnIndex)
{
sRef = SLTool.ToCellReference(pt.StartRowIndex, pt.StartColumnIndex);
}
else
{
sRef = string.Format("{0}:{1}", SLTool.ToCellReference(pt.StartRowIndex, pt.StartColumnIndex), SLTool.ToCellReference(pt.EndRowIndex, pt.EndColumnIndex));
}
cf.SequenceOfReferences.Items.Add(new StringValue(sRef));
}
}
foreach (SLConditionalFormattingRule cfr in this.Rules)
{
cf.Append(cfr.ToConditionalFormattingRule());
}
return cf;
}
private void AddConditionalFormatting()
{
//use openxml directly to apply conditional formatting.
using (SpreadsheetDocument report = SpreadsheetDocument.Open(fileName, true))
{
Workbook workbook = report.WorkbookPart.Workbook;
WorksheetPart worksheetPart = workbook.WorkbookPart.WorksheetParts.First();
DifferentialFormats differentialFormats = new DifferentialFormats() { Count = (UInt32Value)2U };
DifferentialFormat lessThanFormat = new DifferentialFormat();
Font lessThanFont = new Font();
lessThanFont.Append(new Condense() { Val = false });
lessThanFont.Append(new Extend() { Val = false });
lessThanFont.Append(new Color() { Rgb = "FF9C0006" });
Fill lessThanFill = new Fill();
PatternFill lessThanPatternFill = new PatternFill();
lessThanPatternFill.Append(new BackgroundColor() { Rgb = "FFFFC7CE" });
lessThanFill.Append(lessThanPatternFill);
lessThanFormat.Append(lessThanFont);
lessThanFormat.Append(lessThanFill);
DifferentialFormat greaterThanFormat = new DifferentialFormat();
Font greaterThanFont = new Font();
greaterThanFont.Append(new Condense() { Val = false });
greaterThanFont.Append(new Extend() { Val = false });
greaterThanFont.Append(new Color() { Rgb = "FF006100" });
Fill greatherThanFill = new Fill();
PatternFill greaterThanPatternFill = new PatternFill();
greaterThanPatternFill.Append(new BackgroundColor() { Rgb = "FFC6EFCE" });
greatherThanFill.Append(greaterThanPatternFill);
greaterThanFormat.Append(greaterThanFont);
greaterThanFormat.Append(greatherThanFill);
differentialFormats.Append(lessThanFormat);
differentialFormats.Append(greaterThanFormat);
workbook.WorkbookPart.WorkbookStylesPart.Stylesheet.Append(differentialFormats);
ConditionalFormatting conditionalFormatting = new ConditionalFormatting() {
SequenceOfReferences = new ListValue<StringValue>() { InnerText = "D2:D10" }
};
ConditionalFormattingRule greaterThanRule = new ConditionalFormattingRule()
{
Type = ConditionalFormatValues.CellIs,
FormatId = (UInt32Value)1U,
Priority = 2,
Operator = ConditionalFormattingOperatorValues.GreaterThan
};
Formula greaterThanFormula = new Formula();
greaterThanFormula.Text = "0";
greaterThanRule.Append(greaterThanFormula);
ConditionalFormattingRule lessThanRule = new ConditionalFormattingRule()
{
Type = ConditionalFormatValues.CellIs,
FormatId = (UInt32Value)0U,
Priority = 1,
Operator = ConditionalFormattingOperatorValues.LessThan
};
Formula lessThanFormula = new Formula();
lessThanFormula.Text = "0";
lessThanRule.Append(lessThanFormula);
conditionalFormatting.Append(greaterThanRule);
conditionalFormatting.Append(lessThanRule);
worksheetPart.Worksheet.Append(conditionalFormatting);
report.WorkbookPart.Workbook.Save();
report.Close();
}
}
public static Page GetChartContentPage(ChartSample chartSample)
{
Page page = null;
switch (chartSample.SampleViewType)
{
case (int)ChartSampleViewType.GETTING_STARTED:
page = new GettingStartedSample();
break;
case (int)ChartSampleViewType.CHART_TYPES:
page = new ChartTypesSample();
break;
case (int)ChartSampleViewType.MIXED_CHART_TYPES:
page = new MixedChartTypesSample();
break;
case (int)ChartSampleViewType.LEGEND_AND_TITLES:
page = new TitleAndLegendSample();
break;
case (int)ChartSampleViewType.TOOLTIPS:
page = new TooltipsSample();
break;
case (int)ChartSampleViewType.STYLING_SERIES:
page = new StylingSeriesSample();
break;
case (int)ChartSampleViewType.CUSTOMIZING_AXES:
page = new CustomizingAxesSample();
break;
case (int)ChartSampleViewType.THEMING:
page = new ThemingSample();
break;
case (int)ChartSampleViewType.SELECTION_MODES:
page = new SelectionModesSample();
break;
case (int)ChartSampleViewType.TOGGLE_SERIES:
page = new ToggleSeriesSample();
break;
case (int)ChartSampleViewType.DYNAMIC_CHARTS:
page = new DynamicChartsSample();
break;
case (int)ChartSampleViewType.BUBBLE_CHART:
page = new BubbleChartSample();
break;
case (int)ChartSampleViewType.FINANCIAL_CHART:
page = new FinancialChart();
break;
case (int)ChartSampleViewType.ZOOMING_AND_SCROLLING:
page = new ZoomingAndScrolling();
break;
case (int)ChartSampleViewType.HITTEST:
page = new HitTest();
break;
case (int)ChartSampleViewType.ANIMATION:
page = new AnimationOptions();
break;
case (int)ChartSampleViewType.MUTIPLE_AXES:
page = new MultipleAxesSamples();
break;
case (int)ChartSampleViewType.CUSTOM_PLOT_ELEMENTS:
page = new CustomPlotElements();
break;
case (int)ChartSampleViewType.CONDITIONAL_FORMAT:
page = new ConditionalFormatting();
break;
case (int)ChartSampleViewType.DATA_LABEL:
page = new DataLabelSample();
break;
case (int)ChartSampleViewType.SNAPSHOT:
page = new Snapshot();
break;
case (int)ChartSampleViewType.UPDATE_ANIMATION:
page = new UpdateAnimation();
break;
case (int)ChartSampleViewType.SCROLLING:
page = new ScrollingSample();
break;
}
page.Title = chartSample.Name;
return(page);
}
private void ReadSheet(string sheetPath, Sheet sheet)
{
Dictionary<uint, SharedFormula> sharedFormulas = new Dictionary<uint, SharedFormula>();
try
{
var xml = ReadFile<CT_Worksheet>(sheetPath);
if (xml.sheetFormatPr != null)
{
if (xml.sheetFormatPr.customHeight)
sheet.DefaultRowHeight = xml.sheetFormatPr.defaultRowHeight;
if (xml.sheetFormatPr.defaultColWidthSpecified)
sheet.DefaultColumnWidth = xml.sheetFormatPr.defaultColWidth;
}
if (xml.sheetViews!=null && xml.sheetViews.sheetView!=null)
foreach (var sheetView in xml.sheetViews.sheetView)
if (sheetView.workbookViewId == 0) //default view
{
sheet.ShowGridLines = sheetView.showGridLines;
if (sheetView.selection != null && sheetView.selection.Length == 1)
{
if (sheetView.selection[0].activeCell != null)
sheet.ActiveCell = Cell.Parse(sheetView.selection[0].activeCell);
}
}
if (xml.cols != null)
{
foreach (var col in xml.cols)
{
sheet.Columns.AppendRange((int)col.min - 1, (int)col.max - 1, new SheetColumn
{
BestFit = col.bestFit,
Width = col.customWidth && col.widthSpecified ? col.width : (double?)null,
style = GetStyle(col.style),
Phonetic = col.phonetic,
OutlineLevel = col.outlineLevel,
Hidden = col.hidden
});
}
}
if (xml.colBreaks != null)
foreach (var b in xml.colBreaks.brk)
if (b.man)
sheet.ColumnBreaks.Add((int)b.id - 1);
if (xml.mergeCells!=null && xml.mergeCells.mergeCell != null)
foreach (var mc in xml.mergeCells.mergeCell)
{
var range = Range.Parse(mc.@ref);
sheet[range].Merge();
}
if (xml.sheetData!=null)
foreach (var rd in xml.sheetData)
{
var row = sheet[(int)rd.r - 1];
if (rd.customFormat)
row.Style = GetStyle(rd.s);
if (rd.customHeight && rd.htSpecified)
row.Height = rd.ht;
row.Phonetic = rd.ph;
row.Collapsed = rd.collapsed;
row.Hidden = rd.hidden;
if (rd.c != null)
foreach (var c in rd.c)
{
var cell = Cell.Parse(c.r);
var data = row[cell.Col];
data.Style = GetStyle(c.s);
if (c.v != null)
{
object value = null;
switch (c.t)
{
case ST_CellType.n:
var n = Convert.ToDouble(c.v, CultureInfo.InvariantCulture);
value = data.style != null && NumberFormat.IsDateTimeFormat(data.style.format) ? value = XlioUtil.ToDateTime(n) : n;
break;
case ST_CellType.str:
case ST_CellType.inlineStr:
value = c.v;
break;
case ST_CellType.s:
value = sharedStrings[Convert.ToInt32(c.v, CultureInfo.InvariantCulture)];
break;
case ST_CellType.b:
value = c.v != null && c.v.Length > 0 && (c.v[0] == 'T' || c.v[0] == 't' || c.v[0] == '1');
break;
}
row[cell.Col].Value = value;
}
if (c.f != null)
{
switch (c.f.t)
{
case ST_CellFormulaType.normal:
row[cell.Col].Formula = c.f.Value;
break;
case ST_CellFormulaType.shared:
if (c.f.siSpecified)
{
SharedFormula sharedFormula = null;
if (c.f.@ref != null && c.f.Value != null) //shared formula definition
{
var range = Range.Parse(c.f.@ref);
var origin = cell;
var formula = c.f.Value;
sharedFormula = new SharedFormula(formula, range, origin);
sharedFormulas[c.f.si] = sharedFormula;
}
else
sharedFormulas.TryGetValue(c.f.si, out sharedFormula);
row[cell.Col].SharedFormula = sharedFormula;
}
break;
case ST_CellFormulaType.array:
row[cell.Col].Formula = "{" + c.f.Value + "}";
break;
}
}
}
if (xml.rowBreaks != null)
foreach (var brk in xml.rowBreaks.brk)
if (brk.man)
sheet.RowBreaks.Add((int)brk.id - 1);
if (xml.pageSetup != null)
{
switch (xml.pageSetup.orientation)
{
case ST_Orientation.portrait:
sheet.Page.Orientation = PageOrientation.Portrait;
break;
case ST_Orientation.landscape:
sheet.Page.Orientation = PageOrientation.Landscape;
break;
}
if (xml.pageSetup.scale > 0)
sheet.Page.Scale = (int)xml.pageSetup.scale;
}
if (xml.pageMargins != null)
{
if (xml.pageMargins != null)
sheet.Page.Margins = new PageMargins
{
Bottom = xml.pageMargins.bottom,
Footer = xml.pageMargins.footer,
Header = xml.pageMargins.header,
Left = xml.pageMargins.left,
Right = xml.pageMargins.right,
Top = xml.pageMargins.top
};
}
}
//conditional formatting
if (xml.conditionalFormatting != null)
foreach (CT_ConditionalFormatting conditionalFormatting in xml.conditionalFormatting)
{
var rules = new List<CFRule>();
if (conditionalFormatting.cfRule !=null)
foreach (var cfRule in conditionalFormatting.cfRule)
{
var rule = GetConditionalFormattingRule(cfRule);
rules.Add(rule);
}
ConditionalFormatting cf = new ConditionalFormatting
{
Ranges = conditionalFormatting.sqref.ToList(),
Rules = rules
};
sheet.ConditionalFormatting.Add(cf);
}
}
catch (Exception ex)
{
Debug.WriteLine(ex.Message);
Debug.WriteLine(ex.StackTrace);
throw;
}
}
private void ReadSheet(string sheetPath, Sheet sheet)
{
Dictionary <uint, SharedFormula> sharedFormulas = new Dictionary <uint, SharedFormula>();
try
{
var xml = ReadFile <CT_Worksheet>(sheetPath);
if (xml.sheetFormatPr != null)
{
if (xml.sheetFormatPr.customHeight)
{
sheet.DefaultRowHeight = xml.sheetFormatPr.defaultRowHeight;
}
if (xml.sheetFormatPr.defaultColWidthSpecified)
{
sheet.DefaultColumnWidth = xml.sheetFormatPr.defaultColWidth;
}
}
if (xml.sheetViews != null && xml.sheetViews.sheetView != null)
{
foreach (var sheetView in xml.sheetViews.sheetView)
{
if (sheetView.workbookViewId == 0) //default view
{
sheet.ShowGridLines = sheetView.showGridLines;
if (sheetView.selection != null && sheetView.selection.Length == 1)
{
if (sheetView.selection[0].activeCell != null)
{
sheet.ActiveCell = Cell.Parse(sheetView.selection[0].activeCell);
}
}
}
}
}
if (xml.cols != null)
{
foreach (var col in xml.cols)
{
sheet.Columns.AppendRange((int)col.min - 1, (int)col.max - 1, new SheetColumn
{
BestFit = col.bestFit,
Width = col.customWidth && col.widthSpecified ? col.width : (double?)null,
style = GetStyle(col.style),
Phonetic = col.phonetic,
OutlineLevel = col.outlineLevel,
Hidden = col.hidden
});
}
}
if (xml.colBreaks != null)
{
foreach (var b in xml.colBreaks.brk)
{
if (b.man)
{
sheet.ColumnBreaks.Add((int)b.id - 1);
}
}
}
if (xml.mergeCells != null && xml.mergeCells.mergeCell != null)
{
foreach (var mc in xml.mergeCells.mergeCell)
{
var range = Range.Parse(mc.@ref);
sheet[range].Merge();
}
}
if (xml.sheetData != null)
{
foreach (var rd in xml.sheetData)
{
var row = sheet[(int)rd.r - 1];
if (rd.customFormat)
{
row.Style = GetStyle(rd.s);
}
if (rd.customHeight && rd.htSpecified)
{
row.Height = rd.ht;
}
row.Phonetic = rd.ph;
row.Collapsed = rd.collapsed;
row.Hidden = rd.hidden;
if (rd.c != null)
{
foreach (var c in rd.c)
{
var cell = Cell.Parse(c.r);
var data = row[cell.Col];
data.Style = GetStyle(c.s);
if (c.v != null)
{
object value = null;
switch (c.t)
{
case ST_CellType.n:
var n = Convert.ToDouble(c.v, CultureInfo.InvariantCulture);
value = data.style != null && NumberFormat.IsDateTimeFormat(data.style.format) ? value = XlioUtil.ToDateTime(n) : n;
break;
case ST_CellType.str:
case ST_CellType.inlineStr:
value = c.v;
break;
case ST_CellType.s:
value = sharedStrings[Convert.ToInt32(c.v, CultureInfo.InvariantCulture)];
break;
case ST_CellType.b:
value = c.v != null && c.v.Length > 0 && (c.v[0] == 'T' || c.v[0] == 't' || c.v[0] == '1');
break;
}
row[cell.Col].Value = value;
}
if (c.f != null)
{
switch (c.f.t)
{
case ST_CellFormulaType.normal:
row[cell.Col].Formula = c.f.Value;
break;
case ST_CellFormulaType.shared:
if (c.f.siSpecified)
{
SharedFormula sharedFormula = null;
if (c.f.@ref != null && c.f.Value != null) //shared formula definition
{
var range = Range.Parse(c.f.@ref);
var origin = cell;
var formula = c.f.Value;
sharedFormula = new SharedFormula(formula, range, origin);
sharedFormulas[c.f.si] = sharedFormula;
}
else
{
sharedFormulas.TryGetValue(c.f.si, out sharedFormula);
}
row[cell.Col].SharedFormula = sharedFormula;
}
break;
case ST_CellFormulaType.array:
row[cell.Col].Formula = "{" + c.f.Value + "}";
break;
}
}
}
}
if (xml.rowBreaks != null)
{
foreach (var brk in xml.rowBreaks.brk)
{
if (brk.man)
{
sheet.RowBreaks.Add((int)brk.id - 1);
}
}
}
if (xml.pageSetup != null)
{
switch (xml.pageSetup.orientation)
{
case ST_Orientation.portrait:
sheet.Page.Orientation = PageOrientation.Portrait;
break;
case ST_Orientation.landscape:
sheet.Page.Orientation = PageOrientation.Landscape;
break;
}
if (xml.pageSetup.scale > 0)
{
sheet.Page.Scale = (int)xml.pageSetup.scale;
}
}
if (xml.pageMargins != null)
{
if (xml.pageMargins != null)
{
sheet.Page.Margins = new PageMargins
{
Bottom = xml.pageMargins.bottom,
Footer = xml.pageMargins.footer,
Header = xml.pageMargins.header,
Left = xml.pageMargins.left,
Right = xml.pageMargins.right,
Top = xml.pageMargins.top
}
}
;
}
}
}
//conditional formatting
if (xml.conditionalFormatting != null)
{
foreach (CT_ConditionalFormatting conditionalFormatting in xml.conditionalFormatting)
{
var rules = new List <CFRule>();
if (conditionalFormatting.cfRule != null)
{
foreach (var cfRule in conditionalFormatting.cfRule)
{
var rule = GetConditionalFormattingRule(cfRule);
rules.Add(rule);
}
}
ConditionalFormatting cf = new ConditionalFormatting
{
Ranges = conditionalFormatting.sqref.ToList(),
Rules = rules
};
sheet.ConditionalFormatting.Add(cf);
}
}
}
catch (Exception ex)
{
Debug.WriteLine(ex.Message);
Debug.WriteLine(ex.StackTrace);
throw;
}
}
