ClosedXML | NET library for reading , manipulating and writing Excel | Data Visualization library

Actually Image SQL datatype is a little bit old fashioned, but will translate to byte[] in c#, so this o r a slightly modified code will do the trick

With PropertyInfo and Dictionary you can catch the JSON-data.

The simple answer is you are adding the service in the Server project and then trying to consume it in the client project. The normal answer would be to set the service up in the Client project.

That however wouldn't work. I can see file system operations which won't work on the Client. You need to re-design and use an API call to generate and provide the Workbook from the server.

You can check out my library Sylvan.Data.Excel. It has no external dependencies, is cross plat, open-source, MIT licensed, and also the fastest Excel data reader for .NET. It supports both .xls, .xlsx, and .xlsb all through the same API. It is not as fully featured as some other libraries, it only supports reading (not writing), but it should be a pretty direct replacement for ACE, because it implements DbDataReader. If you run into any issues/questions feel free to open them in the GitHub repo.

Using it is pretty straightforward:

I found this to be a little clunky but I believe this to be the correct approach.

First You have to create the worksheet by the title. Then send datatable and work sheet to new function. Then You can create custom excel file.

There is principle in programming called DRY. It stands for Don't Repeat Yourself. I think you can see that you have violated this principle. When you are building Sql strings, like I did here, be careful of spaces. You need a space between words. I tried to put it at the end of the strings. The Debug.Print will show you if the string came out correctly.

When you have a series of If statements that target the same value, a Select Case is easier to read and write.

It is not necessary to add column headers to the DataGridView. The binding of the DataTable to the grid will handle this. I believe the columns will auto-size to accomidate the data.

Several of the database objects in ADO.net provide a Dispose method where they release unmanaged resources. If there is a Dispose method it should be called. Using...End Using blocks do this for us even if there is an error (it will also close the connection). These objects need to be declared in the method where they are used in a Using block.

Don't mess with the DataGridView. You only need the DataTable to interact with Excel.

The Wrapper to close Excel might seem odd but Excel in a b_tch to get rid of. I believe the arrangement here works.

Solved it!

Just add template.Generate(); after adding the variables into the template.

You may want to re-think your approach to reading the excel file. One possible issue is the if (firstRow) { … … statement, which is odd and makes a dangerous assumption. The code “assumes” that each column of data “has” a header cell. In other words, the number of columns added to the DataTable will be determined by the number of cells found (with some text in the cell) on the “FIRST” row. What if a column of data does NOT have a header cell?

Therefore, if there are any rows that have data to the right of the first row’s cells with headers, then the DataTable will not have the correct number of columns… and, when the code gets to these cells in the else portion below… the code will most likely crash when i goes beyond dt’s column count.

The code needs to guarantee that dt has the correct number of columns to avoid the problem described above. One way to help is to find the two cells that define the “top-left” cell where the data begins (as it may not necessarily always be the first cell in the worksheet) and “bottom-right” cell where the “last” cell with data is located.

Once we have these two cells (top-left and bottom-right)… then, we can determine how many columns are needed in the DataTable… and… we can almost guarantee that all the data in the worksheet will fit in the DataTable.

Below is one possible solution using the ideas described above. Note, the code below does not use a particular worksheet name and simply uses the first worksheet in the given workbook.