private void WriteDatasheet(SpreadsheetDocument doc, WorksheetPart worksheetPart, DataTable source, string tabName)
{
MemorySpreadsheet spreadSheet = new MemorySpreadsheet();
int rowNum = 1;
int colNum = 1;
int maxRow = source.Rows.Count + 1; // 1 for header
int maxCol = source.Columns.Count;
foreach (DataColumn oneColumn in source.Columns)
{
spreadSheet.SetCellValue(rowNum, colNum, oneColumn.ColumnName,
GetStyleIndex(doc, rowNum, colNum, maxRow, maxCol));
colNum += 1;
}
rowNum += 1;
foreach (DataRow oneRow in source.Rows)
{
for (colNum = 1; colNum <= source.Columns.Count; colNum += 1)
{
spreadSheet.SetCellValue(rowNum, colNum, oneRow[colNum - 1] /* Index starts at zero */,
GetStyleIndex(doc, rowNum, colNum, maxRow, maxCol));
}
rowNum += 1;
}
WorksheetAccessor.SetSheetContents(doc, worksheetPart, spreadSheet);
WorksheetAccessor.SetRange(doc, tabName, tabName, 1, 1, rowNum - 1, source.Columns.Count);
}
static void Main(string[] args)
{
// Change sheet name in formulas
using (OpenXmlMemoryStreamDocument streamDoc = new OpenXmlMemoryStreamDocument(
SmlDocument.FromFileName("../../Formulas.xlsx")))
{
using (SpreadsheetDocument doc = streamDoc.GetSpreadsheetDocument())
{
WorksheetAccessor.FormulaReplaceSheetName(doc, "Source", "'Source 2'");
}
streamDoc.GetModifiedSmlDocument().SaveAs("../../FormulasUpdated.xlsx");
}
// Change sheet name in formulas
using (OpenXmlMemoryStreamDocument streamDoc = new OpenXmlMemoryStreamDocument(
SmlDocument.FromFileName("../../Formulas.xlsx")))
{
using (SpreadsheetDocument doc = streamDoc.GetSpreadsheetDocument())
{
WorksheetPart sheet = WorksheetAccessor.GetWorksheet(doc, "References");
WorksheetAccessor.CopyCellRange(doc, sheet, 1, 1, 7, 5, 4, 8);
}
streamDoc.GetModifiedSmlDocument().SaveAs("../../FormulasCopied.xlsx");
}
}
static private void findStartData(SpreadsheetDocument doc, out int col, out int row, int maxCol, int maxRow)
{
var wsheet = WorksheetAccessor.GetWorksheet(doc, "Sheet1");
col = row = 1;
Object cv = null;
do
{
cv = WorksheetAccessor.GetCellValue(doc, wsheet, col, row);
if (cv != null)
{
do
{
col -= 1;
cv = WorksheetAccessor.GetCellValue(doc, wsheet, col, row);
} while (cv != null);
col += 1;
return;
}
else
{
col += 1;
row += 1;
}
} while (cv == null && row <= maxRow && col <= maxCol);
}
static void Main(string[] args)
{
var n = DateTime.Now;
var tempDi = new DirectoryInfo(string.Format("ExampleOutput-{0:00}-{1:00}-{2:00}-{3:00}{4:00}{5:00}", n.Year - 2000, n.Month, n.Day, n.Hour, n.Minute, n.Second));
tempDi.Create();
// Change sheet name in formulas
using (OpenXmlMemoryStreamDocument streamDoc = new OpenXmlMemoryStreamDocument(
SmlDocument.FromFileName("../../Formulas.xlsx")))
{
using (SpreadsheetDocument doc = streamDoc.GetSpreadsheetDocument())
{
WorksheetAccessor.FormulaReplaceSheetName(doc, "Source", "'Source 2'");
}
streamDoc.GetModifiedSmlDocument().SaveAs(Path.Combine(tempDi.FullName, "FormulasUpdated.xlsx"));
}
// Change sheet name in formulas
using (OpenXmlMemoryStreamDocument streamDoc = new OpenXmlMemoryStreamDocument(
SmlDocument.FromFileName("../../Formulas.xlsx")))
{
using (SpreadsheetDocument doc = streamDoc.GetSpreadsheetDocument())
{
WorksheetPart sheet = WorksheetAccessor.GetWorksheet(doc, "References");
WorksheetAccessor.CopyCellRange(doc, sheet, 1, 1, 7, 5, 4, 8);
}
streamDoc.GetModifiedSmlDocument().SaveAs(Path.Combine(tempDi.FullName, "FormulasCopied.xlsx"));
}
}
/// <summary>
/// Creates a spreadsheet document from a value table
/// </summary>
/// <param name="filePath">Path to store the document</param>
/// <param name="headerList">Contents of first row (header)</param>
/// <param name="valueTable">Contents of data</param>
/// <param name="initialRow">Row to start copying data from</param>
/// <returns></returns>
public static void Create(SpreadsheetDocument document, List <string> headerList, string[][] valueTable, int initialRow)
{
headerRow = initialRow;
//Creates a worksheet with given data
WorksheetPart worksheet = WorksheetAccessor.Create(document, headerList, valueTable, headerRow);
}
public void Formulas1()
{
var sourceFile = GetFilePath("Formulas1/Formulas.xlsx");
// Change sheet name in formulas
using (var streamDoc = new OpenXmlMemoryStreamDocument(SmlDocument.FromFileName(sourceFile)))
{
using (var doc = streamDoc.GetSpreadsheetDocument())
{
WorksheetAccessor.FormulaReplaceSheetName(doc, "Source", "'Source 2'");
}
streamDoc.GetModifiedSmlDocument().SaveAs(Path.Combine(TempDir, "FormulasUpdated.xlsx"));
}
// Change sheet name in formulas
using (var streamDoc = new OpenXmlMemoryStreamDocument(SmlDocument.FromFileName(sourceFile)))
{
using (var doc = streamDoc.GetSpreadsheetDocument())
{
var sheet = WorksheetAccessor.GetWorksheet(doc, "References");
WorksheetAccessor.CopyCellRange(doc, sheet, 1, 1, 7, 5, 4, 8);
}
streamDoc.GetModifiedSmlDocument().SaveAs(Path.Combine(TempDir, "FormulasCopied.xlsx"));
}
}
public static string SetFormula(string sheetName, int startRow, int startColumn, int endRow, int endColumn, bool fixedFormula = true)
{
var formula = new StringBuilder();
if (string.IsNullOrEmpty(sheetName) == false)
{
formula.Append(sheetName);
formula.Append('!');
}
if (fixedFormula)
{
formula.Append('$');
}
formula.Append(WorksheetAccessor.GetColumnId(startColumn));
if (fixedFormula)
{
formula.Append('$');
}
formula.Append(startRow);
formula.Append(':');
if (fixedFormula)
{
formula.Append('$');
}
formula.Append(WorksheetAccessor.GetColumnId(endColumn));
if (fixedFormula)
{
formula.Append('$');
}
formula.Append(endRow);
return(formula.ToString());
}
/// <summary>
/// Gets a formatted representation of a cell range from a worksheet
/// </summary>
private static string GetRangeReference(string worksheet, int startColumn, int startRow, int endColumn, int endRow)
{
return(string.Format("{0}!{1}{2}:{3}{4}",
worksheet,
WorksheetAccessor.GetColumnId(startColumn),
startRow,
WorksheetAccessor.GetColumnId(endColumn),
endRow
));
}
public void BuildExcelDocument(DataTable source, string targetFile, string tabName)
{
using (OpenXmlMemoryStreamDocument streamDoc = OpenXmlMemoryStreamDocument.CreateSpreadsheetDocument())
{
using (SpreadsheetDocument doc = streamDoc.GetSpreadsheetDocument())
{
WorksheetAccessor.CreateDefaultStyles(doc);
// We could use TableName as tabName: source.TableName
WorksheetPart sheetPart = WorksheetAccessor.AddWorksheet(doc, tabName);
WriteDatasheet(doc, sheetPart, source, tabName);
}
//streamDoc.GetModifiedSmlDocument().SaveAs(targetFile);
streamDoc.GetModifiedSmlDocument().SaveAs(targetFile);
}
}
protected override void ProcessRecord()
{
foreach (var document in AllDocuments("Set-OpenXmlSpreadSheetCellValue"))
{
try
{
if (!(document is SmlDocument))
{
throw new PowerToolsDocumentException("Not a spreadsheet document.");
}
OutputDocument(WorksheetAccessor.SetCellValue((SmlDocument)document, worksheetName, fromRow, toRow, fromColumn, toColumn, _value));
}
catch (Exception e)
{
WriteError(PowerToolsExceptionHandling.GetExceptionErrorRecord(e, document));
}
}
}
private static void Main()
{
var n = DateTime.Now;
var tempDi = new DirectoryInfo(string.Format("ExampleOutput-{0:00}-{1:00}-{2:00}-{3:00}{4:00}{5:00}", n.Year - 2000, n.Month, n.Day, n.Hour, n.Minute, n.Second));
tempDi.Create();
// Update an existing pivot table
var qs = new FileInfo("../../QuarterlySales.xlsx");
var qsu = new FileInfo(Path.Combine(tempDi.FullName, "QuarterlyPivot.xlsx"));
var row = 1;
using (var streamDoc = new OpenXmlMemoryStreamDocument(
OpenXmlPowerToolsDocument.FromFileName(qs.FullName)))
{
using (var doc = streamDoc.GetSpreadsheetDocument())
{
var sheet = WorksheetAccessor.GetWorksheet(doc, "Range");
using (var source = new StreamReader("../../PivotData.txt"))
{
while (!source.EndOfStream)
{
var line = source.ReadLine();
if (line.Length > 3)
{
var fields = line.Split(',');
var column = 1;
foreach (var item in fields)
{
if (double.TryParse(item, out var num))
{
WorksheetAccessor.SetCellValue(sheet, row, column++, num);
}
else
{
WorksheetAccessor.SetCellValue(sheet, row, column++, item);
}
}
}
row++;
}
}
sheet.PutXDocument();
WorksheetAccessor.UpdateRangeEndRow(doc, "Sales", row - 1);
}
streamDoc.GetModifiedSmlDocument().SaveAs(qsu.FullName);
}
// Create from scratch
row = 1;
var maxColumn = 1;
using (var streamDoc = OpenXmlMemoryStreamDocument.CreateSpreadsheetDocument())
{
using (var doc = streamDoc.GetSpreadsheetDocument())
{
WorksheetAccessor.CreateDefaultStyles(doc);
var sheet = WorksheetAccessor.AddWorksheet(doc, "Range");
var ms = new MemorySpreadsheet();
var southIndex = WorksheetAccessor.GetStyleIndex(doc, 0, 8, 1, 2,
new WorksheetAccessor.CellAlignment {
HorizontalAlignment = WorksheetAccessor.CellAlignment.Horizontal.Center
},
true, false);
var gradient = new WorksheetAccessor.GradientFill(90);
gradient.AddStop(new WorksheetAccessor.GradientStop(0, new WorksheetAccessor.ColorInfo("FF92D050")));
gradient.AddStop(new WorksheetAccessor.GradientStop(1, new WorksheetAccessor.ColorInfo("FF0070C0")));
var northIndex = WorksheetAccessor.GetStyleIndex(doc, 0,
WorksheetAccessor.GetFontIndex(doc, new WorksheetAccessor.Font
{
Italic = true,
Size = 8,
Color = new WorksheetAccessor.ColorInfo(WorksheetAccessor.ColorInfo.ColorType.Theme, 1),
Name = "Times New Roman",
Family = 1
}),
WorksheetAccessor.GetFillIndex(doc, gradient),
WorksheetAccessor.GetBorderIndex(doc, new WorksheetAccessor.Border
{
DiagonalDown = true,
Diagonal =
new WorksheetAccessor.BorderLine(WorksheetAccessor.BorderLine.LineStyle.Thin, new WorksheetAccessor.ColorInfo("FF616100"))
}),
null, false, false);
WorksheetAccessor.CheckNumberFormat(doc, 100, "_(\"$\"* #,##0.00_);_(\"$\"* \\(#,##0.00\\);_(\"$\"* \"-\"??_);_(@_)");
var amountIndex = WorksheetAccessor.GetStyleIndex(doc, 100, 0, 0, 0, null, false, false);
using (var source = new StreamReader("../../PivotData.txt"))
{
while (!source.EndOfStream)
{
var line = source.ReadLine();
if (line.Length > 3)
{
var fields = line.Split(',');
var column = 1;
foreach (var item in fields)
{
if (double.TryParse(item, out var num))
{
if (column == 6)
{
ms.SetCellValue(row, column++, num, amountIndex);
}
else
{
ms.SetCellValue(row, column++, num);
}
}
else if (item == "Accessories")
{
ms.SetCellValue(row, column++, item, WorksheetAccessor.GetStyleIndex(doc, "Good"));
}
else if (item == "South")
{
ms.SetCellValue(row, column++, item, southIndex);
}
else if (item == "North")
{
ms.SetCellValue(row, column++, item, northIndex);
}
else
{
ms.SetCellValue(row, column++, item);
}
}
maxColumn = column - 1;
}
row++;
}
}
WorksheetAccessor.SetSheetContents(sheet, ms);
WorksheetAccessor.SetRange(doc, "Sales", "Range", 1, 1, row - 1, maxColumn);
var pivot = WorksheetAccessor.AddWorksheet(doc, "Pivot");
WorksheetAccessor.CreatePivotTable(doc, "Sales", pivot);
// Configure pivot table rows, columns, data and filters
WorksheetAccessor.AddPivotAxis(pivot, "Year", WorksheetAccessor.PivotAxis.Column);
WorksheetAccessor.AddPivotAxis(pivot, "Quarter", WorksheetAccessor.PivotAxis.Column);
WorksheetAccessor.AddPivotAxis(pivot, "Category", WorksheetAccessor.PivotAxis.Row);
WorksheetAccessor.AddPivotAxis(pivot, "Product", WorksheetAccessor.PivotAxis.Row);
WorksheetAccessor.AddDataValue(doc, pivot, "Amount");
WorksheetAccessor.AddPivotAxis(pivot, "Region", WorksheetAccessor.PivotAxis.Page);
}
streamDoc.GetModifiedSmlDocument().SaveAs(Path.Combine(tempDi.FullName, "NewPivot.xlsx"));
}
// Add pivot table to existing spreadsheet
// Demonstrate multiple data fields
using (var streamDoc = new OpenXmlMemoryStreamDocument(
OpenXmlPowerToolsDocument.FromFileName("../../QuarterlyUnitSales.xlsx")))
{
using (var doc = streamDoc.GetSpreadsheetDocument())
{
var pivot = WorksheetAccessor.AddWorksheet(doc, "Pivot");
WorksheetAccessor.CreatePivotTable(doc, "Sales", pivot);
// Configure pivot table rows, columns, data and filters
WorksheetAccessor.AddPivotAxis(pivot, "Year", WorksheetAccessor.PivotAxis.Column);
WorksheetAccessor.AddPivotAxis(pivot, "Quarter", WorksheetAccessor.PivotAxis.Column);
WorksheetAccessor.AddPivotAxis(pivot, "Category", WorksheetAccessor.PivotAxis.Row);
WorksheetAccessor.AddPivotAxis(pivot, "Product", WorksheetAccessor.PivotAxis.Row);
WorksheetAccessor.AddDataValue(doc, pivot, "Total");
WorksheetAccessor.AddDataValue(doc, pivot, "Quantity");
WorksheetAccessor.AddDataValue(doc, pivot, "Unit Price");
WorksheetAccessor.AddPivotAxis(pivot, "Region", WorksheetAccessor.PivotAxis.Page);
}
streamDoc.GetModifiedSmlDocument().SaveAs(Path.Combine(tempDi.FullName, "QuarterlyUnitSalesWithPivot.xlsx"));
}
}
static void Main(string[] args)
{
// Update an existing pivot table
FileInfo qs = new FileInfo("../../QuarterlySales.xlsx");
FileInfo qsu = new FileInfo("../../QuarterlyPivot.xlsx");
int row = 1;
using (OpenXmlMemoryStreamDocument streamDoc = new OpenXmlMemoryStreamDocument(
SmlDocument.FromFileName(qs.FullName)))
{
using (SpreadsheetDocument doc = streamDoc.GetSpreadsheetDocument())
{
WorksheetPart sheet = WorksheetAccessor.GetWorksheet(doc, "Range");
using (StreamReader source = new StreamReader("../../PivotData.txt"))
{
while (!source.EndOfStream)
{
string line = source.ReadLine();
if (line.Length > 3)
{
string[] fields = line.Split(',');
int column = 1;
foreach (string item in fields)
{
double num;
if (double.TryParse(item, out num))
{
WorksheetAccessor.SetCellValue(doc, sheet, row, column++, num);
}
else
{
WorksheetAccessor.SetCellValue(doc, sheet, row, column++, item);
}
}
}
row++;
}
}
sheet.PutXDocument();
WorksheetAccessor.UpdateRangeEndRow(doc, "Sales", row - 1);
}
streamDoc.GetModifiedSmlDocument().SaveAs(qsu.FullName);
}
// Create from scratch
row = 1;
int maxColumn = 1;
using (OpenXmlMemoryStreamDocument streamDoc = OpenXmlMemoryStreamDocument.CreateSpreadsheetDocument())
{
using (SpreadsheetDocument doc = streamDoc.GetSpreadsheetDocument())
{
WorksheetAccessor.CreateDefaultStyles(doc);
WorksheetPart sheet = WorksheetAccessor.AddWorksheet(doc, "Range");
MemorySpreadsheet ms = new MemorySpreadsheet();
#if false
int font0 = WorksheetAccessor.GetFontIndex(doc, new WorksheetAccessor.Font
{
Size = 11,
Color = new WorksheetAccessor.ColorInfo(WorksheetAccessor.ColorInfo.ColorType.Theme, 1),
Name = "Calibri",
Family = 2,
Scheme = WorksheetAccessor.Font.SchemeType.Minor
});
int font2 = WorksheetAccessor.GetFontIndex(doc, new WorksheetAccessor.Font
{
Bold = true,
Size = 18,
Color = new WorksheetAccessor.ColorInfo(WorksheetAccessor.ColorInfo.ColorType.Theme, 3),
Name = "Cambria",
Family = 2,
Scheme = WorksheetAccessor.Font.SchemeType.Major
});
int font3 = WorksheetAccessor.GetFontIndex(doc, new WorksheetAccessor.Font
{
Bold = true,
Size = 15,
Color = new WorksheetAccessor.ColorInfo(WorksheetAccessor.ColorInfo.ColorType.Theme, 3),
Name = "Calibri",
Family = 2,
Scheme = WorksheetAccessor.Font.SchemeType.Minor
});
int font4 = WorksheetAccessor.GetFontIndex(doc, new WorksheetAccessor.Font
{
Bold = true,
Size = 13,
Color = new WorksheetAccessor.ColorInfo(WorksheetAccessor.ColorInfo.ColorType.Theme, 3),
Name = "Calibri",
Family = 2,
Scheme = WorksheetAccessor.Font.SchemeType.Minor
});
int font5 = WorksheetAccessor.GetFontIndex(doc, new WorksheetAccessor.Font
{
Bold = true,
Size = 11,
Color = new WorksheetAccessor.ColorInfo(WorksheetAccessor.ColorInfo.ColorType.Theme, 3),
Name = "Calibri",
Family = 2,
Scheme = WorksheetAccessor.Font.SchemeType.Minor
});
int font6 = WorksheetAccessor.GetFontIndex(doc, new WorksheetAccessor.Font
{
Size = 11,
Color = new WorksheetAccessor.ColorInfo("FF006100"),
Name = "Calibri",
Family = 2,
Scheme = WorksheetAccessor.Font.SchemeType.Minor
});
int font7 = WorksheetAccessor.GetFontIndex(doc, new WorksheetAccessor.Font
{
Size = 11,
Color = new WorksheetAccessor.ColorInfo("FF9C0006"),
Name = "Calibri",
Family = 2,
Scheme = WorksheetAccessor.Font.SchemeType.Minor
});
int font8 = WorksheetAccessor.GetFontIndex(doc, new WorksheetAccessor.Font
{
Size = 11,
Color = new WorksheetAccessor.ColorInfo("FF9C6500"),
Name = "Calibri",
Family = 2,
Scheme = WorksheetAccessor.Font.SchemeType.Minor
});
int font9 = WorksheetAccessor.GetFontIndex(doc, new WorksheetAccessor.Font
{
Size = 11,
Color = new WorksheetAccessor.ColorInfo("FF3F3F76"),
Name = "Calibri",
Family = 2,
Scheme = WorksheetAccessor.Font.SchemeType.Minor
});
int font10 = WorksheetAccessor.GetFontIndex(doc, new WorksheetAccessor.Font
{
Bold = true,
Size = 11,
Color = new WorksheetAccessor.ColorInfo("FF3F3F3F"),
Name = "Calibri",
Family = 2,
Scheme = WorksheetAccessor.Font.SchemeType.Minor
});
int font11 = WorksheetAccessor.GetFontIndex(doc, new WorksheetAccessor.Font
{
Bold = true,
Size = 11,
Color = new WorksheetAccessor.ColorInfo("FFFA7D00"),
Name = "Calibri",
Family = 2,
Scheme = WorksheetAccessor.Font.SchemeType.Minor
});
int font12 = WorksheetAccessor.GetFontIndex(doc, new WorksheetAccessor.Font
{
Size = 11,
Color = new WorksheetAccessor.ColorInfo("FFFA7D00"),
Name = "Calibri",
Family = 2,
Scheme = WorksheetAccessor.Font.SchemeType.Minor
});
int font13 = WorksheetAccessor.GetFontIndex(doc, new WorksheetAccessor.Font
{
Bold = true,
Size = 11,
Color = new WorksheetAccessor.ColorInfo(WorksheetAccessor.ColorInfo.ColorType.Theme, 0),
Name = "Calibri",
Family = 2,
Scheme = WorksheetAccessor.Font.SchemeType.Minor
});
int font14 = WorksheetAccessor.GetFontIndex(doc, new WorksheetAccessor.Font
{
Size = 11,
Color = new WorksheetAccessor.ColorInfo("FFFF0000"),
Name = "Calibri",
Family = 2,
Scheme = WorksheetAccessor.Font.SchemeType.Minor
});
int font15 = WorksheetAccessor.GetFontIndex(doc, new WorksheetAccessor.Font
{
Italic = true,
Size = 11,
Color = new WorksheetAccessor.ColorInfo("FF7F7F7F"),
Name = "Calibri",
Family = 2,
Scheme = WorksheetAccessor.Font.SchemeType.Minor
});
int font16 = WorksheetAccessor.GetFontIndex(doc, new WorksheetAccessor.Font
{
Bold = true,
Size = 11,
Color = new WorksheetAccessor.ColorInfo(WorksheetAccessor.ColorInfo.ColorType.Theme, 1),
Name = "Calibri",
Family = 2,
Scheme = WorksheetAccessor.Font.SchemeType.Minor
});
int font17 = WorksheetAccessor.GetFontIndex(doc, new WorksheetAccessor.Font
{
Size = 11,
Color = new WorksheetAccessor.ColorInfo(WorksheetAccessor.ColorInfo.ColorType.Theme, 0),
Name = "Calibri",
Family = 2,
Scheme = WorksheetAccessor.Font.SchemeType.Minor
});
int fill0 = WorksheetAccessor.GetFillIndex(doc, new WorksheetAccessor.PatternFill(WorksheetAccessor.PatternFill.PatternType.None, null, null));
int fill1 = WorksheetAccessor.GetFillIndex(doc, new WorksheetAccessor.PatternFill(WorksheetAccessor.PatternFill.PatternType.Gray125, null, null));
int fill2 = WorksheetAccessor.GetFillIndex(doc, new WorksheetAccessor.PatternFill(WorksheetAccessor.PatternFill.PatternType.Solid,
null, new WorksheetAccessor.ColorInfo("FFC6EFCE")));
int fill3 = WorksheetAccessor.GetFillIndex(doc, new WorksheetAccessor.PatternFill(WorksheetAccessor.PatternFill.PatternType.Solid,
null, new WorksheetAccessor.ColorInfo("FFFFC7CE")));
int fill4 = WorksheetAccessor.GetFillIndex(doc, new WorksheetAccessor.PatternFill(WorksheetAccessor.PatternFill.PatternType.Solid,
null, new WorksheetAccessor.ColorInfo("FFFFEB9C")));
int fill5 = WorksheetAccessor.GetFillIndex(doc, new WorksheetAccessor.PatternFill(WorksheetAccessor.PatternFill.PatternType.Solid,
null, new WorksheetAccessor.ColorInfo("FFFFCC99")));
int fill6 = WorksheetAccessor.GetFillIndex(doc, new WorksheetAccessor.PatternFill(WorksheetAccessor.PatternFill.PatternType.Solid,
null, new WorksheetAccessor.ColorInfo("FFF2F2F2")));
int fill7 = WorksheetAccessor.GetFillIndex(doc, new WorksheetAccessor.PatternFill(WorksheetAccessor.PatternFill.PatternType.Solid,
null, new WorksheetAccessor.ColorInfo("FFA5A5A5")));
int fill8 = WorksheetAccessor.GetFillIndex(doc, new WorksheetAccessor.PatternFill(WorksheetAccessor.PatternFill.PatternType.Solid,
null, new WorksheetAccessor.ColorInfo("FFFFFFCC")));
int fill9 = WorksheetAccessor.GetFillIndex(doc, new WorksheetAccessor.PatternFill(WorksheetAccessor.PatternFill.PatternType.Solid,
null, new WorksheetAccessor.ColorInfo(WorksheetAccessor.ColorInfo.ColorType.Theme, 4)));
int fill10 = WorksheetAccessor.GetFillIndex(doc, new WorksheetAccessor.PatternFill(WorksheetAccessor.PatternFill.PatternType.Solid,
new WorksheetAccessor.ColorInfo(WorksheetAccessor.ColorInfo.ColorType.Indexed, 65),
new WorksheetAccessor.ColorInfo(4, 0.79998168889431442)));
int fill11 = WorksheetAccessor.GetFillIndex(doc, new WorksheetAccessor.PatternFill(WorksheetAccessor.PatternFill.PatternType.Solid,
new WorksheetAccessor.ColorInfo(WorksheetAccessor.ColorInfo.ColorType.Indexed, 65),
new WorksheetAccessor.ColorInfo(4, 0.59999389629810485)));
int fill12 = WorksheetAccessor.GetFillIndex(doc, new WorksheetAccessor.PatternFill(WorksheetAccessor.PatternFill.PatternType.Solid,
new WorksheetAccessor.ColorInfo(WorksheetAccessor.ColorInfo.ColorType.Indexed, 65),
new WorksheetAccessor.ColorInfo(4, 0.39997558519241921)));
int fill13 = WorksheetAccessor.GetFillIndex(doc, new WorksheetAccessor.PatternFill(WorksheetAccessor.PatternFill.PatternType.Solid,
null, new WorksheetAccessor.ColorInfo(WorksheetAccessor.ColorInfo.ColorType.Theme, 5)));
int fill14 = WorksheetAccessor.GetFillIndex(doc, new WorksheetAccessor.PatternFill(WorksheetAccessor.PatternFill.PatternType.Solid,
new WorksheetAccessor.ColorInfo(WorksheetAccessor.ColorInfo.ColorType.Indexed, 65),
new WorksheetAccessor.ColorInfo(5, 0.79998168889431442)));
int fill15 = WorksheetAccessor.GetFillIndex(doc, new WorksheetAccessor.PatternFill(WorksheetAccessor.PatternFill.PatternType.Solid,
new WorksheetAccessor.ColorInfo(WorksheetAccessor.ColorInfo.ColorType.Indexed, 65),
new WorksheetAccessor.ColorInfo(5, 0.59999389629810485)));
int fill16 = WorksheetAccessor.GetFillIndex(doc, new WorksheetAccessor.PatternFill(WorksheetAccessor.PatternFill.PatternType.Solid,
new WorksheetAccessor.ColorInfo(WorksheetAccessor.ColorInfo.ColorType.Indexed, 65),
new WorksheetAccessor.ColorInfo(5, 0.39997558519241921)));
int fill17 = WorksheetAccessor.GetFillIndex(doc, new WorksheetAccessor.PatternFill(WorksheetAccessor.PatternFill.PatternType.Solid,
null, new WorksheetAccessor.ColorInfo(WorksheetAccessor.ColorInfo.ColorType.Theme, 6)));
int fill18 = WorksheetAccessor.GetFillIndex(doc, new WorksheetAccessor.PatternFill(WorksheetAccessor.PatternFill.PatternType.Solid,
new WorksheetAccessor.ColorInfo(WorksheetAccessor.ColorInfo.ColorType.Indexed, 65),
new WorksheetAccessor.ColorInfo(6, 0.79998168889431442)));
int fill19 = WorksheetAccessor.GetFillIndex(doc, new WorksheetAccessor.PatternFill(WorksheetAccessor.PatternFill.PatternType.Solid,
new WorksheetAccessor.ColorInfo(WorksheetAccessor.ColorInfo.ColorType.Indexed, 65),
new WorksheetAccessor.ColorInfo(6, 0.59999389629810485)));
int fill20 = WorksheetAccessor.GetFillIndex(doc, new WorksheetAccessor.PatternFill(WorksheetAccessor.PatternFill.PatternType.Solid,
new WorksheetAccessor.ColorInfo(WorksheetAccessor.ColorInfo.ColorType.Indexed, 65),
new WorksheetAccessor.ColorInfo(6, 0.39997558519241921)));
int fill21 = WorksheetAccessor.GetFillIndex(doc, new WorksheetAccessor.PatternFill(WorksheetAccessor.PatternFill.PatternType.Solid,
null, new WorksheetAccessor.ColorInfo(WorksheetAccessor.ColorInfo.ColorType.Theme, 7)));
int fill22 = WorksheetAccessor.GetFillIndex(doc, new WorksheetAccessor.PatternFill(WorksheetAccessor.PatternFill.PatternType.Solid,
new WorksheetAccessor.ColorInfo(WorksheetAccessor.ColorInfo.ColorType.Indexed, 65),
new WorksheetAccessor.ColorInfo(7, 0.79998168889431442)));
int fill23 = WorksheetAccessor.GetFillIndex(doc, new WorksheetAccessor.PatternFill(WorksheetAccessor.PatternFill.PatternType.Solid,
new WorksheetAccessor.ColorInfo(WorksheetAccessor.ColorInfo.ColorType.Indexed, 65),
new WorksheetAccessor.ColorInfo(7, 0.59999389629810485)));
int fill24 = WorksheetAccessor.GetFillIndex(doc, new WorksheetAccessor.PatternFill(WorksheetAccessor.PatternFill.PatternType.Solid,
new WorksheetAccessor.ColorInfo(WorksheetAccessor.ColorInfo.ColorType.Indexed, 65),
new WorksheetAccessor.ColorInfo(7, 0.39997558519241921)));
int fill25 = WorksheetAccessor.GetFillIndex(doc, new WorksheetAccessor.PatternFill(WorksheetAccessor.PatternFill.PatternType.Solid,
null, new WorksheetAccessor.ColorInfo(WorksheetAccessor.ColorInfo.ColorType.Theme, 8)));
int fill26 = WorksheetAccessor.GetFillIndex(doc, new WorksheetAccessor.PatternFill(WorksheetAccessor.PatternFill.PatternType.Solid,
new WorksheetAccessor.ColorInfo(WorksheetAccessor.ColorInfo.ColorType.Indexed, 65),
new WorksheetAccessor.ColorInfo(8, 0.79998168889431442)));
int fill27 = WorksheetAccessor.GetFillIndex(doc, new WorksheetAccessor.PatternFill(WorksheetAccessor.PatternFill.PatternType.Solid,
new WorksheetAccessor.ColorInfo(WorksheetAccessor.ColorInfo.ColorType.Indexed, 65),
new WorksheetAccessor.ColorInfo(8, 0.59999389629810485)));
int fill28 = WorksheetAccessor.GetFillIndex(doc, new WorksheetAccessor.PatternFill(WorksheetAccessor.PatternFill.PatternType.Solid,
new WorksheetAccessor.ColorInfo(WorksheetAccessor.ColorInfo.ColorType.Indexed, 65),
new WorksheetAccessor.ColorInfo(8, 0.39997558519241921)));
int fill29 = WorksheetAccessor.GetFillIndex(doc, new WorksheetAccessor.PatternFill(WorksheetAccessor.PatternFill.PatternType.Solid,
null, new WorksheetAccessor.ColorInfo(WorksheetAccessor.ColorInfo.ColorType.Theme, 9)));
int fill30 = WorksheetAccessor.GetFillIndex(doc, new WorksheetAccessor.PatternFill(WorksheetAccessor.PatternFill.PatternType.Solid,
new WorksheetAccessor.ColorInfo(WorksheetAccessor.ColorInfo.ColorType.Indexed, 65),
new WorksheetAccessor.ColorInfo(9, 0.79998168889431442)));
int fill31 = WorksheetAccessor.GetFillIndex(doc, new WorksheetAccessor.PatternFill(WorksheetAccessor.PatternFill.PatternType.Solid,
new WorksheetAccessor.ColorInfo(WorksheetAccessor.ColorInfo.ColorType.Indexed, 65),
new WorksheetAccessor.ColorInfo(9, 0.59999389629810485)));
int fill32 = WorksheetAccessor.GetFillIndex(doc, new WorksheetAccessor.PatternFill(WorksheetAccessor.PatternFill.PatternType.Solid,
new WorksheetAccessor.ColorInfo(WorksheetAccessor.ColorInfo.ColorType.Indexed, 65),
new WorksheetAccessor.ColorInfo(9, 0.39997558519241921)));
int border1 = WorksheetAccessor.GetBorderIndex(doc, new WorksheetAccessor.Border
{
Bottom = new WorksheetAccessor.BorderLine(WorksheetAccessor.BorderLine.LineStyle.Thick,
new WorksheetAccessor.ColorInfo(WorksheetAccessor.ColorInfo.ColorType.Theme, 4))
});
int border2 = WorksheetAccessor.GetBorderIndex(doc, new WorksheetAccessor.Border
{
Bottom = new WorksheetAccessor.BorderLine(WorksheetAccessor.BorderLine.LineStyle.Thick, new WorksheetAccessor.ColorInfo(4, 0.499984740745262))
});
int border3 = WorksheetAccessor.GetBorderIndex(doc, new WorksheetAccessor.Border
{
Bottom = new WorksheetAccessor.BorderLine(WorksheetAccessor.BorderLine.LineStyle.Medium, new WorksheetAccessor.ColorInfo(4, 0.39997558519241921))
});
int border4 = WorksheetAccessor.GetBorderIndex(doc, new WorksheetAccessor.Border
{
Left = new WorksheetAccessor.BorderLine(WorksheetAccessor.BorderLine.LineStyle.Thin, new WorksheetAccessor.ColorInfo("FF7F7F7F")),
Right = new WorksheetAccessor.BorderLine(WorksheetAccessor.BorderLine.LineStyle.Thin, new WorksheetAccessor.ColorInfo("FF7F7F7F")),
Top = new WorksheetAccessor.BorderLine(WorksheetAccessor.BorderLine.LineStyle.Thin, new WorksheetAccessor.ColorInfo("FF7F7F7F")),
Bottom = new WorksheetAccessor.BorderLine(WorksheetAccessor.BorderLine.LineStyle.Thin, new WorksheetAccessor.ColorInfo("FF7F7F7F"))
});
int border5 = WorksheetAccessor.GetBorderIndex(doc, new WorksheetAccessor.Border
{
Left = new WorksheetAccessor.BorderLine(WorksheetAccessor.BorderLine.LineStyle.Thin, new WorksheetAccessor.ColorInfo("FF3F3F3F")),
Right = new WorksheetAccessor.BorderLine(WorksheetAccessor.BorderLine.LineStyle.Thin, new WorksheetAccessor.ColorInfo("FF3F3F3F")),
Top = new WorksheetAccessor.BorderLine(WorksheetAccessor.BorderLine.LineStyle.Thin, new WorksheetAccessor.ColorInfo("FF3F3F3F")),
Bottom = new WorksheetAccessor.BorderLine(WorksheetAccessor.BorderLine.LineStyle.Thin, new WorksheetAccessor.ColorInfo("FF3F3F3F"))
});
int border6 = WorksheetAccessor.GetBorderIndex(doc, new WorksheetAccessor.Border
{
Bottom = new WorksheetAccessor.BorderLine(WorksheetAccessor.BorderLine.LineStyle.Double, new WorksheetAccessor.ColorInfo("FFFF8001"))
});
int border7 = WorksheetAccessor.GetBorderIndex(doc, new WorksheetAccessor.Border
{
Left = new WorksheetAccessor.BorderLine(WorksheetAccessor.BorderLine.LineStyle.Double, new WorksheetAccessor.ColorInfo("FF3F3F3F")),
Right = new WorksheetAccessor.BorderLine(WorksheetAccessor.BorderLine.LineStyle.Double, new WorksheetAccessor.ColorInfo("FF3F3F3F")),
Top = new WorksheetAccessor.BorderLine(WorksheetAccessor.BorderLine.LineStyle.Double, new WorksheetAccessor.ColorInfo("FF3F3F3F")),
Bottom = new WorksheetAccessor.BorderLine(WorksheetAccessor.BorderLine.LineStyle.Double, new WorksheetAccessor.ColorInfo("FF3F3F3F"))
});
int border8 = WorksheetAccessor.GetBorderIndex(doc, new WorksheetAccessor.Border
{
Left = new WorksheetAccessor.BorderLine(WorksheetAccessor.BorderLine.LineStyle.Thin, new WorksheetAccessor.ColorInfo("FFB2B2B2")),
Right = new WorksheetAccessor.BorderLine(WorksheetAccessor.BorderLine.LineStyle.Thin, new WorksheetAccessor.ColorInfo("FFB2B2B2")),
Top = new WorksheetAccessor.BorderLine(WorksheetAccessor.BorderLine.LineStyle.Thin, new WorksheetAccessor.ColorInfo("FFB2B2B2")),
Bottom = new WorksheetAccessor.BorderLine(WorksheetAccessor.BorderLine.LineStyle.Thin, new WorksheetAccessor.ColorInfo("FFB2B2B2"))
});
int border9 = WorksheetAccessor.GetBorderIndex(doc, new WorksheetAccessor.Border
{
Top = new WorksheetAccessor.BorderLine(WorksheetAccessor.BorderLine.LineStyle.Thin,
new WorksheetAccessor.ColorInfo(WorksheetAccessor.ColorInfo.ColorType.Theme, 4)),
Bottom = new WorksheetAccessor.BorderLine(WorksheetAccessor.BorderLine.LineStyle.Double,
new WorksheetAccessor.ColorInfo(WorksheetAccessor.ColorInfo.ColorType.Theme, 4))
});
#endif
int southIndex = WorksheetAccessor.GetStyleIndex(doc, 0, 8, 1, 2,
new WorksheetAccessor.CellAlignment {
HorizontalAlignment = WorksheetAccessor.CellAlignment.Horizontal.Center
},
true, false);
WorksheetAccessor.GradientFill gradient = new WorksheetAccessor.GradientFill(90);
gradient.AddStop(new WorksheetAccessor.GradientStop(0, new WorksheetAccessor.ColorInfo("FF92D050")));
gradient.AddStop(new WorksheetAccessor.GradientStop(1, new WorksheetAccessor.ColorInfo("FF0070C0")));
int northIndex = WorksheetAccessor.GetStyleIndex(doc, 0,
WorksheetAccessor.GetFontIndex(doc, new WorksheetAccessor.Font
{
Italic = true,
Size = 8,
Color = new WorksheetAccessor.ColorInfo(WorksheetAccessor.ColorInfo.ColorType.Theme, 1),
Name = "Times New Roman",
Family = 1
}),
WorksheetAccessor.GetFillIndex(doc, gradient),
WorksheetAccessor.GetBorderIndex(doc, new WorksheetAccessor.Border
{
DiagonalDown = true,
Diagonal =
new WorksheetAccessor.BorderLine(WorksheetAccessor.BorderLine.LineStyle.Thin, new WorksheetAccessor.ColorInfo("FF616100"))
}),
null, false, false);
WorksheetAccessor.CheckNumberFormat(doc, 100, "_(\"$\"* #,##0.00_);_(\"$\"* \\(#,##0.00\\);_(\"$\"* \"-\"??_);_(@_)");
int amountIndex = WorksheetAccessor.GetStyleIndex(doc, 100, 0, 0, 0, null, false, false);
using (StreamReader source = new StreamReader("../../PivotData.txt"))
{
while (!source.EndOfStream)
{
string line = source.ReadLine();
if (line.Length > 3)
{
string[] fields = line.Split(',');
int column = 1;
foreach (string item in fields)
{
double num;
if (double.TryParse(item, out num))
{
if (column == 6)
{
ms.SetCellValue(row, column++, num, amountIndex);
}
else
{
ms.SetCellValue(row, column++, num);
}
}
else if (item == "Accessories")
{
ms.SetCellValue(row, column++, item, WorksheetAccessor.GetStyleIndex(doc, "Good"));
}
else if (item == "South")
{
ms.SetCellValue(row, column++, item, southIndex);
}
else if (item == "North")
{
ms.SetCellValue(row, column++, item, northIndex);
}
else
{
ms.SetCellValue(row, column++, item);
}
}
maxColumn = column - 1;
}
row++;
}
}
WorksheetAccessor.SetSheetContents(doc, sheet, ms);
WorksheetAccessor.SetRange(doc, "Sales", "Range", 1, 1, row - 1, maxColumn);
WorksheetPart pivot = WorksheetAccessor.AddWorksheet(doc, "Pivot");
WorksheetAccessor.CreatePivotTable(doc, "Sales", pivot);
// Configure pivot table rows, columns, data and filters
WorksheetAccessor.AddPivotAxis(doc, pivot, "Year", WorksheetAccessor.PivotAxis.Column);
WorksheetAccessor.AddPivotAxis(doc, pivot, "Quarter", WorksheetAccessor.PivotAxis.Column);
WorksheetAccessor.AddPivotAxis(doc, pivot, "Category", WorksheetAccessor.PivotAxis.Row);
WorksheetAccessor.AddPivotAxis(doc, pivot, "Product", WorksheetAccessor.PivotAxis.Row);
WorksheetAccessor.AddDataValue(doc, pivot, "Amount");
WorksheetAccessor.AddPivotAxis(doc, pivot, "Region", WorksheetAccessor.PivotAxis.Page);
}
streamDoc.GetModifiedSmlDocument().SaveAs("../../NewPivot.xlsx");
}
// Add pivot table to existing spreadsheet
// Demonstrate multiple data fields
using (OpenXmlMemoryStreamDocument streamDoc = new OpenXmlMemoryStreamDocument(
SmlDocument.FromFileName("../../QuarterlyUnitSales.xlsx")))
{
using (SpreadsheetDocument doc = streamDoc.GetSpreadsheetDocument())
{
WorksheetPart pivot = WorksheetAccessor.AddWorksheet(doc, "Pivot");
WorksheetAccessor.CreatePivotTable(doc, "Sales", pivot);
// Configure pivot table rows, columns, data and filters
WorksheetAccessor.AddPivotAxis(doc, pivot, "Year", WorksheetAccessor.PivotAxis.Column);
WorksheetAccessor.AddPivotAxis(doc, pivot, "Quarter", WorksheetAccessor.PivotAxis.Column);
WorksheetAccessor.AddPivotAxis(doc, pivot, "Category", WorksheetAccessor.PivotAxis.Row);
WorksheetAccessor.AddPivotAxis(doc, pivot, "Product", WorksheetAccessor.PivotAxis.Row);
WorksheetAccessor.AddDataValue(doc, pivot, "Total");
WorksheetAccessor.AddDataValue(doc, pivot, "Quantity");
WorksheetAccessor.AddDataValue(doc, pivot, "Unit Price");
WorksheetAccessor.AddPivotAxis(doc, pivot, "Region", WorksheetAccessor.PivotAxis.Page);
}
streamDoc.GetModifiedSmlDocument().SaveAs("../../QuarterlyUnitSalesWithPivot.xlsx");
}
}
/// <summary>
/// Gets a formatted representation of a cell range from a worksheet
/// </summary>
private static string GetRangeReference(string worksheet, int column, int row)
{
return(string.Format("{0}!{1}{2}", worksheet, WorksheetAccessor.GetColumnId(column), row));
}
private int GetStyleIndex(SpreadsheetDocument doc, int row, int col, int maxRow, int maxCol)
{
int numFmt = 0;
int font = 0;
int fill = 0;
WorksheetAccessor.Border border = new WorksheetAccessor.Border();
WorksheetAccessor.CellAlignment alignment = new WorksheetAccessor.CellAlignment();
bool hidden = false;
bool locked = false;
string colorHtmlStr = "FF3F3F3F";
// Header
if (row == 1)
{
//numFmt = 3;
font = WorksheetAccessor.GetFontIndex(doc, new WorksheetAccessor.Font
{
Size = 12,
Color =
new WorksheetAccessor.ColorInfo(
WorksheetAccessor.ColorInfo.ColorType.Indexed, 0),
Name = "Calibri",
Family = 2,
Scheme = WorksheetAccessor.Font.SchemeType.Minor
});
border.Top = new WorksheetAccessor.BorderLine(
WorksheetAccessor.BorderLine.LineStyle.Medium,
new WorksheetAccessor.ColorInfo(colorHtmlStr));
alignment.HorizontalAlignment = WorksheetAccessor.CellAlignment.Horizontal.Center;
fill = WorksheetAccessor.GetFillIndex(doc, new WorksheetAccessor.PatternFill(
WorksheetAccessor.PatternFill.PatternType.Solid,
new WorksheetAccessor.ColorInfo(WorksheetAccessor.ColorInfo.ColorType.Indexed, 1),
new WorksheetAccessor.ColorInfo(WorksheetAccessor.ColorInfo.ColorType.Theme, 4)
));
//fill = WorksheetAccessor.GetFillIndex(doc,
// new WorksheetAccessor.PatternFill(
// WorksheetAccessor.PatternFill.PatternType.Solid,
// new WorksheetAccessor.ColorInfo(
// WorksheetAccessor.ColorInfo.ColorType.Theme, 2),
// new WorksheetAccessor.ColorInfo(
// WorksheetAccessor.ColorInfo.ColorType.Theme, 2)));
}
else
{
//numFmt = 1;
//font = WorksheetAccessor.GetFontIndex(doc, new WorksheetAccessor.Font
// {
// Bold = true,
// Size = 8,
// Color =
// new WorksheetAccessor.ColorInfo(WorksheetAccessor.ColorInfo.ColorType.Indexed, 0),
// Name = "Calibri",
// Family = 1
// });
border.Top = new WorksheetAccessor.BorderLine(
WorksheetAccessor.BorderLine.LineStyle.Thin,
new WorksheetAccessor.ColorInfo(colorHtmlStr));
if (row % 2 == 0)
{
fill = WorksheetAccessor.GetFillIndex(doc,
new WorksheetAccessor.PatternFill(
WorksheetAccessor.PatternFill.PatternType.Solid,
null,
new WorksheetAccessor.ColorInfo(2, 0.79998168889431442)));
}
else
{
fill = WorksheetAccessor.GetFillIndex(doc,
new WorksheetAccessor.PatternFill(
WorksheetAccessor.PatternFill.PatternType.Solid,
null,
new WorksheetAccessor.ColorInfo(2, 0.59999389629810485)));
}
}
// Left / Right in bold
if (col == 1)
{
border.Left = new WorksheetAccessor.BorderLine(
WorksheetAccessor.BorderLine.LineStyle.Medium,
new WorksheetAccessor.ColorInfo(colorHtmlStr));
}
else
{
border.Left = new WorksheetAccessor.BorderLine(
WorksheetAccessor.BorderLine.LineStyle.Thin,
new WorksheetAccessor.ColorInfo(colorHtmlStr));
}
if (col == maxCol)
{
border.Right = new WorksheetAccessor.BorderLine(
WorksheetAccessor.BorderLine.LineStyle.Medium,
new WorksheetAccessor.ColorInfo(colorHtmlStr));
}
else
{
border.Right = new WorksheetAccessor.BorderLine(
WorksheetAccessor.BorderLine.LineStyle.Thin,
new WorksheetAccessor.ColorInfo(colorHtmlStr));
}
// Last Row or Bottom row of the Header
if (row == maxRow || row == 1)
{
border.Bottom =
new WorksheetAccessor.BorderLine(
WorksheetAccessor.BorderLine.LineStyle.Medium,
new WorksheetAccessor.ColorInfo(colorHtmlStr));
}
else
{
border.Bottom =
new WorksheetAccessor.BorderLine(
WorksheetAccessor.BorderLine.LineStyle.Thin,
new WorksheetAccessor.ColorInfo(colorHtmlStr));
}
return(WorksheetAccessor.GetStyleIndex(doc, numFmt, font, fill, WorksheetAccessor.GetBorderIndex(doc, border), alignment, hidden, locked));
}
// Returns the row and column numbers and worksheet part for the named range
public static WorksheetPart GetFormula(SpreadsheetDocument doc, string formula, out string sheetName, out int startRow, out int startColumn, out int endRow, out int endColumn)
{
sheetName = GetFormulaCoord(formula, out startRow, out startColumn, out endRow, out endColumn);
return(WorksheetAccessor.GetWorksheet(doc, sheetName));
}
private static void UpdateCachedValues(ReportData conf, OpenXmlPart chartPart, SpreadsheetDocument spreadsheetDoc, TableDefinition content)
{
try
{
var chartDoc = chartPart.GetXDocument();
// We update the cached value now
XElement chartCached = chartDoc.Descendants(C.chart).FirstOrDefault();
if (null != chartCached)
{
// We look for Chart Title -----------------------------------------------
var titleElement = chartCached.Descendants(C.title).FirstOrDefault();
if (titleElement == null) // No title so we need to enforce no will be displayed when regenerating Cached Values
{
var autoTitleDeleted = chartCached.Descendants(C.autoTitleDeleted).FirstOrDefault();
if (autoTitleDeleted == null)
{
autoTitleDeleted = new XElement(C.autoTitleDeleted);
chartCached.Add(autoTitleDeleted);
}
autoTitleDeleted.SetAttributeValue(NoNamespace.val, "1");
}
// ----------------------------------------------------------------------
// Series in Graph ------------------------------------------------------
List <XElement> cSerie = chartCached.Descendants(C.ser).ToList();
var formulaToCheck = new List <XElement>();
int cSerieCount = cSerie.Count;
for (int ctSer = 0; ctSer < cSerieCount; ctSer += 1)
{
#region Serie Treatment
var oneSerie = cSerie[ctSer];
// We check if the content as been given for this serie
formulaToCheck.Clear();
formulaToCheck.AddRange(oneSerie.Descendants(C.numRef).Descendants(C.f).Union(oneSerie.Descendants(C.strRef).Descendants(C.f)));
bool isSerieDeleted = false;
foreach (var oneFormula in formulaToCheck)
{
int startRow;
int startCol;
int endRow;
int endCol;
string sheetName = WorksheetAccessorExt.GetFormulaCoord(oneFormula.Value, out startRow, out startCol, out endRow, out endCol);
if ((startRow > content.NbRows && endRow > content.NbRows) ||
(startCol > content.NbColumns && endCol > content.NbColumns))
{
// We need to remove the whole serie as it is out of scope
isSerieDeleted = true;
oneSerie.Remove();
break;
}
// ignore if mapped to a single cell of first row or first column
if ((endCol - startCol == 0 && endRow - startRow == 0) && (startCol == 1 || startRow == 1))
{
continue;
}
// otherwise this is mapped to a range: update
if (startCol == endCol && endRow != content.NbRows && startRow != endRow)
{
endRow = content.NbRows; // -startRow;
oneFormula.Value = WorksheetAccessorExt.SetFormula(sheetName, startRow, startCol, endRow, endCol);
}
if (startRow == endRow && endCol != content.NbColumns && startCol != endCol)
{
endCol = content.NbColumns; // -startRow;
oneFormula.Value = WorksheetAccessorExt.SetFormula(sheetName, startRow, startCol, endRow, endCol);
}
}
// ------------
// No need to go further, the serie is deleted (or will generate error "parent is missing")
if (isSerieDeleted)
{
continue;
}
// We now update Cached Value
IEnumerable <XElement> cachedBlock = oneSerie.Descendants(C.strRef).Union(oneSerie.Descendants(C.numRef));
foreach (XElement oneCache in cachedBlock)
{
var formula = oneCache.Descendants(C.f).FirstOrDefault();
var allCells = oneCache.Descendants(C.pt); //.ToList()
if (formula != null)
{
int startRow;
int startCol;
int endRow;
int endCol;
string sheetName;
WorksheetPart sheet = WorksheetAccessorExt.GetFormula
(spreadsheetDoc, formula.Value
, out sheetName, out startRow
, out startCol, out endRow
, out endCol
);
if (sheet != null)
{
int indexCell = 0;
XElement previousCell = null;
for (int ctRow = startRow; ctRow <= endRow; ctRow += 1)
{
for (int ctCol = startCol; ctCol <= endCol; ctCol += 1)
{
var cachedCell = allCells.FirstOrDefault(x => x.Attribute(NoNamespace.idx) != null &&
x.Attribute(NoNamespace.idx)?.Value == indexCell.ToString());
if (cachedCell == null && previousCell != null)
{
cachedCell = new XElement(previousCell);
cachedCell.Attribute(NoNamespace.idx).Value = indexCell.ToString();
previousCell.Parent?.Add(cachedCell);
}
if (ctRow > content.NbRows || ctCol > content.NbColumns)
{
indexCell += 1;
cachedCell.Remove();
continue;
}
var cellValue = WorksheetAccessor.GetCellValue(spreadsheetDoc, sheet, ctCol, ctRow);
var cachedCellValue = cachedCell.Descendants(C.v).FirstOrDefault();
if (null != cachedCellValue && null != cellValue)
{
cachedCellValue.Value = cellValue.ToString();
}
else
{
LogHelper.Instance.LogWarn("No cell value in cached cell or no cell value in attached spreadsheet.");
}
indexCell += 1;
previousCell = cachedCell;
}
}
// --------------------
// We clean other IDX
var maxId = (endCol - startCol + 1) * (endRow - startRow + 1);
var outOfRangeCells =
allCells.Where(x => x.Attribute(NoNamespace.idx) != null &&
Convert.ToInt32(x.Attribute(NoNamespace.idx)?.Value) >= maxId).ToList();
var nbOutOfRangeCells = outOfRangeCells.Count;
for (int ctCellOut = 0; ctCellOut < nbOutOfRangeCells; ctCellOut += 1)
{
outOfRangeCells[ctCellOut].Remove();
}
// ---------------------
// Update number of elements ptCount
var ptCountCell = oneCache.Descendants(C.ptCount).FirstOrDefault();
if (ptCountCell?.Attribute(NoNamespace.val) != null)
{
ptCountCell.Attribute(NoNamespace.val).Value = allCells.Count().ToString();
}
}
else
{
LogHelper.Instance.LogWarn("Invalid sheet for cached data.");
}
}
else
{
LogHelper.Instance.LogWarn("No formula defining range of cached data.");
}
}
// Data Label Modification
if (content.GraphDataLabelText)
{
// TODO: Create elements if it does not exist
// As of today, the user needs to display Axis value, that will be changed by text by the following code
IEnumerable <XElement> dataLabels = oneSerie.Descendants(C.dLbl);
var tempLbl = dataLabels.FirstOrDefault();
if (tempLbl != null)
{
XElement parentNode = tempLbl.Parent;
// We copied new row if needed
int nbDataLabels = dataLabels.Count();
if (content.DataLabel.Count() > nbDataLabels)
{
var idx = nbDataLabels - 1;
var lastElement = dataLabels.ElementAt(idx);
while (content.DataLabel.Count() > nbDataLabels)
{
XElement newElement = new XElement(lastElement);
var idxValue = newElement.Descendants(C.idx).FirstOrDefault();
// We need to assign IDX at 0 so we're sure it is in the correct range, we change the value afterwards
if (idxValue?.Attribute(NoNamespace.val) != null)
{
idxValue.Attribute(NoNamespace.val).SetValue(0);
}
parentNode?.AddFirst(newElement);
nbDataLabels += 1;
}
}
// We remove DataLabel is there are too many
nbDataLabels = dataLabels.Count();
if (content.DataLabel.Count() < nbDataLabels)
{
while (content.DataLabel.Count() < nbDataLabels)
{
dataLabels.ElementAt(nbDataLabels - 1).Remove();
nbDataLabels -= 1;
}
}
}
// ----
int nbDlbl = dataLabels.Count();
for (int ctDlbl = 0; ctDlbl < nbDlbl; ctDlbl += 1)
{
var oneDataLbl = dataLabels.ElementAt(ctDlbl);
var idxTag = oneDataLbl.Descendants(C.idx).FirstOrDefault();
if (idxTag?.Attribute(NoNamespace.val) != null)
{
idxTag.Attribute(NoNamespace.val).SetValue(ctDlbl);
}
var indexVal = oneDataLbl.Descendants(C.idx).FirstOrDefault();
if (indexVal?.Attribute(NoNamespace.val) == null || "".Equals(indexVal.Attribute(NoNamespace.val).Value))
{
continue;
}
int idxDataLbl = Convert.ToInt32(indexVal.Attribute(NoNamespace.val).Value);
var textTag = oneDataLbl.Descendants(C.tx).FirstOrDefault();
var textValue = textTag?.Descendants(A.t).FirstOrDefault();
textValue?.SetValue(content.DataLabel.ElementAt(idxDataLbl));
}
}
#endregion Serie Treatment
}
}
else
{
LogHelper.Instance.LogWarn("No cached chart data found.");
}
chartPart.PutXDocument(chartDoc);
}
catch (Exception exception)
{
LogHelper.Instance.LogError("Unexpected exception thrown.", exception);
throw;
}
}
private static void ApplyContent(ReportData pClient, OpenXmlPackage pPackage, BlockItem pBlock, TableDefinition pContent, Dictionary <string, string> pOptions)
{
try
{
string chartId = null;
var phElem = GetElements(pBlock.OxpBlock, pClient.ReportType)?.ToList();
if (phElem == null || phElem.Count == 0)
{
LogHelper.Instance.LogError("No placeholder content found.");
return;
}
#region Get the block Id in document
var allElementInPlaceHolder = GetElementsInPlaceHolder(pClient, phElem);
XElement graphicElement = phElem.Descendants(A.graphic).FirstOrDefault() ?? phElem.FirstOrDefault(_ => A.graphic.Equals(_.Name));
if (null != graphicElement)
{
XElement chartElem = graphicElement.Descendants(C.chart).FirstOrDefault();
if (null != chartElem)
{
chartId = chartElem.Attribute(R.id)?.Value;
}
else
{
LogHelper.Instance.LogFatal("Graphic object present but not a graph.");
}
}
else
{
LogHelper.Instance.LogFatal("No graphic / chart object inside the block.");
}
if (null == chartId)
{
pBlock.XBlock.ReplaceWith(allElementInPlaceHolder); return;
}
#endregion Get the block Id in document
var chartPart = GetChartPart(pPackage, pBlock, chartId);
if (null != chartPart)
{
string spreadsheetId = GetSpreadsheetId(chartPart);
if (!string.IsNullOrWhiteSpace(spreadsheetId))
{
#region Associated content management
var embedPackage = (EmbeddedPackagePart)chartPart.GetPartById(spreadsheetId);
if (null != embedPackage)
{
using (var ms = new MemoryStream())
{
#region Set content in memory to work with
using (Stream str = embedPackage.GetStream())
{
StreamHelper.CopyStream(str, ms);
str.Close();
}
#endregion Set content in memory to work with
using (SpreadsheetDocument spreadsheetDoc = SpreadsheetDocument.Open(ms, true))
{
#region Associated Data File content Management
var ws = (OXS.Sheet)spreadsheetDoc.WorkbookPart.Workbook.Sheets.FirstOrDefault();
if (ws != null)
{
string sheetId = ws.Id;
var wsp = (WorksheetPart)spreadsheetDoc.WorkbookPart.GetPartById(sheetId);
XDocument shPart = wsp.GetXDocument();
XElement shData = shPart.Descendants(S.sheetData).FirstOrDefault();
#region Use of the data content (Only data, no titles)
if (null != shData)
{
IEnumerable <XElement> allRows = shData.Descendants(S.row); // => if ToList() cause some graph to fail to generate
int ctRow = 0;
int nbRows = allRows.Count();
// Cleaning Cells ======================================================================
// We clean row not having Cell information
int idxRow = 1;
var nbCorrectSeries = allRows.Where(x => x.Descendants(S.c).Any(y => y.Attribute(NoNamespace.r) != null &&
y.Attribute(NoNamespace.r)?.Value != ""))
.Max(x => x.Descendants(S.c).Count());
for (int ctn = 0; ctn < nbRows; ctn += 1)
{
var oneRow = allRows.ElementAt(ctn);//[ctn];
// DCO - 9/21/2012 - I added the condition that the count of CELL must be indeed equal of numCorrectSeries OR to the number of column
// It happens for graphs with 3 cells defined per row, but only two used (so no Value for third cell), when NbColumns was == 2
var isRowValid = ((oneRow.Descendants(S.c).Count() == nbCorrectSeries || oneRow.Descendants(S.c).Count() >= pContent.NbColumns) &&
(oneRow.Descendants(S.c).Descendants(S.v).Count() == oneRow.Descendants(S.c).Count() ||
oneRow.Descendants(S.c).Descendants(S.v).Count() >= pContent.NbColumns));
// We remove rows that are not defined in content
if (isRowValid == false || ctRow >= pContent.NbRows)
{
oneRow.Remove();
ctn -= 1;
nbRows -= 1;
continue;
}
var _xAttribute = oneRow.Attribute(NoNamespace.r);
if (_xAttribute != null)
{
_xAttribute.Value = idxRow.ToString();
}
idxRow += 1;
}
// ====================================================================================
// Copying new row ========================================================================
// We copied new row if needed and extrapolate formula
// Take cell with no t SharedString
nbRows = allRows.Count();
if (pContent.NbRows > nbRows)
{
// We need to detect the best ROW to copy
// Usually the first row is the header so it contains two cells with SharedString
// Case 1: For others rows, it will be two cells with value.
// Case 2: For other rows, it can be one cell with SharedString and one cell with value (in case of App Nane + value)
var oneSerieRow =
// Case 1
allRows.FirstOrDefault(x => x.Attribute(NoNamespace.r) != null &&
x.Attribute(NoNamespace.r)?.Value != "" && x.Descendants(S.c).Any() &&
x.Descendants(S.c).Attributes(NoNamespace.t).Any() == false)
?? // Case 2: One or several SharedString, but at least one Value (by using < content.NbColumns)
allRows.FirstOrDefault(x => x.Attribute(NoNamespace.r) != null &&
x.Attribute(NoNamespace.r)?.Value != "" && x.Descendants(S.c).Any() &&
x.Descendants(S.c).Attributes(NoNamespace.t).Count() < pContent.NbColumns)
?? // Case 3: Any row but the first (avoiding Header)
allRows.FirstOrDefault(x => x.Attribute(NoNamespace.r) != null &&
x.Attribute(NoNamespace.r)?.Value != "" &&
x.Attribute(NoNamespace.r)?.Value != "1" && x.Descendants(S.c).Any());
if (oneSerieRow != null)
{
var previousRowValue = Convert.ToInt32(oneSerieRow.Attribute(NoNamespace.r)?.Value);
while (nbRows < pContent.NbRows)
{
var newRow = new XElement(oneSerieRow);
var _xAttribute = newRow.Attribute(NoNamespace.r);
if (_xAttribute != null)
{
_xAttribute.Value = (nbRows + 1).ToString(); // DCO Correction, ROW ID starts at 1
}
var serieCells = newRow.Descendants(S.c);
foreach (var oneCell in serieCells)
{
if (oneCell.Attribute(NoNamespace.r) == null)
{
continue;
}
var previousFormula = oneCell.Attribute(NoNamespace.r)?.Value;
// We extrapolate
int indexRow;
int indexCol;
WorksheetAccessorExt.GetRowColumnValue(previousFormula, out indexRow, out indexCol);
int newRowValue = nbRows + 1 + (indexRow - previousRowValue);
var _attribute = oneCell.Attribute(NoNamespace.r);
if (_attribute != null)
{
_attribute.Value = string.Concat(WorksheetAccessor.GetColumnId(indexCol), newRowValue.ToString());
}
if (oneCell.Attributes(NoNamespace.t).Any() != true)
{
continue;
}
var vElement = oneCell.Descendants(S.v).FirstOrDefault();
if (vElement != null)
{
vElement.Value = WorksheetAccessorExt.AddSharedStringValue(spreadsheetDoc, "").ToString();
}
// ---
}
shData.Add(newRow);
nbRows += 1;
}
}
else
{
LogHelper.Instance.LogWarn("Adding Rows: Could not find a row without a SharedString element.");
}
}
//-----
// Define Sheet Dimension ================================================================
int minStartRow = -1;
int minStartCol = -1;
int maxEndRow = -1;
int maxEndCol = -1;
var entireScope = allRows.SelectMany(x => x.Descendants(S.c).Attributes(NoNamespace.r).Select(y => y.Value));
foreach (var oneFormula in entireScope)
{
var startRow = -1;
var endRow = -1;
var startCol = -1;
var endCol = -1;
WorksheetAccessorExt.GetFormulaCoord(oneFormula, out startRow, out startCol,
out endRow, out endCol);
if (minStartRow == -1 || startRow < minStartRow)
{
minStartRow = startRow;
}
if (minStartCol == -1 || startCol < minStartCol)
{
minStartCol = startCol;
}
if (maxEndRow == -1 || endRow > maxEndRow)
{
maxEndRow = endRow;
}
if (maxEndCol == -1 || endCol > maxEndCol)
{
maxEndCol = endCol;
}
}
XElement sheetDimension = shPart.Descendants(S.s + "dimension").FirstOrDefault();
if (sheetDimension?.Attribute(NoNamespace._ref) != null)
{
sheetDimension.Attribute(NoNamespace._ref)?.SetValue(WorksheetAccessorExt.SetFormula("", minStartRow, minStartCol, maxEndRow, maxEndCol, false));
}
// ====================================================================================
int contentEltCount = pContent.Data?.Count() ?? 0;
// Apply values =======================================================================
for (int ctn = 0; ctn < nbRows; ctn++)
{
var oneRow = allRows.ElementAt(ctn);
// TODO: We may have to correct the "spans" in: <row r="1" spans="1:3"
List <XElement> allCells = oneRow.Descendants(S.c).ToList();
var ctCell = 0;
int nbCells = allCells.Count;
for (int ctc = 0; ctc < nbCells; ctc++)
{
var oneCell = allCells[ctc];
// We remove cell if they are not defined as content columns
if (ctCell >= pContent.NbColumns)
{
LogHelper.Instance.LogWarn("More cells that defined content ");
if (null != oneCell.Parent)
{
oneCell.Remove();
}
ctc -= 1;
nbCells -= 1;
continue;
}
// We inject text
var targetText = ((ctRow * pContent.NbColumns + ctCell) < contentEltCount ?
pContent.Data?.ElementAt(ctRow * pContent.NbColumns + ctCell) :
string.Empty);
if (null != targetText && !"<KEEP>".Equals(targetText)) // Keep for managing UniversalGraph
{
var isSharedString = oneCell.Attribute(NoNamespace.t);
if (null != isSharedString && "s".Equals(isSharedString.Value))
{
if ("".Equals(targetText))
{
LogHelper.Instance.LogWarn("Target Text empty for Shared String, this can create abnormal behavior.");
}
var idx = Convert.ToInt32(oneCell.Value);
WorksheetAccessorExt.SetSharedStringValue(spreadsheetDoc, idx, targetText);
}
else
{
XElement cell = oneCell.Descendants(S.v).FirstOrDefault();
if (null != cell)
{
cell.Value = targetText;
}
else
{
LogHelper.Instance.LogWarn("No correct cell value found");
}
}
}
ctCell += 1;
}
ctRow += 1;
}
}
else
{
LogHelper.Instance.LogWarn("Embedded spreadsheet is not formatted correctly");
}
// ====================================================================================
#endregion Get and use of the data content (Only data, no titles)
// We modify Table Definition (defining scope of Graph)
foreach (TableDefinitionPart t in wsp.TableDefinitionParts)
{
t.Table.Reference = String.Concat(WorksheetAccessor.GetColumnId(1), 1, ":",
WorksheetAccessor.GetColumnId(pContent.NbColumns), pContent.NbRows);
// We reduce the scope TableColumn if needed
var columnCount = t.Table.TableColumns.Count;
for (int ctCol = 0; ctCol < columnCount; ctCol += 1)
{
var tabColumn = t.Table.TableColumns.ElementAt(ctCol);
if (ctCol >= pContent.NbColumns)
{
tabColumn.Remove();
ctCol -= 1;
columnCount -= 1; // DCO - 10/23/2012 - Correction when reducing scope to a column (count is corrected afterwards).
continue;
}
// We align column name with the correct Shared String
if (!string.IsNullOrEmpty(pContent.Data?.ElementAt(ctCol)) && ctCol < pContent.NbColumns && "<KEEP>" != pContent.Data.ElementAt(ctCol))
{
tabColumn.SetAttribute(new OpenXmlAttribute("", "name", "", pContent.Data.ElementAt(ctCol)));
}
}
// The Count attribute is not updated correctly, so we do the work for them :)
if (pContent.NbColumns < t.Table.TableColumns.Count)
{
t.Table.TableColumns.SetAttribute(new OpenXmlAttribute("", "count", "", pContent.NbColumns.ToString()));
}
}
// We save the XML content
wsp.PutXDocument(shPart);
}
// We update cached data in Word document
UpdateCachedValues(pClient, chartPart, spreadsheetDoc, pContent);
#endregion Associated Data File content Management
}
// Write the modified memory stream back
// into the embedded package part.
using (Stream s = embedPackage.GetStream())
{
ms.WriteTo(s);
s.SetLength(ms.Length);
}
}
}
else
{
LogHelper.Instance.LogWarn("No embedded excel file found.");
}
#endregion Associated content management
}
else
{
LogHelper.Instance.LogWarn("No spreadsheet identifier found.");
}
#region Additionnal parameters
if (null == pContent.GraphOptions || !pContent.GraphOptions.HasConfiguration)
{
return;
}
Chart chart = chartPart.ChartSpace.Descendants <Chart>().FirstOrDefault();
PlotArea p_c = chart?.PlotArea;
var primaryVerticalAxis = p_c?.Descendants <ValueAxis>().FirstOrDefault(_ => "valAx".Equals(_.LocalName));
if (pContent.GraphOptions.AxisConfiguration.VerticalAxisMinimal.HasValue)
{
if (primaryVerticalAxis != null)
{
primaryVerticalAxis.Scaling.MinAxisValue.Val = DoubleValue.FromDouble(pContent.GraphOptions.AxisConfiguration.VerticalAxisMinimal.Value);
}
}
if (!pContent.GraphOptions.AxisConfiguration.VerticalAxisMaximal.HasValue)
{
return;
}
// ReSharper disable once PossibleInvalidOperationException
if (primaryVerticalAxis != null)
{
primaryVerticalAxis.Scaling.MaxAxisValue.Val = DoubleValue.FromDouble(pContent.GraphOptions.AxisConfiguration.VerticalAxisMaximal.Value);
}
#endregion Additionnal parameters
}
}
catch (Exception exception)
{
LogHelper.Instance.LogError("Unexpected exception thrown.", exception);
throw;
}
}
