Monday, 15 August 2011 07:57

Copy Worksheet in Excel in C#, VB.NET

Excel copy function enables you not only copy worksheets within Excel workbook but also copy worksheets between Excel Workbooks. This section will introduce solutions to copy worksheets in Excel both within one Excel workbook and among workbooks via a .NET Excel component in C#, VB.NET. Besides, all the cell formats in the original Excel worksheets will be completely remained.

Spire.XLS for .NET which enjoys popularity among both enterprise and individual customers enables users to fast manipulate Excel files in C#, VB.NET. With this Excel library, you can copy quickly your Excel worksheets in both cases referred above. And the copied worksheets remain all the formats which looks just the same with original Excel worksheets. You can prove this from picture below:

Copy Excel Worksheet

Before starting your solution, please remember to download Spire.XLS for .NET and install it on your system. After adding the Spire.Xls dll, let us begin Excel copy tasks one by one. First let us see how to copy Excel worksheet within Excel workbook.

Copy Excel Worksheets within Excel Workbook

Copy worksheet within Excel workbook is very easy. You can perform this task only by one line of key code: Worksheet.CopyFrom(Worksheet worksheet). Before copying the worksheets, please make sure that there are specified worksheets where you can copy to and name them. If not, please add them by this method: Worksheet Add(string name).

[C#]
using Spire.Xls;

namespace Copy_Worksheet
{
    class Program
    {
        static void Main(string[] args)
        {
            Workbook workbook = new Workbook();
            workbook.LoadFromFile(@"..\copy worksheets.xls");
            Worksheet worksheet = workbook.Worksheets[0];
            //add worksheets and name them
            workbook.Worksheets.Add("copied sheet1");
            workbook.Worksheets.Add("copied sheet2");
            //copy worksheet to the new added worksheets
            workbook.Worksheets[1].CopyFrom(workbook.Worksheets[0]);
            workbook.Worksheets[2].CopyFrom(workbook.Worksheets[0]);
            workbook.SaveToFile(@"..\copy worksheets.xls",ExcelVersion.Version97to2003);
            System.Diagnostics.Process.Start(@"..\copy worksheets.xls");
        }
    }
}
[VB.NET]
Imports Spire.Xls

Namespace Copy_Worksheet
    
    Class Program
        
        Private Shared Sub Main(ByVal args() As String)
            Dim workbook As Workbook = New Workbook
            workbook.LoadFromFile("..\copy worksheets.xls")
            Dim worksheet As Worksheet = workbook.Worksheets(0)
            'add worksheets and name them
            workbook.Worksheets.Add("copied sheet1")
            workbook.Worksheets.Add("copied sheet2")
            'copy worksheet to the new added worksheets
            workbook.Worksheets(1).CopyFrom(workbook.Worksheets(0))
            workbook.Worksheets(2).CopyFrom(workbook.Worksheets(0))
            workbook.SaveToFile("..\copy worksheets.xls",ExcelVersion.Version97to2003)
            System.Diagnostics.Process.Start("..\copy worksheets.xls")
        End Sub
    End Class
End Namespace

Copy Excel Worksheets between Excel Workbooks

Actually, this solution is not harder compared with the solution above. The difference is that this time the main method is Worksheet. Clone(), while not Worksheet. CopyFrom(), please see it here:
Worksheet newsheet = (Worksheet)worksheet.Clone(worksheet.Parent)

[C#]
using Spire.Xls;

namespace Copy_Worksheet
{
    class Program
    {
        static void Main(string[] args)
        {
            Workbook workbook = new Workbook();
            workbook.LoadFromFile(@"..\copysheet.xls");
            Worksheet worksheet = workbook.Worksheets[0];
            Worksheet newsheet = (Worksheet)worksheet.Clone(worksheet.Parent);
            workbook.Worksheets.Add(newsheet);
            workbook.SaveToFile(@"copy.xls",ExcelVersion.Version97to2003);
            System.Diagnostics.Process.Start(@"copy.xls");
        }
    }
}
[VB.NET]
Imports Spire.Xls

Namespace Copy_Worksheet
    
    Class Program
        
        Private Shared Sub Main(ByVal args() As String)
            Dim workbook As Workbook = New Workbook
            workbook.LoadFromFile("..\copysheet.xls")
            Dim worksheet As Worksheet = workbook.Worksheets(0)
            Dim newsheet As Worksheet = CType(worksheet.Clone(worksheet.Parent),Worksheet)
            workbook.Worksheets.Add(newsheet)
            workbook.SaveToFile("copy.xls",ExcelVersion.Version97to2003)
            System.Diagnostics.Process.Start("copy.xls")
        End Sub
    End Class
End Namespace
Published in Worksheet
Thursday, 11 August 2011 03:06

Decrypt Excel Worksheet in C#, VB.NET

Why Decrypt Excel Worksheet?

To protect our Excel file data information, we can encrypt Excel worksheet by setting password. But we may sometimes forget the password and we can't read the excel data information neither. So, if this happens we need decrypt Excel worksheet. It’s not easy to decrypt Excel Worksheet which has been encrypted because the reason why we encrypt it is to prevent it from reading by people without password. And now, we do not have password.

How to Use C#/VB.NET to Decrypt Excel Worksheet?

Spire.XLS for .NET, a .NET Excel component which enables .NET applications to fast generate, read, write and modify Excel document without Microsoft Office Excel Automation can help us decrypt Excel worksheet by using C#/VB.NET.

Spire.XLS for .NET presents a very easy solution for developers/programmers decrypting Excel worksheet. We just need sheet.Unprotect( "password" ) method and normal encrypted excel worksheet can be decrypted.

Download Spire.XLS for .NET (or Spire.Office) with .NET framework 2.0 (or above) together. The sample code below will show you how to use C#/VB.NET decrypt Excel worksheet through Spire.XLS for .NET.

[C#]
Workbook workbook = new Workbook();
         workbook.LoadFromFile("test.xls");
         Worksheet sheet = workbook.Worksheets[0];
         sheet.Unprotect( "password" );
         workbook.SaveToFile("result.xls",ExcelVersion.Version97to2003);
[VB.NET]
Dim workbook As Workbook
workbook.LoadFromFile("test.xls")
            Worksheet sheet = workbook.Worksheets(0)
            sheet.Unprotect( "password" )
            workbook.SaveToFile("result.xls",ExcelVersion.Version97to2003)

Spire.XLS allows users to operate Excel document directly such as save to stream, save as web response, copy, lock/unlock worksheet, set up workbook properties, etc. As a professional .NET Excel component, it owns the ability of inserting content into Excel document, formatting cells and converting Excel documents to popular office file formats. Spire.XLS for .NET supports Excel 97-2003, Excel 2007 and Excel 2010.

Published in Security