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; } } }