EXCEL导入导出

导出Importexcelservlet类
package com.yysoft.servlet;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.PrintWriter;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import javax.servlet.ServletException;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

import com.yysoft.bean.ResumeBean;
import com.yysoft.dao.ResumeDao;

public class Importexcelservlet extends HttpServlet {

public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
doPost(request,response);
}
public void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {

response.setContentType("text/html");
request.setCharacterEncoding("UTF-8");
response.setCharacterEncoding("UTF-8");
String check=request.getParameter("res");
String [] rid=check.split(",");
String fileName = "system-auto-generate-"+new SimpleDateFormat("yyyy-MM-dd").format(new Date())+".xls";
File file = new File(fileName);
FileOutputStream fileOutputStream = new FileOutputStream(file);
WritableWorkbook wbook = Workbook.createWorkbook(fileOutputStream); // 建立excel文件
WritableSheet wsheet = wbook.createSheet("system-auto-generate", 0); // 工作表名称
wsheet.setColumnView(5, 40);
wsheet.setColumnView(8, 15);
wsheet.setColumnView(11, 15);
wsheet.setColumnView(13, 40);
wsheet.setColumnView(18, 40);
// 设置Excel字体
WritableFont wfont = new WritableFont(WritableFont.ARIAL, 10,
WritableFont.BOLD, false,
jxl.format.UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK);
WritableCellFormat titleFormat = new WritableCellFormat(wfont);
WritableFont detailwfont = new WritableFont(WritableFont.ARIAL, 12,
WritableFont.BOLD, false,
jxl.format.UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK);
WritableCellFormat detailTitleFormart = new WritableCellFormat(detailwfont);
String[] title = {"序号", "月份", "应试姓名","性别","出生日期","籍贯","学历","期望薪酬", "应聘岗位","面试时间","简历获取方式","简历渠道","是否参加面试","初试评价","复试时间","复试结果","是否录用","是否入职","备注" };
List<ResumeBean> list=new ArrayList<ResumeBean>();
ResumeDao rdao=new ResumeDao();
ResumeBean rbean=new ResumeBean();
try
{
int rows = wsheet.getRows();//保存标题的行号
for (int j = 0; j < title.length; j++) {
Label excelTitle = new Label(j,rows, title[j], titleFormat);
wsheet.addCell(excelTitle);
}
for(int i=0;i<rid.length;i++){
rbean.setRid(Integer.parseInt(rid[i]));
list=rdao.SelectResumeByid(rbean);
for(ResumeBean crdto: list){
String dates=crdto.getCreatetime();
String Rreexaminedate=crdto.getRreexaminedate();
if(Rreexaminedate==null){
Rreexaminedate="";
}
String remploy="";
int remplo=crdto.getRemploy();
if(remplo==0){
remploy="否";
}else{
remploy="是";
}
String Runaudition="";
int unaudition=crdto.getRunaudition();
if(unaudition==0){
Runaudition="否";
}else{
Runaudition="是";
}
String Rindustria="";
int Rindustrial=crdto.getRindustrial();
if(Rindustrial==0){
Rindustria="否";
}else{
Rindustria="是";
}
String sexs="";
int sex=crdto.getSex();
if(sex==0){
sexs="女";
}else{
sexs="男";
}
Label content1 = new Label(0, wsheet.getRows(), String.valueOf(crdto.getRid()));
Label content2 = new Label(1, wsheet.getRows(), dates);
Label content3 = new Label(2, wsheet.getRows(), crdto.getRname());//应聘人
Label content4 = new Label(3, wsheet.getRows(), sexs);//性别
Label content5 = new Label(4, wsheet.getRows(), crdto.getRbirth());//出生日期
Label content6 = new Label(5, wsheet.getRows(), crdto.getRaddress());//籍贯
Label content7 = new Label(6, wsheet.getRows(), crdto.getReducation());//学历
Label content8 = new Label(7, wsheet.getRows(), crdto.getRsalary());//期望薪酬
Label content9 = new Label(8, wsheet.getRows(), crdto.getRposition());//应聘岗位
Label content10 = new Label(9, wsheet.getRows(), crdto.getRauditiondate());//面试时间
Label content11 = new Label(10, wsheet.getRows(), crdto.getRfashion());//简历获取方式
Label content12 = new Label(11, wsheet.getRows(), crdto.getRcanal());//简历渠道
Label content13 = new Label(12, wsheet.getRows(), String.valueOf(Runaudition));//是否参加面试
Label content14 = new Label(13, wsheet.getRows(), crdto.getRfirst());//初试评价
Label content15 = new Label(14, wsheet.getRows(), String.valueOf(Rreexaminedate));//复试时间
Label content16 = new Label(15, wsheet.getRows(), crdto.getRfruit());//复试结果
Label content17 = new Label(16, wsheet.getRows(), String.valueOf(remploy));//是否录用
Label content18 = new Label(17, wsheet.getRows(), String.valueOf(Rindustria));//是否入职
Label content19 = new Label(18, wsheet.getRows(), crdto.getRremarks());//备注
wsheet.addCell(content1);
wsheet.addCell(content2);
wsheet.addCell(content3);
wsheet.addCell(content4);
wsheet.addCell(content5);
wsheet.addCell(content6);
wsheet.addCell(content7);
wsheet.addCell(content8);
wsheet.addCell(content9);
wsheet.addCell(content10);
wsheet.addCell(content11);
wsheet.addCell(content12);
wsheet.addCell(content13);
wsheet.addCell(content14);
wsheet.addCell(content15);
wsheet.addCell(content16);
wsheet.addCell(content17);
wsheet.addCell(content18);
wsheet.addCell(content19);
/** 从表信息 **/
}
}
wbook.write(); // 写入文件
wbook.close();
}catch(RowsExceededException ex)
{
ex.printStackTrace();
} catch (WriteException e) {
e.printStackTrace();
}
fileOutputStream.close();
//页面
response.setContentType("text/xml;charset=UTF-8");
response.addHeader("Content-Disposition", "attachment; filename="+fileName);
//确保IE识别本次为下载文件
response.setHeader("Content-Transfer-Encoding","binary");
response.setHeader("Cache-Control", "must-revalidate, post-check=0, pre-check=0");
response.setHeader("Pragma", "public");
ServletOutputStream out = response.getOutputStream();
InputStream inStream=new FileInputStream(fileName);
byte[] b = new byte[1024];
int len;
while((len=inStream.read(b)) >0){
out.write(b,0,len);
}
response.setStatus( response.SC_OK );
response.flushBuffer();
out.close();
inStream.close();
}

}


导入ReadExcelservlet类
package com.yysoft.servlet;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.io.PrintWriter;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.yysoft.bean.ResumeBean;
import com.yysoft.dao.ResumeDao;

import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

public class ReadExcelservlet extends HttpServlet {

public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
doPost(request,response);
}
public void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {

response.setContentType("text/html");
request.setCharacterEncoding("GBK");
response.setCharacterEncoding("GBK");
PrintWriter out = response.getWriter();
String filename=request.getParameter("adjunct");
ResumeDao resdao=new ResumeDao();
ResumeBean rebean=new ResumeBean();
List list=new ArrayList();
InputStream excelResource = null;
try {
excelResource = new FileInputStream(filename);
} catch (FileNotFoundException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
Workbook wb=null;
try {
wb=Workbook.getWorkbook(excelResource);
} catch (BiffException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
Sheet rs=wb.getSheet(0);
int rowsCount=rs.getRows();
String printname="";
String printnamers="";
for(int i=1;i<rowsCount;i++){
String month=rs.getCell(1, i).getContents();
String name=rs.getCell(2, i).getContents();//姓名
String namet="";
if(i>rowsCount){
namet=rs.getCell(2, i+1).getContents();//姓名
}
if(name==null||name.trim().equals(""))
break;
if(month.equals("")){
Date date=new Date();
SimpleDateFormat df=new SimpleDateFormat("MM");
month=df.format(date);
}
String sex=rs.getCell(3, i).getContents();//性别
int sexs=0;
if(sex.equals("男")){
sexs=1;
}
rebean=new ResumeBean();
String birth=rs.getCell(4, i).getContents();//出生日期
String birtht="";
if(i>rowsCount){
birtht=rs.getCell(4, i+1).getContents();//出生日期
}
String birthdate="";
if(birth==null||birth.equals("")){
birthdate="";
}else{
long birthTimeLong = Long.valueOf(birth).intValue();
long ssbir = (birthTimeLong - 70 * 365 - 17 - 2) * 24 * 3600 * 1000;
Date birthtime = new Date(ssbir);
SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
birthdate=df.format(birthtime);
}
String raddress=rs.getCell(5, i).getContents();//籍贯
String raddresst="";
if(i>rowsCount){
raddresst=rs.getCell(5, i+1).getContents();//籍贯
}
String reducation=rs.getCell(6, i).getContents();//学历
String rsalary=rs.getCell(7, i).getContents();//期望薪水
String position=rs.getCell(8, i).getContents();//应聘岗位
String auditiondate=rs.getCell(9,i).getContents();//面试时间
String audate="";
if(auditiondate==null||auditiondate.equals("")){
audate="";
}else{
long tempTimeLong = Long.valueOf(auditiondate).intValue();
long ss = (tempTimeLong - 70 * 365 - 17 - 2) * 24 * 3600 * 1000;
Date dss = new Date(ss);
SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
audate = formatter.format(dss);
}
String fashion=rs.getCell(10, i).getContents();//简历获取方式
String canal=rs.getCell(11, i).getContents();//简历渠道
String unaudition=rs.getCell(12, i).getContents();//是否参加面试
int una=0;
if(unaudition.equals("是")){
una=1;
}
String first=rs.getCell(13, i).getContents();//初试评价
String reexaminedate=rs.getCell(14, i).getContents();//复试时间
String reexdate="";
if(reexaminedate==null||reexaminedate.equals("")){
reexdate="";
}else{
long tempTimeLong = Long.valueOf(reexaminedate).intValue();
long ss = (tempTimeLong - 70 * 365 - 17 - 2) * 24 * 3600 * 1000;
Date dss = new Date(ss);
SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
reexdate = formatter.format(dss);
}
String fruit=rs.getCell(15, i).getContents();//复试结果
String employ=rs.getCell(16, i).getContents();//是否录用
int emp=0;
if(employ.equals("是")){
emp=1;
}
String industrial=rs.getCell(17, i).getContents();//是否入职
int indust=0;
if(industrial.equals("是")){
indust=1;
}
String remarks=rs.getCell(18, i).getContents();//备注
if(remarks.equals("")){
remarks="";
}
if(name.equals(namet)&&birth.equals(birtht)&&raddress.equals(raddresst)){
printname+=name+",";
continue;
}
String fields="where r_name='"+name+"'";
if(birthdate.equals("")||birthdate==null){

}else{
String birthtime=birthdate.substring(0, 4);
fields+=" AND TO_CHAR(r_birth,'YYYY')='"+birthtime+"'";
}
if(raddress.equals("")||raddress==null){

}else{
fields+=" AND r_address='"+raddress+"'";
}
//根据名字,年龄,籍贯三个字段进行查询数据库如果有存在相同数据不添加
int count=resdao.countresumetwo(fields);
if(count!=0){
printname+=name+",";
continue;
}
rebean.setCreatetime(month);//月份
rebean.setRname(name);//应聘者姓名
rebean.setSex(sexs);//性别
rebean.setRbirth(birthdate);//出生日期
rebean.setRaddress(raddress);//籍贯
rebean.setReducation(reducation);//学历
rebean.setRsalary(rsalary);//期望薪水
rebean.setRposition(position);//应聘岗位
rebean.setRauditiondate(audate);//面试时间
rebean.setRfashion(fashion);//简历获取方式
rebean.setRcanal(canal);//简历渠道
rebean.setRunaudition(una);//是否参加面试
rebean.setRfirst(first);//初试评价
rebean.setRreexaminedate(reexdate);//复试时间
rebean.setRfruit(fruit);//复试结果
rebean.setRemploy(emp);//是否录用
rebean.setRindustrial(indust);//是否入职
rebean.setRremarks(remarks);//备注
rebean.setRreexamine(0);//是否复试
list.add(rebean);
}
String printnames[]=printname.split(",");
//这里是去除重复的名字
for(int j=0;j<printnames.length;j++){
if(j!=0){
if(printnames[j].equals(printnames[j-1])){
continue;
}
}
printnamers+=printnames[j]+",";
}
if(!printname.equals("")){
out.println("<script>");
out.println("alert('应聘者"+printnamers+"的简历存在不予添加！');");
out.println("</script>");
}
boolean blag=resdao.addresumeall(list);//添加方法
if(blag){
out.println("<script>");
out.println("alert('Excel文件导入成功！');");
out.println("window.location.href=\"/main/resume_manager.jsp\";");
out.println("</script>");
return;
}else{

}
out.flush();
out.close();
}

}