public void BuildExcel(string input, string fileName, string password)
{
List <object[]> Data = new List <object[]>();
List <string> lines = input.SplitLines();
int rowCount = 0;
int colCount = 0;
foreach (string line in lines)
{
List <string> fields = line.SplitTabs();
colCount = fields.Count();
object[] row = new object[colCount];
for (int i = 0; i < colCount; i++)
{
row[i] = fields[i];
}
Data.Add(row);
rowCount++;
}
using (ExcelPackage pck = new ExcelPackage())
{
//Create the worksheet
ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Sheet1");
if (!string.IsNullOrWhiteSpace(password))
{
pck.Encryption.Password = password;
pck.Encryption.Algorithm = EncryptionAlgorithm.AES192;
pck.Workbook.Protection.LockStructure = true;
}
//Load the datatable into the sheet
if (rowCount > 0)
{
ws.Cells["A1"].LoadFromArrays(Data);
//Format the header for columns
using (ExcelRange rng = ws.Cells[1, 1, 1, colCount])
{
rng.Style.Font.Bold = true;
rng.Style.Fill.PatternType = ExcelFillStyle.Solid; //Set Pattern for the background to Solid
rng.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(79, 129, 189)); //Set color to dark blue
rng.Style.Font.Color.SetColor(Color.White);
for (int i = 1; i <= rng.Count(); i++)
{
ws.Column(i).AutoFit();
}
}
}
if (SaveData(fileName, pck.GetAsByteArray()))
{
System.Diagnostics.Process.Start(fileName);
}
}
}