Deep dive into OpenXML Part 1
Okay guys today I'm going to say about the Excel manipulation using the OpenXML. you can do various things using OpenXML and here I'm going to explain most important things you should know about OpenXML when comes to the Excel programming.
okay then let's start. First of all go to visual studio and create a console Application. To work with OpenXML you need to add several Dlls to your project. so after you create the project then go to project reference and add following dlls.
1. DocumentFormat.OpenXml. you can download this dll from here.
2. WindowsBase
2. Method to Add Basic Style to the worksheet
3. Method to Add a new Worksheet to workBook
4. Method to add Predefined Style
5. Methods to Add SharedStrings
6. Method to Get Column Name
7. Method to get Index of the Shared String
8. Method to set Column width
9. Method to Grouping cells / Make Outline
10. Method to Merge Cells
11. Method to set cell style
hmmm. now I added most of the common methods we want to use when we are working with OpenXML. so I'll explain common methods to set Cell value from the next article. if you have any clarification regarding this post please comment below. have a nice day and see you soon with the 2nd part of this post.
1. DocumentFormat.OpenXml. you can download this dll from here.
2. WindowsBase
okay now we are half there. now our project is ready to welcome the OpenXML. so let start coding and make some application.
so now we need to add a class called ExcelUtilities to put our common method which we are going to use every time. so let's go and create that class and add those common methods and don't forget to add these namespaces to the class.
1. using DocumentFormat.OpenXml;
2. using DocumentFormat.OpenXml.Packaging;
3. using DocumentFormat.OpenXml.Spreadsheet; and I'll explain what will happen from those methods on the go. Okay.
1. Method to create a WorkBook
so now we need to add a class called ExcelUtilities to put our common method which we are going to use every time. so let's go and create that class and add those common methods and don't forget to add these namespaces to the class.
1. using DocumentFormat.OpenXml;
2. using DocumentFormat.OpenXml.Packaging;
3. using DocumentFormat.OpenXml.Spreadsheet; and I'll explain what will happen from those methods on the go. Okay.
1. Method to create a WorkBook
/// <summary>
/// Creates the workbook
/// </summary>
/// <returns>Spreadsheet created</returns>
public static SpreadsheetDocument CreateWorkbook(string fileName)
{
SpreadsheetDocument spreadSheet = null;
// Create the Excel workbook
spreadSheet = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook, false);
// Create the parts and the corresponding objects
// Workbook
spreadSheet.AddWorkbookPart();
spreadSheet.WorkbookPart.Workbook = new Workbook();
spreadSheet.WorkbookPart.Workbook.Save();
// Shared string table
var sharedStringTablePart = spreadSheet.WorkbookPart.AddNewPart<SharedStringTablePart>();
sharedStringTablePart.SharedStringTable = new SharedStringTable();
sharedStringTablePart.SharedStringTable.Save();
// Sheets collection
spreadSheet.WorkbookPart.Workbook.Sheets = new Sheets();
spreadSheet.WorkbookPart.Workbook.Save();
// Style sheet
var workbookStylesPart = spreadSheet.WorkbookPart.AddNewPart<WorkbookStylesPart>();
workbookStylesPart.Stylesheet = new Stylesheet();
workbookStylesPart.Stylesheet.Save();
AddBasicStyles(spreadSheet);
return spreadSheet;
}
2. Method to Add Basic Style to the worksheet
/// <summary>
/// Adds the basic styles to the workbook
/// </summary>
/// <param name="spreadsheet">Spreadsheet to use</param>
/// <returns>True if successful</returns>
public static bool AddBasicStyles(SpreadsheetDocument spreadsheet)
{
Stylesheet stylesheet = spreadsheet.WorkbookPart.WorkbookStylesPart.Stylesheet;
// Numbering formats (x:numFmts)
stylesheet.InsertAt(new NumberingFormats(), 0);
// Currency
stylesheet.GetFirstChild<NumberingFormats>().InsertAt<NumberingFormat>(
new NumberingFormat
{
NumberFormatId = 164,
FormatCode = "#,##0.00" + "\\ \"" + CultureInfo.CurrentUICulture.NumberFormat.CurrencySymbol + "\""
},
0);
// Fonts (x:fonts)
stylesheet.InsertAt(new Fonts(), 1);
// Normal Font
stylesheet.GetFirstChild<Fonts>().InsertAt<Font>(
new Font
{
FontSize = new FontSize { Val = 11 },
FontName = new FontName { Val = "Calibri" }
}, 0);
// Header Bold Font
stylesheet.GetFirstChild<Fonts>().InsertAt<Font>(
new Font
{
FontSize = new FontSize { Val = 11 },
FontName = new FontName { Val = "Calibri" },
Bold = new Bold { Val = true }
},
1);
// Report Header Bold Font
stylesheet.GetFirstChild<Fonts>().InsertAt<Font>(
new Font
{
FontSize = new FontSize { Val = 14 },
FontName = new FontName { Val = "Calibri" },
Bold = new Bold { Val = true }
},
2);
// Fills (x:fills)
stylesheet.InsertAt(new Fills(), 2);
stylesheet.GetFirstChild<Fills>().InsertAt<Fill>(
new Fill
{
PatternFill =
new PatternFill
{
PatternType = new EnumValue<PatternValues> { Value = PatternValues.None },
BackgroundColor = new BackgroundColor { Rgb = HexBinaryValue.FromString("00FF00") },
ForegroundColor = new ForegroundColor { Rgb = HexBinaryValue.FromString("FFFF00") }
// red fill
}
},
0);
stylesheet.GetFirstChild<Fills>().InsertAt<Fill>(
new Fill
{
PatternFill =
new PatternFill
{
BackgroundColor = new BackgroundColor { Rgb = new HexBinaryValue("000000") },
ForegroundColor = new ForegroundColor { Rgb = HexBinaryValue.FromString("123456") }
// red fill
}
},
1);
// Borders (x:borders)
stylesheet.InsertAt(new Borders(), 3);
stylesheet.GetFirstChild<Borders>().InsertAt<Border>(
new Border
{
LeftBorder = new LeftBorder(),
RightBorder = new RightBorder(),
TopBorder = new TopBorder
{
Style = BorderStyleValues.Double,
Color = new Color { Rgb = HexBinaryValue.FromString("00FF00") }
},
BottomBorder = new BottomBorder(),
DiagonalBorder = new DiagonalBorder()
},
0);
// Cell style formats (x:CellStyleXfs)
stylesheet.InsertAt(new CellStyleFormats(), 4);
stylesheet.GetFirstChild<CellStyleFormats>().InsertAt<CellFormat>(
new CellFormat { NumberFormatId = 0, FontId = 0, FillId = 0, BorderId = 0 }, 0);
// Cell formats (x:CellXfs)
stylesheet.InsertAt(new CellFormats(), 5);
// General text
stylesheet.GetFirstChild<CellFormats>().InsertAt<CellFormat>(
new CellFormat
{
FormatId = 0,
NumberFormatId = 0
}, 0);
// Date
stylesheet.GetFirstChild<CellFormats>().InsertAt<CellFormat>(
new CellFormat
{
ApplyNumberFormat = true,
FormatId = 0,
NumberFormatId = 22,
FontId = 1,
FillId = 1,
BorderId = 0
},
1);
// Currency
stylesheet.GetFirstChild<CellFormats>().InsertAt<CellFormat>(
new CellFormat
{
ApplyNumberFormat = true,
FormatId = 0,
NumberFormatId = 164,
FontId = 0,
FillId = 0,
BorderId = 0
},
2);
// Cell Styles
stylesheet.GetFirstChild<CellFormats>().InsertAt<CellFormat>(
new CellFormat
{
FontId = 0 // Normal Font.
},
3);
stylesheet.GetFirstChild<CellFormats>().InsertAt<CellFormat>(
new CellFormat
{
FontId = 1,// Header Font.
Alignment = new Alignment { Horizontal = HorizontalAlignmentValues.Center, Vertical = VerticalAlignmentValues.Center }
},
4);
stylesheet.GetFirstChild<CellFormats>().InsertAt<CellFormat>(
new CellFormat
{
FontId = 2, // Report Title Font.
},
5);
stylesheet.Save();
return true;
}
3. Method to Add a new Worksheet to workBook
/// <summary>
/// Adds a new worksheet to the workbook
/// </summary>
/// <param name="spreadsheet">Spreadsheet to use</param>
/// <param name="name">Name of the worksheet</param>
/// <returns>True if successful</returns>
public static Worksheet AddWorksheet(SpreadsheetDocument spreadsheet, string name)
{
var sheets = spreadsheet.WorkbookPart.Workbook.GetFirstChild<Sheets>();
// Add the worksheet part
var worksheetPart = spreadsheet.WorkbookPart.AddNewPart<WorksheetPart>();
Worksheet worksheet = new Worksheet()
{
MCAttributes = new MarkupCompatibilityAttributes() { Ignorable = "x14ac" }
};
worksheet.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
worksheet.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
worksheet.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
SheetFormatProperties sheetFormatProperties = new SheetFormatProperties()
{
DefaultColumnWidth = 20,
DefaultRowHeight = 15D,
OutlineLevelRow = 1,
DyDescent = 0.25D
};
SheetProperties sheetProperties = new SheetProperties();
OutlineProperties outlineProp = new OutlineProperties()
{
SummaryBelow = false
};
sheetProperties.Append(outlineProp);
worksheet.Append(sheetProperties);
worksheet.Append(sheetFormatProperties);
worksheet.Append(new SheetData());
worksheetPart.Worksheet = worksheet;
worksheetPart.Worksheet.Save();
// Add the sheet and make relation to workbook
var sheet = new Sheet
{
Id = spreadsheet.WorkbookPart.GetIdOfPart(worksheetPart),
SheetId = (uint)(spreadsheet.WorkbookPart.Workbook.Sheets.Count() + 1),
Name = name
};
sheets.Append(sheet);
spreadsheet.WorkbookPart.Workbook.Save();
return worksheetPart.Worksheet;
}
4. Method to add Predefined Style
/// <summary>
/// Adds a predefined style from the given xml
/// </summary>
/// <param name="spreadsheet">Spreadsheet to use</param>
/// <param name="xml">Style definition as xml</param>
/// <returns>True if successful</returns>
public static bool AddPredefinedStyles(SpreadsheetDocument spreadsheet, string xml)
{
if (spreadsheet != null)
{
spreadsheet.WorkbookPart.WorkbookStylesPart.Stylesheet.InnerXml = xml;
spreadsheet.WorkbookPart.WorkbookStylesPart.Stylesheet.Save();
}
return true;
}
5. Methods to Add SharedStrings
/// <summary>
/// Add a single string to shared strings table.
/// Shared string table is created if it doesn't exist.
/// </summary>
/// <param name="spreadsheet">Spreadsheet to use</param>
/// <param name="value">string to add</param>
/// <param name="save">Save the shared string table</param>
/// <returns></returns>
public static bool AddSharedString(SpreadsheetDocument spreadsheet, string value, bool save)
{
SharedStringTable sharedStringTable = spreadsheet.WorkbookPart.SharedStringTablePart.SharedStringTable;
if (0 == sharedStringTable.Count(item => item.InnerText == value))
{
sharedStringTable.AppendChild(new SharedStringItem(new Text(value)));
// Save the changes
if (save)
{
sharedStringTable.Save();
}
}
return true;
}
/// <summary>
/// Adds a list of strings to the shared strings table.
/// </summary>
/// <param name="spreadsheet">The spreadsheet</param>
/// <param name="stringList">Strings to add</param>
/// <returns></returns>
public static bool AddSharedStrings(SpreadsheetDocument spreadsheet, IList<string> stringList)
{
foreach (string item in stringList)
{
AddSharedString(spreadsheet, item, false);
}
spreadsheet.WorkbookPart.SharedStringTablePart.SharedStringTable.Save();
return true;
}
6. Method to Get Column Name
/// <summary>
/// Converts a column number to column name (i.e. A, B, C..., AA, AB...)
/// </summary>
/// <param name="columnIndex">Index of the column</param>
/// <returns>Column name</returns>
public static string ColumnNameFromIndex(uint columnIndex)
{
StringBuilder columnName = new StringBuilder();
while (columnIndex > 0)
{
uint remainder = (columnIndex - 1) % 26;
columnName.Append(Convert.ToChar(65 + remainder).ToString(CultureInfo.InvariantCulture) + columnName);
columnIndex = (columnIndex - remainder) / 26;
}
return columnName.ToString();
}
7. Method to get Index of the Shared String
/// <summary>
/// Returns the index of a shared string.
/// </summary>
/// <param name="spreadsheet">Spreadsheet to use</param>
/// <param name="item">String to search for</param>
/// <returns>Index of a shared string. -1 if not found</returns>
public static int IndexOfSharedString(SpreadsheetDocument spreadsheet, string item)
{
SharedStringTable sharedStringTable = spreadsheet.WorkbookPart.SharedStringTablePart.SharedStringTable;
bool found = false;
int index = 0;
foreach (SharedStringItem sharedString in sharedStringTable.Elements<SharedStringItem>())
{
if (sharedString.InnerText == item)
{
found = true;
break;
}
index++;
}
return found ? index : -1;
}
8. Method to set Column width
/// <summary>
/// Sets the column width
/// </summary>
/// <param name="worksheet">Worksheet to use</param>
/// <param name="columnIndex">Index of the column</param>
/// <param name="width">Width to set</param>
/// <returns>True if successful</returns>
public static bool SetColumnWidth(Worksheet worksheet, int columnIndex, int width)
{
// Get the column collection exists
Columns columns = worksheet.Elements<Columns>().FirstOrDefault();
if (columns == null)
{
return false;
}
// Get the column
Column column = columns.Elements<Column>().FirstOrDefault(item => item.Min == columnIndex);
if (column == null)
{
return false;
}
column.Width = width;
column.CustomWidth = true;
worksheet.Save();
return true;
}
9. Method to Grouping cells / Make Outline
/// <summary>
/// Grouping Function
/// </summary>
/// <param name="worksheet"></param>
/// <param name="rowIndex"></param>
/// <param name="outlineSpan"></param>
/// <param name="hidden"></param>
/// <param name="outlineLevel"></param>
/// <param name="isTopRow"></param>
/// <returns></returns>
public static Row MakeOutlinedRow(Worksheet worksheet, uint rowIndex, string outlineSpan, bool hidden, uint outlineLevel, bool isTopRow)
{
// Get the sheet data
var sheetData = worksheet.GetFirstChild<SheetData>();
// Find the row to set outline details
Row row = sheetData.Elements<Row>().FirstOrDefault(item => item.RowIndex == rowIndex);
if (row != null)
{
row.Spans = new ListValue<StringValue> { InnerText = outlineSpan };
row.DyDescent = 0.25D;
if (!isTopRow)
{
row.OutlineLevel = (byte)outlineLevel;
}
row.Collapsed = true;
if (hidden)
{
row.Hidden = true;
}
}
return row;
}
10. Method to Merge Cells
/// <summary>
/// This method will merge two cells
/// </summary>
/// <param name="sheetName">Instance of the worksheet</param>
/// <param name="cell1Name">Cell 1 Name</param>
/// <param name="cell2Name">Cell 2 Name</param>
public static void MergeTwoCells(Worksheet sheetName, string cell1Name, string cell2Name)
{
// Open the document for editing.
Worksheet worksheet = sheetName;
if (worksheet == null || string.IsNullOrEmpty(cell1Name) || string.IsNullOrEmpty(cell2Name))
{
return;
}
// Verify if the specified cells exist, and if they do not exist, create them.
//CreateSpreadsheetCellIfNotExist(worksheet, cell1Name);
//CreateSpreadsheetCellIfNotExist(worksheet, cell2Name);
MergeCells mergeCells;
if (worksheet.Elements<MergeCells>().Any())
{
mergeCells = worksheet.Elements<MergeCells>().First();
}
else
{
mergeCells = new MergeCells();
// Insert a MergeCells object into the specified position.
if (worksheet.Elements<CustomSheetView>().Any())
{
worksheet.InsertAfter(mergeCells, worksheet.Elements<CustomSheetView>().First());
}
else if (worksheet.Elements<DataConsolidate>().Any())
{
worksheet.InsertAfter(mergeCells, worksheet.Elements<DataConsolidate>().First());
}
else if (worksheet.Elements<SortState>().Any())
{
worksheet.InsertAfter(mergeCells, worksheet.Elements<SortState>().First());
}
else if (worksheet.Elements<AutoFilter>().Any())
{
worksheet.InsertAfter(mergeCells, worksheet.Elements<AutoFilter>().First());
}
else if (worksheet.Elements<Scenarios>().Any())
{
worksheet.InsertAfter(mergeCells, worksheet.Elements<Scenarios>().First());
}
else if (worksheet.Elements<ProtectedRanges>().Any())
{
worksheet.InsertAfter(mergeCells, worksheet.Elements<ProtectedRanges>().First());
}
else if (worksheet.Elements<SheetProtection>().Any())
{
worksheet.InsertAfter(mergeCells, worksheet.Elements<SheetProtection>().First());
}
else if (worksheet.Elements<SheetCalculationProperties>().Any())
{
worksheet.InsertAfter(mergeCells, worksheet.Elements<SheetCalculationProperties>().First());
}
else
{
worksheet.InsertAfter(mergeCells, worksheet.Elements<SheetData>().First());
}
}
// Create the merged cell and append it to the MergeCells collection.
MergeCell mergeCell = new MergeCell() { Reference = new StringValue(cell1Name + ":" + cell2Name), };
mergeCells.Append(mergeCell);
worksheet.Save();
}
11. Method to set cell style
/// <summary>
/// Sets a specified cell style to a cell style defined
/// in the styles.xml
/// </summary>
/// <param name="worksheet">Excel Worksheet</param>
/// <param name="columnIndex">Column Index</param>
/// <param name="rowIndex">Row Index</param>
/// <param name="styleIndex">Style Index (From Styles.xml)</param>
/// <returns>False if an error occurred</returns>
public static bool SetCellStyle(Worksheet worksheet, uint columnIndex, uint rowIndex, uint styleIndex)
{
var sheetData = worksheet.GetFirstChild<SheetData>();
Row row = null;
Cell cell = null;
string cellAddress = ColumnNameFromIndex(columnIndex) + rowIndex;
// Check if the row exists, create if necessary
if (sheetData.Elements<Row>().Count(item => item.RowIndex == rowIndex) != 0)
{
row = sheetData.Elements<Row>().First(item => item.RowIndex == rowIndex);
}
if (row != null)
{
int cellCount = row.Elements<Cell>().Count(item => item.CellReference.Value == cellAddress);
// Check if the cell exists, create if necessary
if (cellCount > 0)
{
cell = row.Elements<Cell>().First(item => item.CellReference.Value == cellAddress);
}
else
{
return false;
}
}
if (cell != null)
{
cell.StyleIndex = styleIndex;
worksheet.Save();
}
else
{
return false;
}
return true;
}
hmmm. now I added most of the common methods we want to use when we are working with OpenXML. so I'll explain common methods to set Cell value from the next article. if you have any clarification regarding this post please comment below. have a nice day and see you soon with the 2nd part of this post.
Implementation for SetCellValue is not provided
ReplyDelete