根据列值将一个Excel工作表拆分为多个工作表 [英] Split one excel sheet into multiple sheets based on column value

问题描述

可能是它的重复但我在任何地方得到解决方案。



我想将一个excel文件分成多个工作表或多个文件。

我在VBA上获得了一些代码，但我在c＃window应用程序中严格需要它。我无法将VBA代码转换为c＃。



我的文件类似于：



名称月份工资

[A] 1月50k

[B] 1月55k

[A] 2月45K

[B ] 2月60K



我想为每个名字创建单独的表格，如：





名称月工资

[A] 1月50k

[A] 2月45K



和



名称月工资

[B] 1月55k

[B] 2月60K



我想要两个单独的文件。一个用于名称[A]，另一个用于名称[B]，每个文件上只有相应的行。



我尝试过：



我在VBA中使用的代码。但有些类在C＃中有所不同。所以我无法在C中实现它＃

May be its repeated but I dint get the solution anywhere.

I want to split an excel file into multiple sheets or multiple files.
I got some code on VBA but I strictly need it in c# window application. I am unable to convert the VBA code to c#.

I have file like:

Name Month Salary
[A] Jan 50k
[B] Jan 55k
[A] Feb 45K
[B] Feb 60K

I want to create separate sheet for each name like:


Name Month Salary
[A] Jan 50k
[A] Feb 45K

And

Name Month Salary
[B] Jan 55k
[B] Feb 60K

I want two separate files. One for Name [A] and other for Name [B] having their corresponding rows only on each file.

What I have tried:

The code I am working with in VBA. But some classes are different in C#. So I am unable to implement it in C#

Option Explicit
Sub SplitIntoSeperateFiles()

Dim OutBook As Workbook
Dim DataSheet As Worksheet, OutSheet As Worksheet
Dim FilterRange As Range
Dim UniqueNames As New Collection
Dim LastRow As Long, LastCol As Long, _
    NameCol As Long, Index As Long
Dim OutName As String

'set references and variables up-front for ease-of-use
Set DataSheet = ThisWorkbook.Worksheets("Sheet1")
NameCol = 1
LastRow = DataSheet.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
LastCol = DataSheet.Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
Set FilterRange = Range(DataSheet.Cells(1, NameCol), DataSheet.Cells(LastRow, LastCol))

'loop through the name column and store unique names in a collection
For Index = 2 To LastRow
    On Error Resume Next
        UniqueNames.Add Item:=DataSheet.Cells(Index, NameCol), Key:=DataSheet.Cells(Index, NameCol)
    On Error GoTo 0
Next Index

'iterate through the unique names collection, writing 
'to new workbooks and saving as the group name .xls
Application.DisplayAlerts = False
For Index = 1 To UniqueNames.Count
    Set OutBook = Workbooks.Add
    Set OutSheet = OutBook.Sheets(1)
    With FilterRange
        .AutoFilter Field:=NameCol, Criteria1:=UniqueNames(Index)
        .SpecialCells(xlCellTypeVisible).Copy OutSheet.Range("A1")
    End With
    OutName = ThisWorkbook.FullName
    OutName = Left(OutName, InStrRev(OutName, "\"))
    OutName = OutName & UniqueNames(Index)
    OutBook.SaveAs Filename:=OutName, FileFormat:=xlExcel8
    OutBook.Close SaveChanges:=False
    Call ClearAllFilters(DataSheet)
Next Index
Application.DisplayAlerts = True

End Sub

'safely clear all the filters on data sheet
Sub ClearAllFilters(TargetSheet As Worksheet)
    With TargetSheet
        TargetSheet.AutoFilterMode = False
        If .FilterMode Then
            .ShowAllData
        End If
    End With
End Sub

推荐答案

即使有最好的意愿，也无法给出你想要的答案。

Even with best will, it is impossible to give you the answer you want.

引用：

我想将一个excel文件拆分成多个工作表或多个文件。

I want to split an excel file into multiple sheets or multiple files.

这是解释你想要的唯一部分，但它是没有办法可以用来制作你想要的程序，所以任何关于这个问题的工作都是浪费时间。



现在，您可以做的最好的解释是发布您在VBA中的源代码。

This is the only part that explain what you want, but it is no way near something usable to make the program you want, so any work on the question is a waste of time.

For now, the best explanation you can do is publish the source code you have in VBA.

引用：

我对逻辑完全失明

C＃的逻辑与VBA完全相同。

因为你不喜欢得到逻辑，我怀疑你不是程序员。

程序员花了很多时间学习所有必要的技术并获得必要的知识。这就是为什么喜欢你的人，没有时间或者没有学习编程的费用，付钱让他们去做这个工作。



你的问题属于类别做我的工作/家庭作业免费，这就是为什么你会有最大的困难找到一个免费的人。

我最好的建议：聘请程序员。

The logic of the C# will be exactly the same as VBA.
since you don't get the logic, I suspect you are not a programmer.
Programmers spend a awful lot of time studying to learn all necessary techniques and acquire the necessary knowledge. That is why people like you, that have no time or no <ill to learn programming, pay them to do the job.

Your question fit in category "do my job/homework for free", that why you will have greatest difficulties to find someone to it for free.
My best advice: hire a programmer.

您可以在CopyData中执行类似的操作。将文件名发送到CopyData void CopyData（string strFile）。然后在range.Copy之后添加以下行。

You could do something like this in CopyData. Send the filename to CopyData void CopyData(string strFile). Then after range.Copy add the following lines.

Excel.Range range_name = sheetDest.get_Range(string.Format("E{0}", _currentRowCount), string.Format("E{0}", _currentRowCount + sheetRowCount));
range_name.Value = strFile;

这篇关于根据列值将一个Excel工作表拆分为多个工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！