Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > How can I count Rows and Columns
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 4
Default How can I count Rows and Columns
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.xlWindow s,
"\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.C ells[rowIndex,
colIndex2]).Value2.ToString();
Rif =
((Microsoft.Office.Interop.Excel.Range)workSheet.C ells[rowIndex,
colIndex3]).Value2.ToString();
Contacto =
((Microsoft.Office.Interop.Excel.Range)workSheet.C ells[rowIndex,
colIndex4]).Value2.ToString();
Zona =
((Microsoft.Office.Interop.Excel.Range)workSheet.C ells[rowIndex,
colIndex5]).Value2.ToString();
Estado =
((Microsoft.Office.Interop.Excel.Range)workSheet.C ells[rowIndex,
colIndex6]).Value2.ToString();
Direcion =
((Microsoft.Office.Interop.Excel.Range)workSheet.C ells[rowIndex,
colIndex7]).Value2.ToString();
Telf =
((Microsoft.Office.Interop.Excel.Range)workSheet.C ells[rowIndex,
colIndex8]).Value2.ToString();
Serial =
((Microsoft.Office.Interop.Excel.Range)workSheet.C ells[rowIndex,
colIndex9]).Value2.ToString();
documentDate =
((Microsoft.Office.Interop.Excel.Range)workSheet.C ells[rowIndex,
colIndex10]).Value2;
fechaInstalacion =
DateTime.FromOADate(Convert.ToDouble(documentDate) );
Observaciones =
((Microsoft.Office.Interop.Excel.Range)workSheet.C ells[rowIndex,
colIndex11]).Value2.ToString();
Afiliado =
((Microsoft.Office.Interop.Excel.Range)workSheet.C ells[rowIndex,
colIndex12]).Value2.ToString();
terminalID =
((Microsoft.Office.Interop.Excel.Range)workSheet.C ells[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!!!

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,565
Default How can I count Rows and Columns
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" wrote in message
...
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.xlWindow s,
"\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.C ells[rowIndex,
colIndex2]).Value2.ToString();
Rif =
((Microsoft.Office.Interop.Excel.Range)workSheet.C ells[rowIndex,
colIndex3]).Value2.ToString();
Contacto =
((Microsoft.Office.Interop.Excel.Range)workSheet.C ells[rowIndex,
colIndex4]).Value2.ToString();
Zona =
((Microsoft.Office.Interop.Excel.Range)workSheet.C ells[rowIndex,
colIndex5]).Value2.ToString();
Estado =
((Microsoft.Office.Interop.Excel.Range)workSheet.C ells[rowIndex,
colIndex6]).Value2.ToString();
Direcion =
((Microsoft.Office.Interop.Excel.Range)workSheet.C ells[rowIndex,
colIndex7]).Value2.ToString();
Telf =
((Microsoft.Office.Interop.Excel.Range)workSheet.C ells[rowIndex,
colIndex8]).Value2.ToString();
Serial =
((Microsoft.Office.Interop.Excel.Range)workSheet.C ells[rowIndex,
colIndex9]).Value2.ToString();
documentDate =
((Microsoft.Office.Interop.Excel.Range)workSheet.C ells[rowIndex,
colIndex10]).Value2;
fechaInstalacion =
DateTime.FromOADate(Convert.ToDouble(documentDate) );
Observaciones =
((Microsoft.Office.Interop.Excel.Range)workSheet.C ells[rowIndex,
colIndex11]).Value2.ToString();
Afiliado =
((Microsoft.Office.Interop.Excel.Range)workSheet.C ells[rowIndex,
colIndex12]).Value2.ToString();
terminalID =
((Microsoft.Office.Interop.Excel.Range)workSheet.C ells[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!!!



Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 8,520
Default How can I count Rows and Columns
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" wrote:

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.xlWindow s,
"\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.C ells[rowIndex,
colIndex2]).Value2.ToString();
Rif =
((Microsoft.Office.Interop.Excel.Range)workSheet.C ells[rowIndex,
colIndex3]).Value2.ToString();
Contacto =
((Microsoft.Office.Interop.Excel.Range)workSheet.C ells[rowIndex,
colIndex4]).Value2.ToString();
Zona =
((Microsoft.Office.Interop.Excel.Range)workSheet.C ells[rowIndex,
colIndex5]).Value2.ToString();
Estado =
((Microsoft.Office.Interop.Excel.Range)workSheet.C ells[rowIndex,
colIndex6]).Value2.ToString();
Direcion =
((Microsoft.Office.Interop.Excel.Range)workSheet.C ells[rowIndex,
colIndex7]).Value2.ToString();
Telf =
((Microsoft.Office.Interop.Excel.Range)workSheet.C ells[rowIndex,
colIndex8]).Value2.ToString();
Serial =
((Microsoft.Office.Interop.Excel.Range)workSheet.C ells[rowIndex,
colIndex9]).Value2.ToString();
documentDate =
((Microsoft.Office.Interop.Excel.Range)workSheet.C ells[rowIndex,
colIndex10]).Value2;
fechaInstalacion =
DateTime.FromOADate(Convert.ToDouble(documentDate) );
Observaciones =
((Microsoft.Office.Interop.Excel.Range)workSheet.C ells[rowIndex,
colIndex11]).Value2.ToString();
Afiliado =
((Microsoft.Office.Interop.Excel.Range)workSheet.C ells[rowIndex,
colIndex12]).Value2.ToString();
terminalID =
((Microsoft.Office.Interop.Excel.Range)workSheet.C ells[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!!!

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 4
Default How can I count Rows and Columns
Thanks Jacob, it works perfectly!!!!

"Jacob Skaria" wrote:

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" wrote:

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.xlWindow s,
"\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.C ells[rowIndex,
colIndex2]).Value2.ToString();
Rif =
((Microsoft.Office.Interop.Excel.Range)workSheet.C ells[rowIndex,
colIndex3]).Value2.ToString();
Contacto =
((Microsoft.Office.Interop.Excel.Range)workSheet.C ells[rowIndex,
colIndex4]).Value2.ToString();
Zona =
((Microsoft.Office.Interop.Excel.Range)workSheet.C ells[rowIndex,
colIndex5]).Value2.ToString();
Estado =
((Microsoft.Office.Interop.Excel.Range)workSheet.C ells[rowIndex,
colIndex6]).Value2.ToString();
Direcion =
((Microsoft.Office.Interop.Excel.Range)workSheet.C ells[rowIndex,
colIndex7]).Value2.ToString();
Telf =
((Microsoft.Office.Interop.Excel.Range)workSheet.C ells[rowIndex,
colIndex8]).Value2.ToString();
Serial =
((Microsoft.Office.Interop.Excel.Range)workSheet.C ells[rowIndex,
colIndex9]).Value2.ToString();
documentDate =
((Microsoft.Office.Interop.Excel.Range)workSheet.C ells[rowIndex,
colIndex10]).Value2;
fechaInstalacion =
DateTime.FromOADate(Convert.ToDouble(documentDate) );
Observaciones =
((Microsoft.Office.Interop.Excel.Range)workSheet.C ells[rowIndex,
colIndex11]).Value2.ToString();
Afiliado =
((Microsoft.Office.Interop.Excel.Range)workSheet.C ells[rowIndex,
colIndex12]).Value2.ToString();
terminalID =
((Microsoft.Office.Interop.Excel.Range)workSheet.C ells[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!!!

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
count rows with content in some columns Jack Sons Excel Discussion (Misc queries) 11 August 5th 09 12:33 AM
Count rows with criteria from two columns? Bert Hyman Excel Worksheet Functions 3 January 16th 09 01:17 AM
To change Columns(4).Rows.Count Orlando Excel Programming 1 August 23rd 07 01:18 AM
Count entries in columns and rows Cliff Excel Worksheet Functions 3 April 6th 06 01:29 AM
Reduce columns and rows count? murat Excel Worksheet Functions 3 March 16th 05 07:43 PM


All times are GMT +1. The time now is 05:01 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement