Пример #1
0
        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, "");
        }
Пример #2
0
        /// <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;
            }
        }
Пример #3
0
        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);
        }
Пример #4
0
        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;
            }
        }
Пример #5
0
        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();
            }
        }
Пример #6
0
        /**
         * @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;
            }
        }
Пример #7
0
        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();
                }
            });
        }
Пример #8
0
        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);
        }
Пример #9
0
        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);
        }