Determine if an Excel File Contains VBA Macros in C#, VB.NET
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:
Full code:
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();
}
}
}
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
Add Footer to Existing PDF in C#, VB.NET
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:
Full Code:
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");
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")
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:
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:
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);
Remove text and image watermarks in presentation slides
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.
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:
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);
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);
}
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:
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:
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);
}
}
}
Add Header to Existing PDF in C#, VB.NET
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:
Full Code:
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");
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")
How to align the shape on word document in C#
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.
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);
Add Shadow Effect to Chart DataLabels in PowerPoint in C#
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:
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);
}
}
}
How to rotate the shape on word document in C#
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:
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);
