代码之家 › 专栏 › 技术社区 › dreojs16

将包含Excel公式的数据对象数组插入给定范围内的现有Excel工作表中

excel-interop winforms excel c#

dreojs16 · 技术社区 · 7 年前

我想将对象粘贴到已经打开的Excel文件中的某个范围。

我应该补充一点,当datatable包含诸如“this is a test”之类的字符串而不是诸如“=SUM(A1:A5)”之类的Excel公式时,一切正常

这是我得到的。我忽略了什么?

    private void InsertBridgeCalcBlock()
    {
        Excel.Application xlApp = (Excel.Application)System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application");

        try
        {
            xlActiveCell = xlApp.ActiveCell;
            DataTable dt = new DataTable();

            try
            {
                using (SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM " + sDBBridgeCalcTable, conn))
                {
                    da.Fill(dt);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error Message:" + Environment.NewLine + ex);
            }

            object[,] BridgeCalcTable = new object[dt.Rows.Count + 1,dt.Columns.Count];
            for (var i = 0; i < dt.Rows.Count; i++)
                for (var j = 1; j < dt.Columns.Count; j++)
                {
                    BridgeCalcTable[i, j-1] = dt.Rows[i][j];
                }

            Excel.Range insertBridgeCalcTableRange = xlApp.Range[xlApp.ActiveSheet.Cells[xlActiveCell.Row-2, 11], xlApp.ActiveSheet.Cells[xlActiveCell.Row-2 + dt.Rows.Count - 1, 11 + dt.Columns.Count]]; // set insertrange
            xlApp.ActiveSheet.EnableCalculation = true;
            insertBridgeCalcTableRange.Value = BridgeCalcTable; // fill range with data

        }
        catch (Exception ex)
        {
            MessageBox.Show("Error Message:" + Environment.NewLine + ex);
        }
    }

异常内容如下:

    System.Runtime.InteropServices.COMException (0x800A03EC): 
    Exception from HRESULT: 0x800A03EC at System.RuntimeType.ForwardCallToInvokeMember(String memberName, BindingFlags flags, Object target, Int32[] aWarpperTypes, MessageData& msgData)
    at Microsoft.Office.Interop.Excel.Rage.ser_Value(obejct value) at LookApp2016.Form1.InsertBridgeCalcBlock() in ~~myfilelocation~~ line 2886

1 回复 | 直到 7 年前

dreojs16 7 年前

虽然我仍然想知道为什么Interop在数据表中使用Excel公式时引发异常,

通过输入Excel.Range.CopyFromRecordSet,我找到了一个可能的答案。像这样:

        ADODB.Recordset BridgeCalcRecordset = ConvertToRecordset(dt);
        insertBridgeCalcTableRange.CopyFromRecordset(BridgeCalcRecordset);

我用以下方法解决了这个问题:

     insertBridgeCalcTableRange.Formula = insertBridgeCalcTableRange.Value;

我的代码是相同的,但我正在使用此代码将我的数据集转换为ADODB记录集(他们说这样也更快): 网络明星在 http://www.nullskull.com/q/10057748/hi-all.aspx

    static public ADODB.Recordset ConvertToRecordset(DataTable inTable)
    {
        ADODB.Recordset result = new ADODB.Recordset();
        result.CursorLocation = ADODB.CursorLocationEnum.adUseClient;

        ADODB.Fields resultFields = result.Fields;
        System.Data.DataColumnCollection inColumns = inTable.Columns;

        foreach (DataColumn inColumn in inColumns)
        {
            resultFields.Append(inColumn.ColumnName
                , TranslateType(inColumn.DataType)
                , inColumn.MaxLength
                , inColumn.AllowDBNull ? ADODB.FieldAttributeEnum.adFldIsNullable :
                                         ADODB.FieldAttributeEnum.adFldUnspecified
                , null);
        }

        result.Open(System.Reflection.Missing.Value
                , System.Reflection.Missing.Value
                , ADODB.CursorTypeEnum.adOpenStatic
                , ADODB.LockTypeEnum.adLockOptimistic, 0);

        foreach (DataRow dr in inTable.Rows)
        {
            result.AddNew(System.Reflection.Missing.Value,
                          System.Reflection.Missing.Value);

            for (int columnIndex = 0; columnIndex < inColumns.Count; columnIndex++)
            {
                resultFields[columnIndex].Value = dr[columnIndex];
            }
        }

        return result;
    }

    static ADODB.DataTypeEnum TranslateType(Type columnType)
    {
        switch (columnType.UnderlyingSystemType.ToString())
        {
            case "System.Boolean":
                return ADODB.DataTypeEnum.adBoolean;

            case "System.Byte":
                return ADODB.DataTypeEnum.adUnsignedTinyInt;

            case "System.Char":
                return ADODB.DataTypeEnum.adChar;

            case "System.DateTime":
                return ADODB.DataTypeEnum.adDate;

            case "System.Decimal":
                return ADODB.DataTypeEnum.adCurrency;

            case "System.Double":
                return ADODB.DataTypeEnum.adDouble;

            case "System.Int16":
                return ADODB.DataTypeEnum.adSmallInt;

            case "System.Int32":
                return ADODB.DataTypeEnum.adInteger;

            case "System.Int64":
                return ADODB.DataTypeEnum.adBigInt;

            case "System.SByte":
                return ADODB.DataTypeEnum.adTinyInt;

            case "System.Single":
                return ADODB.DataTypeEnum.adSingle;

            case "System.UInt16":
                return ADODB.DataTypeEnum.adUnsignedSmallInt;

            case "System.UInt32":
                return ADODB.DataTypeEnum.adUnsignedInt;

            case "System.UInt64":
                return ADODB.DataTypeEnum.adUnsignedBigInt;

            case "System.String":
            default:
                return ADODB.DataTypeEnum.adVarChar;
        }
    }