Excel Data Export With Formulas
The sample demonstrates how to export data to datatable with calculating formulas
SparkLine Chart
The sample demonstrates how to insert SparkLine into an excel workbook.
Excel Cell Names
The sample demonstrates how to define named cell references or ranges in excel workbook.
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.
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.
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);
}
}
}
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:
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.
How to Replace Text in a Word Template with HTML
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)!
Set an Array of Values into a Range in Excel Worksheet
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)!
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.
using Spire.Xls;
namespace MergeCells
{
class Program
{
static void Main(string[] args)
{
Workbook workbook = new Workbook();
workbook.LoadFromFile("test.xls");
workbook.Worksheets[0].Rows[0].Merge();
workbook.SaveToFile("result.xls");
}
}
}
Imports Spire.Xls
Namespace MergeCells
Class Program
Private Shared Sub Main(args As String())
Dim workbook As New Workbook()
workbook.LoadFromFile("test.xls")
workbook.Worksheets(0).Rows(0).Merge()
workbook.SaveToFile("result.xls")
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 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.
C#/VB.NET: Copy Worksheets in Excel
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.
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.
- Package Manager
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.
- C#
- VB.NET
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.
- C#
- VB.NET
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.
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.
using Spire.Xls;
namespace DescryptWorksheet
{
class Program
{
static void Main(string[] args)
{
Workbook workbook = new 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.
C#/VB.NET: Protect or Unprotect Excel Documents
When sharing Excel files with other people or sending files out of your organization, you may want to protect sensitive data from being changed, moved, or deleted. The easiest way to prevent accidental or deliberate changes in the contents is to restrict editing on a worksheet or password protect an entire workbook. In this article, you will learn how to protect and unprotect a workbook or a worksheet in C# and VB.NET using Spire.XLS for .NET.
- Password Protect an Entire Workbook
- Protect a Worksheet with a Specific Protection Type
- Allow Users to Edit Ranges in a Protected Worksheet
- Lock Specific Cells in a Worksheet
- Unprotect a Password Protected Worksheet
- Remove or Reset Password of an Encrypted Workbook
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 DLL files can be either downloaded from this link or installed via NuGet.
PM> Install-Package Spire.XLS
Password Protect an Entire Workbook in C# and VB.NET
By encrypting an Excel document with a password, you ensure that only you and authorized individuals can read or edit it. The following are the steps to password protect a workbook using Spire.XLS for .NET.
- Create a Workbook object.
- Load an Excel file using Workbook.LoadFromFile() method.
- Protect the workbook using a password using Workbook.Protect() method.
- Save the workbook to another Excel file using Workbook.SaveToFile() method.
- C#
- VB.NET
using Spire.Xls;
namespace PasswordProtectWorkbook
{
class Program
{
static void Main(string[] args)
{
//Create a Workbook object
Workbook workbook = new Workbook();
//Load an Excel file
workbook.LoadFromFile(@"C:\Users\Administrator\Desktop\sample.xlsx");
//Protect workbook with a password
workbook.Protect("psd-123");
//Save the workbook to another Excel file
workbook.SaveToFile("Encrypted.xlsx", ExcelVersion.Version2016);
}
}
}
Protect a Worksheet with a Specific Protection Type in C# and VB.NET
If you wish to grant people permission to read your Excel document but restrict the types of modifications they are allowed to make on a worksheet, you can protect the worksheet with a specific protection type. The table below lists a variety of pre-defined protection types under the SheetProtectionType enumeration.
| Protection Type | Allow users to |
| Content | Modify or insert content. |
| DeletingColumns | Delete columns. |
| DeletingRows | Delete rows. |
| Filtering | Set filters. |
| FormattingCells | Format cells. |
| FormattingColumns | Format columns. |
| FormattingRows | Format rows. |
| InsertingColumns | Insert columns. |
| InsertingRows | Insert rows. |
| InsertingHyperlinks | Insert hyperlinks . |
| LockedCells | Select locked cells. |
| UnlockedCells | Select unlocked cells. |
| Objects | Modify drawing objects. |
| Scenarios | Modify saved scenarios. |
| Sorting | Sort data. |
| UsingPivotTables | Use pivot table and pivot chart. |
| All | Do any operations listed above on the protected worksheet. |
| None | Do nothing on the protected worksheet. |
The following are the steps to protect a worksheet with a specific protection type using Spire.XLS for .NET.
- Create a Workbook object.
- Load an Excel file using Workbook.LoadFromFile() method.
- Get a specific worksheet through Workbook.Worksheets[index] property.
- Protect the worksheet with a protection type using Worksheet.Protect(string password, SheetProtectionType options) method.
- Save the workbook to another Excel file using Workbook.SaveToFile() method.
- C#
- VB.NET
using Spire.Xls;
namespace ProtectWorksheetWithSpecificProtectionType
{
class Program
{
static void Main(string[] args)
{
//Create a Workbook object
Workbook workbook = new Workbook();
//Load an Excel file
workbook.LoadFromFile(@"C:\Users\Administrator\Desktop\sample.xlsx");
//Get a specific worksheet
Worksheet worksheet = workbook.Worksheets[0];
//Protect the worksheet with the permission password and the specific protect type
worksheet.Protect("psd-permission", SheetProtectionType.None);
//Save the workbook to another Excel file
workbook.SaveToFile("ProtectWorksheet.xlsx", ExcelVersion.Version2016);
}
}
}
Allow Users to Edit Ranges in a Protected Worksheet in C# and VB.NET
In certain cases, you may need to allow users to be able to edit selected ranges in a protected worksheet. The following steps demonstrate how to.
- Create a Workbook object.
- Load an Excel file using Workbook.LoadFromFile() method.
- Get a specific worksheet through Workbook.Worksheets[index] property.
- Specify editable cell ranges using Worksheet.AddAllowEditRange() method.
- Protect the worksheet with a protection type using Worksheet.Protect(string password, SheetProtectionType options) method.
- Save the workbook to another Excel file using Workbook.SaveToFile() method.
- C#
- VB.NET
using Spire.Xls;
namespace AllowEditRanges
{
class Program
{
static void Main(string[] args)
{
//Create a Workbook object
Workbook workbook = new Workbook();
//Load an Excel file
workbook.LoadFromFile(@"C:\Users\Administrator\Desktop\sample.xlsx");
//Get the first worksheet
Worksheet sheet = workbook.Worksheets[0];
//Add ranges that allow editing
sheet.AddAllowEditRange("Range One", sheet.Range["A5:A6"]);
sheet.AddAllowEditRange("Range Two", sheet.Range["A8:B11"]);
//Protect the worksheet with a password and a protection type
sheet.Protect("psd-permission", SheetProtectionType.All);
//Save the workbook to another Excel file
workbook.SaveToFile("AllowEditRange.xlsx", ExcelVersion.Version2016);
}
}
}
Unprotect a Password Protected Worksheet in C# and VB.NET
To remove the protection of a password-protected worksheet, invoke the Worksheet.Unprotect() method and pass in the original password as a parameter. The detailed steps are as follows.
- Create a Workbook object.
- Load an Excel file using Workbook.LoadFromFile() method.
- Get a specific worksheet through Workbook.Worksheets[index] property.
- Remove the protection using Worksheet.Unprotect(string password) method.
- Save the workbook to another Excel file using Workbook.SaveToFile() method.
- C#
- VB.NET
using Spire.Xls;
namespace UnprotectWorksheet
{
class Program
{
static void Main(string[] args)
{
//Create a Workbook object
Workbook workbook = new Workbook();
//Load an Excel file containing protected worksheet
workbook.LoadFromFile(@"C:\Users\Administrator\Desktop\ProtectedWorksheet.xlsx");
//Get the first worksheet
Worksheet sheet = workbook.Worksheets[0];
//Unprotect the worksheet using the specified password
sheet.Unprotect("psd-permission");
//Save the workbook to anther Excel file
workbook.SaveToFile("UnprotectWorksheet.xlsx", ExcelVersion.Version2016);
}
}
}
Remove or Reset Password of an Encrypted Workbook in C# and VB.NET
To remove or reset password of an encrypted workbook, you can use the Workbook.Unprotect() method and the Workbook.Protect() method, respectively. The following steps show you how to load an encrypted Excel document and delete or change the password of it.
- Create a Workbook object.
- Specify the open password through Workbook.OpenPassword property.
- Load the encrypted Excel file using Workbook.LoadFromFile() method.
- Remove the encryption using Workbook.Unprotect() method. Or change the password using Workbook.Protect() method.
- Save the workbook to another Excel file using Workbook.SaveToFile() method.
- C#
- VB.NET
using Spire.Xls;
namespace RemoveOrResetPassword
{
class Program
{
static void Main(string[] args)
{
//Create a Workbook object
Workbook workbook = new Workbook();
//Specify the open password
workbook.OpenPassword = "psd-123";
//Load an encrypted Excel file
workbook.LoadFromFile(@"C:\Users\Administrator\Desktop\Encrypted.xlsx");
//Unprotect workbook
workbook.UnProtect();
//Reset password
//workbook.Protect("newpassword");
//Save the workbook to another Excel file
workbook.SaveToFile("Unprotect.xlsx", ExcelVersion.Version2016);
}
}
}
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.