Procedure BatchdbgridehdataToExcel (DBGRID: TDBGRIDEH; TITLE: STRING; DRAWGRIDLINE: BOOLEAN; RANGEFIELDS: TSTRINGLIST);
This process exports data in TDBGRIDEH to Excel. This process can export TDBGRIDEH multilayer headers into Excel and can also combine the same value of the given field, for example:
parameter:
DBGRID: TDBGRIDEH is the title to export the grid control of the data, title to the report, DrawGridline controls whether to draw grid lines, Rangefields is a list of fields that require merged data.
Fill in the following code in the OnClick event of the Print button:
var RangeFields: TStringList; begin file: // true draw the grid lines // RangeFields: = TStringList.Create; RangeFields.Add ( 'TJDWMC'); try if RadioButton1.Checked then BatchDBGridEhDataToExcel (DBGrid_JTGS, Caption, true, RangeFields) Else BatchdbgridehdattoExcel (dbgrid_lyj, caption, true, rangefields); finally rangefields.free; end; end;
Start the Excel program after execution, the display interface is as follows:
Note: When the merge grid occurs, it will appear as a "No Merger Grid" dialog, please shock "Yes".
It needs to be declared:
const file: // Excel used constants // xlHairline = $ 00000001; xlMedium = $ FFFFEFD6; xlThick = $ 00000004; xlThin = $ 00000002; const file: // Excel used constants // xlContinuous = $ 00000001; xlDash = $ FFFFEFED; xlDashDot = $ 00000004; xlDashDotDot = $ 00000005; xlDot = $ FFFFEFEA; xlDouble = $ FFFFEFE9; xlSlantDashDot = $ 0000000D; xlLineStyleNone = $ FFFFEFD2; const xlAll = $ FFFFEFF8; xlAutomatic = $ FFFFEFF7; xlBoth = $ 00000001; xlCenter = $ FFFFEFF4; xlChecker = $ 00000009; xlCircle = $ 00000008; xlCorner = $ 00000002; xlCrissCross = $ 00000010; xlCross = $ 00000004; xlDiamond = $ 00000002; xlDistributed = $ FFFFEFEB; xlDoubleAccounting = $ 00000005; xlFixedValue = $ 00000001; xlFormats = $ FFFFEFE6; xlGray16 = $ 00000011; xlGray8 = $ 00000012; xlGrid = $ 0000000F; xlHigh = $ FFFFEFE1; xlInside = $ 00000002; xlJustify = $ FFFFEFDE; xlLightDown = $ 0000000D; xlLightHorizontal = $ 0000000B; xlLightUp = $ 0000000E; xlLightVertical = $ 0000000C; xlLow = $ FFFFEFDA; xlManual = $ FFFFEFD9; xlMinusValues = $ 00000003; xlModule = $ FFFFEFD3; xlNextToAxis = $ 00000004; xlNone = $ FFFFEFD2; xlNotes = $ FFFFEFD0; xlOff = $ FFFFEFCE; xlOn = $ 00000001; xlPercent = $ 00000002; xlPlus = $ 00000009; xlPlusValues = $ 00000002; xlSemiGray75 = $ 0000000A; xlShowLabel = $ 00000004 ; xlShowLabelAndPercent = $ 00000005; xlShowPercent = $ 00000003; xlShowValue = $ 00000002; xlSimple = $ FFFFEFC6; xlSingle = $ 00000002; xlSingleAccounting = $ 00000004; xlSolid = $ 00000001; xlSquare = $ 00000001; xlStar = $ 00000005;
xlStError = $ 00000004; xlToolbarButton = $ 00000002; xlTriangle = $ 00000003; xlGray25 = $ FFFFEFE4; xlGray50 = $ FFFFEFE3; xlGray75 = $ FFFFEFE2; xlBottom = $ FFFFEFF5; xlLeft = $ FFFFEFDD; xlRight = $ FFFFEFC8; xlTop = $ FFFFEFC0; xl3DBar = $ FFFFEFFD; xl3DSurface = $ FFFFEFF9; xlBar = $ 00000002; xlColumn = $ 00000003; xlCombination = $ FFFFEFF1; xlCustom = $ FFFFEFEE; xlDefaultAutoFormat = $ FFFFFFFF; xlMaximum = $ 00000002; xlMinimum = $ 00000004; xlOpaque = $ 00000003; xlTransparent = $ 00000002; xlBidi = $ FFFFEC78 ; xlLTR = $ FFFFEC75;; xlRTL = $ FFFFEC74; xlFullScript = $ 00000001; xlLatin = $ FFFFEC77; FFFFEC76 xlContext = $ xlPartialScript = $ 00000002; xlMixedScript = $ 00000003; xlMixedAuthorizedScript = $ 00000004; xlVisualCursor = $ 00000002; xlLogicalCursor = $ 00000001; xlSystem = $ 00000001; xlPartial = 00000003; Xlbidinumeral = $ 00000003; XLbidicalendar = $ 00000003; XLGREGORIAN = $ 00000002; XLcomplete = $ 00000004; Xlscale = $ 00000003; XLClosed = $ 00000003; xlColor1 = $ 00000007; xlColor2 = $ 00000008; xlColor3 = $ 00000009; xlConstants = $ 00000002; xlContents = $ 00000002; xlBelow = $ 00000001; xlCascade = $ 00000007; xlCenterAcrossSelection = $ 00000007; xlChart4 = $ 00000002; xlChartSeries = $ 00000011; xlChartShort = $ 00000006; xlChartTitles = $ 00000012; xlClassic1 = $ 00000001; xlclassic2 = $ 00000002; xlclassic3 = $ 00000003; xl3deffects1 = $ 0000000d; xl3deffects2 = $ 0000000E; XLABOVE = $ 00000000; XLACCOUNTING1 = $ 00000004; xLaccounting2 =
$ 00000005; xlAccounting3 = $ 00000006; xlAccounting4 = $ 00000011; xlAdd = $ 00000002; xlDebugCodePane = $ 0000000D; xlDesktop = $ 00000009; xlDirect = $ 00000001; xlDivide = $ 00000005; xlDoubleClosed = $ 00000005; xlDoubleOpen = $ 00000004; xlDoubleQuote = $ 00000001; xlEntireChart = $ 00000014; xlExcelMenus = $ 00000001 ; xlExtended = $ 00000003; xlFill = $ 00000005; xlFirst = $ 00000000; xlFloating = $ 00000005; xlFormula = $ 00000005; xlGeneral = $ 00000001; xlGridline = $ 00000016; xlIcons = $ 00000001; xlImmediatePane = $ 0000000C; xlInteger = $ 00000002; xlLast = $ 00000001; xlLastCell = $ 0000000B ; xlList1 = $ 0000000A; xlList2 = $ 0000000B; xlList3 = $ 0000000C; xlLocalFormat1 = $ 0000000F; xlLocalFormat2 = $ 00000010; xlLong = $ 00000003; xlLotusHelp = $ 00000002; xlMacrosheetCell = $ 00000007; xlMixed = $ 00000002; xlMultiply = $ 00000004; xlNarrow = $ 00000001; xlNoDocuments = $ 00000003; xlopen = $ 00000002; xloutside = $ 00000003; Xlreference = 00000004; XLSemiautomati c = $ 00000002; xlShort = $ 00000001; xlSingleQuote = $ 00000002; xlStrict = $ 00000002; xlSubtract = $ 00000003; xlTextBox = $ 00000010; xlTiled = $ 00000001; xlTitleBar = $ 00000008; xlToolbar = $ 00000001; xlVisible = $ 0000000C; xlWatchPane = $ 0000000B; xlWide = $ 00000003; xlWorkbookTab = $ 00000006; xlWorksheet4 = $ 00000001; xlWorksheetCell = $ 00000003; xlWorksheetShort = $ 00000005; xlAllExceptBorders = $ 00000006; xlLeftToRight = $ 00000002; xlTopToBottom = $ 00000001; xlVeryHidden = $ 00000002;
XLDrawingObject = $ 0000000E; Const {THE LIST OF VTFONT STYLES}
{FontstyleConstants}
VtfontStylebold = 1; vtfontStyleitalic = 2; vtfontstyleustom = 4;
{THE LIST OF VTFONT EFFECTS}
{FonteffectsConstants}
Vtfonteffectstrikethrough = 256; vtfonteffectunderline = 512;
-------------------------------------------------- -------------------------------------------------- ---
procedure SetTitleInExcel (Sheet: OleVariant; FirstRow, FirstCol, LastRow, LastCol: integer; Title: string); var RangeStr: string; Range: Variant; begin Sheet.Activate; RangeStr: = GetRangStr (FirstRow, FirstCol, LastRow, LastCol); Range: = Sheet.Range [RangeStr]; Range.Merge (TRUE); Range.Font.Size: = 14; Range.Font.Name:= 'Black Body'; Range.Font.FontStyle: = VTFONTSTYLD; Range.horizontaLAlignment: = XLCenter; Range.VerticalAlignment: = XLCenter; Range.Value: = Title; End;
Function GetrangStr (Firstcol: Integer): String; Var IA, IB: Integer; Begin Result: = '; if (Firstrow <1) or (Lastrow <1) OR Lastcol <1).
IA: = firstcol div 26; ib: = firstcol mod 26; if = 0 the begin IA: = IA-1; IB: = 26;
IA = 0 Then Result: = CHR (ORD ('A') IB-1) INTTOSTR (FIRSTROW) ':' Else Result: = CHR (ORD ('A') IA-1) CHR ( ORD ('A') IB-1) INTOSTR (FIRSTROW) ':';
IA: = LastCol Div 26; IB: = Lastcol MOD 26; IF IB = 0 THEN BEGIN IA: = IA-1; IB: = 26; END;
IA = 0 Then Result: = Result CHR (ORD ('a') IB-1) INTTOSTR (LASTROW) Else Result: = Result CHR (ORD ('A') IA-1) CHR ( ord ( 'A') iB-1) IntToStr (LastRow); end; procedure DrawGridInExcel (Sheet: OleVariant; FirstRow, FirstCol, LastRow, lastCol: integer); var RangeStr: string; Range: Variant; begin Sheet.Activate ; RangeStr: = GetRangStr (FirstRow, FirstCol, LastRow, lastCol); Range: = Sheet.Range [RangeStr]; Range.Columns.Interior.ColorIndex: = 0; Range.Borders.LineStyle: = xlHairline; Range.Font.Size : = 8; Range.Font.name:= '体 _GB2312'; end;
Procedure TransmuiltTitrestr (Text: String; Tstrings); Var str: string; index: integer; begin str: = text; list ('|', str); while index> 0 do beg, while index> 0 do beg, = POS ('|' .Add (Str, 1, INDEX-1); str: = copy (Str, Index 1, Length (Str) -index); index: = POS ('|', str); end; if index = 0 Then List.Add (STR); END;
Function My_DataSetToExcelSheet (DataSet: TDataSet; m_Fields: tstringlist; Sheet: OleVariant; RangeFields: TStrings; DrawGridLine: Boolean; var FirstRow, FirstCol: integer): Boolean; var DataFirstRow, Row, Col, i, j: Integer; BK: TBookMark; LastValue, CurrentValue: String; RangeStr: String; Range: Variant; RangefirstRow, Rangefirstcol: Integer; List: TstringList; File: // A string of string of strings for storage composite headings // maxTvcount: integer; // Title the largest portrait Row // Begin Result: = false; if not dataset.active kilns; bk: = Dataset.getBookmark; dataset.disablecontrols; sheet.actiVate; try file: // custom complex column title // maxTVcount: = 0; List : = TStringList.create; Try Col: = firstcol; for i: = 0 to m_fields.count-1 do beg: = firstrow;
Transmuilttitlestr (m_fields.strings [i]). DisplayLabel, List); if list.count> maxTvcount dam = list.count; for j: = 0 to list.count-1 do begin sheet.cells Row, col): = List.Strings [J]; INC (ROW); End; INC; End; end;
File: // Draw Grid // if DrawGridline Then Begin DrawGridinexcel (Sheet, First, 1, FirstVcount.Recordcount MaxTvcount-1, M_Fields.count); End; File: // Transverse Merge Title Grid // for I: = firstrow to first-type file: // Record the current row // ROW: = i; file: // Calculate // if m_fields.count> 0 THEN BEGIN RANGEFIRSTCOL: = 1; LastValue: = Sheet.cells.Item [ROW, RANGEFIRSTCOL];
For J: = 2 to m_fields.count do begin currentValue: = sheet.cells.item [Row, J]; if CurrentValue <> LastValue1 Begin file: // Merge cell // if LastValue <> 'Then Begin RangeStr : = GetrangStr (ROW, RANGEFIRSTCOL, ROW, J-1); Range: = Sheet.Range [RangeSTR]; File: //Range.Merge (false); Range.Mergecells: = true; Range.WrapText: = true; Range.horizontalalignment: = xlcenter; Range.VerticalAlignment: = XlCenter; Range.Value: = LastValue;
Rangefirstcol: = J; LastValue: = Sheet.cells.Item [ROW, RANGEFIRSTCOL]; END; End; File: // Merge cell // if LastValue <> 'Then Begin RangeStr: = GetrangStr (ROW, Rangefirstcol, Row , m_fields.count); Range: = Sheet.Range [RANGESTR]; // range.merge (false); Range.Mergecells: = true; Range.WrapText: = true; range.horizontalalignment: = xlCenter; Range.VerticalAlignment: = XlCenter; Range.Value: = LastValue; End; RangefirstCol: = m_fields.count 1; LastValue: = Sheet.cells.Item [ROW, RANGEFIRSTCOL]; END; END; File: // Longitudinal combined header grid, will Longitudinal lasting 其 其 其 其 空:> b 1> 1>> .Cells.Item [J, I]; if currentValue <> '' Then Begin if j <> firstrow maxTvcount-1 Then Begin file: // Merge cell // rangeStr: = GETR Angstr (J, I, FIRSTROW MAXTVCOUNT-1, I); Range: = Sheet.Range [RangeStr]; Range.Merge (false); Range.WrapText: = true; Range.hor; Range.VerticalAlignment: = XLCENTER; Range.Value: = CurrentValue; End; Break; End;
File: // The first index number of data // datafirstrow: = firstrow maxTvcount;
Row: = DataFirstROW;
File: // Fill in the table content // dataset.first; while not dataset.eof do begin col: = 1;
For i: = 0 to m_fields.count-1 do begin sheet.cells (row, col): = dataset.fieldbyname (m_fields.strings [i]). asstring; inc (col (amount; end; row: = row 1 ;
Dataset.next;
IF m_fields.count> 0 THEN col: = col-1; file: // Merge item value // for i: = 0 to Rangefields.count-1 Do Begin Col: = m_fields.indexof (Rangefields.Strings [i ]) 1; if DataSet.Recordcount> 0 Then Begin Rangefirstrow: = DataFirstRow; LastValue: = Sheet.cells.Item [RangefirstRow, col];
For J: = 1 to DataSet.Recordcount-1 Do Begin CurrentValue: = Sheet.cells.Item [DataFirstRow J, Col]; if CurrentValue <> LastValue The Begin File: // Merge cell // rangeStr: = GetrangStr RangeFirstRow, Col, DataFirstRow j-1, Col); Range: = Sheet.Range [RangeStr]; Range.Merge (false); Range.WrapText: = true; Range.HorizontalAlignment: = xlCenter; Range.VerticalAlignment: = xlCenter Range.Value: = LastValue;
RangeFirstRow: = DataFirstRow j; LastValue: = Sheet.Cells.Item [RangeFirstRow, Col]; end; end; file: // merged cells // RangeStr: = GetRangStr (RangeFirstRow, Col, DataFirstRow DataSet.RecordCount-1 , Range: = Sheet.Range [RangeStr]; Range.Merge (false); Range.WrapText: = true; range.horizontalalignment: = xlcenter; Range.Value: = LastValue;
Rangefirstrow: = DataFirstRow Dataset.RecordCount; LastValue: = Sheet.cells.Item [Rangefirstrow, Col]; End;
Result: = True; finally DataSet.GotoBookMark (BK); DataSet.EnableControls; end; end; procedure BatchDBGridEhDataToExcel (DBGrid: TDBGridEh; Title: string; DrawGridLine: Boolean; RangeFields: TStringList); var s: tstringlist; i: integer; Begin if not dbgrid.datasource.DataSet.active the beginning! ', mtwarning, [mbok], 0); exit; end; s: = tstringlist.create; try for i: = 0 To DBGrid.Columns.Count-1 do begin s.Add (DBGrid.Columns [i] .FieldName); DBGrid.DataSource.DataSet.FieldByName (DBGrid.Columns [i] .FieldName) .DisplayLabel: = DBGrid.Columns [i] .Title.caption; end; my_datasettoExcel (dbgrid.datasource.dataset, s, rangefields, drawgridline, true, title, ''); finally s.free; end; end;
