public static void Run()
{
var pck = new ExcelPackage();
//Create a datatable with the directories and files from the current directory...
DataTable dt = GetDataTable(FileOutputUtil.GetDirectoryInfo("."));
var wsDt = pck.Workbook.Worksheets.Add("FromDataTable");
//Load the datatable and set the number formats...
wsDt.Cells["A1"].LoadFromDataTable(dt, true, TableStyles.Medium9);
wsDt.Cells[2, 2, dt.Rows.Count + 1, 2].Style.Numberformat.Format = "#,##0";
wsDt.Cells[2, 3, dt.Rows.Count + 1, 4].Style.Numberformat.Format = "mm-dd-yy";
wsDt.Cells[wsDt.Dimension.Address].AutoFitColumns();
//Select Name and Created-time...
var collection = (from row in dt.Select() select new { Name = row["Name"], Created_time = (DateTime)row["Created"] });
var wsEnum = pck.Workbook.Worksheets.Add("FromAnonymous");
//Load the collection starting from cell A1...
wsEnum.Cells["A1"].LoadFromCollection(collection, true, TableStyles.Medium9);
//Add some formating...
wsEnum.Cells[2, 2, dt.Rows.Count - 1, 2].Style.Numberformat.Format = "mm-dd-yy";
wsEnum.Cells[wsEnum.Dimension.Address].AutoFitColumns();
//Load a list of FileDTO objects from the datatable...
var wsList = pck.Workbook.Worksheets.Add("FromList");
List <FileDTO> list = (from row in dt.Select()
select new FileDTO
{
Name = row["Name"].ToString(),
Size = row["Size"].GetType() == typeof(long) ? (long)row["Size"] : 0,
Created = (DateTime)row["Created"],
LastModified = (DateTime)row["Modified"],
IsDirectory = (row["Size"] == DBNull.Value)
}).ToList <FileDTO>();
//Load files ordered by size...
wsList.Cells["A1"].LoadFromCollection(from file in list
orderby file.Size descending
where file.IsDirectory == false
select file, true, TableStyles.Medium9);
wsList.Cells[2, 2, dt.Rows.Count + 1, 2].Style.Numberformat.Format = "#,##0";
wsList.Cells[2, 3, dt.Rows.Count + 1, 4].Style.Numberformat.Format = "mm-dd-yy";
//Load directories ordered by Name...
wsList.Cells["F1"].LoadFromCollection(from file in list
orderby file.Name ascending
where file.IsDirectory == true
select new
{
Name = file.Name,
Created = file.Created,
Last_modified = file.LastModified
}, //Use an underscore in the property name to get a space in the title.
true, TableStyles.Medium11);
wsList.Cells[2, 7, dt.Rows.Count + 1, 8].Style.Numberformat.Format = "mm-dd-yy";
//Load the list using a specified array of MemberInfo objects. Properties, fields and methods are supported.
var rng = wsList.Cells["J1"].LoadFromCollection(list,
true,
TableStyles.Medium10,
BindingFlags.Instance | BindingFlags.Public,
new MemberInfo[] {
typeof(FileDTO).GetProperty("Name"),
typeof(FileDTO).GetField("IsDirectory"),
typeof(FileDTO).GetMethod("ToString")
}
);
wsList.Tables.GetFromRange(rng).Columns[2].Name = "Description";
wsList.Cells[wsList.Dimension.Address].AutoFitColumns();
//...and save
var fi = FileOutputUtil.GetFileInfo("04-LoadingData.xlsx");
pck.SaveAs(fi);
}
public static void Run()
{
//Create a Sample 19 directory...
var outputDir = FileOutputUtil.GetDirectoryInfo("19-EncryptionAndProtection");
//create the three FileInfo objects...
FileInfo templateFile = FileOutputUtil.GetFileInfo(outputDir, "template.xlsx");
FileInfo answerFile = FileOutputUtil.GetFileInfo(outputDir, "answers.xlsx");
FileInfo JKAnswerFile = FileOutputUtil.GetFileInfo(outputDir, "JKAnswers.xlsx");
//Create the template...
using (ExcelPackage package = new ExcelPackage(templateFile))
{
//Lock the workbook totally
var workbook = package.Workbook;
workbook.Protection.LockWindows = true;
workbook.Protection.LockStructure = true;
workbook.View.SetWindowSize(150, 525, 14500, 6000);
workbook.View.ShowHorizontalScrollBar = false;
workbook.View.ShowVerticalScrollBar = false;
workbook.View.ShowSheetTabs = false;
//Set a password for the workbookprotection
workbook.Protection.SetPassword("EPPlus");
//Encrypt with no password
package.Encryption.IsEncrypted = true;
var sheet = package.Workbook.Worksheets.Add("Quiz");
sheet.View.ShowGridLines = false;
sheet.View.ShowHeaders = false;
using (var range = sheet.Cells["A:XFD"])
{
range.Style.Fill.PatternType = ExcelFillStyle.Solid;
range.Style.Fill.BackgroundColor.SetColor(Color.LightGray);
range.Style.Font.Name = "Broadway";
range.Style.Hidden = true;
}
sheet.Cells["A1"].Value = "Quiz-Sweden";
sheet.Cells["A1"].Style.Font.Size = 18;
sheet.Cells["A3"].Value = "Enter your name:";
sheet.Column(1).Width = 30;
sheet.Column(2).Width = 80;
sheet.Column(3).Width = 20;
sheet.Cells["A7"].Value = "What is the name of the capital of Sweden?";
sheet.Cells["A9"].Value = "At which place did the Swedish team end up in the Soccer Worldcup 1994?";
sheet.Cells["A11"].Value = "What is the first name of the famous Swedish inventor/scientist that founded the Nobel-prize?";
using (var r = sheet.Cells["B3,C7,C9,C11"])
{
r.Style.Fill.BackgroundColor.SetColor(Color.WhiteSmoke);
r.Style.Border.Top.Style = ExcelBorderStyle.Dotted;
r.Style.Border.Top.Color.SetColor(Color.Black);
r.Style.Border.Right.Style = ExcelBorderStyle.Dotted;
r.Style.Border.Right.Color.SetColor(Color.Black);
r.Style.Border.Bottom.Style = ExcelBorderStyle.Dotted;
r.Style.Border.Bottom.Color.SetColor(Color.Black);
r.Style.Border.Left.Style = ExcelBorderStyle.Dotted;
r.Style.Border.Left.Color.SetColor(Color.Black);
r.Style.Locked = false;
r.Style.Hidden = false;
r.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
}
sheet.Select("B3");
sheet.Protection.SetPassword("EPPlus");
sheet.Protection.AllowSelectLockedCells = false;
//Options question 1
var list1 = sheet.Cells["C7"].DataValidation.AddListDataValidation();
list1.Formula.Values.Add("Bern");
list1.Formula.Values.Add("Stockholm");
list1.Formula.Values.Add("Oslo");
list1.ShowErrorMessage = true;
list1.Error = "Please select a value from the list";
var list2 = sheet.Cells["C9"].DataValidation.AddListDataValidation();
list2.Formula.Values.Add("First");
list2.Formula.Values.Add("Second");
list2.Formula.Values.Add("Third");
list2.ShowErrorMessage = true;
list2.Error = "Please select a value from the list";
var list3 = sheet.Cells["C11"].DataValidation.AddListDataValidation();
list3.Formula.Values.Add("Carl Gustaf");
list3.Formula.Values.Add("Ingmar");
list3.Formula.Values.Add("Alfred");
list3.ShowErrorMessage = true;
list3.Error = "Please select a value from the list";
//Save, and the template is ready for use
package.Save();
//Quiz-template is done, now create the answer template and encrypt it...
using (var packageAnswers = new ExcelPackage(package.Stream)) //We use the stream from the template here to get a copy of it.
{
var sheetAnswers = packageAnswers.Workbook.Worksheets[0];
sheetAnswers.Cells["C7"].Value = "Stockholm";
sheetAnswers.Cells["C9"].Value = "Third";
sheetAnswers.Cells["C11"].Value = "Alfred";
packageAnswers.Encryption.Algorithm = EncryptionAlgorithm.AES192; //For the answers we want a little bit stronger encryption
packageAnswers.SaveAs(answerFile, "EPPlus"); //Save and set the password to EPPlus. The password can also be set using packageAnswers.Encryption.Password property
}
//Ok, Since this is qan example we create one user answer...
using (var packageAnswers = new ExcelPackage(package.Stream))
{
var sheetUser = packageAnswers.Workbook.Worksheets[0];
sheetUser.Cells["B3"].Value = "Jan Källman";
sheetUser.Cells["C7"].Value = "Bern";
sheetUser.Cells["C9"].Value = "Third";
sheetUser.Cells["C11"].Value = "Alfred";
packageAnswers.SaveAs(JKAnswerFile, "JK"); //We use default encryption here (AES128) and Password JK
}
}
//Now lets correct the user form...
var packAnswers = new ExcelPackage(answerFile, "EPPlus"); //Supply the password, so the file can be decrypted
var packUser = new ExcelPackage(JKAnswerFile, "JK"); //Supply the password, so the file can be decrypted
var wsAnswers = packAnswers.Workbook.Worksheets[0];
var wsUser = packUser.Workbook.Worksheets[0];
//Enumerate the three answers
foreach (var cell in wsAnswers.Cells["C7,C9,C11"])
{
wsUser.Cells[cell.Address].Style.Fill.PatternType = ExcelFillStyle.Solid;
if (cell.Value.ToString().Equals(wsUser.Cells[cell.Address].Value.ToString(), StringComparison.OrdinalIgnoreCase)) //Correct Answer?
{
wsUser.Cells[cell.Address].Style.Fill.BackgroundColor.SetColor(Color.Green);
}
else
{
wsUser.Cells[cell.Address].Style.Fill.BackgroundColor.SetColor(Color.Red);
}
}
packUser.Save();
packUser.Dispose();
packAnswers.Dispose();
packUser.Dispose();
}
