Reading Specific Columns or other ideas?

Jun 9, 2013 at 2:15 PM
At the moment I need 3 specific columns from an xls file and have been loading the whole sheet into a datatable. From there I am deleting rows and columns I do not need. It is sort of working but also beginning to get sloppy I feel.

Does anyone have an idea for me to pull those columns directly or possibly another way to approach this? Thanks!
Jul 22, 2013 at 4:32 PM
If you only need the Cells from specific Columns you can try to do this:
private static List<ICell> GetSpecificColumnsCells(ISheet sheet, int fromColumn, int toColumn)
        {
            List<ICell> specificValues = new List<ICell>();

            //In here we are filtering ONLY the columns that we want
            var records = ((HSSFSheet)sheet).Sheet.GetValueRecords()
                                    .Where((v) => v.Column >= fromColumn && v.Column <= toColumn)
                                    .Select((s) => s);

            foreach (var record in records)
            {   //GetRow will never throw exception cause we are pulling the records from existing ones
                specificValues.Add(sheet.GetRow(record.Row).GetCell(record.Column));
            }

            return specificValues;
        }
Marked as answer by tonyqus on 11/17/2013 at 4:24 PM
Jul 22, 2013 at 7:46 PM
Edited Jul 22, 2013 at 7:48 PM
You could also use Linq magic with the DataTable:
var records = from a in _dataTable.AsEnumerable()
                  where a.Field<String>("Column1") == "SomeValue"
                  select a;
I like the DataTable data structure, even if it is a little passe with some developers. And I think it lends itself quite well as a wrapper for the Excel data format (columns, rows, etc).
Marked as answer by tonyqus on 11/17/2013 at 4:25 PM