CODEWITHSUNDEEP
c#.netexcel
Russak
Russak

Reputation: 111

.NET Excel File Parser

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.

Upvotes: 2

Views: 8092

Answers (7)

Alex Angas
Alex Angas

Reputation: 60027

Check out Excel Data Reader GitHub (formerly 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 (link is to Codeplex and out of date) 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.)

Upvotes: 0

Vagaus
Vagaus

Reputation: 4215

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

Best

Upvotes: 0

volody
volody

Reputation: 7189

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.

Upvotes: 2

Alex Pacurar
Alex Pacurar

Reputation: 5861

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

Upvotes: 0

Nick Bedford
Nick Bedford

Reputation: 4435

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();

Upvotes: 2

Guy Starbuck
Guy Starbuck

Reputation: 21873

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();
}

Upvotes: 2

Simon
Simon

Reputation: 994

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

Upvotes: 1

Related Questions