统计各个数据库的各个数据表的总数，然后写入到excel中

1、最近项目基本进入最后阶段了，然后会统计一下各个数据库的各个数据表的数据量，开始使用的报表工具，report-designer，开源的，研究了两天，发现并不是很好使，最后自己下班回去，晚上思考，想着还不如自己做一个，领导下命令，说这个活给你了，你做好给经理就行了。然后就开始不断的做。思路大概如下所示：

第一步，链接各个数据源，由于项目的数据库牵扯到mysql数据库，postgresql数据库，greenplum数据库，然后mysql里面有十几个库，每个库里面有相同的数据表，然后postgresql和greenplum是一个数据库有相同的数据表。由于greenplum集群版性能很好，所以对于大数据量的话，用greenplum进行查询十分方便快捷，也是关系型数据库，和mysql的语法基本性一致。不扯这个了。

第二步，由于使用了maven项目的，所以引入依赖就行了。由于greenplum的jar包，在maven仓库里面没有找到，我就在maven项目的classpath里面引入了公司的包，如下所示：

在.classpath里面，最下面加入这一行，就引入我这个jar包。这个是公司/lib项目里面的jar包，greenplum的依赖回头再找一下。

<classpathentry kind="lib" path="/lib/jdbc/greenplum.jar"/>

 依赖如下所示：

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">
  <modelVersion>4.0.0</modelVersion>
  <groupId>com.charts</groupId>
  <artifactId>com.fline.aic.charts</artifactId>
  <packaging>war</packaging>
  <version>0.0.1-SNAPSHOT</version>
  <name>com.fline.aic.charts Maven Webapp</name>
  <url>http://maven.apache.org</url>
  
  <dependencies>
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>3.8.1</version>
      <scope>test</scope>
    </dependency>
    <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>3.9</version>
    </dependency>
    <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.6</version>
    </dependency>
    <!-- https://mvnrepository.com/artifact/com.pivotal/greenplum-jdbc -->
    <!-- <dependency>
        <groupId>com.pivotal</groupId>
        <artifactId>greenplum-jdbc</artifactId>
        <version>5.1.4</version>
    </dependency> -->
    <!-- https://mvnrepository.com/artifact/org.postgresql/postgresql -->
    <dependency>
        <groupId>org.postgresql</groupId>
        <artifactId>postgresql</artifactId>
        <version>42.1.4</version>
    </dependency>
    <!-- https://mvnrepository.com/artifact/com.alibaba/fastjson -->
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>fastjson</artifactId>
        <version>1.2.47</version>
    </dependency>
    <!-- https://mvnrepository.com/artifact/commons-beanutils/commons-beanutils -->
    <dependency>
        <groupId>commons-beanutils</groupId>
        <artifactId>commons-beanutils</artifactId>
        <version>1.9.3</version>
    </dependency>
    <!-- https://mvnrepository.com/artifact/org.apache.commons/commons-lang3 -->
    <dependency>
        <groupId>org.apache.commons</groupId>
        <artifactId>commons-lang3</artifactId>
        <version>3.4</version>
    </dependency>
    <!-- https://mvnrepository.com/artifact/commons-logging/commons-logging -->
    <dependency>
        <groupId>commons-logging</groupId>
        <artifactId>commons-logging</artifactId>
        <version>1.1.1</version>
    </dependency>
    <!-- https://mvnrepository.com/artifact/commons-collections/commons-collections -->
    <dependency>
        <groupId>commons-collections</groupId>
        <artifactId>commons-collections</artifactId>
        <version>3.2.1</version>
    </dependency>
    <!-- https://mvnrepository.com/artifact/com.mchange/c3p0 -->
    <dependency>
        <groupId>com.mchange</groupId>
        <artifactId>c3p0</artifactId>
        <version>0.9.5.2</version>
    </dependency>
    <!--  -->
    <dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.15</version>
    </dependency>
    <!-- https://mvnrepository.com/artifact/org.json/json -->
    <dependency>
        <groupId>org.json</groupId>
        <artifactId>json</artifactId>
        <version>20160810</version>
    </dependency>
    
  </dependencies>
  <build>
    <finalName>com.fline.aic.charts</finalName>
  </build>
  
  
  
</project>

第三步、我使用了db.properties文件。放到srcmain esources路径下面。然后由于牵扯到公司信息，这里面放了大概23个url连接。

形如如下所示：

#1.db_xxx
db_xxx_driver=com.mysql.jdbc.Driver
db_xxx_url=jdbc:mysql://xxx:3306/db_xxx
db_xxx_user=xxx5 db_xxx_password=xxx
......

第四步，搞一个连接的工具类。大概搞23个这样的东西，重复代码就行了，然后测试一下看看是否能够连接成功。

package com.fline.aic.utils;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ResourceBundle;

/**
 * 
 * @Description TODO
 * @author biehl
 * @Date 2018年9月21日 上午9:32:04
 *
 */
public class JdbcUtils {

      //1
      private static String db_xxx_driver;
      private static String db_xxx_url;
      private static String db_xxx_user;
      private static String db_xxx_password;

      
      // 1
      static {
            db_xxx_driver = ResourceBundle.getBundle("db").getString("db_xxx_driver");
            db_xxx_url = ResourceBundle.getBundle("db").getString("db_xxx_url");
            db_xxx_user = ResourceBundle.getBundle("db").getString("db_xxx_user");
            db_xxx_password = ResourceBundle.getBundle("db").getString("db_xxx_password");
      }
      
      /**
       * 1
       * @return
       * @throws ClassNotFoundException
       * @throws SQLException
       */
      public static Connection getxxxConnection() throws ClassNotFoundException, SQLException {
            // 加载数据库驱动
            Class.forName(db_xxx_driver);
            // System.out.println("测试加载数据库成功");
            Connection con = DriverManager.getConnection(db_xxx_url, db_xxx_user, db_xxx_password);
            // System.out.println("测试数据库链接成功");
            return con;
      }
      
      
      /**
       * 
       * @param con
       * @param ps
       * @param rs
       */
      public static void closeConnection(Connection con, PreparedStatement ps, ResultSet rs) {
            if (rs != null) {// 关闭资源，避免出现异常
                  try {
                        rs.close();
                  } catch (SQLException e) {
                        e.printStackTrace();
                  }
            }
            if (ps != null) {
                  try {
                        ps.close();
                  } catch (SQLException e) {
                        e.printStackTrace();
                  }
            }
            if (con != null) {
                  try {
                        con.close();
                  } catch (SQLException e) {
                        e.printStackTrace();
                  }
            }
      }

      public static void main(String[] args) {
            try {
                  JdbcUtils.getxxxConnection();
                  System.out.println("xxx前置库连接成功.....");
                  System.out.println("=======================================");
                  
            } catch (ClassNotFoundException e) {
                  e.printStackTrace();
            } catch (SQLException e) {
                  e.printStackTrace();
            }
      }

}

第五步、搞一个实体类，简写了这里。

package com.fline.aic.vo;

import java.io.Serializable;

/**
 * 
 * @Description TODO
 * @author biehl
 * @Date 2018年9月21日 上午10:50:47
 *
 */
public class CountEntity implements Serializable {

    /**
     * 
     */
    private static final long serialVersionUID = 1L;
    private Integer sx;// xx
    private Integer bj;// xx
    private Integer yh;// xx
    private Integer zz;// xx

    public Integer getSx() {
        return sx;
    }

    public void setSx(Integer sx) {
        this.sx = sx;
    }

    public Integer getBj() {
        return bj;
    }

    public void setBj(Integer bj) {
        this.bj = bj;
    }

    public Integer getYh() {
        return yh;
    }

    public void setYh(Integer yh) {
        this.yh = yh;
    }

    public Integer getZz() {
        return zz;
    }

    public void setZz(Integer zz) {
        this.zz = zz;
    }

    public CountEntity(Integer sx, Integer bj, Integer yh, Integer zz) {
        super();
        this.sx = sx;
        this.bj = bj;
        this.yh = yh;
        this.zz = zz;
    }

    public CountEntity() {
        super();
    }

    @Override
    public String toString() {
        return "CountEntity [sx=" + sx + ", bj=" + bj + ", yh=" + yh + ", zz=" + zz + "]";
    }

}

第六步、查询一下，统计报表数据量。

package com.fline.aic.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.fline.aic.utils.JdbcUtils;
import com.fline.aic.vo.CountEntity;

/**
 * 
 * @Description TODO
 * @author biehl
 * @Date 2018年9月21日 上午10:33:03
 *
 */
public class QueryDataOfCharts {

      private static QueryDataOfCharts queryDataOfCharts;

      private QueryDataOfCharts() {
      }

      public static QueryDataOfCharts getInstance() {
            if (queryDataOfCharts == null) {
                  queryDataOfCharts = new QueryDataOfCharts();
            }
            return queryDataOfCharts;
      }

      public Connection con = null;
      public PreparedStatement ps = null;
      public ResultSet rs = null;

      /**
       * 1
       * 
       * @return
       */
      public CountEntity queryDbxxx() {
            try {
                  Connection xxxConnection = JdbcUtils.getxxxConnection();
                  String sql = "select
"
                              + "   (sx_directory.sx + sx_general_basic.sx + sx_general_extend.sx + sx_general_material.sx + sx_general_fee_project.sx + sx_general_questions.sx + sx_punish_basic.sx + sx_punish_questions.sx + sx_handle_basic.sx + sx_handle_material.sx + sx_handle_questions.sx + sx_public_basic.sx + sx_public_extend.sx + sx_public_material.sx + sx_public_fee_project.sx + sx_public_questions.sx + sx_check_basic.sx + sx_check_questions.sx + sx_zone_organization.sx) as sx,(bj_pro_accept.bj + bj_pro_process.bj + bj_pro_result.bj + bj_pro_specialprocedure.bj + bj_pro_material.bj) as bj,(yh_uc_province_user.yh + yh_uc_corporator_identity.yh + yh_uc_corporator_account.yh + yh_uc_info_enterprise.yh + yh_uc_info_association.yh + yh_uc_info_central_dept.yh + yh_uc_gov_org.yh + yh_uc_gov_region.yh + yh_uc_gov_staff.yh) as yh,(zz_lic_data.zz) as zz
"
                              + "from 
"
                              + "(select count(1) as sx from up_task_directory) as sx_directory JOIN
"
                              + "(select count(1) as sx from up_task_general_basic) as sx_general_basic ON 1=1 JOIN
"
                              + "(select count(1) as sx from up_task_general_extend) as sx_general_extend ON 1=1 JOIN
"
                              + "(select count(1) as sx from up_task_general_material) as sx_general_material ON 1=1 JOIN
"
                              + "(select count(1) as sx from up_task_general_fee_project) as sx_general_fee_project ON 1=1 JOIN
"
                              + "(select count(1) as sx from up_task_general_questions) as sx_general_questions ON 1=1 JOIN
"
                              + "(select count(1) as sx from up_task_punish_basic) as sx_punish_basic ON 1=1 JOIN
"
                              + "(select count(1) as sx from up_task_punish_questions) as sx_punish_questions ON 1=1 JOIN
"
                              + "(select count(1) as sx from up_task_handle_basic) as sx_handle_basic ON 1=1 JOIN
"
                              + "(select count(1) as sx from up_task_handle_material) as sx_handle_material ON 1=1 JOIN
"
                              + "(select count(1) as sx from up_task_handle_questions) as sx_handle_questions ON 1=1 JOIN
"
                              + "(select count(1) as sx from up_task_public_basic) as sx_public_basic ON 1=1 JOIN
"
                              + "(select count(1) as sx from up_task_public_extend) as sx_public_extend ON 1=1 JOIN
"
                              + "(select count(1) as sx from up_task_public_material) as sx_public_material ON 1=1 JOIN
"
                              + "(select count(1) as sx from up_task_public_fee_project) as sx_public_fee_project ON 1=1 JOIN
"
                              + "(select count(1) as sx from up_task_public_questions) as sx_public_questions ON 1=1 JOIN
"
                              + "(select count(1) as sx from up_task_check_basic) as sx_check_basic ON 1=1 JOIN
"
                              + "(select count(1) as sx from up_task_check_questions) as sx_check_questions ON 1=1 JOIN
"
                              + "(select count(1) as sx from up_zone_organization) as sx_zone_organization ON 1=1 JOIN
"
                              + "(select count(1) as bj from up_pro_accept) as bj_pro_accept ON 1=1 JOIN
"
                              + "(select count(1) as bj from up_pro_process) as bj_pro_process ON 1=1 JOIN
"
                              + "(select count(1) as bj from up_pro_result) as bj_pro_result ON 1=1 JOIN
"
                              + "(select count(1) as bj from up_pro_specialprocedure) as bj_pro_specialprocedure ON 1=1 JOIN
"
                              + "(select count(1) as bj from up_pro_material) as bj_pro_material ON 1=1 JOIN 
"
                              + "(select count(1) as yh from up_uc_province_user) as yh_uc_province_user ON 1=1 JOIN
"
                              + "(select count(1) as yh from up_uc_corporator_identity) as yh_uc_corporator_identity ON 1=1 JOIN
"
                              + "(select count(1) as yh from up_uc_corporator_account) as yh_uc_corporator_account ON 1=1 JOIN
"
                              + "(select count(1) as yh from up_uc_info_enterprise) as yh_uc_info_enterprise ON 1=1 JOIN
"
                              + "(select count(1) as yh from up_uc_info_association) as yh_uc_info_association ON 1=1 JOIN 
"
                              + "(select count(1) as yh from up_uc_info_central_dept) as yh_uc_info_central_dept ON 1=1 JOIN
"
                              + "(select count(1) as yh from up_uc_gov_org) as yh_uc_gov_org ON 1=1 JOIN
"
                              + "(select count(1) as yh from up_uc_gov_region) as yh_uc_gov_region ON 1=1 JOIN
"
                              + "(select count(1) as yh from up_uc_gov_staff) as yh_uc_gov_staff ON 1=1 JOIN
"
                              + "(select count(1) as zz from  up_lic_data) as zz_lic_data ON 1=1";
                  ps = xxxConnection.prepareStatement(sql);
                  rs = ps.executeQuery();
                  CountEntity ce = null;
                  if (rs.next()) {
                        ce = new CountEntity();
                        ce.setSx(rs.getInt("sx"));
                        ce.setBj(rs.getInt("bj"));
                        ce.setYh(rs.getInt("yh"));
                        ce.setZz(rs.getInt("zz"));
                        return ce;
                  } else {
                        return null;
                  }
            } catch (ClassNotFoundException e) {
                  e.printStackTrace();
            } catch (SQLException e) {
                  e.printStackTrace();
            }
            return null;
      }

      public static void main(String[] args) {
            QueryDataOfCharts instance = QueryDataOfCharts.getInstance();
            CountEntity queryDbxxx = instance.queryDbxxx();
            System.out.println(
                        "xxx " + queryDbxxx + "
" + "=========================================================");
            
      }

}

其实巴拉巴拉一大堆，我感觉上面这个大sql才是比较有意思的东西。其实好好看看挺好的，就是把一类的统计相加，然后最后输出到excel里面，还是比较有意思的。

第七步，就是将查询的数据量输出到excel里面就行了：

统计报表就有意思了，将统计的数据量放到list里面，然后将list放到map里面。这样一行的都放到list里面。不同行放到不同的list里面，这样循环遍历输出的时候就可以将不同的放到不同的行里面，完美的解决我的报表统计功能。

package com.fline.aic.excel;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.fline.aic.dao.QueryDataOfCharts;
import com.fline.aic.vo.CountEntity;

/**
 * 
 * @Description TODO
 * @author biehl
 * @Date 2018年9月21日 上午11:37:28
 *
 */
public class WriteExcelForXSSF {

      private static WriteExcelForXSSF writeExcelForXSSF;

      private WriteExcelForXSSF() {
      }

      public static WriteExcelForXSSF getInstance() {
            if (writeExcelForXSSF == null) {
                  writeExcelForXSSF = new WriteExcelForXSSF();
            }
            return writeExcelForXSSF;
      }

      /**
       * 
       */
      public Map<Integer, List<Integer>> readDbAreaOfDabase() {
            // Map集合
            Map<Integer, List<Integer>> map = new HashMap<Integer, List<Integer>>();
            // List集合
            List<Integer> list = new ArrayList<Integer>();
            // 获取到QueryDataOfCharts对象
            QueryDataOfCharts instance = QueryDataOfCharts.getInstance();
            // 查询到xxx的数据
            CountEntity queryDbxxx = instance.queryDbxxx();
            list.add(queryDbxxx.getSx());
            list.add(queryDbxxx.getBj());
            list.add(queryDbxxx.getZz());
            list.add(queryDbxxx.getYh());
            map.put(0,list);            
            return map;
      }

      public void writeDbAreaForXSSF() {
            // 创建一个空的工作簿
            Workbook workbook = new XSSFWorkbook();
            // 创建一个sheet页
            Sheet sheet = workbook.createSheet("xxxxxx报表");

            // 合并单元格
            /*
             * sheet.addMergedRegion(new CellRangeAddress( 2,//第一行（从0开始） 2,//最后一行（从0开始）
             * 0,//第一列（从0开始） 26 //最后一列（从0开始） ));
             */

            // 创建一行,开始是0行,设置第2行
            Row row = sheet.createRow(1);
            // 创建一个单元格，第一列
            // Cell cell = row.createCell(1);
            // 第一行第一列设置值
            // cell.setCellValue("资源共享服务中心数据汇聚统计表");
            // row.createCell(0).setCellValue("资源共享服务中心数据汇聚统计表");

            // 设置字体
            Font font = workbook.createFont();
            font.setFontHeightInPoints((short) 20);
            font.setFontName("Courier New");
            font.setBold(true);

            // 设置数字的字体
            Font font2 = workbook.createFont();
            font2.setFontHeightInPoints((short) 10);
            font2.setFontName("Courier New");
            font2.setBold(true);

            // 设置样式
            CellStyle cs = workbook.createCellStyle();
            cs.setFont(font);

            CellStyle cs2 = workbook.createCellStyle();
            cs2.setFont(font2);

            // 将要设置字体的单元格进行设置
            // 创建一个单元格，第一列
            Cell cell = row.createCell(1);
            // 第一行第一列设置值
            cell.setCellValue("资源共享服务中心数据汇聚统计表");
            cell.setCellStyle(cs);

            // 设置一行
            Row row3 = sheet.createRow(3);
            // 创建一列,第一列设置地方前置库名称
            Cell cell3 = row3.createCell(0);
            // 为这一行这一列设置值
            cell3.setCellValue("xxx");
            cell3.setCellStyle(cs2);

            // 设置一行
            Row row4 = sheet.createRow(4);
            // 创建一列,第一列设置地方前置库名称
            Cell cell4 = row4.createCell(0);
            // 为这一行这一列设置值
            cell4.setCellValue("广东");
            cell4.setCellStyle(cs2);

            // 设置一行
            Row row5 = sheet.createRow(5);
            // 创建一列,第一列设置地方前置库名称
            Cell cell5 = row5.createCell(0);
            // 为这一行这一列设置值
            cell5.setCellValue("江苏");
            cell5.setCellStyle(cs2);

            // 设置一行
            Row row6 = sheet.createRow(6);
            // 创建一列,第一列设置地方前置库名称
            Cell cell6 = row6.createCell(0);
            // 为这一行这一列设置值
            cell6.setCellValue("贵州");
            cell6.setCellStyle(cs2);

            // 设置一行
            Row row7 = sheet.createRow(7);
            // 创建一列,第一列设置地方前置库名称
            Cell cell7 = row7.createCell(0);
            // 为这一行这一列设置值
            cell7.setCellValue("山东");
            cell7.setCellStyle(cs2);

            // 设置一行
            Row row8 = sheet.createRow(8);
            // 创建一列,第一列设置地方前置库名称
            Cell cell8 = row8.createCell(0);
            // 为这一行这一列设置值
            cell8.setCellValue("上海");
            cell8.setCellStyle(cs2);

            // 设置一行
            Row row9 = sheet.createRow(9);
            // 创建一列,第一列设置地方前置库名称
            Cell cell9 = row9.createCell(0);
            // 为这一行这一列设置值
            cell9.setCellValue("安徽");
            cell9.setCellStyle(cs2);

            // 设置一行
            Row row10 = sheet.createRow(10);
            // 创建一列,第一列设置地方前置库名称
            Cell cell10 = row10.createCell(0);
            // 为这一行这一列设置值
            cell10.setCellValue("四川");
            cell10.setCellStyle(cs2);

            // 设置一行
            Row row11 = sheet.createRow(11);
            // 创建一列,第一列设置地方前置库名称
            Cell cell11 = row11.createCell(0);
            // 为这一行这一列设置值
            cell11.setCellValue("重庆");
            cell11.setCellStyle(cs2);
            
            
            Row row12 = sheet.createRow(2);
            // 创建一列,第一列设置地方前置库名称
            Cell cell12 = row12.createCell(1);
            // 为这一行这一列设置值
            cell12.setCellValue("脱敏后中间库");
            cell12.setCellStyle(cs2);
            
            // 创建一列,第一列设置地方前置库名称
            Cell cell13 = row12.createCell(5);
            // 为这一行这一列设置值
            cell13.setCellValue("汇聚数据区");
            cell13.setCellStyle(cs2);

            // 创建一列,第一列设置地方前置库名称
            Cell cell14 = row12.createCell(9);
            // 为这一行这一列设置值
            cell14.setCellValue("汇聚前置库");
            cell14.setCellStyle(cs2);
            
            // 创建一列,第一列设置地方前置库名称
            Cell cell15 = row12.createCell(12);
            // 为这一行这一列设置值
            cell15.setCellValue("应用前置库");
            cell15.setCellStyle(cs2);

            // 创建一列,第一列设置地方前置库名称
            Cell cell16 = row12.createCell(15);
            // 为这一行这一列设置值
            cell16.setCellValue("核心数据区");
            cell16.setCellStyle(cs2);

            // 创建一列,第一列设置地方前置库名称
            Cell cell17 = row12.createCell(19);
            // 为这一行这一列设置值
            cell17.setCellValue("共享前置库");
            cell17.setCellStyle(cs2);
            
            //xxx，xxx，xxx，xxx
            Row row13 = sheet.createRow(3);
            // 创建一列,第一列设置地方前置库名称
            Cell cell18 = row13.createCell(1);
            // 为这一行这一列设置值
            cell18.setCellValue("xxx");
            cell18.setCellStyle(cs2);
            
            Cell cell19 = row13.createCell(2);
            // 为这一行这一列设置值
            cell19.setCellValue("xxx");
            cell19.setCellStyle(cs2);
            
            Cell cell20 = row13.createCell(3);
            // 为这一行这一列设置值
            cell20.setCellValue("xxx");
            cell20.setCellStyle(cs2);
            
            Cell cell21 = row13.createCell(4);
            // 为这一行这一列设置值
            cell21.setCellValue("xxx");
            cell21.setCellStyle(cs2);
            
            Cell cell22 = row13.createCell(5);
            // 为这一行这一列设置值
            cell22.setCellValue("xxx");
            cell22.setCellStyle(cs2);
            
            Cell cell23 = row13.createCell(6);
            // 为这一行这一列设置值
            cell23.setCellValue("xxx");
            cell23.setCellStyle(cs2);

            Cell cell24 = row13.createCell(7);
            // 为这一行这一列设置值
            cell24.setCellValue("xxx");
            cell24.setCellStyle(cs2);
            
            Cell cell25 = row13.createCell(8);
            // 为这一行这一列设置值
            cell25.setCellValue("xxx");
            cell25.setCellStyle(cs2);
            
            Cell cell26 = row13.createCell(9);
            // 为这一行这一列设置值
            cell26.setCellValue("xxx");
            cell26.setCellStyle(cs2);
            
            Cell cell27 = row13.createCell(10);
            // 为这一行这一列设置值
            cell27.setCellValue("xxx");
            cell27.setCellStyle(cs2);
      
            Cell cell28 = row13.createCell(11);
            // 为这一行这一列设置值
            cell28.setCellValue("xxx");
            cell28.setCellStyle(cs2);
            
            Cell cell29 = row13.createCell(12);
            // 为这一行这一列设置值
            cell29.setCellValue("xxx");
            cell29.setCellStyle(cs2);
            
            Cell cell30 = row13.createCell(13);
            // 为这一行这一列设置值
            cell30.setCellValue("xxx");
            cell30.setCellStyle(cs2);
      
            Cell cell31 = row13.createCell(14);
            // 为这一行这一列设置值
            cell31.setCellValue("xxx");
            cell31.setCellStyle(cs2);
      
            
            Cell cell32 = row13.createCell(15);
            // 为这一行这一列设置值
            cell32.setCellValue("xxx");
            cell32.setCellStyle(cs2);
            
            Cell cell33 = row13.createCell(16);
            // 为这一行这一列设置值
            cell33.setCellValue("xxx");
            cell33.setCellStyle(cs2);

            Cell cell34 = row13.createCell(17);
            // 为这一行这一列设置值
            cell34.setCellValue("xxx");
            cell34.setCellStyle(cs2);
            
            Cell cell35 = row13.createCell(18);
            // 为这一行这一列设置值
            cell35.setCellValue("xxx");
            cell35.setCellStyle(cs2);

            
            Cell cell36 = row13.createCell(19);
            // 为这一行这一列设置值
            cell36.setCellValue("xxx");
            cell36.setCellStyle(cs2);
            
            Cell cell37 = row13.createCell(20);
            // 为这一行这一列设置值
            cell37.setCellValue("xxx");
            cell37.setCellStyle(cs2);

            int sxCount = 0;// xxx
            int bjCount = 0;// xxx
            int yhCount = 0;// xxx
            int zzCount = 0;// xxx
            int sumCount = 0;// xxx,xxx,xxx,xxx总计
            // 读取查询的xxx数据库的统计数据
            WriteExcelForXSSF instance = WriteExcelForXSSF.getInstance();
            Map<Integer, List<Integer>> readDbAreaOfDabase = instance.readDbAreaOfDabase();
            for (int i = 0; i < readDbAreaOfDabase.size(); i++) {
                  List<Integer> list = readDbAreaOfDabase.get(i);
                  // 设置一行
                  Row row2 = sheet.createRow(i + 4);
                  for (int j = 0; j < list.size(); j++) {
                        // 创建一列,第二列设置数值
                        Cell cell2 = row2.createCell(j + 1);
                        // 获取这一行一这列的值
                        Integer value = list.get(j);
                       
                        // 为这一行这一列设置值
                        cell2.setCellValue(value);
                        cell2.setCellStyle(cs2);
                        //打印输出合计数量
                        //System.out.println(sxCount + "," + bjCount + "," + yhCount + "," + zzCount);
                  }
            }
            
            
            // 创建输出流
            try {
                  File file = new File("C:\Users\Aiyufei\Desktop\poi.xlsx");
                  if (file.exists()) {
                        file.delete();
                  } else {
                        try {
                              file.createNewFile();
                        } catch (IOException e) {
                              e.printStackTrace();
                        }
                  }
                  FileOutputStream fos = new FileOutputStream(file);
                  System.out.println(file.getName() + " ,excel文件已经成功创建.....");
                  try {
                        // 写入流中，创建此excel
                        workbook.write(fos);
                  } catch (IOException e) {
                        e.printStackTrace();
                  }
                  try {
                        // 关闭流
                        fos.close();
                  } catch (IOException e) {
                        e.printStackTrace();
                  }
            } catch (FileNotFoundException e) {
                  // TODO Auto-generated catch block
                  e.printStackTrace();
            }
      }

      public static void main(String[] args) {
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
            System.out.println("开始时间:" + sdf.format(new Date()));
            WriteExcelForXSSF instance = WriteExcelForXSSF.getInstance();
            instance.writeDbAreaForXSSF();
            System.out.println("结束时间:" + sdf.format(new Date()));
      }

}

 待续.....