Thread Safe ( multi-thread access to EPPlus API )

/* * User: yanghui * */ using OfficeOpenXml; using System.Threading; using System; using System.Collections; using System.Collections.Generic; using OfficeOpenXml.Style; using System.Drawing; namespace DB.Risk.DataAnalytics.ReportingService.Core { ///

/// Excel manipulation, ONLY for 2007+ Version, NOT 2003 or earlier ///

public class ExcelUtil { private static object m_lockObject = new object(); public const int WAITING_TIME = 20 * 60 * 100; // 20 minutes ///

/// Export a DataTable to a EXCEL 2007 file ///

/// /// /// /// /// /// public static void ExportTo2007XLSX(System.Data.DataTable _sourceDataTable, string _templateFilePath,string _sheetName, string _startCell,bool _withHeader, string _saveAsFilePath) { if (Monitor.TryEnter(m_lockObject, WAITING_TIME)) { try { System.IO.FileInfo source = new System.IO.FileInfo(_templateFilePath); using (var package = new OfficeOpenXml.ExcelPackage(source)) { ExcelWorksheet ws = package.Workbook.Worksheets[_sheetName]; ws.Cells[_startCell].LoadFromDataTable(_sourceDataTable, _withHeader); if (_saveAsFilePath == null || _templateFilePath.ToLower() == _saveAsFilePath.ToLower() || _saveAsFilePath == "") { package.Save(); } else { var target = System.IO.File.OpenWrite(_saveAsFilePath); package.SaveAs(target); target.Close(); } } } finally { Monitor.Exit(m_lockObject); } } else { EmailAdminWaitTooLong("Create template:" + _templateFilePath + System.Environment.NewLine + "sheetName:" + _sheetName); } } private static void EmailAdminWaitTooLong(string _body) { EmailUtil.NotifyAdmins(_subject:"Failed to operate Excel file, wait more than "+WAITING_TIME/(60*100)+" minutes", _body:_body ); } public static void UpdateCellValue(string _filePath, string _sheetName,string _cell, string _value) { if (Monitor.TryEnter(m_lockObject, WAITING_TIME)) { try { System.IO.FileInfo source = new System.IO.FileInfo(_filePath); using (var package = new OfficeOpenXml.ExcelPackage(source)) { ExcelWorksheet ws = package.Workbook.Worksheets[_sheetName]; ws.Cells[_cell].Value = _value; package.Save(); } } finally { Monitor.Exit(m_lockObject); } } else { EmailAdminWaitTooLong("Update one cell FilePath:" + _filePath + System.Environment.NewLine + "sheetName:" + _sheetName); } } public static void CopySheet(string _filePath, string _destinationSheetName, string _sourceSheetName) { if (Monitor.TryEnter(m_lockObject, WAITING_TIME)) { try { System.IO.FileInfo source = new System.IO.FileInfo(_filePath); using (var package = new OfficeOpenXml.ExcelPackage(source)) { if (package.Workbook.Worksheets[_destinationSheetName] != null) { package.Workbook.Worksheets.Delete(_destinationSheetName); } package.Workbook.Worksheets.Copy(_sourceSheetName, _destinationSheetName); package.Save(); } } finally { Monitor.Exit(m_lockObject); } } else { EmailAdminWaitTooLong("CopySheet FilePath:" + _filePath + System.Environment.NewLine + "sourceSheetName:" + _sourceSheetName); } } public static void UpdateCellsValues(string _filePath, string _sheetName,string[] _cells, string[] _values) { if (Monitor.TryEnter(m_lockObject, WAITING_TIME)) { try { System.IO.FileInfo source = new System.IO.FileInfo(_filePath); using (var package = new OfficeOpenXml.ExcelPackage(source)) { ExcelWorksheet ws = package.Workbook.Worksheets[_sheetName]; for (int i = 0; i < _cells.Length; i++) { ws.Cells[_cells[i]].Value = _values[i]; } package.Save(); } } finally { Monitor.Exit(m_lockObject); } } else { EmailAdminWaitTooLong("Update cells FilePath:" + _filePath + System.Environment.NewLine + "sheetName:" + _sheetName); } } public static void DeleteSheets(string _filePath, string[] _sheetsNameToDelete) { if (Monitor.TryEnter(m_lockObject, WAITING_TIME)) { try { System.IO.FileInfo source = new System.IO.FileInfo(_filePath); using (var package = new OfficeOpenXml.ExcelPackage(source)) { foreach (var item in package.Workbook.Worksheets) { if (((IList)_sheetsNameToDelete).Contains(item.Name)) package.Workbook.Worksheets.Delete(item); } package.Save(); } } finally { Monitor.Exit(m_lockObject); } } } public static void DeleteSheetsExcept(string _filePath, string[] _sheetsNameToKeep) { if (Monitor.TryEnter(m_lockObject, WAITING_TIME)) { try { System.IO.FileInfo source = new System.IO.FileInfo(_filePath); using (var package = new OfficeOpenXml.ExcelPackage(source)) { foreach (var item in package.Workbook.Worksheets) { if (((IList)_sheetsNameToKeep).Contains(item.Name)) continue; else package.Workbook.Worksheets.Delete(item); } package.Save(); } } finally { Monitor.Exit(m_lockObject); } } } public static void FormatHeader1(string _filePath,string _sheetName, int _rowStart, int _columnStart, int _rowEnd, int _columnEnd) { if (Monitor.TryEnter(m_lockObject, WAITING_TIME)) { try { System.IO.FileInfo source = new System.IO.FileInfo(_filePath); using (var package = new OfficeOpenXml.ExcelPackage(source)) { ExcelWorksheet ws = package.Workbook.Worksheets[_sheetName]; ExcelRange range = ws.Cells[_rowStart, _columnStart, _rowEnd, _columnEnd]; range.Style.Fill.PatternType = ExcelFillStyle.Solid; range.Style.Fill.BackgroundColor.SetColor(Color.DarkBlue); range.Style.Font.Color.SetColor(Color.White); range.Style.Font.Bold = true; package.Save(); } } finally { Monitor.Exit(m_lockObject); } } } public static void UpdateTraficLight(string _filePath, string _sheetName, int _rowStart, int _columnStart, int _rowCount) { if (Monitor.TryEnter(m_lockObject, WAITING_TIME)) { try { System.IO.FileInfo source = new System.IO.FileInfo(_filePath); using (var package = new OfficeOpenXml.ExcelPackage(source)) { ExcelWorksheet ws = package.Workbook.Worksheets[_sheetName]; for (int i = 0; i < _rowCount; i++) { if (ws.Cells[_rowStart + i, _columnStart].Value.ToString() == "Amber") { ws.Cells[_rowStart + i, _columnStart].Style.Fill.PatternType = ExcelFillStyle.Solid; ws.Cells[_rowStart + i, _columnStart].Style.Fill.BackgroundColor.SetColor(Color.Yellow); ws.Cells[_rowStart + i, _columnStart].Style.Font.Color.SetColor(Color.Yellow); } else if (ws.Cells[_rowStart + i, _columnStart].Value.ToString() == "Green") { ws.Cells[_rowStart + i, _columnStart].Style.Fill.PatternType = ExcelFillStyle.Solid; ws.Cells[_rowStart + i, _columnStart].Style.Fill.BackgroundColor.SetColor(Color.DarkGreen); ws.Cells[_rowStart + i, _columnStart].Style.Font.Color.SetColor(Color.DarkGreen); } } package.Save(); } } finally { Monitor.Exit(m_lockObject); } } } public static bool ConvertExceltoCSV(string _excelFilePath, string _csvPath, out string _message) { bool succeed = false; _message = ""; if (_excelFilePath.ToLower().EndsWith(".xls")) { } else if (_excelFilePath.ToLower().EndsWith(".xlsx")) { } else { _message = _excelFilePath + " formate is not correct."; return false; } return succeed; } } }

Central Button Creator (driven by users permission) in MVC

In view, call to create the button @Html.Button("Create", "user", -1) the function public static class HtmlExtensions { public static IHtmlString Button(this HtmlHelper htmlHelper, string buttonText, string controllerName, int id) { using (var db = new DA_Reporting_ServiceContext()) { User user = db.Users.First(u => u.User_Login == htmlHelper.ViewContext.HttpContext.User.Identity.Name); var button = new TagBuilder("input"); //button.SetInnerText(buttonText); button.MergeAttribute("type", "submit"); button.MergeAttribute("value", buttonText); if (!user.IsAuthorized(controllerName, id)) { button.MergeAttribute("disabled", "disabled"); } return new HtmlString(button.ToString()); } } public static IHtmlString PermitLink(this HtmlHelper htmlHelper, string linkText, string controllerName, string actionName, object routeValues) { bool hasPermission = false; using (var db = new DA_Reporting_ServiceContext()) { User user = db.Users.First(u => u.User_Login == htmlHelper.ViewContext.HttpContext.User.Identity.Name); hasPermission = user.IsAuthorized(controllerName, int.Parse(routeValues.ToString().Split(',')[0].Split('=')[1])); // get the first parameter as the default id } if (hasPermission) { var link = new TagBuilder("a"); link.SetInnerText(linkText); StringBuilder sb= new StringBuilder(); sb.AppendFormat(@"/{0}/{1}?",controllerName,actionName); foreach (var item in ParseRouteValues(routeValues)) { sb.Append(item.Key + "=" + item.Value+"&"); } link.MergeAttribute("href", sb.ToString()); return new HtmlString(link.ToString()); } return new HtmlString(""); } public static IHtmlString Message(this HtmlHelper htmlHelper, TempDataDictionary tempData) { var label = new TagBuilder("label"); if (!String.IsNullOrEmpty((string)tempData["info"])) { label.SetInnerText(((string)tempData["info"]).Replace(System.Environment.NewLine,"
")); label.AddCssClass("InfoMessage"); } if (!String.IsNullOrEmpty((string)tempData["error"])) { label.SetInnerText(((string)tempData["error"]).Replace(Environment.NewLine,"
")); label.AddCssClass("ErrorMessage"); } if(label.InnerHtml.Length>0) return new HtmlString("

"+label.ToString()+"

"); return new HtmlString(label.ToString()); } private static Dictionary ParseRouteValues(object _routeValues) { string values = _routeValues.ToString().Substring(1, _routeValues.ToString().Length - 2); Dictionary pairs = new Dictionary(); foreach (var item in values.Split(',')) { pairs.Add(item.Split('=')[0].Trim(), item.Split('=')[1].Trim()); } return pairs; } public static IHtmlString EnumToSelectList(this HtmlHelper htmlHelper, T enumSource, string selectedValue) where T : struct { var list = new TagBuilder("select"); foreach (T item in Enum.GetValues(typeof(T)).Cast()) { var option = new TagBuilder("option"); //option.MergeAttribute("value",(int)item); option.SetInnerText(item.ToString()); list.InnerHtml += option.ToString(); } return new HtmlString(list.ToString()); } }

C#_Impersonator Use Another Account to run

// call the Impersonator try { using (new Impersonator("name", "domain", "password")) { string dtsPath = O_926_MIM_Imagine_Import.Project_Path + @"\Import_Imagine.dtsx"; Dictionary conn = new Dictionary(); string message = ""; if (Util.RunDTS(dtsPath, conn, out message)) { this.m_result.Message = dtsPath + " completed successfully."; } else { this.m_result.Message = dtsPath + " failed." + Environment.NewLine + message; this.m_result.Type = StepResultType.Error; } } } catch (Exception ex) { this.m_result.Message = ex.Message; this.m_result.Type = StepResultType.Error; } } //define the Impersonator using System; using System.Security.Principal; using System.Runtime.InteropServices; using System.ComponentModel; namespace SomeNameSpace.Core { public class Impersonator : IDisposable { ///

/// Constructor. Starts the impersonation with the given credentials. /// Please note that the account that instantiates the Impersonator class /// needs to have the 'Act as part of operating system' privilege set. ///

/// The name of the user to act as. /// The domain name of the user to act as. /// The password of the user to act as. public Impersonator( string userName, string domainName, string password) { ImpersonateValidUser(userName, domainName, password); } public void Dispose() { UndoImpersonation(); } #region P/Invoke. [DllImport("advapi32.dll", SetLastError = true)] private static extern int LogonUser( string lpszUserName, string lpszDomain, string lpszPassword, int dwLogonType, int dwLogonProvider, ref IntPtr phToken); [DllImport("advapi32.dll", CharSet = CharSet.Auto, SetLastError = true)] private static extern int DuplicateToken( IntPtr hToken, int impersonationLevel, ref IntPtr hNewToken); [DllImport("advapi32.dll", CharSet = CharSet.Auto, SetLastError = true)] private static extern bool RevertToSelf(); [DllImport("kernel32.dll", CharSet = CharSet.Auto)] private static extern bool CloseHandle( IntPtr handle); private const int LOGON32_LOGON_INTERACTIVE = 2; private const int LOGON32_LOGON_NetworkClearText = 8; private const int LOGON32_PROVIDER_DEFAULT = 0; #endregion #region Private member. // ------------------------------------------------------------------ ///

/// Does the actual impersonation. ///

/// The name of the user to act as. /// The domain name of the user to act as. /// The password of the user to act as. private void ImpersonateValidUser( string _userName, string _domain, string _password) { WindowsIdentity tempWindowsIdentity = null; IntPtr token = IntPtr.Zero; IntPtr tokenDuplicate = IntPtr.Zero; try { if (RevertToSelf()) { if (LogonUser( _userName, _domain, _password, LOGON32_LOGON_NetworkClearText, LOGON32_PROVIDER_DEFAULT, ref token) != 0) { if (DuplicateToken(token, 2, ref tokenDuplicate) != 0) { tempWindowsIdentity = new WindowsIdentity(tokenDuplicate); impersonationContext = tempWindowsIdentity.Impersonate(); } else { throw new Win32Exception(Marshal.GetLastWin32Error()); } } else { throw new Win32Exception(Marshal.GetLastWin32Error()); } } else { throw new Win32Exception(Marshal.GetLastWin32Error()); } } finally { if (token != IntPtr.Zero) { CloseHandle(token); } if (tokenDuplicate != IntPtr.Zero) { CloseHandle(tokenDuplicate); } } } ///

/// Reverts the impersonation. ///

private void UndoImpersonation() { if (impersonationContext != null) { impersonationContext.Undo(); } } private WindowsImpersonationContext impersonationContext = null; // ------------------------------------------------------------------ #endregion } }