poi导入导出excel，兼容xls和xlsx

最新推荐文章于 2024-09-26 14:20:39 发布

非非哥

最新推荐文章于 2024-09-26 14:20:39 发布

阅读量1.6w

点赞数 11

分类专栏：开发经验

本文链接：https://blog.csdn.net/feng2036/article/details/81505197

版权

开发经验专栏收录该内容

19 篇文章 0 订阅

订阅专栏

项目需求导入学生成绩后，导出统计结果。所以参考网上博文用poi写了excel导入导出。

相关jar的maven地址

                <!-- excel -->
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi</artifactId>
			<version>3.14</version>
		</dependency>
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-ooxml</artifactId>
			<version>3.14</version>
		</dependency>
		<!-- excel -->
		<dependency>
			<groupId>org.apache.log4j</groupId>
			<artifactId>com.springsource.org.apache.log4j</artifactId>
			<version>1.2.15</version>
		</dependency>

		<!-- jxl表格操作-->
		<dependency>
			<groupId>net.sourceforge.jexcelapi</groupId>
			<artifactId>jxl</artifactId>
			<version>2.6.12</version>
		</dependency>

导入的内容如图：

实体类代码

package com.edu.model;

import java.util.Date;

public class StudentScore {
    private Integer id;

    /**
     * 成绩单ID
     */
    private Integer scoreReportId;

    /**
     * 准考证号
     */
    private String testPermitNumber;

    /**
     * 监测号
     */
    private String monitorNumber;

    /**
     * 姓名
     */
    private String name;

    /**
     * 学校
     */
    private String school;

    /**
     * 语文
     */
    private Double chinese;

    /**
     * 数学
     */
    private Double math;

    /**
     * 英语
     */
    private Double english;

    /**
     * 品社
     */
    private Double moralitySociety;

    /**
     * 科学
     */
    private Double science;

    /**
     * 状态（0正常 1删除）
     */
    private String status;

    /**
     * 创建人
     */
    private Long createBy;

    /**
     * 创建时间
     */
    private Date createDate;

    /**
     * 更新人
     */
    private Long updateBy;

    /**
     * 更新时间
     */
    private Date updateDate;

    /**
     * @return id
     */
    public Integer getId() {
        return id;
    }

    /**
     * @param id
     */
    public void setId(Integer id) {
        this.id = id;
    }

    /**
     * 获取成绩单ID
     *
     * @return score_report_id - 成绩单ID
     */
    public Integer getScoreReportId() {
        return scoreReportId;
    }

    /**
     * 设置成绩单ID
     *
     * @param scoreReportId 成绩单ID
     */
    public void setScoreReportId(Integer scoreReportId) {
        this.scoreReportId = scoreReportId;
    }

    /**
     * 获取准考证号
     *
     * @return test_permit_number - 准考证号
     */
    public String getTestPermitNumber() {
        return testPermitNumber;
    }

    /**
     * 设置准考证号
     *
     * @param testPermitNumber 准考证号
     */
    public void setTestPermitNumber(String testPermitNumber) {
        this.testPermitNumber = testPermitNumber;
    }

    /**
     * 获取监测号
     *
     * @return monitor_number - 监测号
     */
    public String getMonitorNumber() {
        return monitorNumber;
    }

    /**
     * 设置监测号
     *
     * @param monitorNumber 监测号
     */
    public void setMonitorNumber(String monitorNumber) {
        this.monitorNumber = monitorNumber;
    }

    /**
     * 获取姓名
     *
     * @return name - 姓名
     */
    public String getName() {
        return name;
    }

    /**
     * 设置姓名
     *
     * @param name 姓名
     */
    public void setName(String name) {
        this.name = name;
    }

    /**
     * 获取学校
     *
     * @return school - 学校
     */
    public String getSchool() {
        return school;
    }

    /**
     * 设置学校
     *
     * @param school 学校
     */
    public void setSchool(String school) {
        this.school = school;
    }

    /**
     * 获取语文
     *
     * @return chinese - 语文
     */
    public Double getChinese() {
        return chinese;
    }

    /**
     * 设置语文
     *
     * @param chinese 语文
     */
    public void setChinese(Double chinese) {
        this.chinese = chinese;
    }

    /**
     * 获取数学
     *
     * @return math - 数学
     */
    public Double getMath() {
        return math;
    }

    /**
     * 设置数学
     *
     * @param math 数学
     */
    public void setMath(Double math) {
        this.math = math;
    }

    /**
     * 获取英语
     *
     * @return english - 英语
     */
    public Double getEnglish() {
        return english;
    }

    /**
     * 设置英语
     *
     * @param english 英语
     */
    public void setEnglish(Double english) {
        this.english = english;
    }

    /**
     * 获取品社
     *
     * @return morality_society - 品社
     */
    public Double getMoralitySociety() {
        return moralitySociety;
    }

    /**
     * 设置品社
     *
     * @param moralitySociety 品社
     */
    public void setMoralitySociety(Double moralitySociety) {
        this.moralitySociety = moralitySociety;
    }

    /**
     * 获取科学
     *
     * @return science - 科学
     */
    public Double getScience() {
        return science;
    }

    /**
     * 设置科学
     *
     * @param science 科学
     */
    public void setScience(Double science) {
        this.science = science;
    }

    /**
     * 获取状态（0正常 1删除）
     *
     * @return status - 状态（0正常 1删除）
     */
    public String getStatus() {
        return status;
    }

    /**
     * 设置状态（0正常 1删除）
     *
     * @param status 状态（0正常 1删除）
     */
    public void setStatus(String status) {
        this.status = status;
    }

    /**
     * 获取创建人
     *
     * @return create_by - 创建人
     */
    public Long getCreateBy() {
        return createBy;
    }

    /**
     * 设置创建人
     *
     * @param createBy 创建人
     */
    public void setCreateBy(Long createBy) {
        this.createBy = createBy;
    }

    /**
     * 获取创建时间
     *
     * @return create_date - 创建时间
     */
    public Date getCreateDate() {
        return createDate;
    }

    /**
     * 设置创建时间
     *
     * @param createDate 创建时间
     */
    public void setCreateDate(Date createDate) {
        this.createDate = createDate;
    }

    /**
     * 获取更新人
     *
     * @return update_by - 更新人
     */
    public Long getUpdateBy() {
        return updateBy;
    }

    /**
     * 设置更新人
     *
     * @param updateBy 更新人
     */
    public void setUpdateBy(Long updateBy) {
        this.updateBy = updateBy;
    }

    /**
     * 获取更新时间
     *
     * @return update_date - 更新时间
     */
    public Date getUpdateDate() {
        return updateDate;
    }

    /**
     * 设置更新时间
     *
     * @param updateDate 更新时间
     */
    public void setUpdateDate(Date updateDate) {
        this.updateDate = updateDate;
    }
}

导入工具类

package com.edu.Utils;

import com.edu.model.StudentScore;
import org.apache.log4j.Logger;
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.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

public class ImportExcel {
    private static Logger logger  = Logger.getLogger(ImportExcel.class);
    private final static String xls = "xls";
    private final static String xlsx = "xlsx";

    /**
     * 读入excel文件，解析后返回
     * @param file
     * @throws IOException
     */
    public static List<StudentScore> readExcel(MultipartFile file,Integer scoreReportId) throws IOException {
        //检查文件
        checkFile(file);
        //获得Workbook工作薄对象
        Workbook workbook = getWorkBook(file);
        //创建返回对象，把每行中的值作为一个数组，所有行作为一个集合返回
        List<StudentScore> list = new ArrayList<StudentScore>();
        if(workbook != null){
            for(int sheetNum = 0;sheetNum < workbook.getNumberOfSheets();sheetNum++){
                //获得当前sheet工作表
                Sheet sheet = workbook.getSheetAt(sheetNum);
                if(sheet == null){
                    continue;
                }
                //获得当前sheet的开始行
                int firstRowNum  = sheet.getFirstRowNum();
                //获得当前sheet的结束行
                int lastRowNum = sheet.getLastRowNum();
                //循环除了第二行的所有行
                for(int rowNum = firstRowNum+1;rowNum <= lastRowNum;rowNum++){
                    //获得当前行
                    Row row = sheet.getRow(rowNum);
                    if(row == null){
                        continue;
                    }
                    //获得当前行的开始列
                    int firstCellNum = row.getFirstCellNum();
                    //获得当前行的列数
                    int lastCellNum = row.getPhysicalNumberOfCells();
                    String[] cells = new String[row.getPhysicalNumberOfCells()];
                    //循环当前行
                    for(int cellNum = firstCellNum; cellNum < lastCellNum;cellNum++){
                        Cell cell = row.getCell(cellNum);
                        cells[cellNum] = getCellValue(cell);
                    }
                    StudentScore studentScore = excelToObject(cells,scoreReportId);
                    list.add(studentScore);
                }
            }
            workbook.close();
        }
        return list;
    }

    public static void checkFile(MultipartFile file) throws IOException{
        //判断文件是否存在
        if(null == file){
            logger.error("文件不存在！");
            throw new FileNotFoundException("文件不存在！");
        }
        //获得文件名
        String fileName = file.getOriginalFilename();
        //判断文件是否是excel文件
        if(!fileName.endsWith(xls) && !fileName.endsWith(xlsx)){
            logger.error(fileName + "不是excel文件");
            throw new IOException(fileName + "不是excel文件");
        }
    }
    public static Workbook getWorkBook(MultipartFile file) {
        //获得文件名
        String fileName = file.getOriginalFilename();
        //创建Workbook工作薄对象，表示整个excel
        Workbook workbook = null;
        try {
            //获取excel文件的io流
            InputStream is = file.getInputStream();
            //根据文件后缀名不同(xls和xlsx)获得不同的Workbook实现类对象
            if(fileName.endsWith(xls)){
                //2003
                workbook = new HSSFWorkbook(is);
            }else if(fileName.endsWith(xlsx)){
                //2007
                workbook = new XSSFWorkbook(is);
            }
        } catch (IOException e) {
            logger.info(e.getMessage());
        }
        return workbook;
    }
    public static String getCellValue(Cell cell){
        String cellValue = "";
        if(cell == null){
            return cellValue;
        }
        //把数字当成String来读，避免出现1读成1.0的情况
        if(cell.getCellType() == Cell.CELL_TYPE_NUMERIC){
            cell.setCellType(Cell.CELL_TYPE_STRING);
        }
        //判断数据的类型
        switch (cell.getCellType()){
            //数字
            case Cell.CELL_TYPE_NUMERIC:
                cellValue = String.valueOf(cell.getNumericCellValue());
                break;
            //字符串
            case Cell.CELL_TYPE_STRING:
                cellValue = String.valueOf(cell.getStringCellValue());
                break;
            //Boolean
            case Cell.CELL_TYPE_BOOLEAN:
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            //公式
            case Cell.CELL_TYPE_FORMULA:
                cellValue = String.valueOf(cell.getCellFormula());
                break;
            //空值
            case Cell.CELL_TYPE_BLANK:
                cellValue = "";
                break;
            //故障
            case Cell.CELL_TYPE_ERROR:
                cellValue = "非法字符";
                break;
            default:
                cellValue = "未知类型";
                break;
        }
        return cellValue;
    }

    public static StudentScore excelToObject(String[] cells,Integer scoreReportId){
        StudentScore studentScore = new StudentScore();
        studentScore.setScoreReportId(scoreReportId);
        for(int i = 0 ; i<cells.length; i++){
            switch (i){
                case 0:
                    studentScore.setTestPermitNumber(cells[i]);
                    break;
                case 1:
                    studentScore.setMonitorNumber(cells[i]);
                    break;
                case 2:
                    studentScore.setName(cells[i]);
                    break;
                case 3:
                    studentScore.setSchool(cells[i]);
                    break;
                case 4:
                    if(isNumber(cells[i])){
                        studentScore.setChinese(Double.parseDouble(cells[i]));
                    }else {
                        studentScore.setEnglish(Double.parseDouble("0"));
                    }
                    break;
                case 5:
                    if(isNumber(cells[i])){
                        studentScore.setMath(Double.parseDouble(cells[i]));
                    }else {
                        studentScore.setEnglish(Double.parseDouble("0"));
                    }
                    break;
                case 6:
                    if(isNumber(cells[i])){
                        studentScore.setMoralitySociety(Double.parseDouble(cells[i]));
                    }else {
                        studentScore.setEnglish(Double.parseDouble("0"));
                    }
                    break;
                case 7:
                    if(isNumber(cells[i])){
                        studentScore.setScience(Double.parseDouble(cells[i]));
                    }else {
                        studentScore.setEnglish(Double.parseDouble("0"));
                    }
                    break;
                case 8:
                    if(isNumber(cells[i])){
                        studentScore.setEnglish(Double.parseDouble(cells[i]));
                    }else {
                        studentScore.setEnglish(Double.parseDouble("0"));
                    }
                    break;
                default:
                    System.out.println("参数存在问题");
                    break;
            }
        }
        return studentScore;
    }

    public static boolean isNumber(String str){
        String reg = "^[0-9]+(.[0-9]+)?$";
        return str.matches(reg);
    }


}

导入的时候兼容xls和xlsx类型，下图红框内的代码

导出结果如图：

注意这个excel需要分好几个sheet页

实体类代码

package com.edu.model;

import javax.persistence.*;

@Table(name = "school_score")
public class SchoolScore {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;

    /**
     * 学校名称
     */
    @Column(name = "school_name")
    private String schoolName;

    /**
     * 年级
     */
    private String grade;

    /**
     * 成绩单号
     */
    private Integer scoreReportId;

    /**
     * 科目
     */
    private String subject;

    /**
     * 总人数
     */
    @Column(name = "stu_number")
    private Integer stuNumber;

    /**
     * 总分
     */
    private Double score;

    /**
     * 平均分
     */
    @Column(name = "average_score")
    private Double averageScore;

    /**
     * 最高分
     */
    @Column(name = "max_score")
    private Double maxScore;

    /**
     * 最低分
     */
    @Column(name = "min_score")
    private Double minScore;

    /**
     * 及格分
     */
    private Double passScore;

    /**
     * 优秀分
     */
    private Double goodScore;

    /**
     * 及格人数
     */
    @Column(name = "pass_number")
    private Integer passNumber;

    /**
     * 及格率
     */
    @Column(name = "pass_rate")
    private Double passRate;

    /**
     * 优秀人数
     */
    @Column(name = "good_marks_number")
    private Integer goodMarksNumber;

    /**
     * 优秀率
     */
    @Column(name = "good_marks_rate")
    private Double goodMarksRate;

    /**
     * 三项指标分值
     */
    @Column(name = "three_indicators")
    private Double threeIndicators;

    /**
     * 上限
     */
    private Double upperLimit;

    /**
     * 下限
     */
    private Double lowerLimit;

    /**
     * 100
     */
    private Integer hundred;

    /**
     * 90-99
     */
    @Column(name = "ninety_hundred")
    private Integer ninetyHundred;

    /**
     * 80-89
     */
    @Column(name = "eighty_ninety")
    private Integer eightyNinety;

    /**
     * 70-79
     */
    @Column(name = "seventy_eighty")
    private Integer seventyEighty;

    /**
     * 60-69
     */
    @Column(name = "sixty_seventy")
    private Integer sixtySeventy;

    /**
     * 50-59
     */
    @Column(name = "fifty_sixty")
    private Integer fiftySixty;

    /**
     * 40-49
     */
    @Column(name = "forty_fifty")
    private Integer fortyFifty;

    /**
     * 0-39
     */
    @Column(name = "zero_forty")
    private Integer zeroForty;

    /**
     * @return id
     */
    public Integer getId() {
        return id;
    }

    /**
     * @param id
     */
    public void setId(Integer id) {
        this.id = id;
    }

    /**
     * 获取学校名称
     *
     * @return school_name - 学校名称
     */
    public String getSchoolName() {
        return schoolName;
    }

    /**
     * 设置学校名称
     *
     * @param schoolName 学校名称
     */
    public void setSchoolName(String schoolName) {
        this.schoolName = schoolName;
    }

    /**
     * 获取年级
     *
     * @return grade - 年级
     */
    public String getGrade() {
        return grade;
    }

    /**
     * 设置年级
     *
     * @param grade 年级
     */
    public void setGrade(String grade) {
        this.grade = grade;
    }

    /**
     * 获取科目
     *
     * @return subject - 科目
     */
    public String getSubject() {
        return subject;
    }

    /**
     * 设置科目
     *
     * @param subject 科目
     */
    public void setSubject(String subject) {
        this.subject = subject;
    }

    /**
     * 获取总人数
     *
     * @return stu_number - 总人数
     */
    public Integer getStuNumber() {
        return stuNumber;
    }

    /**
     * 设置总人数
     *
     * @param stuNumber 总人数
     */
    public void setStuNumber(Integer stuNumber) {
        this.stuNumber = stuNumber;
    }

    /**
     * 获取总分
     *
     * @return score - 总分
     */
    public Double getScore() {
        return score;
    }

    /**
     * 设置总分
     *
     * @param score 总分
     */
    public void setScore(Double score) {
        this.score = score;
    }

    /**
     * 获取平均分
     *
     * @return average_score - 平均分
     */
    public Double getAverageScore() {
        return averageScore;
    }

    /**
     * 设置平均分
     *
     * @param averageScore 平均分
     */
    public void setAverageScore(Double averageScore) {
        this.averageScore = averageScore;
    }

    /**
     * 获取最高分
     *
     * @return max_score - 最高分
     */
    public Double getMaxScore() {
        return maxScore;
    }

    /**
     * 设置最高分
     *
     * @param maxScore 最高分
     */
    public void setMaxScore(Double maxScore) {
        this.maxScore = maxScore;
    }

    /**
     * 获取最低分
     *
     * @return min_score - 最低分
     */
    public Double getMinScore() {
        return minScore;
    }

    /**
     * 设置最低分
     *
     * @param minScore 最低分
     */
    public void setMinScore(Double minScore) {
        this.minScore = minScore;
    }

    /**
     * 获取及格人数
     *
     * @return pass_number - 及格人数
     */
    public Integer getPassNumber() {
        return passNumber;
    }

    /**
     * 设置及格人数
     *
     * @param passNumber 及格人数
     */
    public void setPassNumber(Integer passNumber) {
        this.passNumber = passNumber;
    }

    /**
     * 获取及格率
     *
     * @return pass_rate - 及格率
     */
    public Double getPassRate() {
        return passRate;
    }

    /**
     * 设置及格率
     *
     * @param passRate 及格率
     */
    public void setPassRate(Double passRate) {
        this.passRate = passRate;
    }

    /**
     * 获取优秀人数
     *
     * @return good_marks_number - 优秀人数
     */
    public Integer getGoodMarksNumber() {
        return goodMarksNumber;
    }

    /**
     * 设置优秀人数
     *
     * @param goodMarksNumber 优秀人数
     */
    public void setGoodMarksNumber(Integer goodMarksNumber) {
        this.goodMarksNumber = goodMarksNumber;
    }

    /**
     * 获取优秀率
     *
     * @return good_marks_rate - 优秀率
     */
    public Double getGoodMarksRate() {
        return goodMarksRate;
    }

    /**
     * 设置优秀率
     *
     * @param goodMarksRate 优秀率
     */
    public void setGoodMarksRate(Double goodMarksRate) {
        this.goodMarksRate = goodMarksRate;
    }

    /**
     * 获取三项指标分值
     *
     * @return three_indicators - 三项指标分值
     */
    public Double getThreeIndicators() {
        return threeIndicators;
    }

    /**
     * 设置三项指标分值
     *
     * @param threeIndicators 三项指标分值
     */
    public void setThreeIndicators(Double threeIndicators) {
        this.threeIndicators = threeIndicators;
    }

    /**
     * 获取100
     *
     * @return hundred - 100
     */
    public Integer getHundred() {
        return hundred;
    }

    /**
     * 设置100
     *
     * @param hundred 100
     */
    public void setHundred(Integer hundred) {
        this.hundred = hundred;
    }

    /**
     * 获取90-99
     *
     * @return ninety_hundred - 90-99
     */
    public Integer getNinetyHundred() {
        return ninetyHundred;
    }

    /**
     * 设置90-99
     *
     * @param ninetyHundred 90-99
     */
    public void setNinetyHundred(Integer ninetyHundred) {
        this.ninetyHundred = ninetyHundred;
    }

    /**
     * 获取80-89
     *
     * @return eighty_ninety - 80-89
     */
    public Integer getEightyNinety() {
        return eightyNinety;
    }

    /**
     * 设置80-89
     *
     * @param eightyNinety 80-89
     */
    public void setEightyNinety(Integer eightyNinety) {
        this.eightyNinety = eightyNinety;
    }

    /**
     * 获取70-79
     *
     * @return seventy_eighty - 70-79
     */
    public Integer getSeventyEighty() {
        return seventyEighty;
    }

    /**
     * 设置70-79
     *
     * @param seventyEighty 70-79
     */
    public void setSeventyEighty(Integer seventyEighty) {
        this.seventyEighty = seventyEighty;
    }

    /**
     * 获取60-69
     *
     * @return sixty_seventy - 60-69
     */
    public Integer getSixtySeventy() {
        return sixtySeventy;
    }

    /**
     * 设置60-69
     *
     * @param sixtySeventy 60-69
     */
    public void setSixtySeventy(Integer sixtySeventy) {
        this.sixtySeventy = sixtySeventy;
    }

    /**
     * 获取50-59
     *
     * @return fifty_sixty - 50-59
     */
    public Integer getFiftySixty() {
        return fiftySixty;
    }

    /**
     * 设置50-59
     *
     * @param fiftySixty 50-59
     */
    public void setFiftySixty(Integer fiftySixty) {
        this.fiftySixty = fiftySixty;
    }

    /**
     * 获取40-49
     *
     * @return forty_fifty - 40-49
     */
    public Integer getFortyFifty() {
        return fortyFifty;
    }

    /**
     * 设置40-49
     *
     * @param fortyFifty 40-49
     */
    public void setFortyFifty(Integer fortyFifty) {
        this.fortyFifty = fortyFifty;
    }

    /**
     * 获取0-39
     *
     * @return zero_forty - 0-39
     */
    public Integer getZeroForty() {
        return zeroForty;
    }

    /**
     * 设置0-39
     *
     * @param zeroForty 0-39
     */
    public void setZeroForty(Integer zeroForty) {
        this.zeroForty = zeroForty;
    }

    public Double getPassScore() {
        return passScore;
    }

    public void setPassScore(Double passScore) {
        this.passScore = passScore;
    }

    public Double getGoodScore() {
        return goodScore;
    }

    public void setGoodScore(Double goodScore) {
        this.goodScore = goodScore;
    }

    public Double getUpperLimit() {
        return upperLimit;
    }

    public void setUpperLimit(Double upperLimit) {
        this.upperLimit = upperLimit;
    }

    public Double getLowerLimit() {
        return lowerLimit;
    }

    public void setLowerLimit(Double lowerLimit) {
        this.lowerLimit = lowerLimit;
    }

    public Integer getScoreReportId() {
        return scoreReportId;
    }

    public void setScoreReportId(Integer scoreReportId) {
        this.scoreReportId = scoreReportId;
    }
}

导出工具类

package com.edu.Utils;

import com.edu.common.Constant;
import com.edu.model.SchoolScore;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.util.CellRangeAddress;

import java.io.IOException;
import java.util.List;

public class ExportExcel {
    /**
     * 创建excel表
     */
    public static HSSFWorkbook CreateExcel(List<SchoolScore> list) throws IOException {
        //创建HSSFWorkbook对象(excel的文档对象)
        HSSFWorkbook wb = new HSSFWorkbook();
        creatSheet(wb,Constant.EXCEL_SUBJECT_NAME_CHINESE,list);

        return wb;
    }

    public static HSSFWorkbook creatSheet(HSSFWorkbook wb,String sheetName,List<SchoolScore> list){
        //建立新的sheet对象（excel的表单）
        HSSFSheet sheet = wb.createSheet(sheetName);
        // 设置列宽
        for(int i = 0 ; i<19;i++){
            if (i==0){
                sheet.setColumnWidth((short) i, (short) 4800);
            }else{
                sheet.setColumnWidth((short) i, (short) 2000);
            }
        }
        //在sheet里创建第一行
        HSSFRow row0=sheet.createRow(0);
        //设置行高
        row0.setHeight((short) ((short) 36*20));
        //创建单元格
        HSSFCell cellTitle=row0.createCell(0);
        //设置单元格样式
        HSSFCellStyle styleTitle = creatStyle(wb, "黑体", 20, HSSFCellStyle.ALIGN_CENTER,false,true);
        cellTitle.setCellStyle(styleTitle);
        //设置单元格内容
        cellTitle.setCellValue(Constant.EXCEL_TITLE);
        //合并单元格CellRangeAddress构造参数依次表示起始行，截至行，起始列， 截至列
        sheet.addMergedRegion(new CellRangeAddress(0,0,0,18));

        //第二行
        HSSFRow row1 =sheet.createRow(1);
        row1.setHeight((short) ((short) 22*20));
        HSSFCell cell1_1 = row1.createCell(0);
        HSSFCellStyle styleContent1 = creatStyle(wb, "宋体", 12, HSSFCellStyle.ALIGN_CENTER,false,false);
        cell1_1.setCellStyle(styleContent1);
        cell1_1.setCellValue("年级:六年级");
        //
        HSSFCell cellSubject= row1.createCell(1);
        HSSFCellStyle styleContent2 = creatStyle(wb, "宋体", 12, HSSFCellStyle.ALIGN_RIGHT,false,false);
        cellSubject.setCellStyle(styleContent2);
        cellSubject.setCellValue("科目：" + sheetName);
        sheet.addMergedRegion(new CellRangeAddress(1,1,1,18));
        //
        HSSFCellStyle styleHead = creatStyle(wb, "宋体", 12, HSSFCellStyle.ALIGN_CENTER,true,true);
        //第三行
        HSSFRow row2 =sheet.createRow(2);
        row2.setHeight((short) ((short) 49*20));
        HSSFCell cell2_0 = row2.createCell(0);
        cell2_0.setCellStyle(styleHead);
        cell2_0.setCellValue("学   校");
        HSSFCell cell2_1 = row2.createCell(1);
        cell2_1.setCellStyle(styleHead);
        cell2_1.setCellValue("总人数");
        HSSFCell cell2_2 = row2.createCell(2);
        cell2_2.setCellStyle(styleHead);
        cell2_2.setCellValue("总分");
        HSSFCell cell2_3 = row2.createCell(3);
        cell2_3.setCellStyle(styleHead);
        cell2_3.setCellValue("平均分");
        HSSFCell cell2_4 = row2.createCell(4);
        cell2_4.setCellStyle(styleHead);
        cell2_4.setCellValue("最高分");
        HSSFCell cell2_5 = row2.createCell(5);
        cell2_5.setCellStyle(styleHead);
        cell2_5.setCellValue("最低分");
        HSSFCell cell2_6 = row2.createCell(6);
        cell2_6.setCellStyle(styleHead);
        cell2_6.setCellValue("合格人数");
        HSSFCell cell2_7 = row2.createCell(7);
        cell2_7.setCellStyle(styleHead);
        cell2_7.setCellValue("合格率（%）");
        HSSFCell cell2_8 = row2.createCell(8);
        cell2_8.setCellStyle(styleHead);
        cell2_8.setCellValue("优秀人数");
        HSSFCell cell2_9 = row2.createCell(9);
        cell2_9.setCellStyle(styleHead);
        cell2_9.setCellValue("优秀率（%）");
        HSSFCell cell2_10 = row2.createCell(10);
        cell2_10.setCellStyle(styleHead);
        cell2_10.setCellValue("三项指标分值");
        HSSFCell cell2_11 = row2.createCell(11);
        cell2_11.setCellStyle(styleHead);
        cell2_11.setCellValue("100");
        HSSFCell cell2_12 = row2.createCell(12);
        cell2_12.setCellStyle(styleHead);
        cell2_12.setCellValue("90-99");
        HSSFCell cell2_13 = row2.createCell(13);
        cell2_13.setCellStyle(styleHead);
        cell2_13.setCellValue("80-89");
        HSSFCell cell2_14 = row2.createCell(14);
        cell2_14.setCellStyle(styleHead);
        cell2_14.setCellValue("70-79");
        HSSFCell cell2_15 = row2.createCell(15);
        cell2_15.setCellStyle(styleHead);
        cell2_15.setCellValue("60-69");
        HSSFCell cell2_16 = row2.createCell(16);
        cell2_16.setCellStyle(styleHead);
        cell2_16.setCellValue("50-59");
        HSSFCell cell2_17 = row2.createCell(17);
        cell2_17.setCellStyle(styleHead);
        cell2_17.setCellValue("40-49");
        HSSFCell cell2_18 = row2.createCell(18);
        cell2_18.setCellStyle(styleHead);
        cell2_18.setCellValue("0-39");

        //第四行
        HSSFCellStyle styleContent = creatStyle(wb, "宋体", 12, HSSFCellStyle.ALIGN_CENTER,true,false);
        int rowNumber = 3;
        for (SchoolScore schoolScore : list) {
            HSSFRow row3 =sheet.createRow(rowNumber);
            row3.setHeight((short) ((short) 49*20));
            //
            HSSFCell cell3_0 = row3.createCell(0);
            cell3_0.setCellStyle(styleContent);
            cell3_0.setCellValue(schoolScore.getSchoolName());
            //
            HSSFCell cell3_1 = row3.createCell(1);
            cell3_1.setCellStyle(styleContent);
            cell3_1.setCellValue(schoolScore.getStuNumber());
            //
            HSSFCell cell3_2 = row3.createCell(2);
            cell3_2.setCellStyle(styleContent);
            cell3_2.setCellValue(schoolScore.getScore());
            //
            HSSFCell cell3_3 = row3.createCell(3);
            cell3_3.setCellStyle(styleContent);
            cell3_3.setCellValue(schoolScore.getAverageScore());
            //
            HSSFCell cell3_4 = row3.createCell(4);
            cell3_4.setCellStyle(styleContent);
            cell3_4.setCellValue(schoolScore.getMaxScore());
            //
            HSSFCell cell3_5 = row3.createCell(5);
            cell3_5.setCellStyle(styleContent);
            cell3_5.setCellValue(schoolScore.getMinScore());
            //
            HSSFCell cell3_6 = row3.createCell(6);
            cell3_6.setCellStyle(styleContent);
            cell3_6.setCellValue(schoolScore.getPassNumber());
            //
            HSSFCell cell3_7 = row3.createCell(7);
            cell3_7.setCellStyle(styleContent);
            cell3_7.setCellValue(schoolScore.getPassRate());
            //
            HSSFCell cell3_8 = row3.createCell(8);
            cell3_8.setCellStyle(styleContent);
            cell3_8.setCellValue(schoolScore.getGoodMarksNumber());
            //
            HSSFCell cell3_9 = row3.createCell(9);
            cell3_9.setCellStyle(styleContent);
            cell3_9.setCellValue(schoolScore.getGoodMarksRate());
            //
            HSSFCell cell3_10 = row3.createCell(10);
            cell3_10.setCellStyle(styleContent);
            cell3_10.setCellValue(schoolScore.getThreeIndicators());
            //
            HSSFCell cell3_11 = row3.createCell(11);
            cell3_11.setCellStyle(styleContent);
            cell3_11.setCellValue(schoolScore.getHundred());
            //
            HSSFCell cell3_12 = row3.createCell(12);
            cell3_12.setCellStyle(styleContent);
            cell3_12.setCellValue(schoolScore.getNinetyHundred());
            //
            HSSFCell cell3_13 = row3.createCell(13);
            cell3_13.setCellStyle(styleContent);
            cell3_13.setCellValue(schoolScore.getEightyNinety());
            //
            HSSFCell cell3_14 = row3.createCell(14);
            cell3_14.setCellStyle(styleContent);
            cell3_14.setCellValue(schoolScore.getSeventyEighty());
            //
            HSSFCell cell3_15 = row3.createCell(15);
            cell3_15.setCellStyle(styleContent);
            cell3_15.setCellValue(schoolScore.getSixtySeventy());
            //
            HSSFCell cell3_16 = row3.createCell(16);
            cell3_16.setCellStyle(styleContent);
            cell3_16.setCellValue(schoolScore.getFiftySixty());
            //
            HSSFCell cell3_17 = row3.createCell(17);
            cell3_17.setCellStyle(styleContent);
            cell3_17.setCellValue(schoolScore.getFortyFifty());
            //
            HSSFCell cell3_18 = row3.createCell(18);
            cell3_18.setCellStyle(styleContent);
            cell3_18.setCellValue(schoolScore.getZeroForty());
            //
            rowNumber++;
        }
        //最后一行
        HSSFRow rowLast =sheet.createRow(rowNumber);
        rowLast.setHeight((short) ((short) 22*20));
        HSSFCell cellLast = rowLast.createCell(0);
        HSSFCellStyle styleLast = creatStyle(wb, "宋体", 12, HSSFCellStyle.ALIGN_CENTER,false,false);
        cellLast.setCellStyle(styleContent1);
        cellLast.setCellValue("备注：六年级监测科目语文(100分)、数学(100分)、品社(30分)、科学（30分）、英语（40分）五科，总分300分。此成绩只作为教学评价，不作为学校考核。");
        sheet.addMergedRegion(new CellRangeAddress(rowNumber,rowNumber,0,18));
        return wb;
    }

    /**
     *
     * @param wb
     * @param fontName 字体名称
     * @param fontHeightInPoints 字体大小
     * @param alignment 水平位置
     * @param border 是否有边框
     * @return
     */
    public static HSSFCellStyle creatStyle(HSSFWorkbook wb, String fontName, int fontHeightInPoints,short alignment,boolean border,boolean boldweight){
        //设置内容字体
        HSSFFont fontContent = wb.createFont();
        if (boldweight){
            // 字体加粗
            fontContent.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        }
        fontContent.setFontName(fontName);
        fontContent.setFontHeightInPoints((short) fontHeightInPoints);
        //创建内容样式
        HSSFCellStyle styleContent = wb.createCellStyle();
        styleContent.setFont(fontContent);
        styleContent.setWrapText(true);
        styleContent.setAlignment(alignment);
        styleContent.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        if (border){
            styleContent.setBorderBottom(HSSFCellStyle.BORDER_THIN);
            styleContent.setBottomBorderColor(HSSFColor.BLACK.index);
            styleContent.setBorderLeft(HSSFCellStyle.BORDER_THIN);
            styleContent.setLeftBorderColor(HSSFColor.BLACK.index);
            styleContent.setBorderRight(HSSFCellStyle.BORDER_THIN);
            styleContent.setRightBorderColor(HSSFColor.BLACK.index);
            styleContent.setBorderTop(HSSFCellStyle.BORDER_THIN);
            styleContent.setTopBorderColor(HSSFColor.BLACK.index);
        }
        return styleContent;
    }
}

controller

package com.edu.controller;

import com.edu.Utils.ExportExcel;
import com.edu.Utils.ImportExcel;
import com.edu.common.Constant;
import com.edu.model.SchoolScore;
import com.edu.model.StudentScore;
import com.edu.service.ScoreReportService;
import com.edu.service.StudentScoreService;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.util.List;

@Controller
public class IndexController {
    @Autowired
    private ScoreReportService scoreReportService;
    @Autowired
    private StudentScoreService studentScoreService;

    @RequestMapping("/importExcel")
    public String importExcel(@RequestParam("file")MultipartFile file,@RequestParam("id")Integer scoreReportId, HttpServletRequest request) throws IOException {
        List<StudentScore> list = ImportExcel.readExcel(file,scoreReportId);
        studentScoreService.insertStudentScores(list);
        return "redirect:/index";
    }

    @RequestMapping(value = "/exportExcel/{scoreReportId}")
    public void exportExcel(HttpServletResponse response,@PathVariable("scoreReportId")Integer scoreReportId) throws IOException {
        HSSFWorkbook wb = new HSSFWorkbook();
        //三科综合
        String three = Constant.SUBJECT_NAME_ENGLISH + "+" + Constant.SUBJECT_NAME_MORALITYSOCIETY + "+" + Constant.SUBJECT_NAME_SCIENCE;
        creatSheet(scoreReportId,wb,three,"三科综合");
        //两科综合
        String two = Constant.SUBJECT_NAME_CHINESE + "+" + Constant.SUBJECT_NAME_MATH;
        creatSheet(scoreReportId,wb,two,"两科综合");
        //语文
        creatSheet(scoreReportId,wb,Constant.SUBJECT_NAME_CHINESE,Constant.EXCEL_SUBJECT_NAME_CHINESE);
        //英语
        creatSheet(scoreReportId,wb,Constant.SUBJECT_NAME_ENGLISH,Constant.EXCEL_SUBJECT_NAME_ENGLISH);
        //数学
        creatSheet(scoreReportId,wb,Constant.SUBJECT_NAME_MATH,Constant.EXCEL_SUBJECT_NAME_MATH);
        //品社
        creatSheet(scoreReportId,wb,Constant.SUBJECT_NAME_MORALITYSOCIETY,Constant.EXCEL_SUBJECT_NAME_MORALITYSOCIETY);
        //科学
        creatSheet(scoreReportId,wb,Constant.SUBJECT_NAME_SCIENCE,Constant.EXCEL_SUBJECT_NAME_SCIENCE);
        //输出Excel文件
        OutputStream output=response.getOutputStream();
        response.reset();
        response.setHeader("Content-disposition", "attachment; filename=details.xls");
        response.setContentType("application/msexcel");
        wb.write(output);
        output.close();
    }

    public void creatSheet(Integer scoreReportId,HSSFWorkbook wb, String subject, String sheetName){
        SchoolScore schoolScore = new SchoolScore();
        schoolScore.setScoreReportId(scoreReportId);
        schoolScore.setSubject(subject);
        List<SchoolScore> list = scoreReportService.creatSchoolScore(schoolScore);
        ExportExcel.creatSheet(wb, sheetName,list);
    }
}