org.apache.poi poi 4.0.1 org.apache.poi poi-ooxml 4.0.1
实体类
@Data
@EqualsAndHashCode(callSuper = false)
@ApiModel(value="Tea对象", description="")
@AllArgsConstructor
public class Tea implements Serializable {
private static final long serialVersionUID = 1L;
@TableId(value = "tid", type = IdType.AUTO)
private Integer tid;
private String name;
}
package com.example.try_test.entity;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
public class Excel {
private static final Logger LOGGER = LoggerFactory.getLogger(Excel.class);
//模拟数据
public static List getTea() {
Tea tea = new Tea(1, "qweqwe");
Tea tea2 = new Tea(1, "qweqwe2");
Tea tea3 = new Tea(1, "qweqwe3");
Tea tea4 = new Tea(1, "qweqwe4");
Tea tea5 = new Tea(1, "qweqwe5");
Tea tea6 = new Tea(1, "qweqwe");
Tea tea7 = new Tea(1, "qweqwe7");
Tea tea8 = new Tea(1, "qweqwe8");
List list = new ArrayList<>();
list.add(tea);
list.add(tea2);
list.add(tea3);
list.add(tea4);
list.add(tea5);
list.add(tea6);
list.add(tea7);
list.add(tea8);
return list;
}
public static void main(String[] args) {
FileOutputStream outputStream = null;
try {
//创建一个工作本
Workbook wb = new XSSFWorkbook();
//为文本创建文本样式
CellStyle style = wb.createCellStyle();
XSSFFont font = new XSSFFont();
font.setBold(true);
font.setColor(Font.COLOR_RED);
style.setFont(font);
style.setAlignment(HorizontalAlignment.CENTER);
Sheet sheet = wb.createSheet("qweqwe11");
//创建一行
Row row = sheet.createRow(0);
//为首行设置标题和样式
Cell cell = row.createCell(0);
cell.setCellValue("教师编号");
cell.setCellStyle(style);
cell = row.createCell(1);
cell.setCellStyle(style);
cell.setCellValue("教师名称");
//获取列表内容
List tea = getTea();
int len = tea.size();
for (int i = 0; i < len; i++) {
//循环为每一行设置信息
Tea tea1 = tea.get(i);
row = sheet.createRow(i + 1);
row.createCell(0).setCellValue(tea1.getTid());
row.createCell(1).setCellValue(tea1.getName());
}
LOGGER.info("输出结束");
outputStream = new FileOutputStream("D:\excel\qweqwe.xlsx");
wb.write(outputStream);
} catch (IOException e) {
LOGGER.error("出错了！！！");
} finally {
if (outputStream != null) {
try {
outputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
}
结果：
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Cat {
private Integer catId;
private Double weigth;
private Double height;
private Integer age;
private String color;
}
@Data
@AllArgsConstructor
@NoArgsConstructor
public class CatCopy {
@ExcelResource(value = "宠物猫编号")
private Integer catId;
@ExcelResource(value = "宠物猫重量")
private Double weigth;
@ExcelResource(value = "宠物猫高度")
private Double height;
@ExcelResource(value = "宠物猫颜色")
private String color;
}
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface ExcelResource {
String value() default "";
}
@Getter
public enum ExcelTypeEnum {
CAT(12,"小猫")
;
private Integer code;
private String desc;
ExcelTypeEnum(Integer code, String desc) {
this.code = code;
this.desc = desc;
}
}
@Service
public class CatService {
private static final Logger LOGGER= LoggerFactory.getLogger(CatService.class);
public static List getCat()
{
List list=new ArrayList<>();
Cat cat = new Cat(1, 12.0, 20.0, 5, "红色");
Cat cat2 = new Cat(2, 12.0, 20.0, 5, "红色");
Cat cat3 = new Cat(3, 12.0, 20.0, 5, "红色");
Cat cat4 = new Cat(4, 12.0, 20.0, 5, "黑色");
Cat cat5 = new Cat(5, 12.0, 20.0, 5, "白色");
Cat cat6 = new Cat(6, 12.0, 20.0, 5, "黑白色");
list.add(cat6);
list.add(cat);
list.add(cat2);
list.add(cat3);
list.add(cat4);
list.add(cat5);
return list;
}
public static String outPutExcel(String path,ExcelTypeEnum type,String fileName)
{
//创建一个工作簿，在这里，工作簿有下表的，比如一个excel可能有几个文件
XSSFWorkbook workbook = new XSSFWorkbook();
//这里就是excel中的一个文件
XSSFSheet sheet = workbook.createSheet("宠物信息表");
//设置工作簿小格样式
XSSFCellStyle style = workbook.createCellStyle();
style.setAlignment(HorizontalAlignment.CENTER);
XSSFFont font = workbook.createFont();
//设置字体样式
font.setBold(true);
font.setFontName("黑体");
font.setFontHeightInPoints((short) 12);
style.setFont(font);
XSSFRow row = sheet.createRow(0);
//通过自定义注解获取注解的信息
List values = getNameAndValue(getObject(type));
int len=values.size();
//设置标题和其样式
XSSFCell cell=null;
for(int i=0;i
cell = row.createCell(i);
cell.setCellStyle(style);
cell.setCellValue(values.get(i));
}
//具体内容的样式重新创建
XSSFCellStyle dataStyle = workbook.createCellStyle();
dataStyle.setAlignment(HorizontalAlignment.CENTER);
List cat = getCat();
List list = changeObject(cat, type);
int catLen=list.size();
for(int i=0;i
row=sheet.createRow(i+1);
CatCopy catCopy = (CatCopy) list.get(i);
row.createCell(0).setCellValue(catCopy.getCatId());
row.createCell(1).setCellValue(catCopy.getWeigth());
row.createCell(2).setCellValue(catCopy.getHeight());
row.createCell(3).setCellValue(catCopy.getColor());
}
FileOutputStream fileOutputStream=null;
try {
String name=path+ "\"+fileName;
fileOutputStream=new FileOutputStream(name);
workbook.write(fileOutputStream);
}catch (IOException e) {
}finally {
if(fileOutputStream!=null){
try {
fileOutputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
return null;
}
public static List changeObject(List list,ExcelTypeEnum type)
{
switch (type){
case CAT:
{
int len=list.size();
List list1=new ArrayList<>();
for(int i=0;i
CatCopy catCopy = new CatCopy();
BeanUtils.copyProperties(list.get(i),catCopy);
list1.add(catCopy);
}
return list1;
}
default: return null;
}
}
private static List getNameAndValue(Object o)
{
List list=new ArrayList<>();
//获取属性名称
Field[] fields = o.getClass().getDeclaredFields();
if(fields!=null){
for(Field field:fields)
{
ExcelResource annotation = field.getAnnotation(ExcelResource.class);
if(annotation.value()!=null&&!annotation.value().equals("")) {
list.add(annotation.value());
}
}
}else {
LOGGER.error("不存在成员变量");
return null;
}
return list;
}
public static void main(String[] args) {
outPutExcel("D:\excel",ExcelTypeEnum.CAT,"ty.xlsx");
}
public static Object getObject(ExcelTypeEnum type)
{
switch (type){
case CAT: return new CatCopy();
default:
return null;
}
}
}
结果：
