精华内容
下载资源
问答
  • 如何用Excel处理200万行以上数据?
    千次阅读
    2020-12-18 17:54:07

    关于如何用Excel处理200万行以上数据问题,现在已经完全没有压力了,虽然Excel工作表本身只支持1048576行数据,而且如果真的在一个表里数据导到100万行以上,这个表基本就跑不动了。

    但是,随着Excel2016的内置新功能Power Query、Power Pivot等(Excel2010或Excel2013可到微软官方下载相应的插件)的推出,这个问题已经得到很好的解决。

    理论上,Power Query和Power Pivot支持的数据行数是没有限制的,但Power Pivot仅支持2G以下的数据文件。

    大神高飞曾就Power Query和Power Pivot对大数据支持做过相关测试,情况如下:

    Excel 一亿行数据分析实践(总结篇)

    高飞 PowerBI极客

    测试目的

    本次测试目的并非与其他数据分析方法对比优劣、而是尝试介绍一种完全基于EXCEL的本地化大数据集处理方式。

    分析人员常用的大数据处理方式

    本次演示的方式

    这种方式的优点

    降低成本。减少工具间的切换成本,直接使用Excel作为存储和分析工具。

    展现灵活。展现端继续使用Excel,发挥它灵活、自定义程度高的优势。

    便于交付。其他方式得到的结果为了便于交付,还要导出为Excel,而现在整个分析流都在Excel内部完成。

    结果可交互。PowerPivot相当于一个存储了源数据的OLAP引擎,通过控制切片器等外部筛选条件,可以迅速、动态的查看结果,使用其他方法,可能需要返回分析端改变计算条件重新导出。

    测试项目一:数据导入和耗时

    向Excel导入大数据,有两种方式:

    PowerPivot导入,直接导入,不支持数据转换和清洗操作。

    PowerQuery导入,在导入前可以对数据做预处理。

    本次使用的测试数据集共有19列,有多列需要进行格式转换和日期提取操作,使用第一种方式,需要导入后在PowerPivot内部进行,使用方式二可以在载入前完成,很明显的是,对于方式二,预处理步骤越多,加载时间会越长。

    下图展示了不同量级不同导入方式的耗时情况(单位:秒)

    为了直接对比PowerQuery和PowerPivot的加载效率,增加了一个*号方式,这种方式不对数据做任何清洗转换,直接加载到模型,与PowerPivot步骤相同。

    现象

    对比前两行结果,PowerQuery的数据导入效率与PowerPivot不分伯仲。

    PowerQuery没有数据量的限制,而PowerPivot不到导入超过2G的文件。

    清洗步骤和数据量的增多,都会显著增加PowerQuery的导入时间,比如一亿行数据,即使三个简单的清洗步骤,用时已经超过了30分钟

    结论

    PowerPivot导入方式使用的是Access连接器,受限于Access文件本身的限制,不能导入超过2G的数据,这也说明,PowerPivot数据存储能力超过了Access。

    PowerQuery是轻型ETL工具,处理大数据集性能不强(基于Excel版本的 PQ)。

    如果尝试使用Buffer函数缓存数据,会发现这个缓存过程非常漫长,实际上,Buffer函数并不适合缓存大数据集,因为无法压缩数据,内存可能会很快爆掉。

    测试项目二:文件压缩比率

    对比不同导入方式生成的文件大小,与数据源文件做比较。

    影响文件压缩比率的因素,主要是数据集本身的特征和PowerPivot引擎的性能

    结论:

    数量级越大,压缩比率越高。

    同一数据量级,清洗步骤越多,最终文件会越大,并且随着数据量的增加,这种现象会越明显。

    测试项目三:简单分析的效率

    我们真正关心的内容是,Excel能否快速、高效的对大数据集开展分析。

    简单分析定义的场景是,逐月统计有多少位顾客发生了购买。做法是把年和月拖入透视表行字段,将CustomerKey拖入值区域,修改值汇总方式为统计不重复值。

    测试发现,即便使用一亿行数据,这个计算过程的用时也很短,小于1s。于是我增加了一点难度,加入两个切片器对结果做交叉筛选,计算用时仍然小于1s,看来PowerPivot处理这类分析比较轻松,最终此项测试没有计时。

    测试项目四:复杂分析的效率

    新客统计:逐月计算当月产生购买的顾客中,有多少是新客户(第一笔购买发生在当月)

    为了获取到PowerPivot引擎的计算时间,测试在DAX Studio内完成,同时为了模拟透视表的计算结果,需要对公式做一点改动。

    计算用时(毫秒)

    二次运算的用时指的是首次运算结束后,不清空缓存再次执行重复计算所花费的时间。相比第一次运算,节约时间在30%左右。原因是DAX的两个引擎中,有一个可以缓存计算结果,被缓存的内容可以在之后被公式内部调用,也可以跨公式调用。

    结合这个知识,对DAX的表达式进行优化,可以获得更好的性能表现,下面是新客统计优化之后的写法,我们来对比计值时间的变化。

    优化后计算用时(毫秒)

    可以看出引擎的缓存起到了显著效果,二次计算直接调用首次运算的结果,计算时间不随数据量的增加而增加。

    以一亿行数据集的结果为例,对比算法优化前后的用时:

    复杂统计测试项目二,流失客户统计

    与新客的呈现方式相同,依然是逐月计算当月的流失客户,不同的是流失客户的定义更为复杂。

    自定义一个流失天数,被判定流失的客户需同时满足以下两个条件:

    所有在当月之前最后一次购买的日期+自定义流失天数,落在当前时间区间内。

    当月如果发生购买,第一次购买日期不能早于判定流失的日期。

    流失客户公式和计算结果

    计值流如此复杂的一个公式,PowerPivot会耗时多久呢,我只用了一亿行数据的文件做测试,结果是首次计算4093ms,二次计算1720ms。

    说明:

    1. 以上测试模拟了透视表的呈现布局,而且你可以加入切片器改变公式的上下文条件,迅速得出特定产品、特定商户和特定促销活动的新客户以及流失客户,非常方便。

    2. 时间统计基于少量的测试结果,存在一定偶然性,仅供参考。

    测试环境

    电脑配置也是影响计算性能的重要因素,需要说明的是,以上进行的所有测试都基于台式机,在做现场分享的时候,我在笔记本电脑上重新运行了一遍流失客户公式,两个环境的用时如下:

    结合平时其他测试,我的笔记本执行同样的计算,用时平均在台式机的两倍左右。两台电脑的配置如下

    注意:提升CPU主频、核心数、1、2、3级缓存;内存的大小和频率都会提升引擎的性能表现。

    总结

    对于本地化大数据集的分析,本文提供了一种新的可能,严格来讲,2010年的时候你已经可以使用,只不过彼时它羽翼未丰,计算性能和稳定性难堪大任。

    而现在,你已经见识到了这套工具(PowerPivot+PowerQuery)的能力,无论大数据还是复杂运算,Excel公式和VBA已经无法望其项背。

    一般说来,积累通常是好事,财富可以通过积累不断增加、写作能力可以通过积累不断增强,但在某些知识领域,迭代是如此的快速和彻底,以至于底层的方法论都将被淘汰掉,过去的知识成为此刻的负担,你最好尽快丢掉过去,拥抱未来

    从上面可以看出,使用Excel处理200万行的数据完全没有问题,那么,Power Query和Power Pivot怎么用呢?可以参考以下系列内容:

    Power Query从入门到实战80篇

    Power Pivot基础及Dax入门15篇

    更多精彩内容,敬请关注【Excel到PowerBI】

    私信我即可下载60+Excel函数、数据透视10篇及Power系列功能95篇汇总训练材料

    我是大海,微软认证Excel专家,企业签约Power BI顾问

    让我们一起学习,共同进步!

    更多相关内容
  • Excel处理数万条数据很慢,怎么办?

    千次阅读 2020-12-28 18:57:41
    数据量大,Excel公式处理确实会非常慢,那么Excel是不是不能处理几十万行或者上百万行的数据,当然不是,现在的Excel已经不仅仅是利用Excel公式或者VBA来进行数据处理了。Excel中还有两个超级强大的数据处理模块:...

    这个问题来自知乎,以下是我的回答。

    数据量大,Excel公式处理确实会非常慢,那么Excel是不是不能处理几十万行或者上百万行的数据,当然不是,现在的Excel已经不仅仅是利用Excel公式或者VBA来进行数据处理了。

    Excel中还有两个超级强大的数据处理模块:PowerQuery和PowerPivot。

    题主的问题主要在于用Excel公式速度会变的很慢,那这个问题使用PowerQuery就可以解决。

    PowerQuery在哪里?

    Excel2010和Excel2013需要单独装PowerQuery插件,Excel2016及其之后的版本已经内置在Excel中,见下图:

    然后就可以进入Powerquery编辑器中,窗口是这样的,

    是不是感觉完全不像Excel,而像是进入了另外一个软件中,其实花半天时间熟悉了PQ这些界面的功能,你的Excel水平将大幅提升,轻松处理各种在excel中很难处理或者无法处理的事情。

    关于界面功能的介绍,请参考:

    也可以通过这篇文章了解一下PQ的强大:

    关于上面提到的PowerPivot,是非常强大的数据建模分析模块,适合建立模型,多表分析,远远超过Excel数据透视的分析功能。

    并且现在Excel中这两个模块还被微软整合进了更强大的工具:PowerBI,无论是数据清洗、数据建模、数据可视化,都可以在这里轻松完成,更重要的是,还是免费的。

    关于PowerQuery、PowerPivot以及PowerBI的学习,请来PowerBI星球,阅读更多干货。

    所以数据处理遇到问题,不能只局限在Excel现有认知的框架内,还要多了解一些Excel的最新功能,是不是有更适合的工具。

    思路打开、认知提升才能实现真正的效率提升。

    我是采悟,PowerBI星球运营者,如果你刚开始接触Power BI,可在微信公众号后台回复"PowerBI",获取《七天入门Power BI》电子书,帮你快速提升工作效率。

    加入PowerBI星球,添加采悟微信:PowerBI001,进入会员专享微信交流群。

    展开全文
  • 在99%的人眼中,Excel处理的最大数据是100万行,假如现在是300万行,估计都束手无策,只能寄希望于数据库。不学习真的限制了你的想象力,300万行数据Excel也能轻松统计。300万行一个表肯定是存放不下,可以存放在...

    这场软件PK,是时候分出胜负了,今天就是大结局。有必杀技在手,Excel已经看到了胜利的曙光。

    在99%的人眼中,Excel能处理的最大数据是100万行,假如现在是300万行,估计都束手无策,只能寄希望于数据库。不学习真的限制了你的想象力,300万行数据Excel也能轻松统计。

    300万行一个表肯定是存放不下,可以存放在多个工作表、多个工作簿甚至是数据库,不管何种储存形式,Excel都可以处理。今天,卢子就以存放在多个工作表为例进行说明。

    源文件:

    https://pan.baidu.com/s/1dwYqcMxblg2o_4B23IxusQ

    在Excel存放着3个工作表,每个工作表100万行数据,合计300万,现在要统计每个姓名的总金额。

    Step 01点数据→新建查询→从文件→从工作簿,浏览到指定的工作簿,导入,在导航器再选择工作簿的名称(相当于选中所有工作表),点编辑。

    Step 02选择Data这一列,右键选择删除其他列,再点扩展按钮,确定。展开后就看到所有数据,将第一行用作标题。

    Step 03点关闭并上载至,选择仅创建连接,勾选将此数据添加到数据模型。划重点,这一步是最重要的,不能有半点差错。数据模型就相当于一个大型的数据库,甚至可以容纳几千万行数据,正因为如此,才能让Excel突破百万行数据。

    Step 04直接创建透视表,默认情况下透视表的数据源就是数据模型中的数据,再勾选姓名和金额就完成统计。

    神奇吗?有没一种想亲自体验一把的想法?

    好了,办公软件之王最终还是归Excel。毕竟Excel是亲儿子,十年相伴,WPS不过是半路捡来的,从私心上,卢子当然是希望Excel能赢。

    作者:卢子,清华畅销书作者,《Excel效率手册 早做完,不加班》系列丛书创始人,个人公众号:Excel不加班(ID:Excelbujiaban)

    展开全文
  • 由于项目需要对大量Excel数据进行输入输出处理,在使用JXL,POI后发现很容易出现OOM,最后在网上找到阿里的开源项目EasyExcel能很快速的读取写入超大Excel文件。经过大量的调试优化,现通过JAVA生成104万行20列的...
  • C# 操作Excel,包含数据导入,数据导出到Excel,及对数据判断处理
  • Aspose.Cells是一款功能强大的Excel文档处理和转换控件,开发人员和客户电脑无需安装Microsoft Excel也能在应用程序中实现类似Excel的强大数据管理功能,支持所有Excel格式类型的操作,在没有Microsoft Excel的环境...
  • Spire.XLS:一款Excel处理神器

    万次阅读 2018-11-12 13:58:20
    正文 前言:最近项目里面有一些对Excel操作的需求,博主想都没想...导入Excel后,需要切割Excel的Sheet页,然后每个Sheet页单独生成一个PDF文件。 导出Excel的时候,项目里面需要将一些数据表格以图表的形式在Exc...

    正文

    前言:最近项目里面有一些对Excel操作的需求,博主想都没想,NPOI呗,简单、开源、免费,大家都喜欢!确实,对于一些简单的Excel导入、导出、合并单元格等,它都没啥太大的问题,但是这次的需求有两点是NPOI搞不定的:

    1. 导入Excel后,需要切割Excel的Sheet页,然后每个Sheet页单独生成一个PDF文件。
    2. 导出Excel的时候,项目里面需要将一些数据表格以图表的形式在Excel里面展示。

    找了一圈资料,对于Excel生成pdf,网上的答案千篇一律:使用COM组件的方式,通过调用服务器上面的Office组件里面的东西去转。这种方式需要在服务器上面安装Office,这倒是其次,最重要的是,权限的问题很头疼。博主已经按照这种方式实现了,调试的时候没问题,部署到IIS上面之后又出了各种权限的问题,好不容易在一台服务器上面部署成功了,放到另一台服务器上面按照同样的方式部署,却还是提示“拒绝访问”。博主也是醉了。而对于Excel生成图表,NPOI暂时没找到实现方式,COM组件的方式可以,但是实现起来略显复杂,并且这东西庞大、不太稳定,尤其是咱们大部分人个人电脑上面装的Office都不是正版,使用起来也很蛋疼。

    基于此,经过一番努力,找到了这么一个第三方组件Spire.XLS。这两天体验了一把,使用起来还比较顺手,在此来简单介绍下这个组件的使用吧。

    一、组件介绍

    Spire.XLS是E-iceblue开发的一套基于企业级的专业Office文档处理的组件之一,全称Spire.Office for .NET。旗下有Spire.Doc,Spire XLS,Spire.PDF,Spire.BarCode等多款专业组件,为各种Office文档在程序处理上提供了很大的方便,官方为各种功能提供了大量的在线api,简化了使用组件的难度。组件使用时不需要本地Office组件的支持。Spire.Office是一款企业级组件,它提供了收费版本和免费版本两种级别,一般来说,对于个人的应用,免费版本已足够用。比如对于上文博主遇到的问题,Spire.XLS组件就提供了很好的实现机制,如果你也遇到了NPOI解决不了的问题,不妨试试这个。

    “XLS”是Excel文件的后缀之一,顾名思义,Spire.XLS当然就是针对Excel表格处理的组件喽,本篇,博主将结合上文遇到的问题来看看Spire.XLS组件的强大功能。

    二、组件安装使用

    对于组件的安装,在此还是提供两种方式:

    1、官方下载安装

    下载地址。官方下载的安装包是msi结尾的,安装时需要选择支持的VS版本等信息,软件的安装就不做过多说明,有兴趣的可以下载试试。

    2、Nuget安装

    大家最喜欢的应该还是Nuget方式吧,简单,方便,并且易于管理。博主也是不太喜欢为了一个组件而去单独下载一个安装包。

    Spire.XLS也提供了Nuget的方式,只需要搜索Spire,选择免费版的组件即可:

    安装完成后自动引用了需要的dll

     

    三、组件功能介绍

    关于Excel的一些常用操作,比如取值、赋值、设置单元格样式等,这里就不做过多介绍,无论是Com组件、NPOI还是Aspose,这些都是最基础的功能。下面就针对上文提出的几个问题着重说明下。

    1、Excel转PDF

    (1)COM组件实现思路回顾

    关于Excel转PDF的实现,网上找到的解决方案基本一样,大致代码如此:

          /// <summary>
             /// 把Excel文件转换成PDF格式文件  
             /// </summary>
             /// <param name="sourcePath">源文件路径</param>
             /// <param name="targetPath">目标文件路径</param>
             /// <returns>true=转换成功</returns>
            public bool XLSConvertToPDF(string sourcePath, string targetPath)
            {
                Logger.Info("开始转pdf");
                bool result = false;
                XlFixedFormatType targetType = XlFixedFormatType.xlTypePDF;
                object missing = Type.Missing;
                Microsoft.Office.Interop.Excel.Application application = null;
                Microsoft.Office.Interop.Excel.Workbook workBook = null;
                try
                {
                    application = new Application();
                    application.Interactive = false;
                    object target = targetPath;
                    object type = targetType;
                    workBook = application.Workbooks.Open(sourcePath, missing, missing, missing, missing, missing,
                        missing, missing, missing, missing, missing, missing, missing, missing, missing);
                    application.Interactive = true;
                    workBook.ExportAsFixedFormat(targetType, target, XlFixedFormatQuality.xlQualityStandard, true, false, missing, missing, missing, missing);
                    result = true;
                }
                catch(Exception ex)
                {
                    Logger.Error("excel转pdf异常,异常信息:" + ex.Message + "。堆栈信息:" + ex.StackTrace); 
                    result = false;
                }
                finally
                {
                    if (workBook != null)
                    {
                        workBook.Close(true, missing, missing);
                        workBook = null;
                    }
                    if (application != null)
                    {
                        application.Quit();
                        application = null;
                    }
                    GC.Collect();
                    GC.WaitForPendingFinalizers();
                    GC.Collect();
                    GC.WaitForPendingFinalizers();
                }
                return result;
            }

    这个方法需要依赖于本机上面的office Com组件,如果你安装Office的时候,没有安装com组件相关的dll,这个方法也是用不了的,并且还有一个最大的问题就是执行 application.Workbooks.Open(sourcePath, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing); 这一个方法的时候需要当前用户有操作Excel Application这个组件的权限,尤其是部署到IIS上面之后,需要配置一系列的权限,很是麻烦。

    (2)Spire.XLS实现转换

    通过上文,我们知道,Spire.Office提供了Spire.XLS和Spire.PDF两个组件,那么他们之间的转换就简单了。我们还是模拟一个文件上传的功能。

    前端有一个上传控件:

     <input type="file" name="txt_file" id="txt_file" class="file-loading" />

    后台有一个接收上传文件的方法如下:

         [HttpPost]
            public JsonResult UploadFile()
            {
                var strRes = string.Empty;
                var oFile = Request.Files["txt_file"];
                Workbook book = new Workbook();
                book.LoadFromStream(oFile.InputStream);
                var strFullName = @"D:\Data\Upload\" + "First" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".pdf";
                book.SaveToPdf(strFullName);
                return Json(new object { }, JsonRequestBehavior.AllowGet);
            }

    就这么简单的几句话即可实现将上传的Excel转成PDF文件。根据源文件生成Workbook对象,Spire.XLS提供了多种方式,我们最常用的两种方式如下:

    // 根据文件路径生成workbook.
            public void LoadFromFile(string fileName);
    // 根据文件流生成workbook.
            public void LoadFromStream(Stream stream);

    2.1、最原始的转换

    原始Excel文件:

    转换成PDF之后

    2.2、不好看?加一个边框即可。

    转换之后

    2.3、自定义转换的PDF

    有些情况下,我们Excel里面有很多列,导致默认生成的pdf换行问题,这样将会导致PDF的可读性很差,这种情况,Spire.XLS为我们提供了自定义转换PDF的方式,比如可以指定PDF的页宽,页高,大小等等属性。

    比如有如下Excel文档需要转换成PDF文件:

    如果按照常规的转换,生成的PDF的宽度不足以显示Excel的所有列,于是转换出来的效果这样:

    为了解决这种问题,组件为我们提供了如下方法:

            [HttpPost]
            public JsonResult UploadFile()
            {
                var strRes = string.Empty;
                var oFile = Request.Files["txt_file"];
    
                Workbook book = new Workbook();
                book.LoadFromStream(oFile.InputStream);
                var strFullName = @"D:\Data\Upload\" + "First" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".pdf";
                PdfDocument pdfDocument = new PdfDocument();
                pdfDocument.PageSettings.Orientation = PdfPageOrientation.Landscape;
                pdfDocument.PageSettings.Width = 1800;//指定PDF的宽度
                pdfDocument.PageSettings.Height = 1000;//指定PDF的高度
    
                PdfConverterSettings settings = new PdfConverterSettings();
                settings.TemplateDocument = pdfDocument;
    
                PdfConverter pdfConverter = new PdfConverter(book);
                pdfDocument = pdfConverter.Convert(settings);
                pdfDocument.SaveToFile(strFullName);
                return Json(new object { }, JsonRequestBehavior.AllowGet);
            }

    这样就可以正常了,如果你的Excel列更多,可以适当调整宽度和高度。得到的结果如下

    还有更多强大的功能大家有兴趣可以慢慢探索,官方文档写得还算详细。

    2.4、Excel转其他类型

    除了转为PDF,Spire.XLS还支持转换为其他类型,比如常见的xml、Image、Html等。如果大家有这方面的需求,可以深究一下。

    2、Excel生成图表

    2.1、Excel图表生成原理分析

    通过下面一张图先来看看Excel里面生成图表的原理

    通过这张图我们可以看到,Excel生成图表首先需要当前文档里面存在数据表格,然后选中相应的数据表格,最后选择生成的图表类型,Excel应用会自动帮你生成相应的数据图表

    2.2、Spire.XLS生成简单图表

    知道了上面Excel生成图表的原理,我们再来看看Spire.XLS组件如何帮助我们解决生成图表的问题。关于生成图表,Spire.XLS组件提供了很多的选择,覆盖了Excel里面各种自带的图表类型、统计方法等。下面先来看一个简单点的例子。

           [HttpPost]
            public JsonResult ExportData()
            {
                try
                {
                    Workbook book = new Workbook();
                    Worksheet sheet = book.Worksheets[0];
                    var random = new Random();
                    var iCellcount = 1;
                    //1.设置表头
                    sheet.Range[1, iCellcount++].Text = "部门名称";
                    sheet.Range[1, iCellcount++].Text = "部门人数";
                    var lstDeptName = new List<string>() { "市场部", "策划部", "公关部", "行政部", "开发部" };
                    var a = 0;
                    //2.构造表数据
                    for (var i = 2; i < 7; i++)
                    {
                        iCellcount = 1;
                        sheet.Range[i, iCellcount++].Text = lstDeptName[a++];
                        sheet.Range[i, iCellcount++].NumberValue = random.Next(1, 100); ;
                    }
              //3.生成图表
                    SetChart(sheet, ExcelChartType.BarClustered);var strFullName = @"D:\Data\Upload\" + "Export" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx";
                    book.SaveToFile(strFullName, ExcelVersion.Version2010);
                }
                catch (Exception ex)
                { }
                return Json(true, JsonRequestBehavior.AllowGet);
            }
    
            private void SetChart(Worksheet sheet, ExcelChartType chartFormat)
            {
                //1.设置sheet页的名称
                sheet.Name = "Chart data";
                sheet.GridLinesVisible = false;
    
                Chart chart = sheet.Charts.Add();
    
                //2.指定生成图表的区域
                chart.DataRange = sheet.Range["A1:B6"];
                chart.SeriesDataFromRange = false;
    
                //3.指定图表的所在位置
                chart.LeftColumn = 5;
                chart.TopRow = 2;
                chart.RightColumn = 11;
                chart.BottomRow = 29;
                chart.ChartType = chartFormat;
    
                //4.设置图表的名称以及x、y轴的名称
                chart.ChartTitle = "部门信息";
                chart.ChartTitleArea.IsBold = true;
                chart.ChartTitleArea.Size = 12;
    
                chart.PrimaryCategoryAxis.Title = "部门";
                chart.PrimaryCategoryAxis.Font.IsBold = true;
                chart.PrimaryCategoryAxis.TitleArea.IsBold = true;
    
                chart.PrimaryValueAxis.Title = "人数";
                chart.PrimaryValueAxis.HasMajorGridLines = false;
                chart.PrimaryValueAxis.TitleArea.TextRotationAngle = 90;
                chart.PrimaryValueAxis.MinValue = 0;
                chart.PrimaryValueAxis.TitleArea.IsBold = true;
    
                //5.设置图表的值
                Spire.Xls.Charts.ChartSerie cs = chart.Series[0];
                cs.CategoryLabels = sheet.Range["A2:A6"];
                cs.Values = sheet.Range["B2:B6"];
                cs.DataFormat.ShowActiveValue = true;
                chart.Legend.Position = LegendPositionType.Top;
            }

    复制代码

    通过以上一段代码得到的Excel内容如下:

    代码释疑:关于上面的代码不难,但还是想做些简单的说明。

    1. 首先填充表格数据,Spire.XLS读写数据表格使用的是sheet.Range[i, iCellcount++].Text这种方式。值得一提的是这里的行列索引都是从1开始的。Range除了提供行列索引的方式,还提供了Range["B1"].Text这种方式去读取值。
    2. 通过上文Excel生成图表原理我们知道,出了有数据表格,还得选中生成图表的区域,上述代码里面通过 chart.DataRange = sheet.Range["A1:B6"]; 这一句去指定区域,和Excel里面的操作方式保持一致。
    3. 通过 chart.ChartType = chartFormat; 来指定需要生成的图表类型,Spire.XLS里面通过一个枚举类型包含了各种图表类型。
    4. 除了上面的这些,组件还支持指定图表在文档中的位置、图表坐标的最大值最小值。并且能够通过
      Spire.Xls.Charts.ChartSerie cs = chart.Series[0];
      cs.CategoryLabels = sheet.Range["A2:A6"];
      cs.Values = sheet.Range["B2:B6"];

      这种方式去指定分类和值的区域,更加符合Excel的操作习惯。当然,如无特殊,这些完全可以不用指定。

    2.3、对两项或者多项进行统计

    上面只是一个最简单的例子,如果要对多列进行统计呢?我们继续来看这个例子,我们将代码改成这样:

         [HttpPost]
            public JsonResult ExportData()
            {
                try
                {
                    Workbook book = new Workbook();
                    Worksheet sheet = book.Worksheets[0];
                    var random = new Random();
                    var iCellcount = 1;
                    //1.设置表头
                    sheet.Range[1, iCellcount++].Text = "部门名称";
                    sheet.Range[1, iCellcount++].Text = "在职人数";
                    sheet.Range[1, iCellcount++].Text = "离职人数";
                    var lstDeptName = new List<string>() { "市场部", "策划部", "公关部", "行政部", "开发部" };
                    var a = 0;
                    //2.构造表数据
                    for (var i = 2; i < 7; i++)
                    {
                        iCellcount = 1;
                        sheet.Range[i, iCellcount++].Text = lstDeptName[a++];
                        sheet.Range[i, iCellcount++].NumberValue = random.Next(1, 100);
                        sheet.Range[i, iCellcount++].NumberValue = random.Next(1, 100); ;
                    }
    //3.生成图表
                    SetChart(sheet, ExcelChartType.BarClustered);
                    var strFullName = @"D:\Data\Upload\" + "Export" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx";
                    book.SaveToFile(strFullName, ExcelVersion.Version2010);
                }
                catch (Exception ex){}
                return Json(true, JsonRequestBehavior.AllowGet);
            }
    
            private void SetChart(Worksheet sheet, ExcelChartType chartFormat)
            {
                //1.设置sheet页的名称
                sheet.Name = "Chart data";
                sheet.GridLinesVisible = false;
    
                Chart chart = sheet.Charts.Add();
    
                //2.指定生成图表的区域
                chart.DataRange = sheet.Range["A1:C6"];
                chart.SeriesDataFromRange = false;
    
                //3.指定图表的所在位置
                chart.LeftColumn = 5;
                chart.TopRow = 2;
                chart.RightColumn = 11;
                chart.BottomRow = 29;
                chart.ChartType = chartFormat;
    
                //4.设置图表的名称以及x、y轴的名称
                chart.ChartTitle = "部门信息";
                chart.ChartTitleArea.IsBold = true;
                chart.ChartTitleArea.Size = 12;
    
                chart.PrimaryCategoryAxis.Title = "部门";
                chart.PrimaryCategoryAxis.Font.IsBold = true;
                chart.PrimaryCategoryAxis.TitleArea.IsBold = true;
    
                chart.PrimaryValueAxis.Title = "人数";
                chart.PrimaryValueAxis.HasMajorGridLines = false;
                chart.PrimaryValueAxis.TitleArea.TextRotationAngle = 90;
                chart.PrimaryValueAxis.MinValue = 0;
                chart.PrimaryValueAxis.TitleArea.IsBold = true;
    
                //5.设置图表的值
                Spire.Xls.Charts.ChartSerie cs = chart.Series[0];
                cs.DataFormat.ShowActiveValue = true;
                cs.DataFormat.ShowBubble = true;
                chart.Legend.Position = LegendPositionType.Top;
    
            }

    复制代码

    得到结果如下:

    这里唯一的变化是数据区域,只要指定我们需要生成图表的区域是哪部分,Excel会自动进行计算并生成图表。

    2.4、各种类型的图表展示

    上文说过, chart.ChartType = chartFormat; 这一句可以设置图表的类型,在Spire.XLS里面定义了一系列的图表类型:

     ExcelChartType

    我们来看看一些比较常见的图表

    2.4.1、饼状图

    ExcelChartType.Pie

    ExcelChartType.Pie3D

    2.4.2、连线图

    ExcelChartType.Line3D

    ExcelChartType.LineStacked

    2.4.3、区域图

    2.4.4、雷达图

    2.4.5、圆形柱状图

     

    3、其他功能介绍

    关于Spire.XLS的其他亮点功能,博主也还在研究,已经知道的一些常用功能比如(1)支持单元格合并、冻结、注释;(2)数据库方式的导入导出;(3)Sheet页的复制、切割、显示、隐藏等;(4)页眉页脚的设置;(5)数据的分组、排序;(6)像Excel插入图片,设置图片样式等。这些功能有些已经实现,有些还在研究,等以后有机会再发出来供大家参考。因为篇幅问题,这篇先到这里吧。

    四、总结

    以上简单总结了下Spire.XLS组件几个特色功能,很好的解决了博主遇到的问题,博主觉得在一定程度上,Spire.XLS组件能拟补NPOI、COM组件的部分不足。还有很多其他特色功能待以后整理之后连带测试Demo一起发出。如果你也遇到一些其他组件解决不了的问题,不妨试试它,或许会带给你惊喜。当然,如果本文能够帮到你,还是希望园友们帮忙推荐,博主下次继续努力!

    本文原创出处:http://www.cnblogs.com/landeanfen/

    欢迎各位转载,但是未经作者本人同意,转载文章之后必须在文章页面明显位置给出作者和原文连接,否则保留追究法律责任的权利

    展开全文
  • 用python来自动生成excel数据文件。python处理excel文件主要是第三方模块库xlrd、xlwt、xluntils和pyExcelerator,除此之外,python处理excel还可以用win32com和openpyxl模块
  • pandas针对excel处理(更新)

    千次阅读 多人点赞 2020-07-24 09:33:17
    读取文件 import padas df = pd.read_csv("") #读取文件 pd.read_clipboard() #读取粘贴板的内容 ...df["price_new"] = df["price"].apply(lambda pri:pyi.lower()) #新列对老列处理 df["pricee"]
  • 今天拿到实验的数据,要进行一些数据处理。将中文名改成拼音并需要将姓氏首字母和名字首字母大写,中间加空格。类似如下: 用到的工具: 用到了汉子转拼音的Visual Basic 模块 实现首字母大写的PROPER() 函数 ...
  • EXCEL——处理大批量数据

    千次阅读 2021-05-07 09:53:23
    EXCEL——处理大批量数据方法简介比如例子 方法简介 EXCEL中的元素是按照顺序表存贮的,不适合删减元素,所以我们在删除数据的时候可以转化为选中需要的数据,直接复制粘贴速度非常快。 比如 删除一个元素,后面的...
  • Excel处理笛卡尔积

    千次阅读 2018-02-11 10:22:28
    工作中遇到需要处理笛卡尔积的需求,用数据库只需把需要做笛卡尔积的各列进行外链接就可以了,想到Excel应该可以处理这样的需求,就百度学习了一下,但还是看不太懂,下面只是依葫芦画瓢做了一遍,记录一下。...
  • MFC处理Excel文件

    2018-08-20 10:39:29
    基于VS2013编译,MFC连接Excel,进行读取,数据显示,数据处理,数据保存成新Excel文件等操作,详情见博客
  • python之excel处理画图

    千次阅读 2017-04-12 09:52:43
    最近换了一家公司,公司老大就给我一个excel让我练练手,简单的来说就是用python提取excel中得到的信息流,并作图
  • 1、批量填充 step1、将A列合并单元格区域取消 选中A2:A13,进行操作:【开始】—>【对齐方式】—>单击【合并后居中】 ...step2、获取并定位所有的空值 ...选中A2:A13,进行操作:【开始】—>...
  • Excel处理数据—第几次出现 函数

    千次阅读 2019-12-13 14:47:35
    Demo 函数:=COUNTIF(A$2:A2,A2)
  • POI处理Excel中各种日期格式问题

    千次阅读 2020-12-24 19:32:57
    前不久写过一篇随笔《EXCEL解析之终极方法WorkbookFactory》,提到使用WorkbookFactory来处理Excel文件数据,最近发现一个问题就是这个办法不能很好的处理各种日期格式,比如下面这些:那么如何将这些格式正确识别并...
  • Qt处理Excel的一些库

    千次阅读 2019-06-27 10:26:50
    收集一些操作Excel的库。
  • Excel处理 中文转拼音缩写

    千次阅读 2018-08-23 11:36:51
    1、打开Excel 2、Alt+F11打开VBA 3、右键左上角工程项目,添加模块 4、将以下代码复制到代码区 Function pinyin(p As String) As String i = Asc(p) Select Case i Case -20319 To -20284: pinyin = "A&...
  • 列表创建 : 最简单的方法用方括号[]将元素括起来: [10, 20, 30, 40] ['crunchy frog', 'ram bladder', 'lark涉及到处理excel文件中日期格式数据这里自己整理下元组(tuple)是不可变得 元组用圆...
  • excel处理数据正确为1,错误为0

    千次阅读 2018-11-13 11:56:10
    excel处理数据正确为1,错误为0 1.需要cmd下载 pip install numpy 的模板 2.注意文件的路径问题不要出错 3.还有文件的编码格式 ------encoding = 'gbk' import pandas as pd import numpy as np #读取excel...
  • 自己写的一个读取excel的例子
  • 此代码为matlab处理多批次excel的范例,可用于多个相同数据格式excel批量处理,并生成可用于论文格式的matlab曲线图
  • 最近接到一个需求,是导入excel表格,请求后台的接口需要提供两个数据,一个是导入的数据对应的分类id,一个是导入的数据对应的json类型,这就要求在请求之前要把excel数据处理成json数据,前端用的是react框架,...
  • 利用Excel处理OTU表

    千次阅读 多人点赞 2018-09-07 16:07:55
    1 OTU表数据一维化(使用excel2016版本以上) 现在的数据有2个维度,OTU和样本,接下来我们要把维度降低到一维,只保留OTU这个维度,原样本信息以数据标签的形式标注在值的后面。 选择数据→从表格,这里会打开一...
  • 后台回复‘0816’,加入Python交流群~ 9个库的简介环境配置及可实现操作 1、xlrdxlrd是一个从Excel文件读取数据和格式化信息的库,支持.xls以及.xlsx文件。http://xlrd.readthedocs.io/en/latest/1、xlrd支持.xls,....
  • excel中十多万条数据应如何处理

    千次阅读 2020-12-18 17:53:34
    数据量大,Excel公式处理确实会非常慢,那么Excel是不是不能处理几十万行或者上百万行的数据,当然不是,现在的Excel已经不仅仅是利用Excel公式或者VBA来进行数据处理了。Excel中还有两个超级强大的数据处理模块:...
  • 如何用matlab处理excel文件中的数据

    千次阅读 2021-04-21 09:56:09
    matlab读取excel文件可用xlsread函数,向excel中写数据可用xlswrite函数。注意:matlab不识别中文,读写的文件中最好不含有中文。举例说明如下:1、matlab读取excel文件数据:bb=xlsread('c:\feature.xls','A0:A40')...
  • Golang对excel进行处理

    万次阅读 2018-10-06 09:59:58
    国庆节祝我们的祖国更加繁荣昌盛,想必大家在国庆...之前有关Golang的文章主要是基于Golang的标准库来讲解的,今天我们来看看Golang的一个开源库,它可以用来处理xlsx文件,首先下载包 go get github.com/teale...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 237,445
精华内容 94,978
关键字:

excel处理

友情链接: 频谱泄漏.zip