1、在pom.xml文件中添加maven依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.9</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.9</version>
</dependency>
2、前端代码
<script src="../../js/jquery-1.11.1.min.js"></script>
<script src="../../js/bootstrap.min.js"></script>
<script src="../../js/bootstrap-modal.js"></script>
<button type="button" class="btn btn-primary" onclick="stockPrizeExport()">库存导出</button>
/**导出库存清单*/
function stockPrizeExport(){
debugger;
var index = layer.confirm('确定要导出现有库存奖品吗?', {
btn: ['确定','取消'],title: "提示" //按钮
}, function(){
$("#global_body").mask("数据正在下载,请耐心等待...");
$.ajax({
url: '/activityList/stockPrizeExport.do',
type: 'post',
async: true,
dataType : "json",
error : function(data) {
$("#global_body").unmask();
layer.alert("库存奖品导出异常!");
},
success : function(data) {
debugger;
$("#global_body").unmask();
if(data.resultCode==200){
window.location.href = data.path;
}
layer.close(index);
}
});
});
}
3、后台代码
String filePath = request.getSession().getServletContext().getRealPath(File.separator) + "/excleFile/stockListExport/";
/**获取需要下载的Excle文件的名称*/
String fileName = activityManagerService.getDownloadExcleFilePath(filePath);
String path = "/excleFile/stockListExport/" + fileName;
/**
* 获取需要下载文件名称
*
* @param parameterMap
* @return
*/
@Override
@Transactional
public String getDownloadExcleFilePath(String filePath) throws Exception {
String returnExclePath = "";//需要返回的文件名称
String downloadExcleFileName = "";//需要下载的Excle的名称
XSSFWorkbook workbook = new XSSFWorkbook();
/**库存奖品清单*/
List<Map<String, Object>> stockPrizeList = activityManagerDao.stockPrizeExport();
workbook = exportExcel(stockPrizeList);
downloadExcleFileName = "库存奖品清单";//需要导出的Excle的名称
OutputStream out = null;
File file1 = new File(filePath);
//如果文件夹不存在则创建
if (!file1.exists()) {
file1.mkdir();
}
String fileName = downloadExcleFileName + ".xlsx";
File file = new File(filePath + fileName);
if (file.exists()) {
file.delete();
}
out = new FileOutputStream(filePath + fileName); // 输出目的地
workbook.write(out);
returnExclePath = fileName;
return returnExclePath;
}
/**
* 给Excle中填充数据
*
* @param dataList
* @return
* @throws Exception
*/
private XSSFWorkbook exportExcel(List dataList, Integer type) throws Exception {
XSSFWorkbook workbook = null;
try {
workbook = new XSSFWorkbook();
//创建工作表实例
XSSFSheet sheet = workbook.createSheet("sheet1");
//设置列宽
this.setSheetColumnWidth(sheet, type);
//获取样式
XSSFCellStyle style = this.createTitleStyle(workbook);
if (dataList != null && dataList.size() > 0) {
//创建表头
XSSFRow row = sheet.createRow((short) 0);
this.createCell(row, 0, style, XSSFCell.CELL_TYPE_STRING, "序号");
this.createCell(row, 1, style, XSSFCell.CELL_TYPE_STRING, "奖品名称");
this.createCell(row, 2, style, XSSFCell.CELL_TYPE_STRING, "奖品规格");
this.createCell(row, 3, style, XSSFCell.CELL_TYPE_STRING, "剩余件数");
this.createCell(row, 4, style, XSSFCell.CELL_TYPE_STRING, "奖品类型");
this.createCell(row, 5, style, XSSFCell.CELL_TYPE_STRING, "库存导入人");
this.createCell(row, 6, style, XSSFCell.CELL_TYPE_STRING, "库存导入时间");
this.createCell(row, 7, style, XSSFCell.CELL_TYPE_STRING, "库存奖品状态");
//给excel填充数据
for (int i = 0; i < dataList.size(); i++) {
//填充数据
Map<String, String> data = (Map<String, String>) dataList.get(i);
//创建新行
XSSFRow row1 = sheet.createRow((short) (i + 1));
this.createCell(row1, 0, style, XSSFCell.CELL_TYPE_STRING, i + 1);
if (data.get("stock_prize_name") != null) {//奖品名称
this.createCell(row1, 1, style, XSSFCell.CELL_TYPE_STRING, data.get("stock_prize_name"));
}
if (data.get("stock_prize_model") != null) {//奖品规格
this.createCell(row1, 2, style, XSSFCell.CELL_TYPE_STRING, data.get("stock_prize_model"));
}
if (data.get("stock_overplus_count") != null) {//剩余件数
this.createCell(row1, 3, style, XSSFCell.CELL_TYPE_STRING, data.get("stock_overplus_count"));
}
if (data.get("stock_prize_type") != null) {//奖品类型
String stockPrizeType = "";//奖品类型
switch (data.get("stock_prize_type")) {
case "1":
stockPrizeType = "电子卷";
break;
case "2":
stockPrizeType = "实物";
break;
}
this.createCell(row1, 4, style, XSSFCell.CELL_TYPE_STRING, stockPrizeType);
}
if (data.get("creation_by") != null) {//库存导入人
this.createCell(row1, 5, style, XSSFCell.CELL_TYPE_STRING, data.get("creation_by"));
}
if (data.get("creation_date") != null) {//库存导入时间
this.createCell(row1, 6, style, XSSFCell.CELL_TYPE_STRING, data.get("creation_date"));
}
if (data.get("stock_prize_status") != null) {//库存奖品状态
String stockPrizeStatus = "";//库存奖品状态
switch (data.get("stock_prize_status")) {
case "0":
stockPrizeStatus = "禁用";
break;
case "1":
stockPrizeStatus = "启用";
break;
}
this.createCell(row1, 7, style, XSSFCell.CELL_TYPE_STRING, stockPrizeStatus);
}
}
} else {
this.createCell(sheet.createRow(0), 0, style, XSSFCell.CELL_TYPE_STRING, "查无资料");
}
} catch (Exception e) {
e.printStackTrace();
}
return workbook;
}
/**
* 设置列宽与列数
*
* @param sheet
*/
private void setSheetColumnWidth(XSSFSheet sheet, Integer type) throws Exception {
// 根据你数据里面的记录有多少列,就设置多少列
sheet.setColumnWidth(0, 1000);
sheet.setColumnWidth(1, 4000);
sheet.setColumnWidth(2, 4000);
sheet.setColumnWidth(3, 2000);
sheet.setColumnWidth(4, 2000);
sheet.setColumnWidth(5, 3000);
sheet.setColumnWidth(6, 6000);
sheet.setColumnWidth(7, 3000);
}
/**
* 设置excel的title样式
*
* @param wb
* @return
*/
private XSSFCellStyle createTitleStyle(XSSFWorkbook wb) throws Exception {
XSSFFont boldFont = wb.createFont();
boldFont.setFontHeight((short) 200);
XSSFCellStyle style = wb.createCellStyle();
style.setFont(boldFont);
style.setDataFormat(HSSFDataFormat.getBuiltinFormat("###,##0.00"));
return style;
}
/**
* 创建Excel单元格
*
* @param row
* @param column
* @param style
* @param cellType
* @param value
*/
private void createCell(XSSFRow row, int column, XSSFCellStyle style, int cellType, Object value) throws Exception {
XSSFCell cell = row.createCell(column);
if (style != null) {
cell.setCellStyle(style);
}
switch (cellType) {
case XSSFCell.CELL_TYPE_BLANK: {
}
break;
case XSSFCell.CELL_TYPE_STRING: {
cell.setCellValue(value.toString());
}
break;
case XSSFCell.CELL_TYPE_NUMERIC: {
cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC);
cell.setCellValue(Double.parseDouble(value.toString()));
}
break;
default:
break;
}
}