精华内容
下载资源
问答
  • 最近做的一个ITFIN的项目,后台需要用POI实现导出功能,导出的数据中有文本格式,也货币格式,所以为了方便将来导出的表格做计算,存放货币的单元格需要设置为数值类型。  导出的Excel单元格都是文本...

    最近做的一个ITFIN的项目中,后台需要用POI实现导出功能,导出的数据中有文本格式,也有货币格式,所以为了方便在将来导出的表格中做计算,存放货币的单元格需要设置为数值类型。

      导出的Excel的单元格都是文本格式(单元格左上角有个小三角):

    这里写图片描述

      费了不少功夫,终于把“小三角”去掉了,这里总结并分享一下问题的解决方法。 

      通过poi导出excel的过程大致是这样的:

         规定单元格的格式 
            ↓ 
          创建单元格 
            ↓ 
         设置单元格的格式 
            ↓ 
         设置数据的格式 
            ↓ 
        把数据存放到单元格中 
            ↓ 
          通过IO流输出


    背景POI导出Excel时设置单元格类型为数值类型


      要想存放数值的单元格以数值类型导出,其中最关键的步骤就是上面加粗的两步,设置单元格的格式和向单元格中存放数据。

      核心代码如下:

        /**
         * 导出Excel-胡玉洋-2015年11月11日
         * 
         *@param outPutParam Excel数据实体,包括要导出的excel标头、列标题、数据等
         * */
        private void createContentRows(ExcelParam outPutParam) {
            HSSFWorkbook workbook=new HSSFWorkbook(); //创建一个Excel文件
            // 遍历集合数据,产生数据行
            for (int i = 0; i < outPutParam.getContent().size(); i++) {
                int rowIndex = i + 2;
                HSSFRow contentRow = sheet.createRow(rowIndex);
                Map<String, Object> rowDate = outPutParam.getContent().get(i);
                //遍历列
                for (int j = 0; j < outPutParam.getTitleList().size(); j++) {       
                    Title headTitle = outPutParam.getTitleList().get(j);//获取第i行第j列列标题
                    String headerName = headTitle.getName();//获取第j列列标识
                    Object data = rowDate.get(headerName);//获取第i行第j列所放数据
                    HSSFCellStyle contextstyle =workbook.createCellStyle();
                    HSSFCell contentCell = contentRow.createCell(j);                
                    Boolean isNum = false;//data是否为数值型
                    Boolean isInteger=false;//data是否为整数
                    Boolean isPercent=false;//data是否为百分数
                    if (data != null || "".equals(data)) {
                        //判断data是否为数值型
                        isNum = data.toString().matches("^(-?\\d+)(\\.\\d+)?$");
                        //判断data是否为整数(小数部分是否为0)
                        isInteger=data.toString().matches("^[-\\+]?[\\d]*$");
                        //判断data是否为百分数(是否包含“%”)
                        isPercent=data.toString().contains("%");
                    }
    
                    //如果单元格内容是数值类型,涉及到金钱(金额、本、利),则设置cell的类型为数值型,设置data的类型为数值类型
                    if (isNum && !isPercent) {
                        HSSFDataFormat df = workbook.createDataFormat(); // 此处设置数据格式
                        if (isInteger) {
                            contextstyle.setDataFormat(df.getBuiltinFormat("#,#0"));//数据格式只显示整数
                        }else{
                            contextstyle.setDataFormat(df.getBuiltinFormat("#,##0.00"));//保留两位小数点
                        }                   
                        // 设置单元格格式
                        contentCell.setCellStyle(contextstyle);
                        // 设置单元格内容为double类型
                        contentCell.setCellValue(Double.parseDouble(data.toString()));
                    } else {
                        contentCell.setCellStyle(contextstyle);
                        // 设置单元格内容为字符型
                        contentCell.setCellValue(data.toString());
                    }
                }
            }
        }
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51

      如上,有两个比较重要的点: 
      1、先用正则表达式判断数据是否为数值型,如果为数值型,则设置单元格格式为整数或者小数; 
      2、然后往单元格中存放数据的时候要设置数据的格式为double类型,如果查看poi的源码HSSFCell.java会发现设置数据的方法如下,所以用setCellValue(double)方法即可。

       这里写图片描述


    优化


      到了这里,您可能以为万事大吉啊了,其实上面的代码有个陷阱,如果不经过大数据量的测试是发觉不出来的哦~~

      如果数据量大的话,系统可能会报错“The maximum number of cell styles was exceeded. You can define up to 4000 styles in a .xls workbook”,原因是style创建的次数太多了,解决这个问题的方法很简单,在循环体外面创建单元格格式contextstyle(即把它当成一个“全局”变量),不要在循环内部创建。

      正确的代码如下:

        /**
         * 导出Excel-胡玉洋-2015年11月11日
         * 
         *@param outPutParam Excel数据实体,包括要导出的excel标头、列标题、数据等
         * */
        private void createContentRows(ExcelParam outPutParam) {
            HSSFWorkbook workbook=new HSSFWorkbook(); //创建一个Excel文件
            HSSFCellStyle contextstyle =workbook.createCellStyle();
            // 遍历集合数据,产生数据行
            for (int i = 0; i < outPutParam.getContent().size(); i++) {
                int rowIndex = i + 2;
                HSSFRow contentRow = sheet.createRow(rowIndex);
                Map<String, Object> rowDate = outPutParam.getContent().get(i);
                //遍历列
                for (int j = 0; j < outPutParam.getTitleList().size(); j++) {       
                    Title headTitle = outPutParam.getTitleList().get(j);//获取第i行第j列列标题
                    String headerName = headTitle.getName();//获取第j列列标识
                    Object data = rowDate.get(headerName);//获取第i行第j列所放数据
                    HSSFCell contentCell = contentRow.createCell(j);                
                    Boolean isNum = false;//data是否为数值型
                    Boolean isInteger=false;//data是否为整数
                    Boolean isPercent=false;//data是否为百分数
                    if (data != null || "".equals(data)) {
                        //判断data是否为数值型
                        isNum = data.toString().matches("^(-?\\d+)(\\.\\d+)?$");
                        //判断data是否为整数(小数部分是否为0)
                        isInteger=data.toString().matches("^[-\\+]?[\\d]*$");
                        //判断data是否为百分数(是否包含“%”)
                        isPercent=data.toString().contains("%");
                    }
    
                    //如果单元格内容是数值类型,涉及到金钱(金额、本、利),则设置cell的类型为数值型,设置data的类型为数值类型
                    if (isNum && !isPercent) {
                        HSSFDataFormat df = workbook.createDataFormat(); // 此处设置数据格式
                        if (isInteger) {
                            contextstyle.setDataFormat(df.getBuiltinFormat("#,#0"));//数据格式只显示整数
                        }else{
                            contextstyle.setDataFormat(df.getBuiltinFormat("#,##0.00"));//保留两位小数点
                        }                   
                        // 设置单元格格式
                        contentCell.setCellStyle(contextstyle);
                        // 设置单元格内容为double类型
                        contentCell.setCellValue(Double.parseDouble(data.toString()));
                    } else {
                        contentCell.setCellStyle(contextstyle);
                        // 设置单元格内容为字符型
                        contentCell.setCellValue(data.toString());
                    }
                }
            }
        }
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51

      最后导出的正确格式:

    这里写图片描述

    展开全文
  • 你好,我是小干部。上回我们学习了excel 的公式组成以及编辑。这次接着往下学。一、数据的分类 excel的数据可以分为... 在Excel中,身份证号一般就记录为文本类型。因为身份证号18位,保存为数值类型后,后三位...

    0034f4d1154e9fd31b9c71d26cdae301.png

    你好,我是小干部。

    上回我们学习了excel 的公式组成以及编辑。这次接着往下学。

    一、数据的分类

          excel的数据可以分为以下5类:文本数值日期和时间逻辑值错误值

          其中:

    1.  文本不参与数学运算。是一种文字信息。文本类型的数据我们也可以称之为字符串或文本字符串。

            在Excel中,身份证号一般就记录为文本类型。因为身份证号有18位,保存为数值类型后,后三位的数字会变为0,导致信息的丢失。

    2.  数值就是可以进行数学运算的数据。或者说,需要进行数学运算的数据,都要保存为数值类型。

    3.  日期值和时间值在Excel中被存储为数值形式,拥有数值所具有的一切运算功能。

        4.  逻辑值只有“TRUE”和“FALSE”两种。TRUE为真,FALSE为假。

        5.  错误值大多是由于Excel公式的计算错误产生,共有8种类型。

    excel的错误值

    错误原因

    #DIV/0!

    在公式中将0作为了除数,出现此错误值

    #VALUE!

    当单元格中所使用的函数参数或操作数类型出现错误时,出现此错误值

    #N/A

    当数值对函数或公式不可用时,出现此错误值

    #NUM!

    如果公式或者函数中使用了无效数值,出现此错误值

    #REF!

    当单元格引用无效时将显示该错误

    #NAME?

    在公式中使用了excel不能识别的文本时,出现此错误值

    #NULL!

    当指定了两个并不相交的区域的交点时,出现此错误

    #########

    当单元格中数值的长度大于单元格列宽时   或者   日期时间公式出现负值时,出现此错误值

                                              表一  Excel错误值

    二、公式中的运算符

    1.  算术运算符算术运算符包括加(+)、减(-)、乘(*)、除(/)、乘幂(^)、百分号(%)等,执行算术运算返回的结果只能是数值类型的数据。

    2.  比较运算符比较运算符包括等于(=)、不等于(<>)、大于(>)、小于(=)、小于等于(<=)等,执行比较运算返回的结果只能是逻辑值“TRUE”或“FALSE” 。

      在Excel的所有数据类型中,数值最小,文本比数值大,最大的是逻辑值TRUE。而日期在excel中保存为数值,是数值的特殊显示样式。数值有多大,对应的日期就有多大。错误值与任何数据进行运算,都返回该错误。

      7793e934644f11e22082da54aa1c2fcf.png

                                           图一   数据的大小

    3. 文本运算符文本运算符只有一个“&”。用于将两个数据合并为一个文本类型的数据。

        4.  引用运算符

    当在公式中引用一个单元格区域的时候,可能会用到引用运算符。excel中引用运算符有3个:冒号(:)、单个空格、逗号(,)。

    运算符

    公式举例

    公示结果

    B2:E6

    返回以B2为左上角,E6为右下角的矩形区域。

    空格

    (A4:E5 B2:C10)

    返回A4:E5和B2:C10交叉的区域,即两个区域的公共区域

    (A3:A5,B2:C10)

    返回A3:A5和B2:C10两个不相连区域组成的合并区域

                                      表二    引用运算符

    三、单元格的引用

    单元格的引用方式有3种:相对引用绝对引用混合引用

    相对引用、绝对引用、混合引用区别就在于:

    ·       使用相对引用的单元格内容会随着位置的变化发生变化;

    ·       使用绝对引用的单元格内容则是任尔东西南北风,我自岿然不动。只认准选中的单元格内容;

    ·       混合引用则算是两者折中,可以保持行不动,列动;也可以保持列不动,行动。

    以下来详细讲明。这三种引用方式要掌握,对excel的自动填充大有裨益。

    1、相对引用

          在公式中,所有类似“A1”的单元格地址都是相对引用,也就是除了行号和列标外,没有其他字符。

          如果在公式中使用相对引用的单元格,那引用的是相对于公式所在单元格的某个位置的单元格。如视频中所示。

     相对引用

           在视频中,C2=A2*B2,A2、B2、C2都是相对引用。在计算C2时,将用自身单元格这一行的左边两列的数字相乘,这就是公式记住的引用目标的位置。所以在填充时,C3、C4、C5也都是用自身单元格这一行的左边两列数字相乘。结果自然就不同。

            在使用相对引用的公式中,公式和它引用的单元,就像人和人的影子一样,始终按相同的步调、相同的距离和方向在工作表区域中移动。

            所以,在有着相同的计算规则的问题时,只需要写一个公式,就可以利用相对引用的特点进行填充,不需要重复编辑公式。

    2、绝对引用

           如果单元格地址的行号和列标前均加上“$”符号,则表示该单元格地址使用绝对引用。如:$A$1、$B$4 等。

           行号、列标前的“$”就像一把锁,锁住了单元格的位置。

                                                    绝对引用

           视频中,C2=$A$2*$B$2,A2、B2都是绝对引用,在填充时,绝对引用的单元格都不会发生改变,所以C3、C4、C5都是A2*B2的结果 。

           所以,如果你的公式需要固定引用某个单元格,可以利用绝对引用的特点来进行填充。

    3、混合引用

         如果只是在行号或者列标前加“$”,则是混合引用。如:$A1、B$2 。那么,就是加“$”的行号或列标使用绝对引用,没加的是相对引用。

                                                   混合引用

          混合引用是相对引用和绝对引用的混合体。集结了两种方式的优势。所以也要掌握好用法。

          这三种引用方式有快捷键可以快速切换。方法是:选中要切换的单元格地址,按键可以快速切换。

           在书上,看到有一种很形象的理解混合引用的话:有钱的是大爷,有$(美元)的谁也叫不走,没“$”的只好当跟屁虫。

           如果需要引用其他工作表或工作簿中的单元格,也可以直接通过鼠标点选的方式输入单元格地址。在公式中显示为“=‘工作表名称’!+单元格地址”,如:=单价!B1   。如果工作表名称以数字开头,或包括“$”、“%”、等特殊字符,还应该把工作表的名称写在一对半角单引号之间,如:“='2月'!B1” 。

            如果觉得有所收获,记得关注小干部哦~

    42b5800bd271c479833cf3d322337a8c.png

    展开全文
  • 一、xlwings简介imagexlwings优点...关于xlwingsxlwings开源免费,能够非常方便的读写Excel文件数据,并且能够进行单元格格式的修改。xlwings还可以和matplotlib、numpy以及pandas无缝连接,支持读写numpy、pan...

    一、xlwings简介

    image

    xlwings优点

    excel已经成为必不可少的数据处理软件,几乎天天在用。python有很多支持操作excel的第三方库,xlwings是其中一个。

    关于xlwings

    xlwings开源免费,能够非常方便的读写Excel文件中的数据,并且能够进行单元格格式的修改。

    xlwings还可以和matplotlib、numpy以及pandas无缝连接,支持读写numpy、pandas数据类型,将matplotlib可视化图表导入到excel中。

    最重要的是xlwings可以调用Excel文件中VBA写好的程序,也可以让VBA调用用Python写的程序。

    开源免费,一直在更新

    xlwings同类工具

    python操作Excel的模块,网上提到的模块大致有:xlwings、xlrd、xlwt、openpyxl,pandas等。

    xlwings功能总结

    一、用python读写Excel文件,实际上就是读写有格式的文本文件,操作excel文件和操作text、csv文件没有区别,Excel文件只是用来储存数据。

    二、除了操作数据,还可以调整Excel文件的表格宽度、字体颜色等。

    另外需要提到的是用COM调用Excel的API操作Excel文档也是可行的,相当麻烦基本和VBA没有区别

    xlwings中文文档

    xlwings结构图

    image

    二、xlwings基本操作

    (一)引入库

    import xlwings as xw

    (二)打开 excel

    # 打开Excel程序,默认设置:程序可见,只打开不新建工作薄,屏幕更新关闭

    app=xw.App(visible=True,add_book=False)

    app.display_alerts=False

    app.screen_updating=False

    # 其他操作:

    # 屏幕更新。就是说代码对于excel的操作你可以看见。关闭实时更新,可以加快脚本运行。默认是True。

    # app.screen_updating = False

    # App进程pid

    # app.pid

    # 返回一个打开的全部workbook的列表。Python打开的和手动打开的是不互通的

    # app.books

    # 终止进程,强制退出。

    # app.quit() #不保存的情况下,退出excel程序

    (三)工作簿

    注意工作簿应该首先被打开

    1、新建Excel文档

    # 创建新的book

    # 方式一

    wb = app.books.add()

    # 方式二

    wk = xw.Book()

    很多教程在提到新建App时都说这两种方式是一样的,实际上是有区别的,

    方式1是在当前App下新建一个Book,

    方式2是创建一个新的App,并在新App中新建一个Book

    # 方式三,与方式一方法相似

    wk = xw.books.add()

    2、 打开Excel文档

    # 支持绝对路径和相对路径

    wb = app.books.open('filepath')

    wk = xw.Book('filepath')

    wk = xw.books.open('filepath')

    # 练习的时候建议直接用下面这条

    # wb = xw.Book('example.xlsx')

    # 这样的话就不会频繁打开新的Excel

    3、打开未储存或未关闭的excel实例

    wk = xw.Book('Book1')

    wk = xw.books['Book1'] #也可以使用索引

    如果在两个Excel实例中打开了相同的文件,则需要完全限定它并包含应用程序实例。

    您将通过xw.apps.keys()找到您的应用实例密钥(PID):

    xw.apps[10559].books['FileName.xlsx']

    查看所有的实例进程:

    xw.apps.keys() #输出list

    kill所有的实例进程:

    for i in xw.apps.keys():

    i = 'taskkill/pid ' + str(i) + ' -t -f'

    os.system(i)

    4、打开活动的工作簿

    wb = xw.books.active

    5、保存

    # 保存工作簿,若未指定路径,保存在当前工作目录。

    wb.save(path=None)

    6、关闭

    #在没有保存的情况下关闭。

    wk.close()

    7、退出Excel

    app.quit()

    (四)工作表

    1、打开工作表

    # 可以用名字也可以用索引

    sheet = xw.books['工作簿的名字'].sheets['sheet的名字']

    sheet = xw.books['工作簿的名字'].sheets[0]

    2、打开活动工作表

    sheet = xw.sheets.active

    3、返回sheet指定的book

    book_name = sheet.book

    4、返回一个range对象,表示sheet上所有的单元格

    sheet_cells = sheet.cells

    5、获取或设置Sheet的名称

    sheet.name

    # 返回所有的工作表特定名称。

    sheet_names_list = sheet.names

    6、获取sheet中的所有图表集合

    sheet.charts

    7、清空表中所有数据和格式。

    sheet.clear()

    8、清除工作表的内容,但保留格式

    sheet.clear_contents()

    9、删除工作表

    sheet.delete()

    10、返回表索引(与excel相同)

    sheet.index

    11、创建一个新的Sheet并使其成为活动工作表

    wb.sheets.add(name=None, before=None, after=None)

    #参数:

    name(str,default None) - 新工作表的名称。 如果为None,则默认为Excel的name.

    before (Sheet, default None) - 一个对象,指定在新工作表添加之前的added.

    after (Sheet, default None) - 指定工作表之后的新工作表的对象。

    12、在整个工作表上自动调整列,行或两者的宽度

    sheet.autofit(axis=None)

    # 参数:

    axis (string, default None)

    要自动调整行, 使用以下之一: rows 或 r,

    要自动调整列, 使用以下之一: columns h c,

    要自动调整行和列, 不提供参数

    13、获取excel sheet多少行多少列

    app = xw.App(visible=False, add_book=False)

    xls = app.books.open(excel_file)

    sheet = xls.sheets[0]

    last_cell = sheet.used_range.last_cell

    nrows = sheet.used_range.last_cell.row

    ncols = sheet.used_range.last_cell.colum

    (五)单元格

    1、引用A1单元格

    rng = xw.books['工作簿的名字'].sheets['sheet的名字'].range('A1')

    # 或者

    sheet=xw.books['工作簿的名字'].sheets['sheet的名字']

    rng=sheet.range('A1')

    2、引用活动工作表上的单元格

    # 注意Range首字母大写

    rng=xw.Range('A1')

    其中需要注意的是单元格的完全引用路径是:

    # 第一个Excel程序的第一个工作薄的第一张sheet的第一个单元格

    xw.apps[0].books[0].sheets[0].range('A1')

    迅速引用单元格的方式是

    sht=xw.books['名字'].sheets['名字']

    # A1单元格

    rng=sht[’A1']

    rng=sht['a1']

    # A1:B5单元格

    rng=sht['A1:B5']

    # 第一行的第一列即a1

    rng=sht[0,0]

    # B1单元格

    rng=sht[0,1]

    3、引用区域单元格

    # A1:J10

    rng=sht[:10,:10]

    # A1:E10

    rng=sht[:10,:5]

    rng=sht.range('a1:a5')

    #rng = sht['a1:a5']

    #rng = sht[:5,0]

    4、对于单元格也可以用表示行列的tuple进行引用

    # A1单元格的引用

    xw.Range(1,1)

    #A1:C3单元格的引用

    xw.Range((1,1),(3,3))

    (六)写入数据

    1、写入单个值

    # 注意".value“

    sht.range('A1').value=1

    2、写入列表

    默认按行插入

    # 将列表[1,2,3]储存在A1:C1中

    sht.range('A1').value=[1,2,3]

    # 等同于

    sht.range('A1:C1').value = [1,2,3]

    按列插入

    # 将列表[1,2,3]储存在A1:A3中

    sht.range('A1').options(transpose=True).value=[1,2,3]

    其他方法

    列表

    一维列表:

    表示Excel中行或列的范围作为简单列表返回,

    这意味着一旦它们在Python中,您就丢失了有关方向的信息。

    如果这是一个问题,下一点将向您展示如何保留此信息:

    ######################## 列方向(嵌套列表)################

    列表中,每个元素都已列表方式保存,存储时,是按照列方向保存的

    sht = xw.Book().sheets[0]

    sht.range('A1').value = [[1],[2],[3],[4],[5]] # 列方向(嵌套列表)

    返回值为

    sht.range('A1:A5').value

    [1.0, 2.0, 3.0, 4.0, 5.0]

    ################## 行方向,普通列表 #######################

    sht.range('A1').value = [1, 2, 3, 4, 5]

    sht.range('A1:E1').value

    返回值为

    [1.0, 2.0, 3.0, 4.0, 5.0]

    要强制单个单元格作为列表到达,请使用:

    sht.range('A1').options(ndim=1).value

    返回值为

    [1.0]

    多行输入就要用二维列表

    重点:

    # 将2x2表格,即二维数组,储存在A1:B2中,如第一行1,2,第二行3,4

    sht.range('A1').options(expand='table').value=[[1,2],[3,4]]

    (七)读取数据

    1、读取单个值

    # 将A1的值,读取到a变量中

    a=sht.range('A1').value

    2、读取范围的值

    返回的值是列表形式,多行多列为二维列表。

    但有一点要注意,返回的数值默认是浮点数

    #将第一行的值,即将A1到A2的值,读取到a列表中

    a=sht.range('A1:A2').value

    # 将第一行和第二行的数据按二维数组的方式读取

    a=sht.range('A1:B2').value

    3、读取Excel表格的行、列

    读取excel的第一列,先计算单元格的行数

    通过这种方法统计的工作区域的行或者列,不能被空值分隔

    空值分隔后面的区域,不在统计范围内。

    更好的方式是通过last_cell方式获取最下边且最右边的一个单元格。

    last_cell = sheet.used_range.last_cell

    last_row = last_cell.row

    last_col = last_cell.column

    读取excel的第一列,先计算单元格的行数

    - 读取excel的第一列,先计算单元格的行数

    - 通过这种方法统计的工作区域的行或者列,不能被空值分隔

    - 空值分隔后面的区域,不在统计范围内。

    - 更好的方式是通过last_cell方式获取最下边且最右边的一个单元格。

    # 获取工作表的活动区域

    rng = sht.range('a1').expand('table')

    # 获取活动区域的行数

    nrows = rng.rows.count

    # 接着就可以按准确范围读取了

    a = sht.range(f'a1:a{nrows}').value

    同理选取一行的数据也一样

    ncols = rng.columns.count

    #用切片

    fst_col = sht[0,:ncols].value

    获取行数、列数,更好且更准确的方法

    # 更好的方式是通过last_cell方式获取最下边且最右边的一个单元格。

    last_cell = sheet.used_range.last_cell

    last_row = last_cell.row

    last_col = last_cell.column

    (八)常用函数和方法

    1、Book 工作簿常用的api

    # 新建工作簿

    xw.books.add()

    # 引用当前活动工作簿

    xw.books.active

    wb=xw.books[‘工作簿名称']

    wb.activate()激活为当前工作簿

    wb.fullname 返回工作簿的绝对路径

    wb.name 返回工作簿的名称

    wb.save(path=None) 保存工作簿,默认路径为工作簿原路径,若未保存则为脚本所在的路径

    wb.close() 关闭工作簿

    代码例子:

    # 引用Excel程序中,当前的工作簿

    wb=xw.books.acitve

    # 返回工作簿的绝对路径

    x=wb.fullname

    # 返回工作簿的名称

    x=wb.name

    # 保存工作簿,默认路径为工作簿原路径,若未保存则为脚本所在的路径

    x=wb.save(path=None)

    # 关闭工作簿

    x=wb.close()

    2、sheet 常用的api

    # 新建工作表

    xw.sheets.add(name=None,before=None,after=None)

    # 引用当前活动sheet

    xw.sheets.active

    # 引用某指定sheet

    sht=xw.books['工作簿名称'].sheets['sheet的名称']

    # 激活sheet为活动工作表

    sht.activate()

    # 清除sheet的内容和格式

    sht.clear()

    # 清除sheet的内容

    sht.contents()

    # 获取sheet的名称

    sht.name

    # 删除sheet

    sht.delete

    3、range常用的api

    # 引用当前活动工作表的单元格

    rng=xw.Range('A1')

    # 加入超链接

    # rng.add_hyperlink(r'www.baidu.com','百度',‘提示:点击即链接到百度')

    # 获得range的超链接

    rng.hyperlink

    # 取得当前range的地址

    rng.address

    rng.get_address()

    # 获得单元格的绝对地址

    rng.get_address(row_absolute=True, column_absolute=True,include_sheetname=False, external=False)

    # 清除range的内容

    rng.clear_contents()

    # 清除格式和内容

    rng.clear()

    # 取得range的背景色,以元组形式返回RGB值

    rng.color

    # 设置range的颜色

    rng.color=(255,255,255)

    # 清除range的背景色

    rng.color=None

    # 返回range中单元格的数量

    rng.count

    # 返回current_region当前区域

    rng.current_region

    # 返回ctrl + 方向

    rng.end('down')

    # 获取公式或者输入公式

    rng.formula='=SUM(B1:B5)'

    # 数组公式

    rng.formula_array

    # range平移

    rng.offset(row_offset=0,column_offset=0)

    #range进行resize改变range的大小

    rng.resize(row_size=None,column_size=None)

    # 获得range的第一列列标

    rng.column

    # 获得列宽

    rng.column_width

    # 返回range的总宽度

    rng.width

    # range的第一行行标

    rng.row

    # 行的高度,所有行一样高返回行高,不一样返回None

    rng.row_height

    # 返回range的总高度

    rng.height

    ###################################################

    # 获得range中右下角最后一个单元格

    rng.last_cell

    # 返回range的行数和列数

    rng.shape

    # 返回range所在的sheet

    rng.sheet

    #返回range的所有行

    rng.rows

    # range的第一行

    rng.rows[0]

    # range的总行数

    rng.rows.count

    # 返回range的所有列

    rng.columns

    # 返回range的第一列

    rng.columns[0]

    # 返回range的列数

    rng.columns.count

    # 所有range的大小自适应

    rng.autofit()

    # 所有列宽度自适应

    rng.columns.autofit()

    # 所有行宽度自适应

    rng.rows.autofit()

    三、Python工具类,通过代码操作Excel表格

    以下是我的工具类代码,转载请注明出处。

    #!/usr/bin/python

    # -*- coding: UTF-8 -*-

    import os

    import xlwings

    class ToolExcel(object):

    __file_name = "workbook.xlsx"

    __sheet_name = "Sheet1"

    # 新建工作簿

    @staticmethod

    def workbook_new(file_name: str = __file_name):

    # 工作簿文件路径

    workbook_file_path = os.path.join(os.getcwd(), "workbook", file_name)

    # 工作簿当前目录

    workbook_dir_path = os.path.dirname(workbook_file_path)

    # 如果不存在目录路径,就创建

    if not os.path.exists(workbook_dir_path):

    # 创建工作簿路径,makedirs可以创建级联路径

    os.makedirs(workbook_dir_path)

    # 如果不存在,Excel工作簿文件,就创建工作簿

    if not os.path.exists(workbook_file_path):

    # 打开Excel程序,APP程序(即Excel程序)不可见,只打开不新建工作薄,屏幕更新关闭

    app = xlwings.App(visible=False, add_book=False)

    # Excel工作簿显示警告,不显示

    app.display_alerts = False

    # 工作簿屏幕更新,不更新

    app.screen_updating = False

    # 创建工作簿

    wb = app.books.add()

    # 保存工作簿,若未指定路径,保存在当前工作目录。

    wb.save(workbook_file_path)

    # 关闭工作簿

    wb.close()

    # 退出Excel

    app.quit()

    # 读取工作簿全部内容,返回二维列表

    @staticmethod

    def workbook_read(file_name=__file_name, sheet_name=__sheet_name):

    # 工作簿文件路径

    workbook_file_path = os.path.join(os.getcwd(), "workbook", file_name)

    # 如果文件存在,就执行

    if os.path.exists(workbook_file_path):

    # 打开Excel程序,APP程序(即Excel程序)不可见,只打开不新建工作薄,屏幕更新关闭

    app = xlwings.App(visible=False, add_book=False)

    # Excel工作簿显示警告,不显示

    app.display_alerts = False

    # 工作簿屏幕更新,不更新

    app.screen_updating = False

    # 打开工作簿

    wb = app.books.open(workbook_file_path)

    # 获取活动的工作表

    sheet = wb.sheets[sheet_name]

    # 获取已编辑的矩形区域,最底部且最右侧的单元格

    last_cell = sheet.used_range.last_cell

    # 最大行数

    last_row = last_cell.row

    # 最大列数

    last_col = last_cell.column

    """

    # 读取二维列表

    # 注释:如果含有 .options(expand='table').value 参数,空值隔断的部分,不会被读取

    # data = sheet.range((1, 1), (last_row, last_col)).options(expand='table').value

    """

    # 读取二维列表

    data = sheet.range((1, 1), (last_row, last_col)).value

    # 关闭工作簿

    wb.close()

    # 退出Excel

    app.quit()

    return data

    # 写入二维列表,追加模式

    @staticmethod

    def workbook_append(data: list = None, file_name=__file_name, sheet_name=__sheet_name):

    # 工作簿文件路径

    workbook_file_path = os.path.join(os.getcwd(), "workbook", file_name)

    # 如果工作簿不存在,就创建工作簿

    if not os.path.exists(workbook_file_path):

    ToolExcel.workbook_new()

    # 如果文件存在,就执行

    if os.path.exists(workbook_file_path):

    # 打开Excel程序,APP程序(即Excel程序)不可见,只打开不新建工作薄,屏幕更新关闭

    app = xlwings.App(visible=False, add_book=False)

    # Excel工作簿显示警告,不显示

    app.display_alerts = False

    # 工作簿屏幕更新,不更新

    app.screen_updating = False

    # 打开工作簿

    wb = app.books.open(workbook_file_path)

    # 获取活动的工作表

    sheet = wb.sheets[sheet_name]

    # 获取已编辑的矩形区域,最底部且最右侧的单元格

    last_cell = sheet.used_range.last_cell

    # 最大行数

    last_row = last_cell.row

    # 写入二维列表,追加模式

    sheet.range((last_row + 1, 1)).options(expand='table').value = data

    # # 保存文件,保存以后重新读取单元格,重新获取所有活动区域的cell.

    # # 是否保存, 有待考证?

    # wb.save()

    # 获取已编辑的矩形区域,最底部且最右侧的单元格

    last_cell = sheet.used_range.last_cell

    # 最大行数

    last_row = last_cell.row

    # 最大列数

    last_col = last_cell.column

    # 在range中,cell的大小自适应

    sheet.range((1, 1), (last_row, last_col)).columns.autofit()

    # 保存文件

    wb.save()

    # 关闭工作簿

    wb.close()

    # 退出Excel

    app.quit()

    # 写入二维列表,重写模式

    @staticmethod

    def workbook_rewrite(data: list = None, file_name=__file_name, sheet_name=__sheet_name):

    # 工作簿文件路径

    workbook_file_path = os.path.join(os.getcwd(), "workbook", file_name)

    # 如果工作簿不存在,就创建工作簿

    if not os.path.exists(workbook_file_path):

    ToolExcel.workbook_new()

    # 如果文件存在,就执行

    if os.path.exists(workbook_file_path):

    # 打开Excel程序,APP程序(即Excel程序)不可见,只打开不新建工作薄,屏幕更新关闭

    app = xlwings.App(visible=False, add_book=False)

    # Excel工作簿显示警告,不显示

    app.display_alerts = False

    # 工作簿屏幕更新,不更新

    app.screen_updating = False

    # 打开工作簿

    wb = app.books.open(workbook_file_path)

    # 获取活动的工作表

    sheet = wb.sheets[sheet_name]

    # 清除sheet的内容和格式

    sheet.clear()

    # 写入二维列表,重写模式

    sheet.range("A1").options(expand='table').value = data

    # 获取已编辑的矩形区域,最底部且最右侧的单元格

    last_cell = sheet.used_range.last_cell

    # 最大行数

    last_row = last_cell.row

    # 最大列数

    last_col = last_cell.column

    # 所有range的大小自适应

    sheet.range((1, 1), (last_row, last_col)).columns.autofit()

    # 保存文件

    wb.save()

    # 关闭工作簿

    wb.close()

    # 退出Excel

    app.quit()

    四、参考文献:

    展开全文
  • 最近做的一个ITFIN的项目,导出的数据中有文本格式,也货币格式,所以为了方便将来导出的表格做计算,存放货币先用正则表达式判断数据是否为数值型,如果为数值型,则设置单元格格式为整数或者小数;...




    背 景


      最近做的一个ITFIN的项目中,后台需要用POI实现导出功能,导出的数据中有文本格式,也有货币格式,所以为了方便在将来导出的表格中做计算,存放货币的单元格需要设置为数值类型。

      导出的Excel的单元格都是文本格式(单元格左上角有个小三角):

    这里写图片描述

      费了不少功夫,终于把“小三角”去掉了,这里总结并分享一下问题的解决方法。


      通过poi导出excel的过程大致是这样的:

         规定单元格的格式
            ↓
          创建单元格
            ↓
         设置单元格的格式
            ↓
         设置数据的格式
            ↓
        把数据存放到单元格中
            ↓
          通过IO流输出


    背景POI导出Excel时设置单元格类型为数值类型

      要想存放数值的单元格以数值类型导出,其中最关键的步骤就是上面加粗的两步,设置单元格的格式和向单元格中存放数据。

      核心代码如下:

    	/**
    	 * 导出Excel-2015年11月11日
    	 * 
    	 *@param outPutParam Excel数据实体,包括要导出的excel标头、列标题、数据等
    	 * */
    	private void createContentRows(ExcelParam outPutParam) {
    		HSSFWorkbook workbook=new HSSFWorkbook(); //创建一个Excel文件
    		// 遍历集合数据,产生数据行
    		for (int i = 0; i < outPutParam.getContent().size(); i++) {
    			int rowIndex = i + 2;
    			HSSFRow contentRow = sheet.createRow(rowIndex);
    			Map<String, Object> rowDate = outPutParam.getContent().get(i);
    			//遍历列
    			for (int j = 0; j < outPutParam.getTitleList().size(); j++) {		
    				Title headTitle = outPutParam.getTitleList().get(j);//获取第i行第j列列标题
    				String headerName = headTitle.getName();//获取第j列列标识
    				Object data = rowDate.get(headerName);//获取第i行第j列所放数据
    				HSSFCellStyle contextstyle =workbook.createCellStyle();
    				HSSFCell contentCell = contentRow.createCell(j);				
    				Boolean isNum = false;//data是否为数值型
    				Boolean isInteger=false;//data是否为整数
    				Boolean isPercent=false;//data是否为百分数
    				if (data != null || "".equals(data)) {
    					//判断data是否为数值型
    					isNum = data.toString().matches("^(-?\\d+)(\\.\\d+)?$");
    					//判断data是否为整数(小数部分是否为0)
    					isInteger=data.toString().matches("^[-\\+]?[\\d]*$");
    					//判断data是否为百分数(是否包含“%”)
    					isPercent=data.toString().contains("%");
    				}
    				
    				//如果单元格内容是数值类型,涉及到金钱(金额、本、利),则设置cell的类型为数值型,设置data的类型为数值类型
    				if (isNum && !isPercent) {
    					HSSFDataFormat df = workbook.createDataFormat(); // 此处设置数据格式
    					if (isInteger) {
    						contextstyle.setDataFormat(df.getBuiltinFormat("#,#0"));//数据格式只显示整数
    					}else{
    						contextstyle.setDataFormat(df.getBuiltinFormat("#,##0.00"));//保留两位小数点
    					}					
    					// 设置单元格格式
    					contentCell.setCellStyle(contextstyle);
    					// 设置单元格内容为double类型
    					contentCell.setCellValue(Double.parseDouble(data.toString()));
    				} else {
    					contentCell.setCellStyle(contextstyle);
    					// 设置单元格内容为字符型
    					contentCell.setCellValue(data.toString());
    				}
    			}
    		}
    	}
    

      如上,有两个比较重要的点:
      1、先用正则表达式判断数据是否为数值型,如果为数值型,则设置单元格格式为整数或者小数;
      2、然后往单元格中存放数据的时候要设置数据的格式为double类型,如果查看poi的源码HSSFCell.java会发现设置数据的方法如下,所以用setCellValue(double)方法即可。

    这里写图片描述


    优化

      到了这里,您可能以为万事大吉啊了,其实上面的代码有个陷阱,如果不经过大数据量的测试是发觉不出来的哦~~

      如果数据量大的话,系统可能会报错“The maximum number of cell styles was exceeded. You can define up to 4000 styles in a .xls workbook”,原因是style创建的次数太多了,解决这个问题的方法很简单,在循环体外面创建单元格格式contextstyle(即把它当成一个“全局”变量),不要在循环内部创建。

      正确的代码如下:

    	/**
    	 * 导出Excel-2015年11月11日
    	 * 
    	 *@param outPutParam Excel数据实体,包括要导出的excel标头、列标题、数据等
    	 * */
    	private void createContentRows(ExcelParam outPutParam) {
    		HSSFWorkbook workbook=new HSSFWorkbook(); //创建一个Excel文件
    		HSSFCellStyle contextstyle =workbook.createCellStyle();
    		// 遍历集合数据,产生数据行
    		for (int i = 0; i < outPutParam.getContent().size(); i++) {
    			int rowIndex = i + 2;
    			HSSFRow contentRow = sheet.createRow(rowIndex);
    			Map<String, Object> rowDate = outPutParam.getContent().get(i);
    			//遍历列
    			for (int j = 0; j < outPutParam.getTitleList().size(); j++) {		
    				Title headTitle = outPutParam.getTitleList().get(j);//获取第i行第j列列标题
    				String headerName = headTitle.getName();//获取第j列列标识
    				Object data = rowDate.get(headerName);//获取第i行第j列所放数据
    				HSSFCell contentCell = contentRow.createCell(j);				
    				Boolean isNum = false;//data是否为数值型
    				Boolean isInteger=false;//data是否为整数
    				Boolean isPercent=false;//data是否为百分数
    				if (data != null || "".equals(data)) {
    					//判断data是否为数值型
    					isNum = data.toString().matches("^(-?\\d+)(\\.\\d+)?$");
    					//判断data是否为整数(小数部分是否为0)
    					isInteger=data.toString().matches("^[-\\+]?[\\d]*$");
    					//判断data是否为百分数(是否包含“%”)
    					isPercent=data.toString().contains("%");
    				}
    				
    				//如果单元格内容是数值类型,涉及到金钱(金额、本、利),则设置cell的类型为数值型,设置data的类型为数值类型
    				if (isNum && !isPercent) {
    					HSSFDataFormat df = workbook.createDataFormat(); // 此处设置数据格式
    					if (isInteger) {
    						contextstyle.setDataFormat(df.getBuiltinFormat("#,#0"));//数据格式只显示整数
    					}else{
    						contextstyle.setDataFormat(df.getBuiltinFormat("#,##0.00"));//保留两位小数点
    					}					
    					// 设置单元格格式
    					contentCell.setCellStyle(contextstyle);
    					// 设置单元格内容为double类型
    					contentCell.setCellValue(Double.parseDouble(data.toString()));
    				} else {
    					contentCell.setCellStyle(contextstyle);
    					// 设置单元格内容为字符型
    					contentCell.setCellValue(data.toString());
    				}
    			}
    		}
    	}
    

      最后导出的正确格式:

    这里写图片描述




    【 转载请注明出处——胡玉洋《POI导出Excel时设置单元格类型为数值类型》


    展开全文
  • Excel中数据类型

    2019-11-11 17:42:01
    在Excel中主要的数据类型分四类:数值、文本、逻辑值、错误值。 数值型:大小之分,可进行运算。 数值型数据包括0~9中的数字以及含有正号、负号、货币符号、百分号等任一种符号的数据。默认情况下,数值自动沿...
  •  最近做的一个ITFIN的项目,后台需要用POI实现导出功能,导出的数据中有文本格式,也货币格式,所以为了方便将来导出的表格做计算,存放货币的单元格需要设置为数值类型。  导出的Excel单元格都是文本...
  • 我们利用Excel制作表格时,设置单元格格式对于小伙伴们来说并不陌生,几乎每次制作表格时都会用到,但种自定义格式用到的就比较少了,今天小编分享几个自定义单元格格式的方法,花上1分钟学会,可以让工作效率...
  • POI对Excel单元格格式区分

    千次阅读 2015-10-27 15:29:24
    这些数据格式POI的HSSFDataFormat类里都相应的定义。 HSSFDataFormat是HSSF子项目里面定义的一个类。类HSSFDataFormat允许用户新建数据格式类型。HSSFDataFormat类包含静态方法static java.lang.String ...
  • 在Excel中,我们录入的数据可以根据录入的数据是否产生变化分为常量和公式,先了解一下Excel中的常量哪些数据数据类型吧。常量常量是一个计算机语言的术语,它的广义概念就是不变化的量,在我们录入源数据后,不是...
  • .NETBarcode Library的应用二 介绍 在上一篇我已经简单介绍了这个函数库(条形码应用之一------------函数库的...它们的类型被存储在Excel文件的一列(如上图BarcodeType),它们的数据包含在另一列(Bar...
  • 有数据时显示边框,录入数据时动态自动添加边框或填充色不仅能产生动画效果,也能产生良好的视觉效果,让格式变得更加美观、数据更加清晰,生活和工作中都比较实用。下面看一下具体操作:自动添加边框方法:1、...
  • Excel中有3种基本的数据类型:数值型、字符型和逻辑型,python中也不例外,我们可以一一对应来看。 1.数值型 先来想一下Excel中的数值类型的数据是什么样的,数字、百分数、分数、小数、货币、科学计数、日期和...
  • EXCEL是个很让人头疼的软件,因为大凡只要是公司上班,基本就绕不开这个软件,它还总喜欢跟数据捆绑一起,这个单元格、那个单元格,各种各样的数据一起,让你厘清,再进行计算汇总,分分钟把你拉回高中数学...
  • 一、分析前奏拿到一份数据后先要复制...Excel有不同数据类型,主要包括三种:字符串型(文本型)、数值型、逻辑型(T/F),其中字符串型不能直接用于做计算,所以尤其要注意以字符串形式存储的数字,先要更改为数值...
  • EXCEL单元格中的数值位数很多时,会自动采用科学计数法显示,如何取消科学计算法?以下三种方法如下: 方法一: 1、选定单个单元格或批量选择单元格后,右键选择设置单元格格式。 2、选择文本格式,确定。 3...
  • 因工作需要,要将Excel中的数据导入到Oracle中(农民工活儿),但是导入一直报错,后来发现在Excel中有一个数值型的列异常,虽然已经将该列转换成了“数值”类型,但将该单元格的内容粘贴到记事本中后发现数据外面...
  • 一、POI设置Excel表格的单元格格式(...这些数据格式POI的HSSFDataFormat类里都相应的定义。  HSSFDataFormat是HSSF子项目里面定义的一个类。类HSSFDataFormat允许用户新建数据格式类型。HSSFDataFormat类
  • Excel可识别的数据类型有数值、日期或时间、文本、公式、错误值与逻辑值(本技巧不讨论此类数据)。进一步了解Excel所识别单元格内输入数据的类型,可以最大程度地避免因数据类型错误而造成的麻烦输入数值任何由数字...
  • 相信关注表哥公众号的读者朋友每天多多少少都会和Excel...01####错误####错误在Excel中非常常见,但其实并不能当作是一种错误类型,是由于当前单元格宽度满足不了数值的长度就会出现显示不全的情况。如下所示:▎解...
  • 一开始是for体内创建样式但是因为数据量大会报【the maximum number of unique fonts in a workbook is limited to 32767】的错误,所以按照官方的案例把创建单元格样式放到了for循环外面,但现在个问题是我现在...
  • 在Excel工作表输入和编辑数据是使用Excel最基本的操作之一,每天与Excel打交道,你清楚它哪些数据类型吗,清楚怎么将文本型数值转换为可参与计算的数值型数据吗,今天,让我们一起走进Excel深入了解吧 一、数据...
  • 关于xlwingsxlwings开源免费,能够非常方便的读写Excel文件的数据,并且能够进行单元格格式的修改。xlwings还可以和matplotlib、numpy以及pandas无缝连接,支持读写numpy、pandas数据类型,将matplotlib可视化图表...
  • 首先得说一下关于excel中数据直接导入到表中的规范 1:一行记录对应数据库为一条记录,不能使用合并单元格. 2:不能允许标题,而是直接写出列名的值,列名顺序与值要一一对应. 3:对于某一列的值如果全为空的话,...
  • 世界上最深入人心的数据分析工具,是Excel,日本的程序员考试中,程序...Excel中数据类型主要:文本型,数值型,逻辑型。如果右键单元格显示为常规型,表示和数据本身表示出的类型相同。数值类型一般是右对...
  • ***请采用Excel2013或者2016版本,与教程保持一致,便于学习***工作,我们经常要向上级领导汇报某个指标的进度或完成情况。时候,我们会用仪表盘或温度计图来展示数据。不会这类型图表的朋友,不用...
  • Excel可识别的数据类型有数值、日期或时间、文本、公式、错误值与逻辑值(本技巧不讨论此类数据)。进一步了解Excel所识别单元格内输入数据的类型,可以最大程度地避免因数据类型错误而造成的麻烦。输入数值任何由数字...

空空如也

空空如也

1 2 3 4 5 ... 12
收藏数 239
精华内容 95
关键字:

在excel中单元格数据类型有