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
0 Comments

This post covers storing and retrieving settings with a WPF combobox for the UI. If you are looking for a solution to store settings across devices, for instance a Windows Surface tablet and a Windows Phone powered device, you should check out the blog of Mike Taulty. That is called roaming data storage. And if you are not working on an app for Windows RT/metro or Windows Phone but are just coding some WPF application, you should read this post from Scott Hanselman.

Let’s start with some code now that it’s clear what this post covers and what it doesn’t cover.

Here is my XAML from my userinterface:

<ComboBox x:Name="cbCountry" 
    SelectionChanged="ComboBox_SelectionChanged" 
    PlaceholderText="Select country" SelectedValuePath="Tag">
    <ComboBoxItem Content="Australia" Tag="au"/>
    <ComboBoxItem Content="New Zealand" Tag="nz"/>
</ComboBox>

Please note the ‘SelectedValuePath’ is being set to ‘Tag’, otherwise it would grab the ‘comboboxitem’ or ‘content’ like this:

var comboboxContent = ((ComboBoxItem)cbCountry.SelectedValue).Content;

With the value path set to tag, it will grab ‘au’ or ‘nz’ as selected value.

This is the constructor of the xaml page containing my settings.

var localSettings = Windows.Storage.ApplicationData.Current.LocalSettings;

this.InitializeComponent();

if (localSettings.Values.ContainsKey("country"))
{
    cbCountry.SelectedValue = localSettings.Values["country"];
}

and this is the code for the selectionchanged event of the combobox.

private void ComboBox_SelectionChanged(object sender, SelectionChangedEventArgs e)
{
    var localSettings = Windows.Storage.ApplicationData.Current.LocalSettings;
    localSettings.Values["country"] = cbCountry.SelectedValue;
}

Good luck with this small code sample. If this is not what you are looking for, please check the links mentioned at the start. I strongly believe that they take you to the code that you are looking for (if the code above is insufficient)

Pin on pinterest Plus on Googleplus Post on LinkedIn
0 Comments

I have a table filled with numbers. It is a table with the name numbers and there is only one field named ‘num’ which is an integer (so max value is 2.147.483.647 as seen on MSDN) and the integer is  an identity and unique.

2014-03-05 12_10_47-VM530.ssinternational - dbo.numbers - Microsoft SQL Server Management Studio

The table just contains numbers from 1 until 10.000. I use it to inner join, for instance:

You can have a table with a row: productA is 10 times in stock. You can then inner join the numbers table if you need 10 rows of productA.

select * from products 
inner join numbers on num <= stock

But I only had 10.000 rows in it and encountered a bug in my system because 10.000 was not enough. So I had to increase it. I have found several solutions to create a numbers table. But no snippet to update an existing number table.

So here is my snippet to add additional numbers to your number table:

begin transaction
SET IDENTITY_INSERT [numbers] ON

DECLARE @i INT;
SELECT @i = 10001;
SET NOCOUNT ON
WHILE @i <= 1000000
BEGIN
    INSERT INTO [numbers] (num) VALUES (@i);
    select @i= @i + 1;
end;

SET IDENTITY_INSERT [numbers] OFF
commit
Good luck!
Pin on pinterest Plus on Googleplus Post on LinkedIn