How to split a range and save the contents into different worksheets or workbook using XlsIO
Data in a worksheet can be split into different worksheets or workbooks. To achieve this, the data in the source worksheet needs to be accessed using the UsedRange property to find the total rows and columns used, and then split based on the requirement.
The code below shows how to save 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.
