|
 | 作者: advantech [advantech]
 论坛用户 |
登录 |
| access建的的一个表,表中有一个是长二进制数据的字段,如何才能将这个表导到excel中? |
| 地主 发表时间: 05-10-01 14:48 |
 | 回复: zhoen889 [zhoen889]  论坛用户 |
登录 |
|
(1)快速导出数据到Excel（三）：利用Excel内置功能 -------------------------------------------------------------------------------- 去Baidu（百度）搜索┊去Yisou（一搜）搜索┊去Google搜索┊ -------------------------------------------------------------------------------- 将数据导出到Excel的方法有多种，速度有快慢之分，我用过三种方法，速度都比较快，下面的一种利用Excel内置的功能，是三种之中最快的。其中最主要的是下面两句： xlQuery := xlSheet.QueryTables.Add(ADOQExport.Recordset ,xlSheet.Range['A3']); xlQuery.Refresh; 不过我这里稍为复杂一点，要通过某种条件完成分类汇总。 function ExportToExcel: Boolean; var xlApp, xlBook, xlSheet, xlQuery: Variant; SQLCmd: String; i, iNextRow: Integer; //设定单元格默认格式 procedure ExcelSetDefaultFormat; begin xlSheet.Cells.Font.Name := '宋体'; xlSheet.Cells.Font.Size := 12; xlSheet.Cells.VerticalAlignment := 2; //xlSheet.Cells.RowHeight := 17.25; xlSheet.Range['C:D'].HorizontalAlignment := xlCenter; end; //输出标题 procedure ExcelSetHeader; begin xlSheet.Range['A1'].Value := '显示在报表第一行的标题'; xlSheet.Range['A1:F1'].HorizontalAlignment := 7; xlSheet.Range['1:1'].Font.Size := 18; xlSheet.Range['1:1'].Font.Bold := true; xlSheet.Range['A2'].Value := '文件编号：WL/B 19'; xlSheet.Range['A2'].Font.Size := 11; xlSheet.Range['F2'].Value := '记录编号：GZ-023'; xlSheet.Range['F2'].HorizontalAlignment := xlRight; xlSheet.Range['F2'].Font.Size := 11; xlSheet.Range['A3'].Value := 'XXXXX有限公司'; xlSheet.Range['F3'].Value := '日期：2005-X-X' ; xlSheet.Range['F3'].HorizontalAlignment := xlRight; //输出字段名 ADOQExport.SQL.Strings[4] := 'where 1=0'; if ADOQExport.Active then ADOQExport.Requery else ADOQExport.Open; xlQuery := xlSheet.QueryTables.Add(ADOQExport.Recordset ,xlSheet.Range['A4']); xlQuery.FieldNames := true; xlQuery.RowNumbers := False; xlQuery.FillAdjacentFormulas := False; xlQuery.PreserveFormatting := True; xlQuery.RefreshOnFileOpen := False; xlQuery.BackgroundQuery := True; xlQuery.RefreshStyle := xlOverwriteCells; //xlInsertDeleteCells; xlQuery.SavePassword := True; xlQuery.SaveData := True; xlQuery.AdjustColumnWidth := True; xlQuery.RefreshPeriod := 0; xlQuery.PreserveColumnInfo := True; xlQuery.Refresh; iNextRow := 5; end; //设置页脚 procedure ExcelSetFooter; begin xlSheet.PageSetup.LeftFooter := '制表：' + DM.UserInfo.UserName; xlSheet.PageSetup.CenterFooter := '审核：'; xlSheet.PageSetup.RightFooter := '第 &P 页，共 &N 页'; end; //输出汇总数据 procedure ExcelSetSum; begin xlSheet.Range[Format('A%d', [iNextRow])].Value := '条数合计(条)'; xlSheet.Range[Format('A%d:B%0:d', [iNextRow])].HorizontalAlignment := 7; xlSheet.Range[Format('C%d', [iNextRow])].Value := FloatToStr(DBGridEh1.Columns[6].Footer.SumValue); xlSheet.Range[Format('C%d:F%0:d', [iNextRow])].HorizontalAlignment := 7; xlSheet.Range[Format('A%d:F%0:d', [iNextRow])].Font.Bold := true; Inc(iNextRow); xlSheet.Range[Format('A%d', [iNextRow])].Value := '重量合计(kg)'; xlSheet.Range[Format('A%d:B%0:d', [iNextRow])].HorizontalAlignment := 7; xlSheet.Range[Format('C%d', [iNextRow])].Value := FloatToStr(DBGridEh1.Columns[7].Footer.SumValue); xlSheet.Range[Format('C%d:F%0:d', [iNextRow])].HorizontalAlignment := 7; xlSheet.Range[Format('A%d:F%0:d', [iNextRow])].Font.Bold := true; end; //根据类别输出数据到Excel procedure ExportData(DataType: Byte); begin SQLCmd := Format('where DataType=%d ', [DataType]); ADOQExport.SQL.Strings[4] := SQLCmd; if ADOQExport.Active then ADOQExport.Requery else ADOQExport.Open; ProgressBar1.StepIt; if not ADOQExport.IsEmpty then begin //标题 xlSheet.Range[Format('A%d', [iNextRow])].Value := DM.GetDataTypeStr(DataType);//将DataType转换为相应的文字显示 xlSheet.Range[Format('A%d:F%0:d', [iNextRow])].HorizontalAlignment := 7; xlSheet.Range[Format('A%d:F%0:d', [iNextRow])].Font.Bold := true; Inc(iNextRow); xlQuery := xlSheet.QueryTables.Add(ADOQExport.Recordset ,xlSheet.Range[Format('A%d', [iNextRow])]); xlQuery.FieldNames := false; xlQuery.Refresh; Inc(iNextRow, ADOQExport.RecordCount); xlSheet.Range[Format('A%d', [iNextRow])].Value := DM.GetDataTypeStr(DataType) + '合计(条)'; xlSheet.Range[Format('A%d:B%0:d', [iNextRow])].HorizontalAlignment := 7; xlSheet.Range[Format('C%d', [iNextRow])].Value := Format('=SUM(C%d:C%d)', [iNextRow-ADOQExport.RecordCount, iNextRow-1]); xlSheet.Range[Format('D%d', [iNextRow])].Value := Format('=SUM(D%d:D%d)', [iNextRow-ADOQExport.RecordCount, iNextRow-1]); xlSheet.Range[Format('A%d:F%0:d', [iNextRow])].Font.Bold := true; Inc(iNextRow); end; ProgressBar1.StepIt; end; begin Result := true; ShowProgress(0, cbbDataType.KeyItems.Count*2+2, 0); //调用前面例子中的函数显示进度面板 Screen.Cursor := crHourGlass; try try //建立OLE对象 xlApp := CreateOleObject('Excel.Application'); xlBook := xlApp.Workbooks.Add; xlSheet := xlBook.Worksheets['sheet1']; xlApp.Visible := false; ProgressBar1.StepIt; //设置格式 ExcelSetDefaultFormat; //输出标题内容 ExcelSetHeader; ProgressBar1.StepIt; //查询结果，导到EXCEL for i:=0 to cbbDataType.KeyItems.Count-1 do //cbbDataType: TDBComboBoxEh ExportData(StrToInt(cbbDataType.KeyItems.Strings[i])); //输出汇总内容 ExcelSetSum; //设置边框 xlSheet.Range[Format('A4:F%d', [iNextRow])].Borders.LineStyle := xlContinuous; xlSheet.Cells.EntireColumn.AutoFit; //输出页脚 ExcelSetFooter; except if not VarIsNull(xlApp) then begin xlApp.Quit; xlApp.Disconnect; xlApp := Unassigned; xlApp := NULL; end; result := false; Exit; end; finally pnlShadow.Visible := false; pnlProgress.Visible := false; Screen.Cursor := crDefault; xlSheet := Unassigned; xlBook := Unassigned; if not VarIsNull(xlApp) then begin xlApp.Visible := true; xlApp := Unassigned; end; if ADOQExport.Active then ADOQExport.Close; end; end; (2)快速导出数据到Excel（一）：利用剪贴板 -------------------------------------------------------------------------------- 去Baidu（百度）搜索┊去Yisou（一搜）搜索┊去Google搜索┊ -------------------------------------------------------------------------------- 将数据导出到Excel的方法有多种，速度有快慢之分，我用过三种方法，速度都比较快，下面的一种是通过剪贴板进行，不过在个别W2K以上的系统，由于字符集编码不同，中文内容导出到Excel后可能变成乱码。 //显示进度条面板 procedure ShowProgress(Min, Max, Position: integer); begin pnlProgress.Left := (ClientWidth - pnlProgress.Width) div 2; ProgressBar1.Min := Min; ProgressBar1.Max := Max; ProgressBar1.Position := Position; pnlProgress.Visible := true; pnlProgress.Update; end; //将数据库数据添加到DataList function GetDataList(DataList: TStringList): Boolean; var S: string; i: integer; begin Result := true; DataList.Clear; try try DataList.Add('这是标题'); ProgressBar1.StepIt; ADOQuery1.DisableControls; with ADOQuery1 do begin First; S := ''; for i:=0 to FieldCount-1 do if Fields[i].Visible then S := S + Fields[i].DisplayLabel + #9; //先导出字段名，用制表符分开 DataList.Add(S); ProgressBar1.StepIt; While Not Eof do begin S := ''; for i:=0 to FieldCount-1 do if Fields[i].Visible then S := S + Fields[i].DisplayText + #9;//导出数据显示内容 DataList.Add(S); ProgressBar1.StepIt; Application.ProcessMessages; Next; end; end; except Result := false; end; finally ADOQuery1.EnableControls; end; end; function ExportByClipboard: Boolean; var List: TStringList; FileName: string; ASheet: Variant; begin ShowProgress(0, ADOQuery1.RecordCount+3, 0); Result := true; FileName := 'C:\abc.xls'; Excel.Connect; //Excel: TExcelApplication控件 try try Excel.DisplayAlerts[0] := false; Excel.Visible[0] := false; Excel.Caption := 'XXXXX导出(Excel)'; Excel.Workbooks.Add(xlWBATWorksheet, 0); ASheet := Excel.Worksheets.Item[1]; //设定默认格式 Excel.Cells.Font.Name := '宋体'; Excel.Cells.Font.Size := 10; Excel.Cells.VerticalAlignment := 2; //设定标题格式 Excel.Range['A1', 'Z1'].HorizontalAlignment := 7; Excel.Range['A1', 'Z1'].Font.Size := 16; Excel.Range['A1', 'Z1'].RowHeight := 22; Excel.Range['A2', 'Z2'].HorizontalAlignment := 3; Excel.Range['A2', 'Z2'].Font.Bold := true; List := TStringList.Create; try try if GetDataList(List) then begin //锁定计算机并将数据粘到Excel里 BlockInput(true); Clipboard.AsText := List.Text; ASheet.Paste; Clipboard.Clear; BlockInput(false); ProgressBar1.StepIt; end; finally List.Free; end; except Result := false; pnlProgress.Visible := false; Exit; end; ProgressBar1.StepIt; Excel.Workbooks.Item[1].SaveCopyAs(FileName, 0); Excel.Workbooks.Item[1].Close(false, FileName, 0, 0); finally Excel.Quit; Excel.Disconnect; end; except Result := false; pnlProgress.Visible := false; Exit; end; ProgressBar1.Position := ProgressBar1.Max; MessageBox(Handle, PChar('数据成功导出到' + FileName), '导出数据', MB_ICONINFORMATION or MB_OK); pnlProgress.Visible := false; end; |
| B1层 发表时间: 05-10-03 11:09 |
 | 回复: zxdlovedjw [zxdlovedjw]  论坛用户 |
登录 |
|
用DEPHI太麻烦了 用PB就可以搞定了 |
| B2层 发表时间: 05-11-11 17:59 |
|
20CN网络安全小组版权所有
Copyright © 2000-2010 20CN Security Group. All Rights Reserved.
论坛程序编写：NetDemon
粤ICP备05087286号
论坛: 编程破解
标题: 如何将access中的表导出导excel?