saving and loading a grid to an excel file

[Administrator]

I am using the routines in the attached file to try and save / load a 2-sheet workbook to an excel file. I am doing it this way because I couldn't figure out how to handle the named ranges. (I kept getting error messages about the named ranges not being of the correct format/type or something.) Anyway, I thought this should work and it does save the file correctly. But when I try to load a file where 'grid' has an equation in it, it doesn't load properly. A call to sheet.Columns.Count() returns the wrong number of columns. The grid in question was sparsely populated, with data/equations only in one column of 5 columns in the sheet. I assume I am doing this incorrectly. Can you point out how I should do it?



save and load routines_cd5ca6c6.zip

[Administrator]

The specific error that I get when trying to use the gridExcelConverter is: "ParseException was unhandled. xxx is not a valid named range." In the test case that I used, the variable in question was 'foobar' which was defined as a named range with a value of 'foo + bar' where 'foo' was a named range with a value of 3, and 'bar' was a named range with a value of 4. I have attached a copy of the project with the save function edited to show how I was trying to use the excelGridConverter, in case that helps.



WindowsFormsApplication1_a3b803ed.zip

[Christopher Issac Sunder K]

Hi Jan,

Thank you for your interest in Syncfusion Products.

Regret for the delayed response. Please try using RegisterGridAsSheet and AddNamedRange to achieve your requirements as shown below.

//loadBtn click
  private void loadBtn_Click(object sender, EventArgs e)
        {
            openDlg = new OpenFileDialog();
            DialogResult dr = openDlg.ShowDialog();
            if (dr == DialogResult.OK)
            {
                Syncfusion.GridExcelConverter.GridExcelConverterControl gecc = new Syncfusion.GridExcelConverter.GridExcelConverterControl();
                gecc.QueryImportExportCellInfo += new Syncfusion.GridExcelConverter.GridImportExportCellInfoEventHandler(gecc_QueryImportExportCellInfo);
                gecc.ExcelToGrid(openDlg.FileName, varGrid.Model);
            }
        }

//In  QueryImportExportCellInfo event
    void gecc_QueryImportExportCellInfo(object sender, Syncfusion.GridExcelConverter.GridImportExportCellInfoEventArgs e)
        {
            if (e.Action == Syncfusion.GridExcelConverter.GridConverterAction.Import)
            {
                ExcelEngine excelEngine = new ExcelEngine();
                IApplication application = excelEngine.Excel;
                IWorkbook workbook = application.Workbooks.Open(openDlg.FileName);
                varGrid.TableStyle.CellType = "FormulaCell";
                GridFormulaEngine engine;
                int sheetFamilyID = GridFormulaEngine.CreateSheetFamilyID();
                GridFormulaEngine.RegisterGridAsSheet("Sheet1", this.varGrid.Model, sheetFamilyID);

                engine = ((GridFormulaCellModel)varGrid.Model.CellModels["FormulaCell"]).Engine;
                
                INames Names = workbook.Names;//Collect NamedRanges from Excel workbook
                foreach (IName nam in Names)
                {
                    engine.AddNamedRange(nam.Name.ToUpper(), nam.Value.Replace("'", ""));//Add NamedRanges to GridControl
                }
                workbook.Close();
                excelEngine.Dispose();
            }
        }

//In SaveBtn Click

      private void saveBtn_Click(object sender, EventArgs e)
        {
            saveDlg = new SaveFileDialog();
            DialogResult dr = saveDlg.ShowDialog();
            if (dr == DialogResult.OK)
            {

    //other codes…

    Hashtable ranges = new Hashtable();
                ranges = engine.NamedRanges;//Collect NamedRanges from GridControl;
                foreach (string key in ranges.Keys)
                {
                    IName name = sheet0.Names.Add(key);
                    name.RefersToRange = sheet0.Range[ranges[key].ToString()];
                   sheet0.Range[ranges[key].ToString()].Value = name.Value; // assign named ranges to excel
                }
                Syncfusion.GridExcelConverter.GridExcelConverterControl gecc = new Syncfusion.GridExcelConverter.GridExcelConverterControl();
                gecc.ExportRange(GridRangeInfo.Table(), varGrid.Model, sheet0, Syncfusion.GridExcelConverter.ConverterOptions.Default);

//other codes..
            }
        }

Please refer the modified sample which illustrates the above.

http://www.syncfusion.com/uploads/redirect.aspx?&team=support&file=WindowsFormsApplication1-507481537.zip

Please let me know if you have any further concerns.

Regards,
Christopher K.

[Administrator]

Thank you for the ideas. I tried compiling your uploaded sample. It didn't work either. (It didn't allow me to define the variable in the variables grid. Oh well... I have a crude work around. I guess I will just have to use that.

v/r
Jan

[Christopher Issac Sunder K]

Hi Jan,

Thanks for the patience.

Regret for the inconvenience caused. Please refer the modified sample from following link.

http://www.syncfusion.com/uploads/redirect.aspx?&team=support&file=WindowsFormsApplication1_runnable1935076419.zip

Please let me know if you have any other concerns.

Regards,
Christopher K.