We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy. Image for the cookie policy date
Unfortunately, activation email could not send to your email. Please try again.
Syncfusion Feedback

How to split a range and save the contents into different worksheets or workbook using XlsIO

Platform: WinForms |
Control: XlsIO

Data in a worksheet can be split into different worksheets or workbooks. To achieve this, the data in the source worksheet need to be accessed by UsedRange property to find the total rows and columns used, then need to split based on the requirement.

The below code shows the portion of saving different workbooks or worksheets by splitting a range.

C#

                //Creating new workbook to save worksheet as a separate file.
                if (!saveInSameWorkbook.Checked)
                {
                    newWorkbook = application.Workbooks.Create(1);
                    newWorkbook.Worksheets[0].Name = worksheet.Name + "_" + i.ToString();
                    tempSheet = newWorkbook.Worksheets[0];
                }
 
                //Creating new worksheet.
                else
                {
                    tempSheet = workbook.Worksheets.Create(worksheet.Name + "_" + i.ToString());
                }
 
                //Condition check to include header row from source sheet while copying.
                if (includeHeader.Checked)
                {
                    worksheet.Range[1, firstColumn, 1, lastColumn].CopyTo(tempSheet.Range[1, firstColumn, 1, lastColumn]);
                    if (firstRow == 1)
                        firstRow++;
                    worksheet.Range[firstRow, firstColumn, rowsofSheet, lastColumn].CopyTo(tempSheet.Range[2, 1, totalRows, lastColumn]);
                }
                else
                {
                    worksheet.Range[firstRow, firstColumn, rowsofSheet, lastColumn].CopyTo(tempSheet.Range[1, 1, totalRows, lastColumn]);
                }
                if (totalRows <= usedRange.Rows.Length)
                {
                    firstRow = rowsofSheet + 1;
                    if (includeHeader.Checked)
                        rowsofSheet += (totalRows - 1);
                    else
                        rowsofSheet += (totalRows);
                }
 
                //Saving sheet in separate workbook.
                if (saveInSeparateWorkbook.Checked)
                {
                    newWorkbook.SaveAs(GetFullOutputPath("sheet" + i.ToString() + ".xlsx"));
                }
 
                //Saving sheet in CSV format.
                else if (saveAsCsv.Checked)
                {
                    newWorkbook.SaveAs("sheet" + i.ToString() + ".csv", ",");
                }
            }
 
            //Saving sheets in same workbook.
            if (saveInSameWorkbook.Checked)
            {
                workbook.SaveAs(GetFullOutputPath("Output.xlsx"));
                MessageBox.Show(string.Format("{0} Worksheet has been created", sheetCount));
            }

 

VB

 
                    'Creating new workbook for saving sheet as seperate file.
                    If Not saveInSameWorkbook.Checked Then
                        newWorkbook = application.Workbooks.Create(1)
                        newWorkbook.Worksheets(0).Name = worksheet.Name + "_" + i.ToString()
                        tempSheet = newWorkbook.Worksheets(0)
 
                        'Creating new worksheet.
                    Else
                        tempSheet = workbook.Worksheets.Create(worksheet.Name + "_" + i.ToString())
                    End If
 
                    'Condition check to include header row from source sheet while copying.
                    If includeHeader.Checked Then
                        worksheet.Range(1, firstColumn, 1, lastColumn).CopyTo(tempSheet.Range(1, firstColumn, 1, lastColumn))
                        If firstRow = 1 Then
                            firstRow += 1
                        End If
                        worksheet.Range(firstRow, firstColumn, rowsofSheet, lastColumn).CopyTo(tempSheet.Range(2, 1, totalRows, lastColumn))
                    Else
                        worksheet.Range(firstRow, firstColumn, rowsofSheet, lastColumn).CopyTo(tempSheet.Range(1, 1, totalRows, lastColumn))
                    End If
                    If totalRows <= usedRange.Rows.Length Then
                        firstRow = rowsofSheet + 1
                        If includeHeader.Checked Then
                            rowsofSheet += (totalRows - 1)
                        Else
                            rowsofSheet += (totalRows)
                        End If
                    End If
 
                    'Saving sheet in separate workbook.
                    If saveInSeperateWorkbook.Checked Then
                        newWorkbook.SaveAs(GetFullOutputPath("sheet" + i.ToString() + ".xlsx"))
 
                        'Saving sheet in CSV format.
                    ElseIf saveAsCsv.Checked Then
                        newWorkbook.SaveAs("sheet" + i.ToString() + ".csv", ",")
                    End If
                    i += 1
                End While
 
                'Saving sheets in same workbook
                If saveInSameWorkbook.Checked Then
                    workbook.SaveAs(GetFullOutputPath("Output.xlsx"))
                    MessageBox.Show(String.Format("{0} Worksheet has been created", sheetCount))

 

The sample which illustrates the above behavior can be downloaded here.

 

2X faster development

The ultimate WinForms UI toolkit to boost your development speed.
ADD COMMENT
You must log in to leave a comment

Please sign in to access our KB

This page will automatically be redirected to the sign-in page in 10 seconds.

Up arrow icon

Warning Icon You are using an outdated version of Internet Explorer that may not display all features of this and other websites. Upgrade to Internet Explorer 8 or newer for a better experience.Close Icon

Live Chat Icon For mobile