0

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

6
Contributors
25
Replies
32
Views
6 Years
Discussion Span
Last Post by mukund1988
Featured Replies
  • 1

    It means you don't have a variable named 'excelWorksheet' that is in [URL="http://msdn.microsoft.com/en-us/library/aa691132%28VS.71%29.aspx"]scope[/URL] Read More

0

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

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

0

>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

0

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

0

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

0

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.

0

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

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

0

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

0

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.

0

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

0

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

0

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

0
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

0

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.

0

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

0

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 topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.