Click here to Skip to main content
15,889,216 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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

C#
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);
           }
       }

C#
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();
 }
Posted
Updated 8-May-16 22:15pm
v2

1 solution

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.
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900