我使用epplus将查询结果放入Excel中。我有一个名为类别的列,我需要在每个类别之后插入一个新行以包含一个小计。
我的excel看起来像
Category | Quantity
A | 5
A | 10
A | 3
B | 2
B | 3
C | 2
唯一真正正确的是我们总是从A2开始。A类从A2开始,以A4结束,但假设我们不知道,我该如何获得最后一行,以便我可以在此之后插入一行,然后插入B类等。
。预期结果:
Category | Quantity
A | 5
A | 10
A | 3
Total | 18
B | 2
B | 3
Total | 5
C | 2
Total | 2
查询根据要求放置数据的查询:
sqlcmd = new sqlcommand(query, con);
sqlreader = sqlcmd.executereader();
while (sqlreader.read())
{
ws.Cells[rownum, 1].Value = sqlreader["Category"];
ws.Cells[rownum, 2].Value = sqlreader["Quantity"];
rownum = rownum + 1;
}
新代码:
string currentcategory = "";
string newcategory = "";
while (sqlreader.read())
{
if (currentcategory == "")
{
currentcategory= global_dr["category"].ToString();
newcategory= global_dr["category"].ToString();
}
else
{
newcategory= global_dr["category"].ToString();
}
if (newcategory == currentcategory)
{
ws.Cells[rownum, 1].Value = sqlreader["category"];
ws.Cells[rownum, 2].Value = sqlreader["Quantity"];
rownum = rownum + 1;
}
else
{
rownum = rownum + 1;
ws.Cells[rownum, 1].Value = sqlreader["category"];
ws.Cells[rownum, 2].Value = sqlreader["Quantity"];
rownum = rownum + 1;
currentcategory = "";
}
}
上面的代码可用于为小计的一些空排添加一些错误,这是示例输出
Category | Quantity
A | 5
A | 10
A | 3
B | 2
B | 3
C | 2
D | 1
预期:
C | 2
D | 1
如果类别只有1行,我当前的代码有错误。
更多编辑:填写数据后,通过添加行来解决上述问题。
int rowstart = 1;
while (ws.Cells[rowstart, 1].Value.ToString() != "")
{
if (ws.Cells[rowstart, 1].Value.ToString() != ws.Cells[rowstart + 1, 1].Value.ToString())
{
ws.InsertRow(rowstart + 1, 1);
rowstart = rowstart + 2;
}
else
{
rowstart = rowstart + 1;
}
}
新问题:一旦到达结尾,示例最后一行带有10的文本,我将获得第11行的Object reference not set to an instance of an object
以下是一个简单的示例,可以帮助您完成如何获取A。
的最后一行 static void Main(string[] args)
{
ExcelPackage ep = new ExcelPackage(new FileInfo(@"d:tempEPTest.xlsx"));
ExcelWorksheet ws = ep.Workbook.Worksheets.First();
//Get all the cells with text "A" in column "A"
var acells = from cell in ws.Cells["A:A"] where cell.Text.Equals("A") select cell;
//To insert row after the last identified "A" increment the row number by 1
ws.InsertRow(acells.Last().End.Row + 1,1);
ep.Save();
}
要进一步理解和示例访问epplus.codeplex.com
类似的东西 - 您可能需要进行一些调试,因为我完全生锈了C#
const long ROW_START = 2;
var sqlcmd = new SqlCommand(query, con);
var sqlreader = sqlcmd.ExecuteReader();
string c;
double q;
string curr_c = "~~~~~~~~~";
double tot = 0;
long r = 0;
long rownum = ROW_START;
while (sqlreader.Read())
{
q = (double)sqlreader["Quantity"];
c = sqlreader["Category"].ToString();
if (c != curr_c)
{
if (rownum > ROW_START)
{
ws.Cells[rownum, 1].Value = "Total";
ws.Cells[rownum, 2].Value = tot;
rownum += 1;
}
curr_c = c;
tot = 0;
}
ws.Cells[rownum, 1].Value = c;
ws.Cells[rownum, 2].Value = q;
tot += q;
rownum += 1;
}
ws.Cells[rownum, 1].Value = "Total";
ws.Cells[rownum, 2].Value = tot;