Skip to main content

How to encrypt an entire excel file using AES-128 encryption

What Is AES?

AES stands for Advanced Encryption Standard. It is a symmetric block cipher that is used by the U.S. government to protect classified information. AES is used worldwide to protect classified data around the world. AES is essential in cybersecurity, electronic data protection, and computer security.


Variations of AES

AES is used in three block cipher versions namely AES 128, AES 192, AES 256.

AES 128 uses 128-bit key length to encrypt and decrypt block messages. This is a symmetric secret key which means it uses same secret key for encrypting and decrypting message blocks.

AES Encryption of Excel File

Today we will AES 128 to encrypt an Excel File. The excel file looks like the following image.


To encrypt this excel file, we will use C#. First, we need to create a console project. Let's name the project ExcelEncryption.

First, we try to understand what we need to achieve. We need a KEY and Initialization Vector (IV) pair for AES 128 encryption. Let’s say, the KEY IV is as follows 

public const string KEY = "asdfghjklpoiuytr";
public const string IV = "rtyuioplkjhgfdsa";

Next, we will create an Excel application object and with the object, we will create a new excel workbook and get the first worksheet with the following code

Excel.Application xlApp = new Excel.Application();
Excel.Workbook xlWorkBook = xlApp.Workbooks.Open(@"D:\Book1_48.xlsx");
Excel.Worksheet xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

 

Here in the place of "D:\Book1_48.xlsx", we just need to specify your input file location.

Then, we will find out the row count and column count from our excel as follows 

Excel.Range xlRange = xlWorkSheet.UsedRange;
int totalRows = xlRange.Rows.Count;
int totalColumns = xlRange.Columns.Count;

Then we write our encryption code as follows

public static string EncodeToByteArray(string plainText) {
  if (plainText == null || plainText.Length <= 0) {
    throw new ArgumentNullException(nameof(plainText));
  }
  byte[] encrypted;
  string resultInByte64Array;
  using(var rijAlg = new RijndaelManaged()) {
    rijAlg.Mode = CipherMode.CBC;
    rijAlg.Padding = PaddingMode.PKCS7;
    rijAlg.FeedbackSize = 128;

    rijAlg.Key = System.Text.Encoding.UTF8.GetBytes(KEY);
    rijAlg.IV = System.Text.Encoding.UTF8.GetBytes(IV);

    var encryptor = rijAlg.CreateEncryptor(rijAlg.Key, rijAlg.IV);

    using(var msEncrypt = new MemoryStream()) {
      using(var csEncrypt = new CryptoStream(msEncrypt, encryptor, CryptoStreamMode.Write)) {
        using(var swEncrypt = new StreamWriter(csEncrypt)) {
          swEncrypt.Write(plainText);
        }
        encrypted = msEncrypt.ToArray();
        resultInByte64Array = Convert.ToBase64String(encrypted);
      }
    }
  }
  return resultInByte64Array;
}

Then we go back to our Main function and start a for loop for the rows and columns. For each excel cells, we will pass the value of the excel cell to our previously written EncodeToByteArray() function

for (int rowCount = 2; rowCount <= totalRows; rowCount++) {
  for (int colCount = 1; colCount <= totalColumns; colCount++) {
    firstValue = Convert.ToString((xlRange.Cells[rowCount, colCount] as Excel.Range).Text);
    xlWorkSheet.Cells[rowCount, colCount] = EncodeToByteArray(firstValue);
  }
}

 Finally, we will write code for saving the excel file.  You need to replace the "D:\Book1_48_output.xlsx" to your own destination. You don’t need to create the output excel file. It will be created automatically.

xlApp.DisplayAlerts = false;
xlWorkBook.SaveAs(@"D:\Book1_48_output.xlsx", Excel.XlFileFormat.xlOpenXMLWorkbook, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlNoChange, Excel.XlSaveConflictResolution.xlLocalSessionChanges, Missing.Value, Missing.Value, Missing.Value, Missing.Value);

xlWorkBook.Close();
xlApp.Quit();

Marshal.ReleaseComObject(xlWorkSheet);
Marshal.ReleaseComObject(xlWorkBook);
Marshal.ReleaseComObject(xlApp);

So, we are done. Your output file will look like this.


The full C# Code will look like this.

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Runtime.InteropServices;
using System.Security.Cryptography;
using System.Text;
using System.Threading.Tasks;
using Excel = Microsoft.Office.Interop.Excel;

namespace EncryptionForLoanDisburseJMeter {
  class Program {
    public const string KEY = "asdfghjklpoiuytr";
    public const string IV = "rtyuioplkjhgfdsa";
    static void Main(string[] args) {

      Excel.Application xlApp = new Excel.Application();
      Excel.Workbook xlWorkBook = xlApp.Workbooks.Open(@"D:\Book1_48.xlsx");
      Excel.Worksheet xlWorkSheet = (Excel.Worksheet) xlWorkBook.Worksheets.get_Item(1);

      Excel.Range xlRange = xlWorkSheet.UsedRange;
      int totalRows = xlRange.Rows.Count;
      int totalColumns = xlRange.Columns.Count;

      string firstValue,
      secondValue;

      for (int rowCount = 2; rowCount <= totalRows; rowCount++) {
        for (int colCount = 1; colCount <= totalColumns; colCount++) {
          firstValue = Convert.ToString((xlRange.Cells[rowCount, colCount] as Excel.Range).Text);
          xlWorkSheet.Cells[rowCount, colCount] = EncodeToByteArray(firstValue);
        }
      }
      xlApp.DisplayAlerts = false;
      xlWorkBook.SaveAs(@"D:\Book1_48_output.xlsx", Excel.XlFileFormat.xlOpenXMLWorkbook, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlNoChange, Excel.XlSaveConflictResolution.xlLocalSessionChanges, Missing.Value, Missing.Value, Missing.Value, Missing.Value);

      xlWorkBook.Close();
      xlApp.Quit();

      Marshal.ReleaseComObject(xlWorkSheet);
      Marshal.ReleaseComObject(xlWorkBook);
      Marshal.ReleaseComObject(xlApp);

    }

    public static string EncodeToByteArray(string plainText) {
      if (plainText == null || plainText.Length <= 0) {
        throw new ArgumentNullException(nameof(plainText));
      }
      byte[] encrypted;
      string resultInByte64Array;
      using(var rijAlg = new RijndaelManaged()) {
        rijAlg.Mode = CipherMode.CBC;
        rijAlg.Padding = PaddingMode.PKCS7;
        rijAlg.FeedbackSize = 128;

        rijAlg.Key = System.Text.Encoding.UTF8.GetBytes(KEY);
        rijAlg.IV = System.Text.Encoding.UTF8.GetBytes(IV);

        var encryptor = rijAlg.CreateEncryptor(rijAlg.Key, rijAlg.IV);

        using(var msEncrypt = new MemoryStream()) {
          using(var csEncrypt = new CryptoStream(msEncrypt, encryptor, CryptoStreamMode.Write)) {
            using(var swEncrypt = new StreamWriter(csEncrypt)) {
              swEncrypt.Write(plainText);
            }
            encrypted = msEncrypt.ToArray();
            resultInByte64Array = Convert.ToBase64String(encrypted);
          }
        }
      }
      return resultInByte64Array;
    }
  }
}

Hope you enjoyed this!

Labels:

Comments

Post a Comment

Most Loved Posts

Threadpool - A deadly poison wait for SQL Server (The What, When and How)

Introduction  Threadpool is a  poison  wait. Yes, I mean it. Its poison for SQL Server, its poison for the Business and of course, the end-users! The most devastating thing about threadpool is you hardly recognize it because it comes in disguise, meaning you see no memory or cpu pressure in the system, yet you cannot run any query, it seems like your SQL Server is frozen solid. That scary, isn't it?
2 comments
Read more

SQL Schema Compare with Visual Studio (A complete Guide)

Introduction When you're working on your Dev Database, an urgent issue comes along, and you instantly solve it by changing Scheme in the Staging Database or Production Database :3, few more these type of patching and you're completely out of sync! A lot of paid alternatives are there like SQL Data Compare by RedGate, but my first choice is Visual Studio's SQL Data Tools. In the following article, I tried to image-describe the steps for SQL Data Tool. Like I said before, there are lots of handly DBAtools out there to compare Schema between two DB Sources. I would like to discuss how you can compare two SQL Server DB with Visual Studio. Make sure you have SQL Server Data tools checked while installing Visual Studio.
Post a Comment
Read more

Slow SQL Server : What we should NOT do

 Try to list the best practices of SQL Server. It will require a heck of a time. Try to list the Bad Practices and it will require more than the best practice list , of course, probably you’ll end up getting frustrated . (seeing all the oops configurations and its effect on SQL Server )
Post a Comment
Read more

How to deal with Slow SQL Server due to Autogrowth issue

  Why you should not stick to SQL Server’s default Initial file size and autogrowth We hear a lot of these statements : My SQL Server is running slow My Production DB was fine when we started, But it is staggeringly slow now My Business end users are frustrated to wait too long Well, there are lots of reasons why your SQL Server might be slow. Setting the Autogrowth option to default is definitely one of the vital ones which we seem to ignore most of the time. Slow SQL Server and Tortoise SQL Server provides you with some default settings for autogrowth when you install it for the first time. These default cases are defined with increment by 8MB or by 10%. You need to change it to suit your own needs. For Small application, this default value might work but as soon as your system grows, you feel the impact of it more often. What Happens SQL Server Files needs more space SQL Server Requests the Server PC for more space The Server PC takes the request and asks the SQL request...
Post a Comment
Read more

Why not use Select * in SQL Server

  Select * We often use the Select *  to fetch data from tables of SQL Server.
2 comments
Read more

SQL Data Tools - Compare Data

Compare Data between two tables SQL Server Database with the same schema architecture can differ in different environments like Dev, Staging, and Production, especially in configuration tables. Let's see how we can easily sync the data in two different tables.
1 comment
Read more