ssh mysql 导入_SSH框架中关于EXCEL表格导入到MySQL数据库

packagecom.action;importjava.io.File;importjava.io.FileInputStream;importjava.io.FileOutputStream;importjava.io.IOException;importjava.io.InputStream;importjava.io.OutputStream;importjava.util.List;importjava.util.Map;importjavax.servlet.http.HttpServletRequest;importjavax.servlet.http.HttpServletResponse;importjavax.servlet.http.HttpSession;importjxl.Sheet;importjxl.Workbook;importjxl.read.biff.BiffException;importorg.apache.poi.xssf.usermodel.XSSFCell;importorg.apache.poi.xssf.usermodel.XSSFRow;importorg.apache.poi.xssf.usermodel.XSSFSheet;importorg.apache.poi.xssf.usermodel.XSSFWorkbook;importorg.apache.struts2.ServletActionContext;importcom.pojo.BoatTable;importcom.service.BoatService;public classBoatAction {privateBoatService boatService;privateFile file;//文件名和文件类型必须为 文件名+FileName和 文件名+ContentType

privateString fileFileName;privateString fileContentTypt;publicFile getFile() {returnfile;

}public voidsetFile(File file) {this.file =file;

}publicString getFileFileName() {returnfileFileName;

}public voidsetFileFileName(String fileFileName) {this.fileFileName =fileFileName;

}publicString getFileContentTypt() {returnfileContentTypt;

}public voidsetFileContentTypt(String fileContentTypt) {this.fileContentTypt =fileContentTypt;

}publicBoatService getBoatService() {returnboatService;

}public voidsetBoatService(BoatService boatService) {this.boatService =boatService;

}/*** 导入船舶信息

* 上传

*@return*@throwsIOException*/

public String importBoatInfo() throwsIOException{

HttpServletRequest request=ServletActionContext.getRequest();

HttpSession session=request.getSession();

String path=ServletActionContext.getServletContext().getRealPath("/general");

InputStream in= newFileInputStream(file);

OutputStream out= new FileOutputStream(path+"/"+this.getFileFileName());byte[] b = new byte[1024];int size =in.read(b);while (size>0){

out.write(b,0,size);

size=in.read(b);

}

out.close();

in.close();

session.setAttribute("fileName", this.getFileFileName());return null;

}//导入数据库

public String intoDB() throwsIOException{

HttpServletRequest request=ServletActionContext.getRequest();

HttpSession session=request.getSession();

String path=ServletActionContext.getServletContext().getRealPath("/general");

String fileName= null;while(fileName == null){

fileName= (String) session.getAttribute("fileName");

}

File f= new File(path + "/" +fileName);try{//用jxl解析excel2003

if(this.getFileFileName()!=null && this.getFileFileName().indexOf(".xlsx")==-1){

Workbook book= Workbook.getWorkbook(f);//

Sheet sheet = book.getSheet(0); //获得第一个工作表对象

for (int i = 1; i < sheet.getRows(); i++) {

BoatTable boat= newBoatTable();

boat.setBenglishname(sheet.getCell(0, i).getContents());

boat.setBchinesename(sheet.getCell(1, i).getContents());

boat.setBregist(sheet.getCell(2, i).getContents());

boat.setBboat(sheet.getCell(3, i).getContents());

boat.setBrecongnize(sheet.getCell(4, i).getContents());

boat.setBmaterial(sheet.getCell(5, i).getContents());

boat.setBlength(Double.parseDouble(sheet.getCell(6, i).getContents()));

boat.setBwidth(Double.parseDouble(sheet.getCell(7, i).getContents()));

boat.setBdeep(Double.parseDouble(sheet.getCell(8, i).getContents()));

boat.setBsum(Integer.parseInt(sheet.getCell(9, i).getContents()));

boat.setBsulttle(Integer.parseInt(sheet.getCell(10, i).getContents()));

boat.setBgross(Integer.parseInt(sheet.getCell(11, i).getContents()));

boat.setBpower(Integer.parseInt(sheet.getCell(12, i).getContents()));

boat.setBlog(sheet.getCell(13, i).getContents());

boat.setBremark(sheet.getCell(14, i).getContents());

boat.setBcheck(sheet.getCell(15, i).getContents());

boat.setBwater(sheet.getCell(16, i).getContents());

boat.setBoperat(sheet.getCell(17, i).getContents());

boat.setBrange(sheet.getCell(18, i).getContents());

boat.setBkind(sheet.getCell(19, i).getContents());

boat.setBowner(sheet.getCell(20, i).getContents());//添加到数据库

boatService.addOneBoat(boat);

}

}else{ //用poi解析excel2007//构建1个工作簿对象

XSSFWorkbook xwb = null;try{

xwb= new XSSFWorkbook(newFileInputStream(f.getPath()));

}catch(Exception e) {

e.printStackTrace();

}//得到第一张工作表

XSSFSheet xSheet = xwb.getSheetAt(0);//循环工作表的每一行

for (int rowNum = 1; rowNum <= xSheet.getLastRowNum(); rowNum++) {

XSSFRow xRow=xSheet.getRow(rowNum);if (xRow == null) {continue;

}

BoatTable boat= newBoatTable();

boat.setBenglishname(xRow.getCell(0).getStringCellValue());

boat.setBchinesename(xRow.getCell(1).getStringCellValue());

boat.setBregist(xRow.getCell(2).getStringCellValue());if(xRow.getCell(3).getCellType() ==XSSFCell.CELL_TYPE_NUMERIC){

boat.setBboat(Double.valueOf(xRow.getCell(3).getNumericCellValue()).toString());

}else{

boat.setBboat(xRow.getCell(3).getStringCellValue());

}

boat.setBrecongnize(xRow.getCell(4).getStringCellValue());

boat.setBmaterial(xRow.getCell(5).getStringCellValue());

boat.setBlength(xRow.getCell(6).getNumericCellValue());

boat.setBwidth(xRow.getCell(7).getNumericCellValue());

boat.setBdeep(xRow.getCell(8).getNumericCellValue());

boat.setBsum((int)xRow.getCell(9).getNumericCellValue());

boat.setBsulttle((int)xRow.getCell(10).getNumericCellValue());

boat.setBgross((int)xRow.getCell(11).getNumericCellValue());

boat.setBpower((int)xRow.getCell(12).getNumericCellValue());

boat.setBlog(xRow.getCell(13).getStringCellValue());

boat.setBremark(xRow.getCell(14).getStringCellValue());//如果单元格里面为数字，用String取得话会报异常，故有可能为数字的都要判断一下

if(xRow.getCell(15).getCellType() ==XSSFCell.CELL_TYPE_NUMERIC){

boat.setBcheck(Double.valueOf(xRow.getCell(15).getNumericCellValue()).toString());

}else{

boat.setBcheck(xRow.getCell(15).getStringCellValue());

}if(xRow.getCell(16).getCellType() ==XSSFCell.CELL_TYPE_NUMERIC){

boat.setBwater(Double.valueOf(xRow.getCell(16).getNumericCellValue()).toString());

}else{

boat.setBwater(xRow.getCell(16).getStringCellValue());

}if(xRow.getCell(17).getCellType() ==XSSFCell.CELL_TYPE_NUMERIC){

boat.setBoperat(Double.valueOf(xRow.getCell(17).getNumericCellValue()).toString());

}else{

boat.setBoperat(xRow.getCell(17).getStringCellValue());

}

boat.setBrange(xRow.getCell(18).getStringCellValue());

boat.setBkind(xRow.getCell(19).getStringCellValue());

boat.setBowner(xRow.getCell(20).getStringCellValue());//添加到数据库

boatService.addOneBoat(boat);

}

}

}catch(BiffException e) {

e.printStackTrace();

}catch(IOException e) {

e.printStackTrace();

}

f.delete();

session.removeAttribute("fileName");return null;

}

}