Cell foreground color bug

Aspose.Cells Product Family
1

Hello,

I think I found a bug with version 2.9.7.0

What I’m doing is creating a DataTable and creating rows at runtime. Then I add the DataTable to a DataView.

The DataTable has 6 columns. I want the cell color of all cells in the first three columns to be changed.

If there are values in all the cells, the color changes as expected.
If the value of cell 3 is the color changes as expected.
If the value of cell 4 is the color changes as expected.
If the value of cell 4 is and the value of cell 3 is NULL the color of cell 3 is not changed.

I am trying to create an Excel2000 spreadsheet.

I am including an excel file that illustrates my problem.

2

Could you please post your code here? Thank you.

3

Imports System.IO

Public Class WebForm2
Inherits System.Web.UI.Page

#Region " Web Form Designer Generated Code "

'This call is required by the Web Form Designer.
Private Sub InitializeComponent()

End Sub

'NOTE: The following placeholder declaration is required by the Web Form Designer.
'Do not delete or move it.
Private designerPlaceholderDeclaration As System.Object

Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
'CODEGEN: This method call is required by the Web Form Designer
'Do not modify it using the code editor.
InitializeComponent()
End Sub

#End Region

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here

Dim dtLocation As DataTable = CreateLocationDataTableSource()
Dim dvLocation As New DataView(dtLocation)
Dim dsT As New DataSet
dsT.Tables.Add(dvLocation.Table)

CreateXls(dsT)

End Sub

Public Sub CreateXls(ByVal data As DataSet)

Dim xlsWriter As New Aspose.Excel.Excel

With xlsWriter.Worksheets.Item(0).Cells
.ImportDataTable(data.Tables(0), True, "A1")
.Columns(0).Style.ForegroundColor = Drawing.Color.Yellow
.Columns(1).Style.ForegroundColor = Drawing.Color.Yellow
.Columns(2).Style.ForegroundColor = Drawing.Color.Yellow
End With

xlsWriter.Save("6.csv", Aspose.Excel.FileFormatType.Excel2000, Aspose.Excel.SaveType.OpenInBrowser, Response)

End Sub

Private Function CreateLocationDataTableSource() As DataTable
Dim dtL As New DataTable

dtL.Columns.Add(New DataColumn("Location Name", GetType(String)))
dtL.Columns.Add(New DataColumn("Address1", GetType(String)))
dtL.Columns.Add(New DataColumn("Address2", GetType(String)))
dtL.Columns.Add(New DataColumn("City", GetType(String)))

Dim dr As DataRow

dr = dtL.NewRow
dr.Item("Location Name") = "Location Name"
dr.Item("Address1") = ""
dr.Item("Address2") = "Address2"
dr.Item("City") = "City"
dtL.Rows.Add(dr)


dr = dtL.NewRow
dr.Item("Location Name") = "Location Name"
dr.Item("Address1") = "Address1"
dr.Item("Address2") = ""
dr.Item("City") = "City"
dtL.Rows.Add(dr)


dr = dtL.NewRow
dr.Item("Location Name") = "Location Name"
dr.Item("Address1") = "Address1"
dr.Item("Address2") = ""
dr.Item("City") = ""
dtL.Rows.Add(dr)


dr = dtL.NewRow
dr.Item("Location Name") = ""
dr.Item("Address1") = ""
dr.Item("Address2") = "Address2"
dr.Item("City") = "City"
dtL.Rows.Add(dr)


dr = dtL.NewRow
dr.Item("Location Name") = ""
dr.Item("Address1") = "Address1"
dr.Item("Address2") = ""
dr.Item("City") = "City"
dtL.Rows.Add(dr)


dr = dtL.NewRow
dr.Item("Location Name") = "Location Name"
dr.Item("Address1") = "Address1"
dr.Item("Address2") = "Address2"
dr.Item("City") = "City"
dtL.Rows.Add(dr)


dr = dtL.NewRow
dr.Item("Location Name") = "Location Name"
dr.Item("Address1") = "Address1"
dr.Item("Address2") = "Address2"
dr.Item("City") = "City"
dtL.Rows.Add(dr)


Return dtL


End Function

End Class

4

Thanks for your help.

I fixed it and you will get the new hotfix at the start of next week.

5

I found a couple more quirks. I'm not sure if the fix you made remedies these or not, but...


I modified the CreateXls function from the previously submitted code as follows:

Public Sub CreateXls(ByVal data As DataSet)

Dim xlsWriter As New Aspose.Excel.Excel

With xlsWriter.Worksheets.Item(0)
.Cells.ImportDataTable(data.Tables(0), True, "A1")

.Cells.Columns(0).Style.IsLocked = True
.Cells.Columns(1).Style.IsLocked = True
.Cells.Columns(2).Style.IsLocked = True

Dim i As Integer
For i = 1 To 6
On Error Resume Next
.Cells("A" & i).Style.IsLocked = False
.Cells("B" & i).Style.IsLocked = False
.Cells("C" & i).Style.IsLocked = False
.Cells("D" & i).Style.IsLocked = False
If Err.Number <> 0 Then
Stop
End If
Next

.Cells.Columns(0).Style.ForegroundColor = Drawing.Color.Yellow
.Cells.Columns(1).Style.ForegroundColor = Drawing.Color.Yellow
.Cells.Columns(2).Style.ForegroundColor = Drawing.Color.Yellow

.Protect(Aspose.Excel.ProtectionType.All, "test", "")
End With

xlsWriter.Save("6.csv", Aspose.Excel.FileFormatType.Excel2000, Aspose.Excel.SaveType.OpenInExcel, Response)

End Sub

Here are a couple problems:
The cell color gets reset to transparent for each cell I explicitly set to false.
Running the code at full speed results in an error being thrown. Stepping through line by line is fine.

6

Seems to work now. Thanks