java使用poi导入导出Excel备忘录

1.先放入依赖。

  
		
		    org.apache.poi
		    poi-ooxml
		    3.14
	    
	      
        
           org.apache.poi
           poi
           3.14
        

2.创建工具类

public static void exportExcel(HttpServletResponse response, List header, List> dataList) throws Exception {
    exportExcel(response, "主标题", "副标题", header, dataList);
}


public static void exportExcel(HttpServletResponse response, String title, String subheading, List header, List> dataList) throws Exception {
    //获取一个HSSFWorkbook对象
    HSSFWorkbook workbook = new HSSFWorkbook();
    HSSFCellStyle style = getHSSFCellStyle(workbook);
    //创建一个sheet
    HSSFSheet sheet = workbook.createSheet("Sheet1");
    //创建一个标题行
    CellRangeAddress cellRangeAddress = new CellRangeAddress(0, 0, 0, header.size());
    //创建一个副标题行
    CellRangeAddress cellRangeAddress2 = new CellRangeAddress(1, 1, 0, header.size());
    sheet.addMergedRegion(cellRangeAddress);
    sheet.addMergedRegion(cellRangeAddress2);

    //标题，居中
    HSSFRow row0 = sheet.createRow(0);
    HSSFCell cell0 = row0.createCell(0);
    cell0.setCellValue(title);
    cell0.setCellStyle(style);
    // 第一行
    HSSFRow row1 = sheet.createRow(1);
    HSSFCell cell1 = row1.createCell(0);
    //副标题
    cell1.setCellValue(subheading);
    cell1.setCellStyle(style);

    //表头
    HSSFRow row = sheet.createRow(2);

    HSSFCell cell = null;
    for (int i = 0; i < header.size(); i++) {
        cell = row.createCell(i);
        cell.setCellValue(header.get(i));
        cell.setCellStyle(style);
    }

    //数据
    for (int i = 0; i < dataList.size(); i++) {
        row = sheet.createRow(i + 3);
        for (int j = 0; j < dataList.get(i).size(); j++) {
            row.createCell(j).setCellValue(dataList.get(i).get(j));
        }
    }

    OutputStream outputStream = response.getOutputStream();
    //设置页面不缓存
    response.reset();
    String filename = title;
    //设置返回文件名的编码格式
    response.setCharacterEncoding("utf-8");
    filename = URLEncoder.encode(filename, "utf-8");
    response.setHeader("Content-disposition", "attachment;filename=" + filename + ".xls");
    response.setContentType("application/msexcel");
    workbook.write(outputStream);
    outputStream.close();
}


public static List> importExcel(MultipartFile file, int sheetIndex, int headerIndex) throws Exception {
    Workbook workbook = null;
    //返回的data
    List> data = new ArrayList<>();
    workbook = getWorkbook(file);
    //导入某一页
    if (sheetIndex != -1 && sheetIndex > -1) {
        Sheet sheet = workbook.getSheetAt(sheetIndex);
        List> lists = importoneSheet(sheet, headerIndex);
        data.addAll(lists);
    } else {
        //导入全部
        for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
            Sheet sheet = workbook.getSheetAt(i);
            if (sheet == null) {
                continue;
            }
            List> lists = importoneSheet(sheet, headerIndex);
            data.addAll(lists);
        }
    }
    return data;
}


public static List> importExcel(MultipartFile file, int headerIndex) throws Exception {
    return importExcel(file, -1, headerIndex);
}


private static HSSFCellStyle getHSSFCellStyle(HSSFWorkbook workbook) {
    HSSFCellStyle style = workbook.createCellStyle();
    //居中
    style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    return style;
}



private static List> importoneSheet(Sheet sheet, int headerIndex) throws Exception {
    List> data = new ArrayList<>();
    int row = sheet.getLastRowNum();
    //row = -1 表格中没有数据
    //row = headerIndex 表格中表头以下没有数据（指没有有用数据）
    if (row == -1 || row == headerIndex) {
        throw new Exception("表格中没有有用数据!");
    }
    //通过表头获取共多少列
    int coloumNum = sheet.getRow(headerIndex).getPhysicalNumberOfCells();
    //从表头下一行开始取数据
    for (int i = headerIndex + 1; i <= row; i++) {
        Row row1 = sheet.getRow(i);
        List