How can I count Rows and Columns

  • Thread starter Thread starter Jean Osorio
  • Start date Start date
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!!!
 
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...
 
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...
 
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...
 
Back
Top