示例#1
0
        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);
            }
        }