C# - How to write data in selected cell from datatable.

Hi,

I am using datatable and it contains some values.

i want tp write those values in excel file which is already contain some data in sheet1.

so my output would print in where i mentioned the cell.

for example,

my datatable should write starting cell in C12.

so the datatable should write from there. (see sample output image attached).

EmpNo      Name      Dept      Place
101      John      MATHS      USA
102      Robert      PHYSICS      INDIA
103      Anu      ENGLISH      CHINA

output image.(PFA)

How to write datatable value in c#..?
Output_Excel.png
Ganesh STech Lead cum developerAsked:
Who is Participating?
 
NorieConnect With a Mentor VBA ExpertCommented:
Perhaps this will set you in the right direction, though there are many ways to do this sort of thing.
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;



namespace DTtoExcelRange
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            DataTable dt =  new DataTable();

            dt.Columns.Add("EmpNo");
            dt.Columns.Add("Name");
            dt.Columns.Add("Dept");
            dt.Columns.Add("Place");
          
            dt.Rows.Add(100, "John", "MATHS", "USA");
            dt.Rows.Add(101, "Robert", "PHYSICS", "INDIA");
            dt.Rows.Add(102, "Anu", "ENGLISH", "CHINA");
            dataGridView1.DataSource = dt;
            
        }

        private void button2_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            var appXL = new Excel.Application();

            appXL.Visible = true;

            var wbXL = appXL.Workbooks.Open("C:\\Test\\EETestWB.xlsx");

            var wsXL = (Excel.Worksheet) wbXL.Sheets[1];

            var rngXL = (Excel.Range) wsXL.Cells[12, "C"];
            
            DataTable dt = (DataTable)(dataGridView1.DataSource);

            //var arr = dt.Rows[1].ItemArray;

            foreach (DataColumn dc in dt.Columns)
            {
                rngXL.Value = dc.ToString();
                rngXL = rngXL.Offset[0, 1];
            }

            rngXL = (Excel.Range)wsXL.Cells[13, "C"];

            foreach (DataRow  dr in dt.Rows)
            {
                var arr = dr.ItemArray;
                rngXL.Resize[1, dt.Columns.Count].Value = arr;
                rngXL = rngXL.Offset[1, 0];
            }

            GC.Collect();
            GC.WaitForPendingFinalizers();

            wbXL.Save();

            appXL.UserControl = true;

        }
    }
}

Open in new window

1
 
NorieVBA ExpertCommented:
Is it the entire datatable, including headers, you want to write to Excel?
0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
Please review this on general interop features from C#:
https://docs.microsoft.com/en-us/dotnet/csharp/programming-guide/interop/how-to-access-office-onterop-objects

 var excelApp = new Excel.Application();
  excelApp.Visible = true;
excelApp.Workbooks.Open(@"C:\test.xlsx");
Excel._Worksheet workSheet = (Excel.Worksheet)excelApp.ActiveSheet;
workSheet.Cells[12, "C"] = "Test";

Open in new window


You should be able to copy the data to the cells from there using different columns / offsets.  Let me know if you need more help.

Note the above starts a new application of excel.  If you're excel is already opened you can use the following (from https://stackoverflow.com/questions/1118735/get-instance-of-excel-application-with-c-sharp-by-handle):

public Excel.Application StartExcel()
{
    Excel.Application instance = null;
    try
    {
       instance = (Excel.Application)System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application");
    }
    catch (System.Runtime.InteropServices.COMException ex)
    {
       instance = new Excel.Application();
    }

    return instance;
}

Open in new window

0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
Ganesh STech Lead cum developerAuthor Commented:
Excellent solution..
0
 
Ganesh STech Lead cum developerAuthor Commented:
Output_Excel.pngHow to set border for each  cell (for all the records from data table).

for example,

i want to print all the cell with border at all the records including header. (with header font is bold).
0
 
NorieVBA ExpertCommented:
Can you start a new question for that?
0
 
Ganesh STech Lead cum developerAuthor Commented:
ok.thanks...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.