package com.java.utils;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
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.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

/**
 * excel 导出
 *
 * @author Admin
 * @date 2020年5月20日
 */
@RestController
public class TestExcel {

    @GetMapping(value = "/export", produces = "application/json; charset=utf-8")
    public String createExcel(HttpServletResponse response) throws IOException {

        // 创建HSSFWorkbook对象(excel的文档对象)
        Workbook wb = new HSSFWorkbook();
        // HSSFCellStyle style = this.getStyle(wb);
        // 建立新的sheet对象（excel的表单）
        Sheet sheet = wb.createSheet("成绩表");

        // 在sheet里创建第一行，参数为行索引(excel的行)，可以是0～65535之间的任何一个
       // HSSFRow row1 = sheet.createRow(0);
        Row row1= sheet.createRow(0);
        // 创建单元格（excel的单元格，参数为列索引，可以是0～255之间的任何一个
        Cell cell = row1.createCell(0);
        // 设置单元格内容
        cell.setCellValue("学员考试成绩一览表");
        // cell.setCellStyle(style);
        // 合并单元格CellRangeAddress构造参数依次表示起始行，截至行，起始列， 截至列
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 3));
        // 在sheet里创建第二行
        //HSSFRow row2 = sheet.createRow(1);
        Row row2= sheet.createRow(1);
        // 创建单元格并设置单元格内容
        row2.createCell(0).setCellValue("姓名");
        row2.createCell(1).setCellValue("班级");
        row2.createCell(2).setCellValue("笔试成绩");
        row2.createCell(3).setCellValue("机试成绩");
        // row2.getCell(0).setCellStyle(style);
        // row2.getCell(1).setCellStyle(style);
        // row2.getCell(2).setCellStyle(style);
        // row2.getCell(3).setCellStyle(style);
        CellRangeAddress c = CellRangeAddress.valueOf("A2:D2");
        sheet.setAutoFilter(c);
        //c.formatAsString();
        List<StudentInfo> stuList = new ArrayList<StudentInfo>();
        StudentInfo stu1 = new StudentInfo();
        stu1.setStuName("狗老齐");
        stu1.setStuClass("5班");
        stu1.setBishiResult("28");
        stu1.setJishiResult("29");
        stuList.add(stu1);
        StudentInfo stu2 = new StudentInfo();
        stu2.setStuName("靓仔");
        stu2.setStuClass("5班");
        stu2.setBishiResult("82");
        stu2.setJishiResult("92");
        stuList.add(stu2);
        StudentInfo stu3 = new StudentInfo();
        stu3.setStuName("阳仔");
        stu3.setStuClass("8班");
        stu3.setBishiResult("82");
        stu3.setJishiResult("92");
        stuList.add(stu3);
        StudentInfo stu4 = new StudentInfo();
        stu4.setStuName("1仔");
        stu4.setStuClass("8班");
        stu4.setBishiResult("82");
        stu4.setJishiResult("92");
        stuList.add(stu4);
        StudentInfo stu5 = new StudentInfo();
        stu5.setStuName("2仔");
        stu5.setStuClass("8班");
        stu5.setBishiResult("82");
        stu5.setJishiResult("92");
        stuList.add(stu5);
        StudentInfo stu6 = new StudentInfo();
        stu6.setStuName("3仔");
        stu6.setStuClass("8班");
        stu6.setBishiResult("82");
        stu6.setJishiResult("92");
        stuList.add(stu6);
        int len=0;
        for (int i = 0; i < stuList.size(); i++) {
            StudentInfo stu = stuList.get(i);
            Row row3 = sheet.createRow(2 + i);
            row3.createCell(0).setCellValue(stu.getStuName());
            row3.createCell(1).setCellValue(stu.getStuClass());
            row3.createCell(2).setCellValue(stu.getBishiResult());
            row3.createCell(3).setCellValue(stu.getJishiResult());
            // row3.getCell(0).setCellStyle(style);
            // row3.getCell(1).setCellStyle(style);
            // row3.getCell(2).setCellStyle(style);
            // row3.getCell(3).setCellStyle(style);
            String stringCellValue = row3.getCell(0).getStringCellValue();
            if("阳仔".equals(stringCellValue)){
                len=i+2;
            }

        }
        sheet.groupRow(2,len-1);
        sheet.groupRow(len+1,stuList.size());

        //sheet.getCellRange("A4:A5").groupByRows(true);

        // 输出Excel文件
        OutputStream output = response.getOutputStream();
        response.reset();
        response.setHeader("Content-disposition", "attachment; filename=test.xls");
        response.setContentType("application/msexcel");
        wb.write(output);
        output.close();
        return null;
    }

   

}

 

转载于：https://blog.csdn.net/weixin_43840872/article/details/108280680