使用 jxl 实现复杂的excel 表格导出 java代码
1. 使用 jxl 插件 jxl.jar
2. 导出excel 表格
3. java
4.myeclipse
5.mysql
6.ssm
7. 导出的效果
图中红色部分为从数据库中获取的动态数据。
8. 这是我们公司的项目，所以后台代码中我只粘贴出 excel 代码。如果不懂得可以联系我。
QQ:905352007
9. excel 代码段
public void exportCourseContent(HttpServletRequest request,HttpServletResponse response, List<Outline> list,
String course_name) {
WritableWorkbook wwb = null;
OutputStream os = null;
SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
try {
os = response.getOutputStream();// 取得输出流
response.reset();// 清空输出流
// excel 文件的 MIME 类型
response.setContentType("application/msexcel");
//在导出前对名称根据浏览器做下处理
String agent = request.getHeader("USER-AGENT").toLowerCase();
response.setContentType("application/vnd.ms-excel");
//***************很重要
String fileName = course_name;//文件名中文乱码
String codedFileName = java.net.URLEncoder.encode(fileName, "UTF-8");
if (agent.contains("firefox")) {
response.setCharacterEncoding("utf-8");
response.setHeader("content-disposition", "attachment;filename=" + new String(fileName.getBytes(), "ISO8859-1") + ".xls");
} else {
response.setHeader("content-disposition", "attachment;filename=" + codedFileName + ".xls");
}
wwb = jxl.Workbook.createWorkbook(os);
WritableSheet ws = wwb.createSheet("课程统计", 10); // 创建一个工作表
// 设置标题单元格的文字格式
WritableFont titleFont = new WritableFont(WritableFont.createFont("宋体"), 12,
WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE,Colour.BLACK);
WritableCellFormat titleFontFormat = new WritableCellFormat(titleFont);
titleFontFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
titleFontFormat.setAlignment(Alignment.CENTRE);
titleFontFormat.setBackground(Colour.LIGHT_TURQUOISE);
// 设置内容数据单元格的文字格式
WritableFont cellFont = new WritableFont(WritableFont.createFont("宋体"), 12,
WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE,Colour.BLACK);
WritableCellFormat cellFontFormat = new WritableCellFormat(cellFont);
cellFontFormat.setVerticalAlignment(VerticalAlignment.CENTRE);//垂直居中
cellFontFormat.setAlignment(Alignment.LEFT);//文字对齐方式
// 设置内容数据单元格的文字格式
WritableFont cellFont1 = new WritableFont(WritableFont.createFont("宋体"), 12,
WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE,Colour.BLACK);
WritableCellFormat cellFontFormat1 = new WritableCellFormat(cellFont1);
cellFontFormat1.setVerticalAlignment(VerticalAlignment.CENTRE);//垂直居中
cellFontFormat1.setAlignment(Alignment.CENTRE);//文字对齐方式
ws.getSettings().setDefaultColumnWidth(18);
ws.getSettings().setDefaultRowHeight(300);
CellView cellView = new CellView();
cellView.setAutosize(true); //设置自动大小
/*======= 设置列宽 =====*/
ws.setColumnView(0, 30);//根据内容自动设置列宽
ws.setColumnView(1, 30);
ws.setColumnView(2, 30);
ws.setColumnView(3, 24);
ws.setColumnView(4, 24);
ws.setColumnView(5, 24);
//ws.mergeCells(0, 0, 3, 0);//合并单元格 第一个参数从哪个列开始合并，第二个参数为第几行，第三个参数为到那一列结束，第四个参数为合并几行
/*======= 填充标题 =====*/
ws.addCell(new Label(0, 0, "章名", titleFontFormat)); // 第一个参数为: 第几列，第二个参数为: 第几行
ws.addCell(new Label(1, 0, "节名", titleFontFormat));
ws.addCell(new Label(2, 0, "单元名", titleFontFormat));
ws.addCell(new Label(3, 0, "单元序号", titleFontFormat));
ws.addCell(new Label(4, 0, "单元类型", titleFontFormat));
ws.addCell(new Label(5, 0, "视频时长", titleFontFormat));
/*======= 填充内容 =====*/
/** 循环章 */
Outline outline = null;
Outline childoutline = null;
CourseContent content = null;
List<Outline> childlist = null;
List<CourseContent> contentlist = null;
String dan="";
int oc_count = 0;
int occ_count = 0;
int content_count = 0;
int outline_content_count = 0;
dan="--";
//循环章
for (int i = 0; i < list.size(); i++) {
outline = list.get(i);
childlist = outline.getChild();
oc_count = content_count+1;
ws.addCell(new Label(0, content_count+1, "" + outline.getTitle(),cellFontFormat));// 章的内容
//循环节
for(int j = 0; j < childlist.size(); j++){
childoutline = childlist.get(j);
contentlist = childoutline.getContent();
occ_count = outline_content_count+1;
ws.addCell(new Label(1, outline_content_count+1, "" + childoutline.getTitle(),cellFontFormat));
//循环单元
if(contentlist==null||contentlist.size()<=0){
ws.addCell(new Label(2, outline_content_count+1,dan,cellFontFormat));
ws.addCell(new Label(3, outline_content_count+1,dan,cellFontFormat1));
ws.addCell(new Label(4, outline_content_count+1,dan,cellFontFormat1));
ws.addCell(new Label(5, outline_content_count+1,dan,cellFontFormat1));
content_count++;
outline_content_count++;
}else{
for(int k = 0; k < contentlist.size(); k++){
content = contentlist.get(k);
ws.addCell(new Label(2, content_count+1,content.getTitle(),cellFontFormat));
ws.addCell(new Label(3, content_count+1,content.getSort()+"",cellFontFormat1));
if(content.getType()!=null){
if(content.getType()==1){
ws.addCell(new Label(4, content_count+1,"视频",cellFontFormat1));
}else if(content.getType()==2){
ws.addCell(new Label(4, content_count+1,"文档",cellFontFormat1));
}else{
ws.addCell(new Label(4, content_count+1,"其他",cellFontFormat1));
}
}else{
ws.addCell(new Label(4, content_count+1,"暂无",cellFontFormat1));
}
if(content.getVideo_time()!=null){
ws.addCell(new Label(5, content_count+1,content.getVideo_time()+"",cellFontFormat1));
}else{
ws.addCell(new Label(5, content_count+1,0+"",cellFontFormat1));
}
content_count++;
outline_content_count++;
}
}
//合并节单元格
ws.mergeCells(1, occ_count, 1, outline_content_count);
}
//合并章单元格
ws.mergeCells(0, oc_count, 0, content_count);
}
ws.toString();
wwb.write();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (wwb != null) {
wwb.close();
}
if (os != null) {
os.close();
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
10. 当然了，这只是我想到的一种方法，如果有哪个大神有更简单的方法，欢迎在下方留言！
