Knowledgebase (2345)
Children categories
In the MS Word Header & Footer Tools options, we could choose "Different First Page" and "Different odd and even pages". The article "How to create different headers/footers for odd and even pages" introduces the method to set different odd and even pages using Spire.Doc. Spire.DOC also provides an easy and quick method to add different first page header & footer. This article is going to introduce the method to add different first page header & footer.
FYI, if you only need the first page header and footer, please just set the first page header & footer and leave the rest alone. In this way, your Word document will only have header & footer in the first page, which provides a simpler way to add a header only into the first page of a document than the method mentioned in the article "How to add a header only into the first page of a document".
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.
Step 1: Load the sample document that only contains text.
Document document = new Document();
document.LoadFromFile("T.docx");
Step 2: Get the section and set the property true.
Section section = document.Sections[0]; section.PageSetup.DifferentFirstPageHeaderFooter = true;
Step 3: Set the first page header. Here we append a picture as the header.
Paragraph paragraph1 = section.HeadersFooters.FirstPageHeader.AddParagraph();
paragraph1.Format.HorizontalAlignment = HorizontalAlignment.Right;
DocPicture headerimage = paragraph1.AppendPicture(Image.FromFile("2.bmp"));
Step 4: Set the first page footer.
Paragraph paragraph2 = section.HeadersFooters.FirstPageFooter.AddParagraph();
paragraph2.Format.HorizontalAlignment = HorizontalAlignment.Center;
TextRange FF = paragraph2.AppendText("First Page Footer");
FF.CharacterFormat.FontSize = 20;
Step 5: Set the other header & footer. If you only need the first page header & footer, don't set this.
Paragraph paragraph3 = section.HeadersFooters.Header.AddParagraph();
paragraph3.Format.HorizontalAlignment = HorizontalAlignment.Center;
TextRange NH = paragraph3.AppendText("If you only need first page header, don't set this.");
NH.CharacterFormat.FontSize = 20;
Paragraph paragraph4 = section.HeadersFooters.Footer.AddParagraph();
paragraph4.Format.HorizontalAlignment = HorizontalAlignment.Center;
TextRange NF = paragraph4.AppendText("If you only need first page footer, don't set this.");
NF.CharacterFormat.FontSize = 20;
Step 6: save the document and launch to see effects.
document.SaveToFile("R.docx", FileFormat.Docx2010);
System.Diagnostics.Process.Start("R.docx");
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 Mirror_Margin
{
class Program
{
static void Main(string[] args)
{
Document document = new Document();
document.LoadFromFile("T.docx");
Section section = document.Sections[0];
section.PageSetup.DifferentFirstPageHeaderFooter = true;
Paragraph paragraph1 = section.HeadersFooters.FirstPageHeader.AddParagraph();
paragraph1.Format.HorizontalAlignment = HorizontalAlignment.Right;
DocPicture headerimage = paragraph1.AppendPicture(Image.FromFile("2.bmp"));
Paragraph paragraph2 = section.HeadersFooters.FirstPageFooter.AddParagraph();
paragraph2.Format.HorizontalAlignment = HorizontalAlignment.Center;
TextRange FF = paragraph2.AppendText("First Page Footer");
FF.CharacterFormat.FontSize = 20;
Paragraph paragraph3 = section.HeadersFooters.Header.AddParagraph();
paragraph3.Format.HorizontalAlignment = HorizontalAlignment.Center;
TextRange NH = paragraph3.AppendText("If you only need first page header, don't set this.");
NH.CharacterFormat.FontSize = 20;
Paragraph paragraph4 = section.HeadersFooters.Footer.AddParagraph();
paragraph4.Format.HorizontalAlignment = HorizontalAlignment.Center;
TextRange NF = paragraph4.AppendText("If you only need first page footer, don't set this.");
NF.CharacterFormat.FontSize = 20;
document.SaveToFile("R.docx", FileFormat.Docx2010);
System.Diagnostics.Process.Start("R.docx");
}
}
}
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.
