无法将数据集导出到多张Excel文件 [英] cant export dataset to multiple sheets excel file

问题描述

大家好，

我正在尝试将数据集(多个表)移动到多个工作表.
它为每个数据表创建工作表，但仅传输单个数据表的数据.
我找不到我的错误.
请帮帮我.
以下是我编写的代码.

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.

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.

推荐答案

我认为您需要在开始编写单元格内容之前设置对要向其中写入数据的工作表的引用.

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;
   }
}

因此，请勿使用应用程序变量来编写单元格内容(我认为是指"ActiveSheet")，而应使用您显式检索的WorkSheet对象访问单元格.如前所述，使用Range对象可能更好

在代码结束时，您甚至没有使用任何这些变量，它们的意义是什么?

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?

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

不要忘记清理所有COM Interop

Don''t forget to clean up all your COM Interop

这篇关于无法将数据集导出到多张Excel文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！