Formatting Numbers and Dates via code

Question 1
------------
I’ve been going through the documentation, including Excel’s VBA docs, trying to find what the various Number formats are per each Byte value. I have not been able to find this info anywhere.

For instance, in your demo code, Invoice.aspx.vb, you create a “Number7” Style object, and set the Number property to 7: style.Number = 7. How do you know what numeric/date format this will result in? I guess I could set up an experiment to populate a spreadsheet using a single numeric value and iterate through the entire set of Byte values. But, I’d rather avoid going to that extreme if the Byte value to format mapping is documented somewhere.


Question 2
------------
I’ve got some code to dynamically populate an Excel spreadsheet by iterating through an ADO.NET DataTable object. I capture the DataTable’s cell type to string, and use this to decide which typecast/conversion function to use when putting the DataTable’s cell value into the Excel Cell object. The code works fine except that when I cast what should be a DateTime value to the DateTime type, it shows up in the Excel spreadsheet as text, and I’m not able to toggle its format with the various date/time formats because Excel sees it as text and not a true date/time value. Any ideas about this problem? Here’s my VB.NET code and Oracle table info in case you need it:

Oracle table info for DSG_SUMMARY
------------------------------------------
DESIGNATION VARCHAR2 (14) NOT NULL,
STATUS CHAR (1),
PLANSTRT DATE,
PLAN_YR NUMBER (4),
PLAN_QTR NUMBER (1),
PLAN_MO NUMBER (2),
ACTSTRT DATE,
ACT_YR NUMBER (4),
ACT_QTR NUMBER (1),
ACT_MO NUMBER (2),
LLH DATE,
LLH_YR NUMBER (4),
LLH_QTR NUMBER (1),
LLH_MO NUMBER (2),
ACRES NUMBER (7,2),
PP_EST NUMBER (12,2),
PP_REM NUMBER (12,2),
PP_PCTVAR NUMBER (12,6),
PS_EST NUMBER (12,2),
PS_REM NUMBER (12,2),
PS_PCTVAR NUMBER (12,6),
PS_MIX_EST NUMBER (12,6),
PS_MIX_REM NUMBER (12,6),
PS_MIX_VAR NUMBER (12,6),
HP_EST NUMBER (12,2),
HP_REM NUMBER (12,2),
HP_PCTVAR NUMBER (12,6),
HS_EST NUMBER (12,2),
HS_REM NUMBER (12,2),
HS_PCTVAR NUMBER (12,6),
HS_MIX_EST NUMBER (12,6),
HS_MIX_REM NUMBER (12,6),
HS_MIX_VAR NUMBER (12,6)




VB.NET
---------
Private Sub btnSend2Excel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSend2Excel.Click

Dim conn As OracleConnection = _
New OracleConnection(ConfigurationSettings.AppSettings(“OracleConnectionString”))
Dim da As OracleDataAdapter
Dim dt As DataTable = New DataTable()
Dim i As Int32
Dim j As Int32
Dim dataType As String

Try

'Get data from Oracle and store in a DataTable
conn.Open()
da = New OracleDataAdapter(“SELECT * FROM dsg_summary”, conn)
da.Fill(dt)
conn.Close()

'Set up a new Excel workbook object
Dim newExcel As Excel = New Excel()
Dim sheets As Worksheets = newExcel.Worksheets
Dim sheet As Worksheet
sheet = sheets.GetAt(0)
Dim cells As Cells = sheet.Cells

Dim startRow As Int32 = 0

'Create header
For i = 0 To (dt.Columns.Count - 1)
cells.GetAt(startRow, i).PutValue(dt.Columns(i).ColumnName)
Next
startRow += 1

'Insert rows
For i = 0 To (dt.Rows.Count - 1)
For j = 0 To (dt.Columns.Count - 1)
dataType = dt.Rows(i)(j).GetType.ToString
Select Case dataType
Case Is = “System.DateTime”
cells.GetAt(startRow, j).PutValue(Convert.ToDateTime(dt.Rows(i)(j)))
Case Is = “System.Decimal”
cells.GetAt(startRow, j).PutValue(Convert.ToDecimal(dt.Rows(i)(j)))
Case Is = “System.String”
cells.GetAt(startRow, j).PutValue(Convert.ToString(dt.Rows(i)(j)))
Case Is = “System.Number”
cells.GetAt(startRow, j).PutValue(Convert.ToDecimal(dt.Rows(i)(j)))
Case Is = “System.DBNull”
cells.GetAt(startRow, j).PutValue(DBNull.Value.ToString)
Case Else
cells.GetAt(startRow, j).PutValue(Convert.ToString(dt.Rows(i)(j)))
End Select
Next
startRow += 1
Next

newExcel.Save(“HarvestTracking.xls”, SaveType.OpenInBrowser, FileFormatType.Default, Response)

Catch ex As Exception
Response.Write(“An error occurred:” & ControlChars.CrLf & ex.Message & ex.StackTrace)
Finally
If conn.State = ConnectionState.Open Then
conn.Close()
End If
da.Dispose()
End Try

End Sub

Dear Nathan,

Welcome you to come back again!

This quick post just addresses the first question.

It’s a mistake to omit the Value/Format String table of CellFormat.Number Property in last release in the API Reference.

Now please see it CellFormat.Number Property.

It will be added in the API Reference in next release next week.

I’m very sorry for this omittance. Please accept my personal apology for any inconvenience you may have experienced.

Soon we will reply second question and the other post. Thanks for your kindly patience.


Dear Nathan,

This post is for question 2 only.

Excel can store types such as int, float and string. Date/time is not directly supported.

Is there someway, in VB.NET, to convert the Data/Time datatype value into the numeric serial value that Excel can represent as a date, and then assign this value to the Cell object? I know Excel VBA has functions for doing this, but does the .NET Framework have something that would produce an equivalent value that Excel can handle? I’m going to dig into this a little further to see if there is a solution.

By the way, it seems we ran into the date issue before when I was playing with the Designer spreadsheet functionality of Aspose.Excel. I believe you fixed the problem, so there must be something your API is doing behind the scenes to correctly transform a .NET date-time datatype into a numeric value that Excel can interpret and represent as a date.


Dear Nathan,

Very sorry for reply so late!

Now we’re investigating it and soon will reply.

Thanks for your kindly patience.

Dear Nathan,

We decide to add Cell.PutValue(DateTime timeValue) method which help you for your purpose if you use it together with existing CellFormat.Number or proposed CellFormat.CustomNumberFormatString , or formatting the cell in Designer file at design time.

This feature will be available within 2-3 weeks. Please let me know if you need it immediately when it is available.

The Cell.PutValue(DateTime timeValue) and CellFormat.CustomNumberFormatString will be very helpful features to have. Please do let me know as soon as the new features are available.

It is very refreshing to work with software products from a company that listens to what their customers need, and actually delivers on the suggestions of its customers and so quickly! Keep up the good work and momentum. And, thank you for your help.

Sometimes it is best to put a problem on the “back burner” for a while and work on something else. After digging around in the .NET Framework docs, I came up with the following workaround for the DateTime problem (which I mentioned in my previous posting under Question 2). Here is the new version of the portion of the Select Case statment that deals with the DateTime conversion that works great:

Case Is = “System.DateTime”
Dim dateValue As Date = Convert.ToDateTime(dt.Rows(i)(j))
cells.GetAt(startRow, j).PutValue(Convert.ToDecimal(dateValue.ToOADate))
cells.GetAt(startRow, j).Format.Number = 14


The key is to cast the value into a Date variable, then while assigning the value of the Date variable into the Cell object, convert it into an OLE Automation date value (which generates the serial numeric representation of a date that Excel natively understands.

For good measure, I assigned a Format.Number property value of 14 to the Cell object so the date value would show up as m/d/yy.

It would still be useful to have a built-in Aspose.Excel method for converting .NET DateTime to native Excel numeric serial date value (fewer lines of code for those of us who use Aspose.Excel). Also, the ability to specify custom numeric/date formats is still desirable. My users are picky, and so I must in turn be picky. They want to see dates formatted sometimes as mm/dd/yy, and other times as mm/dd/yyyy.

Dear Nathan,

The proposed Cell.PutValue(DateTime dateTime) or Cell.PutValue(Object object) will save you from writing the following code:


Case Is = “System.DateTime”
Dim dateValue As Date = Convert.ToDateTime(dt.Rows(i)(j))
cells.GetAt(startRow, j).PutValue(Convert.ToDecimal(dateValue.ToOADate))


While you need write code


cells.GetAt(startRow, j).Format.Number = 14


Or you can format it in your designer file at design time.

Is it OK for you?


Yes, the Cell.PutValue(DateTime dateTime) would be more convenient and desirable.

Also, it would be advantageous to be able to specify custom numeric/date formats and not be limited to just the 50 pre-defined ones available through the CellFormat.Number property.

Hi,

Thanks for considering Aspose.Cells.

All these functions have been added in our newer releases.

Please download and use the latest version:

Aspose.Cells for .NET 7.3.1
to make use of these functions.

If you have any questions, please feel free to ask us, we will be glad to help you.