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);
}
}
此博客只是为了工作中再次需要 赋值黏贴所用。 疑问直接联系