Solved

Export xls data to datatable without using Microsoft.Interop

Posted on 2014-10-01
3
1,488 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 23

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 63

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

Is Your DevOps Pipeline Leaking?

Is your CI/CD pipeline a hodge-podge of randomly connected tools? You’ve likely got a tool to fix one problem & then a different tool to fix another, resulting in a cluster of tools with overlapping functionality. Learn how to optimize your pipeline with Gartner's recommendations

Question has a verified solution.

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

More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

688 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