1、最近项目基本进入最后阶段了,然后会统计一下各个数据库的各个数据表的数据量,开始使用的报表工具,report-designer,开源的,研究了两天,发现并不是很好使,最后自己下班回去,晚上思考,想着还不如自己做一个,领导下命令,说这个活给你了,你做好给经理就行了。然后就开始不断的做。思路大概如下所示:
第一步,链接各个数据源,由于项目的数据库牵扯到mysql数据库,postgresql数据库,greenplum数据库,然后mysql里面有十几个库,每个库里面有相同的数据表,然后postgresql和greenplum是一个数据库有相同的数据表。由于greenplum集群版性能很好,所以对于大数据量的话,用greenplum进行查询十分方便快捷,也是关系型数据库,和mysql的语法基本性一致。不扯这个了。
第二步,由于使用了maven项目的,所以引入依赖就行了。由于greenplum的jar包,在maven仓库里面没有找到,我就在maven项目的classpath里面引入了公司的包,如下所示:
在.classpath里面,最下面加入这一行,就引入我这个jar包。这个是公司/lib项目里面的jar包,greenplum的依赖回头再找一下。
1 <classpathentry kind="lib" path="/lib/jdbc/greenplum.jar"/>
依赖如下所示:
1 <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
2 xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">
3 <modelVersion>4.0.0</modelVersion>
4 <groupId>com.charts</groupId>
5 <artifactId>com.fline.aic.charts</artifactId>
6 <packaging>war</packaging>
7 <version>0.0.1-SNAPSHOT</version>
8 <name>com.fline.aic.charts Maven Webapp</name>
9 <url>http://maven.apache.org</url>
10
11 <dependencies>
12 <dependency>
13 <groupId>junit</groupId>
14 <artifactId>junit</artifactId>
15 <version>3.8.1</version>
16 <scope>test</scope>
17 </dependency>
18 <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
19 <dependency>
20 <groupId>org.apache.poi</groupId>
21 <artifactId>poi-ooxml</artifactId>
22 <version>3.9</version>
23 </dependency>
24 <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
25 <dependency>
26 <groupId>mysql</groupId>
27 <artifactId>mysql-connector-java</artifactId>
28 <version>5.1.6</version>
29 </dependency>
30 <!-- https://mvnrepository.com/artifact/com.pivotal/greenplum-jdbc -->
31 <!-- <dependency>
32 <groupId>com.pivotal</groupId>
33 <artifactId>greenplum-jdbc</artifactId>
34 <version>5.1.4</version>
35 </dependency> -->
36 <!-- https://mvnrepository.com/artifact/org.postgresql/postgresql -->
37 <dependency>
38 <groupId>org.postgresql</groupId>
39 <artifactId>postgresql</artifactId>
40 <version>42.1.4</version>
41 </dependency>
42 <!-- https://mvnrepository.com/artifact/com.alibaba/fastjson -->
43 <dependency>
44 <groupId>com.alibaba</groupId>
45 <artifactId>fastjson</artifactId>
46 <version>1.2.47</version>
47 </dependency>
48 <!-- https://mvnrepository.com/artifact/commons-beanutils/commons-beanutils -->
49 <dependency>
50 <groupId>commons-beanutils</groupId>
51 <artifactId>commons-beanutils</artifactId>
52 <version>1.9.3</version>
53 </dependency>
54 <!-- https://mvnrepository.com/artifact/org.apache.commons/commons-lang3 -->
55 <dependency>
56 <groupId>org.apache.commons</groupId>
57 <artifactId>commons-lang3</artifactId>
58 <version>3.4</version>
59 </dependency>
60 <!-- https://mvnrepository.com/artifact/commons-logging/commons-logging -->
61 <dependency>
62 <groupId>commons-logging</groupId>
63 <artifactId>commons-logging</artifactId>
64 <version>1.1.1</version>
65 </dependency>
66 <!-- https://mvnrepository.com/artifact/commons-collections/commons-collections -->
67 <dependency>
68 <groupId>commons-collections</groupId>
69 <artifactId>commons-collections</artifactId>
70 <version>3.2.1</version>
71 </dependency>
72 <!-- https://mvnrepository.com/artifact/com.mchange/c3p0 -->
73 <dependency>
74 <groupId>com.mchange</groupId>
75 <artifactId>c3p0</artifactId>
76 <version>0.9.5.2</version>
77 </dependency>
78 <!-- -->
79 <dependency>
80 <groupId>org.apache.poi</groupId>
81 <artifactId>poi-ooxml</artifactId>
82 <version>3.15</version>
83 </dependency>
84 <!-- https://mvnrepository.com/artifact/org.json/json -->
85 <dependency>
86 <groupId>org.json</groupId>
87 <artifactId>json</artifactId>
88 <version>20160810</version>
89 </dependency>
90
91 </dependencies>
92 <build>
93 <finalName>com.fline.aic.charts</finalName>
94 </build>
95
96
97
98 </project>
第三步、我使用了db.properties文件。放到src\main\resources路径下面。然后由于牵扯到公司信息,这里面放了大概23个url连接。
形如如下所示:
1 #1.db_xxx
2 db_xxx_driver=com.mysql.jdbc.Driver
3 db_xxx_url=jdbc:mysql://xxx:3306/db_xxx
4 db_xxx_user=xxx5 db_xxx_password=xxx
......
第四步,搞一个连接的工具类。大概搞23个这样的东西,重复代码就行了,然后测试一下看看是否能够连接成功。
1 package com.fline.aic.utils;
2
3 import java.sql.Connection;
4 import java.sql.DriverManager;
5 import java.sql.PreparedStatement;
6 import java.sql.ResultSet;
7 import java.sql.SQLException;
8 import java.util.ResourceBundle;
9
10 /**
11 *
12 * @Description TODO
13 * @author biehl
14 * @Date 2018年9月21日 上午9:32:04
15 *
16 */
17 public class JdbcUtils {
18
19 //1
20 private static String db_xxx_driver;
21 private static String db_xxx_url;
22 private static String db_xxx_user;
23 private static String db_xxx_password;
24
25
26 // 1
27 static {
28 db_xxx_driver = ResourceBundle.getBundle("db").getString("db_xxx_driver");
29 db_xxx_url = ResourceBundle.getBundle("db").getString("db_xxx_url");
30 db_xxx_user = ResourceBundle.getBundle("db").getString("db_xxx_user");
31 db_xxx_password = ResourceBundle.getBundle("db").getString("db_xxx_password");
32 }
33
34 /**
35 * 1
36 * @return
37 * @throws ClassNotFoundException
38 * @throws SQLException
39 */
40 public static Connection getxxxConnection() throws ClassNotFoundException, SQLException {
41 // 加载数据库驱动
42 Class.forName(db_xxx_driver);
43 // System.out.println("测试加载数据库成功");
44 Connection con = DriverManager.getConnection(db_xxx_url, db_xxx_user, db_xxx_password);
45 // System.out.println("测试数据库链接成功");
46 return con;
47 }
48
49
50 /**
51 *
52 * @param con
53 * @param ps
54 * @param rs
55 */
56 public static void closeConnection(Connection con, PreparedStatement ps, ResultSet rs) {
57 if (rs != null) {// 关闭资源,避免出现异常
58 try {
59 rs.close();
60 } catch (SQLException e) {
61 e.printStackTrace();
62 }
63 }
64 if (ps != null) {
65 try {
66 ps.close();
67 } catch (SQLException e) {
68 e.printStackTrace();
69 }
70 }
71 if (con != null) {
72 try {
73 con.close();
74 } catch (SQLException e) {
75 e.printStackTrace();
76 }
77 }
78 }
79
80 public static void main(String[] args) {
81 try {
82 JdbcUtils.getxxxConnection();
83 System.out.println("xxx前置库连接成功.....");
84 System.out.println("=======================================");
85
86 } catch (ClassNotFoundException e) {
87 e.printStackTrace();
88 } catch (SQLException e) {
89 e.printStackTrace();
90 }
91 }
92
93 }
第五步、搞一个实体类,简写了这里。
1 package com.fline.aic.vo;
2
3 import java.io.Serializable;
4
5 /**
6 *
7 * @Description TODO
8 * @author biehl
9 * @Date 2018年9月21日 上午10:50:47
10 *
11 */
12 public class CountEntity implements Serializable {
13
14 /**
15 *
16 */
17 private static final long serialVersionUID = 1L;
18 private Integer sx;// xx
19 private Integer bj;// xx
20 private Integer yh;// xx
21 private Integer zz;// xx
22
23 public Integer getSx() {
24 return sx;
25 }
26
27 public void setSx(Integer sx) {
28 this.sx = sx;
29 }
30
31 public Integer getBj() {
32 return bj;
33 }
34
35 public void setBj(Integer bj) {
36 this.bj = bj;
37 }
38
39 public Integer getYh() {
40 return yh;
41 }
42
43 public void setYh(Integer yh) {
44 this.yh = yh;
45 }
46
47 public Integer getZz() {
48 return zz;
49 }
50
51 public void setZz(Integer zz) {
52 this.zz = zz;
53 }
54
55 public CountEntity(Integer sx, Integer bj, Integer yh, Integer zz) {
56 super();
57 this.sx = sx;
58 this.bj = bj;
59 this.yh = yh;
60 this.zz = zz;
61 }
62
63 public CountEntity() {
64 super();
65 }
66
67 @Override
68 public String toString() {
69 return "CountEntity [sx=" + sx + ", bj=" + bj + ", yh=" + yh + ", zz=" + zz + "]";
70 }
71
72 }
第六步、查询一下,统计报表数据量。
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\r\n"
+ " (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\r\n"
+ "from \r\n"
+ "(select count(1) as sx from up_task_directory) as sx_directory JOIN\r\n"
+ "(select count(1) as sx from up_task_general_basic) as sx_general_basic ON 1=1 JOIN\r\n"
+ "(select count(1) as sx from up_task_general_extend) as sx_general_extend ON 1=1 JOIN\r\n"
+ "(select count(1) as sx from up_task_general_material) as sx_general_material ON 1=1 JOIN\r\n"
+ "(select count(1) as sx from up_task_general_fee_project) as sx_general_fee_project ON 1=1 JOIN\r\n"
+ "(select count(1) as sx from up_task_general_questions) as sx_general_questions ON 1=1 JOIN\r\n"
+ "(select count(1) as sx from up_task_punish_basic) as sx_punish_basic ON 1=1 JOIN\r\n"
+ "(select count(1) as sx from up_task_punish_questions) as sx_punish_questions ON 1=1 JOIN\r\n"
+ "(select count(1) as sx from up_task_handle_basic) as sx_handle_basic ON 1=1 JOIN\r\n"
+ "(select count(1) as sx from up_task_handle_material) as sx_handle_material ON 1=1 JOIN\r\n"
+ "(select count(1) as sx from up_task_handle_questions) as sx_handle_questions ON 1=1 JOIN\r\n"
+ "(select count(1) as sx from up_task_public_basic) as sx_public_basic ON 1=1 JOIN\r\n"
+ "(select count(1) as sx from up_task_public_extend) as sx_public_extend ON 1=1 JOIN\r\n"
+ "(select count(1) as sx from up_task_public_material) as sx_public_material ON 1=1 JOIN\r\n"
+ "(select count(1) as sx from up_task_public_fee_project) as sx_public_fee_project ON 1=1 JOIN\r\n"
+ "(select count(1) as sx from up_task_public_questions) as sx_public_questions ON 1=1 JOIN\r\n"
+ "(select count(1) as sx from up_task_check_basic) as sx_check_basic ON 1=1 JOIN\r\n"
+ "(select count(1) as sx from up_task_check_questions) as sx_check_questions ON 1=1 JOIN\r\n"
+ "(select count(1) as sx from up_zone_organization) as sx_zone_organization ON 1=1 JOIN\r\n"
+ "(select count(1) as bj from up_pro_accept) as bj_pro_accept ON 1=1 JOIN\r\n"
+ "(select count(1) as bj from up_pro_process) as bj_pro_process ON 1=1 JOIN\r\n"
+ "(select count(1) as bj from up_pro_result) as bj_pro_result ON 1=1 JOIN\r\n"
+ "(select count(1) as bj from up_pro_specialprocedure) as bj_pro_specialprocedure ON 1=1 JOIN\r\n"
+ "(select count(1) as bj from up_pro_material) as bj_pro_material ON 1=1 JOIN \r\n"
+ "(select count(1) as yh from up_uc_province_user) as yh_uc_province_user ON 1=1 JOIN\r\n"
+ "(select count(1) as yh from up_uc_corporator_identity) as yh_uc_corporator_identity ON 1=1 JOIN\r\n"
+ "(select count(1) as yh from up_uc_corporator_account) as yh_uc_corporator_account ON 1=1 JOIN\r\n"
+ "(select count(1) as yh from up_uc_info_enterprise) as yh_uc_info_enterprise ON 1=1 JOIN\r\n"
+ "(select count(1) as yh from up_uc_info_association) as yh_uc_info_association ON 1=1 JOIN \r\n"
+ "(select count(1) as yh from up_uc_info_central_dept) as yh_uc_info_central_dept ON 1=1 JOIN\r\n"
+ "(select count(1) as yh from up_uc_gov_org) as yh_uc_gov_org ON 1=1 JOIN\r\n"
+ "(select count(1) as yh from up_uc_gov_region) as yh_uc_gov_region ON 1=1 JOIN\r\n"
+ "(select count(1) as yh from up_uc_gov_staff) as yh_uc_gov_staff ON 1=1 JOIN\r\n"
+ "(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 + "\n" + "=========================================================");
}
}
其实巴拉巴拉一大堆,我感觉上面这个大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()));
}
}
待续.....