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());
}
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);
}
}
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;
}
}
/// <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);
}
}
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);
}
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));
}
/// <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);
}
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;
}
}
/// <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++;
}
public static string EncodeColumn(int C)
{
return(TCellAddress.EncodeColumn(C));
}
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);
}