This project has moved. For the latest updates, please go here.

Fill worksheet dynamically

Mar 3, 2012 at 5:34 PM

Hi all. I have a datagridview and I want to fill the cells of my worksheet dynamically from it. Is it possible? Is there a way to populate the worksheet from the gridview? this works

workSheet.Cells[2,2].Value = "test";

but this does not

for (int r = 0; r < dataGridView1.RowCount; r++)
                {
                    for (int c = 0; c < dataGridView1.ColumnCount; c++)
                    {

workSheet.Rows[r,c].Value = dataGridView1.Rows[r].Cells[c].Value.ToString();
}
}

Any alternatives of how to accomplish this? Is there a way to populate worksheet from a datatable or dataset?

Please note that it is the first time that I use NetOffice. Thanks in advance

Coordinator
Mar 3, 2012 at 6:01 PM
Edited Mar 3, 2012 at 6:02 PM
Hey,
All collections in office starts with the index 1. add "+1" to the destination adress
and use the Cells property. looks like this:

for
(int r = 0; r < dataGridView1.RowCount; r++) { for (int c = 0; c < dataGridView1.ColumnCount; c++) { workSheet.Cells[r+1,c+1].Value = dataGridView1.Rows[r].Cells[c].Value.ToString(); } }


the non zero based index is not implemented by NetOffice.
this is an MS-Office "feature" independent from the way you
accessing office. (NetOffice, VBA, VSTO, Interop...)

*happy coding
Sebastian
Mar 3, 2012 at 6:33 PM

It works!!! Thank you so much!!!  I was not aware of this "feature". :) Is there a way to speed up this process for large datasets???

Coordinator
Mar 3, 2012 at 7:04 PM

one way is OleDb:

http://www.developmentalmadness.com/archive/2008/09/12/readwrite-excel-data-using-oledb.aspx

http://www.codeproject.com/Articles/37055/Working-with-MS-Excel-xls-xlsx-Using-MDAC-and-Oled

antother way is open-xml. supported since excel 2007. open-xml is a xml specification to describe a worksheet completly.
you dont need excel to create an open-xml document and its fast of course(just create a textfile), but advanced knowledge about, xml, xml namespace and so on. a nice and easy way to deal with open xml is the closed-xml project: http://closedxml.codeplex.com

if you have a COM Addin for excel and you want fast processing data in excel then Excel-DNA is the best way on this planet. http://exceldna.codeplex.com
Excel-DNA doesnt use COM behind and read/writeback 1 million cells in a second!


*Sebastian

Mar 3, 2012 at 8:20 PM
Edited Mar 3, 2012 at 8:25 PM

Actually I am looking to speedup the process of writing dataset to excel with netoffice. I use background worker for this. I am testing it now. In any case, thank you very much Sebastian!! I will look at the links and will come back for further questions :)

Coordinator
Mar 3, 2012 at 8:56 PM

please keep in your mind: COM Interop and multithreading doesnt works together. you have to access ms-excel from the created thread.
you can calculate and recieve data in the async backgroundworker DoWork trigger.  but use only RunWorkerCompleted to set any stuff in your worksheet. otherwise you have various problems, exceptions, freeze of the application, excel chrashes or doesnt remove from process list if you close and dispose them.

*Sebastian

Mar 4, 2012 at 2:59 PM

So this code is wrong? Excel*32 in process manager does not close/kill itself when finishing writing the sheet. Same happens with no backgroundworker. Could you please suggest me a fix? Windows 64bit pro here. Thank you.

  private void button2_Click(object sender, EventArgs e)
        {
            sFD.Filter = "Xlsx Files|*.xls|All Files|*.*";
            sFD.FilterIndex = 2;
            DialogResult result = sFD.ShowDialog();

            if (result == DialogResult.OK)
            {
                string fileout = sFD.FileName;

               backgroundWorker1.RunWorkerAsync(fileout);            
                 
        }


               
                
            
            else
            {
                MessageBox.Show("Opening file aborted");
            }
        }

        private void backgroundWorker1_DoWork(object sender, DoWorkEventArgs e)
        {
            string fileout = (string)e.Argument;
            LateBindingApi.Core.Factory.Initialize();
            
            Excel.Application excelApplication = new Excel.Application();
            excelApplication.DisplayAlerts = false;

            // add a new workbook
            Excel.Workbook workBook = excelApplication.Workbooks.Add();
            Excel.Worksheet workSheet = (Excel.Worksheet)workBook.Worksheets[1];
           
           
             for (int c = 0; c < dataGridView1.ColumnCount; c++)
             {


                 workSheet.Cells[1, c + 1].Value = dataGridView1.Rows[0].Cells[c].Value.ToString();

            }


            for (int r = 1; r < dataGridView1.RowCount; r++)
            {
                for (int c = 0; c < dataGridView1.ColumnCount; c++)
                {
                    

                    workSheet.Cells[r , c+1 ].Value = dataGridView1.Rows[r].Cells[c].Value.ToString();
                  
                }

            }

            workBook.SaveAs(fileout, XlFileFormat.xlWorkbookNormal, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                                                        NetOffice.ExcelApi.Enums.XlSaveAsAccessMode.xlExclusive);

            // close excel and dispose reference
            excelApplication.Quit();
            excelApplication.Dispose();
        }

        private void backgroundWorker1_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
        {
           

           
            MessageBox.Show("Workbook saved.");
        }
    }

Coordinator
Mar 4, 2012 at 8:27 PM

your code looks okay. keep an eye of com proxies in your app with: LateBindingApi.Core.Factory.ProxyCount;
did you call LateBindingApi.Core.Factory.Initialize() multiple times?

*Sebastian

Mar 4, 2012 at 10:20 PM

As far as I know I call it once as it is shown above. How can I terminate EXCEL*32 process when worker finishes do_work Code?

Coordinator
Mar 6, 2012 at 7:18 AM

i'm suprised. the dispose mechanism in NetOffice works serious. its possible you send me your solution to public.sebastian@web.de ?
*Sebastian

Mar 7, 2012 at 7:40 PM

Sorry for the delay, you have mail.

Nick

Coordinator
Mar 7, 2012 at 8:32 PM
Edited Mar 7, 2012 at 8:33 PM

hey nick,

i do a test and its as i said. it works fine without the background worker but with the worker you have issues.
You have to create excel in STA threads and you cant change the kind of thread for the backgroundworker. but you can use a normal Thread.

 void Foo()
 {
   System.Threading.Thread thread = new System.Threading.Thread(BigJob);
   thread.SetApartmentState(System.Threading.ApartmentState.STA);
   thread.Start();
 }

 void BigJob()
 {
    try
    {
       string fileout = "C:\\...\\myFile.xlsx";
       LateBindingApi.Core.Factory.Initialize();
       Excel.Application excelApplication = new Excel.Application();
       excelApplication.DisplayAlerts = false;
       
       // add a new workbook
       Excel.Workbook workbook = excelApplication.Workbooks.Add();
       Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];

       // doe something here

       workbook.SaveAs(fileout, XlFileFormat.xlWorkbookNormal, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                                                       NetOffice.ExcelApi.Enums.XlSaveAsAccessMode.xlExclusive);
       workbook.Close();
       // close excel and dispose reference
       excelApplication.Quit();
       excelApplication.Dispose();
     }
     catch (Exception exception)
     {
        MessageBox.Show(exception.Message);
     }
  }

thats works good for me.
you can also try to save performance and dont using any thread stuff. you can push the performance with some tricks.
the performance slow down is asociated with the count of calls to excel. reduce these calls. one basic trick is a value array.

  // create the value matrix array 
  object[,] tempArray = new object[dataGridView1.RowCount, dataGridView1.ColumnCount];

  for (int r = 0; r < dataGridView1.RowCount; r++)
  {
     for (int c = 0; c < dataGridView1.ColumnCount; c++)
        tempArray[r , c ] = dataGridView1.Rows[r].Cells[c].Value.ToString();
  }
  
  // get the range, the size of the range needs the same or bigger size as value array
  // i use $A1:$Z1000 here as hardcoded example, thats a real wide range an big enough i think.
  Excel.Range range = workSheet.Range["$A1:$Z1000"]
  range.Value2 = tempArray;
  
  // one call and much faster 

*xlRegards
Mar 8, 2012 at 12:37 PM

Seems to work ok! Thank you SO MUCH Sebastian.!!!! You saved me ;) Thank you again!

Apr 16, 2012 at 5:14 PM
Edited Apr 16, 2012 at 5:29 PM

When exporting data to excel keep in mind the following restrictions. It's my experience as they all caused errors in real-live-code when dealing with a large amount of data (some million! rows, all datatypes in cells):

  • Using the old excel file format (*.xls) you are not allowed to export more than 65535 rows
  • Using excel <= Version 11 you are not allowed to write text cells larger than 911 characters (see http://support.microsoft.com/kb/818808/en-us)
  • When writing numbers as strings (eg postal code "00123") you should set the column format. This prevents from trimming the zeros:
    workSheet.Columns[col].NumberFormat = "@";
  • Using more rows than about 50.000 at once with the method above (range.Value2 = tempArray) generates an com exception.
    In this case you should use a loop and transform the data in chunks of about 50.000.
    I didn't find any hint for this in the www, so if someone has further infos on this please let me know and reply to this topic.
  • Using DateTime values < 1.1.1900 generally are not allowed in excel
  • Using and accessing the Excel-range should be like this, using Value not Value2:
    var start = workSheet.Cells[row1, col1];
    var end = workSheet.Cells[row2, col2];
    using( var range = workSheet.get_Range(start, end))
    {
        range.Value = tempArray;
    }

 

Jul 16, 2013 at 5:25 PM
I have one problem regarding date format

I use portuguese date format (dd - mm - yyyy)

when the file is created the dates appear in (mm-dd-yyyy) format
how do i change to the default format date appear inn (dd-mm-yyyy)??
Jul 17, 2013 at 9:41 AM
Never mind. Problem Solved