将数据从一张纸复制到另一张 [英] Copying data from one sheet to another

问题描述

我有一张带有两张纸的Excel电子表格。用户想要做的是将一些数据从第二张表复制或转置到主表。诀窍是，一些用户数据跨越多行，但每个
属性使用一列。

I have an Excel spreadsheet with two sheets. What the user would like to have done is to have some of the data copied or transposed from the second sheet to the main sheet. The trick is, some of the user data spans multiple rows but uses one column for each attribute.

例如下面是从中复制/转置数据的第二张表。用户希望将F，G，H和I列复制到主工作表中。

For example below is the 2nd sheet where the data is being copied/transposed from. The user would like columns F,G,H, and I copied to the main sheet.

但是有些学生有多行，而FGHI列的行数取决于行。

However some students have multiple rows and the FGHI columns have different values depending on the row.

推荐答案

Hello JMiler，

Hello JMiler,

我在Visual中开发了以下代码Studio几年前。它仍然适用于我。希望能给你一个想法

I developed the code below in Visual Studio few years ago. it still works for me. hopefully give you an idea

Public Class ModifyExcelFile
    Dim xlApp As Excel.Application
    Dim xlWorkBook As Excel.Workbook
    Dim xlWorkSheet As Excel.Worksheet
    Dim xlRange As Excel.Range

    Dim FilePath_str As String
    Dim Sheet_str As String
    Dim WorkSheetIndex_int As Integer
    Dim NewName_str As String

    Dim Dcc_con_str As String
    Dim CnEVM_Obj As New SqlClient.SqlConnection
    Dim daEVM_Obj As New SqlClient.SqlDataAdapter
    Dim dsEVM_Obj As New DataSet
    Dim nIcondll As System.Windows.Forms.NotifyIcon
    Dim dbu As New dbUtility.MydbUtilities()


    Public Property Dcc_ConnString_ID()
        Get
            Return Dcc_con_str
        End Get
        Set(ByVal value)
            Dcc_con_str = value
        End Set
    End Property

    Public Property FilePath_ID()
        Get
            Return FilePath_str
        End Get
        Set(ByVal value)
            FilePath_str = value
        End Set
    End Property
    Public Property Sheet_ID()
        Get
            Return Sheet_str
        End Get
        Set(ByVal value)
            Sheet_str = value
        End Set
    End Property
    Public Property SheetIndex_ID()
        Get
            Return WorkSheetIndex_int
        End Get
        Set(ByVal value)
            WorkSheetIndex_int = value
        End Set
    End Property
    Public Property NewWorkSeetName()
        Get
            Return NewName_str
        End Get
        Set(ByVal value)
            NewName_str = value
        End Set
    End Property
    Private Sub OpenExcelFile()
        dbu.CheckExcellProcesses()
        xlApp = New Excel.ApplicationClass
        xlWorkBook = xlApp.Workbooks.Open(FilePath_ID)
    End Sub
    Private Sub OpenExcelFile(ByVal Sh As String)
        dbu.CheckExcellProcesses()
        xlApp = New Excel.ApplicationClass
        xlWorkBook = xlApp.Workbooks.Open(FilePath_ID)
        xlWorkSheet = xlWorkBook.Worksheets(Sh)
    End Sub
    Public Sub CreateNewSheet()
        If (FilePath_ID <> "") Then
            OpenExcelFile()
            xlWorkBook.Worksheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value)
            xlWorkBook.Save()
            xlWorkBook.Close()
            xlApp.Quit()
            xlWorkSheet = Nothing
            xlWorkBook = Nothing
            xlApp = Nothing
            dbu.KillXl()
        Else
            Windows.Forms.MessageBox.Show("Please Set File Path Property first", "Check File Path")
        End If
    End Sub
    Public Sub CopyAsNewFile()
        If (FilePath_ID <> "") Then
            Dim TheF As New FileInfo(FilePath_ID)
            OpenExcelFile(Sheet_ID)
            'xlWorkSheet.Copy(Missing.Value, Missing.Value)
            xlWorkBook.Worksheets.Copy(Missing.Value, Missing.Value)

            '            xlWorkBook.SaveAs("C:\Copy of " + TheF.Name,Excel.xl
            xlWorkBook.Close()
            xlApp.Quit()
            xlWorkSheet = Nothing
            xlWorkBook = Nothing
            xlApp = Nothing
            dbu.KillXl()
        Else
            Windows.Forms.MessageBox.Show("Please Set File Path Property first", "Check File Path")
        End If
    End Sub
    Public Sub CopyDatainNewSheet()
        OpenExcelFile(Sheet_ID)
        'xlWorkBook.Worksheets.Copy(After:=xlWorkBook.Worksheets(xlWorkBook.Worksheets.Count))
        xlWorkBook.Worksheets(SheetIndex_ID).Copy(Before:=xlWorkBook.Worksheets(1))

        xlWorkBook.Worksheets(1).Name = NewWorkSeetName
        xlWorkBook.Save()
        xlWorkBook.Close()
        xlApp.Quit()
        xlWorkSheet = Nothing
        xlWorkBook = Nothing
        xlApp = Nothing
        dbu.KillXl()
    End Sub
End Class

这篇关于将数据从一张纸复制到另一张的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！