ExcelCell Number Format
GemBox.Spreadsheet is a .NET component that enables developers to read, write, convert and print spreadsheet files (XLSX, XLS, ODS, CSV, HTML, PDF or XPS) from their .NET applications using one simple API. GemBox.Spreadsheet Free is free of charge while GemBox.Spreadsheet Professional is a commercial version licensed per developer.
Following example demonstrates how you can use GemBox.Spreadsheet to get formatted cell value. Formatted cell value is a textual representation of a cell value which is formatted with a number format applied on that cell.
Using this example you can also find out how specific number format from MS Excel should be used in GemBox.Spreadsheet API. Create new Excel file with MS Excel and populate its A2 cell in the first worksheet with a value and number format. Use that file as input to this example ('Upload your file' below) and run the example. Use value from output file's D column in GemBox.Spreadsheet API.
Screenshot
See the full code below, use RUN EXAMPLE to execute.
1using GemBox.Spreadsheet;
2using System;
3using System.IO;
4
5class Sample
6{
7 [STAThread]
8 static void Main(string[] args)
9 {
10 // If using Professional version, put your serial key below.
11 SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");
12
13 ExcelFile ef = ExcelFile.Load("NumberFormat.xlsx");
14
15 var ws = ef.Worksheets[0];
16
17 ws.Cells[0, 2].Value = "ExcelCell.Value";
18 ws.Columns[2].Style.NumberFormat = "@";
19
20 ws.Cells[0, 3].Value = "CellStyle.NumberFormat";
21 ws.Columns[3].Style.NumberFormat = "@";
22
23 ws.Cells[0, 4].Value = "ExcelCell.GetFormattedValue()";
24 ws.Columns[4].Style.NumberFormat = "@";
25
26 for (int i = 1; i < ws.Rows.Count; i++)
27 {
28 ExcelCell sourceCell = ws.Cells[i, 0];
29
30 ws.Cells[i, 2].Value = sourceCell.Value == null ? null : sourceCell.Value.ToString();
31 ws.Cells[i, 3].Value = sourceCell.Style.NumberFormat;
32 ws.Cells[i, 4].Value = sourceCell.GetFormattedValue();
33 }
34
35 // Auto-fit columns
36 for (int i = 0; i < 5; i++)
37 ws.Columns[i].AutoFit();
38
39 ef.Save("Number Format.xls");
40 }
41}
1Imports GemBox.Spreadsheet
2Imports System.IO
3
4Module Samples
5
6 Sub Main()
7
8 ' If using Professional version, put your serial key below.
9 SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY")
10
11 Dim ef As ExcelFile = ExcelFile.Load("NumberFormat.xlsx")
12
13 Dim ws = ef.Worksheets(0)
14
15 ws.Cells(0, 2).Value = "ExcelCell.Value"
16 ws.Columns(2).Style.NumberFormat = "@"
17
18 ws.Cells(0, 3).Value = "CellStyle.NumberFormat"
19 ws.Columns(3).Style.NumberFormat = "@"
20
21 ws.Cells(0, 4).Value = "ExcelCell.GetFormattedValue()"
22 ws.Columns(4).Style.NumberFormat = "@"
23
24 For i As Integer = 0 To ws.Rows.Count
25 Dim sourceCell = ws.Cells(i, 0)
26
27 If (sourceCell.Value IsNot Nothing) Then
28 ws.Cells(i, 2).Value = sourceCell.Value.ToString()
29 End If
30
31 ws.Cells(i, 3).Value = sourceCell.Style.NumberFormat
32 ws.Cells(i, 4).Value = sourceCell.GetFormattedValue()
33 Next
34
35 ' Auto-fit columns
36 For i As Integer = 0 To 4
37 ws.Columns(i).AutoFit()
38 Next
39
40 ef.Save("Number Format.xls")
41
42 End Sub
43
44End Module
Check next sample or find out more about GemBox.Spreadsheet and GemBox Software.