Category / Section
How to split a range and save the contents into different worksheets or workbook using XlsIO
2 mins read
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.