How to select a group of rows that have specific values in a cell or column

Problem

Often it necessary to filter the rows on one worksheet based on a certain criteria and copy over the affected rows to a separate worksheet. It is fairly easy to achieve this result with a little coding.

ExcelApplication object allows you to fully parse and design the Excel document from your code, therefore giving you the ability to provide all the conditional logic to structure your final report.

Solution

Given the following data in one worksheet:

Company Name
PRICES
Date Open High Low Close Volume Adj Close*
5-Jul-06 20.47 20.47 20.13 20.28 336,400 20.28
3-Jul-06 20.97 21.07 20.85 21.05 115,000 21.05
30-Jun-06 21.27 21.27 21.02 21.13 215,700 21.13
29-Jun-06 20.4 21.02 20.38 21.02 314,000 21.02
28-Jun-06 20.37 20.37 20.05 20.14 403,900 20.14
27-Jun-06 20.24 20.24 19.8 19.86 257,700 19.86
26-Jun-06 20.2 20.27 20.05 20.22 703,700 20.22
23-Jun-06 20.45 20.45 20.17 20.2 302,500 20.2
22-Jun-06 20.3 20.5 20.07 20.26 291,700 20.26
21-Jun-06 20.08 20.45 20.04 20.31 160,000 20.31
20-Jun-06 20.12 20.2 20 20.03 278,500 20.03
19-Jun-06 20.33 20.37 20.06 20.11 301,100 20.11
16-Jun-06 20.03 20.31 20.03 20.16 480,800 20.16
15-Jun-06 19.82 20.5 19.82 20.42 301,000 20.42
14-Jun-06 19.4 19.66 19.38 19.61 211,000 19.61
13-Jun-06 19.25 19.53 18.96 19.09 523,700 19.09
12-Jun-06 20.45 20.56 20.11 20.11 239,900 20.11
9-Jun-06 20.16 20.53 20.11 20.22 297,600 20.22
8-Jun-06 19.3 20.21 19.25 20.15 1,641,200 20.15
7-Jun-06 21.35 21.41 21.21 21.29 684,700 21.29
6-Jun-06 22.15 22.15 21.51 21.67 299,000 21.67
5-Jun-06 22.51 22.51 21.89 21.91 293,300 21.91
2-Jun-06 22.63 22.78 22.5 22.7 754,100 22.7
1-Jun-06 21.9 22.24 21.86 22.24 281,200 22.24

**Criteria for selected rows (blue highlight) to copy is volume < 300,000

Result sheet should contain:

3-Jul-06 20.97 21.07 20.85 21.05 115,000 21.05
30-Jun-06 21.27 21.27 21.02 21.13 215,700 21.13
27-Jun-06 20.24 20.24 19.8 19.86 257,700 19.86
22-Jun-06 20.3 20.5 20.07 20.26 291,700 20.26
21-Jun-06 20.08 20.45 20.04 20.31 160,000 20.31
20-Jun-06 20.12 20.2 20 20.03 278,500 20.03
14-Jun-06 19.4 19.66 19.38 19.61 211,000 19.61
12-Jun-06 20.45 20.56 20.11 20.11 239,900 20.11
9-Jun-06 20.16 20.53 20.11 20.22 297,600 20.22
6-Jun-06 22.15 22.15 21.51 21.67 299,000 21.67
5-Jun-06 22.51 22.51 21.89 21.91 293,300 21.91
1-Jun-06 21.9 22.24 21.86 22.24 281,200 22.24

Algorithm

  1. Loop through the cells and evaluate your test expression. In this case the cells to test are in the Volume column.
  2. If the expression evaluates to false, move on to the next row value in our test column.
  3. If the expression evaluates to true, call a helper function CopyWorksheetRow (sample below) to copy the values of current row from original worksheet to the destination worksheet.

Helper Method


/// /// Copies values of up to 20 columns for a given row from the original sheet
/// to the destination sheet
/// /// Worksheet object from which to copy from
/// Worksheet object to which to copy to
/// Row number to copy from the origin to the
/// destination
public static void CopyWorksheetRow(Worksheet origin, Worksheet destination, int row_from)
{
// create an area of 1 row / 20 columns (Note our sample only has 7 columns)
Area selected_row = origin.CreateArea(row_from, 0, 1, 20);
// out of that area extract only the cells
// with values (Note this will select that area of only 7 columns)
Area populated_cells = selected_row.PopulatedCells;
for(int x = 0; x < populated_cells.ColumnCount; x++)
{
destination.Cells[row_to_start_at,x].Value = populated_cells[0, x].Value;
destination.Cells[row_to_start_at,x].Style = populated_cells[0, x].Style;
}
row_to_start_at++;
}

Code to test the condition


ExcelApplication xap = new ExcelApplication();
Workbook wb = xap.Open(Page.MapPath("datadoc.xls"));


Worksheet data_sheet = wb.Worksheets[0];
Worksheet filtered_datasheet = wb.Worksheets.CreateWorksheet("mysheet", 1);


// set up a loop to look throught the cells in column 6 (indexed as 5) from
// rows 6 to 71
for(int x = 5; x < 71; x++)
{
int cellval = int.Parse(data_sheet.Cells[x, 5].Value.ToString());


// if the cells value matches our criteria
// call a function to copy this row onto a separate sheet.
// in my case I want to copy the rows where
// the volume of stock traded is under 300,000
if(cellval < 300000)
{
CopyWorksheetRow(data_sheet, filtered_datasheet, data_sheet.Cells[x, 5].RowNumber);
}
}
//Save to disk on the server
xap.Save(wb, Page.MapPath("C:\\MyReports\\output.xls"));

Related posts: