Hi,
Is there a way to avoid using scientific notation on a large numeric value when using smart marker?
Let’s say if my data from the data source includes a large numeric value (e.g. “123456789000”), when I process the workbook to import the data from the data source, I want to see the exact number and not 1.23457E+11.
To avoid scientific notation for large numeric values when using smart markers in Aspose.Cells, you can format the cell to display the number as text. This can be achieved by prefixing the number with a single quote (') in your data source, or by setting the cell format programmatically.
Here’s a simple example of how to set the format in your code:
// Load your workbook
var workbook = new Workbook("yourfile.xlsx");
// Access the first worksheet
var worksheet = workbook.Worksheets[0];
// Set the format of the cell to text
worksheet.Cells["A1"].SetStyle(new Style() { Number = 49 }); // 49 is the code for text format
// Now import your data with smart markers
workbook.Save("output.xlsx");
If you are importing data from a data source, ensure that the large numeric values are treated as strings in the source itself. This way, they will be displayed correctly without scientific notation.
For further information, please consult the official Aspose.Cells documentation on smart markers and cell formatting.
Well, Aspose.Cells follows MS Excel standards, so in MS Excel worksheet, if you input the value “123456789000” into a cell manually, the value would be converted to scientific notations i.e.,
“1.23457E+11”. It is expected behavior of Aspose.Cells when processing Smart Markers and pasting/filling values. I think for your needs, you got to apply the numeric formatting, e.g. you may specify the custom formatting i.e. “0” and set the style for the cells in the columns after data is inserted based on processed markers. You may even specify the style for the whole column cells in one go (see the document for your reference). Now when you read/view the value, it will give you your desired results, see the sample code for your reference:
Workbook wb = new Workbook("Book1.xlsx");
Worksheet sheet = wb.getWorksheets().get(0);
Style style = sheet.getCells().get("A1").getStyle();
style.setCustom("0");
sheet.getCells().get("A1").setStyle(style);
System.out.println(sheet.getCells().get("A1").getStringValue());
...
Also, see the document on numbers formatting for your reference. Number Settings|Documentation
Hope, this helps a bit.