how to consolidate data in excel from multiple worksheets?

Question

I have to merge the worksheet and worksheet 1 data into a single sheet. I tried the FileStream method to open the file and read the data from there and paste it into the destination worksheet.

    Qdv.UserApi.IWbs WBS = es.CurrentVersion.Wbs;
            System.Data.DataTable Tasks = WBS.GetFullData(true, new Qdv.UserApi.LocationOfTotals());

            // Create a new workbook and worksheet.
            SpreadsheetGear.IWorkbook workbook = SpreadsheetGear.Factory.GetWorkbook();
            SpreadsheetGear.IWorksheet worksheet = workbook.Worksheets["Sheet1"];

            // Get the top left cell for the DataTable.
            SpreadsheetGear.IRange range = worksheet.Cells["A1"];
            

            // Copy the DataTable to the worksheet range.
            range.CopyFromDataTable(Tasks, SpreadsheetGear.Data.SetDataFlags.None);

            //Auto size all worksheet columns which contain data.
            worksheet.UsedRange.Columns.AutoFit();
            worksheet.Name = "WBS Details";
            



            SpreadsheetGear.IWorkbook WBK = es.GetActiveMinuteWorkbook(true);
            SpreadsheetGear.IWorksheet worksheet1 = WBK.Worksheets[0];
            worksheet1.Name = "Minutes Details";

Answer 1

It's a bit unclear in your question what is the source sheet and destination sheet, and where exactly you plan to copy the contents in the destination sheet. So for now I will provide a more generalized answer on how to copy one range to another. If you need more clarity for your particular case, please update your post with more code or pseudo-code that makes your intentions clear.

In general, you can use IRange.Copy(...) to copy one range to another, which can include from one sheet to another. You can use IWorksheet.UsedRange to get the portion of a worksheet that is "used" which in your case might be handy to both figure out what "source" range to copy as well as figure out where in relation to the destination sheet's used range where to copy the source range to.

For example, the below sample code takes the UsedRange from the "src" worksheet and copies it directly below the UsedRange of the "dest" worksheet:

// Get source workbook, worksheet, used range.
IWorkbook srcWorkbook = Factory.GetWorkbook("...");
IWorksheet srcWorksheet = srcWorkbook.Worksheets["Sheet1"];
IRange srcUsedRange = srcWorksheet.UsedRange;

// Get destination workbook, worksheet, used range.
IWorkbook destWorkbook = Factory.GetWorkbook("...");
IWorksheet destWorksheet = destWorkbook.Worksheets["Sheet1"];
IRange destUsedRange = destWorksheet.UsedRange;

// Get the number of rows in the destination used range.
int destUsedRangeRowCount = destUsedRange.RowCount;

// Use the IRange[...] indexer to create a new IRange that represents the cell
// immediately below "destUsedRange" and in the same first column.  So if
// "destUsedRange" is A1:D15, then "destRange" would be A16.
IRange destRange = destUsedRange[destUsedRangeRowCount, 0];

// Copy source range to destination range.
srcUsedRange.Copy(destRange);