SXSSFWorkbook的簡單使用
- 2020 年 5 月 23 日
- 筆記
在工作中使用到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"
"//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>
