* 导出
* @throws Exception
*/
public void excelExport() throws Exception{
// 读取参数并查询数据
String qyfl = ActionUtils.getParamater(getRequest(), "qyfl");
String columnName = ActionUtils.getParamater(getRequest(), "columnName");
String s_value = ActionUtils.getParamater(getRequest(), "s_value");
Map<String, Object> map = ActionUtils.paramter2Map("qyfl", qyfl, "columnName", columnName, "s_value", s_value);
List<Qyjbxx> list = commonService.getGyQyjbxxList(map, null);
// 定义数据列
List<String> zwColumns = Arrays.asList("企业分类","单位名称","社会信用代码",
"法定代表人姓名","法定代表人身份证","经营场所","企业类型","所属行业","注册资本",
"经营范围","一般经营项目","设立日期","核准日期","所在地区");
List<String> ywColumns = Arrays.asList("qyfl","qymc","shxydm","fddbrxm","fddbrsfz","jycs","qylx","sshy",
"zczb","jyfw","ybjyxm","slrq","hzrq","employeeCity");
ExcelUtils. exportDataByExcel(getResponse(), "企业基本信息导出数据.xls", zwColumns, ywColumns, list, Qyjbxx.class,
new IExcelHandle() {
// 定义数据转换接口,对日期和企业分类数据进行文本转换
@Override
public Object handle(String name, Object object) throws Exception {
if ("qyfl".equals(name)){
if ("0".equals(object)){
return "先证后核类";
}else if("1".equals(object)){
return "暂时保留类";
}else if("2".equals(object)){
return "暂停行使类";
}else if("3".equals(object)){
return "取消类";
}else if("4".equals(object)){
return "其他";
}else{
return "";
}
}else if ("slrq".equals(name)){
return ActionUtils.date2String((Date) object, "yyyy-MM-dd");
}else if ("hzrq".equals(name)){
return ActionUtils.date2String((Date) object, "yyyy-MM-dd");
}
return object==null?"":object.toString();
}
});
}
/**
* 获取一个Excel导出数据
* @param response
* @param filename 提供下载的文件名称
* @param zwColumns 中文字段名称,用于Excel模板内顶部展示说明
* @param ywColumns 英文字段名称,用于关联数据列以便导入数据到Excel文档中,应该于实体类中字段一致
* @param list 需要导出的数据列表,请提供实体类的List即可
* @param clazz 需要导出的数据实体类的Class,例 ExcelUtils.class 可以获得
* @param IExcelHandle Excel字段转换接口,用于对字段写入Excel时做数据格式转换
* @throws Exception
*/
public static void exportDataByExcel(HttpServletResponse response, String filename, List<String> zwColumns, List<String> ywColumns, List list, Class clazz, IExcelHandle handle) throws Exception{
if(zwColumns.size() != ywColumns.size()){
throw new IllegalAccessException("中文字段和英文字段不一致,请检查调用参数是否错误!");
}
Map<String, Method> readMethodMap = new HashMap<String, Method>();
for(String column : ywColumns){
try{
PropertyDescriptor pd = new PropertyDescriptor(column, clazz);
Method method = pd.getReadMethod();
if (method == null){
throw new IllegalAccessException(column + "字段无法于实体类映射,请检查参数!");
}
readMethodMap.put(column, method);
}catch(Exception e){
throw new IllegalAccessException("实体类解析出现异常[" + e.getMessage() + "],请检查参数!");
}
}
Workbook wb = createWookbook(zwColumns);
Sheet sheet = wb.getSheetAt(0);
int i = 1;
for (Object o : list){
Row row = sheet.createRow(i);
int j = 0;
for (String column : ywColumns){
Object value = readMethodMap.get(column).invoke(o);
if (handle != null){
value = handle.handle(column, value);
}
row.createCell(j).setCellValue(value==null?"":value.toString());
j++;
}
i++;
}
response.setContentType("application/vnd.ms-excel");
response.addHeader("Content-Disposition", "attachment; filename=\"" + URLEncoder.encode(filename, "utf-8") + "\"");
wb.write(response.getOutputStream());
}
/**
*
*/
private static Workbook createWookbook(List<String> columns){
Workbook wb = new HSSFWorkbook();Sheet sheet = wb.createSheet();
Row row = sheet.createRow(0);
int i = 0;
for(String s : columns){
row.createCell(i).setCellValue(s);
i++;
}
return wb;
}