在工作中使用到SXSSFWorkbook来导出Excel，写一篇博客记录一下SXSSFWorkbook的使用方法
1.介绍
SXSSFWorkbook是属于apache基金会的Excel导出工具类，从其官网上可以看出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允许编写非常大的文件而不会耗尽内存，
因为在任何时候，只有可配置的一部分行保存在内存中。
2.使用
首先我们定义一个列的实体类，包含列的名称，样式等
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 percentCellStyle = wb.createCellStyle();
//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"));
columns.add(new ExcelColumnInfo("日志级别", "LOG_LEVEL"));
columns.add(new ExcelColumnInfo("备注", "REMARK"));
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接口与xml文件配置如下
@Mapper
@Repository
public interface SystemLogMapper {
List<HashMap<String, Object>> getSystemLog(@Param("params") Map<String, Object> params);
}
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.XXX.XXX.SystemLogMapper">
<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>
</mapper>