Use C # To import and export data to Excel

  

1. Implementation Principle

Import/export data in MS Excel using C #]

Sometimes we may require to generate Excel file from our reports, read from excel files to import data etc. this can be achieved using Office Interop (Office Automation) assemblies, but Office Automation in Web servers, got some issues; (More details: http://support.microsoft.com/kb/257757 ). the alternative is using OleDb provider. you may need to add one more attribute to connection string to connect to the Excel file. and connection string will be

string connectionString = "Provider=Microsoft.Jet.OleDb.4.0; Data Source=C:\Sample.xls; Extended Properties=Excel 8.0;"

Please note the "Extended Properties" attribute. This attribute helps us to query the excel file.

Exporting Data from Data Table to Excel File.

string connectionString = "Provider=Microsoft.Jet.OleDb.4.0; Data Source=C:\Sample.xls; Extended Properties=Excel 8.0;"// Establish a connection to the data source.using(OleDbConnection Connection = new OleDbConnection(connectionString)){Connection.Open()//creating a new Sheet with name sample and three columns with Heading firstname, lastname and emailusing(OleDbCommand command = new OleDbCommand()){command.Connection = Connection;command.CommandText = "CREATE TABLE [Sample$](FirstName Char(255), LastName char(255), Email char(255))";command.ExecuteNonQuery();}//Adding records to the Sample Worksheetusing(OleDbCommand command = new OleDbCommand()){command.Connection = Connection;command.CommandText = "INSERT INTO TABLE [Sample$](FirstName,LastName,Email) VALUES('Anuraj','P','anuraj.p@example.com')";command.ExecuteNonQuery();command.CommandText = "INSERT INTO TABLE [Sample$](FirstName,LastName,Email) VALUES('sreekumar','vn','sreekumar.vn@example.com')";command.ExecuteNonQuery();}}

Import the Data from Excel

DataTable dt;string connectionString = "Provider=Microsoft.Jet.OleDb.4.0; Data Source=C:\Sample.xls; Extended Properties=Excel 8.0;"// Establish a connection to the data source.using(OleDbConnection Connection = new OleDbConnection(connectionString)){Connection.Open()//reading data from excel to Data Tableusing(OleDbCommand command = new OleDbCommand()){command.Connection = Connection;command.CommandText = "SELECT * FROM [Sample]";using(OleDbDataAdapter adapter =new OleDbDataAdapter()){adapter.SelectCommand = command;adapter.Fill(dt);}}}

 

 

 2. Program Design

Assembly class diagram

The above uses vs directly to survive. It does not conform to the UML class diagram specification, but the main class relationships have been outlined.

The core class is the ExcelFile class, which depends on HDRType, ExcelVersion, and AppConfigKey. AppConfig is the configuration management class.

Flexible component call methods:

1. Call it in static mode directly

// Call the ExcelFile component to perform the import operation. DataTable [] dtExcelDatas = ExcelFile. GetData (fileSavePath, excelVersion, HDRType. Yes, true); // call the ExcelFile component to perform the export operation. ExcelFile. SetData (exportData [0], tempFilePath + fileName, excelVersion, HDRType. Yes );

 

2. Call with object-oriented method

ExcelFile ef = new ExcelFile();ef.Version = ExcelVersion.Excel8;ef.HDR = HDRType.Yes;ef.DataSource = dt;ef.FilePath = "d:\\test.xls";ef.Import();

 

The Demo interface is as follows:

 3. main advantages of this component

1. Use Oledb to connect to Excel without relying on Com components.

2. Multi-page Sheet import is supported.

3. You have your own configuration file and can flexibly configure component parameters.

4. When the imported data exceeds the maximum allowed data volume per page, the automatic paging mechanism is enabled.

 

Program source CODE: CODE