|
|
EXCEL自动分类汇总不太方便，现改用C#操作，支持多列分类自动汇总，并且自动生成公式。
class CategorySum : IEquatable<CategorySum>
{
public string name;
public string model;
public double sum;
public string formula;
public bool Equals(CategorySum other)
{
if (other == null) return false;
return (this.name.Equals(other.name) && this.model.Equals(other.model));
}
}
private void button5_Click(object sender, EventArgs e)
{
//exceledit.ActivateSheet("1#");
List<CategorySum> categorysums = new List<CategorySum>();
for (int i = Convert.ToInt32(textBox1.Text); i <= Convert.ToInt32(textBox2.Text); i++)
{
string name = exceledit.ReadData(i, 3).Trim();
string model = exceledit.ReadData(i, 4).Trim();
double sum = 0;
string formula = "F" + i.ToString();
double.TryParse(exceledit.ReadData(i, 6),out sum );
if (name.Length != 0 || model.Length != 0)
{
if (!categorysums.Exists(x => (x.name == name && x.model == model)))
categorysums.Add(new CategorySum { name = name, model = model, sum = sum, formula = "=" + formula });
else
{
CategorySum find= categorysums.Find(x => (x.name == name && x.model == model));
find.sum += sum;
find.formula += "+"+formula;
}
}
}
int row=6;
foreach (CategorySum categorysum in categorysums)
{
exceledit.WriteData(categorysum.name , row, 12);
exceledit.WriteData(categorysum.model , row, 13);
exceledit.WriteData(categorysum.sum.ToString() , row, 14);
exceledit.WriteData(categorysum.formula , row, 15);
row++;
}
}
|
|
[复制链接]
/2 
发表于 2017-4-19 10:18:08
发表于 2017-5-1 17:59:20
