Excel导入保存附件和解析数据

一，前端上传附件的组件

1、先给一个下载模板的按钮

　　　　　　// 下载Excel模板
downLoadExcel: function () {
window.open(GLOBAL_CONFIG.webSiteRoot + "/main/common/files/xxx.xls");
},

2、Element上传控件

　　　　　　　　　　　　　　 导入Excel

3、js中上传有关

var vue = new Vue({
el: '#app',
data: {
// 导入的后台接口地址
importFileUrl : '/excellImport/importFile',

methods：{
// 上传前对文件的类型和大小判断
beforeUpload : function (file) {
var self = this;
const extension = file.name.split('.')[1] === 'xls'
const extension2 = file.name.split('.')[1] === 'xlsx'
const isLt2M = file.size / 1024 / 1024 < 50
if (!extension && !extension2 ) {
alert('上传文件只能是 xls、xlsx 格式!')
}
if (!isLt2M) {
alert('上传文件大小不能超过 50MB!')
}
console.log(file);
return extension || extension2 && isLt2M
},
// 文件上传成功
uploadSuccess: function (response, file, flieList) {
this.initTable();
console.log(response);
alert(response.msg);
},
// 文件上传失败
uploadFail: function (err, file, fileList) {
alert('上传失败，请重试！');
},

}

其中，data 中的 importFileUrl : '/excellImport/importFile', 就是后台的接口地址；

二、java代码

@SuppressWarnings({"unchecked", "finally" })
@ResponseBody
@RequestMapping(value="/importFile",method= RequestMethod.POST)
@ApiOperation("导入excel文件操作接口")
public JsonResult importFile(HttpServletRequest request, HttpServletResponse response,
@RequestParam("file") MultipartFile[] excelFile, XfUser user) throws AccessExpiredException, DaoAccessException, Exception {
System.out.println("==========================================-进入导入excel文件操作接口==========================");
JsonResult jsonResult = JsonResult.getDefaultResult();
try {
response.setCharacterEncoding("utf-8");
// 手动调用PrintWriter向客户端输入返回值，若本方法是有返回值的，则不需要
// PrintWriter out = response.getWriter();

        //文件保存本地目录路径  
        String savePath = request.getSession().getServletContext().getRealPath(PATH\_LINE) + "main"+PATH\_LINE+"xxx"+PATH\_LINE+"xxx"+PATH\_LINE+"xxx"+PATH\_LINE+"upload";  
        //文件保存目录URL  
        String saveUrl = request.getContextPath() + PATH\_LINE +"upload"+PATH\_LINE+"file"+PATH\_LINE;

        if(!ServletFileUpload.isMultipartContent(request)){  

// out.print(getError("请选择文件。"));
// out.close();
jsonResult.setMsg("请选择文件。");
return jsonResult;
}
//检查目录
File uploadDir = new File(savePath);
if(!uploadDir.isDirectory()){
uploadDir.mkdirs(); //目录不存在就创建目录
}
//检查目录写权限
if(!uploadDir.canWrite()){
// out.print(getError("上传目录没有写权限。"));
// out.close();
jsonResult.setMsg("上传目录没有写权限。");
return jsonResult;
}

        String dirName = request.getParameter("dir");  
        if (dirName == null) {  
            dirName = "file";  
        }

        //定义允许上传的文件扩展名  
        Map<String, String> extMap = new HashMap<String, String>();  
        extMap.put("image", "gif,jpg,jpeg,png,bmp");  
        extMap.put("flash", "swf,flv");  
        extMap.put("media", "swf,flv,mp3,wav,wma,wmv,mid,avi,mpg,asf,rm,rmvb");  
        extMap.put("file", "doc,docx,xls,xlsx,ppt,htm,html,xml,txt,zip,rar,gz,bz2");

        if(!extMap.containsKey(dirName)){  

// out.print(getError("目录名不正确。"));
// out.close();
jsonResult.setMsg("目录名不正确。");
return jsonResult;
}
//创建文件夹
savePath += PATH_LINE+dirName + PATH_LINE;
saveUrl += PATH_LINE;
File saveDirFile = new File(savePath);
if (!saveDirFile.exists()) {
saveDirFile.mkdirs(); //保存到项目工程文件夹指定目录中
}

        SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");  
        String ymd = sdf.format(new Date());  
        savePath += ymd + PATH\_LINE;  
        saveUrl += ymd + PATH\_LINE;  
        File dirFile = new File(savePath); //存到本地之后在获取解析  
        if (!dirFile.exists()) {  
            dirFile.mkdirs();  
        }

        //最大文件大小  
        long maxSize = 10000000;

        // 保存文件  
        for(MultipartFile iFile : excelFile){  
            String fileName = iFile.getOriginalFilename();

            //检查文件大小  
            if(iFile.getSize() > maxSize){  

// out.print(getError("上传文件大小超过限制。"));
// out.close();
jsonResult.setMsg("上传文件大小超过限制。");
return jsonResult;
}
//检查扩展名
String fileExt = fileName.substring(fileName.lastIndexOf(".") + 1).toLowerCase();
if(!Arrays.asList(extMap.get(dirName).split(",")).contains(fileExt)){
//return getError("上传文件扩展名是不允许的扩展名。\n只允许" + extMap.get(dirName) + "格式。");
// out.print(getError("上传文件扩展名是不允许的扩展名。\n只允许" + extMap.get(dirName) + "格式。"));
// out.close();
jsonResult.setMsg("上传文件扩展名是不允许的扩展名。\n只允许" + extMap.get(dirName) + "格式。");
return jsonResult;
}

            // 解析Excel数据存入数据库  
            JsonResult<Object> jsonResultHandle  = handleExcelData(iFile, user); //调用另外一个方法解析excel中的数据交互项目生产库  
            // 解析或者入库有问题则反馈到前端  
            if(!jsonResultHandle.getSuccess()){  
                // 返回导入信息到前端页面  
                jsonResult.setSuccess(false);  
                jsonResult.setMsg(jsonResultHandle.getMsg());  
                return jsonResult;  
            }

            // 返回导入信息到前端页面  
            jsonResult.setMsg(jsonResultHandle.getMsg());

            SimpleDateFormat df = new SimpleDateFormat("yyyyMMddHHmmss");  
            String newFileName = df.format(new Date()) + "\_" + iFile.getName() + "." + fileExt;  
            try{  
                File uploadedFile = new File(savePath, newFileName);

                // 写入文件  
                FileUtils.copyInputStreamToFile(iFile.getInputStream(), uploadedFile);  
            }catch(Exception e){  

// out.print(getError("上传文件失败。"));
// out.close();
jsonResult.setMsg("上传文件失败。");
return jsonResult;
}

            JSONObject obj = new JSONObject();  
            obj.put("error", 0);  
            obj.put("url", saveUrl + newFileName);

// out.print(obj.toJSONString());
// out.close();
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
logger.error("", e);
System.out.println(e);

    } finally {  
        System.out.println("===========================================结束导入excel文件操作接口==");  
        return jsonResult;  
    }

}

/\*\*  
 \*  解析Excel数据存入数据库  
 \*  @param file  
 \*  @param user  
 \*  @return  
 \*/  
@SuppressWarnings("unchecked")  
public JsonResult<Object> handleExcelData(MultipartFile file, XfUser user) throws Exception{  
    System.out.println("==================================================开始解析Excel数据存入数据库==");  
    // 返回导入的结果信息  
    String resultString = "";  
    JsonResult<Object> jsonResult = JsonResult.getDefaultResult();  
    if (file != null) {  
        try {  
            List<ImportexcelParam> ImportexcelParamList = null;

            // 获取解析的excel数据  
            JsonResult<List<List<String>>> JsonResultList = ExcelUtil.parseExcel(file.getInputStream(),  
                    file.getOriginalFilename());  
            // 解析有问题反馈到调用地方  
            if(!JsonResultList.getSuccess()){  
                jsonResult.setSuccess(false);  
                jsonResult.setMsg(JsonResultList.getMsg());  
                return jsonResult;  
            }  
            // excel数据解析成功，进行获取和入库处理  
            List<List<List<String>>> resultList = JsonResultList.getData();

            // 目前只检查第一个sheet  
            if (resultList != null && !resultList.isEmpty()) {  
                for (List<List<String>> sheetList : resultList) {  
                    ImportexcelParamList = new ArrayList<>();  
                    if (sheetList != null && !sheetList.isEmpty()) {  
                        if (sheetList.size() > 200) {  
                            jsonResult.setSuccess(false);  
                            jsonResult.setMsg("单次上传文件记录条数不能超过200条！");  
                            return jsonResult;  
                        }  
                        if(sheetList.get(0).size() != 20){  
                            jsonResult.setSuccess(false);  
                            jsonResult.setMsg("请使用正确的导入模板！");  
                            return jsonResult;  
                        }  
                        // 导入开始时间，毫秒  
                        long startTime = System.currentTimeMillis();  
                        // 记录导入成功的数据条数  
                        int successImportNum = 0;  
                        // 重复的编号记录  
                        String repeatInfo="";  
                        for (List<String> rowList : sheetList){  
                            if (!rowList.isEmpty()) {  
                                // 投诉人和投诉内容筛选有效行  
                                if(StringUtils.isNullOrEmpty(rowList.get(13))||StringUtils.isNullOrEmpty(rowList.get(14))){  
                                    continue;  
                                }  
                                // 导入数据举报编号去重  
                                XfImportexcel xfImportexcel = iXfImportexcelService.getXfImportexcelByBussinesNo(rowList.get(2));  
                                if (xfImportexcel != null) {  
                                    repeatInfo += "【";  
                                    repeatInfo += rowList.get(2);  
                                    repeatInfo += "】";  
                                    continue;  
                                }  
                                ImportexcelParam ImportexcelParam = new ImportexcelParam();  
                                ImportexcelParam.setStatus(rowList.get(0));//状态  
                                ImportexcelParam.setEmergencyLevel(rowList.get(1));//紧急程度  
                                ImportexcelParam.setBussinesNo(rowList.get(2));//业务编号  
                                ImportexcelParam.setCallSysNo(rowList.get(3));//来电编号  
                                ImportexcelParam.setRepeatFlag("初件".equals(rowList.get(4))?false:true);//重复标志  
                                ImportexcelParam.setReplyFlag("未答复".equals(rowList.get(5))?false:true);//答复标志  
                                ImportexcelParam.setProcessUnitName(rowList.get(6));//处理单位  
                                ImportexcelParam.setOperator(rowList.get(7));//经办人  
                                ImportexcelParam.setProcessamento(rowList.get(8));//处理方式  
                                ImportexcelParam.setProcessStatus(rowList.get(9));//处理状态  
                                ImportexcelParam.setCallPhoneNum(rowList.get(10));//来电号码  
                                ImportexcelParam.setLinkTelphone(rowList.get(11));//联系电话  
                                ImportexcelParam.setCreateBy(rowList.get(12));//创建人  
                                ImportexcelParam.setCallPerson(rowList.get(13));//来电人  
                                ImportexcelParam.setComplainContent(rowList.get(14));//投诉内容  
                                ImportexcelParam.setCallDate(StringUtils.isNullOrEmpty(rowList.get(15))?null:TimeUtils.string2Timestamp(DateUtil.YYYY\_MM\_DD\_HH\_MM\_SS, rowList.get(15)));//来电日期  
                                ImportexcelParam.setCallPhoneNum(rowList.get(16));//来电人数  
                                ImportexcelParam.setItemType(rowList.get(17));//事项分类  
                                ImportexcelParam.setCallPurpose(rowList.get(18));//来电目的  
                                ImportexcelParam.setProcessTimeLimit(StringUtils.isNullOrEmpty(rowList.get(19))?null:TimeUtils.string2Timestamp(DateUtil.YYYY\_MM\_DD\_HH\_MM\_SS, rowList.get(19)));//处理时限  
                                ImportexcelParamList.add(ImportexcelParam);

                            }  
                        }  
                        // 调用excel数据导入保存业务实现方法  
                        successImportNum = iXfImportexcelService.save(ImportexcelParamList, user);  
                        // 导入失败条数  
                        int failImportNum = ImportexcelParamList.size()-successImportNum;  
                        // 导入结束时间，毫秒  
                        long endTime = System.currentTimeMillis();  
                        if(repeatInfo!=""){  
                            resultString = "成功导入"+successImportNum+"条记录，失败"+failImportNum+"条；共耗时"+(endTime-startTime)+"毫秒;"+repeatInfo+"编号数据已存在";  
                        }else{  
                            resultString = "成功导入"+successImportNum+"条记录，失败"+failImportNum+"条；共耗时"+(endTime-startTime)+"毫秒。";  
                        }  
                        // 解析和入库都无问题，设置sucess和信息反馈  
                        jsonResult.setSuccess(true, resultString);  
                        System.out.println("================================================="+resultString+"=========");

                    }else{  
                        jsonResult.setSuccess(false, "文件没有数据记录");  
                    }  
                }  

// List> sheetList = resultList.get(0);

            }else{  
                jsonResult.setSuccess(false, "文件没有数据记录");  
            }

        }catch (Exception e) {  
            // TODO Auto-generated catch block  
            e.printStackTrace();  
        }  
        // String path = "E:/springUpload" + file.getOriginalFilename();  
        // 上传  
        // file.transferTo(new File(path));  
    }else{  
        jsonResult.setSuccess(false, "无法获取文件");  
    }  
    System.out.println("==================================================结束解析Excel数据存入数据库======");  
    return jsonResult;  
}

2、解析Excel封装工具类

package com.xxx.xx.utils;

import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.http.HttpServletResponse;

import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
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.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.mlsc.fw.common.exception.ParamDataException;
import com.mlsc.fw.common.utils.DateUtil;
import com.xxx.fw.web.JsonResult;

public class ExcelUtil {
private static Logger logger = Logger.getLogger(ExcelUtil.class);

public static final String OFFICE\_EXCEL\_2003\_POSTFIX = "xls";  
public static final String OFFICE\_EXCEL\_2010\_POSTFIX = "xlsx";  
public static final String CELL\_TYPE\_STRING="String";

/\*\*  
 \* 适用于第一行是标题行的excel，  
 \*  
 \* resultList --> sheetList --> rowList  
 \*  
 \* @throws Exception  
 \*/  
@SuppressWarnings("unchecked")  
public static JsonResult<List<List<String>>> parseExcel(InputStream in, String suffixName) throws ParamDataException {  
    JsonResult<List<List<String>>> jsonResult = JsonResult.getDefaultResult();  
    List<List<List<String>>> resultList = new ArrayList<>();  
    List<List<String>> sheetList = null;  
    List<String> rowList = null;  
    String fileType = suffixName.substring(suffixName.lastIndexOf(".") + 1, suffixName.length());  
    Workbook wb = null;  
    try {  
        if (fileType.equalsIgnoreCase(OFFICE\_EXCEL\_2003\_POSTFIX)) {  
            wb = new HSSFWorkbook(in);  
        } else if (fileType.equalsIgnoreCase(OFFICE\_EXCEL\_2010\_POSTFIX)) {  
            wb = new XSSFWorkbook(in);  
        } else {  
            jsonResult.setSuccess(false);  
            jsonResult.setMsg("请使用正确的导入模板");  
            return jsonResult;  
        }

        int sheetSize = wb.getNumberOfSheets();  
        // 文件中不止一张工作表  
        if(sheetSize>1){  
            jsonResult.setSuccess(false);  
            jsonResult.setMsg("请使用正确的导入模板");  
            return jsonResult;  
        }  
        for (int i = 0; i < sheetSize; i++) {// 遍历sheet页  
            Sheet sheet = wb.getSheetAt(i);

            int rowSize = sheet.getLastRowNum() + 1;  
            if(rowSize<=1){  
                jsonResult.setSuccess(false);  
                jsonResult.setMsg("导入模板没有数据记录");  
                return jsonResult;  
            }  
            sheetList = new ArrayList<>();  
            int columnSize = 0;

            // 从第一行标题行开始向下遍历行，包括第一行  
            for (int j = 0; j < rowSize; j++) {  
                Row row = sheet.getRow(j);

                // 遍历第一行作用是为了获取每一行的列数  
                if (j == 0) {  
                    // bypass the 1st row  
                    columnSize = row.getLastCellNum();  
                    continue;  
                }

                // 略过空行  
                if (row == null) {  
                    continue;  
                }  
                rowList = new ArrayList<>();  
                for (int m = 0; m < columnSize; m++) {  

// if (m == 0 && (row.getCell(3)==null||row.getCell(3).equals("")||row.getCell(3).getCellType() ==HSSFCell.CELL_TYPE_BLANK) ) {
// break;
// }
if (row.getCell(m) != null){
rowList.add(getValue(row.getCell(m)));
} else {
rowList.add("");
}
}
if (rowList.size() == columnSize)
sheetList.add(rowList);
}
resultList.add(sheetList);
}

    } catch (Exception e) {  
        logger.error("", e);  
        throw new ParamDataException("上传文件内容格式不正确，请检查文件内容格式！");  
    } finally {  
        if (wb != null) {  
            try {  
                wb.close();  
            } catch (Exception e) {  
                logger.error("关闭Workbook出现异常！", e);  
            }  
        }  
        if (in != null) {  
            try {  
                in.close();  
            } catch (Exception e) {  
                logger.error("关闭输入流出现异常！", e);  
            }  
        }  
    }  
    jsonResult.setSuccess(true);  
    jsonResult.setData(resultList);  
    return jsonResult;  
}

public static void exportByTemplate(FileInfo fileInfo, List<DataInfo> dataInfoList, HttpServletResponse response) {  
    try {  
        setResponseHeader(fileInfo.getGenerateFileName(), response);  
        XSSFWorkbook workbook = createWorkBook(fileInfo, dataInfoList, response);  
        outputFile(fileInfo, workbook, response);  
    } catch (UnsupportedEncodingException e) {  
        logger.error("", e);  
    }

}

private static void outputFile(FileInfo fileInfo, XSSFWorkbook workbook, HttpServletResponse response) {  
    BufferedOutputStream bufferedOutPut = null;  
    try {  
        OutputStream output = response.getOutputStream();  
        bufferedOutPut = new BufferedOutputStream(output);  
        bufferedOutPut.flush();  
        workbook.write(bufferedOutPut);  
    } catch (IOException e) {  
        logger.error("", e);  
    } finally {  
        if (workbook != null) {  
            try {  
                workbook.close();  
            } catch (IOException e) {  
            }  
        }  
        if (bufferedOutPut != null) {  
            try {  
                bufferedOutPut.close();  
            } catch (IOException e) {  
            }  
        }

    }  
}

@SuppressWarnings("deprecation")  
private static XSSFWorkbook createWorkBook(FileInfo fileInfo, List<DataInfo> dataInfoList,  
        HttpServletResponse response) {  
    XSSFWorkbook workbook = null;

    try {  
        File fi = new File(fileInfo.getTemplatePath() + fileInfo.getTemplateName());  
        FileInputStream fs = new FileInputStream(fi);  
        // 读取excel模板  
        workbook = new XSSFWorkbook(fs);  
        if (dataInfoList == null || dataInfoList.size() == 0) {  
            return workbook;  
        }  
        int rowIndex = 0;  
        int columnIndex = 0;  
        // sheet  
        for (DataInfo dataInfo : dataInfoList) {  
            if(dataInfo.getSheetIndex()==null){  
                continue;  
            }  
            XSSFSheet sheet = workbook.getSheetAt(dataInfo.getSheetIndex());  
            rowIndex = dataInfo.getRowStart();  
            if(StringUtils.isNotEmpty(dataInfo.getModifiedTitle())){  
                CellStyle cs=sheet.getRow(0).getCell(0).getCellStyle();  
                sheet.getRow(0).getCell(0).setCellValue(dataInfo.getModifiedTitle());  
                sheet.getRow(0).getCell(0).setCellStyle(cs);  
            }  
            if (dataInfo.getData() == null || dataInfo.getData().isEmpty()) {  
                continue;  
            }  
            XSSFRow styleRow = null;  
            CellStyle style = null;  
            CellStyle style0 = null;  
            // row  
            for (int rIndex = 0; rIndex < dataInfo.getData().size(); rIndex++) {  
                if (dataInfo.getData().get(rIndex) == null || dataInfo.getData().get(rIndex).length == 0) {  
                    continue;  
                }  
                columnIndex = dataInfo.getColumnStart();  
                XSSFRow row = sheet.getRow(rowIndex);  
                if (row == null) {  
                    row = sheet.createRow(rowIndex);  
                }  
                if(rIndex==0){  
                    styleRow = sheet.getRow(rowIndex);  
                }

                // cell  
                for (int cIndex = 0; cIndex < dataInfo.getColumnLength(); cIndex++) {  
                    if(styleRow.getCell(columnIndex)==null){  
                        System.out.println(222);  
                    }  
                    style = styleRow.getCell(columnIndex).getCellStyle();

                    if(dataInfo.isGenIndex()){  
                        XSSFCell cell0 = row.getCell(0);  
                        if(cell0==null){  
                            cell0=row.createCell(0);  
                        }  
                        cell0.setCellValue(rIndex+1);  
                        style0 = styleRow.getCell(0).getCellStyle();  
                        cell0.setCellStyle(style0);  
                    }  
                    XSSFCell cell = row.getCell(columnIndex);  
                    if (cell == null) {  
                        cell = row.createCell(columnIndex);  
                    }  

// if(cIndex==17){
// System.out.println(333);
// }
// System.out.println("sheet:"+dataInfo.getSheetIndex()+"/rIndex:"+rIndex+"/cIndex:"+cIndex);
// if(null == dataInfo.getData().get(rIndex)[cIndex]){
// System.out.println(111);
// }
if(dataInfo.getTypeMap()!=null && dataInfo.getTypeMap().containsKey(cIndex)){
if(CELL_TYPE_STRING.equals(dataInfo.getTypeMap().get(cIndex))){
cell.setCellValue(dataInfo.getData().get(rIndex)[cIndex]);
}
}else if(null != dataInfo.getData().get(rIndex)[cIndex]
&& dataInfo.getData().get(rIndex)[cIndex].matches("^(-?\\d+)(\\.\\d+)?$")){
cell.setCellValue(Double.parseDouble(dataInfo.getData().get(rIndex)[cIndex]));
}else{
cell.setCellValue(dataInfo.getData().get(rIndex)[cIndex]);
}
cell.setCellStyle(style);
columnIndex++;
}
rowIndex++;
}
}
List hideSheetList=new ArrayList<>();
for(DataInfo dataInfo : dataInfoList){
hideSheetList.add(dataInfo.getSheetIndex());
}

        for(int i=0;i<workbook.getNumberOfSheets();i++){  
            if(!hideSheetList.contains(i)){  
                workbook.setSheetHidden(i, 2);  
            }  
        }

    } catch (IOException e) {  
        logger.error("", e);  
    }  
    return workbook;  
}

@SuppressWarnings("deprecation")  
private static String getValue(Cell cell) {  
    DecimalFormat df = new DecimalFormat("#.###");  
    if (cell == null) {  
        return "";  
    }  
    switch (cell.getCellType()) {  
        case HSSFCell.CELL\_TYPE\_NUMERIC:  
            if (HSSFDateUtil.isCellDateFormatted(cell)) {  
                SimpleDateFormat sdf = new SimpleDateFormat(DateUtil.YYYY\_MM\_DD);  
                return sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));  
            }  
            return df.format(cell.getNumericCellValue());  
        case HSSFCell.CELL\_TYPE\_STRING:  
            return cell.getStringCellValue();  
        case HSSFCell.CELL\_TYPE\_FORMULA:  
            return cell.getCellFormula();  
        case HSSFCell.CELL\_TYPE\_BLANK:  
            return "";

    }  
    return "";  
}

@SuppressWarnings("unused")  
public static String  getExcelAcceptDepartment(InputStream in, String suffixName) throws ParamDataException {  
    String header = "";  
    String fileType = suffixName.substring(suffixName.lastIndexOf(".") + 1, suffixName.length());  
    Workbook wb = null;  
    try {  
        if (fileType.equalsIgnoreCase(OFFICE\_EXCEL\_2003\_POSTFIX)) {  
            wb = new HSSFWorkbook(in);  
        } else if (fileType.equalsIgnoreCase(OFFICE\_EXCEL\_2010\_POSTFIX)) {  
            wb = new XSSFWorkbook(in);  
        } else {  
            throw new ParamDataException("读取的不是excel文件");  
        }

        int sheetSize = wb.getNumberOfSheets();  
        for (int i = 0; i < sheetSize; i++) {// 遍历sheet页  
            Sheet sheet = wb.getSheetAt(i);  
            header = getValue(sheet.getRow(2).getCell(1)).trim();  
            break;  
        }  
    } catch (Exception e) {  
        logger.error("", e);  
        throw new ParamDataException("上传文件内容格式不正确，请检查文件内容格式！");  
    } finally {  
        if (wb != null) {  
            try {  
                wb.close();  
            } catch (Exception e) {  
                logger.error("关闭Workbook出现异常！", e);  
            }  
        }  
        if (in != null) {  
            try {  
                in.close();  
            } catch (Exception e) {  
                logger.error("关闭输入流出现异常！", e);  
            }  
        }  
    }  
    return header;  
}

private static void setResponseHeader(String fileName, HttpServletResponse response)  
        throws UnsupportedEncodingException {  
    response.reset();  
    // SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");  
    // String date = sdf.format(new Date());  
    // String newFileName=fileName+date;  
    // 指定下载的文件名  
    response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));  
    response.setContentType("application/vnd.ms-excel;charset=UTF-8");  
    response.setHeader("Pragma", "no-cache");  
    response.setHeader("Cache-Control", "no-cache");  
    response.setDateHeader("Expires", 0);  
}  

}