---恢复内容开始---
1.首先来看接口文档(其中一个接口):
接口的编写:
1 /**
2 * 7.11 余额明细查询接口
3 *
4 * @param token
5 * @param pageNum
6 * 非必填 分页查询当前页数,默认为 1
7 * @param pageSize
8 * 非必填 每页记录数,默认为 20
9 * @param orderId
10 * 非必填 订单号
11 * @param startDate
12 * 非必填 开始日期,格式必须:yyyyMMdd
13 * @param endDate
14 * 非必填 截止日期,格式必须:yyyyMMdd
15 * @return
16 */
17 public String getBalanceDetail(String token, int pageNum, int pageSize, String orderId, String startDate, String endDate);
server中对于接口实现的编写:
1 private static final String API_URL_BASE = GlobalCache.getSystemCache("jdNewApiUrlBase");
2 // 通用返回
3 private static final String SUCCESS = "success";
4 private static final String RESULT_MESSAGE = "resultMessage";
5 private static final String RESULT_CODE = "resultCode";
6 private static final String RESULT = "result";
1 /**
2 * 7.11 余额明细查询接口
3 *
4 * @param token
5 * @param pageNum
6 * 非必填 分页查询当前页数,默认为 1
7 * @param pageSize
8 * 非必填 每页记录数,默认为 20
9 * @param orderId
10 * 非必填 订单号
11 * @param startDate
12 * 非必填 开始日期,格式必须:yyyyMMdd
13 * @param endDate
14 * 非必填 截止日期,格式必须:yyyyMMdd
15 * @return
16 */
17 @Override
18 public String getBalanceDetail(String token, int pageNum, int pageSize, String orderId, String startDate, String endDate) {
19 Map<String, Object> paramMap = new HashMap<>();
20 paramMap.put("token", token);
21 if (pageNum > 0) {
22 paramMap.put("pageNum", pageNum);
23 }
24 if (pageSize > 0) {
25 paramMap.put("pageSize", pageSize);
26 }
27 if (StringUtils.isNotBlank(orderId)) {
28 paramMap.put("orderId", orderId);
29 }
30 if (StringUtils.isNotBlank(startDate)) {
31 paramMap.put("startDate", startDate);
32 }
33 if (StringUtils.isNotBlank(endDate)) {
34 paramMap.put("endDate", endDate);
35 }
36 String responseStr;
37 try {
38 responseStr = HttpsUtil.post(API_URL_BASE + "/price/getBalanceDetail", paramMap);
39 } catch (Exception e) {
40 LogUtil.ERROR.error("调用接口失败:" + e);
41 throw new AppException("调用接口失败");
42 }
43 Map<String, Object> detailMsgMap = toHashMap(responseStr);
44 if (isQuerySuccess(detailMsgMap)) {
45 return detailMsgMap.get(RESULT).toString();
46 } else {
47 LogUtil.ERROR.error("调用接口返回失败:" + responseStr);
48 throw new AppException("系统出现异常,错误代码[" + detailMsgMap.get(RESULT_CODE) + "],错误信息[" + detailMsgMap.get(RESULT_MESSAGE) + "]");
49 }
50 }
在controller里面对接口进行调用:
/**
* 查询JD预存余额
* @param request
* @param response
* @return
* @throws IOException
*/
@RequestMapping(params = "method=deposit4JD")
public ModelAndView deposit4JD(HttpServletRequest request,HttpServletResponse response,
String startDate,String endDate,String orderId) {
try {
//获取token
String token = jdServer.getToken();
String map = jdServer.getBalance(token, 4);
map = map.substring(0, map.length()-2);
//首次加载页面不查询列表数据
if ("GET".equalsIgnoreCase(request.getMethod()))
{
return new ModelAndView("/pages/balancesheet/JDdeposit");
}
Map mapDetail = new HashMap<>();
Pager pager = RequestManager.getPager(request);
int pageSize = pager.getPageSize();
int currentPage = (int) pager.getPageNumber();
//token验证、当前页面、每页显示、订单号、开始时间、结束时间
mapDetail = JSONObject.fromObject(jdServer.getBalanceDetail(token, currentPage, pageSize, orderId,startDate, endDate));
/*if(paginater==null){
setAttribute(request, "count", Long.toString(0));
setAttribute(request, "beans", null);
}else{
setAttribute(request, "beans", paginater.getData());
} */
int total = (int) mapDetail.get("total");
if(total > 0){
request.setAttribute("isData", "1");
}
setAttribute(request, "count", mapDetail.get("total").toString());
request.setAttribute("map", map);
Object objData = mapDetail.get("data");
request.setAttribute("objData", objData);
request.setAttribute("startDate", startDate);
request.setAttribute("endDate", endDate);
request.setAttribute("orderId", orderId);
} catch (BizException e) {
e.printStackTrace();
String str = "调用JD查询接口查询预存额明细发生错误!";
LogUtil.ERROR.error(str + ":" + e.getMessage());
ErrorLog error = ErrorLogUtil.getErrorLog(e, request, this.getClass().getName(), str);
elServer.save(error);
throw new BizException(str);
}
return new ModelAndView("/pages/balancesheet/JDdeposit");
}
值可以让页面获取到:
1 ----------------------------
2 <body>
3 <div class="media">
4 <div class="media-body media-middle">
5 <h4 align="center">京东预存款剩余余额:<font color="red"><b>${map }元</b></font></h4>
6 </div>
7 <div class="media-right media-middle e-nowrap">
8 <a href="javascript:void(0)" onclick="addTabs('零点余额查询', 'balancesheet.do?method=queryDayBalance')">零点余额查询</a>
9 </div>
10 </div>
11 <div id="tip" style="display: inline; width: 100%;">
12 <form class="form-horizontal" method="POST" action="balancesheet.do?method=deposit4JD" id="mainForm" name="mainForm">
13 <div class="container-fluid e-checkbor">
14 <span class="e-checktit">查询条件</span>
15 <div class="row">
16 <div class="col-md-4">
17 <div class="form-group">
18 <label for="startTime01" class="col-sm-4 control-label e-nowrap">创建时间:</label>
19 <div class="col-sm-8">
20 <input class="form-control Wdate" name="startDate" id="startDate" value="${startDate}"
21 onFocus="WdatePicker({el:'startDate',dateFmt:'yyyyMMdd',maxDate:'#F{$dp.$D(\'endDate\')}'})" >
22 </div>
23 </div>
24 </div>
25 <div class="col-md-4">
26 <div class="form-group">
27 <label for="endTime01" class="col-sm-4 control-label e-nowrap">到:</label>
28 <div class="col-sm-8">
29 <input class="form-control Wdate" name="endDate" id="endDate" value="${endDate }"
30 onFocus="WdatePicker({el:'endDate',dateFmt:'yyyyMMdd',minDate:'#F{$dp.$D(\'startDate\')}'})">
31 </div>
32 </div>
33 </div>
34 <div class="col-md-4">
35 <div class="form-group">
36 <label for="orderId" class="col-sm-4 control-label e-nowrap">订单号:</label>
37 <div class="col-sm-8">
38 <input id="orderId" name="orderId" class="form-control" value="${orderId}" type="text">
39 </div>
40 </div>
41 </div>
42
43 <div class="col-md-12">
44 <div class="form-group">
45 <div class="col-sm-12 text-right">
46 <input type="submit" value="查询" class="btn btn-primary" />
47 </div>
48 </div>
49 </div>
50 </div>
51 </div>
52 <div class="container-fluid">
53 <div class="row">
54 <div class="col-xs-12 e-padlr-no">
55 <div class="list-group e-marb-no">
56 <div class="list-group-item active e-radius-no">
57 <div class="row">
58 <div class="col-xs-6">
59 <span>JD预存额明细</span>
60 </div>
61 <div class="col-xs-6 text-right">
62 <a class="e-download" href="javascript: void(0)" style='<check:privilege url="balancesheet.do?method=exportExcel"/>'
63 onclick="exportExcel('${isData}','${startDate}','${endDate }','${orderId}')">
64 <i class="glyphicon glyphicon-download-alt"></i>
65 导出Excel
66 </a>
67 </div>
68 </div>
69 </div>
70 </div>
71 </div>
72 </div>
73 </div>
74 </form>
75 </div>
76
77 <div class="ysde">
78 <table class="table table-bordered table-hover table-striped text-center e-table">
79 <thead>
80 <th width="8%" nowrap="nowrap">序号</th>
81 <!-- <th width="8%" nowrap="nowrap">id</th> -->
82 <th width="8%" nowrap="nowrap">创建时间</th>
83 <!-- <th width="8%" nowrap="nowrap">账户类型[标示]</th> -->
84 <th width="8%" nowrap="nowrap">交易金额</th>
85 <th width="8%" nowrap="nowrap">账户名称</th>
86 <th width="8%" nowrap="nowrap">订单号</th>
87 <!-- <th width="8%" nowrap="nowrap">交易类型编号</th> -->
88 <th width="8%" nowrap="nowrap">交易类型</th>
89 <th width="8%" nowrap="nowrap">交易流水号</th>
90 <th width="24%" nowrap="nowrap">备注</th>
91 </thead>
92 <tbody>
93 <form action="" name="subForm" method="post" id="subForm">
94
95 <c:forEach items="${objData}" var="par" varStatus="status">
96 <tr onmouseover="mouseoverTr(this)" onmouseout="mouseoutTr(this)">
97 <td nowrap="nowrap">${status.index+1}</td>
98 <%-- <td nowrap="nowrap">${par['id']}</td> --%>
99 <td nowrap="nowrap">${par['createdDate']}</td>
100 <%-- <td nowrap="nowrap"><c:if test="${par['accountType'] == 1}">普通商户[${par['accountType']}]</c:if></td> --%>
101 <td nowrap="nowrap">${par['amount']}</td>
102 <td nowrap="nowrap">${par['pin']}</td>
103 <td nowrap="nowrap">${par['orderId']}</td>
104 <%-- <td nowrap="nowrap">${par['tradeType']}</td> --%>
105 <td nowrap="nowrap">${par['tradeTypeName']}</td>
106 <td nowrap="nowrap">${par['tradeNo']}</td>
107 <td nowrap="nowrap">
108 <div class="e-spilled" title="${par['notePub']}" >
109 <c:if test="${fn:length(par['notePub']) > 30}">
110 ${fn:substring(par['notePub'],0,30)}...
111 </c:if>
112 <c:if test="${fn:length(par['notePub']) <= 30}">
113 ${par['notePub']}
114 </c:if>
115 </div>
116 </td>
117 </tr>
118 </c:forEach>
119 </form>
120 </tbody>
121 </table>
122 </div>
123
124
125 <pf:rect styleClass="page" align="right">
126 <pn:nav name="count" formName="mainForm" scope="request" /></pf:rect>
127
128 <!--添加窗口-->
129 <div id="w" class="easyui-window e-eWindow" title="" iconCls="icon-save" closed="true">
130 <iframe src="" width="100%" height="100%" frameborder="0" id="iframe" scrolling="yes"></iframe>
131 </div>
132 <input type="hidden" id="delsucc" value="" />
133 <!--********** About js file and plug-in dependencies **********-->
134 <%@ include file="../../pages_public/include/public_foot.jsp" %>
135 <!--********** Use only the current page **********-->
136 <script type="text/javascript" src="${pageContext.request.contextPath}/js/plugin/datePicker/WdatePicker.js"></script>
137 <script type="text/javascript">
138 function exportExcel(isData,startDate,endDate,orderId){
139 if (isData == '1') {
140 $.messager.confirm('提示', '确定下载?', function(r) {
141 if (r) {
142 location.href = "balancesheet.do?method=exportExcel&startDate=" + startDate
143 + "&endDate=" + endDate + "&orderId=" + orderId ;
144 }
145 });
146 } else {
147 $.messager.alert('提示', '没有记录,无法下载!');
148 }
149 }
150 </script>
151 </body>
上面还有一个Excel文档的下载方式,调用controller里面的下载方法:
1 @RequestMapping(params = "method=exportExcel")
2 public ModelAndView exportExcel(HttpServletRequest request, HttpServletResponse response,
3 String startDate,String endDate,String orderId)
4 throws IOException {
5 try {
6 ExportExcle exportExcle = new ExportExcle(); //新建导出对象
7 Object[] headerArray = new Object[] { //表头数组
8 "创建时间",
9 "交易金额",
10 "账户名称",
11 "订单号",
12 "交易类型","交易流水号","备注"};
13 exportExcle.setTitleAndHeader("JD预存额明细", headerArray); //设置标题和表头----------step_1
14
15 // 数据对应的数组List
16 for(Pager pager : exportExcle.getPagerList()) {
17 List<Object[]> contentList = new ArrayList<>(); //内容列表
18 // ---------- 将从数据库获取的数据封装成List开始
19 Map mapDetail = new HashMap<>();
20 String token = jdServer.getToken();
21 //Pager pager = RequestManager.getPager(request);
22 int pageSize = 1000;//pager.getPageSize();
23 int currentPage = 1;//(int) pager.getPageNumber();
24 //token验证、当前页面、每页显示、订单号、开始时间、结束时间
25 mapDetail = JSONObject.fromObject(jdServer.getBalanceDetail(token, currentPage, pageSize, orderId,startDate, endDate));
26 JSONArray objData = (JSONArray)mapDetail.get("data");
27 for(Object object : objData) {
28 Map map = (Map) object;
29 Object[] objArr = new Object[] {
30 map.get("createdDate"),
31 map.get("amount"),
32 map.get("pin"),
33 map.get("orderId"),
34 map.get("tradeTypeName"),
35 map.get("tradeNo"),
36 String.valueOf(map.get("notePub")).trim()
37 };
38 contentList.add(objArr); //增加内容
39 }
40 // ---------- 将从数据库获取的数据封装成List结束
41 exportExcle.setContentPagerDate(contentList, pager); //设置需要导出的内容列表----------step_2
42 }
43 exportExcle.exportWorkbook(response); //将文件写入输出流----------step_3
44 } catch (Exception e) {
45 LogUtil.MSG.error("下载失败了", e);
46 }
47 return null;
48
49 }
在下载Excel时,调用了封装好的exportExcel里面的方法,其封装类如下:
1 package com.eptok.util;
2
3 import java.io.ByteArrayOutputStream;
4 import java.io.FileOutputStream;
5 import java.io.IOException;
6 import java.io.OutputStream;
7 import java.io.UnsupportedEncodingException;
8 import java.math.BigDecimal;
9 import java.text.DecimalFormat;
10 import java.util.ArrayList;
11 import java.util.HashMap;
12 import java.util.List;
13 import java.util.Map;
14
15 import javax.servlet.http.HttpServletResponse;
16
17 import org.apache.poi.hssf.util.HSSFColor;
18 import org.apache.poi.ss.usermodel.Cell;
19 import org.apache.poi.ss.usermodel.CellStyle;
20 import org.apache.poi.ss.usermodel.Font;
21 import org.apache.poi.ss.usermodel.HorizontalAlignment;
22 import org.apache.poi.ss.usermodel.Row;
23 import org.apache.poi.ss.usermodel.Sheet;
24 import org.apache.poi.ss.usermodel.VerticalAlignment;
25 import org.apache.poi.ss.util.CellRangeAddress;
26 import org.apache.poi.xssf.streaming.SXSSFSheet;
27 import org.apache.poi.xssf.streaming.SXSSFWorkbook;
28
29 import hikefa.core.web.page.Pager;
30
31 public class ExportExcle {
32
33 /** 最大导出行数*/
34 public static final int MAX_ROWS = 500000;
35
36 /** 分页行数*/
37 public static final int PAGING_ROWS = 50000;
38
39 /** SXSSF操作行数*/
40 public static final int SXSSF_ROWS = 100;
41
42 /** 缓存行数*/
43 public static final int CACHE_ROWS = 1000;
44
45 /** 创建Workbook*/
46 SXSSFWorkbook workbook = new SXSSFWorkbook(SXSSF_ROWS);
47
48 /** 当前Sheet*/
49 Sheet sheet;
50
51 /** 内容总数*/
52 private int contentCount = 0;
53
54 /** 总页数*/
55 private int pageCount;
56
57 /** 分页列表*/
58 private List<Pager> pagerList = new ArrayList<>();
59
60 /** 标题*/
61 private String title;
62
63 /** 表头数组*/
64 private Object[][] headerArray;
65
66 /** 导出列数*/
67 private int colCount;
68 /** 标题行数*/
69 private int titleRows = 0;
70 /** 标题行数*/
71 private int headerRows = 0;
72
73 /** 样式Map*/
74 private Map<String, CellStyle> styleMap = new HashMap<>();
75
76 /** 列宽数组*/
77 private Integer[] intArray;
78
79 /** 构造方法*/
80 public ExportExcle() {
81 }
82 // //***Demo***
83 // ExportExcle exportExcle = new ExportExcle(); //新建导出对象
84 // int contentCount = 0; //从数据库获取内容总数(可选)----------step_0(数据量大于50000,建议设置)
85 // exportExcle.setContentCount(contentCount); //设置内容总数(可选)----------step_0(数据量大于50000,建议设置)
86 // Object[] headerArray = new Object[] { //表头数组
87 // "",
88 // ""};
89 // exportExcle.setTitleAndHeader("标题", headerArray); //设置标题和表头----------step_1
90 // //遍历分页列表进行操作
91 // for(Pager pager : exportExcle.getPagerList()) {
92 // List<Object[]> contentList = new ArrayList<>(); //内容列表
93 // // ---------- 将从数据库获取的数据封装成List开始
94 // Paginater paginater = server.findSplitPage(pager);
95 // for(Object object : paginater.getData()) {
96 // Map<String, Object> map = (Map<String, Object>) object;
97 // Object[] objArr = new Object[] {};
98 // contentList.add(objArr); //增加内容
99 // }
100 // // ---------- 将从数据库获取的数据封装成List结束
101 // exportExcle.setContentPagerDate(contentList, pager); //设置需要导出的内容列表----------step_2
102 // }
103 // exportExcle.exportWorkbook(response); //将文件写入输出流----------step_3
104
105
106 /** step_0:设置内容总数(不设置则默认为单页导出)*/
107 public void setContentCount(int contentCount) {
108 this.contentCount = contentCount; //设置内容总数
109 }
110
111 /** step_1:设置标题和表头数组(单行),并初始化参数*/
112 public void setTitleAndHeader(String title, Object[] headerArray) {
113 Object[][] headerArrayTemp = new Object[1][];
114 headerArrayTemp[0] = headerArray;
115 setTitleAndHeader(title, headerArrayTemp);
116 }
117 /** step_1:设置标题和表头数组(多行),并初始化参数*/
118 public void setTitleAndHeader(String title, Object[][] headerArray) {
119 this.title = title;
120 this.headerArray = headerArray;
121 parameterChecking(); //校验参数
122 }
123
124 /** step_2:设置需要导出的内容列表*/
125 public void setContentPagerDate(List<Object[]> contentList, Pager pager) {
126 LogUtil.APP.info("开始将[" + title + "] - 第" + pager.getPageNumber() +"页数据写入Excle文件 - "
127 + DateUtil.getCurrentDateTime("yyyy-MM-dd HH:mm:ss"));
128 if(null == contentList || contentList.isEmpty()) {
129 throw new IllegalArgumentException("查询不到需要导出的数据[contentList]");
130 }
131 // ---------- 导出数据到Excle文件Sheet页开始
132 String sheetName = "第" + pager.getPageNumber() +"页 - 共" + pageCount + "页"; //sheet名称
133 sheet = workbook.createSheet(sheetName); //创建工作表
134 writeTitle(); //写入标题
135 writeHeader(); //写入表头数组
136 writeContent(contentList); //写入内容列表
137 autoColumnSize(); //自适应列宽,并冻结窗格
138 // ---------- 导出数据到Excle文件Sheet页结束
139 contentList.clear(); //清空列表
140 LogUtil.APP.info("将[" + title + "] - 第" + pager.getPageNumber() +"页数据写入Excle文件结束 - "
141 + DateUtil.getCurrentDateTime("yyyy-MM-dd HH:mm:ss"));
142 }
143
144 /** step_3:将文件写入输出流*/
145 public void exportWorkbook() {
146 exportWorkbook("D:/"); //默认导出到D盘根目录
147 }
148 /** step_3:将文件写入输出流*/
149 public void exportWorkbook(String path) {
150 FileOutputStream fos = null;
151 try {
152 fos = new FileOutputStream(path + title + ".xlsx");
153 workbook.write(fos);
154 ((SXSSFWorkbook) workbook).dispose();
155 } catch (IOException e) {
156 e.printStackTrace();
157 } finally {
158 if (fos != null) {
159 try {
160 fos.close();
161 } catch (IOException e) {
162 e.printStackTrace();
163 }
164 }
165 }
166 }
167 /** step_3:将文件写入输出流*/
168 public void exportWorkbook(HttpServletResponse response) {
169 ByteArrayOutputStream baos = null;
170 OutputStream os = null;
171 try {
172 baos = new ByteArrayOutputStream();
173 workbook.write(baos);
174 ((SXSSFWorkbook) workbook).dispose();
175 String fileName = new String(title.getBytes("gbk"), "ISO8859-1");
176 response.reset(); // 清空输出流
177 response.setHeader("Content-disposition", "attachment; filename=" + fileName + ".xlsx");
178 response.setContentType("application/msexcel");
179 response.setContentLength(baos.size());
180 os = response.getOutputStream();
181 baos.writeTo(os);
182 os.flush();
183 } catch (IOException e) {
184 e.printStackTrace();
185 } finally {
186 if (baos != null) {
187 try {
188 baos.close();
189 } catch (IOException e) {
190 e.printStackTrace();
191 }
192 }
193 if (os != null) {
194 try {
195 os.close();
196 } catch (IOException e) {
197 e.printStackTrace();
198 }
199 }
200 }
201 }
202
203
204 // --------------------------------------------------调用的方法--------------------------------------------------
205
206
207 /** 校验参数*/
208 private void parameterChecking() {
209 if(null == title) {
210 throw new IllegalArgumentException("标题未设置");
211 }
212 if(null == headerArray || headerArray.length == 0) {
213 throw new IllegalArgumentException("表头数组未设置");
214 } else {
215 if(null != headerArray[0] && 0 < headerArray[0].length) {
216 headerRows = headerArray.length; //设置表头行数
217 colCount = headerArray[0].length; //设置导出列数
218 intArray = new Integer[colCount]; //初始化列宽数组
219 } else {
220 throw new IllegalArgumentException("无法确定导出列数");
221 }
222 }
223 if(contentCount <= 0) {
224 contentCount = PAGING_ROWS; //默认为单页导出
225 }
226 setPagerList(); //设置分页信息
227 initStyleMap(); //初始化样式Map
228 }
229
230 /** 设置分页列表*/
231 private void setPagerList() {
232 if(contentCount > MAX_ROWS) {
233 contentCount = MAX_ROWS;
234 }
235 //计算总页数
236 pageCount = contentCount % PAGING_ROWS > 0 ? contentCount / PAGING_ROWS + 1 : contentCount / PAGING_ROWS;
237 //设置分页列表
238 for (int i = 1; i <= pageCount; i++) {
239 // int paging = i == pageCount && contentCount % PAGING_ROWS > 0 ?
240 // contentCount % PAGING_ROWS : PAGING_ROWS; //当前分页行数
241 // pagingList.add(i, new Pager(i, paging));
242 pagerList.add(new Pager(i, PAGING_ROWS));
243 }
244 }
245 /** 获取分页列表*/
246 public List<Pager> getPagerList() {
247 return pagerList;
248 }
249
250
251 // --------------------------------------------------Excle操作--------------------------------------------------
252
253
254 /** 写入表头标题*/
255 private void writeTitle() {
256 //创建行对象
257 Row row = sheet.createRow(0); //创建行对象
258 Cell cell = row.createCell(0); //创建单元格
259 cell.setCellValue(title); //写入内容
260 setRowStyle(0, styleMap.get("titleStyle")); //设置行样式
261 mergedRegion(0, 0, 0, colCount-1); //合并单元格
262 titleRows = 1; //记录标题行数
263 }
264
265 /** 写入表头数组headerArray[row][col]*/
266 private void writeHeader() {
267 for(int rowNum=0; rowNum < headerRows; rowNum++) {
268 if(null == headerArray[rowNum] || 0 == headerArray[rowNum].length) {
269 continue; //行数据为空,跳出当次循环
270 }
271 //创建行对象
272 Row row = sheet.createRow(rowNum + titleRows);
273 for(int colNum=0; colNum < colCount; colNum++) {
274 Cell cell = row.createCell(colNum); //创建单元格
275 cell.setCellStyle(styleMap.get("headerStyle")); //设置表头样式
276 if(null == headerArray[rowNum][colNum] || "*".equals(headerArray[rowNum][colNum])) {
277 continue; //单元格数据为空,跳出当次循环
278 }
279 setCellValue(cell, headerArray[rowNum][colNum], colNum); //写入内容值
280 }
281 }
282 }
283
284 /** 写入内容列表contentList*/
285 private void writeContent(List<Object[]> contentList) {
286 for(int rowNum = 0; rowNum < contentList.size(); rowNum++) {
287 Object[] array = contentList.get(rowNum);
288 if(null == array || 0 == array.length) {
289 continue; //数组为空,跳出当次循环
290 }
291 //创建行对象
292 Row row = sheet.createRow(rowNum + titleRows + titleRows); //在表头之后
293 for(int colNum = 0; colNum < array.length; colNum++) {
294 if(null == array[colNum]) {
295 continue; //跳出当次循环
296 }
297 Cell cell = row.createCell(colNum); //创建单元格
298 cell.setCellStyle(styleMap.get("contentStyle")); //设置样式
299 setCellValue(cell, array[colNum], colNum); //写入内容值
300 }
301 //每当行数达到设置的值就刷新数据到硬盘,以清理内存
302 if(rowNum % CACHE_ROWS == 0){
303 try {
304 ((SXSSFSheet) sheet).flushRows();
305 } catch (IOException e) {
306 e.printStackTrace();
307 }
308 }
309 }
310 autoMergedRegion(); //自动合并单元格
311 }
312
313 /** 自适应列宽*/
314 private void autoColumnSize() {
315 /*//自适应列宽
316 for(int colNum=0; colNum<headerColSize; colNum++) {
317 sheet.autoSizeColumn(colNum, true); //自适应尺寸
318 //默认最小列宽(取默认宽度值8)
319 int minColumnWidth = (int)((sheet.getDefaultColumnWidth() + 0.72) * 256);
320 if(sheet.getColumnWidth(colNum) < minColumnWidth) {
321 sheet.setColumnWidth(colNum, minColumnWidth);
322 }
323 }*/
324 //批量设置列宽
325 for(int i=0; i<intArray.length; i++) {
326 if(null == intArray[i] || intArray[i] < sheet.getDefaultColumnWidth()) {
327 //默认最小列宽(取默认宽度值8)
328 intArray[i] = sheet.getDefaultColumnWidth();
329 }
330 sheet.setColumnWidth(i, (int)((intArray[i] + 2 + 0.72) * 256)); //参数为列index,列宽(字符数)
331 }
332 //冻结窗格
333 sheet.createFreezePane(0, titleRows + headerRows, 0, titleRows + headerRows);
334 }
335
336
337
338 /** 设置单元格的值*/
339 private void setCellValue(Cell cell, Object obj, int colNum) {
340 //格式化参数
341 if(obj instanceof BigDecimal || obj instanceof Double) {
342 Double decimal = Double.valueOf(obj.toString().replace(" ", ""));
343 obj = new DecimalFormat("#0.00").format(decimal);
344 }
345 cell.setCellValue(obj.toString()); //写入内容
346 try {
347 int length = obj.toString().getBytes("gbk").length;
348 if(intArray[colNum] == null || intArray[colNum] < length) {
349 intArray[colNum] = length;
350 }
351 } catch (UnsupportedEncodingException e) {
352 e.printStackTrace();
353 }
354 }
355
356 /** 自动合并单元格*/
357 private void autoMergedRegion() {
358 if(null == headerArray || 0 == headerArray.length) {
359 return; //表头数组为空,退出方法
360 }
361 if(null == headerArray[0][0]) {
362 headerArray[0][0] = ""; //初始项不能空
363 }
364 //设置表头数值
365 for(int rowNum=0; rowNum < headerRows; rowNum++) {
366 for(int colNum=0; colNum < colCount; colNum++) {
367 if(null != headerArray[rowNum][colNum] && !"*".equals(headerArray[rowNum][colNum])) {
368 autoMergedRegion(rowNum ,colNum); //执行合并操作
369 }
370 }
371 }
372 }
373 /** 执行合并操作*/
374 private void autoMergedRegion(int firstRow ,int firstCol) {
375 int lastRow = headerRows - 1; //最后一行
376 int lastCol = colCount - 1; //最后一列
377 //如果不为最后一行
378 if(firstRow != lastRow) {
379 for(int row = firstRow+1; row < headerRows; row++) {
380 if(null != headerArray[row][firstCol]) {
381 if("*".equals(headerArray[row][firstCol])) {
382 lastRow = row; //定位最后一行,占位符不减1
383 } else {
384 lastRow = row-1; //定位最后一行
385 }
386 break;
387 }
388 }
389 }
390 //如果不为最后一列
391 if(firstCol != lastCol) {
392 for(int col=firstCol+1; col < colCount; col++) {
393 if(null != headerArray[firstRow][col]) {
394 if("*".equals(headerArray[firstRow][col])) {
395 lastCol = col; //定位最后一列,占位符不减1
396 } else {
397 lastCol = col-1; //定位最后一列
398 }
399 break;
400 }
401 }
402 }
403 if(firstRow == lastRow && firstCol == lastCol) {
404 return; //无操作,不执行方法
405 }
406 mergedRegion(firstRow + titleRows, lastRow + titleRows, firstCol, lastCol);
407 }
408 /** 合并单元格*/
409 private void mergedRegion(int firstRow, int lastRow, int firstCol, int lastCol) {
410 sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol, lastCol));
411 }
412
413 /** 设置行样式*/
414 private void setRowStyle(int rowNum, CellStyle style) {
415 if(null == style) {
416 return; //样式为空,退出方法
417 }
418 Row row = sheet.getRow(rowNum);
419 if(row == null) {
420 row = sheet.createRow(rowNum); //如果行对象为空,则创建
421 }
422 for(int colNum = 0; colNum < colCount; colNum++) {
423 Cell cell=row.getCell(colNum);
424 if( cell == null){
425 cell = row.createCell(colNum);
426 cell.setCellValue("");
427 }
428 cell.setCellStyle(style); //设置样式
429 }
430 }
431
432 /** 初始化样式Map*/
433 private void initStyleMap() {
434 CellStyle titleStyle = workbook.createCellStyle();
435 titleStyle.setAlignment(HorizontalAlignment.CENTER);// 水平对齐方式
436 titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直对齐方式
437 // style.setFillForegroundColor(HSSFColor.AQUA.index);// 前景色
438 // style.setFillPattern(FillPatternType.SOLID_FOREGROUND);// 模式(前景色)
439 // style.setWrapText(true);// 自动换行
440 Font font1 = workbook.createFont();// 生成一个字体
441 font1.setColor(HSSFColor.RED.index);// 字体颜色
442 font1.setFontHeightInPoints((short) 16);// 字体大小
443 // font.setBold(true);// 字体加粗
444 font1.setFontName("Arial");// 字体名称
445 titleStyle.setFont(font1);// 设置字体
446 styleMap.put("titleStyle", titleStyle); //放入样式Map
447
448 CellStyle headerStyle = workbook.createCellStyle();
449 headerStyle.setAlignment(HorizontalAlignment.CENTER);// 水平对齐方式
450 headerStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直对齐方式
451 // style.setBorderTop(BorderStyle.THIN);// 上边框
452 // style.setBorderBottom(BorderStyle.THIN);// 下边框
453 // style.setBorderLeft(BorderStyle.THIN);// 左边框
454 // style.setBorderRight(BorderStyle.THIN);// 右边框
455 Font font2 = workbook.createFont();// 生成一个字体
456 font2.setColor(HSSFColor.BLUE.index);// 字体颜色
457 font2.setFontHeightInPoints((short) 10);// 字体大小
458 font2.setFontName("Arial");// 字体名称
459 headerStyle.setFont(font2);// 设置字体
460 styleMap.put("headerStyle", headerStyle); //放入样式Map
461
462 CellStyle contentStyle = workbook.createCellStyle();
463 contentStyle.setAlignment(HorizontalAlignment.CENTER);// 水平对齐方式
464 contentStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直对齐方式
465 Font font3 = workbook.createFont();// 生成一个字体
466 font3.setFontHeightInPoints((short) 10);// 字体大小
467 font3.setFontName("Arial");// 字体名称
468 contentStyle.setFont(font3);// 设置字体
469 styleMap.put("contentStyle", contentStyle); //放入样式Map
470 }
471
472 }
完!!!!