how to get the continuation of the Same Excel sheet from next datatable Export to Excel from c#

Question

When I export a data table to excel how can I identify how many rows are inserted to Excel, and get the next row position of the same Excel sheet to insert the next data table values?

var lines = new List<string>();

string[] columnNames = dataTable.Columns.Cast<DataColumn>().
                              Select(column => column.ColumnName).
                              ToArray();

var header = string.Join(",", columnNames);
lines.Add(header);

var valueLines = dataTable.AsEnumerable()
               .Select(row => string.Join(",", row.ItemArray));            
lines.AddRange(valueLines);

File.WriteAllLines("excel.csv",lines);

Possible duplicate of [How to count the number of rows in excel with data?](http://stackoverflow.com/questions/6301665/how-to-count-the-number-of-rows-in-excel-with-data) — uTeisT, Aug 25 '16 at 13:41
@ alex k :it is true ,but it may be change if any heading or length of the data — Joji Thomas Eapen, Aug 25 '16 at 13:49
@uteist :fom that we can get the no of rows count , there is a second part for my question detail, ie, i need to insert the next data table values to that same Excel as continuation — Joji Thomas Eapen, Aug 25 '16 at 14:02
Well, I assumed that you already know how to modify an excel file. If you post something that shows your work, I can help with the rest. — uTeisT, Aug 25 '16 at 14:44
@utiest the above code i used for the export to excel , i this i need to take the next line of the current escel sheet the to add the next datatable value — Joji Thomas Eapen, Aug 27 '16 at 06:32

score 1 · Accepted Answer · answered Sep 10 '16 at 08:16

private static Microsoft.Office.Interop.Excel.Workbook mWorkBook;
private static Microsoft.Office.Interop.Excel.Sheets mWorkSheets;
private static Microsoft.Office.Interop.Excel.Worksheet mWSheet1;
private static Microsoft.Office.Interop.Excel.Application oXL;
public static void ReadExistingExcel()
{
   string path = @"C:\Tool\Reports1.xls";
   oXL = new Microsoft.Office.Interop.Excel.Application();
   oXL.Visible = true;
   oXL.DisplayAlerts = false;
   mWorkBook = oXL.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
  mWorkSheets = mWorkBook.Worksheets;
   //Get the allready exists sheet
   mWSheet1 = (Microsoft.Office.Interop.Excel.Worksheet)mWorkSheets.get_Item("Sheet1");
   Microsoft.Office.Interop.Excel.Range range= mWSheet1.UsedRange;
   int colCount = range.Columns.Count;
   int rowCount= range.Rows.Count;
   for (int index = 1; index < 15; index++)
   {
      mWSheet1.Cells[rowCount + index, 1] = rowCount +index;
      mWSheet1.Cells[rowCount + index, 2] = "New Item"+index;
   }
   mWorkBook.SaveAs(path, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal,
   Missing.Value, Missing.Value, Missing.Value,    Missing.Value,Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive,
   Missing.Value, Missing.Value, Missing.Value,
   Missing.Value, Missing.Value);
   mWorkBook.Close(Missing.Value, Missing.Value, Missing.Value);
   mWSheet1 = null;
   mWorkBook = null;
   oXL.Quit();
   GC.WaitForPendingFinalizers();
   GC.Collect();
   GC.WaitForPendingFinalizers();
   GC.Collect();
}

this method works fine for me and i rewrite some methods in this to do exact way — Joji Thomas Eapen, Sep 10 '16 at 08:17

how to get the continuation of the Same Excel sheet from next datatable Export to Excel from c#

1 Answers1