Excel文件的下载

 

Excel工具的maven依赖:

<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.9</version>
</dependency>

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

<dependency>
<groupId>jexcelapi</groupId>
<artifactId>jxl</artifactId>
<version>2.6</version>
<type>pom</type>
</dependency>

解析Excel文件:

public List<Map<String,Object>> readerExcelFile(String filePath){
    Workbook wb;
    try(InputStream ins = new FileInputStream(new File(filePath))){
        wb=WorkbookFactory.create(ins);
    }catch(Exception e){
        logger.error(e);
    }
    // 得到Excel工作表对象，获取第一个
    Sheet sheet = wb.getSheetAt(0);
    //总行数
    int trLength=sheet.getLastRowNum()+1;
    // 结果集
    List<Map<String,Object>> res = new ArrayList<>(trLength);
    // 工作行
    Row row;
    // 列数
    int tdLength ;
    //工作单元格
    Cell cell;
    //读取内容
    for(int i=0; i<trLength; i++){
        Map<String, Object> map = New LinKedHashMap();
        // 获取excel的工作行
        row =sheet.getRow(i);
        if(row == null){
            continue;
        }
        //获取总列数
        tdWide = row.getLastCellNum();
        for(int j=0; j< tdWide; j++){
            // 获取工作单元格
            cell = row.getCell(j);
            if(cell == null){
                continue;
            }
            int cellType = cell.getCellType();
            map.put(Integer.toString(j), getCellValue(cell, cellType));
        }
        res.add(map);
    }
    retrun res;
}

private static Object getCellValue(Cell cell, int cellType){
    switch(cellType){
    case Cell.CELL_TYPE_STRING:
        return cell.getStringCellValue();
    case Cell.CELL_TYPE_BOOLEAN:
        return cell.getBooleanCellValue();
    default :
        return null;
    }
}

将数据写入Excel文件

public static Workbook getExcel(List<Map<String,Object>> datas, String[] labelOrder,String path){
    Workbook wb = new SXSSFWorkbook();
    Sheet sh = wb.createSheet();
    // 表头样式
    CellStyle cellStyle = wb.createCellStyle();
    cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    // 正文样式
    CellStyle valueCellStyle = wb.createCellStyle();
    valueCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN))
    valueCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    
    Row lableRow = sh.createRow(0);
    Cell cell;
    // 创建表头
    for (int i = 0; i< labelOrder.length; i++){
        cell = lableRow.createCell(i); 
        cell.setCellSyle(cellStyle);
        cell.setValue(labelOrder[i]);
    }
    // 正文
    for (int i = 0; i< datas.size(); i++){
        Map<String,Object> data = datas.get(i);
        Row row = sh.createRow(i+1);
        for (int j = 0; j< labelOrder.length; j++){
            cell = row.createCell(j); 
            cell.setCellSyle(valueCellStyle );
            cell.setValue(data.get(labelOrder[j]));
    }
    wb.setSheetName(0,sheet1);
    return wb;
}
    

将文件输出至硬盘

public void createFile(Workbook wb ,String path){
    try(FileOutputStream out = new FileOutputStream(path) ){
        wb.write(out);    
    }
}