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);
}
}
}
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);
}
}
}
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.
