- 1. excel 和 csv文件的区别
- Excel工具类
- 3. CSV工具类
Excel
- Excel是一个二进制文件，它保存有关工作簿中所有工作表的信息
- Excel不仅可以存储数据，还可以对数据进行操作
- 保存在excel中的文件不能被文本编辑器打开或编辑
- 由于数值和文本之间没有明确的区别或区分，Excel可能会使用自动格式化功能搞乱您的邮政编码和信用卡号码
- Excel导入数据时消耗更多的内存
CSV
- CSV是一个纯文本格式，用逗号分隔一系列值
- CSV文件只是一个文本文件，它存储数据，但不包含格式，公式，宏等。它也被称为平面文件
- CSV可以用Windows中的任何文本编辑器打开，如记事本，MS Excel，Microsoft Works 9等
- CSV是安全的，可以清楚地区分数值和文本。CSV不处理数据并按原样存储。
- 导入CSV文件可以更快，而且消耗更少的内存
@Slf4j
public class ExcelUtils {
public static void export2Web(HttpServletResponse response, String excelName, String sheetName, Class clazz, List data) throws Exception {
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("UTF-8");
excelName = URLEncoder.encode(excelName, "UTF-8");
response.setHeader("Content-disposition", "attachment;filename=" + excelName + ExcelTypeEnum.XLSX.getValue());
EasyExcel.write(response.getOutputStream(), clazz).sheet(sheetName).doWrite(data);
}
// 导入
public static void excelimport(InputStream stream, Class clazz, AnalysisEventListener listener) throws IOException {
ExcelReader excelReader = null;
try (InputStream in = stream) {
excelReader = EasyExcel.read(in, clazz,
listener).build();
ReadSheet readSheet = EasyExcel.readSheet(0).build();
excelReader.read(readSheet);
} catch (Exception ex) {
log.error("import excel to db fail", ex);
} finally {
// 这里一定别忘记关闭，读的时候会创建临时文件，到时磁盘会崩
if (excelReader != null) {
excelReader.finish();
}
}
}
}
Controller测试
//EXCEL 导出
@GetMapping("/Excel")
public String download(HttpServletResponse response) throws IOException {
//获取数据库数据
List list = testtableService.list();
ArrayList testtableEntity1s = new ArrayList<>();
// 把数据copy成excel样式的
List collect = list.stream().map(x -> {
TesttableEntity1 testtableEntity1 = new TesttableEntity1();
BeanUtils.copyProperties(x, testtableEntity1);
return testtableEntity1;
}).collect(Collectors.toList());
// 执行导出
EasyExcel.write(response.getOutputStream(), TesttableEntity1.class).sheet("模板").doWrite(collect);
return "ok";
}
其中 TesttableEntity1 是需要设置excel 样式的，如下
@Data
@HeadRowHeight(16)
public class TesttableEntity1 implements Serializable {
private static final long serialVersionUID = 1L;
@ExcelProperty(value = "id", index = 0)
@ColumnWidth(19)
private Long id;
@ExcelProperty(value = "名字", index = 1)
@ColumnWidth(19)
private String name;
@ExcelProperty(value = "年龄", index = 2)
@ColumnWidth(19)
private Integer age;
@ExcelProperty(value = "地址", index = 3)
@ColumnWidth(19)
private String position;
@ExcelProperty(value = "身份证", index = 4)
@ColumnWidth(19)
private Integer idCard;
@ExcelProperty(value = "备注", index = 5)
@ColumnWidth(19)
private Integer remark;
@ExcelProperty(value = "是否删除", index = 6)
@ColumnWidth(19)
private Integer isDelete;
}
3. CSV工具类
public class CsvUtils {
private static final String CSV_COLUMN_SEPARATOR = ",";
private static final String CSV_ROW_SEPARATOR = System.lineSeparator();
public static void export2Csv(HttpServletResponse response, Class clazz, List datas) throws Exception {
Objects.requireNonNull(clazz, "CSV导出类型不能为空");
ServletOutputStream outputStream = response.getOutputStream();
List csvFields = parseCsvField(clazz);
StringBuilder builder = new StringBuilder();
if (!CollectionUtils.isEmpty(csvFields) && !CollectionUtils.isEmpty(datas)) {
String title = csvFields.stream().map(CsvField::getTitle).collect(Collectors.joining(","));
builder.append(title);
builder.append(CSV_ROW_SEPARATOR);
for (T data : datas) {
parseCsvFiledValue(data, builder, csvFields);
}
}
// 解决office excel乱码，增加UTF-8标头
outputStream.write(new byte []{(byte) 0xEF, (byte) 0xBB, (byte) 0xBF});
outputStream.write(builder.toString().getBytes(StandardCharsets.UTF_8));
outputStream.flush();
}
public static void setCsvHeader(HttpServletResponse response, String fileName) throws Exception {
//这一个配置加上后网络错误，但生产又是正常的， 疑惑
// response.setContentType("application/ms-txt.numberformat:@");
response.setCharacterEncoding("UTF-8");
fileName = URLEncoder.encode(fileName, "UTF-8");
response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".csv");
}
private static void parseCsvFiledValue(T obj, StringBuilder builder, List csvFields) throws Exception {
Objects.requireNonNull(obj, "CSV出对象不能为空");
Class objClass = obj.getClass();
for (CsvField csvField : csvFields) {
Field field = objClass.getDeclaredField(csvField.getKey());
field.setAccessible(true);
Object value = field.get(obj);
if (Objects.nonNull(value)) {
if (value instanceof LocalDateTime){
// 增加t处理时间格式显示不对问题
builder.append("t").append( DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss").format((LocalDateTime) value)).append("t");
} else{
builder.append(value);
}
} if (csvFields.indexOf(csvField) != csvFields.size() - 1) {
builder.append(CSV_COLUMN_SEPARATOR);
}
} builder.append(CSV_ROW_SEPARATOR);
}
private static List parseCsvField(Class clazz) {
List csvFields = new ArrayList<>();
for (Field declaredField : clazz.getDeclaredFields()) {
CsvProperty csvProperty = declaredField.getAnnotation(CsvProperty.class);
if (Objects.nonNull(csvProperty)) {
CsvField csvField = new CsvField();
csvField.setTitle(csvProperty.title());
csvField.setKey(declaredField.getName());
csvField.setIndex(csvProperty.index());
csvFields.add(csvField);
}
}
return csvFields;
}
@Data
private static class CsvField implements Comparable {
private String title;
private String key;
private int index;
@Override
public int compareTo(@NotNull CsvField next) {
return index - next.getIndex() < 0 ? index : next.getIndex();
}
}
}
自定义注解CsvProperty：
@documented
@Target({FIELD})
@Retention(RUNTIME)
public @interface CsvProperty {
String title();
int index() default -1;
}
导出csv的类
@Data
public class CsvEntity {
@CsvProperty(title = "姓名", index = 1)
private String name;
@CsvProperty(title = "年龄", index = 2)
private Integer age;
@CsvProperty(title = "地址", index = 3)
private String address;
}
Controller导出 csv 文件
//CSV导出
@GetMapping("/CSV")
public String downloadCSV(HttpServletResponse response) throws Exception {
CsvEntity entity1 = new CsvEntity();
entity1.setName("haibo");
entity1.setAge(12);
entity1.setAddress("河南");
CsvEntity entity2 = new CsvEntity();
entity2.setName("chengang");
entity2.setAge(13);
entity2.setAddress("湖南");
CsvEntity entity3 = new CsvEntity();
entity3.setName("qingwen");
entity3.setAge(13);
entity3.setAddress("广州");
List list = new ArrayList<>();
list.add(entity1);
list.add(entity2);
list.add(entity3);
String yyyyMMddHHmmss = DateTimeFormatter.ofPattern("yyyyMMddHHmmss").format(LocalDateTime.now());
CsvUtils.setCsvHeader(response, "报表" + yyyyMMddHHmmss);
CsvUtils.export2Csv(response, CsvEntity.class, list);
return "ok";
}
