Problem to refer to cell

HuaMin Chen
HuaMin Chen used Ask the Experts™
on
Hi,
I get Error and STacktrace below

Exception from HRESULT: 0x800A03EC    at System.RuntimeType.ForwardCallToInvokeMember(String memberName, BindingFlags flags, Object target, Int32[] aWrapperTypes, MessageData& msgData)
   at Microsoft.Office.Interop.Excel.Range.set__Default(Object RowIndex, Object ColumnIndex, Object value)
   at WindowsFormsApplication2.Form1.menuItem5_Click(Object sender, EventArgs e) in c:\dp13\General Application\WindowsFormsApplication5\WindowsFormsApplication2\Form1.cs:line 120

Open in new window

due to last line below
using System;
using System.IO;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using Microsoft.Office.Interop;
using Microsoft.Office.Interop.Excel;
using System.IO.Compression;
using System.Diagnostics;
using System.Text; // This was needed to make ASCIIEncoding function
using Excel = Microsoft.Office.Interop.Excel;

namespace WindowsFormsApplication2
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        ...
        private void menuItem5_Click(object sender, System.EventArgs e)
        {
            string File1;
            File1 = "C:/dp2/List0.txt";
            const string fileName = @"c:/dp2/List0.dat";

            int RowID = 0, ColID = 0;
            Stopwatch stopwatch = new Stopwatch();
            stopwatch.Start();

            try
            {
                OpenFileDialog openFileDialog1 = new OpenFileDialog();
                if (openFileDialog1.ShowDialog() == System.Windows.Forms.DialogResult.OK)
                {
                    //int rCnt = 0;
                    //int cCnt = 0;

                    var ExcelObj = new Excel.Application();
                    Excel.Workbook Book0 = ExcelObj.Workbooks.Open(openFileDialog1.FileName, 0, true, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true);

                    Excel.Worksheet Worksheet0;
                    Worksheet0 = (Excel.Worksheet)Book0.Worksheets.Add();
                    //((Excel.Worksheet)Worksheet0).Visible = Excel.XlSheetVisibility.xlSheetHidden;

                    //Excel.Worksheet Worksheet0 = (Excel.Worksheet)Book0.Worksheets.get_Item(1);
                    //Excel.Range Range0 = Worksheet0.UsedRange;

                    foreach (Excel.Worksheet sheet in Book0.Worksheets)
                    {
                        Excel.Range usedRange = sheet.UsedRange;

                        RowID++; Worksheet0.Cells[RowID, 1] = "^^^^" + sheet.Name + "^^^^"; RowID++;

                        //Iterate the rows in the used range
                        foreach (Excel.Range row in usedRange.Rows)
                        {
                            RowID++;
                            //Do something with the row.

                            //Ex. Iterate through the row's data and put in a string array
                            String[] rowData = new String[row.Columns.Count];
                            //int i = 0, j = 0;

                            for (int i = 0; i < row.Columns.Count; i++)
                            //while (j <= 15)
                            {
                                rowData[i] = Convert.ToString(row.Cells[1, i + 1].Value2);
                                /*if (!string.IsNullOrEmpty(rowData[i]))
                                    j = 0;
                                else
                                    j++;*/

                                if (!string.IsNullOrEmpty(rowData[i]) && rowData[i].IndexOf("@") < 0)
                                //if (!string.IsNullOrEmpty(rowData[i]))
                                {
                                    ColID++;
                                    Worksheet0.Cells[RowID, ColID] = rowData[i];
                                    ...

Open in new window

why?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Dustin SaundersCo-Founder and Chief Architect
Top Expert 2016

Commented:
Could you provide sample files to look at, and it seems your code is truncated, so it's hard to review.
HuaMin ChenProblem resolver

Author

Commented:
Here is the file that is leading to the problem.
HuaMin ChenProblem resolver

Author

Commented:
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Commented:
Hi HuaMinChen,
You should debug your code to get the exact line where you are getting this error.
You may get this error as you set RowID=0. Set RowID=1 then try the code if getting error because of invalid cell index.

below link will help you to understand the non-zero index in excel.

http://stackoverflow.com/questions/12714626/exception-from-hresult-0x800a03ec-error
HuaMin ChenProblem resolver

Author

Commented:
This line
Worksheet0.Cells[RowID, ColID] = rowData[i];

Open in new window


is producing the error. And I did check the value of RowID is over 10000 when the error happened.
Commented:
For MS Excel 2010 Row numbers ranges from 1 to 104857 in total 1048576 rows and Columns ranges from A to XFD in total 16384 columns.

If you check the last column in Excel sheet that will be XFD means total numbers of column in excel can not be more then 16384.

In your code, check the column index while getting the error. Column index must exceeded this range that's why you are getting this error. This error comes when your are trying to access the cell which does not exists in the excel sheet.
HuaMin ChenProblem resolver

Author

Commented:
Such codes
                    foreach (Excel.Worksheet sheet in Book0.Worksheets)
                    {
                        Excel.Range usedRange = sheet.UsedRange;

                        RowID++; Worksheet0.Cells[RowID, 1] = "^^^^" + sheet.Name + "^^^^"; RowID++;

                        //Iterate the rows in the used range
                        foreach (Excel.Range row in usedRange.Rows)
                        {
                            RowID++;
                            //Do something with the row.

                            //Ex. Iterate through the row's data and put in a string array
                            String[] rowData = new String[row.Columns.Count];
                            //int i = 0, j = 0;

                            for (int i = 0; i < row.Columns.Count; i++)
                            //while (j <= 15)
                            {
                                rowData[i] = Convert.ToString(row.Cells[1, i + 1].Value2);
                                /*if (!string.IsNullOrEmpty(rowData[i]))
                                    j = 0;
                                else
                                    j++;*/

                                //if (!string.IsNullOrEmpty(rowData[i]) && rowData[i].IndexOf("@") < 0)
                                if (!string.IsNullOrEmpty(rowData[i]))
                                {
                                    //ColID++;
                                    Worksheet0.Cells[RowID, i + 1] = rowData[i];
                                    Worksheet0.Cells[RowID, i + 1].Interior.Pattern = row.Cells[1, i + 1].Interior.Pattern;
                                    Worksheet0.Cells[RowID, i + 1].Interior.PatternColorIndex = row.Cells[1, i + 1].Interior.PatternColorIndex;
                                    Worksheet0.Cells[RowID, i + 1].Interior.Color = row.Cells[1, i + 1].Interior.Color;
                                    Worksheet0.Cells[RowID, i + 1].Interior.TintAndShade = row.Cells[1, i + 1].Interior.TintAndShade;
                                    Worksheet0.Cells[RowID, i + 1].Interior.PatternTintAndShade = row.Cells[1, i + 1].Interior.PatternTintAndShade;
                                    Worksheet0.Cells[RowID, i + 1].ColumnWidth = row.Cells[1, i + 1].ColumnWidth;
                                    /*using (BinaryWriter writer = new BinaryWriter(File.Open(fileName, FileMode.Append)))
                                    {
                                        writer.Write(rowData[i]);
                                    }
                                    
                                    using (StreamWriter sw = new StreamWriter(@File1, true, Encoding.Unicode))
                                    {
                                        if (rowData[i].IndexOf("@") < 0)
                                            sw.WriteLine(rowData[i]);
                                    }*/
                                }
                            }
                        }

                    }

Open in new window

are really very time-consuming. Any way to improve it?
HuaMin ChenProblem resolver

Author

Commented:
Hi,
I further check that the slowness is due to these sheets below
https://app.box.com/s/1tfkndfxp7tz3qflj1qvcjk2cj84dqyq

why?

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial