精华内容
下载资源
问答
  • 导入数据源excel自动生成报表
    2021-04-17 02:23:40

    JAVA,POI导出EXCEL表,表中所有数据都是从后台直...

    举个例子吧

    public class CreateSimpleExcelToDisk

    {

    /**

    * @功能:手工构建一个简单格式的Excel

    */

    public static List getStudent(String[] str) throws Exception

    {

    List listts = new ArrayList();

    SimpleDateFormat df = new SimpleDateFormat("yyyy-mm-dd");

    Connection conn = DBUtil.getCon();

    String sqlback="";

    for(int i=0;i

    sqlback=sqlback "\"id\"='" str[i] "'or";

    }

    sqlback=sqlback "\"id\"='" str[str.length-1] "'";

    String sql1 = "select * from 表名 where" sqlback;

    System.out.println(sql1 );

    ResultSet rs = DBUtil.getResult(conn, sql1);

    String outStr = "";

    int a=1;

    try {

    while(rs.next()){

    String objid = rs.getString(1);

    String tsname= rs.getString("tsname");

    String tstel= rs.getString("tstel");

    String tscpname= rs.getString("tscpname");

    String tsads=rs.getString("tsads");

    String tsqus=rs.getString("tsqus");

    String tsno=rs.getString("tsno");

    String tsdate=rs.getString("tsdate");

    Tsxx user = new Tsxx(a,tsname,tstel,tscpname,tsads,tsqus,tsno,df.parse(tsdate));

    listts.add(user);

    a ;

    }

    } catch (SQLException e) {

    e.printStackTrace();

    }finally {

    try {

    if (rs != null) {

    rs.close();

    }

    if (conn != null) {

    conn.close();

    }

    } catch (Exception e) {

    }}

    return listts;

    }

    public static void main(String[] args) throws Exception

    {

    // // 第一步,创建一个webbook,对应一个Excel文件

    // HSSFWorkbook wb = new HSSFWorkbook();

    // // 第二步,在webbook中添加一个sheet,对应Excel文件中的sheet

    // HSSFSheet sheet = wb.createSheet("学生表一");

    // // 第三步,在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short

    // HSSFRow row = sheet.createRow((int) 0);

    // // 第四步,创建单元格,并设置值表头 设置表头居中

    // HSSFCellStyle style = wb.createCellStyle();

    // style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式

    //

    // HSSFCell cell = row.createCell((short) 0);

    // cell.setCellValue("学号");

    // cell.setCellStyle(style);

    // cell = row.createCell((short) 1);

    // cell.setCellValue("姓名");

    // cell.setCellStyle(style);

    // cell = row.createCell((short) 2);

    // cell.setCellValue("年龄");

    // cell.setCellStyle(style);

    // cell = row.createCell((short) 3);

    // cell.setCellValue("生日");

    // cell.setCellStyle(style);

    //

    // // 第五步,写入实体数据 实际应用中这些数据从数据库得到,

    // List list = CreateSimpleExcelToDisk.getStudent();

    //

    // for (int i = 0; i < list.size(); i )

    // {

    // row = sheet.createRow((int) i 1);

    // Tsxx stu = (Tsxx) list.get(i);

    // // 第四步,创建单元格,并设置值

    // row.createCell((short) 0).setCellValue((double) stu.getId());

    // row.createCell((short) 1).setCellValue(stu.getName());

    // row.createCell((short) 2).setCellValue((double) stu.getAge());

    // cell = row.createCell((short) 3);

    // cell.setCellValue(new SimpleDateFormat("yyyy-mm-dd").format(stu.getBirth()));

    // }

    // // 第六步,将文件存到指定位置

    // try

    // {

    // FileOutputStream fout = new FileOutputStream("E:/students.xls");

    // wb.write(fout);

    // fout.close();

    // }

    // catch (Exception e)

    // {

    // e.printStackTrace();

    // }

    }

    Java怎样将数据库中数据导出为Excel文件,求完整例...

    举个例子吧

    public class CreateSimpleExcelToDisk

    {

    /**

    * @功能:手工构建一个简单格式的Excel

    */

    public static List getStudent(String[] str) throws Exception

    {

    List listts = new ArrayList();

    SimpleDateFormat df = new SimpleDateFormat("yyyy-mm-dd");

    Connection conn = DBUtil.getCon();

    String sqlback="";

    for(int i=0;i

    sqlback=sqlback "\"id\"='" str[i] "'or";

    }

    sqlback=sqlback "\"id\"='" str[str.length-1] "'";

    String sql1 = "select * from 表名 where" sqlback;

    System.out.println(sql1 );

    ResultSet rs = DBUtil.getResult(conn, sql1);

    String outStr = "";

    int a=1;

    try {

    while(rs.next()){

    String objid = rs.getString(1);

    String tsname= rs.getString("tsname");

    String tstel= rs.getString("tstel");

    String tscpname= rs.getString("tscpname");

    String tsads=rs.getString("tsads");

    String tsqus=rs.getString("tsqus");

    String tsno=rs.getString("tsno");

    String tsdate=rs.getString("tsdate");

    Tsxx user = new Tsxx(a,tsname,tstel,tscpname,tsads,tsqus,tsno,df.parse(tsdate));

    listts.add(user);

    a ;

    }

    } catch (SQLException e) {

    e.printStackTrace();

    }finally {

    try {

    if (rs != null) {

    rs.close();

    }

    if (conn != null) {

    conn.close();

    }

    } catch (Exception e) {

    }}

    return listts;

    }

    public static void main(String[] args) throws Exception

    {

    // // 第一步,创建一个webbook,对应一个Excel文件

    // HSSFWorkbook wb = new HSSFWorkbook();

    // // 第二步,在webbook中添加一个sheet,对应Excel文件中的sheet

    // HSSFSheet sheet = wb.createSheet("学生表一");

    // // 第三步,在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short

    // HSSFRow row = sheet.createRow((int) 0);

    // // 第四步,创建单元格,并设置值表头 设置表头居中

    // HSSFCellStyle style = wb.createCellStyle();

    // style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式

    //

    // HSSFCell cell = row.createCell((short) 0);

    // cell.setCellValue("学号");

    // cell.setCellStyle(style);

    // cell = row.createCell((short) 1);

    // cell.setCellValue("姓名");

    // cell.setCellStyle(style);

    // cell = row.createCell((short) 2);

    // cell.setCellValue("年龄");

    // cell.setCellStyle(style);

    // cell = row.createCell((short) 3);

    // cell.setCellValue("生日");

    // cell.setCellStyle(style);

    //

    // // 第五步,写入实体数据 实际应用中这些数据从数据库得到,

    // List list = CreateSimpleExcelToDisk.getStudent();

    //

    // for (int i = 0; i < list.size(); i )

    // {

    // row = sheet.createRow((int) i 1);

    // Tsxx stu = (Tsxx) list.get(i);

    // // 第四步,创建单元格,并设置值

    // row.createCell((short) 0).setCellValue((double) stu.getId());

    // row.createCell((short) 1).setCellValue(stu.getName());

    // row.createCell((short) 2).setCellValue((double) stu.getAge());

    // cell = row.createCell((short) 3);

    // cell.setCellValue(new SimpleDateFormat("yyyy-mm-dd").format(stu.getBirth()));

    // }

    // // 第六步,将文件存到指定位置

    // try

    // {

    // FileOutputStream fout = new FileOutputStream("E:/students.xls");

    // wb.write(fout);

    // fout.close();

    // }

    // catch (Exception e)

    // {

    // e.printStackTrace();

    // }

    }

    利用java怎么实现生成报表(Excel文件)

    前几天刚好因为工作需要做过相应的excel处理程序编写,这里有一个例子,注释也很详细,具体参见doexecl\src\util\ExeclUtil.java中的writeExcel方法,其中的写出文件的保存路径采用的是配置文件的形式,你也可以直接写死,具体的内容你就看代码吧

    更多相关内容
  • Excel自动数据报表制作

    千次阅读 2022-04-20 17:02:29
    Excel动态数据报表制作

    一、基本概念

    1.表格组成:工作簿-工作表-行/列-单元格
    2.功能区
    (空闲时可逐一进行了解)鼠标悬停在功能区某一按钮上即会出现该功能注释
    功能区

    二、基础操作

    • 源数据备份
      拿到一份数据之后,第一步操作进行备份,以免对数据造成不可还原的修改。
      操作:选中源数据工作表-【右键】选择【移动或复制工作表】-选中【移至最后】并勾选【创建副本】-选中副本-【右键】【重命名】为“源数据备份”
      创建副本

    三、理解数据

    1.数据量级

    • 选中整行:整个数据一共24列
      在这里插入图片描述

    • 选中整列:整个数据一共562行,除去表头数据实际一共521行
      在这里插入图片描述

    2.数据类型

    注:该报表使用的数据为线上外卖门店的数据

    • 日期:指的是一家外卖门店当天所有营业额所归属的日期;
    • 品牌ID:相当于品牌身份证号
    • 品牌名称:门店所在品牌名称
      Ctrl+Shift+L快速筛选,可以快速了解门店品牌数、排序、筛选等;
    • 门店ID:相当于门店的身份证号
    • 门店名称
    • 城市
    • 平台:平台的拼音格式
    • 平台i:平台的中文格式
    • 平台门店名称
      注:同样一家店,存在不一样的格式(如:拌客干拌麻辣烫(武宁路店)→拌客干拌麻辣烫(武宁路店)→拌客·干拌麻辣烫(武宁路店),以增加空格、点的形式区分店名),原因是该门店存在关店重开的情况,每次关店重开后会更换平台门店名称以此与之前的门店做区分,更方便的收集数据。
    • GMV:全称为GrossMerchandiseVolume即商品交易总额,是一段时间内的成交总额的意思,多用于电商行业,一般包含拍下未支付订单金额(未刨除各项补贴的表面金额)。
    • 商家实收:去除平台补贴、商家补贴、红包、满减、配送费、平台抽成等等后,当天实际可以进入银行卡/平台账户收入里的金额
    • 门店曝光量:指的是该门店在外卖平台被用户看到的次数
    • 门店访问量:用户进入该门店的次数
    • 门店下单量
    • 无效当订单
    • 有效订单
    • 曝光人数:对应去重的曝光量
    • 进店人数:对应去重的访问量
    • 下单人数:对应去重的下单量
    • cpc总费用:CPC(Cost Per Click)每产生一次点击所花费的成本。在这个报表中cpc总费用指的是当天广告投放的总花费
    • cpc曝光量:广告投放为门店带来的曝光量
    • cpc访问量:广告投放为门店带来的访问量
    • 商户补贴:商家补贴金额
    • 平台补贴:平台补贴金额

    3.数据含义

    环比

    环比=(本期数/上期数)/上期数=本期数/上期数-1
    

    1)年环比

    2020年环比=(2020年数据-2019年数据)/2020年数据=2020年数据/2019年数据-1
    

    2)月环比

    2020年7月环比=2020年7月数据/2020年6月数据-1
    

    3)日环比

    2020年7月1日环比=2020年7月1日数据/2020年6月30日数据-1
    

    同比

    同比=(本期数-同期数)/本期数=本期数/同期数-1
    

    1)月同比

    2020年7月同比=2020年7月数据/2019年7月数据-1
    

    2)日同比

    2020年7月1日的月同比=2020年7月1日数据/2020年6月1日数据-1
    2020年7月1日的周同比=2020年7月1日数据/2020年6月24日数据-1
    

    四、常用函数

    1.sum:可以对整行、整列、一个区域进行求和

    2.sumif:单条件求和

    sumif(range,criteria,[sum_range])
    sumif(条件判断所在的区域,条件,[用来求和的数值区域])
    

    3.sumifs:多条件求和

    sumifs(sum_range,[criteria_range1],[criteria1],[criteria_range2],[criteria2],..)
    sumifs(用来求和的数值区域,条件1判断所在的区域1,条件1,条件2判断所在的区域1,条件2,...)
    

    例:求整月美团平台的GMV

    =sumifs(GMV列,平台列,"美团",日期列,">="&每月第一天,日期列,"<="&每月最后一天)
    

    注:条件参数直接引用单元格或者使用函数不需要英文双引号,若使用如"美团"这样的字符串做条件参数,需要加英文双引号;以及大于等于号也需要添加英文双引号,并且使用&才能与后面的条件值相连。

    4.subtotal:根据筛选求和、平均等等(11个函数,以数字区分,9为sum函数)

    subtotal(function_num,ref1,[ref2],...)
    subtotal(指定函数,选择区域1,[选择区域2],...)
    

    例如:

    subtotal(9,GMV列)=sum(GMV列) 仅当全选时该等式成立。
    

    5.Year:提取日期的年,以数字格式显示

    YEAR(serial_number) YEAR(日期)
    

    6.Month:提取日期的月

    MONTH(serial_number) MONTH(日期)
    

    7.DAY:提取日期的天

    DAY(serial_number) DAY(日期)
    

    8.DATE:组合年月日

    DATE(year,month,day) DATE(代表年份的数值,代表月份的数值,代表日份的数值)
    

    与YEAR()、MONTH()、DAY()组合,可用来求得上一年、上月、上周、昨天、下一年、下个月、下周、明天等对应日期。例如:

    每个月的第一天=DATE(year(日期),month(日期),1);
    每个月的最后一天=下个月的第一天-1=DATE(year(日期),month(日期)+1,1) -1
    

    注:不要使用excel里的日期格式存储日期,建议使用字符串形式存储日期,否则将表格导入数据库会出现其他问题

    8.if:逻辑判断

    if(logical_test,value_if_true,[value_if_false])
    if(逻辑比较条件,结果成立时返回的值,[结果不成立时返回的值])
    [value_if_false]:该参数选填,没有该参数时,返回False
    

    例:利用if函数嵌套,判断A、B是否为0

    if(A=0,if(B=0,"AB都为0","A等于0,B不等于0"),if(B=0,"A不等于0,B等于0","AB都不等于0"))
    
    TRUE
    TRUE
    FALSE
    FALSE
    TRUE
    FALSE
    输入
    A=0
    B=0
    AB都等于
    结束
    A等于 B不等于
    B=0
    A不等于 B等于
    AB都不等于

    9.vlookup:连接匹配数据

    VLOOKUP(lookup_value,table_array,col_index,[range_lookup])
    VLOOKUP(要查找的数据,要查找的位置和要返回的数据的区域,要返回的数据在区域中的列号,返回近似匹配或精确匹配-1/true或0/false)
    
    • 要查找的位置必须在区域的第一列,否则会报错
    • VLOOKUP只会返回它查找到的第一个值
    • VLOOKUP的模糊匹配非通常的模糊匹配,使用VLOOPUP模糊匹配文本类型数据时可以配合通配符使用。
    通配符:
    *:代替不定数量的字符
    ?:(英文输入状态下)代替一个字符
    

    配合通配符进行模糊匹配(第三个参数为0):

    返回以A开头的数据对应的第二列的值=VLOOKUP("A*",区域,2,0)
    返回以A开头的三个字符对应的第二列的值=VLOOKUP("A??",区域,2,0)
    

    10.match:查找数值在区域中的位置

    MATCH(lookup_value,lookup_array,[match_type])
    MATCH(查找项,查找区域,0)
    

    例:返回A在第几行

    =MATCH("A",A所在列,0)
    

    返回A在第几列

    =MATCH("A",A所在行,0)
    

    11.index:根据区域的位置返回数值

    INDEX(array,row_num,column_num)
    INDEX(区域,行号,列号)
    

    12.match、index一起使用:自动根据列名查找数据
    制作报表时,可以利用match函数在源数据中找到报表表头对应的数据列号或行号,结合Index函数找到对应的列/行进行后续计算。
    例:图一为源数据表,图二为数据报表,根据表头信息在源数据中找到对应数据
    源数据表
    在这里插入图片描述

    过表头查找【蛙小辣·美蛙火锅杯(宝山店)】即【B112】单元格对应的门店ID:

    • 因为要查找的数据在源数据表中,所以INDEX函数第一个区域参数为源数据所有列,即
    '源数据表'!A:X    (!表示非本工作表)
    
    • 【蛙小辣·美蛙火锅杯(宝山店)】的门店ID与该门店的平台店名称在同一行,所以查找门店ID的行号等同于查找【蛙小辣·美蛙火锅杯(宝山店)】在源数据表的行号。【蛙小辣·美蛙火锅杯(宝山店)】在【平台店名称】列,即I列,使用MATCH定位行号,即
    MATCH(B112,'源数据表'!I:I,0)
    
    • 【蛙小辣·美蛙火锅杯(宝山店)】门店ID的列,即查找【门店ID】即【D111】单元格在源数据的列号,【门店ID】在第一行,使用MATCH定位列号,即
    MATCH(D111,'源数据表'!1:1,0)
    
    • 完整函数格式为:
    =INDEX('源数据表'!A:X,MATCH(B112,'源数据表'!I:I,0),MATCH(D111,'源数据表'!1:1,0))
    

    编写好单个函数后,希望通过下拉、右拉可以自动查找对应的其他值。但是,下拉后会出现部分匹配不到数据的情况。

    因为下拉的同时,单元格的选中区域【蛙小辣·美蛙火锅杯(宝山店)】、【门店ID】等会依次向下移动,因此需要固定部分选中区域,让我们在下拉、右拉的同时保持不变。使用美元符号"$"进行锁定操作。

    1)源数据表各区域固定,均需锁住;
    2)[B112]需根据下拉向下拉匹配其他平台门店名称数据,同时右拉时固定该列,因此只需锁定B列,即$B112;
    3)[D111]需根据右拉匹配其他表头数据,同时下拉时固定该行,即 D$111
    
    • 完整函数格式为:
    =INDEX('源数据表'!$A:$X,MATCH($B112,'源数据表'!$I:$I,0),MATCH(D$111,'源数据表'!$1:$1,0))
    

    13.sumifs、match、index顶级用法:自动根据列名的数据和条件进行求和
    INDEX函数中,当行号为0时,返回一整列,当列号为0时返回一整行,因此通过MATCH返回查找值在源数据表中时第几行/列,再通过INDEX定位整列区域,结合sumifs函数进行条件筛选求和计算。
    GMV、进店人数、下单人数均用sumifs、match、index结合的方式计算。
    在这里插入图片描述
    求GMV

    • 利用INDEX、MATCH函数取得求和区域GMV列、取得条件判断区域平台店名称列:
    GMV列=INDEX('源数据表'!$A:$X,0,MATCH(H$111,'源数据表'!$1:$1,0))
    平台店名称列=INDEX('源数据表'!$A:$X,0,MATCH($B$111,'源数据表'!$1:$1,0))
    
    • 组合sumifs函数求出平台门店对应GMV总和
    =SUMIFS(INDEX('源数据表'!$A:$X,0,MATCH(H$111,'源数据表'!$1:$1,0)),INDEX('源数据表'!$A:$X,0,MATCH($B$111,'源数据表'!$1:$1,0)),$B112)
    
    • 通过向下拉动、向右拉动可自动得出门店下单人数总和、下单人数总和
      在这里插入图片描述

    五、自动化报表开发

    1.报表框架

    1)整体分布

    一共分为四个部分:标题、小看板(含目标)、结果指标、过程指标
    报表框架

    2)时间信息

    ①日期
    日期只需填写一个,其他日期均再次日期的基础上引用,可以极大提升效率,更改一个日期可以联动更新整个工作表的数据。
    过程指标中的日期也是通过引用该日期实现。
    日期
    ②星期
    同样引用上一个日期,更改格式即可
    星期
    ③数据时间
    同样进行日期的引用
    数据时间

    2.指标梳理

    1)结果指标

    与商家收入的相关指数
    GMV、商家实收、到手率(商家实收/GMV)、有效订单、无效订单、客单价(GMV/有效订单)

    2)过程指标

    商家经营情况的直观体现
    曝光人数、进店人数、进店转化率(进店人数/曝光人数)、下单人数、下单转化率(下单人数/进店人数)、营销占比(cpc总费用/GMV)

    3.联动筛选

    1)下拉筛选器
    在小看板中目标位置,设置了一个可供切换平台的筛选器,实际上它是一个条件验证,其他函数通过判断当前所选平台来计算对应数据。因此需要先设置该筛选器,后续判断一律引用该筛选器单元格,实现切换平台联动更新数据表。
    在这里插入图片描述
    对应操作:【数据】→【数据验证or数据有效性】→选择【序列】→输入【全部,美团,饿了么】注意使用英文逗号→【确定】
    ![下拉筛选器](https://img-blog.csdnimg.cn/07e70af6f317471aaebd4f3b77a255f6.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5oiR5Y-v5piv5bCP5LuZ5aWz,size_20,color_FFFFFF,t_70,g_se,x_16

    4.条件求和

    1)if、sumif、sumifs函数嵌套逻辑

    求和GMV指标,需判断当前平台以及日期筛选。
    if函数判断当前平台是全部、美团还是饿了么,当平台为全部时,无需进行平台筛选,使用sumif函数按日期筛选GMV求和即可;当平台为美团或饿了么时,需使用sumifs函数同时进行平台筛选及日期筛选GMV求和。

    true
    false
    平台=全部
    sumif 筛选日期求和
    sumifs 筛选平台及日期求和

    2)条件求和函数实现

    if(平台=全部,sumif(日期列,日期,GMV列),sumifs(GMV列,日期列,日期,平台列,平台))
    

    为了实现最大的灵活程度,使用index根据表头查找对应源数据列,只编写一此,通过向下向右拖拽即可自动填充其他求和项。

    • 日期列
    INDEX('源数据'!$A:$X,0,MATCH($A$12,'源数据表'!$1:$1,0))
    
    • GMV列(求和列)
    INDEX('源数据表'!$A:$X,0,MATCH(C$12,'源数据表'!$1:$1,0))
    
    • 平台列
    INDEX('源数据表'!$A:$X,0,MATCH("平台i",'源数据表'!$1:$1,0))
    
    • 完整函数实现
    =IF($H$5="全部",SUMIF(INDEX('源数据'!$A:$X,0,MATCH($A$12,'源数据表'!$1:$1,0)),$A13,INDEX('源数据表'!$A:$X,0,MATCH(C$12,'源数据表'!$1:$1,0))),SUMIFS(INDEX('源数据表'!$A:$X,0,MATCH(C$12,'源数据表'!$1:$1,0)),INDEX('源数据表'!$A:$X,0,MATCH($A$12,'源数据表'!$1:$1,0)),$A13,INDEX('源数据表'!$A:$X,0,MATCH("平台i",'源数据表'!$1:$1,0)),$H$5))
    

    将该函数应用到过程指标中的【曝光人数】,只需将求和列中的GMV单元格修改为曝光人数对应的单元格即可,其他不变。

    INDEX('源数据表'!$A:$X,0,MATCH(C$24,'源数据表'!$1:$1,0))
    
    • 【曝光人数】完整函数实现
    =IF($H$5="全部",SUMIF(INDEX('源数据表'!$A:$X,0,MATCH($A$24,'源数据表'!$1:$1,0)),$A25,INDEX('源数据表'!$A:$X,0,MATCH(C$24,'源数据表'!$1:$1,0))),SUMIFS(INDEX('源数据表'!$A:$X,0,MATCH(C$24,'源数据表'!$1:$1,0)),INDEX('源数据表'!$A:$X,0,MATCH($A$24,'源数据表'!$1:$1,0)),$A25,INDEX('源数据表'!$A:$X,0,MATCH("平台i",'源数据表'!$1:$1,0)),$H$5))
    
    • 商家实收、有效订单、无效订单均可通过GMV指标右拉实现数据自动填充;
    • 到手率=商家实收/GMV客单价=GMV/有效订单,这两个指标进行简单除法运算即可;
    • 进店人数、下单人数均可通过曝光人数指标右拉实现数据自动填充;
    • 进店转化率=进店人数/曝光人数下单转化率=下单人数/进店人数,这两个指标同样进行简单除法运算即可;
    • 营销占比=cpc总费用/GMV。但是当前数据报表中没有展示cpc总费用的数据,需要额外进行计算,计算方式同求GMV的函数实现完全一致,只需将GMV单元格C12修改为"cpc总费用"(注意中文字符串需加上英文格式双引号)即可,再引用结果指标中已经计算好的GMV数据即可。完整函数实现如下:
    =IF($H$5="全部",SUMIF(INDEX('源数据表'!$A:$X,0,MATCH($A$12,'源数据表'!$1:$1,0)),$A13,INDEX('源数据表'!$A:$X,0,MATCH("cpc总费用",'源数据表'!$1:$1,0))),SUMIFS(INDEX('源数据表'!$A:$X,0,MATCH("cpc总费用",'源数据表'!$1:$1,0)),INDEX('源数据表'!$A:$X,0,MATCH($A$12,'源数据表'!$1:$1,0)),$A13,INDEX('源数据表'!$A:$X,0,MATCH("平台i",'源数据表'!$1:$1,0)),$H$5))/C31
    

    3)总计计算

    • GMV、商家实收、有效订单、无效订单指标的总计,使用sum函数计算即可;
    • 总计的到手率、客单价依旧使用简单除法计算;
    • 曝光人数、进店人数、下单人数指标的总计,同样使用sum函数计算即可;
    • 总计的进店转化率、下单转化率依旧使用简单除法计算;
    • 总计的营销占比=一周的cpc总费用总计/一周的GMV总计。GMV总计可以从结果指标中引用C20单元格,cpc总费用总计需额外进行计算。计算一周的cpc总费用,只需要再计算一天cpc总费用函数的基础上,将单个的日期筛选修改为两个日期的大小判断,大于等于当周的第一天,小于等于当周的最后一天,通过引用日期列的开始日期单元格A13、结束日期单元格A19即可。逻辑如下
    if(平台=全部,sumifs(cpc总费用列,日期列,">="&开始日期,日期列,"<="&结束日期),sumifs(cpc总费用列,日期列,">="&开始日期,日期列,"<="&结束日期,平台列,平台))
    
    • cpc总费用总计完整函数实现
    IF($H$5="全部",SUMIFS(INDEX('源数据表'!$A:$X,0,MATCH("cpc总费用",'源数据表'!$1:$1,0)),INDEX('源数据表'!$A:$X,0,MATCH($A$12,'源数据表'!$1:$1,0)),">="&$A13,INDEX('源数据表'!$A:$X,0,MATCH($A$12,'源数据表'!$1:$1,0)),"<="&$A19),SUMIFS(INDEX('源数据表'!$A:$X,0,MATCH("cpc总费用",'源数据表'!$1:$1,0)),INDEX('源数据表'!$A:$X,0,MATCH($A$12,'源数据表'!$1:$1,0)),"<="&$A13,INDEX('源数据表'!$A:$X,0,MATCH($A$12,'源数据表'!$1:$1,0)),"<="&$A19,INDEX('源数据表'!$A:$X,0,MATCH("平台i",'源数据表'!$1:$1,0)),$H$5))
    
    • 总计的营销占比完整函数实现
    =IF($H$5="全部",SUMIFS(INDEX('源数据表'!$A:$X,0,MATCH("cpc总费用",'源数据表'!$1:$1,0)),INDEX('源数据表'!$A:$X,0,MATCH($A$12,'源数据表'!$1:$1,0)),">="&$A13,INDEX('源数据表'!$A:$X,0,MATCH($A$12,'源数据表'!$1:$1,0)),"<="&$A19),SUMIFS(INDEX('源数据表'!$A:$X,0,MATCH("cpc总费用",'源数据表'!$1:$1,0)),INDEX('源数据表'!$A:$X,0,MATCH($A$12,'源数据表'!$1:$1,0)),"<="&$A13,INDEX('源数据表'!$A:$X,0,MATCH($A$12,'源数据表'!$1:$1,0)),"<="&$A19,INDEX('源数据表'!$A:$X,0,MATCH("平台i",'源数据表'!$1:$1,0)),$H$5))/C20
    

    5.迷你图

    小看板中周累计的曝光人数、进店转化率、下单转化率直接引用过程指标数据即可。
    插入迷你图操作
    【选中整周数据】→菜单【插入】→功能区【折线】→选中迷你图放置区域→【确定】→勾选【标记】
    插入迷你图
    勾选标记

    6.同比/环比计算

    周环比=本周数据/上周数据-1

    小看板中周环比的本周有效订单、商家实收、到手率直接引用过程指标数据即可。
    上周的数据可以通过日期偏移7天来求得,可以利用计算cpc总费用的总计来进行修改,逻辑如下:

    if(平台=全部,sumifs(有效订单列,日期列,">="&开始日期-7,日期列,"<="&结束日期-7),sumifs(有效订单列,日期列,">="&开始日期-7,日期列-7,"<="&结束日期,平台列,平台))
    
    • 上周的有效订单(F12单元格)完整函数实现
    IF($H$5="全部",SUMIFS(INDEX('源数据表'!$A:$X,0,MATCH(F$12,'源数据表'!$1:$1,0)),INDEX('源数据表'!$A:$X,0,MATCH($A$12,'源数据表'!$1:$1,0)),">="&A13-7,INDEX('源数据表'!$A:$X,0,MATCH($A$12,'源数据表'!$1:$1,0)),"<="&$A$19-7),SUMIFS(INDEX('源数据表'!$A:$X,0,MATCH(F$12,'源数据表'!$1:$1,0)),INDEX('源数据表'!$A:$X,0,MATCH($A$12,'源数据表'!$1:$1,0)),">="&A13-7,INDEX('源数据表'!$A:$X,0,MATCH($A$12,'源数据表'!$1:$1,0)),"<="&$A$19-7,INDEX('源数据表'!$A:$X,0,MATCH("平台i",'源数据表'!$1:$1,0)),$H$5))
    
    • 周环比有效订单完整函数实现(本周有效订单单元格A9):
    =A9/IF($H$5="全部",SUMIFS(INDEX('源数据表'!$A:$X,0,MATCH(F$12,'源数据表'!$1:$1,0)),INDEX('源数据表'!$A:$X,0,MATCH($A$12,'源数据表'!$1:$1,0)),">="&A13-7,INDEX('源数据表'!$A:$X,0,MATCH($A$12,'源数据表'!$1:$1,0)),"<="&$A$19-7),SUMIFS(INDEX('源数据表'!$A:$X,0,MATCH(F$12,'源数据表'!$1:$1,0)),INDEX('源数据表'!$A:$X,0,MATCH($A$12,'源数据表'!$1:$1,0)),">="&A13-7,INDEX('源数据表'!$A:$X,0,MATCH($A$12,'源数据表'!$1:$1,0)),"<="&$A$19-7,INDEX('源数据表'!$A:$X,0,MATCH("平台i",'源数据表'!$1:$1,0)),$H$5))-1
    
    • 周环比商家实收的函数实现与周环比有效订单的函数实现逻辑一致,仅需把函数中的有效订单单元格A9改为商家实收单元格D12即可,完整函数实现如下(本周商家实收单元格C9):
    =C9/IF($H$5="全部",SUMIFS(INDEX('源数据表'!$A:$X,0,MATCH($D$12,'源数据表'!$1:$1,0)),INDEX('源数据表'!$A:$X,0,MATCH($A$12,'源数据表'!$1:$1,0)),">="&A13-7,INDEX('源数据表'!$A:$X,0,MATCH($A$12,'源数据表'!$1:$1,0)),"<="&$A$19-7),SUMIFS(INDEX('源数据表'!$A:$X,0,MATCH(D12,'源数据表'!$1:$1,0)),INDEX('源数据表'!$A:$X,0,MATCH($A$12,'源数据表'!$1:$1,0)),">="&A13-7,INDEX('源数据表'!$A:$X,0,MATCH($A$12,'源数据表'!$1:$1,0)),"<="&$A$19-7,INDEX('源数据表'!$A:$X,0,MATCH("平台i",'源数据表'!$1:$1,0)),$H$5))-1
    
    • 上周的到手率需要通过上周的商家实收和上周GMV进行计算
    到手率周环比=本周到手率/上周到手率-1
    上周到手率=上周商家实收/上周GMV
    >因此,
    到手率环比=本周到手率/(上周商家实收/上周GMV)-1
    
    • 上周的商家实收已在周环比商家实收中计算过,可直接复制函数使用
    IF($H$5="全部",SUMIFS(INDEX('源数据表'!$A:$X,0,MATCH($D$12,'源数据表'!$1:$1,0)),INDEX('源数据表'!$A:$X,0,MATCH($A$12,'源数据表'!$1:$1,0)),">="&A13-7,INDEX('源数据表'!$A:$X,0,MATCH($A$12,'源数据表'!$1:$1,0)),"<="&$A$19-7),SUMIFS(INDEX('源数据表'!$A:$X,0,MATCH(D12,'源数据表'!$1:$1,0)),INDEX('源数据表'!$A:$X,0,MATCH($A$12,'源数据表'!$1:$1,0)),">="&A13-7,INDEX('源数据表'!$A:$X,0,MATCH($A$12,'源数据表'!$1:$1,0)),"<="&$A$19-7,INDEX('源数据表'!$A:$X,0,MATCH("平台i",'源数据表'!$1:$1,0)),$H$5))
    
    • 上周的GMV与上周的商家实收的函数实现逻辑一致,仅需把函数中的商家实收单元格D12改为GMV单元格C12即可
    IF($H$5="全部",SUMIFS(INDEX('源数据表'!$A:$X,0,MATCH($C$12,'源数据表'!$1:$1,0)),INDEX('源数据表'!$A:$X,0,MATCH($A$12,'源数据表'!$1:$1,0)),">="&A13-7,INDEX('源数据表'!$A:$X,0,MATCH($A$12,'源数据表'!$1:$1,0)),"<="&$A$19-7),SUMIFS(INDEX('源数据表'!$A:$X,0,MATCH($C$12,'源数据表'!$1:$1,0)),INDEX('源数据表'!$A:$X,0,MATCH($A$12,'源数据表'!$1:$1,0)),">="&A13-7,INDEX('源数据表'!$A:$X,0,MATCH($A$12,'源数据表'!$1:$1,0)),"<="&$A$19-7,INDEX('源数据表'!$A:$X,0,MATCH("平台i",'源数据表'!$1:$1,0)),$H$5))
    
    • 周环比到手率完整函数实现(本周到手率单元格E9):
    =E9/(IF($H$5="全部",SUMIFS(INDEX('源数据表'!$A:$X,0,MATCH($D$12,'源数据表'!$1:$1,0)),INDEX('源数据表'!$A:$X,0,MATCH($A$12,'源数据表'!$1:$1,0)),">="&A13-7,INDEX('源数据表'!$A:$X,0,MATCH($A$12,'源数据表'!$1:$1,0)),"<="&$A$19-7),SUMIFS(INDEX('源数据表'!$A:$X,0,MATCH(D12,'源数据表'!$1:$1,0)),INDEX('源数据表'!$A:$X,0,MATCH($A$12,'源数据表'!$1:$1,0)),">="&A13-7,INDEX('源数据表'!$A:$X,0,MATCH($A$12,'源数据表'!$1:$1,0)),"<="&$A$19-7,INDEX('源数据表'!$A:$X,0,MATCH("平台i",'源数据表'!$1:$1,0)),$H$5))/IF($H$5="全部",SUMIFS(INDEX('源数据表'!$A:$X,0,MATCH($C$12,'源数据表'!$1:$1,0)),INDEX('源数据表'!$A:$X,0,MATCH($A$12,'源数据表'!$1:$1,0)),">="&A13-7,INDEX('源数据表'!$A:$X,0,MATCH($A$12,'源数据表'!$1:$1,0)),"<="&$A$19-7),SUMIFS(INDEX('源数据表'!$A:$X,0,MATCH($C$12,'源数据表'!$1:$1,0)),INDEX('源数据表'!$A:$X,0,MATCH($A$12,'源数据表'!$1:$1,0)),">="&A13-7,INDEX('源数据表'!$A:$X,0,MATCH($A$12,'源数据表'!$1:$1,0)),"<="&$A$19-7,INDEX('源数据表'!$A:$X,0,MATCH("平台i",'源数据表'!$1:$1,0)),$H$5)))-1
    

    7.进度条

    1)添加目标
    当平台等于全部时,目标为20W;当平台等于美团时,目标为15W;当平台为饿了么时,目标为5W。函数实现如下

    =IF($H$5="全部",200000,IF($H$5="美团",150000,50000))
    

    2)计算业务进度

    业务进度=截至目前整个月的GMV/目标
    截至目前整个月的GMV=本月的第一天至本周的最后一天的GMV/目标
    
    • 利用DATE、YEAR、MONTH函数求本月的第一天(本周第一天单元格A13)
    DATE(YEAR($A$13),MONTH($A$13),1)
    
    • 求本月第一天至本周最后一天GMV与求上周GMV逻辑一致,将日期判断修改为大于等于本月第一天,小于等于本周最后一天即可,完整函数实现如下:
    IF($H$5="全部",SUMIFS(INDEX('源数据表'!$A:$X,0,MATCH($C$12,'源数据表'!$1:$1,0)),INDEX('源数据表'!$A:$X,0,MATCH($A$12,'源数据表'!$1:$1,0)),">="&DATE(YEAR($A$13),MONTH($A$13),1),INDEX('源数据表'!$A:$X,0,MATCH($A$12,'源数据表'!$1:$1,0)),"<="&$A$19),SUMIFS(INDEX('源数据表'!$A:$X,0,MATCH($C$12,'源数据表'!$1:$1,0)),INDEX('源数据表'!$A:$X,0,MATCH($A$12,'源数据表'!$1:$1,0)),">="&DATE(YEAR($A$13),MONTH($A$13),1),INDEX('源数据表'!$A:$X,0,MATCH($A$12,'源数据表'!$1:$1,0)),"<="&$A$19,INDEX('源数据表'!$A:$X,0,MATCH("平台i",'源数据表'!$1:$1,0)),$H$5))
    

    3)添加进度条
    菜单栏【开始】→功能区【条件格式】→【新建规则】→格式样式【数据条】→最小值/最大值【数字】→值【0】至【1】→选择填充类型、填充颜色→【确定】
    添加进度条

    8.图标指示

    图标指示

    为小看板中的周环比添加图标指示。正数显示为绿色,同时绿色箭头向下;负数或零时显示为红色,同时红色箭头向下。设置条件格式如下:
    菜单栏【开始】→功能区【条件格式】→【新建规则】→选择规则类型【只包含一下内容的单元格设置格式】→【单元格值】【大于】【=0】→【格式】→【字体】→【颜色】

    • 分别设置字体当数值大于0绿色、小于等于0黄色:
      在这里插入图片描述
    • 设置图标
      菜单栏【开始】→功能区【条件格式】→【新建规则】→格式样式【图标集】→选择对应图标→值【>=】【0】→类型【数字】→【确定】
      设置图标

    所有条件设置如下,利用格式刷使该条件格式应用在其他环比数据上
    在这里插入图片描述

    9.突出显示

    • 标记结果指标中GMV低于平均值的数据
      突出显示
      选中条件判断区域→菜单栏【开始】→功能区【条件格式】→【新建规则】→选择规则类型【使用公式确定要设置的单元格】→设置公式,判断C13-C19整列数据→【格式】→【字体】→下划线【单下划线】→字形【粗体】→【确定】
      在这里插入图片描述

    10.细节美化

    1)标题居中、字体加粗放大
    2)关键字、关键数据加粗
    3)表头添加背景颜色、修改字体颜色
    4)添加外框线

    展开全文
  • 二、采集数据源 2、采集【氮氨】数据 3、采集【COD 化学需氧量】数据 4、采集【总氮】数据 三、输出Excel内容样式 1、输出文件命名格式 2、文件内容格式详述 四、解决方案 1、获取并解析【废水企业名单】 2...

    目录

    一、背景

    二、采集数据源

    2、采集【氮氨】数据

    3、采集【COD 化学需氧量】数据

    4、采集【总氮】数据

    三、输出Excel内容样式

    1、输出文件命名格式

    2、文件内容格式详述

    四、解决方案

    1、获取并解析【废水企业名单】

    2、获取并解析【废水企业历史数据】

    3、输出到Excel文件的sheet栏目【废水】

    4、保证数据完整性


    一、背景

    某生态环境治理公司,定期从各官网上获取数据,评估环境污染指数。

    目前采取的方式为人力手抄方式,效率低,资源浪费。

    领导重视效率,要求推动自动化流程,避免人力浪费,提高效率。

    解决方案:Python采集数据源,自动生成Excel报表。

    二、采集数据源

    网址:省控以上重点污染源数据发布

    1、采集两类数据【废水】和【污水厂】

    2、采集【氮氨】数据

    3、采集【COD 化学需氧量】数据

    4、采集【总氮】数据

    三、输出Excel内容样式

    1、输出文件命名格式

    每日生成一份Excel文件,以当天【年月日】标识。

    2、文件内容格式详述

    【废水】和【污水厂】各用一个sheet

    四、解决方案

    1、获取并解析【废水企业名单】

    此处仅做可行性分析用,共1680个企业。

    2、获取并解析【废水企业历史数据】

    此处仅做可行性分析用:

    青州板纸厂12.1-12.24日总排口的氨氮值:

    3、输出到Excel文件的sheet栏目【废水】

    4、保证数据完整性

    目标网站有时响应超时,需要对其做响应码检测:如果结果响应错误,需要做重试处理,保证数据的完整性。

    本次分享结束,欢迎交流合作,商务合作请私聊:6550523

    展开全文
  • Java中导入/导出excel,导出pdf报表信息

    千次阅读 2019-02-08 19:30:08
    1.项目中经常需要用到报表生成,信息导入数据库的功能.主要有以下几种. 2.其中比较简单的是 外部数据无需处理...3.选择导入数据源及相关表位置. 4.选择导入xlsx数据源位置,从第几行开始导入,字段名行的相对行位置...

    1.项目中经常需要用到报表生成,信息导入数据库的功能.主要有以下几种.

    2.其中比较简单的是 外部数据无需处理直接 导入数据库中,这种比较简单.直接利用Navicat数据库工具 导入外部.示例如下
    1.准备customer.xlsx文件信息
    在这里插入图片描述
    2.使用导入向导,选择导入文件的xlsx文件类型在这里插入图片描述
    3.选择导入数据源及相关表位置.
    在这里插入图片描述
    4.选择导入xlsx数据源位置,从第几行开始导入,字段名行的相对行位置.
    在这里插入图片描述
    5.选择导入数据 对应表字段相关信息
    在这里插入图片描述
    6.选择导入模式,此处我使用的是 记录添加模式
    在这里插入图片描述
    7.开始添加 ,影像了几条记录
    在这里插入图片描述
    8.记录添加成功
    在这里插入图片描述

    3.根据需要的信息 生成导出excel表格,具体代码示例如下

    package com.bxd.app.util;
    
    import java.io.File;
    import java.io.FileNotFoundException;
    import java.io.FileOutputStream;
    import java.io.IOException;
    import java.util.ArrayList;
    import java.util.Iterator;
    import java.util.LinkedHashMap;
    import java.util.LinkedList;
    import java.util.List;
    import java.util.Map;
    
    import org.apache.poi.hssf.usermodel.HSSFCell;
    import org.apache.poi.hssf.usermodel.HSSFCellStyle;
    import org.apache.poi.hssf.usermodel.HSSFFont;
    import org.apache.poi.hssf.usermodel.HSSFRichTextString;
    import org.apache.poi.hssf.usermodel.HSSFRow;
    import org.apache.poi.hssf.usermodel.HSSFSheet;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.apache.poi.hssf.util.HSSFColor;
    import org.apache.poi.ss.util.CellRangeAddress;
    
    import com.bxd.app.view.biz.ExportExcelView;
    
    public class ExportExcelUtil {
    
    	/**
    	 * 创建表格标题
    	 * 
    	 * @param wb
    	 *            Excel文档对象
    	 * @param sheet
    	 *            工作表对象
    	 * @param headString
    	 *            标题名称
    	 * @param col
    	 *            标题占用列数
    	 */
    	@SuppressWarnings("deprecation")
    	public static void createHeadTittle(HSSFWorkbook wb, HSSFSheet sheet, String headString, int col) {
    		HSSFRow row = sheet.createRow(0); // 创建Excel工作表的行
    		HSSFCell cell = row.createCell(0); // 创建Excel工作表指定行的单元格
    		row.setHeight((short) 1000); // 设置高度
    
    		cell.setCellType(HSSFCell.ENCODING_UTF_16); // 定义单元格为字符串类型
    		cell.setCellValue(new HSSFRichTextString(headString));
    
    		sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, col)); // 指定标题合并区域
    
    		// 定义单元格格式,添加单元格表样式,并添加到工作簿
    		HSSFCellStyle cellStyle = wb.createCellStyle();
    		cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 指定单元格居中对齐
    		cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 指定单元格垂直居中个对齐
    		cellStyle.setWrapText(true); // 指定单元格自动换行
    
    		// 设置单元格字体
    		HSSFFont font = wb.createFont();
    		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    		font.setFontName("微软雅黑");
    		font.setFontHeightInPoints((short) 16); // 字体大小
    
    		cellStyle.setFont(font);
    		cell.setCellStyle(cellStyle);
    	}
    
    	/**
    	 * 创建表头
    	 * 
    	 * @param wb
    	 *            Excel文档对象
    	 * @param sheet
    	 *            工作表对象
    	 * @param thead
    	 *            表头内容
    	 * @param sheetWidth
    	 *            每一列宽度
    	 */
    	@SuppressWarnings("deprecation")
    	public static void createThead(HSSFWorkbook wb, HSSFSheet sheet, String[] thead, int[] sheetWidth) {
    		HSSFRow row1 = sheet.createRow(1);
    		row1.setHeight((short) 600);
    		// 定义单元格格式,添加单元格表样式,并添加到工作簿
    		HSSFCellStyle cellStyle = wb.createCellStyle();
    		cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    		cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
    		cellStyle.setWrapText(true);
    		cellStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); // 设置背景色
    		cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    		cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); // 设置右边框类型
    		cellStyle.setRightBorderColor(HSSFColor.BLACK.index); // 设置右边框颜色
    
    		// 设置单元格字体
    		HSSFFont font = wb.createFont();
    		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    		font.setFontName("宋体");
    		font.setFontHeightInPoints((short) 10);
    		cellStyle.setFont(font);
    
    		// 设置表头内容
    		for (int i = 0; i < thead.length; i++) {
    			HSSFCell cell1 = row1.createCell(i);
    			cell1.setCellType(HSSFCell.ENCODING_UTF_16);
    			cell1.setCellValue(new HSSFRichTextString(thead[i]));
    			cell1.setCellStyle(cellStyle);
    		}
    
    		// 设置每一列宽度
    		for (int i = 0; i < sheetWidth.length; i++) {
    			sheet.setColumnWidth(i, sheetWidth[i]);
    		}
    	}
    
    	/**
    	 * 填入数据
    	 * 
    	 * @param wb
    	 *            // Excel文档对象
    	 * @param sheet
    	 *            // 工作表对象
    	 * @param result
    	 *            // 表数据
    	 */
    	@SuppressWarnings("deprecation")
    	public static void createTable(HSSFWorkbook wb, HSSFSheet sheet, List<LinkedHashMap<String, String>> result) {
    		// 定义单元格格式,添加单元格表样式,并添加到工作薄
    		HSSFCellStyle cellStyle = wb.createCellStyle();
    		cellStyle.setWrapText(true);
    
    		// 单元格字体
    		HSSFFont font = wb.createFont();
    		font.setFontName("宋体");
    		font.setFontHeightInPoints((short) 10);
    		cellStyle.setFont(font);
    		cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中    
    
    		// 循环插入数据
    		for (int i = 0; i < result.size(); i++) {
    			HSSFRow row = sheet.createRow(i + 2);
    			row.setHeight((short) 400); // 设置高度
    			HSSFCell cell = null;
    			int j = 0;
    			for (String key : (result.get(i).keySet())) {
    				cell = row.createCell(j);
    				cell.setCellStyle(cellStyle);
    				cell.setCellValue(new HSSFRichTextString(result.get(i).get(key)));
    				j++;
    			}
    		}
    	}
    
    	public static void main(String[] args) {
    		//测试hashmap  treemap  linkedhashmap之间的顺序
     		/*Map<String, String>  map=new HashMap<>();
     		System.out.println("hashmap排序");
     		add_keyvalue(map);
     		TreeMap<String, String>  map2=new TreeMap<>();
     		System.out.println("treemap排序");
     		add_keyvalue(map2);
     		LinkedHashMap<String, String>  map3=new LinkedHashMap<>();
     		System.out.println("linkedhash排序");
     		add_keyvalue(map3);*/
     		
    		// 1.封装数据
    		List<ExportExcelView> list = new LinkedList<>();
    		ExportExcelView b1 = new ExportExcelView();
    		b1.setDeclsno("201810251706470169854601");
    		b1.setDecdt("2018-09-22");
    		b1.setEleacno("1209394999");
    		b1.setCustName("张三");
    		b1.setEntName("正信广电");
    		b1.setSaleName("郭启铭");
    		b1.setSaleTel("17342064227");
    		b1.setRealsumretbal("1000");
    		b1.setDecutionFee("100");
    
    		ExportExcelView b2 = new ExportExcelView();
    		b2.setDeclsno("201810251706470176052618");
    		b2.setDecdt("2018-09-22");
    		b2.setEleacno("1209394999");
    		b2.setCustName("赵四");
    		b2.setEntName("正信广电");
    		b2.setSaleName("郭启铭");
    		b2.setSaleTel("17342064227");
    		b2.setRealsumretbal("2000");
    		b2.setDecutionFee("200");
    		list.add(b1);
    		list.add(b2);
    		
    		// 实体类转换为map
    		List<LinkedHashMap<String, String>> result = new ArrayList<>();
    		LinkedHashMap<String, String> map = new LinkedHashMap<>();
            for (ExportExcelView e : list) {
            	map.put("declsno", e.getDeclsno());
            	map.put("decdt", e.getDecdt());
            	map.put("eleacno", e.getEleacno());
            	map.put("custName",e.getCustName());
            	map.put("entName",e.getEntName());
            	map.put("saleName",e.getSaleName());
            	map.put("saleTel",e.getSaleTel());
            	map.put("realsumretbal",e.getRealsumretbal());
            	map.put("decutionFee",e.getDecutionFee());
            	result.add(map);
    		}
    
    		// 2.定义变量值 创建Excel文件
    		String fileName = "正信广电_201809代扣费用表.xls"; // 定义文件名
    		String headString = "正信广电_201809代扣费用表"; // 定义表格标题
    		String sheetName = "正信广电_201809代扣费用表"; // 定义工作表表名
    		String filePath = "D:\\"; // 文件本地保存路径
    		String[] thead = { "扣款流水", "扣款日期", "发电户号", "用户姓名", "开发商",
    				"业务员姓名","业务员手机号","扣款金额(元)", "代扣费用(元)" };
    		int[] sheetWidth = { 7500, 4000, 3000, 3000, 4000, 3000, 5000, 5000,5000}; // 定义每一列宽度
    
    		HSSFWorkbook wb = new HSSFWorkbook(); // 创建Excel文档对象
    		HSSFSheet sheet = wb.createSheet(sheetName); // 创建工作表
    
    		// 3.生成表格
    		// ①创建表格标题
    		createHeadTittle(wb, sheet, headString, 8);
    		// result.get(0).size() - 1为表格占用列数,从0开始
    		// ②创建表头
    		createThead(wb, sheet, thead, sheetWidth);
    		// ③填入数据
    		createTable(wb, sheet, result);
    		
    		FileOutputStream fos;
    		try {
    			fos = new FileOutputStream(new File(filePath + fileName));
    			wb.write(fos);
    			fos.close();
    			wb.close();
    			System.out.println("导出excel成功");
    		} catch (FileNotFoundException ex) {
    			ex.printStackTrace();
    		} catch (IOException ex) {
    			ex.printStackTrace();
    		}
    
    	}
    	
    	public static void add_keyvalue(Map<String, String> map){
    		map.put("351", "11");
    		map.put("512", "222");
    		map.put("853", "333");
    		map.put("125", "333");
    		map.put("341", "333");
    		Iterator<String>  iterator=map.keySet().iterator();
    		while(iterator.hasNext()){
    			System.out.println(iterator.next());
    		}
    	}
    
    }
    
    

    在这里插入图片描述

    2.导出pdf示例,将多张图片合成pdf文件 生成到指定位置

    package com.bxd.app.util;
    
    import java.awt.image.BufferedImage;
    import java.io.File;
    import java.io.FileOutputStream;
    import java.io.IOException;
    
    import javax.imageio.ImageIO;
    
    import com.bxd.core.util.FileUtil;
    import com.lowagie.text.BadElementException;
    import com.lowagie.text.Document;
    import com.lowagie.text.DocumentException;
    import com.lowagie.text.Image;
    import com.lowagie.text.Rectangle;
    import com.lowagie.text.pdf.PdfWriter;
    
    class PrintToPdfUtil {
    	
    	/**
    	 * 
    	 * @param imageFolderPath
    	 *            图片文件夹地址
    	 * @param pdfPath
    	 *            PDF文件保存地址
    	 * 
    	 */
    	public static void toPdf(String imageFolderPath, String pdfPath) {
    		try {
    			// 图片文件夹地址
    			// String imageFolderPath = "D:/Demo/ceshi/";
    			// 图片地址
    			String imagePath = null;
    			// PDF文件保存地址
    			// String pdfPath = "D:/Demo/ceshi/hebing.pdf";
    			// 输入流
    			FileOutputStream fos = new FileOutputStream(pdfPath);
    			
    			// 创建文档
    			Document doc = new Document(null, 0, 0, 0, 0);
    			// doc.open();
    			// 写入PDF文档
    			PdfWriter.getInstance(doc, fos);
    			// 读取图片流
    			BufferedImage img = null;
    			// 实例化图片
    			Image image = null;
    			// 获取图片文件夹对象
    			File file = new File(imageFolderPath);
    			File[] files = file.listFiles();
    			// 循环获取图片文件夹内的图片
    			for (File file1 : files) {
    				if (file1.getName().endsWith(".png") || file1.getName().endsWith(".jpg")
    						|| file1.getName().endsWith(".gif") || file1.getName().endsWith(".jpeg")
    						|| file1.getName().endsWith(".tif")) {
    					// System.out.println(file1.getName());
    					imagePath = imageFolderPath + file1.getName();
    					// 读取图片流
    					img = ImageIO.read(new File(imagePath));
    					// 根据图片大小设置文档大小
    					doc.setPageSize(new Rectangle(img.getWidth(), img.getHeight()));
    					// 实例化图片
    					image = Image.getInstance(imagePath);
    					// 添加图片到文档
    					doc.open();
    					doc.add(image);
    				}
    			}
    			// 关闭文档
    			doc.close();
    		} catch (IOException e) {
    			e.printStackTrace();
    		} catch (BadElementException e) {
    			e.printStackTrace();
    		} catch (DocumentException e) {
    			e.printStackTrace();
    		}
    	}
    
    	public static void main(String[] args) {
    		long time1 = System.currentTimeMillis();
    		toPdf("C:/2018-11-14/", 
    				"C:/hebing.pdf");
    		long time2 = System.currentTimeMillis();
    		int time = (int) ((time2 - time1) / 1000);
    		System.out.println("执行了:" + time + "秒!");
    	}
    }
    
    

    在这里插入图片描述

    3.导入excel文件信息

    package com.bxd.app.util;
    
    import com.bxd.app.dao.biz.BdCustomerDao;
    import com.bxd.app.entity.BdCustomer;
    import com.bxd.core.util.BeanUtil;
    import com.bxd.core.util.IdcardValidator;
    import com.bxd.core.util.MD5;
    import com.bxd.core.util.StringUtil;
    import com.bxd.core.util.TelValidatorUtil;
    import com.bxd.core.util.TextFormater;
    import com.bxd.core.util.UUIDGenerator;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.apache.poi.ss.usermodel.Cell;
    import org.apache.poi.ss.usermodel.DateUtil;
    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 org.slf4j.Logger;
    import org.slf4j.LoggerFactory;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Component;
    
    import java.io.FileInputStream;
    import java.io.FileNotFoundException;
    import java.io.IOException;
    import java.io.InputStream;
    import java.util.ArrayList;
    import java.util.Calendar;
    import java.util.Date;
    import java.util.LinkedHashMap;
    import java.util.LinkedList;
    import java.util.List;
    import java.util.Map;
    import java.util.Map.Entry;
    
    
    @Component
    public class ImportCustomerUtil {
    	
    	private static Logger logger =LoggerFactory.getLogger(ImportCustomerUtil.class);
    	
    	@Autowired
    	public BdCustomerDao bdCustomerDao;
    
    	/**
    	 * 生成需要的数据
    	 * 
    	 * @param passwd
    	 *            密码
    	 * @param filePath
    	 *            文件路径
    	 * @param columns
    	 *            列数据
    	 * @param type
    	 *            01 只需要注册的 02 代表 注册和实名都需要的弄的数据
    	 * @return
    	 */
    	@SuppressWarnings("unused")
    	public static List<BdCustomer> import_excel(String passwd, String filePath, String columns[], String type) {
    		Workbook wb = null;
    		Sheet sheet = null;
    		Row row = null;
    		List<Map<String, String>> list = null;
    		String cellData = null;
    
    		wb = readExcel(filePath);
    		if (wb != null) {
    			// 用来存放表中数据
    			list = new ArrayList<Map<String, String>>();
    			// 获取第一个sheet
    			sheet = wb.getSheetAt(0);
    			// 获取最大行数
    			int rownum = sheet.getPhysicalNumberOfRows();
    			// 获取第一行
    			row = sheet.getRow(0);
    			// 获取最大列数
    			int colnum = row.getPhysicalNumberOfCells();
    			for (int i = 1; i < rownum; i++) {
    				Map<String, String> map = new LinkedHashMap<String, String>();
    				row = sheet.getRow(i);
    				if (row != null) {
    					for (int j = 0; j < colnum; j++) {
    						cellData = (String) getCellFormatValue(row.getCell(j));
    						map.put(columns[j], cellData);
    					}
    				} else {
    					break;
    				}
    				list.add(map);
    			}
    		}
    		// 遍历解析出来的list
    		logger.info("*******excel读取出来的数量:" + list.size() + "*****");
    		List<BdCustomer> excel_result = new LinkedList<BdCustomer>();
    		for (Map<String, String> map : list) {
    			for (Entry<String, String> entry : map.entrySet()) {
    //				logger.info(entry.getKey() + ":" + entry.getValue() + ",");
    			}
    			BdCustomer bdCustomer = BeanUtil.toBean(map, BdCustomer.class);
    			excel_result.add(bdCustomer);// 存储到list集合中
    		}
    		List<BdCustomer> result = new LinkedList<BdCustomer>();
    		int no_register=0;
    		if (type.equals("01")) {//只需要注册的用户数据
    			for (int i = 0; i < excel_result.size(); i++) {
    				// 手机号码格式不正确   不能注册的用户信息及数量
    				if (!TelValidatorUtil.mobileValidator(excel_result.get(i).getTelphone())) {
    					logger.info("姓名:"+excel_result.get(i).getCustName()+","+
    						excel_result.get(i).getTelphone()+"手机号格式不正确");
    					no_register++;
    					continue;
    				}
    				//手机号码格式正确  同时 客户姓名或者 身份证号不正确
    				if (TelValidatorUtil.mobileValidator(excel_result.get(i).getTelphone())
    						&& (StringUtil.isEmpty(excel_result.get(i).getCustName())
    								||excel_result.get(i).getCustName().indexOf("*")!=-1
    								||!"".equals( IdcardValidator.Validate(excel_result.get(i).getIdNo())))) {
    					try {
    						// 对用户信息进行加密
    						BdCustomer bdCustomer = new BdCustomer();
    						bdCustomer.setCustNo("U" + UUIDGenerator.nextSerial());// 如果手机号不为空,则设置用户编号
    						bdCustomer.setCustAcct(DesensitizationUtil.encrypt_private(excel_result.get(i).getTelphone()));
    						bdCustomer.setTelphone(DesensitizationUtil.encrypt_private(excel_result.get(i).getTelphone()));
    						bdCustomer.setPasswd(MD5.crypt(passwd));
    						bdCustomer.setRegTime(TextFormater.format(new Date(), "yyyyMMdd"));
    						bdCustomer.setIsBind("0");
    						bdCustomer.setIsLocked("0");
    						bdCustomer.setSources("1");
    						bdCustomer.setCustName("");
    						bdCustomer.setIdType("01");
    						result.add(bdCustomer);
    					} catch (Exception ex) {
    						ex.printStackTrace();
    					}
    				}
    			}
    			logger.info("无法注册的用户数量:"+no_register);
    		} else {//需要注册和实名的用户数据为
    			for (int i = 0; i < excel_result.size(); i++) {
    				// 手机号正确,姓名及 身份证号格式正确
    				if (TelValidatorUtil.mobileValidator(excel_result.get(i).getTelphone())
    						&& StringUtil.isNotEmpty(excel_result.get(i).getCustName())
    						&&excel_result.get(i).getCustName().indexOf("*")==-1
    								&&"".equals(IdcardValidator.Validate(excel_result.get(i).getIdNo()))
    								) {
    					try {
    						// 对用户信息进行加密
    						BdCustomer bdCustomer = new BdCustomer();
    						//实名认证所需填写信息
    						bdCustomer.setCustName(DesensitizationUtil.encrypt_private(excel_result.get(i).getCustName()));
    						bdCustomer.setIsVerified("01");
    						bdCustomer.setVerifiedTime(TextFormater.format(Calendar.getInstance().getTime(), "yyyyMMdd"));
    						bdCustomer.setCustAge(IdNOToAge(excel_result.get(i).getIdNo()));//根据身份证号计算年龄
    						bdCustomer.setIdNo(DesensitizationUtil.encrypt_private(excel_result.get(i).getIdNo()));
    						bdCustomer.setSources("1");
    						bdCustomer.setCustNo("U" + UUIDGenerator.nextSerial());// 如果手机号不为空,则设置用户编号
    						bdCustomer.setCustAcct(DesensitizationUtil.encrypt_private(excel_result.get(i).getTelphone()));
    						bdCustomer.setTelphone(DesensitizationUtil.encrypt_private(excel_result.get(i).getTelphone()));
    						bdCustomer.setPasswd(MD5.crypt(passwd));
    						bdCustomer.setRegTime(TextFormater.format(new Date(), "yyyyMMdd"));
    						bdCustomer.setIsBind("0");
    						bdCustomer.setIsLocked("0");
    						bdCustomer.setIdType("01");
    						
    						result.add(bdCustomer);
    					} catch (Exception ex) {
    						ex.printStackTrace();
    					}
    				}
    			}
    
    		}
    		return result;
    
    	}
    	
    	
    	
    
    	public static void main(String[] args) {
    		String passwd = "666666d";// 导入用户密码
    		String filepath = "C:\\test.xlsx";// 文件路径地址
    		String columns[] = { "custName", "roofArea", "idNo", "homeAddress", "telphone" };
    		List<BdCustomer> result = import_excel(passwd, filepath, columns, "01");
    		System.out.println(TelValidatorUtil.mobileValidator(""));
    
    	}
    	
    	/**
    	 * 根据身份证号计算年龄
    	 * @param IdNO
    	 * @return
    	 */
    	private static int IdNOToAge(String IdNO){
            Integer  birthyear=Integer.parseInt(IdNO.substring(6, 10));//出生年月
            Integer  year=Integer.parseInt(TextFormater.format(Calendar.getInstance().getTime(),"yyyy"));//
    		return year-birthyear;
    
        }
    
    	// 读取excel
    	@SuppressWarnings("resource")
    	public static Workbook readExcel(String filePath) {
    		Workbook wb = null;
    		if (filePath == null) {
    			return null;
    		}
    		String extString = filePath.substring(filePath.lastIndexOf("."));
    		InputStream is = null;
    		try {
    			is = new FileInputStream(filePath);
    			if (".xls".equals(extString)) {
    				return wb = new HSSFWorkbook(is);
    			} else if (".xlsx".equals(extString)) {
    				return wb = new XSSFWorkbook(is);
    			} else {
    				return wb = null;
    			}
    
    		} catch (FileNotFoundException e) {
    			e.printStackTrace();
    		} catch (IOException e) {
    			e.printStackTrace();
    		}
    		return wb;
    	}
    
    	@SuppressWarnings("deprecation")
    	public static Object getCellFormatValue(Cell cell) {
    		Object cellValue = null;
    		if (cell != null) {
    			// 判断cell类型
    			switch (cell.getCellType()) {
    			case Cell.CELL_TYPE_NUMERIC: {
    				cellValue = String.valueOf(cell.getNumericCellValue());
    				break;
    			}
    			case Cell.CELL_TYPE_FORMULA: {
    				// 判断cell是否为日期格式
    				if (DateUtil.isCellDateFormatted(cell)) {
    					// 转换为日期格式YYYY-mm-dd
    					cellValue = cell.getDateCellValue();
    				} else {
    					// 数字
    					cellValue = String.valueOf(cell.getNumericCellValue());
    				}
    				break;
    			}
    			case Cell.CELL_TYPE_STRING: {
    				cellValue = cell.getRichStringCellValue().getString();
    				break;
    			}
    			default:
    				cellValue = "";
    			}
    		} else {
    			cellValue = "";
    		}
    		return cellValue;
    	}
    }
    
    
    展开全文
  • 用Python自动生成Excel数据报表

    千次阅读 2022-01-11 01:05:57
    大家好,我是辰哥~点击下方名片关注和星标『Python研究者』!????点击关注|设为星标|干货速递????今天带大家来实战一波,使用Python自动生成数据报表!从一条条的数据中,创建出一张数据报...
  • 内容索引:VB源码,报表打印,报表导入 以前帮朋友做的,财政局数据下发并生成报表,然后下发到各分局再进行导入,VB代码实现,中转出的数据格式主要是TXT文本文件,对于初学文本控制和EXCEL报表导出的有一定帮助,...
  • import pandas as pd from openpyxl.drawing.image ...# 说明通过读取数据源生成一个excel多个sheet的可视化数据报表的过程。 # 读取数据源,转换成二维数组。 pd_re = pd.read_excel("C:/Users/Administrator/Des.
  • 团队需要分享、沟通,数据分析师需要洞察数据、分析结果分享给企业领导、团队同事、大众媒体及更多的利益相关方,Word/PowerPoint/WPS仍然是最常见的分享形式。通过分析报告,数据分析师可以轻松的将Smartbi Insight...
  • 用 Python 自动生成 Excel 数据报表~

    千次阅读 2021-04-08 00:27:01
    大家好,我是小z今天给大家来一波实战,使用Python自动生成数据报表!从一条条的数据中,创建出一张数据报表,得出你想要的东西,提高效率。主要使用到pandas、xlwings以及mat...
  • 用Python自动生成Excel报表

    千次阅读 2020-11-06 10:04:25
    在日常工作中,可能会有一些重复无聊的任务,比如说,从 Excel 或数据库中收集一些数据,设置相应的数据格式并做成报表。 类似这种重复无聊的任务,我们完全可以交给 Python 去自动完成,只要第一次把 Python 代码写...
  • 最近在跟隔壁部门闲聊的时候,我发现会有这样的情况。他们跟我吐槽说,每天需要花很多时间做报表,但我发现其实他们80%的报表都是机械、重复式的手工操作,最夸张的一张报表需要花两个小时才能更新完。
  • Excel数据批量转Word表报告生成 功能说明:把Excel中的数据以行为单位,插入到Word中的表格当中。
  • 我是小z今天给大家分享一篇俊红新书《对比Excel,轻松学习Python报表自动化》中关于报表自动化实战的内容,文末也会免费赠送几本新书。这本书是俊红新作,强烈安利!这篇文章将带你了解报表自动化的流程,并教你用...
  •  #region 从datatable中将数据导出到excel  ///  /// 导出DataTable到流,此方法包含所有格式。  /// 2003 最大行数为65535,2007及以上为1048576  ///  /// <param name="dtSource"></param>  /// ...
  • 从MySQL中查询数据并且保存生成excel中 上一篇文中讲到: python批量爬取古诗词并保存之Excel或数据库mysql https://blog.csdn.net/weixin_44404350/article/details/116242727?spm=1001.2014.3001.5501 例如:...
  • 大部分的数据分析师都或多或少掉入这样的陷阱:每天大部分的工作都花在查数上,干着干着变成了“查数菇”。看上去帮老板或其他同事查数据数据分析师天经地义的任务,怎么会成为陷阱呢?我来给你分析分析: 业务...
  • 一份报表自动化的流程 报表自动化实战 将不同的文件进行合并 Excel的基本组成 下图是Excel的中各个部分的组成关系,我们工作中每天会处理很多Excel文件,一个Excel文件其实就是一个工作簿。你在每次新建一个Excel...
  • 提示:文章写完后,目录可以自动生成,如何生成可参考右边的帮助文档 文章目录前言一、power query二、实用功能1.引入库2.读入数据总结 前言 说到数据分析工具,大家第一时间可能会想到excel、python、sql、power...
  • 今天给大家分享一篇俊红新书《对比Excel,轻松学习Python报表自动化》中关于报表自动化实战的内容,文末包邮赠送4本新书。本篇文章将带你了解报表自动化的流程,并教你用Python实现工...
  • 金蝶财务软件的报表如何导出excle文件文件—引出报表—保存的时候把保存格式修改为excle就行了Excel软件生成的文件扩展名是什么1.Excel软件生成的扩展名是.xls或.xlsx2.如果始文件设置中,设置的是Excel工作簿格式,...
  • java 之数据导出导入excel(包含动态导出字段)
  • 前言最近笔者终于把H5-Dooring的后台管理系统初步搭建完成, 有了初步的数据采集和数据分析能力, 接下来我们就复盘一下其中涉及的几个知识点,并一一阐述其在Dooring H5可视化...
  • SpringBoot项目利用POI实现Excel导入导出
  • 1序言Excel、PDF的导出、导入是我们工作中经常遇到的一个问题,刚好今天公司业务遇到了这个问题,顺便记个笔记以防下次遇到相同的问题而束手无策。公司有这么两个需求:需求一、给了一个表单,...
  • 相关文章: Python自动化办公--Pandas玩转Excel【一】 ...python处理Excel实现自动化办公教学(数据筛选、公式操作、单元格拆分合并、冻结窗口、图表绘制等)【三】 python入门之后须掌握的知识点(模块化编程、时间.
  • 本期课程,我们将借助服务端表格组件GcExcel 的模板语法来完成Excel报表生成,通过使用简单的语法标记,可以在不借助任何第三方组件的情况下,结合数据源自动化地生成Excel模板文件。 目前,GcExcel可以生成的...
  • Java Aspose导入导出excel

    2021-08-27 16:05:49
    * 导出Excel数据 * @param query * @return * @throws Exception */ public InputStream exportBwClass(BwClass query) throws Exception { // 导出路径 String logPath = "D:" + File.separator + "excel...
  • 1、为了方便操作,基于Excel报表批量上传数据,也就是把Excel中的数据导入到系统中。 2、通过java代码生成Excel报表。也就是把系统中的数据导出到Excel中,方便查阅。 1、Excel版本 目前世面上的Excel分为两个大...
  • 如何利用excel中的数据源制作数据地图

    万次阅读 多人点赞 2016-07-13 12:40:27
    利用excel中的数据源制作数据地图的方法以不新奇,可以直接利用excel制作,也可以利用插件和其他软件来实现。本文就从这三方面来谈谈如何利用制作数据源地图。

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 4,716
精华内容 1,886
热门标签
关键字:

导入数据源excel自动生成报表