vba 使用 EPPLUS 对 Excel 行进行分组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/41636336/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
grouping excel rows with EPPLUS
提问by Ion
what i need is group rows when the value of the row is the same as the previous row, the value of "B3" is the same of "B2", like this:
我需要的是当行的值与前一行相同时对行进行分组，“B3”的值与“B2”的值相同，如下所示：
i′m usin c# with epplus, and i see how can i do something similar to this with the outline option, and is similar to what i want, but this option have some disadvantages, such as that which doesn′t automatically group based on values and can′t do various groups....
我正在使用带有 epplus 的 c#，我看到如何使用大纲选项执行与此类似的操作，并且与我想要的类似，但是此选项有一些缺点，例如不会自动分组基于价值观，不能做各种团体....
is it possible to do this with EPPLUS? if it′s not posible, how can i add the vba code to c#?, i try this:
是否可以使用 EPPLUS 做到这一点？如果不可能，我如何将 vba 代码添加到 c#？，我试试这个：
StringBuilder vbaCode = new StringBuilder();
vbaCode.AppendLine("Sheets('Sheet1').Activate");
vbaCode.AppendLine("Range('A1: D11').Select");
vbaCode.AppendLine("Selection.Subtotal GroupBy:= 1, Function:= xlSum, TotalList:= Array(2, 3),Replace:= True, PageBreaks:= False, SummaryBelowData:= True");
pck.Save();
but not work, i can′t open the Excel file.
但不起作用，我无法打开 Excel 文件。
EDIT
编辑
With sugested now i try Interop with the group function, but for a extrain reason he is grouping columns not rows, this is the code:
使用 sugested 现在我尝试使用 group 函数进行互操作，但由于一个额外的原因，他将列分组而不是行分组，这是代码：
var ExApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbooks Wbs = ExApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook Wb = Wbs.Open(fi.FullName.ToString());
Microsoft.Office.Interop.Excel.Sheets wss = Wb.Worksheets;
Microsoft.Office.Interop.Excel.Worksheet Ws = (Microsoft.Office.Interop.Excel.Worksheet)wss.get_Item("Sheet1");
Ws.Range["A6:A10"].Group();
Ws.Outline.SummaryRow =Microsoft.Office.Interop.Excel.XlSummaryRow.xlSummaryAbove;
ExApp.Visible = true;
回答by Ernie S
I see this is already answered but figured I would provide an EPPlus way which you can certainly do but you do need to manually create the sum cells:
我看到这已经得到了回答，但我想我会提供一种 EPPlus 方式，您当然可以这样做，但您确实需要手动创建总和单元格：
[TestMethod]
public void Row_Grouping_Test()
{
//http://stackoverflow.com/questions/41636336/grouping-excel-rows-with-epplus
//Throw in some data
var datatable = new DataTable("tblData");
datatable.Columns.AddRange(new[]
{
new DataColumn("Header", typeof (string)), new DataColumn("Col1", typeof (int)), new DataColumn("Col2", typeof (int)), new DataColumn("Col3", typeof (object))
});
for (var i = 0; i < 10; i++)
{
var row = datatable.NewRow();
row[0] = $"Header {i}"; row[1] = i; row[2] = i * 10; row[3] = Path.GetRandomFileName(); datatable.Rows.Add(row);
}
//Create a test file
var fi = new FileInfo(@"c:\temp\Row_Grouping_Test.xlsx");
if (fi.Exists)
fi.Delete();
using (var pck = new ExcelPackage(fi))
{
var worksheet = pck.Workbook.Worksheets.Add("Sheet1");
worksheet.Cells.LoadFromDataTable(datatable, false);
worksheet.Cells["A11"].Value = "TOTAL";
worksheet.Cells["B11"].Formula = "SUBTOTAL(9,B2:B10)";
worksheet.Cells["C11"].Formula = "SUBTOTAL(9,C2:C10)";
worksheet.Row(11).Style.Font.Bold = true;
//Row Group 1 (start with 1 since row index is 1-based)
for (var i = 1; i <= datatable.Rows.Count; i++)
worksheet.Row(i).OutlineLevel = 1;
pck.Save();
}
}
Which looks like this:
看起来像这样：
回答by jonathana
I am not familiar with EPPLUS libary.
but you can achieve that task very easily using Microsoft.Office.Interop.Excelnamespace.
and than you can do it like you do it using Vba- (using the Merge()method)
note that you will need to add a reference of that namespace to your project.
here is an example:
我不熟悉 EPPLUS 库。
但是您可以使用Microsoft.Office.Interop.Excel命名空间非常轻松地完成该任务。
并且您可以像使用 Vba 那样进行操作-（使用该Merge()方法）请注意，您需要将该命名空间的引用添加到您的项目中。
这是一个例子：
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using Microsoft.Office.Interop.Excel;
namespace WindowsFormsApplication32
{
public partial class Form1 : Form
{
public Microsoft.Office.Interop.Excel.Application ExApp;
public Form1()
{
InitializeComponent();
}
private void Form1_Load(object sender, EventArgs e)
{
}
private void button1_Click(object sender, EventArgs e)
{
ExApp = new Microsoft.Office.Interop.Excel.Application();
// CREATE A NEW WORKBOOK (you can also open existing workbook using the Open() method)
Microsoft.Office.Interop.Excel.Workbook Wb = ExApp.Workbooks.Add();
// SET WORKSHEET
Microsoft.Office.Interop.Excel.Worksheet Ws = Wb.Worksheets.Add();
// MERGE CELLS (the answer to your question)
Ws.Range["A1:G5"].Merge();
// GROUP ROWS (the final answer to your question)
Ws.Rows["4:7"].Group();
ExApp.Visible = true;
}
}
}
