public ExcelWorksheet GetSourceWorksheet(DateTime date, ExcelWorkbook workbook)
{
var worksheet = workbook.Worksheets[GetSourceCardName(date)];
if (worksheet == null)
throw new KeyNotFoundException(string.Format("Nie znaleziono karty {0} w pliku wejściowym.", GetSourceCardName(date)));
return worksheet;
}
internal ExcelNamedRange(string name,ExcelWorkbook wb, ExcelWorksheet nameSheet, int index) :
base(wb, nameSheet, name, true)
{
Name = name;
_sheet = nameSheet;
Index = index;
}
internal ExcelStyles(XmlNamespaceManager NameSpaceManager, XmlDocument xml, ExcelWorkbook wb)
: base(NameSpaceManager, xml)
{
_styleXml=xml;
_wb = wb;
_nameSpaceManager = NameSpaceManager;
LoadFromDocument();
}
internal ExcelStyles(XmlNamespaceManager NameSpaceManager, XmlDocument xml, ExcelWorkbook wb) :
base(NameSpaceManager, xml)
{
_styleXml=xml;
_wb = wb;
_nameSpaceManager = NameSpaceManager;
SchemaNodeOrder = new string[] { "numFmts", "fonts", "fills", "borders", "cellStyleXfs", "cellXfs", "cellStyles", "dxfs" };
LoadFromDocument();
}
public Excel(ExcelWorkbook wb)
{
for (int i = 1; i <= wb.Worksheets.Count; i++)
{
ExcelWorksheet sheet = wb.Worksheets[i];
ExcelTable table = new ExcelTable(sheet);
Tables.Add(table);
}
}
public override IEnumerable<SampleEntry> GetSamples(ExcelWorkbook sourceWorkbook, DateTime date)
{
var from = new DateTime(date.Year, date.Month, 1);
var to = new DateTime(date.Year, date.Month, DateTime.DaysInMonth(date.Year, date.Month));
return new DateEnumerable(Period.Daily, from, to).Where(d => !IsSourceEmpty(d, sourceWorkbook))
.Select(d =>
new SampleEntry(this, GetSourceWorksheet(d, sourceWorkbook), d, -1));
}
private void InsertData(string sheetName, ExcelWorkbook wb)
{
var ws = wb.Worksheets[sheetName];
if (!ws.Names.ContainsKey("HeaderRow")) return; //Without the HeaderRow name we can't insert data
int rowCount = 0;
//Find the header row
int headerRow = ws.Names["HeaderRow"].Start.Row;
//Find the headers and insert data for each of them
int column = 1;
while(!String.IsNullOrEmpty(ws.Cells[headerRow, column].Text))
{
//Parse the thing
string columnFormat = ws.Cells[headerRow, column].Value.ToString();
if (columnFormat.Length < 7)
{
//It's gotta have at least 7 characters to be a valid set of options
column++;
continue;
}
string[] opts = columnFormat.Substring(1, columnFormat.Length - 2).Split(";".ToCharArray());
if (opts.Length < 4)
{
//Mis-specified options, skip this one
column++;
continue;
}
string table = opts[0];
string tableColName = opts[1];
string format = opts[2];
string colTitle = opts[3];
string styleSettings = opts.Length >= 5 ? opts[4] : "";
if(string.IsNullOrEmpty(tableColName))
{
//Dump entire table, including headers
InsertFullTable(ws, headerRow, column, _ds.Tables[table], format);
}
else
{
//Dump just a single column
int tmpRowCount = InsertSingleColumn(ws, headerRow, column, _ds.Tables[table], format, tableColName, colTitle);
if (column == 1) rowCount = tmpRowCount;
}
//After we put in the data, apply any required styling to it
ApplyColumnStyleSettings(ws, styleSettings, new ExcelAddress(headerRow + 1, column, headerRow + 1 + _ds.Tables[table].Rows.Count, column));
column++;
}
SetChartSeriesLengths(ws, headerRow, rowCount);
}
private void Close()
{
application.Dispose();
workBook.Dispose();
//System.Runtime.InteropServices.Marshal.ReleaseComObject(application);
application = null;
workBook = null;
workSheet = null;
//System.GC.Collect();
}
public override IEnumerable<SampleEntry> GetSamples(ExcelWorkbook sourceWorkbook, DateTime date)
{
var sourceWorksheet = GetSourceWorksheet(sourceWorkbook);
var from = GetSourceFromNumber();
var to = GetSourceToNumber();
return Enumerable.Range(from, to - from + 1)
.Where(i => !IsSourceEmpty(i, sourceWorksheet))
.Select(i => new SampleEntry(this, sourceWorksheet, date, i));
}
/// <summary>
/// Generates an excel instance
/// </summary>
/// <param name="settings"></param>
private ExcelServerGenerator(NamedLookup settings, bool silent)
: base(settings, silent)
{
ms = new MemoryStream();
app = new ExcelPackage(ms);
workbook = app.Workbook;
app.Workbook.Worksheets.Add("Release Notes");
app.Workbook.Worksheets.MoveToStart("Release Notes");
worksheet = app.Workbook.Worksheets[1];
worksheet.Name = "Release Notes";
worksheet.View.ShowGridLines = false;
}
public IEnumerable<IDictionary<string, object>> ExtractData(Stream fileStream)
{
Package = new ExcelPackage(fileStream);
Workbook = Package.Workbook;
Worksheet = (string.IsNullOrEmpty(SheetName)) ? Workbook.Worksheets[1] : Workbook.Worksheets[SheetName];
Dimension = Worksheet.Dimension;
if (Dimension == null) return Enumerable.Empty<IDictionary<string, object>>();
EndAddress = Dimension.End;
ReadHeaders();
return ReadData();
}
private void Les(ExcelWorkbook workbook, string sheetName, Action<ExcelWorksheet> action)
{
var sheet = workbook.Worksheets.FirstOrDefault(x => x.Name == sheetName);
if (sheet == null)
return;
if (sheet.Dimension == null)
return;
action(sheet);
}
public EPPlusWorksheet(ExcelWorkbook epplusWorkbook, ExcelWorksheet epplusWorksheet,
IWorkbook workbook, DataTable table, String sheetName,
Int32 startRow = 1, Int32 startColumn = 1)
{
_wb = epplusWorkbook;
_ws = epplusWorksheet;
Workbook = workbook;
Table = table;
Name = sheetName;
StartRow = startRow;
StartColumn = startColumn;
}
protected StatbookModel CreateStatbook(ExcelWorkbook workbook)
{
ExcelWorksheet irgf = workbook.Worksheets["IGRF"];
ExcelWorksheet score = workbook.Worksheets["Score"];
ExcelWorksheet lineups = workbook.Worksheets["Lineups"];
ExcelWorksheet fouls = workbook.Worksheets["Penalties"];
StatbookModel model = ProcessIrgf(irgf);
model.Lineups = ProcessLineups(lineups);
model.Penalties = ProcessPenalties(fouls);
model.Scores = ProcessScores(score);
return model;
}
/// <summary>
/// Get or create IWorkbook from file name.
/// </summary>
/// <param name="fileName">Full workbook file path to get or create.</param>
public EPPlusWorkbook(IExcel excel)
{
try
{
Excel = excel;
_package = new ExcelPackage(new FileInfo(Excel.FileName));
_wb = _package.Workbook;
}
catch (Exception)
{
throw;
}
}
private static DataTable getSpecifiedSheet(ExcelWorkbook workbook, int sheetIndex)
{
ExcelWorksheet sheet = workbook.Worksheets[sheetIndex];
DataTable sheetData = new DataTable(sheet.Name);
int rowCount = sheet.Dimension.End.Row;
int colCount = sheet.Dimension.End.Column;
for (int i = 1; i <= colCount; i ++)
{
object cell = sheet.Cells[i, 1].Value;
string columnName = cell != null ? cell.ToString() : string.Empty;
DataColumn column = new DataColumn();
column.DataType = Type.GetType("System.Object");
sheetData.Columns.Add(column);
}
try
{
for (int i = 1; i <= rowCount; i ++)
{
DataRow row = sheetData.NewRow();
for (int j = 1; j <= colCount; j ++)
{
string textColor = string.Empty;
ExcelRange cell = sheet.Cells[i, j];
object cellText = cell.Text;
if(cellText == null)
{
cellText = "--";
}
else
{
textColor = cell.Style.Font.Color.Rgb;
cellText = cellText.ToString();
}
string textFormat = cell.Style.Numberformat.Format;
Dictionary<string, object> box = new Dictionary<string, object> { { "text", cellText }, { "color", textColor}, { "format", textFormat } };
row[j - 1] = box;
}
sheetData.Rows.Add(row);
}
}
catch (Exception e)
{
Console.WriteLine(e.ToString());
}
return sheetData;
}
/// <summary>
/// Stamps information to Excel document properties.
/// </summary>
/// <param name="wb">Workbook handler</param>
/// <param name="title">The title of this document.</param>
/// <param name="subject">The subject for this document.</param>
/// <param name="comments">Comments.</param>
/// <param name="tags">Tags or keywords (use comma delimited string).</param>
public static void StampToDocumentProperties(ExcelWorkbook wb, string title, string subject, string comments, string tags)
{
wb.Properties.Author = "Riza Marhaban";
if (String.IsNullOrEmpty(comments))
wb.Properties.Comments = String.Format("Automatic generated reporting from Edge Site Scan. ({0})", DateTime.Now);
else
wb.Properties.Comments = comments;
if (String.IsNullOrEmpty(title))
wb.Properties.Title = String.Format("Edge Site Scan Report. ({0})", title, DateTime.Now);
else
wb.Properties.Title = String.Format("{0}. ({1})", title, DateTime.Now);
wb.Properties.Subject = subject;
wb.Properties.Keywords = tags;
wb.Properties.Company = "Microsoft";
wb.Properties.Category = "Report Document";
}
/// <summary>
/// Closes the package.
/// </summary>
public void Dispose()
{
if (_package != null)
{
if (Stream != null && (Stream.CanRead || Stream.CanWrite))
{
Stream.Close();
}
_package.Close();
((IDisposable)_stream).Dispose();
if (_workbook != null)
{
_workbook.Dispose();
}
_package = null;
_images = null;
_file = null;
_workbook = null;
_stream = null;
_workbook = null;
}
}
/// <summary>
/// Closes the package.
/// </summary>
public void Dispose()
{
if (_package != null)
{
if (_isExternalStream == false && _stream != null && (_stream.CanRead || _stream.CanWrite))
{
CloseStream();
}
_package.Close();
if (_workbook != null)
{
_workbook.Dispose();
}
_package = null;
_images = null;
_file = null;
_workbook = null;
_stream = null;
_workbook = null;
GC.Collect();
}
}
private static void CalcChain(ExcelWorkbook wb, FormulaParser parser, DependencyChain dc)
{
foreach (var ix in dc.CalcOrder)
{
var item = dc.list[ix];
try
{
var ws = wb.Worksheets.GetBySheetID(item.SheetID);
var v = parser.ParseCell(item.Tokens, ws == null ? "" : ws.Name, item.Row, item.Column);
SetValue(wb, item, v);
}
catch (FormatException fe)
{
throw (fe);
}
catch (Exception e)
{
var error = ExcelErrorValue.Parse(ExcelErrorValue.Values.Value);
SetValue(wb, item, error);
}
}
}
private static void CalcChain(ExcelWorkbook wb, FormulaParser parser, DependencyChain dc, ExcelCalculationOption options)
{
wb.FormulaParser.Configure(config =>
{
config.AllowCircularReferences = options.AllowCircularReferences;
config.PrecisionAndRoundingStrategy = options.PrecisionAndRoundingStrategy;
});
var debug = parser.Logger != null;
foreach (var ix in dc.CalcOrder)
{
var item = dc.list[ix];
try
{
var ws = wb.Worksheets.GetBySheetID(item.SheetID);
var v = parser.ParseCell(item.Tokens, ws == null ? "" : ws.Name, item.Row, item.Column);
SetValue(wb, item, v);
if (debug)
{
parser.Logger.LogCellCounted();
}
Thread.Sleep(0);
}
catch (FormatException fe)
{
throw (fe);
}
catch (CircularReferenceException cre)
{
throw cre;
}
catch (Exception e)
{
var error = ExcelErrorValue.Parse(ExcelErrorValue.Values.Value);
SetValue(wb, item, error);
}
}
}
/// <summary>
/// Closes the package.
/// </summary>
public void Dispose()
{
if(_package != null)
{
if (_isExternalStream==false && Stream != null && (Stream.CanRead || Stream.CanWrite))
{
Stream.Close();
}
_package.Close();
if(_isExternalStream==false) ((IDisposable)_stream).Dispose();
if(_workbook != null)
{
_workbook.Dispose();
}
_package = null;
_images = null;
_file = null;
_workbook = null;
_stream = null;
_workbook = null;
}
}
/// <summary>
///
/// </summary>
/// <param name="input"></param>
/// <param name="output"></param>
/// <param name="Password"></param>
private void Load(Stream input, Stream output, string Password)
{
//Release some resources:
if (this._package != null)
{
this._package.Close();
this._package = null;
}
if (this._stream != null)
{
this._stream.Close();
this._stream.Dispose();
this._stream = null;
}
_isExternalStream = true;
if (input.Length == 0) // Template is blank, Construct new
{
_stream = output;
ConstructNewFile(Password);
}
else
{
Stream ms;
this._stream = output;
if (Password != null)
{
#if !MONO
Stream encrStream = new MemoryStream();
CopyStream(input, ref encrStream);
EncryptedPackageHandler eph = new EncryptedPackageHandler();
Encryption.Password = Password;
ms = eph.DecryptPackage((MemoryStream)encrStream, Encryption);
#endif
#if MONO
throw new NotSupportedException("Encryption is not supported under Mono.");
#endif
}
else
{
ms = new MemoryStream();
CopyStream(input, ref ms);
}
try
{
//this._package = Package.Open(this._stream, FileMode.Open, FileAccess.ReadWrite);
_package = new Packaging.ZipPackage(ms);
}
catch (Exception ex)
{
#if !MONO
EncryptedPackageHandler eph = new EncryptedPackageHandler();
if (Password == null && CompoundDocument.IsStorageILockBytes(CompoundDocument.GetLockbyte((MemoryStream)_stream)) == 0)
{
throw new Exception("Can not open the package. Package is an OLE compound document. If this is an encrypted package, please supply the password", ex);
}
else
{
throw;
}
#endif
#if MONO
throw;
#endif
}
}
//Clear the workbook so that it gets reinitialized next time
this._workbook = null;
}
public ExcelPackage()
{
application = new Xls.ExcelPackage();
workBook = application.Workbook;
workSheet = workBook.Worksheets[1];
}
/// <summary>
///
/// </summary>
/// <param name="input"></param>
/// <param name="output"></param>
/// <param name="Password"></param>
private void Load(Stream input, Stream output, string Password)
{
//Release some resources:
if (this._package != null)
{
this._package.Close();
this._package = null;
}
if (this._stream != null)
{
#if !Core
this._stream.Close();
#endif
this._stream.Dispose();
this._stream = null;
}
_isExternalStream = true;
if (input.Length == 0) // Template is blank, Construct new
{
_stream = output;
ConstructNewFile(Password);
}
else
{
Stream ms;
this._stream = output;
if (Password != null)
{
Stream encrStream = new MemoryStream();
CopyStream(input, ref encrStream);
EncryptedPackageHandler eph = new EncryptedPackageHandler();
Encryption.Password = Password;
ms = eph.DecryptPackage((MemoryStream)encrStream, Encryption);
}
else
{
ms = new MemoryStream();
CopyStream(input, ref ms);
}
try
{
//this._package = Package.Open(this._stream, FileMode.Open, FileAccess.ReadWrite);
_package = new Packaging.ZipPackage(ms);
}
catch (Exception ex)
{
EncryptedPackageHandler eph = new EncryptedPackageHandler();
if (Password == null && CompoundDocument.IsCompoundDocument((MemoryStream)_stream))
{
throw new Exception("Can not open the package. Package is an OLE compound document. If this is an encrypted package, please supply the password", ex);
}
else
{
throw;
}
}
}
//Clear the workbook so that it gets reinitialized next time
this._workbook = null;
}
public static bool NamedStyleExists(this ExcelWorkbook wb, string styleName) => wb.Styles.NamedStyles.FirstOrDefault(c => c.Name == styleName) != null;
public EPPlusWorkbook(ExcelPackage package, FileStream file = null) {
_stream = file;
Package = package;
Workbook = Package.Workbook;
}
public static async Task CalculateAsync(this ExcelWorkbook workbook)
{
await Task.Run(() => Calculate(workbook));
}
internal ExcelNamedRangeCollection(ExcelWorkbook wb)
{
_wb = wb;
_ws = null;
}
/// <summary>
/// Instantiates a new <see cref="ExcelNamedRangeCollection"/> for the specified <paramref name="workbook"/>.
/// </summary>
/// <param name="workbook">The <see cref="ExcelWorkbook"/> that this named range collection is scoped to.</param>
internal ExcelNamedRangeCollection(ExcelWorkbook workbook)
{
this.Workbook = workbook;
this.Worksheet = null;
}
private static void AppendDataToExcel(int countryId, int? year, int? hospitalId, int? month, int? se, DateTime? startDate, DateTime? endDate, ExcelWorkbook excelWorkBook, string storedProcedure, int startRow, int startColumn, int sheet, bool? insert_row)
{
var excelWorksheet = excelWorkBook.Worksheets[sheet];
var row = startRow;
var column = startColumn;
var consString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
using (var con = new SqlConnection(consString))
{
using (var command = new SqlCommand(storedProcedure, con) { CommandType = CommandType.StoredProcedure })
{
command.Parameters.Clear();
command.Parameters.Add("@Country_ID", SqlDbType.Int).Value = countryId;
command.Parameters.Add("@Languaje", SqlDbType.Text).Value = "SPA";
command.Parameters.Add("@Year_case", SqlDbType.Int).Value = year;
command.Parameters.Add("@Hospital_ID", SqlDbType.Int).Value = hospitalId;
command.Parameters.Add("@Mes_", SqlDbType.Int).Value = month;
command.Parameters.Add("@SE", SqlDbType.Int).Value = se;
command.Parameters.Add("@Fecha_inicio", SqlDbType.Date).Value = startDate;
command.Parameters.Add("@Fecha_fin", SqlDbType.Date).Value = endDate;
con.Open();
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
var col = column;
if (row > startRow && insert_row == true) excelWorksheet.InsertRow(row, 1);
for (var i = 0; i < reader.FieldCount; i++)
{
var cell = excelWorksheet.Cells[startRow, col];
if (reader.GetValue(i) != null)
{
int number;
bool isNumber = int.TryParse(reader.GetValue(i).ToString(), out number);
DateTime dt;
bool isDate = DateTime.TryParse(reader.GetValue(i).ToString(), out dt) ;
if (isNumber)
{
excelWorksheet.Cells[row, col].Value = number;
}
else
{
if(isDate)
{
excelWorksheet.Cells[row, col].Value = dt;
}
else
{
excelWorksheet.Cells[row, col].Value = reader.GetValue(i).ToString();
}
}
excelWorksheet.Cells[row, col].StyleID = cell.StyleID;
}
col++;
}
row++;
}
}
command.Parameters.Clear();
con.Close();
}
}
// Apply only if it has a Total row at the end and hast SUM in range, i.e. SUM(A1:A4)
//excelWorksheet.DeleteRow(row, 2);
}
/// <summary>
/// Instantiates a new <see cref="ExcelNamedRangeCollection"/> for the specified <paramref name="workbook"/>
/// and <paramref name="worksheet"/>.
/// </summary>
/// <param name="workbook">The <see cref="ExcelWorkbook"/> that this named range collection belongs to.</param>
/// <param name="worksheet">The <see cref="ExcelWorksheet"/> that this named range collection is scoped to.</param>
internal ExcelNamedRangeCollection(ExcelWorkbook workbook, ExcelWorksheet worksheet)
{
this.Workbook = workbook;
this.Worksheet = worksheet;
}
/// <summary>
/// Creates a new ExcelWorkbookView which provides access to all the
/// view states of the worksheet.
/// </summary>
/// <param name="ns"></param>
/// <param name="node"></param>
/// <param name="wb"></param>
internal ExcelWorkbookView(XmlNamespaceManager ns, XmlNode node, ExcelWorkbook wb) :
base(ns, node)
{
SchemaNodeOrder = wb.SchemaNodeOrder;
}
private static void CalcChain_V2(ExcelWorkbook wb, FormulaParser parser, DependencyChain dc, ExcelRangeBase range = null)
{
if (dc.CalcOrder.Count == 0)
{
if (range != null && range.Address.ToString().StartsWith("P"))
{
}
}
int fiColValidator = FundingConstants_V2.CLAIMS_WORKSHEET_VALIDATOR_COLUMNS.FI_COLUMN;
int fiRowValidator = FundingConstants_V2.CLAIMS_WORKSHEET_VALIDATOR_COLUMNS.FI_ROW;
int ftpColValidator = FundingConstants_V2.CLAIMS_WORKSHEET_VALIDATOR_COLUMNS.FTP_COLUMN;
int ftpRowValidator = FundingConstants_V2.CLAIMS_WORKSHEET_VALIDATOR_COLUMNS.FTP_ROW;
foreach (var ix in dc.CalcOrder)
{
var item = dc.list[ix];
rows.Add(item.Row);
sheetId = item.SheetID;
try
{
var ws = wb.Worksheets.GetBySheetID(item.SheetID);
bool goDefault = true;
if (ws.Cells[fiRowValidator, fiColValidator] != null &&
ws.Cells[fiRowValidator, fiColValidator].Text != "" &&
ws.Cells[fiRowValidator, fiColValidator].Text.ToLower() == FundingConstants_V2.CLAIMS_WORKSHEET_VALIDATOR_COLUMNS.FI_COLUMN_HEADER.ToLower())
{ // FI Worksheet
#region FI WORKSHEETS
if (range != null &&
item.Column == FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.TOTAL_PROGRAMME_COST &&
range.Address.ToString().StartsWith("P") ||
item.Column == FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.GRANT_AMOUNT_FTS)
{
if (ws.Cells[item.Row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.TYPE_OF_SCHEME].Text != null &&
ws.Cells[item.Row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.TYPE_OF_SCHEME].Text != "" &&
ws.Cells[item.Row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.TYPE_OF_SCHEME].Text.ToLower() == FundingConstants_V2.PROGRAM_TYPE.FTS.ToLower())
{
decimal x1 = 0;
bool isValidX1 = Decimal.TryParse(ws.Cells[item.Row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.TOTAL_PROGRAMME_COST].Text.Replace("$", ""), out x1);
if (isValidX1)
{
decimal grantPercentage = .5m;
string nrictype = ws.Cells[item.Row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.NRIC_TYPE].Text.ToString();
string progStartDateStr = ws.Cells[item.Row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.PROG_START_DATE].Text.ToString();
string scAgeFilterStr = ws.Cells[item.Row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.SC_AGE_FILTER].Text.ToString();
bool isSCAgeFilter = !string.IsNullOrEmpty(scAgeFilterStr) && scAgeFilterStr.ToLower() == "yes";
DateTime progStartDate = parseDate(progStartDateStr);
if (nrictype == FundingConstants_V2.CLAIMS_WORKSHEET_CONSTANTS.NRIC_SC &&
progStartDate.CompareTo(FundingConstants_V2.CLAIMS_WORKSHEET_CONSTANTS.SCAGEFILTER_DATE_THRESHOLD) >= 0 &&
isSCAgeFilter
)
{
grantPercentage = .9m;
}
x1 = x1 * grantPercentage;
x1 = Math.Round(x1, 2, MidpointRounding.AwayFromZero);
string formula = string.Format(FundingConstants_V2.CLAIMS_WORKSHEET_FORMULAS.FI_WORKSHEET.GRANT_AMOUNT_FTS, item.Row, item.Row, item.Row, item.Row, item.Row, item.Row, item.Row, item.Row, item.Row, item.Row, item.Row);
if (item.Formula == null)
{
x1 = 0;
}
else if (item.Formula != null && formula != item.Formula.Trim().Replace(" ", ""))
{
x1 = 0;
}
else if (x1 > 2000.00m)
{
x1 = 2000.00m;
}
SetValue(wb, item, x1);
goDefault = false;
}
else
{
SetValue(wb, item, "");
goDefault = false;
}
}
else
{
SetValue(wb, item, "");
goDefault = false;
}
}
else if (range != null &&
item.Column == FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.TOTAL_PROGRAMME_COST &&
range.Address.ToString().StartsWith("P") ||
item.Column == FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.GRANT_AMOUNT_IBFSTS)
{
if (ws.Cells[item.Row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.TYPE_OF_SCHEME].Text != null &&
ws.Cells[item.Row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.TYPE_OF_SCHEME].Text != "" &&
ws.Cells[item.Row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.TYPE_OF_SCHEME].Text.ToLower() == FundingConstants_V2.PROGRAM_TYPE.IBF_STS.ToLower())
{
decimal x1 = 0;
bool isValidX1 = Decimal.TryParse(ws.Cells[item.Row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.TOTAL_PROGRAMME_COST].Text.Replace("$", ""), out x1);
//if(isValidX1)
//{
decimal grantPercentage = .7m;
string nrictype = ws.Cells[item.Row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.NRIC_TYPE].Text.ToString();
string progStartDateStr = ws.Cells[item.Row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.PROG_START_DATE].Text.ToString();
string scAgeFilterStr = ws.Cells[item.Row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.SC_AGE_FILTER].Text.ToString();
bool isSCAgeFilter = !string.IsNullOrEmpty(scAgeFilterStr) && scAgeFilterStr.ToLower() == "yes";
DateTime progStartDate = parseDate(progStartDateStr);
if (nrictype == FundingConstants_V2.CLAIMS_WORKSHEET_CONSTANTS.NRIC_SC &&
progStartDate.CompareTo(FundingConstants_V2.CLAIMS_WORKSHEET_CONSTANTS.SCAGEFILTER_DATE_THRESHOLD) >= 0 &&
isSCAgeFilter
)
{
grantPercentage = .9m;
}
x1 = x1 * grantPercentage;
x1 = Math.Round(x1, 2, MidpointRounding.AwayFromZero);
string formula = string.Format(FundingConstants_V2.CLAIMS_WORKSHEET_FORMULAS.FI_WORKSHEET.GRANT_AMOUNT_IBFSTS, item.Row, item.Row, item.Row, item.Row, item.Row, item.Row, item.Row, item.Row, item.Row, item.Row, item.Row);
if (item.Formula == null)
{
x1 = 0;
}
else if (item.Formula != null && formula != item.Formula.Trim().Replace(" ", ""))
{
x1 = 0;
}
else if (x1 > 7000.00m)
{
x1 = 7000.00m;
}
SetValue(wb, item, x1);
goDefault = false;
//}
//else
//{
// SetValue(wb, item, "");
// goDefault = false;
//}
}
else
{
SetValue(wb, item, "");
goDefault = false;
}
}
else if (item.Column == FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.TOTAL_INTERNAL_COST)
{
//if (ws.Cells[item.Row, FundingConstants.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.INHOUSE_COST].Text != "" ||
// ws.Cells[item.Row, FundingConstants.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.INHOUSE_FEECHARGED].Text != "")
//{
decimal x1 = 0;
decimal x2 = 0;
bool isValidX1 = Decimal.TryParse(ws.Cells[item.Row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.INHOUSE_COST].Text.Replace("$", ""), out x1);
bool isValidX2 = decimal.TryParse(ws.Cells[item.Row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.INHOUSE_FEECHARGED].Text.Replace("$", ""), out x2);
string formula = string.Format(FundingConstants_V2.CLAIMS_WORKSHEET_FORMULAS.FI_WORKSHEET.TOTAL_INTERNAL_COST, item.Row, item.Row);
decimal sum = x1 + x2;
if (item.Formula == null)
{
sum = 0;
}
else if (item.Formula != null && formula != item.Formula.Trim().Replace(" ", ""))
{
sum = 0;
}
else
{
SetValue(wb, item, sum);
goDefault = false;
}
}
else if (item.Column == FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.TOTAL_PROGRAMME_COST)
{
//if (ws.Cells[item.Row, FundingConstants.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.INHOUSE_COST].Text != "" &&
// ws.Cells[item.Row, FundingConstants.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.INHOUSE_FEECHARGED].Text != "")
decimal x1a = 0;
decimal x2a = 0;
bool isValidX1a = Decimal.TryParse(ws.Cells[item.Row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.INHOUSE_COST].Text.Replace("$", ""), out x1a);
bool isValidX2a = decimal.TryParse(ws.Cells[item.Row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.INHOUSE_FEECHARGED].Text.Replace("$", ""), out x2a);
if ((x1a + x2a) != 0m)
{
decimal x1 = 0;
decimal x2 = 0;
bool isValidX1 = Decimal.TryParse(ws.Cells[item.Row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.INHOUSE_COST].Text.Replace("$", ""), out x1);
bool isValidX2 = decimal.TryParse(ws.Cells[item.Row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.INHOUSE_FEECHARGED].Text.Replace("$", ""), out x2);
string formula = string.Format(FundingConstants_V2.CLAIMS_WORKSHEET_FORMULAS.FI_WORKSHEET.TOTAL_PROGRAMME_COST, item.Row, item.Row, item.Row);
decimal sum = x1 + x2;
if (item.Formula == null)
{
sum = 0;
}
else if (item.Formula != null && formula != item.Formula.Trim().Replace(" ", ""))
{
sum = 0;
}
else
{
SetValue(wb, item, sum);
goDefault = false;
}
}
else if (ws.Cells[item.Row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.EXTERNAL_TOTAL].Text != "")
{
decimal x1 = 0;
bool isValidX1 = Decimal.TryParse(ws.Cells[item.Row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.EXTERNAL_TOTAL].Value + "", out x1);
string formula = string.Format(FundingConstants_V2.CLAIMS_WORKSHEET_FORMULAS.FI_WORKSHEET.TOTAL_PROGRAMME_COST, item.Row, item.Row, item.Row);
if (item.Formula == null)
{
x1 = 0;
}
else if (item.Formula != null && formula != item.Formula.Trim().Replace(" ", ""))
{
x1 = 0;
}
else
{
SetValue(wb, item, x1);
goDefault = false;
}
}
}
#endregion
}
else if (ws.Cells[ftpRowValidator, ftpColValidator] != null &&
ws.Cells[ftpRowValidator, ftpColValidator].Text != "" &&
ws.Cells[ftpRowValidator, ftpColValidator].Text.ToLower() == FundingConstants_V2.CLAIMS_WORKSHEET_VALIDATOR_COLUMNS.FTP_COLUMN_HEADER.ToLower())
{ // FTP Worksheet
#region FTP Worksheet
if (range != null &&
item.Column == FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FTP_WORKSHEET.GRANT_AMOUNT_IBFSTS &&
range.Address.ToString().StartsWith("M") ||
item.Column == FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FTP_WORKSHEET.GRANT_AMOUNT_IBFSTS)
{
decimal x1 = 0;
bool isValidX1 = Decimal.TryParse(ws.Cells[item.Row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FTP_WORKSHEET.PROGRAMME_FEE].Value.ToString(), out x1);
//if(isValidX1)
//{
decimal grantPercentage = .7m;
string nrictype = ws.Cells[item.Row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FTP_WORKSHEET.NRIC_TYPE].Text.ToString();
string progStartDateStr = ws.Cells[item.Row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FTP_WORKSHEET.PROG_START_DATE].Text.ToString();
string scAgeFilterStr = ws.Cells[item.Row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FTP_WORKSHEET.SC_AGE_FILTER].Text.ToString();
bool isSCAgeFilter = !string.IsNullOrEmpty(scAgeFilterStr) && scAgeFilterStr.ToLower() == "yes";
DateTime progStartDate = parseDate(progStartDateStr);
if (nrictype == FundingConstants_V2.CLAIMS_WORKSHEET_CONSTANTS.NRIC_SC &&
progStartDate.CompareTo(FundingConstants_V2.CLAIMS_WORKSHEET_CONSTANTS.SCAGEFILTER_DATE_THRESHOLD) >= 0 &&
isSCAgeFilter
)
{
grantPercentage = .9m;
}
x1 = x1 * grantPercentage;
x1 = Math.Round(x1, 2, MidpointRounding.AwayFromZero);
//string formula = "IF(EXACT(N"+item.Row+",\"IBF-STS\"),MIN(O"+item.Row+"*0.5,2000),\"\")";
// =ROUND(MIN(K16*0.7,7000),2)
// string formula = "MIN(K"+item.Row+"*0.7,7000)";
string formula = string.Format(FundingConstants_V2.CLAIMS_WORKSHEET_FORMULAS.FTP_WORKSHEET.GRANT_AMOUNT_IBFSTS, item.Row, item.Row, item.Row, item.Row, item.Row, item.Row, item.Row, item.Row, item.Row, item.Row);
if (item.Formula == null)
{
x1 = 0;
}
else if (item.Formula != null && formula != item.Formula.Trim().Replace(" ", ""))
{
x1 = 0;
}
else if (x1 > 7000.00m)
{
x1 = 7000.00m;
}
SetValue(wb, item, x1);
goDefault = false;
//}
//else
//{
// SetValue(wb, item, "");
// goDefault = false;
//}
}
#endregion
}
if (goDefault)
{
var v = parser.ParseCell(item.Tokens, ws == null ? "" : ws.Name, item.Row, item.Column);
SetValue(wb, item, v);
}
}
catch (FormatException fe)
{
throw (fe);
}
catch (Exception e)
{
var error = ExcelErrorValue.Parse(ExcelErrorValue.Values.Value);
SetValue(wb, item, error);
}
}
var worksheet = wb.Worksheets.GetBySheetID(sheetId);
foreach (int row in rows)
{
if (worksheet.Cells[fiRowValidator, fiColValidator] != null &&
worksheet.Cells[fiRowValidator, fiColValidator].Text != "" &&
worksheet.Cells[fiRowValidator, fiColValidator].Text.ToLower() == FundingConstants_V2.CLAIMS_WORKSHEET_VALIDATOR_COLUMNS.FI_COLUMN_HEADER.ToLower())
{
#region FI WORKSHEETS
if (row >= 22)
{
if (String.IsNullOrEmpty(worksheet.Cells[row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.GRANT_AMOUNT_FTS].Formula) ||
(!String.IsNullOrEmpty(worksheet.Cells[row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.GRANT_AMOUNT_FTS].Formula) &&
worksheet.Cells[row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.GRANT_AMOUNT_FTS].Formula != string.Format(FundingConstants_V2.CLAIMS_WORKSHEET_FORMULAS.FI_WORKSHEET.GRANT_AMOUNT_FTS, row, row, row, row, row, row, row, row, row, row, row)))
{
worksheet._values.SetValue(row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.GRANT_AMOUNT_FTS, 0);
}
if (String.IsNullOrEmpty(worksheet.Cells[row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.GRANT_AMOUNT_IBFSTS].Formula) ||
(!String.IsNullOrEmpty(worksheet.Cells[row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.GRANT_AMOUNT_IBFSTS].Formula) &&
worksheet.Cells[row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.GRANT_AMOUNT_IBFSTS].Formula != string.Format(FundingConstants_V2.CLAIMS_WORKSHEET_FORMULAS.FI_WORKSHEET.GRANT_AMOUNT_IBFSTS, row, row, row, row, row, row, row, row, row, row, row)))
{
worksheet._values.SetValue(row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.GRANT_AMOUNT_IBFSTS, 0);
}
if (String.IsNullOrEmpty(worksheet.Cells[row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.TOTAL_INTERNAL_COST].Formula) ||
(!String.IsNullOrEmpty(worksheet.Cells[row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.TOTAL_INTERNAL_COST].Formula) &&
worksheet.Cells[row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.TOTAL_INTERNAL_COST].Formula != string.Format(FundingConstants_V2.CLAIMS_WORKSHEET_FORMULAS.FI_WORKSHEET.TOTAL_INTERNAL_COST, row, row)))
{
worksheet._values.SetValue(row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.TOTAL_INTERNAL_COST, 0);
}
if (String.IsNullOrEmpty(worksheet.Cells[row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.TOTAL_PROGRAMME_COST].Formula) ||
(!String.IsNullOrEmpty(worksheet.Cells[row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.TOTAL_PROGRAMME_COST].Formula) &&
worksheet.Cells[row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.TOTAL_PROGRAMME_COST].Formula != string.Format(FundingConstants_V2.CLAIMS_WORKSHEET_FORMULAS.FI_WORKSHEET.TOTAL_PROGRAMME_COST, row, row, row)))
{
worksheet._values.SetValue(row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.TOTAL_PROGRAMME_COST, 0);
}
}
#endregion
}
else if (worksheet.Cells[ftpRowValidator, ftpColValidator] != null &&
worksheet.Cells[ftpRowValidator, ftpColValidator].Text != "" &&
worksheet.Cells[ftpRowValidator, ftpColValidator].Text.ToLower() == FundingConstants_V2.CLAIMS_WORKSHEET_VALIDATOR_COLUMNS.FTP_COLUMN_HEADER.ToLower())
{
#region FTP Worksheets
if (row >= 16)
{
if (String.IsNullOrEmpty(worksheet.Cells[row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FTP_WORKSHEET.GRANT_AMOUNT_IBFSTS].Formula) ||
(!String.IsNullOrEmpty(worksheet.Cells[row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FTP_WORKSHEET.GRANT_AMOUNT_IBFSTS].Formula) &&
worksheet.Cells[row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FTP_WORKSHEET.GRANT_AMOUNT_IBFSTS].Formula != string.Format(FundingConstants_V2.CLAIMS_WORKSHEET_FORMULAS.FTP_WORKSHEET.GRANT_AMOUNT_IBFSTS, row, row, row, row, row, row, row, row, row, row)))
{
worksheet.Cells[row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FTP_WORKSHEET.GRANT_AMOUNT_IBFSTS].Value = 0;
}
}
#endregion
}
}
}
internal ExcelProtection(XmlNamespaceManager ns, XmlNode topNode, ExcelWorkbook wb) :
base(ns, topNode)
{
SchemaNodeOrder = wb.SchemaNodeOrder;
}
public static void SetProperties(this ExcelWorkbook wb, OfficeProperties props) => wb.Properties.Clone(props);
internal ExcelProtection(XmlNamespaceManager ns, XmlNode topNode, ExcelWorkbook wb) :
base(ns, topNode)
{
SchemaNodeOrder = wb.SchemaNodeOrder;
}
/// <summary>
///
/// </summary>
/// <param name="input"></param>
/// <param name="output"></param>
/// <param name="Password"></param>
private void Load(Stream input, Stream output, string Password)
{
//Release some resources:
if (this._package != null)
{
this._package.Close();
this._package = null;
}
if (this._stream != null)
{
this._stream.Close();
this._stream.Dispose();
this._stream = null;
}
if (Password != null)
{
Stream encrStream = new MemoryStream();
CopyStream(input, ref encrStream);
EncryptedPackageHandler eph=new EncryptedPackageHandler();
Encryption.Password = Password;
this._stream = eph.DecryptPackage((MemoryStream)encrStream, Encryption);
}
else
{
this._stream = output;
CopyStream(input, ref this._stream);
}
try
{
//this._package = Package.Open(this._stream, FileMode.Open, FileAccess.ReadWrite);
_package = new Packaging.ZipPackage(_stream);
}
catch (Exception ex)
{
EncryptedPackageHandler eph = new EncryptedPackageHandler();
if (Password == null && CompoundDocument.IsStorageILockBytes(CompoundDocument.GetLockbyte((MemoryStream)_stream)) == 0)
{
throw new Exception("Can not open the package. Package is an OLE compound document. If this is an encrypted package, please supply the password", ex);
}
else
{
throw;
}
}
//Clear the workbook so that it gets reinitialized next time
this._workbook = null;
}
internal ExcelNamedRangeCollection(ExcelWorkbook wb, ExcelWorksheet ws)
{
_wb = wb;
_ws = ws;
}
internal ExcelNamedRange(string name, ExcelWorkbook wb, ExcelWorksheet nameSheet) :
base(wb, nameSheet, name, true)
{
Name = name;
_sheet = nameSheet;
}
/// <summary>
/// Creates a new ExcelWorkbookView which provides access to all the
/// view states of the worksheet.
/// </summary>
/// <param name="ns"></param>
/// <param name="node"></param>
/// <param name="wb"></param>
internal ExcelWorkbookView(XmlNamespaceManager ns, XmlNode node, ExcelWorkbook wb) :
base(ns, node)
{
SchemaNodeOrder = wb.SchemaNodeOrder;
}
private static void CalcChain(ExcelWorkbook wb, FormulaParser parser, DependencyChain dc)
{
foreach (var ix in dc.CalcOrder)
{
var item = dc.list[ix];
try
{
var ws = wb.Worksheets.GetBySheetID(item.SheetID);
var v = parser.ParseCell(item.Tokens, ws == null ? "" : ws.Name, item.Row, item.Column);
SetValue(wb, item, v);
}
catch (FormatException fe)
{
throw (fe);
}
catch// (Exception e)
{
var error = ExcelErrorValue.Parse(ExcelErrorValue.Values.Value);
SetValue(wb, item, error);
}
}
}
private void Load(Stream input, Stream output, string password)
{
//Release some resources:
if (this._package != null)
{
this._package.Close();
this._package = null;
}
if (this._stream != null)
{
this._stream.Close();
this._stream.Dispose();
this._stream = null;
}
this._isExternalStream = true;
if (input.Length == 0) // Template is blank, Construct new
{
this._stream = output;
this.ConstructNewFile(password);
}
else
{
Stream ms;
this._stream = output;
if (password != null)
{
#if !MONO
Stream encrStream = new MemoryStream();
ExcelPackage.CopyStream(input, ref encrStream);
EncryptedPackageHandler eph = new EncryptedPackageHandler();
this.Encryption.Password = password;
ms = eph.DecryptPackage((MemoryStream)encrStream, this.Encryption);
#endif
#if MONO
throw new NotSupportedException("Encryption is not supported under Mono.");
#endif
}
else
{
ms = new MemoryStream();
ExcelPackage.CopyStream(input, ref ms);
}
try
{
//this._package = Package.Open(this._stream, FileMode.Open, FileAccess.ReadWrite);
this._package = new Packaging.ZipPackage(ms);
}
catch (Exception ex)
{
#if !MONO
EncryptedPackageHandler eph = new EncryptedPackageHandler();
if (password == null && CompoundDocument.IsStorageILockBytes(CompoundDocument.GetLockbyte((MemoryStream)_stream)) == 0)
{
throw new Exception("Can not open the package. Package is an OLE compound document. If this is an encrypted package, please supply the password", ex);
}
else
{
throw;
}
#endif
#if MONO
throw;
#endif
}
}
//Clear the workbook so that it gets reinitialized next time
this._workbook = null;
}
private static void Init(ExcelWorkbook workbook)
{
workbook._formulaTokens = new CellStore<List<Token>>();;
foreach (var ws in workbook.Worksheets)
{
if (!(ws is ExcelChartsheet))
{
if (ws._formulaTokens != null)
{
ws._formulaTokens.Dispose();
}
ws._formulaTokens = new CellStore<List<Token>>();
}
}
}
public bool IsSourceEmpty(DateTime date, ExcelWorkbook workbook)
{
return Mappings.OfType<CellMapping>()
.Select(m => m.IsSourceValuePresent(GetSourceWorksheet(date, workbook)))
.All(b => !b);
}
private static void SetValue(ExcelWorkbook workbook, FormulaCell item, object v)
{
if (item.Column == 0)
{
if (item.SheetID <= 0)
{
workbook.Names[item.Row].NameValue = v;
}
else
{
var sh = workbook.Worksheets.GetBySheetID(item.SheetID);
sh.Names[item.Row].NameValue = v;
}
}
else
{
var sheet = workbook.Worksheets.GetBySheetID(item.SheetID);
sheet._values.SetValue(item.Row, item.Column, v);
}
}
private bool checkSheetsMatch(ExcelWorkbook current)
{
bool sheetsMatch = false;
int rooms = 0, presenters = 0;
for (int count = 0; count < 2; count++)
{
ExcelWorksheet currentWorksheet = current.Worksheets[count + 1];
if (currentWorksheet.Name.Equals("Rooms"))
{
rooms = 1;
}
if (currentWorksheet.Name.Equals("Presenters"))
{
presenters = 1;
}
}
sheetsMatch = (rooms + presenters == 2);
return sheetsMatch;
}
public ExcelPackage(string pathToTemplate)
{
application = new Xls.ExcelPackage(new FileInfo(pathToTemplate));
workBook = application.Workbook;
workSheet = workBook.Worksheets[1];
}
/// <summary>
/// Closes the package.
/// </summary>
public void Dispose()
{
if(_package != null)
{
if (Stream != null && (Stream.CanRead || Stream.CanWrite))
{
Stream.Close();
}
_package.Close();
((IDisposable)_stream).Dispose();
((IDisposable)_workbook).Dispose();
_package = null;
_images = null;
_file = null;
_workbook = null;
_stream = null;
_workbook = null;
}
}
public void TestInitialize()
{
package = new ExcelPackage();
workbook = package.Workbook;
workbook.Worksheets.Add("NEW1");
}
