public void TestFormulaString()
{
XSSFWorkbook wb = new XSSFWorkbook();
XSSFCell cell = (XSSFCell)wb.CreateSheet().CreateRow(0).CreateCell(0);
CT_Cell ctCell = cell.GetCTCell(); //low-level bean holding cell's xml
cell.SetCellFormula("A2");
Assert.AreEqual(CellType.Formula, cell.CellType);
Assert.AreEqual(cell.CellFormula, "A2");
//the value is not Set and cell's type='N' which means blank
Assert.AreEqual(ST_CellType.n, ctCell.t);
//set cached formula value
cell.SetCellValue("t='str'");
//we are still of 'formula' type
Assert.AreEqual(CellType.Formula, cell.CellType);
Assert.AreEqual(cell.CellFormula, "A2");
//cached formula value is Set and cell's type='STR'
Assert.AreEqual(ST_CellType.str, ctCell.t);
Assert.AreEqual(cell.StringCellValue, "t='str'");
//now remove the formula, the cached formula result remains
cell.SetCellFormula(null);
Assert.AreEqual(CellType.String, cell.CellType);
Assert.AreEqual(ST_CellType.str, ctCell.t);
//the line below failed prior to fix of Bug #47889
Assert.AreEqual(cell.StringCellValue, "t='str'");
//revert to a blank cell
cell.SetCellValue((String)null);
Assert.AreEqual(CellType.Blank, cell.CellType);
Assert.AreEqual(ST_CellType.n, ctCell.t);
Assert.AreEqual(cell.StringCellValue, "");
}
/// <summary>
/// Копирование содержимого ячеек с соранением типа данных
/// </summary>
/// <param name="oldCell"></param>
/// <param name="newCell"></param>
private void CopyCellValue(HSSFCell oldCell, XSSFCell newCell)
{
switch (oldCell.CellType)
{
case CellType.String:
newCell.SetCellValue(oldCell.StringCellValue);
break;
case CellType.Numeric:
newCell.SetCellValue(oldCell.NumericCellValue);
break;
case CellType.Blank:
newCell.SetCellType(CellType.Blank);
break;
case CellType.Boolean:
newCell.SetCellValue(oldCell.BooleanCellValue);
break;
case CellType.Error:
newCell.SetCellErrorValue(oldCell.ErrorCellValue);
break;
case CellType.Formula:
newCell.SetCellFormula(oldCell.CellFormula);
break;
default:
break;
}
}
public int WriteArray_To_ExcelFormulas(int rowAvailableCell, int startingCol, string[,] infoArray)
{
XSSFCellStyle cellStyle = (XSSFCellStyle)workbook.CreateCellStyle();
cellStyle.CloneStyleFrom(defaultCellStyle);
for (int rowCounter = 0; rowCounter <= infoArray.GetUpperBound(0); rowCounter++)
{
for (int columnCounter = 0; columnCounter <= infoArray.GetUpperBound(1); columnCounter++)
{
try
{
XSSFCell cell = (XSSFCell)sheet.GetRow(rowAvailableCell + rowCounter).CreateCell(columnCounter + startingCol);
cell.SetCellType(CellType.Formula);
cell.SetCellFormula(infoArray[rowCounter, columnCounter]);
cell.CellStyle = cellStyle;
}
catch (Exception ex)
{
logger.logException(ex);
consoleLogger.logError(ex.Message);
}
}
}
return(startingCol + infoArray.GetUpperBound(1) + 1);
}
private static void SetCellValue(XSSFCell oldCell, XSSFCell newCell)
{
switch (oldCell.CellType)
{
case CellType.Blank:
newCell.SetCellValue(oldCell.StringCellValue);
break;
case CellType.Boolean:
newCell.SetCellValue(oldCell.BooleanCellValue);
break;
case CellType.Error:
newCell.SetCellErrorValue(oldCell.ErrorCellValue);
break;
case CellType.Formula:
newCell.SetCellFormula(oldCell.CellFormula);
break;
case CellType.Numeric:
newCell.SetCellValue(oldCell.NumericCellValue);
break;
case CellType.String:
newCell.SetCellValue(oldCell.RichStringCellValue);
break;
case CellType.Unknown:
newCell.SetCellValue(oldCell.StringCellValue);
break;
}
}
public void TestBug55843b()
{
XSSFWorkbook wb = new XSSFWorkbook();
try
{
XSSFSheet sheet = wb.CreateSheet("test") as XSSFSheet;
XSSFRow row = sheet.CreateRow(0) as XSSFRow;
XSSFRow row2 = sheet.CreateRow(1) as XSSFRow;
XSSFCell cellA2 = row2.CreateCell(0, CellType.Formula) as XSSFCell;
XSSFCell cellB1 = row.CreateCell(1, CellType.Numeric) as XSSFCell;
cellB1.SetCellValue(10);
XSSFFormulaEvaluator formulaEvaluator = wb.GetCreationHelper().CreateFormulaEvaluator() as XSSFFormulaEvaluator;
cellA2.SetCellFormula("IF(B1=0,\"\",((ROW())))");
CellValue Evaluate = formulaEvaluator.Evaluate(cellA2);
System.Console.WriteLine(Evaluate);
Assert.AreEqual("2", Evaluate.FormatAsString());
cellA2.CellFormula = (/*setter*/ "IF(NOT(B1=0),((ROW())),\"\")");
CellValue EvaluateN = formulaEvaluator.Evaluate(cellA2);
System.Console.WriteLine(EvaluateN);
Assert.AreEqual(Evaluate.ToString(), EvaluateN.ToString());
Assert.AreEqual("2", EvaluateN.FormatAsString());
}
finally
{
wb.Close();
}
}
/**
* @param oldCell
* @param newCell
* @param styleMap
*/
public static void copyCell(HSSFCell oldCell, XSSFCell newCell, Dictionary <int, HSSFCellStyle> styleMap)
{
if (styleMap != null)
{
int stHashCode = oldCell.CellStyle.GetHashCode();
HSSFCellStyle sourceCellStyle = styleMap[stHashCode];
XSSFCellStyle destnCellStyle = (XSSFCellStyle)newCell.CellStyle;
if (sourceCellStyle == null)
{
sourceCellStyle = (HSSFCellStyle)oldCell.Sheet.Workbook.CreateCellStyle();
}
destnCellStyle.CloneStyleFrom(oldCell.CellStyle);
styleMap.Add(stHashCode, sourceCellStyle);
newCell.CellStyle = destnCellStyle;
}
switch (oldCell.CellType)
{
case CellType.String:
newCell.SetCellValue(oldCell.StringCellValue);
break;
case CellType.Numeric:
newCell.SetCellValue(oldCell.NumericCellValue);
break;
case CellType.Blank:
newCell.SetCellValue(string.Empty); //!!!
break;
case CellType.Boolean:
newCell.SetCellValue(oldCell.BooleanCellValue);
break;
case CellType.Error:
newCell.SetCellErrorValue(oldCell.ErrorCellValue);
break;
case CellType.Formula:
newCell.SetCellFormula(oldCell.CellFormula);
break;
default:
break;
}
}
public static async Task CreateFerienliste(string department, DatabaseContext db)
{
await Task.Run(() =>
{
// Path where file will be stored
var templatePath = @"C:\temp\Ferienliste_template.xlsx";
using (var outFile = new FileStream(templatePath.Replace("template", department), FileMode.Create, FileAccess.ReadWrite))
{
// Workbook
var wb = new XSSFWorkbook();
// All years which are used by Absences
var yearsWithHolidays = db.Absence
.Join(db.User, a => a.IdUser, u => u.ID, (a, u) => new { a, u })
.Where((au) => au.u.Department == department)
.Select(au => new[] { au.a.AbsentFrom.Year, au.a.AbsentTo.Year })
.SelectMany(m => m)
.OrderBy(o => o)
.Distinct()
.ToArray();
// Year loop
foreach (var year in yearsWithHolidays)
{
// All users which are used by Absences in this year
var users = from u in db.User
join a in db.Absence on u.ID equals a.IdUser
where (year != DateTime.Now.Year) ? (a.AbsentFrom.Year == year || a.AbsentTo.Year == year) : (u.Deactivated == false) // Show all activated users only in current year
where (u.Department == department) // Always filter by department
orderby u.Username
select u;
List <UserModel> usersThisYear = users.Distinct().ToList();
// Gets the last week of the year and makes it the total of weeks
var totalWeeks = GetWeek(DateTime.Parse(year.ToString() + "-12-31"));
var sh = (XSSFSheet)wb.CreateSheet(year.ToString());
var colCount = 1 + usersThisYear.Count;
var rowNum = 0;
IRow row;
int colNum = 0;
// Row 0
row = sh.CreateRow(rowNum);
foreach (var user in usersThisYear)
{
row.CreateCell(++colNum).SetCellValue(user.Username);
}
// Row 1
row = sh.CreateRow(++rowNum);
colNum = 0;
sh.AddMergedRegion(new CellRangeAddress(rowNum, rowNum + 3, colNum, colNum));
row.CreateCell(colNum).SetCellValue(year);
foreach (var user in usersThisYear)
{
var colLetter = CellReference.ConvertNumToColString(++colNum);
string formula = $"{colLetter}5/{colLetter}3";
XSSFCell cell = (XSSFCell)row.CreateCell(colNum);
cell.SetCellFormula(formula);
XSSFCellStyle style = (XSSFCellStyle)wb.CreateCellStyle();
style.SetDataFormat(9);
cell.CellStyle = style;
}
// Row 2
row = sh.CreateRow(++rowNum);
colNum = 0;
foreach (var user in usersThisYear)
{
row.CreateCell(++colNum).SetCellValue((double)user.Holidays);
}
// Row 3
row = sh.CreateRow(++rowNum);
colNum = 0;
foreach (var user in usersThisYear)
{
var colLetter = CellReference.ConvertNumToColString(++colNum);
string formula = $"{colLetter}3-{colLetter}5";
row.CreateCell(colNum).SetCellFormula(formula);
}
// Row 4
row = sh.CreateRow(++rowNum);
colNum = 0;
foreach (var user in usersThisYear)
{
var colLetter = CellReference.ConvertNumToColString(++colNum);
string formula = $"SUM({colLetter}6:{colLetter}{totalWeeks + 5})";
row.CreateCell(colNum).SetCellFormula(formula);
}
// Calendar week rows
// columnPerUsers stores all users of current year and adds a string with a number of each holiday for a person, where the poition in the string is the week
var columnPerUser = new Dictionary <string, string>();
foreach (var user in usersThisYear)
{
columnPerUser.Add(user.Username, new string('0', totalWeeks));
var absences = db.Absence.Where(a => (a.IdUser == user.ID) && (a.AbsentFrom.Year == year) && (user.Deactivated == false)).ToList();
var daysInWeeks = new int[totalWeeks];
foreach (var a in absences)
{
// https://stackoverflow.com/questions/13440595/get-list-of-dates-from-startdate-to-enddate
//Counts days between a.AbsentFrom and a.AbsentTo
IEnumerable <double> daysToAdd = Enumerable.Range(0, (a.AbsentTo - a.AbsentFrom).Days + 1).ToList().ConvertAll(d => (double)d);
IEnumerable <DateTime> ListOfDates = daysToAdd.Select(a.AbsentFrom.AddDays).ToList();
foreach (var date in ListOfDates)
{
if (date.DayOfWeek != DayOfWeek.Saturday && date.DayOfWeek != DayOfWeek.Sunday)
{
if (a.Negative)
{
daysInWeeks[GetWeek(date) - 1] -= 1; // GetWeek doesn't count from 0
}
else
{
daysInWeeks[GetWeek(date) - 1] += 1; // GetWeek doesn't count from 0
}
}
}
}
columnPerUser[user.Username] = string.Join("", daysInWeeks);
}
// Writes all holidays from all weeks into rows
for (var i = 0; i < totalWeeks; i++)
{
row = sh.CreateRow(++rowNum);
colNum = 0;
row.CreateCell(colNum).SetCellValue("KW" + (i + 1).ToString("D2")); //ToString("D2") for 2 digits like 01
foreach (var user in columnPerUser)
{
row.CreateCell(++colNum).SetCellValue(Int32.Parse(user.Value[i].ToString()));
}
}
// Style
for (int i = 1; i <= sh.GetRow(0).LastCellNum; i++)
{
sh.AutoSizeColumn(i, true); // Not working
}
// Conditional formatting
IConditionalFormattingRule rule;
IPatternFormatting pf;
rule = sh.SheetConditionalFormatting.CreateConditionalFormattingRule(ComparisonOperator.NotEqual, "123456");
rule.CreateFontFormatting().FontColorIndex = IndexedColors.Black.Index;
rule.GetFontFormatting().SetFontStyle(false, true);
rule.CreateBorderFormatting().BorderBottom = rule.CreateBorderFormatting().BorderRight = BorderStyle.Thin;
sh.SheetConditionalFormatting.AddConditionalFormatting(
new[] { CellRangeAddress.ValueOf($"A1:{CellReference.ConvertNumToColString(sh.GetRow(0).LastCellNum - 1)}1") },
rule
);
rule = sh.SheetConditionalFormatting.CreateConditionalFormattingRule(ComparisonOperator.LessThan, "0");
rule.CreateFontFormatting().FontColorIndex = IndexedColors.Red.Index;
sh.SheetConditionalFormatting.AddConditionalFormatting(
new[] { CellRangeAddress.ValueOf($"B4:{CellReference.ConvertNumToColString(sh.GetRow(0).LastCellNum - 1)}4") },
rule
);
rule = sh.SheetConditionalFormatting.CreateConditionalFormattingRule(ComparisonOperator.GreaterThan, "0");
rule.CreateFontFormatting().FontColorIndex = IndexedColors.Black.Index;
pf = rule.CreatePatternFormatting();
pf.FillBackgroundColor = IndexedColors.Grey25Percent.Index;
pf.FillPattern = FillPattern.SolidForeground;
sh.SheetConditionalFormatting.AddConditionalFormatting(
new[] { CellRangeAddress.ValueOf($"B6:{CellReference.ConvertNumToColString(sh.GetRow(0).LastCellNum - 1)}{sh.LastRowNum + 1}") },
rule
);
rule = sh.SheetConditionalFormatting.CreateConditionalFormattingRule(ComparisonOperator.GreaterThan, "1");
rule.CreateFontFormatting().FontColorIndex = IndexedColors.White.Index;
pf = rule.CreatePatternFormatting();
pf.FillBackgroundColor = IndexedColors.Violet.Index;
pf.FillPattern = FillPattern.SolidForeground;
sh.SheetConditionalFormatting.AddConditionalFormatting(
new[] { CellRangeAddress.ValueOf($"B2:{CellReference.ConvertNumToColString(sh.GetRow(0).LastCellNum - 1)}2") },
rule
);
rule = (XSSFConditionalFormattingRule)sh.SheetConditionalFormatting.CreateConditionalFormattingRule(ComparisonOperator.GreaterThan, "0.8");
rule.CreateFontFormatting().FontColorIndex = IndexedColors.Green.Index;
pf = rule.CreatePatternFormatting();
pf.FillBackgroundColor = IndexedColors.LightGreen.Index;
pf.FillPattern = FillPattern.SolidForeground;
sh.SheetConditionalFormatting.AddConditionalFormatting(
new[] { CellRangeAddress.ValueOf($"B2:{CellReference.ConvertNumToColString(sh.GetRow(0).LastCellNum - 1)}2") },
rule
);
rule = (XSSFConditionalFormattingRule)sh.SheetConditionalFormatting.CreateConditionalFormattingRule(ComparisonOperator.LessThanOrEqual, "0.8");
rule.CreateFontFormatting().FontColorIndex = IndexedColors.Black.Index;
pf = rule.CreatePatternFormatting();
pf.FillBackgroundColor = IndexedColors.Red.Index;
pf.FillPattern = FillPattern.SolidForeground;
sh.SheetConditionalFormatting.AddConditionalFormatting(
new[] { CellRangeAddress.ValueOf($"B2:{CellReference.ConvertNumToColString(sh.GetRow(0).LastCellNum - 1)}2") },
rule
);
rule = (XSSFConditionalFormattingRule)sh.SheetConditionalFormatting.CreateConditionalFormattingRule(ComparisonOperator.NotEqual, "123456");
rule.CreateFontFormatting().FontColorIndex = IndexedColors.Violet.Index;
pf = rule.CreatePatternFormatting();
pf.FillBackgroundColor = IndexedColors.Black.Index;
pf.FillPattern = FillPattern.SolidForeground;
sh.SheetConditionalFormatting.AddConditionalFormatting(
new CellRangeAddress[] { CellRangeAddress.ValueOf($"B3:{CellReference.ConvertNumToColString(sh.GetRow(0).LastCellNum - 1)}3") },
rule
);
rule = (XSSFConditionalFormattingRule)sh.SheetConditionalFormatting.CreateConditionalFormattingRule(ComparisonOperator.NotEqual, "123456");
rule.CreateFontFormatting().FontColorIndex = IndexedColors.BrightGreen.Index;
pf = rule.CreatePatternFormatting();
pf.FillBackgroundColor = IndexedColors.Black.Index;
pf.FillPattern = FillPattern.SolidForeground;
sh.SheetConditionalFormatting.AddConditionalFormatting(
new CellRangeAddress[] { CellRangeAddress.ValueOf($"B4:{CellReference.ConvertNumToColString(sh.GetRow(0).LastCellNum - 1)}4") },
rule
);
rule = (XSSFConditionalFormattingRule)sh.SheetConditionalFormatting.CreateConditionalFormattingRule(ComparisonOperator.NotEqual, "123456");
rule.CreateFontFormatting().FontColorIndex = IndexedColors.White.Index;
pf = rule.CreatePatternFormatting();
pf.FillBackgroundColor = IndexedColors.Black.Index;
pf.FillPattern = FillPattern.SolidForeground;
sh.SheetConditionalFormatting.AddConditionalFormatting(
new CellRangeAddress[] { CellRangeAddress.ValueOf($"A2:{CellReference.ConvertNumToColString(sh.GetRow(0).LastCellNum - 1)}5"), CellRangeAddress.ValueOf($"A2:A{sh.LastRowNum + 1}") },
rule
);
}
wb.Write(outFile);
outFile.Close();
}
});
}
public StringBuilder readXls(string file, EERRDataAndMethods eerr, XSSFWorkbook twb, bool applyRate, Double rate)
{
StringBuilder retVal = new StringBuilder("");
HSSFWorkbook wb;
wb = new HSSFWorkbook(new FileStream(file, FileMode.Open));
ISheet sheet = wb.GetSheetAt(0);
IRow r = sheet.GetRow(0);
ICell c = r.GetCell(0);
string company = c.StringCellValue;
XSSFSheet sh = (XSSFSheet)twb.GetSheet("Estado resultado");
string acct = "";
string acctDesc = "";
for (int i = 1; i < sheet.LastRowNum; i++)
{
r = sheet.GetRow(i);
if (r != null)
{
c = r.GetCell(0);
if (c != null)
{
if (c.CellType == CellType.String && (c.StringCellValue).StartsWith(C_STR_IN_ACCOUNT))
{
acct = c.StringCellValue;
acct = acct.Substring(C_STR_IN_ACCOUNT.Length).Trim();
int pos = acct.IndexOf(' ');
acctDesc = acct.Substring(pos + 1).Trim();
acct = acct.Substring(0, pos).Trim();
Console.WriteLine("Account: " + acct + " " + acctDesc);
}
else if (acct.Length > 0 && r.LastCellNum >= 16)
{
//"Estado" 1,"Empresa" 2,"Agrupacion" 3,"Marca," 4,"EERR" 5,"Detalle EERR" 6,"Cuenta" 7,"Desc Cuenta" 8,
//"Mes" 9,"Fecha" 10,"# Compte" 11,"Tipo;Glosa" 12,"Area" 13,"C.Costo" 14,"Item" 15,"Desc Item" 16, "F.Efec" 17,
//"Analisis" 18,"Refer" 19,"Fch Ref" 20,"Fch Vto" 21,"DEBE" 22,"HABER" 23,"SALDO" 24,"Sucursal" 25
string s = "";
XSSFRow row = (XSSFRow)sh.CreateRow(sh.LastRowNum + 1);
XSSFCell cell = (XSSFCell)row.CreateCell(C_OUT_STAT - 1);
cell.SetCellValue(C_DATA_STATUS);
cell = (XSSFCell)row.CreateCell(C_OUT_CIA - 1);
cell.SetCellValue(company);
c = r.GetCell(C_IN_AREA - 1);
cell = (XSSFCell)row.CreateCell(C_OUT_DESC_AREA - 1);
cell.SetCellValue(eerr.getAgrupacion(c.ToString()));
cell = (XSSFCell)row.CreateCell(C_OUT_BRAND - 1);
cell.SetCellValue(eerr.getBrand(getCellValue(c)));
cell = (XSSFCell)row.CreateCell(C_OUT_DET_EERR - 1);
cell.SetCellValue(eerr.getLinea(acct));
cell = (XSSFCell)row.CreateCell(C_OUT_ACCT_NUM - 1);
cell.SetCellValue(acct);
cell = (XSSFCell)row.CreateCell(C_OUT_ACCT_DESC - 1);
cell.SetCellValue(acctDesc);
c = r.GetCell(C_IN_DATE - 1);
cell = (XSSFCell)row.CreateCell(C_OUT_DATE - 1);
cell.SetCellValue(getCellDateValue(c));
cell = (XSSFCell)row.CreateCell(C_OUT_MONTH - 1);
cell.SetCellValue(getMonth(c));
c = r.GetCell(C_IN_COMPTE - 1);
cell = (XSSFCell)row.CreateCell(C_OUT_COMPTE - 1);
cell.SetCellValue(getCellValue(c));
c = r.GetCell(C_IN_TYPE - 1);
cell = (XSSFCell)row.CreateCell(C_OUT_TYPE - 1);
cell.SetCellValue(getCellValue(c));
c = r.GetCell(C_IN_COMMENT - 1);
cell = (XSSFCell)row.CreateCell(C_OUT_COMMENT - 1);
cell.SetCellValue(getCellValue(c));
c = r.GetCell(C_IN_AREA - 1);
cell = (XSSFCell)row.CreateCell(C_OUT_AREA - 1);
cell.SetCellValue(getCellValue(c));
c = r.GetCell(C_IN_COST_CENTER - 1);
cell = (XSSFCell)row.CreateCell(C_OUT_COST_CENT - 1);
cell.SetCellValue(getCellValue(c));
c = r.GetCell(C_IN_ITEM - 1);
cell = (XSSFCell)row.CreateCell(C_OUT_ITEM - 1);
cell.SetCellValue(getCellValue(c));
cell = (XSSFCell)row.CreateCell(C_OUT_ITEM_DESC - 1);
cell.SetCellValue(eerr.getItem(getCellValue(c)));
c = r.GetCell(C_IN_EFF_DATE - 1);
cell = (XSSFCell)row.CreateCell(C_OUT_EFF_DATE - 1);
cell.SetCellValue(getCellDateValue(c));
c = r.GetCell(C_IN_ANALISYS_DATE - 1);
cell = (XSSFCell)row.CreateCell(C_OUT_ANALYSIS_DATE - 1);
cell.SetCellValue(getCellValue(c));
c = r.GetCell(C_IN_REFERENCE - 1);
cell = (XSSFCell)row.CreateCell(C_OUT_REF - 1);
cell.SetCellValue(getCellValue(c));
c = r.GetCell(C_IN_REF_DATE - 1);
cell = (XSSFCell)row.CreateCell(C_OUT_REF_DATE - 1);
cell.SetCellValue(getCellDateValue(c));
c = r.GetCell(C_IN_EXP_DATE - 1);
cell = (XSSFCell)row.CreateCell(C_OUT_EXP_DATE - 1);
cell.SetCellValue(getCellDateValue(c));
XSSFCell deb = null;
short doubleFormat = HSSFDataFormat.GetBuiltinFormat("#,##0"); //wb.CreateDataFormat().GetFormat("#,##0");
double v = 0;
c = r.GetCell(C_IN_DEBIT - 1);
cell = (XSSFCell)row.CreateCell(C_OUT_DEBIT - 1);
deb = cell;
if (c != null)
{
s = c.ToString();
if (!string.IsNullOrEmpty(s) && Double.TryParse(s, out v))
{
//cell = (XSSFCell)row.CreateCell(C_OUT_DEBIT - 1);
cell.SetCellValue((applyRate?rate:1) * v);
cell.SetCellType(CellType.Numeric);
cell.CellStyle.DataFormat = doubleFormat;
}
}
XSSFCell cred = null;
c = r.GetCell(C_IN_CREDIT - 1);
cell = (XSSFCell)row.CreateCell(C_OUT_CREDIT - 1);
cred = cell;
if (c != null)
{
s = c.ToString();
if (!string.IsNullOrEmpty(s) && Double.TryParse(s, out v))
{
//cell = (XSSFCell)row.CreateCell(C_OUT_CREDIT - 1);
cell.SetCellValue((applyRate ? rate : 1) * v);
cell.SetCellType(CellType.Numeric);
cell.CellStyle.DataFormat = doubleFormat;
}
}
c = r.GetCell(C_IN_BALANCE - 1);
if (c != null)
{
s = c.ToString();
if (!string.IsNullOrEmpty(s) && Double.TryParse(s, out v))
{
cell = (XSSFCell)row.CreateCell(C_OUT_BALANCE - 1);
cell.SetCellValue((applyRate ? rate : 1) * v);
cell.SetCellType(CellType.Formula);
cell.SetCellFormula(String.Format("{0}{1}-{2}{3}", C_COL_DEBIT, cell.Row.RowNum + 1, C_COL_CREDIT, cell.Row.RowNum + 1));
cell.CellStyle.DataFormat = doubleFormat;
}
}
c = r.GetCell(C_IN_BRANCH - 1);
if (c != null)
{
s = c.ToString();
if (!string.IsNullOrEmpty(s))
{
cell = (XSSFCell)row.CreateCell(C_OUT_BRANCH - 1);
cell.SetCellValue(eerr.getSucursal(s));
}
}
}
}
}
}
return(retVal);
}
public void TestFormulaString()
{
XSSFWorkbook wb = new XSSFWorkbook();
XSSFCell cell = (XSSFCell)wb.CreateSheet().CreateRow(0).CreateCell(0);
CT_Cell ctCell = cell.GetCTCell(); //low-level bean holding cell's xml
cell.SetCellFormula("A2");
Assert.AreEqual(CellType.Formula, cell.CellType);
Assert.AreEqual(cell.CellFormula, "A2");
//the value is not Set and cell's type='N' which means blank
Assert.AreEqual(ST_CellType.n, ctCell.t);
//set cached formula value
cell.SetCellValue("t='str'");
//we are still of 'formula' type
Assert.AreEqual(CellType.Formula, cell.CellType);
Assert.AreEqual(cell.CellFormula, "A2");
//cached formula value is Set and cell's type='STR'
Assert.AreEqual(ST_CellType.str, ctCell.t);
Assert.AreEqual(cell.StringCellValue, "t='str'");
//now remove the formula, the cached formula result remains
cell.SetCellFormula(null);
Assert.AreEqual(CellType.String, cell.CellType);
Assert.AreEqual(ST_CellType.str, ctCell.t);
//the line below failed prior to fix of Bug #47889
Assert.AreEqual(cell.StringCellValue, "t='str'");
//revert to a blank cell
cell.SetCellValue((String)null);
Assert.AreEqual(CellType.Blank, cell.CellType);
Assert.AreEqual(ST_CellType.n, ctCell.t);
Assert.AreEqual(cell.StringCellValue, "");
// check behavior with setCellFormulaValidation
String invalidFormula = "A", validFormula = "A2";
FormulaParseException fpe = null;
// check that default is true
Assert.IsTrue(wb.CellFormulaValidation);
// check that valid formula does not throw exception
try
{
cell.SetCellFormula(validFormula);
}
catch (FormulaParseException e)
{
fpe = e;
}
Assert.IsNull(fpe);
// check that invalid formula does throw exception
try
{
cell.SetCellFormula(invalidFormula);
}
catch (FormulaParseException e)
{
fpe = e;
}
Assert.IsNotNull(fpe);
fpe = null;
// set cell formula validation to false
wb.CellFormulaValidation = false;
Assert.IsFalse(wb.CellFormulaValidation);
// check that neither valid nor invalid formula throw an exception
try
{
cell.SetCellFormula(validFormula);
cell.SetCellFormula(invalidFormula);
}
catch (FormulaParseException e)
{
fpe = e;
}
Assert.IsNull(fpe);
}
