Example Teaching of JAVA Exporting EXCEL Tables

Partners, recently busy, there is no time to write. Today, I share with you how JAVA exports EXCEL tables. Because this function has been done recently, so share it. If there are any mistakes, please correct them.

In many enterprise office systems, there are often user requirements, need to make statistics on the data and can download Excel files directly. In this case, since the customer has made the request, we should go to meet acridine, after all, the customer is God, how can we achieve it?And see me coming for you 11 times.

Introduction to POI: Jakarta POI is a set of Java API for accessing Microsoft format documents.Jakarta POI has many components, including HSSF for manipulating Excel format files and HWPF for manipulating Word, among which only HSSF for manipulating Excel is relatively mature.

The official home page, http://poi.apache.org/index.html,

API document http://poi.apache.org/apidocs/index.html

Now we use POI technology to export or import Excel, so let's use POI. To export Excel with POI, we first need to download the required jar package and then import it into our project. Students using maven only need to find relevant information.

Dependencies can be added to pom.xml.

1. Download the jar package:

Official download: http://poi.apache.org/download.html Here you can download the latest version and documentation of http://poi.org.html, the current version is 3.7, which uses a more stable version 3.6.

Baidu Disk Download: https://pan.baidu.com/s/1mjhoaWK Password: pkur

2. Add the jar package to the project:

Add the downloaded jar package to the lib folder in the WEBINFO directory. The Eclipse user selects the jar package and right-clicks the Build Path option. The Idea user selects the jar package and right-clicks the Add as Library option.

If you use maven to search for poi on your own in the maven central repository and select the appropriate version, you can also copy the following code directly to pom.xml.

<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.6</version>
</dependency>

Tip: Introducing an jar-dependent package with maven may encounter an bug that the package does not reference, but the maven dependency has been introduced without any errors, but only if 1 references a class below org.apache.poi.hssf.usermodel

An error will be reported as follows: Caused by: java.lang.NoClassDefFoundError: org/apache/poi/hssf/usermodel/HSSFWorkbook.

3. Jakarta POI HSSF API components:

HSSF (component for operating Excel) provides users with objects in the rg.apache.poi.hssf.usermodel package. The main sections include Excel objects, styles, and formats, with the following common objects:

Common components:

Document object for HSSFWorkbook excel Form for HSSFSheet excel Rows of HSSFRow excel Cell of HSSFCell excel HSSFFont excel Font

Style:

HSSFCellStyle cell Style

4. Basic operation steps:

First of all, we should know that one Excel file corresponds to one workbook, one workbook has more than one sheet, and one sheet consists of more than one row (row) and column (cell).So we use poi to export an Excel table

The correct order should be:

1. Open or create "Excel File Object" with HSSFWorkbook

2. Return or create an Sheet object with an HSSFWorkbook object

3. Return row object with Sheet object and get Cell object with row object

4. Read and write Cell objects.

5. Put the generated HSSFWorkbook in HttpServletResponse and respond to the front-end page

5. Export an Excel application example:

Tool class code:

package com.yq.util;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

public class ExcelUtil {

  /**
   *  export Excel
   * @param sheetName sheet Name 
   * @param title  Title 
   * @param values  content 
   * @param wb HSSFWorkbook object 
   * @return
   */
  public static HSSFWorkbook getHSSFWorkbook(String sheetName,String []title,String [][]values, HSSFWorkbook wb){

    //  No. 1 Step, create 1 individual HSSFWorkbook , corresponds to 1 individual Excel file 
    if(wb == null){
      wb = new HSSFWorkbook();
    }

    //  No. 2 Step, in workbook Add in 1 individual sheet, Corresponding Excel In the file sheet
    HSSFSheet sheet = wb.createSheet(sheetName);

    //  No. 3 Step, in sheet Add Header to 0 That's ok , Note the older version poi Yes Excel Limited number of rows and columns 
    HSSFRow row = sheet.createRow(0);

    //  No. 4 Step, create cells, and set the value table header   Set header centering 
    HSSFCellStyle style = wb.createCellStyle();
    style.setAlignment(HSSFCellStyle.ALIGN_CENTER); //  Establish 1 Centered format 

    // Declare Column Objects 
    HSSFCell cell = null;

    // Create Title 
    for(int i=0;i<title.length;i++){
      cell = row.createCell(i);
      cell.setCellValue(title[i]);
      cell.setCellStyle(style);
    }

    // Create Content 
    for(int i=0;i<values.length;i++){
      row = sheet.createRow(i + 1);
      for(int j=0;j<values[i].length;j++){
        // Assign content sequentially to the corresponding column object 
        row.createCell(j).setCellValue(values[i][j]);
      }
    }
    return wb;
  }
}

Controller code:

@Controller
@RequestMapping(value = "/report")
public class ReportFormController extends BaseController {

  @Resource(name = "reportService")
  private ReportManager reportService;

  /**
   *  export report 
   * @return
   */
  @RequestMapping(value = "/export")
  @ResponseBody
  public void export(HttpServletRequest request,HttpServletResponse response) throws Exception {
      // get data 
      List<PageData> list = reportService.bookList(page);

      //excel Title 
     * String[] title = {" Name "," Gender "," Age "," School "," class "};

      　 //excel file name 
     　  String fileName = " Student Information Sheet "+System.currentTimeMillis()+".xls";

  * //sheet name 
     　  String sheetName = " Student Information Sheet ";

 * for (int i = 0; i < list.size(); i++) {
      content[i] = new String[title.length];
      PageData obj = list.get(i);
      content[i][0] = obj.get("stuName").tostring();
      content[i][1] = obj.get("stuSex").tostring();
      content[i][2] = obj.get("stuAge").tostring();
      content[i][3] = obj.get("stuSchoolName").tostring();
      content[i][4] = obj.get("stuClassName").tostring();
 * }

 * // Establish HSSFWorkbook 
 * HSSFWorkbook wb = ExcelUtil.getHSSFWorkbook(sheetName, title, content, null);

 * // Response to Client 
 * try {
 * this.setResponseHeader(response, fileName);
     * OutputStream os = response.getOutputStream();
     * wb.write(os);
     * os.flush();
     * os.close();
  * } catch (Exception e) {
     * e.printStackTrace();
  * }
 * }

  // Send Response Flow Method 
  public void setResponseHeader(HttpServletResponse response, String fileName) {
    try {
      try {
        fileName = new String(fileName.getBytes(),"ISO8859-1");
      } catch (UnsupportedEncodingException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
      }
      response.setContentType("application/octet-stream;charset=ISO8859-1");
      response.setHeader("Content-Disposition", "attachment;filename="+ fileName);
      response.addHeader("Pargam", "no-cache");
      response.addHeader("Cache-Control", "no-cache");
    } catch (Exception ex) {
      ex.printStackTrace();
    }
  }}

Front-end page code:

<button id="js-export" type="button" class="btn btn-primary"> export Excel</button>
$('#js-export').click(function(){
      window.location.href="/report/exportBooksTable.do;
});

Interested friends follow the steps, thank you for your reading and support for this site.