使用vba / excel / c＃将WorkSheet分割成多个文件的解决方案 [英] Solution for Dividing WorkSheet into Multiple Files with vba/excel/c#

问题描述

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

I would like to divide a worksheet into multiple files.

我有一个约10,000行的工作表。有花式格式化，条件格式化，漂亮的颜色，我想保留所有这些属性。

I have a worksheet with about 10,000 rows. there is fancy formatting, conditional formatting, nice colors, and I want to preserve all of these attributes.

我需要把这个工作表分开。

I need to divide this worksheet up.

输入将是：

+-------+----+----+----+----+
| Alex  | 45 |  6 | 23 | 56 |
| Alex  | 61 | 47 | 56 | 56 |
| Liza  | 49 | 70 | 34 | 37 |
| Alex  | 33 | 30 | 22 | 39 |
| Tommy |    | 66 | 62 | 29 |
| Liza  |    | 38 | 49 | 80 |
| Alex  | 23 | 56 | 56 | 39 |
| Liza  | 32 | 46 | 40 | 43 |
| Liza  |    | 90 | 24 | 38 |
| Tommy | 38 | 10 | 52 | 23 |
| Nancy | 35 | 36 | 23 | 25 |
+-------+----+----+----+----+

，输出将是这样的单独文件（请记住，我想保留所有花哨的格式，因此该解决方案可以直接使用excel，而不是仅CSV （因为csv不能保留格式））

and the output would be separate files like this (please keep in mind i want to preserve all the fancy formatting, and thus the solution has work directly with excel, and not with just CSV (because csv cannot retain formatting))

最终产品：

+------+----+----+----+----+
|      |    |    |    |    |
| Alex | 45 |  6 | 23 | 56 |
| Alex | 61 | 47 | 56 | 56 |
| Alex | 33 | 30 | 22 | 39 |
| Alex | 23 | 56 | 56 | 39 |
+------+----+----+----+----+

和

+------+----+----+----+----+
|      |    |    |    |    |
| Liza | 49 | 70 | 34 | 37 |
| Liza |    | 38 | 49 | 80 |
| Liza | 32 | 46 | 40 | 43 |
| Liza |    | 90 | 24 | 38 |
+------+----+----+----+----+

和

+-------+----+----+----+----+
|       |    |    |    |    |
| Nancy | 35 | 36 | 23 | 25 |
+-------+----+----+----+----+

和

+-------+----+----+----+----+
|       |    |    |    |    |
| Tommy |    | 66 | 62 | 29 |
| Tommy | 38 | 10 | 52 | 23 |
+-------+----+----+----+----+

解决方案可以是VBA / .NET的组合。请注意，我需要多个文件作为输出。

the solution can be a combination of VBA/.NET. please note that i need multiple files as outputs.

什么是最快的方式来获得这个工作？非常感谢任何输入！

what is the quickest way to get this working? thanks so much for any input!

请注意，这是excel 2007和更高版本

please note that this is excel 2007 and later

推荐答案

由于Excel格式化通常在a **中有很大的痛苦，我建议尝试以下解决方案：

Since Excel formatting is usually a big pain in the a**, I would recommend to try a following solution:

1. 计算并存储所有唯一的密钥。


2. 为每个密钥创建一个文件副本（如 file_Alex.xls [x] ， file_Liza.xls [x] 等）。


3. 处理每个文件，删除所有非键行，因此所有键条目留下。另外因为你只是删除整行，所有文件中的格式都被保留。

1. Calculate and store all the unique keys.
2. Create a copy of a file for each key (like file_Alex.xls[x], file_Liza.xls[x] and so on).
3. Process each file, deleting all non-key rows, thus all key entries are left. Also because you are only deleting entire rows all the formatting in file is retained.

这是非常优化的，但也是非常简单的解决方案。如果这是一次性工作，那么应该做得很好。

This is very unoptimized, but also extremely simple solution. If it's a one-time job, it should do just fine.

这篇关于使用vba / excel / c＃将WorkSheet分割成多个文件的解决方案的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！