wordpress hit counter
Screen-Cast: Using Open XML and LINQ to XML in a Streaming Fashion to Create Huge Spreadsheets - 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

Screen-Cast: Using Open XML and LINQ to XML in a Streaming Fashion to Create Huge Spreadsheets

Screen-Cast: Using Open XML and LINQ to XML in a Streaming Fashion to Create Huge Spreadsheets

  • Comments 4

This page has been moved.  You can find this content at the following link: Screen-Cast: Using Open XML and LINQ to XML in a Streaming Fashion to Create Huge Spreadsheets

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

Attachment: SpreadsheetWriter.zip
  • Hi Mr White.  Is there a version that is compatible with Visual Studio 2005?

  • This is brilliant.  How could I change your SpreadsheetWriter class to return the spreadsheet as a MemoryStream instead of writing the file to the file system?

  • Figured out the answer to my own question, which I've enclosed below as a static method within your static SpreadsheetWriter class.   This is based on a different blog post you wrote. :)  A lot of logic is reused from your Write method, but I thought maybe it could help someone else.

    Your solution is lightning fast.  I had a very large spreadsheet I am making on the fly with 300,000+ cells being written.  It was taking 40+ seconds with my previous solution.  Incorporating your logic to pull this data in a massive LINQ statement has decreased processing time to 3-4 seconds!  Thank you.

    public static MemoryStream ToMemoryStream(string fileName, Workbook workbook)

           {

               MemoryStream mem = new MemoryStream();

               // create the blank workbook

               char[] base64CharArray = _EmptyXlsx

                   .Where(c => c != '\r' && c != '\n').ToArray();

               byte[] byteArray =

                   System.Convert.FromBase64CharArray(base64CharArray,

                   0, base64CharArray.Length);

               mem.Write(byteArray,0, byteArray.Length);

               using (SpreadsheetDocument sDoc = SpreadsheetDocument.Open(mem, true))

               {

                   WorkbookPart workbookPart = sDoc.WorkbookPart;

                   XDocument wXDoc = workbookPart.GetXDocument();

                   XElement sheetElement = wXDoc

                       .Root

                       .Elements(S.sheets)

                       .Elements(S.sheet)

                       .Where(s => (string)s.Attribute(SSNoNamespace.name) == "Sheet1")

                       .FirstOrDefault();

                   if (sheetElement == null)

                       throw new SpreadsheetWriterInternalException();

                   string id = (string)sheetElement.Attribute(R.id);

                   sheetElement.Remove();

                   workbookPart.PutXDocument();

                   WorksheetPart sPart = (WorksheetPart)workbookPart.GetPartById(id);

                   workbookPart.DeletePart(sPart);

                   XDocument appXDoc = sDoc

                       .ExtendedFilePropertiesPart

                       .GetXDocument();

                   XElement vector = appXDoc

                       .Root

                       .Elements(EP.TitlesOfParts)

                       .Elements(VT.vector)

                       .FirstOrDefault();

                   if (vector != null)

                   {

                       vector.SetAttributeValue(SSNoNamespace.size, 0);

                       XElement lpstr = vector.Element(VT.lpstr);

                       lpstr.Remove();

                   }

                   XElement vector2 = appXDoc

                       .Root

                       .Elements(EP.HeadingPairs)

                       .Elements(VT.vector)

                       .FirstOrDefault();

                   XElement variant = vector2

                       .Descendants(VT.i4)

                       .FirstOrDefault();

                   if (variant != null)

                      variant.Value = "1";

                   sDoc.ExtendedFilePropertiesPart.PutXDocument();

                   if (workbook.Worksheets != null)

                       foreach (var worksheet in workbook.Worksheets)

                           AddWorksheet(sDoc, worksheet);

                   workbookPart.WorkbookStylesPart.PutXDocument();

               }

               return mem;

           }

  • http://www.newsrepeats.com Within last piece of writing we discussed what is the news and multimedia, and how we might realise underlying message they've been giving us through the use of something described as Narrative Theological Biblical judgments.

    http://www.overseebusiness.com It might be all too basic take typically the roof over your internet business for issued, and it happens to be something that people especially rarely carefully consider during the working day. It can be there; it's likely that it's there for you to started working sign in company, and it are likely to be there long set up moved by means of another profession.

    http://www.parentinggood.com Learn learn how to influence your kid's behavior free of them being familiar with it. Developing babies with big self-esteem not to mention confidence will take these 5 keys that explain how guidance them triumph at your life. Give these products the edge you need to succeed and they're going to have the particular needed to produce their objectives.

    http://www.petsrights.com Fleas not to mention ticks : there's definitely seems to be no end at their recurrence. Fortunately there's an ideal way to influence this, with the help of Frontline In addition to. Here's some concise lead on Frontline In addition to and how can help your pup.

    http://www.powerfulsport.com This piece of writing explains the more common misconception that sport from Powerlifting can be described as Power sport exploiting fact will be complete perpendicular. It can be described as strength pursuit. This piece of writing will discuss the difference from the two designs and explain how at the same time are terrific tools for learning not solely powerlifting and yet all athletic.

    http://www.preferjob.com This installment of this ongoing start writing trick series discusses putting a title to requirements. This piece of writing suggests you the ante (not to mention power) from including a particular action driven title who demonstrates how will impact the manufacturer.

    www.preferrealestate.com Buy affordable, sell big, it's some credo just about everyone has heard previously, and it will sound like a sure route to earn money from your real estate investment opportunities, right? Most certainly, it's more demanding than who quote helps it to be sound and you'll have to work diligently to ensure that you notice a great, big return.

    http://www.proinjobs.com A plumber can be described as professional who enhances repairing not to mention installing typically the pipes which were used to sling sewage not to mention water. A good number of plumbers work in your plumbing or a / c industry, and yet nearly fifteen percent from plumbers are actually self-employed.

    www.purchaseshopping.com If you've ever thought on the subject of growing the hops, stop thinking it's essential to now. It is very simple than you feel and plus the knowledge that beer which are hand devised contains hops which are grown to operate an effective back flowerbed and there are a compelling assertion to have a go.

    www.readyforservices.com College enrollees are graduating, and Funeral Day might be fast coming up, which methods summer virtually here. Getting equipped for the hot months doesn't be a very painful experience; teach one self healthy habits that anyone can continue over summer and winter to guide you get to shape and reserve shape.

    http://www.readyparenting.com Clogs your the aging process parent might be overwhelming not to mention confusing. A lot of us are hosted into such situations without the need for notice and / or experience. I thought my father was well-prepared until I recently came across out the guy wasn't. The issues that appear within parent-child character reversal is near impossible than virtually anyone can expect.

    www.realestateeffort.com QR code can be described as versatile tool which may be immensely helpful for real estate marketing. Set to generally be an essential portion of the real estate marketing through close forthcoming, it is just common sense to ensure it is now. Read on to have a clue QR code can really help make a listings offer for sale better.

    http://www.realestategeo.com What was in the old days your ideal home has now become a nightmare. Because of no teenagers to two to three kids, you're running because of room including your last nerve happens to be frayed from Jr's hamster that's out from his cage for ones past month.

    www.realestatemassive.com People are actually avoiding any styles of investment owing to slump not to mention poor defense situation through Pakistan. Owing to recession, building slump not to mention terrorism through Pakistan, rich not to mention professional people quickly immigrating towards advanced friendly welfare cities.

    www.realestatescheme.com Even the foremost luxurious not to mention expensive homes at present are getting back in on typically the act, as residential builders have realized that eco-friendly products can be as useful as beautiful for the reason that their an old counterparts.

    www.realestatetactic.com The right ways to make money from foreclosed family homes are to search out them throughout their pre stages. Unlike the late occasion get unique quick infomercials need to you to consider, a foreclosed home will be your least favorable suitable for you.

    www.reallycoolsports.com These strange beasts, inhabiting a lot of our Cosmic Zoo, are probably born an identical way for the reason that normal super stars. However, Brown Dwarfs never find the critical mass which may be necessary towards ignite his or her's nuclear-fusing furnaces.

    www.realparentinghelp.com Most people haven't truly experienced your life to her fullest until such time as you've long been a dad or mom. It's an experience that we tend to as everyday people struggle with consistently despite using done it again for several thousand years. Consultants publish literature, teachers handle parenting groups, experienced nannies turn up on shows.

    www.selectorbusiness.com It can be described as well-known truth keywords might possibly be the backbone of this Internet. With no it, you simply cannot do much on line, but Along with them, you are able to master msn and so generate potential customers for businesses from their own home, which methods sales.

    http://www.servicesbasic.com Keeping a pool clean happens to be an essential an important part of being some responsible group owner. When people invest in a pool, have a relatively pool built, or invest in a home accompanied by a pool for at first chance, they may not realize the quantity of work not to mention effort switches into 'keeping it again up. a

    http://www.shoppingaspect.com With many kinds of overall body shops to choose from, it's necessary to know what type to choose to try and force the best take good care of your auto or truck. Determine picking an car or truck body shop if your vehicle is short of auto overall body repair.

    http://www.shoppingmargin.com Everyone requirements customized services when they might be ordering hats to showcase a function. Offering a clients sublimating services on their custom hats is a fantastic way to boost business earnings. Here are suggestions for ensuring some flawless result everytime.

    http://www.shoppingprecise.com The general explanation of what happens in some precision system shop can be quite simple. It happens to be an assembly-line system, in of which numerical instructions are fed towards a machine, of which translates some of those commands to robotic procedures, resulting in your creation associated with object and / or objects by a solid discourage of undercooked metal.

    www.specializebusiness.com Many individuals at the moment are very successful in business and at the moment are very rich because of the businesses. Some of these individuals obtain fame in the industry community not to mention their victory stories are actually no secret in the public.

    http://www.techgenerate.com There could be many purpose one would require passport graphics. A passport capacity photograph is absolutely a portrait photograph from a human have to deal with showing prominently despite associated with the top torso accumulate the bare. As typically the name signifies, the to begin with place you will want one for these is some passport.

    www.technologyforreal.com It might be apparent that that allows you to survive not to mention thrive in your real estate industry presently, real estate agents and real estate brokers must include every facet of technology not to mention remain as well as the have got to continually move up their units.

    http://www.techspecially.com There are many excellent opportunities towards advance a radiology career if you have had one or longer areas from specialization. The better specializations you possess, the further competitive you're going to be in the effort market.

    http://www.travelascend.com This month's state takes us in the western U. S. state from Oregon many fantastic snowboarding. First a little bit of background at the state from Oregon on their own. Begin accompanied by a little prehistory by following this link to read about Oregon and then the Columbia plateau.

    http://www.travelescalator.com Koi fish are generally called typically the national perch of Japan as they are popular through China and across the globe. This piece of writing describes the of Koi fish breeding and provides useful advice regarding how kinds of fish are actually categorised as reported by their shapes and colours and markings.

    http://www.travelinglong.com Traveling accompanied by a baby in any car is absolutely not easy, but running longer as opposed to two hours before starting sounds especially dreadful. Right here are three really efficient tips not to mention ideas on how to ready yourself, car including your baby for this long escape.

Page 1 of 1 (4 items)