public static IEnumerable <ValidationErrorInfo> ValidateXml(OpenXmlPowerToolsDocument document)
{
if (document is WmlDocument)
{
using (OpenXmlMemoryStreamDocument streamDoc = new OpenXmlMemoryStreamDocument(document))
using (WordprocessingDocument doc = streamDoc.GetWordprocessingDocument())
{
OpenXmlValidator validator = new OpenXmlValidator();
return(validator.Validate(doc));
}
}
else if (document is SmlDocument)
{
using (OpenXmlMemoryStreamDocument streamDoc = new OpenXmlMemoryStreamDocument(document))
using (SpreadsheetDocument doc = streamDoc.GetSpreadsheetDocument())
{
OpenXmlValidator validator = new OpenXmlValidator();
return(validator.Validate(doc));
}
}
else if (document is PmlDocument)
{
using (OpenXmlMemoryStreamDocument streamDoc = new OpenXmlMemoryStreamDocument(document))
using (PresentationDocument doc = streamDoc.GetPresentationDocument())
{
OpenXmlValidator validator = new OpenXmlValidator();
return(validator.Validate(doc));
}
}
throw new PowerToolsDocumentException("Not an Open XML document.");
}
/// <summary>
/// Apply a cell style to a specific cell
/// </summary>
/// <param name="worksheet">worksheet containing the cell to be affected</param>
/// <param name="fromColumn">Starting Cell Column</param>
/// <param name="toColumn">Ending Cell Column</param>
/// <param name="fromRow">Starting Cell Row</param>
/// <param name="toRow">Ending Cell Row</param>
/// <param name="cellStyle">Cell Style</param>
public static OpenXmlPowerToolsDocument SetCellStyle(SmlDocument doc, string worksheetName, short fromColumn, short toColumn, int fromRow, int toRow, string cellStyle)
{
using (OpenXmlMemoryStreamDocument streamDoc = new OpenXmlMemoryStreamDocument(doc))
{
using (SpreadsheetDocument document = streamDoc.GetSpreadsheetDocument())
{
WorksheetPart worksheet = Get(document, worksheetName);
XDocument worksheetXDocument = XDocument.Load(new XmlTextReader(worksheet.GetStream()));
for (int row = fromRow; row <= toRow; row++)
{
for (short col = fromColumn; col <= toColumn; col++)
{
XElement cellXelement = GetCell(worksheetXDocument, col, row);
cellXelement.SetAttributeValue("s", SpreadSheetStyleAccessor.GetCellStyleIndex(document, cellStyle));
}
}
XmlWriter worksheetWriter = XmlTextWriter.Create(worksheet.GetStream(System.IO.FileMode.Create));
worksheetXDocument.WriteTo(worksheetWriter);
worksheetWriter.Flush();
worksheetWriter.Close();
}
return(streamDoc.GetModifiedDocument());
}
}
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");
}
}
/// <summary>
/// Set the width for a range of columns
/// </summary>
/// <param name="worksheet">Worksheet containing the columns to be affected</param>
/// <param name="fromColumn">Initial column to affect</param>
/// <param name="toColumn">Final column to affect</param>
/// <param name="width">Column width</param>
public static OpenXmlPowerToolsDocument SetColumnWidth(SmlDocument doc, string worksheetName, short fromColumn, short toColumn, int width)
{
using (OpenXmlMemoryStreamDocument streamDoc = new OpenXmlMemoryStreamDocument(doc))
{
using (SpreadsheetDocument document = streamDoc.GetSpreadsheetDocument())
{
WorksheetPart worksheet = Get(document, worksheetName);
//Get the worksheet markup
XDocument worksheetXDocument = XDocument.Load(new XmlTextReader(worksheet.GetStream()));
//Look for worksheet cols element
XElement colsXElement = worksheetXDocument.Root.Element(ns + "cols");
if (colsXElement == null)
{
//cols elements does not exist
//create a new one
colsXElement = new XElement(ns + "cols");
//create a new col element (for setting the width)
//the col element could span more than one column -span is controlled by min (initial column) and max (final column) attributes
colsXElement.Add(new XElement(ns + "col",
new XAttribute("min", fromColumn.ToString()),
new XAttribute("max", toColumn.ToString()),
new XAttribute("width", width.ToString()),
new XAttribute("customWidth", "1")));
//cols element must be added before worksheet sheetData element
worksheetXDocument.Root.Element(ns + "sheetData").AddBeforeSelf(colsXElement);
}
else
{
//look for a col element for the column range indicated for fromColumn and toColumn
XElement colXElement = colsXElement.Elements(ns + "col")
.Where(c => (System.Convert.ToInt32(c.Attribute("min").Value) == fromColumn) && (System.Convert.ToInt32(c.Attribute("max").Value) == toColumn)).FirstOrDefault();
if (colXElement != null)
{
//col element does exist
//change its width value
colXElement.SetAttributeValue("width", width);
}
else
{
//col element does not exist
//create a new one
colsXElement.Add(new XElement(ns + "col",
new XAttribute("min", fromColumn.ToString()),
new XAttribute("max", toColumn.ToString()),
new XAttribute("width", width.ToString()),
new XAttribute("customWidth", "1")));
}
}
//Update the worksheet part markup at worksheet part stream
XmlWriter worksheetWriter = XmlTextWriter.Create(worksheet.GetStream(System.IO.FileMode.Create));
worksheetXDocument.WriteTo(worksheetWriter);
worksheetWriter.Flush();
worksheetWriter.Close();
}
return(streamDoc.GetModifiedDocument());
}
}
public static IEnumerable <ValidationErrorInfo> GetOpenXmlValidationErrors(string fileName,
string officeVersion)
{
#if !NET35
FileFormatVersions fileFormatVersion = FileFormatVersions.Office2013;
#else
FileFormatVersions fileFormatVersion = FileFormatVersions.Office2010;
#endif
try
{
fileFormatVersion = (FileFormatVersions)Enum.Parse(fileFormatVersion.GetType(), officeVersion);
}
catch (Exception)
{
#if !NET35
fileFormatVersion = FileFormatVersions.Office2013;
#else
fileFormatVersion = FileFormatVersions.Office2010;
#endif
}
FileInfo fi = new FileInfo(fileName);
if (Util.IsWordprocessingML(fi.Extension))
{
WmlDocument wml = new WmlDocument(fileName);
using (OpenXmlMemoryStreamDocument streamDoc = new OpenXmlMemoryStreamDocument(wml))
using (WordprocessingDocument wDoc = streamDoc.GetWordprocessingDocument())
{
OpenXmlValidator validator = new OpenXmlValidator(fileFormatVersion);
var errors = validator.Validate(wDoc);
return(errors.ToList());
}
}
else if (Util.IsSpreadsheetML(fi.Extension))
{
SmlDocument Sml = new SmlDocument(fileName);
using (OpenXmlMemoryStreamDocument streamDoc = new OpenXmlMemoryStreamDocument(Sml))
using (SpreadsheetDocument wDoc = streamDoc.GetSpreadsheetDocument())
{
OpenXmlValidator validator = new OpenXmlValidator(fileFormatVersion);
var errors = validator.Validate(wDoc);
return(errors.ToList());
}
}
else if (Util.IsPresentationML(fi.Extension))
{
PmlDocument Pml = new PmlDocument(fileName);
using (OpenXmlMemoryStreamDocument streamDoc = new OpenXmlMemoryStreamDocument(Pml))
using (PresentationDocument wDoc = streamDoc.GetPresentationDocument())
{
OpenXmlValidator validator = new OpenXmlValidator(fileFormatVersion);
var errors = validator.Validate(wDoc);
return(errors.ToList());
}
}
return(Enumerable.Empty <ValidationErrorInfo>());
}
public static IEnumerable <ValidationErrorInfo> GetOpenXmlValidationErrors(string fileName,
string officeVersion)
{
FileFormatVersions fileFormatVersion;
if (!Enum.TryParse(officeVersion, out fileFormatVersion))
{
fileFormatVersion = FileFormatVersions.Office2013;
}
FileInfo fi = new FileInfo(fileName);
if (Util.IsWordprocessingML(fi.Extension))
{
WmlDocument wml = new WmlDocument(fileName);
using (OpenXmlMemoryStreamDocument streamDoc = new OpenXmlMemoryStreamDocument(wml))
using (WordprocessingDocument wDoc = streamDoc.GetWordprocessingDocument())
{
OpenXmlValidator validator = new OpenXmlValidator(fileFormatVersion);
var errors = validator.Validate(wDoc);
return(errors.ToList());
}
}
else if (Util.IsSpreadsheetML(fi.Extension))
{
SmlDocument Sml = new SmlDocument(fileName);
using (OpenXmlMemoryStreamDocument streamDoc = new OpenXmlMemoryStreamDocument(Sml))
using (SpreadsheetDocument wDoc = streamDoc.GetSpreadsheetDocument())
{
OpenXmlValidator validator = new OpenXmlValidator(fileFormatVersion);
var errors = validator.Validate(wDoc);
return(errors.ToList());
}
}
else if (Util.IsPresentationML(fi.Extension))
{
PmlDocument Pml = new PmlDocument(fileName);
using (OpenXmlMemoryStreamDocument streamDoc = new OpenXmlMemoryStreamDocument(Pml))
using (PresentationDocument wDoc = streamDoc.GetPresentationDocument())
{
OpenXmlValidator validator = new OpenXmlValidator(fileFormatVersion);
var errors = validator.Validate(wDoc);
return(errors.ToList());
}
}
return(Enumerable.Empty <ValidationErrorInfo>());
}
/// <summary>
/// Set the value for a specific cell
/// </summary>
/// <param name="worksheet">Worksheet part containing the cell to be affected</param>
/// <param name="fromColumn">Initial column for setting the value</param>
/// <param name="fromRow">Initial row for setting the value</param>
/// <param name="toColumn">Final column for setting the value</param>
/// <param name="toRow">Final row for setting the value</param>
/// <param name="value">Cell value</param>
public static OpenXmlPowerToolsDocument SetCellValue(SmlDocument doc, string worksheetName, int fromRow, int toRow, int fromColumn, int toColumn, string value)
{
using (OpenXmlMemoryStreamDocument streamDoc = new OpenXmlMemoryStreamDocument(doc))
{
using (SpreadsheetDocument document = streamDoc.GetSpreadsheetDocument())
{
WorksheetPart worksheet = Get(document, worksheetName);
XDocument worksheetXDocument = XDocument.Load(new XmlTextReader(worksheet.GetStream()));
for (int row = fromRow; row <= toRow; row++)
{
for (int col = fromColumn; col <= toColumn; col++)
{
AddValue(worksheetXDocument, row, col, value);
}
}
XmlWriter worksheetWriter = XmlTextWriter.Create(worksheet.GetStream(System.IO.FileMode.Create));
worksheetXDocument.WriteTo(worksheetWriter);
worksheetWriter.Flush();
worksheetWriter.Close();
}
return(streamDoc.GetModifiedDocument());
}
}
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");
}
}
public static IEnumerable<ValidationErrorInfo> GetOpenXmlValidationErrors(string fileName,
string officeVersion)
{
#if !NET35
FileFormatVersions fileFormatVersion = FileFormatVersions.Office2013;
#else
FileFormatVersions fileFormatVersion = FileFormatVersions.Office2010;
#endif
try
{
fileFormatVersion = (FileFormatVersions)Enum.Parse(fileFormatVersion.GetType(), officeVersion);
}
catch (Exception)
{
#if !NET35
fileFormatVersion = FileFormatVersions.Office2013;
#else
fileFormatVersion = FileFormatVersions.Office2010;
#endif
}
FileInfo fi = new FileInfo(fileName);
if (Util.IsWordprocessingML(fi.Extension))
{
WmlDocument wml = new WmlDocument(fileName);
using (OpenXmlMemoryStreamDocument streamDoc = new OpenXmlMemoryStreamDocument(wml))
using (WordprocessingDocument wDoc = streamDoc.GetWordprocessingDocument())
{
OpenXmlValidator validator = new OpenXmlValidator(fileFormatVersion);
var errors = validator.Validate(wDoc);
return errors.ToList();
}
}
else if (Util.IsSpreadsheetML(fi.Extension))
{
SmlDocument Sml = new SmlDocument(fileName);
using (OpenXmlMemoryStreamDocument streamDoc = new OpenXmlMemoryStreamDocument(Sml))
using (SpreadsheetDocument wDoc = streamDoc.GetSpreadsheetDocument())
{
OpenXmlValidator validator = new OpenXmlValidator(fileFormatVersion);
var errors = validator.Validate(wDoc);
return errors.ToList();
}
}
else if (Util.IsPresentationML(fi.Extension))
{
PmlDocument Pml = new PmlDocument(fileName);
using (OpenXmlMemoryStreamDocument streamDoc = new OpenXmlMemoryStreamDocument(Pml))
using (PresentationDocument wDoc = streamDoc.GetPresentationDocument())
{
OpenXmlValidator validator = new OpenXmlValidator(fileFormatVersion);
var errors = validator.Validate(wDoc);
return errors.ToList();
}
}
return Enumerable.Empty<ValidationErrorInfo>();
}
public static XElement GetXlsxMetrics(SmlDocument smlDoc, MetricsGetterSettings settings)
{
using (OpenXmlMemoryStreamDocument streamDoc = new OpenXmlMemoryStreamDocument(smlDoc))
{
using (SpreadsheetDocument sDoc = streamDoc.GetSpreadsheetDocument())
{
List<XElement> metrics = new List<XElement>();
bool valid = ValidateAgainstSpecificVersion(sDoc, metrics, DocumentFormat.OpenXml.FileFormatVersions.Office2007, H.SdkValidationError2007);
valid |= ValidateAgainstSpecificVersion(sDoc, metrics, DocumentFormat.OpenXml.FileFormatVersions.Office2010, H.SdkValidationError2010);
#if !NET35
valid |= ValidateAgainstSpecificVersion(sDoc, metrics, DocumentFormat.OpenXml.FileFormatVersions.Office2013, H.SdkValidationError2013);
#endif
return new XElement(H.Metrics,
new XAttribute(H.FileName, smlDoc.FileName),
new XAttribute(H.FileType, "SpreadsheetML"),
metrics,
GetTableInfoForWorkbook(sDoc, settings));
}
}
}
/// <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();
}
}
/// <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());
}
}
public static IEnumerable<ValidationErrorInfo> GetOpenXmlValidationErrors(string fileName,
string officeVersion)
{
FileFormatVersions fileFormatVersion;
if (!Enum.TryParse(officeVersion, out fileFormatVersion))
fileFormatVersion = FileFormatVersions.Office2013;
FileInfo fi = new FileInfo(fileName);
if (Util.IsWordprocessingML(fi.Extension))
{
WmlDocument wml = new WmlDocument(fileName);
using (OpenXmlMemoryStreamDocument streamDoc = new OpenXmlMemoryStreamDocument(wml))
using (WordprocessingDocument wDoc = streamDoc.GetWordprocessingDocument())
{
OpenXmlValidator validator = new OpenXmlValidator(fileFormatVersion);
var errors = validator.Validate(wDoc);
return errors.ToList();
}
}
else if (Util.IsSpreadsheetML(fi.Extension))
{
SmlDocument Sml = new SmlDocument(fileName);
using (OpenXmlMemoryStreamDocument streamDoc = new OpenXmlMemoryStreamDocument(Sml))
using (SpreadsheetDocument wDoc = streamDoc.GetSpreadsheetDocument())
{
OpenXmlValidator validator = new OpenXmlValidator(fileFormatVersion);
var errors = validator.Validate(wDoc);
return errors.ToList();
}
}
else if (Util.IsPresentationML(fi.Extension))
{
PmlDocument Pml = new PmlDocument(fileName);
using (OpenXmlMemoryStreamDocument streamDoc = new OpenXmlMemoryStreamDocument(Pml))
using (PresentationDocument wDoc = streamDoc.GetPresentationDocument())
{
OpenXmlValidator validator = new OpenXmlValidator(fileFormatVersion);
var errors = validator.Validate(wDoc);
return errors.ToList();
}
}
return Enumerable.Empty<ValidationErrorInfo>();
}
/// <summary>
/// Set the width for a range of columns
/// </summary>
/// <param name="worksheet">Worksheet containing the columns to be affected</param>
/// <param name="fromColumn">Initial column to affect</param>
/// <param name="toColumn">Final column to affect</param>
/// <param name="width">Column width</param>
public static OpenXmlPowerToolsDocument SetColumnWidth(SmlDocument doc, string worksheetName, short fromColumn, short toColumn, int width)
{
using (OpenXmlMemoryStreamDocument streamDoc = new OpenXmlMemoryStreamDocument(doc))
{
using (SpreadsheetDocument document = streamDoc.GetSpreadsheetDocument())
{
WorksheetPart worksheet = Get(document, worksheetName);
//Get the worksheet markup
XDocument worksheetXDocument = XDocument.Load(new XmlTextReader(worksheet.GetStream()));
//Look for worksheet cols element
XElement colsXElement = worksheetXDocument.Root.Element(ns + "cols");
if (colsXElement == null)
{
//cols elements does not exist
//create a new one
colsXElement = new XElement(ns + "cols");
//create a new col element (for setting the width)
//the col element could span more than one column -span is controlled by min (initial column) and max (final column) attributes
colsXElement.Add(new XElement(ns + "col",
new XAttribute("min", fromColumn.ToString()),
new XAttribute("max", toColumn.ToString()),
new XAttribute("width", width.ToString()),
new XAttribute("customWidth", "1")));
//cols element must be added before worksheet sheetData element
worksheetXDocument.Root.Element(ns + "sheetData").AddBeforeSelf(colsXElement);
}
else
{
//look for a col element for the column range indicated for fromColumn and toColumn
XElement colXElement = colsXElement.Elements(ns + "col")
.Where(c => (System.Convert.ToInt32(c.Attribute("min").Value) == fromColumn) && (System.Convert.ToInt32(c.Attribute("max").Value) == toColumn)).FirstOrDefault();
if (colXElement != null)
{
//col element does exist
//change its width value
colXElement.SetAttributeValue("width", width);
}
else
{
//col element does not exist
//create a new one
colsXElement.Add(new XElement(ns + "col",
new XAttribute("min", fromColumn.ToString()),
new XAttribute("max", toColumn.ToString()),
new XAttribute("width", width.ToString()),
new XAttribute("customWidth", "1")));
}
}
//Update the worksheet part markup at worksheet part stream
XmlWriter worksheetWriter = XmlTextWriter.Create(worksheet.GetStream(System.IO.FileMode.Create));
worksheetXDocument.WriteTo(worksheetWriter);
worksheetWriter.Flush();
worksheetWriter.Close();
}
return streamDoc.GetModifiedDocument();
}
}
/// <summary>
/// Set the value for a specific cell
/// </summary>
/// <param name="worksheet">Worksheet part containing the cell to be affected</param>
/// <param name="fromColumn">Initial column for setting the value</param>
/// <param name="fromRow">Initial row for setting the value</param>
/// <param name="toColumn">Final column for setting the value</param>
/// <param name="toRow">Final row for setting the value</param>
/// <param name="value">Cell value</param>
public static OpenXmlPowerToolsDocument SetCellValue(SmlDocument doc, string worksheetName, int fromRow, int toRow, int fromColumn, int toColumn, string value)
{
using (OpenXmlMemoryStreamDocument streamDoc = new OpenXmlMemoryStreamDocument(doc))
{
using (SpreadsheetDocument document = streamDoc.GetSpreadsheetDocument())
{
WorksheetPart worksheet = Get(document, worksheetName);
XDocument worksheetXDocument = XDocument.Load(new XmlTextReader(worksheet.GetStream()));
for (int row = fromRow; row <= toRow; row++)
{
for (int col = fromColumn; col <= toColumn; col++)
{
AddValue(worksheetXDocument, row, col, value);
}
}
XmlWriter worksheetWriter = XmlTextWriter.Create(worksheet.GetStream(System.IO.FileMode.Create));
worksheetXDocument.WriteTo(worksheetWriter);
worksheetWriter.Flush();
worksheetWriter.Close();
}
return streamDoc.GetModifiedDocument();
}
}
/// <summary>
/// Apply a cell style to a specific cell
/// </summary>
/// <param name="worksheet">worksheet containing the cell to be affected</param>
/// <param name="fromColumn">Starting Cell Column</param>
/// <param name="toColumn">Ending Cell Column</param>
/// <param name="fromRow">Starting Cell Row</param>
/// <param name="toRow">Ending Cell Row</param>
/// <param name="cellStyle">Cell Style</param>
public static OpenXmlPowerToolsDocument SetCellStyle(SmlDocument doc, string worksheetName, short fromColumn, short toColumn, int fromRow, int toRow, string cellStyle)
{
using (OpenXmlMemoryStreamDocument streamDoc = new OpenXmlMemoryStreamDocument(doc))
{
using (SpreadsheetDocument document = streamDoc.GetSpreadsheetDocument())
{
WorksheetPart worksheet = Get(document, worksheetName);
XDocument worksheetXDocument = XDocument.Load(new XmlTextReader(worksheet.GetStream()));
for (int row = fromRow; row <= toRow; row++)
{
for (short col = fromColumn; col <= toColumn; col++)
{
XElement cellXelement = GetCell(worksheetXDocument, col, row);
cellXelement.SetAttributeValue("s", SpreadSheetStyleAccessor.GetCellStyleIndex(document, cellStyle));
}
}
XmlWriter worksheetWriter = XmlTextWriter.Create(worksheet.GetStream(System.IO.FileMode.Create));
worksheetXDocument.WriteTo(worksheetWriter);
worksheetWriter.Flush();
worksheetWriter.Close();
}
return streamDoc.GetModifiedDocument();
}
}
