POI的一些运用

Action

/**   
*      
* @描述：   下载订单申请
* @author：YAC   
* @time：2017年5月17日 上午9:05:44        
*/

public void cultivaterFileDownLoad() throws Exception {
Admin admin = (Admin) session().getAttribute(IConstants.SESSION_ADMIN);
String order_code = request().getParameter("order_code");
String username = new String(request().getParameter("username").getBytes("iso8859-1"),"utf-8");
String shopName = new String(request().getParameter("shopName").getBytes("iso8859-1"),"utf-8");
String mobilePhone = request().getParameter("mobilePhone");
String startTime = request().getParameter("startTime");
String endTime = request().getParameter("endTime");
long order_state = Convert.strToLong(request().getParameter("order_state"), -1);
String path = session().getServletContext().getRealPath("/");
// 文件下载
List<Map<String,Object>> fileMap = orderService.cultivaterFileDownLoad(order_code,startTime,mobilePhone,endTime,username,shopName,order_state,admin.getUserName(),path);
   String fileName = "订单报表";
  String sheetName="订单列表";
   String titleName="我的订单";
   String[] headers = {"订单编号", "订单时间", "订单金额", "用户名称","店铺名称","手机号","订单状态"};
   String[] columns = { "order_code", "order_time", "order_price", "username", "shop_name", "mobilePhone", "order_state" };
String pattern="yyyy-MM-dd HH:mm:ss";
HSSFWorkbook wb= ExportExcelUtil.exportExcel(fileName,sheetName, titleName, headers, columns, fileMap, pattern);
try {
           fileName = fileName+".xls";
    response().setContentType("application/x-execl");
    response().setHeader("Content-Disposition", "attachment;filename=" + new String(fileName.getBytes(), "ISO-8859-1"));
    ServletOutputStream outputStream = response().getOutputStream();
   wb.write(outputStream);
   outputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
    }

Service

/**   
*      
* @描述：   文件下载
* @throws SQLException 
* @author：YAC   
* @time：2017年5月17日 上午9:05:44        
*/

public List<Map<String, Object>> cultivaterFileDownLoad(String order_code, String startTime, String mobilePhone,
String endTime, String username, String shopName, long order_state, String userName2, String path) throws SQLException {
Connection conn = null;
        List<Map<String, Object>> resultList = null;
        try{
conn = MySQL.getConnection();
resultList =  orderDao.cultivaterFileDownLoad(conn,order_code,startTime,endTime,username,shopName,order_state,path,mobilePhone);
for (int i = 0; i < resultList.size(); i++) {
Map<String, Object> map = resultList.get(i);
Integer order_statas = (Integer) map.get("order_state");
   if(order_statas==0){
    map.put("order_state","待付款");
   }else if(order_statas==1){
    map.put("order_state","已付款");
   }else if(order_statas==2){
    map.put("order_state","已发货");
   }else if(order_statas==3){
    map.put("order_state","已完成");
   }else if(order_statas==4){
    map.put("order_state","已取消");
   }
}
        }catch(Exception e){
e.printStackTrace();
}finally{
if(null!=conn){
conn.close();
}
}
return resultList;
}

Dao

*      
* @描述：   查询订单数据
* @author：YAC   
* @time：2017年5月17日 上午9:05:44        
*/
@SuppressWarnings("null")
public List<Map<String, Object>> cultivaterFileDownLoad(Connection conn, String order_code, String startTime,String endTime,
String username, String shopName, long order_state, String path,String mobilePhone) throws SQLException, DataException {
List<Map<String, Object>> resultList = null;
StringBuffer sql = new StringBuffer("");
sql.append("select a.order_code,a.order_time,a.order_price,a.order_state,b.username,b.mobilePhone,c.shop_name from t_order a left join t_user b on a.user_id=b.id left join t_shop_info c on a.shop_id=c.shop_id WHERE 1 = 1 ");
if(order_state !=-1){
sql.append(" and a.order_state = " + order_state);
}
if(null!=order_code&&!"".equals(order_code.trim())){
sql.append(" and a.order_code = '" + order_code+"'");
}
if (startTime!=null && !startTime.equals("")  && endTime!=null && !endTime.equals("") ) {
sql.append("  and a.order_time BETWEEN '"+startTime + "' AND '"+endTime+"'");
}
if(null!=username&&!"".equals(username.trim())){
sql.append(" and b.username like '%" + username+"%'");
}
if(null!=shopName&&!"".equals(shopName.trim())){
sql.append(" and c.shop_name like '%" + shopName+"%'");
}
if(null!=mobilePhone&&!"".equals(mobilePhone.trim())){
sql.append(" and b.mobilePhone = '" + mobilePhone+"'");
}
DataSet ds = MySQL.executeQuery(conn, sql.toString());
ds.tables.get(0).rows.genRowsMap();
resultList = ds.tables.get(0).rows.rowsMap;
return resultList;
}