locked
Problem to copy rows RRS feed

  • Question

  • Hi,<o:p></o:p>

    Using PasteSpecial by such codes, <o:p></o:p>

                        ExcelObj.ScreenUpdating = false;
    
                        Excel.Worksheet Worksheet0;
                        Worksheet0 = (Excel.Worksheet)Book0.Worksheets.Add();
                        Excel.Range ws0cell0 = Worksheet0.Cells[1, 1];
                        Excel.Range ws0cell;
    
                        foreach (Excel.Worksheet sheet in Book0.Worksheets)
                        {
                            Excel.Range usedRange = sheet.UsedRange;
    
                            RowID++; Worksheet0.Cells[RowID, 1] = "^^^^" + sheet.Name + "^^^^"; RowID++;
                            foreach (Excel.Range row in usedRange.Rows)
                            {
                                RowID++;
                                row.Copy();
                                Worksheet0.Cells[RowID, 1].PasteSpecial(-4104); 
                                ...


    it does take 9 minutes to copy the sheets within this, to one overall sheet, and such speed is still slow. Is there any other better way to improve the speed?


    Many Thanks & Best Regards, Hua Min



    Monday, July 25, 2016 2:06 AM

Answers

  • DocumentFormat.OpenXml.Spreadsheet.Sheets sheets = spreadSheet.WorkbookPart.Workbook.GetFirstChild<DocumentFormat.OpenXml.Spreadsheet.Sheets>();
    • Marked as answer by Jackson_1990 Thursday, July 28, 2016 4:31 AM
    Thursday, July 28, 2016 3:40 AM

All replies

  • I can really only give you the usual "working with office from .NET" 3 part disclaimer:

    1. If you can limit it to the new formats (.xlsx, .docx) you can use the OpenXML SDK. Or ZipArchive and XML reader classes. These formats are public and well known/designed.

    2. If you have to support older formats too, you have to use the (t)rusty Office COM Interop. Wich effectively remote-controls a hidden Excel Window.

    3. For any given input format, output format, specific problem and display technology there might be anotehr solution. But those are far in between.

    Ideally go for option 1. It is plain the best choice. And it can use any acceleration trick in the book (Stream and OS caches).

    Tuesday, July 26, 2016 1:07 AM
  • COM boundary transversal is always slow. If you need to copy large range of data, consider directly using the whole Range to copy to reduce the number of COM+ API calls involved.

    foreach (Excel.Worksheet sheet in Book0.Worksheets)
    {
    	sheet.UsedRange.Copy();
    
    	RowID++; Worksheet0.Cells[RowID, 1] = "^^^^" + sheet.Name + "^^^^"; RowID++;
    
    	Worksheet0.Cells[RowID, 1].PasteSpecial(XlPasteType.xlPasteAll);
    	
    	...

    Alternatively, do what Christopher84 suggested to use .NET native code like OpenXML SDK that won't introduce security boundary to process the file.

    • Edited by cheong00 Tuesday, July 26, 2016 1:42 AM
    Tuesday, July 26, 2016 1:39 AM
  • Hi,
    Thanks a lot to all.
    I get

    OpenXmlPowerTools

    (which is VS solution) and how to share it to my current project?

    Many Thanks & Best Regards, Hua Min


    Tuesday, July 26, 2016 2:12 AM
  • Hi HuaMin  Chen,

    Thank you for posting here.

    The Visual C# forum discuss and ask questions about the C# programming language, IDE, libraries, samples, and tools.

    Since your issue is related to excel for developer forum. I will move the thread to that forum for better support.

    If you have something else , please feel free to contact us.

    Best Regards,

    Hart


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place. Click HERE to participate the survey.

    Tuesday, July 26, 2016 2:28 AM
  • If you use VS2010 or above, you can right click the project in solution explorer, select "Mange NuGet packages...", select "Online" on the left pane and type "openxmlsdk" in the search box on the right upper corner. The "Open XML SDK" from "Microsoft Open Technologies, Inc." is it (Same author, but the one you did selected is the powertool only, not the library).

    EDIT: Wait, that powertool package also have reference to OpenXMLSDK already, so it should have already added required reference for you.

    • Edited by cheong00 Tuesday, July 26, 2016 2:33 AM
    Tuesday, July 26, 2016 2:30 AM
  • >>>I get

    OpenXmlPowerTools

    (which is VS solution) and how to share it to my current project?<<<

    According to your description, you could use directly Open XML SDK. You could download Open XML SDK 2.5 for Microsoft Office, then Using the classes in the Open XML SDK 2.5 is simple. When you have installed the Open XML SDK 2.5, open your existing project or application in Visual Studio, or create a new project or application. Then, in your project or application, add references to the following components.

    DocumentFormat.OpenXml
    WindowsBase

    For more information, click here to refer about Getting started with the Open XML SDK 2.5 for Office

    In addition issue is about OpenXmlPowerTools, you could post it on Issue for PowerTools for Open XML

    Thanks for your understanding.
    Wednesday, July 27, 2016 2:28 AM
  • Many thanks to all.

    How to correct
    Error	2	'DocumentFormat.OpenXml.Packaging.SpreadsheetDocument' does not contain a definition for 'Worksheets' and no extension method 'Worksheets' accepting a first argument of type 'DocumentFormat.OpenXml.Packaging.SpreadsheetDocument' could be found (are you missing a using directive or an assembly reference?)


    due to last line below?
                        using (SpreadsheetDocument Book0 = SpreadsheetDocument.Open(openFileDialog1.FileName, true))
                        {
    
                            Worksheet0 = (Excel.Worksheet)Book0.Worksheets.Add();



    Many Thanks & Best Regards, Hua Min

    Wednesday, July 27, 2016 2:39 AM
  • See the example code on how to add a new worksheet here.

    Btw, I believe that the PowerTool also installs a number of example codes in your machine that you can study to see how things work.

    Wednesday, July 27, 2016 2:57 AM
  • How to add one new Worksheet to WorkbookPart below?
                            foreach (Excel.Worksheet sheet in Book0.WorkbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == Worksheet0))
                            {
                                ...



    Many Thanks & Best Regards, Hua Min

    Wednesday, July 27, 2016 6:05 AM
  • See the function InsertWorksheet() in  the example page I linked to. Make it return newWorksheetPart in the end so you can use it to add contents.
    Wednesday, July 27, 2016 6:41 AM
  • Appreciated and how can I have one specific name to the newly added Worksheet?

    Many Thanks & Best Regards, Hua Min

    Wednesday, July 27, 2016 7:04 AM
  • Modify the function to accept "string sheetName" too and then comment out the line from line 14 to 23 then.

    In this way you can assign the new sheetname as you create the WorksheetPart.

    • Edited by cheong00 Wednesday, July 27, 2016 7:18 AM
    Wednesday, July 27, 2016 7:17 AM
  • Appreciated a lot.

    How to resolve
    Error	3	Cannot implicitly convert type 'Microsoft.Office.Interop.Excel.Sheets' to 'DocumentFormat.OpenXml.Spreadsheet.Worksheet'. An explicit conversion exists (are you missing a cast?)	C:\App\WindowsFormsApplication5\WindowsFormsApplication2\Form1.cs	237	71	WindowsFormsApplication2
    Error	4	The type 'Microsoft.Office.Interop.Excel.Sheets' cannot be used as type parameter 'T' in the generic type or method 'DocumentFormat.OpenXml.OpenXmlElement.GetFirstChild<T>()'. There is no implicit reference conversion from 'Microsoft.Office.Interop.Excel.Sheets' to 'DocumentFormat.OpenXml.OpenXmlElement'.	C:\App\WindowsFormsApplication5\WindowsFormsApplication2\Form1.cs	237	71	WindowsFormsApplication2
    Error	5	'Sheets' is an ambiguous reference between 'Microsoft.Office.Interop.Excel.Sheets' and 'DocumentFormat.OpenXml.Spreadsheet.Sheets'	C:\App\WindowsFormsApplication5\WindowsFormsApplication2\Form1.cs	237	119	WindowsFormsApplication2



    due to this line?
                    DocumentFormat.OpenXml.Spreadsheet.Worksheet sheets = spreadSheet.WorkbookPart.Workbook.GetFirstChild<Sheets>();
    


    Many Thanks & Best Regards, Hua Min

    Thursday, July 28, 2016 1:42 AM
  • The Office COM object and OpenXML SDK parts don't mix.

    You're required to replace everything in Microsoft.Office.Interop.* in that function with corresponding objects in the OpenXML SDK, because it's different library.

    • Edited by cheong00 Thursday, July 28, 2016 1:55 AM
    Thursday, July 28, 2016 1:54 AM
  • Sorry, what change to spreadSheet below?

                using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(docName, true))
                {
                    // Add a blank WorksheetPart.
                    WorksheetPart newWorksheetPart = spreadSheet.WorkbookPart.AddNewPart<WorksheetPart>();
                    newWorksheetPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet(new SheetData());
    
                    DocumentFormat.OpenXml.Spreadsheet.Worksheet sheets = spreadSheet.WorkbookPart.Workbook.GetFirstChild<Sheets>();
    


    Many Thanks & Best Regards, Hua Min

    Thursday, July 28, 2016 2:36 AM
  • DocumentFormat.OpenXml.Spreadsheet.Sheets sheets = spreadSheet.WorkbookPart.Workbook.GetFirstChild<DocumentFormat.OpenXml.Spreadsheet.Sheets>();
    • Marked as answer by Jackson_1990 Thursday, July 28, 2016 4:31 AM
    Thursday, July 28, 2016 3:40 AM