SXSSFWorkbook的简单使用
MrZhaoyx 人气:0在工作中使用到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.orghttps://img.qb5200.com/download-x/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>
在调用时,只需在Controller层调用ExportSystemLogService.exportExcel(params)方法即可。
加载全部内容