Convert an Excel table to a range from your C#/VB.NET applications
The following example shows how you can convert an Excel table to a range by using only the GemBox.Spreadsheet .NET component. You can do a basic conversion with just a few lines of code. You only need an IDE like Visual Studio or JetBrains Rider and .NET Framework, .NET Core, or any other platform that implements .NET Standard. Follow these steps to convert from one file format to another: You can test converting your Excel tables with the interactive example below. Just upload your file that contains a table, choose output file format, click Run Example, and download the generated file. The C#/VB.NET code will be compiled, and your file will be converted with only .NET framework and the GemBox.Spreadsheet component. The code copies the table's header and data without formatting, and removes the original table. GemBox.Spreadsheet supports Excel tables for the XLSX file format only. To see more about support for tables, check the Create Excel table example.Steps for converting an Excel table to a range
Convert Excel tables online
using System;
using System.Linq;
using GemBox.Spreadsheet;
using GemBox.Spreadsheet.Tables;
class Program
{
static void Main(string[] args)
{
// If you are using the Professional version, enter your serial key below.
SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");
ExcelFile workbook = ExcelFile.Load("%InputFileName%");
// Iterate through all sheets and tables
foreach (ExcelWorksheet sheet in workbook.Worksheets)
{
foreach (Table table in sheet.Tables.ToArray())
{
// Make space to copy data
table.Range.Insert(InsertShiftDirection.Right);
// Copy header row
table.Range.GetSubrangeRelative(0, 0, table.Range.Width, 1).CopyTo(table.Range.FirstRowIndex, table.Range.FirstColumnIndex - table.Range.Width);
// Copy data
table.DataRange.CopyTo(table.Range.FirstRowIndex + 1, table.Range.FirstColumnIndex - table.Range.Width);
// Remove table
sheet.Tables.Remove(table, RemoveShiftDirection.Left);
}
}
workbook.Save("ExcelTableToRange.%OutputFileType%");
}
}
Imports System
Imports System.Linq
Imports GemBox.Spreadsheet
Imports GemBox.Spreadsheet.Tables
Module Program
Sub Main()
' If you are using the Professional version, enter your serial key below.
SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY")
Dim workbook As ExcelFile = ExcelFile.Load("%InputFileName%")
' Iterate through all sheets And tables
For Each sheet As ExcelWorksheet In workbook.Worksheets
For Each table As Table In sheet.Tables.ToArray()
' Make space to copy data
table.Range.Insert(InsertShiftDirection.Right)
' Copy header row
table.Range.GetSubrangeRelative(0, 0, table.Range.Width, 1).CopyTo(table.Range.FirstRowIndex, table.Range.FirstColumnIndex - table.Range.Width)
' Copy data
table.DataRange.CopyTo(table.Range.FirstRowIndex + 1, table.Range.FirstColumnIndex - table.Range.Width)
' Remove table
sheet.Tables.Remove(table, RemoveShiftDirection.Left)
Next
Next
workbook.Save("ExcelTableToRange.%OutputFileType%")
End Sub
End Module
Support for Excel tables
