poi如何解决excel导入数据库问题

Question

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.FileNotFoundException;
import java.util.HashMap;
import java.util.StringTokenizer;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;import java.sql.*;public class ReadExcel{
public static void main(String[] args){
String name = "";
int city_id = 0;
int price_index ;
int consume_index ;
String house_price = "";
String house_min_price = "";
String house_max_price = "";
String shop_price = "";
String shop_min_price = "";
String shop_max_price = "";

Connection conn = null;
Statement stmt = null;

try{

Class.forName("oracle.jdbc.driver.OracleDriver");
conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:oracle9i","careerdiary","careerdiary");
stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                                      ResultSet.CONCUR_UPDATABLE);  
    }catch(Exception e){
     e.printStackTrace();    
    }

try{
HSSFWorkbook workbook= new HSSFWorkbook(new FileInputStream("address.xls"));
HSSFSheet sheet = workbook.getSheetAt(0);

int maxRow = sheet.getLastRowNum();
int index = 1;
for(int i = 0; i <= maxRow; i++){
HSSFRow row = sheet.getRow(i);

HSSFCell cell = row.getCell((short)0);
name = cell.getStringCellValue();

cell = row.getCell((short)1);
if(cell.getCellType() == HSSFCell.CELL_TYPE_BLANK){
System.out.println(name);
city_id++;
continue;
}

price_index = (int)(cell.getNumericCellValue()*100);

cell = row.getCell((short)2);
consume_index = (int)(cell.getNumericCellValue());

cell = row.getCell((short)3);
house_price = cell.getStringCellValue();
house_min_price = house_price.substring(0, house_price.indexOf("-"));
house_max_price = house_price.substring(house_price.indexOf("-")+1, house_price.length());

cell = row.getCell((short)4);
shop_price = cell.getStringCellValue();
shop_min_price = shop_price.substring(0, shop_price.indexOf("-"));
shop_max_price = shop_price.substring(shop_price.indexOf("-")+1, shop_price.length());

System.out.print(index + " " + name + " ");
System.out.print(price_index + " ");
System.out.print(consume_index + " ");
System.out.print(house_min_price + " ");
System.out.print(house_max_price + " ");
System.out.print(shop_min_price + " ");
System.out.println(shop_max_price + " ");

String sql = "update area set"
+ " area_name = '" + name + "'"
+ " ,price_index = " + price_index
+ " ,consume_index = " + consume_index
+ " ,house_min_price = " + house_min_price
+ " ,house_max_price = " + house_max_price
+ " ,shop_min_price  = " + shop_min_price
+ " ,shop_max_price  = " + shop_max_price
+ " ,city_id = " + city_id
+ " where area_id = " + index;
System.out.println(sql);
try{
stmt.execute(sql);
}catch(SQLException e){
System.out.println(sql);
e.printStackTrace();
}

index++;
}

try{
stmt.close();
conn.close();
}catch(SQLException e){
e.printStackTrace();
}
}catch(FileNotFoundException e){
System.out.println("File not Found");
}catch(IOException e1){
e1.printStackTrace();
}
}
}

Answer 1

1.读取EXCEL文件
// ?量声明 
  java.lang.String strFileName; //文件名 
  java.io.File objFile; //文件?象 
  java.io.FileReader objFileReader; //?文件?象 
  char[] chrBuffer = new char[10]; //?冲 
  int intLength; //???出的字符数(一个中文?一个字符)    strFileName = "e:\\test.xls"; 
  String strNewFileName = "e:\\test1207.xls"; 
   FileInputStream inputStream = null;
inputStream = new FileInputStream(strFileName);

FileOutputStream fos = new FileOutputStream(strNewFileName);

HSSFWorkbook wb = new HSSFWorkbook(inputStream); 
//HSSFWorkbook wb = new HSSFWorkbook();

System.out.print(wb.getNumberOfSheets()+"\n"); 
System.out.print(wb.getNumberOfNames()); 
//    HSSFSheet s = wb.createSheet();\
HSSFSheet s = wb.getSheet("Sheet1");
HSSFRow row = s.getRow(1);
short j=1;

HSSFCell cell = row.getCell(j);
for (int i = 1; i <= 11; i++) {
if (cell == null) cell = row.createCell((short)3);
cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
cell.setCellValue(Math.round(Math.random()*1000));
System.out.println("cell========" + cell.getNumericCellValue());
}
j=2;

cell = row.getCell(j);
for (int i = 1; i <= 11; i++) {
if (cell == null) 
cell = row.createCell((short)3);
cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
cell.setCellValue(Math.round(Math.random()*1000));
System.out.println("cell========" + cell.getNumericCellValue());
}
wb.setSheetName(0, "lvxlTest"); wb.write(fos);
fos.close();  
2.写入数据库
fin = getFileIn().getInputStream();
int flength = getFileIn().getFileSize();
data = new byte[flength];
int i = 0;
int itotal = 0;
for (; itotal < flength; itotal = i + itotal) { i = fin.read(data, itotal, flength - itotal); }
fin.close();