Excel导入工具详解-优快云博客

package com.wyj.utils;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.GregorianCalendar;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.DateUtil;
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 com.wyj.constant.ImportConstant;
import com.wyj.constant.ImportErrorCode;


public class ImportUtils
{
    public final static String IMPORT_SPECIAL_CHAR_REG = "[\u0000-\uFFFF]*[#~@,;:\\/\\?\\*\\<\\>\\|\\&\\\"\\\\]+[\u0000-\uFFFF]*";

    /**
     * 判断是否为空或者空格
     * 
     * @param value
     * @return
     */
    public static boolean equalsNull( Object value )
    {
        if ( null != value && !("").equals( value ) )
        {
            if ( !("").equals( value.toString().trim() )
                    && null != value.toString().trim() )
            {
                return false;
            }
        }
        return true;
    }

    /**
     * 判断value是否与正则表达式匹配
     * 
     * @param value
     * @param regex
     * @return
     */
    public static boolean containsSpecialChar( Object value, String regex )
    {
        if ( !equalsNull( value ) )
        {

            if ( value.toString().matches( (regex) ) )
            {
                return true;
            }
        }
        return false;
    }

    /**
     * 统计空行
     * 
     * @param sheet
     *            单元格
     * @return
     */
    public static int countBlankLine( Sheet sheet )
    {
        int colDataCount = 0;
        int colNum = 0;
        Row row = null;
        int blankLineCount = 0;
        // 获取最后一行单元格
        int j = sheet.getLastRowNum();
        // 空行统计
        for ( ; j > 0; j-- )
        {
            // 得到excel的行
            row = sheet.getRow( j );

            if ( null != row )
            {
                // 得到单元格的列数
                colNum = row.getLastCellNum();

                for ( int k = 0; k < colNum; k++ )
                {
                    if ( !ImportUtils.equalsNull( row.getCell( k ) ) )
                    {
                        colDataCount++;
                        break;
                    }
                }
                // 是否有数据
                if ( colDataCount == 0 )
                {
                    blankLineCount++;
                } else
                {
                    break;
                }
            }
        }

        return blankLineCount;
    }

    /**
     *获取单元格的数据
     * 
     * @param cell
     *            单元格
     * @return
     */
    public static Object getCellData( Cell cell )
    {
        DataFormatter dataFormatter = new DataFormatter();
        String data = "";
        // 格式化数字
        DecimalFormat decimalFormat = new DecimalFormat("0.00");
        if ( cell != null )
        {
            switch ( cell.getCellType() )
            {
                case Cell.CELL_TYPE_BLANK:
                    data = "";
                    break;
                case Cell.CELL_TYPE_ERROR:
                    data = "";
                    break;
                case Cell.CELL_TYPE_BOOLEAN:
                    data = cell.getBooleanCellValue() + "";
                    break;
                case Cell.CELL_TYPE_FORMULA:// 公式
                    data = cell.getNumericCellValue() + "";
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    if ( DateUtil.isCellDateFormatted( cell ) )
                    {
                        data = cell.getDateCellValue() + "";
                    } else
                    {
                        data =decimalFormat.format(cell.getNumericCellValue());
                    }
                    break;
                case Cell.CELL_TYPE_STRING:
                    data = cell.getRichStringCellValue() + "";
                    break;
                default:
                    data = dataFormatter.formatCellValue( cell );
            }
        }
        return data.trim();

    }
    /**
     * 设置单元格的数据
     * @param value 读取值
     * @param cell 单元格
     * @return
     * @throws ParseException
     */
    @SuppressWarnings( {"static-access"} )
    public static Cell setCellValue( Object value, Cell cell ) throws ParseException
    {
        String textValue = "";

        if ( value instanceof Integer )
        {
            int intValue = (Integer)value;
            cell.setCellValue( intValue );
        } else if ( value instanceof Float )
        {
            float fValue = (Float)value;
            cell.setCellValue( fValue );
        } else if ( value instanceof Double )
        {
            double dValue = (Double)value;
            cell.setCellValue( dValue );
        } else if ( value instanceof Long )
        {
            long longValue = (Long)value;
            cell.setCellValue( longValue );
        } else if ( value instanceof Date )
        {
            Date date = (Date)value;
            SimpleDateFormat sdf = new SimpleDateFormat( "yyyy-MM-dd" );
            textValue = sdf.format( date );
            cell.setCellValue( textValue );
        } else if ( value instanceof String )
        {
            // 其它数据类型都当作字符串简单处理
            textValue = value.toString();
            cell.setCellValue( textValue );
        } else if ( value instanceof Boolean )
        {
            boolean bValue = (Boolean)value;
            textValue = "是";
            if ( !bValue )
            {
                textValue = "否";
            }
        } else if ( value instanceof GregorianCalendar )
        {
            GregorianCalendar calendar = (GregorianCalendar)value;
            Date d = calendar.getTime();
            SimpleDateFormat sdf = new SimpleDateFormat( ImportConstant.DATE_FORMATE); 
            textValue = sdf.format( d );
        } else
        {
            textValue = "";
            cell.setCellValue( textValue );
        }
        
        return cell;
    }

    
    /**
     * 统计空列
     * 
     * @param sheet
     *            单元格
     * @return
     */
    public static int countBlankColumn( Sheet sheet )
    {
        // 数据列
        int colDataCount = 0;
        // 空列统计
        int lastBlankColCount = 0;
        // 得到首行
        Row row = sheet.getRow( sheet.getFirstRowNum() );

        if ( null != row )
        {
            // 获取最后一行单元格
            int cellNum = row.getLastCellNum() - 1;
            // 空行统计
            for ( int j = cellNum; j >= 0; j-- )
            {
                if ( !ImportUtils.equalsNull( row.getCell( j ) ) )
                {
                    colDataCount++;
                }

                if ( colDataCount == 0 )
                {
                    lastBlankColCount++;
                } else
                {
                    break;
                }

            }
        }

        return lastBlankColCount;
    }

    /**
     * 是否是数字
     * 
     * @author wWX167397 wangyijun
     * @param value
     *            传入的值
     * @return 布尔值
     */
    public static boolean isDigital( Object value )

    {
        boolean flag = false;

        // only input number
        Pattern pattern = Pattern.compile( "^(\\d)+((\\.)?(\\d)+)?$" );

        Matcher m = pattern.matcher( value.toString() );
        flag = m.matches();

        return flag;
    }

    /**
     * 获取Sheet
     * 
     * @param file 文件
     * @param fileName  文件名称
     * @return
     * @throws Exception
     */
    public static Sheet getSheet( File file, String fileName )
            throws Exception
    {
        Workbook workbook = null;
        FileInputStream in = null;

        try
        {
            in = new FileInputStream( file );
            if ( fileName.toLowerCase().endsWith( ".xlsx" ) )
            {
                workbook = new XSSFWorkbook( in );
            } else
            {
                workbook = new HSSFWorkbook( in );

            }
        } catch ( FileNotFoundException e )
        {

            throw new Exception( ImportErrorCode.FILE_NOT_FOUND );
        } catch ( IOException e )
        {
            throw new Exception( ImportErrorCode.FILE_READ_ERROR );
        } finally
        {
            if ( in != null )
            {
                try
                {
                    in.close();
                } catch ( IOException e )
                {
                    throw new Exception( ImportErrorCode.FILE_READ_ERROR );
                }
            }
        }
        Sheet sheet = workbook.getSheetAt( 0 );
        if ( null == sheet )
        {
            throw new Exception( ImportErrorCode.DATA_NOT_EXIST );
        }
        if ( sheet.getLastRowNum() < 1 )
        {
            throw new Exception( ImportErrorCode.DATA_NOT_EXIST );
        }
        return sheet;

    }

    /**
     * 获取头部信息
     * 
     * @param sheet
     * @return
     * @throws Exception
     */
    public static List<String> getHeader( Sheet sheet ) throws Exception
    {

        Row row = sheet.getRow( 0 );
        List<String> list = null;
        // 数据格式化
        DataFormatter dataFormatter = new DataFormatter();
        if ( row != null )
        {
            int cellCount = row.getLastCellNum();
            list = new ArrayList<String>( cellCount );
            String cellValue = null;
            // 得到空列
            int blankColCount = countBlankColumn( sheet );

            for ( int i = 0; i < cellCount - blankColCount; i++ )
            {
                cellValue = dataFormatter.formatCellValue( row.getCell( i ) );
                list.add( cellValue.trim().toLowerCase() );

            } // excel列标题不能为空
            if ( list.size() == 0 )
            {
                throw new Exception(
                        ImportErrorCode.EXCEL_COL_TITLE_NOT_BE_EMPTY );
            }

        } else
        {
            throw new Exception(
                    ImportErrorCode.EXCEL_COL_TITLE_NOT_BE_EMPTY );
        }

        return list;
    }

    /**
     * 校验头部信息
     * 
     * @param headerList
     * @param headerArray
     * @param headerSize
     * @return
     * @throws Exception
     */
    public static boolean validateHeader( List<String> headerList,
            String[] headerArray, int headerSize ) throws Exception
    {

        if ( headerList.size() != headerSize )
        {
            throw new Exception(
                    ImportErrorCode.EXCEL_COL_NUMBER_NOT_BE_CORRECT );
        }
        int index = 0;

        // CHECKSTYLE:OFF
        for ( String s : headerList )
        { // CHECKSTYLE:ON
            if ( s.toLowerCase().equals(
                    headerArray[index].trim().toLowerCase() ) )
            {
                index++;
            } else
            {
                throw new Exception(
                        ImportErrorCode.EXCEL_COL_TITLE_NOT_BE_CORRECT );
            }
        }

        return true;
    }

    public static boolean isInteger(String data)
    {
        boolean flag = false;
        
        // only input number
        Pattern pattern = Pattern.compile( "^((\\d)+)?((\\.)?(0)+)?$" );

        Matcher m = pattern.matcher( data.toString() );
        flag = m.matches();

        return flag;
    }

}
导入的工具类
