1. 报表
报表:简单的说,报表就是用表格、图表等格式来动态显示数据,可以用公式表示为:“报表 = 多样的格式 + 动态的数据”。
报表的种类有很多:Excel报表,PDF报表,网页报表等,他们各有优缺点
在本课程中,我们主要来将Excel报表。
对于Excel报表的技术实现上也有很多种选择:
- JXL:支持xls文件操作
- POI:支持xls和xlsx文件操作
我们只要来讲POI技术,要使用POI就要导入其坐标,如下
<!--POI-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.0.1</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.0.1</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>4.0.1</version>
</dependency>
1.1 POI写Excel文件
在测试包下创建POI测试类:com.itheima.service.store.PoiTest
public class PoiTest {
@Test
public void testWriteByPoi() throws IOException {
//1.获取到对应的Excel文件,工作簿文件
Workbook wb = new XSSFWorkbook();
//2.创建工作表
Sheet sheet = wb.createSheet();
wb.createSheet("这是啥呀");
//3.创建工作表中的行对象
Row row = sheet.createRow(1);
//4.创建工作表中行中的列对象
Cell cell = row.createCell(1);
//5.在列中写数据
cell.setCellValue("测试一下单元格");
//创建一个文件对象,作为excel文件内容的输出文件
File f = new File("test.xlsx");
//输出时通过流的形式对外输出,包装对应的目标文件
OutputStream os = new FileOutputStream(f);
//将内存中的workbook数据写入到流中
wb.write(os);
wb.close();
os.close();
}
}
使用单元测试进行测试!
1.2 POI读Excel文件
创建读Excel的测试方法:testReadByPoi
@Test
public void testReadByPoi() throws IOException {
//1.获取要读取的文件工作簿对象
Workbook wb = new XSSFWorkbook("test.xlsx");
//2.获取工作表
Sheet s = wb.getSheetAt(0);
//3.获取行
Row row = s.getRow(3);
//4.获取列
Cell cell = row.getCell(1);
//5.根据数据的类型获取数据
// String data = cell.getStringCellValue();
// double data = cell.getNumericCellValue();
boolean data = cell.getBooleanCellValue();
System.out.println(data);
wb.close();
}
直接读取第一节创建好的Excel文件
1.3 题目模板表头制作
前两节我们讲了如何去读取及写入Excel数据,操作相对简单,但是实际业务中我们要操作的Excel报表还是比较繁琐的,我们可以从今日课程资料中找到我们最终要导出报表的模板:资料\Excel解析\模板.xlsx
这种形式的我们如何去操作呢?
在测试类中再编写一个测试方法:testProjectPoi
@Test
public void testProjectPoi() throws IOException {
//1.获取到对应的Excel文件,工作簿文件
Workbook wb = new XSSFWorkbook();
//2.创建工作表
Sheet s = wb.createSheet("题目数据文件");
//制作标题
s.addMergedRegion(new CellRangeAddress(1,1,1,12));
Row row_1 = s.createRow(1);
Cell cell_1_1 = row_1.createCell(1);
cell_1_1.setCellValue("在线试题导出信息");
//创建一个样式
CellStyle cs_title = wb.createCellStyle();
cs_title.setAlignment(HorizontalAlignment.CENTER);
cs_title.setVerticalAlignment(VerticalAlignment.CENTER);
cell_1_1.setCellStyle(cs_title);
//制作表头
//制作数据区
//创建一个文件对象,作为excel文件内容的输出文件
File f = new File("test.xlsx");
//输出时通过流的形式对外输出,包装对应的目标文件
OutputStream os = new FileOutputStream(f);
//将内存中的workbook数据写入到流中
wb.write(os);
wb.close();
os.close();
}
1.4 题目模板标题制作
下面我们接着来做Excel的表头
在测试方法testProjectPoi
中继续编写代码
@Test
public void testProjectPoi() throws IOException {
//1.获取到对应的Excel文件,工作簿文件
Workbook wb = new XSSFWorkbook();
//2.创建工作表
Sheet s = wb.createSheet("题目数据文件");
//设置通用配置
// s.setColumnWidth(4,100);
//制作标题
s.addMergedRegion(new CellRangeAddress(1,1,1,12));
Row row_1 = s.createRow(1);
Cell cell_1_1 = row_1.createCell(1);
cell_1_1.setCellValue("在线试题导出信息");
//创建一个样式
CellStyle cs_title = wb.createCellStyle();
cs_title.setAlignment(HorizontalAlignment.CENTER);
cs_title.setVerticalAlignment(VerticalAlignment.CENTER);
cell_1_1.setCellStyle(cs_title);
//制作表头
String[] fields = {"题目ID","所属公司ID","所属目录ID","题目简介","题干描述",
"题干配图","题目分析","题目类型","题目难度","是否经典题","题目状态","审核状态"};
Row row_2 = s.createRow(2);
for (int i = 0; i < fields.length; i++) {
Cell cell_2_temp = row_2.createCell(1 + i); //++
cell_2_temp.setCellValue(fields[i]); //++
CellStyle cs_field = wb.createCellStyle();
cs_field.setAlignment(HorizontalAlignment.CENTER);
cell_2_temp.setCellStyle(cs_field);
}
//制作数据区
//创建一个文件对象,作为excel文件内容的输出文件
File f = new File("test.xlsx");
//输出时通过流的形式对外输出,包装对应的目标文件
OutputStream os = new FileOutputStream(f);
//将内存中的workbook数据写入到流中
wb.write(os);
wb.close();
os.close();
}
1.5 题目模板数据制作
我们继续来做数据区
@Test
public void testProjectPoi() throws IOException {
//1.获取到对应的Excel文件,工作簿文件
Workbook wb = new XSSFWorkbook();
//2.创建工作表
Sheet s = wb.createSheet("题目数据文件");
//设置通用配置
// s.setColumnWidth(4,100);
CellStyle cs_field = wb.createCellStyle();
cs_field.setAlignment(HorizontalAlignment.CENTER);
cs_field.setBorderTop(BorderStyle.THIN);
cs_field.setBorderBottom(BorderStyle.THIN);
cs_field.setBorderLeft(BorderStyle.THIN);
cs_field.setBorderRight(BorderStyle.THIN);
//制作标题
s.addMergedRegion(new CellRangeAddress(1,1,1,12));
Row row_1 = s.createRow(1);
Cell cell_1_1 = row_1.createCell(1);
cell_1_1.setCellValue("在线试题导出信息");
//创建一个样式
CellStyle cs_title = wb.createCellStyle();
cs_title.setAlignment(HorizontalAlignment.CENTER);
cs_title.setVerticalAlignment(VerticalAlignment.CENTER);
cell_1_1.setCellStyle(cs_title);
//制作表头
String[] fields = {"题目ID","所属公司ID","所属目录ID","题目简介","题干描述",
"题干配图","题目分析","题目类型","题目难度","是否经典题","题目状态","审核状态"};
Row row_2 = s.createRow(2);
for (int i = 0; i < fields.length; i++) {
Cell cell_2_temp = row_2.createCell(1 + i); //++
cell_2_temp.setCellValue(fields[i]); //++
cell_2_temp.setCellStyle(cs_field);
}
//制作数据区
List<Question> questionList = new ArrayList<>();
Question qq = new Question();
qq.setId("1");
qq.setPicture("12");
qq.setReviewStatus("13");
qq.setAnalysis("14");
qq.setCatalogId("15");
qq.setCompanyId("16");
qq.setDifficulty("17");
qq.setIsClassic("18");
qq.setRemark("19");
qq.setState("21");
qq.setSubject("31");
qq.setType("41");
questionList.add(qq);
Question qqq = new Question();
qqq.setId("1");
qqq.setPicture("12");
qqq.setReviewStatus("13");
qqq.setAnalysis("14");
qqq.setCatalogId("15");
qqq.setCompanyId("16");
qqq.setDifficulty("17");
qqq.setIsClassic("18");
qqq.setRemark("19");
qqq.setState("21");
qqq.setSubject("31");
qqq.setType("41");
questionList.add(qqq);
int row_index = 0;
for (Question q : questionList) {
int cell_index = 0;
Row row_temp = s.createRow(3 + row_index++);
Cell cell_data_1 = row_temp.createCell(1 + cell_index++);
cell_data_1.setCellValue(q.getId()); //++
cell_data_1.setCellStyle(cs_field);
Cell cell_data_2 = row_temp.createCell(1 + cell_index++);
cell_data_2.setCellValue(q.getCompanyId()); //++
cell_data_2.setCellStyle(cs_field);
Cell cell_data_3 = row_temp.createCell(1 + cell_index++);
cell_data_3.setCellValue(q.getCatalogId()); //++
cell_data_3.setCellStyle(cs_field);
Cell cell_data_4 = row_temp.createCell(1 + cell_index++);
cell_data_4.setCellValue(q.getRemark()); //++
cell_data_4.setCellStyle(cs_field);
Cell cell_data_5 = row_temp.createCell(1 + cell_index++);
cell_data_5.setCellValue(q.getSubject()); //++
cell_data_5.setCellStyle(cs_field);
Cell cell_data_6 = row_temp.createCell(1 + cell_index++);
cell_data_6.setCellValue(q.getPicture()); //++
cell_data_6.setCellStyle(cs_field);
Cell cell_data_7 = row_temp.createCell(1 + cell_index++);
cell_data_7.setCellValue(q.getAnalysis()); //++
cell_data_7.setCellStyle(cs_field);
Cell cell_data_8 = row_temp.createCell(1 + cell_index++);
cell_data_8.setCellValue(q.getType()); //++
cell_data_8.setCellStyle(cs_field);
Cell cell_data_9 = row_temp.createCell(1 + cell_index++);
cell_data_9.setCellValue(q.getDifficulty()); //++
cell_data_9.setCellStyle(cs_field);
Cell cell_data_10 = row_temp.createCell(1 + cell_index++);
cell_data_10.setCellValue(q.getIsClassic()); //++
cell_data_10.setCellStyle(cs_field);
Cell cell_data_11 = row_temp.createCell(1 + cell_index++);
cell_data_11.setCellValue(q.getState()); //++
cell_data_11.setCellStyle(cs_field);
Cell cell_data_12 = row_temp.createCell(1 + cell_index++);
cell_data_12.setCellValue(q.getReviewStatus()); //++
cell_data_12.setCellStyle(cs_field);
}
---------------------------------------------------------------
for (int i = 0; i < questionList.size(); i++) {
Question question = questionList.get(i);
XSSFRow row = s.createRow(i + 3);
XSSFCell cell1 = row.createCell(1);
cell1.setCellValue(question.getId());
XSSFCell cell2 = row.createCell(2);
cell2.setCellValue(question.getCompanyId());
XSSFCell cell3 = row.createCell(3);
cell3.setCellValue(question.getAnalysis());
}
这个方法更好些!
----------------------------------------------------------------
//创建一个文件对象,作为excel文件内容的输出文件
File f = new File("test.xlsx");
//输出时通过流的形式对外输出,包装对应的目标文件
OutputStream os = new FileOutputStream(f);
//将内存中的workbook数据写入到流中
wb.write(os);
wb.close();
os.close();
}
测试即可!
1.6 题目报表数据准备
(1)找到/WEB-INF/pages/store/question/list.jsp
页面,修改导出题目的链接
<button type="button" class="btn btn-default" title="导出题目" οnclick=location.href="${ctx}/store/question?operation=downloadReport">
<i class="fa fa-download"></i>导出题目</button>
12
(2)在后台servlet中添加对应的方法
// uri:/store/question?operation=list
@WebServlet("/store/question")
public class QuestionServlet extends BaseServlet {
@Override
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
String operation = request.getParameter("operation");
if("list".equals(operation)){
this.list(request,response);
}
//其他的else if判断省略
else if("downloadReport".equals(operation)){
this.downloadReport(request,response);
}
}
private void downloadReport(HttpServletRequest request, HttpServletResponse response) throws IOException {
//生成报告的文件,然后传递到前端页面
questionService.getReport();
}
}
(3)在业务层QuestionService
添加一个方法getReport
public void getReport() throws IOException;
12
(4)在对应的实现类中去实现该方法,把之前在测试类中的测试方法testProjectPoi
里面的所有代码拷贝过来,其中数据我们应该是从数据库中查询出来,因此调用dao完成数据的查询
@Override
public void getReport() throws IOException{
//获取对应要展示的数据
SqlSession sqlSession = null;
List<Question> questionList = null;
try{
//1.获取SqlSession
sqlSession = MapperFactory.getSqlSession();
//2.获取Dao
QuestionDao questionDao = MapperFactory.getMapper(sqlSession,QuestionDao.class);
//3.调用Dao层操作
questionList = questionDao.findAll();
}catch (Exception e){
throw new RuntimeException(e);
//记录日志
}finally {
try {
TransactionUtil.close(sqlSession);
}catch (Exception e){
e.printStackTrace();
}
}
//1.获取到对应的Excel文件,工作簿文件
Workbook wb = new XSSFWorkbook();
//2.创建工作表
Sheet s = wb.createSheet("题目数据文件");
//设置通用配置
// s.setColumnWidth(4,100);
CellStyle cs_field = wb.createCellStyle();
cs_field.setAlignment(HorizontalAlignment.CENTER);
cs_field.setBorderTop(BorderStyle.THIN);
cs_field.setBorderBottom(BorderStyle.THIN);
cs_field.setBorderLeft(BorderStyle.THIN);
cs_field.setBorderRight(BorderStyle.THIN);
//制作标题
s.addMergedRegion(new CellRangeAddress(1,1,1,12));
Row row_1 = s.createRow(1);
Cell cell_1_1 = row_1.createCell(1);
cell_1_1.setCellValue("在线试题导出信息");
//创建一个样式
CellStyle cs_title = wb.createCellStyle();
cs_title.setAlignment(HorizontalAlignment.CENTER);
cs_title.setVerticalAlignment(VerticalAlignment.CENTER);
cell_1_1.setCellStyle(cs_title);
//制作表头
String[] fields = {"题目ID","所属公司ID","所属目录ID","题目简介","题干描述",
"题干配图","题目分析","题目类型","题目难度","是否经典题","题目状态","审核状态"};
Row row_2 = s.createRow(2);
for (int i = 0; i < fields.length; i++) {
Cell cell_2_temp = row_2.createCell(1 + i); //++
cell_2_temp.setCellValue(fields[i]); //++
cell_2_temp.setCellStyle(cs_field);
}
//制作数据区
int row_index = 0;
for (Question q : questionList) {
int cell_index = 0;
Row row_temp = s.createRow(3 + row_index++);
Cell cell_data_1 = row_temp.createCell(1 + cell_index++);
cell_data_1.setCellValue(q.getId()); //++
cell_data_1.setCellStyle(cs_field);
Cell cell_data_2 = row_temp.createCell(1 + cell_index++);
cell_data_2.setCellValue(q.getCompanyId()); //++
cell_data_2.setCellStyle(cs_field);
Cell cell_data_3 = row_temp.createCell(1 + cell_index++);
cell_data_3.setCellValue(q.getCatalogId()); //++
cell_data_3.setCellStyle(cs_field);
Cell cell_data_4 = row_temp.createCell(1 + cell_index++);
cell_data_4.setCellValue(q.getRemark()); //++
cell_data_4.setCellStyle(cs_field);
Cell cell_data_5 = row_temp.createCell(1 + cell_index++);
cell_data_5.setCellValue(q.getSubject()); //++
cell_data_5.setCellStyle(cs_field);
Cell cell_data_6 = row_temp.createCell(1 + cell_index++);
cell_data_6.setCellValue(q.getPicture()); //++
cell_data_6.setCellStyle(cs_field);
Cell cell_data_7 = row_temp.createCell(1 + cell_index++);
cell_data_7.setCellValue(q.getAnalysis()); //++
cell_data_7.setCellStyle(cs_field);
Cell cell_data_8 = row_temp.createCell(1 + cell_index++);
cell_data_8.setCellValue(q.getType()); //++
cell_data_8.setCellStyle(cs_field);
Cell cell_data_9 = row_temp.createCell(1 + cell_index++);
cell_data_9.setCellValue(q.getDifficulty()); //++
cell_data_9.setCellStyle(cs_field);
Cell cell_data_10 = row_temp.createCell(1 + cell_index++);
cell_data_10.setCellValue(q.getIsClassic()); //++
cell_data_10.setCellStyle(cs_field);
Cell cell_data_11 = row_temp.createCell(1 + cell_index++);
cell_data_11.setCellValue(q.getState()); //++
cell_data_11.setCellStyle(cs_field);
Cell cell_data_12 = row_temp.createCell(1 + cell_index++);
cell_data_12.setCellValue(q.getReviewStatus()); //++
cell_data_12.setCellStyle(cs_field);
}
//创建一个文件对象,作为excel文件内容的输出文件
File f = new File("test.xlsx");
//输出时通过流的形式对外输出,包装对应的目标文件
OutputStream os = new FileOutputStream(f);
//将内存中的workbook数据写入到流中
wb.write(os);
wb.close();
os.close();
}
1.7 题目报表业务实现
现在后台已经能够生成Excel文件并且填充了数据,但是真实的业务中我们是需要将这个文件下载到客户端
(1)修改接口方法getReport
,添加返回值
/**
* 获取包含了数据的流对象
* @return 包含了报表数据的流对象
* @throws IOException
*/
ByteArrayOutputStream getReport() throws IOException;
123456
(2)在实现类中实现该方法时,将内存中的Excel相关数据写入到ByteArrayOutputStream
流中
@Override
public ByteArrayOutputStream getReport() throws IOException {
//前面的代码无变动 故省略
/**
//创建一个文件对象,作为excel文件内容的输出文件
File f = new File("test.xlsx");
//输出时通过流的形式对外输出,包装对应的目标文件
OutputStream os = new FileOutputStream(f);
//将内存中的workbook数据写入到流中
wb.write(os);
wb.close();
os.close();
*/
//将内存中的workbook数据写入到流中
ByteArrayOutputStream os = new ByteArrayOutputStream();
wb.write(os);
wb.close();
return os;
}
(3)修改后台servlet的downloadReport
方法
private void downloadReport(HttpServletRequest request, HttpServletResponse response) throws IOException {
//返回的数据类型为文件xlsx类型
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
String fileName = new String("测试文件名.xlsx".getBytes(),"iso8859-1");
response.addHeader("Content-Disposition","attachment;fileName="+fileName);
//生成报告的文件,然后传递到前端页面
ByteArrayOutputStream os = questionService.getReport();
//获取产生响应的流对象
ServletOutputStream sos = response.getOutputStream();
//将数据从原始的字节流对象中提取出来写入到servlet对应的输出流中
os.writeTo(sos);
//将输出流刷新
sos.flush();
os.close();
}