Building reports is a common task for me and I often struggle putting images in Excel so this post is just a small reference for myself to easy find how I have done it before.

I have build SQL before which would build html to display product images.

SQL like:

select '<tr><td>' + productid + '</td></tr>' from products  

And would paste that in a text file which has <table>…</table> and would save it as .html

That’s ugly and hard to work with, but it would/could show images (if you use <img src=’http://www.google.com/’ etc.)

Copying from the SMSS result panel

image

and pasting in Excel is an option to work with the data (apply extra filters or ordering etc.)

The open source SQL Operations Studio even has a community contribution to export directly to Excel! I just found out searching for the download link that the product is renamed to Azure Data Studio

https://docs.microsoft.com/nl-nl/sql/azure-data-studio/download?view=sql-server-2017

But then you only have a filename or uri or (local) path to a file in a sheet

image

I stored the Excel file as one with macro’s enabled and enabled the developer menu on the lint.

  1. Click the File tab.
  2. Click Options.
  3. Click Customize Ribbon.
  4. Under Customize the Ribbon and under Main Tabs, select the Developer check box.

And I used this small VB sub:

Sub PlacePics()

    Dim Path As String, Pics As Range, Pic As Range
    
    Path = "https://www.ourcompany.com/images/"
    Set Pics = ActiveSheet.Range("F2:F2164")
        
    For Each Pic In Pics
        On Error Resume Next
        Pic.RowHeight = 100
        Pic.Offset(0, 0).Select
        ActiveSheet.Pictures.Insert(Path & Pic.Value).Select
    Next Pic
End Sub

It might take a while to process (depending on the amount of rows). But it gets the job done.

Good luck!

Pin on pinterest Plus on Googleplus Post on LinkedIn

I am currently working on a side-project with the code-first approach. I really like how the framework does migrations and you can use the .sql files in the migration folder etc.

But I am still wrapping my head around the model/poco classes I want to use and the data structure is still changing in this stage of development. I have moved from 1-n relations to n-n etc.

I sometimes stumbled on a situation where there were foreign key constraints which made it (almost) impossible to process a migration. I also did not found how to revert one or cancel one if you have a pending one and cannot update the database.

Because I was still starting with the project I could not bother to loose all the data and start over with a clean/empty database. I have read several blogs about how to do this, but I wanted to blog my situation. Perhaps it’s useful for someone, perhaps it’s just an online note to myself.

Here is how I did it:

  1. Delete the ‘__MigrationHistory’ table
  2. Delete all files in the ‘Migrations’ folder of your project except the ‘Configuration.cs’
  3. Delete all other tables (one by one) in the DB
  4. In the package manager console, select the correct project and ‘Add-Migration <name>’
  5. Update-Database (this will recreate all tables and use the initial data from the configuration file, if you had any)

A lot of blogposts are based on keeping your data. In that case, you should skip step 3 (obvious) and comment out the ‘Up’ method when you have added your migration and have the generated file open.

This is a slightly modified version of this blogpost which uses the brand new SQL Operations Studio 0.23.6


Good luck!

Pin on pinterest Plus on Googleplus Post on LinkedIn