【Excel表格导入导出poi工具类】

【Excel表格导入导出poi工具类】

1.在项目中导入poi的maven依赖

<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>

2.创建工具类

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class PoiUtil {
    //校验文件是否合法
    public static void checkFile(MultipartFile file) throws IOException{
        //判断文件是否存在
        if(null == file){
            throw new FileNotFoundException("文件不存在！");
        }
        //获得文件名
        String fileName = file.getOriginalFilename();
        //判断文件是否是excel文件
        if(!fileName.endsWith("xls") && !fileName.endsWith("xlsx")){
            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) {
            e.printStackTrace();
        }
        return workbook;
    }
    public static String getCellValue(Cell cell){
        String cellValue = "";
        if(cell == null){
            return cellValue;
        }
        //如果当前单元格内容为日期类型，需要特殊处理
        String dataFormatString = cell.getCellStyle().getDataFormatString();
        /*if(dataFormatString.equals("m/d/yy")){
            cellValue = new SimpleDateFormat(DATE_FORMAT).format(cell.getDateCellValue());
            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;
            case Cell.CELL_TYPE_BOOLEAN: //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;
    }

    //读取excel中的每一行数据
    public static  List<List<String>> readXls(Workbook workbook) throws IOException {
        Sheet sheet = workbook.getSheetAt(0);
        Row row = null;
        Cell cell = null;
        List<List<String>> list = new ArrayList<List<String>>();
            for(int i =(sheet.getFirstRowNum()+1);i<=(sheet.getPhysicalNumberOfRows()-1);i++){
            row = sheet.getRow(i);
            if(row == null){
                continue;
            }
            List<String> rowList = new ArrayList<>();
            for(int j = row.getFirstCellNum();j<row.getPhysicalNumberOfCells();j++){
                Cell cellValue = row.getCell(j);
                if(null == cellValue){
                    continue;
                }
                rowList.add(PoiUtil.getCellValue(cellValue));
            }
            if(rowList.size() != 0){
                list.add(rowList);
            }
        }
        workbook.close();
        return list;
    }


    //导出excel
    /**
     * Description: 1、将list中的data写入表格中 导出一个sheet
     * @param sheetNum (sheet的位置，0表示第一个表格中的第一个sheet)
     */
    public static <T, S> void exportExcel(SXSSFWorkbook wb,int sheetNum, String keyName, List<T> list,
                                               List<String> fieldNames,List<String> sheetHead)
    {
        // 创建一个Excel的sheet
        Sheet sheet = wb.createSheet(keyName);
        wb.setSheetName(sheetNum,keyName);
        for (int j = 0; j <= list.size(); j++ )
        {
            Row row = sheet.createRow(j);// 第j行
            JSONObject jsonObj = null;
            if (j != 0)
            {
                jsonObj = (JSONObject) JSON.toJSON(list.get(j - 1));
            }
            for (int k = 0; k < fieldNames.size(); k++ )
            {
                // 第一列单元格
                Cell cell = row.createCell(k);
                // 第一行
                if (j == 0)
                {
                    // 第一列数据
                    cell.setCellValue(sheetHead.get(k));
                }
                else
                {
                    // 将bean对象转换成JSON对象
                    String content = jsonObj.getString(fieldNames.get(k));
                    if (content == null)
                    {
                        content = "";
                    }
                    cell.setCellValue(content);
                }
            }

        }
        setSizeColumn(sheet, fieldNames.size());
    }

    //导出excel
    /**
     * Description: 1、将list中的data写入表格中 导出一个sheet
     */
    public static <T, S> SXSSFWorkbook export( String keyName, List<T> list,
                                              List<String> fieldNames,List<String> sheetHead)
    {
        // 内存中保留 10000 条数据，以免内存溢出，其余写入 硬盘
        SXSSFWorkbook wb = new SXSSFWorkbook(2000);
        // 创建一个Excel的sheet
        Sheet sheet = wb.createSheet(keyName);
        for (int j = 0; j <= list.size(); j++ )
        {
            Row row = sheet.createRow(j);// 第j行
            JSONObject jsonObj = null;
            if (j != 0)
            {
                jsonObj = (JSONObject) JSON.toJSON(list.get(j - 1));
            }
            for (int k = 0; k < fieldNames.size(); k++ )
            {
                // 第一列单元格
                Cell cell = row.createCell(k);
                // 第一行
                if (j == 0)
                {
                    // 第一列数据
                    cell.setCellValue(sheetHead.get(k));
                }
                else
                {
                    // 将bean对象转换成JSON对象
                    String content = jsonObj.getString(fieldNames.get(k));
                    if (content == null)
                    {
                        content = "";
                    }
                    cell.setCellValue(content);
                }
            }

        }
        setSizeColumn(sheet, fieldNames.size());
        return wb;
    }

    /**
     * Description: 1、调整列宽,兼容中文
     *
     * @param sheet
     * @param size
     * @author: yizl
     * @date: Nov 1, 2018 11:31:56 AM
     */
    private static void setSizeColumn(Sheet sheet, int size)
    {
        for (int columnNum = 0; columnNum < size; columnNum++ )
        {
            int columnWidth = sheet.getColumnWidth(columnNum) / 256;
            for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++ )
            {
                Row currentRow;
                // 当前行未被使用过
                if (sheet.getRow(rowNum) == null)
                {
                    currentRow = sheet.createRow(rowNum);
                }
                else
                {
                    currentRow = sheet.getRow(rowNum);
                }

                if (currentRow.getCell(columnNum) != null)
                {
                    Cell currentCell = currentRow.getCell(columnNum);
                    if (currentCell.getCellType() == Cell.CELL_TYPE_STRING)
                    {
                        int length = currentCell.getStringCellValue().getBytes().length;
                        if (columnWidth < length)
                        {
                            columnWidth = length;
                        }
                    }
                }
            }

            // Excel的长度为字节码长度*256,*1.3为了处理数字格式
            columnWidth = (int)Math.floor(columnWidth * 256 * 1.3);
            //单元格长度大于20000的话也不美观,设置个最大长度
            columnWidth = columnWidth >= 20000 ? 20000 : columnWidth;
            //设置每列长度
            sheet.setColumnWidth(columnNum, columnWidth);
        }
    }

    /**
     * Description: 1、通过浏览器workbook以流的形式输出,为了处理中文表名路是你吗问题.
     *
     * @param workbook
     *            文件对象
     * @param request
     * @param response
     * @param fileName
     *            文件名
     * @author: yizl
     * @date: Oct 30, 2018 1:06:27 PM
     */
    public static void writeToResponse(SXSSFWorkbook workbook, HttpServletRequest request,
                                       HttpServletResponse response, String fileName)
    {
        try
        {
            String userAgent = request.getHeader("User-Agent");
            // 解决中文乱码问题
            String fileName1 = fileName + ".xlsx";
            String newFilename = URLEncoder.encode(fileName1, "UTF8");
            // 如果没有userAgent，则默认使用IE的方式进行编码，因为毕竟IE还是占多数的
            String rtn = "filename=\"" + newFilename + "\"";
            if (userAgent != null)
            {
                userAgent = userAgent.toLowerCase();
                // IE浏览器，只能采用URLEncoder编码
                if (userAgent.indexOf("IE") != -1)
                {
                    rtn = "filename=\"" + newFilename + "\"";
                }
                // Opera浏览器只能采用filename*
                else if (userAgent.indexOf("OPERA") != -1)
                {
                    rtn = "filename*=UTF-8''" + newFilename;
                }
                // Safari浏览器，只能采用ISO编码的中文输出
                else if (userAgent.indexOf("SAFARI") != -1)
                {
                    rtn = "filename=\"" + new String(fileName1.getBytes("UTF-8"), "ISO8859-1")
                            + "\"";
                }
                // FireFox浏览器，可以使用MimeUtility或filename*或ISO编码的中文输出
                else if (userAgent.indexOf("FIREFOX") != -1)
                {
                    rtn = "filename*=UTF-8''" + newFilename;
                }
            }

            String headStr = "attachment;  " + rtn;
            response.setContentType("APPLICATION/ms-excel");
            response.setCharacterEncoding("utf-8");
            response.setHeader("Content-Disposition", headStr);
            // 响应到客户端
            OutputStream os = response.getOutputStream();
            workbook.write(os);
        }
        catch (UnsupportedEncodingException e)
        {

            e.printStackTrace();
        }
        catch (IOException e)
        {
            e.printStackTrace();
        }
    }

    /**
     * Description: 1、列头单元格样式
     *
     * @param workbook
     * @return
     * @author: yizl
     * @date: Oct 30, 2018 1:22:44 PM
     */
    public static CellStyle getColumnTopStyle(SXSSFWorkbook workbook)
    {

        // 设置字体
        Font font = workbook.createFont();
        // 设置字体大小
        font.setFontHeightInPoints((short)11);
        // 字体加粗
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        // 设置字体名字
        font.setFontName("Courier New");
        // 设置样式;
        CellStyle style = workbook.createCellStyle();
        // 设置底边框;
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        // 设置底边框颜色;
        style.setBottomBorderColor(HSSFColor.BLACK.index);
        // 设置左边框;
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        // 设置左边框颜色;
        style.setLeftBorderColor(HSSFColor.BLACK.index);
        // 设置右边框;
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        // 设置右边框颜色;
        style.setRightBorderColor(HSSFColor.BLACK.index);
        // 设置顶边框;
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        // 设置顶边框颜色;
        style.setTopBorderColor(HSSFColor.BLACK.index);
        // 在样式用应用设置的字体;
        style.setFont(font);
        // 设置自动换行;
        style.setWrapText(false);
        // 设置水平对齐的样式为居中对齐;
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        // 设置垂直对齐的样式为居中对齐;
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        return style;
    }

    /**
     * Description: 1、设置表体的单元格样式
     *
     * @param workbook
     * @return
     * @author: yizl
     * @date: Oct 30, 2018 1:18:42 PM
     */
    private static CellStyle getBodyStyle(SXSSFWorkbook workbook)
    {
        // 创建单元格样式
        CellStyle cellStyle = workbook.createCellStyle();
        // 设置单元格居中对齐
        cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
        // 设置单元格居中对齐
        cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
        // 创建单元格内容不显示自动换行
        cellStyle.setWrapText(false);
        // 设置单元格字体样式
        XSSFFont font = (XSSFFont)workbook.createFont();
        font.setFontName("Courier New");// 设置字体
        font.setFontHeight(11);// 设置字体的大小
        cellStyle.setFont(font);// 将字体添加到表格中去
        // 设置单元格边框为细线条
        cellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);
        return cellStyle;
    }
}