Cells (28)
Cell type refers to the data type in a cell. There are six cell types in Spire.XLS, i.e. String, Number, Formula, Boolean, Error and Blank. This article is going to show you how to get the cell types of specified cells in an Excel file using Spire.XLS.
Detail steps
Step 1: Create a Workbook instance and load the Excel file.
Workbook workbook = new Workbook();
workbook.LoadFromFile("Input.xlsx");
Step 2: Get the second worksheet.
Worksheet sheet = workbook.Worksheets[1];
Step 3: Get the cell types of the cells in range "A2:A7".
foreach (CellRange range in sheet.Range["A2:A7"])
{
XlsWorksheet.TRangeValueType cellType = sheet.GetCellType(range.Row, range.Column, false);
sheet[range.Row, range.Column+1].Text = cellType.ToString();
sheet[range.Row, range.Column + 1].Style.Font.Color = Color.Red;
sheet[range.Row, range.Column+1].Style.Font.IsBold = true;
}
Step 4: Save the file.
workbook.SaveToFile("GetCellType.xlsx", ExcelVersion.Version2013);
Output:
Full code:
using System.Drawing;
using Spire.Xls;
using Spire.Xls.Core.Spreadsheet;
namespace GetCellType
{
class Program
{
static void Main(string[] args)
{
//Load the Excel file
Workbook workbook = new Workbook();
workbook.LoadFromFile("Input.xlsx");
//Get the second worksheet
Worksheet sheet = workbook.Worksheets[1];
//Get the cell types of the cells in range “A2:A7”
foreach (CellRange range in sheet.Range["A2:A7"])
{
XlsWorksheet.TRangeValueType cellType = sheet.GetCellType(range.Row, range.Column, false);
sheet[range.Row, range.Column+1].Text = cellType.ToString();
sheet[range.Row, range.Column + 1].Style.Font.Color = Color.Red;
sheet[range.Row, range.Column+1].Style.Font.IsBold = true;
}
//Save the file
workbook.SaveToFile("GetCellType.xlsx", ExcelVersion.Version2013);
}
}
}
Shrink to fit is a useful option in Excel, it enables us to automatically reduce the font size in a cell until the text fits within the cell. This article demonstrates how to accomplish the same functionality programmatically in C# using Spire.XLS.
Below is the screenshot of the input Excel file:
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: Specify the cell range to shrink text.
CellRange cell = sheet.Range["A1:E3"];
Step 4: Enable ShrinkToFit.
CellStyle style = cell.Style; style.ShrinkToFit = true;
Step 5: Save the file.
workbook.SaveToFile("ShrinkTextToFitCell.xlsx", ExcelVersion.Version2013);
Output:
Full code:
using Spire.Xls;
namespace ShrinkText
{
class Program
{
static void Main(string[] args)
{
//Load the Excel file
Workbook workbook = new Workbook();
workbook.LoadFromFile(@"Input.xlsx");
//Get the first worksheet
Worksheet sheet = workbook.Worksheets[0];
//The cell range to shrink text
CellRange cell = sheet.Range["A1:E3"];
//Enable ShrinkToFit
CellStyle style = cell.Style;
style.ShrinkToFit = true;
//Save the file
workbook.SaveToFile("ShrinkTextToFitCell.xlsx", ExcelVersion.Version2013);
}
}
}
Using Spire.XLS, you're able to apply multiple fonts in a single cell in order to create rich-formatted text within the cell, you can also extract the formatted text from the cell(s) in an existing Excel document. The following code snippets will show you how to read rich text from an Excel cell in C# and VB.NET.
Step 1: Create a Workbook instance and load a sample Excel file.
Workbook wb = new Workbook(); wb.LoadFromFile(@"C:\Users\Administrator\Desktop\Sample.xlsx");
Step 2: Get the first worksheet.
Worksheet sheet= wb.Worksheets[0];
Step 3: Get the rtf text from the specified cell.
richTextBox1.Rtf = sheet.Range["B2"].RichText.RtfText;
Output:
Full Code:
private void btn_read_Click(object sender, EventArgs e)
{
Workbook wb = new Workbook();
wb.LoadFromFile(@"C:\Users\Administrator\Desktop\Sample.xlsx");
Worksheet sheet= wb.Worksheets[0];
richTextBox1.Rtf = sheet.Range["B2"].RichText.RtfText;
}
Private Sub btn_read_Click(ByVal sender As Object, ByVal e As EventArgs)
Dim wb As Workbook = New Workbook()
wb.LoadFromFile("C:\Users\Administrator\Desktop\Sample.xlsx")
Dim sheet As Worksheet = wb.Worksheets(0)
richTextBox1.Rtf = sheet.Range("B2").RichText.RtfText
End Sub
When working with an Excel document, some blank rows or columns may appear due to the modification of data. Although having blank rows or columns in a worksheet isn't necessarily a bad thing, in most cases they are still undesirable. Furthermore, such blank rows or columns may cause errors if formulas are applied. To delete these blank rows or columns, you can simply select and delete them, but if there are a lot of blank rows and columns, deleting them manually is a very time-consuming task. In this article, you will learn how to programmatically delete blank rows and columns in an Excel worksheet using Spire.XLS for .NET.
Install Spire.XLS for .NET
To begin with, you need to add the DLL files included in the Spire.XLS for .NET package as references in your .NET project. The DLL files can be either downloaded from this link or installed via NuGet.
PM> Install-Package Spire.XLS
Delete Blank Rows and Columns in Excel
The detailed steps are as follows.
- Instantiate a Workbook object.
- Load a sample Excel file using Workbook.LoadFromFile() method.
- Get a specified worksheet using Workbook.Worksheets[] property.
- Loop through all used rows in the specified worksheet and determine whether the row is blank using XlsRange.IsBlank property.
- Delete the blank rows using Worksheet.DeleteRow() method.
- Loop through all used columns in the specified worksheet and determine whether the column is blank using XlsRange.IsBlank property.
- Delete the blank columns using Worksheet.DeleteColumn() method.
- Save the result file using Workbook.SaveToFile() method.
- C#
- VB.NET
using System.Linq;
using Spire.Xls;
namespace DeleteBlankRowsAndColumns
{
class Program
{
static void Main(string[] args)
{
//Instantiate a Workbook object
Workbook workbook = new Workbook();
//Load a sample Excel file
workbook.LoadFromFile("sample.xlsx");
//Get the first worksheet
Worksheet sheet = workbook.Worksheets[0];
//Delete blanks rows
for (int i = sheet.Rows.Count() - 1; i >= 0; i--)
{
if (sheet.Rows[i].IsBlank)
{
sheet.DeleteRow(i + 1); //Index parameter in DeleteRow method starts from 1
}
}
//Delete blank columns
for (int j = sheet.Columns.Count() - 1; j >= 0; j--)
{
if (sheet.Columns[j].IsBlank)
{
sheet.DeleteColumn(j + 1); //Index parameter in DeleteColumn method starts from 1
}
}
//Save the file
workbook.SaveToFile("DeleteBlankRowsAndColumns.xlsx", ExcelVersion.Version2013);
}
}
}
Apply for a Temporary License
If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.
In Spire.XLS, we can hide or show the headers of rows and columns by setting the RowColumnHeadersVisible property of XlsWorksheet class. This article elaborates the steps of how to accomplish this function using Spire.XLS.
The following screenshot shows the input file which contain one worksheet with row and column headers.
Detail steps:
Step 1: Instantiate a Workbook instance 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: Hide or show the headers of rows and columns in the worksheet.
//Hide the headers of rows and columns sheet.RowColumnHeadersVisible = false; //Show the headers of rows and columns //sheet.RowColumnHeadersVisible = true;
Step 4: Save the file.
workbook.SaveToFile("Output.xlsx");
The screenshot after hiding the row and column headers:
Full code:
using Spire.Xls;
namespace ShowRowColumnHeader
{
class Program
{
static void Main(string[] args)
{
// Instantiate a Workbook instance
Workbook workbook = new Workbook();
//Load the Excel file
workbook.LoadFromFile("Input.xlsx");
//Get the first worksheet
Worksheet sheet = workbook.Worksheets[0];
//Hide the headers of rows and columns
sheet.RowColumnHeadersVisible = false;
//Show the headers of rows and columns
//sheet.RowColumnHeadersVisible = true;
//Save the file
workbook.SaveToFile("Output.xlsx");
}
}
}
How to remove the value and format from Excel Cell range
2017-06-20 08:39:09 Written by support iceblueWhen we operate the data on an Excel worksheet, we may need to delete text from a cell in a spreadsheet document and sometimes even remove all the contents with format from the cell range. This article will demonstrate how to remove the value and format from Excel Cell range with the help of Spire.XLS.
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 first worksheet from the workbook.
Worksheet sheet = workbook.Worksheets[0];
Step 3: Set the value as null to remove the original content from the Excel Cell.
sheet.Range["A1"].Value = "";
Step 4: Clear the contents to remove the original content from the Excel Cell.
sheet.Range["A3"].ClearContents();
Step 5: Remove the contents with format from the Excel cell.
sheet.Range["B1"].ClearAll();
Step 6: Save document to file.
workbook.SaveToFile("result.xlsx",FileFormat.Version2010);
Effective screenshot of remove the value and format from Excel Cell range:
Full codes:
using Spire.Xls;
namespace RemoveValue
{
class Program
{
static void Main(string[] args)
{
Workbook workbook = new Workbook();
workbook.LoadFromFile("Sample.xlsx");
Worksheet sheet = workbook.Worksheets[0];
sheet.Range["A1"].Value = "";
sheet.Range["A3"].ClearContents();
sheet.Range["B1"].ClearAll();
workbook.SaveToFile("result.xlsx", FileFormat.Version2010);
}
}
}
Hide the content on Cell by setting the number format
2017-03-29 08:07:48 Written by support iceblueWe have already demonstrated how to using Spire.XLS hide excel columns and rows in C#. Sometimes we don't want to show the data on a certain cell to others but not hide the whole row or column. Then we can only hide the data on this cell by setting the number format for it. This article will focus on showing how to hide the content on a certain cell by setting the number format as ";;;" to hide the content to others.
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 worksheet = workbook.Worksheets[0];
Step 3: Hide the area by setting the number format as ";;;".
worksheet.Range["E2"].NumberFormat = ";;;";
Step 4: Save the document to file.
workbook.SaveToFile("Result.xlsx", FileFormat.Version2010);
Effective screenshot of hide the content on Excel cell by setting the number format:
Full codes:
using Spire.Xls;
namespace HideContent
{
class Program
{
static void Main(string[] args)
{
Workbook workbook = new Workbook();
workbook.LoadFromFile("Sample.xlsx");
Worksheet worksheet = workbook.Worksheets[0];
worksheet.Range["E2"].NumberFormat = ";;;";
workbook.SaveToFile("Result.xlsx", FileFormat.Version2010);
}
}
}
Spire.Xls.CellRange class provides a method named Intersect(CellRange range) that is used to find the intersection of certain ranges. This is very useful when we need to get the common value(s) of two ranges in an excel worksheet.
In below picture, we take range A2:C8 and range B2:D8 as an example. Cells filled in yellow color are the intersection of the two ranges.
Now refer to the following detail steps:
Step 1: Instantiate an object of Workbook class 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: Get the intersection of the two ranges and print the common values of them.
CellRange range = sheet.Range["A2:C8"].Intersect(sheet.Range["B2:D8"]);
foreach (CellRange r in range)
{
Console.WriteLine(r.Value);
}
Output:
Full code:
using System;
using Spire.Xls;
namespace Get_the_instersection_of_two_ranges
{
class Program
{
static void Main(string[] args)
{
Workbook workbook = new Workbook();
workbook.LoadFromFile("Sample.xlsx");
Worksheet sheet = workbook.Worksheets[0];
CellRange range = sheet.Range["A2:C8"].Intersect(sheet.Range["B2:D8"]);
foreach (CellRange r in range)
{
Console.WriteLine(r.Value);
}
Console.ReadKey();
}
}
}
Imports Spire.Xls
Namespace Get_the_instersection_of_two_ranges
Class Program
Private Shared Sub Main(args As String())
Dim workbook As New Workbook()
workbook.LoadFromFile("Sample.xlsx")
Dim sheet As Worksheet = workbook.Worksheets(0)
Dim range As CellRange = sheet.Range("A2:C8").Intersect(sheet.Range("B2:D8"))
For Each r As CellRange In range
Console.WriteLine(r.Value)
Next
Console.ReadKey()
End Sub
End Class
End Namespace
How to Apply Multiple Fonts in a Single Cell in C#, VB.NET
2016-09-15 03:44:07 Written by support iceblueTo make the text within a cell diverse, we can apply different font to different range of characters. Spire.XLS also provides the ability to apply multiple fonts in a single cell by using RichText.SetFont() method. This article presents how to create different fonts in a workbook and apply them to a certain cell in C# and VB.NET.
Code Snippet:
Step 1: Initialize an instance of Workbook class and get the first worksheet.
Workbook wb = new Workbook(); Worksheet sheet = wb.Worksheets[0];
Step 2: Create a font object in workbook, setting the font color, size and type.
ExcelFont font1 = wb.CreateFont(); font1.KnownColor = ExcelColors.LightBlue; font1.IsBold = true; font1.Size = 10;
Step 3: Create another font object specifying its properties.
ExcelFont font2 = wb.CreateFont(); font2.KnownColor = ExcelColors.Red; font2.IsBold = true; font2.IsItalic = true; font2.FontName = "Times New Roman"; font2.Size = 11;
Step 4: Write a RichText string to the cell 'A1', and set the font for the specific range of characters using RichText.SetFont() method.
RichText richText = sheet.Range["A1"].RichText; richText.Text = "This document was created with Spire.XLS for .NET."; richText.SetFont(0, 29, font1); richText.SetFont(31, 48, font2);
Step 5: Save the file.
wb.SaveToFile("MultiFonts.xlsx", ExcelVersion.Version2010);
Output:
Full Code:
using Spire.Xls;
namespace ApplyMutipleFont
{
class Program
{
static void Main(string[] args)
{
Workbook wb = new Workbook();
Worksheet sheet = wb.Worksheets[0];
ExcelFont font1 = wb.CreateFont();
font1.KnownColor = ExcelColors.LightBlue;
font1.IsBold = true;
font1.Size = 10;
ExcelFont font2 = wb.CreateFont();
font2.KnownColor = ExcelColors.Red;
font2.IsBold = true;
font2.IsItalic = true;
font2.FontName = "Times New Roman";
font2.Size = 11;
RichText richText = sheet.Range["A1"].RichText;
richText.Text = "This document was created with Spire.XLS for .NET.";
richText.SetFont(0, 29, font1);
richText.SetFont(31, 48, font2);
wb.SaveToFile("MultiFonts.xlsx", ExcelVersion.Version2010);
}
}
}
Imports Spire.Xls
Namespace ApplyMutipleFont
Class Program
Private Shared Sub Main(args As String())
Dim wb As New Workbook()
Dim sheet As Worksheet = wb.Worksheets(0)
Dim font1 As ExcelFont = wb.CreateFont()
font1.KnownColor = ExcelColors.LightBlue
font1.IsBold = True
font1.Size = 10
Dim font2 As ExcelFont = wb.CreateFont()
font2.KnownColor = ExcelColors.Red
font2.IsBold = True
font2.IsItalic = True
font2.FontName = "Times New Roman"
font2.Size = 11
Dim richText As RichText = sheet.Range("A1").RichText
richText.Text = "This document was created with Spire.XLS for .NET."
richText.SetFont(0, 29, font1)
richText.SetFont(31, 48, font2)
wb.SaveToFile("MultiFonts.xlsx", ExcelVersion.Version2010)
End Sub
End Class
End Namespace
Spire.XLS provides a class named Worksheet, it contains a MergedCells property which makes it easy for us to obtain the merged cells in a worksheet. This property will return an array of the merged cell ranges, after that we can do any desired operations such as unmerge and apply formatting on the cells in the ranges. This article explains how to detect all merged cells in a worksheet and unmerge them once using Spire.XLS.
Here we used a template Excel file which has some merged cells:
Detail steps:
Step 1: Instantiate a Workbook object and load the Excel file.
Workbook workbook = new Workbook();
workbook.LoadFromFile("Sample.xlsx");
Step 2: Access the first worksheet by passing its sheet index.
Worksheet sheet = workbook.Worksheets[0];
Step 3: Get the merged cell ranges in the first worksheet and put them into a CellRange array.
CellRange[] range = sheet.MergedCells;
Step 4: Traverse through the array and unmerge the merged cells.
foreach (CellRange cell in range)
{
cell.UnMerge();
}
Step 5: Save the file.
workbook.SaveToFile("Output.xlsx",ExcelVersion.Version2010);
After executing the code, we'll get the following output file:
Full codes:
using Spire.Xls;
namespace Detect_Merged_Cells
{
class Program
{
static void Main(string[] args)
{
Workbook workbook = new Workbook();
workbook.LoadFromFile("Sample.xlsx");
Worksheet sheet = workbook.Worksheets[0];
CellRange[] range = sheet.MergedCells;
foreach (CellRange cell in range)
{
cell.UnMerge();
}
workbook.SaveToFile("Output.xlsx",ExcelVersion.Version2010);
}
}
}
Imports Spire.Xls
Namespace Detect_Merged_Cells
Class Program
Private Shared Sub Main(args As String())
Dim workbook As New Workbook()
workbook.LoadFromFile("Sample.xlsx")
Dim sheet As Worksheet = workbook.Worksheets(0)
Dim range As CellRange() = sheet.MergedCells
For Each cell As CellRange In range
cell.UnMerge()
Next
workbook.SaveToFile("Output.xlsx",ExcelVersion.Version2010)
End Sub
End Class
End Namespace
