Formatting string as currency

Aspose.Cells Product Family
1

The following code creates a spreadsheet using ImportDataTable where there are two columns, column one is type string, column two integer.

When I format the two columns the second column formats as expected but not the first column which is set to the same style.

What do I need to change so that the string column format matches that of the integer column?

   Sub FormatToCurrency()
      Dim FileName As String = "Formatting.xls"
 
      Dim Table As New DataTable
      With Table.Columns
         .AddRange(New DataColumn() _
            { _
               New DataColumn("NumberText", _
                              System.Type.GetType("System.String")), _
               New DataColumn("NumberNumber", _
                              System.Type.GetType("System.Int32")) _
            } _
         )
      End With
 
      Table.Rows.Add(New Object() {"100", 100})
      Table.Rows.Add(New Object() {"1000", 1000})
      Table.Rows.Add(New Object() {"10000", 10000})
      Table.Rows.Add(New Object() {"12567", 12567})
  
      Dim Book As Workbook = New Workbook()
      Dim Sheet As Worksheet
 
      Sheet = Book.Worksheets(0)
 
      Book.Styles.Add()
      Dim style As Style = Book.Styles(0)
      style.Number = 6
      style.ForegroundColor = Color.Green
 
      Dim styleFlag As StyleFlag = New StyleFlag()
      styleFlag.All = True
      styleFlag.ShrinkToFit = True
 
      Sheet.Cells.Columns(0).ApplyStyle(style, styleFlag)
      Sheet.Cells.Columns(1).ApplyStyle(style, styleFlag)
 
      Sheet.Cells.ImportDataTable(Table, True, "A2")
      Sheet.Cells("A1").PutValue("Report title goes here", True)
 
      Book.Save(FileName, FileFormatType.Default)
      Process.Start(FileName)
 
   End Sub
2

Hi,

Thanks for sharing the sample code.

Well, for your first column, the reason is simple. You have stored numbers as text/string in the first column in the worksheet, so you cannot format the data as currency. You need to convert those strings into numbers first. This behavior is same as MS Excel.

Aspose.Cells does allow importing data table and formatting strings to numbers providing the overloaded version of the ImportDataTable method:

public int ImportDataTable(
DataTable dataTable ,
bool isFieldNameShown ,
int firstRow ,
int firstColumn ,
bool insertRows ,
bool convertStringToNumber
);
You need to put “True” for the last parameter.

Please change the line of your code:

Sheet.Cells.ImportDataTable(Table, True, “A2”)
to:
Sheet.Cells.ImportDataTable(Table, True, 1,0,False, True );

Thank you.

3

Thanks, will do.