#VALUE error when importing manually edited excel file

[Ahmed]

Hello everyone,

I'm using XlsIO for exporting my SfDataGrid records to an Excel file and it is OK.

Next, I'm creating an import void to import the previously exported Excel file and all is OK.

The problem is, however, when I edit the exported file (e.g. add rows, edit rows, copy and paste from other Excel or any source) then when I re-import the file I have a #VALUE error in some rows even if the Copy & Paste used to edit the exported file is done using plain text paste!!!

I'm really stuck and I'm on a near deadline of a project and need the reply very fast indeed..

Below are my export and Import voids:

Export:

var options = new ExcelExportingOptions();
                options.ExportMode = ExportMode.Text;
                options.ExcludeColumns.Add("Action");
                options.ExcludeColumns.Add("Action2");
                var excelEngine = DgEmails.ExportToExcel(DgEmails.View, options);
                var workBook = excelEngine.Excel.Workbooks[0];
                workBook.ActiveSheet.UsedRange.AutofitColumns();
                workBook.ActiveSheet.UsedRange.NumberFormat = "@";
                workBook.ActiveSheet.Rows[0].CellStyle.Color = Color.Gray;
                workBook.ActiveSheet.Rows[0].CellStyle.Font.Color = ExcelKnownColors.White;
                workBook.ActiveSheet.Rows[0].CellStyle.Font.Bold = true;

                try
                {
                    workBook.SaveAs(dlg.FileName);
                    MessageBox.Show(this, "تم حفظ الملف بنجاح.", "تصدير قائمة البريد", Msgs.btnOk, Msgs.icnInf, Msgs.defBtn1, Msgs.opt);
                }
                catch (Exception ex)
                {
                    if (ex.Message.Contains("cannot access the file"))
                    {
                        MessageBox.Show(this, "الملف المحدد قيد الاستخدام، قم بإغلاق الملف المحدد أو قم بتغيير اسم الملف.", "خطأ أثناء التصدير", Msgs.btnOk, Msgs.icnErr, Msgs.defBtn1, Msgs.opt);
                    }
                }
                workBook.Close();
                excelEngine.Dispose();

Import ----------------------
ExcelEngine excelEngine = new ExcelEngine();
                IApplication application = excelEngine.Excel;
                application.DefaultVersion = ExcelVersion.Excel2013;
                IWorkbook xls = application.Workbooks.Open(dlg.FileName);
                
            //DataTable dt = xls.ActiveSheet.ExportData
                //MessageBox.Show(dt.Columns[0].ColumnName);
                //DsEmails.Tables["StoresEmails"].Merge(dt);

                for (int i = 1; i < xls.ActiveSheet.Rows.Count() - 1; i++)
                {

                    DsEmails.Tables["StoresEmails"].Rows.Add(
                    xls.ActiveSheet.GetText(i,0),
                      xls.ActiveSheet.GetText(i, 1),
                     xls.ActiveSheet.GetText(i, 2),
                     xls.ActiveSheet.GetText(i, 3)
                     );
                }

                DsEmails.AcceptChanges();

                xls.Close();
                excelEngine.Dispose();

Attachment: JupiterEmailsList2510202015157_6f16f2b4.zip

[Konduru Keerthi Konduru Ravichandra Raju]

Hi Ahmed, 

Greetings from Syncfusion. 

The Excel document you have shared us in seems to be corrupted. We request you to share the issue reproducing sample along with the Excel document, to investigate the query and provide prompt solution at the earliest. 

Also, please confirm the Syncfusion XlsIO version you are using at your end. 

Regards, 

Keerthi. 

[Ahmed]

Hi Ahmed, 

Greetings from Syncfusion. 

The Excel document you have shared us in seems to be corrupted. We request you to share the issue reproducing sample along with the Excel document, to investigate the query and provide prompt solution at the earliest. 

Also, please confirm the Syncfusion XlsIO version you are using at your end. 

Regards, 

Keerthi. 

1- The Excel file is not the problem, however, I've attached a new one.

2- XlsIO version is "18.3460.0.35".

2- In the Excel file these cells (A4, A5, A6, A8) are pasted (as plain text) from another Excel file and they are causing the #VALUE error when I read them.

3- Below is my code:

var options = new ExcelExportingOptions();
                options.ExportMode = ExportMode.Text;
                options.ExcludeColumns.Add("Action");
                options.ExcludeColumns.Add("Action2");
                var excelEngine = DgEmails.ExportToExcel(DgEmails.View, options);
                var workBook = excelEngine.Excel.Workbooks[0];
                workBook.ActiveSheet.UsedRange.AutofitColumns();
                workBook.ActiveSheet.UsedRange.NumberFormat = "@";
                workBook.ActiveSheet.Rows[0].CellStyle.Color = Color.Gray;
                workBook.ActiveSheet.Rows[0].CellStyle.Font.Color = ExcelKnownColors.White;
                workBook.ActiveSheet.Rows[0].CellStyle.Font.Bold = true;

                try
                {
                    workBook.SaveAs(dlg.FileName);
                    MessageBox.Show(this, "تم حفظ الملف بنجاح.", "تصدير قائمة البريد", Msgs.btnOk, Msgs.icnInf, Msgs.defBtn1, Msgs.opt);
                }
                catch (Exception ex)
                {
                    if (ex.Message.Contains("cannot access the file"))
                    {
                        MessageBox.Show(this, "الملف المحدد قيد الاستخدام، قم بإغلاق الملف المحدد أو قم بتغيير اسم الملف.", "خطأ أثناء التصدير", Msgs.btnOk, Msgs.icnErr, Msgs.defBtn1, Msgs.opt);
                    }
                }
                workBook.Close();
                excelEngine.Dispose();

Import ----------------------
ExcelEngine excelEngine = new ExcelEngine();
                IApplication application = excelEngine.Excel;
                application.DefaultVersion = ExcelVersion.Excel2013;
                IWorkbook xls = application.Workbooks.Open(dlg.FileName);
  

                for (int i = 1; i < xls.ActiveSheet.Rows.Count() - 1; i++)
                {

                    DsEmails.Tables["StoresEmails"].Rows.Add(
                    xls.ActiveSheet.GetText(i,0),
                      xls.ActiveSheet.GetText(i, 1),
                     xls.ActiveSheet.GetText(i, 2),
                     xls.ActiveSheet.GetText(i, 3)
                     );
                }

                DsEmails.AcceptChanges();

                xls.Close();
                excelEngine.Dispose();

Attachment: JupiterEmailsList_94334f89.rar

[Konduru Keerthi Konduru Ravichandra Raju]

Hi Ahmed, 

Thanks for sharing the requested details and the Excel document. 

We are able to reproduce the #VALUE! Error while trying to retrieve the values of cells A4,A5,A6 and A8. We are validating this query currently and will share the details on October 28^th,2020. 

Regards, 

Keerthi. 

[Ahmed]

Hi Ahmed, 

Thanks for sharing the requested details and the Excel document. 

We are able to reproduce the #VALUE! Error while trying to retrieve the values of cells A4,A5,A6 and A8. We are validating this query currently and will share the details on October 28^th,2020. 

Regards, 

Keerthi. 

Thanks for your support.

I really appreciate this, and waiting for your reply.

[Shamini Kiruba Sobers]

Hi Ahmed, 

Thanks for the patience. 

We have validated the scenario and found that the cells A4, A5, A6, and A8 are using cards which is not supported in XlsIO. Currently, we do not have any plans to implement this feature. So we suggest you to remove the cards from the cells and use normal cell values. 

Regards, 

Shamini 

[Ahmed]

Hi Ahmed, 

Thanks for the patience. 

We have validated the scenario and found that the cells A4, A5, A6, and A8 are using cards which is not supported in XlsIO. Currently, we do not have any plans to implement this feature. So we suggest you to remove the cards from the cells and use normal cell values. 

Regards, 

Shamini 

Cards?!!

Whatever, I'm pasting as a plain text that pastes only the text of the copied cell.

[Konduru Keerthi Konduru Ravichandra Raju]

Hi Ahmed, 

Thanks for the update. 

As mentioned in our previous update, the cells A4, A5, A6, and A8 are using cards which is not supported in XlsIO. Please look into the following video screenshot.  

Video Screenshot: https://www.syncfusion.com/downloads/support/forum/159085/ze/SCREEN~1-842275736.zip  

As this this cards feature is not supported by Syncfusion XlsIO, the cell value is returned as #VALUE! Kindly let us know if this information helps. 

Regards, 

Keerthi. 

[Ahmed]

Thanks for your reply.

But I didn't add any cards, jus copy & paste..

How colud that happen?

And is there any way to remove cards from XlsIO code?

[Konduru Keerthi Konduru Ravichandra Raju]

Hi Ahmed, 

Thanks for the update. 

As Syncfusion XlsIO do not have support for cards in Excel cell, it is not possible to remove them as well. We suggest you to create a new Excel document and copy the required content into this new document, to overcome the issue. 

Regards, 

Keerthi.