Java

报表生成与读取

略

Excel

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
<!-- Excel解析工具类  -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>${poi.version}</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>${poi.version}</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml-schemas</artifactId>
    <version>${poi.version}</version>
</dependency>

Excel的读取

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
//新建一个集合用来保存数据
List<Area> list = new ArrayList<>();

//new一个HSSFWorkbook对象传入Excel文件的输入流
//该对象代表了这个Excel文件
HSSFWorkbook hssfWorkbook = new HSSFWorkbook(new FileInputStream(file));
//一个Excel有多个子表(Sheet)
HSSFSheet sheet = hssfWorkbook.getSheetAt(0);
//迭代这个sheet可以遍历表中每一行
for (Row cells : sheet) {
    //跳过第一行,如果行号等于0就跳过
    if (cells.getRowNum() == 0) {
        continue;
    }
    //(业务需要)第一列如果为空就跳过不录入
    if (cells.getCell(0) == null || StringUtils.isBlank(cells.getCell(0).getStringCellValue())) {
        continue;
    }
    //封装一行数据,然后把数据放到list中
    Area area = new Area();
    area.setId(cells.getCell(0).getStringCellValue());
    area.setProvince(cells.getCell(1).getStringCellValue());
    area.setCity(cells.getCell(2).getStringCellValue());
    area.setDistrict(cells.getCell(3).getStringCellValue());
    area.setPostcode(cells.getCell(4).getStringCellValue());

    list.add(area);
}

另外一个例子:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
List<SubArea> list = new ArrayList<>();
HSSFWorkbook hssfWorkbook = new HSSFWorkbook(new FileInputStream(file));
HSSFSheet sheet = hssfWorkbook.getSheetAt(0);
for (Row cells : sheet) {
    if (cells.getRowNum() == 0) {
        continue;
    }
    if (cells.getCell(0) == null || StringUtils.isBlank(cells.getCell(0).getStringCellValue())) {
        continue;
    }
    SubArea sub_Area = new SubArea();
    //set操作
    sub_Area.setId(cells.getCell(0).getStringCellValue());

    FixedArea fixedArea = fixedAreaRepository.findOne(cells.getCell(1).getStringCellValue());
    sub_Area.setFixedArea(fixedArea);

    Area area = areaRepository.findOne(cells.getCell(2).getStringCellValue());
    sub_Area.setArea(area);

    sub_Area.setKeyWords(cells.getCell(3).getStringCellValue());
    sub_Area.setStartNum(cells.getCell(4).getStringCellValue());
    sub_Area.setEndNum(cells.getCell(5).getStringCellValue());
    sub_Area.setSingle(cells.getCell(6).getStringCellValue().charAt(0));
    sub_Area.setAssistKeyWords(cells.getCell(7).getStringCellValue());

    list.add(sub_Area);
}

Excel的生成与下载

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
//数据库中查找封装的List集合
List<WayBill> wayBills = wayBillService.findWayBills(model);

//生成报表
HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
HSSFSheet hssfSheet = hssfWorkbook.createSheet("运单数据");

//创建一行(表头)
HSSFRow headRow = hssfSheet.createRow(0);
headRow.createCell(0).setCellValue("运单号");
headRow.createCell(1).setCellValue("寄件人");
headRow.createCell(2).setCellValue("寄件人电话");
headRow.createCell(3).setCellValue("寄件人地址");
headRow.createCell(4).setCellValue("收件人");
headRow.createCell(5).setCellValue("收件人电话");
headRow.createCell(6).setCellValue("收件人地址");

//遍历运单集合,写入一条条数据
for (WayBill wayBill : wayBills) {
    //在最后一行的下一行创建一行.
    HSSFRow dataRow = hssfSheet.createRow(hssfSheet.getLastRowNum() + 1);
    //写入数据
    dataRow.createCell(0).setCellValue(wayBill.getWayBillNum());
    dataRow.createCell(1).setCellValue(wayBill.getSendName());
    dataRow.createCell(2).setCellValue(wayBill.getSendMobile());
    dataRow.createCell(3).setCellValue(wayBill.getSendAddress());
    dataRow.createCell(4).setCellValue(wayBill.getRecName());
    dataRow.createCell(5).setCellValue(wayBill.getRecMobile());
    dataRow.createCell(6).setCellValue(wayBill.getRecAddress());
}
//修改头,达到下载效果
ServletActionContext.getResponse().setContentType("application/vnd.ms-excel");
String fileName = "运单数据.xls";
String user_agent = ServletActionContext.getRequest().getHeader("user-agent");

String filename = FileUtils.encodeDownloadFilename(fileName, user_agent);

ServletActionContext.getResponse().setHeader("Content-Disposition",
        "attachment;filename=" + filename);
//得到Servlet输出流
ServletOutputStream outputStream = ServletActionContext.getResponse().getOutputStream();
//向流写入数据
hssfWorkbook.write(outputStream);

PDF

itext方式:

1
2
3
4
5
6
7
8
9
10
11
<!-- itext -->
<dependency>
    <groupId>com.lowagie</groupId>
    <artifactId>itext</artifactId>
    <version>4.2.1</version>
</dependency>
<dependency>
    <groupId>com.itextpdf</groupId>
    <artifactId>itext-asian</artifactId>
    <version>5.2.0</version>
</dependency>

jasperreport:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
<!-- groovy -->
<dependency>
    <groupId>org.codehaus.groovy</groupId>
    <artifactId>groovy-all</artifactId>
    <version>2.2.0</version>
</dependency>

<!-- jasperreport -->
<dependency>
    <groupId>net.sf.jasperreports</groupId>
    <artifactId>jasperreports</artifactId>
    <version>5.2.0</version>
    <!--有可能存在版本冲突-->
    <exclusions>
        <exclusion>
            <groupId>com.lowagie</groupId>
            <artifactId>itext</artifactId>
        </exclusion>
        <exclusion>
            <artifactId>jackson-databind</artifactId>
            <groupId>com.fasterxml.jackson.core</groupId>
        </exclusion>
        <exclusion>
            <artifactId>jackson-annotations</artifactId>
            <groupId>com.fasterxml.jackson.core</groupId>
        </exclusion>
        <exclusion>
            <artifactId>jackson-core</artifactId>
            <groupId>com.fasterxml.jackson.core</groupId>
        </exclusion>
    </exclusions>
</dependency>

生成PDF(itext方式)

Document–>Document与输出流建立联系–>打开Document–>开始编辑–>关闭Document
关闭后Document会自动存到输出流中

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
    //查出数据
    List<WayBill> wayBills = wayBillService.findWayBills(model);

    //修改头,达到下载效果
    ServletActionContext.getResponse().setContentType("application/pdf");
    String fileName = "运单数据.pdf";
    String user_agent = ServletActionContext.getRequest().getHeader("user-agent");

    String filename = FileUtils.encodeDownloadFilename(fileName, user_agent);

    ServletActionContext.getResponse().setHeader("Content-Disposition",
            "attachment;filename=" + filename);

    //生成PDF格式文件
    //得到Document对象
    Document document = new Document();
    //Servlet输出流和document建立联系
    PdfWriter.getInstance(document, ServletActionContext.getResponse().getOutputStream());
    //开始编辑
    document.open();
    //准备写入数据
    //新建PDF中的表格,配置表格属性,宽,边框,数据在表格中的布局
    Table table = new Table(7);
    table.setWidth(90);
    table.setBorder(1);
    table.getDefaultCell().setHorizontalAlignment(Element.ALIGN_CENTER);
    table.getDefaultCell().setVerticalAlignment(Element.ALIGN_CENTER);

    //设置字体
    BaseFont baseFont = BaseFont.createFont("STSongStd-Light", "UniGB-UCS2-H",
            false);
    Font font = new Font(baseFont, 10, Font.NORMAL, Color.BLUE);

    // 写表头
    table.addCell(buildCell("运单号", font));
    table.addCell(buildCell("寄件人", font));
    table.addCell(buildCell("寄件人电话", font));
    table.addCell(buildCell("寄件人地址", font));
    table.addCell(buildCell("收件人", font));
    table.addCell(buildCell("收件人电话", font));
    table.addCell(buildCell("收件人地址", font));

    // 循环写数据
    for (WayBill wayBill : wayBills) {
        table.addCell(buildCell(wayBill.getWayBillNum(), font));
        table.addCell(buildCell(wayBill.getSendName(), font));
        table.addCell(buildCell(wayBill.getSendMobile(), font));
        table.addCell(buildCell(wayBill.getSendAddress(), font));
        table.addCell(buildCell(wayBill.getRecName(), font));
        table.addCell(buildCell(wayBill.getRecMobile(), font));
        table.addCell(buildCell(wayBill.getRecAddress(), font));
    }
    // 将表格加入文档
    document.add(table);

    document.close();

//buildCell()方法是自定义的方法
private Cell buildCell(String content, Font font)
        throws BadElementException {
    Phrase phrase = new Phrase(content, font);
    return new Cell(phrase);
}

生成PDF(jasperreports)

jasperreports需要使用模板,模板有专门工具来生成
比较复杂,但是定制性强.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
//数据库查出数据
List<WayBill> wayBills = wayBillService.findWayBills(model);

//修改头,达到下载效果
ServletActionContext.getResponse().setContentType("application/pdf");
String fileName = "运单数据.pdf";
String user_agent = ServletActionContext.getRequest().getHeader("user-agent");

String filename = FileUtils.encodeDownloadFilename(fileName, user_agent);

ServletActionContext.getResponse().setHeader("Content-Disposition",
        "attachment;filename=" + filename);

//根据模板生成PDF
//找到模板的位置
String jasperPath = ServletActionContext.getServletContext().getRealPath("/WEB-INF/jasper/waybill.jrxml");
//生成jasperReport对象
JasperReport jasperReport = JasperCompileManager.compileReport(jasperPath);

//设置模板数据
//Paramerter变量
Map<String,Object> paramerters = new HashMap<>();

paramerters.put("company","胡帅公司");

//Field变量
JasperPrint jasperPrint = JasperFillManager.fillReport(jasperReport,
        paramerters, new JRBeanCollectionDataSource(wayBills));
//生成PDF客户端
JRPdfExporter jrPdfExporter = new JRPdfExporter();
//配置打印者
jrPdfExporter.setParameter(JRExporterParameter.JASPER_PRINT,jasperPrint);
//配置输出流
jrPdfExporter.setParameter(JRExporterParameter.OUTPUT_STREAM,ServletActionContext.getResponse().getOutputStream());
//导出
jrPdfExporter.exportReport();