how to copy and insert a specific rows at excell with gembox spreadsheet

Question

please help. i use gembox.spreadsheet library to insert and copy at spesific rows within 2 sheets at excel file. but it still got problem with invalid argument.

public void InsertCopyData()
{
    SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");
    ExcelFile ef = new ExcelFile();

    // Loads Excel file.
    ef.LoadXls(@"C:\templateExcel\DataTable.xls");

    // Selects first and 2nd worksheet.
    ExcelWorksheet w1 = ef.Worksheets[0];
    ExcelWorksheet w2 = ef.Worksheets[1];

    //insert copy file  
    w1.InsertCopy(w1.Rows["A1"], w2.Rows["A4"]);


    //Saves the file in XLS format.
    ef.SaveXls(@"C:\templateExcel\Insert DataTable.xls");
}

score 2 · Answer 1 · answered Mar 30 '15 at 08:58

2

Note that GemBox.Spreadsheet can enable you to insert worksheets, rows and/or columns. The API that you are using with an invalid arguments is for inserting a worksheets copy, in order to insert a rows copy use the following:

// Inserts specified number of copied rows before the current row.
var currentRow = w1.Rows["A1"];
currentRow.InsertCopy(1, w2.Rows["A4"]);

answered Mar 30 '15 at 08:58

GemBox Dev Team

669
5
18

This works, but it causes 2 issues: a. ArgumentOutOfRangeException - Row index can't be larger than maximum row index (1048575) when calling GetUsedCellRange(true) b. Excel 2013 shows a warning 'We found a problem with some content' – Alonzzo2 Sep 06 '16 at 16:31
I'm afraid I would need to investigate your input and output Excel file in order to tell you exactly what the problem is. You should submit a support ticket with [this link](http://www.gemboxsoftware.com/support-center/new-ticket) and provide some information (or files) with which we'll be able to reproduce your issue. – GemBox Dev Team Sep 28 '16 at 12:00

NAY AUNG SOE WIN · Answer 2 · 2013-03-15T09:44:17.733

You cannot use like this: ef.LoadXls(@"C:\templateExcel\DataTable.xls");

You should write to load existing Excel file.

GemBox.Spreadsheet.ExcelFile ef = new GemBox.Spreadsheet.ExcelFile();
ef = GemBox.Spreadsheet.**ExcelFile.Load**("D:\\Example.xlsx");
ExcelWorksheet ws = ef.Worksheets["Sheet1"];  (or)   ExcelWorksheet ws = ef.Worksheets[0]; 
// writing data to excel file code...
ws.Cells[0, 0].Value = example_1;
ws.Cells[1, 0].Value = example_2;
ef.Save("D:\\Example.xlsx");

how to copy and insert a specific rows at excell with gembox spreadsheet

2 Answers2