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、第二步
String path = request.getSession().getServletContext().getRealPath(File.separator) + "/activity/downloadFile/";
String returnExclePath = "";//需要返回的路径
String downloadExcleFileName = "";//需要下载的Excle的名称
HSSFWorkbook workbook = new HSSFWorkbook();
/**需要往Excle表中写入的数据*/
List<Map<String, Object>> activityPrizeList = activityManagerDao.getActivityPrizeList(actId);
workbook = exportExcel(activityPrizeList);
downloadExcleFileName = "奖品清单";//需要下载的Excle的名称
OutputStream out = null;
File file1 = new File(path);
//如果文件夹不存在则创建
if (!file1.exists()) {
file1.mkdir();
}
String fileName = downloadExcleFileName + ".xls";
File file = new File(path + fileName);
if (file.exists()) {
file.delete();
}
out = new FileOutputStream(path + fileName); // 输出目的地
workbook.write(out);
returnExclePath = "/activity/downloadFile/" + fileName;//返回值为returnExclePath
3、第三步
/**给Excle中填充数据*/
private HSSFWorkbook exportExcel(List dataList) throws Exception {
HSSFWorkbook workbook = null;
try {
workbook = new HSSFWorkbook();
//创建工作表实例
HSSFSheet sheet = workbook.createSheet("sheet1");
//设置列宽
this.setSheetColumnWidth(sheet);
//获取样式
HSSFCellStyle style = this.createTitleStyle(workbook);
if (dataList != null && dataList.size() > 0) {
//创建表头
HSSFRow row = sheet.createRow((short) 0);
this.createCell(row, 0, style, HSSFCell.CELL_TYPE_STRING, "序号");
this.createCell(row, 1, style, HSSFCell.CELL_TYPE_STRING, "活动名称");
this.createCell(row, 2, style, HSSFCell.CELL_TYPE_STRING, "奖品名称");
this.createCell(row, 3, style, HSSFCell.CELL_TYPE_STRING, "奖品规格");
this.createCell(row, 4, style, HSSFCell.CELL_TYPE_STRING, "奖品类型");
this.createCell(row, 5, style, HSSFCell.CELL_TYPE_STRING, "总数量");
this.createCell(row, 6, style, HSSFCell.CELL_TYPE_STRING, "已中奖数");
//给excel填充数据
for (int i = 0; i < dataList.size(); i++) {
//填充数据
Map<String, String> data = (Map<String, String>) dataList.get(i);
//创建新行
HSSFRow row1 = sheet.createRow((short) (i + 1));
this.createCell(row1, 0, style, HSSFCell.CELL_TYPE_STRING, i + 1);
if (data.get("act_name") != null) {//库存活动名称
this.createCell(row1, 1, style, HSSFCell.CELL_TYPE_STRING, data.get("act_name"));
}
if (data.get("prize_name") != null) {//奖品名称
this.createCell(row1, 2, style, HSSFCell.CELL_TYPE_STRING, data.get("prize_name"));
}
if (data.get("stock_prize_model") != null) {//奖品规格
this.createCell(row1, 3, style, HSSFCell.CELL_TYPE_STRING, data.get("stock_prize_model"));
}
if (data.get("prize_type") != null) {//奖品类型
String prizeType = "";//奖品类型
switch ( data.get("prize_type") ) {
case "1":
prizeType = "电子卷";
break;
case "2":
prizeType = "实物";
break;
case "3":
prizeType = "谢谢参与";
break;
}
this.createCell(row1, 4, style, HSSFCell.CELL_TYPE_STRING, prizeType);
}
if (data.get("prize_count") != null) {//总数量
this.createCell(row1, 5, style, HSSFCell.CELL_TYPE_STRING, data.get("prize_count"));
}
if (data.get("prize_already_count") != null) {//已中奖数
this.createCell(row1, 6, style, HSSFCell.CELL_TYPE_STRING, data.get("prize_already_count"));
}
}
} else {
this.createCell(sheet.createRow(0), 0, style, HSSFCell.CELL_TYPE_STRING, "查无资料");
}
} catch (Exception e) {
e.printStackTrace();
}
return workbook;
}
4、第四步
/**设置列宽与列数*/
private void setSheetColumnWidth(HSSFSheet sheet,Integer type) {
// 根据你数据里面的记录有多少列,就设置多少列
sheet.setColumnWidth(0, 1000);
sheet.setColumnWidth(1, 6000);
sheet.setColumnWidth(2, 6000);
sheet.setColumnWidth(3, 6000);
sheet.setColumnWidth(4, 8000);
sheet.setColumnWidth(5, 6000);
sheet.setColumnWidth(6, 5000);
}
5、第五步
/** 设置excel的title样式*/
private HSSFCellStyle createTitleStyle(HSSFWorkbook wb) {
HSSFFont boldFont = wb.createFont();
boldFont.setFontHeight((short) 200);
HSSFCellStyle style = wb.createCellStyle();
style.setFont(boldFont);
style.setDataFormat(HSSFDataFormat.getBuiltinFormat("###,##0.00"));
return style;
}
6、第六步
/** 创建Excel单元格*/
private void createCell(HSSFRow row, int column, HSSFCellStyle style, int cellType, Object value) {
HSSFCell cell = row.createCell(column);
if (style != null) {
cell.setCellStyle(style);
}
switch (cellType) {
case HSSFCell.CELL_TYPE_BLANK: {
}
break;
case HSSFCell.CELL_TYPE_STRING: {
cell.setCellValue(value.toString());
}
break;
case HSSFCell.CELL_TYPE_NUMERIC: {
cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
cell.setCellValue(Double.parseDouble(value.toString()));
}
break;
default:
break;
}
}
以上代码为自己在工作中亲自编写