首页 科技正文

365体育投注:SXSSFWorkbook的简朴使用

admin 科技 2020-08-19 43 1

在工作中使用到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>

在挪用时,只需在Controller层挪用ExportSystemLogService.exportExcel(params)方式即可。

,

欧博网址开户

www.cx11yx.cn欢迎进入欧博网址(Allbet Gaming),欧博网址开放会员注册、代理开户、电脑客户端下载、苹果安卓下载等业务。

|
版权声明

本文仅代表作者观点,
不代表本站Allbet的立场。
本文系作者授权发表,未经许可,不得转载。

评论

精彩评论
  • 2020-08-19 00:04:47

    欧博亚洲官网开户网址欢迎进入欧博亚洲官网开户网址(Allbet Game):www.aLLbetgame.us,欧博官网是欧博集团的官方网站。欧博官网开放Allbet注册、Allbe代理、Allbet电脑客户端、Allbet手机版下载等业务。姐妹们快看神仙