At some point, programmers may need to determine if an Excel file contains VBA macros. This article is going to show you how to programmatically determine if an Excel file contains VBA macros in C# and VB.NET using Spire.XLS.

Detail steps:

Step 1: Instantiate a Workbook object and load the Excel file.

Workbook workbook = new Workbook();
workbook.LoadFromFile("Macro.xlsm");

Step 2: Determine if the Excel file contains VBA macros.

bool hasMacros = false;
hasMacros = workbook.HasMacros;
if (hasMacros)
{
    Console.WriteLine("The file contains VBA macros");
}

else
{
    Console.WriteLine("The file doesn't contain VBA macros");
}

Screenshot:

Determine if an Excel File Contains VBA Macros in C#, VB.NET

Full code:

[C#]
using System;
using Spire.Xls;

namespace Determine_if_Excel_file_contains_macros
{
    class Program
    {
        static void Main(string[] args)
        {
            //Instantiate a Workbook object
            Workbook workbook = new Workbook();
            //Load the Excel file
            workbook.LoadFromFile("Macro.xlsm");

            bool hasMacros = false;

            //Determine if the Excel file contains VBA macros
            hasMacros = workbook.HasMacros;

            if (hasMacros)
            {
                Console.WriteLine("The file contains VBA macros");
            }

            else
            {
                Console.WriteLine("The file doesn't contain VBA macros");
            }

            Console.ReadKey();
        }
    }
}
[VB.NET]
Imports System
Imports Spire.Xls

Namespace Determine_if_Excel_file_contains_macros
    Class Program
        Private Shared Sub Main(ByVal args As String())
            Dim workbook As Workbook = New Workbook()
            workbook.LoadFromFile("Macro.xlsm")
            Dim hasMacros As Boolean = False
            hasMacros = workbook.HasMacros

            If hasMacros Then
                Console.WriteLine("The file contains VBA macros")
            Else
                Console.WriteLine("The file doesn't contain VBA macros")
            End If

            Console.ReadKey()
        End Sub
    End Class
End Namespace

In this article, you will learn how to add footer to an existing PDF document using Spire.PDF. For a page in a ready-made PDF document, the coordinate system follows the following rules.

  • The origin of the PDF coordinate system (0, 0) represents the top-left corner of the PDF page.
  • The x-axis extends to the right and the y-axis extends downward.

Before you draw text or page number at the specified coordinates in the empty space, you must measure the margins of the page yourself to prevent the footer from overlapping the body.

Step 1: Load a sample PDF document.

PdfDocument doc = new PdfDocument();
doc.LoadFromFile("SalesReport.pdf");

Step 2: Get the coordinates that you want to start adding footer.

SizeF pageSize = doc.Pages[0].Size;
float x = 90;
float y = pageSize.Height - 72;

Step 3: Add a line, some text, page number and page count to the footer space.

for (int i = 0; i < doc.Pages.Count; i++)
{
    //draw line at bottom
    PdfPen pen = new PdfPen(PdfBrushes.Gray, 0.5f);
    doc.Pages[i].Canvas.DrawLine(pen, x, y, pageSize.Width - x, y);

    //draw text at bottom
    PdfTrueTypeFont font = new PdfTrueTypeFont(new Font("Calibri", 14f));
    PdfStringFormat format = new PdfStringFormat(PdfTextAlignment.Left);
    String footerText = "E-iceblue Technology Co., Ltd.\nTel:86-028-81705109\nWebsite:http://www.e-iceblue.com";
    doc.Pages[i].Canvas.DrawString(footerText, font, PdfBrushes.RoyalBlue, x, y + 2, format);

    //draw page number and page count at bottom
    PdfPageNumberField number = new PdfPageNumberField();
    PdfPageCountField count = new PdfPageCountField();
    PdfCompositeField compositeField = new PdfCompositeField(font, PdfBrushes.RoyalBlue, "Page {0} of {1}", number, count);
    compositeField.StringFormat = new PdfStringFormat(PdfTextAlignment.Right, PdfVerticalAlignment.Top);
    SizeF size = font.MeasureString(compositeField.Text);
    compositeField.Bounds = new RectangleF(pageSize.Width - x, y + 2, size.Width, size.Height);
    compositeField.Draw(doc.Pages[i].Canvas);
}

Step 4: Save to file.

doc.SaveToFile("output.pdf");

Output:

Add Footer to Existing PDF in C#, VB.NET

Full Code:

[C#]
PdfDocument doc = new PdfDocument();
doc.LoadFromFile("SalesReport.pdf");

SizeF pageSize = doc.Pages[0].Size;
float x = 90;
float y = pageSize.Height - 72;

for (int i = 0; i < doc.Pages.Count; i++)
{
    //draw line at bottom
    PdfPen pen = new PdfPen(PdfBrushes.Gray, 0.5f);
    doc.Pages[i].Canvas.DrawLine(pen, x, y, pageSize.Width - x, y);

    //draw text at bottom
    PdfTrueTypeFont font = new PdfTrueTypeFont(new Font("Calibri", 14f));
    PdfStringFormat format = new PdfStringFormat(PdfTextAlignment.Left);
    String footerText = "E-iceblue Technology Co., Ltd.\nTel:86-028-81705109\nWebsite:http://www.e-iceblue.com";
    doc.Pages[i].Canvas.DrawString(footerText, font, PdfBrushes.RoyalBlue, x, y + 2, format);

    //draw page number and page count at bottom
    PdfPageNumberField number = new PdfPageNumberField();
    PdfPageCountField count = new PdfPageCountField();
    PdfCompositeField compositeField = new PdfCompositeField(font, PdfBrushes.RoyalBlue, "Page {0} of {1}", number, count);
    compositeField.StringFormat = new PdfStringFormat(PdfTextAlignment.Right, PdfVerticalAlignment.Top);
    SizeF size = font.MeasureString(compositeField.Text);
    compositeField.Bounds = new RectangleF(pageSize.Width - x, y + 2, size.Width, size.Height);
    compositeField.Draw(doc.Pages[i].Canvas);
}

doc.SaveToFile("output.pdf");
[VB.NET]
Dim doc As PdfDocument = New PdfDocument()
doc.LoadFromFile("SalesReport.pdf")
Dim pageSize As SizeF = doc.Pages(0).Size
Dim x As Single = 90
Dim y As Single = pageSize.Height - 72

For i As Integer = 0 To doc.Pages.Count - 1
    Dim pen As PdfPen = New PdfPen(PdfBrushes.Gray, 0.5F)
    doc.Pages(i).Canvas.DrawLine(pen, x, y, pageSize.Width - x, y)
    Dim font As PdfTrueTypeFont = New PdfTrueTypeFont(New Font("Calibri", 14F))
    Dim format As PdfStringFormat = New PdfStringFormat(PdfTextAlignment.Left)
    Dim footerText As String = "E-iceblue Technology Co., Ltd." & vbLf & "Tel:86-028-81705109" & vbLf & "Website:http://www.e-iceblue.com"
    doc.Pages(i).Canvas.DrawString(footerText, font, PdfBrushes.RoyalBlue, x, y + 2, format)
    Dim number As PdfPageNumberField = New PdfPageNumberField()
    Dim count As PdfPageCountField = New PdfPageCountField()
    Dim compositeField As PdfCompositeField = New PdfCompositeField(font, PdfBrushes.RoyalBlue, "Page {0} of {1}", number, count)
    compositeField.StringFormat = New PdfStringFormat(PdfTextAlignment.Right, PdfVerticalAlignment.Top)
    Dim size As SizeF = font.MeasureString(compositeField.Text)
    compositeField.Bounds = New RectangleF(pageSize.Width - x, y + 2, size.Width, size.Height)
    compositeField.Draw(doc.Pages(i).Canvas)
Next

doc.SaveToFile("output.pdf")
Wednesday, 30 May 2018 07:57

Hide or Unhide Shape in Excel in C#

Spire.XLS supports to hide or unhide certain shapes in Excel worksheet through IShape.Visible property. This article demonstrates the detail steps to hide or unhide a shape using Spire.XLS and C#.

Below is the screenshot of the example Excel file:

Hide or Unhide Shape in Excel in C#

Detail steps:

Step 1: Instantiate a Workbook object and load the Excel file.

Workbook workbook = new Workbook();
workbook.LoadFromFile("Input.xlsx");

Step 2: Get the first worksheet.

Worksheet sheet = workbook.Worksheets[0];

Step 3: Hide the second shape in the worksheet.

//Hide the second shape in the worksheet
sheet.PrstGeomShapes[1].Visible = false;

//Show the second shape in the worksheet
//sheet.PrstGeomShapes[1].Visible = true;

Step 4: Save the file.

workbook.SaveToFile("HideShape.xlsx", ExcelVersion.Version2013);

Output:

Hide or Unhide Shape in Excel in C#

Full code:

//Instantiate a Workbook object
Workbook workbook = new Workbook();
//Load the Excel file
workbook.LoadFromFile("Input.xlsx");

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

//Hide the second shape in the worksheet
sheet.PrstGeomShapes[1].Visible = false;

//Show the second shape in the worksheet
//sheet.PrstGeomShapes[1].Visible = true;
                        
//Save the file
workbook.SaveToFile("HideShape.xlsx", ExcelVersion.Version2013);

We have demonstrated how to use Spire.Presentation to add text watermark and image watermark to the presentation slides. This article will show how to remove text and image watermarks in presentation slides in C#.

Firstly, view the sample document contains the text and image watermark.

Remove text and image watermarks in presentation slides

Step 1: Create a presentation document and load the document from the file

Presentation ppt = new Presentation();
ppt.LoadFromFile("Sample.pptx", FileFormat.Pptx2013);

Step 2: Remove the text and image watermark.

Remove text watermark by removing the shape with contains the text string "Confidential".

for (int i = 0; i < ppt.Slides.Count; i++)
{
    for (int j = 0; j < ppt.Slides[i].Shapes.Count; j++)
    {
        if (ppt.Slides[i].Shapes[j] is IAutoShape)
        {
            IAutoShape shape = ppt.Slides[i].Shapes[j] as IAutoShape;
            if (shape.TextFrame.Text.Contains("Confidential"))
            {
                ppt.Slides[i].Shapes.Remove(shape);
            }
        }
    }
}

Remove image watermark by setting SlideBackground.Fill.FillType as none.

for (int i = 0; i < ppt.Slides.Count; i++)
{
    ppt.Slides[i].SlideBackground.Fill.FillType = FillFormatType.None;
}

Step 3: Save the document to file.

ppt.SaveToFile("RemoveWartermark.pptx", FileFormat.Pptx2013);

Effective screenshot after removing the text and image watermark:

Remove text and image watermarks in presentation slides

Remove text and image watermarks in presentation slides

Full codes:

Remove text watermark in presentation slides:

Presentation ppt = new Presentation();
ppt.LoadFromFile("Sample.pptx", FileFormat.Pptx2013);

for (int i = 0; i < ppt.Slides.Count; i++)
{
    for (int j = 0; j < ppt.Slides[i].Shapes.Count; j++)
    {
        if (ppt.Slides[i].Shapes[j] is IAutoShape)
        {
            IAutoShape shape = ppt.Slides[i].Shapes[j] as IAutoShape;
            if (shape.TextFrame.Text.Contains("Confidential"))
            {
                ppt.Slides[i].Shapes.Remove(shape);
            }
        }
    }
}
ppt.SaveToFile("RemoveTextWartermark.pptx", FileFormat.Pptx2013);

Remove image watermark in presentation slides:

Presentation ppt = new Presentation();
ppt.LoadFromFile("Sample2.pptx", FileFormat.Pptx2013);

for (int i = 0; i < ppt.Slides.Count; i++)
{
    ppt.Slides[i].SlideBackground.Fill.FillType = FillFormatType.None;
}

ppt.SaveToFile("RemovePicWatermak.pptx", FileFormat.Pptx2013);
Thursday, 24 May 2018 08:41

Remove footers from word document in C#

There are up to three kinds of headers and footers on a word document (for first, even and odd pages) and Spire.Doc supports to insert footer and header to the word document in C#. In the following example, we will load a document with headers and footers, and we will remove all footers from all sections, but leaves headers.

Step 1: Create a new instance of word document and load the sample document from file.

Document doc = new Document();
doc.LoadFromFile("Sample.docx");

Step 2: Get the first section from file.

Section section = doc.Sections[0];

Step 3: Traverse the word document and clear all footers in different type.

foreach (Paragraph para in section.Paragraphs)
{
    foreach (DocumentObject obj in para.ChildObjects)
    {
        HeaderFooter footer;
        footer = section.HeadersFooters[HeaderFooterType.FooterFirstPage];
        if (footer != null)
            footer.ChildObjects.Clear();

        footer = section.HeadersFooters[HeaderFooterType.FooterOdd];
        if (footer != null)
            footer.ChildObjects.Clear();

        footer = section.HeadersFooters[HeaderFooterType.FooterEven];
        if (footer != null)
            footer.ChildObjects.Clear();
                          
    }

Step 4: Save the document to file.

doc.SaveToFile("Result.docx", FileFormat.Docx2013);

Full codes of removing footers but keeping the header on word document:

static void Main(string[] args)
{

    Document doc = new Document();
    doc.LoadFromFile("Sample.docx");

    Section section = doc.Sections[0];

    foreach (Paragraph para in section.Paragraphs)
    {
        foreach (DocumentObject obj in para.ChildObjects)
        {
            HeaderFooter footer;
            footer = section.HeadersFooters[HeaderFooterType.FooterFirstPage];
            if (footer != null)
                footer.ChildObjects.Clear();

            footer = section.HeadersFooters[HeaderFooterType.FooterOdd];
            if (footer != null)
                footer.ChildObjects.Clear();

            footer = section.HeadersFooters[HeaderFooterType.FooterEven];
            if (footer != null)
                footer.ChildObjects.Clear();
                              
        }
        
        doc.SaveToFile("Result.docx", FileFormat.Docx2013);


    }
Wednesday, 23 May 2018 08:50

Refresh PivotTable in Excel in C#

By default, the data source of PivotTable won't be refreshed automatically. If we update the data source of our PivotTable, the PivotTable that was built on that data source needs to be refreshed. This article is going to elaborate how to refresh PivotTable in Excel programmatically in c# using Spire.XLS.

Below is the screenshot of the example Excel file:

Refresh PivotTable in Excel in C#

Detail steps:

Step 1: Instantiate a Workbook object and load the Excel file.

Workbook workbook = new Workbook();
workbook.LoadFromFile(@"Sample.xlsx");

Step 2: Get the first worksheet.

Worksheet sheet = workbook.Worksheets[0];

Step 3: update the data source of PivotTable.

sheet.Range["C2"].Value = "199";

Step 4: Get the PivotTable that was built on the data source.

XlsPivotTable pt = workbook.Worksheets[0].PivotTables[0] as XlsPivotTable;

Step 5: Refresh the data of PivotTable.

pt.Cache.IsRefreshOnLoad = true;

Step 6: Save the file.

workbook.SaveToFile("Output.xlsx", ExcelVersion.Version2013);

Output after updating data source and refreshing the PivotTable:

Refresh PivotTable in Excel in C#

Full code:

using Spire.Xls;
using Spire.Xls.Core.Spreadsheet.PivotTables;

namespace Refresh_Pivot_Table_in_Excel
{
    class Program
    {
        static void Main(string[] args)
        {
            //Instantiate a Workbook object 
            Workbook workbook = new Workbook();
            //Load the Excel file
            workbook.LoadFromFile(@"Sample.xlsx");

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

            //Update the data source of PivotTable
            sheet.Range["C2"].Value = "199";

            //Get the PivotTable that was built on the data source
            XlsPivotTable pt = workbook.Worksheets[0].PivotTables[0] as XlsPivotTable;

            //Refresh the data of PivotTable
            pt.Cache.IsRefreshOnLoad = true;

            //Save the file
            workbook.SaveToFile("Output.xlsx", ExcelVersion.Version2013);
        }
    }
}

In this article, you will learn how to add header to an existing PDF document using Spire.PDF. For a page in a ready-made PDF document, the coordinate system follows the following rules.

  • The origin of the PDF coordinate system (0, 0) represents the top-left corner of the PDF page.
  • The x-axis extends to the right and the y-axis extends downward.

Before you draw text or image at the specified coordinates in the empty space, you must measure the margins of the page yourself to prevent the header from overlapping the body.

Step 1: Load a sample PDF document.

PdfDocument doc = new PdfDocument();
doc.LoadFromFile("sample.pdf");

Step 2: Draw image in the top empty space of each page.

for (int i = 0; i < doc.Pages.Count; i++)
{
    PdfImage headerImage = PdfImage.FromFile("logo.png");
    float width = headerImage.Width / 3;
    float height = headerImage.Height / 3;
    doc.Pages[i].Canvas.DrawImage(headerImage, 85, 20, width, height);
}

Step 3: Draw text in the top empty space of each page.

for (int i = 0; i < doc.Pages.Count; i++)
{
    string headerText = "HEADER TEXT";
    PdfTrueTypeFont font = new PdfTrueTypeFont(new Font("Impact", 15f));
    SizeF size = font.MeasureString(headerText);
    doc.Pages[i].Canvas.DrawString(headerText, font, PdfBrushes.RoyalBlue, doc.Pages[i].Size.Width - 90 - size.Width, 35);
}

Step 4: Save to file.

doc.SaveToFile("output.pdf");

Output:

Add Header to Existing PDF in C#, VB.NET

Full Code:

[C#]
PdfDocument doc = new PdfDocument();
doc.LoadFromFile("sample.pdf");

for (int i = 0; i < doc.Pages.Count; i++)
{
    PdfImage headerImage = PdfImage.FromFile("logo.png");
    float width = headerImage.Width / 3;
    float height = headerImage.Height / 3;
    doc.Pages[i].Canvas.DrawImage(headerImage, 85, 20, width, height);

    string headerText = "HEADER TEXT";
    PdfTrueTypeFont font = new PdfTrueTypeFont(new Font("Impact", 15f));
    SizeF size = font.MeasureString(headerText);
    doc.Pages[i].Canvas.DrawString(headerText, font, PdfBrushes.RoyalBlue, doc.Pages[i].Size.Width - 90 - size.Width, 35);
}

doc.SaveToFile("output.pdf");
[VB.NET]
Dim doc As PdfDocument =  New PdfDocument() 
doc.LoadFromFile("sample.pdf")

Dim i As Integer
For  i = 0 To  doc.Pages.Count- 1  Step  i + 1
    Dim headerImage As PdfImage =  PdfImage.FromFile("logo.png") 
    Dim width As single =  headerImage.Width / 3 
    Dim height As single =  headerImage.Height / 3 
    doc.Pages(i).Canvas.DrawImage(headerImage, 85, 20, width, height)

    Dim headerText As String =  "HEADER TEXT" 
    Dim font As PdfTrueTypeFont =  New PdfTrueTypeFont(New Font("Impact",15f)) 
    Dim size As SizeF =  font.MeasureString(headerText) 
    doc.Pages(i).Canvas.DrawString(headerText, font, PdfBrushes.RoyalBlue, doc.Pages(i).Size.Width - 90 - size.Width, 35)
Next

doc.SaveToFile("output.pdf")

Spire.Doc offers the property ShapeObject. HorizontalAlignment and ShapeObject. Vertical Alignment to enable developers to align the shapes horizontally or vertically. This article will show you to how to align the shapes on the word document in C#.

Step 1: Create a new instance of word document and load the document from file.

Document doc = new Document();
doc.LoadFromFile("Sample.docx");

Step 2: Get the first section from file.

Section section = doc.Sections[0];

Step 3: Traverse the word document and set the horizontal alignment as left.

foreach (Paragraph para in section.Paragraphs)
{
    foreach (DocumentObject obj in para.ChildObjects)
    {
        if (obj is ShapeObject)
        {
          (obj as ShapeObject).HorizontalAlignment = ShapeHorizontalAlignment.Left;

        }
    }
}

Step 4: Save the document to file.

doc.SaveToFile("Result.docx", FileFormat.Docx);

Effective screenshot after setting the alignment of the shapes.

How to align the shape on word document in C#

How to align the shape on word document in C#

Full codes:

Document doc = new Document();
doc.LoadFromFile("Sample.docx");

Section section = doc.Sections[0];

foreach (Paragraph para in section.Paragraphs)
{
    foreach (DocumentObject obj in para.ChildObjects)
    {
        if (obj is ShapeObject)
        {
            (obj as ShapeObject).HorizontalAlignment = ShapeHorizontalAlignment.Left;

            ////Set the vertical alignment as top
            //(obj as ShapeObject).VerticalAlignment = ShapeVerticalAlignment.Top;
        }
    }
}

doc.SaveToFile("Result.docx", FileFormat.Docx2013);

Adding shadow effect is one of the good ways to make a data label stand out on your chart. This article is going to show you how to add shadow effect to a chart data label in PowerPoint using Spire.Presentation.

Detail steps:

Step 1: Initialize a Presentation object and load the PowerPoint file.

Presentation ppt = new Presentation();
ppt.LoadFromFile(@"test.pptx");

Step 2: Get the chart.

IChart chart = ppt.Slides[0].Shapes[0] as IChart;

Step 3: Add a data label to the first chart series.

ChartDataLabelCollection dataLabels = chart.Series[0].DataLabels;            
ChartDataLabel Label = dataLabels.Add();
Label.LabelValueVisible = true;

Step 4: Add outer shadow effect to the data label.

Label.Effect.OuterShadowEffect = new OuterShadowEffect();
//Set shadow color
Label.Effect.OuterShadowEffect.ColorFormat.Color = Color.Yellow;
//Set blur
Label.Effect.OuterShadowEffect.BlurRadius = 5;
//Set distance
Label.Effect.OuterShadowEffect.Distance = 10;
//Set angle
Label.Effect.OuterShadowEffect.Direction = 90f;

Step 5: Save the file.

ppt.SaveToFile("Shadow.pptx", FileFormat.Pptx2010);

Screenshot:

Add Shadow Effect to Chart DataLabels in PowerPoint in C#

Full code:

using System.Drawing;
using Spire.Presentation;
using Spire.Presentation.Charts;
using Spire.Presentation.Collections;
using Spire.Presentation.Drawing;

namespace Add_Shadow_Effect_to_Chart_Datalabel
{
    class Program
    {
        static void Main(string[] args)
        {
            //Initialize a Presentation object
            Presentation ppt = new Presentation();
            //Load the PowerPoint file
            ppt.LoadFromFile(@"test.pptx");

            //Get the chart
            IChart chart = ppt.Slides[0].Shapes[0] as IChart;

            //Add a data label to the first chart series
            ChartDataLabelCollection dataLabels = chart.Series[0].DataLabels;            
            ChartDataLabel Label = dataLabels.Add();
            Label.LabelValueVisible = true;
            
            //Add outer shadow effect to the data label

            Label.Effect.OuterShadowEffect = new OuterShadowEffect();
            //Set shadow color
            Label.Effect.OuterShadowEffect.ColorFormat.Color = Color.Yellow;
            //Set blur
            Label.Effect.OuterShadowEffect.BlurRadius = 5;
            //Set distance
            Label.Effect.OuterShadowEffect.Distance = 10;
            //Set angle
            Label.Effect.OuterShadowEffect.Direction = 90f;            

            //Save the file
            ppt.SaveToFile("Shadow.pptx", FileFormat.Pptx2010);
        }
    }
}

Spire.Doc supports to insert new shapes to the word document and from version 6.4.11, Spire.Doc public the property ShapeObject.Rotation to enable developers to rotate the shapes. This article will show you to how to rotate the shapes on the word document in C#.

Step 1: Create a new instance of word document and load the document from file.

Document doc = new Document();
doc.LoadFromFile("Sample.docx");

Step 2: Get the first section from file.

Section section = doc.Sections[0];

Step 3: Traverse the word document and set the shape rotation as 10.

foreach (Paragraph para in section.Paragraphs)
{
    foreach (DocumentObject obj in para.ChildObjects)
    {
        if (obj is ShapeObject)
        {
            (obj as ShapeObject).Rotation = 10.0;
        }
    }
}

Step 4: Save the document to file.

doc.SaveToFile("Result.docx", FileFormat.Docx);

Effective screenshot after rotate the shapes:

How to rotate the shape on word document in C#

Full codes:

Document doc = new Document();
doc.LoadFromFile("Sample.docx");

Section section = doc.Sections[0];

foreach (Paragraph para in section.Paragraphs)
{
    foreach (DocumentObject obj in para.ChildObjects)
    {
        if (obj is ShapeObject)
        {
            (obj as ShapeObject).Rotation = 10.0;
        }
    }
}

doc.SaveToFile("Result.docx", FileFormat.Docx);