为什么Delphi Excel OLE函数不符合规范? [英] Why Delphi Excel OLE functions does not correspond to the specificiation?

问题描述

我正在尝试以编程方式从Delphi代码，扩展问题和源代码在从Delphi(OLE)复制Excel粘贴所有格式的文件?

I am trying to copy-paste from one Excel into another Excel programmatically from the Delphi code, the extded question and the source code is in Excel Copy-Paste from Delphi (OLE) with all the formatting?

可以按照

It is possible to copy-paste the column-width and data&formatting in Excel applications following https://www.extendoffice.com/documents/excel/1867-excel-copy-column-width.html using 2 consecutive operations from the Excel applications: 1) Paste Special... - Other Paste Options - Column Widths; 2) Paste Special... - Other Paste Options - All Using Source Theme.

Excel规范 https://docs.microsoft .com/en-us/office/vba/api/excel.range.pastespecial 和

Excel specification https://docs.microsoft.com/en-us/office/vba/api/excel.range.pastespecial and https://docs.microsoft.com/en-us/office/vba/api/excel.xlpastetype defines, that the respective codes for those 2 operations are:

xlPasteColumnWidths     8   Copied column width is pasted.
xlPasteAllUsingSourceTheme  13  Everything will be pasted using the source theme.

因此，我的问题的解决方案应该是:

So, the solution for my problem should be:

Sheet.Range['A1','CJ26'].PasteSpecial(8);
Sheet.Range['A1','CJ26'].PasteSpecial(13);

但这创建了一个非常奇怪的excel-不复制列宽，复制格式，但是将数据复制为超链接.

But that creates very strange excel - column widths are not copied, formatting is copied, but data are copied as the hyperlinks.

Excel_TLB显示常量确实正确:

Excel_TLB shows that constants are correct indeed:

type
  XlPasteType = TOleEnum;
const
  xlPasteAllUsingSourceTheme = $0000000D; //13
  xlPasteColumnWidths = $00000008; //8
  xlPasteValidation = $00000006;

因此，很明显，Delphi Excel OLE不符合规范，即具有Excel规范或Excel库的东西已经变质了！该怎么办?

So, clearly, Delphi Excel OLE does not conform to the specification, i.e. something with Excel specification or with Excel libraries has gone bad! What to do?

我手动重复了此复制粘贴序列并记录了VB宏-该宏正是我要运行的Delphi代码:

I repeated this sequence of copy-paste manually and recorded VB macro - this macro is exactly the Delphi code which I am trying to run:

Sub Macro1()
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone _
        , SkipBlanks:=False, Transpose:=False
    Range("E10").Select
    Application.CutCopyMode = False
End Sub

推荐答案

CreateOleObject('Excel.Application')的两个单独调用； Delphi代码中的代码已完成，这就是为什么粘贴无法按预期方式以编程方式工作的原因.我为同一Excel.Application实例的单独工作簿调用复制/粘贴，然后PastSpecial(代码8和13)按预期工作.这样就解决了这个问题.

Two separate calls of CreateOleObject('Excel.Application'); in the Delphi code were made and that is why the paste did not work programmatically as expected. I copy/paste is called for the separate workbooks of the same instance of the Excel.Application, then PastSpecial (with codes 8 and 13) works as expected. That solves the issue.

这篇关于为什么Delphi Excel OLE函数不符合规范?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！