Click here to Skip to main content
14,743,934 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
We are using Excel Interop in C# for Excel sheets formatting. The issue is ,I am getting above exception when i run an excel (2000kb). the weird thing is every time i get exception in different pages . Exception is not consistent. When i run an excel with 20 pages .i get exception in page 2. The same excel when i run i get exception in page 6. when i delete all other pages and run that particular page whc i was getting exception i will not get any exception at all.

Here is the code where i m getting exception.


i tried checking null and assigning values where it is .. it didnt work.

Please suggest what is wrong here.


What I have tried:

private void FixSheetHeaders(ref Excel.Application m_appTemplate)
        {
            Excel.Range rngUsed = null;
            Excel.Range rngHdrStatus = null;
            Excel.Range rngHdr = null;
            Excel.Range rngPageBreak = null;
            Excel.Range rngRedundentHdr = null;
            int iPBRow = 0;
            bool bHdr = false;
            object[,] arr_objValue = null;
            int iRowsToLoop = 0;

            try
            {
                Excel.Worksheet ws = m_wsCurrent;
                ws.DisplayPageBreaks = true;
                ws.DisplayPageBreaks = false;
                DoEvents();
                if (ws.HPageBreaks.Count > 0)
                {
                    if (PageBreakIssueFound(ref m_appTemplate) == true)
                    {
                        ResetPageBreak(ref m_appTemplate, true);
                        if (CallResetPageBreakAgainPageBreakRow() == true)
                        {
                            ResetPageBreak(ref m_appTemplate, true);
                        }
                        //m_appTemplate.ActiveWindow.View = Excel.XlWindowView.xlPageLayoutView;
                        DoEvents();
                        rngUsed = ws.UsedRange;
                        iRowsToLoop = rngUsed.Rows.Count;
                        rngUsed = ws.Cells[1, INT_COLUMN_INDEX];
                        rngUsed = rngUsed.get_Resize(iRowsToLoop, Type.Missing);
                        DoEvents();
                        arr_objValue = rngUsed.Value2;
                        iPBRow = ws.HPageBreaks[1].Location.Row;
                        bHdr = false;
                        DoEvents();


                        try
                        {

                            for (int i = 1; i <= iRowsToLoop; i++)
                            {
                                bHdr = false;
                                if (arr_objValue[i, 1] != null && arr_objValue[i, 1].ToString().Substring(0, 1) == "1")
                                {
                                    rngHdrStatus = rngUsed[i, 1];
                                    rngHdr = rngHdrStatus.EntireRow;
                                    bHdr = true;
                                    DoEvents();
                                }
                                if (bHdr == false && rngHdr != null && PageBreakRow(ws, i) == true)
                                {
                                    //madhu
                                    if (ws.Cells[i, 1] != null)
                                    {
                                        rngHdrStatus = ws.Cells[i, 1];
                                    }
                                    //  add header only when there is data
                                    if (arr_objValue[i, 1] != null)
                                    {
                                        //madhu
                                        if (arr_objValue[i - 1, 1] != null)
                                        {
                                            //check if the data in the last row is not header
                                            if (arr_objValue[i - 1, 1].ToString().Substring(0, 1) != "1")
                                            {
                                                rngHdr.Copy();
                                                iRowsToLoop += 1;
                                            }
                                            //if header, insert a row so the header is moved to new page
                                            else
                                            {
                                                rngRedundentHdr = rngUsed[i - 1, 1].EntireRow;
                                                rngRedundentHdr.Insert(Excel.XlInsertShiftDirection.xlShiftDown, Type.Missing);
                                                rngRedundentHdr = rngUsed[i - 1, 1].EntireRow;  //Hide the blank row that was inserted
                                                rngRedundentHdr.Hidden = true;
                                            }
                                        }
                                    }
                                    DoEvents();
                                    rngHdrStatus.Insert(Excel.XlInsertShiftDirection.xlShiftDown, Type.Missing);
                                    DoEvents();
                                    //madhu
                                    if (ws.Cells[i, 1] != null)
                                    {
                                        rngPageBreak = ws.Cells[i, 1];
                                        rngPageBreak.Worksheet.HPageBreaks.Add(rngPageBreak);
                                    }

                                    DoEvents();
                                    arr_objValue = rngUsed.Value2;
                                    m_appTemplate.ActiveWindow.LargeScroll(1, Type.Missing, Type.Missing, Type.Missing);
                                    DoEvents();
                                    m_iScroll += 1;
                                    if (ws.Cells[rngHdrStatus.Row + 10, 1] != null)
                                    {
                                        rngHdrStatus = ws.Cells[rngHdrStatus.Row + 10, 1];
                                        rngHdrStatus.Select();
                                    }
                                    DoEvents();
                                    ws.DisplayPageBreaks = false;
                                    ResetPageBreak(ref m_appTemplate, false);
                                    if (CallResetPageBreakAgainPageBreakRow() == true)
                                    {
                                        ResetPageBreak(ref m_appTemplate, false);
                                    }
                                }
                                else if (i > 0 && bHdr == true && PageBreakRow(ws, i) == true)
                                {
                                    if (ws.Cells[i, 1] != null)
                                    {
                                        rngHdrStatus = ws.Cells[i, 1];
                                        ws.HPageBreaks.Add(rngHdrStatus);
                                    }
Posted
Updated 23-Mar-20 23:57pm
Comments
Patrice T 24-Mar-20 2:24am
   
What is a 'page' in excel ?
Which line get the error ?
Member 14781099 24-Mar-20 2:57am
   
Page means sheet
I dont know which line exactly ,because in a sheet there will be 1000 rows to debug and exception behavior is not consistent.
Patrice T 24-Mar-20 3:09am
   
the line in your code, when error pop.
Patrice T 24-Mar-20 3:10am
   
Use Improve question to update your question.
So that everyone can pay attention to this information.

Quote:
Page means sheet
I dont know which line exactly ,because in a sheet there will be 1000 rows to debug and exception behavior is not consistent.

If you are going to learn to develop code, you need to know the terminology and use it correctly.
Excel has Sheets, not Pages: each sheet is made of y Rows of x Columns.
Code has Lines.

So when you are asked "Which line get the error?" it means "which line of the code in the pile you dumped on us does the error occur on?" and you are told pretty explicitly with the error message, and in the debugger it will stop on the erroneous line of code so you can see what is going on.

We don't have access to your data, and it's the data that is showing up the problem in your code - so you need both your code running and the data it is processing to start finding the error, and we can't do that for you.

So, it's going to be up to you.
Fortunately, you have a tool available to you which will help you find out what is going on: the debugger. If you don't know how to use it then a quick Google for "Visual Studio debugger" should give you the info you need.

Run your app in the debugger, and when the error occurs it will stop - you will see what line is giving the problem, and what specific data is involved. That may be enough for you to get an idea how to fix it, but if it isn't, but a breakpoint on the first line in the function, and run your code through the debugger. Then look at your code, and at your data and work out what should happen manually. Then single step each line checking that what you expected to happen is exactly what did. When it isn't, that's when you have a problem, and you can back-track (or run it again and look more closely) to find out why.

Sorry, but we can't do that for you - time for you to learn a new (and very, very useful) skill: debugging!
   
Arrays in C# are zero-base indexed. This means that, for an array of N elements, these elements are indexed from 0 to N-1, not from 1 to N.
In your code, each time you are indexing, you are using 1-based indices. You should correct that and see where that gets you:
// ...
rngUsed = ws.Cells[0, INT_COLUMN_INDEX];
// ...
for (int i = 0; i < iRowsToLoop; i++)
// ...
if (arr_objValue[i, 0] != null && arr_objValue[i, 0].ToString().Substring(0, 1) == "1")
// ...
rngHdrStatus = rngUsed[i, 0];
// etc.
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900