Pagesetup - Spire.XLS Demos

Page Setup

Page Setup (16)

Copy Excel Worksheet in Silverlight

Demo
App.xaml
App.xaml.cs
App.xaml.vb
MainPage.xaml
MainPage.xaml.cs
MainPage.xaml.vb

The sample demonstrates how to Copy Excel Worksheet in Silverlight via Spire.XLS.

<Application xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
             xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml" 
             x:Class="copySheet.App">
             
    <Application.Resources>
        
    </Application.Resources>
</Application>

using System;
using System.Windows;

namespace copySheet
{
    public partial class App : Application
    {

        public App()
        {
            this.Startup += this.Application_Startup;
            this.Exit += this.Application_Exit;
            this.UnhandledException += this.Application_UnhandledException;

            InitializeComponent();
        }

        private void Application_Startup(object sender, StartupEventArgs e)
        {
            this.RootVisual = new MainPage();
        }

        private void Application_Exit(object sender, EventArgs e)
        {

        }

        private void Application_UnhandledException(object sender, ApplicationUnhandledExceptionEventArgs e)
        {
            // If the app is running outside of the debugger then report the exception using
            // the browser's exception mechanism. On IE this will display it a yellow alert 
            // icon in the status bar and Firefox will display a script error.
            if (!System.Diagnostics.Debugger.IsAttached)
            {

                // NOTE: This will allow the application to continue running after an exception has been thrown
                // but not handled. 
                // For production applications this error handling should be replaced with something that will 
                // report the error to the website and stop the application.
                e.Handled = true;
                Deployment.Current.Dispatcher.BeginInvoke(delegate { ReportErrorToDOM(e); });
            }
        }

        private void ReportErrorToDOM(ApplicationUnhandledExceptionEventArgs e)
        {
            try
            {
                string errorMsg = e.ExceptionObject.Message + e.ExceptionObject.StackTrace;
                errorMsg = errorMsg.Replace('"', '\'').Replace("\r\n", @"\n");

                System.Windows.Browser.HtmlPage.Window.Eval("throw new Error(\"Unhandled Error in Silverlight Application " + errorMsg + "\");");
            }
            catch (Exception)
            {
            }
        }
    }
}

Imports System.Windows

Namespace copySheet
	Partial Public Class App
		Inherits Application

		Public Sub New()
			AddHandler Me.Startup, AddressOf Application_Startup
			AddHandler Me.Exit, AddressOf Application_Exit
			AddHandler Me.UnhandledException, AddressOf Application_UnhandledException

			InitializeComponent()
		End Sub

		Private Sub Application_Startup(ByVal sender As Object, ByVal e As StartupEventArgs)
			Me.RootVisual = New MainPage()
		End Sub

		Private Sub Application_Exit(ByVal sender As Object, ByVal e As EventArgs)

		End Sub

		Private Sub Application_UnhandledException(ByVal sender As Object, ByVal e As ApplicationUnhandledExceptionEventArgs)
			' If the app is running outside of the debugger then report the exception using
			' the browser's exception mechanism. On IE this will display it a yellow alert 
			' icon in the status bar and Firefox will display a script error.
			If Not Debugger.IsAttached Then

				' NOTE: This will allow the application to continue running after an exception has been thrown
				' but not handled. 
				' For production applications this error handling should be replaced with something that will 
				' report the error to the website and stop the application.
				e.Handled = True
				Deployment.Current.Dispatcher.BeginInvoke(Sub() ReportErrorToDOM(e))
			End If
		End Sub

		Private Sub ReportErrorToDOM(ByVal e As ApplicationUnhandledExceptionEventArgs)
			Try
				Dim errorMsg As String = e.ExceptionObject.Message + e.ExceptionObject.StackTrace
				errorMsg = errorMsg.Replace(""""c, "'"c).Replace(vbCrLf, vbLf)

				System.Windows.Browser.HtmlPage.Window.Eval("throw new Error(""Unhandled Error in Silverlight Application " & errorMsg & """);")
			Catch e1 As Exception
			End Try
		End Sub
	End Class
End Namespace

<UserControl x:Class="copySheet.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="300" d:DesignWidth="400">

    <Grid x:Name="LayoutRoot" Background="White" Loaded="LayoutRoot_Loaded">
        <Button Content="Run" Height="23" HorizontalAlignment="Left" Margin="296,262,0,0" Name="button1" VerticalAlignment="Top" Width="75" Click="button1_Click" />
    </Grid>
</UserControl>

using System.Windows;
using System.Windows.Controls;
using System.Reflection;
using System.IO;

using Spire.Xls;

namespace copySheet
{
    public partial class MainPage : UserControl
    {
        //create a workbook
        private Workbook workbook = null;

        //create a SaveFileDialog to save the workbook
        private SaveFileDialog saveFileDialog = null;
        public MainPage()
        {
            InitializeComponent();
            this.workbook = new Workbook();
            this.saveFileDialog = new SaveFileDialog();
            this.saveFileDialog.Filter = "Excel Documents(*.xls)|*.xls";
        }

        private void button1_Click(object sender, RoutedEventArgs e)
        {
            //create a worksheet into the workbook
            Worksheet sheetDest = this.workbook.CreateEmptySheet("Sheet2");

            //copy the first worksheet to the new worksheet
            sheetDest.CopyFrom(this.workbook.Worksheets[0]);

            //save the workbook
            bool? result = this.saveFileDialog.ShowDialog();
            if (result.HasValue && result.Value)
            {
                using (Stream saveStr = this.saveFileDialog.OpenFile())
                {
                    this.workbook.SaveToStream(saveStr);
                }
            }
        }

        private void LayoutRoot_Loaded(object sender, RoutedEventArgs e)
        {
            //load the Excel template
            Assembly assembly = this.GetType().Assembly;
            foreach (string name in assembly.GetManifestResourceNames())
            {
                if (name.EndsWith("parts.xls"))
                {
                    using (Stream fileStr = assembly.GetManifestResourceStream(name))
                    {
                        this.workbook.LoadFromStream(fileStr);
                    }
                }
            }
        }
    }
}

Imports System.Windows
Imports System.Windows.Controls
Imports System.Reflection
Imports System.IO

Imports Spire.Xls

Namespace copySheet
	Partial Public Class MainPage
		Inherits UserControl
		'create a workbook
		Private workbook As Workbook = Nothing

		'create a SaveFileDialog to save the workbook
		Private saveFileDialog As SaveFileDialog = Nothing
		Public Sub New()
			InitializeComponent()
			Me.workbook = New Workbook()
			Me.saveFileDialog = New SaveFileDialog()
			Me.saveFileDialog.Filter = "Excel Documents(*.xls)|*.xls"
		End Sub

		Private Sub button1_Click(ByVal sender As Object, ByVal e As RoutedEventArgs)
			'create a worksheet into the workbook
			Dim sheetDest As Worksheet = Me.workbook.CreateEmptySheet("Sheet2")

			'copy the first worksheet to the new worksheet
			sheetDest.CopyFrom(Me.workbook.Worksheets(0))

			'save the workbook
			Dim result? As Boolean = Me.saveFileDialog.ShowDialog()
			If result.HasValue AndAlso result.Value Then
				Using saveStr As Stream = Me.saveFileDialog.OpenFile()
					Me.workbook.SaveToStream(saveStr)
				End Using
			End If
		End Sub

		Private Sub LayoutRoot_Loaded(ByVal sender As Object, ByVal e As RoutedEventArgs)
			'load the Excel template
			Dim [assembly] As System.Reflection.Assembly = Me.GetType().Assembly
			For Each name As String In [assembly].GetManifestResourceNames()
				If name.EndsWith("parts.xls") Then
					Using fileStr As Stream = [assembly].GetManifestResourceStream(name)
						Me.workbook.LoadFromStream(fileStr)
					End Using
				End If
			Next name
		End Sub
	End Class
End Namespace

Published in Page Setup

Demo
C# source
VB.Net source

The sample demonstrates how to replace data in Excel workbook via Spire.XLS.

using Spire.Xls;
using System;

namespace Replace
{
    class Program
    {
        static void Main(string[] args)
        {
            //create a workbook
            Workbook workbook = new Workbook();
            workbook.LoadFromFile(@"..\..\..\..\Data\country.xls",ExcelVersion.Version97to2003);
            Worksheet worksheet = workbook.Worksheets[0];
           
            //replace the "South America" to "China";
            try
            {
                //find the word will be replaced
                CellRange range = worksheet.FindString("South America", false, false);
                
                //use the word "China" repalce the word just founded
                worksheet.Replace(range.Value, "China");
            }
            catch (System.Exception)
            {

                //if couldn't find the word,give a message
                Console.WriteLine("The text doesn't exist the word you want to replace");

            }

            //save the workbook
            workbook.SaveToFile("sample.xls", ExcelVersion.Version97to2003);
            System.Diagnostics.Process.Start(workbook.FileName);
        }
    }
}

Imports Spire.Xls
Imports System

Namespace Replace
	Friend Class Program
		Shared Sub Main(ByVal args() As String)
			'create a workbook
			Dim workbook As New Workbook()
			workbook.LoadFromFile("..\..\..\..\Data\country.xls",ExcelVersion.Version97to2003)
			Dim worksheet As Worksheet = workbook.Worksheets(0)

			'replace the "South America" to "China";
			Try
				'find the word will be replaced
				Dim range As CellRange = worksheet.FindString("South America", False, False)

				'use the word "China" repalce the word just founded
				worksheet.Replace(range.Value, "China")
			Catch e1 As System.Exception

				'if couldn't find the word,give a message
				Console.WriteLine("The text doesn't exist the word you want to replace")

			End Try

			'save the workbook
			workbook.SaveToFile("sample.xls", ExcelVersion.Version97to2003)
			System.Diagnostics.Process.Start(workbook.FileName)
		End Sub
	End Class
End Namespace

Published in Page Setup

Demo
C# source
VB.Net source

The sample demonstrates how to copy cell data in Excel workbook via Spire.XLS.

using Spire.Xls;

namespace CopyCellData
{
    class Program
    {
        static void Main(string[] args)
        {
            Workbook workbook = new Workbook();
            workbook.LoadFromFile(@"..\..\..\..\Data\parts.xls",ExcelVersion.Version97to2003);
            Worksheet worksheet = workbook.Worksheets[0];
           
            //copy the A3-E5 to A19-E21
            worksheet.Copy(worksheet.Range["A3:E5"],worksheet.Range["A19:E21"],true);

            workbook.SaveToFile("sample.xls", ExcelVersion.Version97to2003);
            System.Diagnostics.Process.Start(workbook.FileName);
        }
    }
}

Imports Spire.Xls

Namespace CopyCellData
	Friend Class Program
		Shared Sub Main(ByVal args() As String)
			Dim workbook As New Workbook()
			workbook.LoadFromFile("..\..\..\..\Data\parts.xls",ExcelVersion.Version97to2003)
			Dim worksheet As Worksheet = workbook.Worksheets(0)

			'copy the A3-E5 to A19-E21
			worksheet.Copy(worksheet.Range("A3:E5"),worksheet.Range("A19:E21"),True)

			workbook.SaveToFile("sample.xls", ExcelVersion.Version97to2003)
			System.Diagnostics.Process.Start(workbook.FileName)
		End Sub
	End Class
End Namespace

Published in Page Setup

Demo
C# source
VB.Net source

The sample demonstrates how to auto fit column in Excel workbook via Spire.XLS.

using Spire.Xls;

namespace AutofitColumn
{
    class Program
    {
        static void Main(string[] args)
        {
            Workbook workbook = new Workbook();
            workbook.LoadFromFile(@"..\..\..\..\Data\country.xls");
            Worksheet sheet = workbook.Worksheets[0];

            //autofit the third column
            sheet.AutoFitColumn(1);

            workbook.SaveToFile("sample.xls", ExcelVersion.Version97to2003);
            System.Diagnostics.Process.Start("sample.xls");
        }
    }
}

Imports Spire.Xls

Namespace AutofitColumn
	Friend Class Program
		Shared Sub Main(ByVal args() As String)
			Dim workbook As New Workbook()
			workbook.LoadFromFile("..\..\..\..\Data\country.xls")
			Dim sheet As Worksheet = workbook.Worksheets(0)

			'autofit the third column
			sheet.AutoFitColumn(1)

			workbook.SaveToFile("sample.xls", ExcelVersion.Version97to2003)
			System.Diagnostics.Process.Start("sample.xls")
		End Sub
	End Class
End Namespace

Published in Page Setup

Demo
C# source
VB.Net source

The sample demonstrates how to set cell fill in Excel workbook via Spire.XLS.

using Spire.Xls;
using System.Drawing;

namespace CellFill
{
    class Program
    {
        static void Main(string[] args)
        {
            //create a workbook
            Workbook workbook = new Workbook();
            Worksheet sheet = workbook.Worksheets[0];
            
            //set the color of Range["C3"] 
            sheet.Range["B3:B4"].Style.Color = Color.Yellow;
            sheet.Range["B3:B4"].Style.FillPattern = ExcelPatternType.Angle;

            //set the fillpattern of Range["D4"]
            sheet.Range["C6:C7"].Style.Color = Color.GreenYellow;
            sheet.Range["C6:C7"].Style.FillPattern = ExcelPatternType.ThickDiagonalCrosshatch;

            //set the fillpattern of Range["E5"]
            sheet.Range["D9:D10"].Style.Color = Color.BlueViolet;
            sheet.Range["D9:D10"].Style.FillPattern = ExcelPatternType.Vertical;

            //set the fillpattern of Range["A1:C2"] 
            sheet.Range["E12:F15"].Style.Color = Color.LightSkyBlue;
            sheet.Range["E12:F15"].Style.FillPattern = ExcelPatternType.ForwardDiagonal;

            //set the fillpattern of Range["G9:H9"]
            sheet.Range["G17:I21"].Style.Color = Color.Bisque;
            sheet.Range["G17:I21"].Style.FillPattern = ExcelPatternType.Percent25Gray;

            //save the workbook
            workbook.SaveToFile("sample.xls", ExcelVersion.Version97to2003);
            System.Diagnostics.Process.Start(workbook.FileName);
        }
    }
}

Imports Spire.Xls
Imports System.Drawing

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

			'set the color of Range["C3"] 
			sheet.Range("B3:B4").Style.Color = Color.Yellow
			sheet.Range("B3:B4").Style.FillPattern = ExcelPatternType.Angle

			'set the fillpattern of Range["D4"]
			sheet.Range("C6:C7").Style.Color = Color.GreenYellow
			sheet.Range("C6:C7").Style.FillPattern = ExcelPatternType.ThickDiagonalCrosshatch

			'set the fillpattern of Range["E5"]
			sheet.Range("D9:D10").Style.Color = Color.BlueViolet
			sheet.Range("D9:D10").Style.FillPattern = ExcelPatternType.Vertical

			'set the fillpattern of Range["A1:C2"] 
			sheet.Range("E12:F15").Style.Color = Color.LightSkyBlue
			sheet.Range("E12:F15").Style.FillPattern = ExcelPatternType.ForwardDiagonal

			'set the fillpattern of Range["G9:H9"]
			sheet.Range("G17:I21").Style.Color = Color.Bisque
			sheet.Range("G17:I21").Style.FillPattern = ExcelPatternType.Percent25Gray

			'save the workbook
			workbook.SaveToFile("sample.xls", ExcelVersion.Version97to2003)
			System.Diagnostics.Process.Start(workbook.FileName)
		End Sub
	End Class
End Namespace

Published in Page Setup

Demo
C# source
VB.Net source

The sample demonstrates how to design borders in Excel workbook via Spire.XLS.

using Spire.Xls;
using System.Drawing;

namespace Borders
{
    class Program
    {
        static void Main(string[] args)
        {
            //create a workbook
            Workbook workbook = new Workbook();
            Worksheet sheet = workbook.Worksheets[0];
            sheet.Range["B2"].Text = "Home Page";
            
            //set the border of Range["B2"]
            sheet.Range["B2"].Borders.LineStyle = LineStyleType.Thick;
            sheet.Range["B2"].Borders[BordersLineType.DiagonalDown].LineStyle = LineStyleType.None;
            sheet.Range["B2"].Borders[BordersLineType.DiagonalUp].LineStyle = LineStyleType.None;
            sheet.Range["B2"].Borders.Color = Color.Red;
            sheet.AutoFitColumn(2);

            //set the border of Range["C4"]
            sheet.Range["C4"].Borders.LineStyle = LineStyleType.Double;
            sheet.Range["C4"].Borders[BordersLineType.DiagonalUp].LineStyle = LineStyleType.None;
            sheet.Range["C4"].Borders[BordersLineType.DiagonalDown].LineStyle = LineStyleType.None;
            sheet.Range["C4"].Borders.Color = Color.Green;

            //set the border of Range["D6"]
            sheet.Range["D6"].Borders.LineStyle = LineStyleType.Medium;
            sheet.Range["D6"].Borders[BordersLineType.DiagonalDown].LineStyle = LineStyleType.None;
            sheet.Range["D6"].Borders[BordersLineType.DiagonalUp].LineStyle = LineStyleType.None;
            sheet.Range["D6"].Borders.Color = Color.HotPink;
           
            //set the border of Range["E8"]
            sheet.Range["E8"].Borders.LineStyle = LineStyleType.Dashed;
            sheet.Range["E8"].Borders[BordersLineType.DiagonalDown].LineStyle = LineStyleType.None;
            sheet.Range["E8"].Borders[BordersLineType.DiagonalUp].LineStyle = LineStyleType.None;
            sheet.Range["E8"].Borders.Color = Color.Yellow;
           
            //set the border of Range["F10"]
            sheet.Range["F10"].Borders.LineStyle = LineStyleType.Hair;
            sheet.Range["F10"].Borders[BordersLineType.DiagonalDown].LineStyle = LineStyleType.None;
            sheet.Range["F10"].Borders[BordersLineType.DiagonalUp].LineStyle = LineStyleType.None;
            sheet.Range["F10"].Borders.Color = Color.SeaGreen;
           
            //set the border of Range["D6"]
            sheet.Range["G12"].Borders.LineStyle = LineStyleType.SlantedDashDot;
            sheet.Range["G12"].Borders[BordersLineType.DiagonalDown].LineStyle = LineStyleType.None;
            sheet.Range["G12"].Borders[BordersLineType.DiagonalUp].LineStyle = LineStyleType.None;
            sheet.Range["G12"].Borders.Color = Color.Blue;
            workbook.SaveToFile("sample.xls", ExcelVersion.Version97to2003);
            System.Diagnostics.Process.Start(workbook.FileName);
        }
    }
}

Imports Spire.Xls
Imports System.Drawing

Namespace Borders
	Friend Class Program
		Shared Sub Main(ByVal args() As String)
			'create a workbook
			Dim workbook As New Workbook()
			Dim sheet As Worksheet = workbook.Worksheets(0)
			sheet.Range("B2").Text = "Home Page"

			'set the border of Range["B2"]
			sheet.Range("B2").Borders.LineStyle = LineStyleType.Thick
			sheet.Range("B2").Borders(BordersLineType.DiagonalDown).LineStyle = LineStyleType.None
			sheet.Range("B2").Borders(BordersLineType.DiagonalUp).LineStyle = LineStyleType.None
			sheet.Range("B2").Borders.Color = Color.Red
			sheet.AutoFitColumn(2)

			'set the border of Range["C4"]
			sheet.Range("C4").Borders.LineStyle = LineStyleType.Double
			sheet.Range("C4").Borders(BordersLineType.DiagonalUp).LineStyle = LineStyleType.None
			sheet.Range("C4").Borders(BordersLineType.DiagonalDown).LineStyle = LineStyleType.None
			sheet.Range("C4").Borders.Color = Color.Green

			'set the border of Range["D6"]
			sheet.Range("D6").Borders.LineStyle = LineStyleType.Medium
			sheet.Range("D6").Borders(BordersLineType.DiagonalDown).LineStyle = LineStyleType.None
			sheet.Range("D6").Borders(BordersLineType.DiagonalUp).LineStyle = LineStyleType.None
			sheet.Range("D6").Borders.Color = Color.HotPink

			'set the border of Range["E8"]
			sheet.Range("E8").Borders.LineStyle = LineStyleType.Dashed
			sheet.Range("E8").Borders(BordersLineType.DiagonalDown).LineStyle = LineStyleType.None
			sheet.Range("E8").Borders(BordersLineType.DiagonalUp).LineStyle = LineStyleType.None
			sheet.Range("E8").Borders.Color = Color.Yellow

			'set the border of Range["F10"]
			sheet.Range("F10").Borders.LineStyle = LineStyleType.Hair
			sheet.Range("F10").Borders(BordersLineType.DiagonalDown).LineStyle = LineStyleType.None
			sheet.Range("F10").Borders(BordersLineType.DiagonalUp).LineStyle = LineStyleType.None
			sheet.Range("F10").Borders.Color = Color.SeaGreen

			'set the border of Range["D6"]
			sheet.Range("G12").Borders.LineStyle = LineStyleType.SlantedDashDot
			sheet.Range("G12").Borders(BordersLineType.DiagonalDown).LineStyle = LineStyleType.None
			sheet.Range("G12").Borders(BordersLineType.DiagonalUp).LineStyle = LineStyleType.None
			sheet.Range("G12").Borders.Color = Color.Blue
			workbook.SaveToFile("sample.xls", ExcelVersion.Version97to2003)
			System.Diagnostics.Process.Start(workbook.FileName)
		End Sub
	End Class

Published in Page Setup

Demo
C# source
VB.Net source

The sample demonstrates how to set background color in Excel workbook via Spire.XLS.

using System.Drawing;

using Spire.Xls;

namespace BackgrandColor
{
    class Program
    {
        static void Main(string[] args)
        {
            Workbook workbook = new Workbook();
            workbook.LoadFromFile(@"..\..\..\..\Data\country.xls",ExcelVersion.Version97to2003);
            Worksheet worksheet = workbook.Worksheets[0];

            //set the backgroundcolor=LightBlue of Range["A2:E9"]
            worksheet.Range["A2:E9"].Style.Color = Color.LightBlue;

            //set the backgroundcolor=Silver of Range["A11:E18"]
            worksheet.Range["A11:E18"].Style.Color = Color.Silver;

            workbook.SaveToFile("sample.xls", ExcelVersion.Version97to2003);
            System.Diagnostics.Process.Start(workbook.FileName);
        }
    }
}

Imports System.Drawing

Imports Spire.Xls

Namespace BackgrandColor
	Class Program
		Private Shared Sub Main(args As String())
			Dim workbook As New Workbook()
			workbook.LoadFromFile("..\..\..\..\Data\country.xls", ExcelVersion.Version97to2003)
			Dim worksheet As Worksheet = workbook.Worksheets(0)

			'set the backgroundcolor=LightBlue of Range["A2:E9"]
			worksheet.Range("A2:E9").Style.Color = Color.LightBlue

			'set the backgroundcolor=Silver of Range["A11:E18"]
			worksheet.Range("A11:E18").Style.Color = Color.Silver

			workbook.SaveToFile("sample.xls", ExcelVersion.Version97to2003)
			System.Diagnostics.Process.Start(workbook.FileName)
		End Sub
	End Class
End Namespace

Published in Page Setup

Demo
C# source
VB.Net source

The sample demonstrates how to delete row in Excel workbook via Spire.XLS.

using Spire.Xls;

namespace RowDelete
{
    class Program
    {
        static void Main(string[] args)
        {
            Workbook workbook = new Workbook();
            workbook.LoadFromFile(@"..\..\..\..\Data\parts.xls");
            Worksheet sheet = workbook.Worksheets[0];
            
            //delete the second row from sheet
            sheet.DeleteRow(2);
           
            //delete the seventh and eighth rows from sheet
            sheet.DeleteRow(7,2);

            workbook.SaveToFile("sample.xls", ExcelVersion.Version97to2003);
            System.Diagnostics.Process.Start(workbook.FileName);
        }
    }
}

Imports Spire.Xls

Namespace RowDelete
	Friend Class Program
		Shared Sub Main(ByVal args() As String)
			Dim workbook As New Workbook()
			workbook.LoadFromFile("..\..\..\..\Data\parts.xls")
			Dim sheet As Worksheet = workbook.Worksheets(0)

			'delete the second row from sheet
			sheet.DeleteRow(2)

			'delete the seventh and eighth rows from sheet
			sheet.DeleteRow(7,2)

			workbook.SaveToFile("sample.xls", ExcelVersion.Version97to2003)
			System.Diagnostics.Process.Start(workbook.FileName)
		End Sub
	End Class
End Namespace

Published in Page Setup

Demo
C# source
VB.Net source

The sample demonstrates how to delete workbook column in Excel workbook via Spire.XLS.

using Spire.Xls;

namespace DeleteColumn
{
    class Program
    {
        static void Main(string[] args)
        {
            Workbook workbook = new Workbook();
            workbook.LoadFromFile(@"..\..\..\..\Data\vendors.xls",ExcelVersion.Version97to2003);
            Worksheet sheet = workbook.Worksheets[0];
 
            //delete the first column of sheet
            sheet.DeleteColumn(1); 

            workbook.SaveToFile("sample.xls", ExcelVersion.Version97to2003);
            System.Diagnostics.Process.Start(workbook.FileName);
        }
    }
}

Imports Spire.Xls

Namespace DeleteColumn
	Friend Class Program
		Shared Sub Main(ByVal args() As String)
			Dim workbook As New Workbook()
			workbook.LoadFromFile("..\..\..\..\Data\vendors.xls",ExcelVersion.Version97to2003)
			Dim sheet As Worksheet = workbook.Worksheets(0)

			'delete the first column of sheet
			sheet.DeleteColumn(1)

			workbook.SaveToFile("sample.xls", ExcelVersion.Version97to2003)
			System.Diagnostics.Process.Start(workbook.FileName)
		End Sub
	End Class
End Namespace

Published in Page Setup

Demo
C# source
VB.Net source

The sample demonstrates how to set row height in Excel workbook via Spire.XLS.

using Spire.Xls;

namespace SetRowHeight
{
    class Program
    {
        static void Main(string[] args)
        {
            Workbook workbook = new Workbook();
            workbook.LoadFromFile(@"..\..\..\..\Data\parts.xls");
            Worksheet sheet = workbook.Worksheets[0];
            
            //set the RowHeight=25 of the first row
            sheet.SetRowHeight(1, 25);

            workbook.SaveToFile("sample.xls", ExcelVersion.Version97to2003);
            System.Diagnostics.Process.Start(workbook.FileName);
        }
    }
}

Imports Spire.Xls

Namespace SetRowHeight
	Friend Class Program
		Shared Sub Main(ByVal args() As String)
			Dim workbook As New Workbook()
			workbook.LoadFromFile("..\..\..\..\Data\parts.xls")
			Dim sheet As Worksheet = workbook.Worksheets(0)

			'set the RowHeight=25 of the first row
			sheet.SetRowHeight(1, 25)

			workbook.SaveToFile("sample.xls", ExcelVersion.Version97to2003)
			System.Diagnostics.Process.Start(workbook.FileName)
		End Sub
	End Class
End Namespace

Published in Page Setup

Demo
C# source
VB.Net source

The sample demonstrates how to hide worksheet row in Excel workbook via Spire.XLS.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using Spire.Xls;

namespace RowHide
{
    class Program
    {
        static void Main(string[] args)
        {
            Workbook workbook = new Workbook();
            workbook.LoadFromFile(@"..\..\..\..\Data\parts.xls");
            Worksheet sheet = workbook.Worksheets[0];

            //hide the fourth row of sheet
            sheet.HideRow(4);

            //hide the seventh row of sheet
            sheet.HideRow(7);

            workbook.SaveToFile("sample.xls", ExcelVersion.Version97to2003);
            System.Diagnostics.Process.Start(workbook.FileName);
        }
    }
}

Imports System
Imports System.Collections.Generic
Imports System.Linq
Imports System.Text

Imports Spire.Xls

Namespace RowHide
	Friend Class Program
		Shared Sub Main(ByVal args() As String)
			Dim workbook As New Workbook()
			workbook.LoadFromFile("..\..\..\..\Data\parts.xls")
			Dim sheet As Worksheet = workbook.Worksheets(0)

			'hide the fourth row of sheet
			sheet.HideRow(4)

			'hide the seventh row of sheet
			sheet.HideRow(7)

			workbook.SaveToFile("sample.xls", ExcelVersion.Version97to2003)
			System.Diagnostics.Process.Start(workbook.FileName)
		End Sub
	End Class
End Namespace

Published in Page Setup

Demo
C# source
VB.Net source

The sample demonstrates how to hide worksheet column in Excel workbook via Spire.XLS.

using Spire.Xls;

namespace HideColumn
{
    class Program
    {
        static void Main(string[] args)
        {
            Workbook workbook = new Workbook();
            workbook.LoadFromFile(@"..\..\..\..\Data\vendors.xls");
            Worksheet sheet = workbook.Worksheets[0];
            
            //hide the first column  of sheet
            sheet.HideColumn(1);

           //hide the sixth column of sheet
            sheet.HideColumn(6);
            
            workbook.SaveToFile("sample.xls", ExcelVersion.Version97to2003);
            System.Diagnostics.Process.Start(workbook.FileName);
        }
    }
}

Imports Spire.Xls

Namespace HideColumn
	Friend Class Program
		Shared Sub Main(ByVal args() As String)
			Dim workbook As New Workbook()
			workbook.LoadFromFile("..\..\..\..\Data\vendors.xls")
			Dim sheet As Worksheet = workbook.Worksheets(0)

			'hide the first column  of sheet
			sheet.HideColumn(1)

		   'hide the sixth column of sheet
			sheet.HideColumn(6)

			workbook.SaveToFile("sample.xls", ExcelVersion.Version97to2003)
			System.Diagnostics.Process.Start(workbook.FileName)
		End Sub
	End Class
End Namespace

Published in Page Setup

Demo
C# source
VB.Net source

The sample demonstrates how to add worksheet in Excel workbook via Spire.XLS.

using System.Drawing;
using System.Data;
using System.Data.OleDb;

using Spire.Xls;

namespace AddSheet
{
    class Program
    {
        static void Main(string[] args)
        {
            Workbook workbook = new Workbook();
            workbook.LoadFromFile(@"..\..\..\..\Data\parts.xls");

            //add one worksheet to workbook
            workbook.CreateEmptySheet("NewSheet");
            
           //set the new sheet's context
            Worksheet sheet = workbook.Worksheets[1];
            string connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=..\\..\\..\\..\\Data\\database.accdb;Persist Security Info=False;";
            string sqlCountry="select * from vendors";
            DataTable vendor = new DataTable();
            using (OleDbConnection conn = new OleDbConnection(connString))
            {
                OleDbDataAdapter adapter = new OleDbDataAdapter(sqlCountry, conn);
                adapter.Fill(vendor);
            }
            sheet.InsertDataTable(vendor, true, 1, 1);

            //set the style of the new sheet
            sheet.AllocatedRange.AutoFitColumns();
            sheet.Range[1, 1, 1, vendor.Columns.Count].Style.Color = Color.DeepSkyBlue;
            sheet.Range[1, 1, 1, vendor.Columns.Count].Style.Font.IsBold = true;
            sheet.Range[1, 1, 1, vendor.Columns.Count].HorizontalAlignment = HorizontalAlignType.Center;
            sheet.Range[2, 1, vendor.Rows.Count + 1, vendor.Columns.Count].Style.Color = Color.SkyBlue;
            sheet.AllocatedRange.Style.Borders.LineStyle = LineStyleType.Thin;
            sheet.AllocatedRange.Borders[BordersLineType.DiagonalDown].LineStyle = LineStyleType.None;
            sheet.AllocatedRange.Borders[BordersLineType.DiagonalUp].LineStyle = LineStyleType.None;
                 
            workbook.SaveToFile("sample.xls", ExcelVersion.Version97to2003);
            System.Diagnostics.Process.Start(workbook.FileName);
        }
    }
}

Imports System.Drawing
Imports System.Data
Imports System.Data.OleDb

Imports Spire.Xls

Namespace AddSheet
	Class Program
		Private Shared Sub Main(args As String())
			Dim workbook As New Workbook()
			workbook.LoadFromFile("..\..\..\..\Data\parts.xls")

			'add one worksheet to workbook
			workbook.CreateEmptySheet("NewSheet")

			'set the new sheet's context
			Dim sheet As Worksheet = workbook.Worksheets(1)
			Dim connString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=..\..\..\..\Data\database.accdb;Persist Security Info=False;"
			Dim sqlCountry As String = "select * from vendors"
			Dim vendor As New DataTable()
			Using conn As New OleDbConnection(connString)
				Dim adapter As New OleDbDataAdapter(sqlCountry, conn)
				adapter.Fill(vendor)
			End Using
			sheet.InsertDataTable(vendor, True, 1, 1)

			'set the style of the new sheet
			sheet.AllocatedRange.AutoFitColumns()
			sheet.Range(1, 1, 1, vendor.Columns.Count).Style.Color = Color.DeepSkyBlue
			sheet.Range(1, 1, 1, vendor.Columns.Count).Style.Font.IsBold = True
			sheet.Range(1, 1, 1, vendor.Columns.Count).HorizontalAlignment = HorizontalAlignType.Center
			sheet.Range(2, 1, vendor.Rows.Count + 1, vendor.Columns.Count).Style.Color = Color.SkyBlue
			sheet.AllocatedRange.Style.Borders.LineStyle = LineStyleType.Thin
			sheet.AllocatedRange.Borders(BordersLineType.DiagonalDown).LineStyle = LineStyleType.None
			sheet.AllocatedRange.Borders(BordersLineType.DiagonalUp).LineStyle = LineStyleType.None

			workbook.SaveToFile("sample.xls", ExcelVersion.Version97to2003)
			System.Diagnostics.Process.Start(workbook.FileName)
		End Sub
	End Class
End Namespace

Published in Page Setup

Demo
C# source
VB.Net source

The sample demonstrates how to add workbook row via Spire.XLS.

using System.Drawing;
using System.Data;
using System.Data.OleDb;
using Spire.Xls;

namespace AddRow
{
    class Program
    {
        static void Main(string[] args)
        {
            Workbook workbook = new Workbook();
            workbook.LoadFromFile(@"..\..\..\..\Data\parts.xls");
            Worksheet sheet = workbook.Worksheets[0];
            
            //add 3 rows in sheet
            sheet.InsertRow(3,3);
            
            //set the new rows' style
            sheet.Range["A3:G5"].Style.Color = Color.GreenYellow;
            sheet.Range["A3:G5"].Style.Borders.LineStyle = LineStyleType.Thin;
            sheet.Range["A3:G5"].Borders[BordersLineType.DiagonalDown].LineStyle = LineStyleType.None;
            sheet.Range["A3:G5"].Borders[BordersLineType.DiagonalUp].LineStyle = LineStyleType.None;
            sheet.Range["A3:B5"].HorizontalAlignment = HorizontalAlignType.Center;
            sheet.Range["C3:C5"].HorizontalAlignment = HorizontalAlignType.Left;
            sheet.Range["D3:G5"].HorizontalAlignment = HorizontalAlignType.Right;
            
            //add one row in sheet
            sheet.InsertRow(7);
            
            //set the style of the added row
            sheet.Range["A7:G7"].Style.Color = Color.GreenYellow;
            sheet.Range["A7:G7"].Style.Borders.LineStyle = LineStyleType.Thin;
            sheet.Range["A7:G7"].Borders[BordersLineType.DiagonalDown].LineStyle = LineStyleType.None;
            sheet.Range["A7:G7"].Borders[BordersLineType.DiagonalUp].LineStyle = LineStyleType.None;
            sheet.Range["A7:B7"].HorizontalAlignment = HorizontalAlignType.Center;
            sheet.Range["C7"].HorizontalAlignment = HorizontalAlignType.Left;
            sheet.Range["D7:G7"].HorizontalAlignment = HorizontalAlignType.Right;

           //insert data into the new rows
            DataTable parts = GetData();
            for (int i = 3,datarowIndex=8; i <= 5;datarowIndex++, i++)
            {
                for (int j = 1; j <= parts.Columns.Count; j++)
                {
                    sheet.Range[i, j].Value2 = parts.Rows[datarowIndex][j - 1];
                }
            }

            for (int j = 1; j <= parts.Columns.Count; j++)
            {
                sheet.Range[7, j].Value2 = parts.Rows[6][j - 1];
            }
            
           
            workbook.SaveToFile("sample.xls", ExcelVersion.Version97to2003);
            System.Diagnostics.Process.Start(workbook.FileName);
        }

        static private DataTable GetData()
        {
            string connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=..\\..\\..\\..\\Data\\database.accdb;Persist Security Info=False;";
            string sqlParts = "select * from parts";
            DataTable parts = new DataTable();
            using(OleDbConnection conn=new OleDbConnection(connString))
            {
                OleDbDataAdapter adapter = new OleDbDataAdapter(sqlParts, conn);
                adapter.Fill(parts);
            }
            return parts;
        }
    }
}

Imports System.Drawing
Imports System.Data
Imports System.Data.OleDb
Imports Spire.Xls

Namespace AddRow
	Friend Class Program
		Shared Sub Main(ByVal args() As String)
			Dim workbook As New Workbook()
			workbook.LoadFromFile("..\..\..\..\Data\parts.xls")
			Dim sheet As Worksheet = workbook.Worksheets(0)

			'add 3 rows in sheet
			sheet.InsertRow(3,3)

			'set the new rows' style
			sheet.Range("A3:G5").Style.Color = Color.GreenYellow
			sheet.Range("A3:G5").Style.Borders.LineStyle = LineStyleType.Thin
			sheet.Range("A3:G5").Borders(BordersLineType.DiagonalDown).LineStyle = LineStyleType.None
			sheet.Range("A3:G5").Borders(BordersLineType.DiagonalUp).LineStyle = LineStyleType.None
			sheet.Range("A3:B5").HorizontalAlignment = HorizontalAlignType.Center
			sheet.Range("C3:C5").HorizontalAlignment = HorizontalAlignType.Left
			sheet.Range("D3:G5").HorizontalAlignment = HorizontalAlignType.Right

			'add one row in sheet
			sheet.InsertRow(7)

			'set the style of the added row
			sheet.Range("A7:G7").Style.Color = Color.GreenYellow
			sheet.Range("A7:G7").Style.Borders.LineStyle = LineStyleType.Thin
			sheet.Range("A7:G7").Borders(BordersLineType.DiagonalDown).LineStyle = LineStyleType.None
			sheet.Range("A7:G7").Borders(BordersLineType.DiagonalUp).LineStyle = LineStyleType.None
			sheet.Range("A7:B7").HorizontalAlignment = HorizontalAlignType.Center
			sheet.Range("C7").HorizontalAlignment = HorizontalAlignType.Left
			sheet.Range("D7:G7").HorizontalAlignment = HorizontalAlignType.Right

		   'insert data into the new rows
			Dim parts As DataTable = GetData()
			Dim i As Integer = 3
			Dim datarowIndex As Integer=8
			Do While i <= 5
				For j As Integer = 1 To parts.Columns.Count
					sheet.Range(i, j).Value2 = parts.Rows(datarowIndex)(j - 1)
				Next j
				datarowIndex += 1
				i += 1
			Loop

			For j As Integer = 1 To parts.Columns.Count
				sheet.Range(7, j).Value2 = parts.Rows(6)(j - 1)
			Next j


			workbook.SaveToFile("sample.xls", ExcelVersion.Version97to2003)
			System.Diagnostics.Process.Start(workbook.FileName)
		End Sub

		Private Shared Function GetData() As DataTable
			Dim connString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=..\..\..\..\Data\database.accdb;Persist Security Info=False;"
			Dim sqlParts As String = "select * from parts"
			Dim parts As New DataTable()
			Using conn As New OleDbConnection(connString)
				Dim adapter As New OleDbDataAdapter(sqlParts, conn)
				adapter.Fill(parts)
			End Using
			Return parts
		End Function
	End Class
End Namespace

Published in Page Setup

Demo
C# source
VB.Net source

The sample demonstrates how to Add Column in Excel workbook via Spire.XLS.

using System.Drawing;
using System.Data;
using System.Data.OleDb;
using Spire.Xls;

namespace AddColumn
{
    class Program
    {
        static void Main(string[] args)
        {
            Workbook workbook = new Workbook();
            workbook.LoadFromFile(@"..\..\..\..\Data\parts-column.xls");
            Worksheet sheet = workbook.Worksheets[0];

            //add one column
            sheet.InsertColumn(3);

            //get the data of the new column
            DataColumn dataColumn = GetColumnData(4);

            //insert the data into the new column
            sheet.InsertDataColumn(dataColumn, true, 1, 3);

            //add two columns
            sheet.InsertColumn(5, 2);

            //get the data of the two new columns
            DataColumn dataColumn4 = GetColumnData(5);
            DataColumn dataColumn5 = GetColumnData(6);

            //insert the data into the new columns
            sheet.InsertDataColumn(dataColumn4, true, 1, 5);
            sheet.InsertDataColumn(dataColumn5, true, 1, 6);

            int rowCount = 0;
            rowCount = sheet.Rows.Length;
            //set the added columns's style
            SetFormat(sheet, 3,rowCount);
            SetFormat(sheet, 5,rowCount);
            SetFormat(sheet, 6,rowCount);

            workbook.SaveToFile("sample.xls", ExcelVersion.Version97to2003);
            System.Diagnostics.Process.Start(workbook.FileName);
        }
        static private DataColumn GetColumnData(int columnIndex)
        {
            string connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=..\\..\\..\\..\\Data\\database.accdb;Persist Security Info=False;";
            DataTable parts = new DataTable();
            using (OleDbConnection conn = new OleDbConnection(connString))
            {
                string sqlParts = "select * from parts";
                OleDbDataAdapter adapter = new OleDbDataAdapter(sqlParts, conn);
                adapter.Fill(parts);
            }
            DataColumn column = parts.Columns[columnIndex];
            return column;
        }

        static private void SetFormat(Worksheet sheet, int columnIndex,int rowCount)
        {
            //set the header style
            sheet.Range[1, columnIndex].Style.Font.IsBold = true;
            sheet.Range[1, columnIndex].Style.Color = Color.DeepSkyBlue;
            sheet.Range[1, columnIndex].HorizontalAlignment = HorizontalAlignType.Center;
            sheet.Range[1, columnIndex].AutoFitColumns();

            //draw the lines
            sheet.Range[1, columnIndex, rowCount-1, columnIndex].Style.Borders.LineStyle = LineStyleType.Thin;
            sheet.Range[1, columnIndex, rowCount-1, columnIndex].Borders[BordersLineType.DiagonalDown].LineStyle = LineStyleType.None;
            sheet.Range[1, columnIndex, rowCount-1, columnIndex].Borders[BordersLineType.DiagonalUp].LineStyle = LineStyleType.None;

            //set the data rows's color
            sheet.Range[2, columnIndex, rowCount-1, columnIndex].Style.Color = Color.SkyBlue;
        }
    }
}

Imports System.Drawing
Imports System.Data
Imports System.Data.OleDb
Imports Spire.Xls

Namespace AddColumn
	Friend Class Program
		Shared Sub Main(ByVal args() As String)
			Dim workbook As New Workbook()
			workbook.LoadFromFile("..\..\..\..\Data\parts-column.xls")
			Dim sheet As Worksheet = workbook.Worksheets(0)

			'add one column
			sheet.InsertColumn(3)

			'get the data of the new column
			Dim dataColumn As DataColumn = GetColumnData(4)

			'insert the data into the new column
			sheet.InsertDataColumn(dataColumn, True, 1, 3)

			'add two columns
			sheet.InsertColumn(5, 2)

			'get the data of the two new columns
			Dim dataColumn4 As DataColumn = GetColumnData(5)
			Dim dataColumn5 As DataColumn = GetColumnData(6)

			'insert the data into the new columns
			sheet.InsertDataColumn(dataColumn4, True, 1, 5)
			sheet.InsertDataColumn(dataColumn5, True, 1, 6)

			Dim rowCount As Integer = 0
			rowCount = sheet.Rows.Length
			'set the added columns's style
			SetFormat(sheet, 3,rowCount)
			SetFormat(sheet, 5,rowCount)
			SetFormat(sheet, 6,rowCount)

			workbook.SaveToFile("sample.xls", ExcelVersion.Version97to2003)
			System.Diagnostics.Process.Start(workbook.FileName)
		End Sub
		Private Shared Function GetColumnData(ByVal columnIndex As Integer) As DataColumn
			Dim connString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=..\..\..\..\Data\database.accdb;Persist Security Info=False;"
			Dim parts As New DataTable()
			Using conn As New OleDbConnection(connString)
				Dim sqlParts As String = "select * from parts"
				Dim adapter As New OleDbDataAdapter(sqlParts, conn)
				adapter.Fill(parts)
			End Using
			Dim column As DataColumn = parts.Columns(columnIndex)
			Return column
		End Function

		Private Shared Sub SetFormat(ByVal sheet As Worksheet, ByVal columnIndex As Integer, ByVal rowCount As Integer)
			'set the header style
			sheet.Range(1, columnIndex).Style.Font.IsBold = True
			sheet.Range(1, columnIndex).Style.Color = Color.DeepSkyBlue
			sheet.Range(1, columnIndex).HorizontalAlignment = HorizontalAlignType.Center
			sheet.Range(1, columnIndex).AutoFitColumns()

			'draw the lines
			sheet.Range(1, columnIndex, rowCount-1, columnIndex).Style.Borders.LineStyle = LineStyleType.Thin
			sheet.Range(1, columnIndex, rowCount-1, columnIndex).Borders(BordersLineType.DiagonalDown).LineStyle = LineStyleType.None
			sheet.Range(1, columnIndex, rowCount-1, columnIndex).Borders(BordersLineType.DiagonalUp).LineStyle = LineStyleType.None

			'set the data rows's color
			sheet.Range(2, columnIndex, rowCount-1, columnIndex).Style.Color = Color.SkyBlue
		End Sub
	End Class
End Namespace

Published in Page Setup

Demo
C# source
VB.Net source

The sample demonstrates how to work with page setup in an excel workbook.

		private void btnRun_Click(object sender, System.EventArgs e)
		{
			Workbook workbook = new Workbook();
			
			//Initialize worksheet
			workbook.CreateEmptySheets(1);
			Worksheet sheet = workbook.Worksheets[0];
			sheet.GridLinesVisible = false;

			//Writes sample data
			CreateSampleData(sheet);

			//Sets page setup properties
			sheet.PageSetup.Orientation = PageOrientationType.Landscape;
			sheet.PageSetup.PaperSize = PaperSizeType.PaperA3;

			//Sets page break
			sheet.VPageBreaks.Add(sheet.Range["A5"]);
			sheet.HPageBreaks.Add(sheet.Range["C6"]);

			workbook.SaveToFile("Sample.xls");
			ExcelDocViewer(workbook.FileName);
		}

		private void CreateSampleData(Worksheet sheet)
		{
			//Country
			sheet.Range["A1"].Value = "Country";
			sheet.Range["A2"].Value = "Cuba";
			sheet.Range["A3"].Value = "Mexico";
			sheet.Range["A4"].Value = "France";
			sheet.Range["A5"].Value = "German";

			//Jun
			sheet.Range["B1"].Value = "Jun";
			sheet.Range["B2"].NumberValue = 6000;
			sheet.Range["B3"].NumberValue = 8000;
			sheet.Range["B4"].NumberValue = 9000;
			sheet.Range["B5"].NumberValue = 8500;

			//Aug
			sheet.Range["C1"].Value = "Aug";
			sheet.Range["C2"].NumberValue = 3000;
			sheet.Range["C3"].NumberValue = 2000;
			sheet.Range["C4"].NumberValue = 2300;
			sheet.Range["C5"].NumberValue = 4200;

			//Style
			sheet.Range["A1:C1"].Style.Font.IsBold = true;
			sheet.Range["A2:C2"].Style.KnownColor = ExcelColors.LightYellow;
			sheet.Range["A3:C3"].Style.KnownColor = ExcelColors.LightGreen1;
			sheet.Range["A4:C4"].Style.KnownColor = ExcelColors.LightOrange;
			sheet.Range["A5:C5"].Style.KnownColor = ExcelColors.LightTurquoise;

			//Border
			sheet.Range["A1:C5"].Style.Borders[BordersLineType.EdgeTop].Color = Color.FromArgb(0, 0, 128);
			sheet.Range["A1:C5"].Style.Borders[BordersLineType.EdgeTop].LineStyle = LineStyleType.Thin;
			sheet.Range["A1:C5"].Style.Borders[BordersLineType.EdgeBottom].Color = Color.FromArgb(0, 0, 128);
			sheet.Range["A1:C5"].Style.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Thin;
			sheet.Range["A1:C5"].Style.Borders[BordersLineType.EdgeLeft].Color = Color.FromArgb(0, 0, 128);
			sheet.Range["A1:C5"].Style.Borders[BordersLineType.EdgeLeft].LineStyle = LineStyleType.Thin;
			sheet.Range["A1:C5"].Style.Borders[BordersLineType.EdgeRight].Color = Color.FromArgb(0, 0, 128);
			sheet.Range["A1:C5"].Style.Borders[BordersLineType.EdgeRight].LineStyle = LineStyleType.Thin;

			sheet.Range["B2:C5"].Style.NumberFormat = "\"$\"#,##0";
		}

		private void ExcelDocViewer( string fileName )
		{
			try
			{
				System.Diagnostics.Process.Start(fileName);
			}
			catch{}
		}

		Private Sub btnRun_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnRun.Click
			Dim workbook As Workbook = New Workbook()

			'Initialize worksheet
			workbook.CreateEmptySheets(1)
			Dim sheet As Worksheet = workbook.Worksheets(0)
			sheet.GridLinesVisible = False

			'Writes sample data
			CreateSampleData(sheet)

			'Sets page setup properties
			sheet.PageSetup.Orientation = PageOrientationType.Landscape
			sheet.PageSetup.PaperSize = PaperSizeType.PaperA3

			'Sets page break
			sheet.VPageBreaks.Add(sheet.Range("A5"))
			sheet.HPageBreaks.Add(sheet.Range("C6"))

			workbook.SaveToFile("Sample.xls")
			ExcelDocViewer(workbook.FileName)
		End Sub

		Private Sub CreateSampleData(ByVal sheet As Worksheet)
			'Country
			sheet.Range("A1").Value = "Country"
			sheet.Range("A2").Value = "Cuba"
			sheet.Range("A3").Value = "Mexico"
			sheet.Range("A4").Value = "France"
			sheet.Range("A5").Value = "German"

			'Jun
			sheet.Range("B1").Value = "Jun"
			sheet.Range("B2").NumberValue = 6000
			sheet.Range("B3").NumberValue = 8000
			sheet.Range("B4").NumberValue = 9000
			sheet.Range("B5").NumberValue = 8500

			'Aug
			sheet.Range("C1").Value = "Aug"
			sheet.Range("C2").NumberValue = 3000
			sheet.Range("C3").NumberValue = 2000
			sheet.Range("C4").NumberValue = 2300
			sheet.Range("C5").NumberValue = 4200

			'Style
			sheet.Range("A1:C1").Style.Font.IsBold = True
			sheet.Range("A2:C2").Style.KnownColor = ExcelColors.LightYellow
			sheet.Range("A3:C3").Style.KnownColor = ExcelColors.LightGreen1
			sheet.Range("A4:C4").Style.KnownColor = ExcelColors.LightOrange
			sheet.Range("A5:C5").Style.KnownColor = ExcelColors.LightTurquoise

			'Border
			sheet.Range("A1:C5").Style.Borders(BordersLineType.EdgeTop).Color = Color.FromArgb(0, 0, 128)
			sheet.Range("A1:C5").Style.Borders(BordersLineType.EdgeTop).LineStyle = LineStyleType.Thin
			sheet.Range("A1:C5").Style.Borders(BordersLineType.EdgeBottom).Color = Color.FromArgb(0, 0, 128)
			sheet.Range("A1:C5").Style.Borders(BordersLineType.EdgeBottom).LineStyle = LineStyleType.Thin
			sheet.Range("A1:C5").Style.Borders(BordersLineType.EdgeLeft).Color = Color.FromArgb(0, 0, 128)
			sheet.Range("A1:C5").Style.Borders(BordersLineType.EdgeLeft).LineStyle = LineStyleType.Thin
			sheet.Range("A1:C5").Style.Borders(BordersLineType.EdgeRight).Color = Color.FromArgb(0, 0, 128)
			sheet.Range("A1:C5").Style.Borders(BordersLineType.EdgeRight).LineStyle = LineStyleType.Thin

			sheet.Range("B2:C5").Style.NumberFormat = """$""#,##0"
		End Sub

		Private Sub ExcelDocViewer(ByVal fileName As String)
			Try
				System.Diagnostics.Process.Start(fileName)
			Catch
			End Try
		End Sub

Published in Page Setup

Tagged under

News Category

Page Setup (16)

Copy Excel Worksheet in Silverlight

Replace Data in Excel in C#, VB.NET

Copy Cell Data in Excel in C#, VB.NET

AutoFit Column in Exce in C#, VB.NET

Set Cell Fill in Excel in C#, VB.NET

Design Borders in Excel in C#, VB.NET

Set Background Color in Excel in C#, VB.NET

Delete Row in Excel in C#, VB.NET

Delete Column in Excel in C#, VB.NET

Set Row Height in Excel Workbook in C#, VB.NET

Hide Worksheet Row in Excel in C#, VB.NET

Hide Worksheet Column in Excel in C#, VB.NET

Add Worksheet in Excel Workbook in C#, VB.NET

Add Row in Excel Workbook in C#, VB.NET

Add Column in Excel Workbook in C#, VB.NET

EXCEL Pagesetup in C#, VB.NET