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

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

---

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!

---

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.

链接地址: http://www.djcxy.com/p/35770.html

上一篇: 尝试将工作表复制到vba中的现有工作簿

下一篇: 在类/对象中使用Excel对象（工作表，工作簿...）