· 7 years ago · Jan 31, 2019, 03:34 AM
1using DocumentFormat.OpenXml;
2using DocumentFormat.OpenXml.Packaging;
3using DocumentFormat.OpenXml.Spreadsheet;
4using System;
5using System.Collections.Generic;
6using System.Data;
7using System.Globalization;
8using System.IO;
9using System.Linq;
10using System.Net;
11using System.Runtime.Serialization.Formatters.Binary;
12using System.Text;
13using System.Threading;
14
15//goals
16//reduce overall complexity.
17//refine code to take the least amount of execution steps to process a command.
18//write a more functional server that can handle multiple clients
19
20namespace ServeReports
21{
22 public class ServeReports
23 {
24 public static DataSet reports = new DataSet("Reports");
25 private const int PORT = 8183;
26 private const int MAX_THREADS = 4;
27 private const int DATA_READ_TIMEOUT = 2_000_000;
28 private const int STORAGE_SIZE = 1024;
29
30 private static WaitHandle[] waitHandles;
31 private static HttpListener listener;
32
33 private struct ThreadParams
34 {
35 public AutoResetEvent ThreadHandle;
36 public HttpListenerContext ClientSocket;
37 public int ThreadIndex;
38 }
39 [Serializable]
40 public class TemplateObject
41 {
42 public string NameOfReport { get; set; }
43 public string[] Format { get; set; }
44 public string[,] Content { get; set; }
45 }
46
47 [Serializable]
48 public class TemplateContainer
49 {
50 public TemplateObject TObject = new TemplateObject();
51 public string[] ContentArray { get; set; }
52 public int FormatLength { get; set; }
53 public int ContentLength { get; set; }
54 }
55
56 public static void Config()
57 {
58 if (!Directory.Exists(Directory.GetCurrentDirectory() + "\Configs"))
59 {
60 Directory.CreateDirectory(Directory.GetCurrentDirectory() + "\Configs");
61 }
62 if (!Directory.Exists(Directory.GetCurrentDirectory() + "\Worksheets"))
63 {
64 Directory.CreateDirectory(Directory.GetCurrentDirectory() + "\Worksheets");
65 }
66 }
67
68 public static bool TemplateInit(string reportName, string[] header, bool createNew, HttpListenerContext socket)
69 {
70 if (TemplateInit(reportName, header, createNew))
71 {
72 socket.Response.OutputStream.Write(Encoding.UTF8.GetBytes("Successfully Initialized " + reportName), 0, ("Successfully Initialized " + reportName).Length);
73 return true;
74 }
75 socket.Response.OutputStream.Write(Encoding.UTF8.GetBytes("Failed Initialization " + reportName), 0, ("Failed Initialization " + reportName).Length);
76 return false;
77 }
78
79 //initializes Excel Template
80 //<Parameter> ReportName: Names the Sheet and Excel
81 //<Parameter> Header : array of values
82 //<Parameter> CreateNew : boolean indicating if the template needs to be created or updated.
83 public static bool TemplateInit(string reportName, string[] header, bool createNew)
84 {
85 string reportString = reportName;
86 BinaryFormatter binFormatter = new BinaryFormatter();
87 if (header.Length != 0)
88 {
89 //Handle Creation of new template
90 if (createNew)
91 {
92 try
93 {
94 //build and store template
95 TemplateContainer Template = new TemplateContainer();
96 //name the report
97 Template.TObject.NameOfReport = reportName;
98 //StoreLength
99 Template.FormatLength = header.Length;
100 //initialize the format string
101 Template.TObject.Format = new string[Template.FormatLength];
102 //fill out the Format
103 header.CopyTo(Template.TObject.Format, 0);
104 //serialize Template to remember the reports we have setup.
105 FileStream fs = new FileStream(Directory.GetCurrentDirectory() + "\Configs\" + reportString, FileMode.Create, FileAccess.Write, FileShare.None);
106 binFormatter.Serialize(fs, Template);
107 fs.Close();
108 return true;
109 }
110 //write all exceptions to console window on server
111 catch (Exception ex)
112 {
113 throw ex;
114 }
115 //clean up
116 finally
117 {
118 binFormatter = null;
119 }
120 }
121 //UPDATE Template
122 else
123 {
124 try
125 {
126 //check if report config exists
127 if (File.Exists(Directory.GetCurrentDirectory() + "\Configs\" + reportString))
128 {
129 //Deserialize TemplateObject
130 FileStream fs = new FileStream(Directory.GetCurrentDirectory() + "\Configs\" + reportString, FileMode.Open, FileAccess.Read, FileShare.None);
131 TemplateContainer Template = (TemplateContainer)binFormatter.Deserialize(fs);
132 fs.Close();
133 //write out the header
134 Template.TObject.Format = new string[header.Length];
135 header.CopyTo(Template.TObject.Format, 0);
136 //realign content if possible
137 if (Template.ContentLength > 0 && Template.ContentLength % Template.FormatLength == 0)
138 {
139 TemplateFill(reportName, Template.ContentArray);
140 }
141 fs = new FileStream(Directory.GetCurrentDirectory() + "\Configs\" + reportString, FileMode.Open, FileAccess.Write, FileShare.None);
142 binFormatter.Serialize(fs, Template);
143 fs.Close();
144 return true;
145 }
146 else
147 {
148 Console.Write("Configuration does not exist, can not update what does not exist.");
149 return false;
150 }
151 }
152 catch (Exception ex)
153 {
154 throw ex;
155 }
156 finally
157 {
158 binFormatter = null;
159 }
160 }
161 }
162 return false;
163 }
164
165 public static bool TemplateFill(string reportName, string[] content, HttpListenerContext socket)
166 {
167 if (TemplateFill(reportName, content))
168 {
169 socket.Response.OutputStream.Write(Encoding.UTF8.GetBytes(reportName + " successfully filled"), 0, (reportName + " successfully filled").Length);
170 return true;
171 }
172 socket.Response.OutputStream.Write(Encoding.UTF8.GetBytes(reportName + " fill failed"),0, (reportName + " fill failed").Length);
173 return false;
174 }
175
176 //Fill in Template content
177 //<Parameter> ReportName: Names the Sheet and Excel
178 //<Parameter> Content : array of values
179
180 public static bool TemplateFill(string reportName, string[] content)
181 {
182 if (content == null)
183 {
184 throw new ArgumentNullException(nameof(content));
185 }
186
187 string reportString = reportName;
188 BinaryFormatter binFormatter = new BinaryFormatter();
189 TemplateContainer Template = new TemplateContainer();
190 try
191 {
192 //check if report config exists
193 if (File.Exists(Directory.GetCurrentDirectory() + "\Configs\" + reportString))
194 {
195 FileStream fs = new FileStream(Directory.GetCurrentDirectory() + "\Configs\" + reportString, FileMode.Open, FileAccess.Read, FileShare.None);
196 Template = (TemplateContainer)binFormatter.Deserialize(fs);
197 fs.Close();
198 if (content.Length % Template.FormatLength == 0)
199 {
200 int NumberOfRowsToAdd = content.Length / Template.FormatLength;
201 int row = 0;
202 int Column = 0, pColumn = 0;
203 Template.TObject.Content = new string[NumberOfRowsToAdd, Template.FormatLength];
204 do
205 {
206 Template.TObject.Content[row, pColumn] = content[Column];
207 Column = Column + 1;
208 pColumn = pColumn + 1;
209 if (pColumn == Template.FormatLength)
210 {
211 row = row + 1;
212 pColumn = 0;
213 }
214 } while (Column < (Template.FormatLength * NumberOfRowsToAdd));
215 Template.ContentArray = content;
216 Template.ContentLength = content.Length;
217 FileStream fe = new FileStream(Directory.GetCurrentDirectory() + "\Configs\" + reportString, FileMode.Open, FileAccess.Write, FileShare.None);
218 binFormatter.Serialize(fe, Template);
219 fe.Close();
220 return true;
221 }
222 }
223 return false;
224 }
225 catch (Exception ex)
226 {
227 throw ex;
228 }
229 finally
230 {
231 binFormatter = null;
232 }
233 }
234
235 //Build the Excel File from TemplateObject
236
237 public static DataSet AddSheet(string reportName, ref DataSet ds)
238 {
239 string reportString = reportName;
240 DataTable dt = new DataTable(reportName)
241 {
242 Locale = CultureInfo.CurrentCulture
243 };
244 //init serializer
245 BinaryFormatter binFormatter = new BinaryFormatter();
246 try
247 {
248 //check if report config exists
249 if (File.Exists(Directory.GetCurrentDirectory() + "\Configs\" + reportString))
250 {
251 //Deserialize it
252 FileStream fs = new FileStream(Directory.GetCurrentDirectory() + "\Configs\" + reportString, FileMode.Open, FileAccess.Read, FileShare.None);
253 TemplateContainer template = (TemplateContainer)binFormatter.Deserialize(fs);
254 fs.Close();
255 //write out the format
256 for (int i = 1; i < template.TObject.Format.Length + 1; i++)
257 {
258 dt.Columns.Add(template.TObject.Format[i - 1]);
259 }
260 //get the numer of rows to add
261 int NumberOfRowsToAdd = template.TObject.Content.Length / template.TObject.Format.Length;
262 //get the working row
263
264 for (int rows = 0; rows < NumberOfRowsToAdd; rows++)
265 {
266 string[] array = new string[template.TObject.Format.Length];
267 for (int columns = 0; columns < template.TObject.Format.Length; columns++)
268 {
269 array[columns] = template.TObject.Content[rows, columns];
270 }
271 object[] dr = dt.NewRow().ItemArray = array;
272 dt.Rows.Add(dr);
273 }
274 ds.Tables.Add(dt);
275 return ds;
276 //handle error
277 }
278 else
279 {
280 //log.Error("No Configuration file setup for this action.");
281 return ds;
282 }
283 }
284 catch (Exception ex)
285 {
286 throw ex;
287 }
288 }
289
290 public static void DataSetToExcel(DataSet ds, string destination)
291 {
292 try
293 {
294 using (SpreadsheetDocument workbook = SpreadsheetDocument.Create(Directory.GetCurrentDirectory() + "\Worksheets\" + destination + ".xlsx", SpreadsheetDocumentType.Workbook, true))
295 {
296 workbook.AddWorkbookPart();
297 workbook.WorkbookPart.Workbook = new Workbook
298 {
299 Sheets = new Sheets()
300 };
301
302 uint sheetId = 1;
303
304 foreach (DataTable table in ds.Tables)
305 {
306 // workbook.WorkbookPart.Workbook.ExcelNamedRange(table.TableName, table.TableName, "A", "1", "I", "1");
307 WorksheetPart sheetPart = workbook.WorkbookPart.AddNewPart<WorksheetPart>();
308 SheetData sheetData = new SheetData();
309 sheetPart.Worksheet = new Worksheet(sheetData);
310
311 Sheets sheets = workbook.WorkbookPart.Workbook.GetFirstChild<Sheets>();
312 string relationshipId = workbook.WorkbookPart.GetIdOfPart(sheetPart);
313
314 if (sheets.Elements<Sheet>().Count() > 0)
315 {
316 sheetId = sheets.Elements<Sheet>().Select(s => s.SheetId.Value).Max() + 1;
317 }
318
319 Sheet sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = table.TableName };
320 sheets.Append(sheet);
321
322 Row headerRow = new Row();
323
324 List<string> columns = new List<string>();
325
326 foreach (DataColumn column in table.Columns)
327 {
328 columns.Add(column.ColumnName);
329
330 Cell cell = new Cell
331 {
332 DataType = CellValues.String,
333 CellValue = new CellValue(column.ColumnName)
334 };
335
336 headerRow.AppendChild(cell);
337 }
338
339 sheetData.AppendChild(headerRow);
340 foreach (DataRow dsrow in table.Rows)
341 {
342 Row newRow = new Row();
343 foreach (string col in columns)
344 {
345 Cell cell = new Cell
346 {
347 DataType = CellValues.String,
348 CellValue = new CellValue(dsrow[col].ToString()) //
349 };
350 newRow.AppendChild(cell);
351 }
352 sheetData.AppendChild(newRow);
353 }
354 }
355 }
356 }
357 catch (Exception ex)
358 {
359 throw ex;
360 }
361 }
362 public static void CreateServer()
363 {
364 Config();
365 waitHandles = new WaitHandle[MAX_THREADS];
366 for (int i = 0; i < MAX_THREADS; ++i)
367 {
368 waitHandles[i] = new AutoResetEvent(true);
369 }
370
371 listener = new HttpListener();
372 listener.Prefixes.Add("http://127.0.0.1:8183/");
373 listener.Start();
374
375 while (true)
376 {
377 //Console.WriteLine("Waiting for a connection");
378 HttpListenerContext sock = listener.GetContext();
379
380 // Console.WriteLine("Got a connection");
381 //Console.WriteLine("Waiting for idle thread");
382 int index = WaitHandle.WaitAny(waitHandles);
383
384 //Console.WriteLine("Starting new thread to process client");
385
386 ThreadParams context = new ThreadParams()
387 {
388 ThreadHandle = (AutoResetEvent)waitHandles[index],
389 ClientSocket = sock,
390 ThreadIndex = index
391 };
392
393 ThreadPool.QueueUserWorkItem(ProcessSocketConnection, context);
394 }
395 }
396
397 private static void ProcessSocketConnection(object threadState)
398 {
399 ThreadParams state = (ThreadParams)threadState;
400 //Console.WriteLine($"Thread {state.ThreadIndex} is processing connection{state.ClientSocket.RemoteEndPoint}");
401
402 // This should be an extra method. In general this code should be more modular!
403 byte[] recievBuffer = Encoding.UTF8.GetBytes(state.ClientSocket.Request.RawUrl);
404
405
406 // Do your data Processing in this Method.
407 DoWork(state.ClientSocket, recievBuffer);
408
409 Cleanup();
410 // This is a local Function introduced in c#7
411 void Cleanup()
412 {
413 //Console.WriteLine("Doing clean up tasks");
414 state.ClientSocket.Response.Close();
415
416 recievBuffer = new byte[STORAGE_SIZE];
417
418 state.ThreadHandle.Set();
419 }
420 }
421
422 private static void DoWork(HttpListenerContext client, byte[] data)
423 {
424
425 byte[] amp = Encoding.UTF8.GetBytes("&");
426 //Controller For API
427 ReadOnlySpan<byte> sdata = data.AsSpan<byte>();
428
429 //API Build report template Columns
430 //<Parameter> reportname: name of report to create or update
431 //<Parameter> header: comma delimenated column names.
432 //<Parameter> createnew: true/false to create or update the report
433
434 //the below example will create a new Template Object with the format set to values passed to the header parameter
435 //http://127.0.0.1:8183/?reportname=DynamicReport&header=field1,field2,field3,field4&createnew=true
436
437 //the next example matches the existing report by name and updates the header to the new values passed
438 //http://127.0.0.1:8183/?reportname=DynamicReport&header=column1,column2,column3,column4&createnew=false
439 if (sdata.StartsWith(Encoding.UTF8.GetBytes("/?reportname=")) && sdata.IndexOf(Encoding.UTF8.GetBytes("&header=")) > 0 && sdata.IndexOf(Encoding.UTF8.GetBytes("&createnew=")) > 0)
440 {
441 //string testing = Encoding.UTF8.GetString(sdata.ToArray());
442 ReadOnlySpan<byte> name = sdata.Slice(13);
443 name = name.Slice(0, name.IndexOf(amp));
444 ReadOnlySpan<byte> header = sdata.Slice(13 + name.Length + 8);
445 header = header.Slice(0, header.IndexOf(amp));
446 bool screate = sdata.Slice(sdata.LastIndexOf(amp) + 11).SequenceEqual(Encoding.UTF8.GetBytes("true")) ? true : false;
447 //Must Overload this method
448
449 TemplateInit(Encoding.UTF8.GetString(name.ToArray()), Encoding.UTF8.GetString(header.ToArray()).Split(','), screate, client);
450 }
451 //API Add report data
452 //<Parameter SET> /?reportname=nameOfReport: name of report to add content to
453 //<Parameter SET> &content=: comma delimenated column values
454
455 //example
456 //http://127.0.0.1:8183/?reportname=DynamicReport&content=1,2,3,4,a,b,c,d
457 else if (sdata.StartsWith(Encoding.UTF8.GetBytes("/?reportname=")) && sdata.IndexOf(Encoding.UTF8.GetBytes("&content=")) > 0)
458 {
459 ReadOnlySpan<byte> name = sdata.Slice(13);
460 name = name.Slice(0, name.IndexOf(amp));
461 ReadOnlySpan<byte> content = sdata.Slice(13 + name.Length + 9);
462 //Must Overload this method
463 TemplateFill(Encoding.UTF8.GetString(name.ToArray()), Encoding.UTF8.GetString(content.ToArray()).Split(','), client);
464 AddSheet(Encoding.UTF8.GetString(name.ToArray()), ref reports);
465 }
466 //API GET resulting report by name
467 //<Parameter SET> /?getreport=nameOfReport
468
469 //example
470 //http://127.0.0.1:8183/?getreport=DynamicReeport
471 else if (sdata.StartsWith(Encoding.UTF8.GetBytes("/?getreport=")))
472 {
473 ReadOnlySpan<byte> name = sdata.Slice(12);
474 DataSetToExcel(reports, Encoding.UTF8.GetString(name.ToArray()));
475 long len = new FileInfo(Directory.GetCurrentDirectory() + "\Worksheets\" + Encoding.UTF8.GetString(name.ToArray()) + ".xlsx").Length;
476 DeliverFile(client, Encoding.UTF8.GetString(name.ToArray()) + ".xlsx", int.Parse(len.ToString()));
477 }
478 //API Query available reports
479 //<Parameter> "/?reports" : basic request to list available reports
480
481 //example
482 //http://127.0.0.1:8183/?reports
483 else if (sdata.StartsWith(Encoding.UTF8.GetBytes("/?report")))
484 {
485 string sbdata = "<center><p>Available Reports</p><table>";
486 foreach (string file in Directory.EnumerateFiles(Directory.GetCurrentDirectory() + "\Worksheets\", "*.xlsx"))
487 {
488 sbdata += "<tr><td>" + Path.GetFileName(file) + "</td></tr>";
489 }
490 sbdata += "</table></center>";
491 client.Response.OutputStream.Write(Encoding.UTF8.GetBytes(sbdata), 0, sbdata.Length);
492 }
493
494 }
495
496 public static void DeliverFile(HttpListenerContext client, string fileName, int contentLength, string mimeHeader = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", int statusCode = 200)
497 {
498 HttpListenerResponse response = client.Response;
499 try
500 {
501 string prevDirectory = Directory.GetCurrentDirectory();
502 Directory.SetCurrentDirectory(prevDirectory + "\Worksheets\");
503 response.StatusCode = statusCode;
504 response.StatusDescription = "OK";
505 response.ContentType = mimeHeader;
506 response.ContentLength64 = contentLength;
507 response.AddHeader("Content-disposition", "attachment; filename=" + fileName);
508 response.SendChunked = false;
509 using (BinaryWriter bw = new BinaryWriter(response.OutputStream))
510 {
511
512 byte[] bContent = File.ReadAllBytes(fileName);
513 bw.Write(bContent, 0, bContent.Length);
514 bw.Flush();
515 bw.Close();
516 }
517 Directory.SetCurrentDirectory(prevDirectory);
518 //Console.WriteLine("Total Bytes : " + ContentLength.ToString());
519 }
520 catch (Exception ex)
521 {
522 //Console.WriteLine(ex.Message);
523 }
524 }
525 }
526
527 internal static class Program
528 {
529 /// <summary>
530 /// The main entry point for the application.
531 /// </summary>
532 [STAThread]
533 private static void Main()
534 {
535 ServeReports.CreateServer();
536 }
537 }
538}