导出带有多个标签页的Excel表格代码
来源:互联网 发布:防网络尖兵方法 编辑:程序博客网 时间:2024/06/06 17:24
控制层代码:
@RequestMapping(value = "/getFile")
public void getFile(String endTime, String beginTime, HttpServletResponse response) {
try {
//读取conf.properties文件
ResourceBundle bundle = ResourceBundle.getBundle("conf");
//读取conf.properties文件里的文件路径
String pathStr = bundle.getString("file.fileRootPath");
//设置相对路径加文件名
String filename = pathStr + File.separator + "上课报名统计" + ".xls";
//在内存中创建表格文件
File fileExists = new File(filename);
response.setContentType("application/octet-stream");
//设置响应头,控制浏览器下载该文件
response.addHeader("Content-Disposition", "attachment; filename=/" + new String(("上课报名统计数据信息.xsl").getBytes("GB2312"), "iso8859-1") + File.separator);
//读取要下载的文件,保存到文件输入流
FileInputStream in = new FileInputStream(fileExists);
//创建输出流
OutputStream out = response.getOutputStream();
//创建缓冲区
byte buffer[] = new byte[1024];
int len = 0;
//循环将输入流中的内容读取到缓冲区当中
while ((len = in.read(buffer)) > 0) {
//输出缓冲区的内容到浏览器,实现文件下载
out.write(buffer, 0, len);
}
//关闭文件输入流
in.close();
//关闭输出流
out.close();
} catch (Exception e) {
e.printStackTrace();
}
}
服务层代码(主要是查询装填表格使用的数据,创建出文件)
@Override
public void getList(String time) throws Exception {
ResourceBundle bundle = ResourceBundle.getBundle("conf");//读取properties文件
String pathStr = bundle.getString("file.fileRootPath");
String filename = pathStr + File.separator + time + ".xls";
File fileExists = new File(filename);
//获取现在的时间
String nowTime = DateUtil.formatDate(System.currentTimeMillis());
//判断下载的是现在的时间等于指定的时间,就生成下载。
if (time.equals(nowTime)) {
File file = new File(filename);
file.createNewFile();
long beginTime = (Long) DateUtil.getStartAndEndTime(time).get("startTime");
long endTime = (Long) DateUtil.getStartAndEndTime(time).get("endTime");
Map map = new HashMap();
//上课
List<ClassBeginEntity> beginList = this.dataStaDao.getClassBeginList(beginTime, endTime);
//推广
List<StaRecordEntity> recordList = this.dataStaDao.getStaRecordList(beginTime, endTime);
//报名
List<EntryRecordEntity> entryList = this.dataStaDao.getEntryRecordList(beginTime, endTime);
map.put("beginList", beginList);
map.put("recordList", recordList);
map.put("entryList", entryList);
GeneratingExcelTable.export_table(map, time, file);
}
//如果没有就生成下载。
if (!fileExists.exists()) {
File file = new File(filename);
file.createNewFile();
long beginTime = (Long) DateUtil.getStartAndEndTime(time).get("startTime");
long endTime = (Long) DateUtil.getStartAndEndTime(time).get("endTime");
Map map = new HashMap();
//上课
List<ClassBeginEntity> beginList = this.dataStaDao.getClassBeginList(beginTime, endTime);
//推广
List<StaRecordEntity> recordList = this.dataStaDao.getStaRecordList(beginTime, endTime);
//报名
List<EntryRecordEntity> entryList = this.dataStaDao.getEntryRecordList(beginTime, endTime);
map.put("beginList", beginList);
map.put("recordList", recordList);
map.put("entryList", entryList);
GeneratingExcelTable.export_table(map, time, file);
}
}
表格填充数据的类(主要就是这):
package com.sanhai.nep.managerService.util;
import com.sanhai.nep.managerService.entity.ClassBeginEntity;
import com.sanhai.nep.managerService.entity.EntryRecordEntity;
import com.sanhai.nep.managerService.entity.StaRecordEntity;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.write.*;
import java.io.File;
import java.util.*;
/**
* Created by 胥源博 on 2016/7/29.
*/
public class GeneratingExcelTable {
private static WritableCellFormat wcf_value; // 表格数据样式
private static WritableCellFormat wcf_value_left;
private static WritableCellFormat wcf_key; // 表头样式
private static WritableCellFormat wcf_name_left; // 表名样式
private static WritableCellFormat wcf_name_right; // 表名样式
private static WritableCellFormat wcf_name_center; // 表名样式
private static WritableCellFormat wcf_title; // 页名称样式
private static WritableCellFormat wcf_percent_float;
private static int maxLieShu = 12;
/**
* 向表格中填充准备好的数据信息
* 在这里是创建一个表格但是有三个标签页
*
* @param map 准备好的数据:有三个list集合数据
* @param time 时间 在这里当作文件名了
* @param file 在内存中创建好的文件对象
* @throws Exception
*/
public static void export_table(Map map, String time, File file) throws Exception {
// 生成Excel文件
/****** 定义表格格式start *****/
WritableFont wf_key = new jxl.write.WritableFont(WritableFont.createFont("微软雅黑"), 10, WritableFont.BOLD);
WritableFont wf_value = new jxl.write.WritableFont(WritableFont.createFont("微软雅黑"), 10, WritableFont.NO_BOLD);
//设置单元格样式
wcf_value = new WritableCellFormat(wf_value); //单元格字体样式
wcf_value.setAlignment(jxl.format.Alignment.CENTRE); //单元格水平对齐样式
wcf_value.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE); //单元格垂直对齐样式
wcf_value.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN); //单元格边框样式
wcf_value_left = new WritableCellFormat(wf_value);
wcf_value_left.setAlignment(jxl.format.Alignment.LEFT);
wcf_value_left.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
wcf_value_left.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
wcf_value_left.setWrap(true);
wcf_key = new WritableCellFormat(wf_key);
wcf_key.setAlignment(jxl.format.Alignment.CENTRE);
wcf_key.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
wcf_name_left = new WritableCellFormat(wf_key);
wcf_name_left.setAlignment(Alignment.LEFT);
wcf_name_left.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
wcf_name_right = new WritableCellFormat(wf_key);
wcf_name_right.setAlignment(Alignment.LEFT);
wcf_name_center = new WritableCellFormat(wf_key);
wcf_name_center.setAlignment(Alignment.CENTRE);
jxl.write.NumberFormat wf_percent_float = new jxl.write.NumberFormat("0.00"); //定义单元浮点数据类型
wcf_percent_float = new jxl.write.WritableCellFormat(wf_value, wf_percent_float);
wcf_percent_float.setAlignment(jxl.format.Alignment.CENTRE);
wcf_percent_float.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
wcf_percent_float.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
WritableFont wf_title = new jxl.write.WritableFont(WritableFont.createFont("微软雅黑"), 24, WritableFont.NO_BOLD); //定义标题样式
wcf_title = new WritableCellFormat(wf_title);
wcf_title.setAlignment(Alignment.CENTRE);
wcf_title.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
/****** 定义表格格式end *****/
//在指定的路径生成空白的xls文件
List beginList = (List) map.get("beginList");
List recordList = (List) map.get("recordList");
List entryList = (List) map.get("entryList");
Iterator beginList_it = beginList.iterator();
Iterator recordList_it = recordList.iterator();
Iterator entryList_it = entryList.iterator();
//上课记录工作薄
//首先要使用Workbook类的工厂方法创建一个可写入的工作薄(Workbook)对象
WritableWorkbook wb = Workbook.createWorkbook(file);
//设置Excel工作簿名称
WritableSheet ws = wb.createSheet("上课记录", 0);
int startRowNum = 0; // 起始行
int startColNum = 0; // 起始列
int maxColSize = maxLieShu; // 最大列数
// 设置列宽
ws.setColumnView(0, 35);
ws.setColumnView(1, 35);
ws.setColumnView(2, 35);
ws.setColumnView(3, 35);
ws.setColumnView(4, 35);
ws.setColumnView(5, 35);
ws.setColumnView(6, 35);
ws.setColumnView(7, 35);
ws.setColumnView(8, 35);
ws.setColumnView(9, 35);
ws.setColumnView(10, 35);
ws.setColumnView(11, 35);
ws.setColumnView(12, 35);
ws.addCell(new Label(startColNum, startRowNum, time + "数据信息", wcf_title));
ws.mergeCells(startColNum, startRowNum, startColNum + maxColSize - 1, startRowNum); //合并单元格,合并(1,0)到(1,9)
startColNum = 0;
startRowNum++;
//第1行,绘制表头
ws.addCell(new Label(startColNum, startRowNum, "用户ID", wcf_key));
startColNum++;
ws.addCell(new Label(startColNum, startRowNum, "系列课程标题", wcf_key));
startColNum++;
ws.addCell(new Label(startColNum, startRowNum, "课程ID", wcf_key));
startColNum++;
ws.addCell(new Label(startColNum, startRowNum, "用户角色(0:老师,10:一对一学生,20:旁听学生,30:试听学生)", wcf_key));
startColNum++;
ws.addCell(new Label(startColNum, startRowNum, "用户名字", wcf_key));
startColNum++;
ws.addCell(new Label(startColNum, startRowNum, "电话", wcf_key));
startColNum++;
ws.addCell(new Label(startColNum, startRowNum, "用户身份(0:老师,2:学生)", wcf_key));
startColNum++;
ws.addCell(new Label(startColNum, startRowNum, "地区", wcf_key));
startColNum++;
ws.addCell(new Label(startColNum, startRowNum, "学校", wcf_key));
startColNum++;
//将行数加1,列数重置为0
startRowNum++;
startColNum = 0;
//添加记录
while (beginList_it.hasNext()) {
ClassBeginEntity ar = (ClassBeginEntity) beginList_it.next();
ws.addCell(new Label(startColNum, startRowNum, ar.getUserId(), wcf_key));
startColNum++;
ws.addCell(new Label(startColNum, startRowNum, ar.getClassTitle(), wcf_key));
startColNum++;
ws.addCell(new Label(startColNum, startRowNum, ar.getCourseId(), wcf_key));
startColNum++;
ws.addCell(new Label(startColNum, startRowNum, ar.getUserRole(), wcf_key));
startColNum++;
ws.addCell(new Label(startColNum, startRowNum, ar.getUserName(), wcf_key));
startColNum++;
ws.addCell(new Label(startColNum, startRowNum, ar.getPhoneNumber(), wcf_key));
startColNum++;
ws.addCell(new Label(startColNum, startRowNum, ar.getUserIdEntity(), wcf_key));
startColNum++;
ws.addCell(new Label(startColNum, startRowNum, ar.getAreaName(), wcf_key));
startColNum++;
ws.addCell(new Label(startColNum, startRowNum, ar.getSchool(), wcf_key));
startColNum++;
//将行数加1,列数重置为0
startRowNum++;
startColNum = 0;
}
//设置Excel工作簿名称
WritableSheet ws_sta = wb.createSheet("推广记录", 1);
int startRowNum_sta = 0; // 起始行
int startColNum_sta = 0; // 起始列
// 设置列宽
ws_sta.setColumnView(0, 35);
ws_sta.setColumnView(1, 35);
ws_sta.setColumnView(2, 35);
ws_sta.setColumnView(3, 35);
ws_sta.setColumnView(4, 35);
ws_sta.setColumnView(5, 35);
ws_sta.setColumnView(6, 35);
ws_sta.setColumnView(7, 35);
ws_sta.setColumnView(8, 35);
ws_sta.setColumnView(9, 35);
ws_sta.setColumnView(10, 35);
ws_sta.setColumnView(11, 35);
ws_sta.setColumnView(12, 35);
ws_sta.addCell(new Label(startColNum_sta, startRowNum_sta, time + "数据信息", wcf_title));
ws_sta.mergeCells(startColNum_sta, startRowNum_sta, startColNum_sta + maxColSize - 1, startRowNum_sta); //合并单元格,合并(1,0)到(1,9)
startColNum_sta = 0;
startRowNum_sta++;
//第1行,绘制表头
ws_sta.addCell(new Label(startColNum_sta, startRowNum_sta, "旁听ID", wcf_key));
startColNum_sta++;
ws_sta.addCell(new Label(startColNum_sta, startRowNum_sta, "班海ID", wcf_key));
startColNum_sta++;
ws_sta.addCell(new Label(startColNum_sta, startRowNum_sta, "课海ID", wcf_key));
startColNum_sta++;
ws_sta.addCell(new Label(startColNum_sta, startRowNum_sta, "学生昵称", wcf_key));
startColNum_sta++;
ws_sta.addCell(new Label(startColNum_sta, startRowNum_sta, "课程标题", wcf_key));
startColNum_sta++;
ws_sta.addCell(new Label(startColNum_sta, startRowNum_sta, "课程主题", wcf_key));
startColNum_sta++;
ws_sta.addCell(new Label(startColNum_sta, startRowNum_sta, "上课时间", wcf_key));
startColNum_sta++;
ws_sta.addCell(new Label(startColNum_sta, startRowNum_sta, "推广时间", wcf_key));
startColNum_sta++;
ws_sta.addCell(new Label(startColNum_sta, startRowNum_sta, "学校", wcf_key));
startColNum_sta++;
ws_sta.addCell(new Label(startColNum_sta, startRowNum_sta, "学生信息", wcf_key));
startColNum_sta++;
ws_sta.addCell(new Label(startColNum_sta, startRowNum_sta, "学校ID", wcf_key));
startColNum_sta++;
//将行数加1,列数重置为0
startRowNum_sta++;
startColNum_sta = 0;
//添加记录
while (recordList_it.hasNext()) {
StaRecordEntity ar = (StaRecordEntity) recordList_it.next();
ws_sta.addCell(new Label(startColNum_sta, startRowNum_sta, ar.getPtId(), wcf_key));
startColNum_sta++;
ws_sta.addCell(new Label(startColNum_sta, startRowNum_sta, ar.getBhUserId(), wcf_key));
startColNum_sta++;
ws_sta.addCell(new Label(startColNum_sta, startRowNum_sta, ar.getKhUserId(), wcf_key));
startColNum_sta++;
ws_sta.addCell(new Label(startColNum_sta, startRowNum_sta, ar.getStuName(), wcf_key));
startColNum_sta++;
ws_sta.addCell(new Label(startColNum_sta, startRowNum_sta, ar.getPtTitle(), wcf_key));
startColNum_sta++;
ws_sta.addCell(new Label(startColNum_sta, startRowNum_sta, ar.getTheme(), wcf_key));
startColNum_sta++;
ws_sta.addCell(new Label(startColNum_sta, startRowNum_sta, ar.getStartClass(), wcf_key));
startColNum_sta++;
ws_sta.addCell(new Label(startColNum_sta, startRowNum_sta, ar.getTui(), wcf_key));
startColNum_sta++;
ws_sta.addCell(new Label(startColNum_sta, startRowNum_sta, ar.getSchoolNickName(), wcf_key));
startColNum_sta++;
ws_sta.addCell(new Label(startColNum_sta, startRowNum_sta, ar.getStuDetails(), wcf_key));
startColNum_sta++;
ws_sta.addCell(new Label(startColNum_sta, startRowNum_sta, ar.getSchoolId(), wcf_key));
startColNum_sta++;
//将行数加1,列数重置为0
startRowNum_sta++;
startColNum_sta = 0;
}
//购买工作簿
//设置Excel工作簿名称
WritableSheet ws_mai = wb.createSheet("购买记录", 2);
int startRowNum_mai = 0; // 起始行
int startColNum_mai = 0; // 起始列
// 设置列宽
ws_mai.setColumnView(0, 35);
ws_mai.setColumnView(1, 35);
ws_mai.setColumnView(2, 35);
ws_mai.setColumnView(3, 35);
ws_mai.setColumnView(4, 35);
ws_mai.setColumnView(5, 35);
ws_mai.setColumnView(6, 35);
ws_mai.setColumnView(7, 35);
ws_mai.setColumnView(8, 35);
ws_mai.setColumnView(9, 35);
ws_mai.setColumnView(10, 35);
ws_mai.setColumnView(11, 35);
ws_mai.setColumnView(12, 35);
ws_mai.addCell(new Label(startColNum_mai, startRowNum_mai, time + "数据信息", wcf_title));
ws_mai.mergeCells(startColNum_mai, startRowNum_mai, startColNum_mai + maxColSize - 1, startRowNum_mai); //合并单元格,合并(1,0)到(1,9)
startColNum_mai = 0;
startRowNum_mai++;
//第1行,绘制表头
ws_mai.addCell(new Label(startColNum_mai, startRowNum_mai, "用户ID", wcf_key));
startColNum_mai++;
ws_mai.addCell(new Label(startColNum_mai, startRowNum_mai, "用户昵称", wcf_key));
startColNum_mai++;
ws_mai.addCell(new Label(startColNum_mai, startRowNum_mai, "电话", wcf_key));
startColNum_mai++;
ws_mai.addCell(new Label(startColNum_mai, startRowNum_mai, "价格(元)", wcf_key));
startColNum_mai++;
ws_mai.addCell(new Label(startColNum_mai, startRowNum_mai, "系列课程标题名", wcf_key));
startColNum_mai++;
ws_mai.addCell(new Label(startColNum_mai, startRowNum_mai, "课程标题", wcf_key));
startColNum_mai++;
ws_mai.addCell(new Label(startColNum_mai, startRowNum_mai, "地区", wcf_key));
startColNum_mai++;
ws_mai.addCell(new Label(startColNum_mai, startRowNum_mai, "学校名", wcf_key));
startColNum_mai++;
ws_mai.addCell(new Label(startColNum_mai, startRowNum_mai, "开始时间", wcf_key));
startColNum_mai++;
//将行数加1,列数重置为0
startRowNum_mai++;
startColNum_mai = 0;
//添加记录
while (entryList_it.hasNext()) {
EntryRecordEntity ar = (EntryRecordEntity) entryList_it.next();
ws_mai.addCell(new Label(startColNum_mai, startRowNum_mai, ar.getUserId(), wcf_key));
startColNum_mai++;
ws_mai.addCell(new Label(startColNum_mai, startRowNum_mai, ar.getNickName(), wcf_key));
startColNum_mai++;
ws_mai.addCell(new Label(startColNum_mai, startRowNum_mai, ar.getPhoneNumber(), wcf_key));
startColNum_mai++;
ws_mai.addCell(new Label(startColNum_mai, startRowNum_mai, Long.valueOf(ar.getOrderActualPrice()) / 100 + "", wcf_key));
startColNum_mai++;
ws_mai.addCell(new Label(startColNum_mai, startRowNum_mai, ar.getCourseTitle(), wcf_key));
startColNum_mai++;
ws_mai.addCell(new Label(startColNum_mai, startRowNum_mai, ar.getCoursesName(), wcf_key));
startColNum_mai++;
ws_mai.addCell(new Label(startColNum_mai, startRowNum_mai, ar.getAreaName(), wcf_key));
startColNum_mai++;
ws_mai.addCell(new Label(startColNum_mai, startRowNum_mai, ar.getSchool(), wcf_key));
startColNum_mai++;
ws_mai.addCell(new Label(startColNum_mai, startRowNum_mai, ar.getCoursesBeginTimes(), wcf_key));
startColNum_mai++;
//将行数加1,列数重置为0
startRowNum_mai++;
startColNum_mai = 0;
}
//注意在这里要生成多个表格标签页就不能中间关闭流
wb.write(); //生成Excel工作簿
wb.close();
}
}
依赖的maven:
<!-- excle表格jar -->
<dependency>
<groupId>net.sourceforge.jexcelapi</groupId>
<artifactId>jxl</artifactId>
<version>2.6.10</version>
</dependency>
注意:在添加多个标签页的时候不能在中间关闭流,不然只会生成最后一个标签页
阅读全文
0 0
- 导出带有多个标签页的Excel表格代码
- 导出EXCEL表格的代码
- 导出多个表格到EXCEL或者ET的JavaScript代码
- 如何利用API导出带有页眉页脚的excel
- 从GridView控件以Excel表格导出代码的注释
- mysql导出成excel表格的php代码
- asp.net把数据导出到Excel表格的代码
- java写的excel表格数据导出代码
- 最简单实用的GridView导出到Excel表格代码
- 用POI实现导出Excel表格的后台java代码
- Excel表格导出,java代码,采用反射加注解的方式获得excel表格中的数据
- excel导出模板带有下拉的模板
- 导出的Excel带有下拉框
- SL 表格的Excel导出
- 简单的导出excel 表格
- Easyui excel表格的导出
- excel表格的导入导出
- java的导出Excel表格
- = 改为 in
- 网易面试题记录(题目来源-->牛客网)
- 64位驱动相对偏移的计算E8call
- 吴恩达关于dev / test sets的形象解释
- shiro配置
- 导出带有多个标签页的Excel表格代码
- 生成简单的Excel表格示例
- 【Shiro权限管理】15.Shiro授权流程分析
- WARN OgnlValueStack:68
- hashmap hash后得出下标的方法
- springMVC 简易运行流程
- Python基础
- LeetCode 442.Find All Duplicates in an Array
- 数据库事务特性和隔离级别