Link to home
Start Free TrialLog in
Avatar of Peter Chan
Peter ChanFlag for Hong Kong

asked on

Problem to refer to cell

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?
Avatar of Dustin Saunders
Dustin Saunders
Flag of United States of America image

Could you provide sample files to look at, and it seems your code is truncated, so it's hard to review.
Avatar of Peter Chan

ASKER

Here is the file that is leading to the problem.
SOLUTION
Avatar of ROMA CHAUHAN
ROMA CHAUHAN
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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?
Hi,
I further check that the slowness is due to these sheets below
https://app.box.com/s/1tfkndfxp7tz3qflj1qvcjk2cj84dqyq

why?