把格子改成文本格式用“分列”最彻底(如NumberFormate并不起作用,如同在GUI操作一样,设置单元格格式为文本,但不会调整显示。这个问题比较纠结,不知是否有人知道其原因)。
WorkSheet.Columns[1].NumberFormat:='@'好像也不稳定。
分列调用的是range.TextToColumns,可从宏录制中看到,这个比较详细http://www.c
各个参数在GUI上基本上是一一对应的。
package.cpath=[[C:\Program Files\Lua\5.1\clibs\?.dll]]
require "luacom"
function print_table(t) for k,v in pairs(t) do print(k,v) end end
excel = luacom.CreateObject("Excel.Application")
excel.Visible = true
excel.Workbooks:Add();
--luacom.ViewTypeLib(excel);
sheet=excel.Sheets(1);
local r=sheet:Range("E6");
r.Value2="ABC";
range=sheet:Range("C2");
--range.ColumnWidth=20;
--luacom.DumpTypeInfo(range.Interior);
--print(range.Style);
--luacom.DumpTypeInfo(range.Style);
--print(range.Style.Interior.Color);
--range.Style.Interior.Color=16711680;
--print(range.NumberFormatLocal);
--range.NumberFormate='@';
local cell=range.Cells(1,1);
cell:Select();
local covTrue={Type="bool", Value=true};
local covFalse={Type="bool", Value=false};
local covOptional={Type="error", Value=2147614724};
local covNull={Type="null", Value=0};
cell.Value2="123456789098765";
range:TextToColumns(range, 1, 1, covTrue, covTrue, covTrue, covTrue, covTrue, covTrue, {Type="string", Value="#"}, {{1,2}, {2,2}}, covNull, covNull, covNull);
local usedrange=sheet.UsedRange;
print(range.Rows.Row, range.Columns.Column);
print(sheet.Rows.Count, sheet.Columns.Count);
print(usedrange.Rows.Count, usedrange.Columns.Count);
--luacom.DumpTypeInfo(range.Rows);
--print(cell.TextToColumns);
--cell.ShrinkToFit=true;
--cell.WrapText=true;
--luacom.DumpTypeInfo(range.DisplayFormat());
local merg=sheet:Range("A3:B3");
merg:Merge(covTrue);
merg:BorderAround({Type="uint2", Value=1}, {Type="uint2", Value=4}, covNull, {Type="uint2", Value=255});
merg.Value2="merg";
--merg.Font.ColorIndex=3;
merg.Font.Color=0xFFAAFF;
merg.Font.Bold=true;
sheet.Columns(2).ColumnWidth=30;
sheet.Rows(2).RowHeight=40;
local typeinfo=luacom.GetTypeInfo(excel);
local typelib=typeinfo:GetTypeLib()
local enums=typelib:ExportEnumerations()
--print_table(enums);
cell.Interior.ColorIndex=43;
cell.Interior.Pattern=enums.Constants.xlSolid;
cell.HorizontalAlignment=enums.Constants.xlCenter;
cell.VertialAlignment=enums.Constants.xlCenter;
--print_table(enums.Constants);
do
local column=sheet.Columns("C");
column:Select();
local x=sheet.Application.Selection;
x.Value2="123456789098765";
x:TextToColumns(x, 1, 1, covTrue, covTrue, covTrue, covTrue, covTrue, covTrue, {Type="string", Value="#"}, {{1,2}, {2,2}}, covNull, covNull, covNull);
end
--
--excel=nil;
--完
public: Object^ TextToColumns(
Object^ Destination,
XlTextParsingType^ DataType,
XlTextQualifier^ TextQualifier,
Object^ ConsecutiveDelimiter,
Object^ Tab,
Object^ Semicolon,
Object^ Comma,
Object^ Space,
Object^ Other,
Object^ OtherChar,
Object^ FieldInfo,
Object^ DecimalSeparator,
Object^ ThousandsSeparator,
Object^ TrailingMinusNumbers
);
比较喜欢这篇http://dev-b
参考:
http://www.b
http://math2.org/luasear ch-2/luadist -extract/lua sql-2.1.1.di st/src/ado/a do.lua