excel 的操作是基于apache POI 开源项目,这个项目能操作excel,word等
官网:https://poi.apache.org/
maven依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.10-FINAL</version>
</dependency>
spring mvc可以支持excel的下载 继承AbstractExcelView
eg:View层:QrmallCashExcelView
public class QrmallCashExcelView extends AbstractExcelView {
@SuppressWarnings("unchecked")
@Override
protected void buildExcelDocument(Map<String, Object> model,
HSSFWorkbook workbook, HttpServletRequest request,
HttpServletResponse response) throws Exception {
List<QrmallCash> items = (List<QrmallCash>) model.get("items");
HSSFSheet sheet = workbook.createSheet();
sheet.setDefaultColumnWidth(30);
HSSFCellStyle style = getTableTitle(workbook);
getCell(sheet, 0, 0, style, "昵称");
getCell(sheet, 0, 1, style, "姓名");
getCell(sheet, 0, 2, style, "金额");
getCell(sheet, 0, 3, style, "时间");
for (int i = 0; i < items.size(); i++) {
QrmallCash item = items.get(i);
User user = item.getUser();
String name = user.getIdent() == null
|| StringUtils.isEmpty(user.getIdent().getName()) ? ""
: user.getIdent().getName();
getCell(sheet, i + 1, 0, style, name);
getCell(sheet, i + 1, 1, style, user.getNickname());
getCell(sheet, i + 1, 2, style, item.getAmount() / 100 + "元");
getCell(sheet, i + 1, 3, style, DateFormatUtils.format(
item.getCdate(), "yyyy-MM-dd HH:mm:ss"));
}
StringBuffer fileName = new StringBuffer()
.append("平台资金流转情况报表")
.append(DateFormatUtils.format(System.currentTimeMillis(),
"yyyy-MM-dd")).append(".xls");
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-disposition", "attachment;filename="
+ URLEncoder.encode(fileName.toString(), "UTF-8"));
OutputStream outputStream = response.getOutputStream();
workbook.write(outputStream);
outputStream.flush();
IOUtils.closeQuietly(outputStream);
}
public HSSFCell getCell(HSSFSheet sheet, int row, int col,
HSSFCellStyle style, String text) {
HSSFCell cell = getCell(sheet, row, col);
cell.setCellStyle(style);
setText(cell, text);
return cell;
}
private void processBaseStyle(HSSFCellStyle style) {
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
}
private void processBaseFont(HSSFFont font) {
font.setFontName("宋体");
}
private HSSFFont getTableTitleFont(HSSFWorkbook workbook) {
HSSFFont font = workbook.createFont();
font.setFontHeightInPoints((short) 14);
processBaseFont(font);
return font;
}
private HSSFCellStyle getTableTitle(HSSFWorkbook workbook) {
HSSFCellStyle style = workbook.createCellStyle();
processBaseStyle(style);
style.setFont(getTableTitleFont(workbook));
return style;
}
}
Controller层
@RequestMapping(value = "/export", method = RequestMethod.GET)
public ModelAndView export(HttpServletRequest request,
HttpServletResponse response, ModelMap modelMap) throws IOException {
String name = StringUtils.trimToEmpty(request.getParameter("name"));
String type = StringUtils.trimToEmpty(request.getParameter("type"));
String startTime = StringUtils.trimToEmpty(request
.getParameter("startTime"));
String endTime = StringUtils.trimToEmpty(request
.getParameter("endTime"));
PageVo page = this.processPageVo(request,
baseListUrl + "?" + WebUtils.createQrmallCashParams(request));
Map<String, String> mapParams = new HashMap<String, String>();
mapParams.put("name", name);
mapParams.put("type", type);
mapParams.put("startTime", startTime);
mapParams.put("endTime", endTime);
List<QrmallCash> items = new ArrayList<QrmallCash>();
page.setTotalPageCount(1);
while (page.getCurrPageNum() <= page.getTotalPageCount()) {
List<QrmallCash> temp = qrmallCashService.list(page, mapParams);
for (QrmallCash qrmallCash : temp) {
User wxUser = wxUserService.get(qrmallCash.getFromid());
processUser(wxUser);
qrmallCash.setUser(wxUser);
}
items.addAll(temp);
page.setCurrPageNum(page.getCurrPageNum() + 1);
}
modelMap.put("items", items);//根据条件查询数据 放入ModelMap中
QrmallCashExcelView view = new QrmallCashExcelView();
return new ModelAndView(view, modelMap);
}
jsp页面:
<script type="text/javascript">
$('#export').bind('click',function(){
location.href= "${basePath}qrmallCash/export?" + $("form").serialize();
});
</script>
$(“form”).serialize()获取表单中input的值