如何在vb.net中修改现有的Excel文件 [英] How to modify existing Excel file in vb.net

问题描述

我要修改现有的Excel文件.

I want to modify an existing Excel file.

下面的代码片段在documents文件夹中生成一个重复的修改后的excel文件，而不是修改原始文件.

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

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

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

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

推荐答案

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

这篇关于如何在vb.net中修改现有的Excel文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！