CODEWITHSUNDEEP
c#.netexcelimport-from-excel
Leonard
Leonard

Reputation: 3092

Excel import automation

I've come to a complete standstill: I need to import data from Excel to a Microsoft SQL Server 2008 database. Certain columns, starting at specific rows, contains data information that I need to extract and properly insert into the database, and the file itself is provided as a stream.

I've been looking into Microsoft's solution Excel 12.0 Object Library, but it seems dependant on Office being installed in the production environment? Or is it possible to deploy an application to an environment where Office is not installed?

I've also cast a glance upon Excel Data Reader, but it doesn't seem to function for xlsx or Office 2010-documents, complaining about a 'bad file signature'.

What are your recommendations?

Upvotes: 0

Views: 932

Answers (4)

Alex Yang
Alex Yang

Reputation: 339

Did you have a chance to try: http://spread.grapecity.com/spread-services/ or https://www.nuget.org/packages/Spread.Services/? It gives you a comprehensive API to manipulate Microsoft Excel-compatible spreadsheets without MS office installed in target machines.

Upvotes: 0

Doozer Blake
Doozer Blake

Reputation: 7797

If you're just reading in the data and it's known what you want out of it, I typically just used OLEDB to read in the data and then do what I need to with that.

Quick sample using a DbDataReader to quickly go through the rows, giving a ConnectionString and SheetName

using System.Data.Common;
using System.Data.OleDb;

using (OleDbConnection connection = new OleDbConnection(ConnectionString))
{
    connection.ConnectionString = ConnectionString;
    connection.Open();
    using (DbCommand command = connection.CreateCommand())
    {
        command.CommandText = "SELECT * FROM [" + SheetName + "]";
        using (DbDataReader DR = command.ExecuteReader())
        {
            while (DR.Read())
            {
            // Read your data here.
            }
        }
    }
}

A different example can be found here.

Upvotes: 1

Stealth Rabbi
Stealth Rabbi

Reputation: 10346

I use Microsoft.Office.Interop.Excel for parsing excel files. Normally, yes, the computer deployed to would need to have Office installed, which would put the Interop libraries in the Global Assembly Cache. I get around this by copying the DLL in with the deployment project. You could also try embedding the interop library in to your assembly.

Here's an example using interop: http://support.microsoft.com/kb/302084

Upvotes: 1

Michał Powaga
Michał Powaga

Reputation: 23183

Excel Data Reader should be fine. Have you used:

//2. Reading from a OpenXml Excel file (2007 format; *.xlsx)
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);

to open your xlsx file?

Upvotes: 1

Related Questions