/// <summary>
/// Adds a given worksheet to the document
/// </summary>
/// <param name="worksheet">Worksheet document to add</param>
/// <returns>Worksheet part just added</returns>
public static WorksheetPart Add(SpreadsheetDocument doc, XDocument worksheet)
{
// Associates base content to a new worksheet part
WorkbookPart workbook = doc.WorkbookPart;
WorksheetPart worksheetPart = workbook.AddNewPart <WorksheetPart>();
worksheetPart.PutXDocument(worksheet);
// Associates the worksheet part to the workbook part
XDocument document = doc.WorkbookPart.GetXDocument();
int sheetId =
document.Root
.Element(ns + "sheets")
.Elements(ns + "sheet")
.Count() + 1;
int worksheetCount =
document.Root
.Element(ns + "sheets")
.Elements(ns + "sheet")
.Where(
t =>
t.Attribute("name").Value.StartsWith("sheet", StringComparison.OrdinalIgnoreCase)
)
.Count() + 1;
// Adds content to workbook document to reference worksheet document
document.Root
.Element(ns + "sheets")
.Add(
new XElement(ns + "sheet",
new XAttribute("name", string.Format("sheet{0}", worksheetCount)),
new XAttribute("sheetId", sheetId),
new XAttribute(relationshipsns + "id", workbook.GetIdOfPart(worksheetPart))
)
);
doc.WorkbookPart.PutXDocument();
return(worksheetPart);
}
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>
/// Method for adding a new table definition part
/// </summary>
/// <param name="worksheet">Worksheet to add the table to</param>
/// <param name="tableStyle">Style to be assigned to the table</param>
/// <param name="useHeaders">Set a header row</param>
/// <param name="fromColumn">Initial column for table</param>
/// <param name="toColumn">Final column for table</param>
/// <param name="fromRow">Intial row for table</param>
/// <param name="toRow">Final row for table</param>
public static OpenXmlPowerToolsDocument Add(SmlDocument doc, string worksheetName, string tableStyle, bool useHeaders, short fromColumn, short toColumn, int fromRow, int toRow)
{
using (OpenXmlMemoryStreamDocument streamDoc = new OpenXmlMemoryStreamDocument(doc))
{
using (SpreadsheetDocument document = streamDoc.GetSpreadsheetDocument())
{
//Getting the id for this table
int tableId = GetNextTableId(document);
//Set the table cell range
string tableRange = string.Format("{0}{1}:{2}{3}", WorksheetAccessor.GetColumnId(fromColumn),
fromRow,
WorksheetAccessor.GetColumnId(toColumn),
toRow);
//Creating a new id for the relationship between the table definition part and the worksheet
string tableRelationShipId = "rId" + Guid.NewGuid();
//Create a new table definition part
WorksheetPart worksheet = WorksheetAccessor.Get(document, worksheetName);
TableDefinitionPart table = worksheet.AddNewPart <TableDefinitionPart>(tableRelationShipId);
//string tableColumns = string.Empty;
XElement tableColumnsXElement = new XElement(ns + "tableColumns", new XAttribute("count", (toColumn - fromColumn) + 1));
//Get the name for table column elements from the first table row
string[] tableHeaders = GetTableHeaders(document, worksheet, fromRow, fromColumn, toColumn);
for (int i = 0; i <= (toColumn - fromColumn); i++)
{
//Create the markup for the SpreadsheetML table column elements
tableColumnsXElement.Add(
new XElement(ns + "tableColumn", new XAttribute("id", i + 1), new XAttribute("name", tableHeaders[i])));
}
XElement tableXElement =
new XElement(ns + "table",
new XAttribute("xmlns", ns), //default namespace
new XAttribute("id", tableId),
new XAttribute("name", "Table" + tableId.ToString()),
new XAttribute("displayName", "Table" + tableId.ToString()),
new XAttribute("ref", tableRange),
new XAttribute("totalsRowShown", "0"));
if (useHeaders)
{
tableXElement.Add(
new XElement(ns + "autoFilter", new XAttribute("ref", tableRange)));
}
tableXElement.Add(tableColumnsXElement);
tableXElement.Add(
new XElement(ns + "tableStyleInfo",
new XAttribute("name", tableStyle),
new XAttribute("showFirstColumn", "0"),
new XAttribute("showLastColumn", "0"),
new XAttribute("showRowStripes", "0"),
new XAttribute("showColumnStripes", "0")));
//Write the markup to the Table Definition Part Stream
XmlWriter tablePartStreamWriter = XmlWriter.Create(table.GetStream());
tableXElement.WriteTo(tablePartStreamWriter);
tablePartStreamWriter.Flush();
tablePartStreamWriter.Close();
//Create or modify the table parts definition at worksheet (for setting the relationship id with the new table)
XDocument worksheetMarkup = worksheet.GetXDocument();
//Look for the tableParts element at worksheet markup
XElement tablePartsElement = worksheetMarkup.Root.Element(ns + "tableParts");
if (tablePartsElement != null)
{
//tableParts elements does exist at worksheet markup
//increment the tableParts count attribute value
short tableCount = System.Convert.ToInt16(tablePartsElement.Attribute("count").Value);
tablePartsElement.SetAttributeValue("count", tableCount++.ToString());
}
else
{
//tableParts does not exist at worksheet markup
//create a new tableParts element
tablePartsElement = new XElement(ns + "tableParts",
new XAttribute(ns + "count", "1"));
worksheetMarkup.Root.Add(tablePartsElement);
}
//create the tablePart element
XElement tablePartEntryElement = new XElement(ns + "tablePart",
new XAttribute(relationshipns + "id", tableRelationShipId));
//add the new tablePart element to the worksheet tableParts element
tablePartsElement.Add(tablePartEntryElement);
worksheet.PutXDocument();
}
return(streamDoc.GetModifiedDocument());
}
}
