Export datatable to XLS/CSV compatible with MS-Office and Open-Office

Download source files - 15.1 Kb

Sample Image - ExportXLSCSV.gif

Introduction

Most of the time we get a requirement from the client to export data into .XLS or .CSV format. These are the commonly used solutions by the developer:

Response.ContentType = ="application/vnd.ms-excel", this requires Excel to be properly installed on the client machine.
Now to save the cost, developer can generate the Excel file on the server, and later on download the generated file. But this still requires one license on the server and normally gives a performance problem as the Excel object is unmanaged.
Using XML/XSLT solution is available here, in which user can generate a file on server and download it without consuming Excel license on the server. Later, the user can modify the file if they have a full Excel version or view in case Excel viewer is installed.

But my client was slighly more conserned about the cost, they were using Open-Office and not the full version of Excel to save the license cost. Which leads to the development of this code library which makes XLS compatible with Open-Office and Excel without using any license.

Using this library code

This library is implemented in C# without using any unmanaged resource and it's very simple to use. Here are the examples:

To generate Excel:

string fileName = 
  System.Guid.NewGuid().ToString().Replace("-","") + ".xls";
(new ExportDemo.Libraries.ExcelConvertor()).Convert(
  GenerateDataTable(),Server.MapPath("."),fileName); 
Response.Redirect(fileName);

To generate CSV:

string fileName = 
  System.Guid.NewGuid().ToString().Replace("-","") + ".xls"; 
(new ExportDemo.Libraries.ExcelConvertor()).Convert(
  GenerateDataTable(),Server.MapPath("."),fileName); 
Response.Redirect(fileName);

Points of Interest

This component is as free as the .NET Framework library.

History

Initial version added on 29-June '05.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

About the Author

Vivek Singh - IN

Web Developer

India

Member

Article Top

Poor

Excellent

Add a reason or comment to your vote: x
Votes of 3 or less require a comment

Comments and Discussions

You must Sign In to use this message board. (secure sign-in)

Search this forum
	Profile popups Noise Layout Per page

My vote of 4

Morteza Karimian

19:30 4 Oct '11

because use of data table instead of gridview and do not use of COM components or third party DLL
Sign In·View Thread·Permalink

My vote of 5

delko

12:39 27 Sep '10

Great Vivek, excellent piece or code.
Sign In·View Thread·Permalink

VB Version

frank stuart browne

17:08 9 Jul '09

Thanks! I ended up converting the XLS part to VB.Net Smile | :)

See below

Imports Microsoft.VisualBasic
Imports System
Imports System.Data
Imports System.IO
Imports System.Text

Public Class ExcelConverter
'/// <summary>
'/// To generate excel file.
'/// </summary>
'/// <param name="oDataTable"></param>
'/// <param name="directoryPath"></param>
'/// <param name="fileName"></param>
'/// <returns></returns>

Public Function Convert(ByVal oDataTable As DataTable, ByVal directoryPath As String, ByVal fileName As String)
Dim fullpath As String = ""
If (directoryPath.Substring(directoryPath.Length - 1, 1) = "\" Or directoryPath.Substring(directoryPath.Length - 1, 1) = "/") Then
fullpath = directoryPath + fileName
Else
fullpath = directoryPath + "\" + fileName
End If

Dim SW As StreamWriter
SW = File.CreateText(fullpath)

Dim oStringBuilder As New StringBuilder()

'/********************************************************
'* Start, check for border width
'* ******************************************************/
Dim borderWidth As Integer = 0

If (_ShowExcelTableBorder) Then
borderWidth = 1
End If
'/********************************************************
'* End, Check for border width
'* ******************************************************/

'/********************************************************
'* Start, Check for bold heading
'* ******************************************************/

Dim boldTagStart As String = ""
Dim boldTagEnd As String = ""

If (_ExcelHeaderBold) Then
boldTagStart = "<B>"
boldTagEnd = "</B>"
End If

'/********************************************************
'* End,Check for bold heading
'* ******************************************************/

oStringBuilder.Append("<Table border=" + borderWidth.ToString + ">")

'/*******************************************************************
'* Start, Creating table header
'* *****************************************************************/

oStringBuilder.Append("<TR>")

For Each oDataColumn As DataColumn In oDataTable.Columns 'Check back here
oStringBuilder.Append("<TD>" + boldTagStart + oDataColumn.ColumnName + boldTagEnd + "</TD>")
Next

oStringBuilder.Append("</TR>")

'/*******************************************************************
'* End, Creating table header
'* *****************************************************************/

'/*******************************************************************
'* Start, Creating rows
'* *****************************************************************/

For Each oDataRow As DataRow In oDataTable.Rows

oStringBuilder.Append("<TR>")

For Each oDataColumn As DataColumn In oDataTable.Columns

If IsNumeric(oDataRow(oDataColumn.ColumnName)) Then
oStringBuilder.Append("<TD align=right>" + oDataRow(oDataColumn.ColumnName).ToString + "</TD>")
Else
oStringBuilder.Append("<TD>" + oDataRow(oDataColumn.ColumnName).ToString + "</TD>")
End If
Next
oStringBuilder.Append("</TR>")
Next

'/*******************************************************************
'* End, Creating rows
'* *****************************************************************/

oStringBuilder.Append("</Table>")
SW.WriteLine(oStringBuilder.ToString())
SW.Close()

Return fullpath

End Function

Private _ShowExcelTableBorder As Boolean = False

' '/// <summary>
' '/// To show or hide the excel table border
' '/// </summary>
Public Property ShowExcelTableBorder() As Boolean
Get
Return _ShowExcelTableBorder
End Get
Set(ByVal value As Boolean)
_ShowExcelTableBorder = value
End Set
End Property

Private _ExcelHeaderBold As Boolean = True

' '/// <summary>
' '/// To make header bold or normal
' '/// </summary>
Public Property ExcelHeaderBold() As Boolean
Get
Return _ExcelHeaderBold
End Get
Set(ByVal value As Boolean)
_ExcelHeaderBold = value
End Set
End Property

End Class

Re: VB Version

Member 4201835

19:13 26 Jan '10

Dear VS, this is very useful for me. i need to apply the following: * background color * font styles * row height is it possible to apply? if so can you please send me the code? thanks and regards, dhanasekaran. g chennai, india.
Sign In·View Thread·Permalink

Useful

padmanabhan_ntrjn

0:19 19 Nov '08

HI, This article is very useful and also simple........ Indians are always great..........Proud to be Indian....... Padmanabhan
Sign In·View Thread·Permalink

a bug

shaorang

19:44 11 Apr '08

Hello and thanks for your work, it has been very useful for me but I have found a little bug in ExcelConvertor.cs (I dont know if the same fail is present in CSVConvertor.cs because I havent used it). This is the fail: when I set ExcelHeaderBold I got an exception, i think is because ExcelHeaderBold = value; must be _ExcelHeaderBold = value; in the next function

public bool ExcelHeaderBold 

		{

			get

			{

				return _ExcelHeaderBold;

			}

			set

			{

				ExcelHeaderBold = value;

			}

		}

Great!

sraap1972

9:47 8 Mar '07

This was just what I was looking for. I am newer to the web and asp.net and was looking to save some data out as a CSV. Thanks! Scott
Sign In·View Thread·Permalink

ashvinidave

9:54 5 Jan '07

Can we use this library for our college report generation application project. we are developing the same in VB.NEt. and facing issues with xport in XLS. as we also don't have Microsoft Excel installed. we use Open Office and Excel Viewer. Thanks in advance.
Sign In·View Thread·Permalink

Re: Hi

Vivek Singh - IN

0:39 8 Jan '07

Please use it, for any purpose. Best Regards, VS
Sign In·View Thread·Permalink

Re: Hi

CikaPero

21:55 19 Sep '11

Hi,

you can try this Excel C# / VB.NET component. If you are not generating large Excel files, you can use it for free. It works without Excel Automation which makes it ideal for Excel ASP.NET applications:

Here is an Excel VB.NET sample how to export DataTable to browser:

Dim people As DataTable = DirectCast(Session("people"), DataTable)
' Create excel file.
Dim ef As New ExcelFile()
Dim ws As ExcelWorksheet = ef.Worksheets.Add("DataSheet")
ws.InsertDataTable(people, "A1", True)
 
Response.Clear()
 
' Stream file to browser, in required type.
Select Case Me.RadioButtonList1.SelectedValue
    Case "XLS"
        Response.ContentType = "application/vnd.ms-excel"
        Response.AddHeader("Content-Disposition", "attachment; filename="
             & "Report.xls")
        ef.SaveXls(Response.OutputStream)
        Exit Select
 
    Case "XLSX"
        Response.ContentType = "application/vnd.openxmlformats"
        Response.AddHeader("Content-Disposition", "attachment; filename="
             & "Report.xlsx")
        ' With XLSX it is a bit more complicated as MS Packaging API
        ' can't write directly to Response.OutputStream.
        ' Therefore we use temporary MemoryStream.
        Dim ms As New MemoryStream()
        ef.SaveXlsx(ms)
        ms.WriteTo(Response.OutputStream)
        Exit Select
End Select
 
Response.End()

Export Russian Character Data to CSV or XLS File

MSJ_PK

0:03 3 Aug '05

Hi,

My requirement is to export Russian Data stored in Oracle 9i Database to a CSV file.

When the below code is used to export data the Russian Characters are shown as Junk characters when the CSV file is opened in MSExcel. If the same file is opened in Notepad, the Russian Characters are displayed correctly.

Can you advice, how to export Russian Characters that can also be displayed correctly when viewed in Excel Sheet.

It will be highly appreciated.

With Regards,

/////////////////////////////////////////////////////////////////////////////
/////////////////////// PROGRAM CODE ////////////////////////////////////////
/////////////////////////////////////////////////////////////////////////////

String strFileName = "";
FileStream fs = null;
BufferedStream buff = null;
StreamWriter writer = null;
bool bExported = false;

try
{
SaveFileDialog saveFileDlg = new SaveFileDialog();
saveFileDlg.Filter = "CSV Format|*.csv";
saveFileDlg.Title = "Save File";
saveFileDlg.RestoreDirectory = true ;
saveFileDlg.ShowDialog();

if(saveFileDlg.FileName != "")
{
strFileName = saveFileDlg.FileName;
saveFileDlg.Dispose();
fs = new FileStream
(strFileName,FileMode.OpenOrCreate,FileAccess.ReadWrite);
buff = new BufferedStream(fs);
writer = new StreamWriter(buff);
writer.Write(strWriteData);
writer.Flush();
bExported = true;
writer.Close();
buff.Close();
fs.Close();
}
return bExported;
}
catch(Exception exp)
{
throw exp;
}
finally
{
writer = null;
buff = null;
fs = null;
}

Re: Export Russian Character Data to CSV or XLS File

Vivek Singh - IN

0:58 3 Aug '05

I think you are saving it in ANSI Encoding, try to save it in UNICODE Encoding. It should solve your purpose. Best Regards, VS
Sign In·View Thread·Permalink	4.40/5 (5 votes)

Re: Export Russian Character Data to CSV or XLS File

gg4237

1:36 13 Nov '09

Hi, If you are using .NET DataSet class to hold your data in app memory then you could export it to CSV with GemBox.Spreadsheet .NET Excel component. GemBox.Spreadsheet supports International characters (Unicode support) so you could check it out.
Sign In·View Thread·Permalink