csv导入导出

柔情只为你懂 2022-12-05 02:36 216阅读 0赞

## 一、依赖 ##

<dependency>
                <groupId>org.apache.commons</groupId>
                <artifactId>commons-csv</artifactId>
                <version>1.7</version>
            </dependency>

## 二、工具类 ##

import lombok.extern.slf4j.Slf4j;
    import org.apache.poi.hssf.usermodel.HSSFCell;
    import org.apache.poi.hssf.usermodel.HSSFRow;
    import org.apache.poi.hssf.usermodel.HSSFSheet;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    
    import java.io.BufferedWriter;
    import java.io.ByteArrayOutputStream;
    import java.io.IOException;
    import java.io.OutputStreamWriter;
    import java.nio.charset.StandardCharsets;
    import java.util.Iterator;
    import java.util.LinkedHashMap;
    import java.util.List;
    import java.util.Map;
    
    /** * CSV文件帮助类 */
    @Slf4j
    public class CsvUtils { 
    
    
        /** * 词语分隔符,特殊符号 */
        public static final String WORDS_SPLITTER = "=_,";
    
    
        /** * 导出csv文件 * @param date 数据 * @return */
        public static byte[] exportCSV(List<LinkedHashMap<String, Object>> date) { 
            ByteArrayOutputStream out = new ByteArrayOutputStream();
            BufferedWriter buffCvsWriter = null;
            try { 
                OutputStreamWriter osw = new OutputStreamWriter(out, StandardCharsets.UTF_8);
                osw.write(new String(new byte[] {  (byte) 0xEF, (byte) 0xBB,(byte) 0xBF }));
                buffCvsWriter = new BufferedWriter(osw);
                // 将body数据写入表格
                if(date != null){ 
                    for (Iterator<LinkedHashMap<String, Object>> iterator = date.iterator(); iterator.hasNext();) { 
                        fillDataToCsvDate(buffCvsWriter, iterator.next());
                        if (iterator.hasNext()) { 
                            buffCvsWriter.newLine();
                        }
                    }
                }
                // 刷新缓冲
                buffCvsWriter.flush();
            } catch (IOException e) { 
               log.error("缓存区异常：{}",e);
            } finally { 
                // 释放资源
                if (buffCvsWriter != null) { 
                    try { 
                        buffCvsWriter.close();
                    } catch (IOException e) { 
                        log.error("释放资源失败：{}",e);
                    }
                }
            }
            return out.toByteArray();
        }
    
        public static byte[] exportXlsx(Map<String, List<LinkedHashMap<String, Object>>> tableData) { 
            ByteArrayOutputStream out = new ByteArrayOutputStream();
            try { 
                HSSFWorkbook workbook = new HSSFWorkbook();
                // 创建多个sheet
                for (Map.Entry<String, List<LinkedHashMap<String, Object>>> entry : tableData.entrySet()) { 
                    fillDataToXlsx(workbook.createSheet(entry.getKey()), entry.getValue());
                }
                workbook.write(out);
            } catch (IOException e) { 
               log.error("生成Excel失败：{}",e);
            }
            return out.toByteArray();
        }
    
        /** * 将linkedHashMap中的数据，写入xlsx表格中 * * @param sheet * @param data */
        private static void fillDataToXlsx(HSSFSheet sheet, List<LinkedHashMap<String, Object>> data) { 
            HSSFRow currRow;
            HSSFCell cell;
            LinkedHashMap row;
            Map.Entry propertyEntry;
            int rowIndex = 0;
            int cellIndex = 0;
            for (Iterator<LinkedHashMap<String, Object>> iterator = data.iterator(); iterator.hasNext(); ) { 
                row = iterator.next();
                currRow = sheet.createRow(rowIndex++);
                for (Iterator<Map.Entry> propertyIterator = row.entrySet().iterator(); propertyIterator.hasNext(); ) { 
                    propertyEntry = propertyIterator.next();
                    if (propertyIterator.hasNext()) { 
                        String value = String.valueOf(propertyEntry.getValue());
                        cell = currRow.createCell(cellIndex++);
                        cell.setCellValue(value);
                    } else { 
                        String value = String.valueOf(propertyEntry.getValue());
                        cell = currRow.createCell(cellIndex++);
                        cell.setCellValue(value);
                        break;
                    }
                }
                if (iterator.hasNext()) { 
                    cellIndex = 0;
                }
            }
        }
    
        public static String getValue(String[] item,int index){ 
            if(item.length > index){ 
                String value = item[index];
                return value;
            }
            return "";
        }
    
        private static String getFileSuffix(String originalFilename) { 
            if (org.apache.commons.lang3.StringUtils.isNotBlank(originalFilename)) { 
                int dotIndex = originalFilename.lastIndexOf('.');
                if (dotIndex > -1) { 
                    return originalFilename.substring(dotIndex).trim();
                }
            }
            return "";
        }
    
        private static void fillDataToCsvDate(BufferedWriter buffCvsWriter, LinkedHashMap row) throws IOException { 
            Map.Entry propertyEntry;
            for (Iterator<Map.Entry> propertyIterator = row.entrySet().iterator(); propertyIterator.hasNext();) { 
                propertyEntry = propertyIterator.next();
                buffCvsWriter.write("\"" + propertyEntry.getValue().toString() + "\"");
                if (propertyIterator.hasNext()) { 
                    buffCvsWriter.write(WORDS_SPLITTER);
                }
            }
        }
       
    }

## 三、导出 ##

File file = new File(filePath);
            if(!new File(filePath).exists()){ 
                new File(filePath).mkdirs();
            }
            try (FileOutputStream stream = new FileOutputStream(file)) { 
                LinkedHashMap<String, Object> header = new LinkedHashMap<>();
                header.put("1", "测试1");
                header.put("2", "测试2");
                List<LinkedHashMap<String, Object>> date = new ArrayList<>();
                date.add(header);
                date.addAll(data);
                stream.write(CsvUtils.exportCSV(date));
            } catch (Exception e) { 
                log.error("导出失败",e);
            }

## 四、导入 ##

List<String> resultB = new ArrayList();
            int i = 0;
            try { 
                BufferedReader reader = new BufferedReader(new InputStreamReader(in, StandardCharsets.UTF_8));
                String line = reader.readLine();
                if(line == null){ 
                    return OperationResult.fail("文件头不正确，请按照模板导入！");
                }
                List<String> listA = Arrays.asList(line.split(WORDS_SPLITTER));
                listA.stream().forEach(e-> airyList.add(e.substring(1,e.length()-1)));
                while (true) { 
                    String lines = reader.readLine();
                    if (lines == null) { 
                        break;
                    }
                    // 第一行标题不转换
                    if (i >= 0) { 
                        String[] item = lines.split(WORDS_SPLITTER);
                        String value = getValue(item, 0);
                        if(StringUtils.isEmpty(value) || "\"\"".equals(value)){ 
                            break;
                        }
                        list.add(value);
                    }
                    i++;
                }
            } catch (Exception e) { 
                log.error("解析文件失败：{}, 第{}条数据",e, i);
                return OperationResult.fail("解析文件失败！");
            }