static void Main(string[] args)
{
/* Step 1. Get a list of xlsl files in current folder */
String[] Files = Directory.GetFiles(Environment.CurrentDirectory, "*.xlsm");
/* Create an instance of our encryption class */
AesEncryption Encryptor = new AesEncryption(Key, Vector);
/* Step 2. Iterate */
foreach (String pFile in Files)
{
/* Has a template already been generated? */
if (File.Exists(Path.GetFileNameWithoutExtension(pFile) + " Template.xlsx")) {
continue;
}
/* Debug */
Console.WriteLine("Generating template for " + Path.GetFileNameWithoutExtension(pFile));
/* Open Workbook */
SpreadsheetGear.IWorkbook _ReadWB =
SpreadsheetGear.Factory.GetWorkbook(pFile);
SpreadsheetGear.IWorkbookSet _OutWbSet =
SpreadsheetGear.Factory.GetWorkbookSet();
SpreadsheetGear.IWorkbook _WriteWB =
_OutWbSet.Workbooks.Add();
/* Generate a model guid */
_ReadWB.Worksheets[0].Cells["A150"].Formula = Guid.NewGuid().ToString();
/* Get parameter model, Index 0 always */
SpreadsheetGear.IWorksheet _ModelSheet = _ReadWB.Worksheets[0];
SpreadsheetGear.IWorksheet _ListSheet = _ReadWB.Worksheets["Lists"];
/* Transfer */
SpreadsheetGear.ISheet is2 = _ListSheet.CopyAfter(_WriteWB.Worksheets[0]);
SpreadsheetGear.ISheet is1 = _ModelSheet.CopyAfter(_WriteWB.Worksheets[0]);
/* Set their names */
is1.Name = "Model Param";
is2.Name = "Lists";
_WriteWB.Worksheets[1].Cells["B29"].Validation.Add(SpreadsheetGear.ValidationType.List,
SpreadsheetGear.ValidationAlertStyle.Information, SpreadsheetGear.ValidationOperator.Default,
"=Lists!$A$1:$A$3", "");
_WriteWB.Worksheets[1].Cells["B30"].Validation.Delete();
_WriteWB.Worksheets[1].Cells["B30"].Validation.Add(SpreadsheetGear.ValidationType.List,
SpreadsheetGear.ValidationAlertStyle.Information, SpreadsheetGear.ValidationOperator.Default,
"=Lists!$C$1:$C$4", "");
_WriteWB.Worksheets[1].Cells["B33"].Validation.Add(SpreadsheetGear.ValidationType.List,
SpreadsheetGear.ValidationAlertStyle.Information, SpreadsheetGear.ValidationOperator.Default,
"=Lists!$E$1:$E$3", "");
_WriteWB.Worksheets[1].Cells["B36"].Validation.Add(SpreadsheetGear.ValidationType.List,
SpreadsheetGear.ValidationAlertStyle.Information, SpreadsheetGear.ValidationOperator.Default,
"=Lists!$G$1:$G$6", "");
_WriteWB.Worksheets[1].Cells["B37"].Validation.Add(SpreadsheetGear.ValidationType.List,
SpreadsheetGear.ValidationAlertStyle.Information, SpreadsheetGear.ValidationOperator.Default,
"=Lists!$I$1:$I$9", "");
/* Remove left-owers */
_WriteWB.Worksheets[0].Delete();
/* Save original workbook */
_ReadWB.Save();
_ReadWB.Close();
/* Save file */
_WriteWB.SaveAs(Path.GetFileNameWithoutExtension(pFile) + " Template.xlsx",
SpreadsheetGear.FileFormat.OpenXMLWorkbook);
/* Now encrypt our workbook */
Byte[] WbData = File.ReadAllBytes(pFile);
Byte[] EncryptedData = Encryptor.Encrypt(Encryptor.ByteArrToString(WbData));
File.WriteAllBytes(pFile, EncryptedData);
}
}
