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

We have recently moved from hosted mail (pop3/imap) to Office365. This works perfectly with Outlook 2016 on Windows. The 2016 mac version of Outlook is also really good. However it just does not auto display the shared mailboxes. This can be easily solved.

just a simple, file –> open –> other users folder. and select the shared mailbox.

Found https://community.office365.com/en-us/f/158/p/287203/877055#877055

No outlook

It is a lot harder to add a shared mailbox to the default mail.app in Mac OS X. This manual configuration did not work. It suggests to use your main account and a slash and then the shared name or alias.

Here are the configuration details (which did not work for me)

IMAP:

server: outlook.office365.com

username: my-personal-mailbox@my-domain.com/shared-folder-alias-name

password: personal-password

(if necessary: Port 993, SSL = ON and Password authentication)

SMTP:

server: smtp.office365.com

username: my-personal-mailbox@my-domain.com

password: personal-password

(if necessary: Port 587, SSL = ON and Password authentication)

 

I could not get it to work.

but this did work:

login as admin in office365 go to “users” and not the shared mailboxes.

 

snip_20160317163145

There are users for shared mailboxes, but no one uses the user account. It is just internally needed for the mailboxes. Click the one that is corresponding with the shared mailbox and reset the password.

You can now use the credentials as above. Just use the pseudo user from the user list and the new password and as mail address the one from the shared mailbox

and outlook.office365.com as server. You can use the default mail.app in Mac OS X (10.10 or 10.11) It is a lot more work then with outlook, but if you are really fond of the default application, this is a solution to keep using it without moving to outlook.

 

Enjoy and good luck!

Pin on pinterest Plus on Googleplus Post on LinkedIn
0 Comments

We have a lot of customer data in our system and I wanted to have a shared contact list with all the customer data for my co-workers.

I looking into some Office365 docs and found this walkthrough to create the folder: https://www.cogmotive.com/blog/office-365-tips/create-a-company-shared-contacts-folder-in-office-365

snip_20160322100016

So afterwards you have an empty but shared contact folder. I thought that I needed the Microsoft Graph to access these contacts. Which would require my app to be in azure and it cannot have a command line only because of the authentication. More about that approach can be found here http://dev.office.com/getting-started/office365apis

But it seems that this is not needed for my simple one time import https://msdn.microsoft.com/en-us/office/office365/api/contacts-rest-operations

I thought that it might be a job for a PowerShell script and found this http://www.infinitconsulting.com/2015/01/bulk-import-contacts-to-office-365/

But there is an other option. Use Interop, because I as an Office365 user have Outlook 2016 on my Windows 10 machine and Visual Studio 2015. So create a new (console) application and add a reference to Outlook Interop:

I used this code to get to the “customers” address book:

using System;
using Outlook = Microsoft.Office.Interop.Outlook;

namespace UploadContactsToOffice365
{
    class Program
    {
        static void Main(string[] args)
        {
            var ap = new Outlook.Application();
            
            foreach(Outlook.Folder f in ap.Session.Folders)
            {
                if (f.Name.ToLower().Contains("openbare")) // jp openbare mappen (public folders)
                {
                    Console.WriteLine(f.FullFolderPath);
                    foreach (Outlook.Folder f2 in f.Folders)
                    {
                        if (f2.Name.ToLower().Contains("alle")) // alle openbare mappen (all public folders)
                        {
                            Console.WriteLine(f2.FullFolderPath);
                            foreach (Outlook.Folder f3 in f2.Folders)
                            {
                                if (f3.Name.ToLower().Contains("klanten")) // customers (folder name)
                                {
                                    Console.WriteLine(f3.FullFolderPath);
                                    foreach (Outlook.Folder f4 in f3.Folders)
                                    {
                                        if (f4.AddressBookName.ToLower().Contains("customers"))
                                        {
                                            Console.WriteLine(f4.FullFolderPath);
                                            Console.WriteLine("----------");

                                            /// display current items:
                                            //Outlook.Items oItems = f4.Items;
                                            //for (int i = 1; i <= oItems.Count; i++)
                                            //{
                                            //    Outlook._ContactItem oContact = (Outlook._ContactItem)oItems[i];
                                            //    Console.WriteLine(oContact.FullName);
                                            //    oContact = null;
                                            //}
                                            
                                            // add test item:

                                            Outlook.ContactItem newContact = (Outlook.ContactItem)ap.CreateItem(Outlook.OlItemType.olContactItem);
                                            try
                                            {
                                                newContact.FirstName = "Jo";
                                                newContact.LastName = "Berry";
                                                newContact.Email1Address = "somebody@example.com";
                                                newContact.CustomerID = "123456";
                                                newContact.PrimaryTelephoneNumber = "(425)555-0111";
                                                newContact.MailingAddressStreet = "123 Main St.";
                                                newContact.MailingAddressCity = "Redmond";
                                                newContact.MailingAddressState = "WA";
                                                newContact.Move(f4);
                                                newContact.Save();                                                
                                            }
                                            catch (Exception ex)
                                            {
                                                Console.WriteLine("The new contact was not saved. " + ex.Message);
                                            }
                                            
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
            }

            Console.ReadKey();
        }
    }
}

This is not the prettiest code I have written. So there must be a better way. But this is just a one time application to loop through some database records and add contacts. So I will just leave it like this. Please let me know in the comments if you have suggestions to improve readability. The code snippet will give this contact in your (and the rest of the companies) outlook:

If you are looking how to import a folder full with vcf cards you should take a look at this MSDN article.

This should give you enough pointers to bulk add contacts to a shared folder in Office365 (Outlook 2016)

Good luck!

Pin on pinterest Plus on Googleplus Post on LinkedIn
0 Comments

There are various options to build a word document in your C# code, I choose the lightweight docx nuget package. There are some really good examples on the Codeplex website of docx http://docx.codeplex.com/ if you look at the “Advanced Examples”

The thing is that Office products tend to localize variables such as table designs:

image

I am dutch, but I cannot translate it to a value of the enum `TableDesign`

If I would translate it, it would be something like listtable 7 colorful accent 6. It is still (even after this test file) not a valid entry of the table design enumeration.

using (DocX document = DocX.Create("C:/Temp/tables.docx"))
{
    foreach (TableDesign td in (TableDesign[])Enum.GetValues(typeof(TableDesign)))
    {
        Table aTable = document.InsertTable(5, 5); // because office preview is also 5 x 5
        aTable.Design = td;
        aTable.Rows[0].Cells[0].Paragraphs.First().Append(td.ToString());
    }
    document.Save();
}

 

The “source” (all 6 lines of code, lol) is available on github https://github.com/jphellemons/DocxTables but perhaps more important is the output file. This might save you some time. https://github.com/jphellemons/DocxTables/raw/master/tables.docx

 

Good luck with this small, but awesome nuget package!

Pin on pinterest Plus on Googleplus Post on LinkedIn
1 Comments

294528-microsoft-office-365-and-office-2013A while ago, I made something in Asp.Net with CarlosAg Excel XML Writer Library. This is a free component to generate Excel (.xls) documents (xml). It does not require an installation of Office on your IIS server.

Here is a code sample to show how easy it works.

using CarlosAg.ExcelXmlWriter;

class TestApp {
    static void Main(string[] args) {
        Workbook book = new Workbook();
        Worksheet sheet = book.Worksheets.Add("Sample");
        WorksheetRow row =  sheet.Table.Rows.Add();
        row.Cells.Add("Hello World");
        book.Save(@"c:\test.xls");
    }
}

The downside is that it is a library dated from 2005 and it has no xlsx support (office 2010, office 365). But it works!

So I made an generic handler (.ashx) which gives this popup to download the Excel workbook. [more]

public void ProcessRequest (HttpContext context) {
    context.Response.ContentType = "application/vnd.ms-excel";
    context.Response.AddHeader("Content-Disposition", 
        "attachment; filename=Report-" + DateTime.Now.ToString("yyyy-MM-dd") + ".xls");

    GenerateMyExcel gme = new GenerateMyExcel ();
    Workbook book = gme.GetExcelReport();
    
    book.Save(context.Response.OutputStream);
}

And I also made a method which takes this workbook and attaches it and mails it.

MailMessage msg = new MailMessage("from@mydomain.com", 
    "to@mydomain.com", "Report", "look at attachment");
GenerateMyExcel gme = new GenerateMyExcel();
Workbook book = gme.GetExcelReport();
MemoryStream ms = new MemoryStream();
book.Save(ms);
Byte[] byteArray = ms.ToArray();
ms.Close();
MemoryStream StreamToAttach = new MemoryStream(byteArray);
msg.Attachments.Add(new Attachment(StreamToAttach, "report.xls"));

SmtpClient sc = new SmtpClient();
try
{
    sc.Send(msg);
}
catch (Exception ex)
{
    System.Diagnostics.Debug.WriteLine(ex.Message + " " + ex.StackTrace);
}
StreamToAttach.Close();

But When I opened the file, my excel in Office 365 Home Premium Preview on Windows 8, I get this “File is corrupt” message.

image

image

Was this information helpful? Seriously?

Hours debugging later, I found out that it was a security issue. When you open the properties of the file (and have a Dutch Windows):

report-prop

There is this security message: “This file came from another computer and might be blocked…” So When I hit ‘unblock’ and ‘ok’, I can open the file normally!

This took me a day, because I thought that there was some thing with the CarlosAg library which made my file corrupt. Like setting a cell to number when there is a string inside.

The next time that I want to generate an Excel file, I will use EPPlus.

Pin on pinterest Plus on Googleplus Post on LinkedIn