导出excel工具类

//导出excel
   public void ExportExcel(String fileName,String tableName,List strid, HttpServletResponse response,
   HttpServletRequest request){
response.setContentType("application/msexcel");// 定义输出类型
String headname="";
String[] sheetname=fileName.split("\\.");
try {
headname = new String(fileName.getBytes("gbk"), "ISO8859-1");
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
}// 解决中文
response.setHeader("content-disposition", "attachment; filename=" + headname);// 设定输出文件头
WritableWorkbook wwb;
try {
OutputStream os = response.getOutputStream();
wwb = Workbook.createWorkbook(os);
WritableSheet ws = wwb.createSheet(sheetname[0], 0);
WritableCellFormat normalFont = new WritableCellFormat(
new WritableFont(WritableFont.createFont("宋体"), 10));
normalFont.setBorder(Border.ALL, BorderLineStyle.THIN);
WritableCellFormat smallhead = new WritableCellFormat(
new WritableFont(WritableFont.createFont("宋体"), 12, WritableFont.BOLD));
smallhead.setAlignment(Alignment.CENTRE);

LoginInfo name = new LoginInfo();
    String username = name.getPrincipal();
    User user = userservice.findByLoginName(username);
String strsql="select * from impexcelrule where tableeng='"+tableName+"' and companyid='"
+user.getCompanyid()+"'";
List<Impexcelrule> excel =Impexcelruleservice.findAllByCondition(strsql, Impexcelrule.class);
Map<String,String> mapExcel=new HashMap<>();//定义一个map用来存放查出来的值。
String mergecolumn=excel.get(0).getMergecolumn();
/**java中的“.”和“|”作为分隔符的话，必须在前面加"\\"，才能正确的分割**/
String[] merges=mergecolumn.split("\\|");
StringBuilder sb=new StringBuilder();
for(int i=0;i<merges.length;i++){
String kv=merges[i];
String[] kvcell=kv.split(":");
mapExcel.put(kvcell[0], kvcell[1]);
if(sb.length()>0)
sb.append(",");
sb.append(kvcell[1]);
}

for(int i=0;i<mapExcel.size();i++){
ws.setColumnView(i, 30);//每一列的列宽
}

/*获取数据库名字*/
Properties props = Resources.getResourceAsProperties("application.properties");
String url = props.getProperty("jdbc.url");
String[] sp1=url.split("/");
String DataName=sp1[3].split("\\?")[0];
String sql="SELECT column_name,column_comment FROM information_schema.Columns "
+ "WHERE table_name='"+tableName+"' AND table_schema='"+DataName+"'";
List ls=Impexcelruleservice.GetListByCondition(sql);
JSONArray jsonArray = JSONArray.fromObject (ls);

/**根据模板中设置显示字段**/
Set<String> set=mapExcel.keySet();
for(int i=0;i<ls.size();i++){
JSONObject js=jsonArray.getJSONObject(i);
String column_name=js.getString("column_name");
String column_comment=js.getString("column_comment");
for(String key : set){
String field=mapExcel.get(key);   //通过键获得值
if(column_name.equals(field)){
ws.addCell(new Label(Integer.valueOf(key), 0, column_comment, smallhead));  
}
}
}
StringBuilder str= new StringBuilder();
str.append("select "+sb.toString()+" from "+tableName+" where id in (");
for(int i=0;i<strid.size();i++){
if(i==0){
str.append("'"+strid.get(i)+"'");
}else{
str.append(",'"+strid.get(i)+"'");
}
}
str.append(")");
ls=Impexcelruleservice.GetListByCondition(str.toString());
jsonArray = JSONArray.fromObject (ls);
for(int i=0;i<ls.size();i++){
JSONObject js=jsonArray.getJSONObject(i);
for(String key : set){
String field=mapExcel.get(key);   //通过键获得值
String value=js.getString(field);
if(value==null || "null".equals(value))
value="";
ws.addCell(new Label(Integer.valueOf(key), i+1, value, normalFont));
}
}
sys.addLog("\""+sheetname[0]+"\"导出成功", "导出", request);
wwb.write(); 
wwb.close(); 

} catch (Exception e) {
e.printStackTrace();

}
   }