How To: Export Multiple C1TrueDbGrids to Excel Sheets

There are many scenarios where we would want to export data into Excel format. The data could be from multiple ComponentOne TrueDbGrid for WinForms controls or from any other data bound control. However, using a C1TrueDbGrid control's ExportToExcel method we can't export multiple C1TrueDbGrids to a single Excel file. In my previous blog I explained How To: Export Multiple C1TrueDbGrids to a Single PDF. Now, I will explain how to export multiple C1TrueDbGrid controls to multiple sheets of an Excel file.

Export C1TrueDbGrids to Excel files

After binding multiple C1TrueDbGrid controls we need to save them to separate Excel files using ExcelExportToExcel() method.

 C1TrueDBGrid1.ExportToExcel("..\\\..\\\Excel1.xls", true);  
 C1TrueDBGrid2.ExportToExcel("..\\\..\\\Excel2.xls", true);  

Use ComponentOne Excel Control

Then using ComponentOne Excel control for WinForms, load multiple Excel files in different sheets of an Excel workbook and save the Excel workbook.

 string path = Application.StartupPath;  
 int pos = path.IndexOf("\\\bin");  
 path = path.Substring(0, pos);  
 string tempdir = Application.ExecutablePath.Substring(0,  
 Application.ExecutablePath.LastIndexOf("\\\") + 1);  
 string xlsFileName = tempdir + "CombineSheets.xls";  
 if (System.IO.File.Exists(xlsFileName))  
  {  
     System.IO.File.Delete(xlsFileName);  
  }  

 // clear the book  
 C1XLBook1.Clear();  
 C1XLBook1.Sheets.Clear();  
 string fileName = null;  

 foreach (string fileName_loopVariable in System.IO.Directory.GetFiles(path, "*.xls"))  
  {  
    fileName = fileName_loopVariable;  
    // load Excel file  
    C1.C1Excel.C1XLBook book = new C1.C1Excel.C1XLBook();  
    book.Load(fileName);  

    // clone and rename first sheet (sheet names must be unique)  
    C1.C1Excel.XLSheet clone = book.Sheets[0].Clone();  
    clone.Name = "Sheet" + i;  
    i = i + 1;  
    // add cloned sheet to main book  
    C1XLBook1.Sheets.Add(clone);  
  }  
 C1XLBook1.Save(xlsFileName);  
 System.Diagnostics.Process.Start(xlsFileName);  

This concludes my blog. You can download the samples for detailed implementation. DownloadSample_CS DownloadSample_VB