How to Add Excel Borders with Silverlight

Excel worksheets are formed with cells. Users import data in these cells to save and display. In order to have a good layout, users may set format for these cells, for example, add borders.

With borders, data in cells will be shown more clearly. Also, border plays an important role when users want to print one Excel file. Generally speaking, border includes border line and color. Users can choose border types and colors according to requirement.

In this post, I will introduce a method about how to add borders for Excel cells with Silverlight.

At the beginning, I have prepared an Excel file. I will add borders for some cells. Also, Spire.XLS, a component specialized in operating Excel, is used in this example.

STEPS:

Step 1. Add Excel File as Embedded Source

After creating project and adding Spire.XLS dll file as reference, right click project name and then choose Add. Click existed item to get Excel file which I have prepared. Then, we can find that this file has been added in project. Click it and set change Build Action as Embedded Resource as following picture.

Step 2. Design User Control

Open MainPage.xaml and then design user control. Firstly, add a label and write contents which are about what I will do. Secondly, add a button to click to run program.

MainPage.xaml

<UserControlx:Class="CellBorders.MainPage"

    xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"

    xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"

    xmlns:d="http://schemas.microsoft.com/expression/blend/2008"

    xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"

    mc:Ignorable="d"

    d:DesignHeight="198"d:DesignWidth="329"xmlns:sdk="http://schemas.microsoft.com/winfx/2006/xaml/presentation/sdk"Loaded="LayoutRoot_Loaded">

    <Gridx:Name="LayoutRoot"Height="292"Width="329"Loaded="LayoutRoot_Loaded">

        <Grid.ColumnDefinitions>

            <ColumnDefinitionWidth="*" />

            <ColumnDefinitionWidth="481*" />

        </Grid.ColumnDefinitions>

        <sdk:LabelGrid.Column="1"Height="45"HorizontalAlignment="Left"Margin="23,59,0,0"Name="label1"VerticalAlignment="Top"Width="293"Content="Add Borders for Excel Cells"FontFamily="Arial Black"FontSize="18"FontWeight="Bold"FontStretch="Normal"Foreground="White" />

        <ButtonContent="RUN"Grid.Column="1"Height="33"HorizontalAlignment="Left"Margin="221,128,0,0"Name="button1"VerticalAlignment="Top"Width="73"FontWeight="Normal"Click="button1_Click" />

        <Grid.Background>

            <LinearGradientBrushEndPoint="0.5,1"StartPoint="0.5,0">

                <GradientStopColor="#FF897EAC"Offset="0" />

                <GradientStopColor="White"Offset="1" />

            </LinearGradientBrush>

        </Grid.Background>

    </Grid>

</UserControl>

Then, declare a new workbook for using it following. Also, because of security of Silverlight, file cannot be saved directly so that we need to declare a save file dialog for saving the encrypted file.

        private SaveFileDialog saveFiledialog = null;

        private Workbook workbook = new Workbook();

        public MainPage()

        {

            InitializeComponent();

            this.saveFiledialog = new SaveFileDialog();

            this.saveFiledialog.Filter = "Excel Document(*.xlsx)|*.xlsx";

        }

Step 3. Load Excel File

Click user control and then click Events. Find Loaded. Double click it to write code for loading the embedded Excel file. Declare an assembly and then use foreach sentence to get recourse name in this assembly. Then, use if sentence to judge whether the resource name is Excel file name or not. If so, use this.workbook.LoadFromStream() method to load this file.

        private void LayoutRoot_Loaded(object sender, RoutedEventArgs e)

        {

            Assembly assembly = this.GetType().Assembly;

            foreach(string name in assembly.GetManifestResourceNames())

            {

                if (name.EndsWith("Customers.xlsx"))

                {

                    using(Stream stream =assembly.GetManifestResourceStream(name))

                    {

                        this.workbook.LoadFromStream(stream, ExcelVersion.Version2010);

                    }

                }

            }

        }

Step 4. Add Borders

Initialize worksheet firstly. Add borders for specified cells and then set borders format, including color and line style. In this example, I set border color as cyan and border style as double. By default, borders include top, bottom, right, left and diagonal lines. So, I will set diagonal lines type as none to avoid them appearing in cells.

            Worksheet worksheet = this.workbook.Worksheets[0];

            worksheet.Range["A6:F8"].Style.Borders.Color = Colors.DarkGray;

            worksheet.Range["A6:F8"].Style.Borders.LineStyle = LineStyleType.Double;

            worksheet.Range["A6:F8"].Style.Borders[BordersLineType.DiagonalDown].LineStyle = LineStyleType.None;

            worksheet.Range["A6:F8"].Style.Borders[BordersLineType.DiagonalUp].LineStyle = LineStyleType.None;

Step 5. Save File

Judge if the save file dialogue box can be pops up, if so, save this new Excel file with borders to location which you choose.

            bool? result = this.saveFiledialog.ShowDialog();

            if (result.HasValue && result.Value)

            {

                using (Stream stream = this.saveFiledialog.OpenFile())

                {

                    this.workbook.SaveToStream(stream);

                }

            }

Full MainPage.xaml.cs

using System.Windows;

using System.Windows.Controls;

using System.Windows.Media;

using System.Reflection;

using System.IO;

using Spire.Xls;

namespace CellBorders

{

    public partial class MainPage : UserControl

    {

        private SaveFileDialog saveFiledialog = null;

        private Workbook workbook = new Workbook();

        public MainPage()

        {

            InitializeComponent();

            this.saveFiledialog = new SaveFileDialog();

            this.saveFiledialog.Filter = "Excel Document(*.xlsx)|*.xlsx";

        }

        private void LayoutRoot_Loaded(object sender, RoutedEventArgs e)

        {

            Assembly assembly = this.GetType().Assembly;

            foreach(string name in assembly.GetManifestResourceNames())

            {

                if (name.EndsWith("Customers.xlsx"))

                {

                    using(Stream stream =assembly.GetManifestResourceStream(name))

                    {

                        this.workbook.LoadFromStream(stream, ExcelVersion.Version2010);

                    }

                }

            }

        }

        private void button1_Click(object sender, RoutedEventArgs e)

        {

            Worksheet worksheet = this.workbook.Worksheets[0];

            worksheet.Range["A6:F8"].Style.Borders.Color = Colors.DarkGray;

            worksheet.Range["A6:F8"].Style.Borders.LineStyle = LineStyleType.Double;

            worksheet.Range["A6:F8"].Style.Borders[BordersLineType.DiagonalDown].LineStyle = LineStyleType.None;

            worksheet.Range["A6:F8"].Style.Borders[BordersLineType.DiagonalUp].LineStyle = LineStyleType.None;

            bool? result = this.saveFiledialog.ShowDialog();

            if (result.HasValue && result.Value)

            {

                using (Stream stream = this.saveFiledialog.OpenFile())

                {

                    this.workbook.SaveToStream(stream);

                }

            }

        }

    }

}

RESULT

___________________________________________________________________________________________

Click Here to LEARN MORE about Spire.XLS

Click Here to DOWNLOAD Spire.XLS

Spire.Office also can be used to realize this function.