CODEWITHSUNDEEP
c#excelms-officeoffice-interop
Yona
Yona

Reputation: 289

filter excel sheet

I have an open excel sheet that I have just written to, using Microsoft.Office.Interop.Excel. Now I want to remove lines which have certain values in certain columns. For example, removing all the lines that have the text blah blah in the column called col. Any ideas how to do that? Thanks!

Upvotes: 1

Views: 2232

Answers (2)

Conrad Lotz
Conrad Lotz

Reputation: 8818

My suggestion would be to remove the data (If what you meant by removing lines is in fat records) in the data set that you pass to the Excel Interop application when you create the worksheet. My assumption on what you require is to remove the entire row where that text is present in the column called "col".

For instance: If you pass it a DataTable you can run filters or delete from this object before passing it to the excel sheet

var dataTable = dataBase.FetchData(runDate.ToShortDateString());
DataTable fitleredDataTable = dataTable.Select("(col != 'blah blah')").CopyToDataTable();

var application = new Application();
var workbook = application.Workbooks.Add();
var worksheet = (Worksheet)workbook.Sheets[1];
var columns = fitleredDataTable.Columns.Count;
var rows = fitleredDataTable.Rows.Count;

var range = worksheet.Range["A2", String.Format("{0}{1}", GetExcelColumnName(columns), rows)];

var data = new object[rows, columns];

for (int i = 1; i < fitleredDataTable.Columns.Count; i++)
{
    worksheet.Cells[1, i] = fitleredDataTable.Columns[i - 1].ColumnName;
}
for (int rowNumber = 0; rowNumber < rows; rowNumber++)
{
    for (int columnNumber = 0; columnNumber < columns; columnNumber++)
    {
        data[rowNumber, columnNumber] = fitleredDataTable.Rows[rowNumber][columnNumber].ToString();
    }
}
range.Value = data;

Of course you can also filter it in you Stored Procedure or SQL query.

Upvotes: 3

Tokk
Tokk

Reputation: 4502

Perhaps you should consider to filter out the lines before writing them into the excel-sheet. This way you get rid of the additional work of writing then reading again and deleting lines.

So you would fetch your data (wherever it comes from), filter the list in c# (remove all items with the specific text in the columns) and write the clean list to excel in the end.

Upvotes: 0

Related Questions