private static void GetChain(DependencyChain depChain, ILexer lexer, ExcelNamedRange name, ExcelCalculationOption options)
{
var ws = name.Worksheet;
var id = ExcelCellBase.GetCellID(ws == null?0:ws.SheetID, name.Index, 0);
if (!depChain.index.ContainsKey(id))
{
var f = new FormulaCell()
{
SheetID = ws == null ? 0 : ws.SheetID, Row = name.Index, Column = 0, Formula = name.NameFormula
};
if (!string.IsNullOrEmpty(f.Formula))
{
f.Tokens = lexer.Tokenize(f.Formula, (ws == null ? null : ws.Name)).ToList();
if (ws == null)
{
name._workbook._formulaTokens.SetValue(name.Index, 0, f.Tokens);
}
else
{
ws._formulaTokens.SetValue(name.Index, 0, f.Tokens);
}
depChain.Add(f);
FollowChain(depChain, lexer, name._workbook, ws, f, options);
}
}
}
public void UpdateFormulaAbsolutePartialFixedRangeUpdatesReference()
{
using (var excelPackage = new ExcelPackage())
{
var worksheet = excelPackage.Workbook.Worksheets.Add("Sheet1");
var namedRange = new ExcelNamedRange("someName", excelPackage.Workbook, worksheet, "sheet1!$E5:U$7", 0);
namedRange.UpdateFormula(3, 3, 2, 4, worksheet);
Assert.AreEqual("'sheet1'!$I5:U$9", namedRange.NameFormula);
}
}
public void UpdateFormulaAbsoluteColumnDeleteUpdatesReference()
{
using (var excelPackage = new ExcelPackage())
{
var worksheet = excelPackage.Workbook.Worksheets.Add("Sheet1");
var namedRange = new ExcelNamedRange("someName", excelPackage.Workbook, worksheet, "Sheet1!$E5", 0);
namedRange.UpdateFormula(0, 2, 0, -2, worksheet);
Assert.AreEqual("'Sheet1'!$C5", namedRange.NameFormula);
}
}
public void UpdateFormulaAbsoluteRowUpdatesReference()
{
using (var excelPackage = new ExcelPackage())
{
var worksheet = excelPackage.Workbook.Worksheets.Add("Sheet1");
var namedRange = new ExcelNamedRange("someName", excelPackage.Workbook, worksheet, "Sheet1!E$5", 0);
namedRange.UpdateFormula(2, 0, 4, 0, worksheet);
Assert.AreEqual("'Sheet1'!E$9", namedRange.NameFormula);
}
}
public void UpdateFormulaCommaSeparatedColumnDeleteUpdatesReferences()
{
using (var excelPackage = new ExcelPackage())
{
var worksheet = excelPackage.Workbook.Worksheets.Add("Sheet1");
var namedRange = new ExcelNamedRange("someName", excelPackage.Workbook, worksheet, "Sheet1!$L$10,Sheet1!G$8,Sheet1!F12,Sheet1!$E5", 0);
namedRange.UpdateFormula(0, 2, 0, -2, worksheet);
Assert.AreEqual("'Sheet1'!$J$10,'Sheet1'!G$8,'Sheet1'!F12,'Sheet1'!$C5", namedRange.NameFormula);
}
}
public void UpdateFormulaRelativeColumnUpdateDoesNotChange()
{
const string formula = "Sheet1!E5";
using (var excelPackage = new ExcelPackage())
{
var worksheet = excelPackage.Workbook.Worksheets.Add("Sheet1");
var namedRange = new ExcelNamedRange("someName", excelPackage.Workbook, worksheet, formula, 0);
namedRange.UpdateFormula(0, 2, 0, 5, worksheet);
Assert.AreEqual("'Sheet1'!E5", namedRange.NameFormula);
}
}
/// <summary>
///
/// </summary>
/// <param name="address"></param>
/// <param name="tempNamedRange"></param>
/// <param name="values"></param>
protected ExcelRange ExportRange(string address, ExcelNamedRange tempNamedRange, Dictionary <string, object> values)
{
var worksheet = Report.GetWorksheet(tempNamedRange.Worksheet.Name);
var cellRange = worksheet.Cells[address];
tempNamedRange.Copy(cellRange);
tempNamedRange.CopySettings(cellRange);
ExportValueReplacer.Replace(cellRange, values);
return(cellRange);
}
/// <summary>
///
/// </summary>
/// <param name="namedRange"></param>
/// <param name="range"></param>
private static void CopyRowsSettings(this ExcelNamedRange namedRange, ExcelRange range)
{
for (var i = 0; i < namedRange.Rows; i++)
{
var tempRowIndex = namedRange.Start.Row + i;
var tempRow = namedRange.Worksheet.Row(tempRowIndex);
var rowIndex = range.Start.Column + i;
var row = range.Worksheet.Row(rowIndex);
row.CopyFromTemplate(tempRow);
}
}
/// <summary>
///
/// </summary>
/// <param name="namedRange"></param>
/// <param name="range"></param>
private static void CopyColumnsSettings(this ExcelNamedRange namedRange, ExcelRange range)
{
for (var i = 0; i < namedRange.Columns; i++)
{
var tempColumnIndex = namedRange.Start.Column + i;
var tempColumn = namedRange.Worksheet.Column(tempColumnIndex);
var columnIndex = range.Start.Column + i;
var column = range.Worksheet.Column(columnIndex);
column.CopyFromTemplate(tempColumn);
}
}
/// <summary>
///
/// </summary>
/// <param name="tempNamedRange"></param>
public string GetExportRowAddress(ExcelNamedRange tempNamedRange)
{
var startRow = RowIndex;
var startColumn = tempNamedRange.Start.Column;
var endRow = startRow + tempNamedRange.Rows - 1;
var endColumn = tempNamedRange.Columns;
var address = ExcelCellBase.GetAddress(startRow, startColumn, endRow, endColumn);
RowIndex += tempNamedRange.Rows;
ColumnIndex = 1;
return(address);
}
private static void FillWorksheetData(DataSet data, ExcelWorksheet ws, ExcelNamedRange n, string[] deliminator)
{
if (data.Tables.Contains(n.Name) == false)
{
return;
}
var dt = data.Tables[n.Name];
int row = n.Start.Row;
var cn = new string[n.Columns];
var st = new int[n.Columns];
for (int i = 0; i < n.Columns; i++)
{
cn[i] = (n.Value as object[, ])[0, i].ToString().Replace(deliminator[0], "").Replace(deliminator[1], "");
if (cn[i].Contains("."))
{
cn[i] = cn[i].Split('.')[1];
}
st[i] = ws.Cells[row, n.Start.Column + i].StyleID;
}
foreach (DataRow r in dt.Rows)
{
for (int col = 0; col < n.Columns; col++)
{
if (dt.Columns.Contains(cn[col]))
{
ws.Cells[row, n.Start.Column + col].Value = r[cn[col]];
}
ws.Cells[row, n.Start.Column + col].StyleID = st[col];
}
row++;
}
// extend table formatting range to all rows
foreach (var t in ws.Tables)
{
var a = t.Address;
if (n.Start.Row.Between(a.Start.Row, a.End.Row) &&
n.Start.Column.Between(a.Start.Column, a.End.Column))
{
ExtendRows(t, dt.Rows.Count - 1);
}
}
}
private void _ContractValue(IEnumerable <ContractValue> Contracts)
{
ExcelNamedRange cvalue = Util.Book.Names["REF_CVALUE"];
//塞值
int rowStart = cvalue.Start.Row + 2;
int rowCnt = 0;
foreach (var contract in Contracts)
{
int row = rowStart + rowCnt;
Util.ContractValue.Cells[row, 1].Value = contract.ID.Trim();
Util.ContractValue.Cells[row, 2].Value = contract.CValue;
Util.ContractValue.Cells[row, 3].Value = contract.Curncy.Trim();
rowCnt++;
}
}
/// <summary>
///
/// </summary>
/// <param name="tempNamedRange"></param>
/// <returns></returns>
public string GetExportColumnAddress(ExcelNamedRange tempNamedRange)
{
var startRow = RowIndex;
var startColumn = ColumnIndex;
var endRow = startRow + tempNamedRange.Rows - 1;
var endColumn = ColumnIndex + tempNamedRange.Columns - 1;
var address = ExcelCellBase.GetAddress(startRow, startColumn, endRow, endColumn);
ColumnIndex += tempNamedRange.Columns;
if (ImportedColumnRowsCount == 0 || ImportedColumnRowsCount < tempNamedRange.Rows)
{
ImportedColumnRowsCount = tempNamedRange.Rows;
}
return(address);
}
private void tsGenerate_Click(object sender, EventArgs e)
{
string[] stkacno = Util.INI["SYSTEM"]["STKACCNO"].Split(SPLIT, StringSplitOptions.RemoveEmptyEntries);
string[] futacno = Util.INI["SYSTEM"]["FUTACCNO"].Split(SPLIT, StringSplitOptions.RemoveEmptyEntries);
string[] foreignfutacno = Util.INI["SYSTEM"]["FOREIGNACCNO"].Split(SPLIT, StringSplitOptions.RemoveEmptyEntries);
IEnumerable <ContractValue> contracts = Util.SQL.Query <ContractValue>("SELECT DISTINCT HEAD AS ID,Currency AS Curncy,CValue FROM tblFuture ORDER BY ID");
IEnumerable <STK> stks = Util.SQL.Query <STK>($"SELECT [ACNO] AS AccNo,[COMPANY_NO] AS ID,[COMPANY_NAME] AS Name,[AVA_STOCK_NOS] +[UN_STOCK_NOS] AS Lots,[MARKET_PRICE] AS Price FROM[ETFForBrian].[dbo].[W0_STOCK_PART_DAILY] WHERE PART_DATE='{DataDate}' AND ACNO IN ('{string.Join("','", stkacno)}') ORDER BY ACNO");
IEnumerable <FUT> futs = Util.SQL.Query <FUT>($"SELECT [INVESTOR_ACNO] AS AccNo,[COMMODITY_ID] AS ID,[COMMODITY_NAME] AS NAME,[SETTLEMENT_MONTH] AS YM,[NON_COVER_QTY] AS LOTS,[SETTLEMENT_PRICE] AS PRICE,[CONTRACT_SIZE] AS CVALUE FROM[ETFForBrian].[dbo].[W0_FUTOPT_PART_DAILY] WHERE STRIKE_PRICE = 0 AND PART_DATE ='{DataDate}' AND INVESTOR_ACNO IN ('{string.Join("','", futacno)}') ORDER BY INVESTOR_ACNO ");
IEnumerable <FUT> foreignfuts = Util.SQL.Query <FUT>($"SELECT [INVESTOR_ACNO] AS AccNo,[COMMODITY_ID] AS ID,[COMMODITY_NAME] AS NAME,[SETTLEMENT_MONTH] AS YM,[NON_COVER_QTY] AS LOTS,[SETTLEMENT_PRICE] AS PRICE,[CONTRACT_SIZE] AS CVALUE FROM[ETFForBrian].[dbo].[W0_FOREIGN_FUTOPT_PART_DAILY] WHERE STRIKE_PRICE = 0 AND PART_DATE ='{ForeignDataDate}' AND INVESTOR_ACNO IN ('{string.Join("','", foreignfutacno)}') ORDER BY INVESTOR_ACNO ");
//按資料數量加Row
ExcelNamedRange accno = Util.Book.Names["STKACCNO"];
Util.Position.InsertRow(accno.Start.Row + 1, Math.Max(stks.Count(), futs.Count()), accno.Start.Row);
//資料日期
ExcelNamedRange datadate = Util.Book.Names["DATADATE"];
ExcelNamedRange foreigndatadate = Util.Book.Names["FOREIGNDATADATE"];
datadate.Value = dtpDataDate.Value.ToString("yyyy/MM/dd");
foreigndatadate.Value = dtpForeignDataDate.Value.ToString("yyyy/MM/dd");
//契約乘數
_ContractValue(contracts);
//匯率
_FxRate();
//股票
_Stk(stks);
//期貨
_Fut(futs);
//國外期貨
_Fut(foreignfuts);
//調整欄位大小
Util.Position.Calculate();
for (int i = 1; i < 20; i++)
{
Util.Position.Column(i).AutoFit();
Util.Position.Column(i).BestFit = true;
}
//存檔
Util.SaveAs(FullFileName);
tsStatusTxt.InvokeIfRequired(() =>
{
tsStatusTxt.Text = "報表產生完成！";
});
//tsOpen_Click(tsOpen, EventArgs.Empty);
}
public ExcelNamedRange GetNamedRange(string name)
{
ExcelNamedRange return_value = null;
if (_worksheet.Names.ContainsKey(name))
{
return_value = _worksheet.Names[name];
}
else
{
if (_worksheet.Workbook.Names.ContainsKey(name))
{
return_value = _worksheet.Workbook.Names[name];
}
}
return(return_value);
}
private void _FxRate()
{
ExcelNamedRange fxrate = Util.Book.Names["REF_FXRATE"];
//轉換+排序
Dictionary <string, double> list = new Dictionary <string, double>();
list.Add("TWD", 1D);
foreach (var rate in m_FXRate)
{
switch (rate.Key)
{
case "TWD CMPN Curncy":
list.Add("USD", rate.Value);
break;
case "JPYTWD CMPN Curncy":
list.Add("JPY", rate.Value);
break;
case "CNYTWD CMPN Curncy":
list.Add("CNY", rate.Value);
break;
case "HKDTWD CMPN Curncy":
list.Add("HKD", rate.Value);
break;
}
}
//塞值
int rowStart = fxrate.Start.Row + 2;
int rowCnt = 0;
var rates = list.OrderBy(e => e.Key);
foreach (var rate in rates)
{
int row = rowStart + rowCnt;
Util.FxRate.Cells[row, 1].Value = rate.Key;
Util.FxRate.Cells[row, 2].Value = rate.Value;
rowCnt++;
}
}
public static Byte[] WriteToExcelJobs(string path, DateTime reportDate, DataSet ds)
{
Byte[] fileBytes = null;
ExcelNamedRange namedRange = null;
FileInfo newFile = new FileInfo(path);
using (ExcelPackage xlPackage = new ExcelPackage(newFile))
{
//Check if worksheet with name "Export data" exists and retrieve that instance or null if it doesn't exist
ExcelWorksheet ws = xlPackage.Workbook.Worksheets.FirstOrDefault(x => x.Name == "Export data");
//If worksheet "Export data" was not found, add it
if (ws == null)
{
ws = xlPackage.Workbook.Worksheets.Add("Export data");
}
DataTable dt = ds.Tables[0];
// lbl_ReportDate
namedRange = xlPackage.Workbook.Names["lbl_ReportDate"];
namedRange.Value = reportDate.FormatDateTime(Constants.DateTimeFormat.ReportDateTime);
int nCol = 16;
int startRow = 6;
int iRow = startRow;
if (dt.Rows.Count > 0)
{
for (int i = 1; i <= dt.Rows.Count; i++)
{
DataRow row = dt.Rows[i - 1];
ws.Cells[iRow, 2].Value = i.ConvertToString();
ws.Cells[iRow, 2].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
ws.Cells[iRow, 3].Value = row["FirstName"];
ws.Cells[iRow, 4].Value = row["LastName"];
ws.Cells[iRow, 5].Value = row["JobType"];
ws.Cells[iRow, 6].Value = row["JobStatus"];
ws.Cells[iRow, 7].Value = row["JobDateDisplay"];
ws.Cells[iRow, 8].Value = row["From"];
ws.Cells[iRow, 9].Value = row["Subject"];
ws.Cells[iRow, 10].Value = row["SRID"];
ws.Cells[iRow, 10].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
ws.Cells[iRow, 11].Value = row["SRCreator"];
ws.Cells[iRow, 12].Value = row["SROwner"];
ws.Cells[iRow, 13].Value = row["SRState"];
ws.Cells[iRow, 13].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
ws.Cells[iRow, 14].Value = row["SRStatus"];
ws.Cells[iRow, 14].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
ws.Cells[iRow, 15].Value = row["AttachFile"];
ws.Cells[iRow, 15].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
ws.Cells[iRow, 16].Value = row["Remark"];
ws.Cells[iRow, 17].Value = row["PoolName"];
iRow++;
}
int nRow = iRow - 1;
SetCellStyle(ws.Cells[startRow, 2, nRow, nCol]);
ws.Cells[nRow, 2, nRow, nCol].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
}
//Read the Excel file in a byte array
fileBytes = xlPackage.GetAsByteArray();
}
return(fileBytes);
}
public static Byte[] WriteToExcelNcb(string path, DateTime reportDate, DataSet ds)
{
Byte[] fileBytes = null;
ExcelNamedRange namedRange = null;
FileInfo newFile = new FileInfo(path);
using (ExcelPackage xlPackage = new ExcelPackage(newFile))
{
//Check if worksheet with name "Export data" exists and retrieve that instance or null if it doesn't exist
ExcelWorksheet ws = xlPackage.Workbook.Worksheets.FirstOrDefault(x => x.Name == "Export data");
//If worksheet "Export data" was not found, add it
if (ws == null)
{
ws = xlPackage.Workbook.Worksheets.Add("Export data");
}
DataTable dt = ds.Tables[0];
// lbl_ReportDate
namedRange = xlPackage.Workbook.Names["lbl_ReportDate"];
namedRange.Value = reportDate.FormatDateTime(Constants.DateTimeFormat.ReportDateTime);
int nCol = 26;
int startRow = 6;
int iRow = startRow;
if (dt.Rows.Count > 0)
{
for (int i = 1; i <= dt.Rows.Count; i++)
{
DataRow row = dt.Rows[i - 1];
ws.Cells[iRow, 2].Value = i.ConvertToString();
ws.Cells[iRow, 2].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
ws.Cells[iRow, 3].Value = row["Sla"];
ws.Cells[iRow, 3].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
ws.Cells[iRow, 4].Value = row["CustomerFistname"];
ws.Cells[iRow, 5].Value = row["CustomerLastname"];
ws.Cells[iRow, 6].Value = row["CardNo"];
ws.Cells[iRow, 7].Value = row["CustomerBirthDateDisplay"];
ws.Cells[iRow, 8].Value = row["NcbCheckStatus"];
ws.Cells[iRow, 8].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
ws.Cells[iRow, 9].Value = row["SRId"];
ws.Cells[iRow, 9].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
ws.Cells[iRow, 10].Value = row["SRState"];
ws.Cells[iRow, 10].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
ws.Cells[iRow, 11].Value = row["SRStatus"];
ws.Cells[iRow, 11].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
ws.Cells[iRow, 12].Value = row["ProductGroupName"];
ws.Cells[iRow, 13].Value = row["ProductName"];
ws.Cells[iRow, 14].Value = row["CampaignName"];
ws.Cells[iRow, 15].Value = row["TypeName"];
ws.Cells[iRow, 16].Value = row["AreaName"];
ws.Cells[iRow, 17].Value = row["SubAreaName"];
ws.Cells[iRow, 18].Value = row["SRCreator"];
ws.Cells[iRow, 19].Value = row["SRCreateDateDisplay"];
ws.Cells[iRow, 19].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
ws.Cells[iRow, 20].Value = row["SROwner"];
ws.Cells[iRow, 21].Value = row["OwnerUpdateDisplay"];
ws.Cells[iRow, 21].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
ws.Cells[iRow, 22].Value = row["SRDelegate"];
ws.Cells[iRow, 23].Value = row["SRDelegateUpdateDisplay"];
ws.Cells[iRow, 24].Value = row["MKTUpperBranch1"];
ws.Cells[iRow, 25].Value = row["MKTUpperBranch2"];
ws.Cells[iRow, 26].Value = row["MKTEmployeeBranch"];
ws.Cells[iRow, 27].Value = row["MKTEmployeeName"];
iRow++;
}
int nRow = iRow - 1;
SetCellStyle(ws.Cells[startRow, 2, nRow, nCol]);
ws.Cells[nRow, 2, nRow, nCol].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
}
//Read the Excel file in a byte array
fileBytes = xlPackage.GetAsByteArray();
}
return(fileBytes);
}
/// <summary>
///
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="tempNamedRange"></param>
/// <param name="values"></param>
public ExcelRange ExportColumn <T>(ExcelNamedRange tempNamedRange, T values) where T : class
{
var dicValues = GetPropertyValues(values);
return(ExportColumn(tempNamedRange, dicValues));
}
public static void RunSample17()
{
using (var package = new ExcelPackage())
{
//Sample fx data
var txt = "Date;AUD;CAD;CHF;DKK;EUR;GBP;HKD;JPY;MYR;NOK;NZD;RUB;SEK;THB;TRY;USD\r\n" +
"2016-03-01;6,17350;6,42084;8,64785;1,25668;9,37376;12,01683;1,11067;0,07599;2,06900;0,99522;5,69227;0,11665;1,00000;0,24233;2,93017;8,63185\r\n" +
"2016-03-02;6,27223;6,42345;8,63480;1,25404;9,35350;12,14970;1,11099;0,07582;2,07401;0,99311;5,73277;0,11757;1,00000;0,24306;2,94083;8,63825\r\n" +
"2016-03-07;6,33778;6,38403;8,50245;1,24980;9,32373;12,05756;1,09314;0,07478;2,07171;0,99751;5,77539;0,11842;1,00000;0,23973;2,91088;8,48885\r\n" +
"2016-03-08;6,30268;6,31774;8,54066;1,25471;9,36254;12,03361;1,09046;0,07531;2,05625;0,99225;5,72501;0,11619;1,00000;0,23948;2,91067;8,47020\r\n" +
"2016-03-09;6,32630;6,33698;8,46118;1,24399;9,28125;11,98879;1,08544;0,07467;2,04128;0,98960;5,71601;0,11863;1,00000;0,23893;2,91349;8,42945\r\n" +
"2016-03-10;6,24241;6,28817;8,48684;1,25260;9,34350;11,99193;1,07956;0,07392;2,04500;0,98267;5,58145;0,11769;1,00000;0,23780;2,89150;8,38245\r\n" +
"2016-03-11;6,30180;6,30152;8,48295;1,24848;9,31230;12,01194;1,07545;0,07352;2,04112;0,98934;5,62335;0,11914;1,00000;0,23809;2,90310;8,34510\r\n" +
"2016-03-15;6,19790;6,21615;8,42931;1,23754;9,22896;11,76418;1,07026;0,07359;2,00929;0,97129;5,49278;0,11694;1,00000;0,23642;2,86487;8,30540\r\n" +
"2016-03-16;6,18508;6,22493;8,41792;1,23543;9,21149;11,72470;1,07152;0,07318;2,01179;0,96907;5,49138;0,11836;1,00000;0,23724;2,84767;8,31775\r\n" +
"2016-03-17;6,25214;6,30642;8,45981;1,24327;9,26623;11,86396;1,05571;0,07356;2,01706;0,98159;5,59544;0,12024;1,00000;0,23543;2,87595;8,18825\r\n" +
"2016-03-18;6,25359;6,32400;8,47826;1,24381;9,26976;11,91322;1,05881;0,07370;2,02554;0,98439;5,59067;0,12063;1,00000;0,23538;2,86880;8,20950";
// Add a new worksheet to the empty workbook and load the fx rates from the text
var ws = package.Workbook.Worksheets.Add("Sheet1");
//Load the sample data with a Swedish culture setting
ws.Cells["A1"].LoadFromText(txt, new ExcelTextFormat()
{
Delimiter = ';', Culture = CultureInfo.GetCultureInfo("sv-SE")
}, TableStyles.Light10, true);
ws.Cells["A2:A12"].Style.Numberformat.Format = "yyyy-mm-dd";
ws.Column(1).Width = 10.57;
List <ExcelNamedRange> excelNamedRanges = new List <ExcelNamedRange>();
for (int i = 0; i < 8; i++)
{
int col = i + 2;
string dataAddress;
if (i > 3) //test discontinuous cells.
{
dataAddress = $"{new ExcelAddress(2, col, 8, col).Address},{new ExcelAddress(10, col, 12, col).Address}";
}
else
{
dataAddress = new ExcelAddress(2, col, 12, col).Address;
}
ExcelNamedRange excelNamedRange = new ExcelNamedRange($"namedRange_{i}", ws, ws, dataAddress, ws.Names.Count); //{ IsNameHidden = true };
ws.Names.Add(excelNamedRange.Name, excelNamedRange);
excelNamedRanges.Add(excelNamedRange);
//ExcelNamedRange namedRange = ws.Names.Add($"namedRange_{col}", ws.Cells[2, col, 12, col]);
//ExcelNamedRange namedRange = ws.Names.AddFormula($"namedRange_{col}", ws.Cells[2, col, 12, col].FullAddressAbsolute);
//excelNamedRanges.Add(namedRange);
}
var sparklineLine = ws.SparklineGroups.Add(eSparklineType.Line, ws.Cells[15, 2, 15, 9], excelNamedRanges);
sparklineLine.High = true;
sparklineLine.ColorHigh.SetColor(Color.Red);
sparklineLine.Low = true;
sparklineLine.ColorLow.SetColor(Color.Green);
sparklineLine.Negative = true;
sparklineLine.ColorNegative.SetColor(Color.Blue);
//var dataRng1 = sparklineLine.DataRange;
//ws.InsertRow(1, 10);
//ws.InsertColumn(1, 10);
//var dataRng2 = sparklineLine.DataRange;
package.SaveAs(Utils.GetFileInfo("Sample17.xlsx"));
}
}
/// <summary>
///
/// </summary>
/// <param name="tempNamedRange"></param>
/// <param name="values"></param>
public ExcelRange ExportColumn(ExcelNamedRange tempNamedRange, Dictionary <string, object> values)
{
var address = ExportCalculator.GetExportColumnAddress(tempNamedRange);
return(ExportRange(address, tempNamedRange, values));
}
/// <summary>
///
/// </summary>
/// <param name="tempNamedRange"></param>
public ExcelRange ExportColumn(ExcelNamedRange tempNamedRange)
{
return(ExportColumn(tempNamedRange, new Dictionary <string, object>()));
}
public void GenerateXlsxReport(System.Data.Common.DbConnection conn)
{
SetDbParams(conn);
using (ExcelPackage package = (templateFile != null ? new ExcelPackage(newFile, templateFile) : new ExcelPackage(newFile)))
{
foreach (var tab in def.tabs)
{
var ws = GetWs(package, tab);
var riadok = 1;
var stlpec = 1;
var start = 1;
// set start position for Label - that would be Global or Local
if (templateFile != null)
{
ExcelNamedRange label = null;
try
{
label = ws.Names["Label"];
}
catch
{
//Console.WriteLine("{0} Exception caught.", e);
try
{
label = package.Workbook.Names["Label"];
}
catch
{
SimpleLog.WriteLog("Label field not found in this workbook/template");
}
}
if (label != null)
{
riadok = label.Start.Row;
stlpec = label.Start.Column;
}
}
var nadpis = "";
if (!(tab.title == null || tab.title == ""))
{
if (tab.title.Trim().ToUpper().Substring(0, 6) == "SELECT")
{
nadpis = conn.QuerySingle <string>(tab.title, QuerryArguments(tab.title));
}
else
{
nadpis = tab.title;
}
}
// Main Select
// https://github.com/ericmend/oracleClientCore-2.0/blob/master/test/dotNetCore.Data.OracleClient.test/OracleClientCore.cs
System.Data.IDataReader reader = conn.ExecuteReader(tab.query, QuerryArguments(tab.query));
List <TableFields> rowConfig = GetDeclaration(reader, tab.fields);
outDef.tabs.Add(new Tab {
name = tab.name, title = tab.title, query = tab.query, fields = rowConfig
});
int r = 0;
int activeColCount = 0;
while (reader.Read())
{
r++;
//Initial section for sheet
if (r == 1)
{
if (nadpis != null && nadpis != "")
{
ws.Cells[riadok, stlpec].Value = nadpis;
if (templateFile == null)
{
using (ExcelRange rr = ws.Cells[riadok, stlpec, riadok, stlpec - 1 + rowConfig.Where(o => o.order != 0).Count()])
{
rr.Merge = true;
//rr.Style.Font.SetFromFont(new Font("Britannic Bold", 12, FontStyle.Italic));
rr.Style.Font.Size = 12;
rr.Style.Font.Bold = true;
rr.Style.Font.Color.SetColor(Color.FromArgb(63, 63, 63));
rr.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.CenterContinuous;
rr.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
//r.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(23, 55, 93));
rr.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(242, 242, 242));
}
}
riadok++;
}
// set Data position
if (templateFile != null)
{
ExcelNamedRange label = null;
try
{
label = ws.Names["Data"];
}
catch
{
try
{
label = package.Workbook.Names["Data"];
}
catch
{
SimpleLog.WriteLog("Data field not found in this workbook/template");
}
}
if (label != null)
{
riadok = label.Start.Row - 1; // Header je nad riadkom (above row)
stlpec = label.Start.Column;
}
}
//Add the headers
for (int i = 0; i < rowConfig.Count; i++)
{
if (rowConfig[i].order != 0)
{
activeColCount++;
if (templateFile == null || tab.printHeader)
{
ws.Cells[riadok, activeColCount + stlpec - 1].Value = rowConfig[i].title;
}
ws.Names.Add(rowConfig[i].name, ws.Cells[riadok, activeColCount + stlpec - 1]);
}
}
//Ok now format the values;
//ws.Cells[1, 1, 1, tab.fields.Count].Style.Font.Bold = true; //Font should be bold
//ws.Cells[1, 1, 1, tab.fields.Count].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
//ws.Cells[1, 1, 1, tab.fields.Count].Style.Fill.BackgroundColor.SetColor(Color.Aqua);
if (templateFile == null || tab.printHeader)
{
using (var range = ws.Cells[riadok, stlpec, riadok, activeColCount + stlpec - 1])
{
range.Style.Font.Color.SetColor(Color.White);
range.Style.Fill.PatternType = ExcelFillStyle.Solid;
//range.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(184, 204, 228));
range.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(79, 129, 189));
range.Style.Font.Bold = true;
range.Style.WrapText = true;
//Only need to grab the first cell of the merged range
//ws.Cells[$"A{row}"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
//ws.Cells[$"A{row}"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
}
}
riadok++;
// set line for data
start = riadok;
}
//data section, FETCH RECORDS
for (int i = 0; i < rowConfig.Count(); i++)
{
var colId = rowConfig[i].colId;
if (rowConfig[i].order != 0 && !reader.IsDBNull(colId))
{
var a = reader.GetValue(colId);
switch (rowConfig[i].type)
{
case "String":
var pom = reader.GetString(colId);
ws.SetValue(riadok, rowConfig[i].order + stlpec - 1, pom.Substring(0, pom.Length /*- correctStringChars */));
break;
case "Integer":
ws.SetValue(riadok, rowConfig[i].order + stlpec - 1, reader.GetInt32(colId));
break;
case "DateTime":
ws.SetValue(riadok, rowConfig[i].order + stlpec - 1, reader.GetDateTime(colId));
break;
case "Date":
ws.SetValue(riadok, rowConfig[i].order + stlpec - 1, reader.GetDateTime(colId));
break;
case "Decimal":
ws.SetValue(riadok, rowConfig[i].order + stlpec - 1, reader.GetDecimal(colId));
break;
case "Byte[]":
ws.SetValue(riadok, rowConfig[i].order + stlpec - 1, reader.GetByte(colId));
break;
default:
ws.SetValue(riadok, rowConfig[i].order + stlpec - 1, reader.GetValue(colId).ToString());
break;
}
;
}
}
riadok++;
}
// no rows
if (r == 0)
{
if (nadpis != null)
{
ws.Cells[riadok, stlpec].Value = nadpis;
using (ExcelRange rr = ws.Cells[riadok, stlpec, riadok, 8 + stlpec])
{
rr.Merge = true;
rr.Style.Font.Size = 12;
rr.Style.Font.Bold = true;
rr.Style.Font.Color.SetColor(Color.FromArgb(63, 63, 63));
rr.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.CenterContinuous;
rr.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
rr.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(242, 242, 242));
}
riadok++;
}
ws.Cells[riadok, stlpec].Value = "No rows in querry result";
SimpleLog.WriteLog("No rows in Query from " + tab.name);
}
else
{
foreach (var row in rowConfig.Where(o => o.format != null && o.format != "auto"))
{
ws.Cells[start, row.order + stlpec - 1, riadok, row.order + stlpec - 1].Style.Numberformat.Format = row.format;
}
//ws.Cells[1, 1, Rows, 1].Style.Numberformat.Format = "#,##0";
//ws.Cells[1, 3, Rows, 3].Style.Numberformat.Format = "YYYY-MM-DD";
//ws.Cells[1, 4, Rows, 5].Style.Numberformat.Format = "#,##0.00";
// add comp fields
// worksheet.Cell(5, 2).Formula = string.Format("SUM({0}:{1})", calcStartAddress, calcEndAddress);
// Autofit
if (templateFile == null)
{
ws.Cells[start - 1, stlpec, riadok, activeColCount + stlpec - 1].AutoFitColumns();
foreach (var row in rowConfig.Where(o => o.order != 0))
{
if (ws.Column(row.order).Width < row.minsize)
{
ws.Column(row.order).Width = row.minsize;
}
}
}
//Create an autofilter(global settings) for the range
if (def.autofilter)
{
ws.Cells[start - 1, stlpec, riadok, activeColCount + stlpec - 1].AutoFilter = true;
}
}
if (enabledTimestamp && def.timestamp)
{
ws.Cells[riadok + 2, stlpec].Value = "Created : " + DateTime.Now.ToString("dd.MM.yyyy H:mm:ss");
}
}
package.Workbook.Calculate();
// Set document properties
package.Workbook.Properties.Comments = "Created with EpSqlGen Copyright © 2018 Miroslav Dubovsky";
package.Workbook.Properties.Created = DateTime.Now;
package.Workbook.Properties.Title = outFileName;
var pomProp = System.Configuration.ConfigurationManager.AppSettings.Get("Author");
if (pomProp != null)
{
package.Workbook.Properties.Author = pomProp;
}
pomProp = System.Configuration.ConfigurationManager.AppSettings.Get("Company");
if (pomProp != null)
{
package.Workbook.Properties.Company = pomProp;
}
if (def.version != null)
{
package.Workbook.Properties.Subject = "Template " + defFileName + ", version:" + def.version;
}
else
{
package.Workbook.Properties.Subject = "Template " + defFileName;
}
package.Save();
}
if (!enabledConsoleLog)
{
Console.Write(JsonConvert.SerializeObject(new { output_file = newFile.FullName }, Formatting.Indented));
}
else
{
SimpleLog.WriteLog("Generated file: " + newFile.FullName);
}
}
/// <summary>
///
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="tempNamedRange"></param>
/// <param name="values"></param>
public List <ExcelRange> ExportColumns <T>(ExcelNamedRange tempNamedRange, IEnumerable <T> values) where T : class
{
var valueList = values.ToList();
return(valueList.Select(f => ExportColumn(tempNamedRange, f)).ToList());
}
/// <summary>
///
/// </summary>
/// <param name="namedRange"></param>
/// <param name="range"></param>
public static void CopySettings(this ExcelNamedRange namedRange, ExcelRange range)
{
namedRange.CopyColumnsSettings(range);
namedRange.CopyRowsSettings(range);
}
/// <summary>
///
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="tempNamedRange"></param>
public virtual ExcelRange ExportRow(ExcelNamedRange tempNamedRange)
{
return(RowExporter.ExportRow(tempNamedRange));
}
public static Byte[] WriteToExcelSR(string path, DateTime reportDate, DataSet ds)
{
Byte[] fileBytes = null;
ExcelNamedRange namedRange = null;
FileInfo newFile = new FileInfo(path);
using (ExcelPackage xlPackage = new ExcelPackage(newFile))
{
//Check if worksheet with name "Export data" exists and retrieve that instance or null if it doesn't exist
ExcelWorksheet ws = xlPackage.Workbook.Worksheets.FirstOrDefault(x => x.Name == "Export data");
//If worksheet "Export data" was not found, add it
if (ws == null)
{
ws = xlPackage.Workbook.Worksheets.Add("Export data");
}
DataTable dt = ds.Tables[0];
// lbl_ReportDate
namedRange = xlPackage.Workbook.Names["lbl_ReportDate"];
namedRange.Value = reportDate.FormatDateTime(Constants.DateTimeFormat.ReportDateTime);
int nCol = 38;
int startRow = 6;
int iRow = startRow;
if (dt.Rows.Count > 0)
{
for (int i = 1; i <= dt.Rows.Count; i++)
{
DataRow row = dt.Rows[i - 1];
ws.Cells[iRow, 2].Value = i.ConvertToString();
ws.Cells[iRow, 2].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
ws.Cells[iRow, 3].Value = row["TotalSla"];
ws.Cells[iRow, 3].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
ws.Cells[iRow, 4].Value = row["CurrentAlert"];
ws.Cells[iRow, 4].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
ws.Cells[iRow, 5].Value = row["CustomerFistname"];
ws.Cells[iRow, 6].Value = row["CustomerLastname"];
ws.Cells[iRow, 7].Value = row["CardNo"];
ws.Cells[iRow, 8].Value = row["AccountNo"];
ws.Cells[iRow, 9].Value = row["CarRegisNo"];
ws.Cells[iRow, 10].Value = row["SRNo"];
ws.Cells[iRow, 10].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
ws.Cells[iRow, 11].Value = row["CreatorBranch"];
ws.Cells[iRow, 12].Value = row["ChannelName"];
ws.Cells[iRow, 13].Value = row["CallId"];
ws.Cells[iRow, 14].Value = row["ANo"];
ws.Cells[iRow, 15].Value = row["ProductGroupName"];
ws.Cells[iRow, 16].Value = row["ProductName"];
ws.Cells[iRow, 17].Value = row["CampaignServiceName"];
ws.Cells[iRow, 18].Value = row["TypeName"];
ws.Cells[iRow, 19].Value = row["AreaName"];
ws.Cells[iRow, 20].Value = row["SubAreaName"];
ws.Cells[iRow, 21].Value = row["SRStateName"];
ws.Cells[iRow, 21].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
ws.Cells[iRow, 22].Value = row["SRStatusName"];
ws.Cells[iRow, 22].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
ws.Cells[iRow, 23].Value = row["CloseDateDisplay"];
ws.Cells[iRow, 23].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
ws.Cells[iRow, 24].Value = row["SRIsverifyPassDisplay"];
ws.Cells[iRow, 24].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
ws.Cells[iRow, 25].Value = row["CreatorName"];
ws.Cells[iRow, 26].Value = row["CreateDateDisplay"];
ws.Cells[iRow, 27].Value = row["OwnerName"];
ws.Cells[iRow, 28].Value = row["UpdateDateOwnerDisplay"];
ws.Cells[iRow, 28].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
ws.Cells[iRow, 29].Value = row["DelegatorName"];
ws.Cells[iRow, 30].Value = row["UpdateDelegateDisplay"];
ws.Cells[iRow, 30].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
ws.Cells[iRow, 31].Value = row["SRSubject"];
ws.Cells[iRow, 32].Value = row["SRRemarkDisplay"];
ws.Cells[iRow, 33].Value = row["ContactName"];
ws.Cells[iRow, 34].Value = row["ContactSurname"];
ws.Cells[iRow, 35].Value = row["Relationship"];
ws.Cells[iRow, 36].Value = row["ContactNo"];
ws.Cells[iRow, 37].Value = row["MediaSourceName"];
ws.Cells[iRow, 38].Value = row["AttachFile"];
ws.Cells[iRow, 38].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
ws.Cells[iRow, 39].Value = row["JobType"];
ws.Cells[iRow, 39].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
iRow++;
}
int nRow = iRow - 1;
SetCellStyle(ws.Cells[startRow, 2, nRow, nCol]);
ws.Cells[nRow, 2, nRow, nCol].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
}
//Read the Excel file in a byte array
fileBytes = xlPackage.GetAsByteArray();
}
return(fileBytes);
}
/// <summary>
/// Adds a new sparklinegroup to the collection
/// </summary>
/// <param name="type">Type of sparkline</param>
/// <param name="locationRange">The location of the sparkline group. The range must have one row or column and must match the number of rows/columns in the datarange</param>
/// <param name="dataRange">The data for the sparkline group</param>
/// <returns></returns>
public ExcelSparklineGroup Add(eSparklineType type, ExcelAddressBase locationRange, ExcelNamedRange dataRange)
{
return(Add(type, locationRange, new List <ExcelNamedRange>()
{
dataRange
}));
}
/// <summary>
/// This method follows the calculation chain to get the order of the calculation
/// Goto (!) is used internally to prevent stackoverflow on extremly large dependency trees (that is, many recursive formulas).
/// </summary>
/// <param name="depChain">The dependency chain object</param>
/// <param name="lexer">The formula tokenizer</param>
/// <param name="wb">The workbook where the formula comes from</param>
/// <param name="ws">The worksheet where the formula comes from</param>
/// <param name="f">The cell function object</param>
/// <param name="options">Calcultaiton options</param>
private static void FollowChain(DependencyChain depChain, ILexer lexer, ExcelWorkbook wb, ExcelWorksheet ws, FormulaCell f, ExcelCalculationOption options)
{
Stack <FormulaCell> stack = new Stack <FormulaCell>();
iterateToken:
while (f.tokenIx < f.Tokens.Count)
{
var t = f.Tokens[f.tokenIx];
if (t.TokenType == TokenType.ExcelAddress)
{
var adr = new ExcelFormulaAddress(t.Value);
if (adr.IsTableAddress)
{
adr.SetRCFromTable(ws.Package, new ExcelAddress(f.Row, f.Column, f.Row, f.Column));
}
if (adr.WorkSheet == null && adr.Collide(new ExcelAddress(f.Row, f.Column, f.Row, f.Column)) != ExcelAddress.eAddressCollition.No && !options.AllowCircularReferences)
{
throw (new CircularReferenceException(string.Format("Circular Reference in cell {0}", ExcelAddress.GetAddress(f.Row, f.Column))));
}
if (adr._fromRow > 0 && adr._fromCol > 0)
{
if (string.IsNullOrEmpty(adr.WorkSheet))
{
if (f.ws == null)
{
f.ws = ws;
}
else if (f.ws.SheetID != f.SheetID)
{
f.ws = wb.Worksheets.GetBySheetID(f.SheetID);
}
}
else
{
f.ws = wb.Worksheets[adr.WorkSheet];
}
if (f.ws != null)
{
f.iterator = f.ws._formulas.GetEnumerator(adr.Start.Row, adr.Start.Column, adr.End.Row, adr.End.Column);
goto iterateCells;
}
}
}
else if (t.TokenType == TokenType.NameValue)
{
ExcelNamedRange name = null;
var worksheet = f.ws ?? ws;
// Worksheet-scoped named ranges take precedence over workbook-scoped named ranges.
if (worksheet?.Names?.ContainsKey(t.Value) == true)
{
name = worksheet.Names[t.Value];
}
else if (wb.Names.ContainsKey(t.Value))
{
name = wb.Names[t.Value];
}
if (name != null)
{
var nameFormulaTokens = name.GetRelativeNameFormula(f.Row, f.Column)?.ToList();
if (nameFormulaTokens.Count == 0 && !string.IsNullOrEmpty(name.NameFormula))
{
nameFormulaTokens = name.Workbook.FormulaParser.Lexer.Tokenize(name.NameFormula)?.ToList();
}
// Remove the current named range token and replace it with the named range's formula.
f.Tokens.RemoveAt(f.tokenIx);
f.Tokens.InsertRange(f.tokenIx, nameFormulaTokens);
goto iterateToken;
}
}
else if (t.TokenType == TokenType.Function && t.Value.IsEquivalentTo(Offset.Name))
{
var stringBuilder = new StringBuilder($"{OffsetAddress.Name}(");
int offsetStartIndex = f.tokenIx;
int parenCount = 1;
for (f.tokenIx += 2; parenCount > 0 && f.tokenIx < f.Tokens.Count; f.tokenIx++)
{
var token = f.Tokens[f.tokenIx];
stringBuilder.Append(token.Value);
if (token.TokenType == TokenType.OpeningParenthesis)
{
parenCount++;
}
else if (token.TokenType == TokenType.ClosingParenthesis)
{
parenCount--;
}
}
ExcelRange cell = ws.Cells[f.Row, f.Column];
string originalFormula = cell.Formula;
string addressOffsetFormula = stringBuilder.ToString();
stringBuilder.Clear();
for (int i = 0; i < f.Tokens.Count; i++)
{
if (i == offsetStartIndex)
{
stringBuilder.Append(0);
}
else if (i < offsetStartIndex || i >= f.tokenIx)
{
stringBuilder.Append(f.Tokens[i].Value);
}
}
cell.Formula = stringBuilder.ToString();
var offsetResult = ws.Calculate(addressOffsetFormula, f.Row, f.Column);
cell.Formula = originalFormula;
if (offsetResult is string resultString)
{
ExcelAddress adr = new ExcelAddress(resultString);
var worksheet = string.IsNullOrEmpty(adr.WorkSheet) ? ws : wb.Worksheets[adr.WorkSheet];
// Only complete the OFFSET's dependency chain if a valid existing address was successfully parsed.
if (worksheet != null)
{
f.Tokens.RemoveRange(offsetStartIndex, f.tokenIx - offsetStartIndex);
var offsetResultTokens = wb.FormulaParser.Lexer.Tokenize(adr.FullAddress);
f.Tokens.InsertRange(offsetStartIndex, offsetResultTokens);
f.ws = worksheet;
f.iterator = f.ws._formulas.GetEnumerator(adr.Start.Row, adr.Start.Column, adr.End.Row, adr.End.Column);
goto iterateCells;
}
}
}
f.tokenIx++;
}
depChain.CalcOrder.Add(f.Index);
if (stack.Count > 0)
{
f = stack.Pop();
goto iterateCells;
}
return;
iterateCells:
while (f.iterator != null && f.iterator.MoveNext())
{
var v = f.iterator.Value;
if (v == null || v.ToString().Trim() == "")
{
continue;
}
var id = ExcelAddress.GetCellID(f.ws.SheetID, f.iterator.Row, f.iterator.Column);
if (!depChain.Index.ContainsKey(id))
{
var rf = new FormulaCell()
{
SheetID = f.ws.SheetID, Row = f.iterator.Row, Column = f.iterator.Column
};
if (f.iterator.Value is int)
{
rf.Formula = f.ws._sharedFormulas[(int)v].GetFormula(f.iterator.Row, f.iterator.Column, ws.Name);
}
else
{
rf.Formula = v.ToString();
}
rf.ws = f.ws;
rf.Tokens = lexer.Tokenize(rf.Formula, f.ws.Name).ToList();
ws._formulaTokens.SetValue(rf.Row, rf.Column, rf.Tokens);
depChain.Add(rf);
stack.Push(f);
f = rf;
goto iterateToken;
}
else
{
if (stack.Count > 0)
{
//Check for circular references
foreach (var par in stack)
{
if (ExcelAddress.GetCellID(par.ws.SheetID, par.iterator.Row, par.iterator.Column) == id ||
ExcelAddress.GetCellID(par.ws.SheetID, par.Row, par.Column) == id)
{
if (options.AllowCircularReferences == false)
{
throw (new CircularReferenceException(string.Format("Circular Reference in cell {0}!{1}", par.ws.Name, ExcelAddress.GetAddress(f.Row, f.Column))));
}
else
{
f = stack.Pop();
goto iterateCells;
}
}
}
}
}
}
f.tokenIx++;
goto iterateToken;
}
private static void GetChain(DependencyChain depChain, ILexer lexer, ExcelNamedRange name, ExcelCalculationOption options)
{
var ws = name.Worksheet;
var id = ExcelCellBase.GetCellID(ws==null?0:ws.SheetID, name.Index, 0);
if (!depChain.index.ContainsKey(id))
{
var f = new FormulaCell() { SheetID = ws == null ? 0 : ws.SheetID, Row = name.Index, Column = 0, Formula=name.NameFormula };
if (!string.IsNullOrEmpty(f.Formula))
{
f.Tokens = lexer.Tokenize(f.Formula, (ws==null ? null : ws.Name)).ToList();
if (ws == null)
{
name._workbook._formulaTokens.SetValue(name.Index, 0, f.Tokens);
}
else
{
ws._formulaTokens.SetValue(name.Index, 0, f.Tokens);
}
depChain.Add(f);
FollowChain(depChain, lexer,name._workbook, ws, f, options);
}
}
}
/// <summary>
///
/// </summary>
/// <param name="tempNamedRange"></param>
/// <param name="values"></param>
public virtual ExcelRange ExportColumn(ExcelNamedRange tempNamedRange, Dictionary <string, object> values)
{
return(ColumnExporter.ExportColumn(tempNamedRange, values));
}
