精华内容
下载资源
问答
  • 时隔N年再次拿起VBA-VBA统计文件夹中的excel表格sheet页行数如何插入一段漂亮的代码片生成一个适合你的列表创建一个表格设定内容居中、居左、居右SmartyPants创建一个自定义列表如何创建一个注脚注释也是必不可少...

    如何插入一段漂亮的代码片

    大致的思路演进
    1、先找到了读取文件夹中所有文件的函数'Dir' ,和读取 excel 表格已用行数的函数Sheets.UsedRange.Rows.count,后发现该已使用的行出包括excel表格中的空白行;
    2、发现Sheets()仅支持编号,所以删除了多余的sheet页,又把需要的五个sheet页按顺序排排好。
    3、删除空白行的Workbook.Sheets(n).Rows(m).Delete功能,但是一直失败呢,和下图这样不知道怎么回事,看似删掉了其实还有很多…
    在这里插入图片描述
    4、最后手动删除行并把相关的都注释掉了,啊 VBA还写个毛线呢 _(:з」∠)

    // An highlighted block
    Sub 统计全集团的用户帐号数量()
    
        Dim SCM_COUNT, ES_COUNT, EC_COUNT, DL_COUNT, ZJ_COUNT, count, end_row, i, j, k As Long 'scm es ec 代理 专家 用户数量
        Dim filepath$, file$, thisfile As Workbook
        Dim ran As Range
        
        filepath = ThisWorkbook.Path
        filepath = filepath & "\"
        Debug.Print "FILEPATH = " & filepath
        Debug.Print
        
        file = Dir(filepath & "\*.xlsx")
        Debug.Print "FILE = " & file
        Debug.Print
        
        count = 1
        
        Do While file <> ""   '遍历目录下所有的文件
            
    '        If file <> ThisWorkbook.Name Then
                '激活当前文件
                Set thisfile = Workbooks.Open(filepath & file)
                '打开一个文件 删除空白行
    '            Debug.Print "打开thisfile = " & file & "准备删除空白行"
                'Debug.Print
                '遍历五个sheet页
    '            For i = 1 To 5 Step 1
    '                thisfile.Sheets(i).Columns("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    '                k = 1
    '                Set ran = thisfile.Sheets(i).UsedRange
    '                endrow = ran.Row - 1 + ran.Rows.count
    '                '关闭屏幕刷新
    '                Application.ScreenUpdating = False
    '                '删除当前sheet页的空白行
    '                For j = endrow To 1 Step -1
    '                    If Application.WorksheetFunction.CountA(ran.Rows(j)) = 0 Then
    '                        ran.Rows(j).Delete
    '                        k = k + 1
    '                    Else
    '                        Exit For
    '                    End If
    '                Next
    '                '打开屏幕刷新
    '                Application.ScreenUpdating = True
    '                Debug.Print "删除第 " & i & " 个 sheet页 共计 " & k & " 行空白格"
    '                Debug.Print
    '            Next
    '            Debug.Print "完成文件thisfile = " & file & " 的空白格删除"
    '            Debug.Print
                
                '找到SCM\ES\EC\代理\专家的用户数量,相应sheet表格里面已用的行数
                SCM_COUNT = thisfile.Sheets(1).UsedRange.Rows.count
                ES_COUNT = thisfile.Sheets(2).UsedRange.Rows.count
                EC_COUNT = thisfile.Sheets(3).UsedRange.Rows.count
                DL_COUNT = thisfile.Sheets(4).UsedRange.Rows.count
                ZJ_COUNT = thisfile.Sheets(5).UsedRange.Rows.count
            
                Debug.Print "第 " & count & " 个文件:|" & file & "| SCM用户: |" & SCM_COUNT - 1 & "|       ES用户数: |" & ES_COUNT - 1 & "|      EC用户数: |" & EC_COUNT - 1 & "|      代理用户数: |" & DL_COUNT - 1 & "|      专家用户数: |" & ZJ_COUNT - 1 & "|."
                Debug.Print
            
                thisfile.Close
    '        End If
            
            count = count + 1    '文件数加1
            file = Dir           '读取下一个文件
        Loop
    End Sub
    
    
    

    生成一个适合你的列表

    • 项目
      • 项目
        • 项目
    1. 项目1
    2. 项目2
    3. 项目3
    • 计划任务
    • 完成任务

    创建一个表格

    一个简单的表格是这么创建的:

    项目Value
    电脑$1600
    手机$12
    导管$1

    设定内容居中、居左、居右

    使用:---------:居中
    使用:----------居左
    使用----------:居右

    第一列第二列第三列
    第一列文本居中第二列文本居右第三列文本居左

    SmartyPants

    SmartyPants将ASCII标点字符转换为“智能”印刷标点HTML实体。例如:

    TYPEASCIIHTML
    Single backticks'Isn't this fun?'‘Isn’t this fun?’
    Quotes"Isn't this fun?"“Isn’t this fun?”
    Dashes-- is en-dash, --- is em-dash– is en-dash, — is em-dash

    创建一个自定义列表

    Markdown
    Text-to- HTML conversion tool
    Authors
    John
    Luke

    如何创建一个注脚

    一个具有注脚的文本。1

    注释也是必不可少的

    Markdown将文本转换为 HTML

    KaTeX数学公式

    您可以使用渲染LaTeX数学表达式 KaTeX:

    Gamma公式展示 Γ ( n ) = ( n − 1 ) ! ∀ n ∈ N \Gamma(n) = (n-1)!\quad\forall n\in\mathbb N Γ(n)=(n1)!nN 是通过欧拉积分

    Γ ( z ) = ∫ 0 ∞ t z − 1 e − t d t &ThinSpace; . \Gamma(z) = \int_0^\infty t^{z-1}e^{-t}dt\,. Γ(z)=0tz1etdt.

    你可以找到更多关于的信息 LaTeX 数学表达式here.

    新的甘特图功能,丰富你的文章

    Mon 06 Mon 13 Mon 20 已完成 进行中 计划一 计划二 现有任务 Adding GANTT diagram functionality to mermaid
    • 关于 甘特图 语法,参考 这儿,

    UML 图表

    可以使用UML图表进行渲染。 Mermaid. 例如下面产生的一个序列图::

    张三 李四 王五 你好!李四, 最近怎么样? 你最近怎么样,王五? 我很好,谢谢! 我很好,谢谢! 李四想了很长时间, 文字太长了 不适合放在一行. 打量着王五... 很好... 王五, 你怎么样? 张三 李四 王五

    这将产生一个流程图。:

    链接
    长方形
    圆角长方形
    菱形
    • 关于 Mermaid 语法,参考 这儿,

    FLowchart流程图

    我们依旧会支持flowchart的流程图:

    Created with Raphaël 2.2.0 开始 我的操作 确认? 结束 yes no
    • 关于 Flowchart流程图 语法,参考 这儿.

    导出与导入

    导出

    如果你想尝试使用此编辑器, 你可以在此篇文章任意编辑。当你完成了一篇文章的写作, 在上方工具栏找到 文章导出 ,生成一个.md文件或者.html文件进行本地保存。

    导入

    如果你想加载一篇你写过的.md文件或者.html文件,在上方工具栏可以选择导入功能进行对应扩展名的文件导入,
    继续你的创作。


    1. 注脚的解释 ↩︎

    展开全文
  • /** 读取excel表数据*/public function readExcel($filename){$pathinfo = pathinfo(strtolower($filename));if($pathinfo['extension'] == 'xlsx'){$PHPReader = new PHPExcel_Reader_Excel2007(...

    include "PHPExcel.php";

    /*

    * 读取excel表数据

    */

    public function readExcel($filename){

    $pathinfo = pathinfo(strtolower($filename));

    if($pathinfo['extension'] == 'xlsx'){

    $PHPReader = new PHPExcel_Reader_Excel2007();

    //if(!$PHPReader->canRead($filePath)){

    }elseif($pathinfo['extension'] == 'xls'){

    $PHPReader = new PHPExcel_Reader_Excel5();

    }else{

    return 'not support file type';

    }

    $PHPExcel   = $PHPReader->load($filename);

    // echo is_object($PHPExcel).'mmm';exit; //查看是否初始化成功

    //获取工作表的数目

    $sheetCount = $PHPExcel->getSheetCount();

    for($i = 0; $i < $sheetCount; $i++){

    /**读取excel文件中的第一个工作表*/

    $currentSheet = $PHPExcel->getSheet($i);

    /**取得最大的列号*/

    $allColumn = $currentSheet->getHighestColumn();

    /**取得一共有多少行*/

    $allRow = $currentSheet->getHighestRow();

    /**从第二行开始输出,因为excel表中第一行为列名*/

    for($currentRow = 1; $currentRow <= $allRow; $currentRow++){

    /**从第A列开始输出*/

    $row        = [];

    for($currentColumn= 'A'; $currentColumn <= $allColumn; $currentColumn++){

    /**ord()将字符转为十进制数*/

    $val = $currentSheet->getCellByColumnAndRow(ord($currentColumn) - 65,$currentRow)->getValue();

    /**如果输出汉字有乱码,则需将输出内容用iconv函数进行编码转换,如下将gb2312编码转为utf-8编码输出*/

    //$val = $currentSheet->getCell($currentColumn.$currentRow)->getValue();

    if($val instanceof PHPExcel_RichText){//富文本转换字符串

    $val = $val->__toString();

    }

    $row[] = $val;

    }

    //echo "";

    if(!empty($row)) $sheetdata[]  = $row;

    unset($row);

    }

    $data['sheet'.$i] = $sheetdata;

    $currentSheet     = $sheetdata  = null;

    }

    return $data;

    }

    打印结果$data:

    Array

    (

    [sheet0] => Array

    (

    [0] => Array

    (

    [0] => id

    [1] => uid

    [2] => 昵称

    [3] => 状态

    [4] => 原因

    [5] => ip

    [6] => 手机

    [7] => 时间

    )

    [1] => Array

    (

    [0] => 592

    [1] => 25

    [2] => 0000null

    [3] => 正常

    [4] => 没有原因的原因

    [5] => 195.168.1.5

    [6] => 13800138000

    [7] => 2016-11-23 10:11:49

    )

    )

    [sheet1] => Array

    (

    [0] => Array

    (

    [0] => 列1

    [1] => 列2

    [2] => 列3

    )

    [1] => Array

    (

    [0] => 111值1

    [1] => 222佱2

    [2] => 333值3

    )

    )

    )

    青云大叔出品,必属精品。

    作者:OK兄 浏览次数:295

    展开全文
  • JSP导出EXCELsheet

    2018-03-08 13:50:22
    // 每个Excel页卡最大存放行数 String InsuName = "被保人清单" ; String BnfName = "受益人清单" ; HSSFWorkbook wb = new  HSSFWorkbook(); int  InsuallRowInd = 0; // 当前页卡总行数 int  BnfallRowInd =...


    %>

    <%@page contentType="text/html;charset=GBK"%>

    <%@page import="java.util.*"%>

    <%@page import="java.io.*"%>

    <%@page import="com.sinosoft.lis.pubfun.*"%>

    <%@page import="com.sinosoft.utility.*"%>

    <%@page import="org.apache.poi.hssf.usermodel.*" %>

    <%@page import=" org.apache.poi.hssf.util.*" %>

    <%

    String printoutflag = request.getParameter("printoutflag");

    String Insusql="";

    String Bnfsql="";

    String Contdition="";

      //读取Session中的全局类

    GlobalInput tG = (GlobalInput)session.getAttribute("GI");

    String Grpcontno = request.getParameter("ProposalGrpContNo");

    String tBatchNo[] = request.getParameterValues("InsuImpBatchGrid1");

    String tChk[] = request.getParameterValues("InpInsuImpBatchGridChk");

    System.out.println("printoutflag = "+printoutflag);  //batch:选择批次导出被报人安按钮   all:选择导出全部被报人按钮,不用作判断

       if(printoutflag.equals("batch")){

       Contdition+="and a.BatchNo in (";

     for(int index = 0; index < tBatchNo.length; index++)

     {

     if(tChk[index].equals("1"))

     {

        if(index == tBatchNo.length-1)

        {

        Contdition+=tBatchNo[index];

        }

        else{

        Contdition+=tBatchNo[index];

        Contdition+=",";

        }

     }

     }  

     Contdition = Contdition.substring(0, Contdition.length()-1);

     Contdition+=")";

        }

       else{

       

      Contdition += " and businessno = '"+Grpcontno+"' ";

      

       }

     

      Insusql = "select a.batchno           as 批次号,"

     +"a.SerialNo               as 被保险人序号, "

             +"(select contno from lcinsured b where b.insuredno = a.insuredno and grpcontno = a.businessno)      as 个人保单号,"

             +"a.insuredno               as 被保人客户号,"

             +"a.insuname                as 客户姓名,"

         +"(Select b.CodeName from LDCode b where b.CodeType = 'sex' and b.code = a.insusex)                as 性别,"

             +"a.insubirthday            as 出生日期,"

         +"(Select b.CodeName from LDCode b where b.CodeType = 'idtype' and b.code = a.insuidtype)             as 证件类型,"

             +"a.insuidno                as 证件号码,"       

             +"(Select b.CodeName from LDCode b where b.CodeType = 'occupationtype' and b.code = a.occupationtype)          as 职业类别,"

             +"(Select OccupationName from LDOccupation b where b.occupationcode = a.occupationcode)          as 职业代码,"

             +"a.mobile                  as 被保人移动电话,"        

             +"a.contplancode            as 保险计划,"

             +"'',"//指定生效日标志

             +"(select  b.cvalidate from lccont b where b.grpcontno = a.businessno and b.insuredno = a.insuredno)   as 个人保单生效日期, "                       

             +"(select  b.paytodate from lccont b where b.grpcontno = a.businessno and b.insuredno = a.insuredno)   as 个人保单截止日期,"

             +" '',"//有名单/无名单

             +" '' "//既往重疾理赔标记

             +"from lcgrpimportinsu  a where 1 = 1"

             +Contdition;

      

     System.out.println("Insusql="+Insusql);

     

        Bnfsql = "select a.serialno,"

       +" '' ,"//险种代码

             +"a.bnftype,"

             +"a.Name,"

             +"a.IDType,"

             +"a.IDNo,"

             +"a.Sex,"

             +"a.Birthday,"

             +"a.RelationToInsured,"

             +"a.BnfGrade,"

             +"a.BnfLot,"

             +"a.Mobile,"

             +"a.Phone "

       +" from lcgrpimportbnf a where a.serialno in(select b.serialno from lcgrpimportinsu b where b.checkstate = '3')"

       +Contdition;

      System.out.println("Bnfsql="+Bnfsql);

      

    try {

          //分sheet显示所有数据

    System.out.println("正在生成Sheet清单...");

     

    ExeSQL exeSql = new ExeSQL();

    SSRS t_SSRS = new SSRS();

        t_SSRS = exeSql.execSQL(Insusql.toString());

        SSRS s_SSRS = new SSRS();

        s_SSRS = exeSql.execSQL(Bnfsql.toString());

        

    System.out.println("被保险人最大行数"+t_SSRS.getMaxRow());

    System.out.println("受益人最大行数"+s_SSRS.getMaxRow());

     

    int maxRowNum = 65000;// 每个Excel页卡最大存放行数

    String InsuName = "被保人清单";

    String BnfName = "受益人清单";

    HSSFWorkbook wb = new HSSFWorkbook();

    int InsuallRowInd = 0;// 当前页卡总行数

    int BnfallRowInd = 0;// 当前页卡总行数

    int InsucurrSheetInd = 0;

    int BnfcurrSheetInd = 0;

    HSSFSheet Insusheet = wb.createSheet();

    HSSFSheet Bnfsheet = wb.createSheet();

    Insusheet.setSelected(true);

    Bnfsheet.setSelected(true);

    HSSFRow Insurow = null;

    HSSFRow Bnfrow = null;

    Insurow = Insusheet.createRow(InsuallRowInd++);// 添加主题

    Bnfrow = Bnfsheet.createRow(BnfallRowInd++);// 添加主题

    HSSFFont fontTitle = wb.createFont();//标题样式

    fontTitle.setFontHeightInPoints((short) 10);

    fontTitle.setFontName("宋体");

    fontTitle.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

    HSSFFont fontValue = wb.createFont();//边框样式

    fontValue.setFontHeightInPoints((short) 10);

    fontValue.setFontName("宋体");

    fontValue.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);

    HSSFCellStyle styleBorderTitle = wb.createCellStyle();

    styleBorderTitle.setBorderBottom(HSSFCellStyle.BORDER_THIN);

    styleBorderTitle.setBorderLeft(HSSFCellStyle.BORDER_THIN);

    styleBorderTitle.setBorderRight(HSSFCellStyle.BORDER_THIN);

    styleBorderTitle.setBorderTop(HSSFCellStyle.BORDER_THIN);

    styleBorderTitle.setFillForegroundColor(HSSFColor.YELLOW.index);

    styleBorderTitle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

    styleBorderTitle.setAlignment(HSSFCellStyle.ALIGN_CENTER);

    styleBorderTitle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

    styleBorderTitle.setWrapText(true);

    styleBorderTitle.setFont(fontTitle);

    HSSFCellStyle styleBorderValue = wb.createCellStyle();

    styleBorderValue.setBorderBottom(HSSFCellStyle.BORDER_THIN);

    styleBorderValue.setBorderLeft(HSSFCellStyle.BORDER_THIN);

    styleBorderValue.setBorderRight(HSSFCellStyle.BORDER_THIN);

    styleBorderValue.setBorderTop(HSSFCellStyle.BORDER_THIN);

    styleBorderValue.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index);

    styleBorderValue.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

    styleBorderValue.setAlignment(HSSFCellStyle.ALIGN_CENTER);

    styleBorderValue.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

    styleBorderValue.setWrapText(true);

    styleBorderValue.setFont(fontValue);

    HSSFCell Insucell = Insurow.createCell((short) 0);

    HSSFCell Bnfcell = Bnfrow.createCell((short) 0);

    Insucell.setCellType(HSSFCell.CELL_TYPE_STRING);

    Insucell.setCellStyle(styleBorderTitle);

    Insucell.setEncoding(HSSFCell.ENCODING_UTF_16);

    Insurow.setHeight((short)(20*30));

    Bnfcell.setCellType(HSSFCell.CELL_TYPE_STRING);

    Bnfcell.setCellStyle(styleBorderTitle);

    Bnfcell.setEncoding(HSSFCell.ENCODING_UTF_16);

    Bnfrow.setHeight((short)(20*30));

    int t = 0;

    int s = 0;

    //18

    Insucell = Insurow.createCell((short) t++);

    Insucell.setCellType(HSSFCell.CELL_TYPE_STRING);

    Insucell.setCellStyle(styleBorderTitle);

    Insucell.setEncoding(HSSFCell.ENCODING_UTF_16);

    Insucell.setCellValue("批次号");

    Insucell = Insurow.createCell((short) t++);

    Insucell.setCellType(HSSFCell.CELL_TYPE_STRING);

    Insucell.setCellStyle(styleBorderTitle);

    Insucell.setEncoding(HSSFCell.ENCODING_UTF_16);

    Insucell.setCellValue("被保险人序号");

    Insucell = Insurow.createCell((short) t++);

    Insucell.setCellType(HSSFCell.CELL_TYPE_STRING);

    Insucell.setCellStyle(styleBorderTitle);

    Insucell.setEncoding(HSSFCell.ENCODING_UTF_16);

    Insucell.setCellValue("个人保单号");

    Insucell = Insurow.createCell((short) t++);

    Insucell.setCellType(HSSFCell.CELL_TYPE_STRING);

    Insucell.setCellStyle(styleBorderTitle);

    Insucell.setEncoding(HSSFCell.ENCODING_UTF_16);

    Insucell.setCellValue("客户号");

    Insucell = Insurow.createCell((short) t++);

    Insucell.setCellType(HSSFCell.CELL_TYPE_STRING);

    Insucell.setCellStyle(styleBorderTitle);

    Insucell.setEncoding(HSSFCell.ENCODING_UTF_16);

    Insucell.setCellValue("客户姓名");

    Insucell = Insurow.createCell((short) t++);

    Insucell.setCellType(HSSFCell.CELL_TYPE_STRING);

    Insucell.setCellStyle(styleBorderTitle);

    Insucell.setEncoding(HSSFCell.ENCODING_UTF_16);

    Insucell.setCellValue("性别");

    Insucell = Insurow.createCell((short) t++);

    Insucell.setCellType(HSSFCell.CELL_TYPE_STRING);

    Insucell.setCellStyle(styleBorderTitle);

    Insucell.setEncoding(HSSFCell.ENCODING_UTF_16);

    Insucell.setCellValue("出生日期");

    Insucell = Insurow.createCell((short) t++);

    Insucell.setCellType(HSSFCell.CELL_TYPE_STRING);

    Insucell.setCellStyle(styleBorderTitle);

    Insucell.setEncoding(HSSFCell.ENCODING_UTF_16);

    Insucell.setCellValue("证件类型");

    Insucell = Insurow.createCell((short) t++);

    Insucell.setCellType(HSSFCell.CELL_TYPE_STRING);

    Insucell.setCellStyle(styleBorderTitle);

    Insucell.setEncoding(HSSFCell.ENCODING_UTF_16);

    Insucell.setCellValue("证件号码");

    Insucell = Insurow.createCell((short) t++);

    Insucell.setCellType(HSSFCell.CELL_TYPE_STRING);

    Insucell.setCellStyle(styleBorderTitle);

    Insucell.setEncoding(HSSFCell.ENCODING_UTF_16);

    Insucell.setCellValue("职业类别");

    Insucell = Insurow.createCell((short) t++);

    Insucell.setCellType(HSSFCell.CELL_TYPE_STRING);

    Insucell.setCellStyle(styleBorderTitle);

    Insucell.setEncoding(HSSFCell.ENCODING_UTF_16);

    Insucell.setCellValue("职业代码");

    Insucell = Insurow.createCell((short) t++);

    Insucell.setCellType(HSSFCell.CELL_TYPE_STRING);

    Insucell.setCellStyle(styleBorderTitle);

    Insucell.setEncoding(HSSFCell.ENCODING_UTF_16);

    Insucell.setCellValue("移动电话");

    Insucell = Insurow.createCell((short) t++);

    Insucell.setCellType(HSSFCell.CELL_TYPE_STRING);

    Insucell.setCellStyle(styleBorderTitle);

    Insucell.setEncoding(HSSFCell.ENCODING_UTF_16);

    Insucell.setCellValue("保险计划");

    Insucell = Insurow.createCell((short) t++);

    Insucell.setCellType(HSSFCell.CELL_TYPE_STRING);

    Insucell.setCellStyle(styleBorderTitle);

    Insucell.setEncoding(HSSFCell.ENCODING_UTF_16);

    Insucell.setCellValue("指定生效日标志");

    Insucell = Insurow.createCell((short) t++);

    Insucell.setCellType(HSSFCell.CELL_TYPE_STRING);

    Insucell.setCellStyle(styleBorderTitle);

    Insucell.setEncoding(HSSFCell.ENCODING_UTF_16);

    Insucell.setCellValue("个人保单生效日期");

    Insucell = Insurow.createCell((short) t++);

    Insucell.setCellType(HSSFCell.CELL_TYPE_STRING);

    Insucell.setCellStyle(styleBorderTitle);

    Insucell.setEncoding(HSSFCell.ENCODING_UTF_16);

    Insucell.setCellValue("个人保单截止日期");

    Insucell = Insurow.createCell((short) t++);

    Insucell.setCellType(HSSFCell.CELL_TYPE_STRING);

    Insucell.setCellStyle(styleBorderTitle);

    Insucell.setEncoding(HSSFCell.ENCODING_UTF_16);

    Insucell.setCellValue("有名单/无名单");

    Insucell = Insurow.createCell((short) t++);

    Insucell.setCellType(HSSFCell.CELL_TYPE_STRING);

    Insucell.setCellStyle(styleBorderTitle);

    Insucell.setEncoding(HSSFCell.ENCODING_UTF_16);

    Insucell.setCellValue("既往重疾理赔标记");

    Insucell = Insurow.createCell((short) t++);

    Insucell.setCellType(HSSFCell.CELL_TYPE_STRING);

    Insucell.setCellStyle(styleBorderTitle);

    Insucell.setEncoding(HSSFCell.ENCODING_UTF_16);

    //受益人 13

    Bnfcell = Bnfrow.createCell((short) s++);

    Bnfcell.setCellType(HSSFCell.CELL_TYPE_STRING);

    Bnfcell.setCellStyle(styleBorderTitle);

    Bnfcell.setEncoding(HSSFCell.ENCODING_UTF_16);

    Bnfcell.setCellValue("被保险人序号");

    Bnfcell = Bnfrow.createCell((short) s++);

    Bnfcell.setCellType(HSSFCell.CELL_TYPE_STRING);

    Bnfcell.setCellStyle(styleBorderTitle);

    Bnfcell.setEncoding(HSSFCell.ENCODING_UTF_16);

    Bnfcell.setCellValue("险种代码");

    Bnfcell = Bnfrow.createCell((short) s++);

    Bnfcell.setCellType(HSSFCell.CELL_TYPE_STRING);

    Bnfcell.setCellStyle(styleBorderTitle);

    Bnfcell.setEncoding(HSSFCell.ENCODING_UTF_16);

    Bnfcell.setCellValue("受益人类型");

    Bnfcell = Bnfrow.createCell((short) s++);

    Bnfcell.setCellType(HSSFCell.CELL_TYPE_STRING);

    Bnfcell.setCellStyle(styleBorderTitle);

    Bnfcell.setEncoding(HSSFCell.ENCODING_UTF_16);

    Bnfcell.setCellValue("受益人姓名");

    Bnfcell = Bnfrow.createCell((short) s++);

    Bnfcell.setCellType(HSSFCell.CELL_TYPE_STRING);

    Bnfcell.setCellStyle(styleBorderTitle);

    Bnfcell.setEncoding(HSSFCell.ENCODING_UTF_16);

    Bnfcell.setCellValue("证件类型");

    Bnfcell = Bnfrow.createCell((short) s++);

    Bnfcell.setCellType(HSSFCell.CELL_TYPE_STRING);

    Bnfcell.setCellStyle(styleBorderTitle);

    Bnfcell.setEncoding(HSSFCell.ENCODING_UTF_16);

    Bnfcell.setCellValue("证件号码");

    Bnfcell = Bnfrow.createCell((short) s++);

    Bnfcell.setCellType(HSSFCell.CELL_TYPE_STRING);

    Bnfcell.setCellStyle(styleBorderTitle);

    Bnfcell.setEncoding(HSSFCell.ENCODING_UTF_16);

    Bnfcell.setCellValue("性别");

    Bnfcell = Bnfrow.createCell((short) s++);

    Bnfcell.setCellType(HSSFCell.CELL_TYPE_STRING);

    Bnfcell.setCellStyle(styleBorderTitle);

    Bnfcell.setEncoding(HSSFCell.ENCODING_UTF_16);

    Bnfcell.setCellValue("出生日期");

    Bnfcell = Bnfrow.createCell((short) s++);

    Bnfcell.setCellType(HSSFCell.CELL_TYPE_STRING);

    Bnfcell.setCellStyle(styleBorderTitle);

    Bnfcell.setEncoding(HSSFCell.ENCODING_UTF_16);

    Bnfcell.setCellValue("与被保人关系");

    Bnfcell = Bnfrow.createCell((short) s++);

    Bnfcell.setCellType(HSSFCell.CELL_TYPE_STRING);

    Bnfcell.setCellStyle(styleBorderTitle);

    Bnfcell.setEncoding(HSSFCell.ENCODING_UTF_16);

    Bnfcell.setCellValue("受益级别");

    Bnfcell = Bnfrow.createCell((short) s++);

    Bnfcell.setCellType(HSSFCell.CELL_TYPE_STRING);

    Bnfcell.setCellStyle(styleBorderTitle);

    Bnfcell.setEncoding(HSSFCell.ENCODING_UTF_16);

    Bnfcell.setCellValue("受益份额");

    Bnfcell = Bnfrow.createCell((short) s++);

    Bnfcell.setCellType(HSSFCell.CELL_TYPE_STRING);

    Bnfcell.setCellStyle(styleBorderTitle);

    Bnfcell.setEncoding(HSSFCell.ENCODING_UTF_16);

    Bnfcell.setCellValue("手机");

    Bnfcell = Bnfrow.createCell((short) s++);

    Bnfcell.setCellType(HSSFCell.CELL_TYPE_STRING);

    Bnfcell.setCellStyle(styleBorderTitle);

    Bnfcell.setEncoding(HSSFCell.ENCODING_UTF_16);

    Bnfcell.setCellValue("联系电话");

      if(t_SSRS.getMaxRow() > 0)

    {

      int count = 1 ;

    for(int k = 1 ;k<=t_SSRS.getMaxRow();k++)

    {

    // 判断是否需要添加Excel页卡

    if(InsuallRowInd >= maxRowNum)

    {

    InsuallRowInd = 0;

    Insusheet.setSelected(false);

    // 创建新的页卡

    Insusheet = wb.createSheet("被保人清单");

    wb.setSheetName(InsucurrSheetInd++, InsuName, HSSFWorkbook.ENCODING_UTF_16);

    Insusheet.setSelected(true);

    }

    Insurow = Insusheet.createRow(InsuallRowInd++);

    Insurow.setHeight((short)(20*15));

    for(int m = 1;m<=18;m++){

        Insucell = Insurow.createCell((short) (m-1));

    Insucell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);

    Insucell.setCellStyle(styleBorderValue);

    Insucell.setEncoding(HSSFCell.ENCODING_UTF_16);

    Insucell.setCellValue(t_SSRS.GetText(k, m));

    }

    // 调整列宽

    Insusheet.setColumnWidth((short) 0, (short) (256*10));

    Insusheet.setColumnWidth((short) 1, (short) (256*20));

    Insusheet.setColumnWidth((short) 2, (short) (256*20));

    Insusheet.setColumnWidth((short) 3, (short) (256*20));

    Insusheet.setColumnWidth((short) 4, (short) (256*20));

    Insusheet.setColumnWidth((short) 5, (short) (256*20));

    Insusheet.setColumnWidth((short) 6, (short) (256*20));

    Insusheet.setColumnWidth((short) 7, (short) (256*20));

    Insusheet.setColumnWidth((short) 8, (short) (256*12));

    Insusheet.setColumnWidth((short) 9, (short) (256*12));

    Insusheet.setColumnWidth((short) 10, (short) (256*12));

    Insusheet.setColumnWidth((short) 11, (short) (256*20));

    Insusheet.setColumnWidth((short) 12, (short) (256*20));

    Insusheet.setColumnWidth((short) 13, (short) (256*20));

    Insusheet.setColumnWidth((short) 14, (short) (256*20));

    Insusheet.setColumnWidth((short) 15, (short) (256*20));

    Insusheet.setColumnWidth((short) 16, (short) (256*12));

    Insusheet.setColumnWidth((short) 17, (short) (256*20));

    }

    }

    // 调整列宽

    wb.setSheetName(0, InsuName, HSSFWorkbook.ENCODING_UTF_16);

    Insusheet.setColumnWidth((short) 0, (short) (256*12));

    Insusheet.setColumnWidth((short) 1, (short) (256*20));

    Insusheet.setColumnWidth((short) 2, (short) (256*20));

    Insusheet.setColumnWidth((short) 3, (short) (256*20));

    Insusheet.setColumnWidth((short) 4, (short) (256*20));

    Insusheet.setColumnWidth((short) 5, (short) (256*20));

    Insusheet.setColumnWidth((short) 6, (short) (256*20));

    Insusheet.setColumnWidth((short) 7, (short) (256*20));

    Insusheet.setColumnWidth((short) 8, (short) (256*12));

    Insusheet.setColumnWidth((short) 9, (short) (256*12));

    Insusheet.setColumnWidth((short) 10, (short) (256*12));

    Insusheet.setColumnWidth((short) 11, (short) (256*20));

    Insusheet.setColumnWidth((short) 12, (short) (256*20));

    Insusheet.setColumnWidth((short) 13, (short) (256*20));

    Insusheet.setColumnWidth((short) 14, (short) (256*20));

    Insusheet.setColumnWidth((short) 15, (short) (256*20));

    Insusheet.setColumnWidth((short) 16, (short) (256*12));

    Insusheet.setColumnWidth((short) 17, (short) (256*20));

     

    Insusheet.setSelected(false);

    if(s_SSRS.getMaxRow() >= 0)

    {

      int count = 1 ;

    for(int k = 1 ;k<=s_SSRS.getMaxRow();k++)

    {

    // 判断是否需要添加Excel页卡

    if(BnfallRowInd >= maxRowNum)

    {

    BnfallRowInd = 0;

    Bnfsheet.setSelected(false);

    // 创建新的页卡

    Bnfsheet = wb.createSheet("受益人清单");

    wb.setSheetName(BnfcurrSheetInd, InsuName, HSSFWorkbook.ENCODING_UTF_16);

    Bnfsheet.setSelected(true);

    }

    Bnfrow = Bnfsheet.createRow(BnfallRowInd++);

    Bnfrow.setHeight((short)(20*15));

    for(int m = 1;m<=13;m++){

         System.out.println("m=="+m);

        Bnfcell = Bnfrow.createCell((short) (m-1));

    Bnfcell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);

    Bnfcell.setCellStyle(styleBorderValue);

    Bnfcell.setEncoding(HSSFCell.ENCODING_UTF_16);

    Bnfcell.setCellValue(s_SSRS.GetText(k, m));

    }

    // 调整列宽

    Bnfsheet.setColumnWidth((short) 0, (short) (256*10));

    Bnfsheet.setColumnWidth((short) 1, (short) (256*20));

    Bnfsheet.setColumnWidth((short) 2, (short) (256*20));

    Bnfsheet.setColumnWidth((short) 3, (short) (256*20));

    Bnfsheet.setColumnWidth((short) 4, (short) (256*20));

    Bnfsheet.setColumnWidth((short) 5, (short) (256*20));

    Bnfsheet.setColumnWidth((short) 6, (short) (256*20));

    Bnfsheet.setColumnWidth((short) 7, (short) (256*20));

    Bnfsheet.setColumnWidth((short) 8, (short) (256*12));

    Bnfsheet.setColumnWidth((short) 9, (short) (256*12));

    Bnfsheet.setColumnWidth((short) 10, (short) (256*12));

    Bnfsheet.setColumnWidth((short) 11, (short) (256*20));

    Bnfsheet.setColumnWidth((short) 12, (short) (256*20));

    }

    }

    // 调整列宽

    wb.setSheetName(1,BnfName , HSSFWorkbook.ENCODING_UTF_16);

    Bnfsheet.setColumnWidth((short) 0, (short) (256*12));

    Bnfsheet.setColumnWidth((short) 1, (short) (256*20));

    Bnfsheet.setColumnWidth((short) 2, (short) (256*20));

    Bnfsheet.setColumnWidth((short) 3, (short) (256*20));

    Bnfsheet.setColumnWidth((short) 4, (short) (256*20));

    Bnfsheet.setColumnWidth((short) 5, (short) (256*20));

    Bnfsheet.setColumnWidth((short) 6, (short) (256*20));

    Bnfsheet.setColumnWidth((short) 7, (short) (256*20));

    Bnfsheet.setColumnWidth((short) 8, (short) (256*12));

    Bnfsheet.setColumnWidth((short) 9, (short) (256*12));

    Bnfsheet.setColumnWidth((short) 10, (short) (256*12));

    Bnfsheet.setColumnWidth((short) 11, (short) (256*20));

    Bnfsheet.setColumnWidth((short) 12, (short) (256*20));

    Bnfsheet.setSelected(false);

    System.out.println("校验失败被保人Sheet清单生成完毕...");

    response.reset();

    response.setContentType("application/octet-stream");

    response.setHeader("Content-Disposition","attachment; filename = "+tBatchNo+".xls");

    OutputStream outOS = response.getOutputStream();

    wb.write(outOS);

    outOS.flush();

    outOS.close();

    response.flushBuffer();

    out.clear();

    out = pageContext.pushBody();

    } catch (Exception e) {

    System.out.println(e.getMessage());

    e.printStackTrace();

    System.out.println("导出Excel失败!");

    }

    %>

     

     


    展开全文
  • Excel Sheet Column Title 题目:https://leetcode.com/problems/excel-sheet-column-title/ 把数字转化为Excel 的列====本质等于把10进制的数转化为26进制的字符串 public static void main(String[] args) { ...

    Excel Sheet Column Title 题目:https://leetcode.com/problems/excel-sheet-column-title/

    把数字转化为Excel 的列====本质等于把10进制的数转化为26进制的字符串

     

    public static void main(String[] args) {
    		int n=26;
    		String s = convertToTitle(n);
    		System.out.println(s);
    	}
    	public static  String convertToTitle(int n) {
    		String str="";
    		while(n>0){
    			int num=(n-1)%26;
    			str=(char)('A'+num)+str;
    			n=(n-1)/26;
    		}
    		return str;
    	}

     

    展开全文
  • java 导出excel到多个sheet

    万次阅读 2019-03-01 17:03:44
    java 导出excel 多个sheet 废话不多说了,直接上代码,有需要的盆友,可以看看。@TOC @RequestMapping("/exportAllyScoreCount") public void exportAllyScoreCount(HttpServletRequest request,...
  • Given a positive integer, return its corresponding column title as appear in an Excel sheet. For example: 1 -> A 2 -> B 3 -> C ... 26 -> Z 27 -> AA 28 -> AB Credits
  • Excel单页导出以及多sheet导出

    千次阅读 2019-08-03 14:25:43
    功能一.Excel单页导出,即将所有的数据导入一个sheet页 导出Excel的主方法,所需参数只需要有request和response即可,另外两个是我为了查询写入Excel数据而加入的(一个是我的service,一个是我的实体类),使用时...
  • CatalogExcelUtil.initCell(row.createCell(4), style, "手机号"); CatalogExcelUtil.initCell(row.createCell(5), style, "职务"); CatalogExcelUtil.initCell(row.createCell(6), style, "部门"); //行高 ...
  • 一段用以在导出操作时生成多个sheet页的代码。 1.controller /** * 导出方法 **/ @ApiOperation(value = "盘点—导出", notes = "盘点—导出") @ResponseBody @GetMapping(value = "/export", produces = ...
  • php 导出 excel 导出多个sheet

    千次阅读 2019-09-09 09:44:02
    由来:公司是考务公司,做考试系统的,...一个个导出的话,如果考试人数很多,一个个导出再打印会很麻烦,效率特别低,所以我写了一个程序,导出的签到表,张贴表可以按照考点导出一个excel,这个excel内是多个she...
  • 【easypoi】 实现excel导入导出(多sheet

    万次阅读 热门讨论 2019-07-22 16:47:01
    /** * 手机 */ @Excel(name = "手机",orderNum = "3",width = 20,groupName = "联系方式") private String mobilePhone; /** * 邮箱 */ @Excel(name = "邮箱",orderNum = "4",width = 20,groupName = "联系方式", ...
  • /** * 导出-- 查看筛选条件下:查看当前活动抽奖名单 * @param request * @param response * @throws ... //headers表示excel表中第一行的表头 HSSFRow row = sheet.createRow(0); //在excel表中添加表头 for(int i=0;i
  • 如何移动sheet,对于不会的人,这是一个好问题, 也许你经过多次尝试都没有成功。 下面给出方案。 方案 http://blog.163.com/yj_yuan/blog/static/110842720102315216988/ 步骤就是, 1、 双击目标excel打...
  • 本项目基于SSM框架,简单封装了Excel批量导入导出功能,不用建数据库表一键导入导出Excel,不过这样只适用于对导入的Excel表进行转换,通过模板导出Excel并且能实现导入多个sheet表。上一篇介绍了建表导入导出Excel...
  • 下载sheet.js github官方地址 : https://github.com/SheetJS/js-xlsx 依赖js function openDownloadDialog(url, saveName) { if (typeof url == 'object' && url instanceof Blob) { url = URL....
  • 我们在做性能测试的时候,需要造很多数据来做的,比如登陆接口,造上万条、十万条手机号用户都有可能,现在来说一下如何用python来生成随机手机号,并把手机号和密码写入excel表中。 手机号是前面三位比如131,132,...
  • = {"姓名","性别","生日","手机","部门","职务","公司","公司地址","公司网站","兴趣爱好","备注","创建日期","客户简称","归属人","角色关系","亲密程度" }; // 对象 ExcelExp e1 = new ExcelExp("我的...
  • Excel表格导出,java代码,采用反射加注解的方式获得excel表格中的数据 maven所依赖: org.apache.poi poi-ooxml 3.11-beta1 1.java注解: /** * ClassName:Excel Function: Excel导出列头标记通用...
  • Java中添加从Excel表格中导入数据和将数据导出到Excel表格并下载 jsp页面 总共需要添加四个按钮: 1.选择导入的Excel 文件按钮 2.上传按钮 3.关闭按钮 4.导出Excel文件按钮 上传文件时我利用了表单来导入,在用户...
  • Excel表格导出

    2019-05-25 17:36:52
    员工数据导出到excel表格,导出前要对数据处理,因为一个项目可以导出的数据比较多,在导出前可以先对数据的查询筛选,把需要的数据导出,这样减少了不必要的数据出现。 //导出数据到Excel表格 function ...
  • Android数据转化为Excel表格导入导出

    万次阅读 2016-03-08 14:20:50
    Android数据转化为Excel表格导入导出
  • POI导出数据到Excel的多个Sheet

    万次阅读 2016-04-26 10:10:39
    //删除exporting cookie workbook.write(os); os.flush(); }catch(Exception e){ e.printStackTrace(); } finally { try { os.close(); } catch (IOException e) { e.printStackTrace(); } ...
  • 表格 sheet2'); // XLSX.utils.book_append_sheet(wb, sheet2, '表格 sheet2'); const workbookBlob = workbook2blob(wb); // 导出最后的总表 openDownloadDialog(workbookBlob, '收件人...
  • ws3_new = wb_new.create_sheet("Mysheet", 2) #设定sheet的插入位置 默认插在后面 ws3_new.title = u"要客20人以上集团详情" #设定一个sheet的名字 必须是Unicode max_row=ws.max_row#最大行数 max_column=ws....
  • java导入导出excel表格

    2020-07-07 17:53:03
    java导入导出excel表格 一.导出 public void poiExport(Long[] ids, HttpServletRequest request, HttpServletResponse response) { String orderBatchSn = ""; // 创建HSSFWorkbook对象(excel的文档对象) ...
  • HSSFWorkbook的excel表格的导出 导入jar包 jar包下载 链接:https://pan.baidu.com/s/1LcaOhETqJTGTTc5O2EaFUA 提取码:bux1 复制这段内容后打开百度网盘手机App,操作更方便哦 jsp页面设置 (jdbc很简单...
  • hadoop操作excel表格

    千次阅读 2019-06-09 21:59:59
    Excel 中解析通话记录统计 现在假设数据存在于Excel 文件中。具体的实现方案为: 1、添加POI 解析Excel文件。 2、通过实现FileInputFormat 自定义文件输入流。 3、可以通过文件分片将不同的输出输出到不同的...
  • python对Excel表格操作

    2019-04-19 11:29:00
    操作场景,给一个Excel表格随机生成10万个手机号码 python中常见的对Excel操作模块 xlwt module 将数据写入Excel表 xlrd module 读取Excel表格 xlsxwriter modole xlwt模块的升级版,用于处理大量数据 先用xlwt...
  • php导出excel表格

    2016-06-12 10:13:18
    /*** **php导出excel表格 **/ /** Error reporting */ error_reporting(E_ALL & ~E_NOTICE); date_default_timezone_set('Europe/London'); /** PHPExcel */ require_once '../export/php
  • 在assets下放的excel表格,命名为excel.xls2.添加读取excel需要的jar包”jxl.jar”,assets是建在main级别目录下,建错地方找不到文件3.读取excel中的文件/*获取excel表格中的数据不能在主线程中调用xlsName 为表格的...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 4,528
精华内容 1,811
关键字:

手机excel怎么删除sheet