mssql导入mysql文件_jsp实现Excel文件导入到MySQL数据库中

表格

此步骤需要借助jspsmartupload.jar,poi_3.2.jar和jxl.jar包

1.在jsp界面中:

enctype="multipart/form-data" method="post">

2.对应的servlet的代码:

需要先上传文件,

public void doPost(HttpServletRequest request, HttpServletResponse response)

throws ServletException, IOException {

response.setCharacterEncoding("utf-8");

String type = request.getParameter("type");

if (type.equals("excToMqsql")) {

/*

*先对文件进行上传,代码如下

*/

SmartUpload su = new SmartUpload();

su.initialize(this.getServletConfig(), request, response);

// 设定允许上传的文件(通过扩展名限制)

su.setAllowedFilesList("xls,xlsx");

try {

su.upload();

Files files = su.getFiles();

String temp = "";

for (int i = 0; i < files.getCount(); i++) {

File file = files.getFile(i);

temp = "/upload/staff.xls";//将上传的文件放在根目录下的upload文件夹下并命名为固定的文件名

file.saveAs(temp, SmartUpload.SAVE_VIRTUAL);//将文件进行上传

}

System.out.println("上传成功!");

/*

*文件上传成功后,将调用文件导入的方法;

*/

InStaff es = new InStaff();//创建自身对象;

es.getDate(request, response);//调用导入Excel数据的方法;

// ExcelToMySql(request, response);

} catch (SmartUploadException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

}

// ------------------------------------------

//导入Excel信息的方法

// ----------------------------------------

public void getDate(HttpServletRequest request, HttpServletResponse response)

throws ServletException, IOException {

String errMessage = null;

boolean flags=false;

List liststu = new ArrayList();

// 找到上传后的文件目录,并创建IO流

java.io.File f = new java.io.File(request.getSession().getServletContext().getRealPath("")+"甥汰慯dstaff.xls");

InputStream is = new FileInputStream(f);

try {

// 创建工作簿

Workbook wb = Workbook.getWorkbook(is);

// 创建工作表

jxl.Sheet sheet = wb.getSheet(0);

String content = null;

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

staffBean staff = new staffBean();

for (int j = 0; j < sheet.getColumns(); j++) {

content = sheet.getCell(j, i).getContents();

// System.out.print(content);

if (staff.getName() == null)//

{

staff.setName(sheet.getCell(j, i).getContents());

continue;

}

if (staff.getIdcard() == null) {

staff.setIdcard(sheet.getCell(j, i).getContents());

continue;

}

}

flags=getStaffInfo(staff);

}

/*

* 文件导入成功后进入提示界面

*/

if(flags){

System.out.println("总表导入成功!");

response.sendRedirect("CompareServlet?type=show");

}else{

System.out.println("总表未导入成功!");

errMessage = "导入总表未成功,请重新导入！";

request.setAttribute("error