GridToExcel/Formula Cell question

[Philip Bishop]

I've been messing with GridToExcel today and it seems to work okay for me except when I have a column of formula cells. So for example I have a grid that is 99 cols by 364 rows. The 99th col has a formula in each cell that totals up each col in a row. When I try and export to excel using the following code it blows up. Here is the code...


onverter1.GridToExcel(Me.grdGrossOrder, saveFileDialog.FileName, Syncfusion.GridExcelConverter.ConverterOptions.ColumnHeaders Or Syncfusion.GridExcelConverter.ConverterOptions.RowHeaders)

Here is the error message...

Can't parse formula: a1:W1

Is there something else I need to do so that it's not trying to grab the formula and instead just get the result of the formula from that cell?

Thanks

Phil

[Lingaraj S]

Hi Phil,

Thank you for your interest in Syncfusion products.

I am afraid that I am unable to reproduce the mentioned issue in GridControl, when the GridControl formula cell value is exported to Excel.

I have created a simple sample to test this issue and it is available in the following link.
http://files.syncfusion.com/support/samples/Grid.Windows/7.3.0.20/Forums/GridFormulExport.zip

If you don't want to Export the formula in Excel, then please try using below way to achieve this functionality:

Syncfusion.GridExcelConverter.GridExcelConverterControl gecc = new Syncfusion.GridExcelConverter.GridExcelConverterControl();
// If you dont want to Export the Formula please try to use below event
gecc.QueryImportExportCellInfo += new Syncfusion.GridExcelConverter.GridImportExportCellInfoEventHandler(gecc_QueryImportExportCellInfo);
void gecc_QueryImportExportCellInfo(object sender, Syncfusion.GridExcelConverter.GridImportExportCellInfoEventArgs e)
{
if (e.GridCell.CellType == "FormulaCell")
{
e.ExcelCell.Value = e.GridCell.FormattedText;
e.Handled = true;
}
}

Please let me know if you still face the issue, please try to reproduce the issue in above sample or send a reproducing sample, ti could be helpful for us to analyze the issue.

Regards,
Lingaraj S.

[Philip Bishop]

I can't make your sample work. It's complaining that it can't find GridImportExportCellInfoEventArgs and not being in the namespace. Can you send me a VB sample? Also could you try your test using 4.2.0.37 of your grid? That's the version we are using.

Thanks

Phil

[Lingaraj S]

Hi Phil,

Thank you for the update.

The GridImportExportCellInfoEventHandler event is not included in version 4.2.0.37, so please try to upgrade our latest version to achieve above behavior in Excel Export.

Also we are not able see the actual issue in ExcelExport with mentioned version 4.2.0.37. Now, we have attached a VB sample in below link:
http://files.syncfusion.com/support/samples/Grid.Windows/7.3.0.20/Forums/GridFormula.zip

Please let me know if you still face the issues. Please try to reproduce the issue in above sample, so that we could find out the issue and provide a better solution for you.

Regards,
Lingaraj S.

[Philip Bishop]

We can't go to the new version yet because of some bugs it has where things don't work the same as they do in version 4. They are in the process of being fixed by you now.

I tried your sample and yes yours works. I then added our grid to your form and our code to load it and then the export blows up. Maybe this will help you.





GCControl_8e781a0d.zip

[Philip Bishop]

I had one other question you can answer when you answer the previous post of my where I sent a sample. Does the new version of the grid once we do go to it..Does it then allow you to export to the xlxs extension for the new excel?

[Philip Bishop]

Just wondered if you had any thoughts on my last two posts on this. I hadn't heard anything in a couple of days.

[Lingaraj S]

Hi Phil,

My apologizes for delayed response.

We are able to the issue in GridControl when it is exported to Excel in version 4.2.0.37, the issue has been resolved in our latest version. Also you can Export the GridControl to ".xlxs" file in our latest version.

Please let me know if you have any other concerns.

Regards,
Lingaraj S.

[Philip Bishop]

I have one other question about this. So when you say the new version will allow me to save as xlxs, does that mean I can save to it and xls? Can the user choose?

[Lingaraj S]

Hi Phil,

Thank you for the update.

ExcelConverter doesn't provide any option to set the excel version but the ExcelEngine support this. Without setting excel version, XlsIO saves the file as ".xls" by default. If you need ".xlsx" format in Export, create an excel application using ExcelEngine and set version through ExcelVersion property before exporting to excel. As shown below.

Refer the code below to Export with ".xlsx" format:

Dim gecc As New Syncfusion.GridExcelConverter.GridExcelConverterControl()
Dim engine As New Syncfusion.XlsIO.ExcelEngine()
Dim app As Syncfusion.XlsIO.IApplication = engine.Excel.Application
app.DefaultVersion = Syncfusion.XlsIO.ExcelVersion.Excel2007
Dim book As Syncfusion.XlsIO.IWorkbook = app.Workbooks.Create()
gecc.GridToExcel(Me.gridControl1.Model, book.Worksheets(0))
book.SaveAs(saveFileDialog.FileName)
book.Close()
engine.Dispose()

Please let me know if you have any queries.

Regards,
Lingaraj S.