Question:

I'm trying to set an array of values into a range in Excel Worksheet. This is possible via Microsoft Excel object and allows setting values very quickly. How to use Spire.XLS to manage it?

Answer:

It is possible to output an array of data to a range of worksheet via Spire.XLS. Using arrays sheet.InsertArray() method and the following sample source codes for your reference would be helpful.

using Spire.Xls;
namespace SetArrayofValues
{
    class Program
    {
            private void SetArrayValueExample()
        {
            Workbook workbook = new Workbook();
            workbook.CreateEmptySheets(1);

            Worksheet sheet = workbook.Worksheets[0];

            int maxRow = 10000;
            //int maxRow = 5;
            int maxCol = 200;

            //int maxCol = 5;
            object[,] myarray = new object[maxRow + 1, maxCol + 1];
            bool[,] isred = new bool[maxRow + 1, maxCol + 1];
            for (int i = 0; i <= maxRow; i++)
                for (int j = 0; j <= maxCol; j++)
                {
                    myarray[i, j] = i + j;
                    if ((int)myarray[i, j] > 8)
                        isred[i, j] = true;
                }

            sheet.InsertArray(myarray, 1, 1);

            workbook.SaveToFile("test.xls",ExcelVersion.Version97to2003);
        }


        }
    }

If this doesn't answer your question, please do not hesitate to add your question in our forum. Our technical support will answer soon (we promise response within 1 business day)!

Working with large Excel files often means merging rows, columns, or specific cell ranges to keep the data clean and easy to read. You can merge cells in Excel using C# or VB.NET with just a few lines of code and even center the content both horizontally and vertically for a professional look. Whether it’s combining entire rows, joining columns, or formatting a selected range, these examples show how to automate the process and improve your workflow.

• Merge Rows or Columns in Excel using C#
• Merge a Specific Cell Range in Excel
• Merge and Center Cells in Excel using VB.NET
• The Conclusion

If you often work with Excel files in .NET projects, handling tasks like merging cells, formatting data, or generating reports, you’ll want a tool that makes the process fast and reliable. Spire.XLS is a lightweight library that lets you create, read, and modify Excel files directly in C# or VB.NET—without relying on Microsoft Office. You can add it to your project in seconds via NuGet:

PM> Install-Package Spire.XLS

From there, you’re ready to start automating your Excel workflow.

Merge Rows or Columns in Excel using C#

Merging rows or columns in Excel is a common way to organize data and create professional-looking spreadsheets. For example, you can merge the first row across multiple columns to form a clear, centered table header. Using C# code, this process can be automated easily, saving time and ensuring consistency across large Excel files.

Here's the code example of merging the first row and centering it in Excel using C#:

using Spire.Xls;
namespace MergeCells
{

    class Program
    {

        static void Main(string[] args)
        {
            // Create a Workbook instance and load an Excel file
            Workbook workbook = new Workbook();
            workbook.LoadFromFile("\\Population.xlsx");

            // Get the first worksheet
            Worksheet sheet = workbook.Worksheets[0];

            // Merge the first row
            sheet.Rows[0].Merge();
            // Merge the first column
            //sheet.Columns[0].Merge();

            // Set the alignment to center horizontally and vertically
            sheet.Rows[0].Style.HorizontalAlignment = HorizontalAlignType.Center;
            sheet.Rows[0].Style.VerticalAlignment = VerticalAlignType.Center;

            // Save the Excel file
            workbook.SaveToFile("\\Mergerowsandcenter.xls");

        }
    }
}

Here's the preview of merging the first row in Excel:

Code steps explained:

• Create a Workbook object and read an Excel file.
• Get a worksheet.
• Merge a certain row using Worksheet.Rows[].Merge() method.
• Set the alignment to center horizontally and vertically by adjusting the Style.HorizontalAlignment and Style.VerticalAlignment properties.
• Save the modified Excel file.

The C# code above for merging rows in an Excel file can also be used to merge columns, which is indicated with comments in the code.

After merging cells to create a clean table header, you might sometimes need to undo the operation. You can easily unmerge cells in Excel in C# or VB.NET, restoring the original cell layout without affecting your data.

Merge a Specific Cell Range in Excel

Merging a specific cell range in Excel using C# is useful when you want to highlight a section of data, such as a summary row or a group of related columns. The process is very similar to merging entire rows or columns, and you can use the same Merge() method—applied to a CellRange object via Worksheet.Range[].Merge() method. By specifying the exact range to merge, you can create a clear, organized layout that improves readability and automates the operation across multiple sheets or files without manually adjusting each cell.

The code example below demonstrates how to merge the cell range "B6:E6" in an Excel worksheet using C#:

using Spire.Xls;
namespace MergeCells
{

    class Program
    {

        static void Main(string[] args)
        {
            // Create a Workbook instance and load an Excel file
            Workbook workbook = new Workbook();
            workbook.LoadFromFile("\\Population.xlsx");

            // Get the first worksheet
            Worksheet sheet = workbook.Worksheets[0];

            // Merge the particular cell range in Excel 
            sheet.Range["B6:E6"].Merge();

            // Save the Excel file
            workbook.SaveToFile("\\Mergecellrange.xls");

        }
    }
}

Here's the preview of merging a cell range in an Excel file using C#:

Merge and Center Cells in Excel using VB.NET

Just like in C#, you can merge and center cells in Excel using VB.NET to create clean table headers or highlight key data sections. The steps are very similar, and the same Merge() method applies to rows, columns, or specific cell ranges. If you want, you can even convert C# examples to VB.NET using our handy C# ↔ VB.NET code converter, making it easy to adapt existing code for your project.

Here's a code example of merging the first row and centering it in Excel using VB.NET:

Imports Spire.Xls

Namespace MergeCells

    Friend Class Program

        Private Shared Sub Main(args As String())
            ' Create a Workbook instance and load an Excel file
            Dim workbook As Workbook = New Workbook()
            workbook.LoadFromFile("E:\Administrator\Python1\input\Population.xlsx")

            ' Get the first worksheet
            Dim sheet As Worksheet = workbook.Worksheets(0)

            ' Merge the first row
            sheet.Rows(0).Merge()
            ' Merge the first column
            'sheet.Columns[0].Merge();

            ' Set the alignment to center horizontally and vertically
            sheet.Rows(0).Style.HorizontalAlignment = HorizontalAlignType.Center
            sheet.Rows(0).Style.VerticalAlignment = VerticalAlignType.Center

            ' Save the Excel file
            workbook.SaveToFile("\Mergerowsandcenter.xls")

        End Sub
    End Class
End Namespace

The Conclusion

By following these examples, you can merge cells in Excel using C#, whether it’s entire rows, columns, or specific ranges. With the help of Spire.XLS, you can merge cells in Excel files automatically without hassle and create clear, professional-looking spreadsheets. Start streamlining your workflow today and get a 30-day free license to try all features without restrictions.

Excel copy function enables you to not only copy worksheets within Excel workbook but also copy worksheets between different Excel workbooks. This article will introduce solutions to copy worksheets within one Excel workbook and among different workbooks via Spire.XLS for .NET in C#, VB.NET. Besides, all the cell formats in the original Excel worksheets will be completely remained.

• Copy Excel Worksheets within Excel Workbook
• Copy Excel Worksheets between Excel Workbooks

Install Spire.XLS for .NET

To begin with, you need to add the DLL files included in the Spire.XLS for .NET package as references in your .NET project. The DLLs files can be either downloaded from this link or installed via NuGet.

PM> Install-Package Spire.XLS

Copy Excel Worksheets within Excel Workbook

The following are the steps to duplicate worksheets within an Excel workbook.

• Initialize an instance of Workbook class.
• Load an Excel file using Workbook.LoadFromFile() method.
• Add a new blank sheet to the workbook using WorksheetCollection.Add() method.
• Copy the original worksheet to the new sheet using Worksheet.CopyFrom() method.
• Use Workbook.SaveToFile() method to save the changes to another file.

using Spire.Xls;

namespace CopyExcelworksheet
{
    class Program
    {
        static void Main(string[] args)
        {
            //Load the sample Excel
            Workbook workbook = new Workbook();
            workbook.LoadFromFile("Sample.xlsx");

            //Add worksheet and set its name
            workbook.Worksheets.Add("Sheet1_Copy");
         
           //copy worksheet to the new added worksheets
           workbook.Worksheets[1].CopyFrom(workbook.Worksheets[0]);
          
            //Save the Excel workbook.
            workbook.SaveToFile("Duplicatesheet.xlsx", ExcelVersion.Version2013);
            System.Diagnostics.Process.Start("Duplicatesheet.xlsx");

        }
    }
}

Imports Spire.Xls

Namespace CopyExcelworksheet
    
    Class Program
        
        Private Shared Sub Main(ByVal args() As String)
            'Load the sample Excel
            Dim workbook As Workbook = New Workbook
            workbook.LoadFromFile("Sample.xlsx")
            'Add worksheet and set its name
            workbook.Worksheets.Add("Sheet1_Copy")
            'copy worksheet to the new added worksheets
            workbook.Worksheets(1).CopyFrom(workbook.Worksheets(0))
            'Save the Excel workbook.
            workbook.SaveToFile("Duplicatesheet.xlsx", ExcelVersion.Version2013)
            System.Diagnostics.Process.Start("Duplicatesheet.xlsx")
        End Sub
    End Class
End Namespace

Copy Excel Worksheets between Excel Workbooks

The following are the steps to duplicate worksheets within an Excel workbook.

• Initialize an instance of Workbook class.
• Load an Excel file using Workbook.LoadFromFile() method.
• Get the first worksheet.
• Load another Excel sample document
• Add a new blank sheet to the second workbook using WorksheetCollection.Add() method.
• Copy the original worksheet to the new sheet using Worksheet.CopyFrom() method.
• Use Workbook.SaveToFile() method to save the changes to another file.

using Spire.Xls;

namespace CopyExcelworksheet
{
    class Program
    {
        static void Main(string[] args)
        {
            //Load the sample Excel and get the first worksheet
            Workbook workbook = new Workbook();
            workbook.LoadFromFile("Sample.xlsx");
            Worksheet sheet = workbook.Worksheets[0];
            //Load the second Excel workbook
            Workbook workbook2 = new Workbook();
            workbook2.LoadFromFile("New.xlsx");
            //Add a new worksheet and set its name
            Worksheet targetWorksheet = workbook2.Worksheets.Add("added");
            //Copy the original worksheet to the new added worksheets
            targetWorksheet.CopyFrom(sheet);
            //Save the Excel workbook.
            workbook2.SaveToFile("CopySheetBetweenWorkbooks.xlsx", FileFormat.Version2013);
            System.Diagnostics.Process.Start("CopySheetBetweenWorkbooks.xlsx");

        }
    }
}

Imports Spire.Xls

Namespace CopyExcelworksheet
    
    Class Program
        
        Private Shared Sub Main(ByVal args() As String)
            'Load the sample Excel and get the first worksheet
            Dim workbook As Workbook = New Workbook
            workbook.LoadFromFile("Sample.xlsx")
            Dim sheet As Worksheet = workbook.Worksheets(0)
            'Load the second Excel workbook
            Dim workbook2 As Workbook = New Workbook
            workbook2.LoadFromFile("New.xlsx")
            'Add a new worksheet and set its name
            Dim targetWorksheet As Worksheet = workbook2.Worksheets.Add("added")
            'Copy the original worksheet to the new added worksheets
            targetWorksheet.CopyFrom(sheet)
            'Save the Excel workbook.
            workbook2.SaveToFile("CopySheetBetweenWorkbooks.xlsx", FileFormat.Version2013)
            System.Diagnostics.Process.Start("CopySheetBetweenWorkbooks.xlsx")
        End Sub
    End Class
End Namespace

Apply for a Temporary License

If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.