Use oledb to read Excel

This article mainly provides a class for reading an Excel file using oledb. If you do not write well, I hope you can get an axe.

 

View code Using System;
Using System. Collections. Generic;
Using System. text;

UsingSystem. Data;
UsingSystem. Data. oledb;
UsingSystem. IO;

Namespace Common. Excel
{
// Connection string description
// HDR = yes: Use the first row as the column name of the datatable. The data type of the column is determined based on the data of the column.
// HDR = No: All rows are used as data, all data types are string, and null values are empty strings ""
// IMEX = 0: Export mode. The Excel files opened in this mode can only be used for "writing" purposes.
// IMEX = 1: Import mode. The Excel files opened in this mode can only be used for "read" purposes.
// IMEX = 2: connection mode. The Excel files enabled in this mode can be used for both "read" and "write.

Public Static Class Oledbhandler
{
Public Static String [] Getsheetnames ( String Excelpath)
{
String Connectionstr = getconnectionstr (excelpath, True );
Return Getsheetnamesbyoledb (connectionstr );
}

Public static dataset exceltodataset ( string excelpath)
{< br> return exceltodataset (excelpath, true );
}

Public StaticDataset exceltodataset (StringExcelpath,BoolFirstrowasheader)
{
StringConnectionstr = getconnectionstr (excelpath, firstrowasheader );
String[] Sheetnames = getsheetnamesbyoledb (connectionstr );

Using (Dataset DS =New Dataset ())
{
Foreach ( String Sheetname In Sheetnames)
{
// When you filter hidden tables, the $ symbol is added to the table to read the table from oledb. For some sheet with formulas, oledb creates a hidden table without the $ symbol.
If (Sheetname. endswith ( " $ " ))
{
Datatable dt = exceltodatatablebyoledb (connectionstr, sheetname );
DS. Tables. Add (DT );
}
}

ReturnDS;
}
}

Public StaticDatatable exceltodatatable (StringExcelpath,StringSheetname)
{
ReturnExceltodatatable (excelpath, sheetname,True);
}

Public StaticDatatable exceltodatatable (StringExcelpath,StringSheetname,BoolFirstrowasheader)
{
StringConnectionstr = getconnectionstr (excelpath, firstrowasheader );
ReturnExceltodatatablebyoledb (connectionstr, sheetname );
}

Private Static Datatable exceltodatatablebyoledb ( String Connectionstr, String Sheetname)
{
Using (Datatable dt = New Datatable ())
{
Using (Oledbconnection conn = New Oledbconnection (connectionstr ))
{
Oledbdataadapter da = New Oledbdataadapter ( String . Format ( " Select * from [{0}] " , Sheetname), connectionstr );

Da. Fill (DT );
DT. tablename = sheetname;

ReturnDT;
}
}
}

Private Static String [] Getsheetnamesbyoledb ( String Connectionstr)
{
Using (Oledbconnection conn = New Oledbconnection (connectionstr ))
{
Conn. open ();
Datatable dt = conn. getoledbschematable (oledbschemaguid. Tables, Null );

String[] Sheetnames =New String[DT. Rows. Count];

for ( int I = 0 ; I {< br> sheetnames [I] = DT. rows [I] [ " table_name " ]. tostring ();
}

ReturnSheetnames;
}
}

Private Static StringGetconnectionstr (StringExcelpath,BoolFirstrowasheader)
{
StringSuffix = path. getextension (excelpath );

StringExcelversion;
StringProvider;

Switch (Suffix. tolower ())
{
Case " . Xls " :
Provider = " Microsoft. Jet. oledb.4.0 " ;
Excelversion = " Excel 8.0 " ;
Break ;
Case " . XLSX " :
Provider = " Microsoft. Ace. oledb.12.0 " ;
Excelversion =" Excel 12.0 " ;
Break ;
Default :
Throw New Notsupportedexception (String . Format ( " The file extension [{0}] is not supported. " , Suffix ));
}

If (Firstrowasheader)
{
Return String . Format ( " Provider = {0}; Data Source = {1}; extended properties = '{2}; HDR = yes; IMEX = 1' " , Provider, excelpath, excelversion );
}
Else
{
Return String . Format ( " Provider = {0}; Data Source = {1}; extended properties = '{2}; HDR = no; IMEX = 1' " , Provider, excelpath, excelversion );
}
}
}
}