引入的jar包
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-scratchpad</artifactId>
<version>3.11-beta2</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.11-beta2</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>3.11-beta2</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-excelant</artifactId>
<version>3.11-beta2</version>
</dependency>
第一种方法如下,这种方法很清晰,明了,使用简单,推荐使用第一种
@RequestMapping(value = "/exportOrderCar", method = { RequestMethod.GET, RequestMethod.POST })
@ResponseBody
public BaseRes<Map<String, Object>> exportOrderCar(HttpServletRequest request, HttpServletResponse response,
OrderParam orderParam) {
SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
DecimalFormat dataf = new DecimalFormat("#0.0000"); // 计算结果取四位小数
try {
//获取列表查询的对象list,这就是我们要导出的数据
Page<OrderVo> page = orderService.findOrderPage(orderParam);
List<OrderVo> orderList = page.getRecords();
if (orderList != null && orderList.size() > 0) {
// 2.4 设置表格
String sheetName = "汽车发货通知单表";// 设置sheet名称
//设置列标题,列标题要和相应对象的字段位置对应
String[] excelHeader = { "订单号", "合同编号", "客户名称", "货物类型", "重量(吨)", "运费(元)", "收货地址", "承运商", "车牌号", "司机名称",
"司机电话", "提货日期", "创建日期", "备注" };// 填充表头
HSSFCellStyle style = Export2ExcelUtil.createStyle(workbook, 0);
HSSFCellStyle leftStyle = Export2ExcelUtil.createStyle(workbook, 2);
HSSFCellStyle[] styleList = new HSSFCellStyle[] { style, style, style, style, style, style, style,
style, style, leftStyle, style, leftStyle, style, style };// 设置表格样式
//这里设置导出的每个列的显示长度,和上面的类标题要一一对应
int[] widthList = new int[] { 2000, 5000, 2800, 2800, 2800, 3760, 5000, 3760, 3760, 5000, 2800, 10000,
2800, 2800 };// 设置表格列宽
Map<Integer, Object[]> dataMap = new HashMap<Integer, Object[]>();
// 这里是用来计算某一列需要计算的总数,如果不需要,可以删除
double totalWayFee = 0d;
// 计算总共有多少列数据
int totalNum = 0;
// 2.5 填充数据
if (orderList != null && !orderList.isEmpty()) {
totalNum = orderList.size();
for (int i = 0; i < orderList.size(); i++) {
Object[] obj = new Object[excelHeader.length];
OrderVo vo = orderList.get(i);
int j = 0;
obj[j++] = vo.getOrderNo() != null ? vo.getOrderNo() : "";
obj[j++] = vo.getContractNo() != null ? vo.getContractNo() : "";
obj[j++] = vo.getCustomerName() != null ? vo.getCustomerName() : "";
obj[j++] = vo.getProductType() != null ? vo.getProductType() : "";
obj[j++] = dataf.format(vo.getWeightAll());
obj[j++] = dataf.format(vo.getWayFeeAll());
obj[j++] = vo.getDeliveryAddress() != null ? vo.getDeliveryAddress() : "";
obj[j++] = vo.getCarrierName() != null ? vo.getCarrierName() : "";
obj[j++] = vo.getCarNo() != null ? vo.getCarNo() : "";
obj[j++] = vo.getDriveName() != null ? vo.getDriveName() : "";
obj[j++] = vo.getDriveMobile() != null ? vo.getDriveMobile() : "";
obj[j++] = vo.getPickUpTime() != null ? df.format(vo.getPickUpTime()) : "";
obj[j++] = vo.getCreateTime() != null ? df.format(vo.getCreateTime()) : "";
obj[j++] = vo.getRemark() != null ? vo.getRemark() : "";
totalWayFee = totalWayFee + Double.valueOf(dataf.format(vo.getWayFeeAll()));
dataMap.put(i, obj);
// 计算合计数量
// totalWeight = totalWeight + groWeiVal;
// totalWeight = new Double(dataf.format(totalWeight));
// totalVolume = totalVolume + groVolVal;
// totalVolume = new Double(dataf.format(totalVolume));
// totalPcsQuantity = totalPcsQuantity + quaPcsVal;
// totalScanQuantity = totalScanQuantity + scanQuantity;
}
}
//最后一行添加的参数,一般是合计相关的信息
Object[] totalDataList = new Object[] { "合计", totalNum, null, null, null, totalWayFee, null, null, null,
null, null, null, null, null };
// 2.6 生成EXCEL
workbook = Export2ExcelUtil.createWorkbook(workbook, sheetName, excelHeader, dataMap, totalDataList,
styleList, widthList);
// 2.7 输出EXCEL
SimpleDateFormat ddf = new SimpleDateFormat("yyyy-MM-dd");
String today = ddf.format(new Date());
String fileName = "OrderCar" + today + ".xls";
response.setContentType("application/vnd.ms-excel;charset=utf-8");
response.setHeader("Content-disposition",
"attachment;filename=" + new String(fileName.getBytes("UTF-8"), "iso8859-1"));
outputStream = response.getOutputStream();
workbook.write(outputStream);
outputStream.flush();
return new BaseRes<Map<String, Object>>(BaseRes.CODE_SUCCESS, "导出成功", null);
} else {
return new BaseRes<Map<String, Object>>(BaseRes.CODE_PARAM_ERROR, "导出失败", null);
}
} catch (Exception e) {
logger.error("WEB导出汽车发货通知单-->导出失败!", e);
return new BaseRes<Map<String, Object>>(BaseRes.CODE_PARAM_ERROR, "导出失败!", null);
} finally {
if (outputStream != null) {
try {
workbook.close();
outputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
第二种如下,个人感觉不是很清晰
@RequestMapping("/exportExcel")
public void exportExcel(HttpServletRequest request, HttpServletResponse response) throws IOException {
List<SysUserVo> list = null;
//获取查询参数
String ids = request.getParameter("ids");
// String idFlag = request.getParameter("idFlag");
String account = request.getParameter("account");
String name = request.getParameter("name");
String status = request.getParameter("status");
String orgId = request.getParameter("orgId");
String showAllFlag = request.getParameter("showAllFlag");
if (StringUtils.isBlank(ids)) {
// 导出符合条件的所有
// 查询 数据不分页
list = sysUserService.geUserVoList(account, name, status, orgId, showAllFlag);
} else {
// 导出勾选的
// 根据id 查询
list = sysUserService.getUserVoListByIds(ids);
}
response.setContentType("application/vnd.ms-excel");
String exportFileName = null;
OutputStream fOut = null;
try {
// 进行转码,使其支持中文文件名
exportFileName = RequestUtils.getDownFileNameByBrower(request, "用户信息表");
response.setHeader("content-disposition", "attachment;filename=" + exportFileName + ".xls");
// 产生工作簿对象
HSSFWorkbook workbook = new HSSFWorkbook();
// 产生工作表对象
HSSFSheet sheet = workbook.createSheet();
sheet.setDefaultColumnWidth(15);
HSSFCellStyle headerStyle = (HSSFCellStyle) workbook.createCellStyle();// 创建标题样式
// headerStyle.setFillBackgroundColor(HSSFColor.GREY_25_PERCENT.index);// 设置背景色
// headerStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);// 设置前景色
// headerStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
// headerStyle.setDataFormat(HSSFDataFormat.);
headerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 设置垂直居中
HSSFFont headerFont = (HSSFFont) workbook.createFont(); // 创建字体样式
headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 字体加粗
headerFont.setFontName("宋体"); // 设置字体类型
headerFont.setFontHeightInPoints((short) 10); // 设置字体大小
headerStyle.setFont(headerFont); // 为标题样式设置字体样式
headerStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框
headerStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
// cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框
headerStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框
// 定义居左对齐方式
CellStyle cellStyle = workbook.createCellStyle();
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_BOTTOM);
HSSFFont headerFont2 = (HSSFFont) workbook.createFont(); // 创建字体样式
headerFont2.setFontName("宋体"); // 设置字体类型
headerFont2.setFontHeightInPoints((short) 12); // 设置字体大小
cellStyle.setFont(headerFont2); // 为标题样式设置字体样式
cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框
cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框
cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框
// 生成excel表头
HSSFRow headerRow = sheet.createRow(0);// 创建首行
headerRow.setHeightInPoints(48);
createCell(headerRow, 0, headerStyle, "登录账号");
createCell(headerRow, 1, headerStyle, "员工姓名");
createCell(headerRow, 2, headerStyle, "性别(填男或女)");
createCell(headerRow, 3, headerStyle, "手机号码");
createCell(headerRow, 4, headerStyle, "固定电话");
createCell(headerRow, 5, headerStyle, "邮箱地址");
createCell(headerRow, 6, headerStyle, "所属机构");
int rowNum = 0;
for (SysUserVo sysUserVo : list) {
rowNum++;
HSSFRow row = sheet.createRow(rowNum);// 创建一行
createCell(row, 0, cellStyle, sysUserVo.getAccount());
createCell(row, 1, cellStyle, sysUserVo.getName());
createCell(row, 2, cellStyle, sysUserVo.getSex());
createCell(row, 3, cellStyle, sysUserVo.getMobile());
createCell(row, 4, cellStyle, sysUserVo.getPhone());
createCell(row, 5, cellStyle, sysUserVo.getEmail());
createCell(row, 6, cellStyle, sysUserVo.getOrgName());
}
fOut = response.getOutputStream();
workbook.write(fOut);
} catch (Exception e) {
e.printStackTrace();
} finally {
fOut.flush();
fOut.close();
}
System.out.println("文件生成...");
}
public void createCell(HSSFRow row, int cellNum, CellStyle cellStyle, String cellValue) {
HSSFCell idCell = row.createCell(cellNum);
idCell.setCellStyle(cellStyle);
if (StringUtils.isNotBlank(cellValue))
idCell.setCellValue(cellValue);
}