POI 读取/导出 Excel 文件

POI 读取/导出 Excel 文件

1、导入 jar 包

 <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi-ooxml</artifactId>
      <version>3.17</version>
 </dependency>

2、读取 Excel

package service;

import org.apache.poi.ss.usermodel.CellType;
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 java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.Map;

/**
 * @author cxt
 * @date 2020/7/21
 * 读取Excel文件中的值
 */
public class DataService {

    public static void main(String[] args) throws IOException {
        redExcel("E:\\aa.xslx");
    }

    /**
     * 读取excel内容
     * <p>
     * 用户模式下：
     * 弊端：对于少量的数据可以，单数对于大量的数据，会造成内存占据过大，有时候会造成内存溢出
     * 建议修改成事件模式
     */
    public static void redExcel(String filePath) throws IOException {
    	//读取本地文件
        //InputStream inputStream = new FileInputStream("E:\\aa.xslx");
        //读取导入文件
        File file = new File(filePath);
        if (!file.exists()){
            throw new Exception("文件不存在!");
        }
        InputStream in = new FileInputStream(file);
        // 读取整个Excel
        XSSFWorkbook sheets = new XSSFWorkbook(in);
        // 获取第一个表单Sheet
        XSSFSheet sheetAt = sheets.getSheetAt(0);
        // 获取第一行为标题行
        XSSFRow titleRow = sheetAt.getRow(0);
        
        // 循环读取某一行
		for (Row row : sheetAt) {
			// 读取每一行的单元格
			if (index == 0) {
				index++;
				continue;
			}
			//获取列长度
			int ofCells = row.getPhysicalNumberOfCells();
			//获取行长度
			int ofRows = row.getPhysicalNumberOfRows();

			for (int j = 0; j < row.getLastCellNum(); j++) {
			//单元格处理
			HSSFCell cell = row.getCell(j);
			String cellStr = "";
			if (cell == null) {// 单元格为空设置cellStr为空串
            	cellStr = "";
            } else if (cell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN) {
            	// 对布尔值的处理
            	cellStr = String.valueOf(cell.getBooleanCellValue());
            } else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
            	// 对数字值的处理
            	cellStr = cell.getNumericCellValue() + "";
            } else {
            	// 其余按照字符串处理
            	cellStr = cell.getStringCellValue();
            }

			//将Excel表中单元格的值与数据库表值的对应
			if (j == 0) {
            	String stringCellValue1 = cellStr;
            }
            if (j == 1) {
            	String stringCellValue2 = cellStr;
            }
            if (j == 2) {
            	String stringCellValue3 = cellStr;
            }
			if (j == 3) {
            	String stringCellValue4 = cellStr;
            }
            
			//------保存入数据库------
		}
    }
}

3、读取结果

原 Excel 表格内容：

4、导出 Excel

public String export(HttpServletResponse response) {
	String fileName = "xxx记录";
		List<TpPatrolModel> excelList = tpPatrolModelService.selectTpPatrolModelList(new TpPatrolModel());
		try {
			response.setContentType("application/vnd.ms-excel");
			response.setCharacterEncoding("utf-8");
			fileName = URLEncoder.encode(fileName, "UTF-8");
			response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
			EasyExcel.write(response.getOutputStream(), TpPatrolModel.class).sheet(1).doWrite(excelList);
		} catch (IOException e) {
			e.printStackTrace();
			return e.getMessage();
		}
		return "操作成功";
}


public ApiRest export2(@RequestBody String examId) {
	StringBuffer stringBuffer = new StringBuffer("");
        char s = '=';
        for (int i = 0; i < examId.length(); i++) {
            if (examId.charAt(i) != s) {
                stringBuffer.append(examId.charAt(i));
            }
        }
        examId = stringBuffer.toString();
        List<UserExam> list1 = baseService.selectAllByExamId(examId);
        //创建工作薄
        HSSFWorkbook workbook=new HSSFWorkbook();
        //创建sheet
        HSSFSheet sheet=workbook.createSheet();
        //创建第一行row
        HSSFRow header=sheet.createRow(0);
        //创建单元格并插入表头
        HSSFCell cell=null;
        String[] infos={"序号","考生姓名","考生身份证号","所属单位","考试得分"};
        for(int i=0;i<infos.length;i++){
            cell=header.createCell(i);
            cell.setCellValue(infos[i]);
        }
        //
        HSSFRow body=null;
        for(int i=1;i<=list1.size();i++){
            UserExam fee = list1.get(i-1);
            String realName = sysUserService.selectRealName(fee.getUserId());
            String userName = sysUserService.selectUserName(fee.getUserId());
            String dw = sysUserService.selectDw(fee.getUserId());
            body=sheet.createRow(i);
            cell=body.createCell(0);
            cell.setCellValue(i);
            cell=body.createCell(1);
            cell.setCellValue(realName);
            cell=body.createCell(2);
            cell.setCellValue(userName);
            cell=body.createCell(3);
            cell.setCellValue(dw);
            cell=body.createCell(4);
            cell.setCellValue(fee.getMaxScore());
            System.out.println(realName + userName + dw);
        }

        //创建文件
        String filePath = new String("src/main/resources/" + "文件名称.xls");
        File file=new File(filePath);
        try {
            file.createNewFile();
        } catch (IOException e) {
        // TODO Auto-generated catch block
            e.printStackTrace();
        }
            //文件输出流
        try {
            FileOutputStream stream= FileUtils.openOutputStream(file);
            //写入
            workbook.write(stream);
            //关闭输出流
            stream.close();
        } catch (IOException e) {
// TODO Auto-generated catch block
            e.printStackTrace();
        }
        return super.success("成功",filePath);
}

---

好事定律：每件事最后都会是好事，如果不是好事，说明还没到最后。