如何在Excel中的两个指定列之间插入空列 [英] How to insert empty column between two specified column in Excel

问题描述

我必须在Excel中的两个指定列之间插入2个空列，并且需要使用C#进行计算.请您指导我如何执行此操作.

谢谢

Hi,


I Have to insert 2 empty column between two specified column in Excel and need to do calculation using C#.Could you please guide me how to do this.

Thanks

推荐答案

插入新列的实际任务相当琐碎.不幸的是，几乎无法确定将被视为可接受(有用)的答案.

您尝试了什么，结果如何?

在Excel中使用宏记录器，我们可以看到，如果右键单击C列，然后选择插入"，我们将获得以下VBA代码:

The actual task of inserting a new column is fairly trivial. Unfortunately, it''s near impossible to ascertain what will be considered an acceptable (useful) answer.

What have you tried and what was the result?

Using the macro-recorder in Excel, we can see that if we right-click on column C then select "Insert", we get the following VBA code:

Sub Macro1()
'
' Macro1 Macro
'
    Columns("C:C").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
End Sub

在帮助中搜索列"，我们发现它是工作表对象的成员.调用Columns方法的结果是一个范围.
首先指定此范围
然后被选中.完成后，将在范围上调用Insert方法.

查看Excel帮助中的Range.Insert，我们看到它带有两个(可选)参数-Shift和CopyOrigin(两个VARIANT)

我们很懒，所以我们将看看如果没有两个参数中的任何一个，它是否会产生正确的结果.回到VBA编辑器中，删除两个参数并运行宏.美丽！与我们之前录制的结果相同(在这种情况下).因此，我们暂时放弃这些参数(对于尚未发生的情况，可能需要稍后再添加它们)

因此，有了这些知识，我们就可以将其放入一个独立的应用程序中.

我不使用C#，因此无法为您提供具体指导-但这是在gcc中从C ++使用时的过程.由于您使用的是C#，它将变得更加简单和整洁，因为您将能够添加对MS Office COM对象的引用(我忘了它的名称)，并且仅使用对象本身，而无需全部令人讨厌的IDispatch gobble-d-gook.

Searching the Help for "Columns", we find it to be a member of the Worksheet object. The result of a call to the Columns method is a Range.
This range is first specified
Then is selected. Once done, the Insert method is called on the range.

Looking at the Excel help for Range.Insert, we see that it takes two (optional) parameters - Shift and CopyOrigin (both VARIANTs)

We''re lazy, so we''ll see if it produces the right result without either of the two params. Back to the VBA editor, remove the two params and run the macro. Beautifull! Just the same result (in this case) as it was when we recorded it earlier. So, we''ll ditch the params for now (may have to add them again later for situations not imagained yet)

So, armed with this knowledge we''re ready to put it into a standalone app.

I don''t use C#, so can''t guide you on specifics - but this is the process when used from C++ in gcc. Since you''re using C#, it will be much simpler and cleaner since you''ll be able to Add a Reference to the MS Office COM object (I forget it''s name) and just use the objects themselves, without all of the nasty IDispatch gobble-d-gook.

// Initialize COM for this thread...
   CoInitialize(NULL);

   // Get CLSID for our server...
   CLSID clsid;
   HRESULT hr = CLSIDFromProgID(L"Excel.Application", &clsid);

   if(FAILED(hr)) {

      MessageBox(NULL, L"CLSIDFromProgID() failed", L"Error", 0x10010);
      return -1;
   }

   // Start server and get IDispatch...
   IDispatch *pXlApp;
   hr = CoCreateInstance(clsid, NULL, CLSCTX_LOCAL_SERVER, IID_IDispatch, (void **)&pXlApp);
   if(FAILED(hr)) {
      MessageBox(NULL, L"Excel not registered properly", L"Error", 0x10010);
      return -2;
   }

// ommitted code
// Add code here to make the application visible

// ommited code
// Add code here to either 
//  (a) load a pre-existing file 
// OR
//  (b) get the workbooks object and call the "Add" method on it

    {
        VARIANT result, var1;
        wstring rangeStr;
        IDispatch *pRange;

        // Specify a range that selects a single column
        VariantInit(&result);
        rangeStr = L"C:C";
        var1.bstrVal = SysAllocString(rangeStr.c_str());
        var1.vt = VT_BSTR;
        AutoWrap(DISPATCH_PROPERTYGET, &result, pXlSheet, L"Range", 1, var1);
        pRange = result.pdispVal;

        // select this range
        AutoWrap(DISPATCH_METHOD, NULL, pRange, L"Select", 0);

        // insert a new column
        AutoWrap(DISPATCH_METHOD, NULL, pRange, L"Insert", 0);

        // done with the range. let excel know
        pRange->Release();
    }

这篇关于如何在Excel中的两个指定列之间插入空列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！