Java導(dǎo)入、導(dǎo)出excel用法步驟保姆級(jí)教程(附封裝好的工具類)

更新時(shí)間：2025年03月17日 11:41:33 作者：征塵bjajmd

這篇文章主要介紹了Java導(dǎo)入、導(dǎo)出excel的相關(guān)資料,講解了使用Java和ApachePOI庫將數(shù)據(jù)導(dǎo)出為Excel文件,包括創(chuàng)建工作簿、工作表、行和單元格,設(shè)置樣式和字體,合并單元格,添加公式和下拉選擇等功能,需要的朋友可以參考下

前言

我們?cè)谌粘ｉ_發(fā)中，一定遇到過要將數(shù)據(jù)導(dǎo)出為Excel的需求，那么怎么做呢？在做之前，我們需要思考下Excel的組成。Excel是由四個(gè)元素組成的分別是：WorkBook(工作簿)、Sheet(工作表)、Row(行)、Cell(單元格)，其中包含關(guān)系是從左至右，一個(gè)WorkBook可以包含多個(gè)Sheet，一個(gè)Sheet又是由多個(gè)Row組成，一個(gè)Row是由多個(gè)Cell組成。知道這些后那么我們就使用java來將數(shù)據(jù)以Excel的方式導(dǎo)出。讓我們一起來學(xué)習(xí)吧！

一、引入Apache POI依賴

使用Java實(shí)現(xiàn)將數(shù)據(jù)以Excel的方式導(dǎo)出，需要依賴第三方的庫。我們需要再pom.xml中引入下面的依賴：

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

二、用法&步驟

2.1 創(chuàng)建Excel的元素

1）創(chuàng)建WokrBook

Workbook workbook = new XSSFWorkbook();

2）創(chuàng)建Sheet

 Sheet sheet = workbook.createSheet();

設(shè)置sheet的名稱

 Sheet sheet = workbook.createSheet("sheet名稱");

3）創(chuàng)建行Row

Row row = sheet.createRow(0);

4）創(chuàng)建單元格Cell

Cell cell = row.createCell(0, CellType.STRING);

可以指定單元格的類型，支持的類型有下面7種：

_NONE(-1),
NUMERIC(0),
STRING(1),
//公式
FORMULA(2),
BLANK(3),
//布爾
BOOLEAN(4),
ERROR(5);

5）填充數(shù)據(jù)

 cell.setCellValue("蘋果");

2.3 樣式和字體

如果我們需要導(dǎo)出的Excel美觀一些，如設(shè)置字體的樣式加粗、顏色、大小等等，就需要?jiǎng)?chuàng)建樣式和字體。

創(chuàng)建樣式：

CellStyle cellStyle = workbook.createCellStyle();

1）左右垂直居中

//左右居中
excelTitleStyle.setAlignment(HorizontalAlignment.CENTER);
// 設(shè)置垂直居中
excelTitleStyle.setVerticalAlignment(VerticalAlignment.CENTER);

2）字體加粗、顏色

創(chuàng)建加粗樣式并設(shè)置到CellStyle 中：

Font font = workbook.createFont();
//字體顏色為紅色
font.setColor(IndexedColors.RED.getIndex());
//字體加粗
font.setBold(true);
cellStyle.setFont(font);

指定Cell單元格使用該樣式：

cell.setCellStyle(style);

3）調(diào)整列寬和高

Sheet sheet = workbook.createSheet();
//自動(dòng)調(diào)整列的寬度來適應(yīng)內(nèi)容
sheet.autoSizeColumn(int column); 
// 設(shè)置列的寬度
sheet.setColumnWidth(2, 20 * 256);

autoSizeColumn()傳遞的參數(shù)就是要設(shè)置的列索引。setColumnWidth()第一個(gè)參數(shù)是要設(shè)置的列索引，第二參數(shù)是具體的寬度值，寬度 = 字符個(gè)數(shù) * 256（例如20個(gè)字符的寬度就是20 * 256）

4）傾斜、下劃線

Font font = workbook.createFont();
font.setItalic(boolean italic); 設(shè)置傾斜
font.setUnderline(byte underline); 設(shè)置下劃線

2.4 進(jìn)階用法

1）合并單元格

Workbook workbook = new XSSFWorkbook();
Sheet sheet = workbook.createSheet(fileName);
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 5));

CellRangeAddress()方法四個(gè)參數(shù)分別是fristRow：起始行、lastRow：結(jié)束行、fristCol：起始列、lastCol：結(jié)束列。

如果你想合并從第一行到第二行從一列到第十列的單元格（一共合并20格），那么就是CellRangeAddress(0,1,0,10)。

2）字段必填

//創(chuàng)建數(shù)據(jù)驗(yàn)證
DataValidationHelper dvHelper = sheet.getDataValidationHelper();
//創(chuàng)建要添加校驗(yàn)的單元格對(duì)象
CellRangeAddressList addressList = new CellRangeAddressList(0, 0, 0, 10);
//創(chuàng)建必填校驗(yàn)規(guī)則
DataValidationConstraint constraint = validationHelper.createCustomConstraint("NOT(ISBLANK(A1))");
//設(shè)置校驗(yàn)
DataValidation validation = dvHelper.createValidation(constraint, addressList);
//校驗(yàn)不通過 提示
validation.setShowErrorBox(true);
sheet.addValidationData(validation);

CellRangeAddressList()方法傳遞四個(gè)參數(shù)，分別是：fristRow：起始行、lastRow：結(jié)束行、fristCol：起始列、lastCol：結(jié)束列。CellRangeAddressList(0, 0, 0, 10)表示的就是給第一行從第一列開始到第十列一共十個(gè)單元格添加數(shù)據(jù)校驗(yàn)。

3）添加公式

SUM：求和函數(shù)

//創(chuàng)建SUM公式
FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
Cell sumCell = row.createCell(0);
sumCell.setCellFormula("SUM(A1:A10)");
//計(jì)算SUM公式結(jié)果
Cell sumResultCell = row.createCell(1);
sumResultCell.setCellValue(evaluator.evaluate(sumCell).getNumberValue());

AVERAGE：平均數(shù)函數(shù)

//創(chuàng)建AVERAGE公式
FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
Cell averageCell = row.createCell(0);
averageCell.setCellFormula("AVERAGE(A1:A10)");

//計(jì)算AVERAGE公式結(jié)果
Cell averageResultCell = row.createCell(1);
averageResultCell.setCellValue(evaluator.evaluate(averageCell).getNumberValue());

COUNT：計(jì)數(shù)函數(shù)

//創(chuàng)建COUNT公式
FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
Cell countCell = row.createCell(0);
countCell.setCellFormula("COUNT(A1:A10)");

//計(jì)算COUNT公式結(jié)果
Cell countResultCell = row.createCell(1);
countResultCell.setCellValue(evaluator.evaluate(countCell).getNumberValue());

IF：條件函數(shù)

//創(chuàng)建IF公式
FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
Cell ifCell = row.createCell(0);
ifCell.setCellFormula("IF(A1>B1,\"Yes\",\"No\")");

//計(jì)算IF公式結(jié)果
Cell ifResultCell = row.createCell(1);
ifResultCell.setCellValue(evaluator.evaluate(ifCell).getStringValue());

CONCATENATE：連接函數(shù)

//創(chuàng)建CONCATENATE公式
FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
Cell concatenateCell = row.createCell(0);
concatenateCell.setCellFormula("CONCATENATE(A1,\" \",B1)");

//計(jì)算CONCATENATE公式結(jié)果
Cell concatenateResultCell = row.createCell(1);
concatenateResultCell.setCellValue(evaluator.evaluate(concatenateCell).getStringValue());

4）下拉選擇

//下拉值
private List<String> grade = Arrays.asList("高", "中", "低");
(此處省略n行代碼)
Sheet sheet = workbook.createSheet("sheet");
DataValidation dataValidation = this.addPullDownConstraint(i, sheet, grade );
sheet.addValidationData(dataValidation);

5）設(shè)置單元格的數(shù)據(jù)類型

數(shù)字格式

// 設(shè)置單元格樣式 - 數(shù)字格式
CellStyle numberCellStyle = workbook.createCellStyle();
numberCellStyle.setDataFormat(workbook.createDataFormat().getFormat("#,##0.00"));
//指定單元格
Row row = sheet.createRow(0);
Cell cell = row.createCell(0);
cell.setCellStyle(numberCellStyle);

日期格式

// 設(shè)置單元格樣式 - 日期格式
CellStyle dateCellStyle = workbook.createCellStyle();
dateCellStyle.setDataFormat(workbook.createDataFormat().getFormat("yyyy-MM-dd"));
//指定單元格
Row row = sheet.createRow(0);
Cell cell = row.createCell(0);
cell.setCellStyle(dateCellStyle);

三、導(dǎo)出完整示例

下面的示例使用SpringBoot項(xiàng)目來演示：

pom.xml依賴：

 <dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
    <version>2.7.5</version>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter</artifactId>
    <version>2.7.5</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>4.1.2</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.1.2</version>
</dependency>
<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <version>1.16.16</version>
</dependency>
<!-- https://mvnrepository.com/artifact/com.alibaba.fastjson2/fastjson2 -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>fastjson</artifactId>
    <version>2.0.21</version>
</dependency>
<dependency>
    <groupId>org.apache.commons</groupId>
    <artifactId>commons-lang3</artifactId>
    <version>3.12.0</version>
</dependency>

Controller層代碼：

package shijiangdiya.controller;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import shijiangdiya.utils.ExportUtils;
import javax.servlet.http.HttpServletResponse;

@RestController
@RequestMapping("/sync")
public class ExportController {
}

1）代碼

@Autowired
private HttpServletResponse response;

@PostMapping("/export")
public void export() {
 //模擬json數(shù)據(jù)
    String data = "[{\n" +
            "    \"studentId\": \"20210101\",\n" +
            "    \"name\": \"Alice\",\n" +
            "    \"age\": 20,\n" +
            "    \"credit\": 80\n" +
            "  },\n" +
            "  {\n" +
            "    \"studentId\": \"20210102\",\n" +
            "    \"name\": \"Bob\",\n" +
            "    \"age\": 21,\n" +
            "    \"credit\": 85\n" +
            "  },\n" +
            "  {\n" +
            "    \"studentId\": \"20210103\",\n" +
            "    \"name\": \"Charlie\",\n" +
            "    \"age\": 22,\n" +
            "    \"credit\": 90\n" +
            "  },\n" +
            "  {\n" +
            "    \"studentId\": \"20210104\",\n" +
            "    \"name\": \"David\",\n" +
            "    \"age\": 20,\n" +
            "    \"credit\": 75\n" +
            "  },\n" +
            "  {\n" +
            "    \"studentId\": \"20210105\",\n" +
            "    \"name\": \"Emily\",\n" +
            "    \"age\": 21,\n" +
            "    \"credit\": 82\n" +
            "  },\n" +
            "  {\n" +
            "    \"studentId\": \"20210106\",\n" +
            "    \"name\": \"Frank\",\n" +
            "    \"age\": 22,\n" +
            "    \"credit\": 88\n" +
            "  },\n" +
            "  {\n" +
            "    \"studentId\": \"20210107\",\n" +
            "    \"name\": \"Grace\",\n" +
            "    \"age\": 20,\n" +
            "    \"credit\": 81\n" +
            "  },\n" +
            "  {\n" +
            "    \"studentId\": \"20210108\",\n" +
            "    \"name\": \"Henry\",\n" +
            "    \"age\": 21,\n" +
            "    \"credit\": 89\n" +
            "  },\n" +
            "  {\n" +
            "    \"studentId\": \"20210109\",\n" +
            "    \"name\": \"Isaac\",\n" +
            "    \"age\": 22,\n" +
            "    \"credit\": 92\n" +
            "  },\n" +
            "  {\n" +
            "    \"studentId\": \"20210110\",\n" +
            "    \"name\": \"John\",\n" +
            "    \"age\": 20,\n" +
            "    \"credit\": 78\n" +
            "  },\n" +
            "  {\n" +
            "    \"studentId\": \"20210111\",\n" +
            "    \"name\": \"Kelly\",\n" +
            "    \"age\": 21,\n" +
            "    \"credit\": 84\n" +
            "  },\n" +
            "  {\n" +
            "    \"studentId\": \"20210112\",\n" +
            "    \"name\": \"Linda\",\n" +
            "    \"age\": 22,\n" +
            "    \"credit\": 87\n" +
            "  },\n" +
            "  {\n" +
            "    \"studentId\": \"20210113\",\n" +
            "    \"name\": \"Mike\",\n" +
            "    \"age\": 20,\n" +
            "    \"credit\": 77\n" +
            "  },\n" +
            "  {\n" +
            "    \"studentId\": \"20210114\",\n" +
            "    \"name\": \"Nancy\",\n" +
            "    \"age\": 21,\n" +
            "    \"credit\": 83\n" +
            "  },\n" +
            "  {\n" +
            "    \"studentId\": \"20210115\",\n" +
            "    \"name\": \"Oscar\",\n" +
            "    \"age\": 22,\n" +
            "    \"credit\": 91\n" +
            "  },\n" +
            "  {\n" +
            "    \"studentId\": \"20210116\",\n" +
            "    \"name\": \"Paul\",\n" +
            "    \"age\": 20,\n" +
            "    \"credit\": 76\n" +
            "  },\n" +
            "  {\n" +
            "    \"studentId\": \"20210117\",\n" +
            "    \"name\": \"Queen\",\n" +
            "    \"age\": 21,\n" +
            "    \"credit\": 86\n" +
            "  },\n" +
            "  {\n" +
            "    \"studentId\": \"20210118\",\n" +
            "    \"name\": \"Rachel\",\n" +
            "    \"age\": 22,\n" +
            "    \"credit\": 94\n" +
            "  },\n" +
            "  {\n" +
            "    \"studentId\": \"20210119\",\n" +
            "    \"name\": \"Sarah\",\n" +
            "    \"age\": 20,\n" +
            "    \"credit\": 79\n" +
            "  },\n" +
            "  {\n" +
            "    \"studentId\": \"20210120\",\n" +
            "    \"name\": \"Tom\",\n" +
            "    \"age\": 21,\n" +
            "    \"credit\": 80\n" +
            "  }\n" +
            "]\n";
    ExportUtils.exportExcel("學(xué)生信息", data, Student.class, response);
}

2）工具類

  /**
 * 數(shù)據(jù)導(dǎo)出
 * @param fileName 導(dǎo)出excel名稱
 * @param data 導(dǎo)出的數(shù)據(jù)
 * @param c 導(dǎo)出數(shù)據(jù)的實(shí)體class
 * @param response 響應(yīng)
 * @throws Exception
 */
public static void exportExcel(String fileName, String data, Class<?> c, HttpServletResponse response) throws Exception {
    try {
        // 創(chuàng)建表頭
        // 創(chuàng)建工作薄
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet();
        // 創(chuàng)建表頭行
        Row rowHeader = sheet.createRow(0);
        if (c == null) {
            throw new RuntimeException("Class對(duì)象不能為空!");
        }
        Field[] declaredFields = c.getDeclaredFields();
        List<String> headerList = new ArrayList<>();
        if (declaredFields.length == 0) {
            return;
        }
        for (int i = 0; i < declaredFields.length; i++) {
            Cell cell = rowHeader.createCell(i, CellType.STRING);
            String headerName = String.valueOf(declaredFields[i].getName());
            cell.setCellValue(headerName);
            headerList.add(i, headerName);
        }
        // 填充數(shù)據(jù)
        List<?> objects = JSONObject.parseArray(data, c);
        Object obj = c.newInstance();
        if (!CollectionUtils.isEmpty(objects)) {
            for (int o = 0; o < objects.size(); o++) {
                Row rowData = sheet.createRow(o + 1);
                for (int i = 0; i < headerList.size(); i++) {
                    Cell cell = rowData.createCell(i);
                    Field nameField = c.getDeclaredField(headerList.get(i));
                    nameField.setAccessible(true);
                    String value = String.valueOf(nameField.get(objects.get(o)));
                    cell.setCellValue(value);
                }
            }
        }
        response.setContentType("application/vnd.ms-excel");
        String resultFileName = URLEncoder.encode(fileName, "UTF-8");
        response.setHeader("Content-disposition", "attachment;filename=" + resultFileName + ";" + "filename*=utf-8''" + resultFileName);
        workbook.write(response.getOutputStream());
        workbook.close();
        response.flushBuffer();
    } catch (Exception e) {
        throw new RuntimeException(e);
    }
}

3）結(jié)果

四、導(dǎo)入完整示例

1）代碼

@PostMapping("/import")
public void importExcel(@RequestParam("excel") MultipartFile excel){
    Workbook workbook = null;
    try {
        workbook = WorkbookFactory.create(excel.getInputStream());
        Sheet sheet = workbook.getSheetAt(0);
        List<Student> students = new ArrayList<>();
        int i = 0;
        for (Row row : sheet) {
            Row row1 = sheet.getRow(i + 1);
            if(row1 != null){
                Student data = new Student();
                data.setStudentId(Integer.parseInt(row1.getCell(0).getStringCellValue()));
                data.setName(row1.getCell(1).getStringCellValue());
                data.setAge(Integer.parseInt(row1.getCell(2).getStringCellValue()));
                data.setCredit(Integer.parseInt(row1.getCell(3).getStringCellValue()));
                students.add(data);
            }
        }
        System.out.println(students);
        workbook.close();
    } catch (IOException e) {
        throw new RuntimeException(e);
    }

2）工具類

 /**
     * 導(dǎo)入
     * @param workbook 工作簿
     * @param c 實(shí)體類
     * @return 實(shí)體類集合
     */
    public static <T> List<T> importExcel(Workbook workbook,Class<?> c){
        List<T> dataList = new ArrayList<>();
        try {
            Sheet sheet = workbook.getSheetAt(0);
            int i = 0;
            T o = null;
            for (Row row : sheet) {
                Row row1 = sheet.getRow(i + 1);
                if(row1 != null){
                    o = (T) c.newInstance();
                    Field[] declaredFields = c.getDeclaredFields();
                    for (int i1 = 0; i1 < declaredFields.length; i1++) {
                        String name = declaredFields[i1].getName();
                        Field declaredField1 = o.getClass().getDeclaredField(name);
                        declaredField1.setAccessible(true);
                        Cell cell = row1.getCell(i1);
                        String type = declaredFields[i1].getType().getName();
                        String value = String.valueOf(cell);
                        if(StringUtils.equals(type,"int") || StringUtils.equals(type,"Integer")){
                            declaredField1.set(o,Integer.parseInt(value));
                        } else if(StringUtils.equals(type,"java.lang.String") || StringUtils.equals(type,"char") || StringUtils.equals(type,"Character") ||
                                StringUtils.equals(type,"byte") || StringUtils.equals(type,"Byte")){
                            declaredField1.set(o,value);
                        } else if(StringUtils.equals(type,"boolean") || StringUtils.equals(type,"Boolean")){
                            declaredField1.set(o,Boolean.valueOf(value));
                        } else if(StringUtils.equals(type,"double") || StringUtils.equals(type,"Double")){
                            declaredField1.set(o,Double.valueOf(value));
                        } else if (StringUtils.equals(type,"long") || StringUtils.equals(type,"Long")) {
                            declaredField1.set(o,Long.valueOf(value));
                        } else if(StringUtils.equals(type,"short") || StringUtils.equals(type,"Short")){
                            declaredField1.set(o,Short.valueOf(value));
                        } else if(StringUtils.equals(type,"float") || StringUtils.equals(type,"Float")){
                            declaredField1.set(o,Float.valueOf(value));
                        }
                    }
                }
                dataList.add(o);
            }
            workbook.close();
            return dataList;
        }catch (Exception e){
            e.printStackTrace();
        }
        return dataList;
    }