HttpListener Server for building reports in Excel
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();
}
}
}
c# excel http server
edited 6 mins ago
200_success
129k15153415
asked 3 hours ago
BanMeBanMe
417
$endgroup$
add a comment |
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();
}
}
}
c# excel http server
edited 6 mins ago
200_success
129k15153415
asked 3 hours ago
BanMeBanMe
417
$endgroup$
add a comment |
0
0
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();
}
}
}
c# excel http server
edited 6 mins ago
200_success
129k15153415
asked 3 hours ago
BanMeBanMe
417
$endgroup$
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();
}
}
}
c# excel http server
c# excel http server
edited 6 mins ago
200_success
129k15153415
asked 3 hours ago
BanMeBanMe
417
edited 6 mins ago
200_success
129k15153415
asked 3 hours ago
BanMeBanMe
417
edited 6 mins ago
200_success
129k15153415
edited 6 mins ago
200_success
129k15153415
edited 6 mins ago
200_success
129k15153415
129k15153415
asked 3 hours ago
BanMeBanMe
417
asked 3 hours ago
BanMeBanMe
417
asked 3 hours ago
BanMeBanMe
417
417
add a comment |
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function () {
return StackExchange.using("mathjaxEditing", function () {
StackExchange.MarkdownEditor.creationCallbacks.add(function (editor, postfix) {
StackExchange.mathjaxEditing.prepareWmdForMathJax(editor, postfix, [["\$", "\$"]]);
});
});
}, "mathjax-editing");
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "196"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fcodereview.stackexchange.com%2fquestions%2f212594%2fhttplistener-server-for-building-reports-in-excel%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
draft saved
draft discarded
Thanks for contributing an answer to Code Review Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
Use MathJax to format equations. MathJax reference.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fcodereview.stackexchange.com%2fquestions%2f212594%2fhttplistener-server-for-building-reports-in-excel%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
