How to create Excel file in C#
The following C# code example shows how to use COM interop to create
an Excel file. Before going to create new Excel file programmatically in
C#, you must have Excel installed on your system for this code to run
properly.
Excel Library
To access the object model from Visual C# .NET, you have to add the
Microsoft Excel 15.0 Object Library to you project.
Create a new project in your Visual Studio and add a Command Button to
your C# Form.
How to use COM Interop to Create an Excel Spreadsheet
Form the following pictures you can find how to add Excel reference
library in your project.
Select Add Reference dialogue from Project menu of your Visual Studio
Select Microsoft Excel 15.0 Object Library of COM leftside menu and click
OK button
How to create an Excel Document Programmatically
First we have to initialize the Excel application Object.
[Link] xlApp = new
[Link]();
Before creating new Excel Workbook, you should check whether Excel is
installed in your system.
if (xlApp == null)
{
[Link]("Excel is not properly installed!!");
return;
}
Then create new Workbook
xlWorkBook = [Link](misValue);
After creating the new Workbook, next step is to write content to
worksheet
xlWorkSheet = ([Link])[Link].get_Item(1);
[Link][1, 1] = "ID";
[Link][1, 2] = "Name";
[Link][2, 1] = "1";
[Link][2, 2] = "One";
[Link][3, 1] = "2";
[Link][3, 2] = "Two";
In the above code we write the data in the Sheet1, If you want to write
data in sheet 2 then you should code like this..
xlWorkSheet = ([Link])[Link].get_Item(2);
[Link][1, 1] = "Sheet 2 content";
Save Excel file (SaveAs() method)t
After write the content to the cell, next step is to save the excel file in
your system.
[Link]("[Link]");
How to properly clean up Excel interop
objects
Interop marshaling governs how data is passed in
method arguments and return values between managed
and unmanaged memory during calls. Most data types
have common representations in both managed and
unmanaged memory. The interop marshaler handles
these types for you. Other types can be ambiguous or
not represented at all in managed memory.
[Link] (excelWB);
[Link] (excelApp);
It is important to note that every reference to an Excel COM object had to
be set to null when you have finished with it, including Cells, Sheets,
everything.
The Marshal class is in the [Link] namespace,
so you should import the following namespace.
using [Link];
Creating an Excel Spreadsheet Programmatically
Copy and paste the following source code in your C# project file
using System;
using [Link];
using [Link];
using Excel = [Link];
namespace WindowsFormsApplication3
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
[Link] xlApp = new
[Link]();
if (xlApp == null)
{
[Link]("Excel is not properly installed!!");
return;
}
[Link] xlWorkBook;
[Link] xlWorkSheet;
object misValue = [Link];
xlWorkBook = [Link](misValue);
xlWorkSheet =
([Link])[Link].get_Item(1);
[Link][1, 1] = "ID";
[Link][1, 2] = "Name";
[Link][2, 1] = "1";
[Link][2, 2] = "One";
[Link][3, 1] = "2";
[Link][3, 2] = "Two";
[Link]("d:\\[Link]",
[Link], misValue, misValue, misValue,
misValue, [Link], misValue, misValue,
misValue, misValue, misValue);
[Link](true, misValue, misValue);
[Link]();
[Link](xlWorkSheet);
[Link](xlWorkBook);
[Link](xlApp);
[Link]("Excel file created , you can find the file
d:\\[Link]");
}
}
}
How to open an Excel file in C#
The following section explains how to open and read an Excel file through
C#.
For open or read an Excel file in C# , first you have to add the Microsoft
Excel 12.0 Object Library in you project.
Create a new project and add a Command Button to the Form.
Form the following images you can find how to add Excel reference library
in your project.
Select Reference Dialogue from Project menu
Select Microsoft Excel 12.0 Object Library and click OK button
Copy and paste the following source code in your C# project file.
using [Link];
using Excel = [Link];
namespace WindowsApplication1
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
[Link] xlApp ;
[Link] xlWorkBook ;
[Link] xlWorkSheet ;
object misValue = [Link];
xlApp = new [Link]();
xlWorkBook = [Link]("[Link]-
[Link]", 0, true, 5, "", "", true,
[Link], "\t", false, false, 0,
true, 1, 0);
xlWorkSheet =
([Link])[Link].get_Item(1);
[Link](xlWorkSheet.get_Range("A1","A1").[Link]());
[Link](true, misValue, misValue);
[Link]();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
}
private void releaseObject(object obj)
{
try
{
[Link](obj);
obj = null;
}
catch (Exception ex)
{
obj = null;
[Link]("Unable to release the Object " +
[Link]());
}
finally
{
[Link]();
}
}
}
}
How to read an Excel file using C#
The following program illustrates how to open an existing Excel
spreadsheet in C# using .NET Framework COM interop capability. Also you
can see how to find Named Ranges in Excel and get the range of occupied
cells (Used area) in excel sheet.
Excel Library
To access the object model from Visual C# .NET, you have to add the
Microsoft Excel 15.0 Object Library to you project.
Create a new project in your Visual Studio and add a Command Button to
your C# Form.
How to use COM Interop to Create an Excel Spreadsheet
Form the following pictures you can find how to add Excel reference
library in your project.
Select Add Reference dialogue from Project menu of your Visual Studio
Select Microsoft Excel 15.0 Object Library of COM leftside menu and click
OK button
After import the reference library, we have to initialize the Excel
application Object.
[Link] xlApp = new
[Link] xlWorkBook ;
[Link] xlWorkSheet ;
[Link] range ;
Next step is to open the Excel file and get the specified worksheet.
xlApp = new [Link]();
xlWorkBook = [Link](@"d:\[Link]", 0, true, 5, "",
"", true, [Link], "\t", false,
false, 0, true, 1, 0);
xlWorkSheet = ([Link])[Link].get_Item(1);
After get the selcted worksheet, next step is to specify the used range in
worksheet
How to specify a range in Excel sheet?
If you want to select a specific cell in Excel sheet, you can code like this.
[Link] excelSheet = [Link];
[Link] rng = ([Link])[Link][10, 10];
Reading Named Ranges in Excel
Worksheet.get_Range Method
If you want to select multiple cell value from Excel sheet, you can code
like this.
[Link] excelSheet = [Link];
[Link] rng = ([Link]) excelSheet.get_Range([Link][1,
1],
[Link][3,3]);
How to get the range of occupied cells in excel
sheet
For reading entire content of an Excel file in C#, we
have to know how many cells used in the Excel file. In
order to find the used range we use "UsedRange"
property of xlWorkSheet . A used range includes any cell
that has ever been used. It will return the last cell of
used area.
[Link] range ;
range = [Link];
How to properly clean up Excel interop objects
Interop marshaling governs how data is passed in method arguments and
return values between managed and unmanaged memory during calls.
Most data types have common representations in both managed and
unmanaged memory. The interop marshaler handles these types for you.
Other types can be ambiguous or not represented at all in managed
memory.
[Link] (excelWB);
[Link] (excelApp);
It is important to note that every reference to an Excel COM object had to
be set to null when you have finished with it, including Cells, Sheets,
everything.
The Marshal class is in the [Link] namespace,
so you should import the following namespace.
using [Link];
Open and Read an Excel Spreadsheet Programmatically
Copy and paste the following source code in your C# project file
using System;
using [Link];
using [Link];
using Excel = [Link];
namespace WindowsFormsApplication4
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
[Link] xlApp ;
[Link] xlWorkBook ;
[Link] xlWorkSheet ;
[Link] range ;
string str;
int rCnt ;
int cCnt ;
int rw = 0;
int cl = 0;
xlApp = new [Link]();
xlWorkBook = [Link](@"d:\[Link]", 0,
true, 5, "", "", true, [Link],
"\t", false, false, 0, true, 1, 0);
xlWorkSheet =
([Link])[Link].get_Item(1);
range = [Link];
rw = [Link];
cl = [Link];
for (rCnt = 1; rCnt < = rw; rCnt++)
{
for (cCnt = 1; cCnt < = cl; cCnt++)
{
str = (string)([Link][rCnt, cCnt] as
[Link]).Value2;
[Link](str);
}
}
[Link](true, null, null);
[Link]();
[Link](xlWorkSheet);
[Link](xlWorkBook);
[Link](xlApp);
}
}
Read and Import Excel File into DataSet
In the previous examples we used Microsoft Excel 12.0 Object Library
for read or write to and Excel file . In C# without using Excel Object we
can insert , edit , delete , select etc. in cell content of an Excel file using
OLEDB .
Read Excel Sheet Data into DataTable
Here we are using OleDbConnection , OleDbDataAdapter , DataSet
for doing these operations in an Excel file. You have to import
[Link] in the project for doing these operations . For read the
content from an Excel file using [Link] , We can use the SELECT
command like in SQL Operations.
sample Select sql
sql = "select * from [Sheet1$]"
Here is the sample Excel file .
Open the connection using OLEDB Provider
(provider=[Link].4.0;Data Source='Your
Filename';Extended Properties=Excel 8.0;)
Specify which data you want to read
select * from [Sheet1$]
Excel to Dataset
Here is the screen short after reading from Excel file in C# .
using System;
using [Link];
using [Link];
using Excel = [Link];
namespace WindowsApplication1
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
try
{
[Link] MyConnection ;
[Link] DtSet ;
[Link] MyCommand ;
MyConnection = new
[Link]("provider=[Link].4.0;Data
Source='c:\\[Link]';Extended Properties=Excel 8.0;");
MyCommand = new [Link]("select
* from [Sheet1$]", MyConnection);
[Link]("Table", "TestTable");
DtSet = new [Link]();
[Link](DtSet);
[Link] = [Link][0];
[Link]();
}
catch (Exception ex)
{
[Link] ([Link]());
}
}
}
}
How to insert data to Excel file using
OLEDB
In the previous examples we used Microsoft Excel 12.0 Object Library
for read or write to and Excel file . In C# without using Excel Object we
can insert , edit , delete , select etc. in cell content of an Excel file using
OLEDB .
You have to import [Link] in the project for doing these operations
. For add new content in the cell or insert a new content , We can use the
INSERT command like in SQL Operations.
sample UPDATE sql
sql = "Insert into [Sheet1$] (id,name) values('5','e')"
The following picture shows before and after update of the Sheet.
using System;
using [Link];
using [Link];
using Excel = [Link];
namespace WindowsApplication1
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
try
{
[Link] MyConnection ;
[Link] myCommand = new
[Link]();
string sql = null;
MyConnection = new
[Link]("provider=[Link].4.0;Data
Source='c:\\[Link]';Extended Properties=Excel 8.0;");
[Link]();
[Link] = MyConnection;
sql = "Insert into [Sheet1$] (id,name) values('5','e')";
[Link] = sql;
[Link]();
[Link]();
}
catch (Exception ex)
{
[Link] ([Link]());
}
}
}
}
Add new worksheet in Excel file
You can programmatically insert a worksheet and
then add that worksheet to the collection of
worksheets in the existing workbook. The following
program shows how to add a new worksheet to an
existing Excel file.
Excel Library
To access the object model from Visual C# .NET, you have to add the
Microsoft Excel 12.0 Object Library to you project. In the previous chapter
you can see a step by step instruction on how to add Excel library to your
project.
How to add Excel Library
programmatically Add New Worksheets to Workbooks
In order to add new worksheet to the excel file, this program open an
existing Excel file and add a new worksheet in the existing excel file.
var xlNewSheet = ([Link])[Link](worksheets[1],
[Link], [Link], [Link]);
[Link] = "newsheet";
[Link][1, 1] = "New sheet content";
Add Excel Worksheet without prompts
[Link] = false;
You can use the above code to disable Excel overwrite
promt. DisplayAlerts set to False for suppress prompts and
alert messages while a macro is running. When a message
need a response from the end user, Microsoft Excel chooses
the default response. After you complete the running
process, Microsoft Excel sets this property to True, unless
you are running cross-process code.
Programmatically Select Worksheets
You can Programmatically select Worksheet and set focus on
that worksheet when user open the Excel document.
xlNewSheet = ([Link])[Link].get_Item(2);
[Link]();
Above method shows how to select a specified worksheet, in this way you
can select any existing worksheet in an Excel document.
releaseObject()
Finally, we have to properly clean up Excel interop objects or
release Excel COM objects. Here using a function
releaseObject() to clean up the Excel object properly.
The following source code shows how to insert new worksheet in an excel
file
using System;
using [Link];
using Excel = [Link];
namespace WindowsFormsApplication1
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
[Link] xlApp = new
[Link]();
if (xlApp == null)
{
[Link]("Excel is not properly installed!!");
return;
}
[Link] = false;
string filePath = @"d:\[Link]";
[Link] xlWorkBook = [Link](filePath, 0,
false, 5, "", "", false,
[Link], "", true,false, 0,
true, false, false);
[Link] worksheets = [Link];
var xlNewSheet = ([Link])[Link](worksheets[1],
[Link], [Link], [Link]);
[Link] = "newsheet";
[Link][1, 1] = "New sheet content";
xlNewSheet =
([Link])[Link].get_Item(1);
[Link]();
[Link]();
[Link]();
releaseObject(xlNewSheet);
releaseObject(worksheets);
releaseObject(xlWorkBook);
releaseObject(xlApp);
[Link]("New Worksheet Created!");
}
private void releaseObject(object obj)
{
try
{
[Link](obj);
obj = null;
}
catch (Exception ex)
{
obj = null;
[Link]("Exception Occured while releasing object "
+ [Link]());
}
finally
{
[Link]();
}
}
}
}
How to update data in Excel file using
OLEDB
In the previous examples we used Microsoft Excel 12.0 Object Library
for read or write to and Excel file . In C# without using Excel Object we
can insert , edit , delete , select etc. in cell content of an Excel file using
OLEDB .
Here we are using OleDbConnection , OleDbDataAdapter , DataSet
for doing these operations in an Excel file. You have to import
[Link] in the project for doing these operations . For update the
content in the cell or modify the content in a cell , We can use the UPDATE
command like in SQL Operations.
sample UPDATE sql
sql = "Update [Sheet1$] set name = 'New Name' where id=1"
The following picture shows before and after update of the Sheet.
using System;
using [Link];
using [Link];
using Excel = [Link];
namespace WindowsApplication1
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
try
{
[Link] MyConnection ;
[Link] myCommand = new
[Link]();
string sql = null;
MyConnection = new
[Link]("provider=[Link].4.0;Data
Source='c:\\[Link]';Extended Properties=Excel 8.0;");
[Link]();
[Link] = MyConnection;
sql = "Update [Sheet1$] set name = 'New Name' where id=1";
[Link] = sql;
[Link]();
[Link]();
}
catch (Exception ex)
{
[Link] ([Link]());
}
}
}
}
How to export databse to excel file
The following C# program shows how to export database values to an
Excel file . First we load the data from database to a dataset and then
create a new Excel file and write the data to Excel file .
First step is to Load the Product table data to data set , for detail of
Product table please refer to Database Structure
Next is to create a new Excel file and write the data from dataset to Excel
file.
for (i = 0; i <= [Link][0].[Link] - 1; i++)
{
for (j = 0; j <= [Link][0].[Link] - 1; j++)
{
data = [Link][0].Rows[i].ItemArray[j].ToString();
[Link][i + 1, j + 1] = data;
}
}
using System;
using [Link];
using [Link];
using [Link];
using Excel = [Link];
namespace WindowsApplication1
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
SqlConnection cnn ;
string connectionString = null;
string sql = null;
string data = null;
int i = 0;
int j = 0;
[Link] xlApp ;
[Link] xlWorkBook ;
[Link] xlWorkSheet ;
object misValue = [Link];
xlApp = new [Link]();
xlWorkBook = [Link](misValue);
xlWorkSheet =
([Link])[Link].get_Item(1);
connectionString = "data source=servername;initial
catalog=databasename;user id=username;password=password;";
cnn = new SqlConnection(connectionString);
[Link]();
sql = "SELECT * FROM Product";
SqlDataAdapter dscmd = new SqlDataAdapter(sql, cnn);
DataSet ds = new DataSet();
[Link](ds);
for (i = 0; i <= [Link][0].[Link] - 1; i++)
{
for (j = 0; j <= [Link][0].[Link] - 1; j++)
{
data = [Link][0].Rows[i].ItemArray[j].ToString();
[Link][i + 1, j + 1] = data;
}
}
[Link]("[Link]",
[Link], misValue, misValue, misValue,
misValue, [Link], misValue, misValue,
misValue, misValue, misValue);
[Link](true, misValue, misValue);
[Link]();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
[Link]("Excel file created , you can find the file
c:\\[Link]");
}
private void releaseObject(object obj)
{
try
{
[Link](obj);
obj = null;
}
catch (Exception ex)
{
obj = null;
[Link]("Exception Occured while releasing object "
+ [Link]());
}
finally
{
[Link]();
}
}
}
}
How to export DataGridView to excel file
The following C# program demonstrate how to export data from
Datagridview to an Excel file.
The program first connect database and load data from database to
Datagridview and then create a new excel file and write the data from
Datagridview to Excel file .
First step is to Load the Product table data to DataGridView , for detail of
Product table please refer to Database Structure , and create new Excel
file and write the data from Datagridview to Excel file.
using System;
using [Link];
using [Link];
using [Link];
using Excel = [Link];
namespace WindowsApplication1
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
SqlConnection cnn ;
string connectionString = null;
string sql = null;
connectionString = "data source=servername;initial
catalog=databasename;user id=username;password=password;";
cnn = new SqlConnection(connectionString);
[Link]();
sql = "SELECT * FROM Product";
SqlDataAdapter dscmd = new SqlDataAdapter(sql, cnn);
DataSet ds = new DataSet();
[Link](ds);
[Link] = [Link][0];
}
private void button2_Click(object sender, EventArgs e)
{
[Link] xlApp ;
[Link] xlWorkBook ;
[Link] xlWorkSheet ;
object misValue = [Link];
xlApp = new [Link]();
xlWorkBook = [Link](misValue);
xlWorkSheet =
([Link])[Link].get_Item(1);
int i = 0;
int j = 0;
for (i = 0; i <= [Link] - 1; i++)
{
for (j = 0; j <= [Link] - 1; j++)
{
DataGridViewCell cell = dataGridView1[j, i];
[Link][i + 1, j + 1] = [Link];
}
}
[Link]("[Link]",
[Link], misValue, misValue, misValue,
misValue, [Link], misValue, misValue,
misValue, misValue, misValue);
[Link](true, misValue, misValue);
[Link]();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
[Link]("Excel file created , you can find the file
c:\\[Link]");
}
private void releaseObject(object obj)
{
try
{
[Link](obj);
obj = null;
}
catch (Exception ex)
{
obj = null;
[Link]("Exception Occured while releasing object "
+ [Link]());
}
finally
{
[Link]();
}
}
}
}
How to read an Excel file using C#
The following program illustrates how to open an existing Excel
spreadsheet in C# using .NET Framework COM interop capability. Also you
can see how to find Named Ranges in Excel and get the range of occupied
cells (Used area) in excel sheet.
Excel Library
To access the object model from Visual C# .NET, you have to add the
Microsoft Excel 15.0 Object Library to you project.
Create a new project in your Visual Studio and add a Command Button to
your C# Form.
How to use COM Interop to Create an Excel Spreadsheet
Form the following pictures you can find how to add Excel reference
library in your project.
Select Add Reference dialogue from Project menu of your Visual Studio
Select Microsoft Excel 15.0 Object Library of COM leftside menu and click
OK button
After import the reference library, we have to initialize the Excel
application Object.
[Link] xlApp = new
[Link] xlWorkBook ;
[Link] xlWorkSheet ;
[Link] range ;
Next step is to open the Excel file and get the specified worksheet.
xlApp = new [Link]();
xlWorkBook = [Link](@"d:\[Link]", 0, true, 5, "",
"", true, [Link], "\t", false,
false, 0, true, 1, 0);
xlWorkSheet = ([Link])[Link].get_Item(1);
After get the selcted worksheet, next step is to specify the used range in
worksheet
How to specify a range in Excel sheet?
If you want to select a specific cell in Excel sheet, you can code like this.
[Link] excelSheet = [Link];
[Link] rng = ([Link])[Link][10, 10];
Reading Named Ranges in Excel
Worksheet.get_Range Method
If you want to select multiple cell value from Excel sheet, you can code
like this.
[Link] excelSheet = [Link];
[Link] rng = ([Link]) excelSheet.get_Range([Link][1,
1],
[Link][3,3]);
How to get the range of occupied cells in excel sheet
For reading entire content of an Excel file in C#, we have to know how
many cells used in the Excel file. In order to find the used range we use
"UsedRange" property of xlWorkSheet . A used range includes any cell
that has ever been used. It will return the last cell of used area.
[Link] range ;
range = [Link];
How to properly clean up Excel interop objects
Interop marshaling governs how data is passed in method arguments and
return values between managed and unmanaged memory during calls.
Most data types have common representations in both managed and
unmanaged memory. The interop marshaler handles these types for you.
Other types can be ambiguous or not represented at all in managed
memory.
[Link] (excelWB);
[Link] (excelApp);
It is important to note that every reference to an Excel COM object had to
be set to null when you have finished with it, including Cells, Sheets,
everything.
The Marshal class is in the [Link] namespace,
so you should import the following namespace.
using [Link];
Open and Read an Excel Spreadsheet Programmatically
Copy and paste the following source code in your C# project file
using System;
using [Link];
using [Link];
using Excel = [Link];
namespace WindowsFormsApplication4
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
[Link] xlApp ;
[Link] xlWorkBook ;
[Link] xlWorkSheet ;
[Link] range ;
string str;
int rCnt ;
int cCnt ;
int rw = 0;
int cl = 0;
xlApp = new [Link]();
xlWorkBook = [Link](@"d:\[Link]", 0,
true, 5, "", "", true, [Link],
"\t", false, false, 0, true, 1, 0);
xlWorkSheet =
([Link])[Link].get_Item(1);
range = [Link];
rw = [Link];
cl = [Link];
for (rCnt = 1; rCnt < = rw; rCnt++)
{
for (cCnt = 1; cCnt < = cl; cCnt++)
{
str = (string)([Link][rCnt, cCnt] as
[Link]).Value2;
[Link](str);
}
}
[Link](true, null, null);
[Link]();
[Link](xlWorkSheet);
[Link](xlWorkBook);
[Link](xlApp);
}
}