?
Solved

reading Excel via Microsoft.Office.Interop.Excel

Posted on 2008-11-14
9
Medium Priority
?
960 Views
Last Modified: 2013-11-05
Hello,

I'd like to read data from Excel file via Microsoft.Office.Interop.Excel. I want to get every column from Excel file into one string array.

thanks
0
Comment
Question by:xRalf
  • 7
  • 2
9 Comments
 
LVL 13

Expert Comment

by:kaylanreilor
ID: 22959857
           Microsoft.Office.Interop.Excel.Application ObjExcel = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel.Workbook ObjWorkBook;
            Microsoft.Office.Interop.Excel.Worksheet ObjWorkSheet;

            ObjWorkBook = ObjExcel.Workbooks.Open(@"C:\Temp\Temp.xls", false, true,
                                                  Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                                                  Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                                                  Type.Missing, Type.Missing, Type.Missing, Type.Missing);

... Adn then let see what the documentation proposes.
0
 
LVL 13

Expert Comment

by:kaylanreilor
ID: 22959917
Oups, sorry I forgot then :

ObjWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)ObjWorkBook.Sheets[1];
And then for example let's play with ObjWorkSheet.Cells collection.
0
 
LVL 6

Author Comment

by:xRalf
ID: 22959919
>> ... Adn then let see what the documentation proposes.

Well, and this is all this question about. There are many classes and methods in documentation and I want some advice which of them are convenient for my purposes...
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 13

Expert Comment

by:kaylanreilor
ID: 22959927
... Or ObjWorkSheet.Columns :)
0
 
LVL 13

Expert Comment

by:kaylanreilor
ID: 22960107
OK, you can try something like that :
            Microsoft.Office.Interop.Excel.Application ObjExcel = new Microsoft.Office.Interop.Excel.Application();
            Workbook ObjWorkBook;
            Worksheet ObjWorkSheet;

            ObjWorkBook = ObjExcel.Workbooks.Open(@"C:\Temp\Temp.xls", false, true,
                                                  Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                                                  Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                                                  Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            ObjWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)ObjWorkBook.Sheets[1];
            foreach (Range r in ObjWorkSheet.Rows)
            {
                System.Array myvalues = (System.Array)r.Cells.Value2;
                int a = 1;
            }
0
 
LVL 13

Expert Comment

by:kaylanreilor
ID: 22960210
Re-Oups... Sorry, you said strings :
            ObjWorkBook = ObjExcel.Workbooks.Open(@"C:\Temp\Temp.xls", false, true,
                                                  Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                                                  Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                                                  Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            ObjWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)ObjWorkBook.Sheets[1];
            foreach (Range r in ObjWorkSheet.Rows)
            {
                System.Array myvalues = (System.Array)r.Cells.Value2;
                string Value1 = myvalues.GetValue(1, 1) != null ? myvalues.GetValue(1, 1).ToString() : string.Empty;
                int a = 0;
            }

Open in new window

0
 
LVL 6

Author Comment

by:xRalf
ID: 22960399
Thanks,

and could you explain if I have the array

string [] _column1;

how to get the data from column1 in excel into column1? This is not clear for me from the above code.
0
 
LVL 13

Expert Comment

by:kaylanreilor
ID: 22961274
I propose that you look at the following code in debug (that is idea from the beginning) and then I guess you'll understand quickly what you can do. Look at the strings into the array. This code is buggee don't use it as final version of anything.
            StringBuilder[] arrayStrs = new StringBuilder[ObjWorkSheet.Columns.Count];
            Range r = ObjWorkSheet.Columns;
            for (int i = 0; i < ObjWorkSheet.Columns.Count; ++i)
            {
                arrayStrs[i] = new StringBuilder();
                foreach (Range rc in r.Cells)
                {
                    if (rc.Value2 == null)
                        continue;
                    arrayStrs[i].Append(rc.Value2.ToString());
                    arrayStrs[i].Append(",");
                }
            }

Open in new window

0
 
LVL 13

Accepted Solution

by:
kaylanreilor earned 1500 total points
ID: 22973674
Sorry but I hadn't any Visual Studio nor documentation when I wrote first post... Here is another code snipped closer from what you are looking for:
            int i = 0;
            int noOfDataRowsInExcelFile = ObjWorkSheet.get_Range("A65536", "A65536").get_End(XlDirection.xlUp).Row;
            Range end = ObjWorkSheet.Rows.get_End(XlDirection.xlDown);
            foreach (Range r in ObjWorkSheet.Rows)
            {
                if (i >= noOfDataRowsInExcelFile)
                    break;
                foreach (Range c in r.Columns)
                {
                    if (c.Value2 == null)
                        break;
                    if (arrayStrs[i] == null)
                        arrayStrs[i] = new StringBuilder();
                    arrayStrs[i].Append(c.Value2.ToString());
                    arrayStrs[i].Append(",");
                }
                ++i;
            }

Open in new window

0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Question has a verified solution.

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

This article is for Object-Oriented Programming (OOP) beginners. An Interface contains declarations of events, indexers, methods and/or properties. Any class which implements the Interface should provide the concrete implementation for each Inter…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Suggested Courses

621 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