I want to set date format "mm-dd-yy" to excel entire column of IWorksheet, and date fileld containing value like "01/30/16". After writing excel file i open file and checked date field there is no formatting applied. Same issue with number field.Following is code please have a look and help me out.
Dim cIndex As Integer = 0
Dim rIndex As Integer = 0
For Each dc In dtExport.Columns
cIndex = cIndex + 1
sheet.Range(1, cIndex).Text = dc.ColumnName
Next
For Each dr In dtExport.Rows
rIndex = rIndex + 1
cIndex = 0
For Each dc In dtExport.Columns
cIndex = cIndex + 1
sheet.Range(rIndex + 1, cIndex).Text = dr(dc.ColumnName).ToString()
Next
Next
For Each column In formattedColumns
For Each sheetColumn As IRange In sheet.Columns
If sheetColumn.DisplayText = column.Name Then
If column.DataType.ToLower() = "datetime" Then
sheetColumn.CellStyle.NumberFormat = "mm/dd/yy"
sheetColumn.DateTime = Date.Parse(DateTime.Now)
ElseIf column.DataType.ToLower() = "currency" Then
sheet.Range().NumberFormat = DataHelper.GetSafeStr(column.DataFormat)
End If
Exit For
End If
Next
Next
