JAVA method of exporting an EXCEL table: first use HSSFWorkbook to open or create an "Excel file object"; then use the Sheet object to return the row object, and use the row object to get the Cell object; then read and write the Cell object; and finally generate file into the responsive front-end page.

How to export EXCEL table from JAVA:

Basic steps:

First, we What you should know is that an Excel file corresponds to a workbook, a workbook is composed of multiple sheets, and a sheet is composed of multiple rows and cells.

Then the correct sequence when we use poi to export an Excel table should be:

1. Use HSSFWorkbook to open or create an "Excel file object"

2. Use the HSSFWorkbook object to return or create the Sheet object

3. Use the Sheet object to return the row object, and use the row object to get the Cell object

4. Read and write the Cell object.

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

Export Excel application instance:

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 {
    /**
     * 導(dǎo)出Excel
     * @param sheetName sheet名稱
     * @param title 標(biāo)題
     * @param values 內(nèi)容
     * @param wb HSSFWorkbook對(duì)象
     * @return
     */
    public static HSSFWorkbook getHSSFWorkbook(String sheetName,String []title,String [][]values, HSSFWorkbook wb){
        // 第一步，創(chuàng)建一個(gè)HSSFWorkbook，對(duì)應(yīng)一個(gè)Excel文件
        if(wb == null){
            wb = new HSSFWorkbook();
        }
        // 第二步，在workbook中添加一個(gè)sheet,對(duì)應(yīng)Excel文件中的sheet
        HSSFSheet sheet = wb.createSheet(sheetName);
        // 第三步，在sheet中添加表頭第0行,注意老版本poi對(duì)Excel的行數(shù)列數(shù)有限制
        HSSFRow row = sheet.createRow(0);
        // 第四步，創(chuàng)建單元格，并設(shè)置值表頭 設(shè)置表頭居中
        HSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 創(chuàng)建一個(gè)居中格式
        //聲明列對(duì)象
        HSSFCell cell = null;
        //創(chuàng)建標(biāo)題
        for(int i=0;i<title.length;i++){
            cell = row.createCell(i);
            cell.setCellValue(title[i]);
            cell.setCellStyle(style);
        }
        //創(chuàng)建內(nèi)容
        for(int i=0;i<values.length;i++){
            row = sheet.createRow(i + 1);
            for(int j=0;j<values[i].length;j++){
                //將內(nèi)容按順序賦給對(duì)應(yīng)的列對(duì)象
                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;
    /**
     * 導(dǎo)出報(bào)表
     * @return
     */
    @RequestMapping(value = "/export")
    @ResponseBody
    public void export(HttpServletRequest request,HttpServletResponse response) throws Exception {
           //獲取數(shù)據(jù)
           List<PageData> list = reportService.bookList(page);
           //excel標(biāo)題
        　　String[] title = {"名稱","性別","年齡","學(xué)校","班級(jí)"};
         　//excel文件名
        　 String fileName = "學(xué)生信息表"+System.currentTimeMillis()+".xls";
  　　　　　//sheet名
        　 String sheetName = "學(xué)生信息表";
　　　　　　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();
　　　　　　}
　　　　　　//創(chuàng)建HSSFWorkbook 
　　　　　　HSSFWorkbook wb = ExcelUtil.getHSSFWorkbook(sheetName, title, content, null);
　　　　　　//響應(yīng)到客戶端
　　　　　　try {
　　　　　　　　this.setResponseHeader(response, fileName);
       　　　　OutputStream os = response.getOutputStream();
       　　　　wb.write(os);
       　　　　os.flush();
       　　　　os.close();
 　　　　　　} catch (Exception e) {
       　　　　e.printStackTrace();
 　　　　　　}
　　}
    //發(fā)送響應(yīng)流方法
    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">導(dǎo)出Excel</button>
$(&#39;#js-export&#39;).click(function(){
            window.location.href="/report/exportBooksTable.do;
});

Related learning recommendations: java basics

The above is the detailed content of How to export EXCEL table from JAVA. For more information, please follow other related articles on the PHP Chinese website!