Question export data to excel sheet/report using predefined excel template

mcleane

New member
Joined
Mar 13, 2014
Messages
2
Programming Experience
Beginner
hi for past 7 days i was wondering on how to create excel report from excel Template, populating excel report using sql select query in c# windows forms with sql server 2008. I have create an Excel Template in my same project folder named : Technician Details.xltx.
Given below is my code in c# of exporting c# with sql server 2008 data to excel report using sql select query:
i have done add reference to Microsoft Excel 14.0 Object Library.

Given below is my code in c#:
C#:
 using System;
using System.Collections.Generic;
using System.Configuration;
using System.Text;
using System.Diagnostics;
using System.Runtime.InteropServices;
using Microsoft.Office.Interop;
using Microsoft.Office.Interop.Excel;
using System.Data;
using System.IO;
using System.Data.SqlClient;
using System.Windows.Forms;

namespace DRRS_CSharp
{
    public partial class frmExcelTechnician : Form
    {
        public frmExcelTechnician()
        {
            InitializeComponent();
        }

        private void btnExportToExcel_Click(object sender, EventArgs e)
        {
            string FileName = "D:\\Workarea\vishal\\DRRS in CSharp\\DRRS CSharp\\DRRS CSharp\\Technician Details.xltx";
            string SQLQuery = "Select td.Technician_first_name as Technician_First_name,td.Technician_middle_name as Technician_middle_name,td.Technician_last_name as Technician_last_name,t.technician_dob as Technician_date_of_birth,t.technician_sex as Gender,td.technician_type as designation from Techniciandetail td,Technician t where td.technician_id=t.technician_id and td.status=1";
            SqlConnection conn = new SqlConnection("Data Source=NPD-4\\SQLEXPRESS;Initial Catalog=DRRS;Integrated Security=true");
            DataSet ds = new DataSet();
            SqlDataAdapter da = new SqlDataAdapter(SQLQuery, conn);
            da.Fill(ds);
            Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application();
            Workbook ExcelWorkBook = null;
            Worksheet ExcelWorkSheet = null;
            ExcelApp.Visible = true;
            ExcelWorkBook = ExcelApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
            List<string> SheetNames = new List<string>();
            SheetNames.Add("Technician Details");
            try
            {
                for (int i = 1; i < ds.Tables.Count; i++)
                    ExcelWorkBook.Worksheets.Add(); //Adding New sheet in Excel Workbook
                for (int i = 0; i < ds.Tables.Count; i++)
                {
                    int r = 1;
                    ExcelWorkSheet = ExcelWorkBook.Worksheets[i + 1];
                    for (int col = 1; col <= ds.Tables[i].Columns.Count; col++)
                        ExcelWorkSheet.Cells[r, col] = ds.Tables[i].Columns[col - 1].ColumnName;
                    r++;

                    for (int row = 0; row < ds.Tables[i].Rows.Count; row++) //r stands for ExcelRow and col for ExcelColumn
                    {
                        // Excel row and column start positions for writing Row=1 and Col=1
                        for (int col = 1; col <= ds.Tables[i].Columns.Count; col++)
                            ExcelWorkSheet.Cells[r, col] = ds.Tables[i].Rows[row][col - 1].ToString();
                        r++;
                    }
                    ExcelWorkSheet.Name = SheetNames[i];//Renaming the ExcelSheets
                }
                ExcelWorkBook.SaveAs("D:\\Workarea\vishal\\DRRS in CSharp\\DRRS CSharp\\Technician.xlsx");
                ExcelWorkBook.Close();

                ExcelApp.Quit();

                Marshal.ReleaseComObject(ExcelWorkSheet);

                Marshal.ReleaseComObject(ExcelWorkBook);

                Marshal.ReleaseComObject(ExcelApp);

            }
            catch (Exception exHandle)
            {
                Console.WriteLine("Exception: " + exHandle.Message);
                Console.ReadLine();
            }
        }
    }
}
The above code works for generating default excel report. But what i want is the result of sql select query should be placed into columns from from Excel Template named:Technician Details.xltx into excel sheet Technician.xlsx.
As you can see from my code in below line:
C#:
string FileName="D:\\Workarea\vishal\\DRRS in CSharp\\DRRS CSharp\\DRRS CSharp\\Technician Details.xltx";
But what i want is when i execute the program or when i click button1 i
want the sql server data to go their appropriate columns as i have done
the design in excel template named: Technician.

i have an excel template named: Technician Details.xltx in my same
project folder.In the below design Column Header with text: Technician First Name starts at A column 3rd cell
Similarly Column Header with text: Technician Middle Name starts at B column 3rd cell, Column Header with text: Technician Last Name starts at C column 3rd cell, Column Header with text: Date of Birth starts at D column 3rd cell, Column Header with text: Gender starts at E column 3rd cell and Column Header with text: Type starts at F column 3rd cell. My Question is how to place results of sql query into appropriate columns in Excel Sheet/Report from Excel Template?.

My question is how do i fill results of query into appropriate columns from Excel Template into excel report?.

Can anyone help me please? Any help or guidance in solving this problem would be greatly appreciated.
Given below is my design of my Excel Template:
 
Top Bottom