如何在 vb.net 中修改现有的 Excel 文件

How to modify existing Excel file in vb.net

提问by Monica

I want to modify an existing Excel file.

我想修改现有的 Excel 文件。

The following piece of code generates a duplicate modified excel file in documents folder instead of modifying the original file.

以下代码在文档文件夹中生成重复的修改后的 excel 文件，而不是修改原始文件。

Note: the file which I want to modify is not in the Documentsfolder

注意：我要修改的Documents文件不在文件夹中

Imports Microsoft.Office.Interop.Excel

Public Class Form1

Public ExcelFolder As String
Public selectedfile As String
Public excel As Application
Public workbook As Workbook
Public sheet As Worksheet
Public r As Range
Public array(,) As Object

Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load

    ExcelFolder = "D:\monica\YUKEN\MARK\Excel"

    Dim dir As New IO.DirectoryInfo(ExcelFolder)
    Dim dir1 As IO.FileInfo() = dir.GetFiles
    Dim file As IO.FileInfo

    For Each file In dir1

        ComboBox1.Items.Add(file)

    Next

End Sub

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

    selectedfile = ComboBox1.GetItemText(ComboBox1.SelectedItem)
    TextBox1.Text = selectedfile

    Dim path As String
    path = ExcelFolder & "\" & selectedfile

    excel = New Application

    excel.Workbooks.Open(path)
    workbook = excel.ActiveWorkbook
    sheet = workbook.Worksheets(1)
    r = sheet.UsedRange

    ' Load all cells into 2d array.
    Array = r.Value(XlRangeValueDataType.xlRangeValueDefault)

    ' Get bounds of the array.
    Dim bound0 As Integer = array.GetUpperBound(0) 'last row number
    Dim bound1 As Integer = array.GetUpperBound(1) 'last column number

    'get total number of rows
    Dim totalrows As Integer = bound0 - 1 'since 1st row is header
    TextBox2.Text = CStr(totalrows)

    sheet.Cells(2, 12) = "YES"
    workbook.Save()
    workbook.Close()
    excel.Quit()
    System.Runtime.InteropServices.Marshal.ReleaseComObject(excel) : excel = Nothing
    System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook) : workbook = Nothing
    System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet) : sheet = Nothing

End Sub
End Class

回答by Mukesh Patel

Dim cn As New OleDbConnection
 Dim cm As New OleDbCommand
cn = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;

Data Source=C:\Documents and Settings\crysol\Desktop\TEST\Book1.xls;Extended Properties=""Excel 12.0 Xml;HDR=YES""")
cn.Open()
With cm
    .Connection = cn
    .CommandText = "update [up$] set [name]=?, [QC_status]=?, [reason]=?, [date]=? WHERE [article_no]=?"
    cm = New OleDbCommand(.CommandText, cn)
    cm.Parameters.AddWithValue("?", TextBox2.Text)
    cm.Parameters.AddWithValue("?", ComboBox1.SelectedItem)
    cm.Parameters.AddWithValue("?", TextBox3.Text)
    cm.Parameters.AddWithValue("?", DateTimePicker1.Text)
    cm.Parameters.AddWithValue("?", TextBox1.Text)
    cm.ExecuteNonQuery()
    MsgBox("UPDATE SUCCESSFUL")
    con.Close()
End With

回答by Fenil Patel

Here I have shown to add a sheet to an existing excel file and write data into the newly created sheet.

在这里，我展示了将工作表添加到现有 excel 文件并将数据写入新创建的工作表。

    Try
        Dim oXL As Excel.Application
        Dim oWB As Excel.Workbook
        Dim oSheet As Excel.Worksheet
        Dim oRng As Excel.Range
        'On Error GoTo Err_Handler
        ' Start Excel and get Application object.
        oXL = New Excel.Application

        ' Get a new workbook.
        Dim path As String = ViewState("filepath")
        oWB = oXL.Workbooks.Open(path)
        oSheet = CType(oXL.Worksheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value), Excel.Worksheet)
        'oSheet.Name = "Reject_History"
        Dim totalSheets As Integer = oXL.ActiveWorkbook.Sheets.Count
        CType(oXL.ActiveSheet, Excel.Worksheet).Move(After:=oXL.Worksheets(totalSheets))
        CType(oXL.ActiveWorkbook.Sheets(totalSheets), Excel.Worksheet).Activate()


        'Write Dataset to Excel Sheet
        Dim col As Integer = 0

        For Each dr As DataColumn In DirectCast(ViewState("DisplayNonExisting"), DataTable).Columns

            col += 1
            'Determine cell to write
            oSheet.Cells(10, col).Value = dr.ColumnName

        Next

        Dim irow As Integer = 10
        For Each dr As DataRow In DirectCast(ViewState("DisplayNonExisting"), DataTable).Rows
            irow += 1
            Dim icol As Integer = 0
            For Each c As String In dr.ItemArray
                icol += 1
                'Determine cell to write
                oSheet.Cells(irow, icol).Value = c
            Next
        Next

        ' Make sure Excel is visible and give the user control
        ' of Microsoft Excel's lifetime.
        ' oXL.Visible = True
        ' oXL.UserControl = True

        'oWB.SaveAs(path, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing, False, False, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing)
        'oWB.Close()
        oWB.Save()
        oWB.Close(Type.Missing, Type.Missing, Type.Missing)
        ' Make sure you release object references.

        oRng = Nothing
        oSheet = Nothing
        oWB = Nothing
        oXL = Nothing

    Catch ex As Exception

    End Try