Java操作excel，读取及导出

一. 导出excel，具体代码如下：

public ResponseEntity<byte[]> listExcel(@RequestBody JSONObject param) throws Exception {
	List<Bean> list = userService.queryList(params);

	String[] titles = {"系统序号", "客户名称", "金额", "明细", "审核状态", "创建时间"};
	ByteArrayOutputStream out = new ByteArrayOutputStream();
	// 操作具体的数据
	Workbook wb = userService.exportSalesScore(titles, out, list);
	HttpHeaders headers = new HttpHeaders();
	headers.setContentDispositionFormData("attachment", "充值明细导出.xls");
	headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);

	wb.write(out);
	return new ResponseEntity<byte[]>(out.toByteArray(), headers, HttpStatus.OK);
}


public Workbook exportSalesScore(String[] titles, ByteArrayOutputStream out, List<Bean> list) throws Exception {
    HSSFWorkbook workbook = new HSSFWorkbook();
    HSSFSheet hssfSheet = workbook.createSheet("sheet1");
    HSSFRow row = hssfSheet.createRow(0);
    HSSFCellStyle hssfCellStyle = workbook.createCellStyle();

    HSSFCell hssfCell;
    for (int i = 0; i < titles.length; i++) {
        hssfCell = row.createCell(i);//列索引从0开始
        hssfCell.setCellValue(titles[i]);//列名1
        hssfCell.setCellStyle(hssfCellStyle);//列居中显示
    }

    if(CollectionUtils.isNotEmpty(list)) {
        SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        for (int i = 0; i < list.size(); i++) {
            row = hssfSheet.createRow(i + 1);
            Bean s = list.get(i);
            row.createCell(0).setCellValue(s.getId());
            row.createCell(1).setCellValue(s.getUsername());
            row.createCell(2).setCellValue(s.getMoney());
            row.createCell(3).setCellValue(s.getDetail());
            row.createCell(4).setCellValue(s.getAuditString());
            row.createCell(5).setCellValue(df.format(s.getCreateTime()));
        }
    }
    try {
        workbook.write(out);
        out.flush();
        out.close();
    } catch (Exception e) {
        log.info("导出异常：{}", e);
    }
    return workbook;
}

　　

 

二. 读取excel，具体代码如下：

 

ReadExcelUtils excelReader = new ReadExcelUtils("文件在服务器上的路径");
List<Map<Integer, Object>> mapList = excelReader.readExcelContent();


/**
 * 读取Excel数据内容
 *
 * @param
 * @return Map 包含单元格数据内容的Map对象
 * @author zengwendong
 */
public List<Map<Integer,Object>> readExcelContent() throws Exception{
    if(wb==null){
        throw new Exception("Workbook对象为空！");
    }
    List<Map<Integer,Object>> content = new ArrayList<>();

    sheet = wb.getSheetAt(0);
    // 得到总行数
    int rowNum = sheet.getLastRowNum();
    row = sheet.getRow(0);
    int colNum = row.getPhysicalNumberOfCells();
    // 第一行为表头的标题
    // 正文内容从第四行开始（当前业务需要，企小保灵工小保人员清单表）
    for (int i = 3; i <= rowNum; i++) {
        row = sheet.getRow(i);
        int j = 1;
        Map<Integer,Object> cellValue = new HashMap<>();
        while (j < colNum) {
            Object obj = getCellFormatValue(row.getCell(j));
            cellValue.put(j, obj);
            j++;
        }
        content.add(cellValue);
    }
    return content;
}


public class ReadExcelUtils {
    private Logger logger = LoggerFactory.getLogger(ReadExcelUtils.class);
    private Workbook wb;
    private Sheet sheet;
    private Row row;

    public ReadExcelUtils(String filepath) {
        if(filepath==null){
            return;
        }
        String ext = filepath.substring(filepath.lastIndexOf("."));
        try {
            InputStream is = new FileInputStream(filepath);
            if(".xls".equals(ext)){
                wb = new HSSFWorkbook(is);
            }else if(".xlsx".equals(ext)){
                wb = new XSSFWorkbook(is);
            }else{
                wb=null;
            }
        } catch (FileNotFoundException e) {
            logger.error("FileNotFoundException", e);
        } catch (IOException e) {
            logger.error("IOException", e);
        }
    }

    /**
     * 读取Excel表格表头的内容
     *
     * @param
     * @return String 表头内容的数组
     * @author zengwendong
     */
    public String[] readExcelTitle() throws Exception{
        if(wb==null){
            throw new Exception("Workbook对象为空！");
        }
        sheet = wb.getSheetAt(0);
        row = sheet.getRow(0);
        // 标题总列数
        int colNum = row.getPhysicalNumberOfCells();
        System.out.println("colNum:" + colNum);
        String[] title = new String[colNum];
        for (int i = 0; i < colNum; i++) {
            title[i] = row.getCell(i).getCellFormula();
        }
        return title;
    }

    /**
     * 读取Excel数据内容
     *
     * @param
     * @return Map 包含单元格数据内容的Map对象
     * @author zengwendong
     */
    public List<Map<Integer,Object>> readExcelContent() throws Exception{
        if(wb==null){
            throw new Exception("Workbook对象为空！");
        }
        List<Map<Integer,Object>> content = new ArrayList<>();

        sheet = wb.getSheetAt(0);
        // 得到总行数
        int rowNum = sheet.getLastRowNum();
        row = sheet.getRow(0);
        int colNum = row.getPhysicalNumberOfCells();
        // 第一行为表头的标题
        // 正文内容从第四行开始（当前业务需要，企小保灵工小保人员清单表）
        for (int i = 3; i <= rowNum; i++) {
            row = sheet.getRow(i);
            int j = 1;
            Map<Integer,Object> cellValue = new HashMap<>();
            while (j < colNum) {
                Object obj = getCellFormatValue(row.getCell(j));
                cellValue.put(j, obj);
                j++;
            }
            content.add(cellValue);
        }
        return content;
    }

    /**
     *
     * 根据Cell类型设置数据
     *
     * @param cell
     * @return
     * @author zengwendong
     */
    private Object getCellFormatValue(Cell cell) {
        Object cellvalue = "";
        if (cell != null) {
            // 判断当前Cell的Type
            switch (cell.getCellType()) {
                case Cell.CELL_TYPE_NUMERIC:// 如果当前Cell的Type为NUMERIC
                case Cell.CELL_TYPE_FORMULA: {
                    // 判断当前的cell是否为Date
                    if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) {
                        Date date = cell.getDateCellValue();
                        cellvalue = date;
                    } else {// 如果是纯数字

                        // 取得当前Cell的数值
                        cellvalue = String.valueOf(cell.getNumericCellValue());
                    }
                    break;
                }
                case Cell.CELL_TYPE_STRING:// 如果当前Cell的Type为STRING
                    // 取得当前的Cell字符串
                    cellvalue = cell.getRichStringCellValue().getString();
                    break;
                default:// 默认的Cell值
            }
        }
        return cellvalue;
    }

    public static void main(String[] args) {
        try {
            String filepath = "/Users/yinpz/Downloads/企小保灵工小保人员清单表.xls";
            ReadExcelUtils excelReader = new ReadExcelUtils(filepath);
            List<Map<Integer, Object>> mapList = excelReader.readExcelContent();
            JSONArray jsonArray = JSONArray.parseArray(JSON.toJSONString(mapList));
            for (Object object : jsonArray) {
                JSONObject json = JSON.parseObject(object.toString());
                System.out.println(json);
            }

            // 对读取Excel表格内容测试

            System.out.println(JSON.toJSONString(mapList));

        } catch (FileNotFoundException e) {
            System.out.println("未找到指定路径的文件!");
            e.printStackTrace();
        }catch (Exception e) {
            e.printStackTrace();
        }
    }
}