java-code-example/ExcelEditor/src/ExcelEditor.java at master · cherkavi/java-code-example · GitHub

History

executable file

88 lines (79 loc) · 2.79 KB

Raw

import java.io.File;

import java.sql.Connection;

import java.sql.PreparedStatement;

import jxl.Cell;

import jxl.Sheet;

import jxl.Workbook;

public class ExcelEditor {

public static void main(String[] args) throws Exception{

System.out.println("begin");

Workbook workbook=null;

Connection connection=null;

try{

connection=connector.get_connection_to_firebird("","D:/eclipse_workspace/Astronomy3/database/ASTRONOMY.GDB",null,"SYSDBA","masterkey");

// îòêðûòü ôàéë Excel

workbook=Workbook.getWorkbook(new File("D:\\eclipse_workspace\\Astronomy3\\Information\\City_maps_koordinates\\city_koordinates.xls"));

// ïðîèçâåñòè ìàíèïóëÿöèè

process(workbook,connection);

}finally{

try{

// çàêðûòü ôàéë Excel

workbook.close();

}catch(Exception ex){};

try{

connection.close();

}catch(Exception ex){};

}

System.out.println("-end-");

}

/** ïðîöåññ ðàáîòû ñ ôàéëîì Excel */

private static void process(Workbook workbook, Connection connection){

// Sheet sheet=workbook.getSheet(0);

// Cell[] columnOne=sheet.getColumn(0);

// System.out.println(sheet.getCell(columnCounter,rowCounter).getContents());

Sheet sheet=workbook.getSheet(0);

int rowCount=sheet.getRows();

for(int rowCounter=1;rowCounter<rowCount;rowCounter++){

if(sheet.getRow(rowCounter).length==0)continue;

String name=sheet.getCell(0,rowCounter).getContents();

if((name!=null)&&(!name.trim().equals(""))){

Float latitude=getFloat(sheet.getRow(rowCounter), 1);

Float longitude=getFloat(sheet.getRow(rowCounter), 3);

if((latitude!=null)&&(longitude!=null)){

System.out.println("City: "+name+" Latitude:"+latitude+" Longitude:"+longitude);

saveToDatabase(connection, name, longitude, latitude);

}else{

System.err.println("Row does not recognized:"+rowCounter);

}

}else{

// this row is empty

}

private static void saveToDatabase(Connection connection, String city, Float longitude, Float latitude){

PreparedStatement ps=null;

try{

ps=connection.prepareStatement("insert into a_city(name, longitude, latitude) values(?,?,?)");

ps.setString(1, city);

ps.setFloat(2, longitude);

ps.setFloat(3, latitude);

ps.executeUpdate();

ps.getConnection().commit();

}catch(Exception ex){

System.err.println("saveToDatabase Exception:"+ex.getMessage());

}finally{

try{

ps.close();

}catch(Exception ex){};

}

private static Float getFloat(Cell[] cells, int position){

Float returnValue=null;

try{

returnValue= Integer.parseInt(cells[position].getContents())+ ((float)Integer.parseInt(cells[position+1].getContents()))/60f;

}catch(Exception ex){

System.err.println("getFloat Exception:"+ex.getMessage());

}

return returnValue;

}

java-code-example/ExcelEditor/src/ExcelEditor.java at master · cherkavi/java-code-example · GitHub

Expand file tree

Breadcrumbs

ExcelEditor.java

Latest commit

History

Breadcrumbs

ExcelEditor.java

File metadata and controls