java中.xls格式的Excel下载

1、第一步在pom.xml文件中添加maven依赖

org.apache.poipoi3.9

org.apache.poipoi-ooxml3.9

2、第二步

String path = request.getSession().getServletContext().getRealPath(File.separator) + "/activity/downloadFile/";
String returnExclePath = "";//需要返回的路径
String downloadExcleFileName = "";//需要下载的Excle的名称
HSSFWorkbook workbook = new HSSFWorkbook();
/**需要往Excle表中写入的数据*/
List> 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 data = (Map) 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;}
}

以上代码为自己在工作中亲自编写

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！