Wednesday, 22 February 2017
On 00:17 by Vardan Kumar in C# No comments
Excel sheet from data table and data set in c#
Excel workbook is one of the most popular MS office application, especially when it comes to make a report. Hence it is recommended to learn how to make an excel sheet in c# developing environment.
Before we continue let us briefly understand what a DataSet is:
A DataSet is a collection of data tables objects or instances on which we can set relation through data relation objects and hence navigate through data table architecture.
How DataSet is declared or initialized in c#?
System.Data.DataSet dt_csp = null;
Next we'll declare our data table:
System.Data.DataTable tb_csp = new System.Data.DataTable("SFY" + sfy.ToString());
Here the DataTable method argument is its name which will also be used as the name of our excel sheet which we are just about to make.
sfy is an integer variable which can be set dynamically as financial year value.
We can populate our data table using:
tb_csp.Columns.Add("cspassion Column String"); // To add columns to data table
Note for data table the columns name should be unique otherwise it will throw an exception
tb_csp.Rows.Add("cspassion Row String"); // To add rows to data table
After populating the data table, add it to data set.
dt_csp = new DataSet("General");
dt_csp.Tables.Add(tb_csp);
Hence our data table is added to data set colection.
So we are ready with our data set, now let us populate our excel sheet
- Firstly we have to make an object of Microsoft office excel application Microsoft.Office.Interop.Excel.Application ExcelCsp = new Microsoft.Office.Interop.Excel.Application();
- Now we'll create a workbook. Workbook xlWorkbook = ExcelCsp.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
- Now we'll create a data table collection and add our data set tables to it. DataTableCollection collection = dataSet.Tables;
- Now we'll initialize Sheets and worksheets and create our excel sheets.
Sheets xlSheets = null
Worksheet xlWorksheet = null;
//Create Excel Sheets
xlSheets = ExcelCsp.Sheets;
xlWorksheet = (Worksheet)xlSheets.Add(xlSheets[1], Type.Missing, Type.Missing, Type.Missing);
7.Now we'll add our collection first table to a data table and give our worksheet the same name as data table.
System.Data.DataTable table_csp = collection[0];
xlWorksheet.Name = table_csp.TableName;
8.Now we'll add columns to our excel sheet.
for (int j = 1; j < table_csp.Columns.Count + 1; j++)
{
ExcelCsp.Cells[1, j] = table.Columns[j - 1].ColumnName;
}
9. Now we'll store value of each row and column to excel sheet.
for (int k = 0; k < table_csp.Rows.Count; k++)
{
for (int l = 0; l < table_csp.Columns.Count; l++)
{
ExcelCsp.Cells[k + 2, l + 1] =
table_csp.Rows[k].ItemArray[l].ToString();
}
Source Code:
Microsoft.Office.Interop.Excel.Application ExcelCsp = new Microsoft.Office.Interop.Excel.Application();
Workbook xlWorkbook = ExcelApp.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
DataTableCollection collection = dataSet.Tables;
Sheets xlSheets = null;
Worksheet xlWorksheet = null;
//Create Excel Sheets
xlSheets = ExcelCsp.Sheets;
xlWorksheet = (Worksheet)xlSheets.Add(xlSheets[1],
Type.Missing, Type.Missing, Type.Missing);
System.Data.DataTable table_csp = collection[0];
xlWorksheet.Name = table_csp.TableName;
for (int j = 1; j < table_csp.Columns.Count + 1; j++)
{
ExcelCsp.Cells[1, j] = table_csp.Columns[j - 1].ColumnName;
}
for (int k = 0; k < table_csp.Rows.Count; k++)
{
for (int l = 0; l < table_csp.Columns.Count; l++)
{
ExcelCsp.Cells[k + 2, l + 1] =
table_csp.Rows[k].ItemArray[l].ToString();
}
}
//We have populated our excel sheet successfully, but what now we have to save it also....
xlWorkbook.SaveAs("path");
// And finally make our excel sheet visible
ExcelCsp.Visible = true;((Worksheet)ExcelCsp.ActiveWorkbook.Sheets[ExcelCsp.ActiveWorkbook.Sheets.Count]).Delete();
Subscribe to:
Post Comments (Atom)
Search
Popular Posts
-
Troubleshooting Cisco VPN client Before starting troubleshooting, Let us see what VPN is and what it requires to perform its intended f...
-
Evolution-Mobile Phones With the development of portable technology,wireless communication has so evolved that (According to the announce...
-
File Versioning C# File versioning, saving file with unique file name in c# File versioning allows a user to have several versions of ...
-
Text Box Hint in c# Windows Form Application Text Box Hint in c# Windows Form Application While developing a windows form applicat...
-
Unable to set the Freeze Panes property of Window Class C# It is generally easy to resolve the compile time errors because the reason fo...
0 comments:
Post a Comment