Problem in excel report generation
I am working on an Excel report and I would like the top rows to be blank so that the image can be inserted. However, I don't want the image size to change the column widths, and rather the data below does it.
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
One way to create a report with an image included is to create a templates workbook (.xlt file) in which you have already configured the required formatting and image. Then, in your code, create a new report workbook from the template like this:
.Workbooks.Add("path\to\report_template.xlt")
To make your image fixed in size and regardless of the column width, right-click it, select "Image Format ...", open the "Properties" tab, and in the "Object Positioning" field, select "Do not move or resize with cells ",.
Alternatively, use the code to add an image to an empty workbook like this:
ActiveSheet.Pictures.Insert( "path\to\image.jpg" ).Select
With Selection
.Placement = xlFreeFloating
.PrintObject = True
End With
You can control the first line to be used to output your data by initializing the string variable to the appropriate value.
You can also resize the columns to fit your data using the AutoFit method.
Your code might 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 starting value for the outputRow variable to provide enough room for your image.
source to share
An alternative option, if the image is only required for printed reports, is to place it in the page header.
Worksheets(1).PageSetup.LeftHeaderPicture.Filename = "C:\header.JPG"
The image will be embedded in the file. It will only appear in Preview mode and when you print a report.
source to share