EXCEL插入圖檔並指定圖檔大小

EXCEL插入圖檔並指定圖檔大小
Use Variants, COMOBJ;

var MyExcel: Variant;
aQA,aGONO1,aCUNO,aCLR,Exa:string [18];
x,y, Z,iSheets,iCOL:integer;
ExcelSheet: Variant;
begin
Exa:='ABCDEFGHIJKLMNOPQRS';
Z:=dbgrid.FieldCount;
deletefile(ExcelFileName);
MyExcel := CreateOleOBject('Excel.Application');
MyExcel.WorkBooks.Add;
MyExcel.Visible := True;
MyExcel.WorkBooks[1].Saveas(ExcelFileName);
dbgrid.DataSource.DataSet.First;
MyExcel.Range['A1:J1'].Select;//選取一整行
MyExcel.Selection.Merge;

iSheets:=1;//切換業籤用
MyExcel.WorkBooks[1].WorkSheets[iSheets].Cells[1,1] := ''''+ GetRefLookupValue(cdsData, 'QA', 'QANM');
//設定sheet名稱
MyExcel.WorkBooks[1].WorkSheets[iSheets].Name :=GetRefLookupValue(cdsData, 'QA', 'QANM');
y:=2;
for x:=1 to dbgrid.FieldCount do
begin
//MyExcel.WorkBooks[1].WorkSheets[1].Cells[y,x] := dbgrid.Columns[x-1].title.caption;
// 將該欄設為標選
//MyExcel.WorkBooks[1].WorkSheets[1].Cells[y,x].Select;
// 將標題欄位變粗體字
//MyExcel.Selection.Font.Bold := true;
// 設定欄位寬度
if dbgrid.Fields[x-1].FieldName = 'PICNAM' then
begin
MyExcel.WorkBooks[1].WorkSheets[iSheets].Columns[x].ColumnWidth := 15;
end
else
MyExcel.WorkBooks[1].WorkSheets[iSheets].Columns[x].ColumnWidth := dbgrid.Fields[x-1].DisplayWidth;
end;

//inc(y);
aQA:=dbgrid.DataSource.DataSet.FieldByName('QA').AsString;

while not dbgrid.DataSource.DataSet.eof do
begin
//品牌不同時換頁
if aQA<>dbgrid.DataSource.DataSet.FieldByName('QA').AsString then
begin
MyExcel.WorkSheets.Add;
MyExcel.WorkSheets[iSheets].Activate;
//設定sheet名稱
MyExcel.WorkBooks[1].WorkSheets[iSheets].Name :=GetRefLookupValue(cdsData, 'QA', 'QANM');
MyExcel.Range['A1:J1'].Select;//選取一整行
MyExcel.Selection.Merge;
MyExcel.WorkBooks[1].WorkSheets[iSheets].Cells[1,1] := ''''+GetRefLookupValue(cdsData, 'QA', 'QANM');
for x:=1 to dbgrid.FieldCount do
begin
// 設定欄位寬度
if dbgrid.Fields[x-1].FieldName = 'PICNAM' then
MyExcel.WorkBooks[1].WorkSheets[iSheets].Columns[x].ColumnWidth := 15
else
MyExcel.WorkBooks[1].WorkSheets[iSheets].Columns[x].ColumnWidth := dbgrid.Fields[x-1].DisplayWidth;
end;
y:=2;
end;

MyExcel.WorkBooks[1].WorkSheets[iSheets].Rows[y].RowHeight:=50;
MyExcel.WorkBooks[1].WorkSheets[iSheets].Columns[x].Autofit;
iCOL:=2; //明細資料從第2個欄位開始丟，第一ㄍ欄位丟圖檔
for x:=1 to dbgrid.FieldCount do
begin
if dbgrid.Fields[x-1].FieldName = 'PICNAM' then
begin
if FileExists(Pchar(dbgrid.Fields[x-1].AsString)) then
begin
//插入圖檔
//Note:1.圖檔會自動等比例縮放，所以先只設定寬度即可，否則圖檔會用最小像素顯示
//2.只適用EXCEL2003以下版本
//aPIC:= MyExcel.WorkBooks[1].WorkSheets[iSheets].Pictures.Insert(dbgrid.DataSource.DataSet.FieldByName('PICNAM').Value);
//aPIC.ShapeRange.IncrementLeft(0);
//aPIC.ShapeRange.IncrementTop(0);
//aPIC.ShapeRange.LockAspectRatio := msoFalse; //圖檔大小LOCK
//aPIC.ShapeRange.height:=200;
//aPIC.ShapeRange.width:=110;
//取消底色
//aPIC.ShapeRange.PictureFormat.TransparentBackground := 1;
//aPIC.ShapeRange.PictureFormat.TransparencyColor := RGB(255, 255, 255);
//aPIC.ShapeRange.Fill.Visible := 0;

MyExcel.WorkBooks[1].WorkSheets[iSheets].Cells[y,1].Select;
MyExcel.WorkBooks[1].WorkSheets[iSheets].Pictures.Insert(dbgrid.Fields[x-1].Value);
//設定圖檔大小
MyExcel.WorkBooks[1].WorkSheets[iSheets].Pictures[1].ShapeRange.height:=40;
MyExcel.WorkBooks[1].WorkSheets[iSheets].Pictures[1].ShapeRange.width:=40;
end;
end
else
begin
if dbgrid.Fields[x-1].DataType =ftString then
begin
MyExcel.WorkBooks[1].WorkSheets[iSheets].Cells[y,iCOL] := ''''+dbgrid.Fields[x-1].DisplayText;
end
else
MyExcel.WorkBooks[1].WorkSheets[iSheets].Cells[y,iCOL] := dbgrid.Fields[x-1].DisplayText;
Inc(iCOL);
end;
end;
inc(y);

MyExcel.WorkBooks[1].WorkSheets[iSheets].Cells.Select;
MyExcel.Selection.Columns.AutoFit;
dbgrid.DataSource.DataSet.next;
end;
//MyExcel.WorkBooks[1].WorkSheets[iSheets].Cells.Select;
//MyExcel.Selection.Columns.AutoFit;

//合計：
MyExcel.WorkBooks[1].WorkSheets[1].Cells[y,4] := '合計：';
for x:=5 to 9 do
begin
MyExcel.WorkBooks[1].WorkSheets[1].Cells[y,x] := '=SUM('+Exa[x]+'3:'+Exa[x]+inttostr(y-1)+')';
MyExcel.WorkBooks[1].WorkSheets[1].Cells[y,x].Select;
MyExcel.Selection.Font.color:=clRed;
end;
MyExcel.WorkBooks[1].WorkSheets[1].Cells[y,10] := '=H'+Inttostr(y)+'*100/G'+Inttostr(y);
MyExcel.WorkBooks[1].WorkSheets[1].Cells[y,10].Select;
MyExcel.Selection.Font.color:=clRed;
end;


關於全型轉半形

全型轉半形Function
Function1

function AlphaToAscii(Source: WideString): String;
var
Alpha : WideString;
Ascii : string;
s1 : string;
I, J: Integer;
begin
Result := '';
Alpha := '－.／０１２３４５６７８９：；＜＝＞？ＡＢＣＤＥＦＧＨＩＪＫＬＭＮＯＰＱＲＳＴＵＶＷＸＹＺ｛＼｝︿─ａｂｃｄｅｆｇｈｉｊｋｌｍｎｏｐｑｒｓｔｕｖｗｘｙｚ';
ASCII := '-./0123456789:;<=>?ABCDEFGHIJKLMNOPQRSTUVWXYZ[\]^-abcdefghijklmnopqrstuvwxyz';
for I := 1 to Length(Source) do
begin
S1 := copy(Source,i,1);
J := pos(S1, Alpha );
if J > 0 then
begin
Result := Result + copy(ASCII,J,1);
end;
end;
end;

另一個全部全形轉半形方式

function TfmImport1.textchange(tt:String):String;
var
Chr : array [0..255] of char;
begin
Windows.LCMapString(GetUserDefaultLCID(),LCMAP_HALFWIDTH,PChar(tt),Length(tt)+ 1,chr,Sizeof(chr));
Result := Chr;
end;


資料庫 LOG 檔案太大怎麼清除？(MS SQL 清除 LOG 並縮減空間的語法)

一般時候我們都用以下的語法進行清理
DUMP TRANSACTION {DBName} WITH NO_LOG
可是實體的檔案大小卻不會減少，一樣佔用那麼多的硬碟空間。
我曾經在網路上找到以下的語法，分享給大家參考。

SET NOCOUNT ON DECLARE @LogicalFileName sysname, @MaxMinutes INT, @NewSize INT
USE {DBName ex HRMS} -- 要操作的資料庫名
SELECT
@LogicalFileName = '{Log file name ex HRMS_Log}', -- 日誌文件名
@MaxMinutes = 10, -- Limit on time allowed to wrap log. (M)
@NewSize = 1 -- 你想設定的日誌文件的大小(M)

-- Setup / initialize
DECLARE @OriginalSize int
SELECT @OriginalSize = size FROM sysfiles WHERE name = @LogicalFileName

SELECT 'Original Size of ' + db_name() + ' LOG is ' +
CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB'
FROM sysfiles WHERE name = @LogicalFileName

CREATE TABLE DummyTrans (DummyColumn char (8000) not null)

DECLARE @Counter INT, @StartTime DATETIME, @TruncLog VARCHAR(255)
SELECT @StartTime = GETDATE(), @TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY'

DBCC SHRINKFILE (@LogicalFileName, @NewSize)

EXEC (@TruncLog)

-- Wrap the log if necessary.
WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired
AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName)
AND (@OriginalSize * 8 /1024) > @NewSize
BEGIN -- Outer loop.
SELECT @Counter = 0
WHILE ((@Counter < @OriginalSize / 16) AND (@Counter < 50000))
BEGIN -- update
INSERT DummyTrans VALUES ('Fill Log')
DELETE DummyTrans
SELECT @Counter = @Counter + 1
END
EXEC (@TruncLog)
END

SELECT 'Final Size of ' + db_name() + ' LOG is ' +
CONVERT(VARCHAR(30),size) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(size*8/1024)) + 'MB'
FROM sysfiles WHERE name = @LogicalFileName

DROP TABLE DummyTrans
SET NOCOUNT OFF

如果不要想得那麼複雜和看那些資訊，直接這樣下眼睛閉一閉就過去了...

USE [@DBName]
BACKUP LOG [@DBName] WITH TRUNCATE_ONLY
DBCC SHRINKFILE (@LogicalFileName, @NewSize)

如何在程式一啟動時讓CapsLock大寫鍵亮起來

以下做法可以讓程式一開啟FormCreat的時候大寫鍵(CapsLock)就亮起來
如果使用者打到一半想輸入小寫的時候再自己按掉切換
當然，稍微修改一下，可以用來判斷大寫鍵(CapsLock)是否作用中

//宣告自定類別
type
TKeyType = (ktCapsLock, ktNumLock, ktScrollLock);

//設定是否Led作用

procedure TForm1.SetLedState(KeyCode: TKeyType; bOn: Boolean);
var
KBState: TKeyboardState;
Code: Byte;
begin
case KeyCode of
ktScrollLock: Code := VK_SCROLL;
ktCapsLock: Code := VK_CAPITAL;
ktNumLock: Code := VK_NUMLOCK;
end;
GetKeyboardState(KBState);
if (Win32Platform = VER_PLATFORM_WIN32_NT) then
begin
if Boolean(KBState[Code]) <> bOn then
begin
keybd_event(Code,
MapVirtualKey(Code, 0),
KEYEVENTF_EXTENDEDKEY,
0);

keybd_event(Code,
MapVirtualKey(Code, 0),
KEYEVENTF_EXTENDEDKEY or KEYEVENTF_KEYUP,
0);
end;
end
else
begin
KBState[Code] := Ord(bOn);
SetKeyboardState(KBState);
end;
end;

// Led全部打開

procedure TForm1.Button1Click(Sender: TObject);
begin
SetLedState(ktCapsLock, True); // CapsLock on
SetLedState(ktNumLock, True); // NumLock on
SetLedState(ktScrollLock, True); // ScrollLock on
end;

// Led全部關掉

procedure TForm1.Button2Click(Sender: TObject);
begin
SetLedState(ktCapsLock, false); // CapsLock off
SetLedState(ktNumLock, false); // NumLock off
SetLedState(ktScrollLock, false); // ScrollLock off
end;

//判斷大寫鍵狀態

if Odd(GetKeyState(vk_Capital)) then
begin
edtPassword.Hint:='大寫鍵(CapsLock)狀態：ON';
end
else
begin
edtPassword.Hint:='大寫鍵(CapsLock)狀態：OFF';
end;


列出sql索引資料

SELECT T.NAME AS TABLE_NAME, C.NAME AS COLUMN_NAME, K.COLID, I.NAME AS INDEX_NAME, I.INDID, K.KEYNO
FROM SYSINDEXES AS I LEFT JOIN SYSOBJECTS AS T ON I.ID = T.ID LEFT JOIN SYSINDEXKEYS AS K ON I.ID = K.ID AND I.INDID = K.INDID LEFT JOIN SYSCOLUMNS AS C ON I.ID = C.ID AND K.COLID = C.COLID
WHERE T.XTYPE = 'U' AND (I.NAME LIKE 'PK_%' OR I.NAME LIKE 'IX_%')
ORDER BY T.NAME, I.INDID, K.KEYNO, C.NAME

DataBase資訊SQL語法

SELECT NAME, DBID, SID, MODE, STATUS, STATUS2, CRDATE, RESERVED, CATEGORY, CMPTLEVEL, FILENAME, VERSION, ' ' AS YN
FROM master.dbo.sysdatabases
ORDER BY NAME