Excel操作

Excel操作

.xls 文件：最大256(2的8次方)列，最大65536(2的16次方)行，即横向256个单元格，竖向65536个单元格。

.xlsx 文件：最大16384(2的14次方)列，最大1048576(2的20次方)行，即横向16384个单元格，竖向1048576个单元格。

缓存读取excel

maven

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.0.0</version>
</dependency>
<dependency>
    <groupId>com.monitorjbl</groupId>
    <artifactId>xlsx-streamer</artifactId>
    <version>2.1.0</version>
</dependency>

代码片段

// 判断文件是否存在
File file = new File(filePath);
if (!file.exists()) {
    throw new IOException("文件名为" + file.getName() + "Excel文件不存在！");
}

-------
    
//缓存读取excel 分批加载到内存中，防止数据量大时引发OOM
        FileInputStream fis = new FileInputStream(filePath);
        Workbook workbook = StreamingReader.builder().rowCacheSize(100) // 缓存到内存中的行数，默认是10
                .bufferSize(4098) // 读取资源时，缓存到内存的字节大小，默认是1024
                .open(fis);

-------
//遍历 并获取excel表头
Sheet sheetAt = workbook.getSheetAt(0);
//excel表头
List<String> excelTitles = new ArrayList<>();
//列名下标
Integer nameIndex = 0;
//--------------  读取表头 start ----------------------
//获取第一行表头：用缓存加载，无法使用sheetAt.getRow(i)方法，只能遍历。
for (Row row : sheetAt) {
    for (int j = 0; j < row.getLastCellNum(); j++) {
        String value = getCellFormatValue(row.getCell(j));
        // 缓存读取，用过一次之后就没了，所有要记录好列名
        excelTitles.add(value);
    }
    break;
}

缓存输出excel

采用阿里开源EasyExcel，详情请参考：

maven

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>2.2.7</version>
</dependency>

代码片段

//获取到的excel表头存这里面
List<String> excelTitles = new ArrayList<>();
//封装成easyExcel所需格式
List<List<String>> titles = new ArrayList<List<String>>();
for (String excelTitle : excelTitles) {
    titles.add(Arrays.asList(excelTitle));
}
 // 生成EXCEL 指定输出路径 设置SHEET 去除默认样式
            writer = EasyExcel.write(outputStream).head(titles).useDefaultStyle(false).build();
            WriteSheet writeSheet = EasyExcel.writerSheet(sheetAt.getSheetName()).build();
......
    List<List<Object>> rows = new ArrayList<>();
    //获取每一行数据
    List<Object> oneRow = new ArrayList<>();
for (int i = 0; i < excelTitles.size(); i++) {
    oneRow.add(getCellValue(row.getCell(i)));
}
rows.add(oneRow);
//写excel
writer.write(rows,writeSheet);
......
//大量数据循环分批请求时，记得清空有数据的容器
rows.clear();
//为避免OOM记得将循环中用到的容器及时置空

......
    finally 中记得：
        writer.finish();//调用该方法后才会将数据写入文件
		System.gc();

ps

获取单元格内容

private  String getCellFormatValue(Cell cell) {
        String cellValue = "";
        if (cell != null) {
            // 判断cell类型
            switch (cell.getCellType()) {
                case NUMERIC: {
                    cellValue = String.valueOf(cell.getNumericCellValue());
                    break;
                }
                case STRING: {
                    cellValue = cell.getRichStringCellValue().getString();
                    break;
                }
                default:
                    cellValue = "";
            }
        }
        return cellValue;
    }

获取单元格内容（对日前进行处理）

import org.apache.poi.hssf.usermodel.HSSFDateUtil;

private  Object getCellValue(Cell cell) {
        String cellValue = "";
        if (cell != null) {
            // 判断cell类型
            switch (cell.getCellType()) {
                case NUMERIC: {
                    //日期格式
                    if(HSSFDateUtil.isCellDateFormatted(cell)){
                        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                        return sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue())).toString();
                    }
                    //数字格式
                    return cell.getNumericCellValue();
                }
                case STRING: {
                    return cell.getRichStringCellValue().getString();
                }
                default:
                    cellValue = "";
            }
        }
        return cellValue;
    }

获取单元格内容

 public String getCellValue(Cell cell) {
        Object result = "";  
        if (cell != null) {  
            switch (cell.getCellType()) {  
                case STRING:
                result = cell.getStringCellValue();  
                break;  
                case NUMERIC:
                result = cell.getNumericCellValue();
                break;  
            case BOOLEAN:
                result = cell.getBooleanCellValue();  
                break;  
            case FORMULA:
                result = cell.getCellFormula();  
                break;  
            case ERROR:
                result = cell.getErrorCellValue();  
                break;  
            case BLANK:
                break;  
            default:  
                break;  
            }  
        }  
        return result.toString();  
    }

从mysql中读取大量数据

分页查询

代码案例片段

 Page<xxxDO> xxxDOPage = null;
			// count:查询次数  queryCount:用户所能查询的总数 part:每次查多少条
            Long count =  queryCount/part;
            for (int i = 1; i <= count; i++) {
                page = new Page<XxxDO>(i, part);
                xxxDOPage = xxxService.page(page, queryWrapper);
                xxxDOList = xxxDOPage.getRecords();
                fileNames.add(filePath+"/"+name+i+".xlsx");
                EasyExcel.write(filePath+"/"+name+i+".xlsx", XxxDO.class).useDefaultStyle(false).sheet("数据").doWrite(xxxDOList);
                xxxDOList = null;
                xxxDOPage = null;
                break;
            }
			//对最后一次(余数)进行处理，也可以在上面判断下一次做完
            if (pageList1.getTotal() - count*part > 0 ){
                page = new Page<XxxDO>(count+1, part);
                xxxDOPage = xxxService.page(page, queryWrapper);
                xxxDOList = xxxDOPage.getRecords();
                fileNames.add(filePath+"/"+name+(count+1)+".xlsx");
                EasyExcel.write(filePath+"/"+name+(count+1)+".xlsx", XxxDO.class).useDefaultStyle(false).sheet("数据").doWrite(xxxDOList);
                xxxDOList = null;
                xxxDOPage = null;
            }

流式查询

代码案例片段

public class MyResultHandler implements ResultHandler {
    
    @Override
    public void handleResult(ResultContext resultContext) {
        Object resultObject = resultContext.getResultObject();
        Map<String,Object> map = (Map<String,Object>)resultObject;
        //业务处理...
    }
}

import org.apache.ibatis.annotations.*;
import org.apache.ibatis.mapping.ResultSetType;
import org.apache.ibatis.session.ResultHandler;
@Mapper
public interface XxxDOMapper  {
    @Options(resultSetType = ResultSetType.FORWARD_ONLY, fetchSize = 1000)
    //@ResultType(XxxDO.class)
    //void query(@Param("params") Map params,ResultHandler<XxxDO> handler);
    void query(@Param("sign") int sign,ResultHandler<Map> handler);
}

@Test
public void test() {
    MyResultHandler handler = new MyResultHandler();
    XxxDOMapper.query(handler);
}

<select id="select" resultType="java.util.Map">
		select * from xxx_post_info where sign = #{sign}
	</select>