java Excel导入导出工具类

Wesley13
• 阅读 322

本文章，导入导出依赖提前定义好的模板

package com.jd.nb.wishplat.man.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.Date;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.lang.ArrayUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
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.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.context.request.RequestContextHolder;
import org.springframework.web.context.request.ServletRequestAttributes;
import org.springframework.web.multipart.MultipartFile;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
/**
 * 
 * @author zhenwei.shi
 *
 */
public class ImpAndExpExcel {
   
    public static JSONArray doImpXlsx(MultipartFile file, String[] fields, String[] requiredFields, Integer docReadStartRowIndex) throws IOException {
        XSSFWorkbook wb = new XSSFWorkbook(file.getInputStream());
        
        JSONArray jsonArray = new JSONArray();        
        int startRowIndex = (null == docReadStartRowIndex?2:docReadStartRowIndex);
        
        XSSFSheet sheet = wb.getSheetAt(0);
        
        // 遍历所有行记录，sheet.getLastRowNum()获取的是最后一行的index
        for (int startRow = startRowIndex; startRow <= sheet.getLastRowNum(); startRow++) {
            // 遍历记录所有列
            JSONObject jsonObj = new JSONObject();
            boolean isErrorObj = false;
            
            for (int columnIndex = 0; columnIndex < fields.length; columnIndex++) {
                XSSFCell nowCell = getXssfCell(sheet, startRow, columnIndex);
                String cellValue = getXssfCellValue(nowCell);
                if(isRequired(fields[columnIndex],requiredFields) && StringUtils.isEmpty(cellValue)){
                    isErrorObj = true;
                    break;
                }
                jsonObj.put(fields[columnIndex], cellValue);
            }
            
            if(!isErrorObj){
                jsonArray.add(jsonObj);
            }
        }
        wb.close();
        return jsonArray;
    }
    
    /**
     * 导入03版Excel .xls
     * 依据上传文件返还JSON数组对象，JSON属性为heads
     * @param file 导入的文件
     * @param heads 定义对象的列名
     * @param rowStartIndex 从第几行开始读取
     * @return
     * @throws IOException 
     */
    @SuppressWarnings("resource")
    public static JSONArray doImpXls(MultipartFile file, String[] fields, Integer docReadStartRowIndex) throws IOException {
        POIFSFileSystem fs = new POIFSFileSystem(file.getInputStream());
        HSSFWorkbook wb = new HSSFWorkbook(fs);
        
        JSONArray jsonArray = new JSONArray();        
        int startRowIndex = (null == docReadStartRowIndex?2:docReadStartRowIndex);
        
        HSSFSheet sheet = wb.getSheetAt(0);//只导入sheet第一页
            
        // 遍历所有行记录，sheet.getLastRowNum()获取的是最后一行的index
        for (int startRow = startRowIndex; startRow <= sheet.getLastRowNum(); startRow++) {
            // 遍历记录所有列
            JSONObject jsonObj = new JSONObject();
            boolean isBlankObj = true;
            for (int columnIndex = 0; columnIndex < fields.length; columnIndex++) {
                HSSFCell nowCell = getHssfCell(sheet, startRow, columnIndex, true);
                String value = getHssfCellStringValue(nowCell);
                if(null!=value){
                    value=value.trim();
                }
                jsonObj.put(fields[columnIndex], value);
                if(!StringUtils.isEmpty(value) && !"0".equals(value)){
                    isBlankObj = false;
                }
            }
            
            if(!isBlankObj){
                jsonArray.add(jsonObj);
            }
        }
        return jsonArray;
    }

    /**
     * 导出03版Excel .xls
     * @param object//导出对象，可以是数组可以是对象
     * @param fields//要导出对象的所需要的属性，注意跟模板书序一直
     * @param docTemplatePath 如  1：/D:/saw_workspace/property/property-manage-ui/target/classes//templates/supplier/设施设备管理导入模板.xls
     *                      2：/templates/supplier/供应商管理导入模板.xls
     *                     docTemplatePath = this.getClass().getResource("/").getPath()+/templates/supplier/供应商管理导入模板.xls";
     * @param docWriteStartRowIndex//从模板第几行开始写入
     * @return
     * @throws FileNotFoundException
     * @throws IOException
     */
    public static void doExpXls(Object object,String[] fields,String docTemplatePath,Integer docWriteStartRowIndex) throws IOException{
        doExpXls(object, fields, docTemplatePath, docWriteStartRowIndex,null);
    }
    
    /**
     * 导出03版Excel .xls
     * @param object//导出对象，可以是数组可以是对象
     * @param fields//要导出对象的所需要的属性，注意跟模板书序一直
     * @param docTemplatePath 如  1：/D:/saw_workspace/property/property-manage-ui/target/classes//templates/supplier/设施设备管理导入模板.xls
     *                      2：/templates/supplier/供应商管理导入模板.xls
     *                     docTemplatePath = this.getClass().getResource("/").getPath()+/templates/supplier/供应商管理导入模板.xls";
     * @param docWriteStartRowIndex//从模板第几行开始写入
     * @param sheetRowsCount//sheet页数据最大行数
     * @return
     * @throws IOException
     */
    @SuppressWarnings("resource")
    public static void doExpXls(Object object,String[] fields,String docTemplatePath,Integer docWriteStartRowIndex,Integer sheetRowsCount) throws IOException{
        docTemplatePath = docTemplatePath.replaceAll("\\\\", "/");
        String projectPath = ImpAndExpExcel.class.getResource("/").getPath().replaceAll("\\\\", "/");
        if(!docTemplatePath.contains(projectPath)){
            docTemplatePath = projectPath+"/"+docTemplatePath;
        }
        FileInputStream inputStream = new FileInputStream(new File(docTemplatePath));;
       
        HSSFWorkbook workbook = new HSSFWorkbook(new POIFSFileSystem(inputStream));
        
        JSONArray jsonArr = (JSONArray) JSONArray.toJSON(object);
        int everyCount = null==sheetRowsCount?50000:sheetRowsCount;
        int pageCount = (int)Math.ceil(jsonArr.size()/(everyCount*1.0));
        //创建SHEET页
        for(int i=0; i<pageCount; i++){
            HSSFSheet sheet = null;
            if(0==i){
                sheet = workbook.getSheetAt(0);
            }else{
                sheet = workbook.cloneSheet(0);
            }
            
            int index = workbook.getSheetIndex(sheet);
            workbook.setSheetName(index, "第"+(i+1)+"页");
            workbook.setSheetOrder(sheet.getSheetName(), i);
        }
        //sheet页赋值数据
        for(int i=0; i<pageCount; i++){
            int startIndex = i*everyCount;
            int endIndex = startIndex+everyCount;
            if(i==pageCount-1){
                endIndex = jsonArr.size();
            }
            JSONArray subjsonArr = (JSONArray)JSONArray.toJSON(jsonArr.subList(startIndex, endIndex));
            int startRowIndex = (null == docWriteStartRowIndex?2:docWriteStartRowIndex);
            
            HSSFSheet sheet = workbook.getSheetAt(i);
            
            for (int j = 0; j < subjsonArr.size(); j++,startRowIndex++) {
                JSONObject jsonObj  = subjsonArr.getJSONObject(j);
                for (int colIndex = 0; colIndex < fields.length; colIndex++) {
                    HSSFCell tempCell = getHssfCell(sheet, startRowIndex, colIndex, true);
                    tempCell.setCellValue(jsonObj.getString(fields[colIndex]));    
                }
            }
        }
        
        ServletOutputStream out = null;
        String excName = docTemplatePath.substring(docTemplatePath.lastIndexOf("/")+1);
        try {
            HttpServletResponse response = ((ServletRequestAttributes) RequestContextHolder.getRequestAttributes()).getResponse();
            response.setCharacterEncoding("UTF-8");
            response.setContentType("application/octet-stream; charset=UTF-8");
            response.addHeader("Content-Disposition", "attachment; filename=\""+new String(excName.getBytes("GB2312"),"ISO8859-1")+"\";");// 
            out = response.getOutputStream();
            workbook.write(out);
            out.flush();
        } catch (IOException e) {
            e.printStackTrace();
        } finally{
            try {
                out.close();
                inputStream.close();
            } catch (IOException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
    }
    
    /**
     * 获取导出03版Excel .xls的workbook
     * @param object//导出对象，可以是数组可以是对象
     * @param fields//要导出对象的所需要的属性，注意跟模板书序一直
     * @param docTemplatePath 如  1：/D:/saw_workspace/property/property-manage-ui/target/classes//templates/supplier/设施设备管理导入模板.xls
     *                      2：/templates/supplier/供应商管理导入模板.xls
     *                     docTemplatePath = this.getClass().getResource("/").getPath()+/templates/supplier/供应商管理导入模板.xls";
     * @param docWriteStartRowIndex//从模板第几行开始写入
     * @return
     * @throws FileNotFoundException
     * @throws IOException
     */
    public static HSSFWorkbook getHSSFWorkbook(Object object,String[] fields,String docTemplatePath,Integer docWriteStartRowIndex) throws FileNotFoundException,IOException {
        docTemplatePath = docTemplatePath.replaceAll("\\\\", "/");
        String projectPath = ImpAndExpExcel.class.getResource("/").getPath().replaceAll("\\\\", "/");
        if(!docTemplatePath.contains(projectPath)){
            docTemplatePath = projectPath+"/"+docTemplatePath;
        }
        FileInputStream inputStream = new FileInputStream(new File(docTemplatePath));
        HSSFWorkbook workbook = new HSSFWorkbook(new POIFSFileSystem(inputStream));    
        HSSFSheet sheet = workbook.getSheetAt(0);
        
        int startRowIndex = (null == docWriteStartRowIndex?2:docWriteStartRowIndex);
        
        JSONArray jsonArr = (JSONArray) JSONArray.toJSON(object);
        for (int i = 0; i < jsonArr.size(); i++,startRowIndex++) {
            JSONObject jsonObj  = jsonArr.getJSONObject(i);
            for (int colIndex = 0; colIndex < fields.length; colIndex++) {
                HSSFCell tempCell = getHssfCell(sheet, startRowIndex, colIndex, true);
                
                tempCell.setCellValue(jsonObj.getString(fields[colIndex]));    
            }
        }

        return workbook;
    }
    /**
     * 此方法用于下载指定文件。
     * @param response 用于防止下载乱码，设置输出流的相关信息
     * @param filePath 如  1：/D:/saw_workspace/property/property-manage-ui/target/classes//templates/supplier/设施设备管理导入模板.xls
     *                      2：/templates/supplier/供应商管理导入模板.xls
     *                     filePath = this.getClass().getResource("/").getPath()+/templates/supplier/供应商管理导入模板.xls";
     * @return true 下载成功， false 下载失败
     */
    public static void download(String filePath){
        ServletOutputStream out = null;
        FileInputStream inputStream = null;
        filePath = filePath.replaceAll("\\\\", "/");
        String projectPath = ImpAndExpExcel.class.getResource("/").getPath().replaceAll("\\\\", "/");
        if(!filePath.contains(projectPath)){
            filePath = projectPath+"/"+filePath;
        }
        String fileName = filePath.substring(filePath.lastIndexOf("/")+1, filePath.length());
        try{
            inputStream = new FileInputStream(new File(filePath)); 
            HttpServletResponse response = ((ServletRequestAttributes) RequestContextHolder.getRequestAttributes()).getResponse();
            response.setCharacterEncoding("UTF-8");
            response.setContentType("application/octet-stream; charset=UTF-8");
            response.addHeader("Content-Disposition", "attachment; filename=\""+new String(fileName.getBytes("GB2312"),"ISO8859-1")+"\";");// 
              
            out = response.getOutputStream();  
  
            int b = 0;  
            byte[] buffer = new byte[512];  
            while ((b=inputStream.read(buffer)) != -1){  
                out.write(buffer,0,b);  
            }  
            out.flush();
        } catch (IOException e) {
            e.printStackTrace();
        }finally{
            try {
                 if(inputStream != null){
                     inputStream.close();
                 }
            } catch (IOException e) {
                e.printStackTrace();
            }
            
            try {
                if(out != null){
                    out.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }  
        }
    }
    
    // 获取xlsx单元格，不存在是否创建
    public static XSSFCell getXssfCell(XSSFSheet sheet, int rowIndex, int colIndex,
            boolean isCreate) {
        if (isCreate) {
            XSSFRow row = sheet.getRow(rowIndex);
            if (row == null) {
                row = sheet.createRow(rowIndex);
                row.setHeightInPoints(24);// 设置行的高度（单元格的高度）
            }
            XSSFCell cell = row.getCell(colIndex);
            if (cell == null) {
                cell = row.createCell(colIndex);
            }
            return cell;
        } else {
            return getXssfCell(sheet, rowIndex, colIndex);
        }
    }

    // 获取xlsx单元格
    public static XSSFCell getXssfCell(XSSFSheet sheet, int rowIndex, int colIndex) {
        XSSFRow row = sheet.getRow(rowIndex);
        if (row != null) {
            XSSFCell cell = row.getCell(colIndex);
            if (cell != null) {
                return cell;
            }
        }
        return null;
    }
    
    // 获取xlsx单元格Cell里面的值
    // 因为cell单元格有格式，所以针对不同的格式取值
    public static String getXssfCellValue(XSSFCell cell) {
        String cellValue = "";
        if(null==cell) {
            return cellValue;
        }
        switch (cell.getCellType()) {
            case XSSFCell.CELL_TYPE_STRING:// 字符串类型
                cellValue = cell.getStringCellValue();
                if (cellValue.trim().equals("") || cellValue.trim().length() <= 0)
                    cellValue = "";
                break;
            case XSSFCell.CELL_TYPE_NUMERIC: // 数值类型
                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                    Date d = cell.getDateCellValue();
                    if (d != null) {
                        cellValue = DateTimeUtils.getDate(d);
                    } else {
                        cellValue = "";
                    }
                } else {
                    cellValue = cell.getNumericCellValue() + "";
                    if(cellValue.contains(".")){
                        cellValue = cellValue.substring(0, cellValue.indexOf("."));
                    }
                }
                break;
            case HSSFCell.CELL_TYPE_FORMULA: // 公式
                try{
                    cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                    cellValue = String.valueOf(cell.getNumericCellValue());
                }catch (Exception e) {
                    try{
                        cellValue = String.valueOf(cell.getStringCellValue());
                    }catch(Exception e2){
                        cellValue ="";
                    }
                }
                break;
            case HSSFCell.CELL_TYPE_BLANK:
                break;
            case HSSFCell.CELL_TYPE_BOOLEAN:
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            case HSSFCell.CELL_TYPE_ERROR:
                break;
            default:
                break;
        }
        if(cellValue!=null) {
            cellValue = cellValue.trim();
        }
        return cellValue;
    }
    
    // 获取xls单元格，不存在是否创建
    public static HSSFCell getHssfCell(HSSFSheet sheet, int rowIndex, int colIndex,
            boolean isCreate) {
        if (isCreate) {
            HSSFRow row = sheet.getRow(rowIndex);
            if (row == null) {
                row = sheet.createRow(rowIndex);
                row.setHeightInPoints(24);// 设置行的高度（单元格的高度）
            }
            HSSFCell cell = row.getCell(colIndex);
            if (cell == null) {
                cell = row.createCell(colIndex);
            }
            return cell;
        } else {
            return getHssfCell(sheet, rowIndex, colIndex);
        }
    }

    // 获取xls单元格
    public static HSSFCell getHssfCell(HSSFSheet sheet, int rowIndex, int colIndex) {
        HSSFRow row = sheet.getRow(rowIndex);
        if (row != null) {
            HSSFCell cell = row.getCell(colIndex);
            if (cell != null) {
                return cell;
            }
        }
        return null;
    }

    // 获取xls单元格Cell里面的值
    // 因为cell单元格有格式，所以针对不同的格式取值
    public static String getHssfCellStringValue(HSSFCell cell) {
        String cellValue = "";
        switch (cell.getCellType()) {
        case HSSFCell.CELL_TYPE_STRING:// 字符串类型
            cellValue = cell.getStringCellValue();
            if (cellValue.trim().equals("") || cellValue.trim().length() <= 0)
                cellValue = " ";
            break;
        case HSSFCell.CELL_TYPE_NUMERIC: // 数值类型
            if (HSSFDateUtil.isCellDateFormatted(cell)) {
                Date d = cell.getDateCellValue();
                if (d != null) {
                    cellValue = DateTimeUtils.getDate(d);
                } else {
                    cellValue = "";
                }
            } else {
                cellValue = cell.getNumericCellValue() + "";
                if(cellValue.contains(".")){
                    cellValue = cellValue.substring(0, cellValue.indexOf("."));
                }
            }
            break;
        case HSSFCell.CELL_TYPE_FORMULA: // 公式
            try{
                cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                cellValue = String.valueOf(cell.getNumericCellValue());
            }catch (Exception e) {
                try{
                    cellValue = String.valueOf(cell.getStringCellValue());
                }catch(Exception e2){
                    cellValue ="";
                }
            }
            break;
        case HSSFCell.CELL_TYPE_BLANK:
            cellValue = " ";
            break;
        case HSSFCell.CELL_TYPE_BOOLEAN:
            break;
        case HSSFCell.CELL_TYPE_ERROR:
            break;
        default:
            break;
        }
        return cellValue;
    }
    
    public static boolean isRequired(String checkFiled, String[] requireds) {
        if(StringUtils.isEmpty(checkFiled) || ArrayUtils.isEmpty(requireds)) {
            return false;
        }
        for(String required : requireds) {
            if(required.equals(checkFiled)) {
                return true;
            }
        }
        return false;
    }
}

模板样子

java Excel导入导出工具类

stringJavaJava
点赞
收藏
评论区
推荐文章
blmius blmius
•
2年前
MySQL:[Err] 1292 - Incorrect datetime value: ‘0000-00-00 00:00:00‘ for column ‘CREATE_TIME‘ at row 1
文章目录问题用navicat导入数据时，报错：原因这是因为当前的MySQL不支持datetime为0的情况。解决修改sql\mode:sql\mode:SQLMode定义了MySQL应支持的SQL语法、数据校验等，这样可以更容易地在不同的环境中使用MySQL。全局s
3492
0
0
Jacquelyn38 Jacquelyn38
•
2年前
2020年前端实用代码段，为你的工作保驾护航
有空的时候，自己总结了几个代码段，在开发中也经常使用，谢谢。1、使用解构获取json数据let jsonData  id: 1,status: "OK",data: 'a', 'b';let  id, status, data: number   jsonData;console.log(id, status, number )
1974
0
0
helloworld_34035044 helloworld_34035044
•
1年前
皕杰报表之UUID
​在我们用皕杰报表工具设计填报报表时，如何在新增行里自动增加id呢？能新增整数排序id吗？目前可以在新增行里自动增加id，但只能用uuid函数增加UUID编码，不能新增整数排序id。uuid函数说明：获取一个UUID，可以在填报表中用来创建数据ID语法：uuid()或uuid(sep)参数说明：sep布尔值，生成的uuid中是否包含分隔符''，缺省为
771
0
0
Stella981 Stella981
•
2年前
PHP导入导出EXCELl,CSV
PHP导入导出Excel，CSVHTML<formaction"{:U('Admin/Unit/importcsv')}"method"post"name"myform"id"myform"enctype"multipart/formdata"<input
712
0
0
Stella981 Stella981
•
2年前
Android So动态加载 优雅实现与原理分析
背景:漫品Android客户端集成适配转换功能(基于目标识别(So库35M)和人脸识别库(5M)),导致apk体积50M左右,为优化客户端体验,决定实现So文件动态加载.!(https://oscimg.oschina.net/oscnet/00d1ff90e4b34869664fef59e3ec3fdd20b.png)点击上方“蓝字”关注我
1217
0
0
Wesley13 Wesley13
•
2年前
00：Java简单了解
浅谈Java之概述Java是SUN(StanfordUniversityNetwork)，斯坦福大学网络公司)1995年推出的一门高级编程语言。Java是一种面向Internet的编程语言。随着Java技术在web方面的不断成熟，已经成为Web应用程序的首选开发语言。Java是简单易学，完全面向对象，安全可靠，与平台无关的编程语言。
894
0
0
Stella981 Stella981
•
2年前
Docker 部署SpringBoot项目不香吗？
　　公众号改版后文章乱序推荐，希望你可以点击上方“Java进阶架构师”，点击右上角，将我们设为★“星标”！这样才不会错过每日进阶架构文章呀。　　!(http://dingyue.ws.126.net/2020/0920/b00fbfc7j00qgy5xy002kd200qo00hsg00it00cj.jpg)　　2
788
0
0
Wesley13 Wesley13
•
2年前
35岁是技术人的天花板吗？
35岁是技术人的天花板吗？我非常不认同“35岁现象”，人类没有那么脆弱，人类的智力不会说是35岁之后就停止发展，更不是说35岁之后就没有机会了。马云35岁还在教书，任正非35岁还在工厂上班。为什么技术人员到35岁就应该退役了呢？所以35岁根本就不是一个问题，我今年已经37岁了，我发现我才刚刚找到自己的节奏，刚刚上路。
605
0
0
Wesley13 Wesley13
•
2年前
MySQL部分从库上面因为大量的临时表tmp_table造成慢查询
背景描述Time:20190124T00:08:14.70572408:00User@Host:@Id:Schema:sentrymetaLast_errno:0Killed:0Query_time:0.315758Lock_
1107
0
0
Python进阶者 Python进阶者
•
3个月前
Excel中这日期老是出来00：00：00，怎么用Pandas把这个去除
大家好，我是皮皮。一、前言前几天在Python白银交流群【上海新年人】问了一个Pandas数据筛选的问题。问题如下：这日期老是出来00：00：00，怎么把这个去除。二、实现过程后来【论草莓如何成为冻干莓】给了一个思路和代码如下：pd.toexcel之前把这
122
0
0
Wesley13
Wesley13
Lv1
最大的安心是自律温柔和爱自己。
文章
1.2w
粉丝
19
获赞
1.7k
热门文章
JavPlayer：AI破坏马赛克，大量马赛克破坏版影片流出
8个在线接收手机短信验证码的免费网络服务整理
IDM 激活破解工具
2020年最新可用的磁力搜索引擎&种子搜索网站，持续整理分享
java.net.ConnectException：Connection timed out：connect 出现连接超时的问题？