aspose file tools*
The moose likes Sockets and Internet Protocols and the fly likes generate ipaddresse on excel file to telnet Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Java » Sockets and Internet Protocols
Bookmark "generate ipaddresse on excel file to telnet " Watch "generate ipaddresse on excel file to telnet " New topic
Author

generate ipaddresse on excel file to telnet

wafa khan
Ranch Hand

Joined: Apr 29, 2013
Posts: 42
i want telnet on ipaddresse's router who has the hostname and ipaddress on excel file so i create the code of excel and of telnetclient.java ans the classe routeur have 2 parameters hostname and ipaddress now i want create the main to test telnet on ipaddress of this file excel
this is my code java

----xls.java (to import the data from excel on arraylist<Router>----

public class xls {
private int ind;

public xls(){
}

public int GetInd(){
return ind;
}
public ArrayList<Router> XLS_Import(){
ArrayList<Router> liste = new ArrayList<Router>();
try {
Workbook workbook = Workbook.getWorkbook(new File("Liste.xls"));
Sheet sheet = workbook.getSheet(0);
int i = 2;

do {
liste.add(new Router(sheet.getCell(1, i).getContents(),
sheet.getCell(3, i).getContents()));
// affiche.append(sheet.getCell(1, i).getContents() + "\t " + sheet.getCell(3, i).getContents() + "\n");
i++;
} while (!(sheet.getCell(1, i).getContents().equals("Stop")));
} catch (IOException ex) {
Logger.getLogger(Router.class.getName()).log(Level.SEVERE, null, ex);
} catch (BiffException ex) {
Logger.getLogger(Router.class.getName()).log(Level.SEVERE, null, ex);
}
return liste;

}

-------------Routr.java----------
public class Router {
private String Hostname;
private String adresses;

public Router (String Hostname, String adresses)
{
this.Hostname = Hostname;
this.adresses = adresses;
}


public String getHostname()
{
return Hostname;
}

public String getAdresseip()
{
return adresses;
}
public String toString()
{
return Hostname +" "+ adresses;

}

}
----------Telnet.java------
/*
* To change this template, choose Tools | Templates
* and open the template in the editor.
*/
package telnet;

/**
*
* @author DNS Version Routeurs
*/
import org.apache.commons.net.telnet.*;

import java.io.*;
import java.net.ConnectException;
import java.net.NoRouteToHostException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.net.SocketTimeoutException;
import java.sql.*;
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

public class Telnet3
{
private TelnetClient telnet = new TelnetClient();
private InputStream in;
private PrintStream out;
static Statement st;
static ResultSet rs;
public static Connection con;

public Telnet3(){

}

public String Traitement(String server, String user, String password ){
StringBuffer sa = new StringBuffer();
StringBuffer sb = new StringBuffer();
char ch;
boolean bool=false;

try {
// Connect to the specified server
try{
telnet.setConnectTimeout(100);
telnet.connect( server, 23 );

}catch( SocketTimeoutException e ) {
return "route";
}/*catch( NoRouteToHostException e ) {
return "route";
}catch( ConnectException e ) {
return "route";
}*/

in = telnet.getInputStream();
out = new PrintStream( telnet.getOutputStream() );

//readUntil( "Username: " );
//write( user );
//readUntil( "Password: " );
//writePass( password );

ch = (char)in.read();
do{
ch = (char)in.read();
sb.append(ch);
System.out.print(ch);
}while(ch!='>' && ch!='#' && !sb.toString().contains("Username: "));

if(sb.toString().contains("Username: ")){
write( user );
readUntil( "Password: " );
writePass( password );

sb = new StringBuffer();

do{
ch = (char)in.read();
sb.append(ch);
System.out.print(ch);
//System.out.println("*");
}while(ch!='>' && ch!='#' && ch!='%');

if(ch=='%'){
in.close();
out.close();
telnet.disconnect();
return "No Tacacs";
}else{
in.close();
out.close();
telnet.disconnect();
return "OK";
}

}else{
in.close();
out.close();
telnet.disconnect();
return "No Login";
}



}
catch( Exception e ) {
return "route";
}
}


public String readUntil( String pattern ) {
try {
char lastChar = pattern.charAt( pattern.length() - 1 );
StringBuffer sb = new StringBuffer();
StringBuffer s = new StringBuffer();
boolean found = true;

char ch = ( char )in.read();
while( found ) {
sb.append( ch );
s.append('a');
if( ch == lastChar ) {
if( sb.toString().endsWith( pattern ) ) {
return "no";

}
}


ch = ( char )in.read();
}
}
catch( Exception e ) {
e.printStackTrace();
}
return null;
}




public void write( String value ) {
try {
out.println( value );
out.flush();
}
catch( Exception e ) {
e.printStackTrace();
}
}

public void write1( String value ) {
try {
out.println( value );
out.flush();

}
catch( Exception e ) {
e.printStackTrace();
}
}

public void writePass( String value ) {
try {
out.println( value );
out.flush();
}
catch( Exception e ) {
e.printStackTrace();
}
}



public void disconnect() {
try {
telnet.disconnect();
}
catch( Exception e ) {
e.printStackTrace();
}
}

}
-Main2.java( this is my problem)----
public class Main2 {
public static void main(String[] args) throws BiffException {

try{
xls xls= new xls();
Workbook workbook = Workbook.getWorkbook(new File(Liste.xls));
Sheet sheet = workbook.getSheet(0);

int i=0,j=0;

for (int i = 0; i < sheet.size(); i++)
{

List array = (List) sheet.GetInd(i);

for (int j = 0; j < array.size(); j++)
{
Telnet3 telnet = new Telnet3();
System.out.println(telnet.Traitement(sheet.getCell(3, i).getContents(), "user", "password"));
//sheet.getCell(3, i).getContents() = this is the cell where is the ipaddresse

}

}
}

catch (IOException ex) {
Logger.getLogger(xls.class.getName()).log(Level.SEVERE, null, ex);
}
}
Please help me i dont's know how can i resolve this problem
thank you all
Swastik Dey
Rancher

Joined: Jan 08, 2009
Posts: 1477
    
    6

Paste your code in code tags, its completely unreadable. I have edited the code for you.



Swastik
wafa khan
Ranch Hand

Joined: Apr 29, 2013
Posts: 42
/*
* To change this template, choose Tools | Templates
* and open the template in the editor.
*/
package telnet;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Vector;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.JFrame;
import javax.swing.JOptionPane;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.read.biff.BiffException;
import jxl.write.*;
import jxl.write.biff.RowsExceededException;

/**
*
* @author wassila
*/
public class xls {
private int ind;

public xls(){
}

public int GetInd(){
return ind;
}
public ArrayList<Router> XLS_Import(){
ArrayList<Router> liste = new ArrayList<Router>();
try {
Workbook workbook = Workbook.getWorkbook(new File("Liste.xls"));
Sheet sheet = workbook.getSheet(0);
int i = 2;

do {
liste.add(new Router(sheet.getCell(1, i).getContents(),
sheet.getCell(3, i).getContents()));
// affiche.append(sheet.getCell(1, i).getContents() + "\t " + sheet.getCell(3, i).getContents() + "\n");
i++;
} while (!(sheet.getCell(1, i).getContents().equals("Stop")));
} catch (IOException ex) {
Logger.getLogger(Router.class.getName()).log(Level.SEVERE, null, ex);
} catch (BiffException ex) {
Logger.getLogger(Router.class.getName()).log(Level.SEVERE, null, ex);
}
return liste;

}
/* public void XLS_Export(ArrayList<Liste_xls> liste){
JFrame controllingFrame = new JFrame();
ArrayList<adresses> address;
ind = 0;
try {

Workbook workbook = null;
int i,m;
boolean bool = false;
String stringa1 = null;
String version = null;

try {
workbook = Workbook.getWorkbook(new File("model/Model.xls"));
} catch (BiffException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
} catch (IOException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
Sheet sheet = workbook.getSheet(0);
WritableWorkbook copy = Workbook.createWorkbook(new File("Diagnostic/Route Diag.xls"), workbook);
WritableSheet Add = copy.getSheet(0);

WritableCellFormat formatRed = new WritableCellFormat(new WritableFont(WritableFont.TIMES,10,
WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.RED));

WritableCellFormat formatBlack = new WritableCellFormat(new WritableFont(WritableFont.ARIAL,10,
WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK));

System.out.println("Export XLS");
Label label2;
for(int n=0,ligne=0;n<liste.size();n++){
address = new ArrayList<adresses>(liste.get(n).GetListe());
System.out.println(n+"/"+liste.size());
for(i=0;i<address.size();i++){
if(address.get(i).getDiag().equals("NOK")){
ind++;
}
label2 = new Label(1,ligne+2, address.get(i).getAdpublic(),formatBlack);
Add.addCell(label2);
label2 = new Label(2,ligne+2, address.get(i).getMasque(),formatBlack);
Add.addCell(label2);
label2 = new Label(3,ligne+2, address.get(i).getGW(),formatRed);
Add.addCell(label2);
label2 = new Label(4,ligne+2, address.get(i).getDiag(),formatBlack);
Add.addCell(label2);
label2 = new Label(5,ligne+2, liste.get(n).GetRouter(),formatBlack);
Add.addCell(label2);
ligne++;
}
}
////////////////////////////////////////////////////////////////////

workbook.close();
copy.write();
copy.close();
} catch (WriteException ex) {
Logger.getLogger(xls.class.getName()).log(Level.SEVERE, null, ex);
} catch (IOException ex) {
Logger.getLogger(xls.class.getName()).log(Level.SEVERE, null, ex);
}

try {
Runtime.getRuntime().exec("rundll32 url.dll,FileProtocolHandler Diagnostic.xls");
} catch (Exception ee) {JOptionPane.showMessageDialog(controllingFrame, "there is a problem, "
+ "the Excel File can not be opened");}// afficher le fichier avec l'execution

} */
}
wafa khan
Ranch Hand

Joined: Apr 29, 2013
Posts: 42
/*
* To change this template, choose Tools | Templates
* and open the template in the editor.
*/
package telnet;

/**
*
* @author Youness
*/
public class Router {
private String Hostname;
private String adresses;

public Router (String Hostname, String adresses)
{
this.Hostname = Hostname;
this.adresses = adresses;
}


public String getHostname()
{
return Hostname;
}

public String getAdresseip()
{
return adresses;
}
public String toString()
{
return Hostname +" "+ adresses;

}

}

wafa khan
Ranch Hand

Joined: Apr 29, 2013
Posts: 42
here i have the problem


public class Main2 {
public static void main(String[] args) throws BiffException {

try{
xls xls= new xls();
Workbook workbook = Workbook.getWorkbook(new File(Liste.xls));
Sheet sheet = workbook.getSheet(0);

int i=0,j=0;

for (int i = 0; i < sheet.size(); i++)
{

List array = (List) sheet.GetInd(i);

for (int j = 0; j < array.size(); j++)
{
Telnet3 telnet = new Telnet3();
System.out.println(telnet.Traitement(sheet.getCell(3, 1).getContents(), "user", password"));

}

}
}

catch (IOException ex) {
Logger.getLogger(xls.class.getName()).log(Level.SEVERE, null, ex);
}
}
}
wafa khan
Ranch Hand

Joined: Apr 29, 2013
Posts: 42
im new member here so i'm sorry i don't know how paste the code like you did thank you so much
Swastik Dey
Rancher

Joined: Jan 08, 2009
Posts: 1477
    
    6

What is the problem are you facing?
wafa khan
Ranch Hand

Joined: Apr 29, 2013
Posts: 42
i have a excel file where i have 3 cell : Hostnamerouter + description+ipaddresse
i import this data on arraylist<Router> Router: means the class when i set an get the 2 parameters of routers : ip+hostname

i generete the xls file and import this data on arraylist
the PROBEM is in the class main:
i want telnet on router addresse ip whose in the excel file
like this telnet("this ip address i want chois it from th excel file note write the ip addresse like this 192.168.2.2","user","passwrd")

i'm sorry because i don't speak english very well so i hope you can understand what i want do thank you
Swastik Dey
Rancher

Joined: Jan 08, 2009
Posts: 1477
    
    6

I believe the Xls class is reading the data from the xls file and storing it the list, but in your main method I can't find any code where you are using this code. You seem to be repeating the steps of reading from the excel file.
wafa khan
Ranch Hand

Joined: Apr 29, 2013
Posts: 42
yes so i don't know how can i test this code ?
Swastik Dey
Rancher

Joined: Jan 08, 2009
Posts: 1477
    
    6

I don't have any way to compile and test the codes as I don't have all the required libraries. However You may wish to try the following main method, but do keep backup of your existing code before you make the change. Sorry there was a small error.

wafa khan
Ranch Hand

Joined: Apr 29, 2013
Posts: 42
okey .. thank you
i resolved the problem but if you don't have any problem can i add your address gmail if i have any problem can i contact you because i study network and now i have a project developpement java wth network in my final project of my master last year

thank you for your help
wafa khan
Ranch Hand

Joined: Apr 29, 2013
Posts: 42
the code that you send to me it's true thank you very well
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: generate ipaddresse on excel file to telnet