Java对excel文件操作

最新推荐文章于 2024-06-05 14:01:02 发布

sunxuegang111

最新推荐文章于 2024-06-05 14:01:02 发布

阅读量788

点赞数

本文链接：https://blog.csdn.net/sunxuegang111/article/details/52769886

版权

poi 同时被 2 个专栏收录

1 篇文章 0 订阅

订阅专栏

excel

1 篇文章 0 订阅

订阅专栏

service接口类

package com.deppon.tps.module.aboutexcel.server.poidlexcel.service;

import java.io.ByteArrayOutputStream;
import java.io.File;

import javax.servlet.http.HttpServletResponse;

import com.deppon.tps.module.aboutexcel.server.shared.domain.AnalysisFileEntity;
import com.deppon.tps.module.aboutexcel.server.shared.domain.AnalysisResponseEntity;


public interface IPOIdwExcelUtilsService {

    /**
     * 解析文件并返回成功，失败，失败信息等结果
     * @param entity
     * @return
     */
    public AnalysisResponseEntity importFileData(AnalysisFileEntity entity);


    /**
     * 不需要模板，返回流，不需要生成excel文件
     * @param title
     * @param entity
     * @return
     */
    public ByteArrayOutputStream exportExcel(String title,AnalysisResponseEntity entity);

    /**
     * 需要模板，返回流，不需要生成excel文件
     * @param title
     * @param entity
     * @return
     */
    public ByteArrayOutputStream exportExcel(File file,AnalysisResponseEntity entity);

    /**
     * 需要模板，返回生成的excel路径，需要生成excel文件
     * @param file
     * @param entity
     * @return
     */
    public String createExcel(File file,AnalysisResponseEntity entity);

    /**
     * 下载excel
     * @param path
     * @param response
     */
    public void download(String path, HttpServletResponse response);
}

service 具体实现类

package com.deppon.tps.module.aboutexcel.server.poidlexcel.service.impl;

import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.annotation.Annotation;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.LinkedList;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import javax.xml.bind.annotation.XmlElement;

import org.apache.commons.io.FileUtils;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.sonatype.aether.util.StringUtils;

import com.deppon.foss.framework.exception.BusinessException;
import com.deppon.tps.module.aboutexcel.server.poidlexcel.service.IPOIdwExcelUtilsService;
import com.deppon.tps.module.aboutexcel.server.shared.domain.AnalysisFileEntity;
import com.deppon.tps.module.aboutexcel.server.shared.domain.AnalysisResponseEntity;
import com.esafenet.dll.FileDlpUtil;

public class POIdwExcelUtlsServiceImpl implements IPOIdwExcelUtilsService {
    Logger log = Logger.getLogger(POIdwExcelUtlsServiceImpl.class);
    //sheet0的表头开始在第几行
    public static int SHEET_START_ROW_NUMBER = 0;
    //sheet0的表头开始在第几列
    public static int SHEET_START_COLUMN_NUMBER = 0;

    public static final int SHEET_LIMIT_ROW_NUMBER = 5000;

    /**
     * 导入上传文件
     * @param entity
     * @return
     */
    public AnalysisResponseEntity importFileData(AnalysisFileEntity entity){

        final File historyfile = entity.getFile();
        final File file = decryptFile(historyfile);
        entity.setFile(file);
        log.info("导入文件开始,"+file.getName());
        //对上传的文件进行验证
        judgeFile(file);
        //解析上传的文件
        AnalysisResponseEntity responseentity = analysisFile(entity);

                if(historyfile.exists()){
                    historyfile.delete();   
                }
                if(file.exists()){
                    file.delete();  
                }

        return responseentity;
    }

    private File decryptFile(File file){
        String filepath = file.getAbsolutePath().substring(0,(int)file.getAbsoluteFile().toString().length()-file.getName().length());
        String filename = file.getName().substring(0, file.getName().indexOf("."))+"_UN"+file.getName().substring(file.getName().indexOf("."));
        File copyfile = new File(filepath+filename);

        boolean copysuccess = FileDlpUtil.decryptFile(file.getAbsolutePath(),filepath+filename);
        if(copysuccess){
            return copyfile;    
        }
        return null;
    }

    /**
     * 对上传的文件进行验证
     * @param file
     */
    public void judgeFile(File file){
        judegFileNull(file);
        //判断后缀是否正确
        judegFileSuffix(file);
        //判断文件大小
        judgeFilelength(file);
        //判断内存大小
        judegFreeMemory(file);
    }

    public void judegFileNull(File file){
        if(null == file || file.length()<=0){
            log.error("文件为空!");
            throw new BusinessException("文件为空!");
        }
    } 

    /**
     * 判断后缀是否正确
     * @param file
     */
    public void judegFileSuffix(File file){
        String filename = file.getName();
        String sufffilename = "";
        if ((filename == null) ||((filename != null)&& (filename.length() <= 0))) {
            log.error("文件为空："+file.getName()+"后缀名错误");
            throw new BusinessException("文件后缀名错误,请重新上传");
        }

        int dot = filename.lastIndexOf('.'); 
        if ((dot >-1) && (dot < (filename.length()))) { 
            sufffilename =   filename.substring(dot+1); 
            if(!"xls".equalsIgnoreCase(sufffilename) &&
               !"xlsx".equalsIgnoreCase(sufffilename)&&
               !"xlsm".equalsIgnoreCase(sufffilename)){
                log.error("文件："+file.getName()+"后缀名错误");
                throw new BusinessException("文件后缀名错误,请重新上传");
            }
        } 
    }

    /**
     * 判断文件大小
     * @param file
     */
    public void judgeFilelength(File file){
        long length = file.length();
        //如果文件的长度大于3M
        if(length>3*1024*1024){
            log.error("文件："+file.getName()+"太大,"+file.length());
            throw new BusinessException("文件太大,请重新上传");
        }
    }

    /**
     * 判断内存大小
     * @param file
     */
    public void judegFreeMemory(File file){
        long freememory = Runtime.getRuntime().freeMemory();
        //判断内存是否还有3M可用内存
        if(freememory < 3*1024*1024){
            log.error("文件："+file.getName()+"上传时，内存不足");
            throw new BusinessException("内存不足，请稍后上传！");
        }
    }


    /**
     * 解析上传的文件
     * @param entity
     */
    public AnalysisResponseEntity analysisFile(AnalysisFileEntity entity){
        AnalysisResponseEntity responseentity = new AnalysisResponseEntity();
        // 声明一个工作簿
        XSSFWorkbook workbook = null;
        //表头汉字对应的class字段--数组
        Field[] columnClassNamez = null;
        //表头汉字--数组
        String[] columnNamez = null; 
        //把excel中正确的数据存储起来
        List<Object> list = new LinkedList<Object>();
        //把excel中错误的数据存储起来
        List<String[]> errorlist = new LinkedList<String[]>();
        //把excel中错误的数据存储起来
        List<String> errorMessagelist = new LinkedList<String>();
        //读取sheet结束标示
        boolean readover = false;
        File file = entity.getFile();

        try {
//          workbook = new HSSFWorkbook(new FileInputStream(file));
            workbook = new XSSFWorkbook(new FileInputStream(file));
        } catch (IOException e) {
            log.error("读取"+file.getName()+"报错,具体原因："+e.getMessage());
            throw new BusinessException(e.getMessage());
        }
        //取得excel中的第一个sheet
//      HSSFSheet sheet = workbook.getSheetAt(0);
        XSSFSheet sheet = workbook.getSheetAt(0);
        SHEET_START_ROW_NUMBER = sheet.getFirstRowNum();
        SHEET_START_COLUMN_NUMBER = sheet.getRow(SHEET_START_ROW_NUMBER).getFirstCellNum();
        //限制读取的行数
        if(sheet.getLastRowNum()>SHEET_LIMIT_ROW_NUMBER){
            log.error("读取"+file.getName()+"文件行数过多！");
            throw new BusinessException("读取"+file.getName()+"文件行数过多！");
        }

        //取得sheet的列数
        int columns = sheet.getRow(SHEET_START_ROW_NUMBER).getLastCellNum();
        //表头汉字对应的class字段--数组--初始化
        columnClassNamez = new Field[columns-SHEET_START_COLUMN_NUMBER];
        //表头汉字--数组--初始化
        columnNamez = new String[columns-SHEET_START_COLUMN_NUMBER];

        //取得Field[]根据className和classPath
        Class<?> clazz = getThisClass(entity);
        Field[]  fieldz = clazz.getDeclaredFields();
        //循环第一行所对应的列，确定表头以及对应的javabean字段
        for(int i=SHEET_START_COLUMN_NUMBER;i<columns;i++){
            //取得表头汉字
            columnNamez[i-SHEET_START_COLUMN_NUMBER] = sheet.getRow(SHEET_START_ROW_NUMBER).getCell(i).getStringCellValue();
            //取得表头汉字对应的class字段
            columnClassNamez[i-SHEET_START_COLUMN_NUMBER] = queryClassName(columnNamez[i-SHEET_START_COLUMN_NUMBER],fieldz);
        }


        //循环行
        for(int i=SHEET_START_ROW_NUMBER+1;i<sheet.getLastRowNum()+1;i++){
            //文件读取结束标示
            if(readover){
                break;
            }

            Object obj = null;
            try {
                obj = clazz.newInstance();
            } catch (InstantiationException e) {
                log.error("实例化对象时报错！");
                throw new BusinessException(e.getMessage());
            } catch (IllegalAccessException e) {
                log.error("实例化对象时报错！");
                throw new BusinessException(e.getMessage());
            }

            try{
                //如果第一列序号为空，默认数据读取结束
                if(null == sheet.getRow(i).getCell(SHEET_START_COLUMN_NUMBER)){
                    log.error("行"+(i+1)+"序号为空!");
                    throw new BusinessException("行"+(i+1)+"序号为空!");
                }

                //循环每一列
                for(int j=SHEET_START_COLUMN_NUMBER;j<columns;j++){
                    Method method = null;

                    //如果单元格中的数据为空，则无需调用set方法了
                    if(null == sheet.getRow(i).getCell(j)){
                        continue;
                    }

                    try {
                        method = obj.getClass().getMethod("set"+columnClassNamez[j-SHEET_START_COLUMN_NUMBER].getName().substring(0,1).toUpperCase()+columnClassNamez[j-SHEET_START_COLUMN_NUMBER].getName().substring(1),columnClassNamez[j-SHEET_START_COLUMN_NUMBER].getType() );
                    } catch (SecurityException e) {
                        log.error("实现set方法时报错:"+"set"+columnClassNamez[j-SHEET_START_COLUMN_NUMBER].getName().substring(0,1).toUpperCase()+columnClassNamez[j-SHEET_START_COLUMN_NUMBER].getName().substring(1));
                        throw new BusinessException("实现set方法时报错:"+"set"+columnClassNamez[j-SHEET_START_COLUMN_NUMBER].getName().substring(0,1).toUpperCase()+columnClassNamez[j-SHEET_START_COLUMN_NUMBER].getName().substring(1));
                    } catch (NoSuchMethodException e) {
                        log.error("实现set方法时报错:"+"set"+columnClassNamez[j-SHEET_START_COLUMN_NUMBER].getName().substring(0,1).toUpperCase()+columnClassNamez[j-SHEET_START_COLUMN_NUMBER].getName().substring(1));
                        throw new BusinessException("实现set方法时报错:"+"set"+columnClassNamez[j-SHEET_START_COLUMN_NUMBER].getName().substring(0,1).toUpperCase()+columnClassNamez[j-SHEET_START_COLUMN_NUMBER].getName().substring(1));
                    }

                    try {
                        method.invoke(obj, getmethodInvokeValue(sheet.getRow(i).getCell(j),columnClassNamez[j-SHEET_START_COLUMN_NUMBER].getType().getName()));
                    } catch (IllegalArgumentException e) {
                        log.error("行："+i+"列："+j+"，在赋值"+sheet.getRow(i).getCell(j).getStringCellValue()+"的时候报错");
                        throw new BusinessException("行："+(i+1)+"列："+(j+1)+"，在赋值"+sheet.getRow(i).getCell(j).getStringCellValue()+"的时候报错");
                    } catch (IllegalAccessException e) {
                        log.error("行："+i+"列："+j+"，在赋值"+sheet.getRow(i).getCell(j).getStringCellValue()+"的时候报错");
                        throw new BusinessException("行："+(i+1)+"列："+(j+1)+"，在赋值"+sheet.getRow(i).getCell(j).getStringCellValue()+"的时候报错");
                    } catch (InvocationTargetException e) {
                        log.error("行："+i+"列："+j+"，在赋值"+sheet.getRow(i).getCell(j).getStringCellValue()+"的时候报错");
                        throw new BusinessException("行："+(i+1)+"列："+(j+1)+"，在赋值"+sheet.getRow(i).getCell(j).getStringCellValue()+"的时候报错");
                    }
                }
                //对于已经赋值好的Obj存储到list列表中
                list.add(obj);
            }catch(Exception e){
                //对报错行的处理-加入错误列表
                log.error("赋值报错,行:"+(i+1)+"，具体信息："+e.getMessage()+"保存到错误list中-start");
                //向错误列表中的元素赋值
                String[] errorz = new String[columns-SHEET_START_COLUMN_NUMBER];
                //循环每一列
                for(int x=SHEET_START_COLUMN_NUMBER;x<columns;x++){
                    errorz[x-SHEET_START_COLUMN_NUMBER] = getStrValue(sheet.getRow(i).getCell(x));
                }
                errorlist.add(errorz);
                errorMessagelist.add("赋值报错,行:"+(i+1)+"，具体信息："+e.getMessage());
                log.error("赋值报错,行:"+(i+1)+"，具体信息："+e.getMessage()+"保存到错误list中-end");
            }
        }
        responseentity.setFile(file);
        responseentity.setColumnNamez(columnNamez);
        responseentity.setColumnClassNamez(columnClassNamez);
        responseentity.setList(list);
        responseentity.setErrorlist(errorlist);
        responseentity.setErrorMessagelist(errorMessagelist);
        responseentity.setClassName(entity.getClassName());
//      responseentity.setClassPath(entity.getClassPath());
        return responseentity;
    }


    /**
     * 取得Field[]根据className和classPath
     * @param entity
     * @return
     */
    public Class<?> getThisClass(AnalysisFileEntity entity){
        Class<?> clazz = null;
        try {
            clazz = Class.forName(entity.getClassName());
        } catch (ClassNotFoundException e) {
            log.error(entity.getClassName()+",反射时报错");
            throw new BusinessException(e.getMessage());
        }
        return clazz;
    }

    /**
     * 运用反射，得到javabean中的name
     * @param CellName
     * @param entity
     * @return
     */
    public Field queryClassName(String cellName,Field[]  fieldz){
        Field field= null;
         for(int i=0;i<fieldz.length;i++){
             field = fieldz[i];
             Annotation annotation = field.getAnnotation(XmlElement.class);
             if(null != annotation){
                 XmlElement xmlelement = (XmlElement) annotation;
                 // 只有自己定义的才显示
                 if(!xmlelement.name().equals("##default")) {
                     //对比表头和现在的注释是否一样
                     if(cellName.equals(xmlelement.name())){
                         break;
                     }else{
                         field= null;
                     }
                 }
             }else{
                 field= null;
             }
         }
         if(null == field){
             log.error("列："+cellName+"找不到对应的上传字段");
             throw new BusinessException("列："+cellName+"找不到对应的上传字段");
         }
        return field;
    }

    public Object getCellContent(String cellContent,String classType){

        /** 
           * 基本类型、包装类型、String类型 
           */  
        try{
            if(classType.equals("java.math.BigDecimal")){
                return new BigDecimal(cellContent);
            }else if(classType.equals("java.util.Date")){
                SimpleDateFormat sm = new SimpleDateFormat("YYYY-MM-DD HH24:mm:ss");
                try {
                    return sm.parse(cellContent);
                } catch (ParseException e) {
                    log.error("转换时间时："+cellContent+",报错");
                    throw new BusinessException("转换时间时："+cellContent+",报错");
                }
            }else if(classType.equals("java.lang.Integer") || classType.equals("int")){
                return Integer.parseInt(cellContent);
            }else if(classType.equals("java.lang.Double")|| classType.equals("double")){
                return Double.parseDouble(cellContent);
            }else if(classType.equals("java.lang.Float")|| classType.equals("float")){
                return Float.parseFloat(cellContent);
            }else if(classType.equals("java.lang.Long") || classType.equals("long")){
                return Long.parseLong(cellContent);
            }else if(classType.equals("java.lang.Short") || classType.equals("short")){
                return Short.parseShort(cellContent);
            }else if(classType.equals("java.lang.Byte") || classType.equals("byte")){
                return Byte.parseByte(cellContent);
            }else if(classType.equals("java.lang.Boolean") || classType.equals("boolean")){
                return Boolean.parseBoolean(cellContent);
            }else if(classType.equals("java.lang.Character") || classType.equals("char")){
                return cellContent.toCharArray()[0];
            }else{
                return cellContent;
            }
        }catch(Exception e){
            log.error("转换数据"+cellContent+"格式时,报错");
            throw new BusinessException("转换数据"+cellContent+"格式时,报错");
        }
    }

    /**
     * 替换空格
     * @param str
     * @return
     */
    public static String replaceBlank(String str){
        if(StringUtils.isEmpty(str)){
            return "";
        }
        Pattern pattern = Pattern.compile("\\s*|\t|\r|\n");
        Matcher m = pattern.matcher(str);
        return m.replaceAll("");
    }

    /**
     * 返回参数
     * @param xssfRow
     * @return
     */
    private Object getmethodInvokeValue(XSSFCell xssfRow, String classType) {
        Object cellvalue = null;
        if (xssfRow.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
            cellvalue =  xssfRow.getBooleanCellValue();
        } else if (xssfRow.getCellType() == Cell.CELL_TYPE_NUMERIC) {
            cellvalue =  xssfRow.getNumericCellValue();
        } else {
            cellvalue =  xssfRow.getStringCellValue();
        }
        String cellContent  = String.valueOf(cellvalue);

        /** 
           * 基本类型、包装类型、String类型 
           */  
        try{
            if(classType.equals("java.math.BigDecimal")){
                return new BigDecimal(cellContent);
            }else if(classType.equals("java.util.Date")){
                try {
                    if(xssfRow.getCellType() == Cell.CELL_TYPE_NUMERIC ){
                         if (HSSFDateUtil.isCellDateFormatted(xssfRow)){
                             SimpleDateFormat dateFormat=new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
                             String datestr =dateFormat.format(xssfRow.getDateCellValue());
                             return dateFormat.parse(datestr);
                         }else{
                             return xssfRow.getNumericCellValue();
                         }
                    }else{ 
                        SimpleDateFormat dateFormat=new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
                        return dateFormat.parse(xssfRow.getStringCellValue());
                    }
                } catch (BusinessException e) {
                    log.error("转换时间时："+cellvalue+",报错");
                    throw new BusinessException("转换时间时："+cellvalue+",报错");
                }
            }else if(classType.equals("java.lang.Integer") || classType.equals("int")){
                cellContent = removepoint(cellContent);
                return Integer.valueOf(cellContent);
            }else if(classType.equals("java.lang.Double")|| classType.equals("double")){
                return Double.parseDouble(cellContent);
            }else if(classType.equals("java.lang.Float")|| classType.equals("float")){
                return Float.parseFloat(cellContent);
            }else if(classType.equals("java.lang.Long") || classType.equals("long")){
                return Long.parseLong(cellContent);
            }else if(classType.equals("java.lang.Short") || classType.equals("short")){
                cellContent = removepoint(cellContent);
                return Short.parseShort(cellContent);
            }else if(classType.equals("java.lang.Byte") || classType.equals("byte")){
                cellContent = removepoint(cellContent);
                return Byte.parseByte(cellContent);
            }else if(classType.equals("java.lang.Boolean") || classType.equals("boolean")){
                return Boolean.parseBoolean(cellContent);
            }else if(classType.equals("java.lang.Character") || classType.equals("char")){
                cellContent = removepoint(cellContent);
                return cellContent.toCharArray()[0];
            }else{
                cellContent = removepoint(cellContent);
                return cellContent;
            }
        }catch(Exception e){
            log.error("转换数据"+cellvalue+"格式时,报错");
            throw new BusinessException("转换数据"+cellvalue+"格式时,报错");
        }


    }

    /**
     * 返回值，类型全部转为String
     * @param xssfRow
     * @return
     */
     private String getStrValue(XSSFCell xssfRow) {
         if(null == xssfRow){
             return null;
         }
         if (xssfRow.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
             return String.valueOf(xssfRow.getBooleanCellValue());
         } else if (xssfRow.getCellType() == Cell.CELL_TYPE_NUMERIC) {
             return String.valueOf(xssfRow.getNumericCellValue());
         } else {
             return String.valueOf(xssfRow.getStringCellValue());
         }
     }

     /**
      * 移除小数点
      * @param cellContent
      * @return
      */
     private String removepoint(String cellContent){
         int index = cellContent.indexOf(".");
         if(index == -1){
             return cellContent;
         }
         return cellContent.substring(0, index);
     }


     /**
         * 没有使用模板生成excel，放入流中，并返回流
         * @param title
         * @param headerlist
         * @param filedNamelist
         * @param exporttoexcelentitylist
         * @param out
         * @param pattern
         * @param pattern1
         */
        public ByteArrayOutputStream exportExcel(String title,AnalysisResponseEntity entity){
            String[] headerz = entity.getColumnNamez();
            List<String[]> exporttoexcelentitylist = entity.getErrorlist();
            Field[] filedNamez = entity.getColumnClassNamez();
            List<String>  errorMessageList = entity.getErrorMessagelist();
            ByteArrayOutputStream out = new ByteArrayOutputStream();
            /**
             *  声明一个工作薄  
             */
            XSSFWorkbook workbook = new XSSFWorkbook();  
            /**
             *  生成一个表格  
             */
            XSSFSheet sheet = workbook.createSheet(title);
            /**
             *  设置表格默认列宽度
             */
            sheet.setDefaultColumnWidth(headerz.length);  
            /**
             *  生成一个样式  
             */
            XSSFCellStyle style = workbook.createCellStyle();  
            /**
             *  设置这些样式  
             */
            style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);  
            style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);  
            style.setBorderBottom(HSSFCellStyle.BORDER_THIN);  
            style.setBorderLeft(HSSFCellStyle.BORDER_THIN);  
            style.setBorderRight(HSSFCellStyle.BORDER_THIN);  
            style.setBorderTop(HSSFCellStyle.BORDER_THIN);  
            style.setAlignment(HSSFCellStyle.ALIGN_CENTER);  
            /**
             *  生成一个字体  
             */
            XSSFFont font = workbook.createFont();  
            font.setColor(HSSFColor.VIOLET.index);  
            font.setFontHeightInPoints((short) 12);  
            font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);  
            /**
             *  把字体应用到当前的样式  
             */
            style.setFont(font);  
            /**
             *  生成并设置另一个样式  
             */
            XSSFCellStyle style2 = workbook.createCellStyle();  
            style2.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);  
            style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);  
            style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);  
            style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);  
            style2.setBorderRight(HSSFCellStyle.BORDER_THIN);  
            style2.setBorderTop(HSSFCellStyle.BORDER_THIN);  
            style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);  
            style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);  
            /**
             *  生成另一个字体  
             */
            XSSFFont font2 = workbook.createFont();  
            font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);  
            /**
             *  把字体应用到当前的样式  
             */
            style2.setFont(font2);

            /**
             *  产生表格标题行  
             */
            XSSFRow titlerow = sheet.createRow(SHEET_START_ROW_NUMBER);  
            for (int i = 0; i < headerz.length; i++){
                XSSFCell cell = titlerow.createCell(i);  
                cell.setCellStyle(style);  
                XSSFRichTextString text = new XSSFRichTextString(headerz[i]);  
                cell.setCellValue(text);  
            }

         /**
          *  遍历集合数据，产生数据行  
          */
         if(exporttoexcelentitylist != null && exporttoexcelentitylist.size()>0){
                 for(int i=0;i<exporttoexcelentitylist.size();i++){

                     String[] entity1 = exporttoexcelentitylist.get(i);
                     XSSFRow row = sheet.createRow(i+SHEET_START_ROW_NUMBER+1);

                     for(int j=0;j<filedNamez.length;j++){
                     try {
                            XSSFCell cell = row.createCell(SHEET_START_COLUMN_NUMBER+j);
                            cell.setCellStyle(style2);
                            if(null != entity1[j]){
                                cell.setCellValue(entity1[j]);
                            }
                    } catch (SecurityException e) {
                        log.error("给Cell赋值时报错："+e.getMessage());
                        throw new BusinessException("给Cell赋值时报错："+e.getMessage());
                    }
                 } 
             }

         }

         //把错误信息放在sheet2中
         /**
         *  生成一个sheet  
         */
         XSSFSheet sheet2 = workbook.createSheet("错误信息");

         //循环赋值
         for(int i=0;i<errorMessageList.size();i++){
             XSSFRow title2row = sheet2.createRow(SHEET_START_ROW_NUMBER+i);
             XSSFCell cell = title2row.createCell(SHEET_START_COLUMN_NUMBER);
             cell.setCellValue(errorMessageList.get(i));
         }


         try {
                workbook.write(out);
            } catch (IOException e1) {
                log.error("写入workbook时报错："+e1.getMessage());
                throw new BusinessException("写入workbook时报错："+e1.getMessage());
            }

         return out;
        }

        /**
         * 需要模板，返回生成的excel路径，需要生成excel文件
         * @param sourcefile
         * @param entity
         * @return
         */
        public String createExcel(File sourcefile,AnalysisResponseEntity entity){
            File oldfile = entity.getFile();
            String filepath = oldfile.getAbsolutePath().substring(0,(int)oldfile.getAbsoluteFile().toString().length()-oldfile.getName().length());
            String filename = oldfile.getName().substring(0, oldfile.getName().replaceAll("_UN", "").indexOf("."))+"_ErrorList"+oldfile.getName().substring(oldfile.getName().indexOf("."));
            File destFile = new File(filepath+filename);

            //复制模板
            try {
                FileUtils.copyFile(sourcefile, destFile);
            } catch (IOException e) {
                log.error("复制文件报错！具体信息为"+e.getMessage());
                throw new BusinessException("复制文件报错！具体信息为"+e.getMessage());
            }

            //错误信息列表
            List<String[]> exporttoexcelentitylist = entity.getErrorlist();
            //列对应javabean的字段的Field
            Field[] filedNamez = entity.getColumnClassNamez();
//          //返回的输出流
//          ByteArrayOutputStream out = new ByteArrayOutputStream();
            FileOutputStream out = null;
            //错误信息
            List<String>  errorMessageList = entity.getErrorMessagelist();
            //表头
            String[] columnNamez = entity.getColumnNamez();
            /**
             *  声明一个工作薄  
             */
            XSSFWorkbook workbook = null;
            try {
                workbook = new XSSFWorkbook(new FileInputStream(destFile));
            } catch (FileNotFoundException e2) {
                log.error("文件："+destFile.getName()+",在转换为Excel时报错！，具体内容是："+e2.getMessage());
                throw new BusinessException("文件："+destFile.getName()+",在转换为Excel时报错！，具体内容是："+e2.getMessage());
            } catch (IOException e2) {
                log.error("文件："+destFile.getName()+",在转换为Excel时报错！，具体内容是："+e2.getMessage());
                throw new BusinessException("文件："+destFile.getName()+",在转换为Excel时报错！，具体内容是："+e2.getMessage());
            }
            /**
             *  取得第一个sheet表格  
             */
            XSSFSheet sheet = workbook.getSheetAt(0);

            //验证模板是否被更改
            XSSFRow titlerow = sheet.getRow(SHEET_START_ROW_NUMBER);
            for(int i=0;i<columnNamez.length;i++){
                if(!columnNamez[i].equals(titlerow.getCell(i+SHEET_START_COLUMN_NUMBER).getStringCellValue())){
                    log.error("上传的文件与模板不一致，请还原至模板的格式");
                    throw new BusinessException("文件模板被改动，请还原至模板的格式");
                }   
            }

         /**
          *  遍历集合数据，产生数据行  
          */
         if(exporttoexcelentitylist != null && exporttoexcelentitylist.size()>0){
                 for(int i=0;i<exporttoexcelentitylist.size();i++){

                     String[] entity1 = exporttoexcelentitylist.get(i);
                     XSSFRow row = sheet.createRow(i+SHEET_START_ROW_NUMBER+1);

                     for(int j=0;j<filedNamez.length;j++){
                     try {
                            XSSFCell cell = row.createCell(SHEET_START_COLUMN_NUMBER+j);
                            if(null != entity1[j]){
                                cell.setCellValue(entity1[j]);
                            }
                    } catch (SecurityException e) {
                        log.error("给Cell赋值时报错："+e.getMessage());
                        throw new BusinessException("给Cell赋值时报错："+e.getMessage());
                    }
                 } 
             }

                 //把错误信息放在sheet2中
                 /**
                 * 取得第二个sheet表格  
                 */
                 XSSFSheet sheet2 = workbook.getSheetAt(1);
                 //循环赋值
                 for(int i=0;i<errorMessageList.size();i++){
                     XSSFRow title2row = sheet2.createRow(SHEET_START_ROW_NUMBER+i);
                     XSSFCell cell = title2row.createCell(SHEET_START_COLUMN_NUMBER);
                     cell.setCellValue(errorMessageList.get(i));
                 }
         //如果没有数据，返回生成的地址为空
         }else{
             return null;
         }

         try {
                out = new FileOutputStream(destFile);
                workbook.write(out);
            } catch (IOException e1) {
                log.error("写入workbook时报错："+e1.getMessage());
                throw new BusinessException("写入workbook时报错："+e1.getMessage());
            }

         return destFile.getPath();
        }

        /**
         * 用模板生成excel,放入流中，不需要生成excel文件
         * @param title
         * @param headerlist
         * @param filedNamelist
         * @param exporttoexcelentitylist
         * @param out
         * @param pattern
         * @param pattern1
         */
        public ByteArrayOutputStream exportExcel(File file,AnalysisResponseEntity entity){
            //错误信息列表
            List<String[]> exporttoexcelentitylist = entity.getErrorlist();
            //列对应javabean的字段的Field
            Field[] filedNamez = entity.getColumnClassNamez();
            //返回的输出流
            ByteArrayOutputStream out = new ByteArrayOutputStream();
            //错误信息
            List<String>  errorMessageList = entity.getErrorMessagelist();
            //表头
            String[] columnNamez = entity.getColumnNamez();
            /**
             *  声明一个工作薄  
             */
            XSSFWorkbook workbook = null;
            try {
                workbook = new XSSFWorkbook(new FileInputStream(file));
            } catch (FileNotFoundException e2) {
                log.error("文件："+file.getName()+",在转换为Excel时报错！，具体内容是："+e2.getMessage());
                throw new BusinessException("文件："+file.getName()+",在转换为Excel时报错！，具体内容是："+e2.getMessage());
            } catch (IOException e2) {
                log.error("文件："+file.getName()+",在转换为Excel时报错！，具体内容是："+e2.getMessage());
                throw new BusinessException("文件："+file.getName()+",在转换为Excel时报错！，具体内容是："+e2.getMessage());
            }
            /**
             *  取得第一个sheet表格  
             */
            XSSFSheet sheet = workbook.getSheetAt(0);

            //验证模板是否被更改
            XSSFRow titlerow = sheet.getRow(SHEET_START_ROW_NUMBER);
            for(int i=0;i<columnNamez.length;i++){
                if(!columnNamez[i].equals(titlerow.getCell(i+SHEET_START_COLUMN_NUMBER).getStringCellValue())){
                    log.error("上传的文件与模板不一致，请还原至模板的格式");
                    throw new BusinessException("文件模板被改动，请还原至模板的格式");
                }   
            }

         /**
          *  遍历集合数据，产生数据行  
          */
         if(exporttoexcelentitylist != null && exporttoexcelentitylist.size()>0){
                 for(int i=0;i<exporttoexcelentitylist.size();i++){

                     String[] entity1 = exporttoexcelentitylist.get(i);
                     XSSFRow row = sheet.createRow(i+SHEET_START_ROW_NUMBER+1);

                     for(int j=0;j<filedNamez.length;j++){
                     try {
                            XSSFCell cell = row.createCell(SHEET_START_COLUMN_NUMBER+j);
                            if(null != entity1[j]){
                                cell.setCellValue(entity1[j]);
                            }
                    } catch (SecurityException e) {
                        log.error("给Cell赋值时报错："+e.getMessage());
                        throw new BusinessException("给Cell赋值时报错："+e.getMessage());
                    }
                 } 
             }

                 //把错误信息放在sheet2中
                 /**
                 *  生成一个sheet  
                 */
                 XSSFSheet sheet2 = workbook.createSheet("错误信息");

                 //循环赋值
                 for(int i=0;i<errorMessageList.size();i++){
                     XSSFRow title2row = sheet2.createRow(SHEET_START_ROW_NUMBER+i);
                     XSSFCell cell = title2row.createCell(SHEET_START_COLUMN_NUMBER);
                     cell.setCellValue(errorMessageList.get(i));
                 }
         }

         try {
                workbook.write(out);
            } catch (IOException e1) {
                log.error("写入workbook时报错："+e1.getMessage());
                throw new BusinessException("写入workbook时报错："+e1.getMessage());
            }

         return out;
        }

        /**
         * 导出excel
         * @param os
         * @param response
         */
         public  void download(String path, HttpServletResponse response) {

             if(StringUtils.isEmpty(path)){
                 log.error("path为空！");
                 throw new BusinessException("path为空！");
             }

             File file = new File(path);
             if(!file.exists()){
                 log.error("路径："+path+",对应的文件不存在！");
                 throw new BusinessException("路径："+path+",对应的文件不存在！");
             }

              try {
                    OutputStream os = new FileOutputStream(file); 
                    byte[] content = ((ByteArrayOutputStream) os).toByteArray();
                    InputStream is = new ByteArrayInputStream(content);

                    response.reset();
                    response.setContentType("application/vnd.ms-excel;charset=utf-8");
                    response.setHeader("Content-Disposition", "attachment;filename=" + new String(("transactionManagementlist".toString() + ".xls").getBytes(), "iso-8859-1"));
                    response.setCharacterEncoding("utf-8");
                    ServletOutputStream out = response.getOutputStream();

                    BufferedInputStream bis = null;
                    BufferedOutputStream bos = null;

                    try {

                        bis = new BufferedInputStream(is);
                        bos = new BufferedOutputStream(out);

                        byte[] buff = new byte[1024];
                        int bytesRead;

                        // Simple read/write loop.
                        while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
                            bos.write(buff, 0, bytesRead);
                        }

                    } catch (final IOException e) {
                        throw e;
                    } finally {
                        if (bis != null)
                            bis.close();
                        if (bos != null)
                            bos.close();
                        os.close();

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

}

两个Javabean 一个是AnalysisFileEntity，这个定义是excel文件中的内标题要和传过来的类对应起来的类。

package com.deppon.tps.module.aboutexcel.server.shared.domain;

import java.io.File;

public class AnalysisFileEntity {

    //导入的文件
    public File file;
    //解析成class，所需的name
    public String className;
//  //解析成class，所需的path
//  public String classPath;
    /**
     * @return the file
     */
    public File getFile() {
        return file;
    }
    /**
     * @param file the file to set
     */
    public void setFile(File file) {
        this.file = file;
    }
    /**
     * @return the className
     */
    public String getClassName() {
        return className;
    }
    /**
     * @param className the className to set
     */
    public void setClassName(String className) {
        this.className = className;
    }
//  /**
//   * @return the classPath
//   */
//  public String getClassPath() {
//      return classPath;
//  }
//  /**
//   * @param classPath the classPath to set
//   */
//  public void setClassPath(String classPath) {
//      this.classPath = classPath;
//  }




}

这个JavaBean-AnalysisResponseEntity是存储解析的数据用的

package com.deppon.tps.module.aboutexcel.server.shared.domain;

import java.lang.reflect.Field;
import java.util.LinkedList;
import java.util.List;

public class AnalysisResponseEntity extends AnalysisFileEntity{

    //表头汉字对应的class字段--数组
    Field[] columnClassNamez = null;
    //表头汉字--数组
    String[] columnNamez = null; 
    //把excel中正确的数据存储起来
    List<Object> list = new LinkedList<Object>();
    //把excel中错误的数据存储起来
    List<String[]> errorlist = new LinkedList<String[]>();
    //把excel中错误的数据存储起来
    List<String> errorMessagelist = new LinkedList<String>();
    /**
     * @return the columnClassNamez
     */
    public Field[] getColumnClassNamez() {
        return columnClassNamez;
    }
    /**
     * @param columnClassNamez the columnClassNamez to set
     */
    public void setColumnClassNamez(Field[] columnClassNamez) {
        this.columnClassNamez = columnClassNamez;
    }
    /**
     * @return the columnNamez
     */
    public String[] getColumnNamez() {
        return columnNamez;
    }
    /**
     * @param columnNamez the columnNamez to set
     */
    public void setColumnNamez(String[] columnNamez) {
        this.columnNamez = columnNamez;
    }
    /**
     * @return the list
     */
    public List<Object> getList() {
        return list;
    }
    /**
     * @param list the list to set
     */
    public void setList(List<Object> list) {
        this.list = list;
    }
    /**
     * @return the errorlist
     */
    public List<String[]> getErrorlist() {
        return errorlist;
    }
    /**
     * @param errorlist the errorlist to set
     */
    public void setErrorlist(List<String[]> errorlist) {
        this.errorlist = errorlist;
    }
    /**
     * @return the errorMessagelist
     */
    public List<String> getErrorMessagelist() {
        return errorMessagelist;
    }
    /**
     * @param errorMessagelist the errorMessagelist to set
     */
    public void setErrorMessagelist(List<String> errorMessagelist) {
        this.errorMessagelist = errorMessagelist;
    }
}

*这是测试类*

    @Test
    public void importFileData(){

        AnalysisFileEntity entity = new AnalysisFileEntity();
        File sourcefile = new File("d:\\314746\\Desktop\\test\\testexcel-UN.xlsx");
        File file = new File("d:\\314746\\Desktop\\test\\testexcel1.xlsx");
        entity.setFile(file);
        String className = "com.deppon.tps.module.aboutexcel.server.poidwexcel.shared.domain.testEntity";
        entity.setClassName(className);
        AnalysisResponseEntity  entity1 = poidwExcelUtlsServiceImpl.importFileData(entity);
        List<Object> list = entity1.getList();
        for(int i=0;i<list.size();i++){
            Object obj = list.get(i);
            testEntity entity2 = (testEntity)obj;
            System.out.println(entity2.getIndex());
        }
        //对错误信息的处理
        if(null !=entity1.getErrorlist() && entity1.getErrorlist().size()>0){
            String path = poidwExcelUtlsServiceImpl.createExcel(sourcefile,entity1);
            System.out.println(path);
        }
    }