先创建一个Excel:
package com.kk.jxl;
import java.awt.Color;
import java.io.File;
import java.io.IOException;
import java.util.LinkedHashMap;
import java.util.Map;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import com.kk.DButils.DBData;
import jxl.CellType;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.format.VerticalAlignment;
import jxl.write.Formula;
import jxl.write.Label;
import jxl.write.NumberFormat;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;
/**
* 积分和抽奖.xls
* @author kk
*/
public class ExchangeExcel {
public static WritableWorkbook book = null;
public static WritableSheet sheet = null;
@SuppressWarnings({ "unused", "static-access" })
public static void excel() throws IOException, RowsExceededException, WriteException{
//创建一个Excel文件
book = Workbook.createWorkbook(new File("D://img//积分和抽奖.xls"));
sheet = book.createSheet("积分和抽奖", 0);
//创建字体对象
WritableFont titleFont = new WritableFont(WritableFont.createFont("微软雅黑"), 14, WritableFont.NO_BOLD);
//标题样式
WritableCellFormat titleFormat = new WritableCellFormat(titleFont);
//行样式
WritableCellFormat contentFormat = new WritableCellFormat();
//设置单元格的样式
WritableFont contentFont = new WritableFont(WritableFont.createFont("微软雅黑"), 10, WritableFont.BOLD,false,UnderlineStyle.NO_UNDERLINE);
WritableCellFormat contentFormat2 = new WritableCellFormat(contentFont);
//设置边框
contentFormat2.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN
,jxl.format.Colour.BLACK);
//设置自动换行
contentFormat2.setWrap(true);
//字体加粗
contentFormat.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
// 设置格式居中对齐
titleFormat.setAlignment(jxl.format.Alignment.CENTRE);
contentFormat.setAlignment(jxl.format.Alignment.CENTRE);
contentFormat2.setAlignment(jxl.format.Alignment.CENTRE);
//设置单元格颜色
WritableFont font = new WritableFont(WritableFont.createFont("微软雅黑"), 10, WritableFont.BOLD,false,UnderlineStyle.NO_UNDERLINE);
Color color = Color.decode("#cbccff");
book.setColourRGB(Colour.ORANGE, color.getRed(), color.getGreen(),color.getBlue());
WritableCellFormat wcf = new WritableCellFormat(font);
wcf.setBackground(Colour.ORANGE);
//设置格式居中对齐
wcf.setAlignment(jxl.format.Alignment.CENTRE);
//设置边框
wcf.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN
,jxl.format.Colour.BLACK);
//垂直居中样式
WritableFont verticalfont = new WritableFont(WritableFont.createFont("微软雅黑"), 10, WritableFont.BOLD,false,UnderlineStyle.NO_UNDERLINE);
WritableCellFormat verticalwcf = new WritableCellFormat(verticalfont);
verticalwcf.setVerticalAlignment(VerticalAlignment.CENTRE);
//自动换行
verticalwcf.setWrap(true);
/**合并单元格**/
sheet.mergeCells(0, 0, 13, 0);
sheet.mergeCells(0, 2, 0, 4);
sheet.mergeCells(1, 2, 1, 4);
sheet.mergeCells(0, 5, 0, 7);
sheet.mergeCells(1, 5, 1, 7);
sheet.mergeCells(3, 1, 4, 1);
sheet.mergeCells(3, 2, 4, 2);
sheet.mergeCells(3, 3, 4, 3);
sheet.mergeCells(3, 4, 4, 4);
sheet.mergeCells(3, 5, 4, 5);
sheet.mergeCells(3, 6, 4, 6);
sheet.mergeCells(3, 7, 4, 7);
sheet.mergeCells(5, 1, 6, 1);
sheet.mergeCells(5, 2, 6, 2);
sheet.mergeCells(5, 3, 6, 3);
sheet.mergeCells(5, 4, 6, 4);
sheet.mergeCells(5, 5, 6, 5);
sheet.mergeCells(5, 6, 6, 6);
sheet.mergeCells(5, 7, 6, 7);
sheet.mergeCells(7, 1, 13, 1);
sheet.mergeCells(7, 2, 13, 2);
sheet.mergeCells(7, 3, 13, 3);
sheet.mergeCells(7, 4, 13, 4);
sheet.mergeCells(7, 5, 13, 5);
sheet.mergeCells(7, 6, 13, 6);
sheet.mergeCells(7, 7, 13, 7);
//添加边框
for (int j = 0; j < 8; j++) {
for (int i = 0; i < 14; i++) {
sheet.addCell(new Label(i,j,"",contentFormat2));
}
}
//标题
sheet.addCell(new Label(0, 0,"积分兑奖 ",contentFormat2));
sheet.addCell(new Label(0, 1,"序号",contentFormat2));
sheet.addCell(new Label(1, 1,"应用名称",contentFormat2));
sheet.addCell(new Label(3, 1,"兑换用户数",contentFormat2));
sheet.addCell(new Label(5, 1,"兑换笔数",contentFormat2));
sheet.addCell(new Label(7, 1,"兑换积分数",contentFormat2));
sheet.addCell(new Label(0, 2,"1",contentFormat2));
sheet.addCell(new Label(0, 5,"2",contentFormat2));
sheet.addCell(new Label(1, 2,"积分(duijiang)",contentFormat2));
sheet.addCell(new Label(1, 5,"企业积分(jfdh_qy)",contentFormat2));
sheet.addCell(new Label(2, 2,"本月",contentFormat2));
sheet.addCell(new Label(2, 3,"本年",contentFormat2));
sheet.addCell(new Label(2, 4,"总计",contentFormat2));
sheet.addCell(new Label(2, 5,"本月",contentFormat2));
sheet.addCell(new Label(2, 6,"本年",contentFormat2));
sheet.addCell(new Label(2, 7,"总计",contentFormat2));
//抽奖平台
//添加边框
for (int j = 14; j < 19; j++) {
for (int i = 0; i < 14; i++) {
sheet.addCell(new Label(i,j,"",contentFormat2));
}
}
//合并单元格
sheet.mergeCells(0, 14, 13, 14);
sheet.mergeCells(0, 16, 0, 18);
sheet.mergeCells(2, 15, 4, 15);
sheet.mergeCells(2, 16, 4, 16);
sheet.mergeCells(2, 17, 4, 17);
sheet.mergeCells(2, 18, 4, 18);
sheet.mergeCells(5, 15, 6, 15);
sheet.mergeCells(5, 16, 6, 16);
sheet.mergeCells(5, 17, 6, 17);
sheet.mergeCells(5, 18, 6, 18);
sheet.mergeCells(7, 15, 13, 15);
sheet.mergeCells(7, 16, 13, 16);
sheet.mergeCells(7, 17, 13, 17);
sheet.mergeCells(7, 18, 13, 18);
//标题
sheet.addCell(new Label(0, 14,"抽奖平台",contentFormat2));
sheet.addCell(new Label(0, 15,"序号 ",contentFormat2));
sheet.addCell(new Label(0, 16,"1",contentFormat2));
sheet.addCell(new Label(1, 16,"本月",contentFormat2));
sheet.addCell(new Label(1, 17,"本年",contentFormat2));
sheet.addCell(new Label(1, 18,"总计",contentFormat2));
sheet.addCell(new Label(2, 15,"参与抽奖总人数",contentFormat2));
sheet.addCell(new Label(5, 15,"中奖次数",contentFormat2));
sheet.addCell(new Label(7, 15,"总抽奖次数",contentFormat2));
//交易领奖平台
//添加边框
for (int j = 24; j < 31; j++) {
for (int i = 0; i < 14; i++) {
sheet.addCell(new Label(i,j,"",contentFormat2));
}
}
//合并单元格
sheet.mergeCells(0, 23, 13, 23);
sheet.mergeCells(0, 25, 0, 27);
sheet.mergeCells(0, 28, 0, 30);
sheet.mergeCells(1, 24, 3, 24);
sheet.mergeCells(1, 25, 3, 27);
sheet.mergeCells(1, 28, 3, 30);
sheet.mergeCells(5, 24, 6, 24);
sheet.mergeCells(5, 25, 6, 25);
sheet.mergeCells(5, 26, 6, 26);
sheet.mergeCells(5, 27, 6, 27);
sheet.mergeCells(5, 28, 6, 28);
sheet.mergeCells(5, 29, 6, 29);
sheet.mergeCells(5, 30, 6, 30);
sheet.mergeCells(7, 24, 13, 24);
sheet.mergeCells(7, 25, 13, 25);
sheet.mergeCells(7, 26, 13, 26);
sheet.mergeCells(7, 27, 13, 27);
sheet.mergeCells(7, 28, 13, 28);
sheet.mergeCells(7, 29, 13, 29);
sheet.mergeCells(7, 30, 13, 30);
sheet.addCell(new Label(0, 23,"交易领奖平台",contentFormat2));
sheet.addCell(new Label(0, 24,"序号",contentFormat2));
sheet.addCell(new Label(0, 25,"1",contentFormat2));
sheet.addCell(new Label(0, 28,"2",contentFormat2));
sheet.addCell(new Label(0, 24,"渠道",contentFormat2));
sheet.addCell(new Label(5, 24,"领奖笔数",contentFormat2));
sheet.addCell(new Label(7, 24,"领奖人数",contentFormat2));
sheet.addCell(new Label(1, 25,"网银渠道",contentFormat2));
sheet.addCell(new Label(1, 28,"手机渠道",contentFormat2));
sheet.addCell(new Label(4, 25,"本月",contentFormat2));
sheet.addCell(new Label(4, 26,"本年",contentFormat2));
sheet.addCell(new Label(4, 27,"总计",contentFormat2));
sheet.addCell(new Label(4, 28,"本月",contentFormat2));
sheet.addCell(new Label(4, 29,"本年",contentFormat2));
sheet.addCell(new Label(4, 30,"总计",contentFormat2));
}
}
写个测试
public static void main(String[] args) throws Exception {
test();
}
@SuppressWarnings({ })
public static void test() throws Exception{
excel();
//设置单元格的样式
WritableFont contentFont = new WritableFont(WritableFont.createFont("微软雅黑"), 10, WritableFont.BOLD,false,UnderlineStyle.NO_UNDERLINE);
WritableCellFormat contentFormat2 = new WritableCellFormat(contentFont);
//设置边框
contentFormat2.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN
,jxl.format.Colour.BLACK);
//设置自动换行
contentFormat2.setWrap(true);
//设置水平居中
contentFormat2.setAlignment(jxl.format.Alignment.CENTRE);
sheet.addCell(new Label(2,3,"本月新增",contentFormat2));
Close();
}
public static void Close() throws IOException, WriteException{
//开始执行写入操作
book.write();
//关闭流
book.close();
}
效果如图:
创建一个类,专门写入数据
package com.kk.DButils;
import java.math.BigDecimal;
import java.util.LinkedHashMap;
import java.util.Map;
import jxl.format.UnderlineStyle;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import com.kk.jxl.ExchangeExcel;
/**
* @author KK
* 积分,抽奖,领奖 表
*/
public class ExchangeData {
static ExchangeExcel exchange = new ExchangeExcel();
static DBData data = new DBData();
static Map<String, String[]> Mymap = new LinkedHashMap<String, String[]>();
static final Logger logger = LoggerFactory.getLogger(ExchangeData.class);
@SuppressWarnings({ "static-access", "unused" })
public static void excelWrite(String Years,String Months ) throws Exception{
exchange.excel();
//设置单元格的样式
WritableFont contentFont = new WritableFont(WritableFont.createFont("微软雅黑"), 10, WritableFont.BOLD,false,UnderlineStyle.NO_UNDERLINE);
WritableCellFormat contentFormat2 = new WritableCellFormat(contentFont);
//设置边框
contentFormat2.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN
,jxl.format.Colour.BLACK);
//设置自动换行
contentFormat2.setWrap(true);
//设置水平居中
contentFormat2.setAlignment(jxl.format.Alignment.CENTRE);
//积分本月
String sql = "select `Year`,`Month`,MerchantsNumber,NumberTransactions,AmountMoney from bankandplatform where DepCode = '1000034' and `Year` = '"+Years+"' and `Month` = '"+Months+"' and PlatformName = '2'";
logger.info("sql语句_积分本月:"+sql);
Mymap = data.getData(sql, "mysql");
String AmountMoney = "";
String NumberTransactions = "";
String MerchantsNumber = "";
String Year = "";
String Month = "";
String Channel = "";
for(String[] s : Mymap.values()){
Year = s[0];
Month = s[1];
MerchantsNumber = s[2];
NumberTransactions = s[3];
AmountMoney = s[4];
if(MerchantsNumber.equals("null")){
MerchantsNumber = "0";
}
if(NumberTransactions.equals("null")){
NumberTransactions = "0";
}
if(AmountMoney.equals("null")){
AmountMoney = "0";
}
exchange.sheet.addCell(new Label(3,2,MerchantsNumber,contentFormat2));
exchange.sheet.addCell(new Label(5,2,NumberTransactions,contentFormat2));
exchange.sheet.addCell(new Label(7,2,AmountMoney,contentFormat2));
}
Mymap.clear();
//积分本年
String sql2 = "select `Year`,`Month`,MerchantsNumber,NumberTransactions,AmountMoney from bankandplatform where DepCode = '1000034' and `Year` = '"+Years+"' and `Month` = '"+"00"+"' and PlatformName = '2'";
logger.info("sql语句_积分本年:"+sql2);
Mymap = data.getData(sql2, "mysql");
for(String[] s : Mymap.values()){
Year = s[0];
Month = s[1];
MerchantsNumber = s[2];
NumberTransactions = s[3];
AmountMoney = s[4];
if(MerchantsNumber.equals("null")){
MerchantsNumber = "0";
}
if(NumberTransactions.equals("null")){
NumberTransactions = "0";
}
if(AmountMoney.equals("null")){
AmountMoney = "0";
}
exchange.sheet.addCell(new Label(3,3,MerchantsNumber,contentFormat2));
exchange.sheet.addCell(new Label(5,3,NumberTransactions,contentFormat2));
exchange.sheet.addCell(new Label(7,3,AmountMoney,contentFormat2));
}
Mymap.clear();
//积分历史所有
String sql3 = "select `Year`,`Month`,MerchantsNumber,NumberTransactions,AmountMoney from bankandplatform where DepCode = '1000034' and `Year` = '"+"0000"+"' and `Month` = '"+"00"+"' and PlatformName = '2'";
logger.info("sql语句_积分历史所有:"+sql3);
Mymap = data.getData(sql3, "mysql");
for(String[] s : Mymap.values()){
Year = s[0];
Month = s[1];
MerchantsNumber = s[2];
NumberTransactions = s[3];
AmountMoney = s[4];
if(MerchantsNumber.equals("null")){
MerchantsNumber = "0";
}
if(NumberTransactions.equals("null")){
NumberTransactions = "0";
}
if(AmountMoney.equals("null")){
AmountMoney = "0";
}
exchange.sheet.addCell(new Label(3,4,MerchantsNumber,contentFormat2));
exchange.sheet.addCell(new Label(5,4,NumberTransactions,contentFormat2));
exchange.sheet.addCell(new Label(7,4,AmountMoney,contentFormat2));
}
Mymap.clear();
//企业积分本月
String sql4 = "select `Year`,`Month`,MerchantsNumber,NumberTransactions,AmountMoney from bankandplatform where DepCode = '1000035' and `Year` = '"+Years+"' and `Month` = '"+Months+"' and PlatformName = '2' ";
logger.info("sql语句_企业积分本月:"+sql4);
Mymap = data.getData(sql4, "mysql");
for(String[] s : Mymap.values()){
Year = s[0];
Month = s[1];
MerchantsNumber = s[2];
NumberTransactions = s[3];
AmountMoney = s[4];
if(MerchantsNumber.equals("null")){
MerchantsNumber = "0";
}
if(NumberTransactions.equals("null")){
NumberTransactions = "0";
}
if(AmountMoney.equals("null")){
AmountMoney = "0";
}
exchange.sheet.addCell(new Label(3,5,MerchantsNumber,contentFormat2));
exchange.sheet.addCell(new Label(5,5,NumberTransactions,contentFormat2));
exchange.sheet.addCell(new Label(7,5,AmountMoney,contentFormat2));
}
Mymap.clear();
//企业积分本年
String sql5 = "select `Year`,`Month`,MerchantsNumber,NumberTransactions,AmountMoney from bankandplatform where DepCode = '1000035' and `Year` = '"+Years+"' and `Month` = '"+"00"+"' and PlatformName = '2' ";
logger.info("sql语句_企业积分本年:"+sql5);
Mymap = data.getData(sql5, "mysql");
for(String[] s : Mymap.values()){
Year = s[0];
Month = s[1];
MerchantsNumber = s[2];
NumberTransactions = s[3];
AmountMoney = s[4];
if(MerchantsNumber.equals("null")){
MerchantsNumber = "0";
}
if(NumberTransactions.equals("null")){
NumberTransactions = "0";
}
if(AmountMoney.equals("null")){
AmountMoney = "0";
}
exchange.sheet.addCell(new Label(3,6,MerchantsNumber,contentFormat2));
exchange.sheet.addCell(new Label(5,6,NumberTransactions,contentFormat2));
exchange.sheet.addCell(new Label(7,6,AmountMoney,contentFormat2));
}
Mymap.clear();
//企业积分历史所有
String sql6 = "select `Year`,`Month`,MerchantsNumber,NumberTransactions,AmountMoney from bankandplatform where DepCode = '1000035' and `Year` = '"+"0000"+"' and `Month` = '"+"00"+"' and PlatformName = '2' ";
logger.info("sql语句_企业积分历史所有:"+sql6);
Mymap = data.getData(sql6, "mysql");
for(String[] s : Mymap.values()){
Year = s[0];
Month = s[1];
MerchantsNumber = s[2];
NumberTransactions = s[3];
AmountMoney = s[4];
if(MerchantsNumber.equals("null")){
MerchantsNumber = "0";
}
if(NumberTransactions.equals("null")){
NumberTransactions = "0";
}
if(AmountMoney.equals("null")){
AmountMoney = "0";
}
exchange.sheet.addCell(new Label(3,7,MerchantsNumber,contentFormat2));
exchange.sheet.addCell(new Label(5,7,NumberTransactions,contentFormat2));
exchange.sheet.addCell(new Label(7,7,AmountMoney,contentFormat2));
}
Mymap.clear();
//领奖平台
String ljpt_sql = "select * from costchannel where Type = '领奖平台' and (`Year` = '"+Years+"' and `Month` = '"+Months+"' or `Year` = '"+Years+"' and `Month` = '00' or `Year` = '0000' and `Month` = '00')";
logger.info("sql语句_领奖平台 :"+ljpt_sql);
Mymap = data.getData(ljpt_sql, "mysql");
for(String[] s : Mymap.values()){
System.out.println("map:"+Mymap.size());
Year = s[1];
Month = s[2];
AmountMoney = s[4];
NumberTransactions = s[5];
Channel = s[6];
if(AmountMoney.equals("null")){
AmountMoney = "0";
}
if(NumberTransactions.equals("null")){
NumberTransactions = "0";
}
int Yearint = Integer.parseInt(Year);
int Montint = Integer.parseInt(Month);
if( Channel.equals("网银渠道")){
if(Yearint > 0 && Montint > 0){
exchange.sheet.addCell(new Label(5,25,NumberTransactions,contentFormat2));
exchange.sheet.addCell(new Label(7,25,AmountMoney,contentFormat2));
}
if(Yearint > 0 && Montint == 0){
exchange.sheet.addCell(new Label(5,26,NumberTransactions,contentFormat2));
exchange.sheet.addCell(new Label(7,26,AmountMoney,contentFormat2));
}
if(Yearint == 0 && Montint == 0){
exchange.sheet.addCell(new Label(5,27,NumberTransactions,contentFormat2));
exchange.sheet.addCell(new Label(7,27,AmountMoney,contentFormat2));
}
}
if( Channel.equals("手机渠道")){
if(Yearint > 0 && Montint > 0){
exchange.sheet.addCell(new Label(5,28,NumberTransactions,contentFormat2));
exchange.sheet.addCell(new Label(7,28,AmountMoney,contentFormat2));
}
if(Yearint > 0 && Montint == 0){
exchange.sheet.addCell(new Label(5,29,NumberTransactions,contentFormat2));
exchange.sheet.addCell(new Label(7,29,AmountMoney,contentFormat2));
}
if(Yearint == 0 && Montint == 0){
exchange.sheet.addCell(new Label(5,30,NumberTransactions,contentFormat2));
exchange.sheet.addCell(new Label(7,30,AmountMoney,contentFormat2));
}
}
}
Mymap.clear();
//抽奖平台
String cjpt_sql = "select ID,`Year`,`Month`,MerchantsNumber,NumberTransactions,AmountMoney from bankandplatform where (DepCode = '1000036' or DepCode = '1000037' or DepCode = '1000038') and (`Year` = '"+Years+"' and `Month` = '"+Months+"' or `Year` = '"+Years+"' and `Month` = '00' or `Year` = '0000' and `Month` = '00') ";
logger.info("sql语句_抽奖平台:"+cjpt_sql);
//总抽奖次数
StringBuilder zcjcs = new StringBuilder();
//中奖次数
StringBuilder zjcs = new StringBuilder();
//参与抽奖总人数
StringBuilder cycjzrs = new StringBuilder();
Mymap = data.getData(cjpt_sql, "mysql");
for(String[] s : Mymap.values()){
Year = s[1];
Month = s[2];
MerchantsNumber = s[3];
NumberTransactions = s[4];
AmountMoney = s[5];
//Channel = s[3];
if(MerchantsNumber.equals("null")){
MerchantsNumber = "0";
}
if(NumberTransactions.equals("null")){
NumberTransactions = "0";
}
if(AmountMoney.equals("null")){
AmountMoney = "0";
}
int Yearint = Integer.parseInt(Year);
int Montint = Integer.parseInt(Month);
if(Yearint > 0 && Montint > 0){
zcjcs.append(MerchantsNumber+"@");
}
if(Yearint > 0 && Montint == 0){
zcjcs.append(MerchantsNumber+"@");
}
if(Yearint == 0 && Montint == 0){
zcjcs.append(MerchantsNumber+"@");
}
}
String[] arrZcjcs = zcjcs.toString().split("@");
String gr = arrZcjcs[0];
String gs = arrZcjcs[1];
String xj = arrZcjcs[2];
//总抽奖次数本月
BigDecimal big = new BigDecimal(Integer.parseInt(gr)+Integer.parseInt(gs)+Integer.parseInt(xj));
exchange.sheet.addCell(new Label(2,16,big.toString(),contentFormat2));
String gr_Year = arrZcjcs[3];
String gs_Year = arrZcjcs[4];
String xj_Year = arrZcjcs[5];
//总抽奖次数本年
BigDecimal big_Year = new BigDecimal(Integer.parseInt(gr_Year)+Integer.parseInt(gs_Year)+Integer.parseInt(xj_Year));
exchange.sheet.addCell(new Label(2,17,big_Year.toString(),contentFormat2));
String gr_Total = arrZcjcs[6];
String gs_Total = arrZcjcs[7];
String xj_Total = arrZcjcs[8];
//总抽奖次数历史所有
BigDecimal big_Totalr = new BigDecimal(Integer.parseInt(gr_Total)+Integer.parseInt(gs_Total)+Integer.parseInt(xj_Total));
exchange.sheet.addCell(new Label(2,18,big_Totalr.toString(),contentFormat2));
Mymap.clear();
Mymap = data.getData(cjpt_sql, "mysql");
for(String[] s : Mymap.values()){
Year = s[1];
Month = s[2];
NumberTransactions = s[4];
if(NumberTransactions.equals("null")){
NumberTransactions = "0";
}
int Yearint = Integer.parseInt(Year);
int Montint = Integer.parseInt(Month);
if(Yearint > 0 && Montint > 0){
zjcs.append(NumberTransactions+"@");
}
if(Yearint > 0 && Montint == 0){
zjcs.append(NumberTransactions+"@");
}
if(Yearint == 0 && Montint == 0){
zjcs.append(NumberTransactions+"@");
}
}
//中奖次数本月
String[] arrZjcs = zjcs.toString().split("@");
String gr1 = arrZjcs[0];
String gs1 = arrZjcs[1];
String xj1 = arrZjcs[2];
BigDecimal big1 = new BigDecimal(Integer.parseInt(gr1)+Integer.parseInt(gs1)+Integer.parseInt(xj1));
exchange.sheet.addCell(new Label(5,16,big1.toString(),contentFormat2));
String gr1_Year = arrZjcs[3];
String gs1_Year = arrZjcs[4];
String xj1_Year = arrZjcs[5];
//中奖次数本年
BigDecimal big1_Year = new BigDecimal(Integer.parseInt(gr1_Year)+Integer.parseInt(gs1_Year)+Integer.parseInt(xj1_Year));
exchange.sheet.addCell(new Label(5,17,big1_Year.toString(),contentFormat2));
String gr1_Total = arrZjcs[6];
String gs1_Total = arrZjcs[7];
String xj1_Total = arrZjcs[8];
//中奖次数历史所有
BigDecimal big1_Total = new BigDecimal(Integer.parseInt(gr1_Total)+Integer.parseInt(gs1_Total)+Integer.parseInt(xj1_Total));
exchange.sheet.addCell(new Label(5,18,big1_Total.toString(),contentFormat2));
Mymap.clear();
Mymap = data.getData(cjpt_sql, "mysql");
for(String[] s : Mymap.values()){
Year = s[1];
Month = s[2];
AmountMoney = s[5];
if(AmountMoney.equals("null")){
AmountMoney = "0";
}
int Yearint = Integer.parseInt(Year);
int Montint = Integer.parseInt(Month);
if(Yearint > 0 && Montint > 0){
cycjzrs.append(AmountMoney+"@");
}
if(Yearint > 0 && Montint == 0){
cycjzrs.append(AmountMoney+"@");
}
if(Yearint == 0 && Montint == 0){
cycjzrs.append(AmountMoney+"@");
}
}
//参与抽奖总人数本月
String[] arrCycjzrs = cycjzrs.toString().split("@");
String gr2 = arrCycjzrs[0];
String gs2 = arrCycjzrs[1];
String xj2 = arrCycjzrs[2];
BigDecimal big2 = new BigDecimal(Integer.parseInt(gr2)+Integer.parseInt(gs2)+Integer.parseInt(xj2));
exchange.sheet.addCell(new Label(7,16,big2.toString(),contentFormat2));
String gr2_Year = arrCycjzrs[3];
String gs2_Year = arrCycjzrs[4];
String xj2_Year = arrCycjzrs[5];
//参与抽奖总人数本月
BigDecimal big2_Year = new BigDecimal(Integer.parseInt(gr2_Year)+Integer.parseInt(gs2_Year)+Integer.parseInt(xj2_Year));
exchange.sheet.addCell(new Label(7,17,big2_Year.toString(),contentFormat2));
String gr2_Total = arrCycjzrs[6];
String gs2_Total = arrCycjzrs[7];
String xj2_Total = arrCycjzrs[8];
//参与抽奖总人数本年
BigDecimal big2_Total = new BigDecimal(Integer.parseInt(gr2_Total)+Integer.parseInt(gs2_Total)+Integer.parseInt(xj2_Total));
exchange.sheet.addCell(new Label(7,18,big2_Total.toString(),contentFormat2));
exchange.Close();
}
}
注意:数据写入是从多个数据库查询动态填入的,当然可以优化的点还是有的后面再说