如果我在第一张表中进行更改，则会更新多个工作表。 [英] Multiple sheet get updated if I make change in the first sheet.

问题描述

嗨朋友们，



我有一个将行附加到现有excel文件的方法。我面临的问题是，我的excel文件有两张纸，当我尝试更新第一张纸时，第二张纸会自动填充。所以基本上无论我在表1中所做的更改都会自动反映在表2中。无法找出根本原因。请帮忙。以下是我正在使用的两种方法

Hi Friends,

I have a method which will append the rows to the existing excel file. The problem which i am facing is, my excel file have two sheets, when i try to update the first sheet second sheet get automatically populated. So basically whatever the changes i made in sheet 1 get automatically reflected in sheet 2. Not able to figure out the root cause. please help. Below are the two methods i am using

public static void AddToWorksheetPart(WorksheetPart worksheetPart, WorkbookPart wbPart, DataTable data, int startRow)
       {

          // Stylesheet stylesheet = wbPart.WorkbookStylesPart.Stylesheet;

           // Get Worksheet object
           Worksheet worksheet = worksheetPart.Worksheet;

           // Get SheetData object for worksheet
           SheetData sheetData = worksheet.Descendants<SheetData>().FirstOrDefault();

           // Set starting row for adding data to template
           UInt32Value rowIndex = (UInt32Value)((UInt32)startRow);



           Row row1 = new Row()
           {
               RowIndex = rowIndex++,
               Spans = new ListValue<StringValue>() { InnerText = "1:3" },
               DyDescent = 0.25D
           };

           for (int columnIndex = 0; columnIndex < data.Columns.Count; columnIndex++)
           {
               Cell cell = new Cell() { CellReference = ColumnCaption.Instance.Get((Convert.ToInt32((UInt32)rowIndex) - 2), columnIndex) };
               CellValue cellValue = new CellValue();

               if (columnIndex == 0)
               {
                   cell.DataType = CellValues.String;
               }
               else
               {
                   cell.DataType = CellValues.Number;
               }

               cellValue.Text = data.Columns[columnIndex].ColumnName.ToString().FormatCode();
               cell.Append(cellValue);

               row1.Append(cell);
           }
           sheetData.Append(row1);


           // Iterate through rows of DataTable
           for (int rIndex = 0; rIndex < data.Rows.Count; rIndex++)
           {
               // Create Open XML row object
               Row row = new Row()
               {
                   RowIndex = rowIndex++,
                   Spans = new ListValue<StringValue>() { InnerText = "1:3" },
                   DyDescent = 0.25D
               };

               // Iterate through columns of DataTable
               for (int cIndex = 0; cIndex < data.Columns.Count; cIndex++)
               {
                   if (cIndex == 0)
                   {
                       // Add new cell to the Row object
                       Cell cell = new Cell()
                       {
                           CellReference = ColumnCaption.Instance.Get((Convert.ToInt32((UInt32)rowIndex) - 2), cIndex),
                           DataType = CellValues.String

                       };

                       // Set value to Cell object from DataTable[Row][Column] using CellValue type
                       CellValue cellValue = new CellValue();
                       if (!String.IsNullOrEmpty(data.Rows[rIndex][cIndex].ToString()))
                           cellValue.Text = data.Rows[rIndex][cIndex].ToString();
                       else
                           cellValue.Text = "NULL";

                       cell.Append(cellValue);

                       row.Append(cell);

                   }
                   else
                   {
                       // Add new cell to the Row object
                       Cell cell = new Cell()
                       {
                           CellReference = ColumnCaption.Instance.Get((Convert.ToInt32((UInt32)rowIndex) - 2), cIndex),
                           DataType = CellValues.Number

                       };

                       // Set value to Cell object from DataTable[Row][Column] using CellValue type
                       CellValue cellValue = new CellValue();
                       // cellValue.Text = data.Rows[rIndex][cIndex].ToString();
                       if (!String.IsNullOrEmpty(data.Rows[rIndex][cIndex].ToString()))
                           cellValue.Text = data.Rows[rIndex][cIndex].ToString();
                       else
                           cellValue.Text = "NULL";
                       cell.Append(cellValue);
                       row.Append(cell);
                   }
               }
               // Add Row object to SheetData and Worksheet
               sheetData.Append(row);
           }
       }

public static void AddToExcelParts_Inter(SpreadsheetDocument spreadsheetDoc, DataTable data, int startRow, string sheetName)

 {
     // Get WorkbookPart object for document
     WorkbookPart workbookPart = spreadsheetDoc.WorkbookPart;
     WorksheetPart worksheetPart = GetWorksheetFromSheetName(workbookPart, sheetName);
     // Call method to insert data
      AddToWorksheetPart(workbookPart.WorksheetParts.ElementAt(0),workbookPart, data, startRow);

     // Save the WorkbookPart and document
      workbookPart.Workbook.Save();
 }

推荐答案

您应该学习尽快使用调试器。而不是猜测你的代码在做什么，现在是时候看到你的代码执行并确保它完成你期望的。



调试器允许你跟踪执行逐行检查变量，你会看到它有一个停止做你期望的点。

调试器 - 维基百科，免费的百科全书 [ ^ ]

掌握Visual Studio 2010中的调试 - A初学者指南 [ ^ ]



在添加行的例程开头添加断点。查看调用例程的时间（以及工作表）。

设置一些 WhatchPoint 来跟踪每个工作表的行数。此功能允许您跟踪公式;公式应该类似于 WorkBook.WorkSheet（1）.Rows.Count 和 WorkBook.WorkSheet（2）.Rows.Count 。

并查看出现问题的时间。

You should learn to use the debugger as soon as possible. Rather than guessing what your code is doing, It is time to see your code executing and ensuring that it does what you expect.

The debugger allow you to follow the execution line by line, inspect variables and you will see that there is a point where it stop doing what you expect.
Debugger - Wikipedia, the free encyclopedia[^]
Mastering Debugging in Visual Studio 2010 - A Beginner's Guide[^]

Put a Breakpoint at beginning of the routine that add rows. see how many time your routine is called (and for which WorkSheet).
Set some WhatchPoint to track the number of rows of each WorkSheet. This feature allow you to track a formula; The formulas should look like WorkBook.WorkSheet(1).Rows.Count and WorkBook.WorkSheet(2).Rows.Count .
And see when things go wrong.

这篇关于如果我在第一张表中进行更改，则会更新多个工作表。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！