Java使用poi操作Excel单元格

一、引入依赖

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

二、操作execl表格，生成自己想要的文件

package com.example.testways.utils;

import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.stereotype.Component;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.FileOutputStream;
import java.io.OutputStream;

@Slf4j
@Component
public class ExeclUtil {

    public static HSSFWorkbook downLoadCollectionList(HttpServletRequest request, HttpServletResponse response) throws Exception {
        //创建一个HSSFWorkbook，对应一个Excel文件
        HSSFWorkbook wb = new HSSFWorkbook();
        //创建单元格，并设置值表头 设置表头居中
        HSSFCellStyle style = wb.createCellStyle();
        //文本居中对齐
        style.setAlignment(HorizontalAlignment.CENTER);
        //文本垂直居中
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        //在workbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet = wb.createSheet("sheet1");
        // 标题
        String[] title = {"行人","非机动车","机动车","电动自行车","合计"};
        //声明列对象
        HSSFCell cell = null;
        //合并单元格 第一行  起始行,结束行,起始列,结束列
        //在sheet中添加表头第0行,
        HSSFRow row = sheet.createRow(0);
        //合并第一行 从第一行开始第一行结束，第0列开始 第某列结束
        megerSheet(0, 0, 0, 3,sheet);
        megerSheet(0, 0, 4, 7,sheet);
        megerSheet(0, 0, 8, 10,sheet);
        megerSheet(0, 0, 11, 13,sheet);
        megerSheet(0, 0, 14, 16,sheet);
        int index = 0;
        for (int i = 0; i < title.length; i++) {
            //在第一行第一个单元格
            cell = row.createCell(index);
            //sheet.setColumnWidth(0, 4000);
            //sheet.setDefaultRowHeight((short) 1000);
            // 设置样式为居中
            cell.setCellStyle(style);
            //第一行合并内容
            cell.setCellValue(title[i]);
            //确保为合并的单元格的首列赋值
            if(index >= 8){
                index += 3;
            }else{
                index += 4;
            }
        }
        String[] title1 = {"违法数","通行量","守法率","违法总数","通行量","守法率","违法数","通行量","守法率","头盔佩戴数","通行量","头盔佩戴率","违法数","通行量","守法率"};
        //在sheet中添加表头第1行
        HSSFRow row1 = sheet.createRow(1);
        //合并第一行 从第一行开始第一行结束，第0列开始 第某列结束
        megerSheet(1, 1, 0, 1,sheet);
        megerSheet(1, 1, 4, 5,sheet);
        int index1 = 0;
        for (int i = 0; i < title1.length; i++) {
            //在第2行第一个单元格
            cell = row1.createCell(index1);
            sheet.setColumnWidth(index1, 3000);
            // 设置样式为居中
            cell.setCellStyle(style);
            //第一行合并内容
            cell.setCellValue(title1[i]);
            //确保为合并的单元格的首列赋值
            if(i == 0 || i == 2){
                index1 = i + 2;
            }else{
                if(i == 3){
                    index1 += 2;
                }else{
                    index1 += 1;
                }
            }
        }
        //在sheet中添加表头第1行
        HSSFRow row2 = sheet.createRow(2);
        HSSFRow row3 = sheet.createRow(3);
        HSSFRow row4 = sheet.createRow(4);
        HSSFRow row5 = sheet.createRow(5);
        HSSFRow row6 = sheet.createRow(6);

        String[] title2 = {"闯红灯","闯红灯"};
        String[] title3 = {"不走斑马线","逆行"};
        String[] title4 = {"不走人行道","走机动车道"};
        String[] title5 = {"小计","越线停车"};
        String[] title6 = {"小计","小计"};

        //合并第一行 从第一行开始第一行结束，第0列开始 第某列结束
        megerSheet(5, 6, 0, 0,sheet);
        megerSheet(5, 6, 1, 1,sheet);
        megerSheet(2, 6, 2, 2,sheet);
        megerSheet(2, 6, 3, 3,sheet);
        megerSheet(2, 6, 6, 6,sheet);
        megerSheet(2, 6, 7, 7,sheet);
        megerSheet(2, 6, 8, 8,sheet);
        megerSheet(2, 6, 9, 9,sheet);
        megerSheet(2, 6, 10, 10,sheet);
        megerSheet(2, 6, 11, 11,sheet);
        megerSheet(2, 6, 12, 12,sheet);
        megerSheet(2, 6, 13, 13,sheet);
        megerSheet(2, 6, 14, 14,sheet);
        megerSheet(2, 6, 15, 15,sheet);
        megerSheet(2, 6, 16, 16,sheet);

        createCellAndGetValue(title2,cell,row2,sheet,style);
        createCellAndGetValue(title3,cell,row3,sheet,style);
        createCellAndGetValue(title4,cell,row4,sheet,style);
        createCellAndGetValue(title5,cell,row5,sheet,style);
        createCellAndGetValue(title6,cell,row6,sheet,style);

        long time = System.currentTimeMillis();
        //输出到本地
        //String fileName ="D:\\User\\违法"+time+".xls";
        //FileOutputStream fileOutputStream = new FileOutputStream(fileName);
        //wb.write(fileOutputStream);
        String fileName =time+"路口交通秩序测评结果.xls";
        response.setContentType("application/octet-stream");
        response.reset();
        response.setCharacterEncoding("UTF-8");
        response.setContentType("application/octet-stream");
        // 转码防止乱码
        response.addHeader("Content-Disposition",
                "attachment;filename=" + new String(fileName.getBytes("gb2312"), "ISO8859-1"));
        OutputStream out = response.getOutputStream();
        wb.write(out);
        return wb;
    }

    /**
     * 创建单元格并赋值
     * @param title
     * @param cell
     * @param row
     * @param sheet
     * @param style
     */
    public static void createCellAndGetValue(String[] title, HSSFCell cell, HSSFRow row, HSSFSheet sheet, HSSFCellStyle style){
        int index = 0;
        for (int i = 0; i < title.length; i++) {
            //在第n行第一个单元格
            cell = row.createCell(index);
            sheet.setColumnWidth(index, 4000);
            // 设置样式为居中
            cell.setCellStyle(style);
            //第一行合并内容
            cell.setCellValue(title[i]);
            index += 4;
        }
    }

    /**
     * 合并单元格
     * @param firstRow
     * @param lastRow
     * @param firstCol
     * @param lastCol
     * @param sheet
     */
    public static void megerSheet(int firstRow, int lastRow, int firstCol, int lastCol,HSSFSheet sheet){
        CellRangeAddress callAddress = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
        sheet.addMergedRegion(callAddress);
    }
}

三、测试

package com.example.testways.controller;

import com.example.testways.utils.ExeclUtil;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

@RestController
@RequestMapping("/operatrExecl")
public class OperateExeclController {

    @GetMapping("/downLoadCollectionList")
    public void downLoadCollectionList(HttpServletRequest request, HttpServletResponse response) throws Exception {
        ExeclUtil.downLoadCollectionList(request,response);
    }
}

四、启动项目之后，访问相应的接口，可以看到正在下载execl表格；
本测试代码生成的excel表格效果如图所示：