4 replies to this topic

[ilbossmancato]

Hi everyone,

I need to write a code in C# that can read data inserted in excel file like in the picture (http://img714.images...0620101506.jpg/) (mainly I need to group the row CODE) and the tool have to find all the same information in the columns and then have to group this in an other sheet.

I want to know if is it possible and if anyone can help me.
I am avaible for more details.

Thank you in advance!

[williamevanl]

Yes but it's kind of tricky. :) You have to add the com references for excel.

using Microsoft.Office.Interop.Excel;
using Excel = Microsoft.Office.Interop.Excel;

This goes in header

Excel.Application Oapp;

 Oapp = new Excel.Application();
Oapp = new Excel.Application();                                 

object rowIndex = 1;                                            
object colIndex4 = 2;    


                        Oapp.Visible = false;                                            // hide it   
                        string workbookPath = @Loccom;
                        Excel.Workbook LoadBook = Oapp.Workbooks.Open(workbookPath,      //LoadBook = workbook open
                        0, true, 2, "", "", false, Excel.XlPlatform.xlWindows, "",
                        true, false, 0, true, false, false);
                        Excel.Sheets excelSheets = LoadBook.Worksheets;
                        Excel.Worksheet workSheet = (Excel.Worksheet)LoadBook.ActiveSheet;


string cellstring = ((Excel.Range)workSheet.Cells[rowIndex, colIndex4]).Value2.ToString();  

Use loops and things to get the information you want.
:)

[l@mbd@]

I much prefer LinqToExcel

And before the interop services, I would use OleDB:

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

// ...

    public static DataTable GetData(string documentPath, string worksheet)
    {

            OleDbConnection xlCon = new OleDbConnection(
                "Provider=Microsoft.Jet.Oledb.4.0;" +
                "Data Source=" + documentPath + ";" +
                "Extended Properties=\"Excel 8.0;HDR=Yes;\""
            );

            OleDbCommand getAll = new OleDbCommand(
                "SELECT * FROM [" + worksheet + "$]", xlCon
            );

            DataSet ds = null;

            try
            {
                OleDbDataAdapter adapter = new OleDbDataAdapter();
                adapter.SelectCommand = getAll;
                ds = new DataSet();
                adapter.Fill(ds);
                return ds.Tables[0];
            }
            catch( OleDbException ex ) { throw new ApplicationException(ex.Message); }
            finally
            {
                if( xlCon != null ) { xlCon.Close(); xlCon.Dispose(); }
                if( getAll != null ) getAll.Dispose();
                if( ds != null ) ds.Dispose();
            }
    }

it's an easy solution when all you need is to query the spreadsheet

cheers

[gokuajmes]

Please have a look around this article

http://www.davidhayden.com/blog/dave/archive/2006/05/26/2973.aspx

[While(!EOF)]

Note that you could use the old DDE way of transferring data from Microsofts applications :S but this should be avoided at all costs and if for some odd reason the software that your company has bought is about 20 years old and still uses DDE to transfer data then maybe you'd like to find some happy place during the day so you wont go insane. Just a warning, if you find anything that deals with DDE...dont listen to it...:D