Worksheet

Worksheet (16)

Tuesday, 17 September 2019 07:04

Apply a Style to an Entire Excel Worksheet in C#

Written by support iceblue

Starting from version 9.9.5, Spire.XLS supports applying style to an entire excel worksheet. This article will show you how to apply a style to an entire excel worksheet using Spire.XLS.

Detail steps:

Step 1: Instantiate a Workbook object 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: Create a cell style, specify the cell background color, font color and font size.

CellStyle style = workbook.Styles.Add("newStyle");
style.Color = Color.DarkGray;
style.Font.Color = Color.White;
style.Font.Size = 15;

Step 4: Apply the style to the first worksheet.

sheet.ApplyStyle(style);

Step 5: Save the resultant file.

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

Output:

Apply a Style to an Entire Excel Worksheet in C#

Full code:

using System.Drawing;
using Spire.Xls;

namespace StyleEntireWorksheet
{
    class Program
    {
        static void Main(string[] args)
        {
            //Create a Workbook instance and load the excel file
            Workbook workbook = new Workbook();
            workbook.LoadFromFile("Input.xlsx");

            //Get the first worksheet
            Worksheet sheet = workbook.Worksheets[0];

            //Create a cell style
            CellStyle style = workbook.Styles.Add("newStyle");
            style.Color = Color.DarkGray;
            style.Font.Color = Color.White;
            style.Font.Size = 15;

            //Apply the style to the first worksheet
            sheet.ApplyStyle(style);

            //Save the resultant file
            workbook.SaveToFile("Output.xlsx", ExcelVersion.Version2013);
        }
    }
}
Tuesday, 09 January 2018 02:13

C# Activate a Worksheet

Written by support iceblue

Activate a worksheet means clicking the worksheet's tab on MS Excel. Usually when we open the Excel workbook, it goes to the worksheet when we closed last time. With Spire.XLS, we can set a worksheet as active sheet in the workbook by using the sheet.Activate() method. Then when we open the excel workbook next time, it goes to the activate worksheet we set.

C# Code Snippet of how to activate a worksheet:

Step 1: Create an instance of Excel workbook and load the document from file.

Workbook workbook = new Workbook();
workbook.LoadFromFile("Sample.xlsx");

Step 2: Get the second worksheet from the workbook.

Worksheet sheet = workbook.Worksheets[1];

Step 3: Activate the sheet.

sheet.Activate();

Step 4: Save the document to file.

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

Full codes:

Workbook workbook = new Workbook();
workbook.LoadFromFile("Sample.xlsx");

Worksheet sheet = workbook.Worksheets[1];  
                
sheet.Activate();
         
workbook.SaveToFile("result.xlsx",FileFormat.Version2013);    

When we deal with a workbook with a large number of worksheets, we may need to get the names of those worksheets and then it is easy for us to find the information we want. This article will demonstrate how to use Spire.XLS to get a list of the worksheets in the workbook in two parts.

Get a list of all the worksheet names in a workbook.

static void Main(string[] args)
 {
const string DEMOFILE = @"Sample.xlsx";

    var results = GetAllWorksheets(DEMOFILE);

    foreach (Worksheet item in results)
    {
        Console.WriteLine(item.Name);
    }
    Console.ReadLine();
}
public static WorksheetsCollection GetAllWorksheets(string fileName)
{
    //Initialize a new Workboook object
    Workbook workbook = new Workbook();

    //Load the document
    workbook.LoadFromFile(fileName);

    //Get all worksheets
    WorksheetsCollection worksheets = workbook.Worksheets;

    return worksheets;
}

Get a list of the hidden worksheet names in a workbook

static void Main(string[] args)
 {
     const string DEMOPATH = @"Sample.xlsx";
     List sheets = GetHiddenSheets(DEMOPATH);
     foreach (var sheet in sheets)
     {
         Console.WriteLine(sheet.Name);
     }
     Console.ReadLine();
 }
 public static List GetHiddenSheets(string fileName)
 {
     List returnVal = new List();

     //Initialize a new Workboook object
     Workbook workbook = new Workbook();

     //Load the document
     workbook.LoadFromFile(fileName);

     //Judge whether a worksheet is hidden
     foreach (Worksheet sheet in workbook.Worksheets)
     {
         if (sheet.Visibility == WorksheetVisibility.Hidden)
         {
             returnVal.Add(sheet);
         }
     }
     return returnVal;
 }

Excel enables us to split an excel worksheet into two or four independent panes. After splitting up the window into panes, we can use the horizontal and vertical scroll bars to view and compare data in different parts of the same worksheet. This article demonstrates how to vertical and horizontal split an excel worksheet into four panes programmatically using Spire.XLS.

Detail steps:

Step 1: Instantiate a Workbook instance and load the excel document.

Workbook workbook = new Workbook();
workbook.LoadFromFile("Sample.xlsx");

Step 2: Get the first worksheet.

Worksheet sheet = workbook.Worksheets[0];

Step 3: Split the worksheet horizontally and vertically.

sheet.FirstVisibleColumn = 3;

sheet.FirstVisibleRow = 5;

sheet.VerticalSplit = 110;

sheet.HorizontalSplit = 100;
sheet.ActivePane = 1;

Step 4: Save the excel document.

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

Screenshot after splitting:

How to Split Excel Worksheet into Multiple Panes in C#

Full codes:

using Spire.Xls;

namespace Split_Panes
{
    class Program
    {
        static void Main(string[] args)
        {
            //Load the excel document
            Workbook workbook = new Workbook();
            workbook.LoadFromFile("Sample.xlsx");
            
            //Get the first worksheet
            Worksheet sheet = workbook.Worksheets[0];

            //Vertical and horizontal split the worksheet into four panes

            sheet.FirstVisibleColumn = 3;

            sheet.FirstVisibleRow = 5;

            sheet.VerticalSplit = 110;

            sheet.HorizontalSplit = 100;

            sheet.ActivePane = 1;
 
            //Save the excel document
            workbook.SaveToFile("Output.xlsx", ExcelVersion.Version2013);

            workbook.Dispose();
        }
    }
}
Thursday, 23 February 2017 07:25

How to remove page breaks in a worksheet

Written by support iceblue

We have already demonstrated how to add page breaks in Excel worksheet in C# with the help of Spire.XLS. Spire.XLS supports to remove all the horizontal and vertical page breaks and it also supports to remove the special page breaks. Here comes to the steps of how to remove the page breaks from an Excel worksheet.

Firstly, view the same Excel document with horizontal page breaks and vertical page breaks:

How to remove page breaks in a worksheet

Step 1: Initialize an instance of Workbook and load the document from file.

Workbook workbook = new Workbook();
workbook.LoadFromFile("Sample.xlsx");

Step 2: Get the first worksheet from the workbook.

Worksheet sheet = workbook.Worksheets[0];

Step 3: Clear all the vertical page breaks by call the VPageBreaks.Clear() method.

sheet.VPageBreaks.Clear();

Step 4: Remove the specified horizontal Page Break by call the HPageBreaks.RemoveAt() method.

sheet.HPageBreaks.RemoveAt(0);

Step 5: Set the ViewMode as Preview to see how the page breaks work.

sheet.ViewMode = ViewMode.Preview;

Step 6: Save the document to file.

workbook.SaveToFile("RemovePageBreak.xlsx", FileFormat.Version2010);

Effective screenshot of removing the page breaks in a worksheet:

How to remove page breaks in a worksheet

Full codes:

static void Main(string[] args)
 {

     Workbook workbook = new Workbook();
     workbook.LoadFromFile("Sample.xlsx");

     Worksheet sheet = workbook.Worksheets[0];

     //sheet.HPageBreaks.Clear();
     sheet.VPageBreaks.Clear();

     sheet.HPageBreaks.RemoveAt(0);

              
     sheet.ViewMode = ViewMode.Preview;

     workbook.SaveToFile("RemovePageBreak.xlsx", FileFormat.Version2010);

               
 }
Tuesday, 14 February 2017 06:09

How to remove worksheet in C#

Written by support iceblue

Spire.XLS supports to add a new worksheet to the existing Excel workbook, it also supports to remove a worksheet from the Excel workbook in C#. This article focus on demonstrating how to remove Excel worksheet using sheet name or sheet index with the help of Spire.XLS.

Firstly, view the sample Excel Workbook with three sheets:

How to remove worksheet in C#

Step 1: Initialize an instance of Workbook and load the document from file.

Workbook workbook = new Workbook();
workbook.LoadFromFile("Sample.xlsx");

Step 2: Remove the worksheet by using its sheet name or sheet index by the method of Worksheets.Remove().

// removing a worksheet using its sheet name
workbook.Worksheets.Remove("Report");

// removing a worksheet using its sheet index  
workbook.Worksheets[0].Remove();

Step 3: Save the document to file.

workbook.SaveToFile("Result.xlsx", ExcelVersion.Version2010);

Effective screenshot after removing two worksheets from the Excel workbook:

How to remove worksheet in C#

Full codes:

static void Main(string[] args)
{

    Workbook workbook = new Workbook();
    workbook.LoadFromFile("Sample.xlsx");
    
    workbook.Worksheets.Remove("Report");

    workbook.Worksheets[0].Remove();

    workbook.SaveToFile("Result.xlsx", ExcelVersion.Version2010);
              
}

Sometimes, we may need to change the zoom factor when displaying the data on the excel worksheet to fulfil our requirements. In this article, we will demonstrate how to set the zoom factor on Excel work sheet in C# with the help of Spire.XLS.

Firstly, please view the screenshot of how Microsoft Excel to set the zoom factor after click View--Zoom on the top toolbox:

How to set the Zoom Factor on Excel worksheet in C#

Spire.XLS enables developers to set the value of worksheet’s zoom property to the specific zoom factor via sheet.Zoom. Here comes to the steps of how to control the zoom factor by Spire.XLS.

Step 1: Create a new Excel workbook and load from file.

Workbook wb = new Workbook();
wb.LoadFromFile("Sample.xlsx");

Step 2: Get the first worksheet from the Excel workbook.

Worksheet sheet = wb.Worksheets[0];

Step 3: Set the value of worksheet's zoom property to the specific zoom factor.

sheet.Zoom = 100;

Step 4: Save the document to file.

wb.SaveToFile("result.xlsx", ExcelVersion.Version2010);

Effective screenshot after setting the zoom factor:

How to set the Zoom Factor on Excel worksheet in C#

Full codes:

static void Main(string[] args)
{
    Workbook wb = new Workbook();
    wb.LoadFromFile("Sample.xlsx");

    Worksheet sheet = wb.Worksheets[0];
    sheet.Zoom = 100;

    wb.SaveToFile("result.xlsx", ExcelVersion.Version2010);
        
 }

Gridlines are the faint lines used to distinguish cells on the excel worksheet. It helps readers to view and check the data easily on a worksheet. All Excel worksheets have gridlines by default and when we print the excel files, the gridlines won't be printed. So we may need to hide the gridlines to have exact preview for the print result. With the help of Spire.XLS, developers can easily hide or show the gridlines on Excel worksheet. This article will show you how to display or hide the gridlines on a worksheet in C#. Here comes to the steps:

Step 1: Create a new document and load from file.

Workbook workbook = new Workbook();
workbook.LoadFromFile("sample.xlsx");

Step 2: Get the worksheets in the excel workbook.

Worksheet worksheet = workbook.Worksheets[0];
Worksheet worksheet1 = workbook.Worksheets[1];

Step 3: Hide or show the gridlines by setting the property of GridLinesVisible.

//Hiding the gridlines for the first worksheet
worksheet.GridLinesVisible = false;
//Show the gridlines for the second worksheet
worksheet1.GridLinesVisible = true;

Step 4: Save the document to file.

workbook.SaveToFile("result.xlsx", ExcelVersion.Version2010);

Effective screenshot of hide the gridlines on excel worksheet:

How to hide or show gridlines on a worksheet in C#

The screenshot of show the gridlines on excel worksheet:

How to hide or show gridlines on a worksheet in C#

Full codes:

static void Main(string[] args)
{
    Workbook workbook = new Workbook();
    workbook.LoadFromFile("sample.xlsx");

    Worksheet worksheet = workbook.Worksheets[0];
    Worksheet worksheet1 = workbook.Worksheets[1];
    
    worksheet.GridLinesVisible = false;
    worksheet1.GridLinesVisible = true;
    workbook.SaveToFile("result.xlsx", ExcelVersion.Version2010);
}

Spire.XLS supports to convert the whole excel workbook into Image, PDF, HTML and other files formats; it also supports to hide or show the worksheet in C#. When we have hidden worksheet in the excel files, developers can also use Spire.XLS to only convert the visible or hidden excel worksheets to other file formats by judge the property of WorksheetVisibility. This article will show you how to only convert visible or hidden worksheet to image in C#.

Here comes to the steps:

Step 1: Create a new document and load from file.

Workbook book = new Workbook();
book.LoadFromFile("sample.xlsx", ExcelVersion.Version2010);

Step 2: Traverse every worksheet in the excel file.

foreach (Spire.Xls.Worksheet ws2 in book.Worksheets)

Step 3: Call the property of WorksheetVisibility to judge visible or hidden excel worksheet.

//only converts the visible sheet
if (ws2.Visibility == WorksheetVisibility.Visible)
//only converts the hidden sheet
if (ws2.Visibility == WorksheetVisibility.Hidden)

Step 4: Use SaveToImage to convert Excel worksheet to Image.

ws2.SaveToImage("result.jpg");

Effective screenshots:

Only convert the visible worksheet to Image in .jpg format.

Only convert the visible worksheet to Image in .jpg format

Only convert the hidden worksheet to Image in .png format.

Only convert the hidden worksheet to Image in .png format

Full codes:

static void Main(string[] args)
{
    Workbook book = new Workbook();
    book.LoadFromFile("sample.xlsx", ExcelVersion.Version2010);

    foreach (Spire.Xls.Worksheet ws2 in book.Worksheets)
    {
        //only convert the visible sheet
        if (ws2.Visibility == WorksheetVisibility.Visible)
            ////only convert the hidden sheet
            //if (ws2.Visibility == WorksheetVisibility.Hidden)
        ws2.SaveToImage("result.jpg");
    }
}
Thursday, 18 June 2015 01:37

How to hide or show Excel worksheet in C#

Written by support iceblue

Sometimes, you want to hide an excel worksheet to make your workbook tidy and clear. With Spire.XLS for .NET, you can hide any worksheet in a workbook to remove it from view. The data in hidden worksheets is not visible, but it can still be referenced from other worksheets. You can display the hidden worksheets when you need. This article will show you how to hide or show Excel worksheet in C#.

Step 1: Create a new document and load from file.

Workbook wb = new Workbook();
wb.LoadFromFile("Sample.xlsx");

Step 2: Call the class of WorksheetVisibility to set the property to hidden or visible for the worksheet.

//Hide Sheet2
wb.Worksheets[1].Visibility = WorksheetVisibility.Hidden;

//Show the sheet
wb.Worksheets[j].Visibility = WorksheetVisibility.Visible;

Step 3: Save the document to file.

wb.SaveToFile("Result.xlsx",ExcelVersion.Version2010);

Effective screenshot after hide sheet2:

How to hide or show Excel worksheet in C#

Full codes:

namespace HideShowExcelWorksheet
{
    class Program
    {
        static void Main(string[] args)
        {
            Workbook wb = new Workbook();
            wb.LoadFromFile("Sample.xlsx");
            wb.Worksheets[1].Visibility = WorksheetVisibility.Hidden;
            wb.SaveToFile("Result.xlsx",ExcelVersion.Version2010);
         }
    }
}
Page 1 of 2