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!