File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

generate ipaddresse on excel file to telnet

 
wafa khan
Ranch Hand
Posts: 42
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1601
5
Android Java Java ME
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Paste your code in code tags, its completely unreadable. I have edited the code for you.

 
wafa khan
Ranch Hand
Posts: 42
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
/*
* 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
Posts: 42
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
/*
* 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
Posts: 42
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 42
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1601
5
Android Java Java ME
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
What is the problem are you facing?
 
wafa khan
Ranch Hand
Posts: 42
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1601
5
Android Java Java ME
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 42
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
yes so i don't know how can i test this code ?
 
Swastik Dey
Rancher
Posts: 1601
5
Android Java Java ME
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 42
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 42
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic