Lập Trình .NET

//your excel file path
string filepath = @"C:\Users\xxx\Desktop\xxx.xlsx";
//an excel file is just a zip file
using (var zip = System.IO.Compression.ZipFile.OpenRead(filepath))
using (var stream = zip.GetEntry("xl/theme/theme1.xml").Open())
using (var rd = new StreamReader(stream))
{
 var xml = rd.ReadToEnd();
}

cd %windir%\Microsoft.NET\Framework\v4.0.30319\ 
msbuild MySolution.sln /t:Build /p:RunOctoPack=true

 
rename D:\Test\App.config web.config
cd %windir%\Microsoft.NET\Framework\v4.0.30319\
aspnet_regiis -pdf "appSettings" "D:\Test"
rename D:\Test\web.config App.config
pause

 
cd %windir%\Microsoft.NET\Framework\v4.0.30319\
aspnet_regiis -pef "appSettings" "D:\Test\"

 
cd %windir%\Microsoft.NET\Framework\v4.0.30319\
aspnet_regiis -pdf "appSettings" "D:\Test\"

        static void Main(string[] argsx)
        {
            Expression e = new Expression("if (And(NOT(ISBLANK(ReceivedDate)), ReceivedDate >= Date(2017,01,01), ReceivedDate <= Date(2018,12,31)), Total * 0.1, Total * VAT)");

            e.Parameters["ReceivedDate"] = DateTime.Parse("2018/01/01");
            e.Parameters["VAT"] = (decimal)12 / 100;
            e.Parameters["Total"] = 1000;

            e.EvaluateFunction += delegate (string name, FunctionArgs args)
            {
                var operatorName = name.ToUpper();
                if (operatorName == "DATE")
                {
                    args.Result = new DateTime((int)args.Parameters[0].Evaluate(), (int)args.Parameters[1].Evaluate(), (int)args.Parameters[2].Evaluate());
                }
                if (operatorName == "AND")
                {
                    args.Result = args.Parameters.All(x => (bool)x.Evaluate());
                }
                if (operatorName == "ISBLANK")
                {
                    var temp = args.Parameters[0].Evaluate();
                    args.Result = temp == null || string.IsNullOrWhiteSpace(temp.ToString());
                }
                if (operatorName == "NOT")
                {
                    args.Result = !(bool)args.Parameters[0].Evaluate();
                }
            };

            var rs = e.Evaluate();
        }

var authManager = HttpContext.Current.GetOwinContext().Authentication;
var authRs = authManager.AuthenticateAsync("Bearer").Result;

public static decimal RemoveTrailingZeros(this decimal d)
{
 return d / 1.0000000000000000000000000000M;
}

public static string ToStringIgnoreTrailingZeros(this decimal d)
{
 return d.ToString("#,##0.############################");
}

            var connection = new ConnectionFactory
            {
                HostName = [HostName],
                UserName = [UserName],
                Password = [Password],
                AutomaticRecoveryEnabled = true
            }.CreateConnection();

SELECT o.name, o.xtype, m.definition, m.uses_ansi_nulls, m.uses_quoted_identifier
FROM sys.sql_modules  m  
INNER JOIN sysobjects o ON m.object_id = o.id 

SET NOCOUNT ON;  
DECLARE @objectid int;  
DECLARE @indexid int;  
DECLARE @partitioncount bigint;  
DECLARE @schemaname nvarchar(130);   
DECLARE @objectname nvarchar(130);   
DECLARE @indexname nvarchar(130);   
DECLARE @partitionnum bigint;  
DECLARE @partitions bigint;  
DECLARE @frag float;  
DECLARE @command nvarchar(4000);   
-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function   
-- and convert object and index IDs to names.  
SELECT  
    object_id AS objectid,  
    index_id AS indexid,  
    partition_number AS partitionnum,  
    avg_fragmentation_in_percent AS frag  
INTO #work_to_do  
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')  
WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;  

-- Declare the cursor for the list of partitions to be processed.  
DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;  

-- Open the cursor.  
OPEN partitions;  

-- Loop through the partitions.  
WHILE (1=1)  
    BEGIN;  
        FETCH NEXT  
           FROM partitions  
           INTO @objectid, @indexid, @partitionnum, @frag;  
        IF @@FETCH_STATUS < 0 BREAK;  
        SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)  
        FROM sys.objects AS o  
        JOIN sys.schemas as s ON s.schema_id = o.schema_id  
        WHERE o.object_id = @objectid;  
        SELECT @indexname = QUOTENAME(name)  
        FROM sys.indexes  
        WHERE  object_id = @objectid AND index_id = @indexid;  
        SELECT @partitioncount = count (*)  
        FROM sys.partitions  
        WHERE object_id = @objectid AND index_id = @indexid;  

-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.  
        IF @frag < 30.0  
            SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';  
        IF @frag >= 30.0  
            SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';  
        IF @partitioncount > 1  
            SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));
        PRINT N'Executing: ' + @command;  
        EXEC (@command);  
        PRINT N'Executed: ' + @command;  
    END;  

-- Close and deallocate the cursor.  
CLOSE partitions;  
DEALLOCATE partitions;  

-- Drop the temporary table.  
DROP TABLE #work_to_do;  
GO

SELECT a.index_id
,name
,avg_fragmentation_in_percent
,OBJECT_NAME(a.object_id) as TableName
FROM sys.dm_db_index_physical_stats (DB_ID(),null, NULL, NULL, NULL) AS a  
JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id
where avg_fragmentation_in_percent > 30
order by avg_fragmentation_in_percent desc

CREATE FUNCTION dbo.IsNullOrEmpty(@text nvarchar(max)) returns bit as
BEGIN
 RETURN IIF( @text IS NULL OR LEN(@text) = 0, 1, 0)
END

        private static void SqlBulkCopy(string tableName, DataTable dataTable, SqlConnection connection)
        {
            using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
            {
                bulkCopy.DestinationTableName = "[" + tableName + "]";
                foreach (DataColumn dtColum in dataTable.Columns)
                {
                    bulkCopy.ColumnMappings.Add(dtColum.ColumnName, dtColum.ColumnName);
                }
                bulkCopy.WriteToServer(dataTable);
            }
        }

select 'row.' + COLUMN_NAME + ' = ' 
+ IIF(DATA_TYPE = 'nvarchar','reader["'+COLUMN_NAME+'"] as string','')
+ IIF(DATA_TYPE = 'uniqueidentifier' and Is_nullable = 'NO','Guid.Parse(reader["'+COLUMN_NAME+'"].ToString())','')
+ IIF(DATA_TYPE = 'uniqueidentifier' and Is_nullable = 'YES','reader["'+COLUMN_NAME+'"].ToString() != string.Empty ? Guid.Parse(reader["'+COLUMN_NAME+'"].ToString()) : (Guid?)null','')
+ IIF(DATA_TYPE = 'bit' and Is_nullable = 'NO','bool.Parse(reader["'+COLUMN_NAME+'"].ToString())','')
+ IIF(DATA_TYPE = 'bit' and Is_nullable = 'YES','reader["'+COLUMN_NAME+'"].ToString() != string.Empty ? bool.Parse(reader["'+COLUMN_NAME+'"].ToString()) : (bool?)null','')
+ IIF(DATA_TYPE = 'int' and Is_nullable = 'NO','int.Parse(reader["'+COLUMN_NAME+'"].ToString())','')
+ IIF(DATA_TYPE = 'int' and Is_nullable = 'YES','reader["'+COLUMN_NAME+'"].ToString() != string.Empty ? int.Parse(reader["'+COLUMN_NAME+'"].ToString()) : (int?)null','')
+ IIF(DATA_TYPE = 'timestamp','reader["'+COLUMN_NAME+'"] as byte[]','')
-- another types add here
+';'
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = '@TABLE_NAME'

worksheet.PrinterSettings.FitToPage = true;
worksheet.PrinterSettings.FitToWidth = 1;
worksheet.PrinterSettings.FitToHeight = 0;

public static void AddToolTip(ExcelRange excelRange, string title, string text)
{
    var dataValidation = excelRange.DataValidation.AddAnyDataValidation();
    dataValidation.ShowInputMessage = true;
    dataValidation.PromptTitle = title;
    dataValidation.Prompt = text;
}

private RSAParameters _publicKey;
private RSAParameters _privateKey;

public void AssignNewKey()
{
    using (var rsa = new RSACryptoServiceProvider(2048))
    {
        rsa.PersistKeyInCsp = false;
        _publicKey = rsa.ExportParameters(false);
        _privateKey = rsa.ExportParameters(true);
    }
}

public byte[] EncryptData(byte[] dataToEncrypt)
{
    using (var rsa = new RSACryptoServiceProvider(2048))
    {
        rsa.PersistKeyInCsp = false;
        rsa.ImportParameters(_publicKey);

        return rsa.Encrypt(dataToEncrypt, true);
    }
}

public byte[] DecryptData(byte[] dataToDecrypt)
{
    using (var rsa = new RSACryptoServiceProvider(2048))
    {
        rsa.PersistKeyInCsp = false;

        rsa.ImportParameters(_privateKey);
        return rsa.Decrypt(dataToDecrypt, true);
    }
}

public void AssignNewKey(string publicKeyPath, string privateKeyPath)
{
    using (var rsa = new RSACryptoServiceProvider(2048))
    {
        rsa.PersistKeyInCsp = false;

        if (File.Exists(privateKeyPath))
        {
            File.Delete(privateKeyPath);
        }

        if (File.Exists(publicKeyPath))
        {
            File.Delete(publicKeyPath);
        }

        var publicKeyfolder = Path.GetDirectoryName(publicKeyPath);
        var privateKeyfolder = Path.GetDirectoryName(privateKeyPath);

        if (!Directory.Exists(publicKeyfolder))
        {
            Directory.CreateDirectory(publicKeyfolder);
        }

        if (!Directory.Exists(privateKeyfolder))
        {
            Directory.CreateDirectory(privateKeyfolder);
        }

        File.WriteAllText(publicKeyPath, rsa.ToXmlString(false));
        File.WriteAllText(privateKeyPath, rsa.ToXmlString(true));
    }
}

public byte[] EncryptData(string publicKeyPath, byte[] dataToEncrypt)
{
    using (var rsa = new RSACryptoServiceProvider(2048))
    {
        rsa.PersistKeyInCsp = false;
        rsa.FromXmlString(File.ReadAllText(publicKeyPath));

        return rsa.Encrypt(dataToEncrypt, false);
    }
}

public byte[] DecryptData(string privateKeyPath, byte[] dataToDecrypt)
{
    using (var rsa = new RSACryptoServiceProvider(2048))
    {
        rsa.PersistKeyInCsp = false;
        rsa.FromXmlString(File.ReadAllText(privateKeyPath));
        return rsa.Decrypt(dataToDecrypt, false);
    }
}

const string ContainerName = "MyContainer";

public void AssignNewKey()
{
    CspParameters cspParams = new CspParameters(1);
    cspParams.KeyContainerName = ContainerName;
    cspParams.Flags = CspProviderFlags.UseMachineKeyStore;
    cspParams.ProviderName = "Microsoft Strong Cryptographic Provider";

    var rsa = new RSACryptoServiceProvider(cspParams) { PersistKeyInCsp = true };
}

public void DeleteKeyInCsp()
{
    var cspParams = new CspParameters { KeyContainerName = ContainerName };
    var rsa = new RSACryptoServiceProvider(cspParams) { PersistKeyInCsp = false };

    rsa.Clear();                         
}

public byte[] EncryptData(byte[] dataToEncrypt)
{
    var cspParams = new CspParameters { KeyContainerName = ContainerName };

    using (var rsa = new RSACryptoServiceProvider(2048, cspParams))
    {
        return rsa.Encrypt(dataToEncrypt, false);
    }
}

public byte[] DecryptData(byte[] dataToDecrypt)
{
    var cspParams = new CspParameters { KeyContainerName = ContainerName };

    using (var rsa = new RSACryptoServiceProvider(2048, cspParams))
    {                               
        return rsa.Decrypt(dataToDecrypt, false);
    }
}

public byte[] Encrypt(byte[] dataToEncrypt, byte[] key, byte[] iv)
{            
    using (var des = new DESCryptoServiceProvider())
    {
        des.Mode = CipherMode.CBC;
        des.Padding = PaddingMode.PKCS7;

        des.Key = key;
        des.IV = iv;

        using (var memoryStream = new MemoryStream())
        {
            var cryptoStream = new CryptoStream(memoryStream, des.CreateEncryptor(), 
                CryptoStreamMode.Write);

            cryptoStream.Write(dataToEncrypt, 0, dataToEncrypt.Length);
            cryptoStream.FlushFinalBlock();

            return memoryStream.ToArray();
        }
    }
}

public byte[] Decrypt(byte[] dataToDecrypt, byte[] key, byte[] iv)
{                                
    using (var des = new DESCryptoServiceProvider())
    {
        des.Mode = CipherMode.CBC;
        des.Padding = PaddingMode.PKCS7;

        des.Key = key;
        des.IV = iv;

        using (var memoryStream = new MemoryStream())
        {                       
            var cryptoStream = new CryptoStream(memoryStream, des.CreateDecryptor(), 
                CryptoStreamMode.Write);

            cryptoStream.Write(dataToDecrypt, 0, dataToDecrypt.Length);
            cryptoStream.FlushFinalBlock();

            return memoryStream.ToArray();
        }
    }                            
}

public byte[] Encrypt(byte[] dataToEncrypt, byte[] key, byte[] iv)
{            
    using (var des = new TripleDESCryptoServiceProvider())
    {
        des.Mode = CipherMode.CBC;
        des.Padding = PaddingMode.PKCS7;

        des.Key = key;
        des.IV = iv;
       
        using (var memoryStream = new MemoryStream())
        {
            var cryptoStream = new CryptoStream(memoryStream, des.CreateEncryptor(), 
                CryptoStreamMode.Write);

            cryptoStream.Write(dataToEncrypt, 0, dataToEncrypt.Length);
            cryptoStream.FlushFinalBlock();

            return memoryStream.ToArray();
        }
    }
}

public byte[] Decrypt(byte[] dataToDecrypt, byte[] key, byte[] iv)
{
    using (var des = new TripleDESCryptoServiceProvider())
    {
        des.Mode = CipherMode.CBC;
        des.Padding = PaddingMode.PKCS7;

        des.Key = key;
        des.IV = iv;

        using (var memoryStream = new MemoryStream())
        {                       
            var cryptoStream = new CryptoStream(memoryStream, des.CreateDecryptor(), 
                CryptoStreamMode.Write);

            cryptoStream.Write(dataToDecrypt, 0, dataToDecrypt.Length);
            cryptoStream.FlushFinalBlock();

            var decryptBytes = memoryStream.ToArray();

            return decryptBytes;
        }
    }                            
}

public byte[] Encrypt(byte[] dataToEncrypt, byte[] key, byte[] iv)
{            
    using (var aes = new AesCryptoServiceProvider())
    {
        aes.Mode = CipherMode.CBC;
        aes.Padding = PaddingMode.PKCS7;
        
        aes.Key = key;
        aes.IV = iv;

        using (var memoryStream = new MemoryStream())
        {
            var cryptoStream = new CryptoStream(memoryStream, aes.CreateEncryptor(), 
                CryptoStreamMode.Write);

            cryptoStream.Write(dataToEncrypt, 0, dataToEncrypt.Length);
            cryptoStream.FlushFinalBlock();

            return memoryStream.ToArray();
        }
    }
}

public byte[] Decrypt(byte[] dataToDecrypt, byte[] key, byte[] iv)
{                                
    using (var aes = new AesCryptoServiceProvider())
    {
        aes.Mode = CipherMode.CBC;
        aes.Padding = PaddingMode.PKCS7;

        aes.Key = key;
        aes.IV = iv;

        using (var memoryStream = new MemoryStream())
        {                       
            var cryptoStream = new CryptoStream(memoryStream, aes.CreateDecryptor(), 
                CryptoStreamMode.Write);

            cryptoStream.Write(dataToDecrypt, 0, dataToDecrypt.Length);
            cryptoStream.FlushFinalBlock();

            var decryptBytes = memoryStream.ToArray();

            return decryptBytes;
        }
    }                            
}

public static byte[] ComputeHmacsha1(byte[] toBeHashed, byte[] key)
{
    using (var hmac = new HMACSHA1(key))
    {
        return hmac.ComputeHash(toBeHashed);
    }
}
 
public static byte[] ComputeHmacsha256(byte[] toBeHashed, byte[] key)
{
    using (var hmac = new HMACSHA256(key))
    {
        return hmac.ComputeHash(toBeHashed);
    }
}
 
public static byte[] ComputeHmacsha512(byte[] toBeHashed, byte[] key)
{
    using (var hmac = new HMACSHA512(key))
    {
        return hmac.ComputeHash(toBeHashed);
    }
}
 
public static byte[] ComputeHmacmd5(byte[] toBeHashed, byte[] key)
{
    using (var hmac = new HMACMD5(key))
    {
        return hmac.ComputeHash(toBeHashed);
    }
}

public static byte[] ComputeHashSha1(byte[] toBeHashed)
{
    using (var sha1 = SHA1.Create())
    {
        return sha1.ComputeHash(toBeHashed);
    }
}
 
public static byte[] ComputeHashSha256(byte[] toBeHashed)
{
    using (var sha256 = SHA256.Create())
    {
        return sha256.ComputeHash(toBeHashed);
    }
}
 
public static byte[] ComputeHashSha512(byte[] toBeHashed)
{
    using (var sha512 = SHA512.Create())
    {
        return sha512.ComputeHash(toBeHashed);
    }
}
 
public static byte[] ComputeHashMd5(byte[] toBeHashed)
{
    using (var md5 = MD5.Create())
    {
        return md5.ComputeHash(toBeHashed);
    }
}

public static void AddWatermarkUsingHeaderFooterImage(OfficeOpenXml.ExcelWorksheet worksheet, System.Drawing.Image image)
{
 //Enable PageLayoutView mode to see the watermark
 worksheet.View.PageLayoutView = true;
 worksheet.HeaderFooter.EvenHeader.InsertPicture(image, PictureAlignment.Centered);
 worksheet.HeaderFooter.OddHeader.InsertPicture(image, PictureAlignment.Centered);
}

public static void AddWatermarkUsingBackgroundImage(OfficeOpenXml.ExcelWorksheet worksheet, System.Drawing.Image image)
{
  worksheet.BackgroundImage.Image = image;
}