使用数据将数据添加到现有的Excel文件中 [英] Adding data to existing excel file with data

问题描述

您好，我有一个Excel文件，其中已经包含数据.我希望找到最后一个空行，并将列表视图中的数据追加到该行中，但是我不知道如何以及在下面的代码中将代码放置在何处.我使用了在互联网上找到的功能代码.下面是代码:

Hi, I have an excel file that contain data already. I wish to find the last empty row and append data from a listview to it but I don''t know how and where to place the code in the code below. I used a Function Code that I found on the internet. Below is the code:

Public Function ExportListViewToExcel(ByVal MyListView As System.Windows.Forms.ListView)

        '=====================================================================        '== This is our Excel Class ==
        '== Remember to add a refrence to Microsoft Excel XX.XX Object Libary ==
        '=========================================================================
        Dim ExcelReport As Microsoft.Office.Interop.Excel.ApplicationClass

        '=========================================================================================
        '== TMAX_COLOURS is the maximum amount of colours excel will let us save in the palet ==
        '=========================================================================================
        Const MAX_COLOURS As Int16 = 40

        '=======================================================================
        '== Excel will only allow 25 colums per sheet (starting at 0 = 254) ==
        '=======================================================================
        Const MAX_COLUMS As Int16 = 254

        '=====================================================
        '== Simple counter to count items in our listview ==
        '=====================================================
        Dim i As Integer
        Dim New_Item As ListViewItem

        '==========================================================
        '== The Colum Letter(and number) we are on and the row ==
        '==========================================================
        Dim TempColum As Int16
        Dim ColumLetter As String
        Dim TempRow As Int16
        Dim TempColum2 As Int16

        '=============================================
        '== Colours we have added to Excels palet ==
        '=============================================
        Dim AddedColours As Int16 = 1
        Dim MyColours As Hashtable = New Hashtable

        '===========================================================================
        '== Variables to control if we need to add a new colour to Excels palet ==
        '===========================================================================
        Dim AddNewBackColour As Boolean = True
        Dim AddNewFrontColour As Boolean = True
        Dim BackColour As String
        Dim FrontColour As String

        '============================================================
        '== Setup our Excel Class and open a new workbook to use ==
        '============================================================
        ExcelReport = New Microsoft.Office.Interop.Excel.ApplicationClass

        '-- Setting this to false and then resetting it to true at the end will stop
        '-- a user clicking around in excel and causing a possible error
        ExcelReport.Visible = False

        Dim pxlBook As Workbook
        pxlBook = ExcelReport.Workbooks.Add()
        ExcelReport.Worksheets("Sheet1").Select()
        ExcelReport.Sheets("Sheet1").Name = MyListView.Name

        '=======================================================
        '== Add all the colums from the listview into Excel ==
        '=======================================================
        i = 0
        Do Until i = MyListView.Columns.Count

            '==========================================================================
            '== Work out which colum we are on ==
            '== The Colums Go from A-Z and to IV, so A-Z to AA-AZ to BA-BZ etc etc ==
            '==========================================================================
            If i > MAX_COLUMS Then

                MsgBox("Too many Colums added")
                Exit Do

            End If
            TempColum = i
            TempColum2 = 0
            Do While TempColum > 25

                TempColum -= 26
                TempColum2 += 1

            Loop

            ColumLetter = Chr(97 + TempColum)
            If TempColum2 > 0 Then ColumLetter = Chr(96 + TempColum2) & ColumLetter

            '=======================================================
            '== Add all the colums from the listview into Excel ==
            '=======================================================
            ExcelReport.Range(ColumLetter & 1).Value = MyListView.Columns(i).Text
            ExcelReport.Range(ColumLetter & 1).Font.Name = MyListView.Font.Name
            ExcelReport.Range(ColumLetter & 1).Font.Size = MyListView.Font.Size
            i += 1

        Loop

        '==================================================================================
        '== Move to the second row in Excel and get all the items out of the list view ==
        '==================================================================================
        TempRow = 2
        For Each New_Item In MyListView.Items

            i = 0
            Do Until i = New_Item.SubItems.Count

                '==========================================================================
                '== Work out which colum we are on ==
                '== The Colums Go from A-Z and to IV, so A-Z to AA-AZ to BA-BZ etc etc ==
                '==========================================================================
                If i > MAX_COLUMS Then

                    MsgBox("Too many Colums added")
                    Exit Do

                End If

                TempColum = i
                TempColum2 = 0

                Do While TempColum > 25

                    TempColum -= 26
                    TempColum2 += 1

                Loop

                ColumLetter = Chr(97 + TempColum)
                If TempColum2 > 0 Then ColumLetter = Chr(96 + TempColum2) & ColumLetter

                '===========================================================================
                '== Add all the List View colums into Excel ==
                '== We also get the List Views font type and size and set it to the row ==
                '===========================================================================
                ExcelReport.Range(ColumLetter & TempRow).Value = New_Item.SubItems(i).Text
                ExcelReport.Range(ColumLetter & TempRow).Font.Name = New_Item.Font.Name
                ExcelReport.Range(ColumLetter & TempRow).Font.Size = New_Item.Font.Size

                '============================================================
                '== Reset the check to see if we have found a new colour ==
                '============================================================
                AddNewFrontColour = False
                AddNewBackColour = False
                Try

                    '===================================================================
                    '== Check our Colours Hashtable for a colour with the same name ==
                    '== as the backcolour of our listview item ==
                    '===================================================================
                    BackColour = MyColours(New_Item.BackColor.ToString)
                    If BackColour = "" Then AddNewBackColour = True

                    '===================================================================
                    '== Check our Colours Hashtable for a colour with the same name ==
                    '== as the Text colour of our listview item ==
                    '===================================================================
                    FrontColour = MyColours(New_Item.ForeColor.ToString)
                    If FrontColour = "" Then AddNewFrontColour = True

                Catch ex As Exception

                    AddNewFrontColour = False
                    AddNewBackColour = False

                End Try

                '=========================================================================
                '== If there is room for new colours and we have found some, add them ==
                '== to Excels palet ==
                '=========================================================================
                If AddedColours < MAX_COLOURS And (AddNewFrontColour Or AddNewBackColour) And (New_Item.BackColor.ToArgb <> -1) Then

                    If AddNewBackColour Then

                        MyColours.Add(New_Item.BackColor.ToString, AddedColours)
                        ExcelReport.Workbooks.Item(1).Colors(AddedColours) = RGB(New_Item.BackColor.R, New_Item.BackColor.G, New_Item.BackColor.B)
                        AddedColours += 1

                    End If

                    If AddNewFrontColour Then

                        MyColours.Add(New_Item.ForeColor.ToString, AddedColours)
                        ExcelReport.Workbooks.Item(1).Colors(AddedColours) = RGB(New_Item.ForeColor.R, New_Item.ForeColor.G, New_Item.ForeColor.B)
                        AddedColours += 1

                    End If

                End If

                '========================================================================
                '== Now all we need to do is select the rown and set the two colours ==
                '== Interior is back backgrounf and Font is the font colour ==
                '========================================================================
                ExcelReport.Rows(TempRow & ":" & TempRow).select()
                ExcelReport.Selection.Interior.ColorIndex = MyColours(New_Item.BackColor.ToString)
                ExcelReport.Selection.Font.ColorIndex = MyColours(New_Item.ForeColor.ToString)

                i += 1
            Loop
            TempRow += 1
        Next
        '=======================================================================
        '== Now all thats left to do is select all the colums and rows ==
        '== Resize them so they are all the right widths to see the data ==
        '== and finaly select from A1 so the user is taken to the start ==
        '=====================================================================       ExcelReport.Cells.Select()
        ExcelReport.Cells.EntireColumn.AutoFit()
        ExcelReport.Cells.Range("A1").Select()
        '-------------------------------------------The File Overwrite Prompt Disbled And Enabled
        Dim blnAlertStatus As Boolean
        '-------------------------------------------Turn off alerts
        blnAlertStatus = ExcelReport.DisplayAlerts
        ExcelReport.DisplayAlerts = False
        '-------------------------------------------do processing
        pxlBook.Close(SaveChanges:=True, FileName:="c:\Daily Purchases.xlsx")
        '-------------------------------------------Turn on alerts
        ExcelReport.DisplayAlerts = True

推荐答案

在代码中找到注释``移至Excel中的第二行，并将所有项目移出列表视图''. tempRow = 2就在下面.该行代码设置要插入数据的行.

因此，您需要的是下面的代码段，作为仅用于添加数据的新方法.您需要做的只是将tempRow设置为放置插入数据的行.

In the code you have found look for the comment ''Move to the second row in Excel and get all the items out of the list view''. Just underneath is tempRow = 2. That line of code sets the row at which the data will be inserted.

So what you need is the section of code from there down as a new method just for adding data. All you should need to do is set tempRow to the row where the inserted data should be placed.

这篇关于使用数据将数据添加到现有的Excel文件中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！