Knowledgebase (2311)
Children categories
Spire.XLS provides a class named Worksheet, it contains a MergedCells property which makes it easy for us to obtain the merged cells in a worksheet. This property will return an array of the merged cell ranges, after that we can do any desired operations such as unmerge and apply formatting on the cells in the ranges. This article explains how to detect all merged cells in a worksheet and unmerge them once using Spire.XLS.
Here we used a template Excel file which has some merged cells:
Detail steps:
Step 1: Instantiate a Workbook object and load the Excel file.
Workbook workbook = new Workbook();
workbook.LoadFromFile("Sample.xlsx");
Step 2: Access the first worksheet by passing its sheet index.
Worksheet sheet = workbook.Worksheets[0];
Step 3: Get the merged cell ranges in the first worksheet and put them into a CellRange array.
CellRange[] range = sheet.MergedCells;
Step 4: Traverse through the array and unmerge the merged cells.
foreach (CellRange cell in range)
{
cell.UnMerge();
}
Step 5: Save the file.
workbook.SaveToFile("Output.xlsx",ExcelVersion.Version2010);
After executing the code, we'll get the following output file:
Full codes:
using Spire.Xls;
namespace Detect_Merged_Cells
{
class Program
{
static void Main(string[] args)
{
Workbook workbook = new Workbook();
workbook.LoadFromFile("Sample.xlsx");
Worksheet sheet = workbook.Worksheets[0];
CellRange[] range = sheet.MergedCells;
foreach (CellRange cell in range)
{
cell.UnMerge();
}
workbook.SaveToFile("Output.xlsx",ExcelVersion.Version2010);
}
}
}
Imports Spire.Xls
Namespace Detect_Merged_Cells
Class Program
Private Shared Sub Main(args As String())
Dim workbook As New Workbook()
workbook.LoadFromFile("Sample.xlsx")
Dim sheet As Worksheet = workbook.Worksheets(0)
Dim range As CellRange() = sheet.MergedCells
For Each cell As CellRange In range
cell.UnMerge()
Next
workbook.SaveToFile("Output.xlsx",ExcelVersion.Version2010)
End Sub
End Class
End Namespace
With the help of Spire.PDF, we have already demonstrated how to add text with different styles to a PDF file. By using the method canvas.drawstring offered by Spire.PDF, we can set the position, font, brush and style for the adding texts. With the PdfFontStyle, we can set the style to underline, bold, italic, regular and strikeout. Sometimes we may need to set multiple font style for the same texts within one paragraph, such as bold and italic together.
Here comes to the code snippet of how to apply two kinds of font styles together for the text on PDF in C#.
Step 1: Create a new PDF document.
PdfDocument pdf = new PdfDocument();
Step 2: Add a new page to the PDF file.
PdfPageBase page = pdf.Pages.Add(PdfPageSize.A4, new PdfMargins());
Step 3: Create the PdfFont as Helvetica with size 10f, apply two font styles for it.
PdfFont font = new PdfFont(PdfFontFamily.Helvetica,10f,PdfFontStyle.Italic | PdfFontStyle.Underline); PdfFont font2 = new PdfFont(PdfFontFamily.Helvetica, 10f, PdfFontStyle.Bold | PdfFontStyle.Strikeout); PdfFont font3 = new PdfFont(PdfFontFamily.Helvetica, 10f, PdfFontStyle.Bold | PdfFontStyle.Underline);
Step 4: Create a brush and set its color.
PdfSolidBrush brush = new PdfSolidBrush(Color.Blue); PdfSolidBrush brush2 = new PdfSolidBrush(Color.Gray); PdfSolidBrush brush3 = new PdfSolidBrush(Color.Green);
Step 5: Draw the text string at the specified location with the specified Brush and Font objects.
page.Canvas.DrawString("This sentence is Italic and underline", font, brush, new PointF(10, 10));
page.Canvas.DrawString("This sentence is Bold and strikeout", font2,brush2, new PointF(10, 40));
page.Canvas.DrawString("This sentence is Bold and underline",font3,brush3,new PointF(10, 70));
Step 6: Save the document to file.
pdf.SaveToFile("reslut.pdf");
Please check the effective screenshot as below:
Full codes:
using Spire.Pdf;
using Spire.Pdf.Graphics;
using System.Drawing;
namespace MultipleFontStyles
{
class Program
{
static void Main(string[] args)
{
PdfDocument pdf = new PdfDocument();
PdfPageBase page = pdf.Pages.Add(PdfPageSize.A4, new PdfMargins());
PdfFont font = new PdfFont(PdfFontFamily.Helvetica, 10f, PdfFontStyle.Italic | PdfFontStyle.Underline);
PdfFont font2 = new PdfFont(PdfFontFamily.Helvetica, 10f, PdfFontStyle.Bold | PdfFontStyle.Strikeout);
PdfFont font3 = new PdfFont(PdfFontFamily.Helvetica, 10f, PdfFontStyle.Bold | PdfFontStyle.Underline);
PdfSolidBrush brush = new PdfSolidBrush(Color.Blue);
PdfSolidBrush brush2 = new PdfSolidBrush(Color.Gray);
PdfSolidBrush brush3 = new PdfSolidBrush(Color.Green);
page.Canvas.DrawString("This sentence is Italic and underline", font, brush, new PointF(10, 10));
page.Canvas.DrawString("This sentence is Bold and strikeout", font2, brush2, new PointF(10, 40));
page.Canvas.DrawString("This sentence is Bold and underline", font3, brush3, new PointF(10, 70));
pdf.SaveToFile("reslut.pdf");
}
}
}
Textbox in Excel is a special kind of graphic object, which allows users to add some text in it. Moreover, textbox can be filled with solid color, gradient, pattern or a picture so that it looks more attractive. This article presents how to add a picture fill to Excel textbox and adjust the position of the picture as well.
Code Snippets:
Step 1: Create a new instance of Workbook class and get the first worksheet.
Workbook workbook = new Workbook(); Worksheet sheet = workbook.Worksheets[0];
Step 2: Add a textbox shape into the worksheet.
ITextBoxShape shape = sheet.TextBoxes.AddTextBox(2, 2, 200, 300);
Step 3: Fill the textbox with a 160x160 pixels picture.
shape.Fill.CustomPicture(@"C:\Users\Administrator\Desktop\logo.png"); shape.Fill.FillType = ShapeFillType.Picture;
Step 4: Save the file.
workbook.SaveToFile("PicFill.xlsx", ExcelVersion.Version2013);
When we add a picture fill to a textbox using above code, the picture will stretch to fill the shape, like below screenshot.
If you want to prevent the image from stretching, you can adjust the size of the textbox or change the position of the picture, for example, place the image at the central position of the textbox. Following code snippet demonstrates how to center align the picture fill.
//If the height of textbox is larger than the height of original picture, set the picture into vertical center
if (textbox.Height > textbox.Fill.Picture.Height)
{
int difH = textbox.Height - textbox.Fill.Picture.Height;
(textbox.Fill as XlsShapeFill).PicStretch.Top = difH * 100 / (textbox.Height * 2);
(textbox.Fill as XlsShapeFill).PicStretch.Bottom = difH * 100 / (textbox.Height * 2);
}
//If the width of textbox is larger than the width of original picture, set the picture into horizontal center
if (textbox.Width > textbox.Fill.Picture.Width)
{
int difW = textbox.Width - textbox.Fill.Picture.Width;
(textbox.Fill as XlsShapeFill).PicStretch.Left = difW * 100 / (textbox.Width * 2);
(textbox.Fill as XlsShapeFill).PicStretch.Right = difW * 100 / (textbox.Width * 2);
}
Full Code:
using Spire.Xls;
using Spire.Xls.Core;
using Spire.Xls.Core.Spreadsheet.Shapes;
namespace AddingPicture
{
class Program
{
static void Main(string[] args)
{
//load Excel file
Workbook workbook = new Workbook();
Worksheet sheet = workbook.Worksheets[0];
ITextBoxShape textbox = sheet.TextBoxes.AddTextBox(2, 2, 200, 300);
textbox.Fill.CustomPicture(@"C:\Users\Administrator\Desktop\logo.png");
textbox.Fill.FillType = ShapeFillType.Picture;
//If the height of textbox is larger than the height of original picture, set the picture into vertical center
if (textbox.Height > textbox.Fill.Picture.Height)
{
int difH = textbox.Height - textbox.Fill.Picture.Height;
(textbox.Fill as XlsShapeFill).PicStretch.Top = difH * 100 / (textbox.Height * 2);
(textbox.Fill as XlsShapeFill).PicStretch.Bottom = difH * 100 / (textbox.Height * 2);
}
//If the width of textbox is larger than the width of original picture, set the picture into horizontal center
if (textbox.Width > textbox.Fill.Picture.Width)
{
int difW = textbox.Width - textbox.Fill.Picture.Width;
(textbox.Fill as XlsShapeFill).PicStretch.Left = difW * 100 / (textbox.Width * 2);
(textbox.Fill as XlsShapeFill).PicStretch.Right = difW * 100 / (textbox.Width * 2);
}
workbook.SaveToFile("PicFill.xlsx", ExcelVersion.Version2013);
}
}
}
Imports Spire.Xls
Imports Spire.Xls.Core
Imports Spire.Xls.Core.Spreadsheet.Shapes
Namespace AddingPicture
Class Program
Private Shared Sub Main(args As String())
'load Excel file
Dim workbook As New Workbook()
Dim sheet As Worksheet = workbook.Worksheets(0)
Dim textbox As ITextBoxShape = sheet.TextBoxes.AddTextBox(2, 2, 200, 300)
textbox.Fill.CustomPicture("C:\Users\Administrator\Desktop\logo.png")
textbox.Fill.FillType = ShapeFillType.Picture
'If the height of textbox is larger than the height of original picture, set the picture into vertical center
If textbox.Height > textbox.Fill.Picture.Height Then
Dim difH As Integer = textbox.Height - textbox.Fill.Picture.Height
TryCast(textbox.Fill, XlsShapeFill).PicStretch.Top = difH * 100 / (textbox.Height * 2)
TryCast(textbox.Fill, XlsShapeFill).PicStretch.Bottom = difH * 100 / (textbox.Height * 2)
End If
'If the width of textbox is larger than the width of original picture, set the picture into horizontal center
If textbox.Width > textbox.Fill.Picture.Width Then
Dim difW As Integer = textbox.Width - textbox.Fill.Picture.Width
TryCast(textbox.Fill, XlsShapeFill).PicStretch.Left = difW * 100 / (textbox.Width * 2)
TryCast(textbox.Fill, XlsShapeFill).PicStretch.Right = difW * 100 / (textbox.Width * 2)
End If
workbook.SaveToFile("PicFill.xlsx", ExcelVersion.Version2013)
End Sub
End Class
End Namespace
