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);
/// <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; }
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"); }