Our application uses FlexCel to import client data (usually customer data). It scans all the rows (using xls.RowCount to determine the number of rows) and if a row is missing some critical information (e.g. and ID or zip code) it reports an error. It seems some Excel files created by CRM systems might have a few hundred rows of genuine data but have thousands of blank rows which FlexCel thinks contains data (i.e. xls.RowCount is > million). When opened in Excel it also thinks there's some data (Ctrl-End goes to row 1,048,576).
Is there a way to "clean" these blank rows and columns using FlexCel?
I can provide an example of such a file but I'd rather do so via email rather than post to a forum.
You can indeed have many empty rows with just format at the end. There is not much we can do about it, so it depends on what you want to do.
If you are importing, the simplest way is to check if all columns in the row you are importing are empty. But this won't work if you have an empty row in the middle of the data and you would like to report that as an error.
If what you want is to fix the file, then you can just loop the file from the last row and delete the rows that are empty. You can use some code like this:
program Project1;
{$APPTYPE CONSOLE}
{$R *.res}
uses
System.SysUtils,
VCL.FlexCel.Core,
FlexCel.XlsAdapter;
function IsEmptyRow(const xls: TExcelFile; const r: integer): boolean;
begin
Result := true;
for var cIndex := 1 to xls.ColCountInRow(r) do
begin
var XF := -1;
if not xls.GetCellValueIndexed(r, cIndex, XF).IsEmpty then exit(false);
end;
end;
function GetMaxNotEmtpyRow(const xls: TExcelFile): integer;
begin
for var r := xls.RowCount downto 1 do
begin
if not IsEmptyRow(xls, r) then exit(r);
end;
Result := 0;
end;
begin
var xls := TXlsFile.Create('r:\test.xlsx', true);
try
var FirstEmptyRow := GetMaxNotEmtpyRow(xls) + 1;
if (FirstEmptyRow <= xls.RowCount) then
begin
xls.DeleteRange(TXlsCellRange.Create(FirstEmptyRow, 1, xls.RowCount, 1), TFlxInsertMode.ShiftRowDown);
end;
xls.Save('r:\result.xlsx');
finally
xls.Free;
end;
end.