Search notes:
Creating a new Excel workbook and worksheet with OLE DB, SQL and C-Sharp
The following
C# example tries to demonstrate how it is possible to create a new
Excel workbook and worksheet with
OLE DB .
using System;
using System.Data.OleDb;
using System.IO;
public class CreateExcel {
static void Main() {
string excelFilePath = $@"{Directory.GetCurrentDirectory()}\created.xlsx";
if (File.Exists(excelFilePath)) {
Console.WriteLine($"{excelFilePath} exists, deleting it.");
File.Delete(excelFilePath);
}
string provider =
// "Microsoft.Jet.OLEDB.4.0"
"Microsoft.ACE.OLEDB.12.0";
Console.WriteLine($"Using provider {provider}");
string connectionString = $"Provider={provider};Data Source={excelFilePath};Extended Properties='Excel 12.0 Xml;HDR=Yes';";
using (OleDbConnection connection = new OleDbConnection(connectionString)) {
connection.Open();
execCommand(connection, "create table tab (id integer, num number, txt varchar)");
execCommand(connection, "insert into tab values(1, 5, 'five')");
execCommand(connection, "insert into tab values(2, 2, 'two' )");
execCommand(connection, "insert into tab values(3, 4, 'four')");
execCommand(connection, "insert into tab values(4, 9, 'nine')");
}
}
static void execCommand(OleDbConnection conn, string sqlText) {
using (OleDbCommand command = new OleDbCommand()) {
command.Connection = conn;
command.CommandText = sqlText;
command.ExecuteNonQuery();
}
}
}
When the program is executed, it creates a workbook with
one worksheet, named
tab (see
create table statement) that is filled with the values that were specified with the
insert into statement.
