Is there a way to paste a dataset from a database query and paste it into an excel worksheet by defining the 1st row and column where the record set is to start from. Example: I retrieve a recordset from a database with 30,000 rows and 20 columns and I want to pate that whole chunk into an excel sheet starting on row 2 and column "A". The result range would be A2:T30,001. Currently I'm using a nested loop and placing the data in cell by cell. In some languages, I can just pate the whole chunk with one command. Hopefully FlexCel might be able to do something like this.
Hi,
We discuss what is available in FlexCel and why here:
Basically, while it is a nice feature from a marketing point of view, it is very rare that you don't want to do any customization in the generated file. And then is when the "ExportToExcel" ends up with a gazillion parameters because it has to support thousands of different customer definitions of "just dump this dataset to Excel". It ends up being more complex to use.
So the alternative is, just put the code in that example inside a method (or use the one you are using), name it "ExportToExcel", add the parameters you want (in your case it seems it would be the start column and row), and then you have a one-line method to export. It won't be slower that if we added this code inside FlexCel and named it that way.
As an example of what I am speaking about, here are the parameters we need to have in a "ExportAGridToExcel" method:
Given all the options in Excel and FlexCel, the list of parameters in this method would be at least twice as long to satisfy everyone,
Thanks for the information. I have looked into the FlexCel Report options and think that is what I'm going to try. Here is a test chunk of code that I ran just to see if things will work. I have two issues.
procedure TMainForm.RunReport;
var
Report: TFlexCelReport;
xlsTemp : TXlsFile;
xlsFile : TXlsFile;
xlsTemplate : TXLSFile;
begin
Report := TFlexCelReport.Create(true);
try
Report.AddTable('Table',Table);
Report.Run('Y:\Template.xlsx','Y:\TempFile.xlsx');
xlsTemp := TXlsFile.Create(false);
xlsTemp.Open('Y:\TempFile.xlsx');
xlsFile := TXlsFile.Create(false);
xlsFile.Open('Y:\MasterTemplate.xlsx');
xlsFile.InsertAndCopyRange(TXlsCellRange.FullRange, 2, 1, 1, TFlxInsertMode.NoneDown , TRangeCopyMode.All, XlsTemp, 1);
xlsFile.Save('Y:\FinalFile.xlsx');
deletefile('Y:\TempFile.xlsx');
finally
xlsTemp.Free;
xlsFile.Free;
Report.Free;
end;
end;
As you can see, I have a Template.xlsx being used as the template for the FlexCelReport process. I also have a file named MasterTemplate.xlsx. That file has some formatting such as columns widths and some columns formatted as a date. I pull data from a SQL database into a data set in "Table" which is a TFDTable. When I use the InsertAndCopyRange to paste the chunk of data from the TempFile.xlsx into the MasterTemplate.xlsx, the formatting I set up in the destination file is removed and replaced with the seemingly empty format of the source file. Is there a way to use the InsertAndCopyRange without upsetting the destination format where the data is placed?
You will notice because I cannot figure out anything better, an inefficient way of getting data out of a dataset and into the final worksheet except placing the dataset data into a temp xlsx file, then opening it up and pasting from there into the final xlsx file as shown with this test. I have read about and tried to get streaming working but cannot get anything to fully work. I know about the FlexCel direct method of placing data into Excel with a nested loop and have been doing that for a long time, but in my initial test, the report method to a temp file and then pasting to the final worksheet is way faster.
Can you please help me figure out these two issues so I can continue to get more confident on using FlexCel?
Thanks so much...
Hi,
Many interested points to discuss here, I'll go in no particular order.
-
Using FlexCelReport to a temp file, then InsertingAndCopying should always be slower than directly filling the data. Why? Because FlexCelReport uses the same API as you to fill the data in the temp file. Internally, it does a nested loop to fill the data, the same as you would do directly. And it is actually doing it twice, because it first has to fill the temp file, then fill the final file cell by cell when you InsertAndCopy the data.
FlexCelReport has no access to any magical method that would make it faster than you using the API directly. So I guess there is something wrong in the way you are doing the fill, maybe the way you access the data from the database? -
I am still not sure on why you are doing 2 passes here. Why fill to a temporary template, then copy it to the final template? Why not run the FlexCelReport in the final template directly?
Note that you can even <#include> the temp report into the final report, but again, this has the problem of filling the cells twice. The best would be to have the report or the API directly fill the cells. -
If you really need to go through the 2-step process: Streaming is not complex, but you don't even need that.
You could do (try/finally omitted because I'm lazy):
XlsTemp := TXlsFile.Create(false);
XlsTemp.Open('Y:\Template.xlsx');
Report.AddTable('Table',Table);
Report.Run(XlsTemp);
//Here you have the report in XlsTemp, without temporary files
xlsFile.InsertAndCopyRange(TXlsCellRange.FullRange, 2, 1, 1, TFlxInsertMode.NoneDown , TRangeCopyMode.All, XlsTemp, 1);
4.To use the format of the final file, well, I was going to write to change TRangeCopyMode.All parameter to TRangeCopyMode.Values, but I see that "Values" isn't there... this is an oversight, we will see to add it. But once again, you shouldn't need to generate the report so then you can copy it into another report. That's inefficient since you are running the 2-level loop to fill the cells twice
Thanks so much for the great info. I am going to look at my nested loop of placing the data into the cells one at a time. Over the years I have put more and more stuff in that process. I need to maybe step out and run a more stripped down version for speed when it comes to thousands of rows of data being pleased into the worksheet.
It works much better now. I exported 22,517 rows in 10 seconds instead of 3 minutes! The real culprit was that I was calling the ConvertString for every cell. Also I was updating the UI showing a count of every row I exported so the user would see what was going on. Now I'm just passing the value of the data as a AsVariant and using the SetCellValue(,,,-1). If there is a faster way, please let me know, but I'm real happy with the results! Thanks so much!