Excel上传到服务器上之后存入数据库（本地练习项目）

来源：互联网发布：淘宝怎么更改身份认证编辑：程序博客网时间：2024/05/14 19:58

JSP页面

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.PrintWriter;
import java.text.DecimalFormat;
import java.util.Arrays;
import java.util.List;
import java.util.Objects;

import javax.servlet.ServletConfig;
import javax.servlet.ServletContext;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import net.netjava.entity.Cucustomer;
import net.netjava.entity.Suer;
import net.netjava.excel.ExcelUtil;
import net.netjava.manager.MCucustomerDAO;
import net.netjava.page.AccessController;
import net.netjava.util.SystemHelper;

import org.apache.commons.fileupload.FileItem;
import org.apache.commons.fileupload.FileUploadException;
import org.apache.commons.fileupload.disk.DiskFileItemFactory;
import org.apache.commons.fileupload.servlet.ServletFileUpload;
import org.apache.commons.fileupload.util.Streams;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
*
* 文件上传具体步骤： 1）获得磁盘文件条目工厂 DiskFileItemFactory 要导包 2）利用 request 获取真实路径
* ，供临时文件存储，和最终文件存储，这两个存储位置可不同，也可相同 3）对 DiskFileItemFactory 对象设置一些属性
* 4）高水平的API文件上传处理 ServletFileUpload upload = new ServletFileUpload(factory);
* 目的是调用 parseRequest（request）方法获得 FileItem 集合list ，
*
* 5）在 FileItem 对象中获取信息，遍历，判断表单提交过来的信息是否是普通文本信息另做处理 6）第一种. 用第三方提供的
* item.write( new File(path,filename) ); 直接写到磁盘上第二种. 手动处理
*
*/
public class UploadUserExcelServlet extends HttpServlet {

private static final long serialVersionUID = 1L;
// 保存文件的目录
private static String PATH_FOLDER = "/";
// 存放临时文件的目录
private static String TEMP_FOLDER = "/";

private static String[] FILE_EXT_LIST = {".xls",".xlsx"};

@Override
public void init(ServletConfig config) throws ServletException {
ServletContext servletCtx = config.getServletContext();
// 初始化路径
// 保存文件的目录
PATH_FOLDER = servletCtx.getRealPath("/upload");
// 存放临时文件的目录,存放xxx.tmp文件的目录
TEMP_FOLDER = servletCtx.getRealPath("/uploadTemp");
}

/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse
* response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
request.setCharacterEncoding("utf-8"); // 设置编码
response.setCharacterEncoding("utf-8");
response.setContentType("text/html;charset=UTF-8");
// 获得磁盘文件条目工厂
DiskFileItemFactory factory = new DiskFileItemFactory();

// 如果没以下两行设置的话，上传大的文件会占用很多内存，
// 设置暂时存放的存储室 , 这个存储室，可以和最终存储文件的目录不同
/**
* 原理它是先存到暂时存储室，然后在真正写到对应目录的硬盘上，按理来说当上传一个文件时，其实是上传了两份，第一个是以 .tem
* 格式的然后再将其真正写到对应目录的硬盘上
*/
factory.setRepository(new File(TEMP_FOLDER));
// 设置缓存的大小，当上传文件的容量超过该缓存时，直接放到暂时存储室
factory.setSizeThreshold(1024 * 1024);

// 高水平的API文件上传处理
ServletFileUpload upload = new ServletFileUpload(factory);
String msg="";
int error=0;

try {
// 提交上来的信息都在这个list里面
// 这意味着可以上传多个文件
// 请自行组织代码
List<FileItem> list = upload.parseRequest(request);
// 获取上传的文件
FileItem item = getUploadFileItem(list);
// 获取文件名
String filename = getUploadFileName(item);
String extName = filename.substring(filename.lastIndexOf(".")).toLowerCase();

if(Arrays.asList(FILE_EXT_LIST).contains(extName)){
filename=SystemHelper.createPK()+extName;
//获取路径
String subpath = null;
for (FileItem fileItem : list) {
if (fileItem.isFormField()) {
if (Objects.equals(fileItem.getFieldName(), "subpath")) {
InputStream stream = fileItem.getInputStream();
subpath = Streams.asString(stream, "UTF-8");
break;
}
}
}
String filePath=PATH_FOLDER;
if (subpath != null) {
filePath += "/" + subpath;
}

if(!new File(filePath).isDirectory()){
new File(filePath).mkdirs();
}

// 真正写到磁盘上
item.write(new File(filePath, filename)); // 第三方提供的

File saveFile = new File(new File(filePath), filename);
msg="/upload/" + (subpath == null ? "" : subpath) + "/" + filename ;

String usermsg = upload(request,saveFile);
if(usermsg.indexOf("失败") != -1){
error = 3;
}
}else{
error=2;
}


} catch (FileUploadException e) {
e.printStackTrace();
msg=e.getLocalizedMessage();
error=1;
} catch (Exception e) {
e.printStackTrace();
msg=e.getLocalizedMessage();
error=1;
}finally{
PrintWriter writer = response.getWriter();

writer.print("{");
writer.print("error:\"" + error + "\",");
writer.print("msg:\"" + msg + "\"");
writer.print("}");

writer.close();

}

}

private FileItem getUploadFileItem(List<FileItem> list) {
for (FileItem fileItem : list) {
if (!fileItem.isFormField()) {
return fileItem;
}
}
return null;
}

private String getUploadFileName(FileItem item) {
// 获取路径名
String value = item.getName();
// 索引到最后一个反斜杠
int start = value.lastIndexOf("/");
// 截取上传文件的字符串名字，加1是去掉反斜杠，
String filename = value.substring(start + 1);

return filename;
}

/**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse
* response)
*/
protected void doPost(HttpServletRequest request,
HttpServletResponse response) throws ServletException, IOException {
this.doGet(request, response);
}

private String upload(HttpServletRequest request,File file) throws Exception {
String msg = "导入成功！";
MCucustomerDAO cuMrg = new MCucustomerDAO();
boolean isrollback = false;
Suer user = AccessController.isValidUser(request.getSession());
try{
//判断excel是什么版本的
boolean isExcel2003 = true;
if (ExcelUtil.isExcel2007(file+"")){
isExcel2003 = false;
}
Workbook wookbook = null;

// 创建对Excel工作簿文件的引用
if (isExcel2003){
wookbook = new HSSFWorkbook(new FileInputStream(file));
}else{
wookbook = new XSSFWorkbook(new FileInputStream(file));
}
// 在Excel文档中，第一张工作表的缺省索引是0
Sheet sheet = wookbook.getSheetAt(0);
//获取到Excel文件中的所有行数
int rows = sheet.getPhysicalNumberOfRows();
//遍历行
for (int j = 1; j < rows; j++) {
// 读取左上端单元格
Row row = sheet.getRow(j);
// 行不为空
String value = "";
if (row != null) {
//获取到Excel文件中的所有的列
int cells = row.getPhysicalNumberOfCells();
//遍历列
for (int t = 0; t < cells; t++) {
//获取到列的值
Cell cell = row.getCell(t);
if (cell != null) {
// 以下是判断数据的类型
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_NUMERIC: // 数字
DecimalFormat df = new DecimalFormat("0");
value += df.format(cell.getNumericCellValue()) + ",";
break;

case HSSFCell.CELL_TYPE_STRING: // 字符串
value += cell.getStringCellValue() + ",";
break;

case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean
value += cell.getBooleanCellValue() + ",";
break;

case HSSFCell.CELL_TYPE_FORMULA: // 公式
value += cell.getCellFormula() + ",";
break;

case HSSFCell.CELL_TYPE_BLANK: // 空值
value += "" + ",";
break;

case HSSFCell.CELL_TYPE_ERROR: // 故障
value += "非法字符" + ",";
break;

default:
value += "未知类型" + ",";
break;
}
}
}
}
//将数据插入到mysql数据库中
String[] val = value.split(",");
Cucustomer cu = new Cucustomer();
cu.setCucustomerclass(0);
cu.setCucustomersource(0);//来自网页
cu.setCucustomertype(0);
cu.setCucrafttype(0);
cu.setCucreateid(user.getUserid());
cu.setCucreatetime(SystemHelper.createTime());
cu.setIsblack(0);
cu.setCuisvip(0);
if("周边游".indexOf(val[4])!=-1){
cu.setCutype(1);
}else if("国内游".indexOf(val[4])!=-1){
cu.setCutype(2);
}else{
cu.setCutype(3);
}

cu.setCucustomername(val[1]);
if("女".equals(val[2])){
cu.setSex(1);
}else{
cu.setSex(0);
}
cu.setCuid(Long.valueOf(val[0]));
cu.setCuphone(val[0]);
cu.setCuidcard(val[3]);
cuMrg.insert(cu);
}


}catch (Exception e) {
msg = "导入数据失败," + e.getLocalizedMessage() + "!";
e.printStackTrace();
isrollback = true;
}

return msg;
}

}

阅读全文

0 0