Getting started with POI reports

In myMES management system, personnel management, orders and other operations need to import and export reports and other logic. In fact, it is the basic operation of the database table. This article introduces the export of Excel. Next time, we will introduce the import of data

Overview of POI reports

Public Address

Demand analysis

In enterprise application development, Excel report is one of the most common report requirements. There are two ways to develop Excel reports:

  • To facilitate operation, Excel – based reports upload data in batches
  • Generate Excel reports through Java code

Excel in two forms

There are two versions of Excel: Excel2003 and Excel2007. The differences are as follows:

Excel2003 Excel2007
The suffix xls xlsx
structure Binary, the core structure is the compound document type structure XML structure type
Single Sheet Data type Row :65535, column :256 Line: 1048579; 16384
The characteristics of Limited storage capacity Based on XML compression, small space, high operation efficiency

Common Excel operation tools:

There are two common EXCEL operations in Java: JXL and POI.

  • JXL can only operate on EXCEL, the architecture is older, only support EXCEL 95-2000 version, now and stop update maintenance
  • POI is an apache project, which can operate Microsoft Word,EXCEL and PPT, including office2003 and 2007. POI has been updated, all of which are relatively mainstream

POI Start operations

POI environment setup 

<! --POI Excel-->

       <dependency>

           <groupId>org.apache.poi</groupId>

           <artifactId>poi</artifactId>

The < version > 4.0.1 < / version >

       </dependency>

       <dependency>

           <groupId>org.apache.poi</groupId>

           <artifactId>poi-ooxml</artifactId>

The < version > 4.0.1 < / version >

       </dependency>

       <dependency>

           <groupId>org.apache.poi</groupId>

           <artifactId>poi-ooxml-schemas</artifactId>

The < version > 4.0.1 < / version >

       </dependency>

Copy the code

POI structure description

  • HSSF provides the ability to read and write Excel documents in XLS format
  • XSSF provides reading and writing Excel documents in XLSX format
  • HWPF provides doc documents in Word format
  • HSLF provides PPT documents for reading and writing Word
  • HDGF provides for reading documents in Visio format
  • HSMF provides the ability to read Outlook documents
  • HPBF provides read documents in Publisher format

Introduction of the API

  • WorkBook:EXCEL document object, classified for different EXCEL types: HSSFWorkbook(2003) and XSSFWorkbook(2007)
  • Sheet: Excel form
  • Row: Excel
  • Cell: an Excel Cell
  • The Font: Excel Font
  • CellStyle: CellStyle

Basic operation

To create the Excel 

public class PoiTest01 {

// Test creating an Excel file

    public static void main(String[] args) throws Exception {

//1. Create workbook

        Workbook wb = new XSSFWorkbook();

//2. Create the Sheet

        Sheet sheet = wb.createSheet("test");

/ / 3. The document flow

        FileOutputStream fos = new FileOutputStream("E:\\test.xlsx");

//4. Write files

        wb.write(fos);

        fos.close();

   }

}

Copy the code

Creating a cell 

// Test to create cells

    public static void main(String[] args) throws Exception {

//1. Create workbook

        Workbook wb = new XSSFWorkbook();

//2. Create the Sheet

        Sheet sheet = wb.createSheet("test");

//3. Create a row object, starting from 0

        Row row = sheet.createRow(3);

//4. Create a cell, starting from 0

        Cell cell = row.createCell(0);

//5. The cell writes data

        cell.setCellValue("Pass the Wisdom Podcast");

/ / 6. File stream

        FileOutputStream fos = new FileOutputStream("E:\\test.xlsx");

//7. Write files

        wb.write(fos);

        fos.close();

   }

Copy the code

Format is set 

// Create a cell style object

        CellStyle cellStyle = wb.createCellStyle();

// Set the border

cellStyle.setBorderBottom(BorderStyle.DASH_DOT); / / bottom border

cellStyle.setBorderTop(BorderStyle.HAIR); / / on the border

// Set the font

Font font = wb.createFont(); // Create a font object

        font.setFontName("Chinese Script"); // Set the font

font.setFontHeightInPoints((short)28); // Set the size

        cellStyle.setFont(font);

// Set width and height

sheet.setColumnWidth(0, 31 * 256); // Set the width of the first column to 31 characters

row.setHeightInPoints(50); // Set the height of the row to 50 points

// Set the display to center

cellStyle.setAlignment(HorizontalAlignment.CENTER); // Horizontal center

cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); // Vertical center

// Set the cell style

        cell.setCellStyle(cellStyle);

// Merge cells

        CellRangeAddress  region =new CellRangeAddress(0, 3, 0, 2);

        sheet.addMergedRegion(region);

Copy the code

Insert the picture 

// Draw a graph

    public static void main(String[] args) throws Exception {

//1. Create workbook

        Workbook wb = new XSSFWorkbook();

//2. Create the Sheet

        Sheet sheet = wb.createSheet("test");

// Read the image stream

        FileInputStream stream=new FileInputStream("e:\\logo.jpg");

        byte[] bytes= IOUtils.toByteArray(stream);

// Read images into binary arrays

        stream.read(bytes);

// Add an image to Excel and return the subscript of the image in the collection of images in Excel

        int pictureIdx = wb.addPicture(bytes,Workbook.PICTURE_TYPE_JPEG);

// Drawing tool class

        CreationHelper helper = wb.getCreationHelper();

// Create a drawing object

Drawing<? > patriarch = sheet.createDrawingPatriarch();

// Create an anchor point and set the image coordinates

        ClientAnchor anchor = helper.createClientAnchor();

anchor.setCol1(0); // Start at 0

anchor.setRow1(0); // Start at 0

// Create the image

        Picture picture = patriarch.createPicture(anchor, pictureIdx);

        picture.resize();

/ / 6. File stream

        FileOutputStream fos = new FileOutputStream("E:\\test.xlsx");

//7. Write files

        wb.write(fos);

        fos.close();

   }

Copy the code

Export POI reports

No matter what the requirements are, only the report export needs the following steps:

  • Construct Excel table data
  • Creating a workbook
  • Create sheet object
  • Creating a Row object
  • Creating a Cell Object
  • Fill in the data, set the style
  • Download to user data as an example, did not spend my article can pay attention to the public number, read before the article

Create a generic class for FileUtil file manipulation 

package com.cn.greemes.common.util;





import cn.hutool.core.io.IoUtil;

import cn.hutool.core.util.IdUtil;

import cn.hutool.poi.excel.BigExcelWriter;

import cn.hutool.poi.excel.ExcelUtil;

import org.apache.poi.xssf.streaming.SXSSFSheet;



import javax.servlet.ServletOutputStream;

import javax.servlet.http.HttpServletResponse;

import java.io.File;

import java.io.IOException;

import java.util.List;

import java.util.Map;



/ * *

* File manipulation

* /

public class FileUtil {





    public static final String SYS_TEM_DIR =System.getProperty("java.io.tmpdir")+ File.separator;





    public static void downloadExcel(List<Map<String, Object>> list, HttpServletResponse response) throws IOException {

        String tempPath = SYS_TEM_DIR + IdUtil.fastSimpleUUID() + ".xlsx";

        File file = new File(tempPath);

        BigExcelWriter writer = ExcelUtil.getBigWriter(file);

// Write out the content once, using the default style, forcing the output title

        writer.write(list, true);

        SXSSFSheet sheet = (SXSSFSheet)writer.getSheet();

/ / the above need strong SXSSFSheet otherwise no trackAllColumnsForAutoSizing method

        sheet.trackAllColumnsForAutoSizing();

// Column width is adaptive

        writer.autoSizeColumnAll();

// Response is an HttpServletResponse object

        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet; charset=utf-8");

//test. XLS is the file name of the pop-up download dialog box

        response.setHeader("Content-Disposition"."attachment; filename=file.xlsx");

        ServletOutputStream out = response.getOutputStream();

// Delete temporary files after termination

        file.deleteOnExit();

        writer.flush(out, true);

// Remember to close the output Servlet stream here

        IoUtil.close(out);

    }

}

Copy the code

Configure in controller 

 @ApiOperation("Export user data")

   @RequestMapping(value = "/export", method = RequestMethod.GET)

   @ResponseBody

   public void export(HttpServletResponse response, @RequestParam(value = "keyword", required = false) String keyword,

                      @RequestParam(value = "pageSize", defaultValue = "5") Integer pageSize,

                      @RequestParam(value = "pageNum", defaultValue = "1") Integer pageNum) throws UnsupportedEncodingException, IOException {

       Page<MesAdmin> adminList = adminService.list(keyword, pageSize, pageNum);



       List<Map<String,Object>> list = new ArrayList();



       for(int i=0; i<149; i++) {

           for (MesAdmin umsAdmin : adminList.getRecords()) {

               Map<String, Object> map = new LinkedHashMap<>(6);

               map.put("Name", umsAdmin.getUsername());

               map.put("Email", umsAdmin.getEmail());

               map.put("Nickname", umsAdmin.getNickName());

               map.put("Remarks information", umsAdmin.getNote());

               map.put("Creation time", umsAdmin.getCreateTime());

               map.put("Last Login Time", umsAdmin.getLoginTime());

               list.add(map);

           }

       }



       fileUtil.downloadExcel(list,response);

   }



Copy the code

Conclusion:

This time, I will introduce the export of Excel. Next time, I will introduce the import of Excel. This is a summary of my work.

Making address:

Making address: https://github.com/bangbangzhou/greemes/tree/master