dynamically generate the headers in excel using spreadsheet gear

Question

Example: Let say A,B,C are columns and 1,2,3 are rows in a sheet.For A column 1,2,3 rows need to merge display as single rows how using spreadsheet gear A B C 1 2 3

Answer 1

I'm not sure what you mean about dynamically generating headers. What is your source of data? To merge columns or rows, you can use the Merge method of the IRange class.

workbookView1.ActiveWorksheet.Cells["A1:A3"].Merge()

Merging into one cell will keep the upper-left most data only.

Answer 2

What you can do is use reflection to send a Enumerable type and convert it to DataTable.

 private static DataTable ConvertToDataTable(IEnumerable<T> enumerable)
        {
            var properties = TypeDescriptor.GetProperties(typeof(T));
            var table = new DataTable();
            foreach (PropertyDescriptor prop in properties)
                table.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);
            foreach (var item in enumerable)
            {
                var row = table.NewRow();
                foreach (PropertyDescriptor prop in properties)
                    row[prop.Name] = prop.GetValue(item) ?? DBNull.Value;
                table.Rows.Add(row);
            }
            return table;
        }

After that let the spreadsheet gear do its job. Convert it to memory stream and then to byte[]

Pass the byte[] to FileContentResult