将数据库中的数据读出，写成csv文件存在本地

墨蓝 2021-10-09 10:36 317阅读 0赞

一：需求分析：将数据从数据库查出后封装到本地的csv文件中

二：开发实现

1.导入依赖：

<dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>druid</artifactId>
        <version>1.0.20</version>
    </dependency>

2.编写工具类：

import java.io.BufferedWriter;  
    import java.io.File;  
    import java.io.FileInputStream;  
    import java.io.FileNotFoundException;  
    import java.io.FileOutputStream;  
    import java.io.IOException;  
    import java.io.InputStream;  
    import java.io.OutputStream;  
    import java.io.OutputStreamWriter;  
    import java.net.URLEncoder;  
    import java.util.Iterator;  
    import java.util.LinkedHashMap;  
    import java.util.List;  
    import java.util.Map;  
    
    import javax.servlet.http.HttpServletResponse;
    
    import com.alibaba.druid.util.StringUtils;  
    
    
    public class CSVUtils {
    	   /**
    	    * 功能说明：获取UTF-8编码文本文件开头的BOM签名。
    	    * BOM(Byte Order Mark)，是UTF编码方案里用于标识编码的标准标记。例：接收者收到以EF BB BF开头的字节流，就知道是UTF-8编码。
    	    * @return UTF-8编码文本文件开头的BOM签名
    	    */
    	    public static String getBOM() {
    
    	         byte b[] = {(byte)0xEF, (byte)0xBB, (byte)0xBF};
    	         return new String(b);
    	    }
    	    
    	  /** 
    	   * 生成CVS文件
    	   * @param exportData 
    	   *       源数据List 
    	   * @param map 
    	   *       csv文件的列表头map 
    	   * @param outPutPath 
    	   *       文件路径 
    	   * @param fileName 
    	   *       文件名称 
    	   * @return 
    	   */  
    	  @SuppressWarnings("rawtypes")  
    	  public static 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;  
    	  }  
    	   
    	  /**
    	   *     生成并下载csv文件
    	   * @param response
    	   * @param exportData
    	   * @param map
    	   * @param outPutPath
    	   * @param fileName
    	   * @throws IOException
    	   */
    	  @SuppressWarnings("rawtypes")
    	  public static void exportDataFile(HttpServletResponse response,List exportData, LinkedHashMap map, String outPutPath,String fileName) throws IOException{
    	      File csvFile = null;  
    	        BufferedWriter csvFileOutputStream = null;  
    	        try {  
    	          File file = new File(outPutPath);  
    	          if (!file.exists()) {  
    	            file.mkdirs();  
    	          }  
    	          //定义文件名格式并创建  
    	          csvFile =new File(outPutPath+fileName+".csv");
    	          if(csvFile.exists()){
    	             csvFile.delete(); 
    	          }
    	          csvFile.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();  
    	          }  
    	        }  
    	        
    	        
    	       
    	       
    	        InputStream in = null;  
    	        try {  
    	          in = new FileInputStream(outPutPath+fileName+".csv");  
    	          int len = 0;  
    	          byte[] buffer = new byte[1024];  
    	         
    	          OutputStream out = response.getOutputStream(); 
    	          response.reset(); 
    	           
    	          response.setContentType("application/csv;charset=UTF-8");  
    	          response.setHeader("Content-Disposition","attachment; filename=" + URLEncoder.encode(fileName+".csv", "UTF-8"));  
    	          response.setCharacterEncoding("UTF-8"); 
    	          while ((len = in.read(buffer)) > 0) {  
    	            out.write(new byte[] { (byte) 0xEF, (byte) 0xBB, (byte) 0xBF });  
    	            out.write(buffer, 0, len);  
    	          }
    	          out.close();
    	        } catch (FileNotFoundException e) {  
    	        } finally {  
    	          if (in != null) {  
    	            try {  
    	              in.close();  
    	            } catch (Exception e) {  
    	              throw new RuntimeException(e);  
    	            }  
    	          }  
    	        }
    	       
    	  }
    	  
    	  /** 
    	   * 删除该目录filePath下的所有文件 
    	   * @param filePath 
    	   *      文件目录路径 
    	   */  
    	  public static void deleteFiles(String filePath) {  
    	    File file = new File(filePath);  
    	    if (file.exists()) {  
    	      File[] files = file.listFiles();  
    	      for (int i = 0; i < files.length; i++) {  
    	        if (files[i].isFile()) {  
    	          files[i].delete();  
    	        }  
    	      }  
    	    }  
    	  }  
    	   
    	  /** 
    	   * 删除单个文件 
    	   * @param filePath 
    	   *     文件目录路径 
    	   * @param fileName 
    	   *     文件名称 
    	   */  
    	  public static void deleteFile(String filePath, String fileName) {  
    	    File file = new File(filePath);  
    	    if (file.exists()) {  
    	      File[] files = file.listFiles();  
    	      for (int i = 0; i < files.length; i++) {  
    	        if (files[i].isFile()) {  
    	          if (files[i].getName().equals(fileName)) {  
    	            files[i].delete();  
    	            return;  
    	          }  
    	        }  
    	      }  
    	    }  
    	  }  
    	   
    	    
    
    }

3.编程Controller层

@RequestMapping(value = "/get")
    		public Object getPerson(){
    //			String sql = "select * from test008 where id=?";
    			 String sql = "select * from event_type   ";
    	           
    		      List<Map<String, Object>> queryForList2 = jdbc.queryForList(sql);
    		      List<Map> exportData = new ArrayList<Map>();  
    		      Map<String, String> row1 = new LinkedHashMap<String, String>();  
    			  
    			    
    		      for (Map<String, Object> map : queryForList2) {
    		    	  row1 = new LinkedHashMap<String, String>();  
    		    	  for (String key : map.keySet()) {   //遍历map
    						row1.put(key, map.get(key).toString());
    		}
    		    	  exportData.add(row1);
    		    	   
    			}
    		    LinkedHashMap<String, String> map = new LinkedHashMap<String, String>();  
    
    		    //设置列名
    		    map.put("id", "id");  
    		    map.put("type_name", "type_name");  
    		    map.put("type_code", "type_code");  
    //		    map.put("4", "第四列名称");  
    		    //这个文件上传到路径，可以配置在数据库从数据库读取，这样方便一些！
    		    String path = "E:/qgis/";  
    //		    E:\qgis
    
    		   //文件名=生产的文件名称+时间戳
    		    Date dt = new Date(System.currentTimeMillis());
    		    SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmssSSS");
    		    String   fileName   =   sdf.format(dt);
    		    
    //		    String fileName = "test3000";  
    		    System.out.println("exportData2:"+exportData);
    		    File file = CSVUtils.createCSVFile(exportData, map, path, fileName);  
    		    String fileName2 = file.getName();  
    		    System.out.println("文件名称：" + fileName2);  
    	 
    			return  "文件名称：" +fileName2;
    		}

转换成json的方法

List<Map<String, Object>> queryForList2 = jdbc.queryForList(sql);
    	      String str = JSON.toJSONString(queryForList2);