Getting Error - Exception thrown: 'System.ArgumentOutOfRangeException' in unvell.ReoGrid.dll

[kunalcjadhav]

Description

I am a new coder in C#, I am using ReoGrid dll to read a text file and load it in a target Excel sheet.
The test text file, I am using has about 400 rows and 74 columns and I am using semicolons as column separators in a text file.
I am getting an error on row 201 of my data, in the section " // Load the data into the worksheet" to load the data part to the grid.

I am creating a console app in .net framework 4.8

---

using System;
using System.Globalization;
using System.IO;
using unvell.ReoGrid;
using unvell.ReoGrid.IO;

namespace LoadTextFiletoExcelSheet
{
internal class Program
{
static void Main(string[] args)
{

        if (args.Length < 3)
        {
            return;
        }

        try
        {
            string inputFilePath = args[0];
            string delimiter = args[1];
            string excelFilePath = args[2];
            string sheetName = args[3];
            string logFolderPath = args[4];
			
            Console.WriteLine("Inputs provided for process as follows:");
            Console.WriteLine("Input File - ", inputFilePath);
            Console.WriteLine("Delimiter - ", delimiter);
            Console.WriteLine("Destination Excel File - ", excelFilePath);
            Console.WriteLine("Destination Sheet Name - ", sheetName);
            Console.WriteLine("Log File Folder - ", logFolderPath);


            // Create a new workbook
            //var workbook = new Workbook();
            var grid = new ReoGridControl();

            // Access or create the specified worksheet
            var worksheet = grid.Worksheets[sheetName] ?? grid.NewWorksheet(sheetName);
            // Read the input file
            string[] lines = File.ReadAllLines(inputFilePath);

            // Specify a culture that treats . as a decimal separator
            CultureInfo culture = new CultureInfo("en-US");

            // Load the data into the worksheet
            for (int i = 0; i < lines.Length; i++)
            {
                string[] values = lines[i].Split(new string[] { delimiter }, StringSplitOptions.None);
                File.AppendAllText(Path.Combine(logFolderPath, "log.txt"), $"Processing line {i} with {values.Length} values.\n");
                for (int j = 0; j < values.Length; j++)
                {
                    if (double.TryParse(values[j], NumberStyles.Any, culture, out double number))
                    {
                        worksheet[i, j] = number;
                    }
                    else
                    {
                        worksheet[i, j] = values[j];
                    }
                }
            }

            // Save the workbook to the Excel fileO
            grid.Save(excelFilePath, unvell.ReoGrid.IO.FileFormat.Excel2007);
            //reoGridControl1.Save("YourFileName.xlsx", unvell.ReoGrid.IO.FileFormat.Excel2007);

            // Log the operation
            File.AppendAllText(Path.Combine(logFolderPath, "log.txt"), $"Data loaded into {sheetName} in {excelFilePath} at {DateTime.Now}\n");

            Console.ReadKey();
        }
        catch (Exception ex)
        {

        }
    }

    static bool HasWriteAccess(string folderPath)
    {
        try
        {
            // Try to create a temporary file
            using (File.Create(Path.Combine(folderPath, Path.GetRandomFileName()), 1, FileOptions.DeleteOnClose))
            {
                // If the file was created successfully, we have write access
                return true;
            }
        }
        catch
        {
            // If an error occurs, we do not have write access
            return false;
        }
    }
}

}

To Reproduce

I am getting an error on row 201 of my data, I am creating a console app in .net framework 4.8

Screenshots

Edition

Environment

Windows 10
OS: English
I would also like to use this tool in Chinese machines

[jingwood]

By default, a new worksheet in ReoGrid is initialized with 200 rows and 100 columns. Given your text file has 400 rows, you'll need to explicitly set the number of rows in your target worksheet to accommodate all the data from your file before loading the data.

worksheet.Resize(400, 74);

[kunalcjadhav]

Yes, this helps, Thank you @jingwood but now I end up in new error in later part of code. While writing data in my target excel file which is .xlsx file that contains Pivot tables using this data. After loading data in Excel Sheet, if I open Excel file Excel complains

If I select Yes, it deletes all existing Pivot tables and only retains 1 blank sheet and target sheet with data in the same.

Does this saving is not possible in ReoGrid, were only data is loaded, or it is related to option used
grid.Save(excelFilePath, unvell.ReoGrid.IO.FileFormat.Excel2007);
is breaking original sheet. Is there any available way out?

Thanks in advance.

[jingwood]

I can't help without more information about the error. If possible can you please share the Excel file?

[Kite-sky]

I use GeoGridControl. The Load () method loads excel file to GeoGridControl control, prompt System. ArgumentOutOfRangeException:"

[Kite-sky]