Knowledgebase (2330)
Children categories
OLE object is used to make content created in one program available in another program, for instance, we can insert Word as OLE object in Excel sheet.
As a robust component, Spire.XLS supports to insert Word and PowerPoint slide as linked object or embedded object into Excel. In this article, we make an example to explain how to insert Word as OLE object into Excel using Spire.XLS and Spire.Doc. Before coding, you need to download Spire.Office and reference related the Dlls in your VS project.
Code Snippet:
Step 1: Define a GetDocImage(string doxcFile) method to get olePicture. Actually, the olePicture is an image of data information in original Word document. The image generated from the specified page will be shown in Excel sheet after inserting OLE object into it.
private static Image GetDocImage(string docxFile)
{
Document document = new Document();
document.LoadFromFile(docxFile);
return document.SaveToImages(0, Spire.Doc.Documents.ImageType.Bitmap);
}
Step 2: Insert OLE object in Excel. After getting the worksheet from Excel file, we call GetDocImage(string doxcFile) method which is defined in the first step to get image source and then use the ws.OleObjects.Add(string FileName, Image image, OleLinkType linkType) method to insert the new OLE object to worksheet.
static void Main(string[] args)
{
//load Excel file
Workbook workbook = new Workbook();
workbook.LoadFromFile("d:\\sample.xlsx");
Worksheet ws = workbook.Worksheets[0];
//insert OLE object
string docx = "d:\\sample.docx";
Image image = GetDocImage(docx);
IOleObject oleObject = ws.OleObjects.Add(docx,image,OleLinkType.Embed);
oleObject.Location=ws.Range["B4"];
oleObject.ObjectType = OleObjectType.WordDocument;
//save the file
workbook.SaveToFile("result.xlsx",ExcelVersion.Version2010);
System.Diagnostics.Process.Start("result.xlsx");
}
Result:
Full Code:
using Spire.Doc;
using Spire.Xls;
using Spire.Xls.Core;
using System.Drawing;
namespace InsertOLEObject
{
class Program
{
private static Image GetDocImage(string docxFile)
{
Document document = new Document();
document.LoadFromFile(docxFile);
return document.SaveToImages(0, Spire.Doc.Documents.ImageType.Bitmap);
}
static void Main(string[] args)
{
//load Excel file
Workbook workbook = new Workbook();
workbook.LoadFromFile("d:\\sample.xlsx");
Worksheet ws = workbook.Worksheets[0];
//insert OLE object
string docx = "d:\\sample.docx";
Image image = GetDocImage(docx);
IOleObject oleObject = ws.OleObjects.Add(docx, image, OleLinkType.Embed);
oleObject.Location = ws.Range["B4"];
oleObject.ObjectType = OleObjectType.WordDocument;
//save the file
workbook.SaveToFile("result.xlsx", ExcelVersion.Version2010);
System.Diagnostics.Process.Start("result.xlsx");
}
}
}
Imports Spire.Doc
Imports Spire.Xls
Imports Spire.Xls.Core
Imports System.Drawing
Namespace InsertOLEObject
Class Program
Private Shared Function GetDocImage(docxFile As String) As Image
Dim document As New Document()
document.LoadFromFile(docxFile)
Return document.SaveToImages(0, Spire.Doc.Documents.ImageType.Bitmap)
End Function
Private Shared Sub Main(args As String())
'load Excel file
Dim workbook As New Workbook()
workbook.LoadFromFile("d:\sample.xlsx")
Dim ws As Worksheet = workbook.Worksheets(0)
'insert OLE object
Dim docx As String = "d:\sample.docx"
Dim image As Image = GetDocImage(docx)
Dim oleObject As IOleObject = ws.OleObjects.Add(docx, image, OleLinkType.Embed)
oleObject.Location = ws.Range("B4")
oleObject.ObjectType = OleObjectType.WordDocument
'save the file
workbook.SaveToFile("result.xlsx", ExcelVersion.Version2010)
System.Diagnostics.Process.Start("result.xlsx")
End Sub
End Class
End Namespace
Sometimes, you may want to hide one or more worksheets in an Excel workbook to prevent the data they contain from being viewed by others. After hiding, the data in the hidden worksheets will no longer be visible, but it can still be referenced by other worksheets. If you want to display the data again, you can show the hidden worksheets at any time. In this article, you will learn how to hide or show worksheets in Excel in C# and VB.NET using Spire.XLS for .NET library.
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
Hide a Worksheet in Excel in C# and VB.NET
There can be two levels of worksheet hiding: hidden and very hidden. An end user can easily show a hidden worksheet using the unhide command of Excel, but if the worksheet is set as very hidden, he/she cannot make the sheet visible again via the Excel user interface.
In Spire.XLS for .NET, you can set a worksheet as hidden or very hidden by setting the Worksheet.Visibility property to WorksheetVisibility.Hidden or WorksheetVisibility.StrongHidden. Please note that you must leave at least one worksheet visible in an Excel workbook.
The following steps demonstrate how to set a worksheet as hidden or very hidden:
- Initialize an instance of the Workbook class.
- Load an Excel file through Workbook.LoadFromFile() method.
- Get a specific worksheet in the file by its index through Workbook.Worksheets[int] property.
- Set the worksheet as hidden or very hidden by setting the Worksheet.Visibility property to WorksheetVisibility.Hidden or WorksheetVisibility.StrongHidden.
- Save the result file using Workbook.SaveToFile() method.
- C#
- VB.NET
using Spire.Xls;
namespace HideWorksheetsInExcel
{
class Program
{
static void Main(string[] args)
{
//Initialize an instance of the Workbook class
Workbook workbook = new Workbook();
//Load an Excel file
workbook.LoadFromFile("Sample.xlsx");
//Get the first worksheet
Worksheet sheet1 = workbook.Worksheets[0];
//Set the worksheet as hidden
sheet1.Visibility = WorksheetVisibility.Hidden;
//Get the second worksheet
Worksheet sheet2 = workbook.Worksheets[1];
//Set the worksheet as very hidden
sheet2.Visibility = WorksheetVisibility.StrongHidden;
//Save the result file
workbook.SaveToFile("HideWorksheets.xlsx", ExcelVersion.Version2013);
}
}
}
Show All Hidden Worksheets in Excel in C# and VB.NET
You can show a hidden worksheet in Excel by setting the Worksheet.Visibility property to WorksheetVisibility.Visible.
The following steps demonstrate how to show all hidden worksheets in an Excel file:
- Initialize an instance of the Workbook class.
- Load an Excel file through Workbook.LoadFromFile() method.
- Iterate through all worksheets in the Excel file.
- Find the hidden or very hidden worksheets, and then make them visible by setting the Worksheet.Visibility property to WorksheetVisibility.Visible.
- Save the result file using Workbook.SaveToFile() method.
- C#
- VB.NET
using Spire.Xls;
namespace ShowHiddenWorksheetsInExcel
{
class Program
{
static void Main(string[] args)
{
//Initialize an instance of the Workbook class
Workbook workbook = new Workbook();
//Load an Excel file
workbook.LoadFromFile("HideWorksheets.xlsx");
//Iterate through all worksheets in the file
foreach (Worksheet sheet in workbook.Worksheets)
{
//Show hidden worksheets
if (sheet.Visibility == WorksheetVisibility.Hidden)
{
sheet.Visibility = WorksheetVisibility.Visible;
}
//Show very hidden worksheets
else if (sheet.Visibility == WorksheetVisibility.StrongHidden)
{
sheet.Visibility = WorksheetVisibility.Visible;
}
}
//Save the result file
workbook.SaveToFile("ShowHiddenWorksheets.xlsx", ExcelVersion.Version2013);
}
}
}
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.
In the tutorials part of Spire.Doc, we have introduced the simple and easy method to "Replace text in Word with Table in C#" and "Replace images in Word with Texts in C#". Sometimes, we need to replace text in Word with image. Spire.Doc also provides a quick and effective solution to achieve this function with a little bit different codes. This article is going to introduce the method to replace text in Word with image.
Note: Before start, please download the latest version of Spire.Doc and add Spire.Doc .dll in the bin folder as the reference of visual studio.
Sample document:
Step 1: Load the sample Word document and the image used to replace the text.
Document document = new Document();
document.LoadFromFile("s.docx");
Image image = Image.FromFile("2.bmp");
Step 2: Find the string "E-iceblue" in the document.
TextSelection[] selections = document.FindAllString("E-iceblue", true, true);
int index = 0;
TextRange range = null;
Step 3: Remove the text and replace it with Image
foreach (TextSelection selection in selections)
{
DocPicture pic = new DocPicture(document);
pic.LoadImage(image);
range = selection.GetAsOneRange();
index = range.OwnerParagraph.ChildObjects.IndexOf(range);
range.OwnerParagraph.ChildObjects.Insert(index, pic);
range.OwnerParagraph.ChildObjects.Remove(range);
}
Step 4: Save and launch the document to see effects.
document.SaveToFile("Sample.doc", FileFormat.Doc);
System.Diagnostics.Process.Start("Sample.doc");
Effects:
Full codes:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Spire.Doc;
using Spire.Doc.Documents;
using Spire.Doc.Fields;
using System.Drawing;
namespace Replace_Text_with_Image
{
class Program
{
static void Main(string[] args)
{
Document document = new Document();
document.LoadFromFile("s.docx");
Image image = Image.FromFile("2.bmp");
TextSelection[] selections = document.FindAllString("E-iceblue", true, true);
int index = 0;
TextRange range = null;
foreach (TextSelection selection in selections)
{
DocPicture pic = new DocPicture(document);
pic.LoadImage(image);
range = selection.GetAsOneRange();
index = range.OwnerParagraph.ChildObjects.IndexOf(range);
range.OwnerParagraph.ChildObjects.Insert(index, pic);
range.OwnerParagraph.ChildObjects.Remove(range);
}
document.SaveToFile("Sample.doc", FileFormat.Doc);
System.Diagnostics.Process.Start("Sample.doc");
}
}
}
