Ignore "Number Stored as Text" error

Ignore "Number Stored as Text" error

Posted by: srg13 on 2 May 2023, 7:22 am EST

• 

  srg13

  • Post Options:
  • Link
    Copy

  Posted 2 May 2023, 7:22 am EST

  Hi,
  
  I have created a excel using Documents for Excel, .NET Edition library. I have a text column where some rows have numerical values. I am getting the error “Number Stored as Text” for those cells. Can you please suggest any settings to ignore this error, so it doesn’t appear in the final spreadsheet?

  Thanks

• 

  prabhat.sharma

  

  • Post Options:
  • Link
    Copy

  Posted 3 May 2023, 2:48 am EST

  Hello Sushant,

  To further investigate this we will be needing the code snippet to see how you are populating the data or a stripped-down sample showing it.

  Regards,
  
  Prabhat Sharma.

• 

  srg13

  • Post Options:
  • Link
    Copy

  Posted 3 May 2023, 11:51 am EST

  I am getting data from the DB. The requirement is to have the number format of that column set to General. The values coming from the DB are a mix of strings and numbers.
  
  I am iterating through the values coming from the DB and writing to the cell using below code

  foreach(var cellValue in Datatable)
  
  {
  
  range.Value = cellValue;
  
  range.NumberFormat = “General”;
  
  }

• 

  prabhat.sharma

  

  • Post Options:
  • Link
    Copy

  Posted 4 May 2023, 1:25 am EST - Updated 4 May 2023, 1:30 am EST

  Hello Sushant,

  We do not face the issue by using your given code snippet. Please see the attached GIF and sample demonstrating the same.

  If you are doing anything different, please let us know and modify the sample accordingly.

  Regards,
  
  Prabhat Sharma.
  
  Number_Stored_as_Text.zip

• 

  srg13

  • Post Options:
  • Link
    Copy

  Posted 4 May 2023, 5:40 am EST

  Thanks for sharing the code. I downloaded and executed in our environment and i am getting that prompt. It comes for the records that are in string format (“12”, “12.7” and “Temp”) and not for the numbers.
  
  I have used Aspose and openXML libraries in the past and found the options there to ignore this error prompt.
  
  In Aspose, I had used below code

  	ErrorCheckOptionCollection opts = sheet.ErrorCheckOptions;
          int index = opts.Add();
          ErrorCheckOption opt = opts[index];
          // Disable the numbers stored as text option
          opt.SetErrorCheck(ErrorCheckType.TextNumber, false);

  Can you please let me know if something equivalent is available in Grapecity Documents for Excel?

  reference to Aspose page - https://docs.aspose.com/cells/net/use-error-checking-options/

• 

  prabhat.sharma

  

  • Post Options:
  • Link
    Copy

  Posted 4 May 2023, 11:17 pm EST

  Hello Sushant,

  Apologies but as of now, there is no way to suppress this error using GrapeCity as this error-checking option is controlled by Excel Application.

  We are discussing this with the development team to see if we can control it somehow. We will update you as soon as we get the update from the developers’ end.
  
  [Internal Tracking ID: DOCXLS-8042]

  Regards,
  
  Prabhat Sharma.

• 

  prabhat.sharma

  

  • Post Options:
  • Link
    Copy

  Posted 30 May 2023, 3:50 am EST

  Hello Sushant,

  Apologies for the delay in response.
  
  You can set the AutoParse property of the Workbook to True to parse the String values to Numeric data.
  
  Please find the attached sample demonstrating the same:

  Workbook wb = new Workbook();
  IWorksheet sheet = wb.Worksheets[0];
  DataTable dt = new DataTable();
  dt.Columns.Add("Col 1", typeof(Object));
  dt.Rows.Add(new object[] { "12" });
  dt.Rows.Add(new object[] { 35 });
  dt.Rows.Add(new object[] { "12.7" });
  dt.Rows.Add(new object[] { "Temp" });
  dt.Rows.Add(new object[] { 15.2 });
  IRange range = sheet.Range["A1:A5"];
  int i = 0;
  foreach (DataRow row in dt.Rows)
  {
      range.Cells[i].Value = row[0];
      range.Cells[i].NumberFormat = "General";
      i = i + 1;
  }
  wb.AutoParse = true;
  wb.Save("Demo.xlsx");

  If you need any other help, please let us know.

  Regards,
  
  Prabhat Sharma.