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:

How to add different first page header & footer

How to add different first page header & footer

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:

How to Insert OLE Object in Excel in C#, VB.NET

Full Code:

[C#]
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");
        }
    }
}
[VB.NET]
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);
        }
    }
}

C#/VB.NET: Hide or Show Worksheets in Excel

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);
        }
    }
}

C#/VB.NET: Hide or Show Worksheets in Excel

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.

How to replace text in Word with image

2015-06-17 08:15:32 Written by Koohji

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:

How to replace text in Word with image

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:

How to replace text in Word with image

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");

        }
    }
}

C#: Split PowerPoint Presentations

2024-08-02 09:07:00 Written by Koohji

PowerPoint presentations are a fundamental tool for communication across various fields. As these presentations grow in size and complexity, managing them effectively becomes crucial. One practical solution is to split larger presentations into smaller and more manageable ones. Whether for adapting presentations to specific audiences, breaking down training modules, or optimizing file sizes for distribution, the ability to split PowerPoint presentations using C# in .NET significantly enhances workflow efficiency. This article will show how to split PowerPoint presentations by slides, slide ranges, and sections with C# using Spire.Presentation for .NET.

Install Spire.Presentation for .NET

To begin with, you need to add the DLL files included in the Spire.Presentation 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.Presentation

Split PowerPoint Presentations by Slides using C#

Using Spire.Presentation for .NET, developers can split a presentation into individual slide presentations by iterating through the slides in the original presentation, adding each slide to a new presentation, and then saving it.

Here are the detailed steps:

  • Create an instance of Presentation class.
  • Load a PowerPoint file using Presentation.LoadFromFile() method.
  • Iterate through the slides in the presentation:
    • Get a slide through Presentation.Slides[] property.
    • Create a new instance of Presentation class and remove the default slide using Presentation.Slides.RemoveAt(0) method.
    • Append the slide to the new presentation using Presentation.Slides.Append() method.
    • Save the new presentation using ISlide.SaveToFile() method.
  • C#
using Spire.Presentation;

namespace SplitPresentationSlide
{
    class Program
    {
        static void Main(string[] args)
        {
            // Create an instance of Presentation class
            Presentation presentation = new Presentation();

            // Load a PowerPoint presentation
            presentation.LoadFromFile("Sample.pptx");

            // Iterate through all slides
            for (int i = 0; i < presentation.Slides.Count; i++)
            {
                // Get a slide
                ISlide slide = presentation.Slides[i];
                // Create a new presentation and remove the default slide
                Presentation newPresentation = new Presentation();
                newPresentation.Slides.RemoveAt(0);
                // Append the slide to the new presentation
                newPresentation.Slides.Append(slide);
                // Save the new presentation
                newPresentation.SaveToFile("output/Presentations/Slide-" + (i +1).ToString() + ".pptx", FileFormat.Pptx2013);
                newPresentation.Dispose();
            }
            presentation.Dispose();
        }
    }
}

C#: Split PowerPoint Presentations

Split PowerPoint Presentations by Slide Ranges using C#

In addition to splitting PowerPoint presentations into individual slide presentations, developers can also split presentations into slide ranges by copying specified ranges of slides to new presentations and saving them.

Here are the detailed steps:

  • Create an instance of Presentation class.
  • Load a PowerPoint presentation using Presentation.LoadFromFile() method.
  • Create new instances of Presentation class and remove the default slides.
  • Append specified ranges of slides to the new presentations using Presentation.Slides.Append() method.
  • Save the new presentations using Presentation.SaveToFile() method.
  • C#
using Spire.Presentation;

namespace SplitPresentationSlide
{
    class Program
    {
        static void Main(string[] args)
        {
            // Create an instance of Presentation class
            Presentation presentation = new Presentation();

            // Load the original PowerPoint presentation
            presentation.LoadFromFile("Sample.pptx");

            // Create two new instances of Presentation class
            Presentation presentation1 = new Presentation();
            Presentation presentation2 = new Presentation();

            // Remove the default blank slides
            presentation1.Slides.RemoveAt(0);
            presentation2.Slides.RemoveAt(0);

            // Append specific ranges of slides to the new presentations
            for (int i = 0; i < 3; i++)
            {
                presentation1.Slides.Append(presentation.Slides[i]);
            }
            for (int i = 3; i < presentation.Slides.Count; i++)
            {
                presentation2.Slides.Append(presentation.Slides[i]);
            }

            // Save the new presentation
            presentation1.SaveToFile("output/Presentations/SLideRange1.pptx", FileFormat.Pptx2013);
            presentation2.SaveToFile("output/Presentations/SLideRange2.pptx", FileFormat.Pptx2013);

            presentation1.Dispose();
            presentation2.Dispose();
            presentation.Dispose();
        }
    }
}

C#: Split PowerPoint Presentations

Split PowerPoint Presentations by Sections using C#

Developers also can split a presentation into sections by iterating through the sections in the presentation, adding each slide within those sections to a new PowerPoint presentation, and then saving it.

Here are the detailed steps:

  • Create an instance of Presentation class and load a PowerPoint presentation using Presentation.LoadFromFile() method.
  • Iterate through the sections in the presentation:
    • Get a section through Presentation.SectionList[] property.
    • Create a new Presentation instance and remove the default slide.
    • Add a section to the new presentation with the same name using Presentation.SectionList.Append() method.
    • Get the slides in the original section using Section.GetSlides() method.
    • Iterate through the slides and add them to the new section using Presentation.SectionList[].Insert() method.
    • Save the new presentation using Presentation.SaveToFile() method.
  • C#
using Spire.Presentation;

namespace SplitPresentationSlide
{
    class Program
    {
        static void Main(string[] args)
        {
            // Create an instance of Presentation class
            Presentation presentation = new Presentation();

            // Load a PowerPoint presentation
            presentation.LoadFromFile("Sample.pptx");

            for (int i = 0; i < presentation.SectionList.Count; i++)
            {
                // Get the current section
                Section section = presentation.SectionList[i];
                // Create a new instance of Presentation class and remove the default slide
                Presentation newPresentation = new Presentation();
                newPresentation.Slides.RemoveAt(0);
                // Add a section to the new presentation
                newPresentation.SectionList.Append(section.Name);
                // Get the slides in the section
                ISlide[] slides = section.GetSlides();
                foreach (ISlide slide in slides)
                {
                    // Insert the slide to the new section in the new presentation
                    newPresentation.SectionList[0].Insert(0, slide);
                }
                // Save the new presentation
                newPresentation.SaveToFile("output/Presentations/Section-" + (i + 1).ToString() + ".pptx", FileFormat.Pptx2019);
                newPresentation.Dispose();
            }
            presentation.Dispose();
        }
    }
}

C#: Split PowerPoint Presentations

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.

Set vertical alignment for table can show contents in different positions. There are three options including Top, Bottom, Middle. Default is Middle. This article talk about how to set vertical alignment for tables via Spire.Doc, the following is the detailed steps:

Step 1: Create a new Word document and add a new section.

Document document = new Document();
Section section = document.AddSection();

Step 2: Add a table with 3 columns and 3 rows. You can set showBoder property as true when you creating the table. Merge the first column as one cell.

Table table = section.AddTable(true);
table.ResetCells(3, 3);
table.ApplyVerticalMerge(0, 0, 2);

Step 3: Set the vertical alignment for each cell, default is top. Here we set the first row as Top, second row as Middle, third row as Bottom.

table.Rows[0].Cells[0].CellFormat.VerticalAlignment = VerticalAlignment.Middle;
table.Rows[0].Cells[1].CellFormat.VerticalAlignment = VerticalAlignment.Top;
table.Rows[0].Cells[2].CellFormat.VerticalAlignment = VerticalAlignment.Top;
table.Rows[1].Cells[1].CellFormat.VerticalAlignment = VerticalAlignment.Middle;
table.Rows[1].Cells[2].CellFormat.VerticalAlignment = VerticalAlignment.Middle;
table.Rows[2].Cells[1].CellFormat.VerticalAlignment = VerticalAlignment.Bottom;
table.Rows[2].Cells[2].CellFormat.VerticalAlignment = VerticalAlignment.Bottom;

Step 4: Append data to table.

Paragraph paraPic = table.Rows[0].Cells[0].AddParagraph();
DocPicture pic = paraPic.AppendPicture(Image.FromFile("1.png"));

String[][] data = {
                       new string[] {"","Spire.Office","Spire.DataExport"},
                       new string[] {"","Spire.Doc","Spire.DocViewer"},
                       new string[] {"","Spire.XLS","Spire.PDF"}
                            };

      for (int r = 0; r < 3; r++)
      {
        TableRow dataRow = table.Rows[r];
        dataRow.Height = 50; 
        for (int c = 0; c < 3; c++)
           {
             if (c == 1)
               {
                  Paragraph par = dataRow.Cells[c].AddParagraph();
                  par.AppendText(data[r][c]);
                  dataRow.Cells[c].Width = (section.PageSetup.ClientWidth) / 2;
                }
             if (c == 2)
                {
                  Paragraph par = dataRow.Cells[c].AddParagraph();
                  par.AppendText(data[r][c]);
                  dataRow.Cells[c].Width = (section.PageSetup.ClientWidth) / 2;
                 } 
              }
         }

Step 5: Save and review.

document.SaveToFile(@"result.docx", FileFormat.Docx2013);
System.Diagnostics.Process.Start(@"result.docx"); 

Result screenshot:

How to set Vertical Alignment for table in Word via Spire.Doc

Full code:

using Spire.Doc;
using Spire.Doc.Documents;
using Spire.Doc.Fields;
using System;
using System.Drawing;
namespace SetVerticalAlignment
{
    class Program
    {

        static void Main(string[] args)
        {

            Document document = new Document();
            Section section = document.AddSection();

            Table table = section.AddTable(true);
            table.ResetCells(3, 3);

            table.ApplyVerticalMerge(0, 0, 2);

            table.Rows[0].Cells[0].CellFormat.VerticalAlignment = VerticalAlignment.Middle;
            table.Rows[0].Cells[1].CellFormat.VerticalAlignment = VerticalAlignment.Top;
            table.Rows[0].Cells[2].CellFormat.VerticalAlignment = VerticalAlignment.Top;
            table.Rows[1].Cells[1].CellFormat.VerticalAlignment = VerticalAlignment.Middle;
            table.Rows[1].Cells[2].CellFormat.VerticalAlignment = VerticalAlignment.Middle;
            table.Rows[2].Cells[1].CellFormat.VerticalAlignment = VerticalAlignment.Bottom;
            table.Rows[2].Cells[2].CellFormat.VerticalAlignment = VerticalAlignment.Bottom;

            Paragraph paraPic = table.Rows[0].Cells[0].AddParagraph();
            DocPicture pic = paraPic.AppendPicture(Image.FromFile("1.png"));

            String[][] data = {
                       new string[] {"","Spire.Office","Spire.DataExport"},
                       new string[] {"","Spire.Doc","Spire.DocViewer"},
                       new string[] {"","Spire.XLS","Spire.PDF"}
                            };

            for (int r = 0; r < 3; r++)
            {
                TableRow dataRow = table.Rows[r];
                dataRow.Height = 50;
                for (int c = 0; c < 3; c++)
                {
                    if (c == 1)
                    {
                        Paragraph par = dataRow.Cells[c].AddParagraph();
                        par.AppendText(data[r][c]);
                        dataRow.Cells[c].Width = (section.PageSetup.ClientWidth) / 2;
                    }
                    if (c == 2)
                    {
                        Paragraph par = dataRow.Cells[c].AddParagraph();
                        par.AppendText(data[r][c]);
                        dataRow.Cells[c].Width = (section.PageSetup.ClientWidth) / 2;
                    }
                }
            }
            document.SaveToFile(@"result.docx", FileFormat.Docx2013);
            System.Diagnostics.Process.Start(@"result.docx");
        }
    }
}

C#: Set Number Format in Excel

2025-04-02 01:13:00 Written by Koohji

In Excel worksheets, the raw data is often displayed as plain numbers that lack intuitiveness. By setting number format, these numbers can be transformed into a more understandable form. For example, setting sales data in a currency format, i.e., adding a currency symbol and a thousands separator, can make the represented amounts clear at a glance. Formatting market share data into a percentage format can clearly shows the proportions of each part, facilitating quick comparisons and analysis. In this article, you will learn how to set number formats in Excel cells in C# using Spire.XLS for .NET.

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

Symbols in Excel Number Format

In Excel number format codes, several symbols are used to define how numbers are displayed. Below is a detailed explanation of the common symbols:

Symbols Description
0 A required digit placeholder, padded with zeros if necessary.
# An optional digit placeholder. It does not display insignificant zeros.
? An optional digit placeholder. It adds spaces to align decimal points and trailing zeros for fractions.
. Represents the decimal point.
, Serves as a thousands separator.
; Separates different sections of a number format code for positive, negative, zero, and text values.
% Multiplies the number by 100 and adds a percentage sign.
E - + Scientific notation.
Currency ($, €, ¥, etc.) Displays the respective currency symbol.
[Color] Specifies a color for the number display.
Date/Time (yyyy, mmmm, mm, dd, hh, ss, AM/PM) Represent year, full month name, month, day, hour, minute, second, and 12-hour clock markers respectively.

Set Number Format in Excel Cells in C#

Spire.XLS for .NET provides the CellRange.NumberValue property to set the number value of a cell and the CellRange.NumberFormat property to set the number format with format code. The detailed steps are as follows:

  • Create a Workbook instance.
  • Get a specified worksheet through Workbook.Worksheets[] property.
  • Get a specified cell through Worksheet.Range[] property.
  • Add text to specified cells through CellRange.Text property.
  • Add numeric values to specified cells through CellRange.NumberValue property, and then set the number formats through CellRange.NumberFormat property.
  • Save the result file using Workbook.SaveToFile() method.
  • C#
using Spire.Xls;

namespace SetNumberFormat
{
    class Program
    {
        static void Main(string[] args)
        {
            // Create a Workbook instance
            Workbook workbook = new Workbook();

            // Get the first worksheet
            Worksheet sheet = workbook.Worksheets[0];

            // Add text to sepcified cells and set cell styles
            sheet.Range["B3"].Text = "Value";
            sheet.Range["C3"].Text = "Number Format";
            sheet.Range["D3"].Text = "Display";
            sheet.Range["E3"].Text = "Notes";
            sheet.Range["B3:E3"].Style.Font.IsBold = true;
            sheet.Range["B3:E3"].Style.KnownColor = ExcelColors.SkyBlue;

            // Number padded with zeros
            sheet.Range["B4"].Text = "123";
            sheet.Range["C4"].Text = "0000";
            sheet.Range["D4"].NumberValue = 123;
            sheet.Range["D4"].NumberFormat = "0000";
            sheet.Range["E4"].Text = "Number padded with zeros";

            // Two-digit decimal number with thousands separator
            sheet.Range["B5"].Text = "1234.5678";
            sheet.Range["C5"].Text = "#,##0.00";
            sheet.Range["D5"].NumberValue = 1234.5678;
            sheet.Range["D5"].NumberFormat = "#,##0.00";
            sheet.Range["E5"].Text = "Two-digit decimal number with thousands separator";

            // Negative number in red
            sheet.Range["B6"].Text = "-1234.5678";
            sheet.Range["C6"].Text = "0;[Red]-0";
            sheet.Range["D6"].NumberValue = -1234.5678;
            sheet.Range["D6"].NumberFormat = "0;[Red]-0";
            sheet.Range["E6"].Text = "Negative number in red";

            // Percentage with one decimal place
            sheet.Range["B7"].Text = "0.12345";
            sheet.Range["C7"].Text = "0.0%";
            sheet.Range["D7"].NumberValue = 0.12345;
            sheet.Range["D7"].NumberFormat = "0.0%";
            sheet.Range["E7"].Text = "Percentage with one decimal place";

            // Number with text
            sheet.Range["B8"].Text = "1234.5678";
            sheet.Range["C8"].Text = "\"Quantity: \"0";
            sheet.Range["D8"].NumberValue = 1234.5678;
            sheet.Range["D8"].NumberFormat = "\"Quantity: \"0";
            sheet.Range["E8"].Text = "Number with text";

            // Number with currency symbol
            sheet.Range["B9"].Text = "1234.5678";
            sheet.Range["C9"].Text = "$#,##0.00";
            sheet.Range["D9"].NumberValue = 1234.5678;
            sheet.Range["D9"].NumberFormat = "$#,##0.00";
            sheet.Range["E9"].Text = "Number with currency symbol";

            // Scientific notation format
            sheet.Range["B10"].Text = "1234.5678";
            sheet.Range["C10"].Text = "0.00E+00";
            sheet.Range["D10"].NumberValue = 1234.5678;
            sheet.Range["D10"].NumberFormat = "0.00E+00";
            sheet.Range["E10"].Text = "Scientific notation";

            // Fraction
            sheet.Range["B11"].Text = "0.5";
            sheet.Range["C11"].Text = "# ?/?";
            sheet.Range["D11"].NumberValue = 0.5;
            sheet.Range["D11"].NumberFormat = "# ?/?";
            sheet.Range["E11"].Text = "Fraction";

            // Date
            sheet.Range["B12"].Text = "45930";
            sheet.Range["C12"].Text = "yyyy-MM-dd";
            sheet.Range["D12"].NumberValue = 45930;
            sheet.Range["D12"].NumberFormat = "yyyy-MM-dd";
            sheet.Range["E12"].Text = "Date";

            // Time
            sheet.Range["B13"].Text = "0.5";
            sheet.Range["C13"].Text = "h:mm:ss AM/PM";
            sheet.Range["D13"].NumberValue = 0.5;
            sheet.Range["D13"].NumberFormat = "h:mm:ss AM/PM";
            sheet.Range["E13"].Text = "Time";

            // Set cell styles for the used range
            sheet.AllocatedRange.Style.Font.FontName = "Calibri";
            sheet.AllocatedRange.Style.Font.Size = 11;
            sheet.AllocatedRange.Style.HorizontalAlignment = HorizontalAlignType.Left;

            // Autofit column width
            sheet.AutoFitColumn(2);
            sheet.AutoFitColumn(3);
            sheet.AutoFitColumn(4);
            sheet.AutoFitColumn(5);

            // Save the result file
            workbook.SaveToFile("ExcelNumberFormat.xlsx", ExcelVersion.Version2016);
        }
    }
}

Custom number formats in an Excel worksheet

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.

How to add line numbers in C#

2015-06-10 07:07:24 Written by Administrator

Line numbers is used to display the number of lines counted automatically by Word next to each line of text. It's very useful when we need to refer to specific lines in a document such as contract or legal papers. Line numbers function in word allows us to set the start value, the number interval, the distance from text and the numbering mode of line numbers. Using Spire.Doc, we could achieve all of features mentioned above. This article is going to introduce how to add line numbers in C# using Spire.Doc.

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 which only has text.

Document document = new Document();
document.LoadFromFile("T.docx");

Step 2: Set the start value of the line numbers.

document.Sections[0].PageSetup.LineNumberingStartValue = 1;

Step 3: Set the interval between displayed numbers.

document.Sections[0].PageSetup.LineNumberingStep = 6;

Step 4: Set the distance between line numbers and text.

document.Sections[0].PageSetup.LineNumberingDistanceFromText = 40f;

Step 5: Set the numbering mode of line numbers. Here we have four choices: None, Continuous, RestartPage and RestartSection.

document.Sections[0].PageSetup.LineNumberingRestartMode = LineNumberingRestartMode.Continuous;

Step 6: Save the document and launch to see effects.

 document.SaveToFile("result.docx",FileFormat.Docx2013);
System.Diagnostics.Process.Start("result.docx");

Effects:

Single Page:

How to add line numbers in C#

Continuous Page:

How to add line numbers in C#

Full Codes:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Spire.Doc;

namespace How_to_add_line_numbering
{
    class Program
    {
        static void Main(string[] args)
        {
            Document document = new Document();
            document.LoadFromFile("T.docx");

            document.Sections[0].PageSetup.LineNumberingStartValue = 1;
            document.Sections[0].PageSetup.LineNumberingStep = 6;
            document.Sections[0].PageSetup.LineNumberingDistanceFromText = 40f;
            document.Sections[0].PageSetup.LineNumberingRestartMode = LineNumberingRestartMode.Continuous;

             document.SaveToFile("result.docx",FileFormat.Docx2013);
            System.Diagnostics.Process.Start("result.docx");
        }
    }
}

We have introduced how to add shapes to the slides by Spire.Presentaion. Spire.Presentation supports to work with lots of shapes, such as triangle, rectangle, ellipse, star, line and so on. This time we will show you how to add line arrow to the slides in C#.

Here comes to the code snippet.

Step 1: Create a PPT document.

Presentation presentation = new Presentation();

Step 2: Add a line to the slides and set its color to red.

IAutoShape shape = presentation.Slides[0].Shapes.AppendShape(ShapeType.Line, new RectangleF(50, 100, 100, 100));
shape.ShapeStyle.LineColor.Color = Color.Red;

Step 3: Set the line arrow by using LineEndType.

shape.Line.LineEndType = LineEndType.StealthArrow;

Step 4: Save and launch to view the PPTX document.

presentation.SaveToFile("shape.pptx", FileFormat.Pptx2010);
System.Diagnostics.Process.Start("shape.pptx");

Effective screenshots:

How to add line arrow to presentation slides in C#

Full codes:

using Spire.Presentation;
using System.Drawing;

namespace AddLineArrow
{

    class Program
    {

        static void Main(string[] args)
        {
            Presentation presentation = new Presentation();
            IAutoShape shape = presentation.Slides[0].Shapes.AppendShape(ShapeType.Line, new RectangleF(50, 100, 100, 100));
            shape.ShapeStyle.LineColor.Color = Color.Red;
            shape.Line.LineEndType = LineEndType.StealthArrow;

            shape = presentation.Slides[0].Shapes.AppendShape(ShapeType.Line, new RectangleF(300, 250, 150, 150));
            shape.Rotation = -45;
            shape.Line.LineEndType = LineEndType.TriangleArrowHead;

            presentation.SaveToFile("shape.pptx", FileFormat.Pptx2010);
            System.Diagnostics.Process.Start("shape.pptx");


        }
    }
}

Content controls provide a way for you to design documents. When you add a content control to a document, the control is identified by a border, a title, and temporary text that can provide instructions to the user, and can prevent users from editing or deleting protected sections of a document.

Bind parts of a document or template to data. You can bind content controls to database fields, managed objects in the .NET Framework, XML elements that are stored in the document, and other data sources.

This article illustrates how to get all controls and their properties including alias, id and tag via Spire.Doc with new method and replace the Barcode with another image.

Refer this article to check old method: Get alias, tag and id of content controls in a Word document in C#

The following is test file new.docx.

New method get alias, tag and id of content controls in a Word document in C#

Here are the steps:

Step 1: Create a new Word document and load the test file.

Document doc = new Document(@"new.docx");

Step 2: Create a list StructureDocument to store tags. Here, each content control will be identified by tag.

public class StructureTags
        {
            List m_structureDocumnt;
            public List StructureDocument
            {
                get
                {
                    if (m_structureDocumnt == null)
                        m_structureDocumnt = new List();

                    return m_structureDocumnt;
                }
            }

        }

Step 3: Use foreach sentence to get all tags in the Word document.

foreach (Section section in doc.Sections)
            {
                foreach (Body body in section.ChildObjects)
                {
                    ModifyBody(body);
                }
            }

Step 4: Show the properties of all controls.

List tagInlines = structureTags.StructureDocument;
            Console.WriteLine("Part1");
            for (int i = 0; i < tagInlines.Count; i++)
            {
                string alias = tagInlines[i].SDTProperties.Alias;   // Can be null or empty
                decimal id = tagInlines[i].SDTProperties.Id;
                string tag = tagInlines[i].SDTProperties.Tag;
                string STDType = tagInlines[i].SDTProperties.SDTType.ToString();

                Console.WriteLine("{0,20},{1,15},{2, 10} - {3}", alias, id, STDType, tag);
                Console.ReadKey();
             }

Step 5: Replace image inside of Picture Content Control.

doc.SaveToFile("replace1.docx", FileFormat.Docx2013);
System.Diagnostics.Process.Start("replace1.docx");

Result Screenshot:

New method get alias, tag and id of content controls in a Word document in C#

New method get alias, tag and id of content controls in a Word document in C#

Full code:

using Spire.Doc;
using Spire.Doc.Documents;
using Spire.Doc.Fields;
using System;
using System.Drawing;
namespace GetAlias
{

    class Program
    {
     
           static StructureTags structureTags = new StructureTags();
        static void Main(string[] args)
        {
            Document doc = new Document(@"new.docx");

            foreach (Section section in doc.Sections)
            {
                foreach (Body body in section.ChildObjects)
                {
                    ModifyBody(body);
                }
            }

            List tagInlines = structureTags.StructureDocument;
            Console.WriteLine("Part1");
            for (int i = 0; i < tagInlines.Count; i++)
            {
                string alias = tagInlines[i].SDTProperties.Alias;
                decimal id = tagInlines[i].SDTProperties.Id;
                string tag = tagInlines[i].SDTProperties.Tag;
                string STDType = tagInlines[i].SDTProperties.SDTType.ToString();

                Console.WriteLine("{0,20},{1,15},{2, 10} - {3}", alias, id, STDType, tag);
                Console.ReadKey();

                if (tagInlines[i].SDTProperties.SDTType == SdtType.Picture)
                {
                    DocPicture picture = tagInlines[i].ChildObjects.FirstItem as DocPicture;
                    if (picture == null)
                    {
                        picture = new DocPicture(doc);
                        picture.LoadImage(Image.FromFile(@"cat.jpg"));
                        tagInlines[i].ChildObjects.Add(picture);
                    }
                    else
                    {
                        picture.LoadImage(Image.FromFile(@"cat.jpg"));
                    }
                }
            }

            doc.SaveToFile("replace1.docx", FileFormat.Docx2013);
            System.Diagnostics.Process.Start("replace1.docx");
        }

        static void ModifyBody(Body body)
        {
            if (body == null)
                return;

            foreach (DocumentObject docObj in body.ChildObjects)
            {
                if (docObj is StructureDocumentTag)
                {
                    structureTags.StructureDocument.Add(docObj as StructureDocumentTag);

                }
                else if (docObj is Table)
                {
                    ModifyTable(docObj as Table);
                }
                else if (docObj is Paragraph)
                {
                    ModifyParagraph(docObj as Paragraph);
                }
            }
        }

        static void ModifyTable(Table table)
        {
            if (table == null)
                return;

            foreach (TableRow row in table.Rows)
            {
                foreach (TableCell cell in row.Cells)
                {
                    if (cell is StructureDocumentTagCell)
                    {
                        structureTags.StructureDocument.Add(cell as StructureDocumentTagCell);
                    }
                    else
                    {
                        ModifyBody(cell);
                    }
                }
            }
        }

        static void ModifyParagraph(Paragraph para)
        {
            if (para == null)
                return;

            foreach (DocumentObject docObj in para.ChildObjects)
            {
                if (docObj is StructureDocumentTagInline)
                {

                    structureTags.StructureDocument.Add(docObj as StructureDocumentTagInline);
                }
            }
        }

        public class StructureTags
        {
            List m_structureDocumnt;
            public List StructureDocument
            {
                get
                {
                    if (m_structureDocumnt == null)
                        m_structureDocumnt = new List();

                    return m_structureDocumnt;
                }
            }
        }

        }
    }
page 50