IndexOutOfRangeException with AddRows in Spread 6

Posted by: carlos-t on 8 September 2017, 2:46 pm EST

  • Posted 8 September 2017, 2:46 pm EST

    1. Suppose you grab a WinForm Spread control in a form (the default row count value is 500)

    2. You create an xml with the Spread Designer and set the row count to 2000

    3. fpSpread1.Open("my.Xml");

    4. fpSpread1.Sheets[0].AddRows(501, 1)   -> ERROR 

     

    IndexOutOfRangeException "Invalid row index specified: 501 (current value of Rows is 500)"

    It fails in version 6.0.3501.2008, but with version 5.0.3518 is OK.

    I attach a small project in Visual Studio 2010 to reproduce the problem.


    2011/10/addRows.zip
  • Replied 8 September 2017, 2:46 pm EST

    We were looking at this and we are having some problem loading your xml file into designer, in looking at the file we realized it was created from an excel file you loaded then saved to xml. We were wondering if you could attach your excel file.
  • Replied 8 September 2017, 2:46 pm EST

    Here is the original .xlsx file, the one I loaded into the designer and then saved it in xml.

     

    Solution so far: 

    fpSpread1.Sheets[0].Open("test_sheetview.xml");  

    fpSpread1.Save("newSpread.xml"false);

    fpSpread1.Open("newSpread.xml");  

    fpSpread1.Sheets[0].AddRows(501, 1);    // Works OK

     

    So loading an xml sheetview is the problem, even saving it and loading it again:

     

    1. Adding rows when loading directly the xml doesn´t work (either the one saved as spread or sheetview) 

    fpSpread1.Sheets[0].Open("test_sheetview.xml"); 

     fpSpread1.Sheets[0].AddRows(501, 1);    // IndexOutOfRangeException

     

    2. If we save the file as sheetview, then load it, it doesn´t work.

     fpSpread1.Sheets[0].Open("test_sheetview.xml");  

    fpSpread1.Sheets[0].Save("new_test_sheetview.xml"false);

    fpSpread1.Sheets[0].Open("new_test_sheetview.xml");  

    fpSpread1.Sheets[0].AddRows(501, 1);  // IndexOutOfRangeException

     



    2011/10/original xlsx file.zip
  • Replied 8 September 2017, 2:46 pm EST

    There seems to be some difference in the way the xml file is being saved between version 5 and version 6 or at least thats a theory we are operating on. I opened your file with this line.

     

    fpSpread1.OpenExcel("C:\\Users\\paul\\Desktop\\original xlsx
    file\\Reporte Global de Suministros por Semana.xlsx"
    );

    set the number of rows with this line

    fpSpread1.ActiveSheet.Rows.Count
    = 2000;

    and save it with this line

    fpSpread1.ActiveSheet.Save("C:\\Users\\paul\\Desktop\\original xlsx
    file\\my.Xml"
    , false);

    I open it with this line

    fpSpread1.ActiveSheet.Open("C:\\Users\\paul\\Desktop\\original xlsx
    file\\my.Xml"
    );

    and add a row with this line.

    fpSpread1.ActiveSheet.AddRows(fpSpread1.Sheets[0].RowCount
    - 1, 1);



    I don't get any errors and it produces a 2001 row spread sheet. Is there any difference between how I created my xml file and you created your's? I'm in the process of trying saving the file with version 5 of the control and opening with version 6 to see if that makes a difference. I'll let you know, if you can think of any differences please let me know.

     







     

  • Replied 8 September 2017, 2:46 pm EST

    I tested this with the latest maintenance release 6.0.3502.2008 and it appeared to work correctly after I clicked my button there were 2001 rows, it did not give me any error.

    using System;

    using System.Collections.Generic;

    using System.ComponentModel;

    using System.Data;

    using System.Drawing;

    using System.Linq;

    using System.Text;

    using System.Windows.Forms;

     

    namespace WindowsFormsApplication16

    {

        public partial class Form1 : Form

        {

            public
    Form1()

            {

                InitializeComponent();

     

                fpSpread1.ActiveSheet.Rows.Count =
    2000;

                fpSpread1.Save("my.Xml", false);



                  }

            private
    void button1_Click(object
    sender, EventArgs e)

            {

                fpSpread1.Open("my.Xml");

                fpSpread1.Sheets[0].AddRows(501,
    1);

            }

        }

    }



     



  • Replied 8 September 2017, 2:46 pm EST

    I have version 6.0.3501.2008, where can I get the latest maintanance release 6.0.3502.2008?

    I will try with these version and will let you know.

      

  • Replied 8 September 2017, 2:46 pm EST

    I realize your code is working but it is not what I need. I must load a pre-saved xml and add rows to it, like this...
    I would like to encourage you to check it with the attached files. 
     
    Thanks, 
     
    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Windows.Forms;
    using FarPoint.Win.Spread.Model;

    namespace addRows
    {
        public partial class Form1 : Form
        {
            public Form1()
            {
                InitializeComponent();
            }

            private void Form1_Load(object sender, EventArgs e)
            {
                string currentDir = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location);
                string xmlPathSheetview = System.IO.Path.Combine(currentDir, "test_sheetview.xml");

                fpSpread1.Sheets[0].Open(xmlPathSheetview); // Load xml with 2000 rows
            }

            private void button1_Click(object sender, EventArgs e)
            {
                // ERROR  -  - This works in Spread v5.0.3518, but not in v6.0.3501.2008
                // --------------------------------------------------------------------------
                // IndexOutOfRangeException "Invalid row index specified: 1999 (current value of Rows is 500)"
               
        fpSpread1.Sheets[0].AddRows(fpSpread1.Sheets[0].RowCount - 1, 1); 
            }
        }
    }

  • Replied 8 September 2017, 2:46 pm EST

    Hello,

    I was able to replicate the issue at my end and it seems to be a bug. I have reported the issue to the development team, the bug number for the same is #99923363. It would be fixed in the next maintenance release.

    Thanks,

    Manpreet Kaur.

  • Replied 8 September 2017, 2:46 pm EST

    When I open your excel file and save the xml file using version 5.0.3518.2008 then open the xml file and add a row using version 6.0.3502.2008 i get the same error you do. the quick work around for you is to open your excel file and save it to xml using version 6 but this is a defect and it will be reported and fixed

  • Replied 8 September 2017, 2:46 pm EST

    It makes sense because I loaded the excel file with the Spread Designer and saved it presumably in version 5.0.3518.2008. Then I opened it and tried to add the row using version 6.

    After saving the file with version 6 solves the problem here as well, so this is what I will do.

    There seems to be a different way of saving the files in between those versions.

     Thanks for the help, Paul.

    Carlos 

     

Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels