JAVA导出CSV文件,统计表格数据,求和的问题

现在很多系统都有导出Excel的功能,但是Excel导出涉及到很多的问题,例如兼容性和效率性能方面的,本人提倡导出CSV文件,CSV是一个文本文件,但是可以被办公软件(WPS,Office)识别的表格文件,好了直接上正题!

方法一:

public  File createCSVFile(List exportData, LinkedHashMap map, String outPutPath, String fileName) {
		File csvFile = null;
		BufferedWriter csvFileOutputStream = null;
		try {
			File file = new File(outPutPath);
			if (!file.exists()) {
				file.mkdirs();
			}
			// 定义文件名格式并创建
			csvFile = new File(outPutPath + fileName + ".csv");
			file.createNewFile();
			// UTF-8使正确读取分隔符","
			// 如果生产文件乱码，windows下用gbk，linux用UTF-8
			csvFileOutputStream = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(csvFile), "UTF-8"),1024);

			// 写入前段字节流，防止乱码
			csvFileOutputStream.write(getBOM());
			// 写入文件头部
			for (Iterator propertyIterator = map.entrySet().iterator(); propertyIterator.hasNext();) {
				java.util.Map.Entry propertyEntry = (java.util.Map.Entry) propertyIterator.next();
				csvFileOutputStream
						.write((String) propertyEntry.getValue() != null ? (String) propertyEntry.getValue() : "");
				if (propertyIterator.hasNext()) {
					csvFileOutputStream.write(",");
				}
			}
			csvFileOutputStream.newLine();
			// 写入文件内容
			for (Iterator iterator = exportData.iterator(); iterator.hasNext();) {
				Object row = (Object) iterator.next();
				for (Iterator propertyIterator = map.entrySet().iterator(); propertyIterator.hasNext();) {
					java.util.Map.Entry propertyEntry = (java.util.Map.Entry) propertyIterator.next();
					String str = row != null ? ((String) ((Map) row).get(propertyEntry.getKey())) : "";

					if (StringUtils.isEmpty(str)) {
						str = "";
					} else {
						str = str.replaceAll("\"", "\"\"");
						if (str.indexOf(",") >= 0) {
							str = "\"" + str + "\"";
						}
					}
					csvFileOutputStream.write(str);
					if (propertyIterator.hasNext()) {
						csvFileOutputStream.write(",");
					}
				}
				if (iterator.hasNext()) {
					csvFileOutputStream.newLine();
				}
			}
			csvFileOutputStream.flush();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				csvFileOutputStream.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
		return csvFile;
	}

如何使用?请看:

private  void excel(List<Map<String, Object>> list) {

			List exportData = new ArrayList<Map>();  
			LinkedHashMap map=new LinkedHashMap<>();
			Map row=null;
			map.put("1", "客户姓名");
			map.put("2", "手机号");
			map.put("3", "性别");
			map.put("4", "出生年月日");
			map.put("5", "交易号");
			map.put("6", "交易渠道");
			map.put("7", "历史申购总额");
			map.put("8", "所剩现值");
			map.put("9", "最后登录时间");		
			
			for (int i = 0; i < list.size(); i++) {
				row = new LinkedHashMap<String, String>();  
				Map<String, Object> obj = list.get(i);
				row.put("1", getString(obj.get("name")));
				row.put("2", getString(obj.get("mobile")));
				row.put("3", getString(obj.get("sex")));
				row.put("4", getString(obj.get("birthday")));
				row.put("5", getString("\t"+obj.get("tradeacco")));//此处避免出现科学计数法
				row.put("6", getString(obj.get("channelno")));
				row.put("7", getString(obj.get("buy")));
				row.put("8", getString(obj.get("money")));
				row.put("9", getString(obj.get("time")));
				exportData.add(row);
			}
			   String path = "E:/";  
			   String fileName = "客户信息表" + System.currentTimeMillis();
			   //文件名=生产的文件名称+时间戳
			
			    File file = createCSVFile(exportData, map, path, fileName);  
			    String fileName2 = file.getName();  
			    System.out.println("文件名称：" + fileName2);  	
			
				
		}

		private static String getString(Object obj) {
			if (obj == null) {
				return null;
			} else {
				return obj.toString();
			}
		}
  public static String getBOM() {

        byte b[] = {(byte)0xEF, (byte)0xBB, (byte)0xBF};
        return new String(b);
   }

这个没有用到org.apache.commons.csv包,是手动拼接的,请看用apache插件的例子

方法二:

	
	/***
	 * 通过Apache工具类将list集合生成CSV文件	
	 * @param list
	 * @throws Exception
	 */
	public void listWriteToCSVLocal(List<Map<String, Object>> list) throws Exception{
		   String path = "E:/";  
		   String fileName = "1.csv";
		   FileOutputStream fos = new FileOutputStream(path+fileName);
	        OutputStreamWriter osw = new OutputStreamWriter(fos, "GBK");

	        CSVFormat csvFormat = CSVFormat.DEFAULT.withHeader( "客户姓名", "手机号", "性别", "出生年月日", "交易号", "交易渠道", "历史申购总额", "所剩现值", "最后登录时间" );
	        CSVPrinter csvPrinter = new CSVPrinter(osw, csvFormat);
	        
	    	for (int i = 0; i < list.size(); i++) {
				Map<String, Object> obj = list.get(i);
				csvPrinter.printRecord
				       (  getString(obj.get("name"))
						 ,getString(obj.get("mobile"))
						 ,getString(obj.get("sex"))
						 ,getString(obj.get("birthday"))
						 ,getString("\t"+obj.get("tradeacco"))
						 ,getString(obj.get("channelno"))
						 ,getString(obj.get("buy"))
						 ,getString(obj.get("money"))
						 ,getString(obj.get("time"))
						 );
			}

	        csvPrinter.flush();
	        csvPrinter.close();
	}
	

如何读取CSV呢?请看:

	/**
	 * 读取csv
	 * @param path
	 * @throws Exception
	 */
	public void ReadCSV(String path) throws Exception {
		File file=new File(path);
		if(!file.exists()){
			file.createNewFile();
		}
        InputStream is = new FileInputStream(path);
        InputStreamReader isr = new InputStreamReader(is, "GBK");
        Reader reader = new BufferedReader(isr);

        CSVParser parser = CSVFormat.EXCEL.withHeader( "name", "mobile", "sex", "birthday", "tradeacco", "channelno", "buy", "money", "time" ).parse(reader);
        List<CSVRecord> list = parser.getRecords();
        BigDecimal buyToatol=new BigDecimal(0.00);
        for (int i=1;i<list.size();i++) {//注意:第一行是表格标题,所以从第二行开始读取
        	Map map=list.get(i).toMap();//转换成Map集合后可以用key读取表格的内容
        	Object buy=map.get("buy");//这里读取金额,读取其它的内容要用不同的key来获取
        	if(buy !=null && !"".equals(buy.toString().trim())){
        		System.out.println("buy="+buy);
        		buyToatol=buyToatol.add(new BigDecimal(buy.toString().trim()));
        	}

        }

        parser.close();
        System.out.println("总申购金额:"+buyToatol);

        parser.close();
    }

我们将json文件转换成list,然后再导出CSV:

   /***
     * 将json文件转换成List集合,然后将list集合转换成CSV文件存储在本地
     */
	 private  void jsonToCSVLocal(){

		 List<Map<String, Object>> list=new ArrayList<>();
		    File file=new File("e://5.json");
	        String jsonString;
			try {
				jsonString = FileUtils.readFileToString(file);

		        com.alibaba.fastjson.JSONArray jsonArray = com.alibaba.fastjson.JSONObject.parseArray(jsonString);

		       for(int i=0;i<jsonArray.size();i++){
		    	   com.alibaba.fastjson.JSONObject jo = (com.alibaba.fastjson.JSONObject) jsonArray.get(i);
		    	  System.out.println(jo);
		    	   list.add(jo);
		    	  
		       }
		       listWriteToCSVLocal(list);
			} catch (Exception e) {
				e.printStackTrace();
			}


	    }