mysql导出excel java代码_Java代码从Mysql数据库导出为excel文件

/**

* 将已选题信息导出为Excel文档

*

* @param mapping

* @param form

* @param request

* @param response

*

@return

* @throws Exception

*/

public ActionForward exExcel(ActionMapping mapping, ActionForm form,

HttpServletRequest request, HttpServletResponse response)

throws Exception {

Integer cid = Integer.valueOf(request.getParameter("cid"));

College col = collegeDao.findByID(cid);

List list = choicesDao.findByCIDCho(col);

String filename = list.get(0).getStudent().getGrade().getGname() + "级"

+ col.getCname();

response.setContentType("text/html;charset=GBK");

response.setContentType("application/xml");

response.setHeader("Content-Disposition", "attachment;filename="

+ new String(filename.getBytes(), "iso-8859-1") + ".xls");

HSSFWorkbook workBook = new HSSFWorkbook(); // 创建 一个excel文档对象

HSSFSheet sheet = workBook.createSheet(); // 创建一个工作薄对象

HSSFCell cell = null; // 声明单元格对象

sheet.setColumnWidth(0, 4000);

sheet.setColumnWidth(1, 10000);

sheet.setColumnWidth(2, 4000);

sheet.setColumnWidth(3, 4000);

sheet.setColumnWidth(4, 5000);

sheet.setColumnWidth(5, 5000);

sheet.setColumnWidth(6, 4000);

sheet.setColumnWidth(7, 4000);

sheet.setColumnWidth(8, 5000);

sheet.setColumnWidth(9, 5000);

HSSFCellStyle style = workBook.createCellStyle(); // 创建样式对象

style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

style.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION);// 水平居中

style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直居中

HSSFFont font = workBook.createFont();// 创建字体对象

font.setFontHeightInPoints((short) 12);

font.setFontName("黑体");// 设置字体

style.setFont(font);

HSSFCellStyle titleStyle = workBook.createCellStyle();// 创建表头样式对象

titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION);// 水平居中

titleStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直居中

HSSFFont titleFont = workBook.createFont();// 创建表头字体对象

titleFont.setFontHeightInPoints((short) 15);

titleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 设置粗体

titleFont.setFontName("黑体");// 设置字体

titleStyle.setFont(titleFont);

// 合并单元格操作

sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, 9));

sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, 9));

cell = sheet.createRow(0).createCell(0);

cell.setCellStyle(titleStyle);

cell.setCellValue(new HSSFRichTextString(list.get(0).getStudent()

.getGrade().getGname()

+ "级" + col.getCname() + "学院"));

HSSFRow row = sheet.createRow(2);

row.setHeightInPoints(20);

cell = row.createCell(0);

cell.setCellStyle(style);

cell.setCellValue(new HSSFRichTextString("毕业设计选题               "

+ "      选题总人数：" + list.size() + " 人"));

// 设置边框样式

HSSFCellStyle tableStyle = workBook.createCellStyle();

tableStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);

tableStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION);// 水平居中

tableStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直居中

HSSFFont tableFont = workBook.createFont();// 创建表格字体对象

tableFont.setFontHeightInPoints((short) 11);

tableFont.setFontName("宋体");// 设置字体

tableStyle.setFont(tableFont);

// 生成表头

String[] bt = new String[] { "选题编号", "选题名", "选题学生学号", "选题学生名",

"选题学生电话", "选题学生邮箱", "指导老师编号", "指导老师名", "指导老师电话", "指导老师邮箱" };

HSSFRow tableRow = sheet.createRow(3); // 创建一个行对象

tableRow.setHeightInPoints(18);

for (int i = 0; i < bt.length; i++) {

cell = tableRow.createCell(i);

cell.setCellStyle(tableStyle);

cell.setCellValue(new HSSFRichTextString(bt[i]));

}

// 写入表格内容

for (int i = 0; i < list.size(); i++) {

tableRow = sheet.createRow(i + 4); // 创建一个行对象

tableRow.setHeightInPoints(16);

Choices cho = list.get(i);

cell = tableRow.createCell(0);

cell.setCellStyle(tableStyle);

cell.setCellValue(new HSSFRichTextString(cho.getSubject()

.getSub_ID().toString()));

cell = tableRow.createCell(1);

cell.setCellStyle(tableStyle);

cell.setCellValue(new HSSFRichTextString(cho.getSubject()

.getSub_name()));

cell = tableRow.createCell(2);

cell.setCellStyle(tableStyle);

cell.setCellValue(new HSSFRichTextString(cho.getStudent().getUser()

.getUser_name()));

cell = tableRow.createCell(3);

cell.setCellStyle(tableStyle);

cell.setCellValue(new HSSFRichTextString(cho.getStudent()

.getStu_name()));

cell = tableRow.createCell(4);

cell.setCellStyle(tableStyle);

cell.setCellValue(new HSSFRichTextString(cho.getStudent()

.getStu_tel()));

cell = tableRow.createCell(5);

cell.setCellStyle(tableStyle);

cell.setCellValue(new HSSFRichTextString(cho.getStudent()

.getStu_mail()));

cell = tableRow.createCell(6);

cell.setCellStyle(tableStyle);

cell.setCellValue(new HSSFRichTextString(cho.getTeacher().getUser()

.getUser_name()));

cell = tableRow.createCell(7);

cell.setCellStyle(tableStyle);

cell.setCellValue(new HSSFRichTextString(cho.getTeacher()

.getTea_name()));

cell = tableRow.createCell(8);

cell.setCellStyle(tableStyle);

cell.setCellValue(new HSSFRichTextString(cho.getTeacher()

.getTea_tel()));

cell = tableRow.createCell(9);

cell.setCellStyle(tableStyle);

cell.setCellValue(new HSSFRichTextString(cho.getTeacher()

.getTea_mail()));

}

workBook.write(response.getOutputStream()); // 将文档对象写入文件输出流

return null;

}