1, OleDB, look in to the code sample following:
public static String ReadIn ( string sheetName )
{
StringBuilder output = new StringBuilder( 512, 4096 );
StringWriter writer = new StringWriter( output );
OpenFileDialog dialog = new OpenFileDialog( );
dialog.Filter = @"Excel Files (*.xls)|*.xls|All Files (*.*)|*.*";
if ( ( dialog.ShowDialog( ) == DialogResult.OK ) )
{
string filename = dialog.FileName;
string connectionString;
string Query;
MessageBox.Show( filename );
connectionString = ( "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
+ ( filename + ( ";Extended Properties=" + ( '"' + ( "Excel 8.0;HDR=Yes;IMEX=1" + ( '"' + ";" ) ) ) ) ) );
Query = ( "SELECT * FROM " + sheetName );
Query = String.Format( "SELECT * FROM [{0}$]", sheetName );
try
{
OleDbConnection connection = new OleDbConnection( connectionString );
OleDbCommand command = new OleDbCommand( Query, connection );
OleDbDataReader reader;
connection.Open( );
reader = command.ExecuteReader( );
while ( reader.Read() )
{
writer.Write( reader.GetValue( 0 ).ToString( ) + " " );
writer.Write( reader.GetValue( 1 ).ToString( ) + " " );
writer.Write( reader.GetValue( 2 ).ToString( ) + " " );
writer.Write( reader.GetValue( 3 ).ToString( ) + " " );
writer.WriteLine( );
}
reader.Close( );
connection.Close( );
}
catch ( Exception ex )
{
writer.WriteLine( ex.Message );
}
return output.ToString( );
}
return @"";
}
{
StringBuilder output = new StringBuilder( 512, 4096 );
StringWriter writer = new StringWriter( output );
OpenFileDialog dialog = new OpenFileDialog( );
dialog.Filter = @"Excel Files (*.xls)|*.xls|All Files (*.*)|*.*";
if ( ( dialog.ShowDialog( ) == DialogResult.OK ) )
{
string filename = dialog.FileName;
string connectionString;
string Query;
MessageBox.Show( filename );
connectionString = ( "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
+ ( filename + ( ";Extended Properties=" + ( '"' + ( "Excel 8.0;HDR=Yes;IMEX=1" + ( '"' + ";" ) ) ) ) ) );
Query = ( "SELECT * FROM " + sheetName );
Query = String.Format( "SELECT * FROM [{0}$]", sheetName );
try
{
OleDbConnection connection = new OleDbConnection( connectionString );
OleDbCommand command = new OleDbCommand( Query, connection );
OleDbDataReader reader;
connection.Open( );
reader = command.ExecuteReader( );
while ( reader.Read() )
{
writer.Write( reader.GetValue( 0 ).ToString( ) + " " );
writer.Write( reader.GetValue( 1 ).ToString( ) + " " );
writer.Write( reader.GetValue( 2 ).ToString( ) + " " );
writer.Write( reader.GetValue( 3 ).ToString( ) + " " );
writer.WriteLine( );
}
reader.Close( );
connection.Close( );
}
catch ( Exception ex )
{
writer.WriteLine( ex.Message );
}
return output.ToString( );
}
return @"";
}
2, Using Microsoft.Office.Core.Excel.dll:
-
Add Microsoft.Office.Core.Excel.dll as a reference to your porject.
-
Start using the Excel functionality using the Microsoft.Office.Core.Excel namespace.
-
You can use the Workbook class to open the xls file and then find your WorkSheet and go to the different cells using Rows, Columns or Cells.