December 2021 – Office Dev Blog

C#/VB.NET: Convert Excel XLS or XLSX to XML and Vice Versa

XML files are extensible markup language files, which are encoded in plain text and can be opened by any text editor. Sometimes you may need to do conversions between Excel and XML. In this article, I will demonstrate how to Convert Excel XLS or XLSX to XML and Convert XML to Excel XLS or XLSX in C# and VB.NET using Spire.XLS for .NET API.

Installation

The DLL files of Spire.XLS for .NET API can be either downloaded from the official website or installed via NuGet by selecting Tools > NuGet Package Manager > Package Manager Console and then adding the following code:

PM> Install-Package Spire.XLS

Convert Excel XLS or XLSX to XML in C# and VB.NET

The SaveAsXML method of Workbook class is used to convert Excel XLS/XLSX file to XML.

The following are the steps to achieve the conversion:

Create an instance of Workbook class.
Load an Excel file using Workbook.LoadFromFile() method (you can also load from stream using Workbook.LoadFromStream() method if you want.).
Call Workbook.SaveAsXml() method to save the Excel file to XML file format.

using Spire.Xls;

namespace ConvertExcelToXml
{
    class Program
    {
        static void Main(string[] args)
        {
            //Create an instance of Workbook class
            Workbook workbook = new Workbook();
            //Load an Excel XLSX file
            workbook.LoadFromFile("Sample.xlsx");
            //Or load an Excel XLS file
            //workbook.LoadFromFile("Sample.xls");
            
            //Save as XML file format
            workbook.SaveAsXml("ToXML.xml");
        }
    }
}

VB.NET

Imports Spire.Xls

Namespace ConvertExcelToXml
    Friend Class Program
        Private Shared Sub Main(ByVal args As String())
            'Create an instance of Workbook class
            Dim workbook As Workbook = New Workbook()
            'Load an Excel XLSX file
            workbook.LoadFromFile("Sample.xlsx")
            'Or load an Excel XLS file
            'workbook.LoadFromFile("Sample.xls");

            'Save as XML file format
            workbook.SaveAsXml("ToXML.xml")
        End Sub
    End Class
End Namespace

Convert XML to Excel XLS or XLSX in C# and VB.NET

The following are the steps to convert an XML file to Excel:

Create an instance of Workbook class.
Load an XML file using Workbook.LoadFromXml() file.
Call Workbook.SaveToFile() method to save the XML file as Excel XLS/XLSX format.

using Spire.Xls;

namespace ConvertXmlToExcel
{
    class Program
    {
        static void Main(string[] args)
        {
            //Create an instance of Workbook class
            Workbook workbook = new Workbook();
            //Load an XML file
            workbook.LoadFromXml("ToXML.xml");

            //Save as XLSX file format
            workbook.SaveToFile("ToExcel.xlsx", ExcelVersion.Version2016);
            //Save as XLS file format
            //workbook.SaveToFile("ToExcel.xls", ExcelVersion.Version97to2003);
        }
    }
}

VB.NET

Imports Spire.Xls

Namespace ConvertXmlToExcel
    Friend Class Program
        Private Shared Sub Main(ByVal args As String())
            'Create an instance of Workbook class
            Dim workbook As Workbook = New Workbook()
            'Load an XML file
            workbook.LoadFromXml("ToXML.xml")

            'Save as XLSX file format
            workbook.SaveToFile("ToExcel.xlsx", ExcelVersion.Version2016)
            'Save as XLS file format
            'workbook.SaveToFile("ToExcel.xls", ExcelVersion.Version97to2003);
        End Sub
    End Class
End Namespace

C# Lock Excel Cells to Prevent Editing

If some cells/rows/columns in an Excel worksheet contain data you do not want readers to edit but only view, you can lock them to prevent editing. In this article, I will introduce how to lock specific cells, rows and columns in Excel in C# using Free Spire.XLS for .NET library.

Installation

You can either download Free Spire.XLS for .NET API from the official website or install it via NuGet by selecting Tools > NuGet Package Manager > Package Manager Console and then adding the following code:

PM> Install-Package FreeSpire.XLS

Lock Specific Cells

The following are the main steps to lock specific cells in an Excel worksheet:

Create an instance of Workbook class, and load an Excel file using Workbook.LoadFromFile() method.
Access the desired worksheet by its index.
Access the used range in the worksheet using Worksheet.Range property and Unlock the range by setting the CellRange.Style.Locked property as false.
Access the range of cells that you want to lock and lock them by setting the CellRange.Style.Locked property as true.
Protect the worksheet with password using Worksheet.Protect() method.
Save the result file using Workbook.SaveToFile() method.

using Spire.Xls;

namespace LockCells
{
    class Program
    {
        static void Main(string[] args)
        {
            //Create a Workbook instance
            Workbook workbook = new Workbook();
            //Load an Excel file
            workbook.LoadFromFile("Input.xlsx");

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

            //By default, all the cells in an Excel worksheet are locked, so we need to unlock them first
            CellRange usedRange = sheet.Range;
            usedRange.Style.Locked = false;

            //Lock the cell range “A1:A3”
            CellRange cells = sheet.Range["A1:C3"]; 
            cells.Style.Locked = true;

            //Protect the worksheet with password
            sheet.Protect("123456", SheetProtectionType.All);

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

When editing the cell A1, I got the following pop-up message:

Lock cells in Excel to prevent editing using c#

Lock a specific row and column

The following are the main steps to lock a specific row or column in an Excel worksheet:

Create an instance of Workbook class, and load an Excel file using Workbook.LoadFromFile() method.
Access the desired worksheet by its index.
Access the used range in the worksheet using Worksheet.Range property and Unlock the range by setting the CellRange.Style.Locked property as false.
Access the specific row or column you want to lock and lock it by setting the CellRange.Style.Locked property as true.
Protect the worksheet with password using Worksheet.Protect() method.
Save the result file using Workbook.SaveToFile() method.

using Spire.Xls;

namespace LockRowAndColumn
{
    class Program
    {
        static void Main(string[] args)
        {

            //Create a Workbook instance
            Workbook workbook = new Workbook();
            //Load an Excel file
            workbook.LoadFromFile("Input.xlsx");

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

            //By default, all the cells in an Excel worksheet are locked, so we need to unlock them first
            CellRange usedRange = sheet.Range;
            usedRange.Style.Locked = false;

            //Lock the first
            CellRange row = sheet.Rows[0];
            row.Style.Locked = true;
            //Lock the third column
            CellRange column = sheet.Columns[2];
            column.Style.Locked = true;

            //Protect the worksheet with password
            sheet.Protect("123456", SheetProtectionType.All);

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

When editing the cell C6 in the third column, I got the following pop-up message:

Lock a row or a column in Excel to prevent editing using c#

Insert Section Breaks and Page Breaks into Word Documents in C#

Section breaks and page breaks are two kinds of document breaks in Microsoft Word. Section breaks allow you to divide a long document into sections and format each section differently. Page breaks can help you end a page at a desired place and start a new page immediately instead of hitting the Enter key a bunch of times. In this article, I will introduce how to insert section breaks and page breaks in a Word document using C#.

Installation

In order to insert section breaks and page breaks, I will be using Free Spire.Doc for .NET which is a free and multifunctional API for creating, manipulating, converting and printing Word documents.

You can either download Free Spire.Doc for .NET API from the official website or install it via NuGet by selecting Tools > NuGet Package Manager > Package Manager Console and then adding the following code:

PM> Install-Package FreeSpire.Doc

Insert Section Breaks

The following are the steps to insert a section break into a Word document:

Create an instance of Document class.
Load a Word document using Document.LoadFromFile() method.
Get the desired paragraph by its index.
Add a section break to the paragraph using Paragraph.InsertSectionBreak() method.
Save the result document using Document.SaveToFile() method.

using Spire.Doc;
using Spire.Doc.Documents;

namespace SectionBreaks
{
    class Program
    {
        static void Main(string[] args)
        {
            //Create a Document instance
            Document document = new Document();
            //Load a Word document
            document.LoadFromFile("Sample.docx");
 
            //Get the second paragraph in the first section
            Paragraph paragraph = document.Sections[0].Paragraphs[1];
            //Add a section break to the paragraph and start the new section on a new page
            paragraph.InsertSectionBreak(SectionBreakType.NewPage);

            //Save the result document
            document.SaveToFile("InsertSectionBreak.docx", FileFormat.Docx2013);
        }
    }
}

Insert Page Breaks

The following are the steps to insert a page break into a Word document:

Create an instance of Document class.
Load a Word document using Document.LoadFromFile() method.
Get the desired paragraph by its index.
Append a page break to the paragraph using Paragraph.AppendBreak(BreakType.PageBreak) method.
Save the result document using Document.SaveToFile() method.

using Spire.Doc;
using Spire.Doc.Documents;

namespace PageBreaks
{
    class Program
    {
        static void Main(string[] args)
        {
            //Create a Document instance
            Document document = new Document();
            //Load a Word document
            document.LoadFromFile("Sample.docx");

            //Get the first section
            Section section = document.Sections[0];
            //Get the second paragraph in the section
            Paragraph paragraph = section.Paragraphs[1];

            //Append a page break to the paragraph
            paragraph.AppendBreak(BreakType.PageBreak);

            //Save the result document
            document.SaveToFile("InsertPageBreak.docx", FileFormat.Docx2013);
        }
    }
}

Hide or Unhide Slides in PowerPoint in Java

In Microsoft PowerPoint, you can hide a slide so that it won’t appear during the Slide Show. You can also unhide it if you want to show them in future presentations. In this article, I will demonstrate how to hide or unhide slides in PowerPoint programmatically in Java using Free Spire.Presentation for Java API.

Add Dependencies

You can either download the jar of Free Spire.Presentation for Java from the official website or install it from maven by adding the following configurations to your maven-based project’s pom.xml file.

<repositories>    
    <repository>    
        <id>com.e-iceblue</id>    
        <name>e-iceblue</name>    
        <url>http://repo.e-iceblue.com/nexus/content/groups/public/</url>    
    </repository>    
</repositories>    
<dependencies>    
    <dependency>    
        <groupId> e-iceblue </groupId>    
        <artifactId>spire.presentation.free</artifactId>    
        <version>3.9.0</version>    
    </dependency>    
</dependencies>

Hide or Unhide Slides

You can use the setHidden method of ISlide class to hide or unhide a slide. The method takes a Boolean parameter indicates whether to hide or unhide a slide during the presentation.

The following are the steps to hide or unhide a slide:

Create an instance of Presentation class.
Load a PowerPoint document using Presentation.loadFromFile() method.
Access the slide that you want to hide or unhide by its index using Presentation.getSlides().get() method.
Hide or unhide the slide using ISlide.setHidden() method.
Save the result document using Presentation.saveToFile() method.

import com.spire.presentation.FileFormat;
import com.spire.presentation.ISlide;
import com.spire.presentation.Presentation;

public class HideOrUnhideSlides {
    public static void main(String []args) throws Exception {
        //Create a Presentation instance
        Presentation ppt = new Presentation();
        //Load a PowerPoint document
        ppt.loadFromFile("Sample.pptx");

        //Get the second slide 
        ISlide slide = ppt.getSlides().get(1);
        //Hide the slide
        slide.setHidden(true);
        //Unhide the slide
        //slide.setHidden(false);

        //Save the result document
        ppt.saveToFile("Result.pptx", FileFormat.PPTX_2013);
    }
}

The following is the output document after hiding the second presentation:

Hide a slide in PowerPoint Presentation in Java

Group or Ungroup Rows and Columns in Excel in C#

When creating an Excel report, you might need to group rows and columns in order to read, analyze and summarize data with ease. This article will demonstrate how to group or ungroup rows and columns in an Excel file in C#.

Group Rows and Columns
Add Subtotals for a Particular Range
Ungroup Rows and Columns

Installation

In order to group or ungroup Excel rows and columns, I will be using Free Spire.XLS for .NET which is a free and multifunctional API for creating, manipulating, converting and printing Excel files.

PM> Install-Package FreeSpire.XLS

Group Rows and Columns

You can group rows and columns by using GroupByRows and GroupByColumns methods of the Worksheet class. Both of the methods accept the following parameters:

Int32: the index of the first row/column in the group.
Int 32: the index of the last row/column in the group.
Boolean: indicates whether to hide rows/columns after grouping.

//Create a Workbook instance
Workbook workbook = new Workbook();
//Load an Excel file
workbook.LoadFromFile(@"Report.xlsx");

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

//Group specific rows
sheet.GroupByRows(2, 6, false);
//Group specific columns
sheet.GroupByColumns(1, 3, false);

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

Add Subtotals for a Particular Range

You can insert subtotals and totals to quickly calculate rows of related data by using the Subtotal method of the Worksheet class. The method accepts the following parameters:

IXLSRange: the range which contains data you wish to subtotal.
Int32: the index of the column by which you wish to group your data.
Int32[]: the specified list of columns on which the subtotals should be calculated.
SubtotalTypes: the function that you want to use to calculate the subtotals.
Boolean: Indicates whether to replace existing subtotals.
Boolean: Indicates whether to insert page breaks between groups.
Boolean: Indicates whether to display the summary results below each set of data.

//Create a Workbook instance
Workbook workbook = new Workbook();
//Load an Excel file
workbook.LoadFromFile(@"Report.xlsx");

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

//Specify the range that you want to add subtotals to
CellRange range = sheet.Range["A2:C11"];

//Add subtotal to the range
sheet.Subtotal(range, 0, new int[] { 2 }, SubtotalTypes.Sum, true, false, true);

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

Add subtotals to specific cell range in Excel in C#

Ungroup Rows and Columns

It is possible to ungroup rows and columns by using the UngroupByRows and UngroupByColumns method of the Worksheet class. Both of the methods accept the following parameters:

Int32: the index of the first row/column in the group.
Int 32: the index of the last row/column in the group.

//Create a Workbook instance
Workbook workbook = new Workbook();
//Load an Excel file
workbook.LoadFromFile(@"Report.xlsx");

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

//Ungroup specific rows
sheet.UngroupByRows(2, 6);
//Ungroup specific columns
sheet.UngroupByColumns(1, 3);

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

Add Math Equations to PowerPoint in Java

Microsoft PowerPoint provides the “Equation” option which allows you to insert math equations to your PowerPoint document manually. In this article, I am going to demonstrate how to insert math equations to PowerPoint shape programmatically in Java using Spire.Presentation for Java library.

Add Dependencies

You can either download the jar of Spire.Presentation for Java from the official website or install it from maven by adding the following configurations to your maven-based project’s pom.xml file.

<repositories>    
    <repository>    
        <id>com.e-iceblue</id>    
        <name>e-iceblue</name>    
        <url>http://repo.e-iceblue.com/nexus/content/groups/public/</url>    
    </repository>    
</repositories>    
<dependencies>    
    <dependency>    
        <groupId> e-iceblue </groupId>    
        <artifactId>spire.presentation </artifactId>    
        <version>4.11.7</version>    
    </dependency>    
</dependencies>

Add Math Equations to PowerPoint

Spire.Presentation for Java supports creating and adding mathematical equations to PowerPoint shape using LaTeX code. You can follow the following steps to achieve this function:

Create a Presentation instance.
Get the reference of a slide by using its index.
Use ShapeList.appendShape() method to add a shape to the first slide.
Use ParagraphCollection.addParagraphFromLatexMathCode() method to create a mathematical equation from LaTeX code and add it to the shape.
Save the result document using Presentation.saveToFile() method.

import com.spire.presentation.*;
import com.spire.presentation.drawing.FillFormatType;

import java.awt.geom.Rectangle2D;

public class AddMathEquations {
    public static void main(String []args) throws Exception {

        //The LaTeX codes
        String latexCode1 = "x^{2} + \\sqrt{x^{2}+1}=2";
        String latexCode2 = "F(x) &= \\int^a_b \\frac{1}{3}x^3";
        String latexCode3 = "\\alpha + \\beta  \\geq \\gamma";
        String latexCode4 = "\\overrightarrow{abc}";

        //Create a Presentation instance
        Presentation ppt = new Presentation();

        //Get the first slide by using its index
        ISlide slide = ppt.getSlides().get(0);

        //Add a shape to the slide
        IAutoShape shape = slide.getShapes().appendShape(ShapeType.RECTANGLE, new Rectangle2D.Float(30, 100, 200, 30));
        shape.getTextFrame().getParagraphs().clear();
        //Add a math equation to the shape using the LaTeX code
        ParagraphEx para = shape.getTextFrame().getParagraphs().addParagraphFromLatexMathCode(latexCode1);

        //Add a shape to the slide
        shape = slide.getShapes().appendShape(ShapeType.RECTANGLE, new Rectangle2D.Float(240, 100, 200, 40));
        shape.getTextFrame().getParagraphs().clear();
        //Add a math equation to the shape using the LaTeX code
        para = shape.getTextFrame().getParagraphs().addParagraphFromLatexMathCode(latexCode2);

        //Add a shape to the slide
        shape = slide.getShapes().appendShape(ShapeType.RECTANGLE, new Rectangle2D.Float(30, 180, 200, 40));
        shape.getTextFrame().getParagraphs().clear();
        //Add a math equation to the shape using the LaTeX code
        para = shape.getTextFrame().getParagraphs().addParagraphFromLatexMathCode(latexCode3);

        //Add a shape to the slide
        shape = slide.getShapes().appendShape(ShapeType.RECTANGLE, new Rectangle2D.Float(240, 180, 200, 40));
        shape.getTextFrame().getParagraphs().clear();
        //Add a math equation to the shape using the LaTeX code
        para = shape.getTextFrame().getParagraphs().addParagraphFromLatexMathCode(latexCode4);

        for (IShape iShape : (Iterable<IShape>)slide.getShapes())
        {
            iShape.getFill().setFillType(FillFormatType.NONE);
            iShape.getLine().setFillType(FillFormatType.NONE);
        }

        //Save the result document
        ppt.saveToFile("AddMathEquations.pptx", FileFormat.PPTX_2013);
        ppt.dispose();
    }
}