Export Spreadsheet with Sortable Excel Table

[Alex]

Hello,

I am trying to export a spreadsheet utilizing RadSpreadProcessing but have run up against a wall. Specifically, I want the exported excel file to contain a sortable table but have found no way of achieving this. Is this possible? Any help would be appreciated. Thanks

Update: I have discovered sorting can be achieved using a filter, but I would still like the exported data to be in a table.

[Dess | Tech Support Engineer, Principal]

Hello, Alex,

I am not sure whether I understand completely the requirement you are trying to accomplish. Could you please elaborate?

If I am not mistaken, you are trying to export the worksheet data to XLSX format, not as a DataTable. However, the data should be sorted and stored as an Excel sortable table. Is that correct?

RadSpreadProcessing offers sorting functionality allowing you to sort the data by a certain column:

        static void Main(string[] args)
        {
            Workbook workbook = new Workbook();
            Worksheet worksheet = workbook.Worksheets.Add();
            worksheet.Name = "Salary Report Q2 2024";
            worksheet.Cells[0, 0].SetValue("Employee's name");
            worksheet.Cells[0, 1].SetValue("Job Position");
            worksheet.Cells[0, 2].SetValue("Hire Date");
            worksheet.Cells[0, 3].SetValue("Salary");
            List<Employee> employees = PopulateWithData();

            //Let’s fill the document with the employee data:

            int startRowIndex = 1;
            for (int i = 0; i < employees.Count; i++)
            {
                worksheet.Cells[startRowIndex + i, 0].SetValue(employees[i].Name);
                worksheet.Cells[startRowIndex + i, 1].SetValue(employees[i].JobPosition.ToString());
                worksheet.Cells[startRowIndex + i, 2].SetValue(employees[i].HireDate);
                worksheet.Cells[startRowIndex + i, 3].SetValue(employees[i].Salary);
            }

            worksheet.Columns[worksheet.UsedCellRange].AutoFitWidth();
            ValuesSortCondition condition = new ValuesSortCondition(0, SortOrder.Ascending);
            worksheet.Cells[1, 0, worksheet.UsedCellRange.RowCount-1, 3].Sort(condition);

            IWorkbookFormatProvider formatProvider = new XlsxFormatProvider();
            string filePath = @"FinalSpreadsheet.xlsx";
            using (FileStream output = new FileStream(filePath, FileMode.Create))
            {
                formatProvider.Export(workbook, output, TimeSpan.FromSeconds(10));
            }
            Process.Start(new ProcessStartInfo() { FileName = filePath, UseShellExecute = true });
        }
        private static List<Employee> PopulateWithData()
        {
            List<Employee> employees = new List<Employee>();
            employees.Add(new Employee("Michael Brown", JobPosition.Developer, new DateTime(2005, 1, 20), 3400));
            employees.Add(new Employee("James Hammer", JobPosition.Manager, new DateTime(2001, 2, 14), 4800));
            employees.Add(new Employee("Amanda Johns", JobPosition.QA, new DateTime(2008, 7, 30), 2600));
            employees.Add(new Employee("Daniel Bolton", JobPosition.Developer, new DateTime(2012, 4, 24), 2900));
            employees.Add(new Employee("Lucas Smith", JobPosition.Manager, new DateTime(2007, 2, 12), 4100));
            employees.Add(new Employee("George Stones", JobPosition.QA, new DateTime(2009, 5, 6), 2850));
            employees.Add(new Employee("Victoria Sloane", JobPosition.Manager, new DateTime(2004, 8, 31), 3150));
            employees.Add(new Employee("Patrick Holmes", JobPosition.Developer, new DateTime(2001, 9, 18), 4200));
            employees.Add(new Employee("Thomas Porter", JobPosition.Developer, new DateTime(2006, 4, 12), 3450));
            return employees;
        }

        public class Employee
        {
            public string Name { get; set; }

            public JobPosition JobPosition { get; set; }

            public DateTime HireDate { get; set; }

            public double Salary { get; set; }

            public Employee(string name, JobPosition jobPosition, DateTime hireDate, double salary)
            {
                this.Name = name;
                this.JobPosition = jobPosition;
                this.HireDate = hireDate;
                this.Salary = salary;
            }
        }

        public enum JobPosition
        {
            Developer,
            QA,
            Manager
        }

The following screenshots illustrate the achieved result.

Before:

After:

XlsxFormatProvider makes it easy to import and export XLSX (Excel Workbook) files. However, if you want to preserve the data as Spreadsheet tables, note that such functionality is currently not supported: 

We already have a similar feature request in our public portal. You can cast your vote for the item, track its progress, subscribe for status changes, and add your comments on the following link: SpreadProcessing: Add support for tables and table styles.

I hope this information helps. If you need any further assistance please don't hesitate to contact me. 

Regards,
Dess | Tech Support Engineer, Principal
Progress Telerik

[Alex]

Hi Dess,

Exporting as a spreadsheet table is exactly what I need. Unfortunately, due to an automated workflow, it is not feasible to manually convert each exported spreadsheet to a table. As such, I have voted for the feature request in hopes it gets added in the future. Thank you

[Yoan]

Hi Alex,

Thank you for following up with an update. I am glad to hear you were able to make some progress on your own. You are indeed correct that you can use the Filtering functionality of the SpreadProcessing library to meet the described requirements.

As for exporting the worksheet data in a table format, I can suggest using the DataTableFormatProvider which allows you to easily convert existing worksheet to DataTable and vice versa.

I hope this fits your requirements. Do not hesitate to let me know if you have any additional questions or if further assistance is required.

Regards,
Yoan
Progress Telerik

[Alex]

Hey Yoan, 

Thanks for the reply. I tried utilizing that format provider, but it doesn't quite achieve what I am looking for. After exporting the data, I would like for it to be grouped into an excel table. I was wondering if some import settings for the format provider could potentially play a role, but I was unable to find any relevant documentation for that. 

Thanks and best regards,
Alex