コード例 #1
0
        private static string Get1Ref(bool R1C1, int Row, int Col, int CellRow, int CellCol, bool RowAbs, bool ColAbs)
        {
            if (Row < 1 || Col < 1 || Row > FlxConsts.Max_Rows + 1 || Col > FlxConsts.Max_Columns + 1)
            {
                return(TFormulaMessages.ErrString(TFlxFormulaErrorValue.ErrRef));
            }

            if (R1C1)
            {
                return(Get1R1C1Ref(Row, Col, CellRow, CellCol, RowAbs, ColAbs));
            }

            string Result = "";  //We won't use a string builder here as it would probably have more overhead.

            if (ColAbs)
            {
                Result = fts(TFormulaToken.fmAbsoluteRef);
            }
            Result = Result + TCellAddress.EncodeColumn(Col);

            if (RowAbs)
            {
                Result = Result + fts(TFormulaToken.fmAbsoluteRef);
            }
            return(Result + Row.ToString());
        }
コード例 #2
0
        private void ReadHyperLinks_Click(object sender, System.EventArgs e)
        {
            if (openFileDialog1.ShowDialog() != DialogResult.OK)
            {
                return;
            }
            Xls = new XlsFile();

            Xls.Open(openFileDialog1.FileName);

            dataGrid.CaptionText = "Hyperlinks on file: " + openFileDialog1.FileName;
            HlDataTable.Rows.Clear();


            for (int i = 1; i <= Xls.HyperLinkCount; i++)
            {
                TXlsCellRange Range = Xls.GetHyperLinkCellRange(i);
                THyperLink    HLink = Xls.GetHyperLink(i);

                string HLinkType = Enum.GetName(typeof(THyperLinkType), HLink.LinkType);

                object[] values = { i,                                                                TCellAddress.EncodeColumn(Range.Left) + Range.Top.ToString(),
                                    TCellAddress.EncodeColumn(Range.Right) + Range.Bottom.ToString(),
                                    HLinkType,
                                    HLink.Text,
                                    HLink.Description,
                                    HLink.TextMark,
                                    HLink.TargetFrame,
                                    HLink.Hint };
                HlDataTable.Rows.Add(values);
            }
        }
コード例 #3
0
ファイル: Form1.cs プロジェクト: trnka/TMS-FlexCel.NET-demos
        private void ImportFile(string FileName)
        {
            try
            {
                XlsFile xls = new XlsFile();
                xls.VirtualMode = true; //Remember to turn virtual mode on, or the event won't be called.

                //By default, FlexCel returns the formula text for the formulas, besides its calculated value.
                //If you are not interested in formula texts, you can gain a little performance by ignoring it.
                //This also works in non virtual mode.
                xls.IgnoreFormulaText = cbIgnoreFormulaText.Checked;

                CellData = new SparseCellArray();

                //Attach the CellReader handler.
                CellReader cr = new CellReader(cbFirst50Rows.Checked, CellData, cbFormatValues.Checked);
                xls.VirtualCellStartReading += new VirtualCellStartReadingEventHandler(cr.OnStartReading);
                xls.VirtualCellRead         += new VirtualCellReadEventHandler(cr.OnCellRead);

                DateTime StartOpen = DateTime.Now;

                //Open the file. As we have a CellReader attached, the cells won't be loaded into memory, they will be passed to the CellReader
                xls.Open(FileName);
                DateTime StartSheetSelect = cr.StartSheetSelect;
                DateTime EndSheetSelect   = cr.EndSheetSelect;

                DateTime EndOpen = DateTime.Now;
                statusBar.Text = "Time to open file: " + (StartSheetSelect - StartOpen).ToString() + "     Time to load file and fill grid: " + (EndOpen - EndSheetSelect).ToString();

                //Set up grid.
                GridCaption.Text = FileName;
                if (CellData != null)
                {
                    DisplayGrid.ColumnCount = CellData.ColCount;
                    DisplayGrid.RowCount    = CellData.RowCount;
                }
                else
                {
                    DisplayGrid.ColumnCount = 0;
                    DisplayGrid.RowCount    = 0;
                }

                for (int i = 0; i < DisplayGrid.ColumnCount; i++)
                {
                    DisplayGrid.Columns[i].Name = TCellAddress.EncodeColumn(i + 1);
                }
            }
            catch
            {
                GridCaption.Text = "Error Loading File";
                CellData         = null;
                throw;
            }
        }
コード例 #4
0
        /// <summary>
        /// Добавляем диапазон в формулу
        /// </summary>
        /// <param name="formulaKey">Ключ формулы</param>
        /// <param name="row">Строка</param>
        /// <param name="addHalfhour">Добавляем колонки получасовок</param>
        /// <param name="addDrum">Добавляем колонки барабанов</param>
        /// <param name="isMinus">Минусовать</param>
        /// <param name="footerName">Название подитога (если нужно), в который добавляем данные</param>
        public void AddToFormula(string formulaKey, int row, bool addHalfhour = true, bool addDrum = true,
                                 bool isMinus = false)
        {
            //Добавляем барабаны для подитога
            if (!string.IsNullOrEmpty(formulaKey))
            {
                if (addDrum)
                {
                    AddRowRangeToFormulaSum(formulaKey + "_drum", new FormulaRowsRange
                    {
                        Col     = _colDrum,
                        Row1    = row,
                        Row2    = row,
                        IsMinus = isMinus,
                    }); //Для итого по перетоку
                }

                //Добавляем получасовки для подитога
                if (addHalfhour)
                {
                    for (var i = 0; i < _halfhourCount; i++)
                    {
                        AddRowRangeToFormulaSum(formulaKey + "_halfhour" + i, new FormulaRowsRange
                        {
                            Col     = _colHalfHour + i,
                            Row1    = row,
                            Row2    = row,
                            IsMinus = isMinus,
                        });
                    }
                }
            }

            if (_isAnalisIntegral && addHalfhour && addDrum)
            {
                //Делаем сравнительный анализ барабанов и получасовок

                var formulaHalfhour = TCellAddress.EncodeColumn(_colHalfHour) + row;

                var formulaStr = formulaHalfhour + " - " +
                                 TCellAddress.EncodeColumn(_colDrum) + row
                                 + _halfhourToIntegralUnitCoeff;

                _xls.SetCellValue(row, _colAnalis,
                                  new TFormula("=" + formulaStr), _xls.ProfileFormat);

                _xls.SetCellValue(row, _colAnalis + 1,
                                  new TFormula("=(" + formulaStr + ")/" + formulaHalfhour), _xls.ProfileFormat);
            }
        }
コード例 #5
0
        internal void WriteFormulaToCell(XlsFile xls, string id, int row, int col, int format, double defaultValue)
        {
            if (_isFormingFormulasToCell)
            {
                var formula = new StringBuilder("=");

                #region Пишем формулу из словаря

                List <FormulaRowsRange> existsRanges;
                if (_formulas.TryGetValue(id, out existsRanges) && existsRanges.Count > 0)
                {
                    foreach (var formulasRange in existsRanges)
                    {
                        formula.Append(!string.IsNullOrEmpty(formulasRange.Before) ? formulasRange.Before : "+");

                        if (!string.IsNullOrEmpty(formulasRange.SheetName))
                        {
                            formula.Append("'").Append(formulasRange.SheetName).Append("'!");
                        }
                        formula.Append(TCellAddress.EncodeColumn(formulasRange.Col)).Append(formulasRange.Row1);
                    }

                    //Обрабатываем последнюю запись
                    try
                    {
                        xls.SetCellValue(row, col, new TFormula(formula.ToString()), format);
                    }
                    catch (Exception ex)
                    {
                        if (_errors != null)
                        {
                            _errors.Append("Ошибка формирования формулы в '" + TCellAddress.EncodeColumn(col) + row + "': " + ex.Message + "\n" + formula);
                        }
                        xls.SetCellValue(row, col, defaultValue, format);
                    }


                    _formulas.Remove(id);
                    return;
                }

                #endregion
            }
            //Нет формулы, или не надо ее формировать
            xls.SetCellValue(row, col, defaultValue, format);
        }
コード例 #6
0
        private static string GetColRange(bool R1C1, int Col1, int Col2, int CellCol, bool Abs1, bool Abs2)
        {
            if (R1C1)
            {
                return(GetFullR1C1Range(TFormulaToken.fmR1C1_C, Col1, Col2, CellCol, Abs1, Abs2));
            }

            string Result = "";

            if (Abs1)
            {
                Result = fts(TFormulaToken.fmAbsoluteRef);
            }
            Result = Result + TCellAddress.EncodeColumn(Col1) + fts(TFormulaToken.fmRangeSep);
            if (Abs2)
            {
                Result = Result + fts(TFormulaToken.fmAbsoluteRef);
            }
            return(Result + TCellAddress.EncodeColumn(Col2));
        }
コード例 #7
0
        /// <summary>
        /// Пишем формулу в excel
        /// </summary>
        /// <param name="id">Идентификатор</param>
        /// <param name="row">Ячейка</param>
        /// <param name="col">Колонка</param>
        /// <param name="format">Формат</param>
        /// <param name="defaultValue">Значение по умолчанию, если не excel, или не найдена формула</param>
        internal void WriteRowRangeFormulaToCell(string id, int row, int col, int format, double defaultValue, FormulaRowsRange rowsRange = null)
        {
            if (_isFormingFormulasToCell)
            {
                var formula = new StringBuilder("=SUM(");

                #region Формирование формулы

                var formingFormulaAction = (Action <string, int, int, int, int?, bool>)((sheetName, row1, row2, col1, col2, isMinus) =>
                {
                    var sheet = isMinus ? "-" : string.Empty;
                    if (!string.IsNullOrEmpty(sheetName))
                    {
                        sheet = "'" + sheetName + "'!";
                    }

                    if (row1 == row2 && !col2.HasValue)
                    {
                        formula.Append(sheet + TCellAddress.EncodeColumn(col1)).Append(row1)
                        .Append(",");
                    }
                    else
                    {
                        formula.Append(sheet + TCellAddress.EncodeColumn(col1)).Append(row1)
                        .Append(":")
                        .Append(sheet + TCellAddress.EncodeColumn(col2 ?? col1)).Append(row2)
                        .Append(",");
                    }
                });

                #endregion

                #region Если пишем формулу не из словаря

                if (rowsRange != null)
                {
                    formingFormulaAction(rowsRange.SheetName, rowsRange.Row1, rowsRange.Row2, rowsRange.Col, rowsRange.Col2, rowsRange.IsMinus);
                    formula.Replace(",", ")", formula.Length - 1, 1); //Удаляем последний ;
                    _xls.SetCellValue(row, col, new TFormula(formula.ToString()), format);
                    return;
                }

                #endregion

                #region Пишем формулу из словаря

                List <FormulaRowsRange> existsRanges;
                if (_formulas.TryGetValue(id, out existsRanges) && existsRanges.Count > 0)
                {
                    var  prevSheetName = string.Empty;
                    var  prevCol       = -1;
                    var  prevRow1      = -1;
                    var  prevRow2      = -1;
                    bool isMinus       = false;
                    foreach (var formulasRange in existsRanges.OrderBy(r => r.SheetName)
                             .ThenBy(r => r.Row1).ThenBy(r => r.Row2))
                    {
                        //Обрабатываем предыдущую запись
                        if (Equals(prevSheetName, (formulasRange.SheetName ?? string.Empty)) &&
                            prevCol == formulasRange.Col && prevRow2 >= formulasRange.Row1 - 1)
                        {
                            //Пересекаются диапазоны, присоединяем диапазон к предыдущему
                            prevRow2 = formulasRange.Row2;
                        }
                        else
                        {
                            //Пишем предыдущую запись
                            if (prevCol > 0)
                            {
                                formingFormulaAction(prevSheetName, prevRow1, prevRow2, prevCol, null, isMinus);
                            }

                            //Сохраняем текущую
                            prevSheetName = formulasRange.SheetName ?? string.Empty;
                            prevCol       = formulasRange.Col;
                            prevRow1      = formulasRange.Row1;
                            prevRow2      = formulasRange.Row2;
                        }

                        isMinus = formulasRange.IsMinus;
                    }

                    //Обрабатываем последнюю запись
                    if (prevCol > 0)
                    {
                        formingFormulaAction(prevSheetName, prevRow1, prevRow2, prevCol, null, isMinus);
                    }

                    formula.Replace(",", ")", formula.Length - 1, 1); //Удаляем последний
                    if (col <= 16384)
                    {
                        _xls.SetCellValue(row, col, new TFormula(formula.ToString()), format);
                    }

                    _formulas.Remove(id);
                    return;
                }

                #endregion
            }
            //Нет формулы, или не надо ее формировать
            _xls.SetCellValue(row, col, defaultValue, format);
        }
コード例 #8
0
        private void ImportFile(string FileName, bool Formatted)
        {
            try
            {
                //Open the Excel file.
                XlsFile  xls       = new XlsFile(false);
                DateTime StartOpen = DateTime.Now;
                xls.Open(FileName);
                DateTime EndOpen = DateTime.Now;

                //Set up the Grid
                DisplayGrid.DataBindings.Clear();
                DisplayGrid.DataSource = null;
                DisplayGrid.DataMember = null;
                DataSet dataSet1 = new DataSet();
                sheetCombo.Items.Clear();

                //We will create a DataTable "SheetN" for each sheet on the Excel sheet.
                for (int sheet = 1; sheet <= xls.SheetCount; sheet++)
                {
                    xls.ActiveSheet = sheet;

                    sheetCombo.Items.Add(xls.SheetName);

                    DataTable Data = dataSet1.Tables.Add("Sheet" + sheet.ToString());
                    Data.BeginLoadData();
                    try
                    {
                        int ColCount = xls.ColCount;
                        //Add one column on the dataset for each used column on Excel.
                        for (int c = 1; c <= ColCount; c++)
                        {
                            Data.Columns.Add(TCellAddress.EncodeColumn(c), typeof(String));  //Here we will add all strings, since we do not know what we are waiting for.
                        }

                        string[] dr = new string[ColCount];

                        int RowCount = xls.RowCount;
                        for (int r = 1; r <= RowCount; r++)
                        {
                            Array.Clear(dr, 0, dr.Length);
                            //This loop will only loop on used cells. It is more efficient than looping on all the columns.
                            for (int cIndex = xls.ColCountInRow(r); cIndex > 0; cIndex--)  //reverse the loop to avoid calling ColCountInRow more than once.
                            {
                                int Col = xls.ColFromIndex(r, cIndex);

                                if (Formatted)
                                {
                                    TRichString rs = xls.GetStringFromCell(r, Col);
                                    dr[Col - 1] = rs.Value;
                                }
                                else
                                {
                                    int    XF  = 0; //This is the cell format, we will not use it here.
                                    object val = xls.GetCellValueIndexed(r, cIndex, ref XF);

                                    TFormula Fmla = val as TFormula;
                                    if (Fmla != null)
                                    {
                                        //When we have formulas, we want to write the formula result.
                                        //If we wanted the formula text, we would not need this part.
                                        dr[Col - 1] = Convert.ToString(Fmla.Result);
                                    }
                                    else
                                    {
                                        dr[Col - 1] = Convert.ToString(val);
                                    }
                                }
                            }
                            Data.Rows.Add(dr);
                        }
                    }
                    finally
                    {
                        Data.EndLoadData();
                    }

                    DateTime EndFill = DateTime.Now;
                    statusBar.Text = String.Format("Time to load file: {0}    Time to fill dataset: {1}     Total time: {2}", (EndOpen - StartOpen).ToString(), (EndFill - EndOpen).ToString(), (EndFill - StartOpen).ToString());
                }

                //Set up grid.
                DisplayGrid.DataSource   = dataSet1;
                DisplayGrid.DataMember   = "Sheet1";
                sheetCombo.SelectedIndex = 0;
                DisplayGrid.CaptionText  = FileName;
            }
            catch
            {
                DisplayGrid.CaptionText = "Error Loading File";
                DisplayGrid.DataSource  = null;
                DisplayGrid.DataMember  = "";
                sheetCombo.Items.Clear();
                throw;
            }
        }
コード例 #9
0
        /// <summary>
        /// Обсчитываем все что выделено между {}
        /// </summary>
        public static void Evaluate(XlsFile xls, ISpreadsheetProperties properties, out int row, StringBuilder errors)
        {
            row = 1;
            var rowCount = Math.Min(xls.RowCount, 100);

            for (var r = 1; r <= rowCount; r++)
            {
                var isNotExistVoidCol = false;
                for (var c = 1; c <= xls.ColCount; c++)
                {
                    var cVal = xls.GetCellValue(r, c);

                    if (!isNotExistVoidCol && cVal != null)
                    {
                        isNotExistVoidCol = true;
                    }

                    var str = cVal as string;
                    if (!string.IsNullOrEmpty(str))
                    {
                        var indxStart = str.IndexOf('{');
                        if (indxStart >= 0)
                        {
                            var indxEnd = str.IndexOf('}');
                            if (indxEnd > 0)
                            {
                                try
                                {
                                    var subStr = str.Substring(indxStart, indxEnd - indxStart + 1);
                                    var ev     = ProryvParsersFactory.ParseTextValue(subStr, properties);
                                    if (str[0] == '=' && ev != null)
                                    {
                                        //Это формула
                                        xls.SetCellValue(r, c, new TFormula(ev.ToString().Replace(',', '.')));
                                    }
                                    else
                                    {
                                        xls.SetCellValue(r, c, str.Replace(subStr, ev.ToString()));
                                    }
                                }
                                catch (Exception ex)
                                {
                                    if (errors != null)
                                    {
                                        errors.Append(" Ошибка в ячейке ").Append(TCellAddress.EncodeColumn(c))
                                        .Append(r).Append(": ").Append(ex.Message).Append(" ")
                                        .Append(
                                            ex.InnerException != null ? ex.InnerException.Message : string.Empty)
                                        .Append("\n");
                                    }
                                }
                            }
                        }
                    }
                }

                //Есть хоть одна заполненная ячейка, смещаем начальную ячейку
                if (isNotExistVoidCol)
                {
                    row = r;
                }
            }

            row++;
        }
コード例 #10
0
ファイル: UCoreConstants.cs プロジェクト: mwilian/demos
 public static string EncodeColumn(int C)
 {
     return(TCellAddress.EncodeColumn(C));
 }
コード例 #11
0
ファイル: ReadExcel.cs プロジェクト: anhpdfu/WebAppForTest
        public static DataSet ToDataSet(byte[] data, bool firstRowIsPropertyName = true)
        {
            bool    formatted = false;
            DataSet dataSet   = new DataSet();

            Stream stream = new MemoryStream(data);

            XlsFile xls = new XlsFile(false);

            xls.Open(stream);

            for (int sheet = 1; sheet <= xls.SheetCount; sheet++)
            {
                xls.ActiveSheet = sheet;

                DataTable Data = dataSet.Tables.Add("Sheet" + sheet.ToString());
                Data.BeginLoadData();

                try
                {
                    int ColCount      = xls.ColCount;
                    int beginIndexRow = 1;

                    if (firstRowIsPropertyName)
                    {
                        beginIndexRow = 2;
                        for (int c = 1; c <= ColCount; c++)
                        {
                            int    r        = 1;
                            int    XF       = 0; // This is the cell format, we will not use it here.
                            object val      = xls.GetCellValueIndexed(r, c, ref XF);
                            string propName = string.Empty;

                            TFormula Fmla = val as TFormula;
                            propName = Convert.ToString(Fmla != null ? Fmla.Result : val);

                            Data.Columns.Add(propName, typeof(String));
                        }
                    }
                    else
                    {
                        beginIndexRow = 1;
                        // Add one column on the dataset for each used column on Excel.
                        for (int c = 1; c <= ColCount; c++)
                        {
                            // Here we will add all strings, since we do not know what we are waiting for.
                            Data.Columns.Add(TCellAddress.EncodeColumn(c), typeof(String));
                        }
                    }

                    string[] dr = new string[ColCount];

                    int rowCount = xls.RowCount;
                    for (int r = beginIndexRow; r <= rowCount; r++)
                    {
                        Array.Clear(dr, 0, dr.Length);
                        //This loop will only loop on used cells. It is more efficient than looping on all the columns.
                        for (int cIndex = xls.ColCountInRow(r); cIndex > 0; cIndex--)  //reverse the loop to avoid calling ColCountInRow more than once.
                        {
                            int Col = xls.ColFromIndex(r, cIndex);

                            if (formatted)
                            {
                                TRichString rs = xls.GetStringFromCell(r, Col);
                                dr[Col - 1] = rs.Value;
                            }
                            else
                            {
                                int    XF  = 0; //This is the cell format, we will not use it here.
                                object val = xls.GetCellValueIndexed(r, cIndex, ref XF);

                                TFormula Fmla = val as TFormula;
                                if (Fmla != null)
                                {
                                    //When we have formulas, we want to write the formula result.
                                    //If we wanted the formula text, we would not need this part.
                                    dr[Col - 1] = Convert.ToString(Fmla.Result);
                                }
                                else
                                {
                                    dr[Col - 1] = Convert.ToString(val);
                                }
                            }
                        }
                        Data.Rows.Add(dr);
                    }
                }
                finally
                {
                    Data.EndLoadData();
                }
            }

            return(dataSet);
        }