Solved

Export xls data to datatable without using Microsoft.Interop

Posted on 2014-10-01
3
1,348 Views
Last Modified: 2014-10-02
I currenlty have an application which exports data from an cls file to a datatable using Microsoft.Interop dll.
This requires Excel installed on any server this application is installed.
How can I export data from an xls file without using Microsoft.Interop dlls

public static void LoadFile(string filePath, string type)
        {
            Microsoft.Office.Interop.Excel.Application appExcel;
            Microsoft.Office.Interop.Excel.Workbook workbook;
            Microsoft.Office.Interop.Excel.Range range;
            Microsoft.Office.Interop.Excel._Worksheet worksheet;
         
                appExcel = new Microsoft.Office.Interop.Excel.Application();
                workbook = appExcel.Workbooks.Open(filePath, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
                worksheet = (Microsoft.Office.Interop.Excel._Worksheet)workbook.Sheets[1];
                range = worksheet.UsedRange;

                int rowCount = range.Rows.Count;
                int colCount = range.Columns.Count;
                System.Data.DataTable dt    = GetDataTable();
                System.Data.DataTable dtSpy = GetSPYDataTable();
                string strHoldingDate = (range.Cells[3, 2]).Value2.ToString();
                strHoldingDate = strHoldingDate.Replace("As of ", string.Empty);
                DateTime holdingDate = Convert.ToDateTime(strHoldingDate);
                try
                {
                    for (int Rnum = 5; Rnum <= rowCount; Rnum++)
                    {
                       // do something
                        dt.Rows.Add(drt);
                       
                       
                    }
                    dt.AcceptChanges();
                    workbook.Close(true);
                    appExcel.Quit();
                    DBManager.SaveData(dt);
             
                }
                catch (Exception ex)
                {

                    string message = ex.Message;
                }
                       

        }
0
Comment
Question by:countrymeister
3 Comments
 
LVL 22

Expert Comment

by:Snarf0001
ID: 40354902
Any chance you can limit the files to xlsx (2007+ format)?
If so, then look at epplus:
http://epplus.codeplex.com/

Works very well, no interop.
0
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 40354913
I haven't used it myself, but you might try using this library:

https://github.com/ExcelDataReader/ExcelDataReader

It is available in NuGet. It claims to only handle 1997 - 2007 Excel files, though. It does not appear to depend on the Interop libraries.
0
 
LVL 62

Accepted Solution

by:
Fernando Soto earned 500 total points
ID: 40355294
Hi countrymeister;

You could also use a NuGet package called linqtoexcel which allows you to use Linq to Excel query to load and extra data and fill your DataTable You can get more information at GitHub Welcome to the LinqToExcel project
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question