快捷導(dǎo)航

教你如何使用JAVA POI

更新時(shí)間：2021年05月25日 11:38:20 作者：張鵬輝

今天教大家怎么學(xué)習(xí)JAVA POI的用法,文中有非常詳細(xì)的代碼示例,對(duì)正在學(xué)習(xí)java的小伙伴們有很好地幫助,需要的朋友可以參考下

一、導(dǎo)入jar包

所需jar包，在pom中添加如下坐標(biāo)即可

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.17</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.17</version>
</dependency>

注意：

操作Excel文件區(qū)分版本：

2003版本（包含2003）以前的擴(kuò)展名為.xls需要用HSSFWorkbook類操作
2007版本（包含2007）以后的擴(kuò)展名為.xlsx需要用XSSFWorkbook類操作

二、導(dǎo)出

2007版本（包含2007）以后的擴(kuò)展名為.xlsx需要用XSSFWorkbook類操作

2003版本（包含2003）以前的擴(kuò)展名為.xls需要用HSSFWorkbook類操作
和 07基本相似就是把XSSFWorkbook換成HSSFWorkbook

后綴名改成點(diǎn)xls

package com.zph.poi;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.core.io.ClassPathResource;
import org.springframework.core.io.Resource;
import org.springframework.stereotype.Service;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.net.URI;
import java.net.URLEncoder;

@Service
public class PoiServiceImpl {


    public void exportExcel2007() throws IOException {
        //創(chuàng)建工作簿 類似于創(chuàng)建Excel文件
        XSSFWorkbook workbook=new XSSFWorkbook();
        //創(chuàng)建 sheetname頁(yè)名
        XSSFSheet sheet = workbook.createSheet("員工信息");
        sheet.setColumnWidth(3,20*256);//給第3列設(shè)置為20個(gè)字的寬度
        sheet.setColumnWidth(4,20*256);//給第4列設(shè)置為20個(gè)字的寬度
        //創(chuàng)建一行,下標(biāo)從0開(kāi)始
        XSSFRow row = sheet.createRow(0);
        //創(chuàng)建這行中的列,下標(biāo)從0開(kāi)始 （表頭）
        XSSFCell cell = row.createCell(0);
        // 給cell 0下表賦值
        cell.setCellValue("姓名");
        //創(chuàng)建這行中的列,并給該列直接賦值
        row.createCell(1).setCellValue("年齡");
        row.createCell(2).setCellValue("性別");
        row.createCell(3).setCellValue("生日");
        row.createCell(4).setCellValue("手機(jī)號(hào)");
        // 設(shè)置表里內(nèi)容
        row = sheet.createRow(1);
        row.createCell(0).setCellValue("T");
        row.createCell(1).setCellValue("保密");
        row.createCell(2).setCellValue("男");
        row.createCell(3).setCellValue("保密");
        row.createCell(4).setCellValue("12121212121");

        row = sheet.createRow(2);
        row.createCell(0).setCellValue("T");
        row.createCell(1).setCellValue("18");
        row.createCell(2).setCellValue("女");
        row.createCell(3).setCellValue("2000-01-01");
        row.createCell(4).setCellValue("12121212122");
        //設(shè)定 路徑
        File file = new File("D:\\zph\\temp\\員工信息2007.xlsx");
        FileOutputStream stream = new FileOutputStream(file);
        // 需要拋異常
        workbook.write(stream);
        //關(guān)流
        stream.close();
    }

    public void exportExcel2003() throws IOException {
        //創(chuàng)建工作簿 類似于創(chuàng)建Excel文件
        HSSFWorkbook workbook=new HSSFWorkbook();
        //創(chuàng)建 sheetname頁(yè)名
        HSSFSheet sheet = workbook.createSheet("員工信息");
        //創(chuàng)建一行,下標(biāo)從0開(kāi)始
        HSSFRow row = sheet.createRow(0);
        //創(chuàng)建這行中的列,下標(biāo)從0開(kāi)始 （表頭）
        HSSFCell cell = row.createCell(0);
        // 給cell 0下表賦值
        cell.setCellValue("姓名");
        //創(chuàng)建這行中的列,并給該列直接賦值
        row.createCell(1).setCellValue("年齡");
        row.createCell(2).setCellValue("性別");
        row.createCell(3).setCellValue("生日");
        row.createCell(4).setCellValue("手機(jī)號(hào)");
        // 設(shè)置表里內(nèi)容
        row = sheet.createRow(1);
        row.createCell(0).setCellValue("T");
        row.createCell(1).setCellValue("保密");
        row.createCell(2).setCellValue("男");
        row.createCell(3).setCellValue("保密");
        row.createCell(4).setCellValue("12121212121");

        row = sheet.createRow(2);
        row.createCell(0).setCellValue("T");
        row.createCell(1).setCellValue("18");
        row.createCell(2).setCellValue("女");
        row.createCell(3).setCellValue("2000-01-01");
        row.createCell(4).setCellValue("12121212122");

        //第一種導(dǎo)出 給定路徑
        //1設(shè)定 路徑 創(chuàng)建文件讀進(jìn)來(lái)在寫內(nèi)容
        File file = new File("D:\\zph\\temp\\員工信息2003.xls");
        FileOutputStream stream = new FileOutputStream(file);
        // 需要拋異常
        workbook.write(stream);
        //關(guān)流
        stream.close();




    }
    public void exportExcel2003(HttpServletRequest request, HttpServletResponse response) throws IOException {
        //第二種導(dǎo)出 從項(xiàng)目中獲取模板
        //String realPath = request.getSession().getServletContext().getRealPath("/");
        Resource resource = new ClassPathResource("templates/員工信息2003Tem.xls");//jar包獲取

        //創(chuàng)建工作簿 類似于創(chuàng)建Excel文件
        HSSFWorkbook workbookTem=new HSSFWorkbook(resource.getInputStream());
        //創(chuàng)建 sheetname頁(yè)名
        HSSFSheet sheetTem = workbookTem.getSheet("員工信息");
        //HSSFSheet sheetTem = workbookTem.getSheetAt(0);
        HSSFRow rowTem = sheetTem.createRow(1);
        rowTem.createCell(0).setCellValue("xmtem");
        rowTem.createCell(1).setCellValue("nltem");
        rowTem.createCell(2).setCellValue("xbtem");
        rowTem.createCell(3).setCellValue("srtem");
        rowTem.createCell(4).setCellValue("sjhtem");
        ServletOutputStream outputStream = response.getOutputStream();
        response.reset();
        String fileName = URLEncoder.encode("員工信息TemOut.xls", "utf-8");
        response.setHeader("Content-disposition","attachment;filename="+fileName);
        response.setContentType("application/x-download;charset=UTF-8");
        // 對(duì)響應(yīng)客戶請(qǐng)求進(jìn)行重新編碼11
        //response.setCharacterEncoding("utf-8");

        workbookTem.write(outputStream);

        outputStream.close();
    }
    public String exportExcel2003(String s,HttpServletRequest request, HttpServletResponse response) throws IOException {
        //第三種直接導(dǎo)出


        //創(chuàng)建工作簿 類似于創(chuàng)建Excel文件
        HSSFWorkbook workbookTem=new HSSFWorkbook();
        //創(chuàng)建 sheetname頁(yè)名
        HSSFSheet sheet = workbookTem.createSheet("員工信息");
        //創(chuàng)建一行,下標(biāo)從0開(kāi)始
        HSSFRow row = sheet.createRow(0);
        //創(chuàng)建這行中的列,下標(biāo)從0開(kāi)始 （表頭）
        HSSFCell cell = row.createCell(0);
        // 給cell 0下表賦值
        cell.setCellValue("姓名");
        //創(chuàng)建這行中的列,并給該列直接賦值
        row.createCell(1).setCellValue("年齡");
        row.createCell(2).setCellValue("性別");
        row.createCell(3).setCellValue("生日");
        row.createCell(4).setCellValue("手機(jī)號(hào)");
        // 設(shè)置表里內(nèi)容
        row = sheet.createRow(1);
        row.createCell(0).setCellValue("T");
        row.createCell(1).setCellValue("保密");
        row.createCell(2).setCellValue("男");
        row.createCell(3).setCellValue("保密");
        row.createCell(4).setCellValue("12121212121");

        row = sheet.createRow(2);
        row.createCell(0).setCellValue("T");
        row.createCell(1).setCellValue("18");
        row.createCell(2).setCellValue("女");
        row.createCell(3).setCellValue("2000-01-01");
        row.createCell(4).setCellValue("12121212122");





        ServletOutputStream outputStream = response.getOutputStream();
        response.reset();

        String fileName = URLEncoder.encode("員工信息TemOut.xls", "utf-8");
        response.setHeader("Content-disposition","attachment;filename="+fileName);
        //response.setContentType("application/x-download;charset=UTF-8");
        response.setContentType("application/vnd.ms-excel");
        //response.setContentType("application/msexcel");
        // 對(duì)響應(yīng)客戶請(qǐng)求進(jìn)行重新編碼11
        //response.setCharacterEncoding("utf-8");

        workbookTem.write(outputStream);

        outputStream.close();
        return s;
    }
}

三、導(dǎo)出

@RequestMapping(value="/upload")
    public String  uploadExcel(@RequestParam("fileData") MultipartFile file, HttpServletRequest request, HttpServletResponse response) throws Exception {
        InputStream in = file.getInputStream();
        String s = poiService.uploadExcel(file, request, response);

        return s;
    }


public String  uploadExcel(MultipartFile file, HttpServletRequest request, HttpServletResponse response) throws Exception {
        InputStream in = file.getInputStream();
        //D:\zph\temp
        // 多態(tài)  拋異常
        //Workbook sheets = new XSSFWorkbook(stream);
        HSSFWorkbook sheets = new HSSFWorkbook(in);
        //獲取一個(gè)工作表(sheet頁(yè))，下標(biāo)從0開(kāi)始
        HSSFSheet sheet = sheets.getSheetAt(0);

        for (int i = 1; i<=sheet.getLastRowNum() ; i++) {


            // 獲取行數(shù)
            Row row = sheet.getRow(i);
            // 獲取單元格 取值
            String value1 = row.getCell(0).getStringCellValue();
            String value2 = row.getCell(1).getStringCellValue();
            String value3 = row.getCell(2).getStringCellValue();
            String value4 = row.getCell(3).getStringCellValue();
            String value5= row.getCell(4).getStringCellValue();

            System.out.println(value1);
            System.out.println(value2);
            System.out.println(value3);
            System.out.println(value4);
            System.out.println(value5);
        }


        //關(guān)流
        sheets.close();
        in.close();

        return "hha";
    }

postman 設(shè)置 post請(qǐng)求請(qǐng)求頭 Content-Type multipart/form-data

在這里插入圖片描述