我有编写一个从TDBGridEh中汇出excel的功能,汇出部份的具体代码如下:function GridSaveToExcel(dbGrid:TDBGridEh):Boolean;
var
FExcel: Variant;
FWorkbook: Variant;
FWorksheet: Variant;
FArray,FArray1,FArray2: Variant;
RangeStr: string;
RangeStrN: string;
StrtCol,StrtRow: Integer;
newbook: Boolean;
OldCursor: TCursor;
SheetName:String;
FileName:String;
FDataSet:TDataSet;
RowCount:Integer;
RowCountN:Integer;
ColCount:Integer;
RowNo:Integer;
ColNo:Integer;
I,N,R,J:Integer;
tmString:String;
FsheetNo:Integer;
begin
dmMain.siSavedialog.DefaultExt := 'xls';
dmMain.siSavedialog.Filter :='Excel files (*.xls)|*.xls';
tmString := GetWinControlFormCaption(dbGrid);
if StringIsValidFileName(tmString) then
dmMain.siSavedialog.filename := tmString
else
dmMain.siSavedialog.filename := '';
tmString := '';
if dmMain.siSavedialog.Execute then
begin
OldCursor := Screen.Cursor;
Screen.Cursor := crHourGlass; FileName := dmMain.siSavedialog.filename; try
FExcel := CreateOleObject('excel.application');
except
Screen.cursor := crDefault;
raise Exception.Create('Excel OLE server not found');
Exit;
end; try
newbook := True; if SheetName = '' then
begin
FWorkBook := FExcel.WorkBooks.Add;
FWorkSheet := FWorkBook.Sheets[1];
end
else
begin
newbook := False;
try
if FileExists(FileName) then
FWorkBook := FExcel.WorkBooks.Open(Filename)
else
begin
FWorkBook := FExcel.WorkBooks.Add;
newbook := true;
end;
except
if VarIsEmpty(FWorkBook) then
FWorkBook := FExcel.WorkBooks.Add;
newbook := true;
end; FWorkSheet := unAssigned;
for I := 1 to FWorkbook.Sheets.Count do
if (FWorkBook.Sheets[I].Name = sheetname) then
FWorkSheet := FWorkBook.Sheets[I]; if VarIsEmpty(FWorksheet) then
begin
FWorkSheet := FWorkBook.WorkSheets.Add;
FWorkSheet.Name := sheetname;
end;
end; FDataSet := dbGrid.DataSource.DataSet;
FDataSet.Last; RowCount := FDataSet.RecordCount + 1; ColCount := 0; for I := 0 to dbGrid.columns.count - 1 do
begin
if (trim(dbGrid.Columns[i].FieldName)<>'') and (not (dbGrid.Columns[i].Field is TGraphicField)) and dbGrid.Columns[i].Visible then
inc(ColCount);
end; FArray := VarArrayCreate([0,RowCount -1,0, ColCount - 1],VarVariant); ColNo := 0 ;
for i := 0 to dbGrid.Columns.Count-1 do
begin
if (trim(dbGrid.Columns[i].FieldName)<>'') and (not (dbGrid.Columns[i].Field is TGraphicField)) and dbGrid.Columns[i].Visible then
begin
tmString := dbGrid.Columns[i].Title.Caption;
tmString := StringReplace(tmString,'|',' ',[rfReplaceAll]);
FArray[0,Colno] := tmString;
Inc(colNo);
end;
end;
RowNo:= 0;
FDataSet.DisableControls;
FDataSet.First;
while not FDataSet.Eof do
begin
ColNo := 0 ;
Inc(RowNo);
for i := 0 to dbGrid.Columns.Count-1 do
begin
if (trim(dbGrid.Columns[i].FieldName)<>'') and (not (dbGrid.Columns[i].Field is TGraphicField)) and dbGrid.Columns[i].Visible then
begin
if FDataSet.FieldByName(dbGrid.Columns[i].FieldName).IsNull then
tmString := ''
else
if (dbGrid.Columns[I].PickList.Count >0) and (dbGrid.Columns[I].KeyList.Count >0) then
begin
if not FDataSet.FieldByName(dbGrid.Columns[i].FieldName).IsNull then//if FDataSet.FieldByName(dbGrid.Columns[i].FieldName).IsNull then
tmString := dbGrid.Columns[I].PickList.Strings[dbGrid.Columns[I].KeyList.Indexof(FDataSet.FieldByName(dbGrid.Columns[i].FieldName).AsString)]
else
tmString :='';
end
else
begin
if (dbGrid.Columns[i].Field is TDatetimeField) or (dbGrid.Columns[i].Field is TDatetimeField) then
begin
tmString := FormatDatetime('mm/dd/yyyy',dbGrid.Columns[i].Field.asDatetime);
end
else
if (dbGrid.Columns[i].Field is TNumericField) then
begin
tmString := FormatFloat(TNumericField(dbGrid.Columns[i].Field).DisplayFormat,dbGrid.Columns[i].Field.AsFloat);
end
else
begin
tmString := dbGrid.Columns[i].Field.AsString;
tmString := StringReplace(tmString,'|',' ',[rfReplaceAll]);
if (dbGrid.Columns[i].FieldName = 'SProduct') and (length(tmString)>600) then
tmString := Copy(tmString,0,600);
end
end;
FArray[Rowno,Colno] := tmString;
inc(colNo);
end;
end;
FDataSet.Next;
end;
FDataSet.EnableControls;
RangeStr := 'A1:'; if (ColCount) > 26 then
begin
if (ColCount - StrtCol) mod 26 = 0 then
begin
RangeStr := RangeStr + Chr(Ord('A') - 2 + ((ColCount - StrtCol) div 26));
RangeStr := RangeStr + 'Z';
end
else
begin
RangeStr := RangeStr + Chr(Ord('A') - 1 + ((ColCount - StrtCol) div 26));
RangeStr := RangeStr + Chr(Ord('A') - 1 + ((ColCount - StrtCol) mod 26));
end;
end
else
RangeStr := RangeStr + Chr(Ord('A') - 1 + (ColCount)); if RowCount < 65000 then //防止超过excel的行数
begin
RangeStr := RangeStr + IntToStr(RowCount);
FWorkSheet.Range[rangestr].Value := FArray;
end
else
begin
FsheetNo := Ceil(RowCount/65000);
for I := 1 to FsheetNo do
begin
{FWorkSheet := unAssigned;
for I := 1 to FWorkbook.Sheets.Count do
if (FWorkBook.Sheets[I].Name = sheetname) then
FWorkSheet := FWorkBook.Sheets[I];}
FWorkSheet := FWorkBook.Sheets[I];
if VarIsEmpty(FWorksheet) then
begin
FWorkSheet := FWorkBook.WorkSheets.Add;
FWorkSheet.Name := 'Sheets'+IntToStr(I);
end;
FWorkSheet := FWorkBook.Sheets[I];
if i < FsheetNo then
RowCountN := 65000-1
else
RowCountN := RowCount - 65000 * (i -1) ; FArray1 := VarArrayCreate([0,0,0, ColCount - 1],VarVariant);
for N := 0 to ColCount - 1 do
begin
FArray1[0,N] := FArray[0,N] ;
end;
RangeStr := 'A1:'+ Chr(Ord('A') - 1 + (ColCount)) +'1';
FWorkSheet.Range[RangeStr].Value := FArray1; FArray2 := VarArrayCreate([0,RowCountN-1,0, ColCount - 1],VarVariant);
for R := 0 to RowCountN-1 do
begin
if I = 1 then
J := 65000*(I-1)+R+1
else
J := 65000*(I-1)+R;
for N := 0 to ColCount - 1 do
FArray2[R,N] := FArray[J,N] ;
end;
RangeStrN := 'A2:' + Chr(Ord('A') - 1 + (ColCount)) + IntToStr(RowCountN+1);
FWorkSheet.Range[RangeStrN].Value := FArray2;
end;
end;
//FWorkSheet.Columns['A:' + LastCol].EntireColumn.AutoFit; if newbook then
FWorkbook.SaveAs(filename)
else
FWorkbook.Save; finally
FExcel.Quit;
FExcel := unAssigned;
end;
if MessageDlg(dmMain.siLang.GetText('CREATESUCCESSOPEN'),mtInformation,[mbYes,mbNo],0) = mrYes then
ShellExecute(0,'Open',Pchar(dmMain.siSavedialog.FileName),'','',1); Screen.Cursor := OldCursor;
end;
end;---问题情况是,当TDBGridEh里面的栏位中的值有超过1000个字符时,执行到
FWorkSheet.Range[rangestr].Value := FArray;
时就中止了,但是如果没有超过1000个字符的资料,汇出就是可以成功执行。在网上查了N久都没有找到解决办法,请各位大侠帮忙解决下。
var
FExcel: Variant;
FWorkbook: Variant;
FWorksheet: Variant;
FArray,FArray1,FArray2: Variant;
RangeStr: string;
RangeStrN: string;
StrtCol,StrtRow: Integer;
newbook: Boolean;
OldCursor: TCursor;
SheetName:String;
FileName:String;
FDataSet:TDataSet;
RowCount:Integer;
RowCountN:Integer;
ColCount:Integer;
RowNo:Integer;
ColNo:Integer;
I,N,R,J:Integer;
tmString:String;
FsheetNo:Integer;
begin
dmMain.siSavedialog.DefaultExt := 'xls';
dmMain.siSavedialog.Filter :='Excel files (*.xls)|*.xls';
tmString := GetWinControlFormCaption(dbGrid);
if StringIsValidFileName(tmString) then
dmMain.siSavedialog.filename := tmString
else
dmMain.siSavedialog.filename := '';
tmString := '';
if dmMain.siSavedialog.Execute then
begin
OldCursor := Screen.Cursor;
Screen.Cursor := crHourGlass; FileName := dmMain.siSavedialog.filename; try
FExcel := CreateOleObject('excel.application');
except
Screen.cursor := crDefault;
raise Exception.Create('Excel OLE server not found');
Exit;
end; try
newbook := True; if SheetName = '' then
begin
FWorkBook := FExcel.WorkBooks.Add;
FWorkSheet := FWorkBook.Sheets[1];
end
else
begin
newbook := False;
try
if FileExists(FileName) then
FWorkBook := FExcel.WorkBooks.Open(Filename)
else
begin
FWorkBook := FExcel.WorkBooks.Add;
newbook := true;
end;
except
if VarIsEmpty(FWorkBook) then
FWorkBook := FExcel.WorkBooks.Add;
newbook := true;
end; FWorkSheet := unAssigned;
for I := 1 to FWorkbook.Sheets.Count do
if (FWorkBook.Sheets[I].Name = sheetname) then
FWorkSheet := FWorkBook.Sheets[I]; if VarIsEmpty(FWorksheet) then
begin
FWorkSheet := FWorkBook.WorkSheets.Add;
FWorkSheet.Name := sheetname;
end;
end; FDataSet := dbGrid.DataSource.DataSet;
FDataSet.Last; RowCount := FDataSet.RecordCount + 1; ColCount := 0; for I := 0 to dbGrid.columns.count - 1 do
begin
if (trim(dbGrid.Columns[i].FieldName)<>'') and (not (dbGrid.Columns[i].Field is TGraphicField)) and dbGrid.Columns[i].Visible then
inc(ColCount);
end; FArray := VarArrayCreate([0,RowCount -1,0, ColCount - 1],VarVariant); ColNo := 0 ;
for i := 0 to dbGrid.Columns.Count-1 do
begin
if (trim(dbGrid.Columns[i].FieldName)<>'') and (not (dbGrid.Columns[i].Field is TGraphicField)) and dbGrid.Columns[i].Visible then
begin
tmString := dbGrid.Columns[i].Title.Caption;
tmString := StringReplace(tmString,'|',' ',[rfReplaceAll]);
FArray[0,Colno] := tmString;
Inc(colNo);
end;
end;
RowNo:= 0;
FDataSet.DisableControls;
FDataSet.First;
while not FDataSet.Eof do
begin
ColNo := 0 ;
Inc(RowNo);
for i := 0 to dbGrid.Columns.Count-1 do
begin
if (trim(dbGrid.Columns[i].FieldName)<>'') and (not (dbGrid.Columns[i].Field is TGraphicField)) and dbGrid.Columns[i].Visible then
begin
if FDataSet.FieldByName(dbGrid.Columns[i].FieldName).IsNull then
tmString := ''
else
if (dbGrid.Columns[I].PickList.Count >0) and (dbGrid.Columns[I].KeyList.Count >0) then
begin
if not FDataSet.FieldByName(dbGrid.Columns[i].FieldName).IsNull then//if FDataSet.FieldByName(dbGrid.Columns[i].FieldName).IsNull then
tmString := dbGrid.Columns[I].PickList.Strings[dbGrid.Columns[I].KeyList.Indexof(FDataSet.FieldByName(dbGrid.Columns[i].FieldName).AsString)]
else
tmString :='';
end
else
begin
if (dbGrid.Columns[i].Field is TDatetimeField) or (dbGrid.Columns[i].Field is TDatetimeField) then
begin
tmString := FormatDatetime('mm/dd/yyyy',dbGrid.Columns[i].Field.asDatetime);
end
else
if (dbGrid.Columns[i].Field is TNumericField) then
begin
tmString := FormatFloat(TNumericField(dbGrid.Columns[i].Field).DisplayFormat,dbGrid.Columns[i].Field.AsFloat);
end
else
begin
tmString := dbGrid.Columns[i].Field.AsString;
tmString := StringReplace(tmString,'|',' ',[rfReplaceAll]);
if (dbGrid.Columns[i].FieldName = 'SProduct') and (length(tmString)>600) then
tmString := Copy(tmString,0,600);
end
end;
FArray[Rowno,Colno] := tmString;
inc(colNo);
end;
end;
FDataSet.Next;
end;
FDataSet.EnableControls;
RangeStr := 'A1:'; if (ColCount) > 26 then
begin
if (ColCount - StrtCol) mod 26 = 0 then
begin
RangeStr := RangeStr + Chr(Ord('A') - 2 + ((ColCount - StrtCol) div 26));
RangeStr := RangeStr + 'Z';
end
else
begin
RangeStr := RangeStr + Chr(Ord('A') - 1 + ((ColCount - StrtCol) div 26));
RangeStr := RangeStr + Chr(Ord('A') - 1 + ((ColCount - StrtCol) mod 26));
end;
end
else
RangeStr := RangeStr + Chr(Ord('A') - 1 + (ColCount)); if RowCount < 65000 then //防止超过excel的行数
begin
RangeStr := RangeStr + IntToStr(RowCount);
FWorkSheet.Range[rangestr].Value := FArray;
end
else
begin
FsheetNo := Ceil(RowCount/65000);
for I := 1 to FsheetNo do
begin
{FWorkSheet := unAssigned;
for I := 1 to FWorkbook.Sheets.Count do
if (FWorkBook.Sheets[I].Name = sheetname) then
FWorkSheet := FWorkBook.Sheets[I];}
FWorkSheet := FWorkBook.Sheets[I];
if VarIsEmpty(FWorksheet) then
begin
FWorkSheet := FWorkBook.WorkSheets.Add;
FWorkSheet.Name := 'Sheets'+IntToStr(I);
end;
FWorkSheet := FWorkBook.Sheets[I];
if i < FsheetNo then
RowCountN := 65000-1
else
RowCountN := RowCount - 65000 * (i -1) ; FArray1 := VarArrayCreate([0,0,0, ColCount - 1],VarVariant);
for N := 0 to ColCount - 1 do
begin
FArray1[0,N] := FArray[0,N] ;
end;
RangeStr := 'A1:'+ Chr(Ord('A') - 1 + (ColCount)) +'1';
FWorkSheet.Range[RangeStr].Value := FArray1; FArray2 := VarArrayCreate([0,RowCountN-1,0, ColCount - 1],VarVariant);
for R := 0 to RowCountN-1 do
begin
if I = 1 then
J := 65000*(I-1)+R+1
else
J := 65000*(I-1)+R;
for N := 0 to ColCount - 1 do
FArray2[R,N] := FArray[J,N] ;
end;
RangeStrN := 'A2:' + Chr(Ord('A') - 1 + (ColCount)) + IntToStr(RowCountN+1);
FWorkSheet.Range[RangeStrN].Value := FArray2;
end;
end;
//FWorkSheet.Columns['A:' + LastCol].EntireColumn.AutoFit; if newbook then
FWorkbook.SaveAs(filename)
else
FWorkbook.Save; finally
FExcel.Quit;
FExcel := unAssigned;
end;
if MessageDlg(dmMain.siLang.GetText('CREATESUCCESSOPEN'),mtInformation,[mbYes,mbNo],0) = mrYes then
ShellExecute(0,'Open',Pchar(dmMain.siSavedialog.FileName),'','',1); Screen.Cursor := OldCursor;
end;
end;---问题情况是,当TDBGridEh里面的栏位中的值有超过1000个字符时,执行到
FWorkSheet.Range[rangestr].Value := FArray;
时就中止了,但是如果没有超过1000个字符的资料,汇出就是可以成功执行。在网上查了N久都没有找到解决办法,请各位大侠帮忙解决下。
ExcelWorkSheet.Cells.Item[Row,Col] := tmString;
但是这样,当需要汇出的资料相当多时,就很花时间,所以才另外开发了用数组做汇出的功能,同样的资料,用上面的代码就可以汇出,还有就是我把值也有直接到Excel中输入,完全是可以输入的,不会出错。
我也有曾考虑过是你讲的这个问题,但是换种方法又不会了。让我觉得这个问题很难解决。