Repository navigation
Expand file tree
/
Copy pathExcelEditor.java
More file actions
executable file
·88 lines (79 loc) · 2.79 KB
/
Copy pathExcelEditor.java
File metadata and controls
executable file
·88 lines (79 loc) · 2.79 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
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;
}
}