Code for utilizing a db backend and spreadsheet file format

[Jason Durnil]

After searching many places I put this together to enable interaction with a db and the spreadsheet as it really does not have code to use a straight up web api. Piecing together from various syncfusion sources this will load straight from a file on the server, it will save to the server and turn the posted data into a xlsio workbook you can modify at will. It has a load from database method that grabs the file server side and opens it up with xlsio capabilities for you to modify at will, this is where you can assign database values directly to the cells and sends the workbook back to the client as json. On the save to server method it does create a usable xlsio workbook to gather data from to write to your database I have also written a function that turns all the recorded data into a list of dictionaries, very usable for assigning data to table.

c#

using System;

using System.Collections.Generic;

using System.IO;

using System.Linq;

using System.Web;

using System.Web.Mvc;

using Newtonsoft.Json;

using Syncfusion.EJ2.Spreadsheet;

using Syncfusion.XlsIO;

namespace SpreadsheetMVC.Controllers

{

    public class HomeController : Controller

    {

        public ActionResult Index()

        {

            return View();

        }

        public ActionResult Open(OpenRequest openRequest)

        {

            return Content(Workbook.Open(openRequest));

        }

        public void Save(SaveSettings saveSettings)

        {

            Workbook.Save(saveSettings);

        }

        public string SaveToServer(SaveSettings saveSettings)

        {

            ExcelEngine excelEngine = new ExcelEngine();

            IApplication application = excelEngine.Excel;

            List<Dictionary<string, string>> records = TransformData(saveSettings);

            try

            {

                // Convert Spreadsheet data as Stream

                Stream fileStream = Workbook.Save<Stream>(saveSettings);

                IWorkbook workbook = application.Workbooks.Open(fileStream);

                var filePath = HttpContext.Server.MapPath("~/Files/") + saveSettings.FileName + ".xlsx";

                workbook.SaveAs(filePath);

                return "Spreadsheet saved successfully.";

            }

            catch (Exception ex)

            {

                // exception code comes here

                return "Failure";

            }

        }

        private List<Dictionary<string, string>> TransformData(SaveSettings saveSettings)

        {

            List<Dictionary<string, string>> records = new List<Dictionary<string, string>>();

            JsonSerializer serializer = new JsonSerializer();

            List<string> columns = new List<string>();

            dynamic deserializedProduct = JsonConvert.DeserializeObject<dynamic>(saveSettings.JSONData);

            dynamic rows = deserializedProduct["sheets"][0]["rows"];

            //construct columns

            var i = 0;

            dynamic columnsD = rows[0]["cells"];

            foreach (var cell in columnsD)

            {

                var column = cell.value.ToString();

                columns.Add(column);

            }

            foreach (var row in rows)

            {

                Dictionary<string, string> record = new Dictionary<string, string>();

                var cells = row["cells"];

                if (cells[0].value != null)

                {

                    var n = 0;

                    if (i != 0)

                    {

                        foreach (var cell in cells)

                        {

                            if (cell.value != null)

                            {

                                if (columns[n] == "Delivery Date")

                                {

                                    DateTime baseDate = DateTime.Parse("Jan 1, 1900");

                                    DateTime calcDate = baseDate.AddDays(int.Parse(cell.value.ToString()) - 2);

                                    record.Add(columns[n], calcDate.ToString());

                                }

                                else

                                {

                                    record.Add(columns[n], cell.value.ToString());

                                }

                            }

                            n++;

                        }

                        records.Add(record);

                    }

                    i++;

                }

            }

            return records;

        }

        [HttpGet]

        public string LoadFromDatabase()

        {

            ExcelEngine excelEngine = new ExcelEngine();

            IWorkbook workbook;

            FileStream fs = System.IO.File.Open(HttpContext.Server.MapPath("~/Files/") + "Sample.xlsx", FileMode.Open); // converting excel file to stream

            workbook = excelEngine.Excel.Workbooks.Open(fs, ExcelOpenType.Automatic);

            IWorksheet worksheet = workbook.Worksheets[0];

            worksheet.Range["A1"].Text = "ThisWorks";

            MemoryStream outputStream = new MemoryStream();

            workbook.SaveAs(outputStream);

            HttpPostedFileBase fileBase = (HttpPostedFileBase)new HttpPostedFile(outputStream.ToArray(), "Sample.xlsx");

            HttpPostedFileBase[] files = new HttpPostedFileBase[1];

            files[0] = fileBase;

            OpenRequest open = new OpenRequest();

            open.File = files;

            fs.Close();

            return Workbook.Open(open);

        }

        public ActionResult About()

        {

            ViewBag.Message = "Your application description page.";

            return View();

        }

        public ActionResult Contact()

        {

            ViewBag.Message = "Your contact page.";

            return View();

        }

    }

    public class HttpPostedFile : HttpPostedFileBase

    {

        private readonly byte[] fileBytes;

        public HttpPostedFile(byte[] fileBytes, string fileName)

        {

            this.fileBytes = fileBytes;

            this.InputStream = new MemoryStream(fileBytes);

            this.FileName = fileName + ".xlsx";

        }

        public override int ContentLength => fileBytes.Length;

        public override string FileName { get; }

        public override Stream InputStream { get; }

    }

}

cshtml

@{

    ViewBag.Title = "Home Page";

}

<button class="e-btn" id="saveButton">Save To Server</button>

<button class="e-btn" id="LoadFromDatabase">Load</button>

@Html.EJS().Spreadsheet("spreadsheet").OpenUrl("Home/Open").SaveUrl("Home/Save").Created("onCreated").Render()

<script>

    document.getElementById('saveButton').addEventListener('click', function () {

        var ssObj = ej.base.getComponent(document.getElementById('spreadsheet'), 'spreadsheet');

        ssObj.saveAsJson().then((response) => {

                var formData = new FormData();

                formData.append('JSONData', JSON.stringify(response.jsonObject.Workbook));

                formData.append('fileName', 'Sample');

            formData.append('saveType', 'Xlsx');

            $.ajax({

                type: "POST",

                url: "Home/SaveToServer",

                data: { JSONData: JSON.stringify(response.jsonObject.Workbook), fileName: "Sample", saveType: "xlsx" },

                success: function () {

                }

            })

        });

        });

    // Intially loading the excel file in spreadsheet from the server

    function onCreated() {

        var request = new XMLHttpRequest();

        request.responseType = "blob";

        request.onload = () => {

            var ssObj = ej.base.getComponent(document.getElementById('spreadsheet'), 'spreadsheet');

            var file = new File([request.response], "Sample.xlsx");

            ssObj.open({ file: file });

        }

        request.open("GET", "/Files/" + "Sample.xlsx");

        request.send();

    }

</script>

<script>

    $(function () {

        document.getElementById("LoadFromDatabase").onclick = function () {

            var spreadsheetObj = ej.base.getComponent(document.getElementById('spreadsheet'), 'spreadsheet');

            fetch('/Home/LoadFromDataBase', {

                method: 'GET',

                headers: {

                    'Content-Type': 'application/json',

                }

            })

                .then((response) => response.json())

                .then((data) => {

                    spreadsheetObj.openFromJson({ file: data }); // convert the json data to file and loaded into spreadsheet

                })

        }

    })

</script>