.NET Excel File Parser

Question

So the company I'm working for is looking for a means to verify that a given .xls/.xlsx file is valid. Which means checking columns and rows and other data. He's having me evaluate GrapeCity Spread and SpreadsheetGear, but I'm wondering if anyone else has any other suggestions of external tools to check out.

We don't need a means to export .xls files or anything like that, just the ability to import them and verify they are valid based on a set of criteria I create.

Thanks.

Answer 1

Depending on your budget, the Aspose libraries are great. Not cheap but work very, very well.

Answer 2

Check out Excel Data Reader on CodePlex. I've used this a few times and it works well.

Be warned however that there are bugs reading .xlsx files where cells are skipped. Apply this patch I submitted for v2.0.1.0 to fix the problem. (The project maintainers don't seem active and I've had problems contacting them.)

Answer 3

You can use the Microsoft.Office.Interop.Excel library to access any workbook the same way you do in Excel VBA.

Code looks like this:

using Excel = Microsoft.Office.Interop.Excel;

Excel.Application excel = new Excel.Application();
Excel.Workbook workbook = excel.Workbooks.Open("datasheet.xls");
Excel.Worksheet worksheet = workbook["Sheet1"] as Excel.Worksheet;

string someData = (worksheet.Range["A2"] as Excel.Range).Value.ToString();

worksheet = null;
workbook.Close();
excel.Quit();

Answer 4

To my mind, the easiest way to handle this is to use an ODBC Excel data provider. I find it more straightforward to work with than the PIAs.

// Connection string for Excel 2007 (.xlsx)
string dbConnStr = @"Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};Dsn=Excel Files;dbq=C:\temp\mySpreadsheet.xlsx";
// Connection string for Excel 98-2003 (.xls)
//string dbConnStr = @"Driver={Microsoft Excel Driver (*.xls)};driverid=790;dbq=C:\temp\mySpreadsheet.xls;defaultdir=c:\temp";

OdbcCommand cmd = new OdbcCommand("Select * from [SheetName$]", new OdbcConnection(dbConnStr));

cmd.Connection.Open();

OdbcDataReader dr = cmd.ExecuteReader();

foreach (System.Data.IDataRecord item in dr)
{
    // Check specific column values, etc
    string id = item["Column Name"].ToString();
}

Answer 5

you can use the oleDb from Microsoft to access the excel data as any other database system. You can get the right connection string from connectionstrings

Answer 6

If you need just to compare cell values you can use ADO.NET driver, for anything else will be required Excel or third party component. I am using SpreadsheetGear. When I was evaluating this component 3 years ago I have found an issue with conditional formatting for cell with absolute reference, but issue was quickly resolved. They have same day support response.

Answer 7

Maybe the NPOI project can be useful (I have never used it though).

Best

Answer 8

You can read a XLS file with Myxls library, is free and open source, you can see an example in the this url: Myxls example