0
\$\begingroup\$

I have coded a web-based Excel file builder and sender, that serves the file over Http(example links in code). I would like to see improvements and any suggestions you might have.

using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System;
using System.Collections.Generic;
using System.Data;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Net;
using System.Runtime.Serialization.Formatters.Binary;
using System.Text;
using System.Threading;

//goals
//reduce overall complexity.
//refine code to take the least amount of execution steps to process a command.
//write a more functional server that can handle multiple clients

namespace ServeReports
{
    public class ServeReports
    {
        public static DataSet reports = new DataSet("Reports");
        private const int PORT = 8183;
        private const int MAX_THREADS = 4;
        private const int DATA_READ_TIMEOUT = 2_000_000;
        private const int STORAGE_SIZE = 1024;

        private static WaitHandle[] waitHandles;
        private static HttpListener listener;

        private struct ThreadParams
        {
            public AutoResetEvent ThreadHandle;
            public HttpListenerContext ClientSocket;
            public int ThreadIndex;
        }
        [Serializable]
        public class TemplateObject
        {
            public string NameOfReport { get; set; }
            public string[] Format { get; set; }
            public string[,] Content { get; set; }
        }

        [Serializable]
        public class TemplateContainer
        {
            public TemplateObject TObject = new TemplateObject();
            public string[] ContentArray { get; set; }
            public int FormatLength { get; set; }
            public int ContentLength { get; set; }
        }

        public static void Config()
        {
            if (!Directory.Exists(Directory.GetCurrentDirectory() + "\\Configs"))
            {
                Directory.CreateDirectory(Directory.GetCurrentDirectory() + "\\Configs");
            }
            if (!Directory.Exists(Directory.GetCurrentDirectory() + "\\Worksheets"))
            {
                Directory.CreateDirectory(Directory.GetCurrentDirectory() + "\\Worksheets");
            }
        }

        public static bool TemplateInit(string reportName, string[] header, bool createNew, HttpListenerContext socket)
        {
            if (TemplateInit(reportName, header, createNew))
            {
               socket.Response.OutputStream.Write(Encoding.UTF8.GetBytes("Successfully Initialized " + reportName), 0, ("Successfully Initialized " + reportName).Length);
                return true;
            }
            socket.Response.OutputStream.Write(Encoding.UTF8.GetBytes("Failed Initialization " + reportName), 0, ("Failed Initialization " + reportName).Length);
            return false;
        }

        //initializes   Excel   Template
        //<Parameter>   ReportName: Names   the Sheet   and Excel
        //<Parameter>   Header  :   array   of  values
        //<Parameter>   CreateNew   :   boolean indicating  if  the template    needs   to  be  created or  updated.
        public static bool TemplateInit(string reportName, string[] header, bool createNew)
        {
            string reportString = reportName;
            BinaryFormatter binFormatter = new BinaryFormatter();
            if (header.Length != 0)
            {
                //Handle    Creation    of  new template
                if (createNew)
                {
                    try
                    {
                        //build and store   template
                        TemplateContainer Template = new TemplateContainer();
                        //name  the report
                        Template.TObject.NameOfReport = reportName;
                        //StoreLength
                        Template.FormatLength = header.Length;
                        //initialize    the format  string
                        Template.TObject.Format = new string[Template.FormatLength];
                        //fill  out the Format
                        header.CopyTo(Template.TObject.Format, 0);
                        //serialize Template    to  remember    the reports we  have    setup.
                        FileStream fs = new FileStream(Directory.GetCurrentDirectory() + "\\Configs\\" + reportString, FileMode.Create, FileAccess.Write, FileShare.None);
                        binFormatter.Serialize(fs, Template);
                        fs.Close();
                        return true;
                    }
                    //write all exceptions  to  console window  on  server
                    catch (Exception ex)
                    {
                        throw ex;
                    }
                    //clean up
                    finally
                    {
                        binFormatter = null;
                    }
                }
                //UPDATE    Template
                else
                {
                    try
                    {
                        //check if  report  config  exists
                        if (File.Exists(Directory.GetCurrentDirectory() + "\\Configs\\" + reportString))
                        {
                            //Deserialize   TemplateObject
                            FileStream fs = new FileStream(Directory.GetCurrentDirectory() + "\\Configs\\" + reportString, FileMode.Open, FileAccess.Read, FileShare.None);
                            TemplateContainer Template = (TemplateContainer)binFormatter.Deserialize(fs);
                            fs.Close();
                            //write out the header
                            Template.TObject.Format = new string[header.Length];
                            header.CopyTo(Template.TObject.Format, 0);
                            //realign   content if  possible
                            if (Template.ContentLength > 0 && Template.ContentLength % Template.FormatLength == 0)
                            {
                                TemplateFill(reportName, Template.ContentArray);
                            }
                            fs = new FileStream(Directory.GetCurrentDirectory() + "\\Configs\\" + reportString, FileMode.Open, FileAccess.Write, FileShare.None);
                            binFormatter.Serialize(fs, Template);
                            fs.Close();
                            return true;
                        }
                        else
                        {
                            Console.Write("Configuration does not exist, can not update what does not exist.");
                            return false;
                        }
                    }
                    catch (Exception ex)
                    {
                        throw ex;
                    }
                    finally
                    {
                        binFormatter = null;
                    }
                }
            }
            return false;
        }

        public static bool TemplateFill(string reportName, string[] content, HttpListenerContext socket)
        {
            if (TemplateFill(reportName, content))
            {               
                  socket.Response.OutputStream.Write(Encoding.UTF8.GetBytes(reportName + " successfully filled"), 0, (reportName + " successfully filled").Length);  
                return true;
            }
            socket.Response.OutputStream.Write(Encoding.UTF8.GetBytes(reportName + " fill failed"),0, (reportName + " fill failed").Length);
            return false;
        }

        //Fill  in  Template    content
        //<Parameter>   ReportName: Names   the Sheet   and Excel
        //<Parameter>   Content :   array   of  values

        public static bool TemplateFill(string reportName, string[] content)
        {
            if (content == null)
            {
                throw new ArgumentNullException(nameof(content));
            }

            string reportString = reportName;
            BinaryFormatter binFormatter = new BinaryFormatter();
            TemplateContainer Template = new TemplateContainer();
            try
            {
                //check if  report  config  exists
                if (File.Exists(Directory.GetCurrentDirectory() + "\\Configs\\" + reportString))
                {
                    FileStream fs = new FileStream(Directory.GetCurrentDirectory() + "\\Configs\\" + reportString, FileMode.Open, FileAccess.Read, FileShare.None);
                    Template = (TemplateContainer)binFormatter.Deserialize(fs);
                    fs.Close();
                    if (content.Length % Template.FormatLength == 0)
                    {
                        int NumberOfRowsToAdd = content.Length / Template.FormatLength;
                        int row = 0;
                        int Column = 0, pColumn = 0;
                        Template.TObject.Content = new string[NumberOfRowsToAdd, Template.FormatLength];
                        do
                        {
                            Template.TObject.Content[row, pColumn] = content[Column];
                            Column = Column + 1;
                            pColumn = pColumn + 1;
                            if (pColumn == Template.FormatLength)
                            {
                                row = row + 1;
                                pColumn = 0;
                            }
                        } while (Column < (Template.FormatLength * NumberOfRowsToAdd));
                        Template.ContentArray = content;
                        Template.ContentLength = content.Length;
                        FileStream fe = new FileStream(Directory.GetCurrentDirectory() + "\\Configs\\" + reportString, FileMode.Open, FileAccess.Write, FileShare.None);
                        binFormatter.Serialize(fe, Template);
                        fe.Close();
                        return true;
                    }
                }
                return false;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                binFormatter = null;
            }
        }

        //Build the Excel   File    from    TemplateObject

        public static DataSet AddSheet(string reportName, ref DataSet ds)
        {
            string reportString = reportName;
            DataTable dt = new DataTable(reportName)
            {
                Locale = CultureInfo.CurrentCulture
            };
            //init  serializer
            BinaryFormatter binFormatter = new BinaryFormatter();
            try
            {
                //check if  report  config  exists
                if (File.Exists(Directory.GetCurrentDirectory() + "\\Configs\\" + reportString))
                {
                    //Deserialize   it
                    FileStream fs = new FileStream(Directory.GetCurrentDirectory() + "\\Configs\\" + reportString, FileMode.Open, FileAccess.Read, FileShare.None);
                    TemplateContainer template = (TemplateContainer)binFormatter.Deserialize(fs);
                    fs.Close();
                    //write out the format
                    for (int i = 1; i < template.TObject.Format.Length + 1; i++)
                    {
                        dt.Columns.Add(template.TObject.Format[i - 1]);
                    }
                    //get   the numer   of  rows    to  add
                    int NumberOfRowsToAdd = template.TObject.Content.Length / template.TObject.Format.Length;
                    //get   the working row

                    for (int rows = 0; rows < NumberOfRowsToAdd; rows++)
                    {
                        string[] array = new string[template.TObject.Format.Length];
                        for (int columns = 0; columns < template.TObject.Format.Length; columns++)
                        {
                            array[columns] = template.TObject.Content[rows, columns];
                        }
                        object[] dr = dt.NewRow().ItemArray = array;
                        dt.Rows.Add(dr);
                    }
                    ds.Tables.Add(dt);
                    return ds;
                    //handle    error
                }
                else
                {
                    //log.Error("No Configuration file setup for this action.");
                    return ds;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static void DataSetToExcel(DataSet ds, string destination)
        {
            try
            {
                using (SpreadsheetDocument workbook = SpreadsheetDocument.Create(Directory.GetCurrentDirectory() + "\\Worksheets\\" + destination + ".xlsx", SpreadsheetDocumentType.Workbook, true))
                {
                    workbook.AddWorkbookPart();
                    workbook.WorkbookPart.Workbook = new Workbook
                    {
                        Sheets = new Sheets()
                    };

                    uint sheetId = 1;

                    foreach (DataTable table in ds.Tables)
                    {
                        //  workbook.WorkbookPart.Workbook.ExcelNamedRange(table.TableName, table.TableName, "A", "1", "I", "1");
                        WorksheetPart sheetPart = workbook.WorkbookPart.AddNewPart<WorksheetPart>();
                        SheetData sheetData = new SheetData();
                        sheetPart.Worksheet = new Worksheet(sheetData);

                        Sheets sheets = workbook.WorkbookPart.Workbook.GetFirstChild<Sheets>();
                        string relationshipId = workbook.WorkbookPart.GetIdOfPart(sheetPart);

                        if (sheets.Elements<Sheet>().Count() > 0)
                        {
                            sheetId = sheets.Elements<Sheet>().Select(s => s.SheetId.Value).Max() + 1;
                        }

                        Sheet sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = table.TableName };
                        sheets.Append(sheet);

                        Row headerRow = new Row();

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

                        foreach (DataColumn column in table.Columns)
                        {
                            columns.Add(column.ColumnName);

                            Cell cell = new Cell
                            {
                                DataType = CellValues.String,
                                CellValue = new CellValue(column.ColumnName)
                            };

                            headerRow.AppendChild(cell);
                        }

                        sheetData.AppendChild(headerRow);
                        foreach (DataRow dsrow in table.Rows)
                        {
                            Row newRow = new Row();
                            foreach (string col in columns)
                            {
                                Cell cell = new Cell
                                {
                                    DataType = CellValues.String,
                                    CellValue = new CellValue(dsrow[col].ToString())    //
                                };
                                newRow.AppendChild(cell);
                            }
                            sheetData.AppendChild(newRow);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public static void CreateServer()
        {
            Config();
            waitHandles = new WaitHandle[MAX_THREADS];
            for (int i = 0; i < MAX_THREADS; ++i)
            {
                waitHandles[i] = new AutoResetEvent(true);
            }

            listener = new HttpListener();
            listener.Prefixes.Add("http://127.0.0.1:8183/");
            listener.Start();

            while (true)
            {
                //Console.WriteLine("Waiting for a connection");
                HttpListenerContext sock = listener.GetContext();

                // Console.WriteLine("Got a connection");
                //Console.WriteLine("Waiting for idle thread");
                int index = WaitHandle.WaitAny(waitHandles);

                //Console.WriteLine("Starting new thread to process client");

                ThreadParams context = new ThreadParams()
                {
                    ThreadHandle = (AutoResetEvent)waitHandles[index],
                    ClientSocket = sock,
                    ThreadIndex = index
                };

                ThreadPool.QueueUserWorkItem(ProcessSocketConnection, context);
            }
        }

        private static void ProcessSocketConnection(object threadState)
        {
            ThreadParams state = (ThreadParams)threadState;
            //Console.WriteLine($"Thread {state.ThreadIndex} is processing connection{state.ClientSocket.RemoteEndPoint}");

            // This should be an extra method. In general this code should be more modular!
            byte[] recievBuffer = Encoding.UTF8.GetBytes(state.ClientSocket.Request.RawUrl);


            // Do your data Processing in this Method.
            DoWork(state.ClientSocket, recievBuffer);

            Cleanup();
            // This is a local Function introduced in c#7
            void Cleanup()
            {
                //Console.WriteLine("Doing clean up tasks");
                state.ClientSocket.Response.Close();

                recievBuffer = new byte[STORAGE_SIZE];

                state.ThreadHandle.Set();
            }
        }

        private static void DoWork(HttpListenerContext client, byte[] data)
        {

            byte[] amp = Encoding.UTF8.GetBytes("&");
            //Controller For API
            ReadOnlySpan<byte>  sdata = data.AsSpan<byte>();

            //API Build report template Columns
            //<Parameter> reportname: name of report to create or update
            //<Parameter> header: comma delimenated column names.
            //<Parameter> createnew: true/false to create or update the report

            //the below example will create a new Template Object with the format set to values passed to the header parameter
            //http://127.0.0.1:8183/?reportname=DynamicReport&header=field1,field2,field3,field4&createnew=true

            //the next example matches the existing report by name and updates the header to the new values passed
            //http://127.0.0.1:8183/?reportname=DynamicReport&header=column1,column2,column3,column4&createnew=false
            if (sdata.StartsWith(Encoding.UTF8.GetBytes("/?reportname=")) && sdata.IndexOf(Encoding.UTF8.GetBytes("&header=")) > 0 && sdata.IndexOf(Encoding.UTF8.GetBytes("&createnew=")) > 0)
            {
                //string testing = Encoding.UTF8.GetString(sdata.ToArray());
                ReadOnlySpan<byte> name = sdata.Slice(13);
                name = name.Slice(0, name.IndexOf(amp));
                ReadOnlySpan<byte> header = sdata.Slice(13 + name.Length + 8);
                header = header.Slice(0, header.IndexOf(amp));
                bool screate = sdata.Slice(sdata.LastIndexOf(amp) + 11).SequenceEqual(Encoding.UTF8.GetBytes("true")) ? true : false;
                //Must Overload this method

                TemplateInit(Encoding.UTF8.GetString(name.ToArray()), Encoding.UTF8.GetString(header.ToArray()).Split(','), screate, client);
            }
            //API Add report data
            //<Parameter SET> /?reportname=nameOfReport: name of report to add content to
            //<Parameter SET> &content=: comma delimenated column values

            //example
            //http://127.0.0.1:8183/?reportname=DynamicReport&content=1,2,3,4,a,b,c,d
            else if (sdata.StartsWith(Encoding.UTF8.GetBytes("/?reportname=")) && sdata.IndexOf(Encoding.UTF8.GetBytes("&content=")) > 0)
            {
                ReadOnlySpan<byte> name = sdata.Slice(13);
                name = name.Slice(0, name.IndexOf(amp));
                ReadOnlySpan<byte> content = sdata.Slice(13 + name.Length + 9);
                //Must Overload this method
                TemplateFill(Encoding.UTF8.GetString(name.ToArray()), Encoding.UTF8.GetString(content.ToArray()).Split(','), client);
                AddSheet(Encoding.UTF8.GetString(name.ToArray()), ref reports);
            }
            //API GET resulting report by name
            //<Parameter SET> /?getreport=nameOfReport

            //example
            //http://127.0.0.1:8183/?getreport=DynamicReeport
            else if (sdata.StartsWith(Encoding.UTF8.GetBytes("/?getreport=")))
            {
                ReadOnlySpan<byte> name = sdata.Slice(12);
                DataSetToExcel(reports, Encoding.UTF8.GetString(name.ToArray()));
                long len = new FileInfo(Directory.GetCurrentDirectory() + "\\Worksheets\\" + Encoding.UTF8.GetString(name.ToArray()) + ".xlsx").Length;
                DeliverFile(client, Encoding.UTF8.GetString(name.ToArray()) + ".xlsx", int.Parse(len.ToString()));
            }
            //API Query available reports
            //<Parameter> "/?reports" : basic request to list available reports

            //example
            //http://127.0.0.1:8183/?reports
            else if (sdata.StartsWith(Encoding.UTF8.GetBytes("/?report")))
            {
                string sbdata = "<center><p>Available Reports</p><table>";
                foreach (string file in Directory.EnumerateFiles(Directory.GetCurrentDirectory() + "\\Worksheets\\", "*.xlsx"))
                {
                    sbdata += "<tr><td>" + Path.GetFileName(file) + "</td></tr>";
                }
                sbdata += "</table></center>";
                client.Response.OutputStream.Write(Encoding.UTF8.GetBytes(sbdata), 0, sbdata.Length);
            }

        }

        public static void DeliverFile(HttpListenerContext client, string fileName, int contentLength, string mimeHeader = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", int statusCode = 200)
        {
            HttpListenerResponse response = client.Response;
            try
            {
                string prevDirectory = Directory.GetCurrentDirectory();
                Directory.SetCurrentDirectory(prevDirectory + "\\Worksheets\\");
                response.StatusCode = statusCode;
                response.StatusDescription = "OK";
                response.ContentType = mimeHeader;
                response.ContentLength64 = contentLength;
                response.AddHeader("Content-disposition", "attachment; filename=" + fileName);
                response.SendChunked = false;
                using (BinaryWriter bw = new BinaryWriter(response.OutputStream))
                {

                    byte[] bContent = File.ReadAllBytes(fileName);
                    bw.Write(bContent, 0, bContent.Length);
                    bw.Flush(); 
                    bw.Close();
                }
                Directory.SetCurrentDirectory(prevDirectory);
                //Console.WriteLine("Total Bytes : " + ContentLength.ToString());
            }
            catch (Exception ex)
            {
                //Console.WriteLine(ex.Message);
            }
        }
    }

    internal static class Program
    {
        /// <summary>
        /// The main entry point for the application.
        /// </summary>
        [STAThread]
        private static void Main()
        {
            ServeReports.CreateServer(); 
        }
    }
}
\$\endgroup\$
  • 1
    \$\begingroup\$ Few quick points (I don't have time to write a full reply at the moment) - don't put 2 classes in one file, don't have your classes so big (see 'SRP'), why are you so heavy on the statics? There is a time and place for them. Never throw ex;, just throw; (you lose valuable data e.g. stacktrace when 'rethrowing' like this). It's safe to call Directory.CreateDirectory even if the directory already exists (in your Config() method). \$\endgroup\$ – JᴀʏMᴇᴇ Jan 31 at 9:47
  • \$\begingroup\$ Thank you for the tips I have taken what you said, and implemented some of the changes. I localized all static variables, but I am unsure how best to attack all my static functions. \$\endgroup\$ – BanMe Jan 31 at 12:31
  • \$\begingroup\$ took you tips and made changes . :) code located here github.com/BanalityOfSeeking/ServeExcel \$\endgroup\$ – BanMe Feb 12 at 3:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.