Tuesday, 06 September 2011 06:39

Excel Cell Names

The sample demonstrates how to define named cell references or ranges in excel workbook.

Download MiscDataTable.xls

Wednesday, 17 August 2011 06:53

Convert Specific Worksheet Cells to Image

This section aims at providing a detail solution for developers on how to convert specific worksheet cells to image via this .NET Excel component Spire.XLS for .NET in C#, VB.NET. This Excel library helps us quickly convert certain excel cells to different image formats such as jpeg, png, bmp, tiff, gif, ico, emf, exif etc.

When we convert worksheet to image, Spire.XLS for .NET provides us a method: Spire.Xls.Worksheet.SaveToImage(int firstRow, int firstColumn, int lastRow, int lastColumn); As we see, there are four parameters passed in this method. These four parameters determine the certain range of cells. After deciding the cell range, we can successfully convert cells to image. Now, let us see the whole task step by step.

Step 1: Create a template Excel Workbook in MS Excel

I create a new Excel file in MS Excel and add some data which have different formats in the first sheet, here is a screenshot of created file.

Excel to Image

Step 2: Download and Install Spire.XLS for .NET

Spire.XLS for .NET is an Excel Api which enables users to quickly generate, read, edit and manipulate Excel files on .NET Platform. Here you can download Spire.XLS for .NET and install it on your development computer. After installing it, Spire.XLS for .NET will run in evaluation mode which is the same when you install other Spire components. This evaluation mode has no time limit.

Step 3: Create a project and Add References

We can create a new project of Console Application either in C# or VB.NET. I create in C#, but we can choose VB.NET too.

In this project, we need to add references in our project. Apart from adding System.Drawing, we will use Spire.XLS for .NET, so we also have to add Spire.Xls.dll, Spire.Common.dll and Spire.License.dll in our download Bin folder of Spire.Xls. Here we can see the default path: "..\Spire.XLS\Bin\NET4.0\Spire.XLS.dll"

Step 4: Convert Specific Worksheet Cells to Image

In this step, first we can initialize a new object of the class Spire.Xls.Workbook, then, load the template Excel file. Since I want to convert cells in the first sheet to image, I need get the first worksheet data in Excel file. You also can get other worksheet data. Finally specify cell range and save the cells in the range as image file. Spire.XLS for .NET supports 12 image formats: Bmp, Emf, Equals, Exif, Gif, Icon, Jpeg, MemoryBmp, Png, ReferenceEquals, Tiff and Wmf.

[C#]
using System.Drawing;
using System.Drawing.Imaging;
using Spire.Xls;
using Spire.Xls.Converter;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            //Initialize a new Workbook object
            Workbook workbook = new Workbook();
            //Open Template Excel file
            workbook.LoadFromFile(@"..\excel to image.xlsx");
            //Get the first wirksheet in Excel file
            Worksheet sheet = workbook.Worksheets[0];
            //Specify Cell Ranges and Save to certain Image formats
            sheet.SaveToImage(1, 1, 6, 3).Save("image1.png",ImageFormat.Png);
            sheet.SaveToImage(7, 1, 12, 3).Save("image2.jpeg", ImageFormat.Jpeg);
            sheet.SaveToImage(13, 1, 18, 3).Save("image3.bmp", ImageFormat.Bmp);
        }
    }
}
          
[VB.NET]
Imports System.Drawing
Imports System.Drawing.Imaging
Imports Spire.Xls
Imports Spire.Xls.Converter

Namespace ConsoleApplication1
	Class Program
		Private Shared Sub Main(args As String())
			'Initialize a new Workbook object
			Dim workbook As New Workbook()
			'Open Template Excel file
			workbook.LoadFromFile("..\excel to image.xlsx")
			'Get the first wirksheet in Excel file
			Dim sheet As Worksheet = workbook.Worksheets(0)
			'Specify Cell Ranges and Save to certain Image formats
			sheet.SaveToImage(1, 1, 6, 3).Save("image1.png", ImageFormat.Png)
			sheet.SaveToImage(7, 1, 12, 3).Save("image2.jpeg", ImageFormat.Jpeg)
			sheet.SaveToImage(13, 1, 18, 3).Save("image3.bmp", ImageFormat.Bmp)
		End Sub
	End Class
End Namespace

Result Task

After executing above code, cells in the first worksheet named "Sheet1" has been converted to three images. They are named "image1.png", "image2.jpeg" and "image3.bmp". You can see them as below:

Excel to Image

In this section, I have introduced how we can convert specific cells to image by using Spire.XLS for .NET. I sincerely hope it can help you and give you some insight.

Spire.XLS for .NET offers fast speed, high efficiency and reliability to satisfy development application requirements. As you see above, the results do show that Spire.XLS for .NET benefit customers from years of research.

We appreciate any kind of queries, comments and suggestions at E-iceblue Forum. We promise a quick reply within minutes or hours as we can.

Question:

I want to replace text in a word template with HTML. I know how to append HTML to last paragraph but this paragraph is in the middle of word document.

Answer:

Below is a VB project demo about text replacing with multiple paragraphs and html, hope it will help you.

TextReplaceWithMultipleParagraph2.zip

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)!

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.

       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)!

Tuesday, 16 August 2011 02:18

How to Merge Cells in Excel

Merge Cells in Excel

Sometimes, we work on excel spreadsheet and need merge cells in no matter row or column. Merge multiple cells into one can help readers easily distinguish data information. And sometimes when a title is to be centered over a particular section of a worksheet, author usually will merge cells to make it look better.

How to Use C#/VB.NET to Achieve Cells Merge in Excel?

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.

Microsoft Excel offers a Merge and Center button for uses to merge cells and center the content in cell. On the other hand, Spire.XLS for .NET also allows developers/programmers use C#/VB.NET to merge cells in Excel worksheet.

Through Spire.XLS for .NET, merge cells in Excel via C#/VB.NET can be as easy as in Microsoft Excel. Download Spire.XLS for .NET (or Spire.Office) with .NET framework 2.0 (or above) together and use the sample code below, we can easily use C#/VB.NET achieve cells merge in Excel.

[C#]
Workbook workbook = new Workbook();
workbook.LoadFromFile("test.xls");
workbook.Worksheets[0].Rows[0].Merge();
workbook.SaveToFile("result.xls");
[VB.NET]
Dim workbook As New Workbook()
workbook.LoadFromFile("test.xls")
workbook.Worksheets(0).Rows(0).Merge()
workbook.SaveToFile("result.xls")

Spire.XLS allows user 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.

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
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.

Wednesday, 10 August 2011 02:48

Protect Excel Workbook and Worksheet in C#

One Excel workbook has several worksheets (default 3) so that users can choose to protect Excel workbook or specified worksheets in this workbook. Solutions in this guide focus on demonstrating how to protect Excel workbook and worksheet in C# and VB.NET with Spire.XLS for .NET.

Protect Workbook

Spire.XLS for .NET provides a Workbook.Protect(String) method of Workbook class to prevent other from opening Excel file without a correct password.

[C#]
using Spire.Xls;

namespace ProtectExcel
{
    class Program
    {
        static void Main(string[] args)
        {
            //Load Workbook
            Workbook book = new Workbook();
            book.LoadFromFile(@"E:\Work\Documents\VendorInfo.xlsx");
            //Protect Workbook
            book.Protect("abc-123");
            //Save and Launch
            book.SaveToFile("ProtectExcel.xlsx", ExcelVersion.Version2010);
            System.Diagnostics.Process.Start("ProtectExcel.xlsx");
        }
    }
}
          
[VB.NET]
Imports Spire.Xls

Namespace ProtectExcel
    Friend Class Program
        Shared Sub Main(ByVal args() As String)
            'Load Workbook
            Dim book As New Workbook()
            book.LoadFromFile("E:\Work\Documents\VendorInfo.xlsx")
            'Protect Workbook
            book.Protect("abc-123")
            'Save and Launch
            book.SaveToFile("ProtectExcel.xlsx", ExcelVersion.Version2010)
            System.Diagnostics.Process.Start("ProtectExcel.xlsx")
        End Sub
    End Class
End Namespace
          

Effect as screenshot below:

Encrypt Excel File

Protect Worksheet

Workbook class contains a WorksheetCollection property to enable users to access specified worksheet. After accessing this worksheet, users can invoke Protect method of XlsWorksheetBase class to protect.

There are two possibilities to protect worksheet.

With Password: Pass String password to Protect worksheet to protect worksheet with a password. After protecting, users cannot do any actions on this worksheet.

With Password and Options: Pass String password and SheetProtectionType options to protect worksheet with password and specified permissions.

The following table presents 18 sheet protection types.

None Represents none flags.
Object Protects shapes.
Scenarios Protects scenarios.
FormattingCells Allows users to format any cells on a protected worksheet.
FormattingColumns Allows users to format any columns on a protected worksheet.
FormattingRows Allows users to format any rows on a protected worksheet.
InsertingColumns Allows users to insert columns on a protected worksheet.
InsertingRows Allows users to insert rows on a protected worksheet.
InsertingHyperlinks Allows users to insert hyperlinks on a protected worksheet.
DeletingColumns Allows users to delete columns on a protected worksheet.
DeletingRows Allows users to delete rows on a protected worksheet.
LockedCells Protects locked cells.
Sorting Allows users to sort on a protected worksheet.
Filtering Allows users to set filters on a protected worksheet.
UsingPivotTable Allows users to use pivot table reports on a protected worksheet.
UnlockedCells Protects users interface, but not macros.
Contents Represents all flags.
All Represents default protection.

Code to protect worksheet with password:

[C#]
using Spire.Xls;

namespace ProtectExcel
{
    class Program
    {
        static void Main(string[] args)
        {
            //Load Workbook
            Workbook book = new Workbook();
            book.LoadFromFile(@"E:\Work\Documents\VendorInfo.xlsx");
            //Protect Worksheet with Password
            Worksheet sheet = book.Worksheets[0];
            sheet.Protect("def-345",SheetProtectionType.None);
            //Save and Launch
            book.SaveToFile("ProtectExcel.xlsx", ExcelVersion.Version2010);
            System.Diagnostics.Process.Start("ProtectExcel.xlsx");
        }
    }
}
          
[VB.NET]
Imports Spire.Xls

Namespace ProtectExcel
    Friend Class Program
        Shared Sub Main(ByVal args() As String)
            'Load Workbook
            Dim book As New Workbook()
            book.LoadFromFile("E:\Work\Documents\VendorInfo.xlsx")
            'Protect Worksheet with Password
            Dim sheet As Worksheet = book.Worksheets(0)
            sheet.Protect("def-345", SheetProtectionType.None)
            'Save and Launch
            book.SaveToFile("ProtectExcel.xlsx", ExcelVersion.Version2010)
            System.Diagnostics.Process.Start("ProtectExcel.xlsx")
        End Sub
    End Class
End Namespace
          

Effect as screenshot below:

Encrypt Excel File

Spire.XLS, specified designed for developers, enables to generate, write, edit and save Excel workbooks (Excel 97-2003, Excel 2007 and Excel 2010) in .NET, Silverlight and WPF with C# and VB.NET.

Tuesday, 09 August 2011 03:46

Easily Lock Excel Worksheet in C#, VB.NET

The most important reason that people lock worksheet when perform tasks in Excel is to protect the original Excel file from editing or modifying by other people. Through Microsoft Excel, you can set the entire Excel worksheet by setting its property as Read Only or just set partial region area cells as Read Only via protecting worksheet. While how to lock worksheet with C#, VB.NET will be the core topic in this section.

Spire.XLS for .NET, as a fast and reliable excel component, enables you to lock your worksheet by setting Worksheet class property: Worksheet.Range.Style.Locked = true. By this component, you can lock any worksheet that you need. In this solution, worksheet one and worksheet two are locked as you view in below picture:

Lock Excel Worksheet

Now, before the detail code, you have to add Spire.Xls dll by download Spire.XLS for .NET.

[C#]
using Spire.Xls;

namespace lock_excel_worksheet
{
    class Program
    {
        static void Main(string[] args)
        {
            Workbook workbook = new Workbook();
            workbook.LoadFromFile(@"..\lock worksheet.xls");
            workbook.Worksheets[0].Range.Style.Locked = true;
            workbook.Worksheets[1].Range.Style.Locked = true;
            workbook.Worksheets[0].Protect("", SheetProtectionType.All);
            workbook.Worksheets[1].Protect("", SheetProtectionType.All);
            workbook.SaveToFile("result.xls",ExcelVersion.Version97to2003);
        }
    }
}
[VB.NET]
Imports Spire.Xls

Namespace lock_excel_worksheet
    
    Class Program
        
        Private Shared Sub Main(ByVal args() As String)
            Dim workbook As Workbook = New Workbook
            workbook.LoadFromFile("..\lock worksheet.xls")
            workbook.Worksheets(0).Range.Style.Locked = true
            workbook.Worksheets(1).Range.Style.Locked = true
            workbook.Worksheets(0).Protect("", SheetProtectionType.All)
            workbook.Worksheets(1).Protect("", SheetProtectionType.All)
            workbook.SaveToFile("result.xls",ExcelVersion.Version97to2003)
        End Sub
    End Class
End Namespace

Spire.XLS allows user 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/Silverlight 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.

Friday, 05 August 2011 03:29

Export Data from Listview to CSV

Why Export Listview to CSV?

CSV file (Comma Separated Values, sometimes also called Comma Delimited) is a specially formatted plain text file which stores spreadsheet or basic database-style information in a very simple format, with one record on each line, and each field within that record separated by a comma. It is often used to exchange data between disparate applications. CSV has become a pseudo standard throughout the industry, even among non-Microsoft platforms because it is used in Microsoft Excel.

CSV files are often used as a simple way to transfer a large volume of spreadsheet or database information between programs, without worrying about special file types. For example, transferring a home-made address book from Excel into a database program such as Filemaker Pro could be done by exporting the file as a CSV from Excel, then importing that CSV into File maker.

How to Export Listview to CSV?

Spire.DataExport for .NET presents an easy solution for exporting Listvie to CSV. Spire.DataExport is a 100% pure .NET component suit for exporting data into MS Excel, MS Word, HTML, XML, PDF, MS Access, DBF, SQL Script, SYLK, DIF, CSV ,MS Clipboard format. Quickly and easily export data from Command, ListView, DataTable components. Save you much time and money by using Spire.DataExport.

Download Spire.DataExport (or Spire.Office) with .NET framework 2.0 (or above) together and use the code below to Export Listview to CSV:

[C#]
this.txtExport1.ActionAfterExport = Spire.DataExport.Common.ActionType.OpenView;
    this.txtExport1.DataFormats.CultureName = "en-us";
    this.txtExport1.DataFormats.Currency = "c";
    this.txtExport1.DataFormats.DateTime = "yyyy-M-d H:mm";
    this.txtExport1.DataFormats.Float = "g";
    this.txtExport1.DataFormats.Integer = "g";
    this.txtExport1.DataFormats.Time = "H:mm";
    this.txtExport1.DataEncoding = Spire.DataExport.Common.EncodingType.ASCII;
    this.txtExport1.DataSource = ExportSource.ListView;
    this.txtExport1.ListView = this.ListView1
    txtExport1.ExportType = TextExportType.CSV;
    txtExport1.FileName = "sample.csv";
    txtExport1.SaveToFile();
[VB.NET]
Me.txtExport1.ActionAfterExport = Spire.DataExport.Common.ActionType.OpenView
            Me.txtExport1.DataFormats.CultureName = "en-us"
            Me.txtExport1.DataFormats.Currency = "c"
            Me.txtExport1.DataFormats.DateTime = "yyyy-M-d H:mm"
            Me.txtExport1.DataFormats.Float = "g"
            Me.txtExport1.DataFormats.Integer = "g"
            Me.txtExport1.DataFormats.Time = "H:mm"
            Me.txtExport1.DataEncoding = Spire.DataExport.Common.EncodingType.ASCII
            Me.txtExport1.DataSource = Common.ExportSource.ListView
            Me.txtExport1.ListView = Me.ListView1
txtExport1.ExportType = TextExportType.CSV
                        txtExport1.FileName = "sample.csv"
                        txtExport1.SaveToFile()