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.
Unfortunately, activation email could not send to your email. Please try again.

GridToExcel/Formula Cell question

Thread ID:

Created:

Updated:

Platform:

Replies:

90272 Sep 23,2009 05:04 PM Nov 11,2009 05:05 AM Windows Forms 9
loading
Tags: GridControl
Philip Bishop
Asked On September 23, 2009 05:04 PM

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 [Syncfusion]
Replied On September 24, 2009 05:21 AM

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
Replied On September 24, 2009 08:50 AM

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 [Syncfusion]
Replied On September 25, 2009 09:25 AM

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
Replied On September 25, 2009 11:50 AM

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
Replied On September 25, 2009 12:33 PM

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
Replied On September 29, 2009 09:10 AM

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 [Syncfusion]
Replied On September 30, 2009 11:21 AM

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
Replied On November 10, 2009 04:35 PM

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 [Syncfusion]
Replied On November 11, 2009 05:05 AM

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.

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.

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.

;