Most effective way to duplicate an Excel worksheet?

Question

I am working on a project upgrading a WPF program that has to work with Excel sheets a lot. It's using Closedxml and Excel interop to manipulate Excel files and add data.

After some trace I found a function that's painfully slow. It use the same method as other similar function, but everything else is working quite fine. The problem is Closedxml.copyto() eats up 85% of processing power.

It's purpose simply is to take one Excel sheet as a template if there is new record that need to be printed. it will copy the first sheet to new sheet then write data into it.

If you have any idea on how to speed up this kind of process, Please let me know!

                    foreach (object[] row in rows)
                    {
                        if (Common.integer(row[0]) < from_no || Common.integer(row[0]) > to_no)
                        {
                            continue;
                        }
                        sheetNum++;
                        if (sheetNum != 1)
                        {

this part use 85% of process Power

                            sheet_edit.CopyTo(sheetNum.ToString());
                            sheet_edit = book.Worksheet(sheetNum);
                        }

                        sheet_edit.Name = row[0].ToString();

                        ct.ThrowIfCancellationRequested();
                        w.ReportProgress(progCnt * 100 / maxCnt);
                        progCnt++;}

Thank you very much!

PS: sorry for my bad English!

PS: To anyone who did downvote my question, Please tell me the reason? Is it Not helpful or ... other reason?

PS: I searched all day but i cant find any answer for this. There are quite some method but those all just dont fit my need.

using interop: not quite faster.

using openxml: it mean i have write more code and it not quite easy to intergrade to this program

using closedXML.copyRange: Sure quite faster but it doesnt copy columns width, row height,... it mean more code, mode process... So not quite faster.

I decided to use dianogtics.process(print) in the loop, that 1st sheet will be reused in every loop. It kind of faster, but we cant choose printer or printer setting... default printer and setting will be used automatically.

I can explain this to my customer and i think this is quite aceptable. But i am still waiting for the answer.. I you happened to know how to fasten this kind of processs up, please let me know!!

Do you mean to copy the worksheet within the same workbook? Or are you duplicating the workbook itself, i.e. the file? — John Wu, Nov 15 '19 at 08:15
If you use ClosedXML why do you also use *interop*? That's what's slowing you down — Panagiotis Kanavos, Nov 15 '19 at 08:15
`The problem is Closedxml.copyto() eats up 85% of processing power.` Only if you work on a single-core machine. Besides, a single threaded operation *is* going to use 100% of the CPU when active, unless something interrupts it, like IO, Thread.Sleep() etc. — Panagiotis Kanavos, Nov 15 '19 at 08:17
In an case, post your code. It's impossible to help with code problems without the code itself. The code is infinitely better than good English too — Panagiotis Kanavos, Nov 15 '19 at 08:18
hi JohnWu, I want to copy the worksheet within the same workbook, but it too slow and i want to make this process faster. — MarioWu, Nov 15 '19 at 08:35
hi Kanavos, i put it in a back ground worker. It surely consume 85% process Power some how. I checked other func too, and this **closedxml.sheet.copyto()** always eat more than 80% of process power. — MarioWu, Nov 15 '19 at 08:46

score 1 · Accepted Answer · answered Nov 18 '19 at 12:50

ClosedXML has to copy each object (cell, style, picture, etc) from the source to the destination. If you have many thousands of cells, then this will consume your CPU cycles.

You should ensure that your source worksheet contains only the cells and styles that you really need. In my experience, I have seen many Excel templates that contain many unused styles and empty cells at bizarre worksheet addresses.

If I were you, I would recreate the template as far as possible in ClosedXML itself (even if just a once-off process). This will ensure that your template is as minimal as possible. ClosedXML doesn't support all features yet, so after you create the template, you may want to add elements (e.g. charts). Then use that saved template in your further processing. It should be much smaller and faster as the one you're using now (my guess).

Other options you could try: An .xlsx file is just a .zip package. You can look at the underlying XML inside the file and determine how many cells or styles there are to be copied.

You can also download the ClosedXML source and narrow down exactly which kind of element is taking up the resources.

Disclaimer: I'm a ClosedXML project maintainer.

Thank you, this is exactly what i need! This file that I am working with is not so complex but it spread on number thousands cells... I have never noticed this since it look so simple and i am not the one who designed it... I am not a interop fan and i am really enjoy working with closeXML. You have been making a great product sir! Cheers! — MarioWu, Nov 18 '19 at 17:58

score 0 · Answer 2 · answered Nov 15 '19 at 09:06

0

Instead of using ClosedXml to copy the sheet , You can use the Excel Interop to do the same. Below is the sample code for copying the worksheet

Excel.Application xlApp = Marshal.GetActiveObject("Excel.Application") as Excel.Application;
Excel.Workbook xlWb = xlApp.ActiveWorkbook as Excel.Workbook;
Excel.Worksheet xlSht = xlWb.Sheets[1];
xlSht.Copy(Type.Missing, xlWb.Sheets[xlWb.Sheets.Count]); 
xlWb.Sheets[xlWb.Sheets.Count].Name = "NEW SHEET";

answered Nov 15 '19 at 09:06

Test12345

1,625
1
12
21

1

This is not an option in many cases. We do not want to install Excel on the server, Closed XML (OpenXML) is the way to go! You could use File.Copy: File.Copy(templatePath, fileName, true); – Mark Jul 25 '22 at 08:01

Most effective way to duplicate an Excel worksheet?

2 Answers2