excel导入

读取Excel中的数据，然后做判断（读取Excel用ExcelUtils）

 

import io.swagger.annotations.Api;
import io.swagger.annotations.ApiImplicitParam;
import io.swagger.annotations.ApiImplicitParams;
import io.swagger.annotations.ApiOperation;
import lombok.extern.slf4j.Slf4j;import org.apache.commons.collections4.CollectionUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.web.bind.annotation.*;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.io.FileOutputStream;
import java.util.*;

@Api(tags = "导入")
@Slf4j
@RestController
@RequestMapping("scoreImport/v1")
@ResponseWrapper
public class EsScoreImportController {

    @Resource
    private UserClient userClient;
    @Resource
    private EsPhaseService esPhaseService;
    @Resource
    private EsScoreService esScoreService;
    @Resource
    private EsSubjectService esSubjectService;


    @ApiOperation(value = "下载导入模板", produces = "application/octet-stream")
    @GetMapping("/template")
    public void downloadTemplate(@Principal String userId, HttpServletResponse response){
        List<String> rowTitleList = getRowTitleList();
        int size = rowTitleList.size();

        //创建一个Excel中的sheet
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet();
        //设置列宽
        for(int i=0;i<size;i++){
            sheet.setColumnWidth(i,15*256);
        }
        //单元格样式 : 新建一个样式--设置字体--设置文本格式(居中or居左or居右)
        HSSFCellStyle titleStyle = workbook.createCellStyle();
        HSSFFont titleFont = workbook.createFont();
        titleStyle.setFont(titleFont);
        titleStyle.setAlignment(HorizontalAlignment.CENTER);
        titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        //首行注意事项：因为与首行的样式不同，所以要重新创建
        // 设置单元格样式(字体颜色、行高、文本格式、换行)
        HSSFCellStyle remarkStyle = workbook.createCellStyle();
        HSSFFont remarkFont = workbook.createFont();
        remarkFont.setFontHeightInPoints((short)9);
        remarkFont.setColor(HSSFFont.COLOR_RED);
        remarkStyle.setFont(remarkFont);
        remarkStyle.setAlignment(HorizontalAlignment.LEFT);
        remarkStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        remarkStyle.setWrapText(true);
        //合并单元格
        CellRangeAddress car = new CellRangeAddress(0, 0, 0, size - 1);
        sheet.addMergedRegion(car);

        HSSFRow remarkRow = sheet.createRow(0);
        remarkRow.setHeightInPoints(3*sheet.getDefaultRowHeightInPoints());
        HSSFCell remarkCell = remarkRow.createCell(0);
        String remark = getRemark();
        remarkCell.setCellValue(new HSSFRichTextString(remark));
        remarkCell.setCellStyle(remarkStyle);

        //表头
        HSSFRow rowTitle = sheet.createRow(1);
        for(int i=0; i<size;i++){
            HSSFCell cell = rowTitle.createCell(i);
            cell.setCellValue(new HSSFRichTextString(rowTitleList.get(i)));
            cell.setCellStyle(titleStyle);
        }

        String fileName = "成绩导入";

        ExportUtils.outputData(workbook,fileName,response);
    }

    @ApiOperation("模板校验")
    @ApiImplicitParam(name = "filePath",value = "文件路径",required = true)
    @GetMapping("/validate")
    public String validate(@RequestParam String filePath){
        try {
            List<String> rowTitleList = getRowTitleList();
            List<String[]> dataList = ExcelUtils.readExcelIgnoreDesc(filePath, rowTitleList.size());
            String errorDataMsg = templateValidate(dataList, rowTitleList);
            if (StringUtils.isNotBlank(errorDataMsg)){
                throw new LocalRuntimeException(errorDataMsg);
            }
        }catch (LocalRuntimeException e){
            throw new LocalRuntimeException(e.getMessage());
        }catch (Exception e){
            e.printStackTrace();
            throw new LocalRuntimeException("上传文件不符合模板要求！");
        }
        return null;
    }

    @ApiOperation("数据导入")
    @ApiImplicitParams({
            @ApiImplicitParam(name = "phaseId", value = "轮次id", required = true),
            @ApiImplicitParam(name = "filePath", value = "文件路径", required = true),
            @ApiImplicitParam(name = "schoolId", value = "学校id", required = true),
            @ApiImplicitParam(name = "subjectId", value = "科目id", required = true)
    })
    @PostMapping("/import")
    public ImportResultDto dataImport(@Principal String userId, @RequestParam String phaseId, @RequestParam String filePath,
                                      @RequestParam String schoolId, @RequestParam String subjectId){

        log.info("业务数据处理中......");
        List<String> titleList = getRowTitleList();

        //获取上传数据，第一行行标是0
        List<String[]> rowData = ExcelUtils.readExcelIgnoreDesc(filePath, titleList.size());
        rowData.remove(0);
        int totalSize = rowData.size();

        //判断基本数据
        if(CollectionUtils.isEmpty(rowData)){
            return new ImportResultDto(totalSize,0,totalSize,"没有导入数据","");
        }

        EsPhaseDto esPhaseDto = esPhaseService.getPhaseById(phaseId);
        if(esPhaseDto==null){
            return new ImportResultDto(totalSize,0,totalSize,"所选轮次不存在","");
        }

        UserDTO userDTO = userClient.getUserById(userId);

        //查出该科目对应的满分
        EsSubjectDto esSubjectDto = esSubjectService.getSubjectBySubjectId(phaseId, subjectId);

        //先查出已存在的成绩名单 成绩名单：先
        List<EsScoreDto> esScoreDtoList = esScoreService.listScoreByPhaseId(new EsCommonQueryDto().setPhaseId(phaseId).setSchoolId(schoolId)
                .setSubjectId(subjectId), userDTO.getUnitId());
        //判断查出的名单中是否有相同的身份证号
        Set<String> sameIdCards = new HashSet<>();
        // 查出的名单中：一个身份证对应一条学生数据
        Map<String, EsScoreDto> esScoreDtoMap = new HashMap<>();
        esScoreDtoList.stream().filter(e->StringUtils.isNotBlank(e.getIdentityCard())).forEach(e->{
            if(esScoreDtoMap.containsKey(e.getIdentityCard())){
                sameIdCards.add(e.getIdentityCard());
            }else {
                esScoreDtoMap.put(e.getIdentityCard(),e);
            }
        });

        List<String[]> errorDataList = new ArrayList<>();
        int successCount = 0;
        int i = 0;
        EsScoreDto esScoreDto;
        List<EsScoreDto> saveList = new ArrayList<>();
        Set<String> sameRows = new HashSet<>();
        for(String[] arr : rowData){
            i++;
            String studentName = StringUtils.trim(arr[0]);
            if(StringUtils.isBlank(studentName)){
                String[] errorData = new String[4];
                errorData[0] = i+"";
                errorData[1] = "第"+i+"行";
                errorData[2] = studentName;
                errorData[3] = "学生姓名不能为空";
                errorDataList.add(errorData);
                continue;
            }
            String idCard = StringUtils.trim(arr[1]);
            if(StringUtils.isBlank(idCard)){
                String[] errorData = new String[4];
                errorData[0] = i+"";
                errorData[1] = "第"+i+"行";
                errorData[2] = idCard;
                errorData[3] = "身份证号不能为空";
                errorDataList.add(errorData);
                continue;
            }else if(sameIdCards.contains(idCard)){
                String[] errorData = new String[4];
                errorData[0] = i+"";
                errorData[1] = "第"+i+"行";
                errorData[2] = idCard;
                errorData[3] = "该身份证号对应的学生存在多个";
                errorDataList.add(errorData);
                continue;
            }else if(!esScoreDtoMap.containsKey(idCard)){
                String[] errorData = new String[4];
                errorData[0] = i+"";
                errorData[1] = "第"+i+"行";
                errorData[2] = idCard;
                errorData[3] = "该身份证号对应的学生不存在";
                errorDataList.add(errorData);
                continue;
            }else if(!studentName.equals(esScoreDtoMap.get(idCard).getStudentName())){
                String[] errorData = new String[4];
                errorData[0] = i+"";
                errorData[1] = "第"+i+"行";
                errorData[2] = "姓名："+studentName+"；身份证号："+idCard;
                errorData[3] = "学生姓名与身份证号不匹配";
                errorDataList.add(errorData);
                continue;
            }
            if(sameRows.contains(idCard+studentName)){
                String[] errorData = new String[4];
                errorData[0] = i+"";
                errorData[1] = "第"+i+"行";
                errorData[2] = "姓名："+studentName+"；身份证号："+idCard;
                errorData[3] = "表中存在重复的学生姓名与身份证号";
                errorDataList.add(errorData);
                continue;
            }
            sameRows.add(idCard+studentName);

            String schoolName = StringUtils.trim(arr[2]);
            if(StringUtils.isNotBlank(schoolName)){
                if(esScoreDtoMap.containsKey(idCard) &&
                        !schoolName.equals(esScoreDtoMap.get(idCard).getSchoolName())){
                    String[] errorData = new String[4];
                    errorData[0] = i+"";
                    errorData[1] = "第"+i+"行";
                    errorData[2] = schoolName;
                    errorData[3] = "学校名称与学生信息不匹配";
                    errorDataList.add(errorData);
                    continue;
                }
            }

            esScoreDto = esScoreDtoMap.get(idCard);
            String score = StringUtils.trim(arr[3]);
            if(StringUtils.isNotBlank(score)){
                if(Float.valueOf(score) > esSubjectDto.getFullScore()){
                    String[] errorData = new String[4];
                    errorData[0] = i+"";
                    errorData[1] = "第"+i+"行";
                    errorData[2] = score;
                    errorData[3] = "成绩不能超过满分";
                    errorDataList.add(errorData);
                    continue;
                }else if(!score.matches("^(0|[1-9]\\d{0,2})(\\.\\d)?$")){
                    String[] errorData = new String[4];
                    errorData[0] = i+"";
                    errorData[1] = "第"+i+"行";
                    errorData[2] = score;
                    errorData[3] = "成绩为3位整数1位小数";
                    errorDataList.add(errorData);
                    continue;
                }
                esScoreDto.setScore(Float.valueOf(score));
            }else {
                esScoreDto.setScore(null);
            }

            String scoreStatus = StringUtils.trim(arr[4]);
            if(StringUtils.isBlank(scoreStatus)){
                String[] errorData = new String[4];
                errorData[0] = i+"";
                errorData[1] = "第"+i+"行";
                errorData[2] = scoreStatus;
                errorData[3] = "状态不能为空";
                errorDataList.add(errorData);
            } else if(StringUtils.isNotBlank(scoreStatus)){
                if("正常".equals(scoreStatus)){
                    esScoreDto.setScoreStatus("0");
                }else if("缺考".equals(scoreStatus)){
                    esScoreDto.setScoreStatus("1");
                }else if("作弊".equals(scoreStatus)){
                    esScoreDto.setScoreStatus("2");
                }else {
                    String[] errorData = new String[4];
                    errorData[0] = i+"";
                    errorData[1] = "第"+i+"行";
                    errorData[2] = scoreStatus;
                    errorData[3] = "状态有误";
                    errorDataList.add(errorData);
                    continue;
                }
            }
            saveList.add(esScoreDto);
            successCount++;
        }

        //错误数据导出
        String errorExcelPath = "";
        if(CollectionUtils.isNotEmpty(errorDataList)){
            HSSFWorkbook workbook = new HSSFWorkbook();
            HSSFSheet sheet = workbook.createSheet();

            List<String> titleList2 = getRowTitleList();
            titleList2.add("错误数据");
            titleList2.add("错误原因");

            //单元格样式
            HSSFCellStyle headStyle = workbook.createCellStyle();
            headStyle.setAlignment(HorizontalAlignment.CENTER);
            headStyle.setVerticalAlignment(VerticalAlignment.CENTER);

            HSSFCellStyle cellStyle = workbook.createCellStyle();
            cellStyle.setAlignment(HorizontalAlignment.LEFT);
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

            HSSFCellStyle errorStyle = workbook.createCellStyle();
            errorStyle.setAlignment(HorizontalAlignment.CENTER);
            errorStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            HSSFFont font = workbook.createFont();
            font.setColor(HSSFFont.COLOR_RED);
            errorStyle.setFont(font);

            HSSFRow rowTitle = sheet.createRow(0);
            for(int j=0;j<titleList2.size();j++){
                sheet.setColumnWidth(j,15*256);
                HSSFCell cell = rowTitle.createCell(j);
                cell.setCellValue(new HSSFRichTextString(titleList2.get(j)));
                cell.setCellStyle(headStyle);
            }

            for(int j=0;j<errorDataList.size();j++){
                HSSFRow row = sheet.createRow(j + 1);
                //errorDataList.get(j) 得到的是一个数组
                //dataDetail 得到的是出错的那条数据
                String[] dataDetail = rowData.get(Integer.parseInt(errorDataList.get(j)[0]) - 1);
                for(int k=0;k<titleList2.size();k++){
                    HSSFCell cell = row.createCell(k);
                    if(k<titleList2.size()-2){
                        cell.setCellValue(new HSSFRichTextString(dataDetail[k]));
                        cell.setCellStyle(cellStyle);
                    }else if(k==titleList2.size()-2){
                        cell.setCellValue(new HSSFRichTextString(errorDataList.get(j)[2]));
                        cell.setCellStyle(errorStyle);
                    }else {
                        cell.setCellValue(new HSSFRichTextString(errorDataList.get(j)[3]));
                        cell.setCellStyle(errorStyle);
                    }
                }
            }
            errorExcelPath=saveErrorExcel(filePath,workbook);
        }

        try{
            esScoreService.updateScoreList(userDTO.getUnitId(),saveList);
        }catch (Exception e){
            e.printStackTrace();
            return new ImportResultDto(totalSize,0,totalSize,"导入出错",errorExcelPath);
        }
        int errorCount = totalSize - successCount;
        log.info("导入结束......");
        return new ImportResultDto(totalSize,successCount,errorCount,"",errorExcelPath);
    }

    public List<String> getRowTitleList(){
        List<String> rowTitleList = new ArrayList<>();
        rowTitleList.add("*姓名");
        rowTitleList.add("*身份证号");
        rowTitleList.add("学校");
        rowTitleList.add("考试成绩");
        rowTitleList.add("*状态");
        return rowTitleList;
    }

    private String getRemark(){
        String remark = "填写注意：\n" + "1.带*为必填项\n";
        return remark;
    }

    public String templateValidate(List<String[]> allDataList, List<String> titleList){
        String errorDataMsg = "";
        if(CollectionUtils.isNotEmpty(allDataList)){
            String[] realTitles = allDataList.get(0);
            if(realTitles != null){
                if(realTitles.length != titleList.size()){
                    return errorDataMsg = "导入数据列表(" + realTitles.length +")与模板列表数不符("
                            + titleList.size() + ")";
                }
                for(int i=0; i<realTitles.length; i++){
                    if(!titleList.contains(realTitles[i])){
                        errorDataMsg = "模板中不存在列名：" + realTitles[i];
                        break;
                    }
                }
            }
        }else {
            errorDataMsg = "模板中不存在数据";
        }
        return errorDataMsg;
    }

    /**
     * 将错误数据导入Excel
     * @param filePath
     * @param workbook
     * @return
     */
    public String saveErrorExcel(String filePath, HSSFWorkbook workbook) {
        if (StringUtils.isBlank(filePath)) {
            return "";
        }
        filePath = filePath.substring(0, filePath.lastIndexOf(".")) + "-errorMessage-" + System.currentTimeMillis() + filePath.substring(filePath.lastIndexOf("."));
        FileOutputStream fileOutputStream = null;
        try {
            fileOutputStream = new FileOutputStream(filePath);
            workbook.write(fileOutputStream); // 输出文件
            fileOutputStream.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            ExportUtils.close(fileOutputStream);
        }
        return filePath;
    }

}

 

ExcelUtils.java

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
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.ss.util.NumberToTextConverter;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.BufferedReader;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.Reader;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

public class ExcelUtils {
    /**
     * 读取excel中的数据
     * @param path
     * @return List<String   [   ]>
     */
    public static List<String[]> readExcel(String path, int totalRow) {
        if (totalRow <= 0) {
            return new ArrayList<String[]>();
        }
        if (path != null && !path.equals("")) {
            String ext = getExt(path);
            if (ext != null && !ext.equals("")) {
                if (ext.equals("xls")) {
                    return readXls(path, 1, totalRow);
                } else if (ext.equals("xlsx")) {
                    return readXlsx(path, 1, totalRow);
                }
            }
        }
        return new ArrayList<String[]>();
    }

    /**
     * 读取excel中的数据
     * 动态获取说明可有可无
     * @param path
     * @return List<String   [   ]>
     */
    public static List<String[]> readExcelIgnoreDesc(String path, int totalRow) {
        if (totalRow <= 0) {
            return new ArrayList<String[]>();
        }
        if (path != null && !path.equals("")) {
            String ext = getExt(path);
            if (ext != null && !ext.equals("")) {
                if (ext.equals("xls")) {
                    return readXls(path, -1, totalRow);
                } else if (ext.equals("xlsx")) {
                    return readXlsx(path, -1, totalRow);
                }
            }
        }
        return new ArrayList<String[]>();
    }

    /**
     * 读取excel中的数据
     * 精确指定行数
     * @param path
     * @return List<String   [   ]>
     */
    public static List<String[]> readExcelByRow(String path, int startRow,
                                                int totalCell) {
        if (totalCell <= 0) {
            return new ArrayList<String[]>();
        }
        if (path != null && !path.equals("")) {
            String ext = getExt(path);
            if (ext != null && !ext.equals("")) {
                if (ext.equals("xls")) {
                    return readXls(path, startRow, totalCell);
                } else if (ext.equals("xlsx")) {
                    return readXlsx(path, startRow, totalCell);
                }
            }
        }
        return new ArrayList<String[]>();
    }

    /**
     * 读取后缀为xls的excel文件的数据
     *
     * @param path
     * @return List<String   [   ]>
     */
    private static List<String[]> readXls(String path, int startRow,
                                          int totalCell) {

        HSSFWorkbook hssfWorkbook = null;
        try {
            InputStream is = new FileInputStream(path);
            hssfWorkbook = new HSSFWorkbook(is);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }

        // 若startRow为-1，判断是否首行是否存在合并单元格
        if (startRow == -1) {
            Sheet sheet = hssfWorkbook.getSheetAt(0);
            int mergeCount = sheet.getNumMergedRegions();
            if (mergeCount == 0) {
                startRow = 0;
            } else {
                startRow = 1;
            }
        }

        List<String[]> list = new ArrayList<String[]>();
        if (hssfWorkbook != null) {
            // Read the Sheet
            HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(0);
            if (hssfSheet == null) {
                return list;
            }
            // Read the Row
            for (int rowNum = startRow; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
                HSSFRow hssfRow = hssfSheet.getRow(rowNum);
                if (hssfRow != null) {
                    boolean hasValue = false;
                    String[] data = new String[totalCell];
                    for (int i = 0; i < totalCell; i++) {
                        Cell cell = hssfRow.getCell(i);
                        data[i] = getCellValue(cell);
                        if (StringUtils.isNotBlank(data[i])){
                            hasValue = true;
                        }
                    }
                    if (hasValue) {
                        list.add(data);
                    }
                }
            }
        }
        return list;
    }
    
    private static List<String[]> readXlsByRows(String path, int startRow, int endRowExclusive) {
        HSSFWorkbook hssfWorkbook = null;
        try {
            InputStream is = new FileInputStream(path);
            hssfWorkbook = new HSSFWorkbook(is);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }

        // 若startRow为-1，判断是否首行是否存在合并单元格
        if (startRow == -1) {
            Sheet sheet = hssfWorkbook.getSheetAt(0);
            int mergeCount = sheet.getNumMergedRegions();
            if (mergeCount == 0) {
                startRow = 0;
            } else {
                startRow = 1;
            }
        }

        List<String[]> list = new ArrayList<String[]>();
        if (hssfWorkbook != null) {
            HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(0);
            if (hssfSheet == null) {
                return list;
            }
            int cellCount = 0;
            for (int rowNum = startRow; rowNum < endRowExclusive; rowNum++) {
                HSSFRow hssfRow = hssfSheet.getRow(rowNum);
                if(cellCount == 0) {
                    cellCount = hssfRow.getLastCellNum();
                }
                if (hssfRow != null) {
                    boolean hasValue = false;
                    String[] data = new String[cellCount];
                    for (int i = 0; i < cellCount; i++) {
                        Cell cell = hssfRow.getCell(i);
                        data[i] = getCellValue(cell);
                        if (StringUtils.isNotBlank(data[i])) {
                            hasValue = true;
                        }
                    }
                    if (hasValue) {
                        list.add(data);
                    }
                }
            }
        }
        return list;
    }

    /**
     * 根据excel单元格类型获取excel单元格值
     *
     * @param cell
     * @return nizq
     */
    private static String getCellValue(Cell cell) {
        String cellvalue = "";
        if (cell != null) {
            // 判断当前Cell的Type
            switch (cell.getCellType()) {
                // 如果当前Cell的Type为NUMERIC
                case HSSFCell.CELL_TYPE_NUMERIC: {
                    short format = cell.getCellStyle().getDataFormat();
                    if (format == 14 || format == 31 || format == 57
                            || format == 58) { // excel中的时间格式
                        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                        double value = cell.getNumericCellValue();
                        Date date = DateUtil.getJavaDate(value);
                        cellvalue = sdf.format(date);
                    }
                    // 判断当前的cell是否为Date
                    else if (HSSFDateUtil.isCellDateFormatted(cell)) { // 先注释日期类型的转换，在实际测试中发现HSSFDateUtil.isCellDateFormatted(cell)只识别2014/02/02这种格式。
                        // 如果是Date类型则，取得该Cell的Date值 // 对2014-02-02格式识别不出是日期格式
                        Date date = cell.getDateCellValue();
                        DateFormat formater = new SimpleDateFormat("yyyy-MM-dd");
                        cellvalue = formater.format(date);
                    } else { // 如果是纯数字
                        // 取得当前Cell的数值
                        cellvalue = NumberToTextConverter.toText(cell
                                .getNumericCellValue());
                    }
                    break;
                }
                // 如果当前Cell的Type为STRIN
                case HSSFCell.CELL_TYPE_STRING:
                    // 取得当前的Cell字符串
                    cellvalue = cell.getStringCellValue().replaceAll("'", "''");
                    break;
                case HSSFCell.CELL_TYPE_BLANK:
                    cellvalue = "";
                    break;
                // 默认的Cell值
                default: {
                    cellvalue = "";
                }
            }
        } else {
            cellvalue = "";
        }
        return cellvalue;
    }

    /**
     * 读取后缀为xlsx的excel文件的数据
     *
     * @param path
     * @param totalCell
     * @return List<String   [   ]>
     */
    private static List<String[]> readXlsx(String path, int startRow,
                                           int totalCell) {

        XSSFWorkbook xssfWorkbook = null;
        try {
            InputStream is = new FileInputStream(path);
            xssfWorkbook = new XSSFWorkbook(is);
        } catch (IOException e) {
            e.printStackTrace();
        }
        List<String[]> list = new ArrayList<String[]>();
        if (xssfWorkbook != null) {
            // Read the Sheet
            XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(0);
            if (xssfSheet == null) {
                return list;
            }
            // Read the Row
            for (int rowNum = startRow; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
                XSSFRow xssfRow = xssfSheet.getRow(rowNum);
                if (xssfRow != null) {
                    boolean hasValue = false;
                    String[] data = new String[totalCell];
                    for (int i = 0; i < totalCell; i++) {
                        Cell cell = xssfRow.getCell(i);
                        data[i] = getCellValue(cell);
                        if (StringUtils.isNotBlank(data[i])) {
                            hasValue = true;
                        }
                    }
                    if (hasValue) {
                        list.add(data);
                    }
                }
            }
        }
        return list;
    }
    
    private static List<String[]> readXlsxByRows(String path, int startRow, int endRowExclusive) {

        XSSFWorkbook xssfWorkbook = null;
        try {
            InputStream is = new FileInputStream(path);
            xssfWorkbook = new XSSFWorkbook(is);
        } catch (IOException e) {
            e.printStackTrace();
        }
        List<String[]> list = new ArrayList<String[]>();
        if (xssfWorkbook != null) {
            XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(0);
            if (xssfSheet == null) {
                return list;
            }
            int cellCount = 0;
            for (int rowNum = startRow; rowNum < endRowExclusive; rowNum++) {
                XSSFRow xssfRow = xssfSheet.getRow(rowNum);
                if(cellCount == 0)
                    cellCount = xssfRow.getLastCellNum();
                if (xssfRow != null) {
                    boolean hasValue = false;
                    String[] data = new String[cellCount];
                    for (int i = 0; i < cellCount; i++) {
                        Cell cell = xssfRow.getCell(i);
                        data[i] = getCellValue(cell);
                        if (StringUtils.isNotBlank(data[i])) {
                            hasValue = true;
                        }
                    }
                    if (hasValue) {
                        list.add(data);
                    }
                }
            }
        }
        return list;
    }

    /**
     * 获取文件扩展名
     *
     * @param path
     * @return String
     */
    private static String getExt(String path) {
        if (path == null || path.equals("") || !path.contains(".")) {
            return null;
        } else {
            return path.substring(path.lastIndexOf(".") + 1, path.length());
        }
    }

    /**
     * 从流中读取excel
     * @param in
     * @param suffix 后缀名（xls、xlsx）
     * @param startRow
     * @return
     * @throws IOException
     */
    public static Map<String, List<String[]>> readExcelFromStream(InputStream in, String suffix, int startRow) throws IOException {
        Map<String, List<String[]>> map = new LinkedHashMap<>();
        if (StringUtils.isBlank(suffix)) {
            throw new IOException("请输入文件后缀名！");
        }
        Workbook hssfWorkbook = suffix.equals("xls") ? new HSSFWorkbook(in) : new XSSFWorkbook(in);
        for (int sheetIndex = 0; sheetIndex < hssfWorkbook.getNumberOfSheets(); sheetIndex++) {
            List<String[]> list = new ArrayList<>();
            // Read the Sheet
            Sheet hssfSheet = hssfWorkbook.getSheetAt(sheetIndex);
            // Read the Row
            for (int rowNum = startRow; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
                Row hssfRow = hssfSheet.getRow(rowNum);
                if (hssfRow == null) {
                    continue;
                }
                int tempRowSize = hssfRow.getLastCellNum();
                String[] values = new String[tempRowSize];
                Arrays.fill(values, "");
                boolean hasValue = false;
                for (int columnNum = 0; columnNum < hssfRow.getLastCellNum(); columnNum++) {
                    String value = getCellValue(hssfRow.getCell(columnNum));
                    values[columnNum] = value;
                    if (StringUtils.isNotBlank(values[columnNum])) {
                        hasValue = true;
                    }
                }
                if (hasValue) {
                    list.add(values);
                }
            }
            map.put(hssfSheet.getSheetName(), list);
        }
        return map;
    }

    /**
     * 从流中读取csv文件
     * @param in
     * @return
     * @throws IOException
     */
    public static List<String[]> readCvsFromStream(InputStream in) throws IOException {
        List<String[]> result = new ArrayList<>();
        Reader reader = new InputStreamReader(in);
        BufferedReader br = new BufferedReader(reader);
        String line = "";
        while ((line = br.readLine()) != null) { //读取到的内容给line变量
            result.add(line.split(","));
        }
        return result;
    }
    
    /**
     * 读取某一行所有列值  直至遇到某一列为空 则返回 getLastCellNum这个获取最后一列
     * @param path
     * @return
     */
    public static List<String> readExcelOneRow(String path,int oneRow) {
        if (oneRow < 0) {
            return new ArrayList<String>();
        }
        if (path != null && !path.equals("")) {
            String ext = getExt(path);
            if (ext != null && !ext.equals("")) {
                if (ext.equals("xls")) {
                    return readXlsOneRow(path,oneRow);
                } else if (ext.equals("xlsx")) {
                    return readXlsxOneRow(path,oneRow);
                }
            }
        }
        return new ArrayList<String>();
    }
    
    /**
     * 读取后缀为xls的excel文件的数据
     *
     * @param path
     * @return List<String>
     */
    private static List<String> readXlsOneRow(String path, int oneRow) {

        HSSFWorkbook hssfWorkbook = null;
        try {
            InputStream is = new FileInputStream(path);
            hssfWorkbook = new HSSFWorkbook(is);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }

        List<String> list = new ArrayList<String>();
        if (hssfWorkbook != null) {
            // Read the Sheet
            HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(0);
            if (hssfSheet == null) {
                return list;
            }
            HSSFRow hssfRow = hssfSheet.getRow(oneRow);
            if(hssfRow!=null) {
                for(int jj=0;jj<hssfRow.getLastCellNum();jj++) {
                    Cell cell = hssfRow.getCell(jj);
                    String cellValue= getCellValue(cell);
                    if (StringUtils.isBlank(cellValue)) {
                        list.add("");
                    }else {
                        list.add(cellValue);
                    }
                }
            }
        }
        return list;
    }
    
    /**
     * 读取后缀为xlsx的excel文件的数据
     *
     * @param path
     * @return List<String>
     */
    private static List<String> readXlsxOneRow(String path, int oneRow) {

        XSSFWorkbook xssfWorkbook = null;
        try {
            InputStream is = new FileInputStream(path);
            xssfWorkbook = new XSSFWorkbook(is);
        } catch (IOException e) {
            e.printStackTrace();
        }
        List<String> list = new ArrayList<String>();
        if (xssfWorkbook != null) {
            // Read the Sheet
            XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(0);
            if (xssfSheet == null) {
                return list;
            }
            XSSFRow xssfRow = xssfSheet.getRow(oneRow);
            if(xssfRow!=null) {
                int ii=0;
                for(int jj=0;jj<xssfRow.getLastCellNum();jj++) {
                    Cell cell = xssfRow.getCell(ii);
                    String cellValue= getCellValue(cell);
                    if (StringUtils.isBlank(cellValue)) {
                        list.add("");
                    }else {
                        list.add(cellValue);
                    }
                }
            }
        }
        return list;
    }

    
    public static List<String[]> readExcelByRows(String path, int startRow, int endRowExclusive) {
        if (endRowExclusive <= startRow) {
            return new ArrayList<String[]>();
        }
        if (path != null && !path.equals("")) {
            String ext = getExt(path);
            if (ext != null && !ext.equals("")) {
                if (ext.equals("xls")) {
                    return readXlsByRows(path, startRow, endRowExclusive);
                } else if (ext.equals("xlsx")) {
                    return readXlsxByRows(path, startRow, endRowExclusive);
                }
            }
        }
        return new ArrayList<String[]>();
    }
}