基于POI导出导入表格,支持复杂表格及特定要求

已于 2023-02-14 10:38:46 修改
阅读量481 收藏
点赞数 1
分类专栏： excel导出导入 文章标签： excel poi
于 2021-02-09 19:48:10 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/m0_46360888/article/details/113774786
版权 
package com.tedu.controller;

import com.tedu.entity.Student;
import com.tedu.service.StudentService;
import com.tedu.util.Common;
import com.tedu.util.ResponseObj;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * TODO
 *
 * @author syl
 */
@RestController
@RequestMapping("/")
@Api("测试导入导出")
@Slf4j
public class ExportImportController {
    /**
     * 日志
     */
    public static final Logger LOG= LoggerFactory.getLogger(ExportImportController.class);



    @Autowired
    private StudentService studentService;

    private  List<String> list;

    @RequestMapping(value = "importData",method = RequestMethod.POST)
    @ApiOperation("导入数据")
    public ResponseObj<Void> importData(@RequestParam MultipartFile file) throws IOException {
        //获取输入流
        InputStream in = file.getInputStream();
        //校验文件格式
        String filename = file.getOriginalFilename();
        String suffix = filename.substring(filename.lastIndexOf("."));
        Workbook work;
        if(Common.Excel.EXCEL_2003.equals(suffix) ){
            work=new HSSFWorkbook(in);
        }else if(Common.Excel.EXCEL_2007.equals(suffix)){
            work=new XSSFWorkbook(in);
        }else {
            log.error("文件格式不对");
            return ResponseObj.fail("500","文件格式不对");
        }
        //关闭流
        in.close();

        //解析表格
        List<List<String>> liAll = parseExcel(work);

        //List<String>转成List<Student>
        List<Student> list=new ArrayList<>();
        for(int i=Common.Number.ZERO;i<liAll.size();i++){
            Student student=new Student();
            List<String> stu = liAll.get(i);
            student.setSno((stu.get(Common.Number.ZERO)));
            student.setClass1((stu.get(Common.Number.ONE)));
            student.setName((stu.get(Common.Number.TWO)));
            student.setSex((stu.get(Common.Number.THREE)));
            student.setChinese((stu.get(Common.Number.FOUR)));
            student.setMath((stu.get(Common.Number.FIVE)));
            student.setEnglish((stu.get(Common.Number.SIX)));
            list.add(student);
        }
        //调用批量新增方法
        int success= studentService.insert(list);
        return ResponseObj.success("200", "成功新增"+success+"行");
    }

    /**
     * 解析表格
     * @param work
     * @return
     */
    private List<List<String>> parseExcel(Workbook work) {
        //表
        Sheet sheet;
        //行
        Row row;
        //单元格
        Cell cell;
        //总行数
        int rowCount;
        //存放导入的数据
        List<List<String>> liAll=new ArrayList<>();
        for (int i = Common.Number.ZERO; i<work.getNumberOfSheets(); i++){
            //获取每一个表
            sheet=work.getSheetAt(i);
            if(sheet==null){
                continue;
            }
            rowCount=sheet.getPhysicalNumberOfRows();
            //获取表中数据
            for (int j=sheet.getFirstRowNum()+Common.Number.TWO;j<rowCount;j++){
                row = sheet.getRow(j);
                if(!isEmptyRow(row)){
                    //存放每行数据
                    List<String> li=new ArrayList<>();
                    for (int k=row.getFirstCellNum(); k <row.getLastCellNum();k++){
                        cell = row.getCell(k);
                        String value= getCellValue(cell).toString();
                        li.add(value);
                    }
                    liAll.add(li);
                }
                else if(isEmptyRow(row)){
                    continue;
                }
            }
        }
        return liAll;
    }

    /**
     * 判断是否为空行
     * @param row
     * @return
     */
    private boolean isEmptyRow(Row row) {
        Cell cell;
        for (int i=row.getFirstCellNum();i<row.getLastCellNum();i++){
            cell=row.getCell(i);
            if (cell!=null && !cell.getCellTypeEnum().equals(CellType.BLANK)){
                return false;
            }
        }
        return true;
    }

    /**
     * 描述：对表格中数值进行格式化
     *
     * @param cell
     *
     * @return
     */
    public Object getCellValue(Cell cell) {
        String result = new String();
        switch (cell.getCellType()) {
            //Excel公式
            case FORMULA:
                try {
                    result = String.valueOf(cell.getNumericCellValue());
                } catch (IllegalStateException e) {
                    result = String.valueOf(cell.getRichStringCellValue());
                }
                break;
            // 数字类型
            case NUMERIC:
                // 处理日期格式、时间格式
                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                    SimpleDateFormat sdf;
                    if (cell.getCellStyle().getDataFormat() == HSSFDataFormat
                            .getBuiltinFormat("h:mm")) {
                        sdf = new SimpleDateFormat("HH:mm");
                    } else {// 日期
                        sdf = new SimpleDateFormat("yyyy-MM-dd");
                    }
                    Date date = cell.getDateCellValue();
                    result = sdf.format(date);
                } else if (cell.getCellStyle().getDataFormat() == 58) {
                    // 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)
                    SimpleDateFormat sdf = new SimpleDateFormat("M月d日");
                    double value = cell.getNumericCellValue();
                    Date date = org.apache.poi.ss.usermodel.DateUtil
                            .getJavaDate(value);
                    result = sdf.format(date);
                } else {
                    double value = cell.getNumericCellValue();
                    CellStyle style = cell.getCellStyle();
                    DecimalFormat format = new DecimalFormat();
                    String temp = style.getDataFormatString();
                    // 单元格设置成常规
                    if (temp.equals("General")) {
                        format.applyPattern("#.##");
                    }
                    result = format.format(value);
                }
                break;
            // String类型
            case STRING:
                result = cell.getRichStringCellValue().toString();
                break;
            case BLANK:
                result = "";
            default:
                result = "";
                break;
        }
        return result;
    }

    public String getFormat(String str) {
        if(str.equals("null")) {
            str="";
            return str;
        }else{
            return str;
        }
    }
    public Integer getFormats(Integer str) {
        if(str==null) {
            str=0;
            return str;
        }else{
            return str;
        }
    }

    /**
     * 获取字符串中的数字订单号、数字金额等，如从"USD 374.69"中获取到374.69、从“交易单号：123456789”获取到123456789
     *
     * @return
     */
    public static String getFormatNumber(String str){
        str = str.trim();
        Pattern p = Pattern.compile("[0-9]");
        int indexNum = 0;
        int lenght = str.length();
        String num = "";
        for(int i=0;i<lenght;i++){
            num += str.charAt(i);
            Matcher m = p.matcher(num);
            if(m.find()){
                indexNum = i;
                break;
            }
        }
        String formatNumber = str.substring(indexNum,lenght);
        return formatNumber;
    }


    /**
     * 导出
     * @param response
     * @throws Exception
     * @apiNote 输出到网页必须在 @RequestMapping添加produces = "application/octet-stream",不然表格导出异常
     */
    @RequestMapping(value = "exportData", method = RequestMethod.POST,produces = "application/octet-stream")
    @ApiOperation("导出数据")
    public void  exportData(HttpServletResponse response){
        inti();
        //创建工作簿
        XSSFWorkbook wk=new XSSFWorkbook();
        //创建表
        XSSFSheet sheet = wk.createSheet("zyp");
        //设置首行
        setFirstRow(sheet,wk);
        //设置第二行
        setSecondRow(sheet,wk);
        //设置主体
        setBody(sheet,wk);
        //输出到磁盘
        //OutputStream out= new BufferedOutputStream(new FileOutputStream("H://zyp.xlsx"));
        OutputStream out= null;
        try {
            out = response.getOutputStream();
            //输出到网页
            response.reset();
            response.setContentType("application/octet-stream; charset=utf-8");
            response.setHeader("Content-Disposition", "attachment; filename=student.xlsx");
            wk.write(out);
            out.flush();
        } catch (IOException e) {
            log.error(e.getMessage());
        }finally {
            //保证输出流关闭
            try {
                if (out!=null){
                    out.close();
                }
            } catch (IOException e) {
                log.error(e.getMessage());
            }
        }


    }

    /**
     * 设置首行
     * @param sheet
     * @param wk
     */
    private void setFirstRow(XSSFSheet sheet,XSSFWorkbook wk){
        XSSFCellStyle cellStyle=setCellStyle(wk, Common.Number.ZERO, Common.Number.ONE_NEGATIVE);
        //合并单元格
        sheet.addMergedRegion(new CellRangeAddress(Common.Number.ZERO, Common.Number.ZERO, Common.Number.ZERO, Common.Number.EIGHT));
        XSSFRow row_0 = sheet.createRow(Common.Number.ZERO);
        row_0.setHeightInPoints(Common.Number.FOURTH);
        XSSFCell cell_0 = row_0.createCell(Common.Number.ZERO);
        cell_0.setCellValue("学生成绩表");
        cell_0.setCellStyle(cellStyle);
    }

    /**
     * 设置第二行
     * @param sheet
     * @param wk
     */
    private void setSecondRow(XSSFSheet sheet, XSSFWorkbook wk){
        XSSFRow row_1 = sheet.createRow(Common.Number.ONE);
        //设置指定行高
        row_1.setHeightInPoints(Common.Number.TWENTY_FIVE);
        XSSFCell cell;
        XSSFCellStyle cellStyle;
        for(int i = Common.Number.ZERO;i <list.size();i++){
            cell=row_1.createCell(i);
            cell.setCellValue(list.get(i));
            cellStyle=setCellStyle(wk,Common.Number.ONE,Common.Number.ONE_NEGATIVE);
            cell.setCellStyle(cellStyle);
        }
    }

    /**
     * 设置主体
     * @param sheet
     * @param wk
     * @throws Exception
     */
    private void setBody(XSSFSheet sheet, XSSFWorkbook wk){
        List<Student> records = studentService.findAll(new Student(), Common.Number.ONE, Common.Number.ONE_HUNDERD).getRecords();
        XSSFRow row;
        XSSFCell cell;
        XSSFCellStyle style;
        List<String> stuList;
        int total;
        for (int i=Common.Number.ZERO;i<records.size();i++){
            row = sheet.createRow(i + Common.Number.TWO);
            row.setHeightInPoints(Common.Number.FIFTEEN);
            for (int j=Common.Number.ZERO;j<list.size();j++){
                cell = row.createCell(j);
                stuList=getDetails(records.get(i));
                cell.setCellValue(stuList.get(j));
                //三门成绩大于240为优秀
                total=Integer.parseInt(stuList.get(Common.Number.FOUR))+Integer.parseInt(stuList.get(Common.Number.FIVE))+Integer.parseInt(stuList.get(Common.Number.SIX));
                if (total>=Common.Number.TWO_HUNDRED_AND_FORTY){
                    style= setCellStyle(wk, i+Common.Number.TWO,Common.Number.ONE_NEGATIVE);
                    cell.setCellStyle(style);
                }else {
                    style= setCellStyle(wk, i+Common.Number.TWO,Common.Number.ZERO);
                    cell.setCellStyle(style);
                }
                //忽略数字转文本错误
                sheet.addIgnoredErrors(new CellReference(cell),IgnoredErrorType.NUMBER_STORED_AS_TEXT);
                //设置指定列宽
                sheet.setColumnWidth(Common.Number.SEVEN, Common.Number.TWENTY_FIVE * Common.Number.TWO_HUNDRED_AND_FIFTY_SIX);
                sheet.setColumnWidth(Common.Number.EIGHT,Common.Number.TWENTY_FIVE * Common.Number.TWO_HUNDRED_AND_FIFTY_SIX);
            }
        }
    }


    private List<String> getDetails(Student s) {
        List<String> list=new ArrayList<>();
        list.add(s.getSno());
        list.add(s.getClass1());
        list.add(s.getName());
        list.add(s.getSex());
        list.add(s.getChinese());
        list.add(s.getMath());
        list.add(s.getEnglish());
        list.add(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(s.getCreateTime()));
        list.add(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(s.getUpdateTime()));
        return list;
    }


    /**
     * 设置样式
     * @param workbook
     * @param rowId -1表示全部
     * @param columnId -1表示全部
     */
    private XSSFCellStyle setCellStyle(XSSFWorkbook workbook,int rowId,int columnId){
        XSSFCellStyle cellStyle = workbook.createCellStyle();
        //水平居中
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        //垂直居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        //上边框
        cellStyle.setBorderTop(BorderStyle.THIN);
        //下边框
        cellStyle.setBorderBottom(BorderStyle.THIN);
        //左边框
        cellStyle.setBorderLeft(BorderStyle.THIN);
        //右边框
        cellStyle.setBorderRight(BorderStyle.THIN);
        //设置字体
        XSSFFont font = workbook.createFont();
        font.setFontName("宋体");
        //设置样式
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        //设置首行样式
        if (rowId==Common.Number.ZERO && columnId==Common.Number.ONE_NEGATIVE){
            //设置背景
            cellStyle.setFillForegroundColor(IndexedColors.BLUE.getIndex());
            font.setBold(true);
        }
        //设置第二行样式
        if (rowId==Common.Number.ONE && columnId==Common.Number.ONE_NEGATIVE) {
            //设置背景
            cellStyle.setFillForegroundColor(IndexedColors.ORANGE.getIndex());
            font.setBold(true);
        }
        //设置主体样式
        if (rowId>Common.Number.ONE && columnId==Common.Number.ONE_NEGATIVE){
            //设置背景
            cellStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
        }else if(rowId>Common.Number.ONE && columnId==Common.Number.ZERO){
            cellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        }
        cellStyle.setFont(font);
        //设置自动换行
        cellStyle.setWrapText(true);
        return cellStyle;
    }

    private void inti() {
        list = new ArrayList<>();
        list.add("学号");
        list.add("班级");
        list.add("姓名");
        list.add("性别");
        list.add("语文");
        list.add("数学");
        list.add("英语");
        list.add("创建时间");
        list.add("修改时间");
    }
}
POI导出复杂格式EXCEL（动态创建表及赋值）
懒散的蜗牛
11-02 829
因为数据结构比较复杂，就选用了比较原始的方法来实现excel得导出，借此机会做一个小总结，希望能帮到你。。 具体的结构是这样的： { a:1, b:2, {} [ {}, {}, ... ], { [ {}, {}, ... ] } } 代码如下： public void export(Ht
Springboot整合poi +vue实现导出导入Excle表格数据展示图形
shshhshshh的博客
11-29 2628
Springboot整合poi +vue实现导出导入Excle表格数据展示图形
java POI 导出复杂要求的表格
04-17
NULL 博文链接：https://zhaining123129-gmail-com.iteye.com/blog/1832560
POI 导出 复杂excel ，根据 需要导出excel初版，除了特殊的字符没处理 还有个别单元格没合并，其他基本完事
ywj776199845的专栏
06-05 244
package tdh.platform.ofc.admin.interceptor; import org.apache.poi.hssf.usermodel.*; import org.apache.poi.ss.util.CellRangeAddress; import org.springframework.stereotype.Service; import tdh.platform.ofc.dto.sale.CalcDataConfrimResponse; import java.util.
poi导出复杂excel
11-21
根据excel模板，拼接出复杂表头（合并行、和并列）的excel，并且能够导出。亲测可以直接运行，内含jar包。如有问题，可私信csdn
java实现excel的导入导出（poi详解）[转]
weixin_34318272的博客
08-28 1917
java实现excel的导入导出（poi详解） 博客分类：   java技术 excel导出poijava    经过两天的研究，现在对excel导出有点心得了。我们使用的excel导出的jar包是poi这个阿帕奇公司的一个项目，后来被扩充了。是比较好用的excel导出工具。 下面来认识一下这个它吧。 我们知道要创建一张excel你得知道excel由什么组成，比如说...
excel poi复杂导出
qq_35226176的博客
12-15 421
业务代码 代码中可把获取数据的调用换成 List直接使用 public String export(MakingOtherfeeDisForSubjectQvo parameter) { //获取数据 List<OreGradeBudgetVo> oreGradeBudgetVoList = this.selectPageList(parameter); Workbook wb = new HSSFWorkbook(); Sheet
复杂多表头excel表格的导出及布局，支持拓展
02-22
本知识点将深入探讨如何在Excel中创建和导出复杂多表头的表格，并支持进一步的拓展。 首先，多表头的设置是为了更好地分类和展示数据。在Excel中，可以利用合并单元格和嵌套表格头来实现这一目标。例如，对于一个...
poi表格导入导出
09-03
在这个特定的场景中，我们关注的是使用POI进行Excel文件的导入与导出操作。Excel文件在数据处理、报表生成以及数据分析等方面有着广泛的应用，因此掌握如何用POI来读写Excel文件是一项实用的技能。 首先，让我们...
poi导入导出Excel表格的所以jar包
12-18
标题提到的"poi导入导出Excel表格的所以jar包"指的是使用Apache POI库进行Excel操作所需的依赖库。 1. **poi-3.8-20120326.jar**: 这是Apache POI的主要核心库，包含了处理Excel文件的核心类和方法。例如，你可以...
使用poi读取、写入复杂excel工具类（包含样式）
10-25
使用poi读取写入复杂excel内容包括样式，工具类
poi复杂导入导出demo
05-05
此poi导出的demo可以导出，不同模板，不同字段长度，多个sheet页，动态生成poi模板，poi复杂导出
java poi 导入导出多个sheet 的excel数据
热门推荐
fk478561641的博客
06-30 2万+
首先要使用java poi要导入相应的jar。导入：HSSFWorkbook workbook=new HSSFWorkbook(new FileInputStream(new File("D:/test.xls")));        HSSFSheet sheet=null;        List&lt;String&gt; sheetNameList = new ArrayList&lt;...
POI实现excel的导入和导出
郭叶兵的博客
07-28 361
03最多是65536 xls 对象HSSFWorkbook 07没有限制 xlsx 对象XSSFWorkbook 升级版SXSSFWorkbook 工作博 工作表sheet 行 列 从0开始 <!--xls(03)--> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version&.
java 使用POI导入复杂excel表格
weixin_44018367的博客
11-19 2304
java 使用POI导入复杂excel表格
java使用原生poi导出复杂Excel
weixin_42268572的博客
09-28 2142
POIExcel文件导出 思路: 现在很多Excel导出都有Excel模板,直接将数据封装好,直接调用即可, 但是我们项目变动较大,所以我选择使用原生POI进行文件导出,方面后期改动灵活, 也可选用固定Template模板,读取固定表头,写入数据... POM依赖配置 POM文件配置: 这里选用的是3.14版本 <!-- poi 文件上传,解析excel--> <dependency> <groupId>org.apache.poi
POI导出复杂表格
最新发布
weixin_46295712的博客
04-03 300
重生之学习java如同49年入国军
POI 导出复杂表格（提供思路）
我不是程序员的博客
08-01 497
导出表格之前必须要理解的几个概念 ps:理解了原理可以自由发挥写出自己想要的表格 工作蒲： HSSFWorkbook：是操作Excel2003以前（包括2003）的版本，扩展名是.xls ，行数有限，最大为65535，由于数量有限因此一般不会发生OOM（内存不足的异常） XSSFWorkbook:是操作Excel2007的版本，扩展名是.xlsx，数据量无限，有可能会出现OOM内存溢出异常 // 新建一个工作蒲 HSSFWorkbook workbook = new HSSFWorkbook();
java中poi导出导入excel
weixin_43744113的博客
09-23 367
excel导出方法 jar包：easypoi-base public static void exportExcel4Map(List<LinkedHashMap<String, Object>> rows, String sheetName, HttpServletResponse response) { try { if (CollectionUtils.isEmpty(rows)) { rows.addAll(EM
2小时掌握Apache POI实现报表导入导出
资源摘要信息:"2小时学会POI报表导入导出功能" Apache POI（Poor Obfuscation Implementation）是一个开放源码的Java函式库，其主要功能是为Java程序提供读写Microsoft Office文件格式的API，使得Java应用程序能够...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值