The sample demonstrates how to set number formatting in an excel workbook.
private void ExcelDocViewer( string fileName )
{
try
{
System.Diagnostics.Process.Start(fileName);
}
catch{}
}
private void btnRun_Click(object sender, System.EventArgs e)
{
Workbook workbook = new Workbook();
Worksheet sheet = workbook.Worksheets[0];
sheet.Range["B1"].Text = "NUMBER FORMATTING";
sheet.Range["B1"].Style.Font.IsBold = true;
sheet.Range["B3"].Text = "0";
sheet.Range["C3"].NumberValue = 1234.5678;
sheet.Range["C3"].NumberFormat = "0";
sheet.Range["B4"].Text = "0.00";
sheet.Range["C4"].NumberValue = 1234.5678;
sheet.Range["C4"].NumberFormat = "0.00";
sheet.Range["B5"].Text = "#,##0.00";
sheet.Range["C5"].NumberValue = 1234.5678;
sheet.Range["C5"].NumberFormat = "#,##0.00";
sheet.Range["B6"].Text = "$#,##0.00";
sheet.Range["C6"].NumberValue = 1234.5678;
sheet.Range["C6"].NumberFormat = "$#,##0.00";
sheet.Range["B7"].Text = "0;[Red]-0";
sheet.Range["C7"].NumberValue = -1234.5678;
sheet.Range["C7"].NumberFormat = "0;[Red]-0";
sheet.Range["B8"].Text = "0.00;[Red]-0.00";
sheet.Range["C8"].NumberValue = -1234.5678;
sheet.Range["C8"].NumberFormat = "0.00;[Red]-0.00";
sheet.Range["B9"].Text = "#,##0;[Red]-#,##0";
sheet.Range["C9"].NumberValue = -1234.5678;
sheet.Range["C9"].NumberFormat = "#,##0;[Red]-#,##0";
sheet.Range["B10"].Text = "#,##0.00;[Red]-#,##0.000";
sheet.Range["C10"].NumberValue = -1234.5678;
sheet.Range["C10"].NumberFormat = "#,##0.00;[Red]-#,##0.00";
sheet.Range["B11"].Text = "0.00E+00";
sheet.Range["C11"].NumberValue = 1234.5678;
sheet.Range["C11"].NumberFormat = "0.00E+00";
sheet.Range["B12"].Text = "0.00%";
sheet.Range["C12"].NumberValue = 1234.5678;
sheet.Range["C12"].NumberFormat = "0.00%";
sheet.Range["B3:B12"].Style.KnownColor = ExcelColors.Gray25Percent;
sheet.AutoFitColumn(2);
sheet.AutoFitColumn(3);
workbook.SaveToFile("Sample.xls");
ExcelDocViewer(workbook.FileName);
}
Private Sub ExcelDocViewer(ByVal fileName As String)
Try
System.Diagnostics.Process.Start(fileName)
Catch
End Try
End Sub
Private Sub btnRun_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnRun.Click
Dim workbook As Workbook = New Workbook()
Dim sheet As Worksheet = workbook.Worksheets(0)
sheet.Range("B1").Text = "NUMBER FORMATTING"
sheet.Range("B1").Style.Font.IsBold = True
sheet.Range("B3").Text = "0"
sheet.Range("C3").NumberValue = 1234.5678
sheet.Range("C3").NumberFormat = "0"
sheet.Range("B4").Text = "0.00"
sheet.Range("C4").NumberValue = 1234.5678
sheet.Range("C4").NumberFormat = "0.00"
sheet.Range("B5").Text = "#,##0.00"
sheet.Range("C5").NumberValue = 1234.5678
sheet.Range("C5").NumberFormat = "#,##0.00"
sheet.Range("B6").Text = "$#,##0.00"
sheet.Range("C6").NumberValue = 1234.5678
sheet.Range("C6").NumberFormat = "$#,##0.00"
sheet.Range("B7").Text = "0;[Red]-0"
sheet.Range("C7").NumberValue = -1234.5678
sheet.Range("C7").NumberFormat = "0;[Red]-0"
sheet.Range("B8").Text = "0.00;[Red]-0.00"
sheet.Range("C8").NumberValue = -1234.5678
sheet.Range("C8").NumberFormat = "0.00;[Red]-0.00"
sheet.Range("B9").Text = "#,##0;[Red]-#,##0"
sheet.Range("C9").NumberValue = -1234.5678
sheet.Range("C9").NumberFormat = "#,##0;[Red]-#,##0"
sheet.Range("B10").Text = "#,##0.00;[Red]-#,##0.000"
sheet.Range("C10").NumberValue = -1234.5678
sheet.Range("C10").NumberFormat = "#,##0.00;[Red]-#,##0.00"
sheet.Range("B11").Text = "0.00E+00"
sheet.Range("C11").NumberValue = 1234.5678
sheet.Range("C11").NumberFormat = "0.00E+00"
sheet.Range("B12").Text = "0.00%"
sheet.Range("C12").NumberValue = 1234.5678
sheet.Range("C12").NumberFormat = "0.00%"
sheet.Range("B3:B12").Style.KnownColor = ExcelColors.Gray25Percent
sheet.AutoFitColumn(2)
sheet.AutoFitColumn(3)
workbook.SaveToFile("Sample.xls")
ExcelDocViewer(workbook.FileName)
End Sub