wbook.write(); // 写入文件
wbook.close();
os.close();
return "success";
}
}
public class ExcelBean {
public String expordExcel(OutputStream os, List courseList,List studentList)
throws Exception {
WritableWorkbook wbook = Workbook.createWorkbook(os); // 建立excel文件
String tmptitle = "课程“"+((Course_info)courseList.get(0)).getCource_name()+"”的选课学生列表"; // 标题
WritableSheet wsheet = wbook.createSheet("第一页", 0); // sheet名称
// 设置excel标题
WritableFont wfont = new WritableFont(WritableFont.ARIAL, 16,
WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE,
Colour.BLACK);
WritableCellFormat wcfFC = new WritableCellFormat(wfont);
wsheet.addCell(new Label(1, 0, tmptitle, wcfFC));
wfont = new jxl.write.WritableFont(WritableFont.ARIAL, 14,
WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE,
Colour.BLACK);
wcfFC = new WritableCellFormat(wfont);
// 开始生成主体内容
wsheet.addCell(new Label(0, 2, "课程名称"));
wsheet.addCell(new Label(1, 2, "学 号"));
.........
for(int i=3;i
{
wsheet.addCell(new Label(0, i, ((Course_info)courseList.get(0)).getCource_name()));
wsheet.addCell(new Label(1, i, ((Student_info)studentList.get(0)).getStudentID()));
...........
}
// 主体内容生成结束
wbook.write(); // 写入文件
wbook.close();
os.close();
return "success";
}
}
控制器：
Java代码
public class EExcelDownController extends AbstractController {
private ICourse_infoManage courseManage;
public void setCourseManage(ICourse_infoManage courseManage) {
this.courseManage = courseManage;
}
@Override
protected ModelAndView handleRequestInternal(HttpServletRequest request,
HttpServletResponse response) throws Exception {
Integer course_id=new Integer(request.getParameter("course_id"));
List courseList=this.courseManage.getCourseById(course_id);
List studentList = this.courseManage.getStudentByCourseId(course_id);
try {
OutputStream os = response.getOutputStream();// 取得输出流
response.reset();// 清空输出流
response.setHeader("Content-disposition", "attachment; filename=student.xls");// 设定输出文件头
response.setContentType("application/msexcel");// 定义输出类型
ExcelBean excelBean = new ExcelBean();
excelBean.expordExcel(os,courseList,studentList);// 调用生成excel文件bean
} catch (Exception e) {
System.out.println(e);
}
return null;
}
}
将Excel文件内容写入到数据库
Java代码
public class EStudentInsertExcelController extends SimpleFormController {
private IStudent_infoManage studentManage;
@Override
protected ModelAndView onSubmit(HttpServletRequest request,
HttpServletResponse response, Object command, BindException errors)
throws Exception {
Student_info student_info = (Student_info) command;
try {
MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
MultipartFile file = multipartRequest.getFile( " Excelfile " ); // 获得文件：
File toFile = new File( " c:\\学生信息临时文件.xls " ); // 产生文件名和空文件
file.transferTo(toFile); // 文件上传
Workbook book = Workbook.getWorkbook(toFile); // 得到工作薄
Sheet sheet = book.getSheet( 0 ); // 获得第一个工作表对象
int row = sheet.getRows(); // /得到该sheet的行数
int column = sheet.getColumns(); // 得到该sheet的列数
System.out.println( " 数据行数= " + row);