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> -->
