Created
February 11, 2018 03:04
-
-
Save hellojinjie/16f9f1031fe2a5c096a926ae9e64bb94 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| var connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\\ExcelWorkBook.xls;Extended Properties=Excel 8.0"; | |
| using (var excelConnection = new OleDbConnection(connectionString)) | |
| { | |
| // The excel file does not need to exist, opening the connection will create the | |
| // excel file for you | |
| excelConnection.Open(); | |
| // data is an object so it works with DBNull.Value | |
| object propertyOneValue = "cool!"; | |
| object propertyTwoValue = "testing"; | |
| var sqlText = "CREATE TABLE YourTableNameHere ([PropertyOne] VARCHAR(100), [PropertyTwo] VARCHAR(100))"; | |
| // Executing this command will create the worksheet inside of the workbook | |
| // the table name will be the new worksheet name | |
| using (var command = new OleDbCommand(sqlText, excelConnection)) { command.ExecuteNonQuery(); } | |
| // Add (insert) data to the worksheet | |
| var commandText = string.Format("Insert Into YourTableNameHere ([PropertyOne], [PropertyTwo]) Values ('{0}', '{1}')", propertyOneValue, propertyTwoValue); | |
| using (var command = new OleDbCommand(commandText, excelConnection)) | |
| { | |
| // We need to allow for nulls just like we would with | |
| // sql, if your data is null a DBNull.Value should be used | |
| // instead of null | |
| command.Parameters.AddWithValue("@PropertyOne", propertyOneValue ?? DBNull.Value); | |
| command.Parameters.AddWithValue("@PropertyTwo", propertyTwoValue ?? DBNull.Value); | |
| command.ExecuteNonQuery(); | |
| } | |
| } |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment