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

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

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

>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

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???

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.

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();
        }

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???

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???

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.

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

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

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

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

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.

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

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

This article has been dead for over six months. Start a new discussion instead.