可以实现上传excel表格来添加名片以及通过浏览器下载查询后的表格,比如用户的名片数据表格(或回收站),用户通过模糊查询后的名片(或回收站)数据表格,管理员查看全部名片(或回收站)数据的表格,管理员查看用户(或回收站)数据表格,管理员模糊查询名片或用户(或回收站)后的数据表格
jsp/html代码
js代码//导出功能 修改href的属性值以达到可以向Down传递参数的目的
$('.export').click(function (){
$('.export').attr('href',"Down?"+"type="+type+"&deltype="+del+"&name="+name);
alert($('.export').attr('href'));
})
上传servlet
package Servlet;
import Driver.IEDao;
import Driver.MingPian;
import Driver.OpDao;
import Driver.User;
import com.google.gson.Gson;
import com.oreilly.servlet.MultipartRequest;
import com.oreilly.servlet.multipart.DefaultFileRenamePolicy;
import com.oreilly.servlet.multipart.FileRenamePolicy;
import com.sun.org.apache.bcel.internal.generic.MULTIANEWARRAY;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import java.io.File;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Enumeration;
import java.util.List;
@WebServlet("/Upload")
public class Upload extends HttpServlet {
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
doPost(req, resp);
}
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
String requestIp = req.getRemoteAddr();
HttpSession hs= req.getSession(true);
String usename=(String)hs.getAttribute("usename");
//this.getServletContext().getRealPath("")获取该servlet的目录,upload是要放置上传文件的文件夹
// 比如下面这条语句代表的就是"E:/javaproject/keshe/out/artifacts/keshe3_war_exploded/upload"这个文件夹
String url = this.getServletContext().getRealPath("") + "upload";
File file = new File(url);
if (!file.exists()) {//一开始可能没有这个文件夹,没有就自动创建
file.mkdirs();
}
int maxPostSize = 5 * 1024 * 1024;
MultipartRequest multi = new MultipartRequest(req, url, maxPostSize, "UTF-8");
Enumeration files = multi.getFileNames();//获取所有上传文件的文件名
String name = files.nextElement();//获取下一个文件的文件名
int dot=multi.getFilesystemName(name).indexOf(".");//getFilesystemName方法是获取上传文件原始的文件名
//dot表示excel.xls中的'.'的位置
String suffix=multi.getFilesystemName(name).substring(dot);
//suffix获取的是后缀名,比如.xls,为了判断文件是.xls还是.xlsx
String fname=multi.getFilesystemName(name);//获取上传的excel的文件名
File exfile=new File(url+"/"+fname);//获取到上传的excel文件
IEDao ieDao=new IEDao();
try {
ieDao.importfromexcel(exfile,suffix,0,usename);//解析插入数据
} catch (SQLException e) {
e.printStackTrace();
}
resp.sendRedirect("usershow.jsp");
}
}
下载servlet
package Servlet;
import Driver.IEDao;
import Driver.MingPian;
import Driver.OpDao;
import Driver.User;
import Utils.ExcelFileUtil;
import com.google.gson.Gson;
import com.oreilly.servlet.MultipartRequest;
import com.oreilly.servlet.ServletUtils;
import com.oreilly.servlet.multipart.DefaultFileRenamePolicy;
import com.oreilly.servlet.multipart.FileRenamePolicy;
import com.sun.org.apache.bcel.internal.generic.MULTIANEWARRAY;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import javax.servlet.ServletException;
import javax.servlet.ServletOutputStream;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import java.io.*;
import java.nio.charset.StandardCharsets;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Enumeration;
import java.util.List;
@WebServlet("/Down")
public class Down extends HttpServlet{
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
doPost(req, resp);
}
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
resp.setContentType("text/html;charset=UTF-8");
HttpSession hs=req.getSession(true);
String usename=(String)hs.getAttribute("usename");
int usetype=(int) hs.getAttribute("usertype");
String name=req.getParameter("name");
// if(name==null) name="";
int type=Integer.parseInt(req.getParameter("type"));
int deltyp=Integer.parseInt(req.getParameter("deltype"));
//上面是获取参数
IEDao ieDao=new IEDao();
HSSFWorkbook workbook=null;//创建工作簿对象
try {
workbook=ieDao.exportToExcel(type,deltyp,usename,usetype,name);//返回已经操作好的表格对象
} catch (Exception e) {
e.printStackTrace();
}
ByteArrayOutputStream os=new ByteArrayOutputStream();//字节输出流
workbook.write(os);//通过字节输出流把数据写入
byte[] content=os.toByteArray();
InputStream is=new ByteArrayInputStream(content);
resp.reset();//清除buffer缓存
resp.setContentType("application/vnd.ms-excel;charset=utf-8");//设置内容类型
//指定下载文件名
resp.setHeader("Content-Disposition", "attachment;filename="+ new String(( "exc.xls").getBytes(), "iso-8859-1"));
//设置响应输出、创建缓冲输入输出流、写文件
ServletOutputStream out = resp.getOutputStream();
BufferedInputStream bis = null;
BufferedOutputStream bos = null;
bis=new BufferedInputStream(is);
bos=new BufferedOutputStream(out);
byte[] buff=new byte[2048];
int byteRead;
while(-1!=(byteRead=bis.read(buff,0,buff.length))){
bos.write(buff,0,byteRead);
}
if(bis!=null) bis.close();
if(bos!=null) bos.close();
}
}
上传下载的JavaBean
package Driver;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.*;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
public class IEDao {
public HSSFWorkbook exportToExcel(int type,int deltype,String usename,int usetype,String name) throws Exception {
//创建工作簿对象,工作表对象
HSSFWorkbook workbook=new HSSFWorkbook();
HSSFSheet sheet=workbook.createSheet("sheet0");
HSSFRow headerRow=sheet.createRow(0);//获取第一行的对象
HSSFCellStyle cellStyle=workbook.createCellStyle();//获取单元格样式操作对象
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//设置单元格样式:居中
//获取单元格,设置单元格的值和样式
HSSFCell cell0=headerRow.createCell(0);
cell0.setCellValue("id");
cell0.setCellStyle(cellStyle);
HSSFCell cell1=headerRow.createCell(1);
if(type==0) cell1.setCellValue("姓名");
else cell1.setCellValue("用户名");
cell1.setCellStyle(cellStyle);
HSSFCell cell2=headerRow.createCell(2);
cell2.setCellValue("性别");
cell2.setCellStyle(cellStyle);
HSSFCell cell3=headerRow.createCell(3);
cell3.setCellValue("年龄");
cell3.setCellStyle(cellStyle);
HSSFCell cell4=headerRow.createCell(4);
cell4.setCellValue("电话");
cell4.setCellStyle(cellStyle);
HSSFCell cell5=headerRow.createCell(5);
cell5.setCellValue("邮箱");
cell5.setCellStyle(cellStyle);
HSSFCell cell6=headerRow.createCell(6);
if(type==0) cell6.setCellValue("所属用户");
else cell6.setCellValue("");
OpDao opDao=new OpDao();//数据库操作的JavaBean
List


