Excel表格导出
<%@ page contentType="text/html;charset=utf-8"%>
<%@ page import="java.util.List"%>
<%@ page import="java.util.ArrayList"%>
<%@ page import="org.json.JSONObject"%>
<%@ page import="com.anolesoft.erp.xyy.util.excel.ExcelUtils"%>
<%@ page import="com.anolesoft.erp.xyy.util.excel.ExcelUtilSource"%>
<%@ page import="com.exedosoft.plat.bo.BOInstance"%>
<%@ page import="com.exedosoft.plat.util.DOGlobals"%>
<%@ page import="com.exedosoft.plat.util.Escape"%>
<%@ page import="com.anolesoft.erp.dao.DataSource"%>
<%@ page import="java.sql.Connection"%>
<%@ page import="java.sql.PreparedStatement"%>
<%@ page import="java.sql.ResultSet"%>
<%@ page import="java.util.HashMap"%>
<%@ page import="java.util.Map"%>
<%@ page import="com.anolesoft.erp.pp.action.PpEnumStatic"%>
<%@ page import="com.anolesoft.erp.xyy.util.StringUtil"%>
<%@ page import="java.text.SimpleDateFormat"%>
<%@ page import="java.util.Date"%>
<%-- <%@ page import="com.anolesoft.erp.pp.action.PlanAction"%> --%>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>导出Excel</title>
<%
Map<String,String> stateMap = new HashMap<String,String>();
stateMap.put("1", "实习");
stateMap.put("2", "在岗");
stateMap.put("3", "离职");
stateMap.put("4", "退休");
stateMap.put("5", "返聘");
stateMap.put("6", "试用");
Map<String,String> politicsMap = new HashMap<String,String>();
politicsMap.put("1", "群众");
politicsMap.put("2", "共青团员");
politicsMap.put("3", "中共预备党员");
politicsMap.put("4", "中共党员");
politicsMap.put("5", "民革党员");
politicsMap.put("6", "民盟盟员");
politicsMap.put("7", "民建会员");
politicsMap.put("8", "民进会员");
politicsMap.put("9", "农工党党员");
politicsMap.put("10", "致公党党员");
politicsMap.put("11", "九三学社社员");
politicsMap.put("12", "台盟盟员");
politicsMap.put("13", "无党派人士");
Map<String,String> educationMap = new HashMap<String,String>();
educationMap.put("1", "博士");
educationMap.put("2", "研究生");
educationMap.put("3", "本科");
educationMap.put("4", "大专");
educationMap.put("5", "高中");
educationMap.put("6", "中专");
Map<String,String> natureMap = new HashMap<String,String>();
natureMap.put("1", "农业户口");
natureMap.put("2", "非农业户口");
Map<String,String> marriagestateMap = new HashMap<String,String>();
marriagestateMap.put("0", "未婚");
marriagestateMap.put("1", "已婚");
marriagestateMap.put("2", "离异");
marriagestateMap.put("3", "丧偶");
Map<String,String> sexMap = new HashMap<String,String>();
sexMap.put("0", "男");
sexMap.put("1", "女");
Map<String,String> nationalityMap = new HashMap<String,String>();
nationalityMap.put("1", "汉族");
nationalityMap.put("2", "壮族");
nationalityMap.put("3", "满族");
nationalityMap.put("4", "回族");
nationalityMap.put("5", "苗族");
nationalityMap.put("6", "维吾尔族");
nationalityMap.put("7", "土家族");
nationalityMap.put("8", "彝族");
nationalityMap.put("9", "蒙古族");
nationalityMap.put("10", "藏族");
nationalityMap.put("11", "布依族");
nationalityMap.put("12", "侗族");
nationalityMap.put("13", "瑶族");
nationalityMap.put("14", "朝鲜族");
nationalityMap.put("15", "白族");
nationalityMap.put("16", "哈尼族");
nationalityMap.put("17", "哈萨克族");
nationalityMap.put("18", "黎族");
nationalityMap.put("19", "傣族");
nationalityMap.put("20", "畲族");
nationalityMap.put("21", "傈僳族");
nationalityMap.put("22", "仡佬族");
nationalityMap.put("23", "东乡族");
nationalityMap.put("24", "高山族");
nationalityMap.put("25", "拉祜族");
nationalityMap.put("26", "水族");
nationalityMap.put("27", "佤族");
nationalityMap.put("28", "纳西族");
nationalityMap.put("29", "羌族");
nationalityMap.put("30", "土族");
nationalityMap.put("31", "仫佬族");
nationalityMap.put("32", "锡伯族");
nationalityMap.put("33", "柯尔克孜族");
nationalityMap.put("34", "达斡尔族");
nationalityMap.put("35", "景颇族");
nationalityMap.put("36", "毛南族");
nationalityMap.put("37", "撒拉族");
nationalityMap.put("38", "布朗族");
nationalityMap.put("39", "塔吉克族");
nationalityMap.put("40", "阿昌族");
nationalityMap.put("41", "普米族");
nationalityMap.put("42", "鄂温克族");
nationalityMap.put("43", "怒族");
nationalityMap.put("44", "京族");
nationalityMap.put("45", "基诺族");
nationalityMap.put("46", "德昂族");
nationalityMap.put("47", "保安族");
nationalityMap.put("48", "俄罗斯族");
nationalityMap.put("49", "裕固族");
nationalityMap.put("50", "乌兹别克族");
nationalityMap.put("51", "门巴族");
nationalityMap.put("52", "鄂伦春族");
nationalityMap.put("53", "独龙族");
nationalityMap.put("54", "塔塔尔族");
nationalityMap.put("55", "赫哲族");
nationalityMap.put("56", "珞巴族");
nationalityMap.put("57", "穿青人");
Connection conn = null;
PreparedStatement pst = null;
ResultSet rs = null;
List<BOInstance> resList = null;//查询结果
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
String nowDate = sdf.format(new Date());
String jsonStr = Escape.unescape(request.getParameter("jsonStr").replace("%7B","{").replace("%7D","}"));
JSONObject json = new JSONObject(jsonStr);
String emp_name = StringUtil.isNull(json.getString("emp_name"));
String emp_sex = StringUtil.isNull(json.getString("emp_sex"));
String emp_nationality = StringUtil.isNull(json.getString("emp_nationality"));
String emp_marriage_state = StringUtil.isNull(json.getString("emp_marriage_state"));
String emp_card_id = StringUtil.isNull(json.getString("emp_card_id"));
String emp_birthday = StringUtil.isNull(json.getString("emp_birthday"));
String emp_contact_mode = StringUtil.isNull(json.getString("emp_contact_mode"));
String emp_politics = StringUtil.isNull(json.getString("emp_politics"));
String emp_employee_code = StringUtil.isNull(json.getString("emp_employee_code"));
String emp_dept_uid = StringUtil.isNull(json.getString("emp_dept_uid"));
String emp_post_uid = StringUtil.isNull(json.getString("emp_post_uid"));
String emp_man_state = StringUtil.isNull(json.getString("emp_man_state"));
try {
List <Map<String, String> >dataList = new ArrayList<Map<String, String> >();//excel结果
conn = DataSource.getDataSource().getConnection();
StringBuffer sql = new StringBuffer();
sql.append(" SELECT a.objuid,a.emp_name,a.sex,a.nationality,a.marriage_state,a.card_id,a.birthday,a.contact_mode,a.birth_address, ");
sql.append(" a.now_address,a.high_education,a.technical_name,a.politics,a.join_party_date,a.hiredate,a.employee_code,a.eid,b.dept_name, ");
sql.append(" c.cname post_name,a.headship,a.man_state,a.remark,a.creater_uid,a.creat_date,a.modifier_uid,a.modification_date, ");
sql.append(" a.household_nature,a.foreign_language,a.computer_ability,a.interview_date,a.probation_period,a.regular_date,a.post_change_date,a.quit_date,a.extension_number,a.homephone,a.mailbox,a.retire_date ");
sql.append(" FROM erp_hr_emp a ");
sql.append(" LEFT JOIN erp_hr_dept b ON (a.dept_uid = b.objuid) ");
sql.append(" LEFT JOIN erp_hr_post c ON (a.post_uid = c.objuid) ");
sql.append(" WHERE a.delete_flag=0 ");
if(emp_name != null && !"".equals(emp_name)){
sql.append(" AND a.emp_name LIKE '%"+emp_name+"%' ");
}
if(emp_sex != null && !"".equals(emp_sex)){
sql.append(" AND a.sex = "+emp_sex+" ");
}
if(emp_nationality != null && !"".equals(emp_nationality)){
sql.append(" AND a.nationality = "+emp_nationality+" ");
}
if(emp_marriage_state != null && !"".equals(emp_marriage_state)){
sql.append(" AND a.marriage_state = "+emp_marriage_state+" ");
}
if(emp_card_id != null && !"".equals(emp_card_id)){
sql.append(" AND a.card_id LIKE '%"+emp_card_id+"%' ");
}
if(emp_birthday != null && !"".equals(emp_birthday)){
sql.append(" AND a.birthday LIKE '"+emp_birthday+"' ");
}
if(emp_contact_mode != null && !"".equals(emp_contact_mode)){
sql.append(" AND a.contact_mode LIKE '%"+emp_contact_mode+"%' ");
}
if(emp_politics != null && !"".equals(emp_politics)){
sql.append(" AND a.politics = "+emp_politics+" ");
}
if(emp_employee_code != null && !"".equals(emp_employee_code)){
sql.append(" AND a.employee_code LIKE '%"+emp_employee_code+"%' ");
}
if(emp_dept_uid != null && !"".equals(emp_dept_uid)){
sql.append(" AND a.dept_uid = '"+emp_dept_uid+"' ");
}
if(emp_post_uid != null && !"".equals(emp_post_uid)){
sql.append(" AND a.post_uid = '"+emp_post_uid+"' ");
}
if(emp_man_state != null && !"".equals(emp_man_state)){
sql.append(" AND a.man_state = "+emp_man_state+" ");
}
sql.append(" ORDER BY a.employee_code ");
pst = conn.prepareStatement(sql.toString());
rs = pst.executeQuery();
resList = StringUtil.ResultToList(rs);
for(int i=0 ; i<resList.size() ; i++){
BOInstance boi = (BOInstance) resList.get(i);
boi.putValue("number", (i+1));
boi.putValue("objuid", StringUtil.isNull(boi.getValue("objuid")));
boi.putValue("emp_name", StringUtil.isNull(boi.getValue("emp_name")));
boi.putValue("employee_code", StringUtil.isNull(boi.getValue("employee_code")));
boi.putValue("dept_uid", StringUtil.isNull(boi.getValue("dept_name")));
boi.putValue("post_uid", StringUtil.isNull(boi.getValue("post_name")));
boi.putValue("headship", StringUtil.isNull(boi.getValue("headship")));
boi.putValue("man_state", StringUtil.transferredMeaning(stateMap,boi.getValue("man_state")));
boi.putValue("contact_mode", StringUtil.isNull(boi.getValue("contact_mode")));
boi.putValue("extension_number", StringUtil.isNull(boi.getValue("extension_number")));
boi.putValue("sex", StringUtil.transferredMeaning(sexMap,boi.getValue("sex")));
boi.putValue("nationality", StringUtil.transferredMeaning(nationalityMap,boi.getValue("nationality")));
boi.putValue("marriage_state", StringUtil.transferredMeaning(marriagestateMap,boi.getValue("marriage_state")));
boi.putValue("card_id", StringUtil.isNull(boi.getValue("card_id")));
boi.putValue("interview_date", StringUtil.isNull(boi.getValue("interview_date")));
boi.putValue("hiredate", StringUtil.isNull(boi.getValue("hiredate")));
boi.putValue("probation_period", StringUtil.isNull(boi.getValue("probation_period")));
boi.putValue("regular_date", StringUtil.isNull(boi.getValue("regular_date")));
boi.putValue("post_change_date", StringUtil.isNull(boi.getValue("post_change_date")));
boi.putValue("quit_date", StringUtil.isNull(boi.getValue("quit_date")));
boi.putValue("retire_date", StringUtil.isNull(boi.getValue("retire_date")));
boi.putValue("foreign_language", StringUtil.isNull(boi.getValue("foreign_language")));
boi.putValue("computer_ability", StringUtil.isNull(boi.getValue("computer_ability")));
boi.putValue("high_education", StringUtil.transferredMeaning(educationMap,boi.getValue("high_education")));
boi.putValue("technical_name", StringUtil.isNull(boi.getValue("technical_name")));
boi.putValue("politics", StringUtil.transferredMeaning(politicsMap,boi.getValue("politics")));
boi.putValue("join_party_date", StringUtil.isNull(boi.getValue("join_party_date")));
boi.putValue("household_nature", StringUtil.transferredMeaning(natureMap,boi.getValue("household_nature")));
boi.putValue("birthday", StringUtil.isNull(boi.getValue("birthday")));
boi.putValue("birth_address", StringUtil.isNull(boi.getValue("birth_address")));
boi.putValue("now_address", StringUtil.isNull(boi.getValue("now_address")));
boi.putValue("creater_uid", StringUtil.isNull(boi.getValue("creater_uid")));
boi.putValue("creat_date", StringUtil.isNull(boi.getValue("creat_date")));
boi.putValue("modifier_uid", StringUtil.isNull(boi.getValue("modifier_uid")));
boi.putValue("modification_date", StringUtil.isNull(boi.getValue("modification_date")));
boi.putValue("homephone", StringUtil.isNull(boi.getValue("homephone")));
boi.putValue("mailbox", StringUtil.isNull(boi.getValue("mailbox")));
dataList.add(boi.getMap());
}
List<String> sheetTitle = new ArrayList<>();
sheetTitle.add("人员信息");
String[][]columns = new String[][]{};
columns = new String[][]{
{"序号","number"},
{"姓名","emp_name"},
{"人员编码","employee_code"},
{"性别","sex"},
{"民族","nationality"},
{"婚姻状态","marriage_state"},
{"身份证号","card_id"},
{"户口性质","household_nature"},
{"出生日期","birthday"},
{"籍贯","birth_address"},
{"现居地址","now_address"},
{"手机号码","contact_mode"},
{"住宅电话","homephone"},
{"电子邮箱","mailbox"},
{"外语语种","foreign_language"},
{"计算机能力","computer_ability"},
{"最高学历","high_education"},
{"技术职称","technical_name"},
{"政治面貌","politics"},
{"入党/团时间","join_party_date"},
{"面试时间","interview_date"},
{"试用期限","probation_period"},
{"部门","dept_uid"},
{"岗位","post_uid"},
{"职务","headship"},
{"人员在岗状态","man_state"},
{"入职时间","hiredate"},
{"转正日期","regular_date"},
{"分机号","extension_number"},
{"系统唯一标识","objuid"},
};
ExcelUtils ex = ExcelUtilSource.getExportSource2003(sheetTitle, columns, dataList);
ex.export(response);
out.clear();
out = pageContext.pushBody();
}catch(Exception e){
e.printStackTrace();
}finally{
if(rs!=null){try{rs.close();} catch(Exception e) {e.printStackTrace();}}
if(pst!=null){try{pst.close();} catch(Exception e) {e.printStackTrace();}}
if(conn!=null){try{conn.close();} catch(Exception e) {e.printStackTrace();}}
}
%>
</head>
</html>
<script language='javascript'>
$(function() {
})
</script>
