jsp 导入excel到mysql_jsp使用POI 导入Excel到数据库

写一个excelInput.jsp页面(struts)

导入文件:

ExcelInputAction.java页面

if(action != null && action.equals("addSave")){

String path = null;

String myFileName = null;

FormFile file = eForm.getExcel_file();//取得上传的文件

System.out.println("11111111111111111111111111111111FormFile----111"+file);

try {

java.io.InputStream stream = file.getInputStream();//把文件读入

String filePath = request.getRealPath("/");//取当前系统路径

ByteArrayOutputStream baos = new ByteArrayOutputStream();

OutputStream bos = new FileOutputStream(filePath + "/ExcelInput/" +

time);//建立一个上传文件的输出流

myFileName = file.getFileName();//上传的文件名

path = filePath+"/ReportInput/"+myFileName;//可以自己起一个文件名保存到指定的目录，现在用原文件名

int bytesRead = 0;

byte[] buffer = new byte[8192];

while ( (bytesRead = stream.read(buffer, 0, 8192)) != -1) {

bos.write(buffer, 0, bytesRead);//将文件写入服务器

}

bos.close();

stream.close();

}catch(Exception e){

System.err.print(e.getMessage());

}

message = logic.readExcelToSql(path, myFileName, eForm, userId, userLevel, userName, ip);

}

ExcelInputLogic.java

public String readExcelToSql(String url, String myFileName, ExcelImportForm form, String userId, String userLevel, String userName, String ip){

POIFSFileSystem fs = null;

HSSFWorkbook wb = null;

String message = "";

FileInputStream stream = null;

try {

// str取得excel.xls路径

stream = new FileInputStream(url);

fs = new POIFSFileSystem(stream);

wb = new HSSFWorkbook(fs);

} catch (Exception e) {

System.out.println("poi ExcelImportLogic---"+e.getMessage());

if(fs == null) {

return "";

}

} finally {

try {

stream.close();

} catch (Exception ee) {

ee.getMessage();

}

}

HSSFSheet sheet = wb.getSheetAt(0);

HSSFRow row = null;

HSSFCell cell = null;

row = sheet.getRow(0);

cell = row.getCell((short) 0);

String type = cell.getStringCellValue();//根据文件头判断，文件上传是否正确

if(type.equals("Excel导入")){

message = readExcelToSql(sheet)

}

return message;

}

//调用ExcelInputLogic.java的另一个方法

public String readExcelToSql(HSSFSheet sheet){

String message = null;

DbConnection db = null;

int err = 0;

int rowNum = 0;

int count = 0;

try{

db = new DbConnection();

HSSFRow row = null;

HSSFCell cell = null;

// 名称列。注意：文本列要定义变量为String类型，而数值列要定义变量为Double类型

String name = "";

// 数值列

double qy_capital;

for (i = 4; i <= rowNum; i++) {//从第5行读取数据

System.out.println("i = "+i);

row = sheet.getRow(i);

if(row == null) {//过滤掉Excel的空行

continue;

}

//1

cell = row.getCell((short) 0);

if(cell == null || cell.getStringCellValue()).trim() == ""){//判断是否为空

name = "";

}else{

name = cell.getStringCellValue();//不为空时取值

}

//2

cell = row.getCell((short) 1);

if(cell == null) {//数值列不能为空，如果为空赋值0

numberColumn = 0;

}else if(cell.getCellType() == HSSFCell.CELL_TYPE_STRING){

message = "";

db.rollup();

return message;

}else {

qy_capital = cell.getNumericCellValue();

}

String sql = "insert into reports(name, numberColumn) values('"+name+"',"+numberColumn+")";

db.executeUpdate(sql);

}

db.commit();

} catch (Exception e) {

e.printStackTrace();

} finally {

db.close();

}

return message;

}

对应的Excel表为aaa.xls