wordpress hit counter
Creating Pivot Tables, Part 2 - OpenXML Developer - Blog - OpenXML Developer
Goodbye and Hello

OpenXmlDeveloper.org is Shutting Down

There is a time for all good things to come to an end, and the time has come to shut down OpenXmlDeveloper.org.

Screen-casts and blog posts: Content on OpenXmlDeveloper.org will be moving to EricWhite.com.

Forums: We are moving the forums to EricWhite.com and StackOverflow.com. Please do not post in the forums on OpenXmlDeveloper.org. Instead, please post in the forums at EricWhite.com or at StackOverflow.com.

Please see this blog post for more information about my plans moving forward.  Cheers, Eric

Creating Pivot Tables, Part 2

Creating Pivot Tables, Part 2

  • Comments 8

This page has been moved.  You can find this content at the following link: Creating Pivot Tables, Part 2

OpenXmlDeveloper.org is shutting down as an active site.  See this blog post at EricWhite.com for more information.

  • BobM -

    How would you do it from scratch - without a pivot table templated in a worksheet ?  That is, create an entirely new workbook, import csv data into a worksheet, and then on another worksheet, add a pivot table that references the data on the other worksheet ?

    For the majority, a template isn't going to work as data is not always a set number of columns nor are the columns consistently static, meaning the header values will be different for different sets of data.

  • This example does show the creation of a spreadsheet from scratch. The video shows both methods.

  • This third part of the series is a screencast that explains the Open XML elements needed for a pivot

  • BobM -

    I've some questions I'm hoping you can help with.

    I've downloaded the PowerTools and run the ExamplePivotTables project to create a new workbook.  The PivtoData.txt in the Range sheet does not show all of the data.  The header row is also missing;   only the data in the columns Year and Amount are visible.  All other column cells are empty.

    However, on the Pivot sheet, the Field List displays all of the Header field values.  I can even select fields to create a pivot and all the data is present in the  Pivot table.   Not quite sure how this is working.

    On another note, I've used my own set of csv data (which contains some empty columns).   It has 1096 rows with 136 columns.   The problem here is that any columns that are empty are throwing an exception in the 'CreatePivotTable() ' method, where it tries to :

      "Convert.ToDouble(value) <  minValue" .

    since the value is "".   I've also run across where the value is a vaild string (eg: "Dennis") and it comes into this line and blows up as well.

    By the way this took  to 40 seconds to load my data into the sheet using the StreamReader.   When I've loaded this data in another OpenXML project, from start to finish, the entire process takes about 5 or 6 seconds, albeit my workbook is corrupted.   I'm using the File.ReadAllLines() method.  Here is the times:

    RunTime 00:00:00.88  <-- created the source data sheet

    RunTime 00:00:05.75   <-- workbook has been created. Under 6 seconds from start t finish.

    [see below code how i load the range data]

    I manually edited this method to account for empty strings and null values but then the Pivot Table is corrupted.   Interestingly, the Field List has  all of the header values, but as I mentioned above, these values are missing from the Range sheet, as well as the empty column cells.

    [start code]

        string[] _csvPivotTableReferenceData = File.ReadAllLines("c:\\temp\\CATS 11-1 - 11-15 Trv.csv");

         // add the rows/cells of data

         int rowCounter = 1;

         foreach (var r in _csvPivotTableReferenceData)

         {

           Row row =

             new Row() { RowIndex = UInt32Value.FromUInt32((uint)rowCounter), Spans = new ListValue<StringValue>() { InnerText = "1:" + _sourceDataColumnCount.ToString() }, DyDescent = 0.25D };

           int cellIndex = 1;

           var cellData = r.Split(new char[] { ',' });

           foreach (var c in cellData)

           {

             // construct Excel column reference for the data

             StringValue cellReference = (StringValue)String.Concat(GetExcelColumnName(cellIndex),rowCounter.ToString());

             Cell cell = new Cell() { CellReference = cellReference, DataType = CellValues.String };

             CellValue cellValue = new CellValue();

             cellValue.Text = c;

             cell.Append(cellValue);

             row.Append(cell);

             cellIndex++;

           }

           sheetData1.Append(row);

           rowCounter++;

         }

    [end code]

  • Bob,

    This is in addition to my comment above [Thu, Feb 2 2012 10:55 AM].

    It looks like all String values do not get added to the sheet.   All of the empty cells should be string data.  Only the numeric data is in the cells.

    I'm looking in the SetCellValue() method of WorksheetAccessor.cs but it looks like the string values are added, so not sure what's going on.  

    BTW, using Office 2010 on Windows 7.

  • Re. refreshing the pivot table in the template example.  When originally creating the pivot table by hand in the template, from the PivotTable Options (right click on the pivot table) you can choose to "Refresh data when opening the file" (see the Data tab).  This will mean the pivot table gets automatically refreshed.

  • listrik dan panas, serta mempunyai titik cair tinggi. Bijih logam ditemukan dengan cara penambangan yang terdapat dalam keadaan murni atau bercampur dengan unsur-unsur seperti karbon, sulfur, fosfor, silikon, serta kotoran seperti tanah liat, pasir, dan tanah.

    http://goo.gl/fgh1ds http://bit.ly/1M92mpb

    https://goo.gl/JdUZyB http://bit.ly/2fj747M

    https://goo.gl/tN8zV8 http://bit.ly/2fxUzs5

    https://goo.gl/ukAjdu http://bit.ly/2futS4Y

    https://goo.gl/hLXjHy http://bit.ly/2ehbUW8

    https://goo.gl/yDMlpq http://bit.ly/2fyvR8K

    https://goo.gl/mfqLZU http://bit.ly/2fWa8tV

    https://goo.gl/qWkW28 http://bit.ly/2fyAqjj

    https://goo.gl/vnyuHb http://bit.ly/2fxOGv0

    https://goo.gl/HuKYjz http://bit.ly/2fzHyua

    https://goo.gl/wkPEKV http://bit.ly/2ehdd7L

    https://goo.gl/Z0J6LF http://bit.ly/2eQlCLv

    https://goo.gl/Aat085 http://bit.ly/2ehaXgv

    https://goo.gl/74Ao5K http://bit.ly/2eyKRRI

    https://goo.gl/LDVtJJ http://bit.ly/2fW5YT9

    https://goo.gl/cK0YaF http://bit.ly/2eHDzhQ

    https://goo.gl/KnGFuL http://bit.ly/2fzLcEC

    http://goo.gl/0MIIQL http://bit.ly/1XHRm6d

    http://goo.gl/TPidJv http://bit.ly/1ivQx0e

    https://goo.gl/x9MnRB http://bit.ly/2fuxSmd

    https://goo.gl/GonlNh http://bit.ly/2bgLGS7

    https://goo.gl/XZ6zpY http://bit.ly/1P41Awa

    https://goo.gl/8IVNQ9 http://bit.ly/2eMs6Km

    https://goo.gl/M52XmL http://bit.ly/2eMs6Km

    https://goo.gl/TrcRsU http://bit.ly/2fllKFP

    https://goo.gl/8IVNQ9 http://bit.ly/2fllKFP

    listrik dan panas, serta mempunyai titik cair tinggi. Bijih logam ditemukan dengan cara penambangan yang terdapat dalam keadaan murni atau bercampur dengan unsur-unsur seperti karbon, sulfur, fosfor, silikon, serta kotoran seperti tanah liat, pasir, dan tanah.

    http://goo.gl/pWtlO9 http://bit.ly/2bz4aJD

    http://goo.gl/f0vB7K http://bit.ly/2bO3W3i

    http://goo.gl/uTL5Cu http://bit.ly/2bz1A6v

    http://goo.gl/lJRzzt http://bit.ly/2bUsUjy

    http://goo.gl/DdDqMr http://bit.ly/2blOnz1

    http://goo.gl/zhFEpe http://bit.ly/2bF0zgB

    http://goo.gl/AfxmL4 http://bit.ly/2c5pYC8

    http://goo.gl/GPxl0D http://bit.ly/2bz25NV

    http://goo.gl/1i7Obd http://bit.ly/2bz1JHl

    http://goo.gl/neJ80l http://bit.ly/2beifS1

    http://goo.gl/sHibPg http://bit.ly/2bfykUC

    http://goo.gl/Mj9qNp http://bit.ly/2bh5XlK

    http://goo.gl/o5s4FC http://bit.ly/2bM119U

    http://goo.gl/3j2MZZ http://bit.ly/2bAvnQT

    http://goo.gl/lHUCmg http://bit.ly/2bOcOpF

    http://goo.gl/KlVYYP http://bit.ly/2bh7Uyz

    http://goo.gl/cPzAze http://bit.ly/2bOez5Y

    http://goo.gl/1tIqEu http://bit.ly/2bfzVd7

    https://goo.gl/8IVNQ9 http://bit.ly/2eMs6Km

    https://goo.gl/M52XmL http://bit.ly/2eMs6Km

    https://goo.gl/TrcRsU http://bit.ly/2fllKFP

    https://goo.gl/8IVNQ9 http://bit.ly/2fllKFP

    https://goo.gl/ZZ66dD http://bit.ly/2eUXZAP

  • http://www.fashionhorizons.com As soon as last snows from winter last of all melt to spring you could be probably itching to include away a heavy knit tops, mufflers not to mention dark styles and slip on something pleasure and bright being the temperatures warm and then the flowers glimpse through. Your daughter will be same process.

    http://www.fashionindigo.com Every instance I pickup an astrological journal or maybe even an every day newspaper, I read through doom not to mention gloom content pieces that seem that will send you and me even deeper towards a slough from despond. Typically the recession, typically the major alterations in business enterprise and power, modern slavery - virtually all coming being the inevitable reaction Pluto keying in Capricorn.

    www.fashioninnovative.com The fashionable fashion environment, despite every single piece of its colorfulness not to mention glamor, is plagued by loads of copycats. This copycat fad results towards a gross loss as well as been rendering many jolts in the fashion industry a long time. The landing of "architectural approach" in fashion has given plenty of new methods of clothes textures.

    http://www.fashionintegral.com It happens to be interesting to make note of that type, society not to mention social life associated with individual are actually seamlessly intertwined with the other person and play an important role through enhancing 1. Everyone through society can be described as victim towards fashion utilizing some form and / or another, in somehow.

    http://www.financeever.com There are actually many literature on exclusive finances available on the market. Usually, the contributor sells some of those books and / or ideas that allows you to increase the personal netting worth in place of serving others to attain their budgetary goals. Here are here are the books that i believe might possibly be the best literature on exclusive finances ever previously created.

    http://www.financemake.com Presently creative and luxuriate in working with the help of photos? Posts a advanced photo album for everyone to have fun with and give out their the entire family. What to do is offer to get the different photos not to mention lay these products out towards a uniquely constructed photo course, one which may be professionally screen printed.

    http://www.financeread.com If you're not spending your Evening of romance weekend with special someone, maybe you can actually snuggle up utilizing your finances fifth get these products in working hard order. Even in the event you're having to pay Valentine's holiday weekend doing a specific thing romantic, get such books to study on some rainy time of day or a second time.

    http://www.financethought.com Your opinions on Finance happens to be an article who explores thoughts that were designed to, could or is going to be considered should more was basically known concerning function from finance. The relationship, if any sort of, between typically the finance from corporation and then the finance of men and women is taken into consideration.

    http://www.financevariable.com As monthly obligations on variable-rate your home loans are beginning to swell, many Americans obtain one way to defer when real of reckoning. They have reevaluated variable-rate mortgages massive to afford home as deals escalate.

    http://www.fixyourautos.com Will typically the bailout get our online car insurance, life insurance protection, health insurance protection and label insurance safer enough right now to relaxation easier after sunset. Here is my opinion on for what reason the bailout was basically needed.

    www.futurebeyondtechnology.com Even in the current less as opposed to stellar personal economic environment, secondary education grads, if they've been smart and desire to think other than their partying solutions, can make themselves a good edge by reasoning ahead, between the parties not to mention games.

    http://www.futuristicjobs.com What it should take becoming a top-notched futurist? Most certainly, there is definitely quite a difference of opinion when considering this subject. Not long ago, a guy who'd delivered our nation in your armed pushes and later got into the realty building arena asked everybody this subject.

    www.greatseomarketing.com Online online marketing is one way to reach out for a wider customers base, one way to get your internet business the accolades and then the visibility it again deserves and one way to earn the profits that you could possibly not need ever wanted, if you were stuck working only in actuality.

    http://www.healthayrveda.com In typically the far far off past in your majestic Himalayas, the practice of Ayrveda began a tough evolution over 5, 000 numerous years. A synergistic structure which synthesizes science, religious beliefs and vision, Ayurvedic remedy was earlier practiced by your prophets termed rishis. Read nore about ayurvedic remedy here.

    www.healthosteopathy.com Osteopathy will be fastest improving health profession in the usa, Canada & The world. It happens to be chosen as one of the top 31 occupations used by CIBC. It is a fantastic career choice for individuals interested through becomming a medical expert.

    www.healthtraditional.com Are you sick of giving the same old gifts all the time? Everyone seems to have purchased not to mention received a variety of baskets from shower jellified and baby cream and a large number of small pans of flowers year after year. Eventually, everyone reaches a degree where they give something different.

    http://www.helpmarketseo.com Marketing SEARCH ENGINE RANKING OPTIMIZATION solutions are actually powerful not to mention effective tools which may greatly increase your online presence from generating good traffic world wide web. This definitely will skyrockets your search engine rankings, which ultimately creates making a small fortune using the on-line world.

    http://www.homeexclusively.com Find through why virtually all home buyers make use of a Realtor to find their residential, and are actually glad many did! Exclusive people agents furnish home buyers with the most protection potential, plus include the experience to guide you find an appropriate home, and negotiation the right price not to mention terms.

    http://www.improveautos.com In the present soft economic crisis and having been through the govt years in the field, auto dealers are looking closely by opportunities for the purpose of enhanced not to mention improved turn a profit. Generally taken into consideration a "necessary evil", the showing department is right now being researched a approach of obtaining new uncouth margin not to mention profit.

    www.initialparenting.com Parents might possibly be the key towards success. Programs started to initiate dad or mom weekends, parent seminars and various other ways towards pull typically the parents towards the healing system. This encourages an healthy view the are family group problems really not a child's concern.

    www.insurancedeadlines.com If not often covered make a decision before it again closes, "You snooze, you lose". You have got to wait before next offered enrollment period purchase a package. Oh hang around... You needs to pay some tax penalty to boot. Whoa!

    www.insuranceextention.com There are a multitude of health insurances companies ahead these days that anyone can be sure that you will get an insurance which may fit suitable for you and know about premiums you are looking for and who too at a low cost price.

    www.insuranceinevitable.com A term coverage of life coverage products is actually to provide you with life coverage within period you've selected as the concept of of that particular plan. When who term has ended, it's a wish any time you continue, that anyone can, or leave the payment plan.

    www.insurancesuperhub.com If you could be sitting having the search engine spider web, aided by the lines sprinting outward connected to the various line, and while someone you could be connected to brew a connection suitable for you, you makes the vibration right from that place. What is usually happening is that each person you could be connected towards is the midst of a web site of his well-known.

    http://www.lawbasis.com To big day, traditional abroad law will never consider person environmental rights for a clean not to mention healthy environment a jus cogens person right. Jus cogens ("compelling law") comes from preemptory 100 % legal principles not to mention norms which were binding concerning all abroad States, regardless their approval.

    http://www.lawdecree.com Whenever you have been waiting to spark up a business (wouldn't matter in case it is an over the internet or off the internet venture), it's consistently good to seek the advice of legal analysts or hometown governing bodies to find an very idea of laws and regulations that definitely will affect your internet business.

    http://www.lawyersdemand.com Bankruptcy can be quite a truly serious matter and nothing worse take an individual maybe a corporation than searching for file for bankruptcy. It is affected by many families and harm their career and lifetime and provide them altogether helpless.

    www.lawyersselection.com The damage may well be like physical trauma, monetary decrease or like psychological problems. In 100 % legal language, we take advantage of the term accident for the aforementioned three important things. Personal injury are generally physical, psychological not to mention monetary or these individuals in mother nature herself.

    www.livetelecastnews.com A reviewing live baseball event concerning TV happens to be an enjoyable past experiences for baseball fans. Throughout cable not to mention satellite registration, live telecast from soccer tournaments are generally watched concerning TV. Ordinary people may well have the chance to watch the favorite sport across the world through their television set.

    www.myhomedevelopment.com When selecting community to set up your latest home you can find key thoughts to ask the ultra-modern home beautiful before deciding on whether you must build where community and / or not. Read this text to realize what some of those key thoughts are.

Page 1 of 1 (8 items)