How to create a PDF file from an Excel Sheet (using C#)

The task is to create a PDF file from each sheet in each Excel file in a folder using C#. Here is one way to do it.

Import the following libraries.

using Microsoft.Office.Interop.Excel;
using System.Runtime.InteropServices;
using Application = Microsoft.Office.Interop.Excel.Application;

Access the Excel files as follows:

var directoryinfo = new DirectoryInfo(folderPath);
var files = from f in directoryinfo.GetFiles() where f.Extension.ToLower().Contains("xls") || f.Extension.ToLower().Contains("xlsx") select f;
var fileInfosList = files.ToList();
Workbook workBook = null;
object misValue = System.Reflection.Missing.Value;
Application app = new Application();

Loop through each file, then loop through each sheet in each file.

foreach (FileInfo fileInfo in fileInfosList)
{
 try
 {
  workBook = app.Workbooks.Open(fileInfo.FullName, 0, true, 5, "", "", true, XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
  Sheets workSheets = workBook.Worksheets;
  string fileName = Path.GetFileNameWithoutExtension(fileInfo.FullName);
  string folderName = fileInfo.DirectoryName;

  foreach (Worksheet workSheet in workSheets)
  {
   workSheet.PageSetup.Orientation = XlPageOrientation.xlLandscape;
   workSheet.PageSetup.FitToPagesWide = 1;
   workSheet.PageSetup.Zoom = false;
   workSheet.PageSetup.LeftMargin = 10;
   workSheet.PageSetup.RightMargin = 10;
   workSheet.PageSetup.TopMargin = 10;
   workSheet.PageSetup.BottomMargin = 10;

   string sheetname = workSheet.Name;
if (folderName != null)
{
    string destinationPath = Path.Combine(folderName, fileName + "_" + sheetname + ".pdf");

    if (!File.Exists(destinationPath))
{
     workSheet.ExportAsFixedFormat(XlFixedFormatType.xlTypePDF, destinationPath);
    }
   }
 }
catch (Exception ex)
{
// Log the exception
workBook?.Close(false, misValue, misValue);
app.Quit();
}
app.Quit();
}

That should do it!

The magic method is “ExportAsFixedFormat(XlFixedFormatType.xlTypePDF, destinationPath)” that creates the PDF file.

Before running the code, you will have to “Autofit Column Width” in Excel for each sheet to make sure the content is not truncated in the PDF.

How to access Outlook (Office365) Emails using C#

The task I was assigned was to:

• access an Outlook account from a C# program
• read the content of each email
• parse the content into a database and
• move the emails to a folder (“Processed” folder for example).

To start, I created a Console application using Visual Studio. Added a reference to Microsoft.Office.Interop.Outlook library using NuGetPackage Manager.

Once you add this library to your references, the following code should work.

Add this line in the usings section:

using Outlook = Microsoft.Office.Interop.Outlook;

Here is the class file with the logic.

public static void ReadEmails()
{
  try
  {
    Outlook.Application oApp = new Outlook.Application();

    // Get the MAPI namespace.
    Outlook.NameSpace oNs = oApp.GetNamespace("MAPI");

    oNs.Logon("Orders@company.com", System.Reflection.Missing.Value,
    System.Reflection.Missing.Value, System.Reflection.Missing.Value);

    Outlook.MAPIFolder inboxFolder = oNs.Folders["Account1"].Folders["Inbox"];
    Outlook.MAPIFolder processedFolder =
    oNs.Folders["Account1"].Folders["Processed"];

    Console.WriteLine(inboxFolder.Items.Count);
    foreach (Outlook.MailItem mailItem in inboxFolder.Items)
    {
      // read the email only if its unread
      if (mailItem.UnRead)
      {
         Console.WriteLine(mailItem.Subject);
         Console.WriteLine(mailItem.SenderName);
         Console.WriteLine(mailItem.ReceivedTime);
         Console.WriteLine(mailItem.Body);

         var body = mailItem.Body;
         if (!string.IsNullOrEmpty(body))
         {
            // parse the email body as needed
         }

         // check for attachments
         if (mailItem.Attachments.Count > 0)
         {
            // process the attachments as desired
         }

         // mark the email as read
         mailItem.UnRead = false;

         // Based on the folder structure, move the email to a particular folder
         mailItem.Move(processedFolder);
      }
    }

    oNs.Logoff();
  }
  catch (Exception e)
  {
    Console.WriteLine("{0} Exception caught: ", e);
  }
}

Notice that the code looks for “Account1”. You would use this code when you have access to more than one Outlook accounts/Folders and you want to access the account/Folder with name “Account1”.

If you do not know the name of your account/Folder, you can get the names of your accounts/Folders using the following code.

var folders = oNs.Folders;

The property that will give you the name of the Folder is the “FolderPath”.

But, if you only have one email account to access, this line will return the inbox.

Outlook.MAPIFolder inboxFolder = oNs.GetDefaultFolder(Outlook.OlDefaultFolders.olFolderInbox);

That’s it. Hope that helps!

How to fix Entity Framework’s “Schema specified is not valid” error

I am using Entity Framework 6.1.3 in my Data Access Layer. Once the Model.edmx is added to the project, configured with a database and build it, it builds fine.

But when I try to use the Data Access Layer in the UI, here is the error that is thrown.

The error mentions adding provider in the entityFramework section in the app.config file but I already had this:

<entityFramework>
   <defaultConnectionFactory type="System.Data.Entity.Infrastructure.LocalDbConnectionFactory, EntityFramework">
     <parameters>
       <parameter value="mssqllocaldb" />
     </parameters>
   </defaultConnectionFactory>
   <providers>
     <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
   </providers>
</entityFramework>

After some research, I found a fix. I added the following line to the Model.Contenxt.cs file in the using’s section and it fixed my error.

using SqlProviderServices = System.Data.Entity.SqlServer.SqlProviderServices;

Keep in mind that Model.Context.cs file is auto-generated each time the Model.edmx is changed. So the above line of code has to be added to the file each time.

Please suggest if anyone has a better way of fixing this issue.

How to ignore SSL/TLS certificate error when making requests to Https Urls from C#

I needed to call an URL and return the response. The URL is in the format “https://xx.xxx.xxx.xxx/api/v1/UserData”.

When I accessed the URL in a browser, I was able to see the response. Because of certificate issue, I would see “Not secure – Your connection to this site is not secure. You should not enter any sensitive information….” warning on the browser.

The final purpose was to access the URL from a program and read the response. Here is the code I started with.

The following are the references needed for the code.

using Newtonsoft.Json;
using System.Net;
using System.Net.Http;
using System.Net.Http.Headers;
using System.Threading.Tasks;

Here is the code that uses HttpClient to make a request and reads the response.

using (var client = new HttpClient())
{
    client.BaseAddress = new Uri(UserDataUrl);
    client.DefaultRequestHeaders.Accept.Clear();
    client.DefaultRequestHeaders.Accept.Add(new
     MediaTypeWithQualityHeaderValue("application/json"));
    Task response = client.GetStringAsync(UserDataUrl);
    response.Wait();

    if (response.Exception != null)
    {
        return null;
    }

    return JsonConvert.DeserializeObject<UserData>(response.Result);
}

I was not seeing the response and the request was throwing the following exception: {“The request was aborted: Could not create SSL/TLS secure channel.”}.

After some searching, I found that the adding the first two lines of code solved the issue and I was able to get the response.

// trust any certificate
ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls12;
ServicePointManager.ServerCertificateValidationCallback +=
    (sender, cert, chain, sslPolicyErrors) => { return true; };
using (var client = new HttpClient())
{
    client.BaseAddress = new Uri(UserDataUrl);
    client.DefaultRequestHeaders.Accept.Clear();
    client.DefaultRequestHeaders.Accept.Add(new
      MediaTypeWithQualityHeaderValue("application/json"));
    Task<string> response = client.GetStringAsync(UserDataUrl);
    response.Wait();

    if (response.Exception != null)
    {
         return null;
    }

    return JsonConvert.DeserializeObject<UserData>(response.Result);
}

In my case, the server was using TLS1.2, so I had to add this:

System.Net.ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls12;

It could be some other SecurityProtocolType in your case.

This is for development purposes only. Ignoring or bypassing SSL/TLS errors in production is not advised.