1.定义注解类和具体实现类
@documented
@Retention(RUNTIME)
@Target({FIELD})
@Inherited
public @interface ExcelColumn {
String title() default "";
int order() default 0;
}
public abstract class ExcelUtil{
private final static Logger log = LoggerFactory.getLogger(ExcelUtil.class);
private final static String EXCEL2003 = ".xls";
private final static String EXCEL2007 = ".xlsx";
private static List readExcel(Class cls,String filename,byte[] excelfile){
List dataList = new ArrayList<>();
Workbook workbook = null;
try {
InputStream inputStream = new ByteArrayInputStream(excelfile);
if (filename.endsWith(EXCEL2007)) {
workbook = new XSSFWorkbook(inputStream);
}
if (filename.endsWith(EXCEL2003)) {
workbook = new HSSFWorkbook(inputStream);
}
System.out.println("workbook "+workbook);
if (workbook != null) {
Map> classMap = new HashMap<>();
List fields = Stream.of(cls.getDeclaredFields()).collect(Collectors.toList());
fields.forEach(field->{
ExcelColumn annotation = field.getAnnotation(ExcelColumn.class);
if (annotation != null) {
String value = annotation.title();
if (value.length()<1)return;
if (!classMap.containsKey(value)) {
classMap.put(value, new ArrayList<>());
}
field.setAccessible(true);
classMap.get(value).add(field);
}
}
);
//索引-->columns
Map> reflectionMap = new HashMap<>(16);
//默认读取第一个sheet
Sheet sheet = workbook.getSheetAt(0);
boolean firstRow = true;
for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) {
Row row = sheet.getRow(i);
//首行 提取注解
if (firstRow) {
for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {
Cell cell = row.getCell(j);
String cellValue = getCellValue(cell);
if (classMap.containsKey(cellValue)) {
reflectionMap.put(j, classMap.get(cellValue));
}
}
firstRow = false;
} else {
//忽略空白行
if (row == null) {
continue;
}
try {
T t = cls.newInstance();
//判断是否为空白行
boolean allBlank = true;
for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {
if (reflectionMap.containsKey(j)) {
Cell cell = row.getCell(j);
String cellValue = getCellValue(cell);
if (cellValue!=null && cellValue.length()>0) {
allBlank = false;
}
List fieldList = reflectionMap.get(j);
fieldList.forEach(
x -> {
try {
handleField(t, cellValue, x);
} catch (Exception e) {
log.error(String.format("reflect field:%s value:%s exception!", x.getName(), cellValue), e);
}
}
);
}
}
if (!allBlank) {
dataList.add(t);
} else {
log.warn(String.format("row:%s is blank ignore!", i));
}
} catch (Exception e) {
log.error(String.format("parse row:%s exception!", i), e);
}
}
}
}
} catch (Exception e) {
log.error(String.format("parse excel exception!"), e);
} finally {
if (workbook != null) {
try {
workbook.close();
} catch (Exception e) {
log.error(String.format("parse excel exception!"), e);
}
}
}
return dataList;
}
private static void handleField(T t, String value, Field field) throws Exception {
Class type = field.getType();
if (type == null || type == void.class || value == null || value.length()<1)return;
if (type == Object.class) {
field.set(t,value);
} else if (Number.class.isAssignableFrom(type)) {
field.set(t,type.getConstructor(String.class).newInstance(value));
} else if (type == Boolean.class) {
field.set(t, Boolean.valueOf(value));
} else if (type == Date.class) {
field.set(t, value);
} else if (type == String.class) {
field.set(t, value);
} else {
field.set(t, type.getConstructor(String.class).newInstance(value));
}
}
private static String getCellValue(Cell cell) {
if (cell == null) {
return "";
}
if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
if (HSSFDateUtil.isCellDateFormatted(cell)) {
return HSSFDateUtil.getJavaDate(cell.getNumericCellValue()).toString();
} else {
return new BigDecimal(cell.getNumericCellValue()).toString();
}
} else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
return Optional.ofNullable(cell.getStringCellValue()).orElse("").trim();
} else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
return Optional.ofNullable(cell.getCellFormula()).orElse("").trim();
} else if (cell.getCellType() == Cell.CELL_TYPE_BLANK) {
return "";
} else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
return String.valueOf(cell.getBooleanCellValue());
} else if (cell.getCellType() == Cell.CELL_TYPE_ERROR) {
return "ERROR";
} else {
return cell.toString().trim();
}
}
public static List readOfByte(Class cls,String filename,byte[] excelfile){
return readExcel(cls,filename,excelfile);
}
public static List readOfFile(Class cls,String filepath){
try {
if (filepath==null || !filepath.matches("^.+\.(?i)(xls|xlsx)$"))
throw new RuntimeException("文件格式不正确");
Path path = Paths.get(filepath);
if(!Files.exists(path))throw new RuntimeException("文件不存在");
byte[] bytes = Files.readAllBytes(path);
return readExcel(cls,path.getFileName().toString(),bytes);
} catch (Exception e) {
e.printStackTrace();
return null;
}
}
public static void writeToFile(List dataList,String savepath){
if(dataList==null || dataList.size()<1)throw new RuntimeException("dataList is Empty");
SXSSFWorkbook workbook = writeExcel(dataList,dataList.get(0).getClass());
File file = new File(savepath);
if(!file.getParentFile().exists())file.getParentFile().mkdirs();
try {
workbook.write(new FileOutputStream(file));
} catch (Exception e) {
e.printStackTrace();
}
}
public static byte[] writeToByte(List dataList){
if(dataList==null || dataList.size()<1)throw new RuntimeException("dataList is Empty");
try {
ByteArrayOutputStream out = new ByteArrayOutputStream();
SXSSFWorkbook workbook = writeExcel(dataList,dataList.get(0).getClass());
workbook.write(out);
return out.toByteArray();
} catch (Exception e) {
e.printStackTrace();
return new byte[0];
}
}
private static SXSSFWorkbook writeExcel(List dataList, Class cls){
Field[] fields = cls.getDeclaredFields();
List fieldList = Arrays.stream(fields)
.filter(field -> {
ExcelColumn annotation = field.getAnnotation(ExcelColumn.class);
if (annotation != null && annotation.order() > 0) {
field.setAccessible(true);
return true;
}
return false;
}).sorted(Comparator.comparing(field -> {
int col = 0;
ExcelColumn annotation = field.getAnnotation(ExcelColumn.class);
if (annotation != null) {
col = annotation.order();
}
return col;
})).collect(Collectors.toList());
SXSSFWorkbook wb = new SXSSFWorkbook(1000);
Sheet sheet = wb.createSheet("Sheet1");
AtomicInteger ai = new AtomicInteger();
{
Row row = sheet.createRow(ai.getAndIncrement());
AtomicInteger aj = new AtomicInteger();
//写入头部
fieldList.forEach(field -> {
ExcelColumn annotation = field.getAnnotation(ExcelColumn.class);
String columnName = "";
if (annotation != null) {
columnName = annotation.title();
}
Cell cell = row.createCell(aj.getAndIncrement());
CellStyle cellStyle = wb.createCellStyle();
cellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
Font font = wb.createFont();
font.setBoldweight(Font.BOLDWEIGHT_NORMAL);
cellStyle.setFont(font);
cell.setCellStyle(cellStyle);
cell.setCellValue(columnName);
});
}
if (dataList!=null && dataList.size()>0) {
dataList.forEach(t -> {
Row row1 = sheet.createRow(ai.getAndIncrement());
AtomicInteger aj = new AtomicInteger();
fieldList.forEach(field -> {
Class type = field.getType();
Object value = "";
try {
value = field.get(t);
} catch (Exception e) {
e.printStackTrace();
}
int index = aj.getAndIncrement();
Cell cell = row1.createCell(index);
//sheet.autoSizeColumn(index);
//sheet.setColumnWidth(index, sheet.getColumnWidth(index)*12/10);
if (value != null) {
if (type == Date.class) {
cell.setCellValue(value.toString());
} else {
cell.setCellValue(value.toString());
}
cell.setCellValue(value.toString());
}
});
});
}
wb.getSheet("Sheet1").createFreezePane(0, 1, 0, 1);
return wb;
}
}
@Data
@EqualsAndHashCode(callSuper=false)
@NoArgsConstructor
@AllArgsConstructor
@ToString(callSuper=true)
@Builder
@Accessors(chain=true)
@Entity(name="table_fileinfo")
public final class FileInfo extends baseEntry{
private static final long serialVersionUID = 1L;
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Integer fid;
@ExcelColumn(title="name",order=1)
@Column(length=255,nullable=false)
private String name;
@ExcelColumn(title="size",order=2)
@Column(length=255,nullable=false)
private String size;
@ExcelColumn(title="time",order=3)
@Column(length=255,nullable=false)
private String time;
@ExcelColumn(title="path",order=4)
@Column(length=255,nullable=false)
private String path;
@ExcelColumn(title="type",order=5)
@Column(length=255,nullable=false)
private String type;
@ExcelColumn(title="fenlei",order=6)
@Column(length=255,nullable=false)
private String fenlei;
@ExcelColumn(title="image",order=7)
@Column(length=255,nullable=false)
private String image;
}
2.测试实现效果成功写入到文件
public static void main(String[] args) {
ExcelUtil.writeToFile(HttpServices.getFiles(null,null,null,null,"D:\桌面\video"),"D:\桌面\test.xlsx");
//List lists = ExcelUtil.readOfFile(FileInfo.class,"D:\桌面\test.xlsx");
//lists.forEach(System.out::println);
}
欢迎分享，转载请注明来源：内存溢出
微信扫一扫
支付宝扫一扫
评论列表（0条）