AutofitRows not working on extra sheets

Aspose.Cells Product Family
1

Hi,

I am adding multiple sheets to my file, and using the new autofitrows function, however it only seems to work on the first worksheet.

Dim designer As WorkbookDesigner = New WorkbookDesigner()

Dim path As String = MapPath(".")

path = path.Substring(0, path.LastIndexOf("\"))

Dim designerFile As String = "c:\DailyReportTemplate.xls"

designer.Open(designerFile)

'Dim ds As DataSet = SqlHelper.ExecuteDataset(System.Configuration.ConfigurationSettings.AppSettings("DB"), CommandType.Text, "SELECT s.Code + CAST((a.Application + 401) as varchar(50)) as Reference, a.Surname, a.FirstNames, a.Title, a.IDNumber, SubString(a.Gender, 1, 1) as Gender, a.Language as HomeLanguage, Replace(a.PhysicalAddress, CHAR(13), ', ') as PhysicalAddress, a.Cell, a.HomePhone, a.WorkPhone, CAST(Replace(a.PhysicalAddress, CHAR(13), ', ') as varchar(3000)) + ', ' + a.PostCode as PostalAddress FROM Applications a, Sources s where a.SourceID = s.SourceID and a.deleted = 0")

'ds.Tables(0).TableName = "Deals"

'Dim designer As WorkbookDesigner = New WorkbookDesigner()

'Dim designerFile As String = "c:\SATFTemplate.xls"

'designer.Open(designerFile)

designer.SetDataSource(ds)

Dim w As Worksheet

Dim dr As DataRow

'dx.Tables.Add("Item")

Dim i As Integer = 1

Dim ii As Integer

Dim iii As Integer = 1

For Each dr In ds.Tables(0).Rows

dr("Customer") = Mid(dr("Customer"), 1, dr("Customer").ToString.IndexOf("_"))

dr("Name") = Mid(dr("Name"), 1, dr("Name").ToString.LastIndexOf("."))

dr("IntExt") = IIf(dr("IntExt") = "Internal Non Capital", "I (NC)", IIf(dr("IntExt") = "Internal Capital", "I (C)", "E"))

If iii <> ds.Tables(0).Rows.Count Then

ii = designer.Workbook.Worksheets.AddCopy("Fig 2")

End If

iii += 1

Next

iii = 1

For Each dr In ds.Tables(0).Rows

designer.Workbook.Worksheets(iii).Name = dr("Name")

iii += 1

Next

designer.Process(0, True)

Dim adr As DataRow

For Each dr In ds.Tables(0).Rows

dx.Tables("Item").Rows.Clear()

adr = dr

dx.Tables("Item").ImportRow(adr)

dx.Tables("Risks").Rows.Clear()

dx.Tables("Issues").Rows.Clear()

Dim dr2 As DataRow

Dim xds2 As DataSet = SqlHelper.ExecuteDataset(System.Configuration.ConfigurationSettings.AppSettings("DB"), CommandType.Text, "dailyrisklist " & dr("Proj_ID"))

xds2.Tables(0).TableName = "Risks"

For Each dr2 In xds2.Tables("Risks").Rows

dr2("Description") = Regex.Replace(dr2("Description").ToString.Replace("
", Chr(13)).Replace("

", Chr(13)), "<(.|\n)*?>", String.Empty)

dr2("Mitigation") = Regex.Replace(dr2("Mitigation").ToString.Replace("
", Chr(13)).Replace("

", Chr(13)), "<(.|\n)*?>", String.Empty)

dr2("Impact") = Regex.Replace(dr2("Impact").ToString.Replace("
", Chr(13)).Replace("

", Chr(13)), "<(.|\n)*?>", String.Empty)

adr = dr2

dx.Tables("Risks").ImportRow(adr)

Next

xds2 = SqlHelper.ExecuteDataset(System.Configuration.ConfigurationSettings.AppSettings("DB"), CommandType.Text, "dailyissuelist " & dr("Proj_ID"))

xds2.Tables(0).TableName = "Issues"

For Each dr2 In xds2.Tables("Issues").Rows

dr2("Description") = Regex.Replace(dr2("Description").ToString.Replace("
", Chr(13)).Replace("

", Chr(13)), "<(.|\n)*?>", String.Empty)

dr2("Mitigation") = Regex.Replace(dr2("Mitigation").ToString.Replace("
", Chr(13)).Replace("

", Chr(13)), "<(.|\n)*?>", String.Empty)

dr2("Priority") = Regex.Replace(dr2("Priority").ToString.Replace("
", Chr(13)).Replace("

", Chr(13)), "<(.|\n)*?>", String.Empty)

adr = dr2

dx.Tables("Issues").ImportRow(adr)

Next

designer.SetDataSource(dx)

designer.Process(i, False)

designer.Workbook.Worksheets(i).AutoFitRows()

'designer.SetDataSource(xds)

'designer.Process(i, True)

'dx.Tables.Remove("Risks")

i += 1

Next

'designer.Workbook.Worksheets(0)

designer.Workbook.Worksheets(0).AutoFitRows()

designer.Save("SmartMarker2.xls", SaveType.OpenInBrowser, FileFormatType.Default, Me.Response)

2

I use the following test code and it works fine:

Dim workbook As Workbook = New Workbook()

Dim i As Integer
For i = 0 To 4

Dim sheet As Worksheet = workbook.Worksheets(i)
sheet.Cells("A1").PutValue("hello world")
sheet.Cells("A1").Style.Font.Size = CType((10 + 2 * i), Short)
sheet.AutoFitRows()

workbook.Worksheets.Add()
Next
workbook.Save("d:\test\abc.xls")

Please debug into your program to check if all worksheets' AutoFitRows method is called.

If you cannot figure out the problem, could you please post your output file here? Thank you.

3

IT is definitely hitting the code, but if you look at the third sheet cell B9, it is definitely not autofitted

4

Autofit doesn’t work on merged cells. That’s same as MS Excel. You can verify it in MS Excel.