Using the code is very easy. This was the primary concern when I was building this library. The primary or top level class is ExcelXmlWorkbook which contains multiple Worksheets. The library resides in Yogesh.Extensions.ExcelXml. The following example shows the various ways of adding cells in a Workbook right from creating a instance.
1: // Create a instance...
2: ExcelXmlWorkbook book = new ExcelXmlWorkbook();
3:
4: // Many such properties exist. Details can be found in the documentation
5: // The author of the document
6: book.Properties.Author = "Yogesh Jagota";
7:
8: // This returns the first worksheet.
9: // Note that we have not declared a instance of a new worksheet
10: // All the dirty work is done by the library.
11: Worksheet sheet = book[0];
12:
13: // Name is the name of the sheet. If not set, the default name
14: // style is "sheet" + sheet number, like sheet1, sheet2
15: sheet.Name = "AgewiseOutstanding";
16:
17: // More on this in documentation
18: sheet.FreezeTopRows = 3;
19:
20: // and this too...
21: sheet.PrintOptions.Orientation = PageOrientation.Landscape;
22: sheet.PrintOptions.SetMargins(0.5, 0.4, 0.5, 0.4);
23:
24: // This is the actual code which sets out the cell values
25: // Note again, that we don't declare any instance at all.
26: // All the work is done by the library.
27: // Index operator takes first value as column and second as row.
28: sheet[0, 0].Value = "Outstanding as on " + DateTime.Now;
29:
30: sheet[0, 1].Value = "Name of Party";
31: sheet[1, 1].Value = "RSM";
32: sheet[2, 1].Value = "ASM";
33: sheet[3, 1].Value = "0-30";
34: sheet[4, 1].Value = "31-60";
35: sheet[5, 1].Value = "61-90";
36: sheet[6, 1].Value = "91+";
37:
38: sheet[0, 2].Value = "M/s Stupid Paymaster";
39: sheet[1, 2].Value = "Mr. Nonsense";
40: sheet[2, 2].Value = "Mr. More Nonsense";
41: sheet[3, 2].Value = 0;
42: sheet[4, 2].Value = 5000;
43: sheet[5, 2].Value = 45000;
44: sheet[6, 2].Value = 0;
45:
46: sheet[0, 3].Value = "M/s Good Paymaster";
47: sheet[1, 3].Value = "Mr. Good RSM";
48: sheet[2, 3].Value = "Mr. Good ASM";
49: sheet[3, 3].Value = 32000;
50: sheet[4, 3].Value = 0;
51: sheet[5, 3].Value = 0;
52: sheet[6, 3].Value = 0;
53: sheet[7, 3].Value = sheet[6, 3];
54:
55: // no extension is added if not present
56: string outputFile = "Outstanding File.xml";
57:
58: book.Export(outputFile);
59:
Importing a File
To import a file, you can either supply a file name or a Stream object to the static ExcelXmlWorkbook.Import method which returns a ExcelXmlWorkbook instance loaded with the fine. If any error occurs, the function simply returns null and there is no way to find out what error occurred. So the preferred way of import is to pass a Stream because it gives you more control with error management.
Exporting the File
All the code is written to disk only when the Export function is called. Export must be supplied with either a Stream or a File name. If any error occurs, the function simply returns false and there is no way to find out what error occurred. So the preferred way of export is to pass a Stream because it gives you more control with error management.
Assigning Values to Cells
Notice the last assignment in the previous example:
1: sheet[7, 3].Value = sheet[6, 2];
Here we are actually assigning a cell to a cell. What will be the value of the cell you might wonder? The cell will not have a value at all. It will have a reference to the assigned cell, something like this when you will open the file in excel, =G3. It wont be a absolute reference, more on that later. So, We can assign these values to a cell.
string bool - All integar types i.e.
byte, sbyte, int, uint, long, ulong, float, double etc. Please note that decimal is not supported.
DateTime Cell Formula [More on this below]
Knowing the type of content a cell contains
Every Cell contains a ContentType readonly field which can be used to check what value type the cell contains. Available values are String, Number, Boolean, DateTime, Formula, UnresolvedValue.
Retrieving Values from Cells
A readonly property GetValue<T> returns the cell value converted to the type supplied. You can use ContentType with GetValue<T> to retrieve the exact value of a cell. GetValue<T> enables strict type checking when retrieving a cell value. Further, if the type supplied with GetValue<T> does not matches the type of the cell type, default(T) is returned instead. For example: if a cell's ContentType == ContentType.Numeric, the only way to retrieve value of the cell is to supply byte, sbyte, int, uint, long, ulong, float, double. If a cell's ContentType == ContentType.String, the only way to retrieve value of the cell is to supply string etc.
Various Ways of Accessing the Cells
There is no hard coded way of accessing a particular cell. There are numerous ways of doing so. For example, the fourth column of the second row in the last example can be set to a value of 1 by...
- Directly using the
ExcelXmlWorkbook class.
1: book[0][3, 1].Value = 1
- Using the
Worksheet class.
1: Worksheet sheet = book[0];
2: sheet[3, 1].Value = 1
- Using the
Row class.
1: Worksheet sheet = book[0];
2: Row row = sheet[1];
3: row[3].Value = 1
- Using the
Cell class.
1: Worksheet sheet = book[0];
2: Row row = sheet[1];
3: Cell cell = row[3];
4: cell.Value = 1
Note that we do not need to declare a instance of a new worksheet, row or cell. All the dirty work is done by the library. This style of coding opens many ways of accessing cells and rows.
Styles
All cells, rows, worksheets have styles which can be set individually. These are Font, Alignment, Interior, Border and DisplayFormat. More information can be found in the documentation about members of the style classes. Changing a worksheet style setting affects all cells in the worksheet. A row setting affects all child cells in the row and a single cell setting affects, well, that very cell. Example:
1: sheet[1, 3].Font.Bold = true;
All the functionality of a style is implemented in a class XmlStyle. You can create a instance to XmlStyle in your code and assign it to the Style property which is present in all cells, rows and worksheets. Example:
1: XmlStyle style = new XmlStyle();
2: style.Font.Bold = true;
3: sheet[1, 3].Style = style;
Ranges
The main reason of writing my own implementation was ranges, which I found missing or not having the powers which ranges should have. In this library, ranges are very powerful and extendible. Range have all the style elements found in cells, rows and worksheets. Example:
1: // This sets the text of cells 1-8 of row 3 to bold
2: Range range = new Range(sheet[0, 2], sheet[7, 2]);
3: range.Font.Bold = true;
Even this is valid code, although many might recommend of doing it the first way...
1: new Range(sheet[0, 2], sheet[7, 2]).Font.Bold = true;
Please note that ranges can not be assigned to a cell value. Assigning it will generate a empty cell. Range can contain a single cell or range of cells. In the above example, we are providing the constructor with the first cell and the last cell. Ranges always contain rectangular ranges just like in Excel.
Applying auto filter to ranges
To apply auto filter to a range, you only need to call the range's AutoFilter method and you are done. Example:
1: new Range(sheet[0, 1], sheet[6, 3]).AutoFilter();
Absolute and Non-absolute Ranges
By default, all ranges output a non-absolute reference. To set up a absolute reference, just set the Absolute property of the range to true.
1: Range range = new Range(sheet[0 ,2], sheet[7, 2]);
2: range.Font.Bold = true;
3: range.Absolute = true;
Functions
Now we come to the real use of ranges and their Absolute property: Adding functions. I think a function in my library can be easily understood by this example which uses the first example in this article.
1: sheet[7, 3].Value = new Formula("sum",
2: new Range(sheet[3, 3], sheet[6, 3]));
or
1: sheet[7, 3].Value = new Formula().Add("sum").StartGroup().Add(
2: new Range(sheet[3, 3], sheet[6, 3])).EndGroup();
When you will open this book in excel, the value of the cell will be =SUM(D4:G4).
Function Parameters
Here we have added one single parameter in the formula constructor. You can add as many parameters as you want using the Add function of the Formula class. Only two types of parameters are allowed though, string or Range. The string parameter type can be used to add any value and named ranges also (Read more about named ranges in documentation). Example:
1: Formula formula = new Formula().Add("sum").StartGroup();
2: formula.Add("D4").Operator(',');
3: // Object initializers just to fit the code in one line
4: // The library is compatible with both VS2005 and VS2008
5: formula.Add(new Range(sheet[4, 3]) { Absolute = true } ).Operator(',');
6: formula.Add(new Range(sheet[5, 3], Range(sheet[6, 3])).EndGroup();
7: sheet[7, 3].Value = formula;
When you will open this book in excel, the value of the cell will be =SUM(D4, $E$4, F4:G4).
Filtering Cells as Parameters by Checking Cell Value or Style
You can also filter all cells and auto add them to the parameter list of a formula by passing a parameter, i.e. a delegate which accepts Cell as its value and returns bool to both Formula constructor or Add. All the values accessors (i.e. Value, IntValue etc.) and cell style can be checked. Examples:
1: // Lets assume column 1,2,3,6 and 7 are bold...
2: XmlStyle style = new XmlStyle();
3: style.Font.Bold = true;
4: // VS2008 style
5: sheet[7, 3].Value = new Formula("sum",
6: new Range(sheet[0, 3], sheet[6, 3]), cell => cell.Style == style);
7: // or VS2005 style
8: sheet[7, 3].Value = new Formula("sum",
9: new Range(sheet[0, 3], sheet[6, 3]),
10: delegate (Cell cell)
11: { return cell.Style == style; } );
1: sheet[7, 3].Value = new Formula("sum",
2: new Range(sheet[0, 3], sheet[6, 3]),
3: cell =>
4: cell.GetValue<int>() > 10000 &&
5: cell.GetValue<int>() <= 50000);
In the first example of style, the value of the cell will be =SUM(A4:C4, F4:G4). Continuous ranges matching to true will be joined as one parameter, i.e. A4:C4 and not three parameters, i.e. A4,B4,C4.
Modifying imported excel xml files
Imported excel xml files can be modified directly via direct assignment just like new files. Further to this, there are many functions which allow Insertion, Deletion and Addition of...
-
One or multiple worksheets in books, eg.
InsertSheetBefore, InsertSheetAfter
-
One or multiple rows and columns in sheets eg.
InsertColumnAfter, InsertColumnsAfter, InsertRowBefore, InsertRowsBefore
-
One or multiple cells in rows eg.
InsertCellBefore, InsertCellsBefore
See the documentation for more on these functions.
Exporting a dataset to a ExcelXmlWorksheet
A static member, in ExcelXmlWorksheet, DataSetToWorkbook is provided which converts a dataset and returns a worksheet reference. All the tables are converted into different sheets of the workbook.
Usage:
1: ExcelXmlWorksheet sheet = ExcelXmlWorksheet.DataSetToWorkbook(sourceDataSet)
Cell Collection
Cell collection is a strongly typed List collection with full support for Linq. You can use the Add method to add Worksheet, Range, Row or Cell. You can add all cells or you can filter the cells using a predicate.