c# - Find starting row number and last row number in excel based on a value using epplus -

i'm putting query result excel using epplus. have column called category , need insert new row after each category include subtotal.

my excel looks this

category |  quantity        |    5        |    10        |    3     b    |    2     b    |    3     c    |    2 

the thing that's true start @ a2. category starts @ a2 , ends @ a4 assume don't know that, how last row can insert row after , category b , on.

expected results:

category |  quantity        |    5        |    10        |    3   total  |    18     b    |    2     b    |    3   total  |    5     c    |    2   total  |    2 

query putting data requested:

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; } 

new code:

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 = "";     } } 

the above code worked adding empty rows subtotal few errors, here's sample output

category |  quantity        |    5        |    10        |    3      b    |    2     b    |    3      c    |    2     d    |    1 

expected:

    c    |    2      d    |    1 

my current code has error if category has 1 row.

more edits: solve above problem adding rows after i've filled data.

 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;      }  } 

new problem: once reach end, example last row text in 10, object reference not set instance of object row 11

following simple example going, how last row of a.

        static void main(string[] args)         {             excelpackage ep = new excelpackage(new fileinfo(@"d:\temp\eptest.xlsx"));             excelworksheet ws = ep.workbook.worksheets.first();             //get cells text "a" in column "a"             var acells = cell in ws.cells["a:a"] cell.text.equals("a") select cell;             //to insert row after last identified "a" increment row number 1             ws.insertrow(acells.last().end.row + 1,1);             ep.save();         } 

to further understand , examples visit epplus.codeplex.com