.NET Excel Layout-Add Excel Background Image in C# and Visual Basic

Each company must have a lot of reports to present monthly sales, financial situation etc. And these reports are often released with a great appearance and layout, for example, well-designed cells or with beautiful charts. If we want to create a wonderful report, how to do?

As is known, Excel is very powerful on displaying, editing and calculating data information so that it was recognized as one of the best choice to create report. Now, the data information has been imported to Excel and next we need to format this Excel to be a appealed one. In this post, I will introduce a solution to make an Excel attractive through adding background image in C# and Visual Basic.

In this example, a .NET Excel component  is used so that I have added its dll as reference in my project.

Firstly, load this Excel file and initialize a Worksheet instance. Secondly, get a Bitmap image instance from specified path. Thirdly, assign this image instance as value for BackgroundImage property of PageSetup of this sheet instance. Finally, save the new Excel file and launch for viewing.

Please follow the code below to insert Excel background image.

CODE:

C#

using System.Drawing;

using Spire.Xls;

 

namespace ExcelBackgroundImage

{

    class Background

    {

        static void Main(string[] args)

        {

            //Load File

            Workbook workbook = new Workbook();

            workbook.LoadFromFile(@”E:\Work\Documents\ExcelFiles\VendorInfo.xlsx”);

            Worksheet sheet = workbook.Worksheets[0];

 

            //Set Background Image

            Bitmap bm = new Bitmap(Image.FromFile(@”E:\Work\Documents\SampleImage\X.jpg”));

            sheet.PageSetup.BackgoundImage = bm;

 

            //Save and Launch

            workbook.SaveToFile(“Background.xlsx”, ExcelVersion.Version2010);

            System.Diagnostics.Process.Start(“Background.xlsx”);

        }

    }

}

 

VB

Imports System.Drawing

Imports Spire.Xls

 

Namespace ExcelBackgroundImage

    Friend Class Background

        Shared Sub Main(ByVal args() As String)

            ‘Load File

            Dim workbook As New Workbook()

            workbook.LoadFromFile(“E:\Work\Documents\ExcelFiles\VendorInfo.xlsx”)

            Dim sheet As Worksheet = workbook.Worksheets(0)

 

            ‘Set Background Image

            Dim bm As New Bitmap(Image.FromFile(“E:\Work\Documents\SampleImage\X.jpg”))

            sheet.PageSetup.BackgoundImage = bm

 

            ‘Save and Launch

            workbook.SaveToFile(“Background.xlsx”, ExcelVersion.Version2010)

            System.Diagnostics.Process.Start(“Background.xlsx”)

        End Sub

    End Class

End Namespace

         

RESULT

DOWNLOAD Spire.XLS for .NET Here

Advertisements

How to Convert Excel to HTML Easily with C#, VB.NET

Microsoft Excel is taken as the ideal tool to display and calculate data. When displaying data, users would like to set format to beautify appearance, for example, font style, background color, borders etc.

However, if we want to present the data information as a table in webpage not Excel, how to do? Some people may choose to write HTML code manually to display. But if there is lots of data, the code will be complicated. So in order to meet this requirement more easily and quickly, people think of another method, to convert Excel to HTML. We can import data to Excel and format it at first and then convert it to HTML. If so, we can find that the well formatted data information can be shown as a table in webpage.

Following, I will introduce an easy method to convert Excel to HTML by using C#, VB.NET.

In my example, I use a .NET Excel component, Spire.XLS for .NET. Through it, we can invoke sheet.SaveToHTML(filename string) method directly to save the Excel file to HTML.

CODE:

C#

using Spire.Xls;

 

namespace ExceltoHTML

{

    class Program

    {

        static void Main(string[] args)

        {

            Workbook workbook = new Workbook();

            workbook.LoadFromFile(@”E:\Work\Documents\ExcelFiles\PartSalesInfo.xlsx”);

 

            Worksheet sheet = workbook.Worksheets[0];

            sheet.SaveToHtml(“xlsx2html.html”);

            System.Diagnostics.Process.Start(“xlsx2html.html”);

        }

    }

}

VB

Imports Spire.Xls

 

Namespace ExceltoHTML

    Friend Class Program

        Shared Sub Main(ByVal args() As String)

            Dim workbook As New Workbook()

            workbook.LoadFromFile(“E:\Work\Documents\ExcelFiles\PartSalesInfo.xlsx”)

 

            Dim sheet As Worksheet = workbook.Worksheets(0)

            sheet.SaveToHtml(“xlsx2html.html”)

            System.Diagnostics.Process.Start(“xlsx2html.html”)

        End Sub

    End Class

End Namespace

           

RESULT

DOWNLOAD Spire.XLS for .NET Here

Sort Excel Data by Using VB.NET Easily and Quickly

Data Sorting is one of the most frequently used functions in Excel. It focuses on adjusting order to make data information to be assigned more regularly. After sorting data, users can analyze and calculate data more conveniently.

In Excel, users can sort data by text in alphabetical order (A to Z or Z to A), by numbers from highest to lowest or lowest to highest. Also, users can sort data according to cell format, such as background color, font color etc.

In this post, I will share a method to sort data by using VB.NET. I prepare an Excel file which saves some vendors information. I want to sort the State column by ascending order. The following screenshot shows the original order.

I use a .NET Excel component, Spire.XLS for .NET in my example for realizing this function more easily and quickly. Therefore, its dll file has been added as reference in my project.

STEPS:

Firstly, I use workbook.LoadFromFile(file name string) method to load the Excel file I want to sort data. Then, initialize the worksheet which data is saved in.

Secondly, use workbook.DataSorter.SortColumns.Add(key, OrderBy type) method to confirm which column to sort and order type. Then, use workbook.DataSorter.Sort(data range) method to confirm data range to sort.

Thirdly, save the Excel file by using workbook.SaveToFile(file name string, ExcelVersion) method. Finally, launch it to get result.

CODE:

Imports Spire.Xls

 

Module Sorting

    Sub Main()

        ‘Load Document

        Dim workbook As New Workbook()

        workbook.LoadFromFile(“E:\Work\Documents\ExcelFiles\VendorInfo.xlsx”, ExcelVersion.Version2010)

        Dim sheet As Worksheet = workbook.Worksheets(0)

 

        ‘Sort Data

        workbook.DataSorter.SortColumns.Add(4, OrderBy.Ascending)

        workbook.DataSorter.Sort(sheet(“E1:E20”))

 

        ‘Save and Launch

        workbook.SaveToFile(“SortData.xlsx”, ExcelVersion.Version2010)

        System.Diagnostics.Process.Start(“SortData.xlsx”)

    End Sub

End Module

RESULT

 

DOWNLOAD Spire.XLS for .NET Here