JAVA实现EXCEL方法一 流的方式

    在传统行业中，很多需求在我们看来似乎是一下伪需求，但是我们很多必须要开发。

    比喻EXCEL，必须要导出来，必须要和他们要求一致。

    大量数据导入

    根据SQL语句生成路劲的文件。

  

   /**

 * 根据sql生成zip文件
 * 
 * @param sql
* @param size
* @param reportName
* @return
* @throws Exception
 */
public List<String> export(String sql, Integer size, String reportName,
      String basePath) throws Exception {
   if (StringUtils.isBlank(sql)) {
      throw new Exception(sql + "is null");
   }
   BossPreparedStatementCreator bossPreparedStatementCreator = new BossPreparedStatementCreator();
   bossPreparedStatementCreator.setSql(sql);
   BossRowCallbackHandler bossRowCallbackHandler = new BossRowCallbackHandler();
   bossRowCallbackHandler.setSize(size);
   bossRowCallbackHandler.setReportName(reportName);
   bossRowCallbackHandler.setBasePath(basePath);
   // 这里为了处理没有数据返回为空的情况
BossResultSetExtractor<ResultSetMetaData> resultSetExtractor = new BossResultSetExtractor<ResultSetMetaData>(
         bossRowCallbackHandler);
   dao.queryDate(bossPreparedStatementCreator, resultSetExtractor);
   List<String> fileNames = bossRowCallbackHandler.getFiles();
   if (bossRowCallbackHandler.getOut() != null) {
      bossRowCallbackHandler.getOut().flush();
      bossRowCallbackHandler.getOut().close();
   }
   if (fileNames.isEmpty()) {
      BossRowCallbackHandler bossRowCallbackHandler1 = new BossRowCallbackHandler();
      bossRowCallbackHandler1.setSize(size);
      bossRowCallbackHandler1.setReportName(reportName);
      bossRowCallbackHandler1.setBasePath(basePath);
      bossRowCallbackHandler1.setSbColumn(resultSetExtractor
            .getSbColumn());
      bossRowCallbackHandler1.createWriter(1);
      if (null != bossRowCallbackHandler1.getOut()) {
         bossRowCallbackHandler1.getOut().flush();
         bossRowCallbackHandler1.getOut().close();
      }
      fileNames = bossRowCallbackHandler1.getFiles();
   }
   return fileNames;
}

public class BossPreparedStatementCreator implements PreparedStatementCreator {
   private String sql;

   public PreparedStatement createPreparedStatement(Connection conn)
         throws SQLException {
      /*System.out.println(conn.getMetaData().getURL()+"--"+conn.getMetaData().getUserName());*/
return conn.prepareCall(sql);
   /* return conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE,
            ResultSet.CONCUR_READ_ONLY);*/
}

   public String getSql() {
      return sql;
   }

   public void setSql(String sql) {
      this.sql = sql;
   }
}

public class BossRowCallbackHandler implements RowCallbackHandler {
   private Integer size;

   private OutputStream out;
   // 字段str
private String sbColumn;
   // 字段列表
private List<ColumnData> columnList = new ArrayList<ColumnData>();
   // 每个csv最大数量
   // 报表名称
private String reportName;
   // 基本路径
private String basePath;
   // 文件列表
private List<String> files = new ArrayList<String>();
   // 文件后缀
private String FileType = ".csv";

   @Override
public void processRow(ResultSet rs) throws SQLException {

      if (null == columnList || columnList.isEmpty()) {
         createColumnList(rs.getMetaData());
      }
      if (null == columnList || columnList.isEmpty())
         return;

      try {
         createWriter(rs.getRow());
      } catch (IOException e) {
         throw new SQLException(e);
      }

      try {
         StringBuffer sbData = new StringBuffer();
         String columnName=null;
         for (ColumnData data : columnList) {
             columnName = data.getColumnName();
            // 字段类型
String dataType = data.getDataType();
            if (null != sbData && 0 != sbData.length()) {
               sbData.append(",");
            }
            Object columnValue = rs.getObject(columnName) == null ? "" : rs
                  .getObject(columnName).toString();
            getValueByType(sbData,dataType, columnValue);
            columnValue=null;
         }
         sbData.append("\r\n");
         FileUtil.writeToCsv(sbData.toString(), out);
         sbData=null;
      } catch (Exception e) {
         throw new SQLException(e);
      }

   /* if (rs.isLast()) {
         try {
            if (null != out)
               out.close();
         } catch (IOException e) {
            throw new SQLException(e);
         }
      }*/
}

   /**
    * 得到数据
    * 
    * @param data
* @return
*/
void getValueByType(StringBuffer sb, String dataType, Object columnValue) {
      // 数字不进行处理
if ("NUMBER".equalsIgnoreCase(dataType)
            || "BINARY_FLOAT".equalsIgnoreCase(dataType)
            || "BINARY_DOUBLE".equalsIgnoreCase(dataType)) {
         sb.append(columnValue);
      } else {
         sb.append("\"\t").append(columnValue).append("\t\"");
      }
   }

   /**
    * 得到字段名称
    * 
    * @param rsmd
* @throws SQLException
    */
public void createColumnList(ResultSetMetaData rsmd) throws SQLException {

      if (rsmd != null) {
         StringBuffer sb = new StringBuffer();
         int count = rsmd.getColumnCount();

         for (int i = 1; i <= count; i++) {
            ColumnData columnData = new ColumnData();
            String columnName = rsmd.getColumnName(i);
            String dataType = rsmd.getColumnTypeName(i);
            columnData.setColumnName(columnName);
            columnData.setDataType(dataType);
            columnList.add(columnData);
            if (null != sb && 0 != sb.length()) {
               sb.append(",");

            }
            sb.append(columnName).append("\t");
         }
         if (null != sb && 0 != sb.length()) {
            sb.append("\r\n");

         }
         this.sbColumn = sb.toString();
      }

   }

   /**
    * 创建新的file
    * 
    * @param rowNum
* @throws IOException
    */
public void createWriter(int rowNum) throws IOException {
      if (rowNum % size == 1) {
         if (out != null) {
            out.flush();
            out.close();
         }
         File file = null;
         String fileNameNew = basePath + File.separator + reportName + "_"
+ (rowNum / size) + FileType;
         file = FileUtil.createCsvFile(fileNameNew);
         out = new BufferedOutputStream(new FileOutputStream(file, true),
               1024 * 1024);
         files.add(fileNameNew);
         FileUtil.writeToCsv(getSbColumn(), out);
      }
   }

   public List<ColumnData> getColumnList() {
      return columnList;
   }

   public void setColumnList(List<ColumnData> columnList) {
      this.columnList = columnList;
   }

   public Integer getSize() {
      return size;
   }

   public void setSize(Integer size) {
      this.size = size;
   }

   public String getReportName() {
      return reportName;
   }

   public void setReportName(String reportName) {
      this.reportName = reportName;
   }

   public List<String> getFiles() {
      return files;
   }

   public void setFiles(List<String> files) {
      this.files = files;
   }

   public String getFileType() {
      return FileType;
   }

   public void setFileType(String fileType) {
      FileType = fileType;
   }

   public String getSbColumn() {
      return sbColumn;
   }

   public void setSbColumn(String sbColumn) {

      this.sbColumn = sbColumn;

   }

   public String getBasePath() {
      return basePath;
   }

   public void setBasePath(String basePath) {
      this.basePath = basePath;
   }

   public OutputStream getOut() {
      return out;
   }

   public void setOut(OutputStream out) {
      this.out = out;
   }
}

public class BossResultSetExtractor<T> implements ResultSetExtractor {
   RowCallbackHandler rch;

   public BossResultSetExtractor(RowCallbackHandler rch) {
      this.rch = rch;
   }

   private String sbColumn;

   /*
    * public ResultSetMetaData extractData(ResultSet rs) throws SQLException {
    * 
    * while (rs.next()) { this.rch.processRow(rs); } rsmd = rs.getMetaData();
    * 
    * return rsmd; }
    */
public ResultSetMetaData extractData(ResultSet rs) throws SQLException {

      while (rs.next()) {
         this.rch.processRow(rs);
      }
      ResultSetMetaData rsmd = rs.getMetaData();
      StringBuffer sb = new StringBuffer();
      int count = rsmd.getColumnCount();

      for (int i = 1; i <= count; i++) {
         String columnName = rsmd.getColumnName(i);
         if (null != sb && 0 != sb.length()) {
            sb.append(",");

         }
         sb.append(columnName).append("\t");
         sbColumn = sb.toString();
      }
      return rsmd;
   }

   public String getSbColumn() {
      return sbColumn;
   }

   public void setSbColumn(String sbColumn) {
      this.sbColumn = sbColumn;
   }

}

public interface Dao {

   public List<String> queryPermissionCode(String sql);

   /**
    * 查询sql，生成文件
    * 
    * @param preparedStatementCreator
* @param rowCallbackHandler
* @throws Exception
    */
void queryDate(PreparedStatementCreator preparedStatementCreator,
         RowCallbackHandler rowCallbackHandler) throws Exception;

   /**
    * 如果返回数据为空处理
    * 
    * @param preparedStatementCreator
* @param resultSetExtractor
* @return
* @throws Exception
    */
public ResultSetMetaData queryDate(
         PreparedStatementCreator preparedStatementCreator,
         ResultSetExtractor<ResultSetMetaData> resultSetExtractor)
         throws Exception;
}

@Repository("dao")
public class DaoImpl implements Dao {
   @Autowired
private JdbcTemplate jdbcTemplate;

   public List<String> queryPermissionCode(String sql) {
      List<String> list = jdbcTemplate.queryForList(sql, String.class);
      return list;
   }

   public void queryDate(PreparedStatementCreator preparedStatementCreator,
         RowCallbackHandler rowCallbackHandler) throws Exception {
      /*
       * System.err.println(jdbcTemplate.getDataSource().getConnection()
       * .getMetaData().getURL());
       */
jdbcTemplate.query(preparedStatementCreator, rowCallbackHandler);
   }

   /**
    * 如果返回数据为空处理
    * 
    * @param preparedStatementCreator
* @param resultSetExtractor
* @return
* @throws Exception
    */
public ResultSetMetaData queryDate(
         PreparedStatementCreator preparedStatementCreator,
         ResultSetExtractor<ResultSetMetaData> resultSetExtractor)
         throws Exception {
      /*
       * System.err.println(jdbcTemplate.getDataSource().getConnection()
       * .getMetaData().getURL());
       */
return jdbcTemplate.query(preparedStatementCreator, resultSetExtractor);
   }
}