实现将Excel表导入到Mysql库
一.将文件上传到服务器,fileupload
1.引入相关JAR commons-fileupload-1.2.jar,commons-io-1.3.2.jar
2.检测文件格式等,上传到服务器文件夹
path = getServletContext().getRealPath("uploadfile");//取得服务器端存取文件的文件夹路径
DiskFileItemFactory factory = new DiskFileItemFactory();
factory.setSizeThreshold(4096); // 设置缓冲区大小
ServletFileUpload upload = new ServletFileUpload(factory);
upload.setSizeMax(4194304);//设置上传文件的最大值
List items = upload.parseRequest(request);// 得到所有的文件
Iterator i = items.iterator();
while(i.hasNext()){
FileItem fi = (FileItem) i.next();
String fileName = fi.getName();
String postfix = fileName.substring(fileName.length() - 3);
if (!postfix.equals("xls"))
{
out.print("<div style='padding-top:5px;padding-bottom:5px;' align=center>");
out.print("<font size=2></font><a href='excel.jsp'><fontsize=2 color=red>选择其他格式重新上传</font></a>");
out.print("</div>");
} else {
if (fileName != null) {
File savedFile = new File(path, fileName );
fi.write(savedFile);
}
…………
}
}
3.取得文件在服务端路径
二.读取上传的Excel表内容,插入库
1.引入相关JAR jxl.jar
2.创建Workbook
Workbook workbook = Workbook.getWorkbook(new java.io.File(path));
3.取得工作表Sheet
Sheet sheet = workbook.getSheet(0);
或Sheet sheet = workbook.getSheet("name");
4.取得单元格内容Cell
Cell cell = sheet.getCell(1,1); //第一个参数为列,第二个参数为行,起始值为0
String contents = cell.getContents();
5.取得数据库表各字段数据格式,将contents转换格式,生成相应Sql语句
ResultSet rs = dc.executeQuery("select * from tablename limit 1");
switch (rs.getMetaData().getColumnType(columnNum)) {
case:CHAR:
……break;
}
6.执行,插入库