Never underestimate the power of Passion!

Wednesday, 22 February 2017

On 00:17 by Vardan Kumar in    3 comments
Excel in C#

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

  1.  Firstly we have to make an object of Microsoft office excel application Microsoft.Office.Interop.Excel.Application ExcelCsp = new Microsoft.Office.Interop.Excel.Application();
  2. Now we'll create a workbook.                                                                                         Workbook xlWorkbook = ExcelCsp.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
  3. Now we'll create a data table collection and add our data set tables to it.       DataTableCollection collection = dataSet.Tables;
  4. 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();
                        }

C# Excel Sheet data table data set
Source Code C#

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();






3 comments:

  1. Really cool post, highly informative and professionally written and I am glad to be a visitor of this perfect blog, thank you for this rare info!
    MS office course in chennai

    ReplyDelete
  2. In the event that you have a more established form, at that point Excel looks in an unexpected way, however the idea of a bookkeeping page is essentially the equivalent. Excel spreadsheet consultant

    ReplyDelete
  3. This usefulness and the entirety of the shortcoming of twofold mouse clicks misses the mark concerning the imprint for some clients who have enormous complex spreadsheets, for example, a monetary model with recipes that connect to various cells or cells a lot further down/across the sheet or to cells on an assortment of different worksheets, or even exercise manuals. excel expert

    ReplyDelete