C#/VB.NET: Lock Specific Cells in Excel

2022-03-04 02:16:00 Written by Koohji

When creating an Excel worksheet, you may enter some data or formulas that you don't wish other users to modify. Locking the corresponding cells can be essential to maintain the integrity of the data and formulas. In this article, you will learn how to lock specific cells in Excel in C# and VB.NET using Spire.XLS for .NET library.

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

Lock Specific Cells in Excel in C# and VB.NET

Normally, the locked option is enabled for all cells in a worksheet. Therefore, before locking a cell or range of cells, all cells must be unlocked. Keep in mind that locking cells doesn’t take effect until the worksheet is protected.

The following are the main steps to lock cells in Excel:

  • Create an instance of Workbook class.
  • Load the Excel file using Workbook.LoadFromFile() method.
  • Get the desired worksheet using Workbook.Worksheet[sheetIndex] property.
  • Access the used range in the worksheet and then unlock all the cells in the range by setting the CellRange.Style.Locked property as false.
  • Access specific cells and then lock them by setting the CellRange.Style.Locked property as true.
  • Protect the worksheet using XlsWorksheetBase.Protect() method.
  • Save the result file using Workbook.SaveToFile() method.
  • C#
  • VB.NET
using Spire.Xls;

namespace LockCells
{
    class Program
    {
        static void Main(string[] args)
        {
            //Create a Workbook instance
            Workbook workbook = new Workbook();
            //Load an Excel file
            workbook.LoadFromFile("Input.xlsx");

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

            //Unlock all cells in the used range of the worksheet
            CellRange usedRange = sheet.Range;            
            usedRange.Style.Locked = false;

            //Lock specific cells
            CellRange cells = sheet.Range["A1:C3"]; 
            cells.Style.Locked = true;

            //Protect the worksheet with password
            sheet.Protect("123456", SheetProtectionType.All);

            //Save the result file
            workbook.SaveToFile("LockCells.xlsx", ExcelVersion.Version2016);
        }
    }
}

C#/VB.NET: Lock Specific Cells in Excel

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.

Optimizing the viewer preferences and zoom factors is crucial for improving the viewing experience of PDF documents. By using the appropriate viewer preferences and zoom factors, you can make your PDF documents more user-friendly, viewable and suitable for different devices and platforms. In this article, we will demonstrate how to set viewer preferences and zoom factors for PDF documents in C# and VB.NET using Spire.PDF for .NET.

Install Spire.PDF for .NET

To begin with, you need to add the DLL files included in the Spire.PDF 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.PDF

Set Viewer Preferences for PDF in C# and VB.NET

Viewer preferences are settings that can be applied to a PDF document to control how it is displayed when it is opened in a PDF viewer. These preferences can affect various aspects of the viewing experience, such as the initial view, page layout, and navigation tabs.

To set the viewer preference for a PDF document using Spire.PDF for .NET, you can follow these steps:

  • Initialize an instance of PdfDocument class.
  • Load a PDF document using PdfDocument.LoadFromFile() method.
  • Get the PdfViewerPreferences object.
  • Set viewer preference for the document using the properties provided by the PdfViewerPreferences class.
  • Save the result document using PdfDocument.SaveToFile() method.
  • C#
  • VB.NET
using Spire.Pdf;

namespace SetViewerPreference
{
    internal class Program
    {
        static void Main(string[] args)
        {
            //Initialize an instance of PdfDocument class
            PdfDocument pdf = new PdfDocument();
            //Load a PDF document
            pdf.LoadFromFile(@"Example.pdf");

            //Get the PdfViewerPreferences object
            PdfViewerPreferences viewerPreferences = pdf.ViewerPreferences;

            //Set viewer preference           
            viewerPreferences.FitWindow = false;
            viewerPreferences.HideMenubar = true;
            viewerPreferences.HideToolbar = true;
            viewerPreferences.CenterWindow= true;
            viewerPreferences.DisplayTitle = false;
            viewerPreferences.PageLayout = PdfPageLayout.SinglePage;
            viewerPreferences.PageMode = PdfPageMode.UseNone;

            //Save the result document
            pdf.SaveToFile("SetViewerPreference.pdf");
            pdf.Close();
        }
    }
}

C#/VB.NET: Set Viewer Preferences and Zoom Factors for PDFs

Set Zoom Factors for PDF in C# and VB.NET

The zoom factor determines the zoom level of the PDF document when it is opened. By default, most PDF viewers set the zoom factor to "Fit Page," which scales the document to fit the width of the viewer window. However, you can also set a specific zoom factor, such as 60%, 150% or 200%, depending on your needs.

To set the zoom factor for a PDF document using Spire.PDF for .NET, you can follow these steps:

  • Initialize an instance of PdfDocument class.
  • Load a PDF document using PdfDocument.LoadFromFile() method.
  • Get a specific page using PdfDocument.Pages[int index] property.
  • Initialize an instance of the PdfDestination class.
  • Set the destination mode, location and zoom factor using PdfDestination.Mode and PdfDestination.Location and PdfDestination.Zoom properties.
  • Initialize an instance of the PdfGoToAction class and pass the PdfDestination instance to the constructor of the class as a parameter.
  • Set the action to be executed when the document is opened using PdfDocument.AfterOpenAction property.
  • Save the result document using PdfDocument.SaveToFile() method.
  • C#
  • VB.NET
using Spire.Pdf;
using Spire.Pdf.Actions;
using Spire.Pdf.General;
using System.Drawing;

namespace SetZoomFactor
{
    internal class Program
    {
        static void Main(string[] args)
        {
            //Initialize an instance of the PdfDocument class
            PdfDocument pdf = new PdfDocument();
            //Load a PDF document
            pdf.LoadFromFile(@"Example.pdf");

            //Get the first page
            PdfPageBase page = pdf.Pages[0];

            //Initialize an instance of the PdfDestination class
            PdfDestination dest = new PdfDestination(page);
            //Set the destination mode
            dest.Mode = PdfDestinationMode.Location;
            //Set the destination location
            dest.Location = new PointF(40f, 40f);
            //Set the zoom factor
            dest.Zoom = 1.5f;

            //Initialize an instance of the PdfGoToAction class
            PdfGoToAction gotoAction = new PdfGoToAction(dest);
            //Set the action to be executed when the document is opened
            pdf.AfterOpenAction = gotoAction;

            //Save the result document
            pdf.SaveToFile("SetZoomFactor.pdf");
            pdf.Close();
        }
    }
}

C#/VB.NET: Set Viewer Preferences and Zoom Factors for PDFs

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.

C#/VB.NET: Convert CSV to DataTable

2022-05-19 06:32:00 Written by Koohji

A DataTable represents a table of in-memory relational data. It can be populated from a data source like Microsoft SQL Server or from a file like CSV or Excel. In this article, you will learn how to populate DataTable from CSV, or in other words, how to convert CSV to DataTable in C# and VB.NET 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

Convert CSV to DataTable in C# and VB.NET

The following are the main steps to convert CSV to DataTable:

  • Initialize an instance of Workbook class.
  • Load a CSV file using Workbook.LoadFromFile() method and passing the file path and the delimiter/separator of the CSV file in the form of string as parameters.
  • Get the desired worksheet by its index (zero-based) through Workbook.Worksheets[sheetIndex] property.
  • Export data from the worksheet to a DataTable using Worksheet.ExportDataTable() method.
    (The ExportDataTable() method has several overloads that can be used to control how the data will be exported, for example, ExportDataTable(CellRange range, bool exportColumnNames, bool computedFormulaValue): this overload allows you to specify the range to be exported along with whether to export columns names and calculated values of formulas.
  • C#
  • VB.NET
using Spire.Xls;
using System;
using System.Data;
using System.Windows.Forms;

namespace ConvertCsvToExcel
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            //Create a Workbook instance
            Workbook workbook = new Workbook();
            //Load a CSV file
            workbook.LoadFromFile(@"C:\Users\Administrator\Desktop\Input.csv", ",");

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

            //Export data from the worksheet to a DataTable 
            DataTable dt = worksheet.ExportDataTable();

            //This overload enables you to specify the range to be exported along with whether to export column names and calculated values of formulas
            //DataTable dt = worksheet.ExportDataTable(worksheet.Range["A1:C10"], true, true);

            //Show the DataTable in a DataGridView control (optional)
            dataGridView1.DataSource = dt;
        }
    }
}

C#/VB.NET: Convert CSV to DataTable

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.

Export Datatable to Excel from Database

2012-11-13 06:38:51 Written by Koohji

This section will show you an easy solution to quickly export datatable from database to Excel via an Excel .NET component in C#, VB.NET.

Spire.XLS for .NET enables you to both export datatable to excel and import excel to datatable. This solution shows you two lines of key souce code for exporting data from datatable to Excel. One is XlsWorksheet.InsertDataTable(System.Data.DataTable dataTable, bool columnHeaders, int firstRow, int firstColumn) which is responsible for importing the data into worksheet. The other is Workbook.SaveToFile(string fileName) that is called to save the workbook to Excel file.

datatable to excel

Here you can download Spire.XLS for .NET and start to perform the datatable to Excel task by below code.

Sample code:

[C#]
private void button1_Click(object sender, EventArgs e)
        {
            //connect database
            OleDbConnection connection = new OleDbConnection();
            connection.ConnectionString @"Provider=""Microsoft.Jet.OLEDB.4.0"";Data Source=""demo.mdb"";User Id=;Password="
            OleDbCommand command = new OleDbCommand();
            command.CommandText = "select * from parts";
            DataSet dataSet = new System.Data.DataSet();
            OleDbDataAdapter dataAdapter = new OleDbDataAdapter(command.CommandText,connection);
            dataAdapter.Fill(dataSet);
            DataTable t = dataSet.Tables[0];
            //export datatable to excel
            Workbook book = new Workbook();
            Worksheet sheet = book.Worksheets[0];
            sheet.InsertDataTable(t, true, 1, 1);
            book.SaveToFile("insertTableToExcel.xls",ExcelVersion.Version97to2003);
            System.Diagnostics.Process.Start("insertTableToExcel.xls");
        }
[VB.NET]
Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        //connect database
        Dim connection As OleDbConnection = New OleDbConnection
        connection.ConnectionString = “Provider=””Microsoft.Jet.OLEDB.4.0””;
Data Source=""demo.mdb""; User Id=;Password="
        Dim command As OleDbCommand = New OleDbCommand
        command.CommandText = "select * from parts"
        Dim dataSet As DataSet = New System.Data.DataSet
        Dim dataAdapter As OleDbDataAdapter = New OleDbDataAdapter(command.CommandText, connection)
        dataAdapter.Fill(dataSet)
        Dim t As DataTable = dataSet.Tables(0)
        //export datatable to excel
        Dim book As Workbook = New Workbook
        Dim sheet As Worksheet = book.Worksheets(0)
        sheet.InsertDataTable(t, True, 1, 1)
        book.SaveToFile("insertTableToExcel.xls",ExcelVersion.Version97to2003)
        System.Diagnostics.Process.Start("insertTableToExcel.xls")
    End Sub
End Class

Remove Attachments from PDF in C#/VB.NET

2012-11-06 05:49:47 Written by Koohji

Different from Word and Excel, PDF enables people to attach various files in it. For the convenience of both protecting the confidential files and avoiding the PDF too large, PDF also allows developers to remove any attachment. This section will introduce a solution to remove attachments from PDF via a PDF component in C#, VB.NET.

Spire.PDF for .NET, a .NET component with rich capabilities in manipulating PDF, enables you to quickly remove attachments from your PDF document. Now please see below picture and view the whole attachments remove solution after it.

Remove PDF Attachments

In the solution, first you need a decision procedure to see whether this PDF file has any attachment, then, remove attachments. There are two situations here. One is to remove all the attachments. You can call this method: Document.Attachments.Clear(). The other is to remove a certain attachment or attachments. You can remove them either by pointing file name for example filename=="e-iceblue.html " or by the file extension such as filename.Contains(".html"). Below solution directly find the PDF via file name. Please remember to download Spire.PDF for .NET before performing the project.

[C#]
using Spire.Pdf;
using Spire.Pdf.Attachments;
using System;
using System.Windows.Forms;


namespace RemoveAttachments
{
    class Program
    {
        private void button1_Click(object sender, EventArgs e)
        {
            OpenFileDialog dialog = new OpenFileDialog();
            dialog.Filter = "PDF document(*.pdf)|*.pdf";
            DialogResult result = dialog.ShowDialog();
            if (result == DialogResult.OK)
            {
                PdfDocument doc = new PdfDocument();
                doc.LoadFromFile(dialog.FileName);
                PdfAttachmentCollection attachments = doc.Attachments;
                PdfAttachment attachment = null;
                if (doc.Attachments.Count > 0)
                {
                    foreach (PdfAttachment oneAttachment in doc.Attachments)
                    {
                        string filename = oneAttachment.FileName;
                        if (filename == "excel.xlsx")
                        {
                            attachment = oneAttachment;
                            break;
                        }
                    }
                }
                if (attachment != null)
                {
                    doc.Attachments.Remove(attachment);
                }
                doc.SaveToFile("test.pdf");
                System.Diagnostics.Process.Start("test.pdf");
            }
        }
    }
}
[VB.NET]
Imports Spire.Pdf
Imports Spire.Pdf.Attachments
Imports System.Windows.Forms


Namespace RemoveAttachments
	Class Program
		Private Sub button1_Click(sender As Object, e As EventArgs)
			Dim dialog As New OpenFileDialog()
			dialog.Filter = "PDF document(*.pdf)|*.pdf"
			Dim result As DialogResult = dialog.ShowDialog()
			If result = DialogResult.OK Then
				Dim doc As New PdfDocument()
				doc.LoadFromFile(dialog.FileName)
				Dim attachments As PdfAttachmentCollection = doc.Attachments
				Dim attachment As PdfAttachment = Nothing
				If doc.Attachments.Count > 0 Then
					For Each oneAttachment As PdfAttachment In doc.Attachments
						Dim filename As String = oneAttachment.FileName
						If filename = "excel.xlsx" Then
							attachment = oneAttachment
							Exit For
						End If
					Next
				End If
				If attachment IsNot Nothing Then
					doc.Attachments.Remove(attachment)
				End If
				doc.SaveToFile("test.pdf")
				System.Diagnostics.Process.Start("test.pdf")
			End If
		End Sub
	End Class
End Namespace

Spire.PDF is a .NET PDF component, which enables users to perform a wide range of PDF processing tasks directly, such as generate, read, write and modify PDF document in WPF, .NET and Silverlight.

C#: Create Barcodes in a Word Document

2024-10-30 01:11:00 Written by Koohji

Creating barcodes in a Word document is a useful technique for enhancing productivity and organization. Barcodes facilitate quick scanning and tracking, making them essential for businesses, events, and personal projects.

This article explains two methods for creating barcodes in a Word document using C#: one with barcode fonts via Spire.Doc for .NET API, and the other using a Barcode API alongside the Word API.

Install Spire.Doc for .NET

To begin with, you need to add the DLL files included in the Spire.Doc 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.Doc

Create Barcodes in a Word Document Using Barcode Fonts

A barcode font is a typeface that converts alphanumeric data into a scannable format of bars and spaces. To use it, you typically need to install the font on your system and then format text in a Word document.

The steps to create barcodes in a Word document using barcode fonts are as follows:

  • Download and install the desired barcode font on your computer.
  • Create a Document object.
  • Load a Word file using Document.LoadFromFile() method.
  • Get a specific section and add a paragraph using Section.AddParagraph() method.
  • Add text to the paragraph using Paragraph.AppendText() method.
  • Apply the barcode font to the text using TextRange.CharacterFormat.FontName property.
  • Set the font size and color for the text.
  • Save the document to a different Word file.
  • C#
using Spire.Doc;
using Spire.Doc.Documents;
using Spire.Doc.Fields;
using System.Drawing;

namespace Name
{
    class Program
    {
        static void Main(string[] args)
        {
            // Create a Document object
            Document document = new Document();

            // Load a Word file
            document.LoadFromFile("C:\\Users\\Administrator\\Desktop\\input.docx");

            // Get a specific section 
            Section section = document.Sections[0];

            // Add a paragraph
            Paragraph paragraph = section.AddParagraph();

            // Append text to the paragraph
            TextRange txtRang = paragraph.AppendText("Hello,World");

            // Apply barcode font to the text
            txtRang.CharacterFormat.FontName = "Code 128";

            // Set the font size and text color
            txtRang.CharacterFormat.FontSize = 80;
            txtRang.CharacterFormat.TextColor = Color.Black;

            // Save the document to a different Word file
            document.SaveToFile("Barcode.docx", FileFormat.Docx);

            // Dispose resources
            document.Dispose();
        }
    }
}

C#: Create Barcodes in a Word Document

Create Barcodes in a Word Document Using Barcode API

Spire.Barcode for .NET is a Barcode API that allows you to easily create a barcode with customized settings, such as barcode type, data, size, and color. You can install the library from NuGet using the following command.

PM> Install-Package Spire.Barcode

After the barcode image is created, you can then insert it to a Word document with the help of the Spire.Doc for .NET library.

The steps to create barcode in a Word document using a Barcode API are as follows:

  • Install Spire.Barcode for .NET in your .NET program.
  • Create a BarcodeSettings object.
  • Specify the barcode type, data, width and other attributes using the properties under the BarcodeSettings object.
  • Generate a barcode image based on the settings using BarCodeGenerator.GenerateImage() method.
  • Create a Document object.
  • Load a Word file using Document.LoadFromFile() method.
  • Get a specific section and add a paragraph using Section.AddParagraph() method.
  • Add the barcode image to the paragraph using Paragraph.AppendPicture() method.
  • Save the document to a different Word file.
  • C#
using Spire.Barcode;
using Spire.Doc;
using Spire.Doc.Documents;
using System.Drawing;

namespace Name
{
    class Program
    {
        static void Main(string[] args)
        {
            // Create a BarcodeSettings object
            BarcodeSettings settings = new BarcodeSettings();

            // Set barcode type
            settings.Type = BarCodeType.QRCode;

            // Set barcode data
            settings.Data2D = "Hello, World";

            // Set the other attributes of the barcode
            settings.X = 1.5f;
            settings.QRCodeECL = QRCodeECL.H;
            settings.ShowTopText = false;
            settings.ShowText = false;

            // Create a BarCodeGenerator object
            BarCodeGenerator generator = new BarCodeGenerator(settings);

            // Generate a barcode image
            Image image = generator.GenerateImage();

            // Create a Document object
            Document document = new Document();

            // Load a Word file
            document.LoadFromFile("C:\\Users\\Administrator\\Desktop\\target.docx");

            // Get a specific section 
            Section section = document.Sections[0];

            // Add a paragraph
            Paragraph paragraph = section.AddParagraph();

            // Add the barcode image to the paragraph
            paragraph.AppendPicture(image);

            // Save the document to a different Word file
            document.SaveToFile("Barcode.docx", FileFormat.Docx);

            // Dispose resources
            document.Dispose();
        }
    }
}

C#: Create Barcodes in a Word Document

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.

Insert Background Image in WPF

2012-10-22 02:51:53 Written by Koohji

Excel Background Image, one kind of page layout setting, is used to beautify files. Actually, with a beautiful background image, the Excel file will be more attractive to readers. Also, different from inserting image in Excel directly, background image will not cover data information. It means that all the data in Excel can be displayed even though background image is inserted.

Spire.XLS for WPF, a professional component to operate Excel files in WPF applications, enables users to insert background image in Excel. This guide will focus on how to realize this function by using C#, VB.NET.

Assign value for BackgroundImage property of PageSetup in Worksheet class to insert background image. Because the type of BackgroundImage is Bitmap, so the assigned value must be bitmap image. The following screenshot shows result after inserting background image.

Insert Excel Background Image

Download and install Spire.XLS for WPF. Then add a button in MainWindow. Double click the button to use the following code to insert background image in Excel.

[C#]
         Bitmap bm = new Bitmap(Image.FromFile(@"E:\Work\Documents\SampleImage\Flower.jpg"));
            sheet.PageSetup.BackgoundImage = bm;
[VB.NET]
         Dim bm As New Bitmap(Image.FromFile("E:\Work\Documents\SampleImage\Flower.jpg"))
        sheet.PageSetup.BackgoundImage = bm

Spire.XLS allows user to operate Excel document directly such as save to stream, save as web response, copy, lock/unlock worksheet, set up workbook properties, etc. As a professional WPF/.NET/Silverlight Excel component, it owns the ability of inserting content into Excel document, formatting cells and converting Excel documents to popular office file formats. Spire.XLS for WPF supports Excel 97-2003, Excel 2007 and Excel 2010.

Convert RTF to HTML in WPF

2012-10-15 07:24:19 Written by Koohji

This section will show you a detail solution to easily convert RTF to HTML in your WPF application via a .NET Word component. Only two lines of core code in total will be used to realize your RTF to HTML task in this solution.

Spire.Doc for WPF, as a professional MS Word component on WPF, enables you to accomplish RTF to HTML task through following two methods: Document.LoadFromFile(string fileName, FileFormat fileFormat) called to load your RTF file from system and Document. SaveToFile(string ilename, FileFormat fileFormat) is used to save the RTF file as HTML.

Now, you can download Spire.Doc for WPF and then, view the effect of RTF to HTML task as below picture:

RTF to HTML

Sample Code:

[C#]
using Spire.Doc;
using Spire.Doc.Documents;

namespace wpfrtftohtml
{
    public partial class MainWindow : Window
    {
        public MainWindow()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, RoutedEventArgs e)
        {
            //Load RTF file
            Document document = new Document();
            document.LoadFromFile(@"..\wpfrtftohtml.rtf", FileFormat.Rtf);
            //Convert rtf to html
            document.SaveToFile("rtftohtml.html", FileFormat.Html);
        }
    }
}
[VB.NET]
Imports Spire.Doc
Imports Spire.Doc.Documents

Namespace wpfrtftohtml
    
    Public Class MainWindow
        Inherits Window
        Public Sub New()
            MyBase.New
            InitializeComponent
        End Sub
        
        Private Sub button1_Click(ByVal sender As Object, ByVal e As RoutedEventArgs)
            'Load RTF file
            Dim document As Document = New Document
            document.LoadFromFile("..\wpfrtftohtml.rtf", FileFormat.Rtf)
            'Convert rtf to html
            document.SaveToFile("rtftohtml.html", FileFormat.Html)
        End Sub
    End Class
End Namespace

A CSV (Comma Separated Values) file is a plain text file that contains data separated by commas. It is widely used to import or export data from one application to another. In some cases, you might need to do conversions between CSV and Excel. In this article, you will learn how to implement this function programmatically in C# and VB.NET using Spire.XLS for .NET library.

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 

Convert Excel to CSV in C# and VB.NET

The following are the steps to convert Excel to CSV:

  • Create an instance of Workbook class.
  • Load an Excel file using Workbook.LoadFromFile() method.
  • Get the desired worksheet by its index using Workbook.Worksheets[index] property.
  • Save the worksheet as CSV using XlsWorksheet.SaveToFile() method. You can choose one of the following overloaded SaveToFile() methods:
    • SaveToFile(string fileName, string separator)
    • SaveToFile(string fileName, string separator, Encoding encoding)
    • SaveToFile(string fileName, string separator, bool retainHiddenData)
  • C#
  • VB.NET
using Spire.Xls;
using System.Text;

namespace ConvertAWorksheetToCsv
{
    class Program
    {
        static void Main(string[] args)
        {
            //Create an instance of Workbook class
            Workbook workbook = new Workbook();
            //Load an Excel file
            workbook.LoadFromFile("Sample.xlsx");

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

            //Save the worksheet as CSV
            sheet.SaveToFile("ExcelToCSV.csv", ",", Encoding.UTF8);
        }
    }
}

C#/VB.NET: Convert Excel to CSV and Vice Versa

Convert CSV to Excel in C# and VB.NET

The following are the steps to convert CSV to Excel:

  • Create an instance of Workbook class.
  • Load a CSV file using Workbook.LoadFromFile(string fileName, string separator, int startRow, int startColumn) method.
  • Get the desired worksheet by its index using Workbook.Worksheets[index] property.
  • Access the used range of the worksheet using Worksheet.AllocatedRange property. Then set CellRange.IgnoreErrorOptions property as IgnoreErrorType.NumberAsText to ignore possible errors while saving the numbers in the range as text.
  • Autofit columns and rows using CellRange.AutoFitColumns() and CellRange.AutoFitRows() methods.
  • Save the CSV to Excel using Workbook.SaveToFile(string fileName, ExcelVersion version) method.
  • C#
  • VB.NET
using Spire.Xls;

namespace ConvertCsvToExcel
{
    class Program
    {
        static void Main(string[] args)
        {
            //Create an instance of Workbook class
            Workbook workbook = new Workbook();

            //Load a CSV file
            workbook.LoadFromFile(@"ExcelToCSV.csv", ",", 1, 1);

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

            //Access the used range in the worksheet
            CellRange usedRange = sheet.AllocatedRange;
            //Ignore errors when saving numbers in the range as text
            usedRange.IgnoreErrorOptions = IgnoreErrorType.NumberAsText;
            //Autofit columns and rows
            usedRange.AutoFitColumns();
            usedRange.AutoFitRows();

            //Save the result file
            workbook.SaveToFile("CSVToExcel.xlsx", ExcelVersion.Version2013);
        }
    }
}

C#/VB.NET: Convert Excel to CSV and CSV to Excel

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.

Excel Panes can be frozen in order to keep certain rows or columns visible when scrolling through the worksheet. This is particularly helpful when you have a huge amount of data that you need to deal with. In this article, you will learn how to freeze rows or/and columns in C# and VB.NET using Sprie.XLS for .NET.

Spire.XLS provides the Worksheet.FreezePanes(int rowIndex, int columnIndex) method to freeze all rows and columns above and left of the selected cell which is determined by the rowIndex and the columnIndex.

C#/VB.NET: Freeze Rows and Columns in Excel

The following sections will demonstrate how to:

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

Freeze the Top Row

To freeze the top row, the selected cell should be the cell (2, 1) – “A2”. The following are the steps to freeze the top row using Spire.XLS for .NET.

  • Create a Workbook object.
  • Load an Excel file using Workbook.LoadFromFile() method.
  • Get a specific worksheet through Workbook.Worksheets[sheetIndex] property.
  • Freeze the top row by passing (2, 1) to the Worksheet.FreezePanes(int rowIndex, int columnIndex) method as the parameter.
  • Save the workbook to another Excel file using Workbook.SaveToFile() method.
  • C#
  • VB.NET
using Spire.Xls;

namespace FreezeTopRow
{
    class Program
    {
        static void Main(string[] args)
        {
            //Create a Workbook instance
            Workbook workbook = new Workbook();

            //Load an Excel document
            workbook.LoadFromFile(@"C:\Users\Administrator\Desktop\sample.xlsx");

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

            //Freeze the top row
            sheet.FreezePanes(2, 1);

            //Save to another file
            workbook.SaveToFile("FreezeTopRow.xlsx", ExcelVersion.Version2016);
        }
    }
}

C#/VB.NET: Freeze Rows and Columns in Excel

Freeze the First Column

To freeze the first column, the selected cell should be the cell (1, 2) – “B1”. The following are the steps to freeze the first column using Spire.XLS for .NET.

  • Create a Workbook object.
  • Load an Excel file using Workbook.LoadFromFile() method.
  • Get a specific worksheet through Workbook.Worksheets[sheetIndex] property.
  • Freeze the top row by passing (1, 2) to the Worksheet.FreezePanes(int rowIndex, int columnIndex) method as the parameter.
  • Save the workbook to another Excel file using Workbook.SaveToFile() method.
  • C#
  • VB.NET
using Spire.Xls;

namespace FreezeFirstColumn
{
    class Program
    {
        static void Main(string[] args)
        {
            //Create a Workbook instance
            Workbook workbook = new Workbook();

            //Load an Excel document
            workbook.LoadFromFile(@"C:\Users\Administrator\Desktop\sample.xlsx");

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

            //Freeze the first column
            sheet.FreezePanes(1, 2);

            //Save to another file
            workbook.SaveToFile("FreezeFirstColumn.xlsx", ExcelVersion.Version2016);
        }
    }
}

C#/VB.NET: Freeze Rows and Columns in Excel

Freeze the First Row and the First Column

To freeze the first row and the first column, the selected cell should be the cell (2, 2) – “B2”. The following are the detailed steps.

  • Create a Workbook object.
  • Load an Excel file using Workbook.LoadFromFile() method.
  • Get a specific worksheet through Workbook.Worksheets[sheetIndex] property.
  • Freeze the first row and the first column by passing (2, 2) to the Worksheet.FreezePanes(int rowIndex, int columnIndex) method as the parameter.
  • Save the workbook to another Excel file using Workbook.SaveToFile() method.
  • C#
  • VB.NET
using Spire.Xls;

namespace FreezeFirstRowAndFirstColumn
{
    class Program
    {
        static void Main(string[] args)
        {
            //Create a Workbook instance
            Workbook workbook = new Workbook();

            //Load an Excel document
            workbook.LoadFromFile(@"C:\Users\Administrator\Desktop\sample.xlsx");

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

            //Freeze the first row and the first column
            sheet.FreezePanes(2, 2);

            //Save to another file
            workbook.SaveToFile("FreezeFirstRowAndFirstColumn.xlsx", ExcelVersion.Version2016);
        }
    }
}

C#/VB.NET: Freeze Rows and Columns in Excel

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.

page 70