read excel rows in C#

[judithSampathwa]

hi there,

i have a code to open an excel file and get the information from the excel file, but how can i get the used rows in the excel file

appreciate a lot if someone could give a tutorial or some guidance

thankx

[kvprajapati]

>but how can i get the used rows in the excel file

Please let us know your thinking on "used" rows.

[abelLazm]

please check the following links these have similar discussion
1st link, ... 2nd Link..... 3rd link

[judithSampathwa]

>but how can i get the used rows in the excel file

Please let us know your thinking on "used" rows.

use rows are if there is nay data in the row
that i what i meant

[judithSampathwa]

please check the following links these have similar discussion
1st link, ... 2nd Link..... 3rd link

hi
the links you have provided is very usedful but it has a way to get the range. how can i get rows???

[Saikalyankumar]

http://social.msdn.microsoft.com/Forums/en-US/vsto/thread/b6e8a28c-6760-4e86-a1aa-e2ce9ec36380/ refer to the link for reading the values from the Excel sheet using C#

[judithSampathwa]

http://social.msdn.microsoft.com/Forums/en-US/vsto/thread/b6e8a28c-6760-4e86-a1aa-e2ce9ec36380/ refer to the link for reading the values from the Excel sheet using C#

hey thanks for the link

but in the link

in the post made by Daya Anand the loop is for 10 this is a fixed number,
in my case i do not know exactly the rows, so that is why i was looking for a way to get the rows in excel

how do i do this

if you could help me out with this
thankx

[Saikalyankumar]

You, can get the Range of Excel rows, that how many rows are used from the link right.

[judithSampathwa]

You, can get the Range of Excel rows, that how many rows are used from the link right.

hi

i used the below code

Microsoft.Office.Interop.Excel.Range xlRange = excelWorksheet.UsedRange;
            int col = xlRange.Columns.Count;
            int row = xlRange.Rows.Count;

it gives an error underlining the excelWorksheet saying it dows not exist in the current context what do i have to do???

[Momerath]

It means you don't have a variable named 'excelWorksheet' that is in scope

[judithSampathwa]

It means you don't have a variable named 'excelWorksheet' that is in scope

yeah i know that but how can i correct it

[Momerath]

By putting one in scope. It's really hard to debug your code when I can't see it from here. Post all relevant code, including any information on errors received and where they occur.

[judithSampathwa]

By putting one in scope. It's really hard to debug your code when I can't see it from here. Post all relevant code, including any information on errors received and where they occur.

below is the code, the bold code is the place that i get the error

public void UploadInfo()
        {
            string ProposalNo = "", proposalTitle = "", topicNo = "", phase = "", title = "", subTitle = "", agency = "",
                    type = "", status = "", pw = "", pr = "", pi = "", pm = "", session = "", sesAmt = "", sesDur = "", 
                    option = "", optAmt = "", optDur = "";

            Microsoft.Office.Interop.Excel.ApplicationClass tt = new Microsoft.Office.Interop.Excel.ApplicationClass();
                tt.Workbooks.Open(filePath,0, false, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "",
                true, false, 0, true, false, false);
            tt.Visible = false;



[B]            Microsoft.Office.Interop.Excel.Range xlRange = excelWorksheet.UsedRange;
            int col = xlRange.Columns.Count;
            int row = xlRange.Rows.Count;[/B]
            

            for(int r=2;r<296;r++)
            {
                ProposalNo = tt.get_Range("a"+r, "a"+r).Text.ToString();
                proposalTitle = tt.get_Range("b" + r, "b" + r).Text.ToString();
                topicNo = tt.get_Range("c" + r, "c" + r).Text.ToString();
                phase = tt.get_Range("d" + r, "d" + r).Text.ToString();
                title = tt.get_Range("e" + r, "e" + r).Text.ToString();
                subTitle = tt.get_Range("f" + r, "f" + r).Text.ToString();
                agency = tt.get_Range("g" + r, "g" + r).Text.ToString();
                type = tt.get_Range("h" + r, "h" + r).Text.ToString();
                status = tt.get_Range("i" + r, "i" + r).Text.ToString();
                pw = tt.get_Range("j" + r, "j" + r).Text.ToString();
                pr = tt.get_Range("k" + r, "k" + r).Text.ToString();
                pi = tt.get_Range("l" + r, "l" + r).Text.ToString();
                pm = tt.get_Range("m" + r, "m" + r).Text.ToString();
                session = tt.get_Range("n" + r, "n" + r).Text.ToString();
                sesAmt = tt.get_Range("o" + r, "o" + r).Text.ToString();
                sesDur = tt.get_Range("p" + r, "p" + r).Text.ToString();
                option = tt.get_Range("q" + r, "q" + r).Text.ToString();
                optAmt = tt.get_Range("r" + r, "r" + r).Text.ToString();
                optDur = tt.get_Range("s" + r, "s" + r).Text.ToString();

                if (!string.IsNullOrEmpty(phase))
                {
                    if (Int32.Parse(phase) == 1 || Int32.Parse(phase) == 0)
                    {
                        InsertProposalQuery(ProposalNo, proposalTitle, topicNo, phase, title, subTitle,
                            agency, type, status, session, sesAmt, sesDur, option, optAmt, optDur);
                    }

                }
                for (int l = 0; l < 1000000000; l++)
                { }
                ProposalNo = ""; proposalTitle = ""; topicNo = ""; phase = ""; title = ""; subTitle = ""; agency = "";
                    type = ""; status = ""; pw = ""; pr = ""; pi = ""; pm = ""; session = ""; sesAmt = ""; sesDur = ""; 
                    option = ""; optAmt = ""; optDur = "";
                

            }
            tt.Workbooks.Close();
            this.Hide();
            MessageBox.Show("Upload Complete!!","Message",MessageBoxButtons.OK,MessageBoxIcon.Information);
            Application.Exit();
        }

[Momerath]

Take a look at this link again and redo how you are opening the workbook. You are missing parts.

[judithSampathwa]

Take a look at this link again and redo how you are opening the workbook. You are missing parts.

HI thanks for the link
when i create the below line

topicNo = (string)(range.Cells[rCnt,3] as Excel.Range).Value2;

it gives an invalid cast exception "Unable to cast object of type 'System.Double' to type 'System.String'."

whay does this mean gow do i get as string???

[Momerath]

Put .ToString() on the end.

[judithSampathwa]

Put .ToString() on the end.

i tries it before then it give and nullReferenceException was unahndle

what do i have to do to this exception???

[Saikalyankumar]

Its an Instance creation problem create it with New keyword at the declaration part of the 'range' object of Excel or for the string which you are used to assign the value.

[judithSampathwa]

Its an Instance creation problem create it with New keyword at the declaration part of the 'range' object of Excel or for the string which you are used to assign the value.

how do i do that

[Saikalyankumar]

First you do one thing debug the program by putting a break point and observe where the nullrefference exception is coming, and tell me.

[judithSampathwa]

First you do one thing debug the program by putting a break point and observe where the nullrefference exception is coming, and tell me.

in the above mentioned code in the above post

in topicNo = (string)(range.Cells[rCnt, 3] as Excel.Range).Value2;

line

[Saikalyankumar]

topicNo=(string)((range.Cells[rCnt,3] as Excel.Range).Value2)

try this instead and reply me.

[judithSampathwa]

topicNo=(string)((range.Cells[rCnt,3] as Excel.Range).Value2)

try this instead and reply me.

it is still the same nullreference exception

Unable to cast object of type 'System.Double' to type 'System.String'.

same exception as previous

[Saikalyankumar]

Ok, some thing wrong in assigning the value to the string

topicNo=((xlRange.Cells[rCnt,3] as Microsoft.Office.Interop.Excel.Range).Value2).ToString();

use the above one.

[judithSampathwa]

Ok, some thing wrong in assigning the value to the string

topicNo=((xlRange.Cells[rCnt,3] as Microsoft.Office.Interop.Excel.Range).Value2).ToString();

use the above one.

this doesn't work either

[mukund1988]

I had the same problem. checked the o/p dynamically and found out a way.

Use topicNo = (range.Cells[rCnt, 3] as Excel.Range).Text instead of
topicNo = (range.Cells[rCnt, 3] as Excel.Range).Value2