A Very Easy to Use Excel XML Import-Export Library

Latest versions [External links]

Introduction

Just like any other Excel XML export article/library writer, my quest started when I wanted to export a simple report to Excel. I knew that writing an Excel XML library is very easy, and I was sure I will find plenty of such libraries on the Internet. I did find many, but none of them were simple, yet intuitive, powerful, and implemented everything I needed. I could take ages to write a background component like an export library, but when using it, I wanted it to do my work fast and without fuss. The library should also be compatible with .NET 2.0/3.0/3.5.

So, I came with this library which is easy and fast to use, yet is very powerful to be considered to be one of the most powerful XML export libraries present. OK, I will count it as a shameless self promotion.

P.S.: The XML file format is only supported by Excel versions XP, 2003, and 2007. Previous versions, i.e., Excel 97 and Excel 2000 do not support this feature.

Features

There are a multitude of features which are present in the library. They are...

Full import and export of Excel XML files
Access cells from anywhere including ExcelXmlWorkbook, Worksheet, Row, and Cell
Full support for formulae and ranges
Imported files' formulae and ranges are parsed to Formula and Range instances
Multitude of Insert, Delete, and Add functions available for ExcelXmlWorkbook, Worksheet, Row, and Cell
Auto management of cell references in the book on any change made via any of the Insert, Delete, and Add functions
Full support for styles in Worksheet, Row, and Cell, and even Range
No instance declaration; all the dirty work is done by the library
Cell ContentType, which enables you to know what the cell actually contains
DataSet to ExcelXmlWorkbook conversion facility
Absolute and non-absolute ranges and Named ranges
Auto filter
Very fast as everything is done via XmlWriter
Ability to freeze rows or columns, or both
Almost all print options
Almost all style options
Many formatting options
Hidden columns and rows

Using the library

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.

// Create the instance
ExcelXmlWorkbook book = new ExcelXmlWorkbook();

// Many such properties exist. Details can be found in the documentation
book.Properties.Author = "Yogesh Jagota"; // The author of the document

// This returns the first worksheet.
// Note that we have not declared a instance of a new worksheet
// All the dirty work is done by the library.
Worksheet sheet = book[0];

// Name is the name of the sheet. If not set, the default name
// style is "sheet" + sheet number, like sheet1, sheet2
sheet.Name = "AgewiseOutstanding";

// More on this in documentation
sheet.FreezeTopRows = 3;

// and this too...
sheet.PrintOptions.Orientation = PageOrientation.Landscape;
sheet.PrintOptions.SetMargins(0.5, 0.4, 0.5, 0.4);

// This is the actual code which sets out the cell values
// Note again, that we don't declare any instance at all.
// All the work is done by the library.
// Index operator takes first value as column and second as row.
sheet[0, 0].Value = "Outstanding as on " + DateTime.Now;

sheet[0, 1].Value = "Name of Party";
sheet[1, 1].Value = "RSM";
sheet[2, 1].Value = "ASM";
sheet[3, 1].Value = "0-30";
sheet[4, 1].Value = "31-60";
sheet[5, 1].Value = "61-90";
sheet[6, 1].Value = "91+";

sheet[0, 2].Value = "M/s Stupid Paymaster";
sheet[1, 2].Value = "Mr. Nonsense";
sheet[2, 2].Value = "Mr. More Nonsense";
sheet[3, 2].Value = 0;
sheet[4, 2].Value = 5000;
sheet[5, 2].Value = 45000;
sheet[6, 2].Value = 0;

sheet[0, 3].Value = "M/s Good Paymaster";
sheet[1, 3].Value = "Mr. Good RSM";
sheet[2, 3].Value = "Mr. Good ASM";
sheet[3, 3].Value = 32000;
sheet[4, 3].Value = 0;
sheet[5, 3].Value = 0;
sheet[6, 3].Value = 0;
sheet[7, 3].Value = sheet[6, 3];

string outputFile = "Outstanding File.xml";
// no extension is added if not present

book.Export(outputFile);

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 file. If any error occurs, the function simply returns null, and there is no way to find out what error occurred. So, the preferred way to 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 to 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:

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 won't be an absolute reference, more on that later. We can assign these values to a cell:

string
bool
All integar types, i.e., byte, sbyte, int, uint, long, ulong, float, double, decimal.
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. The 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 match 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 the value of the cell is to supply a byte, sbyte, int, uint, long, ulong, float, or double. If a cell's ContentType == ContentType.String, the only way to retrieve the value of the cell is to supply a 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.

book[0][3, 1].Value = 1

Using the Worksheet class.

Worksheet sheet = book[0];
sheet[3, 1].Value = 1

Using the Row class.

Worksheet sheet = book[0];
Row row = sheet[1];
row[3].Value = 1

Using the Cell class.

Worksheet sheet = book[0];
Row row = sheet[1];
Cell cell = row[3];
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, and 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:

sheet[1, 3].Font.Bold = true;

All the functionality of a style is implemented in a class XmlStyle. You can create an instance to XmlStyle in your code and assign it to the Style property which is present in all the cells, rows, and worksheets. Example:

XmlStyle style = new XmlStyle();
style.Font.Bold = true;
sheet[1, 3].Style = style;

Ranges

The main reason for 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. Ranges have all the style elements found in cells, rows, and worksheets. Example:

// This sets the text of cells 1-8 of row 3 to bold
Range range = new Range(sheet[0, 2], sheet[7, 2]);
range.Font.Bold = true;

Even this is valid code, although many might recommend doing it the first way...

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 an empty cell. A range can contain a single cell or a 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:

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 an absolute reference, just set the Absolute property of the range to true.

Range range = new Range(sheet[0 ,2], sheet[7, 2]);
range.Font.Bold = true;
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.

sheet[7, 3].Value = FormulaHelper.Formula("sum", new Range(sheet[3, 3], sheet[6, 3]));

or:

sheet[7, 3].Value = new Formula().Add("sum").StartGroup().Add(
            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 a 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 the documentation). Example:

Formula formula = new Formula().Add("sum").StartGroup();

formula.Add("D4").Operator(',');

// Here I am using the object initializers just to fit the code in one line
// The library is compatible with both VS2005 and VS2008
formula.Add(new Range(sheet[4, 3]) { Absolute = true } ).Operator(',');

formula.Add(new Range(sheet[5, 3], Range(sheet[6, 3])).EndGroup();

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 the cell value or style

You can 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 the Formula constructor or Add. All the value accessors (i.e., Value, IntValue, etc.) and cell styles can be checked. Examples:

Example 1:

// Lets assume column 1,2,3,6 and 7 are bold...
XmlStyle style = new XmlStyle();
style.Font.Bold = true;

// VS2008 style
sheet[7, 3].Value = FormulaHelper.Formula("sum", 
                    new Range(sheet[0, 3], sheet[6, 3]), 
                    cell => cell.Style == style);

// or VS2005 style
sheet[7, 3].Value = FormulaHelper.Formula("sum", 
                    new Range(sheet[0, 3], sheet[6, 3]), 
                    delegate (Cell cell) { return cell.Style == style; } );

Example 2:

sheet[7, 3].Value = FormulaHelper.Formula("sum", 
                    new Range(sheet[0, 3], sheet[6, 3]), 
                    cell => cell.GetValue<int>() > 10000 && 
                    cell.GetValue<int>() <= 50000);

In the first example of style, the value of the cell will be =SUM(A4:C4, F4:G4). Continuous range 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, e.g., InsertSheetBefore, InsertSheetAfter.
One or multiple rows and columns in sheets, e.g., InsertColumnAfter, InsertColumnsAfter, InsertRowBefore, InsertRowsBefore.
One or multiple cells in rows e.g., InsertCellBefore, InsertCellsBefore.

See the documentation for more on these functions.

Exporting a DataSet to an 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

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 a Worksheet, Range, Row, or Cell. You can add all cells, or you can filter the cells using a predicate.

Memory

Looking at all this code might make you think that all the cells, rows, Worksheets, ranges must be using too much memory. They must also be having their own separate copy of styles which will cause extra overhead. The answer is no.

I have optimized the library to use as little memory as possible. As far as the styles go, if you have a 100,000 cell Workbook written programmatically, which contains only 10 individual styles, the number of styles in memory will only be 11, i.e., 10 separate styles + 1 default style. Although the styles are added on a book level, if you have 10 books with the 10 same styles present in all of them, the number of style instances active in the program will be 110.

Conclusion

I will love to hear your comments and suggestions. Any bugs can be reported here.

Updates

Code breaking changes in v3.29

The SetHeaderFooterMargin method in PrintOptions has been removed. Use the HeaderMargin and FooterMargin properties instead.

Code breaking changes in v3.06

The formula system does not work the way it used to, so the previous code might break. For backwards compatibility, I have included a static class FormulaHelper which can be used with the previous code. Just replace the previous code in the following way:

cell.Value = new Formula("Sum", new Range(sheet[3, 3], sheet[6, 3]));

with:

cell.Value = FormulaHelper.Formula("Sum", new Range(sheet[3, 3], sheet[6, 3]));

History

[26 Nov 2008] Revision 3.35

Fixed an error with the GUID type storage.
Fixed an error with the format generation of ContentType.Time.

[19 Nov 2008] Revision 3.33

Added GUID cell content type.
Now GetValue<string> converts almost everything to string type including the newly added GUID cell content type.
Fixed SetHeader and SetFooter where empty strings caused issues.
Fixed error with the new IsRawContent property.

[12 Nov 2008] Revision 3.29

Added support for tab and comma delimited export.
Fixed error with Workbook's Add(string sheetName) method, which added two sheets instead of one.
Fixed error in Worksheet's ImportTable method where column index was using an one based index.
Fixed error where merged cells spanned across multiple lines causing a Table error in Excel.
Fixed error where DateTime values were saved in 12 hour format, but did not took AM/PM into account and always saved as AM.
Converted the HeaderMargin and FooterMargin fields to properties, and removed the SetHeaderFooterMargin method.
Added the Header and Footer string properties.
Added multipart header and footer string helpers, i.e., SetHeader and SetFooter.
Added support for print gridlines (EnableGridlines property).
Reset many internal values to private and cleaned up the project.
Added currency display format.
Fixed error where GetValue was not retrieving a decimal value.
Set the default display format of dates to GeneralDate.

[23 Jul 2008] Revision 3.06

New formula system implemented.
RowSpan and ColumnSpan properties added to cell.
Small error in named range rename fixed.
Freeze column won't work if freeze row is set. Fixed.
Style was not saved in Column export. Fixed.
Assembly file contained wrong information about this library. Fixed.
Workbook export can throw a NullReferenceException when new XmlWriter is created. Fixed.
Documented the IStyle interfaces.

[10 Jun 2008] Revision 2.89

Added Print Area support.
Added the TabColor property to Worksheet.
Added reference (HRef) support to cell.
Added support for custom display formats.
Added support for cell patterns.
Fixed error where "Long Date", "Short Date", "Time", and "@" as DisplayFormat caused an exception.
Fixed number format issue where some international formats were not saved properly.

[03 Apr 2008] Revision 2.82

Fixed single cell merge bug.

[19 Mar 2008] Revision 2.81

Fixed multi-Worksheet import bug.

[10 Mar 2008] Revision 2.80

Added decimal support.
Fixed an error where assigning 0 to a cell still caused an exception.

[06 Mar 2008] Revision 2.79

Added cell merge/unmerge support.
Added GetEnumerator support for sheets, rows, and ranges.
Added the CellCollection class.
Fixed an error where numeric output of the cell contained global number format where it should only be US only format. Thanks to Reinhard.
Added six new display format types and removed the Custom format type.
Added Index property to cell which also has a ExcelColumnIndex property which returns columns in Excel format, e.g., A, AA, AC, FA.

[28 Feb 2008] Revision 2.45

Fixed an error where GetValue<T> was not accepting string and DateTime types. Thanks to Karl for pointing this out.

[21 Feb 2008] Revision 2.44

Fixed a error where assigning 0 to a cell caused a exception. Thanks to Ralf for pointing this out.

[20 Feb 2008] Revision 2.43

Complete import support added.
Insertion, deletion, addition facilities added.
See this blog entry for the complete description of new features and changes.

[19 Jan 2008] Revision 1.30

Added complete documentation.
Added DataSet export capability.
Added range auto filter.
Added print row and column headers
Some name changes to remove warnings reported by FxCop. (ExcelXmlWorkBook to ExcelXmlWorkbook, WorkSheet to Worksheet, and CellCompareDelegate to CellCompare.
CellCompare is now in Yogesh.Extensions.ExcelXml and not in Yogesh.Extensions.ExcelXml.Formula.
Added cell comments.

[16 Jan 2008] Revision 1.0

First release on The Code Project.

You must Sign In to use this message board.


Per page

Source code for latest version
AndrusM

0:51 14 Mar '10

Where to find source code for latest version ? There are only dlls for 3.35 ? Andrus. Andrus
Sign In·View Thread·PermaLink

Extention attribute ...
BARMAT Ali

6:49 11 Mar '10

When I add the projects to my solution. building is ok . but when i make reference to th Dlls , I have the following error : Erreur 80 'ExtensionAttribute' is ambigus in namspace 'System.Runtime.CompilerServices'. InternalXmlHelper.vb
Sign In·View Thread·PermaLink

Bugs
webmonk12

9:22 11 Jan '10

The source code doesn't match the compiled DLL's. Note "Cell.IsRawContent". This is full of bugs.
Sign In·View Thread·PermaLink

Creating Formula with String parameter is not working... Please help URGENT!
robertraja13

19:08 14 Dec '09

Hi, I am having problem with formula. I am not able to set the formula to a cell using the string parameter. For e.g, sheet[7, 3].Value = FormulaHelper.Formula("=Sum(6,6)"); is not setting the value as 12 in the cell. Please help! Regards, Saravanan
Sign In·View Thread·PermaLink	1.00/5

Yogesh, what is happen with you
rzubi

6:03 6 Nov '09

I don't see here any comments or updates from you any more for a year almost. Everything ok with you? Good job you have done here.
Sign In·View Thread·PermaLink	2.00/5

Protecting (read-only) cells
Diodeus

10:09 8 Oct '09

This library has been a pleasure to use so far. Is there a way to make cells non-editable by the user?
Sign In·View Thread·PermaLink

Bug in ParseUnresolvedReference
marschills

23:55 7 Sep '09

You're doing the following

Match match;
ParseArgumentType pat = FormulaParser.GetArgumentType(UnresolvedRangeReference, out match);

Range range;

if (cell == null)
	throw new ArgumentNullException("cell");

bool parsed = FormulaParser.ParseRange(cell, match, out range, pat == ParseArgumentType.AbsoluteRange);

However the FormulaParser sets the Match object to null right at the end. (Incidently why are you passing a reference type by reference? Seems a very strange thing to do, the out modifier isn't needed).

The null Match then breaks ParseRange()
which is expecting a non-null Match object

if (match.Groups["File"].Success)

FormulaParser.cs

And the quickest fix:

// If a file name exists, we assume that the file is not
// the same one as this one, as neither excel nor this
// library saves filenames for same file. And as external 
// file references are not supported, return false...
if (match == null || match.Groups["File"].Success)
	return false;

Importing DateTime Cells
hp.ferrada

7:27 27 Aug '09

I think there is a problem with method:

ImportCellData(XmlReader reader, Cell cell)

case "DateTime":
{
DateTime date;
if (DateTime.TryParseExact(reader.Value, "yyyy-MM-dd\\Thh:mm:ss.fff",
CultureInfo.InvariantCulture, DateTimeStyles.None, out date))
{
cell.Value = date;
}
else
cell.Value = reader.Value;
break;
}

because fail with a date like "2009-05-16T16:35:00.000"
i changed to "yyyy-MM-dd\\THH:mm:ss.fff" and seems to work ok.

Cell width and height
Ronardo

14:36 4 Aug '09

Thanks for the awesome library. Is it possible to adjust these? Also does it support auto adjusting of widths? If not, do you have any plans to support these features if indeed these features are supported by Office XML? Thank you.
Sign In·View Thread·PermaLink	4.00/5

Excel with formula
Kamalnathrohit

6:33 4 Aug '09

Hi Yogesh,
Thanks for the helpful tool. Can you help me in writing some complex formulas. I need this as a core functionality in the excel that I am exporting using your tool.
The tool is excellent and very easy to use as told. I couldnt get much help in the help file for writing a formula. I could very well use the "add" formula provided in your documentation and have no idea how to write a different one.

Output file extension?
Bozjak

23:08 30 Jul '09

Hello! Very nice job, you created something really amazing! Thank you! I just have a question about the output file extension. Does it have to be .xml or is there an extension (that still works) that is opened by excel by default?
Sign In·View Thread·PermaLink

Re: Output file extension?
XChronos

6:27 25 Oct '09

You can put the xls extension so there's nothing different for the user.
Sign In·View Thread·PermaLink

Export workbook to dataset? (newbie)
kemaltaskin

3:21 24 Jun '09

Hi, this is a great work! Thank you! I have a question about it. I would like to use your library in order to help my users download the contents of a table, edit it via excel and upload it afterwards.
I generate a dataset from my database and export it to excel; using your library and let my users edit it.
Waht I need is to read that file again and convert it to a dataset again. Is there a way to do that in your library?

Thanks.

Re: Export workbook to dataset? (newbie)
gg4237

2:29 12 Nov '09

Hi! You could try GemBox.Spreadsheet .NET Excel component for reading and writing XLS, XLSX, CSV and HTML files. Free version has some restrictions, but you can use it freely in commercial products. Here is an example how to export/import DataSet to Excel file.
Sign In·View Thread·PermaLink	1.00/5

create empty cell
Radu Martin

0:31 25 May '09

This code doesn't create empty cell: `var excelRow = sheet.AddRow(); var _cell = excelRow.AddCell(); if (!row.IsCreatedTimeNull()) _cell.Value = row.CreatedTime;` If CreatedTime is null (row.IsCreatedTimeNull() == true), empty cell will not be stored in output Excel file. Can you fix it? thx
Sign In·View Thread·PermaLink

Apply border style on a cell
Nadege

1:05 20 Apr '09

Hi,

Thank you for your library.
I have a little question. I try to apply border style to a cell but I can't understand why it doesn't work.

The only styles that appear in the xml file was your 4 styles predefined.

This is my code snippet :

// Style Definition
XmlStyle styleBorder = new XmlStyle();
styleBorder.Border.Color = Color.Black;
styleBorder.Border.Weight = 1;
styleBorder.Border.LineStyle = Borderline.Continuous;

// Code trying to apply style
sheet[7, 9].Value = "Nadège DEROUSSEN";
sheet[7, 9].Style = styleBorder;

Thanks for your help.

Nadège

Re: Apply border style on a cell
Nadege

6:36 20 Apr '09

I found what's wrong in my code.

I have to indicate which sides I want borders. I modify my style definition like this :
// Style Definition
XmlStyle styleBorder = new XmlStyle();
styleBorder.Border.Color = Color.Black;
styleBorder.Border.Weight = 1;
styleBorder.Border.LineStyle = Borderline.Continuous;
styleBorder.Border.Sides = BorderSides.All;

and now, borders appear in my Excel file.

Hope it would help.

Thanks.

Nadège DEROUSSEN
http://nadege.deroussen.net

Insert Picture [modified]
Allam

5:19 6 Apr '09

Is there any way to insert a picture into a cell? Thanks... modified on Monday, April 6, 2009 1:48 PM
Sign In·View Thread·PermaLink	1.00/5

Re: Insert Picture
FilipKrnjic

6:59 9 Jul '09

Hi, Of course you can insert picture in cell. Just google a bit and i'm sure you'll find something about it. Also you can try using 3rd party component like GemBox spreadsheet component which is much better then Excel Automation. You can try free version (free even for commercial use) but it is limited to 150 rows. Filip
Sign In·View Thread·PermaLink	1.33/5

Excel Import
RASHU2010

10:44 15 Mar '09

Export a Gridview to excel

1.Write code for exporting to excel

public static void exportToExcel(DataSet source, string fileName)

{

System.IO.StreamWriter excelDoc;

excelDoc = new System.IO.StreamWriter(fileName);

const string startExcelXML = "<xml version>\r\n<Workbook " +

"xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\"\r\n" +

" xmlns Blush

=\"urn:schemas-microsoft-com:office:office\"\r\n " +

"xmlns:x=\"urn:schemas- microsoft-com:office:" +

"excel\"\r\n xmlns:ss=\"urn:schemas-microsoft-com:" +

"office:spreadsheet\">\r\n <Styles>\r\n " +

"<Style ss:ID=\"Default\" ss:Name=\"Normal\">\r\n " +

"<Alignment ss:Vertical=\"Bottom\"/>\r\n <Borders/>" +

"\r\n <Font/>\r\n <Interior/>\r\n <NumberFormat/>" +

"\r\n <Protection/>\r\n </Style>\r\n " +

"<Style ss:ID=\"BoldColumn\">\r\n <Font " +

"x:Family=\"Swiss\" ss:Bold=\"1\"/>\r\n </Style>\r\n " +

"<Style ss:ID=\"StringLiteral\">\r\n <NumberFormat" +

" ss:Format=\"@\"/>\r\n </Style>\r\n <Style " +

"ss:ID=\"Decimal\">\r\n <NumberFormat " +

"ss:Format=\"0.0000\"/>\r\n </Style>\r\n " +

"<Style ss:ID=\"Integer\">\r\n <NumberFormat " +

"ss:Format=\"0\"/>\r\n </Style>\r\n <Style " +

"ss:ID=\"DateLiteral\">\r\n <NumberFormat " +

"ss:Format=\"mm/dd/yyyy;@\"/>\r\n </Style>\r\n " +

"</Styles>\r\n ";

const string endExcelXML = "</Workbook>";

int rowCount = 0;

int sheetCount = 1;

/*

<xml version>

<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"

xmlns Blush

="urn:schemas-microsoft-com:office:office"

xmlns:x="urn:schemas-microsoft-com:office:excel"

xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">

<Styles>

<Style ss:ID="Default" ss:Name=" Normal ">

<Alignment ss:Vertical="Bottom"/>

<Borders/>

<Font/>

<Interior/>

<NumberFormat/>

<Protection/>

</Style>

<Style ss:ID="BoldColumn">

<Font x:Family="Swiss" ss:Bold="1"/>

</Style>

<Style ss:ID="StringLiteral">

<NumberFormat ss:Format="@"/>

</Style>

<Style ss:ID="Decimal">

<NumberFormat ss:Format="0.0000"/>

</Style>

<Style ss:ID="Integer">

<NumberFormat ss:Format="0"/>

</Style>

<Style ss:ID="DateLiteral">

<NumberFormat ss:Format="mm/dd/yyyy;@"/>

</Style>

</Styles>

<Worksheet ss:Name="Sheet1">

</Worksheet>

</Workbook>

*/

excelDoc.Write(startExcelXML);

excelDoc.Write("<Worksheet ss:Name=\"Sheet" + sheetCount + "\">");

excelDoc.Write("<Table>");

excelDoc.Write("<Row>");

for (int x = 0; x < source.Tables["emp"].Columns.Count; x++)

{

excelDoc.Write("<Cell ss:StyleID=\"BoldColumn\"><Data ss:Type=\"String\">");

excelDoc.Write(source.Tables[0].Columns[x].ColumnName);

excelDoc.Write("</Data></Cell>");

}

excelDoc.Write("</Row>");

foreach (DataRow x in source.Tables[0].Rows)

{

rowCount++;

//if the number of rows is > 64000 create a new page to continue output

if (rowCount == 64000)

{

rowCount = 0;

sheetCount++;

excelDoc.Write("</Table>");

excelDoc.Write(" </Worksheet>");

excelDoc.Write("<Worksheet ss:Name=\"Sheet" + sheetCount + "\">");

excelDoc.Write("<Table>");

}

excelDoc.Write("<Row>"); //ID=" + rowCount + "

for (int y = 0; y < source.Tables[0].Columns.Count; y++)

{

System.Type rowType;

rowType = x[y].GetType();

switch (rowType.ToString())

{

case "System.String":

string XMLstring = x[y].ToString();

XMLstring = XMLstring.Trim();

XMLstring = XMLstring.Replace("&", "&");

XMLstring = XMLstring.Replace(">", ">");

XMLstring = XMLstring.Replace("<", "<");

excelDoc.Write("<Cell ss:StyleID=\"StringLiteral\">" +

"<Data ss:Type=\"String\">");

excelDoc.Write(XMLstring);

excelDoc.Write("</Data></Cell>");

break;

case "System.DateTime":

//Excel has a specific Date Format of YYYY-MM-DD followed by

//the letter 'T' then hh:mm:sss.lll Example 2005-01-31T24:01:21.000

//The Following Code puts the date stored in XMLDate

//to the format above

DateTime XMLDate = (DateTime)x[y];

string XMLDatetoString = ""; //Excel Converted Date

XMLDatetoString = XMLDate.Year.ToString() +

"-" +

(XMLDate.Month < 10 ? "0" +

XMLDate.Month.ToString() : XMLDate.Month.ToString()) +

"-" +

(XMLDate.Day < 10 ? "0" +

XMLDate.Day.ToString() : XMLDate.Day.ToString()) +

"T" +

(XMLDate.Hour < 10 ? "0" +

XMLDate.Hour.ToString() : XMLDate.Hour.ToString()) +

":" +

(XMLDate.Minute < 10 ? "0" +

XMLDate.Minute.ToString() : XMLDate.Minute.ToString()) +

":" +

(XMLDate.Second < 10 ? "0" +

XMLDate.Second.ToString() : XMLDate.Second.ToString()) +

".000";

excelDoc.Write("<Cell ss:StyleID=\"DateLiteral\">" +

"<Data ss:Type=\"DateTime\">");

excelDoc.Write(XMLDatetoString);

excelDoc.Write("</Data></Cell>");

break;

case "System.Boolean":

excelDoc.Write("<Cell ss:StyleID=\"StringLiteral\">" +

"<Data ss:Type=\"String\">");

excelDoc.Write(x[y].ToString());

excelDoc.Write("</Data></Cell>");

break;

case "System.Int16":

case "System.Int32":

case "System.Int64":

case "System.Byte":

excelDoc.Write("<Cell ss:StyleID=\"Integer\">" +

"<Data ss:Type=\"Number\">");

excelDoc.Write(x[y].ToString());

excelDoc.Write("</Data></Cell>");

break;

case "System.Decimal":

case "System.Double":

excelDoc.Write("<Cell ss:StyleID=\"Decimal\">" +

"<Data ss:Type=\"Number\">");

excelDoc.Write(x[y].ToString());

excelDoc.Write("</Data></Cell>");

break;

case "System.DBNull":

excelDoc.Write("<Cell ss:StyleID=\"StringLiteral\">" +

"<Data ss:Type=\"String\">");

excelDoc.Write("");

excelDoc.Write("</Data></Cell>");

break;

default:

throw (new Exception(rowType.ToString() + " not handled."));

}

}

excelDoc.Write("</Row>");

}

excelDoc.Write("</Table>");

excelDoc.Write(" </Worksheet>");

excelDoc.Write(endExcelXML);

excelDoc.Close();

}

2. Add the following code to button click event

private void button2_Click(object sender, EventArgs e)

{

DataSet ds = testdbDataSet;

string filename = "text.xls";

exportToExcel(ds, filename);

}

How to read values from formula cells created in Excel 2003
FooBarNore

23:38 1 Mar '09

Very nice lib, I like to use it.

I encountered the following probem:
(1) Create a sheet in Excel 2003 that contains a formula
(2) Save as XML Speadsheet results in
...
<Row>
      <Cell><Data ss:Type="Number">2</Data></Cell>
      <Cell><Data ss:Type="Number">3</Data></Cell>
      <Cell ss:Formula="=RC[-2]+RC[-1]"><Data ss:Type="Number">5</Data></Cell>
</Row>
...
(3) Read sheet with lib version 3.35
-> the cell value 5 provided by Excel is not offered by Cell.Value.
Instead I receive null...

Is this a bug?
Any Workaround?

Thanx in advance!

<div class="ForumMod">modified on Monday, March 2, 2009 4:56 AM</div>

How to import xml file to a exist sheet?
JLKEngine008

18:48 20 Feb '09

It is very good!! How to import xml file to a exist sheet? It can export a worksheet to a xml file, but can you provide a example which can tell me how to import a xml file into .xls file , and how to display the xml file whith excel format in web forms? please send to me ,my e-mail is hy2001al@163.com, thanks!!
Sign In·View Thread·PermaLink	1.50/5

can you tell me how to import xml to sheet? [modified]
JLKEngine008

21:51 18 Feb '09

can you tell me how to import xml to sheet? can you give me a example to me? I want to display the exported xml file whith .xls in web form ? e-mail: hy2001al@163.com modified on Friday, February 20, 2009 11:48 PM
Sign In·View Thread·PermaLink	1.00/5

Really big problem with extensions
minorello

2:07 15 Feb '09

First of all, nice work! But after adding your extension library to my web apps nothing work. I spent a lot of time and I discover this: You have forced the lib to work with .net 2.0... http://spellcoder.com/blogs/dodyg/archive/2009/01/09/18172.aspx[^] Hope this helps. m-
Sign In·View Thread·PermaLink

Set the value and the formula in same cell
jingzo

23:30 9 Feb '09

Is it possible to set the value and formula in the same cell? Example: sheet[0,0].Value = 1; sheet[0,0].Value = FormulaHelper.Formula("sum", new Range(sheet[0,1], sheet[0,5])); Thanks.
Sign In·View Thread·PermaLink