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

1.先放入依赖。

<!--poi对excel2007以上版本的支持-->  
		<dependency>
		    <groupId>org.apache.poi</groupId>
		    <artifactId>poi-ooxml</artifactId>
		    <version>3.14</version>
	    </dependency>
	    <!--poi对excel2003以下版本的支持-->  
        <dependency>
           <groupId>org.apache.poi</groupId>
           <artifactId>poi</artifactId>
           <version>3.14</version>
        </dependency>

2.创建工具类

/**
 * 导出excel
 *
 * @param response
 * @param header
 * @param dataList
 * @throws Exception
 */
public static void exportExcel(HttpServletResponse response, List<String> header, List<List<String>> dataList) throws Exception {
    exportExcel(response, "主标题", "副标题", header, dataList);
}

/**
 * 导出excel
 *
 * @param response
 * @param title
 * @param subheading
 * @param header
 * @param dataList
 * @throws Exception
 */
public static void exportExcel(HttpServletResponse response, String title, String subheading, List<String> header, List<List<String>> 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();
}

/**
 * 导入数据（单页）
 *
 * @param file        文件
 * @param sheetIndex  页名的索引(从0开始，-1代表全部页)
 * @param headerIndex 表头的索引（用于获取共多少列以及第几行开始读数据）
 * @return
 * @throws IOException
 */
public static List<List<Object>> importExcel(MultipartFile file, int sheetIndex, int headerIndex) throws Exception {
    Workbook workbook = null;
    //返回的data
    List<List<Object>> data = new ArrayList<>();
    workbook = getWorkbook(file);
    //导入某一页
    if (sheetIndex != -1 && sheetIndex > -1) {
        Sheet sheet = workbook.getSheetAt(sheetIndex);
        List<List<Object>> 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<List<Object>> lists = importOneSheet(sheet, headerIndex);
            data.addAll(lists);
        }
    }
    return data;
}

/**
 * 导入数据（所有页）
 *
 * @param file        文件
 * @param headerIndex 表头的索引（用于获取共多少列以及第几行开始读数据）
 * @return
 * @throws IOException
 */
public static List<List<Object>> importExcel(MultipartFile file, int headerIndex) throws Exception {
    return importExcel(file, -1, headerIndex);
}

/**
 * 创建一个style
 *
 * @param workbook
 * @return
 */
private static HSSFCellStyle getHSSFCellStyle(HSSFWorkbook workbook) {
    HSSFCellStyle style = workbook.createCellStyle();
    //居中
    style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    return style;
}


/**
 * 获取一个sheet里的数据
 *
 * @param sheet
 * @param headerIndex
 * @return
 * @throws Exception
 */
private static List<List<Object>> importOneSheet(Sheet sheet, int headerIndex) throws Exception {
    List<List<Object>> 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<Object> list = new ArrayList<>();
        if (row1 != null) {
            for (int j = 0; j < coloumNum; j++) {
                list.add(getCellValue(row1.getCell(j)));
            }
        }
        data.add(list);
    }
    return data;
}

/**
 * 获取workbook
 *
 * @return
 */
private static Workbook getWorkbook(MultipartFile file) throws Exception {
    Workbook workbook = null;
    //获取文件名
    String fileName = file.getOriginalFilename();
    //判断文件格式
    if (fileName.endsWith(XLS)) {
        workbook = new HSSFWorkbook(file.getInputStream());
    } else if (fileName.endsWith(XLSX)) {
        workbook = new XSSFWorkbook(file.getInputStream());
    } else {
        throw new Exception("文件格式有误!");
    }
    return workbook;
}


/**
 * 获取单元格的值
 *
 * @param cell
 * @return
 */
private static String getCellValue(Cell cell) {
    String cellValue = "";
    DecimalFormat df = new DecimalFormat("#");
    switch (cell.getCellType()) {
        case HSSFCell.CELL_TYPE_STRING:
            cellValue = cell.getRichStringCellValue().getString().trim();
            break;
        case HSSFCell.CELL_TYPE_NUMERIC:
            cellValue = df.format(cell.getNumericCellValue()).toString();
            break;
        case HSSFCell.CELL_TYPE_BOOLEAN:
            cellValue = String.valueOf(cell.getBooleanCellValue()).trim();
            break;
        case HSSFCell.CELL_TYPE_FORMULA:
            cellValue = cell.getCellFormula();
            break;
        default:
            cellValue = "";
    }
    return cellValue.trim();
}

3.导入Excel

新建controller

@RequestMapping(value = "/import", method = RequestMethod.POST)
@ResponseBody
public Object importExcel(MultipartFile file) {
    List<List<Object>> test = null;
    try {
        test = ExcelUtil.importExcel(file, 0);
    } catch (Exception e) {
        e.printStackTrace();
    }
    return successView("", test);
}

在controller中是用excel工具类，参数file为excel文件，参数0为表头的索引（注意是索引，实际位置-1），工具类中还有一个参数sheetIndex为导入的是excel文件中的哪一个sheet，如果不传则为导入全部的sheet（当然要保证格式一样）

4.导出Excel

service实现

List<String> header = new ArrayList<>();
		    header.add("需要添加表头");
		    header.add("需要添加表头");
		    header.add("需要添加表头");
		    header.add("需要添加表头");
		   
		    //新建Excel表
		    List<List<String>> dataList = new ArrayList<>();
		   //调方法找数据
		    List<CheckItem> CheckItems = checkItemMapper.getCheckItem();
		    for (int i = 0; i < CheckItems.size(); i++) {
		        List<String> data = new ArrayList<>();
		        data.add(CheckItems.get(i).getCode());
		        data.add(CheckItems.get(i).getName());
		        data.add(CheckItems.get(i).getLabel());
		        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");   
		        String format = sdf.format(CheckItems.get(i).getCreateDate());
		        System.out.println(format);
		        if( format != null || format != ""){
		        	String add = sdf.format(CheckItems.get(i).getCreateDate());
		        	data.add(add);
		        }		
		        dataList.add(data);
		        
//		        导出方法共5个参数，
//		        第一个response：HttpServletResponse，
//		        第二个title：表的主标题，
//		        第三个subheading：副标题，
//		        第四个header：表头list，
//		        第五个dataList：数据list；        
		    }
		    try {
		        exportExcel.exportExcel(response, "主标题","副标题",header, dataList);
		    } catch (Exception e) {
		        e.printStackTrace();
		    }
	}