Image

Image (7)

Tuesday, 27 February 2018 07:22

Delete Images from Excel Worksheet in C#

Written by support iceblue

This article demonstrates how to delete images from an excel worksheet using Spire.XLS component and C#. Below is the screenshot of the sample file we used for demonstration:

Delete Images from Excel Worksheet in C#

Detail steps:

Step 1: Initialize an object of Workbook class 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: Delete all images from the worksheet.

for (int i = sheet.Pictures.Count-1; i >= 0; i--)
{
    sheet.Pictures[i].Remove();
}

Step 4: Save the file.

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

Output:

Delete Images from Excel Worksheet in C#

Full code:

using Spire.Xls;

namespace DeleteImages
{
    class Program
    {
        static void Main(string[] args)
        {
            Workbook workbook = new Workbook();
            workbook.LoadFromFile("Input.xlsx");

            Worksheet sheet = workbook.Worksheets[0];

            for (int i = sheet.Pictures.Count-1; i >= 0; i--)
            {
                sheet.Pictures[i].Remove();
            }

            workbook.SaveToFile("DeleteImages.xlsx", ExcelVersion.Version2013);
        }
    }
}

When we add an image to the Excel worksheet, we always need to reset the size and position for the image to list the image where we want and make the size coordinate with the other elements on the Excel worksheet. This article will focus on demonstrates how to apply picture settings by using Spire.XLS.

Step 1: Initialize an instance of Workbook and get the first worksheet.

Workbook workbook = new Workbook();
Worksheet sheet = workbook.Worksheets[0];

Step 2: Add a picture to the first worksheet.

ExcelPicture picture = sheet.Pictures.Add(1, 1, "Logo.png");

Step 3: Set the size for the picture.

picture.Width = 75;
picture.Height = 75;

Step 4: Set the position for the picture.

picture.Left = 200;
picture.Top = 100;

Step 5: Save the document to file.

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

Effective screenshot after reset the size and position for the image:

Reset the size and position for the image on Excel worksheet

Full codes:

{
    Workbook workbook = new Workbook();
    Worksheet sheet = workbook.Worksheets[0];
         
    ExcelPicture picture = sheet.Pictures.Add(1, 1, "Logo.png");

    picture.Width = 75;
    picture.Height = 75;

    picture.Left = 200;
    picture.Top = 100;
                                  
    workbook.SaveToFile("Output.xlsx",FileFormat.Version2013);
}

In the previous article, we've introduced how to insert image into excel worksheet. In this article, we'll demonstrate how to extract image from Excel worksheet. Please check the below screenshot of the source excel worksheet which contains an image:

How to Extract Image from Excel Worksheet in C#, VB.NET

Code snippet:

Step 1: Instantiate an instance of Workbook class and load the excel file.

Workbook workbook = new Workbook();
workbook.LoadFromFile(@"C:\Users\Administrator\Desktop\test.xlsx");

Step 2: Get the first worksheet.

Worksheet sheet = workbook.Worksheets[0];

Step 3: Get the first picture in the worksheet and save it to disk.

ExcelPicture picture = sheet.Pictures[0];
picture.Picture.Save(@"Image\image.png", ImageFormat.Png);

Screenshot:

How to Extract Image from Excel Worksheet in C#, VB.NET

Full code:

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

namespace Extract_Image
{
    class Program
    {
        static void Main(string[] args)
        {
            Workbook workbook = new Workbook();
            workbook.LoadFromFile(@"C:\Users\Administrator\Desktop\test.xlsx");
            Worksheet sheet = workbook.Worksheets[0];
            ExcelPicture picture = sheet.Pictures[0];
            picture.Picture.Save(@"Image\image.png", ImageFormat.Png);
        }
    }
}
[VB.NET]
Imports System.Drawing.Imaging
Imports Spire.Xls

Namespace Extract_Image
	Class Program
		Private Shared Sub Main(args As String())
			Dim workbook As New Workbook()
			workbook.LoadFromFile("C:\Users\Administrator\Desktop\test.xlsx")
			Dim sheet As Worksheet = workbook.Worksheets(0)
			Dim picture As ExcelPicture = sheet.Pictures(0)
			picture.Picture.Save("Image\image.png", ImageFormat.Png)
		End Sub
	End Class
End Namespace

By default, when we insert an image to a cell, the image automatically be placed at top left corner. If there are some text in the same cell, then the text will be covered by the image. To avoid the problem, we need to vertically or horizontally align the picture. This article focuses on how to align a picture within a cell using Spire.XLS with C#, VB.NET.

Code Snippet:

Step 1: Create an object of Workbook and get the first worksheet.

Workbook wb = new Workbook();
Worksheet sheet = wb.Worksheets[0];

Step 2: Insert an image to the specific cell using Pictures.Add(int topRow, int leftColumn, string filename) method.

string picPath = @"C:\Users\Administrator\Desktop\scenery.jpg";
ExcelPicture picture = sheet.Pictures.Add(1, 1, picPath);

Step 3: Adjust the column width and row height so that the cell can contain the picture.

sheet.Columns[0].ColumnWidth = 50;
sheet.Rows[0].RowHeight = 150;

Step 4: Vertically and horizontally align the image by setting values for LeftColumnOffset and TopRowOffset properties.

picture.LeftColumnOffset = 100;
picture.TopRowOffset = 25;

Step 5: Save the file.

wb.SaveToFile("AlignPicture.xlsx", ExcelVersion.Version2013);

Output:

How to Align a Picture within a Cell in C#, VB.NET

Full Code:

[C#]
Workbook wb = new Workbook();
Worksheet sheet = wb.Worksheets[0];

sheet.Range["A1"].Text = "Align Picture Within A Cell:";
sheet.Range["A1"].Style.VerticalAlignment = VerticalAlignType.Top;

string picPath = @"C:\Users\Administrator\Desktop\scenery.jpg";
ExcelPicture picture = sheet.Pictures.Add(1, 1, picPath);

sheet.Columns[0].ColumnWidth = 50;
sheet.Rows[0].RowHeight = 150;

picture.LeftColumnOffset =100;
picture.TopRowOffset = 25;

wb.SaveToFile("AlignPicture.xlsx", ExcelVersion.Version2013);
[VB.NET]
Dim wb As New Workbook()
Dim sheet As Worksheet = wb.Worksheets(0)

sheet.Range("A1").Text = "Align Picture Within A Cell:"
sheet.Range("A1").Style.VerticalAlignment = VerticalAlignType.Top

Dim picPath As String = "C:\Users\Administrator\Desktop\scenery.jpg"
Dim picture As ExcelPicture = sheet.Pictures.Add(1, 1, picPath)

sheet.Columns(0).ColumnWidth = 50
sheet.Rows(0).RowHeight = 150

picture.LeftColumnOffset = 100
picture.TopRowOffset = 25

wb.SaveToFile("AlignPicture.xlsx", ExcelVersion.Version2013)

Set offset of the filled image could make your chart background pictures zoom and show proportional as you wish. This article shows how to set the offset of image via Spire.XLS.

Here are the steps:

Step 1: Create an instance of Spire.XLS.Workbook.

Workbook book = new Workbook();

Step 2: Load data and create a contrast sheet.

workbook.LoadFromFile("test.xlsx",ExcelVersion.Version2013);
Worksheet sheet = workbook.Worksheets[0];
Worksheet sheet1 = workbook.Worksheets.Add("Contrast"); 

Step 3: Add chart1 and background image to sheet1 as comparision.

Chart chart1 = sheet1.Charts.Add(ExcelChartType.ColumnClustered);
chart1.DataRange = sheet.Range["D1:E9"];
chart1.SeriesDataFromRange = false;

chart1.LeftColumn = 1;
chart1.TopRow = 11;
chart1.RightColumn = 8;
chart1.BottomRow = 33;

chart1.ChartArea.Fill.CustomPicture(Image.FromFile("2.jpg"), "None");

Step 4: Add same chart and background image then set offset of image by transforming image in sheet[0] as form of XlsShapeFill. Then set the property of PicStretch of each direction with percentage and Tile property.

Chart chart = sheet.Charts.Add(ExcelChartType.ColumnClustered);
chart.DataRange = sheet.Range["D1:E9"];
chart.SeriesDataFromRange = false;
                 
chart.LeftColumn = 1;
chart.TopRow = 11;
chart.RightColumn = 8;
chart.BottomRow = 33;
          
chart.ChartArea.Fill.CustomPicture(Image.FromFile("2.jpg"), "None");
            
IChart ichart = sheet.Charts[0];
(ichart.ChartArea.Fill as XlsShapeFill).Tile = false;
(ichart.ChartArea.Fill as XlsShapeFill).PicStretch.Left = 10;
(ichart.ChartArea.Fill as XlsShapeFill).PicStretch.Top = 20;
(ichart.ChartArea.Fill as XlsShapeFill).PicStretch.Right = 10;
(ichart.ChartArea.Fill as XlsShapeFill).PicStretch.Bottom = 5;

Step 5: Save and review.

workbook.SaveToFile("result.xlsx", ExcelVersion.Version2013);
System.Diagnostics.Process.Start("result.xlsx");

Screenshot:

Set the offset of image when the fill way of chart is picture fill via Spire.XLS

Set the offset of image when the fill way of chart is picture fill via Spire.XLS

Full code:

Workbook workbook = new Workbook();
workbook.LoadFromFile("test.xlsx",ExcelVersion.Version2013);
Worksheet sheet = workbook.Worksheets[0];
Worksheet sheet1 = workbook.Worksheets.Add("Contrast");

//add contrast
Chart chart1 = sheet1.Charts.Add(ExcelChartType.ColumnClustered);
chart1.DataRange = sheet.Range["D1:E9"];
chart1.SeriesDataFromRange = false;

//Chart Position
chart1.LeftColumn = 1;
chart1.TopRow = 11;
chart1.RightColumn = 8;
chart1.BottomRow = 33;

chart1.ChartArea.Fill.CustomPicture(Image.FromFile("2.jpg"), "None");

//add original
Chart chart = sheet.Charts.Add(ExcelChartType.ColumnClustered);
chart.DataRange = sheet.Range["D1:E9"];
chart.SeriesDataFromRange = false;
                 
//Chart Position
chart.LeftColumn = 1;
chart.TopRow = 11;
chart.RightColumn = 8;
chart.BottomRow = 33;
          
chart.ChartArea.Fill.CustomPicture(Image.FromFile("2.jpg"), "None");
            
IChart ichart = sheet.Charts[0];
(ichart.ChartArea.Fill as XlsShapeFill).Tile = false;
(ichart.ChartArea.Fill as XlsShapeFill).PicStretch.Left = 10;
(ichart.ChartArea.Fill as XlsShapeFill).PicStretch.Top = 20;
(ichart.ChartArea.Fill as XlsShapeFill).PicStretch.Right = 10;
(ichart.ChartArea.Fill as XlsShapeFill).PicStretch.Bottom = 5;

workbook.SaveToFile("result.xlsx", ExcelVersion.Version2013);
System.Diagnostics.Process.Start("result.xlsx");
Tuesday, 26 February 2013 08:05

Insert Excel Background Image in C#, VB.NET

Written by support iceblue

This section aims at providing developers a solution on how to insert Excel background image in C#, VB.NET via a .NET Excel library Spire.XLS for .NET.

Spire.XLS for .NET enables you to add background image in your Excel file by setting BackgroundImage property of the object PageSetup to be the image you want to insert, you can see the code here: Spire.Xls.Worksheet.PageSetup.BackgroundImage. Now let us start our task.

Task:

If you are not familiar with Spire.XLS for .NET, please allow me to give a short description of it. Spire.XLS for .NET is an Excel application which enables users to perform a wide range of processing tasks in Excel documents such as generate, edit and handle Excel files in C#.

You will use Spire.XLS for .NET, so we can download Spire.XLS for .NET first. After installing it on system, please start your project either in C# Console Application or VB.NET Console Application. You also can create your project in Windows Forms Application. Please do not forget to add Spire.Xls as reference. The default path is “..\Spire.XLS\Bin\Bin\NET4.0\ Spire.XLS.dll”. Below shows the whole code of my project:

[C#]
//initialize a new instance of Workbook
Workbook workbook = new Workbook();
//import an Excel file from system
workbook.LoadFromFile(@"..\Excel background image.xlsx");
//get the first worksheet
Worksheet sheet = workbook.Worksheets[0];
//open an image 
Bitmap bm = new Bitmap(Image.FromFile(@"..\butterflytable.jpg"));
//set the image to be background image of the worksheet
sheet.PageSetup.BackgoundImage = bm;
//save excel file
workbook.SaveToFile("result.xlsx",ExcelVersion.Version2010);
//launch the file
System.Diagnostics.Process.Start("result.xlsx");
[VB.NET]
'initialize a new instance of Workbook
Dim workbook As New Workbook()
'import an Excel file from system
workbook.LoadFromFile("..\Excel background image.xlsx")
'get the first worksheet
Dim sheet As Worksheet = workbook.Worksheets(0)
'open an image 
Dim bm As New Bitmap(Image.FromFile("..\butterflytable.jpg"))
'set the image to be background image of the worksheet
sheet.PageSetup.BackgoundImage = bm
'save excel file
workbook.SaveToFile("result.xlsx",ExcelVersion.Version2010)
'launch the file
System.Diagnostics.Process.Start("result.xlsx")
          

Output File:

After executing above code, I successfully add background image in Excel file, you can see the output file as below:

Insert Excel Background Image

In this section, I have introduced the detail solution on how to insert background image in Excel file with C#, VB.NET. I wish it can help you. If you have any questions, comments or advice, you can add it at E-iceblue Forum, our professionals will give prompt reply.

Thursday, 20 January 2011 10:33

Insert Image in Excel in C#, VB.NET

Written by Administrator

Users are allowed to insert image in Excel files. With image, the appearance of Excel file will be more beautiful. Besides decorating, some images are related to data information in Excel. For example, the image will be a chart. These images can make readers learn data information more clearly. This guide will show an easy method to insert image in Excel with C#, VB.NET

Spire.XLS for .NET, a professional component to operating Excel files, enables users to insert image in Excel by using C# and VB.NET. This guide focuses on how to easily insert image in Excel by using Spire.XLS for .NET.  Developers can use sheet.Pictures.Add(int toprow, int leftcolumn, filename string) method to insert image in Excel directly. Below demonstrates an Excel with an image of flower.

Insert Excel Image

Download and install Spire.XLS for .NET and then use the following code to insert image.

[C#]
using Spire.Xls;

namespace InsertImage
{
    class Program
    {
        static void Main(string[] args)
        {
            //Create Workbook
            Workbook workbook = new Workbook();
            Worksheet sheet = workbook.Worksheets[0];

            //Insert Image
            sheet.Pictures.Add(1, 1, @"E:\work\sample.jpg");

            //Save and Launch
            workbook.SaveToFile("ExcelImage.xlsx", ExcelVersion.Version2010);
            System.Diagnostics.Process.Start("ExcelImage.xlsx");
        }
    }
}
[VB.NET]
Imports Spire.Xls

Namespace InsertImage
    Friend Class Program
        Shared Sub Main(ByVal args() As String)
            'Create Workbook
            Dim workbook As New Workbook()
            Dim sheet As Worksheet = workbook.Worksheets(0)

            'Insert Image
            sheet.Pictures.Add(1, 1, "E:\work\sample.jpg")

            'Save and Launch
            workbook.SaveToFile("ExcelImage.xlsx", ExcelVersion.Version2010)
            System.Diagnostics.Process.Start("ExcelImage.xlsx")
        End Sub
    End Class
End Namespace