Link to home
Start Free TrialLog in
Avatar of AlHal2
AlHal2Flag for United Kingdom of Great Britain and Northern Ireland

asked on

Count cells in Excel spreadsheet from within C#

I have a directory with thousands of Excel spreadsheets and I need to find the number of non blank cells in each excluding row and column headings.  I'm trying to do it using Excel's CountA function.
I'm running the code below which is giving this error message after looking at the first spreadsheet in the directory.  Please can you help?

"COM object that has been separated from its underlying RCW cannot be used"


using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.IO;
using System.Runtime.InteropServices;
using System.Threading;
using Excel = Microsoft.Office.Interop.Excel;

namespace WindowsFormsApplication4
{
    public partial class Form1 : Form
    {

        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            string OutputFile;
            long NumberOfNonBlankCells;
            Excel.Application xlApp;
            Excel.Workbook xlWorkBook;
            Excel.Worksheet xlWorkSheet;
            object misValue = System.Reflection.Missing.Value;
            xlApp = new Excel.ApplicationClass();
            OutputFile = "C:\\Output\\Test2.csv";
            try
            {
                using (StreamWriter sw = new StreamWriter(OutputFile))
                {

                    foreach (string inputfile in Directory.GetFiles("C:\\Output\\Test\\Sample"))
                    {
                        try
                        {
                            sw.Write(inputfile.ToString() + "\n");


                            xlWorkBook = xlApp.Workbooks.Open(inputfile, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
                            //xlApp.WorksheetFunction wsf = xlApp.WorksheetFunction;
                            Excel.WorksheetFunction wsf;

                            xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
                            xlApp.DisplayAlerts = false;

                            int result = (int)xlApp.WorksheetFunction.CountA(xlWorkSheet.Cells.get_Range("b2", "f100"),
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); //wsf.CountA(xlWorkSheet.Cells ,"S","S","K" );


                            xlWorkBook.Close(true, "C:\\test\\FileName.xls", misValue);
                            xlApp.Quit();
                            xlApp.DisplayAlerts = true;
                            releaseObject(xlWorkSheet);
                            releaseObject(xlWorkBook);
                            releaseObject(xlApp);
                           
                            sw.Write(inputfile.ToString() + "\t" + result  + "\n");


                        }
                        catch (Exception ex)
                        {
                            MessageBox.Show(ex.Message);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

        }
        private void releaseObject(object obj)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                obj = null;
            }
            catch (Exception ex)
            {
                obj = null;
                MessageBox.Show("Unable to release the Object " + ex.ToString());
            }
            finally
            {
                GC.Collect();
               
            }
        }

    }
}
ASKER CERTIFIED SOLUTION
Avatar of Barry Cunney
Barry Cunney
Flag of Ireland 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
Avatar of AlHal2

ASKER

It worked on my first test.  Then I got a different message
Exception from HRESULT: 0x800401A8
Avatar of AlHal2

ASKER

This is great.  I also changed
xlWorkBook.Close(true, "C:\\test\\FileName.xls", misValue);
to
xlWorkBook.Close(true, inputfile, misValue);
Avatar of AlHal2

ASKER

The error message was because I was referring to column zz in a 256 column worksheet.