# shr25-common-excel

**Repository Path**: shr25/shr25-common-excel

## Basic Information

- **Project Name**: shr25-common-excel
- **Description**: 常用excel导入导出工具
- **Primary Language**: Java
- **License**: Apache-2.0
- **Default Branch**: master
- **Homepage**: None
- **GVP Project**: No

## Statistics

- **Stars**: 0
- **Forks**: 0
- **Created**: 2022-09-09
- **Last Updated**: 2026-01-20

## Categories & Tags

**Categories**: Uncategorized

**Tags**: None

## README

# modoumama-excel-utils

#### 导出支持多层结构   如：son.name

```java
	List<SteelModel> data = new ArrayList<>();
		for (int i = 0; i < 10; i++) {
			SteelModel steelModel = new SteelModel();
			steelModel.setId(i);
			steelModel.setSon(new Son("子类名字"+i));
			steelModel.setName("名字"+i);
			steelModel.setCode("代码"+i);
			steelModel.setDate(new Date());
			data.add(steelModel);
		}
		String outPath = "D:/excel/basdout.xlsx";
		//创建文件流     
		SheetEntity fields = new SheetEntity();
		fields.setName("商户收益");
		fields.setData(data);
		fields.put(0, "id", "编号",10);
		fields.put(1, "code", "代码");
		fields.put(2, "son.name", "名称");
		fields.put(3, "date", "日期");
		ExportExcel.writeExcel(fields,outPath);
```

### 通过模板导出固定格式数据
```java
SteelModel steelModel = new SteelModel();
		steelModel.setCode("A000001");
		steelModel.setName("李三");

		SheetEntity fields = new SheetEntity();
		fields.setTemplateDate(steelModel);


		String outPath = "D:/excel/basdout.xlsx";
		//创建文件流
		ExportExcel.templateExportExcel(ClassLoader.getSystemResourceAsStream("files/template2.xlsx"),fields,outPath);

```

### 通过模板导出列表数据

```java
List<Hege> heges = new ArrayList<>();
		for (int i = 1; i <= 5; i++) {
			Hege hege = new Hege();
			hege.setXuhao(i+"");
			hege.setNeirong("评价内容"+i);
			hege.setBiaozhun("是或否");
			if(i==3){
				hege.setFenzhi("是");
			}else{
			hege.setFenzhi("否");
			}
			hege.setBeizhu("备注");
			heges.add(hege);
		}


		SheetEntity fields = new SheetEntity();
		fields.put(0, "xuhao", "序号");
		fields.put(1, "neirong", "评价内容");
		fields.put(2, "biaozhun", "评价标准");
		fields.put(3, "fenzhi", "是否关键要求",new InitCell() {
			@Override
			public Object getCallValue(Object value) {
				if(value.equals(1)){
					return "是";
				}else{
					return "否";
				}
			}

			@Override
			public CellStyle getCellStyle(Object value, CellStyle cellStyle, SheetEntity sheetEntity) {
				if(value.equals(1)){
					CellStyle newCellStyle = sheetEntity.getCallStyle("fenzhi-yes");
					if(newCellStyle == null){
						newCellStyle = sheetEntity.getWorkbook().createCellStyle();
						newCellStyle.cloneStyleFrom(cellStyle);
						newCellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);//设置前景填充样式
						newCellStyle.setFillForegroundColor(HSSFColor.BLUE_GREY.index);//前景填充色
						sheetEntity.addCallStyles("fenzhi-yes", newCellStyle);
					}
					cellStyle = newCellStyle;
				}else{
					CellStyle newCellStyle = sheetEntity.getCallStyle("fenzhi-no");
					if(newCellStyle == null){
						newCellStyle = sheetEntity.getWorkbook().createCellStyle();
						newCellStyle.cloneStyleFrom(cellStyle);
						newCellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);//设置前景填充样式
						newCellStyle.setFillForegroundColor(HSSFColor.DARK_RED.index);//前景填充色
						sheetEntity.addCallStyles("fenzhi-no", newCellStyle);
					}
					cellStyle = newCellStyle;
				}

				return cellStyle;
			}
		});
		fields.put(4, "beizhu", "备注");
		//数据插入的行数，从0开始
		fields.setFirst(2);
		fields.setData(heges);
		String outPath = "D:/excel/basdout.xlsx";
		//创建文件流
		ExportExcel.templateExportExcel(ClassLoader.getSystemResourceAsStream("files/template.xlsx"),fields,outPath);
```

### 固定单元格数据+列表数据
```java
SteelModel steelModel = new SteelModel();

		steelModel.setCode("A000001");
		steelModel.setName("李三");

		List<Hege> heges = new ArrayList<>();
		for (int i = 1; i <= 10; i++) {
			Hege hege = new Hege();
			hege.setXuhao(i+"");
			hege.setNeirong("评价内容"+i);
			hege.setBiaozhun("是或否");
			hege.setFenzhi("是");
			hege.setBeizhu("备注");
			heges.add(hege);
		}


		SheetEntity fields = new SheetEntity();
		fields.put(0, "xuhao", "序号");
		fields.put(1, "neirong", "评价内容");
		fields.put(2, "biaozhun", "评价标准");
		fields.put(3, "fenzhi", "是否关键要求");
		fields.put(4, "beizhu", "备注");
		fields.setFirst(2);
		fields.setTemplateDate(steelModel);
		fields.setData(heges);

		String outPath = "D:/excel/basdout.xlsx";
		//创建文件流
		ExportExcel.templateExportExcel(ClassLoader.getSystemResourceAsStream("files/template2.xlsx"),fields,outPath);


```




#### 导出多页数据

```java
//查询条件
		java.sql.Date start = new java.sql.Date(DateUtils.parseDate("2017-01-05", "yyyy-MM-dd").getTime());
		java.sql.Date end =  new java.sql.Date(DateUtils.parseDate("2017-01-25", "yyyy-MM-dd").getTime());
		Random random = new Random();
		
		//模拟数据
		List<SteelModel> datas = new ArrayList<>();
		for (int i = 0; i < 100000; i++) {
			SteelModel steelModel = new SteelModel();
			steelModel.setId(i);
			steelModel.setSon(new Son("子类名字"+i));
			steelModel.setName("名字"+i%8358);
			steelModel.setCode("代码"+i);
			steelModel.setModel("模型"+i%1568);
			steelModel.setType("族类型"+i%3933);
			steelModel.setDate(new Date(start.getTime()+random.nextInt(172800000)*10l));
			datas.add(steelModel);
		}
		
		//处理数据
		List<SteelModel> steel = new ArrayList<>();
		Map<String,SteelModel> steels = new HashMap<>();
		//按data.getName()分组  把相同data.getName()的数据放入到data.dateData里面data.dateData是一个map结构
		//本例中是需要按时间展示所以data.dateData的键 为时间，详情点击进入addDateData方法
		//在获取是数据是  建会作为excel的表头出现
		for (SteelModel data : datas) {
			SteelModel steelModel = steels.get(data.getName());
			
			if(steelModel == null){
				steelModel =data.clone();
				steels.put(steelModel.getName(), steelModel);
				steel.add(steelModel);
			}
			steelModel.addDateData(data);
		}
		
		String outPath = "D:/excel/basdout.xlsx";
		
		
		//excel生成规则
		Map<String, String> steelParm = new HashMap<>();
		
		steelParm.put("代码", "code");
		steelParm.put("code", "代码");
		steelParm.put("模型", "model");
		steelParm.put("model", "模型");
		steelParm.put("族类型", "type");
		steelParm.put("type", "族类型");

		//创建excel页数
		List<String> items =  new ArrayList<>();
		items.add("代码");
		items.add("模型");
		items.add("族类型");
		List<SheetEntity> sheetEntitys = new ArrayList<>();
		//生成
		for (String item : items) {
			//创建文件流     
			SheetEntity fields = new SheetEntity();
			fields.setHead(true);
			fields.setName(item);
			fields.setData(steel);
			Integer day = 20;
			fields.put(0, "name", "名称");
			Calendar calendar = Calendar.getInstance();
			calendar.setTime(start);
			calendar.add(Calendar.DATE, -1);
			String dateStr ;
			for (int i = 0; i < day; i++) {
				calendar.add(Calendar.DATE, 1);
				dateStr = DateFormatUtils.format(calendar, "yyyy-MM-dd");
				fields.put(i+1, "dateData."+dateStr+"."+steelParm.get(item), dateStr,12);
			}
			
			sheetEntitys.add(fields);
		}
		
		ExportExcel.writeExcel(sheetEntitys,outPath);
```

#### 读取数据  没有表头

```java
		ReadExcel readExcel = new ReadExcel("files/notHead.xlsx");
		SheetEntity fields = new SheetEntity();
		fields.put(0, "id");
		fields.put(1, "name");
		fields.put(2, "date");
		
		//需要把表头设置成 没有表头
		fields.setHead(false);
		List<SteelModel> steels = readExcel.readNotHeadExcel(SteelModel.class, fields);
		for (SteelModel steelModel : steels) {
			System.out.println(steelModel.getId()+"---->"+steelModel.getName()+"---->"+steelModel.getDate());
		}
```

#### 自带表头，表头为属性名

```java
		ReadExcel readExcel = new ReadExcel("files/fieldHrad.xlsx");
		List<SteelModel> steels = readExcel.readFieldHeadExcel(SteelModel.class);
		for (SteelModel steelModel : steels) {
			System.out.println(steelModel.getId()+"---->"+steelModel.getName()+"---->"+steelModel.getDate());
		}
```

#### 自带表描述和表头，表头为字段描述

```java
		ReadExcel readExcel = new ReadExcel("files/descHrad.xlsx");
		Map<String, String> fieldMaps = new HashMap<>();
		fieldMaps.put("名称", "name");
		fieldMaps.put("日期", "date");
		fieldMaps.put("编号", "id");
		List<SteelModel> steels = readExcel.readDescHradExcel(SteelModel.class, fieldMaps);
		for (SteelModel steelModel : steels) {
			System.out.println(steelModel.getId()+"---->"+steelModel.getName()+"---->"+steelModel.getDate());
		}
```

#### 自定义读取规则

```java
		ReadExcel readExcel = new ReadExcel("files/descTableHrad.xlsx");
		Map<String, String> fieldMaps = new HashMap<>();
		fieldMaps.put("序号", "xuhao");
		fieldMaps.put("评价内容", "neirong");
		fieldMaps.put("评价标准", "biaozhun");
		fieldMaps.put("分值", "fenzhi");
		fieldMaps.put("备注", "beizhu");
		
		//表头的行数
		fields.setHeadRowNumber(2);
		//数据开始的行数
		fields.setFirst(3);

		List<Hege> heges = readExcel.readDescTableHradExcel(Hege.class, fieldMaps);
		for (Hege hege : heges) {
			System.out.println(hege.getXuhao()+"---->"
					+hege.getNeirong()+"---->"
					+hege.getBiaozhun()+"---->"
					+hege.getFenzhi()+"---->"
					+hege.getBeizhu());
		}
```

#### 获取数据对应的行
方法一： 实现接口，会自动赋值
实现接口类 com.shr25.common.excel.po.ExcelData
```java
public class Test implements ExcelData {
    …… 其他属性
    
    @TableField(exist = false)
    @JsonIgnore
    private Integer excelIndex;

    public Integer getExcelIndex() {
        return excelIndex;
    }

    public void setExcelIndex(Integer excelIndex) {
        this.excelIndex = excelIndex;
    }
}
```
方法二：添加设置，实现InitCell类型，重写getCallValue方法，index参数就是excel中的行号
```java
heetEntity fields = new SheetEntity();
fields.put(0, "id", "序号", 8, new InitCell<Test>() {
    //设置样式
    @Override
    public Object getCallValue(Cell cell, Object value, Test row, Integer index) {
        return value;
    }
});
```