How can I count Rows and Columns

J

Jean Osorio

How can I count Rows and Columns on a Excel Worksheet, this is the code, but
columnas and rows always are equal to zero, does any body can help me
please!!!!. If I read then the data from the work sheet with out this when de
apps find an empty cell, I get an exception. Here is the code.


using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Office.Interop.Excel;
using System.Data.SqlClient;


namespace LeerExcel
{
class Program
{
static void Main(string[] args)
{
string Path = @"c:\Libro1.xlsx";
// initialize the Excel Application class
Microsoft.Office.Interop.Excel.ApplicationClass app = new
ApplicationClass();
// create the workbook object by opening the excel file.
Microsoft.Office.Interop.Excel.Workbook workBook =
app.Workbooks.Open(Path,
0,
true,
5,
"",
"",
true,

Microsoft.Office.Interop.Excel.XlPlatform.xlWindows,
"\t",
false,
false,
0,
true,
1,
0);
// get the active worksheet using sheet name or active sheet
Microsoft.Office.Interop.Excel.Worksheet workSheet =
(Microsoft.Office.Interop.Excel.Worksheet)workBook.ActiveSheet;
int index = 3;
object rowIndex = 4;
object colIndex2 = 2;
object colIndex3 = 3;
object colIndex4 = 4;
object colIndex5 = 5;
object colIndex6 = 6;
object colIndex7 = 7;
object colIndex8 = 8;
object colIndex9 = 9;
object colIndex10 = 10;
object colIndex11 = 11;
object colIndex12 = 12;
object colIndex13 = 13;
//object colIndex3 = 7;
Console.WriteLine("Nombre de la BD: ");
string basedeDatos = Console.ReadLine();
Console.WriteLine("Usuario: ");
string usuariobd = Console.ReadLine();
Console.WriteLine("Contraseña: ");
string contrasena = Console.ReadLine();
Console.WriteLine("Ingrese Nombre de Arrendamiento: ");
string arrendamiento = Console.ReadLine();
try
{


while
(((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex,
colIndex2]).Value2 != null)
{
//rowIndex = 2 + index;
rowIndex = index;
string RazonSocial;
string Rif;
string Contacto;
string Zona;
string Estado;
string Direcion;
string Telf;
string Serial;
object documentDate;
DateTime fechaInstalacion;
string Observaciones;
string Afiliado;
string terminalID;



try
{
RazonSocial =
((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex,
colIndex2]).Value2.ToString();
Rif =
((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex,
colIndex3]).Value2.ToString();
Contacto =
((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex,
colIndex4]).Value2.ToString();
Zona =
((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex,
colIndex5]).Value2.ToString();
Estado =
((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex,
colIndex6]).Value2.ToString();
Direcion =
((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex,
colIndex7]).Value2.ToString();
Telf =
((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex,
colIndex8]).Value2.ToString();
Serial =
((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex,
colIndex9]).Value2.ToString();
documentDate =
((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex,
colIndex10]).Value2;
fechaInstalacion =
DateTime.FromOADate(Convert.ToDouble(documentDate));
Observaciones =
((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex,
colIndex11]).Value2.ToString();
Afiliado =
((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex,
colIndex12]).Value2.ToString();
terminalID =
((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex,
colIndex13]).Value2.ToString();


Console.WriteLine(RazonSocial);
Console.WriteLine(Rif);
Console.WriteLine(Zona);
Console.WriteLine(Estado);
Console.WriteLine(Direcion);
Console.WriteLine(Telf);
Console.WriteLine(Serial);
Console.WriteLine(fechaInstalacion);
Console.WriteLine(Observaciones);
Console.WriteLine(Afiliado);
Console.WriteLine(terminalID);
Console.WriteLine();
}
catch (Exception e)
{
//Console.WriteLine(e.Message);
Console.WriteLine("Error en celda # {0}", index);
Console.ReadLine();
app.Quit();
}


index++;
}


app.Quit();
}


catch (Exception ex)
{


Console.WriteLine(ex.Message);
Console.ReadLine();
app.Quit();
}

}

}
}


Thanks for your helps!!!
 
J

JLGWhiz

Not sure what you are after, but maybe this will help.

The VBA syntax for counting rows and columns is:

rCnt = ActiveSheet.Rows.Count 'counts entire sheet
cCnt = ActiveSheet.Columns.Count 'counts entire sheet
rngRcnt = ActiveSheet.UsedRange.Rows.Count 'rows in Used Range
rngCcnt = ActiveSheet.UsedRange.Columns.Count 'cols om Used Range




Jean Osorio said:
How can I count Rows and Columns on a Excel Worksheet, this is the code,
but
columnas and rows always are equal to zero, does any body can help me
please!!!!. If I read then the data from the work sheet with out this when
de
apps find an empty cell, I get an exception. Here is the code.


using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Office.Interop.Excel;
using System.Data.SqlClient;


namespace LeerExcel
{
class Program
{
static void Main(string[] args)
{
string Path = @"c:\Libro1.xlsx";
// initialize the Excel Application class
Microsoft.Office.Interop.Excel.ApplicationClass app = new
ApplicationClass();
// create the workbook object by opening the excel file.
Microsoft.Office.Interop.Excel.Workbook workBook =
app.Workbooks.Open(Path,
0,
true,
5,
"",
"",
true,

Microsoft.Office.Interop.Excel.XlPlatform.xlWindows,
"\t",
false,
false,
0,
true,
1,
0);
// get the active worksheet using sheet name or active sheet
Microsoft.Office.Interop.Excel.Worksheet workSheet =
(Microsoft.Office.Interop.Excel.Worksheet)workBook.ActiveSheet;
int index = 3;
object rowIndex = 4;
object colIndex2 = 2;
object colIndex3 = 3;
object colIndex4 = 4;
object colIndex5 = 5;
object colIndex6 = 6;
object colIndex7 = 7;
object colIndex8 = 8;
object colIndex9 = 9;
object colIndex10 = 10;
object colIndex11 = 11;
object colIndex12 = 12;
object colIndex13 = 13;
//object colIndex3 = 7;
Console.WriteLine("Nombre de la BD: ");
string basedeDatos = Console.ReadLine();
Console.WriteLine("Usuario: ");
string usuariobd = Console.ReadLine();
Console.WriteLine("Contraseña: ");
string contrasena = Console.ReadLine();
Console.WriteLine("Ingrese Nombre de Arrendamiento: ");
string arrendamiento = Console.ReadLine();
try
{


while
(((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex,
colIndex2]).Value2 != null)
{
//rowIndex = 2 + index;
rowIndex = index;
string RazonSocial;
string Rif;
string Contacto;
string Zona;
string Estado;
string Direcion;
string Telf;
string Serial;
object documentDate;
DateTime fechaInstalacion;
string Observaciones;
string Afiliado;
string terminalID;



try
{
RazonSocial =
((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex,
colIndex2]).Value2.ToString();
Rif =
((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex,
colIndex3]).Value2.ToString();
Contacto =
((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex,
colIndex4]).Value2.ToString();
Zona =
((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex,
colIndex5]).Value2.ToString();
Estado =
((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex,
colIndex6]).Value2.ToString();
Direcion =
((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex,
colIndex7]).Value2.ToString();
Telf =
((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex,
colIndex8]).Value2.ToString();
Serial =
((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex,
colIndex9]).Value2.ToString();
documentDate =
((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex,
colIndex10]).Value2;
fechaInstalacion =
DateTime.FromOADate(Convert.ToDouble(documentDate));
Observaciones =
((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex,
colIndex11]).Value2.ToString();
Afiliado =
((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex,
colIndex12]).Value2.ToString();
terminalID =
((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex,
colIndex13]).Value2.ToString();


Console.WriteLine(RazonSocial);
Console.WriteLine(Rif);
Console.WriteLine(Zona);
Console.WriteLine(Estado);
Console.WriteLine(Direcion);
Console.WriteLine(Telf);
Console.WriteLine(Serial);
Console.WriteLine(fechaInstalacion);
Console.WriteLine(Observaciones);
Console.WriteLine(Afiliado);
Console.WriteLine(terminalID);
Console.WriteLine();
}
catch (Exception e)
{
//Console.WriteLine(e.Message);
Console.WriteLine("Error en celda # {0}", index);
Console.ReadLine();
app.Quit();
}


index++;
}


app.Quit();
}


catch (Exception ex)
{


Console.WriteLine(ex.Message);
Console.ReadLine();
app.Quit();
}

}

}
}


Thanks for your helps!!!
Click to expand...
 
J

Jacob Skaria

Once you get the worksheet object

If you are looking at total number of rows and columns
WorkSheet.Rows.Count
WorkSheet.Columns.Count
OR
Workbook.ActiveSheet.Rows.Count
Workbook.ActiveSheet.Columns.Count

If you are using MS 2007 (***i think...**)
WorkSheet.Rows.CountLarge
WorkSheet.Columns.CountLarge


If you are looking to get the rows used
Worksheet.Usedrange.rows.count
Worksheet.Usedrange.Columns.count


If this post helps click Yes
---------------
Jacob Skaria


Jean Osorio said:
How can I count Rows and Columns on a Excel Worksheet, this is the code, but
columnas and rows always are equal to zero, does any body can help me
please!!!!. If I read then the data from the work sheet with out this when de
apps find an empty cell, I get an exception. Here is the code.


using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Office.Interop.Excel;
using System.Data.SqlClient;


namespace LeerExcel
{
class Program
{
static void Main(string[] args)
{
string Path = @"c:\Libro1.xlsx";
// initialize the Excel Application class
Microsoft.Office.Interop.Excel.ApplicationClass app = new
ApplicationClass();
// create the workbook object by opening the excel file.
Microsoft.Office.Interop.Excel.Workbook workBook =
app.Workbooks.Open(Path,
0,
true,
5,
"",
"",
true,

Microsoft.Office.Interop.Excel.XlPlatform.xlWindows,
"\t",
false,
false,
0,
true,
1,
0);
// get the active worksheet using sheet name or active sheet
Microsoft.Office.Interop.Excel.Worksheet workSheet =
(Microsoft.Office.Interop.Excel.Worksheet)workBook.ActiveSheet;
int index = 3;
object rowIndex = 4;
object colIndex2 = 2;
object colIndex3 = 3;
object colIndex4 = 4;
object colIndex5 = 5;
object colIndex6 = 6;
object colIndex7 = 7;
object colIndex8 = 8;
object colIndex9 = 9;
object colIndex10 = 10;
object colIndex11 = 11;
object colIndex12 = 12;
object colIndex13 = 13;
//object colIndex3 = 7;
Console.WriteLine("Nombre de la BD: ");
string basedeDatos = Console.ReadLine();
Console.WriteLine("Usuario: ");
string usuariobd = Console.ReadLine();
Console.WriteLine("Contraseña: ");
string contrasena = Console.ReadLine();
Console.WriteLine("Ingrese Nombre de Arrendamiento: ");
string arrendamiento = Console.ReadLine();
try
{


while
(((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex,
colIndex2]).Value2 != null)
{
//rowIndex = 2 + index;
rowIndex = index;
string RazonSocial;
string Rif;
string Contacto;
string Zona;
string Estado;
string Direcion;
string Telf;
string Serial;
object documentDate;
DateTime fechaInstalacion;
string Observaciones;
string Afiliado;
string terminalID;



try
{
RazonSocial =
((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex,
colIndex2]).Value2.ToString();
Rif =
((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex,
colIndex3]).Value2.ToString();
Contacto =
((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex,
colIndex4]).Value2.ToString();
Zona =
((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex,
colIndex5]).Value2.ToString();
Estado =
((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex,
colIndex6]).Value2.ToString();
Direcion =
((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex,
colIndex7]).Value2.ToString();
Telf =
((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex,
colIndex8]).Value2.ToString();
Serial =
((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex,
colIndex9]).Value2.ToString();
documentDate =
((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex,
colIndex10]).Value2;
fechaInstalacion =
DateTime.FromOADate(Convert.ToDouble(documentDate));
Observaciones =
((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex,
colIndex11]).Value2.ToString();
Afiliado =
((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex,
colIndex12]).Value2.ToString();
terminalID =
((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex,
colIndex13]).Value2.ToString();


Console.WriteLine(RazonSocial);
Console.WriteLine(Rif);
Console.WriteLine(Zona);
Console.WriteLine(Estado);
Console.WriteLine(Direcion);
Console.WriteLine(Telf);
Console.WriteLine(Serial);
Console.WriteLine(fechaInstalacion);
Console.WriteLine(Observaciones);
Console.WriteLine(Afiliado);
Console.WriteLine(terminalID);
Console.WriteLine();
}
catch (Exception e)
{
//Console.WriteLine(e.Message);
Console.WriteLine("Error en celda # {0}", index);
Console.ReadLine();
app.Quit();
}


index++;
}


app.Quit();
}


catch (Exception ex)
{


Console.WriteLine(ex.Message);
Console.ReadLine();
app.Quit();
}

}

}
}


Thanks for your helps!!!
Click to expand...
 
J

Jean Osorio

Thanks Jacob, it works perfectly!!!!

Jacob Skaria said:
Once you get the worksheet object

If you are looking at total number of rows and columns
WorkSheet.Rows.Count
WorkSheet.Columns.Count
OR
Workbook.ActiveSheet.Rows.Count
Workbook.ActiveSheet.Columns.Count

If you are using MS 2007 (***i think...**)
WorkSheet.Rows.CountLarge
WorkSheet.Columns.CountLarge


If you are looking to get the rows used
Worksheet.Usedrange.rows.count
Worksheet.Usedrange.Columns.count


If this post helps click Yes
---------------
Jacob Skaria


Jean Osorio said:
How can I count Rows and Columns on a Excel Worksheet, this is the code, but
columnas and rows always are equal to zero, does any body can help me
please!!!!. If I read then the data from the work sheet with out this when de
apps find an empty cell, I get an exception. Here is the code.


using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Office.Interop.Excel;
using System.Data.SqlClient;


namespace LeerExcel
{
class Program
{
static void Main(string[] args)
{
string Path = @"c:\Libro1.xlsx";
// initialize the Excel Application class
Microsoft.Office.Interop.Excel.ApplicationClass app = new
ApplicationClass();
// create the workbook object by opening the excel file.
Microsoft.Office.Interop.Excel.Workbook workBook =
app.Workbooks.Open(Path,
0,
true,
5,
"",
"",
true,

Microsoft.Office.Interop.Excel.XlPlatform.xlWindows,
"\t",
false,
false,
0,
true,
1,
0);
// get the active worksheet using sheet name or active sheet
Microsoft.Office.Interop.Excel.Worksheet workSheet =
(Microsoft.Office.Interop.Excel.Worksheet)workBook.ActiveSheet;
int index = 3;
object rowIndex = 4;
object colIndex2 = 2;
object colIndex3 = 3;
object colIndex4 = 4;
object colIndex5 = 5;
object colIndex6 = 6;
object colIndex7 = 7;
object colIndex8 = 8;
object colIndex9 = 9;
object colIndex10 = 10;
object colIndex11 = 11;
object colIndex12 = 12;
object colIndex13 = 13;
//object colIndex3 = 7;
Console.WriteLine("Nombre de la BD: ");
string basedeDatos = Console.ReadLine();
Console.WriteLine("Usuario: ");
string usuariobd = Console.ReadLine();
Console.WriteLine("Contraseña: ");
string contrasena = Console.ReadLine();
Console.WriteLine("Ingrese Nombre de Arrendamiento: ");
string arrendamiento = Console.ReadLine();
try
{


while
(((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex,
colIndex2]).Value2 != null)
{
//rowIndex = 2 + index;
rowIndex = index;
string RazonSocial;
string Rif;
string Contacto;
string Zona;
string Estado;
string Direcion;
string Telf;
string Serial;
object documentDate;
DateTime fechaInstalacion;
string Observaciones;
string Afiliado;
string terminalID;



try
{
RazonSocial =
((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex,
colIndex2]).Value2.ToString();
Rif =
((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex,
colIndex3]).Value2.ToString();
Contacto =
((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex,
colIndex4]).Value2.ToString();
Zona =
((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex,
colIndex5]).Value2.ToString();
Estado =
((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex,
colIndex6]).Value2.ToString();
Direcion =
((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex,
colIndex7]).Value2.ToString();
Telf =
((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex,
colIndex8]).Value2.ToString();
Serial =
((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex,
colIndex9]).Value2.ToString();
documentDate =
((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex,
colIndex10]).Value2;
fechaInstalacion =
DateTime.FromOADate(Convert.ToDouble(documentDate));
Observaciones =
((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex,
colIndex11]).Value2.ToString();
Afiliado =
((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex,
colIndex12]).Value2.ToString();
terminalID =
((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex,
colIndex13]).Value2.ToString();


Console.WriteLine(RazonSocial);
Console.WriteLine(Rif);
Console.WriteLine(Zona);
Console.WriteLine(Estado);
Console.WriteLine(Direcion);
Console.WriteLine(Telf);
Console.WriteLine(Serial);
Console.WriteLine(fechaInstalacion);
Console.WriteLine(Observaciones);
Console.WriteLine(Afiliado);
Console.WriteLine(terminalID);
Console.WriteLine();
}
catch (Exception e)
{
//Console.WriteLine(e.Message);
Console.WriteLine("Error en celda # {0}", index);
Console.ReadLine();
app.Quit();
}


index++;
}


app.Quit();
}


catch (Exception ex)
{


Console.WriteLine(ex.Message);
Console.ReadLine();
app.Quit();
}

}

}
}


Thanks for your helps!!!
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Error in excel com: Microsoft.Office.Interop.Excel Exception from HRESULT: 0x800A03EC 3
How can i prevent the COMException 0x80010108 and 0x80010105? 1

Top