csv和xlsx区别 by CSND-weixin_30535565
autopoi导出xls/xlsx by 看云-jeecg
Java端轻量级CSV读写库javacsv by maven-respository
xlsx、xls(早期的版本)均是Microsoft office下的Excel所维护的电子表格的文件格式
csv则为更加通用的、更加简洁的电子表格的文件格式
接下来将展现一波.csv文件的结构之简洁:
Excel 打开方式
记事本 打开方式
官方推荐使用注解的方式，配置字段、列头、值的映射关系
同时，官方保留了自定义View，支持了手工通过Map配置映射
官方还基于apache.poi封装了ExcelExportUtil,支持导出workbook
public class ExcelView extends MiniAbstractExcelView {
public ExcelView(){}
@Override
protected void renderMergedOutputModel(Map model, HttpServletRequest request, HttpServletResponse response) throws Exception {
String codedFileName = "临时文件";
Workbook workbook = null;
String[] exportFields = null;
Object exportFieldStr = model.get("exportFields");
if (exportFieldStr != null && exportFieldStr != "") {
exportFields = exportFieldStr.toString().split(",");
}
if (model.containsKey("mapList")) {
List list = (List)model.get("mapList"); //maplist 做字段映射用
if (list.size() == 0) {
throw new RuntimeException("MAP_LIST IS NULL");
}
workbook = ExcelExportUtil.exportExcel((ExportParams) model.get("params"), list, (Collection) model.get("data"));
}
if (model.containsKey("fileName")) {
codedFileName = (String)model.get("fileName");
}
if (workbook instanceof HSSFWorkbook) {
codedFileName = codedFileName + ".xls";
} else {
codedFileName = codedFileName + ".xlsx";
}
if (this.isIE(request)) {
codedFileName = URLEncoder.encode(codedFileName, "UTF8");
} else {
codedFileName = new String(codedFileName.getBytes("UTF-8"), "ISO-8859-1");
}
response.setHeader("content-disposition", "attachment;filename=" + codedFileName);
ServletOutputStream out = response.getOutputStream();
workbook.write(out);
out.flush();
}
}
2.2 顺势改造官方demo，实现Map导出ModelAndView
// 导出的入口
@Override
public ModelAndView exportExcelByMap(
String fileName
,String titleName
,List srcList
,Class clz
,List fieldList
,String realname
) {
ModelAndView result = new ModelAndView(new ExcelView());
try {
if(StringUtils.isBlank(realname)){
realname = ((LoginUser) SecurityUtils.getSubject().getPrincipal()).getRealname();
}
//导出参数
ExportParams exportParams=new ExportParams(titleName, "导出人:" + realname, fileName);
exportParams.setType(ExcelType.HSSF);
exportParams.setImagebasePath(upLoadPath);
//导出dataList
List> dataList = this.buildDataListForMapExcel(clz, srcList, fieldList);
//导出mapList
List mapList = this.buildMapListForMapExcel(fieldList);
//准备导出
result.addObject(NormalExcelConstants.FILE_NAME, fileName); //此处设置的filename无效 ,前端会重更新设置一下
result.addObject(NormalExcelConstants.MAP_LIST ,mapList);
result.addObject(NormalExcelConstants.PARAMS ,exportParams);
result.addObject(NormalExcelConstants.DATA_LIST ,dataList);
} catch (Exception e) {
log.info("导出modelAndView 异常");
e.printStackTrace();
}
return result;
}
//构造dataList，即数据集合
@Override
public List> buildDataListForMapExcel(Class clz, List srcList, List fieldList) {
if(clz == null ||srcList ==null ||srcList.isEmpty() ||fieldList ==null ||fieldList.isEmpty()){
log.info("导出dataList 传参为空");
return null;
}
List> result = new ArrayList<>();
try {
for (T t : srcList) {
Map map = new linkedHashMap<>();
for (FieldExcelEntity field : fieldList) {
String fieldName = field.getName();
String fieldValue = "";
Object fieldValueObj = ReflectUtil.getFieldValue(t, fieldName);
if(oConvertUtils.isNotEmpty(fieldValueObj)) fieldValue = fieldValueObj.toString();
map.put(fieldName ,fieldValue);
}
result.add(map);
}
} catch (Exception e) {
log.info("导出dataList 异常");
e.printStackTrace();
}
return result;
}
//构造mapList，即字段、值、列头的映射集合
@Override
public List buildMapListForMapExcel(List fieldList) {
if(fieldList == null ||fieldList.isEmpty()){
log.info("导出mapList 传参为空");
return null;
}
List result = new ArrayList<>();
try {
for (FieldExcelEntity field : fieldList) {
String fieldZhCn = field.getZhCn();
String fieldName = field.getName();
Integer fieldWidth = field.getWidth();
result.add(new ExcelExportEntity(fieldZhCn ,fieldName ,fieldWidth));
}
} catch (Exception e) {
log.info("导出mapList 异常");
e.printStackTrace();
}
return result;
}
补充一下 ,上面的 ExcelExportEntity.java 不过是我自己仿着AutoPoi的注解实现方式，封装的一个实体类而已
@Data
public class FieldExcelEntity {
private String name;
private String zhCn;
private Integer width;
public FieldExcelEntity(){}
public FieldExcelEntity(String name,String zhCn){this.name=name;this.zhCn=zhCn;this.width=15;}
public FieldExcelEntity(String name,String zhCn,Integer width){this.name=name;this.zhCn=zhCn;this.width=width;}
}
2.3 既然已经写出了ModelAndView的导出代码,借助ExcelExportUtil,一样可以实现导出workbook(毕竟底层的poi所支持的)
@Override
public Workbook exportWorkBookByMap(String fileName, String titleName, List srcList, Class clz, List fieldList ,String realname) {
if(StringUtils.isBlank(realname)){
realname = ((LoginUser) SecurityUtils.getSubject().getPrincipal()).getRealname();
}
//导出参数
ExportParams exportParams=new ExportParams(titleName, "导出人:" + realname, fileName);
exportParams.setType(ExcelType.HSSF);
exportParams.setImagebasePath(upLoadPath);
//导出dataList
List> dataList = this.buildDataListForMapExcel(clz, srcList, fieldList);
//导出mapList
List entityList = this.buildMapListForMapExcel(fieldList);
//ExcelExportUtil.exportExcel 有很多重载
return ExcelExportUtil.exportExcel(exportParams, entityList, dataList);
}
2.4 举一反三，根据workbook的特性，我们可以尝试导出MultipartFile
@Override
public MultipartFile exportMultipartFileByMap(String fileName, String titleName, List srcList, Class clz, List fieldList,String realname) {
MultipartFile result = null;
ByteArrayOutputStream os = new ByteArrayOutputStream();
Workbook sheets = this.exportWorkBookByMap(fileName, titleName, srcList, clz, fieldList,realname);
try {
sheets.write(os);
ByteArrayInputStream in = new ByteArrayInputStream(os.toByteArray());
String base64Str = this.ioTobase64(in);
result = this.base64ToMultipart(base64Str);
in.close();
} catch (IOException e) {
e.printStackTrace();
}finally {
try {
os.close();
} catch (IOException e) {
e.printStackTrace();
}
try {
sheets.close();
} catch (IOException e) {
e.printStackTrace();
}
}
return result;
}
2.5 走到这里，那么导出File，文件改名啥的，就不谈了。
略
3. javacsv & csv可以结合csv的Excel、记事本 打开方式的截图，yy一波，应该可以理解下面的代码…
@Override
public File buildCsvFile(List> dataList,String[] nameList,String[] zhCnList ,String fileName) {
File result = null;
try {
result = new File(fileName+".csv");
if(result.exists()) result.delete();
result.createNewFile();
@Cleanup
CsvWriter csvWriter = new CsvWriter(result.getCanonicalPath(),',', Charset.forName("GBK"));
csvWriter.writeRecord(zhCnList);
String[] dataRecord = null;
int forLength = nameList.length;
String columnName = "";
String columnValue = "";
for (Map map : dataList) {
dataRecord = new String[forLength];
for (int i = 0 ;i
参数：
nameList:字段集合
zhCnList:列头集合
dataList:<字段,数据>的集合
3.1 javacsv 使用中遇到的问题
csvWriter.write()写入换行符，并不能换行，只是隔了个单元格，还是处在之前的一行
