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);
}
}
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");
}
}
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"));
}
}