在工作中使用到SXSSFWorkbook来导出Excel，写一篇博客记录一下SXSSFWorkbook的使用方法

SXSSFWorkbook是apache的开源项目，从其官网上可以看出SXSSFWorkbook实现了Workbook接口

Streaming version of XSSFWorkbook implementing the "BigGridDemo" strategy.
This allows to write very large files without running out of memory as
only a configurable portion of the rows are kept in memory at any one time.

SXSSFWorkbook是实现“BigGridDemo”策略的XSSFWorkbook的流媒体版本。SXSSFWorkbook允许编写非常大的文件而不会耗尽内存，
因为在任何时候，只有可配置的一部分行保存在内存中。

首先我们定义一个列的实体类，包含列的名称，样式等

import org.apache.poi.ss.usermodel.CellStyle;

/**
* @describe 定义excel列
*
*/
public class ExcelColumnInfo {
private String columnName;
private String columnCode;
/**
* 单元格样式
*/
private CellStyle cellStyle;
}

public ExcelColumnInfo(String columnName, String columnCode) {  
    super();  
    this.columnName = columnName;  
    this.columnCode = columnCode;  
}

public ExcelColumnInfo(String columnName, String columnCode, CellStyle cellStyle) {  
    super();  
    this.columnName = columnName;  
    this.columnCode = columnCode;  
    this.cellStyle = cellStyle;  
}

public String getColumnName() {  
    return columnName;  
}  
public void setColumnName(String columnName) {  
    this.columnName = columnName;  
}  
public String getColumnCode() {  
    return columnCode;  
}  
public void setColumnCode(String columnCode) {  
    this.columnCode = columnCode;  
}

public CellStyle getCellStyle() {  
    return cellStyle;  
}

public void setCellStyle(CellStyle cellStyle) {  
    this.cellStyle = cellStyle;  
}

}

再定义一个Excel写入单元格数据的工具类

/**
* Excel辅助类
*/
public class ExcelUtil {

public static void setCell(Cell cell, Object obj) {  
    if (obj == null) {  
        cell.setCellValue("");  
    } else if (obj instanceof Integer) {  
        cell.setCellValue((int) obj);  
    } else if (obj instanceof BigDecimal) {  
        cell.setCellValue(((BigDecimal) obj).doubleValue());  
    } else if (obj instanceof String) {  
        cell.setCellValue((String) obj);  
    } else if (obj instanceof Double) {  
        cell.setCellValue((double) obj);  
    } else if (obj instanceof Long) {  
        cell.setCellValue((long) obj);  
    }  
}

public static void setCell(Cell cell, Object obj, CellStyle cellStyle) {  
    cell.setCellStyle(cellStyle);  
    if (obj instanceof Integer) {  
        cell.setCellValue((int) obj);  
    } else if (obj instanceof BigDecimal) {  
        cell.setCellValue(((BigDecimal) obj).doubleValue());  
    } else if (obj instanceof String) {  
        cell.setCellValue((String) obj);  
    } else if (obj instanceof Double) {  
        cell.setCellValue((double) obj);  
    } else if (obj instanceof Long) {  
        cell.setCellValue((long) obj);  
    } else {  
        cell.setCellValue("");  
    }  
}  

}

然后开始写导出的方法

@Service
public class ExportSystemLogService{

@Autowired  
private SystemLogMapper mapper;

public SXSSFWorkbook exportExcel(Map<String, Object> params) throws IOException {

    SXSSFWorkbook wb = new SXSSFWorkbook(1000);  
    //获取表格列信息  
    LinkedHashMap<String, List<ExcelColumnInfo>> excelInfo = getExcelInfo(wb);  
    for(String sheetName : excelInfo.keySet()){  
        //创建sheet页  
        Sheet sheet = wb.createSheet(sheetName);  
        //获取该sheet页的列信息  
        List<ExcelColumnInfo> excelColumnInfo = excelInfo.get(sheetName);  
        //生成Excel数据  
        generateExcelData(sheet,excelColumnInfo,params);  
    }  
    return wb;  
}

protected LinkedHashMap<String, List<ExcelColumnInfo>> getExcelInfo(Workbook wb) {  
    LinkedHashMap<String, List<ExcelColumnInfo>> excelInfo = new LinkedHashMap<>();  
    List<ExcelColumnInfo> columns = new ArrayList<>();CellStyle wrapStyle = wb.createCellStyle();  
    wrapStyle.setWrapText(true);    //设置自动换行

    columns.add(new ExcelColumnInfo("日志编号", "LOG\_ID"));    //后面的columnCode与从数据库中查询出来的字段名一致  
    columns.add(new ExcelColumnInfo("操作时间", "CREATE\_TIME"));  
    columns.add(new ExcelColumnInfo("操作人", "CREATE\_USER"));  
    columns.add(new ExcelColumnInfo("操作模块", "OPERATION\_MODULE"));  
    columns.add(new ExcelColumnInfo("操作类型", "OPERATION\_TYPE"));  
    columns.add(new ExcelColumnInfo("详情", "OPERATION\_DETAIL",wrapStyle));  
    columns.add(new ExcelColumnInfo("日志级别", "LOG\_LEVEL"));  
    columns.add(new ExcelColumnInfo("备注", "REMARK",wrapStyle));

    excelInfo.put("系统日志", columns);  
    return excelInfo;  
}

private void generateExcelData(Sheet sheet,List<ExcelColumnInfo> excelColumnInfo,Map<String, Object> params) {  
    //设置列的宽度，第一个参数为列的序号，从0开始，第二参数为列宽，单位1/256个字节  
    sheet.setColumnWidth(0, 12\*256);  
    sheet.setColumnWidth(2, 16\*256);  
    sheet.setColumnWidth(5, 12\*256);  
    sheet.setColumnWidth(6, 26\*256);  
    sheet.setColumnWidth(7, 26\*256);  
    //设置开始行和开始列  
    int rowIndex = 0;  
    int columnIndex = 0;  
    Row row = sheet.createRow(rowIndex);  
    //创建表头  
    for (ExcelColumnInfo column : excelColumnInfo) {  
        ExcelUtil.setCell(row.createCell(columnIndex++), column.getColumnName());  
    }  
    //获取导出数据  
    List<HashMap<String, Object>> data = mapper.getSystemLog(params);  
    rowIndex = 1;  
    for (HashMap<String, Object> tmp : data) {  
        Row row1 = sheet.createRow(rowIndex);  
        columnIndex = 0;  
        for(ExcelColumnInfo column : excelColumnInfo){  
            Cell cell = row1.createCell(columnIndex);  
            //设置单元格样式  
            if (column.getCellStyle() != null) {  
                cell.setCellStyle(column.getCellStyle());  
            }  
            ExcelUtil.setCell(cell,tmp.get(column.getColumnCode()));  
            columnIndex++;  
        }  
        rowIndex++;  
    }  
}  

}

@Mapper
@Repository
public interface SystemLogMapper {

List<HashMap<String, Object>> getSystemLog(@Param("params") Map<String, Object> params);  

}

<select id="getSystemLog" parameterType="java.util.HashMap" resultType="java.util.HashMap">  
    SELECT  
    LOG\_ID,  
    CREATE\_TIME,  
    CREATE\_USER,  
    OPERATION\_MODULE,  
    OPERATION\_TYPE,  
    OPERATION\_DETAIL,  
    LOG\_LEVEL,  
    REMARK  
    FORM SYSTEM\_LOG  
    ORDER BY CREATE\_TIME DESC  
</select>