none
Importing and Exporting Data into excel via clipboard RRS feed

  • Question

  • Hi

    I want to import and export 1000+ cell data into and from excel via clipboard. Also the cell data is not in a symmetric grid and the solution needs this flexibility.

    From my research so far,

    - the cell data should be in the form of tab delimited.

    - use the StringBuilder to bind my data into rows of string data

    - eventually bind the data to a dataobject to add it to the clipboard using the getdataobject and setdataobject methods

    - use the StringSplit method to break the data down

    Also I welcome suggestions if there is a better way of doing this. 

    I am programming in VB.net framework 4.5 but I do welcome C# solutions as well because I am also after the theory as well.

    Thanks

    Thursday, January 7, 2016 11:47 PM

All replies

  • Hi,

    Since your issue is about Excel. I moved your thread to Excel forum for better support.

    Thanks for your understanding.

    Best Regards,

    Lake Xiao

    Monday, January 11, 2016 3:36 AM
  • Hi tfitzhardinge,

    For this requirement, if your application is the client application (e.g. windows form), you could automate excel by using C# language. To copy the data by using Range.Copy method and paste by using Range.PasteSpecial method.

    Simple sample:

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using Microsoft.Office.Interop.Excel;
     
    namespace CodeCall
    {
        public class ExcelTest
        {
            public static void Main()
            {
                ApplicationClass app = new ApplicationClass();
                Workbook curWorkBook = null;
                Workbook destWorkbook = null;
                Worksheet workSheet = null;
                Worksheet newWorksheet = null;
    Object defaultArg = Type.Missing;
    try
    {
    // Copy the source sheet
    curWorkBook = app.Workbooks.Open("c:\\Book1.xlsx", defaultArg, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg);
    workSheet = (Worksheet)curWorkBook.Sheets[1]; 
    workSheet.UsedRange.Copy(defaultArg);
     
    // Paste on destination sheet
    destWorkbook = app.Workbooks.Open("c:\\Book2.xlsx", defaultArg, false, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg);
    newWorksheet = (Worksheet)destWorkbook.Worksheets.Add(defaultArg, defaultArg, defaultArg, defaultArg);
    newWorksheet.UsedRange._PasteSpecial(XlPasteType.xlPasteValues, XlPasteSpecialOperation.xlPasteSpecialOperationNone, false, false);
    }
    catch (Exception exc)
    {
    System.Windows.Forms.MessageBox.Show(exc.Message);
    }
    finally
    {
    if (curWorkBook != null) {
    curWorkBook.Save();
    curWorkBook.Close(defaultArg, defaultArg, defaultArg);
    }
     
    if (destWorkbook != null)
    {
    destWorkbook.Save();
    destWorkbook.Close(defaultArg, defaultArg, defaultArg);
    }
    }
                app.Quit();
            }
        }
    }

    There is an article about excel automation that may benefit you:

    # How to automate Excel by using Visual C# to fill or to obtain data in a range by using arrays

    https://support.microsoft.com/en-us/kb/302096

    Regards

    Starain


    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, January 12, 2016 3:04 AM
    Moderator
  • Hi Starain

    Thank you for your replay.

    Sorry I forward to mention the software/data workflow.

    So I am creating a plugin that sits inside Autodesk's AutoCAD Civil 3D. Civil 3D has .net capability with their respective namespaces imported.

    So I export data from AutoCAD Civil 3D into Excel and then into Drains. Drains can import data from MS Office Clipboard and XML. So that is why I am importing and exporting data into and from excel via MS Office clipboard.

    So my forum query was how to importing and exporting data into and from excel via MS Office clipboard. Not copying data from excel workbook to excel workbook.


    Tuesday, January 12, 2016 6:53 AM
  • Hi tfitzhardinge,

    As far as I know, excel object model don’t provide the way to do that. We could use Range.Value property to get or set value, then you may put and read the value to/from clipboard through .Net object. You are using VB or C# language, I suggest that you could post it to C# or VB forum for the issue of how to get/set data from/to clipboard.

    On the other hand, you also could use ADO to connect to excel file and do other things.

    # How To Transfer Data from ADO Data Source to Excel with ADO

    https://support.microsoft.com/en-us/kb/295646

    # How to transfer data from an ADO Recordset to Excel with automation

    https://support.microsoft.com/en-us/kb/246335

    Regards

    Starain


    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, January 12, 2016 9:00 AM
    Moderator
  • Hi

    I think stating in my question both clipboard and excel confused the Microsoft Staff and moved the forum post into the wrong forum category. From my research the Clipboard Class is part of the Windows.Forms namespace. The forum post should be moved to the relevant forum category and should not be in the VSTO category.

    My main query is getting and setting data to the clipboard. Not excel.

    Sunday, January 17, 2016 8:14 AM
  • Hi tfitzhardinge,

    You post this thread in Visual Studio General Forum, which is used to discussion the question/issue of Visual Studio. I think the C#, VB or CLR forum is the better place for this issue. You may create a new thread in either of these forums.

    Regards

    Starain


    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.

    Monday, January 18, 2016 1:38 AM
    Moderator