1 // js 触发导出 excel 方法 导出当前页的数据 含有条件查询的结果 2 // js 框架使用的 是 easyui 3 function doExport(){ 4 5 6 var optins = $("#grid").datagrid("getPager").data("pagination").options; 7 var page = optins.pageNumber; 8 var rows = optins.pageSize; 9 var cpname=$("#cpname_id").val(); 10 var adname=$("#adname_id").val(); 11 var start = $("#start_id").val(); 12 var end = $("#end_id").val(); 13 $("<form>").attr({ 14 "action":"${ctx}/rest/trafficbill/doexporttrafficaccount", 15 "method":"POST" 16 }).append("<input type='text' name='page' value='"+page+"'/>") 17 .append("<input type='text' name='cpname' value='"+cpname+"'/>") 18 .append("<input type='text' name='adname' value='"+adname+"'/>") 19 .append("<input type='text' name='startTime' value='"+start+"'/>") 20 .append("<input type='text' name='endTime' value='"+end+"'/>") 21 .append("<input type='text' name='rows' value='"+rows+"'/>").submit(); 22 } 23 24 25 26 27 28 // poi 操作 excel 的 工具类 29 30 31 package com.manage.util; 32 33 import java.util.Date; 34 import java.util.List; 35 import java.util.Map; 36 37 import javax.servlet.http.HttpServletRequest; 38 import javax.servlet.http.HttpServletResponse; 39 40 import org.apache.poi.hssf.usermodel.HSSFCell; 41 import org.apache.poi.hssf.usermodel.HSSFCellStyle; 42 import org.apache.poi.hssf.usermodel.HSSFFont; 43 import org.apache.poi.hssf.usermodel.HSSFSheet; 44 import org.apache.poi.hssf.usermodel.HSSFWorkbook; 45 import org.springframework.web.servlet.view.document.AbstractExcelView; 46 47 import com.pojo.subaccount.PageData; 48 49 /** 50 * 51 * @author jemond 52 * 53 * 2015年6月26日上午9:42:48 54 */ 55 public class ObjectExcelView extends AbstractExcelView{ 56 57 @SuppressWarnings("deprecation") 58 @Override 59 protected void buildExcelDocument(Map<String, Object> model, 60 HSSFWorkbook workbook, HttpServletRequest request, 61 HttpServletResponse response) throws Exception { 62 63 64 65 Date date = new Date(); 66 String filename = Tools.date2Str(date, "yyyyMMddHHmmss"); 67 HSSFSheet sheet; 68 HSSFCell cell; 69 response.setContentType("application/octet-stream"); 70 response.setHeader("Content-Disposition", "attachment;filename="+filename+".xls"); 71 sheet = workbook.createSheet("sheet1"); 72 73 @SuppressWarnings("unchecked") 74 List<String> titles = (List<String>) model.get("titles"); 75 int len = titles.size(); 76 HSSFCellStyle headerStyle = workbook.createCellStyle(); //标题样式 77 headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); 78 headerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); 79 HSSFFont headerFont = workbook.createFont(); //标题字体 80 headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); 81 headerFont.setFontHeightInPoints((short)11); 82 headerStyle.setFont(headerFont); 83 short width = 20,height=25*20; 84 sheet.setDefaultColumnWidth(width); 85 for(int i=0; i<len; i++){ //设置标题 86 String title = titles.get(i); 87 cell = getCell(sheet, 0, i); 88 cell.setCellStyle(headerStyle); 89 setText(cell,title); 90 } 91 sheet.getRow(0).setHeight(height); 92 93 HSSFCellStyle contentStyle = workbook.createCellStyle(); //内容样式 94 contentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); 95 @SuppressWarnings("unchecked") 96 List<PageData> varList = (List<PageData>) model.get("varList"); 97 int varCount = varList.size(); 98 for(int i=0; i<varCount; i++){ 99 PageData vpd = varList.get(i); 100 for(int j=0;j<len;j++){ 101 String varstr = vpd.getString("var"+(j+1)) != null ? vpd.getString("var"+(j+1)) : ""; 102 cell = getCell(sheet, i+1, j); 103 cell.setCellStyle(contentStyle); 104 setText(cell,varstr); 105 } 106 107 } 108 109 } 110 111 112 113 } 114 115 116 117 118 119 // controller 导出excel 120 121 122 @RequestMapping("/doexporttrafficaccount") 123 public ModelAndView doExportTrafficAccount(@RequestParam(value = "page", defaultValue = "1") Integer page, 124 @RequestParam(value = "rows", defaultValue = "30") Integer rows,String cpname,String adname,String startTime,String endTime) throws Exception { 125 126 // 构造一个查询条件 127 TvgameBillingTraffic tbt = new TvgameBillingTraffic(); 128 129 if(StringUtils.isNotBlank(cpname)){ 130 tbt.setCpname(cpname); 131 } 132 if(StringUtils.isNotBlank(adname)){ 133 tbt.setAdname(adname); 134 } 135 if(StringUtils.isNotBlank(startTime)){ 136 tbt.setStartTime(DateFormatUtil.dateToStartTime(startTime)); 137 } 138 if(StringUtils.isNotBlank(endTime)){ 139 tbt.setEndTime(DateFormatUtil.dateToEndTime(endTime)); 140 141 } 142 // 得到 当前页的所有数据 143 EasyUIResult account = this.trafficAccountService.queryTrafficAccount(page, rows, tbt); 144 145 @SuppressWarnings({"unchecked"}) 146 List<TvgameBillingTraffic> userList = (List<TvgameBillingTraffic>)account.getRows(); 147 148 // 封装 excel 参数 149 try { 150 151 // 参数 MAP 152 Map<String, Object> dataMap = new HashMap<String, Object>(); 153 154 // 设置标题 155 List<String> titles = new ArrayList<String>(); 156 titles.add("账务编号"); // 1 157 titles.add("CP名称"); // 2 158 titles.add("计费策略"); // 3 159 titles.add("流量名称"); // 4 160 titles.add("计价金额"); // 5 161 titles.add("流量流水"); // 6 162 titles.add("创建时间"); // 7 163 titles.add("结算状态"); // 8 164 dataMap.put("titles", titles); 165 166 // 存放内容的集合 167 List<PageData> varList = new ArrayList<PageData>(); 168 169 for (TvgameBillingTraffic p : userList) { 170 PageData vpd = new PageData(); 171 vpd.put("var1", String.valueOf(p.getId())); // 1 172 vpd.put("var2", p.getCpname()); // 2 173 vpd.put("var3", p.getChargingname()); // 3 174 vpd.put("var4", p.getAdname()); // 4 175 vpd.put("var5", String.valueOf(p.getPrice())); // 5 176 vpd.put("var6", p.getTraffic()); // 6 177 178 // 格式化时间 存在数据库是 毫秒 值 179 long createTime = p.getCreateTime(); 180 String data = DateFormatUtils.getData(createTime); 181 vpd.put("var7", data); // 7 182 183 184 // 格式化结算状态 存在数据 是 0 或1 185 String settString = "" ; 186 byte settlement = p.getSettlement(); 187 if(settlement == 0){ 188 settString = "未结算"; 189 }else if (settlement == 1) { 190 191 settString = "已经结算"; 192 }else{ 193 settString = "未知状态"; 194 } 195 vpd.put("var8", settString); // 8 196 varList.add(vpd); 197 } 198 199 dataMap.put("varList", varList); 200 201 // 执行excel操作 是一个视图 202 ObjectExcelView erv = new ObjectExcelView(); 203 ModelAndView mv = new ModelAndView(erv, dataMap); 204 return mv; 205 } catch (Exception e) { 206 LOGGER.error("错误"); 207 return null; 208 } 209 210 } 211 212 213 214 215 216 // 操作时间的 工具类 217 218 /** 219 * 220 * @author jemond 221 * 222 * 2015年6月20日上午9:42:48 223 */ 224 package com.common.util; 225 226 import java.text.ParseException; 227 import java.text.SimpleDateFormat; 228 import java.util.Calendar; 229 import java.util.Date; 230 231 232 233 public class DateFormatUtils { 234 235 public static int getSecond(){ 236 String secondStr = String.valueOf(System.currentTimeMillis()); 237 String second = secondStr.substring(0,secondStr.length()-3); 238 return Integer.valueOf(second); 239 } 240 241 public static String getData(long time){ 242 time = time * 1000 ; 243 SimpleDateFormat sdf= new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); 244 String format = sdf.format(new Date(time)); 245 return format; 246 } 247 248 public static String getDateShout(long time){ 249 time = time * 1000 ; 250 SimpleDateFormat sdf= new SimpleDateFormat("yyyy-MM-dd"); 251 String format = sdf.format(new Date(time)); 252 return format; 253 } 254 255 public static long getDateByLong(){ 256 257 SimpleDateFormat sdf= new SimpleDateFormat("yyyy-MM-dd"); 258 long time=0; 259 try { 260 261 Calendar c = Calendar.getInstance(); 262 c.add(Calendar.DATE, - 7); 263 Date monday = c.getTime(); 264 String preMonday = sdf.format(monday); 265 266 time=sdf.parse(preMonday).getTime()/1000; 267 268 269 } catch (ParseException e) { 270 271 e.printStackTrace(); 272 } 273 return time; 274 275 } 276 277 278 279 280 281 /*public static void main(String[] args) { 282 Float p = 565654656565656645645645454564.2265665656f; 283 System.out.println(getData(1435547609)+"=="+p); 284 285 }*/ 286 } 287 288 289 290 291 // spring mvc 的 部分 配置 292 <!-- 配置视图解析器 --> 293 <bean 294 class="org.springframework.web.servlet.view.InternalResourceViewResolver"> 295 <!-- 前缀 --> 296 <property name="prefix" value="/WEB-INF/views/" /> 297 <!-- 后缀 --> 298 <property name="suffix" value=".jsp" /> 299 <!-- 查找顺序 --> 300 <!-- <property name="order" value="2"/> --> 301 </bean> 302 303 304 <!-- 定义Excel视图对象 --> 305 <!-- <bean name="utilView" class="com.manage.util.ObjectExcelView "/> 306 307 定义，根据bean的名称查找视图 308 <bean class="org.springframework.web.servlet.view.BeanNameViewResolver"> 309 查找顺序 310 <property name="order" value="1"/> 311 </bean> -->