updating excel sheet in c# using iterop.excel

Question

I am trying updating a excel sheet in c# and i am using interop.excel. When i update a excel sheet this is update a single data. I want all data update in a single excel sheet. I am using this code

`private void button1_Click(object sender, EventArgs e)
    {
        if (cnt1 >= 1)
        {
            connectionSilca();
            for (int counter = 0; counter < cnt1; counter++)
            {
                fcl.cmd = new OracleCommand("select book_id,book_title,category_id,author,book_pub,book_copies,publisher_name,isbn,copyright_year,date_added,status from book where book_id='" + listBox1.Items[counter].ToString() + "'", fcl.con);

                DataTable dt = new DataTable();
                DataSet DS = new DataSet();
                OracleDataAdapter AD = new OracleDataAdapter(cmd);
                OracleDataAdapter da = new OracleDataAdapter(cmd);
                da.Fill(dt);
                da.Fill(DS);
                //DataTable temptable = DS.Tables[0];
                //ExportToExcel(temptable);
                ExportToExcel(DS.Tables[0]);
            }
        }
        else
        {
            MessageBox.Show("Please Select Atleast One Item");
        }
        con.Close();
        MessageBox.Show("EXCEL DOWNLOAD SUCCESSFULLY");
        datarefresh();
    }

    public void ExportToExcel(DataTable dtCollection)
    {
        var ds = dtCollection;
        Microsoft.Office.Interop.Excel.Worksheet objWorkSheet1 = null;
        Microsoft.Office.Interop.Excel.Application objExcel = new Microsoft.Office.Interop.Excel.Application { Visible = false };
        Microsoft.Office.Interop.Excel.Workbooks objWorkbooks = objExcel.Workbooks;
        Microsoft.Office.Interop.Excel.Workbook objWorkbook = objWorkbooks.Add(Missing.Value);
        Microsoft.Office.Interop.Excel.Sheets objSheets = objWorkbook.Worksheets;
        Microsoft.Office.Interop.Excel.Range objCells;
        Microsoft.Office.Interop.Excel.Range myCell;

        //open the excel file form the location 
        //here is existing excel code
        string path = @"C:\\EXCEL\\DATA\\myData.xls";
        objExcel = new Microsoft.Office.Interop.Excel.Application();
        objExcel.DisplayAlerts = false;
        objWorkbook = objExcel.Workbooks.Open(path, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
        //Get all the sheets in the workbook
        objSheets = objWorkbook.Worksheets;
        //Get the allready exists sheet
        objWorkSheet1 = (Microsoft.Office.Interop.Excel.Worksheet)objSheets.get_Item("Input");
        Microsoft.Office.Interop.Excel.Range range = objWorkSheet1.UsedRange;

        objWorkSheet1.Cells[5, 3]= 100000089;
        objWorkSheet1.Cells[5, 4] = 001;
        objWorkSheet1.Cells[5, 7] = System.DateTime.Now.ToString("ddMMyyyy");
        objWorkSheet1.Cells[5, 10] = "ASHSBCTS2017";

        var iCurrentRow = 12;
        var dt = ds;
        int columnsCount = dt.Columns.Count;
        objWorkSheet1 = (Microsoft.Office.Interop.Excel.Worksheet)(objSheets[1]);
        objCells = objWorkSheet1.Cells;

        /*for (var h = 0; h < dt.Columns.Count; h++)
        {
            myCell = (Microsoft.Office.Interop.Excel.Range)objCells[iCurrentRow, h + 1];
            myCell.Value2 = dt.Columns[h].ColumnName;
        }
        iCurrentRow++;*/
        for (var r = 0; r < dt.Rows.Count; r++)
        {
            for (var c = 0; c < dt.Columns.Count; c++)
            {
                    if (dt.Columns[c].DataType.Name == "String" || dt.Columns[c].DataType.Name == "DateTime")
                    {
                        myCell = (Microsoft.Office.Interop.Excel.Range)objCells[r + iCurrentRow, c + 1];
                        myCell.Value2 = "'" + dt.Rows[r][c].ToString().Trim();
                    }
                    else
                    {
                        myCell = (Microsoft.Office.Interop.Excel.Range)objCells[r + iCurrentRow, c + 1];
                        myCell.Value2 = dt.Rows[r][c];
                    }
                }
            }
        //objWorkSheet1.Cells.EntireRow.AutoFit();
        //objWorkSheet1.Cells.EntireColumn.AutoFit();
        objWorkbook.SaveAs("C:\\EXCEL\\DATA\\myData.xls", Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel12, System.Reflection.Missing.Value, 
            System.Reflection.Missing.Value, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlShared, false, false,
            System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
        objWorkbook.Close(Missing.Value, Missing.Value, Missing.Value);
        objExcel.Quit();
        GC.WaitForPendingFinalizers();
        GC.Collect();
        GC.WaitForPendingFinalizers();
        GC.Collect();
    }`

How to solve this, please give me some solution for this Thanks in advance

i am updating a excel sheet, when i update this is creating per data a different excel sheet, such i have 10 rows of a dataset it's creating 10 different excel file, i want all data export in a single excel sheet — LEARNING STUDENT, Apr 26 '17 at 12:18
have a look at my answer to this question, it may help http://stackoverflow.com/questions/30749573/reading-data-out-of-multiple-excel-docs-and-writing-them-in-another-excel-doc/30749785#30749785 — Jegan, Apr 26 '17 at 12:20
but in this case data in exporting from different excel file to a single excel file, in my case data is exporting from oracle server to excel — LEARNING STUDENT, Apr 26 '17 at 12:33
please see my code once, if any solution for this please give — LEARNING STUDENT, Apr 26 '17 at 12:44
The problem is that you are reading the data from oracle in a for loop, and in that loop for each 'cnt1' you are writing to Excel, but all this happened in button click. You need to separate the these two actions 1) read from the database, then 2) write that data to excel. I'll write you an example code bit later. — Jegan, Apr 26 '17 at 12:44
What are the file names that are created? What happens if you use `Save()` instead of `SaveAs()`? — Crowcoder, Apr 26 '17 at 12:48
actually i have a predefined excel file, and i am updating the file, that file name is myData — LEARNING STUDENT, Apr 26 '17 at 12:56
Yes, but you say it creates many files instead of updating the one file. Is that happening or not? Or are you saying it creates many WorkSheets in the WorkBook instead of updating one WorkSheet? It is unclear what you are asking. — Crowcoder, Apr 26 '17 at 13:00
i need updating all data in a single excel sheet, NOT A SINGLE DATA — LEARNING STUDENT, Apr 26 '17 at 13:14
If data is properly formatted you can modify Excel in the same way you do with Access, using ADO and SQL http://stackoverflow.com/questions/12979605/how-to-retrieve-data-from-excel-with-adodb-connection-if-the-first-line-of-the-w — Fran Cerezo, Apr 26 '17 at 17:11

Jegan · Accepted Answer · 2017-04-26T17:04:36.070

you may want to change the line

objWorkSheet1 = (Microsoft.Office.Interop.Excel.Worksheet)objSheets.get_Item("Input");

with

objWorkSheet1 =  = objWorkbook.Worksheets["Input"] as Worksheet;

Also as I mentioned in the comments, you should separate the read and the write. there is no need for the code below

for (int counter = 0; counter < cnt1; counter++)
{
    .....
    ExportToExcel(DS.Tables[0]);
}

the 'ExportToExcel(DS.Tables[0]);' should come out of that loop.

EDIT

using System;
using System.Collections.Generic;
using System.Data;
using Microsoft.Office.Interop.Excel;
using DataTable = System.Data.DataTable;

namespace ExcelExample
{
    internal class Test
    {
        private int _cnt1 = 0;
        private readonly List<DataTable> _dataTables = new List<DataTable>();

        private void button1_Click(object sender, EventArgs e)
        {
            ReadDataFromDatabase();
            ExportToExcel();
            DataRefresh();
        }

        private void ReadDataFromDatabase()
        {
            try
            {
                if (_cnt1 >= 1)
                {
                    _dataTables.Clear();
                    connectionSilca();
                    for (int counter = 0; counter < _cnt1; counter++)
                    {
                        fcl.cmd = new OracleCommand(
                            "select book_id,book_title,category_id,author,book_pub,book_copies,publisher_name,isbn,copyright_year,date_added,status from book where book_id='" +
                            listBox1.Items[counter].ToString() + "'", fcl.con);

                        var dataSet = new DataSet();

                        OracleDataAdapter oracleDataAdapter = new OracleDataAdapter(cmd);
                        oracleDataAdapter.Fill(dataSet);
                        _dataTables.Add(dataSet.Tables[0]);
                    }
                }
                else
                {
                    MessageBox.Show("Please Select Atleast One Item");
                }
            }
            catch (Exception)
            {
                // catch any connection exceptions.
            }
            finally
            {
                con.Close();
            }
        }

        private void ExportToExcel()
        {
            //open the excel file form the location 
            //here is existing excel code
            const string path = @"C:\\EXCEL\\DATA\\myData.xls";
            var excelApplication = new Application(){Visible = false, DisplayAlerts = false};
            var workbook = excelApplication.Workbooks.Open(path, 0, false, 5, "", "", false, XlPlatform.xlWindows, "", true, false, 0, true, false, false);
            var workSheet = workbook.Worksheets["Input"] as Worksheet;

            if (workSheet != null)
            {
                workSheet.Cells[5, 3] = 100000089;
                workSheet.Cells[5, 4] = 001;
                workSheet.Cells[5, 7] = System.DateTime.Now.ToString("ddMMyyyy");
                workSheet.Cells[5, 10] = "ASHSBCTS2017";

                var cells = workSheet.Cells;
                var rowIndex = 12;

                foreach (var table in _dataTables)
                {
                    for (var r = 0; r < table.Rows.Count; r++)
                    {
                        for (var c = 0; c < table.Columns.Count; c++)
                        {
                            var currentCell = (Range)cells[rowIndex, c + 1];

                            if (table.Columns[c].DataType.Name == "String" || table.Columns[c].DataType.Name == "DateTime")
                            {
                                currentCell.Value2 = "'" + table.Rows[r][c].ToString().Trim();
                            }
                            else
                            {
                                currentCell.Value2 = table.Rows[r][c];
                            }
                        }
                        rowIndex++;
                    }
                }
            }

            workbook.SaveAs("C:\\EXCEL\\DATA\\myData.xls", XlFileFormat.xlExcel12, System.Reflection.Missing.Value,
                System.Reflection.Missing.Value, false, false, XlSaveAsAccessMode.xlShared, false, false,
                System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
            workbook.Close();
            excelApplication.Quit();

            MessageBox.Show("EXCEL DOWNLOAD SUCCESSFULLY");
        }
    }
}

updating excel sheet in c# using iterop.excel

1 Answers1