Problem in excel report generation

Question

I am working on an Excel report and I would like the top rows to be empty to allow for an image to be inserted. However, I do not want the image size to change the width of the columns and would rather the data below do that.

This is what I have so far:

   If ComDset.Tables(0).Rows.Count > 0 Then

    Try
        With Excel
            .SheetsInNewWorkbook = 1
            .Workbooks.Add()
            .Worksheets(1).Select()

            Dim i As Integer = 1
            For col = 0 To ComDset.Tables(0).Columns.Count - 1
                .cells(1, i).value = ComDset.Tables(0).Columns(col).ColumnName
                .cells(1, i).EntireRow.Font.Bold = True
                i += 1
            Next
            i = 2
            Dim k As Integer = 1
            For col = 0 To ComDset.Tables(0).Columns.Count - 1
                i = 2
                For row = 0 To ComDset.Tables(0).Rows.Count - 1
                    .Cells(i, k).Value = ComDset.Tables(0).Rows(row).ItemArray(col)
                    i += 1
                Next
                k += 1
            Next
            filename = "ShiftReport" & Format(MdbDate, "dd-MM-yyyy") & ".xls"
            .ActiveCell.Worksheet.SaveAs(filename)
        End With
        System.Runtime.InteropServices.Marshal.ReleaseComObject(Excel)
        Excel = Nothing
    Catch ex As Exception
        MsgBox(ex.Message)
    End Try

    ' The excel is created and opened for insert value. We most close this excel using this system
    Dim pro() As Process = System.Diagnostics.Process.GetProcessesByName("EXCEL")
    For Each i As Process In pro
        i.Kill()
    Next
End If

Answer 1

I would recommend trying to "merge" the cells in the top 2 rows. You could take A1 through say K2 and merge them and the image would fit just fine in there...

Range("A1:K2").Select
Selection.Merge

Answer 2

One way to create your report with the image included is to create a template workbook (.xlt file) in which you have already set up your required formatting and image(s). Then in your code, create the new report workbook from the template like this:

.Workbooks.Add("path\to\report_template.xlt")

To make your image fixed in size and independent of column widths, right-click on it, choose "Format Picture...", open the Properties tab and within Object Positioning select "Don't move or size with cells".

Alternatively, use code to add the picture to a blank workbook as follows:

ActiveSheet.Pictures.Insert( "path\to\image.jpg" ).Select
With Selection
    .Placement = xlFreeFloating
    .PrintObject = True
End With

You could control the first row to be used to output your data by initialising a row variable to an appropriate value.

You can also fit the column sizes to match your data by using the AutoFit method.

Your code might then look like this:

 Try
    With Excel
        .SheetsInNewWorkbook = 1
        .Workbooks.Add("path\to\report_template.xlt")
        .Worksheets(1).Select()

        Dim outputRow As Integer = 8
        For col = 0 To ComDset.Tables(0).Columns.Count - 1
            .Cells(outputRow, col+1).value = ComDset.Tables(0).Columns(col).ColumnName
            .Cells(outputRow, col+1).EntireRow.Font.Bold = True
        Next

        outputRow += 1
        For y = 0 To ComDset.Tables(0).Rows.Count - 1
            For x = 0 To ComDset.Tables(0).Columns.Count - 1
                .Cells(outputRow + y, x).Value = ComDset.Tables(0).Rows(y).ItemArray(x)
            Next
        Next

        .Cells(outputRow, 1).CurrentRegion.Columns.AutoFit

        filename = "ShiftReport" & Format(MdbDate, "dd-MM-yyyy") & ".xls"
        .ActiveCell.Worksheet.SaveAs(filename)
    End With
    System.Runtime.InteropServices.Marshal.ReleaseComObject(Excel)
    Excel = Nothing
Catch ex As Exception
    MsgBox(ex.Message)
End Try

Choose a suitable initial value for the outputRow variable to give enough space for your image.

Answer 3

An alternate to consider, if the image is only needed for printed reports, is to put it into the page header.

Worksheets(1).PageSetup.LeftHeaderPicture.Filename = "C:\header.JPG"

The image will be embedded in the file. It will only be visible in 'Print preview' mode and when printing the report.