求助：如何修改现有代码实现向Excel插入TextBox数据同时添加PictureBox图片

Got it, let's figure out how to add those PictureBox images to your Excel rows right next to your TextBox content! The catch with your current OLEDB approach is that it treats Excel like a relational database—so it can't directly insert images, since they aren't standard "table data." We need to switch to methods that interact with Excel's object model directly. Here are two solid options:

Option 1: Use Excel Interop (Built-in with Office) #

This uses Microsoft's official Office interop libraries, which work if you (and anyone using your app) have Excel installed.

Step 1: Add the Interop Reference #

First, in your VB.NET project:

• Right-click References → Add Reference
• Go to the COM tab → Find and select Microsoft Excel xx.x Object Library (xx.x is your Office version, like 16.0 for Office 2019/365)

Step 2: Updated Code #

Replace your existing OLEDB code with something like this:

Imports Microsoft.Office.Interop.Excel
Imports System.IO
Imports System.Runtime.InteropServices

'...

' Initialize Excel objects
Dim excelApp As New Application()
Dim workbook As Workbook = excelApp.Workbooks.Open(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "DATA\DATA.xlsm"))
Dim worksheet As Worksheet = workbook.Sheets("YourSheetName") ' Replace with your actual sheet name

' Find the next empty row (assuming column A has existing data)
Dim nextRow As Integer = worksheet.Cells(worksheet.Rows.Count, "A").End(XlDirection.xlUp).Row + 1

' Insert TextBox content into columns A, B, etc.
worksheet.Cells(nextRow, "A").Value = YourTextBox1.Text
worksheet.Cells(nextRow, "B").Value = YourTextBox2.Text
' Add more TextBoxes as needed

' Insert PictureBox image into column C (adjust column letter as needed)
' First, save the PictureBox image to a temporary file
Dim tempPicPath As String = Path.Combine(Path.GetTempPath(), $"temp_img_{Guid.NewGuid()}.png")
YourPictureBox.Image.Save(tempPicPath)

' Add the image to the target cell
Dim insertedPic As Shape = worksheet.Shapes.AddPicture(
    Filename:=tempPicPath,
    LinkToFile:=False,
    SaveWithDocument:=True,
    Left:=worksheet.Cells(nextRow, "C").Left,
    Top:=worksheet.Cells(nextRow, "C").Top,
    Width:=worksheet.Cells(nextRow, "C").Width,
    Height:=worksheet.Cells(nextRow, "C").Height
)

' Clean up the temporary file
File.Delete(tempPicPath)

' Save changes and close Excel properly
workbook.Save()
workbook.Close()
excelApp.Quit()

' Release COM objects to avoid leftover Excel processes
Marshal.ReleaseComObject(insertedPic)
Marshal.ReleaseComObject(worksheet)
Marshal.ReleaseComObject(workbook)
Marshal.ReleaseComObject(excelApp)

Option 2: Use EPPlus (No Office Installation Required) #

EPPlus is a popular open-source library for working with Excel files without needing Office installed. It's great for deployment scenarios where you can't guarantee Office is present.

Step 1: Install EPPlus #

• Open NuGet Package Manager in your project
• Search for EPPlus and install it (note: version 5+ requires a commercial license for business use; use version 4.5.3.3 if you need a free non-commercial option)

Step 2: Updated Code #

Here's how to implement it with EPPlus:

Imports OfficeOpenXml
Imports System.IO
Imports System.Drawing.Imaging

'...

Dim excelPath As String = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "DATA\DATA.xlsm")

Using package As New ExcelPackage(New FileInfo(excelPath))
    Dim worksheet As ExcelWorksheet = package.Workbook.Worksheets("YourSheetName") ' Replace with your sheet name

    ' Get next empty row (handle empty sheet case)
    Dim nextRow As Integer = If(worksheet.Dimension IsNot Nothing, worksheet.Dimension.Rows + 1, 2) ' Start at row 2 if sheet is empty (assuming row 1 is headers)

    ' Insert TextBox content
    worksheet.Cells(nextRow, 1).Value = YourTextBox1.Text ' Column A
    worksheet.Cells(nextRow, 2).Value = YourTextBox2.Text ' Column B

    ' Insert PictureBox image into column C
    Using picStream As New MemoryStream()
        YourPictureBox.Image.Save(picStream, ImageFormat.Png)
        picStream.Position = 0 ' Reset stream position to start

        Dim excelPic As ExcelPicture = worksheet.Drawings.AddPicture($"Row{nextRow}_Pic", picStream)
        ' Position the image to match cell C{nextRow}
        excelPic.SetPosition(nextRow - 1, 0, 2, 0) ' (Row index (0-based), row offset, Column index (0-based), column offset)
        ' Resize image to fit the cell
        excelPic.SetSize(worksheet.Columns(3).Width, worksheet.Rows(nextRow).Height)
    End Using

    ' Save the changes
    package.Save()
End Using

Key Notes #

• OLEDB Limitation: Your original OLEDB method can't handle images because it's designed for tabular data, not Excel's drawing objects. That's why we need these object-model approaches.
• Interop Tips: Always release COM objects properly (like in the first example) to prevent Excel processes from running in the background.
• EPPlus License: Double-check the license terms if you're using this for commercial projects—version 5+ requires a paid license, while 4.x is free for non-commercial use.

内容的提问来源于stack exchange，提问作者Marek01