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.
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.
- Complete programming support
to Excel via C#
- Integrated with to Visual studio
-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
- Integrated with to Visual studio
Cons:
-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
-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.
-It cannot render Office functions. You can't do Excel calculations rendering on the server-side.
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:
-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 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:
-Faster than creating excel file
-Can handle large data size
Cons:
-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.
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
#2: Open the project in Visual Studio then:
|
No comments:
Post a Comment