JAVA导出数据到excel

导出成csv格式

大数据量的导出成csv格式分为以下几步：

1.首先引入需要的jar包 一下是我maven的配置方式

1 2 3 4 5 6 7 8 9 10

<dependency>         <groupId>org.mvel</groupId>         <artifactId>mvel2</artifactId>         <version>2.2.8.Final</version>     </dependency>     <dependency>         <groupId>net.sourceforge.javacsv</groupId>         <artifactId>javacsv</artifactId>         <version>2.0</version>     </dependency>

 2.以下是具体的执行代码，我是用的是jdbcTemplate

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36

public class DownloadVehicleRepair extends AbstractJob {       @Autowired     private JdbcTemplate jdbcTemplate;       @Override     protected void executeBusiness(Long aLong) {         System.out.println("开始执行！！！！！！！！！！");         final String fileName = "车辆维修清单.csv";//压缩包里面的文件         final String[] header = {"序号", "第三方机构代码", "机构名称", "分公司", "合作机构", "单位类别", "主品牌", "品牌名称",                 "被投诉", "涉及欺诈", "黑名单", "审核状态", "维护时间", "维护人员代码"};         final String sql = "您需要执行sql”;           jdbcTemplate.execute(new PreparedStatementCreator() {             @Override             public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {                 PreparedStatement pstmt = connection.prepareStatement(sql);                 return pstmt;             }         }, new PreparedStatementCallback<Integer>() {             @Override             public Integer doInPreparedStatement(PreparedStatement preparedStatement) throws SQLException, DataAccessException {                 ResultSet rs = preparedStatement.executeQuery();                 try {                     CsvUtil.writeCsv(RuntimeEnvironmentUtil.getValue(SysConstent.code,SysConstent.path) + "\\VehicleRepairDetail.zip",                             fileName, header, rs);//RuntimeEnvironmentUtil.getValue()是为了获取你导出到服务器的路径                 } catch (Exception e) {                     e.printStackTrace();                 }                 return 0;             }         });         System.out.println("导出完成！！！！！！！！！！！");       } }

 3.以下是帮助类

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82

public class CsvUtil {     // 编码类型     public static final Charset CHARSET = Charset.forName("GBK");       // 分隔符     public static final char DELIMITER = ',';       // 文件后缀     public static final String SUFFIX = ".csv";         public static void writeCsv(OutputStream out, String[] header, ResultSet rs)             throws IOException, SQLException {         CsvWriter writer = null;         try {             writer = new CsvWriter(out, CsvUtil.DELIMITER, CsvUtil.CHARSET);             writeCsv(writer, header, rs);         } finally {             if (writer != null)                 writer.close();         }     }       public static void writeCsv(CsvWriter writer, String[] header, ResultSet rs)             throws IOException, SQLException {         if (header != null)             writer.writeRecord(header);         ResultSetMetaData md = rs.getMetaData();         int columnCount = md.getColumnCount();         while (rs.next()) {             for (int i = 1; i <= columnCount; i++)                 writer.write(rs.getString(i));             writer.endRecord();         }     }       public static void writeCsv(File file, String[] header, ResultSet rs)             throws IOException, SQLException {         BufferedOutputStream out = null;         FileOutputStream fileOutputStream = null;         try {             fileOutputStream = new FileOutputStream(file);             out = new BufferedOutputStream(fileOutputStream);             writeCsv(out, header, rs);         } finally {             if (out != null) {                 out.flush();                 out.close();             }             if (fileOutputStream != null) {                 fileOutputStream.close();             }         }     }       public static void writeCsv(String csvFilePath, String[] header,                                 ResultSet rs) throws IOException, SQLException {         writeCsv(new File(csvFilePath), header, rs);     }       public static void writeCsv(String zipFilePath, String csvName, String[] header, ResultSet rs)             throws IOException, SQLException {         FileOutputStream fos = null;         BufferedOutputStream bos = null;         ZipOutputStream zos = null;         try {             fos = new FileOutputStream(zipFilePath);             bos = new BufferedOutputStream(fos);             zos = new ZipOutputStream(bos);             zos.putNextEntry(new ZipEntry(csvName));             writeCsv(zos, header, rs);         } finally {             StreamUtil.flush(zos);             StreamUtil.close(zos);             //StreamUtil.flush(bos);             StreamUtil.close(bos);             //StreamUtil.flush(fos);             StreamUtil.close(fos);         }     }   }

 

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

public class StreamUtil {     public static void flush(Flushable flushable) {         if (flushable != null) {             try {                 flushable.flush();             } catch (IOException e) {                 e.printStackTrace();             }         }     }     public static void close(Closeable closeable){         if(closeable!=null){             try {                 closeable.close();             } catch (IOException e) {                 e.printStackTrace();             }         }     } }

 4.下面是下载时的action

1 2 3 4 5 6 7 8 9 10

@RequestMapping(value = "/downloadVehicleRepair", method = RequestMethod.POST)    public ResponseEntity<byte[]> download() throws IOException {        String path = RuntimeEnvironmentUtil.getValue(SysConstent.code,SysConstent.path)+"\\VehicleRepairDetail.zip";        File file = new File(path);        HttpHeaders headers = new HttpHeaders();        String fileName = new String("车辆维修清单.zip".getBytes("UTF-8"), "iso-8859-1");//为了解决中文名称乱码问题        headers.setContentDispositionFormData("attachment", fileName);        headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);        return new ResponseEntity<byte[]>(FileUtils.readFileToByteArray(file), headers, HttpStatus.OK);    }