cant export dataset to multiple sheets excel file

Question

0.00/5 (No votes)

See more:

Hi Everyone,

I am trying to move the dataset (multiple tables) to mutiple sheets.
It's creating the sheets for each datatable but only transfering the data of a single datatable.
I can't find my mistake.
Please help me out.
Below is the code I'v written.

C#

public static void Excel_FromDataTable(DataSet ds)
       {
     Excel._Worksheet[] wsheets = new Excel._Worksheet[ds.Tables.Count];
           Excel.Application excelApps = new Excel.Application();
           int Sheet_count = 0;
           Excel.Sheets sheet;
           Excel.Range[] range = new Excel.Range[ds.Tables.Count];
           Excel.Range[] xlCells = new Excel.Range[ds.Tables.Count];
           try
           {
              Excel.Workbooks workbooks = excelApps.Workbooks;
               Excel.Workbook workbook = excelApps.Workbooks.Add(Type.Missing); 
               for (int i = 0; i < ds.Tables.Count; i++)
               {
                   DataTable dt = ds.Tables[i];
                   Sheet_count += 1;
                   int iCol = 0;
                   foreach (DataColumn c in dt.Columns)
                   {
                       iCol++;
                       excelApps.Cells[1, iCol] = c.ColumnName;
                       excelApps.Cells[1, iCol].Font.Bold = true;
                   }
                   int iRow = 0;
                   foreach (DataRow r in dt.Rows)
                   {
                       iRow++;
                        iCol = 0;
                       foreach (DataColumn c in dt.Columns)
                       {
                           iCol++;
                     excelApps.Cells[iRow + 1, iCol] = r[c.ColumnName]; excelApps.Cells.ColumnWidth = (excelApps.Cells[iRow + 1, iCol].Count() * 50);
                       }
                   }
                   sheet = excelApps.Sheets;
             wsheets[i] = ((Excel._Worksheet)(sheet[Sheet_count]));
                   wsheets[i].Name = dt.TableName;
                   range[i] = wsheets[i].Cells;
                   xlCells[i] = wsheets[i].Cells;
               }
               object missing = System.Reflection.Missing.Value;
               // If wanting to Save the workbook...
               workbook.SaveAs("MYEXCEL.xls", Excel.XlFileFormat.xlWorkbookDefault, missing, missing, false, false, Excel.XlSaveAsAccessMode.xlNoChange, missing, missing, missing, missing, missing);

               // If wanting to make Excel visible and activate the worksheet...
               //excelApps.Visible = true;
               //Excel.Worksheet worksheet = (Excel.Worksheet)excel.ActiveSheet;
               //((Excel._Worksheet)worksheet).Activate();

               // If wanting excel to shutdown...
               //((Excel._Application)excel).Quit();

               //excelApps.Quit();
               excelApps.Workbooks.Close();
           }

           catch (Exception ex)
           {

           }

           finally
           {


           }
       }

please help.

Posted 8-Sep-10 3:07am

sainath437

Updated 8-Sep-10 3:56am

Tom Deketelaere

v2

Add a Solution

Comments

Dalek Dave 8-Sep-10 9:13am

There is nothing like well commented code.

And this is nothing like well commented code !

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Dylan Morley · Answer 1 · 2010-09-08T04:46:00

I think you need to set a reference to the sheet you want to write data into before you start writing the cell contents

for (int i = 0; i < ds.Tables.Count; i++)
{
   DataTable dt = ds.Tables[i];
   Sheet_count += 1;

   // Get a reference to the worksheet
   WorkSheet worksheet = (Worksheet) sheets.get_Item(Sheet_count);

   // Use Worksheet.Cells or preferably a range object
   int iCol = 0;
   foreach (DataColumn c in dt.Columns)
   {
       iCol++;
       worksheet.Cells[1, iCol] = c.ColumnName;
       worksheet.Cells[1, iCol].Font.Bold = true;
   }
}

So don't use the application variable to write the cell contents (which i think refers to the 'ActiveSheet'), but use your explicitly retrieved WorkSheet object to access the Cells. As mentioned, using a Range object is probably better

Towards the end of your code, you're not even using any of these variables, what is the point in them?

C#

wsheets[i].Name = dt.TableName;
range[i] = wsheets[i].Cells;
xlCells[i] = wsheets[i].Cells;

Don't forget to clean up all your COM Interop

cant export dataset to multiple sheets excel file

1 solution

Solution 1

Add your solution here

Preview 0

cant export dataset to multiple sheets excel file

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us