We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy. (Last updated on: November 16, 2018).
Unfortunately, activation email could not send to your email. Please try again.
Syncfusion Feedback

saving and loading a grid to an excel file

Thread ID:

Created:

Updated:

Platform:

Replies:

94679 May 26,2010 06:49 PM UTC Jun 4,2010 09:29 AM UTC WinForms 4
loading
Tags: GridControl
Administrator [Syncfusion]
Asked On May 26, 2010 06:49 PM UTC

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 [Syncfusion]
Replied On May 26, 2010 07:24 PM UTC

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 [Syncfusion]
Replied On June 2, 2010 01:35 PM UTC

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 [Syncfusion]
Replied On June 2, 2010 05:40 PM UTC

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 [Syncfusion]
Replied On June 4, 2010 09:29 AM UTC

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.

CONFIRMATION

This post will be permanently deleted. Are you sure you want to continue?

Sorry, An error occured while processing your request. Please try again later.

Warning Icon You are using an outdated version of Internet Explorer that may not display all features of this and other websites. Upgrade to Internet Explorer 8 or newer for a better experience.Close Icon

;