java 读取各种类型的文件 （四）

后端java，springboot 、前端vue： 对 xls、xlsx 文件的读写，以及前端预览

一、后端读

 public Object readXLSX() {       

　　　　　　 //获取到表       

　　　　　 File file = new File(xxx);

　　　　　FileInputStream inputStream = null;

        XSSFWorkbook wb = null;
        try {
            inputStream = new FileInputStream(file);
            //XSSFWorkbook获取到内容
            try {
                wb = new XSSFWorkbook(inputStream);
            } catch (IOException e) {
                e.printStackTrace();
                LOGGER.error("Excel文件读取失败",e);
            }
            //获取到Sheet1表
            XSSFSheet sheet = wb.getSheetAt(0);
            String sheetName = sheet.getSheetName();//表的名称
　　　　　　　//获取标题栏---第0行
            XSSFRow titleRow = sheet.getRow(0);
            int cellCount = 0;
            String[] titlevalues = new String[titleRow.getLastCellNum()];
            for(int i = 0;i<titleRow.getLastCellNum();i++){
                if(titleRow.getCell(i) == null){
                    continue;
                }else{
                    titlevalues[cellCount] = titleRow.getCell(i).getStringCellValue();//第一行标题行
                    cellCount++;
                }
            }
            firstLine = titlevalues;
            //存放最终结果
//                    int count = 0;//总行数（去掉空白行）
            sumLines = 0;
            //第二行开始，遍历每一行
            for (int rowIndex = 1; rowIndex < sheet.getPhysicalNumberOfRows() ; rowIndex++) {
                XSSFRow xssfRow = sheet.getRow(rowIndex);//获取到表的某行信息
                xssfRow.getPhysicalNumberOfCells();
                String[] values = new String[xssfRow.getLastCellNum()];
                if (xssfRow == null) {//如果为空进入下一个循环
                    continue;
                }
                for (int cellIndex = 0; cellIndex < xssfRow.getLastCellNum() ; cellIndex++) {
                    Cell xssfCell = xssfRow.getCell(cellIndex);//获取某行某列的值
                    String value = " ";
                    if (xssfCell != null) {
                        value = getCellValue(xssfCell);//数据格式的转换
                    }
                    values[cellIndex] = value;
                }
                try {
                    fileData.put(values);
                } catch (InterruptedException e) {
                    e.printStackTrace();
                }
                sumLines++;
            }
        } catch (FileNotFoundException e) {
            e.printStackTrace();
            LOGGER.error("文件不存在");
        }
        return null;
    }

    public Object readXLS() {
        //获取到表
         File file = new File(xxx);
        //读取内容
        FileInputStream inputStream = null;
        HSSFWorkbook wb = null;
        try {
            inputStream = new FileInputStream(file);
            //XSSFWorkbook获取到内容
            try {
                wb = new HSSFWorkbook(inputStream);
            } catch (IOException e) {
                e.printStackTrace();
                LOGGER.error("Excel文件读取失败",e);
            }
            //获取到Sheet1表
            HSSFSheet sheet = wb.getSheetAt(0);
            String sheetName = sheet.getSheetName();//表的名称
            //获取标题栏---第0行
            HSSFRow titleRow = sheet.getRow(0);

            int cellCount = 0;//记录真实列数
            String[] titlevalues = new String[titleRow.getLastCellNum()];
            for(int i = 0;i<titleRow.getLastCellNum();i++){
                if(titleRow.getCell(i) == null){
                    continue;
                }else{
                    titlevalues[cellCount] = titleRow.getCell(i).getStringCellValue();//第一行标题行
                    cellCount++;
                }
            }
            firstLine = titlevalues;
            //存放最终结果
//          int count = 0;//总行数（去掉空白行）
            sumLines = 0;
            //第二行开始，遍历每一行
            for (int rowIndex = 0; rowIndex < sheet.getPhysicalNumberOfRows() ; rowIndex++) {
                HSSFRow hssfRow = sheet.getRow(rowIndex);//获取到表的某行信息
                hssfRow.getPhysicalNumberOfCells();
                String[] values = new String[xssfRow.getLastCellNum()];
                if (xssfRow == null) {//如果为空进入下一个循环
                    continue;
                }
                for (int cellIndex = 0; cellIndex < hssfRow.getLastCellNum() ; cellIndex++) {
                    Cell hssfCell = hssfRow.getCell(cellIndex);//获取某行某列的值
                    String value = " ";
                    if (hssfCell != null) {
                        value = getCellValue(hssfCell);//数据格式的转换
                    }
                    values[cellIndex] = value;
                }
                try {
                    fileData.put(values);
                } catch (InterruptedException e) {
                    e.printStackTrace();
                }
                sumLines++;
            }
        } catch (FileNotFoundException e) {
            e.printStackTrace();
            LOGGER.error("文件不存在");
        }
        return null;
    }

public String getCellValue(Cell cell) {
    String temp = "";
    if (cell == null) {
        return temp;
    }
    CellType cellType = cell.getCellType();//获取类型
    switch (cellType) {
        case STRING:
            return cell.getRichStringCellValue().getString();
        case NUMERIC:
            if (DateUtil.isCellDateFormatted(cell)) {
                Date date = cell.getDateCellValue();
                DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                temp = df.format(date);
                return temp;
            } else {
                if (!String.valueOf(cell.getNumericCellValue()).contains("E")) {
                    return String.valueOf(cell.getNumericCellValue());
                } else {
                    return new DecimalFormat("#").format(cell.getNumericCellValue());
                }
            }
        case FORMULA:
            temp = cell.getStringCellValue();
            return temp;

        default:
            return temp;
    }
}
二、前后台配合预览
　　1、controller
public ResponseEntity<byte[]> previewFile(@RequestBody FileVO fileVO) {

        if(fileVO == null || fileVO.getId() == null){
            return null;
        }
        String filename = fileVO.getFileName();
        byte[] buffer = getXLSXBytes(fileVO,30); 
 　　　　// byte[] buffer = getXLSBytes(fileVO,30);

 　 　　HttpHeaders headers = new HttpHeaders();

        //防止中文名乱码
        //filename = new String(filename.getBytes(StandardCharsets.UTF_8), StandardCharsets.ISO_8859_1);
        try {
            headers.setContentDispositionFormData("attachment",  filename=java.net.URLEncoder.encode(filename, "UTF-8"));
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }
        headers.add(HttpHeaders.CONTENT_TYPE,"application/octet-stream;charset=UTF-8");
        headers.add("file-type",smsTaskFile.getFileType());
        //返回
        return new ResponseEntity<byte[]>(buffer, headers, HttpStatus.CREATED);
    }

//1、HSSFWorkbook；2、HSSFSheet；3、HSSFRow；4、Cell 
private byte[] getXLSBytes(FileVO fileVO, int preLines) {

    byte[] fileBuffer = null;
    int readLine = 0;
    FileInputStream inputStream = null;
    HSSFWorkbook wb = null;
    try {
        inputStream = new FileInputStream(fileVO.getFilePath());
        try {
            wb = new HSSFWorkbook(inputStream);
        } catch (IOException e) {
            e.printStackTrace();
            LOGGER.error("Excel文件读取失败",e);
        }
        //获取到Sheet1表
        HSSFSheet sheet = wb.getSheetAt(0);
        String sheetName = sheet.getSheetName();//表的名称
        System.out.println("sheetName=" + sheetName);

        int sumLines = 0;
        //第二行开始，遍历每一行
        int endLine = sheet.getPhysicalNumberOfRows()> preLines ? preLines :sheet.getPhysicalNumberOfRows();
        StringBuilder content = new StringBuilder("");
        for (int rowIndex = 0; rowIndex < endLine ; rowIndex++) {
            readLine = rowIndex;
            HSSFRow xssfRow = sheet.getRow(rowIndex);//获取到表的某行信息
            if (xssfRow == null) {//如果为空进入下一个循环
                content.append("\n");
                continue;
            }
            for (int cellIndex = 0; cellIndex < xssfRow.getLastCellNum() ; cellIndex++) {
                Cell xssfCell = xssfRow.getCell(cellIndex);//获取某行某列的值
                String value = " ";
                if (xssfCell != null) {
                    value = getCellValue(xssfCell);//数据格式的转换
                }
                content.append(value).append(" ");
            }
            content.append("\n");
            sumLines++;
        }
        fileBuffer = content.toString().getBytes(StandardCharsets.UTF_8);
    } catch (FileNotFoundException e) {
        e.printStackTrace();
        LOGGER.error("文件不存在");
    }
    return fileBuffer;
}

//1、XSSFWorkbook；2、XSSFSheet；3、XSSFRow；4、Cell 
private byte[] getXLSXBytes(FileVO fileVO, int preLines) {

    byte[] fileBuffer = null;
    int readLine = 0;
    //读取内容
    FileInputStream inputStream = null;
    XSSFWorkbook wb = null;
    try {
        inputStream = new FileInputStream(fileVO.getFilePath());
        try {
            wb = new XSSFWorkbook(inputStream);
        } catch (IOException e) {
            e.printStackTrace();
            LOGGER.error("Excel文件读取失败",e);
        }
        //获取到Sheet1表
        XSSFSheet sheet = wb.getSheetAt(0);
        String sheetName = sheet.getSheetName();//表的名称
        System.out.println("sheetName=" + sheetName);

        int sumLines = 0;
        //第二行开始，遍历每一行
        int endLine = sheet.getPhysicalNumberOfRows()> preLines ? preLines :sheet.getPhysicalNumberOfRows();
        StringBuilder content = new StringBuilder("");
        for (int rowIndex = 0; rowIndex < endLine ; rowIndex++) {
            readLine = rowIndex;
            XSSFRow xssfRow = sheet.getRow(rowIndex);//获取到表的某行信息
            if (xssfRow == null) {//如果为空进入下一个循环
                content.append("\n");
                continue;
            }
            for (int cellIndex = 0; cellIndex < xssfRow.getLastCellNum() ; cellIndex++) {
                Cell xssfCell = xssfRow.getCell(cellIndex);//获取某行某列的值
                String value = " ";
                if (xssfCell != null) {
                    value = getCellValue(xssfCell);//数据格式的转换
                }
                content.append(value).append(" ");
            }
            content.append("\n");
            sumLines++;
        }
        fileBuffer = content.toString().getBytes(StandardCharsets.UTF_8);
    } catch (FileNotFoundException e) {
        e.printStackTrace();
        LOGGER.error("文件不存在");
    }
    return fileBuffer;
}

　　2、vue
<el-button v-if="item.key == 'operate'" @click.stop="taskfilePreview(scope.row)" type="text" size="small">预览</el-button>
<el-dialog-side :visible="showPreviewFile" @close="showPreviewFile = false;fileContent=''">

  <h1>只预览文件前30行</h1><br>
  <el-card>
    <el-input v-if="fileContent"
              v-model="fileContent"
              show-word-limit
              :autosize="{ minRows: 12}"
              type="textarea"
    ></el-input>
  </el-card>
</el-dialog-side>

taskfilePreview(row){
  const reader = new FileReader();

  //通过readAsArrayBuffer将blob转换为ArrayBuffer对
  Actions.previewFile(row).then(res => {
    if (res.data) {
      reader.readAsText(res.data) // 这里的res.data是blob文件流
      reader.onload = (event) => {
        var data = event.target.result;
        this.showPreviewFile = true
        this.fileContent = data
      }
    }
  })
}