Индекс находился за пределами массива.system.__comobjectsystem.__comobject
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.
Что я уже пробовал:
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);
}
Patrice T
Что такое "страница" в excel ?
В какой строке появляется ошибка ?
Member 14781099
Страница означает лист
Я не знаю ,какая именно строка, потому что в листе будет 1000 строк для отладки, а поведение исключений не согласовано.
Patrice T
строка в вашем коде, когда появляется ошибка.
Patrice T
Воспользуйся Улучшить вопрос чтобы обновить ваш вопрос.
Чтобы каждый мог обратить внимание на эту информацию.