There are many ways to export data into excel, need to analyze
following requirement before choosing any methods like:
- Volume of data,
- Dynamic behavior,
- Schedule export or Manual export
- Performance & Scalability
- Security
- Operating system, (32 or 64 bit etc.)
Based on above requirement you can decide best approach
for export. I would suggest create proof of concept and do your entire
requirement testing carefully before taking final decision.
1. Export data using Microsoft Office.Interop Library
Microsoft .NET provides
Interop library to create excel object and export data table or data grid view
into excel sheet, this object is very useful when you require designing fancy
excel sheet using all excel features; like: cell design, formula, header &
footer design etc.
Pros:
- Complete programming support
to Excel via C# - Integrated with to Visual
studio
Cons:
-You must have Excel installed
on your system for this code to run properly. -To use COM
interop, you must have administrator or Power User security permissions -It is little slow to handle
heavy data load and not enough to load large size of
dataset
.
2. Export data using OpenXML SDK 2.0
Open XML SDK
2.0 lets developers be more productive by providing design-time capabilities
such as IntelliSense support and a type-safe development experience. You
can download from Download SDK.
Pros:
-Doesn't
require Microsoft Office installed -Made by Microsoft , decent
MSDN documentation -Just one .Net dll require to
use in project -SDK comes with many tools
like diff, validator, etc. -The SDK is stable and it is
supported by Microsoft. -LINQ can be used to navigate
data of excel file. -It performs operations with
just a few lines of code.
Cons:
-It supports only the
documents created in Office 2007 or later (i.e. xlsx, docs etc) -It cannot render Office
functions. You can't do Excel calculations rendering on the server-side.
3. Export data in CSV file using StreamWtiter
If you requirement to just
export the data into plain excel file, single sheet, no column formatting
needed then you should go with CSV file creation instead of Excel. This
solution works very well with large size of data. Tested with 1 million rows
with file size around 2 GB.
Pros:
-No Extra DLL require -Faster than creating excel
file -Can handle large data size
Cons:
-Creates single excel sheet
only -No formatting support -Need to remove special char
like: “’,” (Comma)
|
Public string RemoveInvalidChar(string
input)
{
List<string> invalidItems = new
List<string>()
{
",",
"/r", System.Environment.NewLine
};
foreach (string invalidItem in
invalidItems)
{
input = input.Replace(invalidItem, "");
}
return
input;
}
private void btnExport_Click(object
sender, EventArgs e)
{
int cols;
//open file
StreamWriter
wr = new System.IO.StreamWriter(@"C:\PerformanceTest.csv", false, Encoding.UTF8);
//Create
datatable having huge rows and columns
DataTable
dt = GetDataTable();
//determine the
number of columns and write columns to file
cols = dt.Columns.Count;
for (int i = 0; i < cols; i++)
{
wr.Write(dt.Columns[i].ToString().ToUpper() + ",");
}
wr.WriteLine();
//write rows to
excel file
for (int i = 0; i < (dt.Rows.Count); i++)
{
for (int j = 0; j < cols; j++)
{
if
(dt.Rows[i][j] != null)
{
wr.Write(RemoveInvalidChar(dt.Rows[i][j].ToString())
+ ",");
}
else
{
wr.Write(",");
}
}
wr.WriteLine();
}
//close file
wr.Close();
}
}
|
4. Export data using OLEDB provider
The basic format for the Microsoft.Jet.OLEDB.4.0 provider
is: [32-bit SQL Server for Excel 2003]
Export:
|
INSERT INTO OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 'Excel
8.0;Database=c:\contact.xls;',
'SELECT *
FROM [Sheet1$]')
SELECT TOP 5 doc_id,doc_name FROM document
|
Import:
|
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel
8.0;Database=C:\excel-sql-server.xls',
[Sheet1$])
SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data
Source=C:\excel-sql-server.xls;Extended Properties=Excel 8.0')...[Sheet1$]
|
The
basic format for the Microsoft.ACE.OLEDB.12.0 provider is:[64-bit
SQL Server for any Excel files or 32-bit SQL Server for Excel
2007]
Export:
|
INSERT INTO OPENROWSET ('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=c:\contact.xls;',
'SELECT *
FROM [Sheet1$]')
SELECT TOP 5 doc_id,doc_name FROM document
|
Import:
|
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel
12.0;Database=C:\excel-sql-server.xlsx',
[Sheet1$])
SELECT * FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
'Data
Source=C:\excel-sql-server.xlsx;Extended Properties=Excel 12.0')...[Sheet1$]
|
Note:
-Microsoft.Jet.OLEDB.4.0
and Microsoft.ACE.OLEDB.12.0 has to be registered.
-Microsoft.ACE.OLEDB.12.0 provider
work with SQL Server x64 for any Excel version files.
How to registered OLEDB
|
#1: Manually register
those DLLs.
- Go to Start->Run
and type cmd
- this starts the
Command Prompt
- (also available from
Start->Programs->Accessories->Command Prompt)
- Type cd .. and press
return
- Type cd .. and press
return again (keep doing this until the prompt shows :\> )
- Now you need to go
to a special folder which might be c:\windows\system32 or it might be
c:\winnt\system32 or it might be c:\windows\sysWOW64
try typing each of
these e.g.
cd
c:\windows\sysWOW64
(if it says The
system cannot find the path specified, try the next one)
cd
c:\windows\system32
cd c:\winnt\system32
When one of those
doesn't cause an error, stop, you've found the correct folder
7. Now you need to
register the OLE DB 4.0 DLLs by typing these commands and pressing return
after each
- regsvr32
Msjetoledb40.dll
- regsvr32 Msjet40.dll
- regsvr32
Mswstr10.dll
- regsvr32
Msjter40.dll
- regsvr32
Msjint40.dll
#2: Open the project in Visual Studio then:
- From the solution
explorer right-click your project then click Properties
- Click the Build tab
- Change Platform
target from: Any CPU to x86
- Re-build your
solution
|
