AlHal2
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.InteropServ ices;
using System.Threading;
using Excel = Microsoft.Office.Interop.E xcel;
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:\\Ou tput\\Test \\Sample") )
{
try
{
sw.Write(inputfile.ToStrin g() + "\n");
xlWorkBook = xlApp.Workbooks.Open(input file, 0, true, 5, "", "", true, Microsoft.Office.Interop.E xcel.XlPla tform.xlWi ndows, "\t", false, false, 0, true, 1, 0);
//xlApp.WorksheetFunction wsf = xlApp.WorksheetFunction;
Excel.WorksheetFunction wsf;
xlWorkSheet = (Excel.Worksheet)xlWorkBoo k.Workshee ts.get_Ite m(1);
xlApp.DisplayAlerts = false;
int result = (int)xlApp.WorksheetFuncti on.CountA( xlWorkShee t.Cells.ge t_Range("b 2", "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.C ells ,"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.ToStrin g() + "\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.InteropServ ices.Marsh al.Release ComObject( obj);
obj = null;
}
catch (Exception ex)
{
obj = null;
MessageBox.Show("Unable to release the Object " + ex.ToString());
}
finally
{
GC.Collect();
}
}
}
}
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.InteropServ
using System.Threading;
using Excel = Microsoft.Office.Interop.E
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.
xlApp = new Excel.ApplicationClass();
OutputFile = "C:\\Output\\Test2.csv";
try
{
using (StreamWriter sw = new StreamWriter(OutputFile))
{
foreach (string inputfile in Directory.GetFiles("C:\\Ou
{
try
{
sw.Write(inputfile.ToStrin
xlWorkBook = xlApp.Workbooks.Open(input
//xlApp.WorksheetFunction wsf = xlApp.WorksheetFunction;
Excel.WorksheetFunction wsf;
xlWorkSheet = (Excel.Worksheet)xlWorkBoo
xlApp.DisplayAlerts = false;
int result = (int)xlApp.WorksheetFuncti
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.C
xlWorkBook.Close(true, "C:\\test\\FileName.xls", misValue);
xlApp.Quit();
xlApp.DisplayAlerts = true;
releaseObject(xlWorkSheet)
releaseObject(xlWorkBook);
releaseObject(xlApp);
sw.Write(inputfile.ToStrin
}
catch (Exception ex)
{
MessageBox.Show(ex.Message
}
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message
}
}
private void releaseObject(object obj)
{
try
{
System.Runtime.InteropServ
obj = null;
}
catch (Exception ex)
{
obj = null;
MessageBox.Show("Unable to release the Object " + ex.ToString());
}
finally
{
GC.Collect();
}
}
}
}
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
This is great. I also changed
xlWorkBook.Close(true, "C:\\test\\FileName.xls", misValue);
to
xlWorkBook.Close(true, inputfile, misValue);
xlWorkBook.Close(true, "C:\\test\\FileName.xls", misValue);
to
xlWorkBook.Close(true, inputfile, misValue);
ASKER
The error message was because I was referring to column zz in a 256 column worksheet.
ASKER
Exception from HRESULT: 0x800401A8