File System (01): Manage Excel and PDF file types based on the SpringBoot framework

Keywords: Java Excel Apache github

Source code for this article: GitHub. Click here || GitEE. Click here

1. Introduction to Document Types

1. Excel Documents

Excel is a spreadsheet software.Intuitive interfaces, excellent computing functions and chart tools are often used in system development to transfer data to Excel files or to import Excel data into the system, which involves data conversion.

2. PDF Documents

PDF is a portable document format and an electronic file format, which has many advantages that other electronic document formats cannot compare with.The PDF file format encapsulates text, font type, format, color, and device- and resolution-independent graphics and images in one file.The format file can also contain electronic information such as hypertext links, sound and dynamic images. It supports specialized files with high integration and security reliability.

2. Excel File Management

1. POI Dependency

Apache POI is the open source library of the Apache Software Foundation, which provides API s for Java programs to read and write files in Microsoft Office format.

<!-- Excel rely on -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.9</version>
</dependency>
<!-- 2007 And later -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.9</version>
</dependency>

2. File Read

public static List<List<Object>> readExcel(String path) throws Exception {
    File file = new File(path) ;
    List<List<Object>> list = new LinkedList<>();
    XSSFWorkbook xwb = new XSSFWorkbook(new FileInputStream(file));
    // Read Sheet1 table contents
    XSSFSheet sheet = xwb.getSheetAt(0);
    // Number of rows read: Do not read Excel headers
    for (int i = (sheet.getFirstRowNum()+1); i <= (sheet.getPhysicalNumberOfRows()-1); i++) {
        XSSFRow row = sheet.getRow(i);
        if (row == null) { continue; }
        List<Object> linked = new LinkedList<>();
        for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {
            XSSFCell cell = row.getCell(j);
            if (cell == null) { continue; }
            Object value ;
            // This needs to be handled according to the actual business situation
            switch (cell.getCellType()) {
                case XSSFCell.CELL_TYPE_NUMERIC:
                    //Handle numeric band {.0} problem
                    value = Double.valueOf(String.valueOf(cell)).longValue() ;
                    break;
                default:
                    value = cell.toString();
            }
            linked.add(value);
        }
        if (linked.size()!= 0) {
            list.add(linked);
        }
    }
    return list;
}

3. File Creation

public static void createExcel(String excelName, String[] headList,List<List<Object>> dataList)
        throws Exception {
    // Create Excel Workbook
    XSSFWorkbook workbook = new XSSFWorkbook();
    XSSFSheet sheet = workbook.createSheet();
    // Create Header
    XSSFRow row = sheet.createRow(0);
    for (int i = 0; i < headList.length; i++) {
        XSSFCell cell = row.createCell(i);
        cell.setCellType(XSSFCell.CELL_TYPE_STRING);
        cell.setCellValue(headList[i]);
    }
    //Add data
    for (int line = 0; line < dataList.size(); line++) {
        XSSFRow rowData = sheet.createRow(line+1);
        List<Object> data = dataList.get(line);
        for (int j = 0; j < headList.length; j++) {
            XSSFCell cell = rowData.createCell(j);
            cell.setCellType(XSSFCell.CELL_TYPE_STRING);
            cell.setCellValue((data.get(j)).toString());
        }
    }
    FileOutputStream fos = new FileOutputStream(excelName);
    workbook.write(fos);
    fos.flush();
    fos.close();
}

4. File Export

public static void exportExcel(String[] headList, List<List<Object>> dataList,
                               OutputStream outputStream) throws Exception {
    // Create Excel Workbook
    XSSFWorkbook workbook = new XSSFWorkbook();
    XSSFSheet sheet = workbook.createSheet();
    // Create Header
    XSSFRow row = sheet.createRow(0);
    for (int i = 0; i < headList.length; i++) {
        XSSFCell cell = row.createCell(i);
        cell.setCellType(XSSFCell.CELL_TYPE_STRING);
        cell.setCellValue(headList[i]);
    }
    //Add data
    for (int line = 0; line < dataList.size(); line++) {
        XSSFRow rowData = sheet.createRow(line+1);
        List<Object> data = dataList.get(line);
        for (int j = 0; j < headList.length; j++) {
            XSSFCell cell = rowData.createCell(j);
            cell.setCellType(XSSFCell.CELL_TYPE_STRING);
            cell.setCellValue((data.get(j)).toString());
        }
    }
    workbook.write(outputStream);
    outputStream.flush();
    outputStream.close();
}

5. File Export Interface

@RestController
public class ExcelWeb {
    @RequestMapping("/web/outExcel")
    public void outExcel (HttpServletResponse response) throws Exception {
        String exportName = "2020-01-user-data" ;
        response.setContentType("application/vnd.ms-excel");
        response.addHeader("Content-Disposition", "attachment;filename="+
                             URLEncoder.encode(exportName, "UTF-8") + ".xlsx");
        List<List<Object>> dataList = ExcelUtil.readExcel("F:\\file-type\\user-excel.xlsx") ;
        String[] headList = new String[]{"user ID", "User name", "Cell-phone number"} ;
        ExcelUtil.exportExcel(headList,dataList,response.getOutputStream()) ;
    }
}

3. PDF File Management

1. IText dependency

iText is a Java component that generates PDF reports.By using page or API encapsulation on the server side to generate PDF reports, clients can directly display or download to the local through hyperlinks, which are often used in system development to generate more formal reports or electronic documents of contract types.

<dependency>
    <groupId>com.itextpdf</groupId>
    <artifactId>itextpdf</artifactId>
    <version>5.5.11</version>
</dependency>
<dependency>
    <groupId>com.itextpdf.tool</groupId>
    <artifactId>xmlworker</artifactId>
    <version>5.5.11</version>
</dependency>

2. API secondary packaging

First, do a second encapsulation of basic styles such as tables, paragraphs, pictures, etc. for the API provided by Itext to better suit your business.

public class PdfFontUtil {
    private PdfFontUtil(){}

    /**
     * Paragraph Style Acquisition
     */
    public static Paragraph getParagraph (String content, Font font,Integer alignment){
        Paragraph paragraph = new Paragraph(content,font) ;
        if (alignment != null && alignment >= 0){
            paragraph.setAlignment(alignment);
        }
        return paragraph ;
    }
    /**
     * Picture Style
     */
    public static Image getImage (String imgPath,float width,float height) throws Exception {
        Image image = Image.getInstance(imgPath);
        image.setAlignment(Image.MIDDLE);
        if (width > 0 && height > 0){
            image.scaleAbsolute(width, height);
        }
        return image ;
    }
    /**
     * Table Generation
     */
    public static PdfPTable getPdfPTable01 (int numColumns,float totalWidth) throws Exception {
        // Form processing
        PdfPTable table = new PdfPTable(numColumns);
        // Set table width ratio to%100
        table.setWidthPercentage(100);
        // Set Width: Average Width
        table.setTotalWidth(totalWidth);
        // Lock-in width
        table.setLockedWidth(true);
        // Set the blank width above the table
        table.setSpacingBefore(10f);
        // Set the blank width under the table
        table.setSpacingAfter(10f);
        // Set table default to no border
        table.getDefaultCell().setBorder(0);
        table.setPaddingTop(50);
        table.setSplitLate(false);
        return table ;
    }
    /**
     * Table Content
     */
    public static PdfPCell getPdfPCell (Phrase phrase){
        return new PdfPCell (phrase) ;
    }
    /**
     * Table Content Styled
     */
    public static void addTableCell (PdfPTable dataTable,Font font,List<String> cellList){
        for (String content:cellList) {
            dataTable.addCell(getParagraph(content,font,-1));
        }
    }
}

3. Generating PDF files

Here, based on the above tool class, draw a PDF page for reference.

public class PdfPage01 {
    // Basic Configuration
    private static String PDF_SITE = "F:\\file-type\\PDF Page 2020-01-15.pdf" ;
    private static String FONT = "C:/Windows/Fonts/simhei.ttf";
    private static String PAGE_TITLE = "PDF Data Export Report" ;
    // Base Style
    private static Font TITLE_FONT = FontFactory.getFont(FONT, BaseFont.IDENTITY_H,20, Font.BOLD);
    private static Font NODE_FONT = FontFactory.getFont(FONT, BaseFont.IDENTITY_H,15, Font.BOLD);
    private static Font BLOCK_FONT = FontFactory.getFont(FONT, BaseFont.IDENTITY_H,13, Font.BOLD, BaseColor.BLACK);
    private static Font INFO_FONT = FontFactory.getFont(FONT, BaseFont.IDENTITY_H,12, Font.NORMAL,BaseColor.BLACK);
    private static Font CONTENT_FONT = FontFactory.getFont(FONT, BaseFont.IDENTITY_H, BaseFont.NOT_EMBEDDED);

    private static void createPdfPage () throws Exception {
        // create documents
        Document document = new Document();
        PdfWriter writer = PdfWriter.getInstance(document, new FileOutputStream(PDF_SITE));
        document.open();
        // Report Title
        document.add(PdfFontUtil.getParagraph(PAGE_TITLE,TITLE_FONT,1)) ;
        document.add(PdfFontUtil.getParagraph("\n Business Name: XXX Technology Co., Ltd.",INFO_FONT,-1)) ;
        document.add(PdfFontUtil.getParagraph("\n Generation time: 2020-01-15\n\n",INFO_FONT,-1)) ;
        // Report Content
        // Paragraph Title + Report Chart
        document.add(PdfFontUtil.getParagraph("Urban Data Distribution Statistics",NODE_FONT,-1)) ;
        document.add(PdfFontUtil.getParagraph("\n· Visual Chart\n\n",BLOCK_FONT,-1)) ;
        // Set Picture Width and Height
        float documentWidth = document.getPageSize().getWidth() - document.leftMargin() - document.rightMargin();
        float documentHeight = documentWidth / 580 * 320;
        document.add(PdfFontUtil.getImage("F:\\file-type\\myChart.jpg",documentWidth-80,documentHeight-80)) ;
        // Data Table
        document.add(PdfFontUtil.getParagraph("\n· Data Details\n\n",BLOCK_FONT,-1)) ;
        PdfPTable dataTable = PdfFontUtil.getPdfPTable01(4,400) ;
        // Set up tables
        List<String> tableHeadList = tableHead () ;
        List<List<String>> tableDataList = getTableData () ;
        PdfFontUtil.addTableCell(dataTable,CONTENT_FONT,tableHeadList);
        for (List<String> tableData : tableDataList) {
            PdfFontUtil.addTableCell(dataTable,CONTENT_FONT,tableData);
        }
        document.add(dataTable);
        document.add(PdfFontUtil.getParagraph("\n· Report Description\n\n",BLOCK_FONT,-1)) ;
        document.add(PdfFontUtil.getParagraph("Data reports can monitor daily promotions." +
                "Different data performance can be analyzed to improve the promotion effect.",CONTENT_FONT,-1)) ;
        document.newPage() ;
        document.close();
        writer.close();
    }
    private static List<List<String>> getTableData (){
        List<List<String>> tableDataList = new ArrayList<>() ;
        for (int i = 0 ; i < 3 ; i++){
            List<String> tableData = new ArrayList<>() ;
            tableData.add("Zhejiang"+i) ;
            tableData.add("Hangzhou"+i) ;
            tableData.add("276"+i) ;
            tableData.add("33.3%") ;
            tableDataList.add(tableData) ;
        }
        return tableDataList ;
    }
    private static List<String> tableHead (){
        List<String> tableHeadList = new ArrayList<>() ;
        tableHeadList.add("Province") ;
        tableHeadList.add("City") ;
        tableHeadList.add("Number") ;
        tableHeadList.add("Percentage") ;
        return tableHeadList ;
    }
    public static void main(String[] args) throws Exception {
        createPdfPage () ;
    }
}

4. Page Effect

IV. Web Page to PDF

1. Page Jar Package Dependency

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-thymeleaf</artifactId>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-freemarker</artifactId>
</dependency>

2. Writing Page Styles

<!DOCTYPE html>
<html lang="en" xmlns:th="http://www.w3.org/1999/xhtml">
<head>
    <meta charset="UTF-8"/>
    <title>Title</title>
    <style>
        body{font-family:SimSun;}
    </style>
</head>
<body>
//Project information: <br/>
//Name: ${name}<br/>
//Author: ${author}<br/><br/>
<img 
src="https://img2018.cnblogs.com/blog/1691717/201906/1691717-20190603213911854-1098366582.jpg"/>
<br/>
</body>
</html>

3. Core Configuration Class

public class PageConfig {
    private static final String DEST = "F:\\file-type\\HTML Page 2020-01-15.pdf";
    private static final String HTML = "/pdf_page_one.html";
    private static final String FONT = "C:/Windows/Fonts/simsun.ttc";
    private static Configuration freemarkerCfg = null ;
    static {
        freemarkerCfg = new Configuration(Configuration.DEFAULT_INCOMPATIBLE_IMPROVEMENTS);
        //freemarker's template directory
        try {
            String path = "TODO:Template Path{custom}" ;
            freemarkerCfg.setDirectoryForTemplateLoading(new File(path));
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    /**
     * create documents
     */
    private static void createPdf(String content,String dest) throws Exception {
        Document document = new Document();
        PdfWriter writer = PdfWriter.getInstance(document, new FileOutputStream(dest));
        document.open();
        XMLWorkerFontProvider fontImp = new XMLWorkerFontProvider(XMLWorkerFontProvider.DONTLOOKFORFONTS);
        fontImp.register(FONT);
        XMLWorkerHelper.getInstance().parseXHtml(writer, document,
                new ByteArrayInputStream(content.getBytes()), null, Charset.forName("UTF-8"), fontImp);
        document.close();
    }
    /**
     * Page Rendering
     */
    private static String freeMarkerRender(Map<String, Object> data, String htmlTmp) throws Exception {
        Writer out = new StringWriter();
        Template template = freemarkerCfg.getTemplate(htmlTmp,"UTF-8");
        template.process(data, out);
        out.flush();
        out.close();
        return out.toString();
    }
    /**
     * Method Entry
     */
    public static void main(String[] args) throws Exception {
        Map<String,Object> data = new HashMap<> ();
        data.put("name","smile");
        data.put("author","Cicada") ;
        String content = PageConfig.freeMarkerRender(data,HTML);
        PageConfig.createPdf(content,DEST);
    }
}

4. Conversion Effect Chart

5. Source code address

File types are covered in this section under the source ware18-file-parent/case-file-type directory.

GitHub·address
https://github.com/cicadasmile/middle-ware-parent
GitEE·address
https://gitee.com/cicadasmile/middle-ware-parent

Recommended reading:

Microservice Architecture Theme

<1. Introduction to project technology selection, illustration of Architecture>

<2. Business Architecture Design, System Hierarchical Management>

<3. Introduction to Database Selection, Business Data Planning and Design>

<4. Integration of middleware and encapsulation of public services>

<5. Application Design of SpringCloud Basic Components>

<6. Chat architecture through business, application, technology, storage>

Posted by Round on Thu, 06 Feb 2020 18:54:39 -0800

Hot Keywords

©2024 Programmer Group