use Excel Objects (Worksheet, Workbooks ...) in Classes/Objects

Question

i've got a problem with the import of Excel Worksheets into a summary Workbook.

First i try to import the data as DataTable, using:

`Dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

            tableName = GetTableNames(Dt);

            com = new OleDbDataAdapter(SqlQuery(tableName[0]), conn);

            com.Fill(DtSet);

            Dt = DtSet.Tables[0];`

works fine except the fact, that some of the headline got lost, after copy the data to a new sheet:

private static void ImportData(DataTable source, string p)
    {
        int pos = Globals.ThisWorkbook.Sheets.Count;
        object lastSheet = Globals.ThisWorkbook.Sheets.get_Item(pos);
        Globals.ThisWorkbook.Sheets.Add(Type.Missing, lastSheet, Type.Missing, Type.Missing);
        Excel.Worksheet ws = (Excel.Worksheet)Globals.ThisWorkbook.ActiveSheet;
        ws.Name = p;
        // column headings
        for (var i = 0; i < source.Columns.Count; i++)
        {
            ws.Cells[1, i + 1] = source.Columns[i].ColumnName;
        }

        // rows
        for (var i = 0; i < source.Rows.Count; i++)
        {
            // to do: format datetime values before printing
            for (var j = 0; j < source.Columns.Count; j++)
            {
                ws.Cells[i + 2, j + 1] = source.Rows[i][j];
            }
        }
    }

Second, i hoped to import fix that by importing it via interop so i implemented this one:

        public Excel.Worksheet GetWorksheet(string path)
    {
        Excel.Application vApp = new Excel.Application();
        Excel.Workbook vBook = vApp.Workbooks.Open(path);
        Excel.Worksheet ws = vBook.Sheets.get_Item(1);
        vBook.Close();
        vApp.Quit();
        return ws;
    }

seems to work also fine, until i try to copy this sheet to the active Workbook:

        private static void ImportData(Excel.Worksheet source, string p)
    {
        int pos = Globals.ThisWorkbook.Sheets.Count;
        source.Copy(Globals.ThisWorkbook.Worksheets[pos]);            
    }

this should copy the source to the last position within Globals.ThisWorkbook... but all i got is a COMException

Now i run out of ideas how to fix this. Hope you can help.

Mirko

Anoop H.N · Accepted Answer · 2016-11-29 11:21:36Z

1

I suggest not to use COM objects. Try, OpenXML and ClosedXML to create excel sheets dynamically.

[http://www.c-sharpcorner.com/article/closed-xml-to-read-excel-files-in-asp-net-c-sharp/]

Hope this helps!

answered Nov 29, 2016 at 11:21

Anoop H.N

1,2621 gold badge15 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Exar666Kun Over a year ago

Thansk for your comment, the problem is using external libraries or software isn't allowed in my company before an approval. The software needs to be finished within two days, the approval takes to long. :-)

Anoop H.N Over a year ago

Yes I can understand. You are using Microsoft.Interop.Excel Right? I too had used it. Our product was hosted in the cloud server purchased by client. We could not convince our client to install Microsoft Office Primary Interop Assembly package in the server. So I used openXML and ClosedXML which is absolutely free. Performance was also great with efficient code.

Community · Accepted Answer · 2017-05-23 11:53:01Z

0

thanks to the guy posted this here: StackOverflow

I need to load the source workbook into the active application instead of starting an own application and get the worksheet from there:

            Excel.Application destApp = Globals.ThisWorkbook.Application;
            Excel.Workbook destWB = destApp.Workbooks[1];
            Excel.Workbook sourceWB = destApp.Workbooks.Open(d.Path);
            int pos = Globals.ThisWorkbook.Sheets.Count;
            Excel.Worksheet destWS = ((Excel.Worksheet)destWB.Worksheets[pos]);
            Excel.Worksheet sourceWS = ((Excel.Worksheet)sourceWB.Worksheets[1]);
            sourceWS.Name = d.Name;
            sourceWB.Save();
            sourceWS.Copy(destWS);
            sourceWB.Close();

Thats all and its pretty fast.

edited May 23, 2017 at 11:53

CommunityBot

11 silver badge

answered Nov 29, 2016 at 12:22

Exar666Kun

1451 silver badge18 bronze badges

Collectives™ on Stack Overflow

use Excel Objects (Worksheet, Workbooks ...) in Classes/Objects

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related