Count same value in column and write into a new excel file

Question

I would like to read an excel file with c# and count all the same values in a column and then write the value + how often I found the value in another excel file. So if I would have something like this:

      A            B          C
  *something*      110    *something*   
  *something*      120    *something*  
  *something*      120    *something*  
  *something*      130    *something*  
  *something*      110    *something*
  *something*      120    *something*  
  *something*      110    *something*

And my column is "B" that I want to count. I would like to get into a new excel an output something like this:

 A             B
 110           3
 120           3
 130           1

What is the easiest way?

score 0 · Accepted Answer · answered Aug 28 '12 at 00:55

Although it's a little advanced, I would suggest creating a PivotTable to do what you're asking. It's a perfect fit for this and doesn't require much coding.

Try this code out (don't forget to change "Same" for the actual column name and the Workbook location as well):

using System;
using System.Collections.Generic;
using System.Text;
using Excel = Microsoft.Office.Interop.Excel;

namespace ExcelFun01
{
    class Program
    {
        static void Main(string[] args)
        {
            Excel.Application xlApp = new Excel.Application();
            Excel.Workbook xlWb = xlApp.Workbooks.Open(@"C:\stackoverflow.xlsx");
            Excel.Worksheet xlWs = (Excel.Worksheet)xlWb.Sheets[1]; // Sheet1
            Excel.Worksheet xlWsNew = (Excel.Worksheet)xlWb.Sheets.Add();


            // find the data range
            Excel.Range dataRange = getDataRange(ref xlWs);

            // start by creating the PivotCache - this tells Excel that there is a data connection
            // to data inside the workbook (could be used to get external data, too)
            Excel.PivotCache pc = xlWb.PivotCaches().Create(Excel.XlPivotTableSourceType.xlDatabase
                                                            ,dataRange
                                                            ,Excel.XlPivotTableVersionList.xlPivotTableVersion14);

            // create the pivot table and set the destination to the new sheet at A1
            Excel.PivotTable pt = pc.CreatePivotTable(xlWsNew.Range["A1"]);

            // get the PivotField "Same" for easy referencing
            Excel.PivotField pf = (Excel.PivotField)pt.PivotFields("Same");

            // first add the count
            pt.AddDataField(pf, "Count of Same", Excel.XlConsolidationFunction.xlCount);

            // now add the row with the same field
            pf.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
            pf.Position = 1;

            // behold!!!
            xlWsNew.Select();
            xlApp.Visible = true;
        }

        private static Excel.Range getDataRange(ref Excel.Worksheet xlWs)
        {
            Excel.Range rng = xlWs.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell);
            Excel.Range dataRange = xlWs.Range["A1", rng.Address];
            return dataRange;
        }
    }
}

Here's what the PivotTable would look like:

enter image description here

Count same value in column and write into a new excel file

1 Answers1