I need to performs some basic stuffs with Excel files.
Copy a specified range of data from a worksheet to a new Excel / WorkSheet file. Difficulty is that I need to copy data from the 3rd Row, until the last one.
On the new Excel file : I need to add new columns that will basically performs some calculation (A1+B1+D1 on the first row, A2+B2+D2 on the second row and so on, until the last row)
This doesn't seems complicated and I guess that FlexCell can do this "finger in the noze". Can you help me on this ?
2. If the sheet you are copying from xls1, has references to other sheets (say you have in sheet1 a formula =Sheet2!A1) and you only copy sheet1, those formulas will be wrong in the new file. So that's why I added a ConvertFormulasToValues call, with only external formulas.
program Project46;
{$APPTYPE CONSOLE}
{$R *.res}
uses
System.SysUtils, VCL.FlexCel.Core, FlexCel.XlsAdapter;
var
xls1, xls2: TExcelFile;
colcount: integer;
begin
xls1 := TXlsFile.Create('r:\test.xlsx');
try
xls2 := TXlsFile.Create(1, TExcelFileFormat.v2016, true);
try
xls1.ConvertFormulasToValues(true); //not needed if you don't have formulas in xls1 pointing to other sheets.
xls2.InsertAndCopyRange(
TXlsCellRange.Create(3, 1, xls1.RowCount, 1), //copy from row 3 in xls1. columns don't matter, we will copy full rows
1, //copy to row 1 in xls2.
1, //copy to column A in xls2
1, //copy just one time.
TFlxInsertMode.ShiftRowDown, //copy full rows and insert them into the new file.
TRangeCopyMode.All,
xls1, //copy form xls1
1 //from sheet 1 (to activesheet in xls2)
);
//cache xls1.colcount, since it is a slow method. RowCount isn't slow, but colCount is.
colcount := xls1.ColCount;
xls2.SetCellValue(1, colcount + 1, TFormula.Create('=A1+B1+D1'));
xls2.InsertAndCopyRange( //expand the formula down for xls1.RowCount -3 rows.
TXlsCellRange.Create(1, colcount + 1, 1, colcount + 1),
2, colcount + 1, xls1.RowCount - 3,
TFlxInsertMode.NoneDown //don't insert, only copy.
);
xls2.Save('r:\result.xlsx');
finally
xls2.Free;
end;
finally
xls1.Free;
end;
end.