Java使用poi导出数据到excel表格并下载1

Java使用poi导出数据到excel表格并下载1

	
	String path = "ExportToExcel"; //自己放常量的地方声明变量
			// 取得互联网程序的绝对地址
			String realPath = request.getSession().getServletContext().getRealPath("/");
			//方法在下面 . 参数自由定义
			HSSFWorkbook workBook = createResultTempBook("班级考试成绩模板", list);
			//生成文件  fileName 自己定义 传递给页面 或者方法 进行下载即可。
			OutputStream out = new FileOutputStream(realPath + File.separator + path + File.separator + fileName + ".xls");
            workBook.write(out);

发请求下载文件即可 。需要将服务器放文件地方的路径给过来。以及名字
public void exportResultTemplate() throws Exception {
		try {
			String fileName = request.getParameter("fileName");
			String path = Constant.EXPORT_EXCEL_PATH;
			// 取得互联网程序的绝对地址
			String realPath = request.getSession().getServletContext().getRealPath("/");
			String filePath = realPath + File.separator + path + File.separator + fileName + ".xls";
			File file = new File(filePath);
			// 以流的形式下载文件
            InputStream fis = new BufferedInputStream(new FileInputStream(filePath));
            byte[] buffer = new byte[fis.available()];
            fis.read(buffer);
            fis.close();
			// 清空response
            response.reset();
            // 设置response的Header
            response.setHeader("Content-Disposition", "attachment; filename=" + new String(("仅仅是个名字").getBytes("gb2312"), "iso8859-1")+".xls");
            response.setHeader("Content-Length", "" + file.length());
            response.setContentType("application/octet-stream;charset=utf-8");
            
            OutputStream toClient = new BufferedOutputStream(response.getOutputStream());
            toClient.write(buffer);
            toClient.flush();
            toClient.close();
            file.delete();
		} catch (IOException ex) {
			ex.printStackTrace();
		}
	}


public static HSSFWorkbook createResultTempBook(String sheetName, List<StudentVo> dataList, 
			String courseNames, String resultName, String resultType, String courses, String classId, String grade, String term) {
		HSSFWorkbook workbook = null;
		HSSFSheet sheet = null;
		HSSFRow row = null;
		try {
			workbook = new HSSFWorkbook();
			// 创建Excel的工作sheet,对应到一个excel文档的tab
			sheet = workbook.createSheet(sheetName);  想分sheet 就多创建几个方进去
			ExportExcelUtil exportExcel = new ExportExcelUtil(workbook, sheet);
			int firstNum = 0;
			int rowNum = 1;
		
			//exportExcel.createExcelRow(workbook, sheet, rowNum, 600, 10,  "列名名字", 400, "bold", "center");
			exportExcel.createExcelRow(workbook, sheet, firstNum, 500, studentInfoNum, null, 400, "bold", "center");
			exportExcel.createExcelRow(workbook, sheet, rowNum, 500, 1, null, 400, "bold", "center");
			HSSFCellStyle firstRowCellStyle = exportExcel.createCellFontStyle(workbook, 240, "bold", "center");
			firstRowCellStyle.setFillForegroundColor(HSSFColor.YELLOW.index);
			firstRowCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
			firstRowCellStyle.setFillBackgroundColor(HSSFColor.YELLOW.index);
			HSSFCellStyle cellStyleHead = exportExcel.createCellFontStyle(workbook, 240, "bold", "center");
			HSSFCellStyle cellStyle = exportExcel.createCellFontStyle(workbook, 200, "normal", "center");
			HSSFCellStyle noeditCellStyle = exportExcel.createCellFontStyle(workbook, 200, "normal", "center");
			noeditCellStyle.setFillForegroundColor(HSSFColor.YELLOW.index);
			noeditCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
			noeditCellStyle.setFillBackgroundColor(HSSFColor.YELLOW.index);
			
			String[] courseNms = courseNames.split(",");
			String[] courseIds = courses.split(",");
			int len = courseIds.length;
			// 取得规则  
			//int endVal = 8 + len * 2 - 1;
			int endVal = studentInfoNum + len - 1;
	        HSSFDataValidation validate = exportExcel.setDataValidation((short) 2, (short) dataList.size() + 1, (short) studentInfoNum, (short) endVal);  
	        // 设定规则  
	        validate.createErrorBox("输入值类型或大小有误", "数值型，请输入0~999之间的数值");
	        validate.setShowErrorBox(true);
			sheet.addValidationData(validate);
			
			row = sheet.getRow(rowNum);
			int headColNum = 0;
			exportExcel.createCell(workbook, sheet, row, headColNum++, "center", "列头", 3500, cellStyleHead);
			// 新增班级名称
			exportExcel.createCell(workbook, sheet, row, headColNum++, "center", "列头列头", 3500, cellStyleHead);
			//exportExcel.createCell(workbook, sheet, row, headColNum++, "center", "列头列头", 3500, cellStyleHead);
			exportExcel.createCell(workbook, sheet, row, headColNum++, "center", "列头列头列头", 3500, cellStyleHead);
			exportExcel.createCell(workbook, sheet, row, headColNum++, "center", "列头列头", 3500, cellStyleHead);
			exportExcel.createCell(workbook, sheet, row, headColNum++, "center", "列头", 3500, cellStyleHead);
			exportExcel.createCell(workbook, sheet, row, headColNum++, "center", "列头", 3500, cellStyleHead);
			exportExcel.createCell(workbook, sheet, row, headColNum++, "center", "列头列头", 3500, cellStyleHead);
			exportExcel.createCell(workbook, sheet, row, headColNum++, "center", "列头列头", 6800, cellStyleHead);
			
			HSSFRow firstRow = sheet.getRow(firstNum);
			HSSFCell cell = firstRow.getCell(0);
			cell.setCellValue("");
			cell.setCellStyle(firstRowCellStyle);
			
			for (int i = 0; i < courseNms.length; i++) {
				//exportExcel.createCell(workbook, sheet, row, headColNum++, "center", "", 3500, cellStyleHead);
				exportExcel.createCell(workbook, sheet, row, headColNum++, "center", courseNms[i], 5000, cellStyleHead);
		
				exportExcel.createCell(workbook, sheet, firstRow, headColNum - 1, "center", courseIds[i], 5000, firstRowCellStyle);
			}
			
			for (StudentVo student : dataList) {
				rowNum++;
				exportExcel.createExcelRowInFor(sheet, rowNum, 350, 1, null);
				row = sheet.getRow(rowNum);
				int colNum = 0;
				// 多个班级一起导出，班级代码从学生中获取
				exportExcel.createCell(workbook, sheet, row, colNum++, "center", student.getClassCode(), 3500, noeditCellStyle);
				//exportExcel.createCell(workbook, sheet, row, colNum++, "center", student.getId().toString(), 3500, noeditCellStyle);
				// 班级名称
				exportExcel.createCell(workbook, sheet, row, colNum++, "center", student.getClassName(), 3500, noeditCellStyle);
				exportExcel.createCell(workbook, sheet, row, colNum++, "center", student.getRecordCode(), 3500, noeditCellStyle);
				exportExcel.createCell(workbook, sheet, row, colNum++, "center", student.getName(), 3500, noeditCellStyle);
				exportExcel.createCell(workbook, sheet, row, colNum++, "center", grade, 3500, noeditCellStyle);
				exportExcel.createCell(workbook, sheet, row, colNum++, "center", term, 3500, noeditCellStyle);
				exportExcel.createCell(workbook, sheet, row, colNum++, "center", resultType, 3500, noeditCellStyle);
				exportExcel.createCell(workbook, sheet, row, colNum++, "center", resultName, 6800, noeditCellStyle);
				for (int i = 0; i < courseNms.length; i++) {
					//exportExcel.createCell(workbook, sheet, row, colNum++, "center", courseIds[i], 3500, noeditCellStyle);
					exportExcel.createCell(workbook, sheet, row, colNum++, "center", "", 5000, cellStyle);
				}
			}
	

			exportExcel.createSummaryRow(workbook, sheet, studentInfoNum, "*", 240, "bold", "right");
			return workbook;
		} catch (Exception e) {
			throw new ServiceException("！", e);
		}
	}

此博客只是为了工作中再次需要 赋值黏贴所用。 疑问直接联系