/// <summary>
/// Читает данные с текущего листа XLS-файла.
/// </summary>
/// <param name="xlsFile">XSL-файл.</param>
/// <returns>Таблица с прочитанными данными.</returns>
private static DataTable ReadDataTable(XlsFile xlsFile)
{
// установить имя таблицы
DataTable table = new DataTable(xlsFile.GetSheetName(xlsFile.ActiveSheet));
// первая строка - названия столбцов
// вторая строка - типы столбцов
if (xlsFile.RowCount < 2)
throw new CoreInvalidOperationException(Resources.ResourceManager.GetString("InvalidFileFormatException"));
// прочитать имена столбцов для таблицы
for (int col = 0; col < xlsFile.ColCount; col++)
{
if (xlsFile.GetCellValue(1, col + 1) == null)
break;
// прочитать тип столбца
Type columnType = Type.GetType(xlsFile.GetCellValue(2, col + 1).ToString(), false);
if (columnType == null)
throw new CoreInvalidOperationException(Resources.ResourceManager.GetString("InvalidFileFormatException"));
table.Columns.Add(xlsFile.GetCellValue(1, col + 1).ToString(), columnType);
}
// прочитать данные
int rowFrom = 2;
/* XlsFile тупо обрабатывает
* вложенные картинки. Поместить картинку в определённую клетку можно,
* а вот взять её из клетки нельзя. Взять картинку можно только из массива
* рисунков Excel, начинающегося почему то с 2 (хотя в доке написано ч 1!).
* Будем надеяться, что они там в правильном порядке лежат.
*/
int imageCount = 2;
for (int row = rowFrom; row < xlsFile.RowCount; ++row)
{
// Т.к. XlsFile очень часто врёт по поводу количества строк в файле,
// то мы подстраховываемся таким образом. Считаем, что данные кончились,
// если колонка 'A' в строке пустая
if (xlsFile.GetCellValue(row + 1, 1) == null)
break;
object[] values = new object[table.Columns.Count];
for(int col = 0; col < table.Columns.Count; ++col)
{
if(table.Columns[col].DataType == typeof( byte[] ))
{
TXlsImgType imageType = TXlsImgType.Unknown;
values[col] = ConvertValue( xlsFile.GetImage( imageCount++, ref imageType ),
table.Columns[col].DataType );
}
else
{
values[col] = ConvertValue( xlsFile.GetCellValue( row + 1, col + 1 ),
table.Columns[col].DataType );
}
}
table.Rows.Add(values);
}
return table;
}
/// <summary>
///
/// </summary>
/// <param name="xls"></param>
/// <param name="sheet">Start from 1</param>
/// <returns></returns>
public static SheetData GetSheetData(XlsFile xls, int sheet)
{
xls.ActiveSheet = sheet;
SheetData sheetData = new SheetData();
int colCount = xls.GetColCount(sheet);
int rowCount = xls.GetRowCount(sheet);
int row = 1;
for (int col = 1; col <= colCount; col++)
{
string val = Convert.ToString(xls.GetCellValue(row, col));
if (string.IsNullOrEmpty(val))
{
break;
}
sheetData.Columns.Add(val.Trim());
}
colCount = sheetData.Columns.Count();
row++;
while (row <= rowCount)
{
object[] recordRow = new object[colCount];
for (int col = 1; col <= colCount; col++)
{
recordRow[col - 1] = xls.GetCellValue(row, col);
}
sheetData.Records.Add(recordRow);
row++;
}
return(sheetData);
}
private static void FillColRange(TKeyList Items, XlsFile xls, TXlsCellRange Range, int[] Keys, TSortOrder[] SortOrder, IComparer Comparer)
{
for (int c = Range.Left; c <= Range.Right; c++)
{
object[] Values;
if (Keys == null)
{
int Len = Range.RowCount;
if (Len > 8)
{
Len = 8; //avoid taking up too much memory.
}
Values = new object[Len];
for (int r = 0; r < Values.Length; r++)
{
Values[r] = xls.GetCellValue(Range.Top + r, c);
}
}
else
{
Values = new object[Keys.Length];
for (int r = 0; r < Keys.Length; r++)
{
Values[r] = xls.GetCellValue(Keys[r], c);
}
}
Items.Add(new TKeyItem(c, Values, SortOrder, Comparer));
}
}
private static void FillRowRange(TKeyList Items, XlsFile xls, TXlsCellRange Range, int[] Keys, TSortOrder[] SortOrder, IComparer Comparer)
{
for (int r = Range.Top; r <= Range.Bottom; r++)
{
object[] Values;
if (Keys == null)
{
int Len = Range.ColCount;
if (Len > 8)
{
Len = 8; //avoid taking up too much memory.
}
Values = new object[Len];
for (int c = 0; c < Values.Length; c++)
{
Values[c] = xls.GetCellValue(r, Range.Left + c);
}
}
else
{
Values = new object[Keys.Length];
for (int c = 0; c < Keys.Length; c++)
{
Values[c] = xls.GetCellValue(r, Keys[c]);
}
}
Items.Add(new TKeyItem(r, Values, SortOrder, Comparer));
}
}
protected void BtnReadCellA1_Click(object sender, EventArgs e)
{
ExcelFile Xls = new XlsFile();
if (FileBox.PostedFile == null || FileBox.PostedFile.InputStream == null || FileBox.PostedFile.InputStream.Length == 0)
{
LabelA1.Text = "No file selected";
return;
}
FileBox.PostedFile.InputStream.Position = 0;
try
{
Xls.Open(FileBox.PostedFile.InputStream);
object v = Xls.GetCellValue(1, 1);
if (v == null)
{
LabelA1.Text = "Cell A1 is empty";
}
else
{
LabelA1.Text = "Cell A1 has the value: " + Convert.ToString(v);
}
}
catch (Exception ex)
{
LabelA1.Text = ex.Message;
}
}
public static DataTable LerTitulosAba(XlsFile excel)
{
DataTable tabela = new DataTable();
int qtdeColunas = excel.ColCountOnlyData;
for (int cont = 1; cont <= qtdeColunas; cont++)
{
Object coluna = excel.GetCellValue(1, cont);
if (coluna != null)
{
try
{
tabela.Columns.Add(coluna.ToString());
}
catch (DuplicateNameException)
{
string nomeArquivo = excel.ActiveFileName.Split('\\')[excel.ActiveFileName.Split('\\').Length - 1],
aba = excel.ActiveSheetByName;
throw new Exception("O arquivo \"" + nomeArquivo + "\"" + ", na aba \"" + aba + "\", está com o nome de coluna \"" + coluna.ToString() + "\" duplicada. \n\n Por favor renomeie a coluna e salve o arquivo antes de prosseguir.");
}
}
}
return tabela;
}
public static ToaDo getPositionTableFlexExcel(XlsFile Result, string TextFil, int Sheet = 1)
{
Result.ActiveSheet = Sheet;//we'll read sheet1. We could loop over the existing sheets by using xls.SheetCount and xls.ActiveSheet
for (int row = 1; row <= Result.RowCount; row++)
{
for (int colIndex = 1; colIndex <= Result.GetColCount(Sheet, false); colIndex++) //Don't use xls.ColCount as it is slow: See Performance.Pdf
{
try
{
var valueCell = Result.GetCellValue(row, colIndex);
if (valueCell != null)
{
var dataString = valueCell.ToString();
if (!string.IsNullOrEmpty(dataString))
{
if (dataString.ToUpper().IndexOf(TextFil.ToUpper()) >= 0)//bat key ton tai table vidu: <#ChiTiet.TENDICHVU>
{
return(new ToaDo()
{
X = row, Y = colIndex
});
}
}
}
}
catch (Exception)
{
}
}
}
return(new ToaDo()
{
X = 0, Y = 0
});
}
/// <summary>
/// Constructs a reader for an Excel spreadsheet.
/// </summary>
/// <param name="filename"></param>
public XlsImportFile(string filename)
{
_xls = new XlsFile(filename);
// Get the field names from the first row.
for (int c = 1; c <= _xls.ColCount; c++)
{
_fieldNames.Add(_xls.GetCellValue(1, c)?.ToString());
}
}
private ImportRecord getRecord(int r)
{
List <object> vals = new List <object>();
for (int c = 1; c <= _xls.ColCount; c++)
{
object v = _xls.GetCellValue(r, c);
vals.Add(v);
}
return(new ImportRecord(_fieldNames, vals));
}
private int GetLastRow(XlsFile xls)
{
int row = 0;
while (true)
{
string val = Convert.ToString(xls.GetCellValue(++row, 1));
if (string.IsNullOrEmpty(val))
{
return(row);
}
}
}
/// <summary>
/// Obtener el contenido de una celda
/// </summary>
/// <param name="pRowNumber"></param>
/// <param name="pColumnNumber"></param>
/// <param name="pSheetName"></param>
/// <returns></returns>
public string GetValueFromColAndRow(int pRowNumber, int pColumnNumber, string pSheetName = null)
{
try
{
if (!string.IsNullOrWhiteSpace(pSheetName))
{
XlsFile.ActiveSheetByName = pSheetName; // Seleccionar la hoja de trabajo
}
object vValue = XlsFile.GetCellValue(pRowNumber, pColumnNumber); // Obtener el contenido de la celda
return(vValue != null?vValue.ToString() : string.Empty); // Si se obtuvo el contenido de la celda, se devuelve en formato string
}
catch (Exception vE)
{
throw vE;
}
}
string GetCellOrFormula(int row)
{
object cell = xls.GetCellValue(row, 1);
if (cell == null)
{
return("");
}
TFormula fmla = (cell as TFormula);
if (fmla != null)
{
return(fmla.Text);
}
return(Convert.ToString(cell));
}
private double ReadDoubleName(string Name)
{
TXlsCellRange Range = Xls.GetNamedRange(Name, 0);
if (Range == null)
{
throw new Exception("There is no range named " + Name + " in the template");
}
object val = Xls.GetCellValue(Range.Top, Range.Left);
if (!(val is Double))
{
throw new Exception("The range named " + Name + " does not contain a number");
}
return((Double)val);
}
Dictionary <string, string> GetFailTCDefects(XlsFile xls)
{
Dictionary <string, string> defects = new Dictionary <string, string>();
xls.ActiveSheetByName = "FailTC";
int lastRow = xls.GetRowCount(xls.ActiveSheet);
for (var i = 1; i <= lastRow; i++)
{
string val = Convert.ToString(xls.GetCellValue(i, 1));
if (!string.IsNullOrEmpty(val) && !defects.ContainsKey(val))
{
defects.Add(val, "Yes");
}
}
return(defects);
}
private int FindRow(XlsFile xls, string cq)
{
int row = 0;
while (true)
{
string val = Convert.ToString(xls.GetCellValue(++row, 1));
if (string.IsNullOrEmpty(val))
{
break;
}
if (cq.Equals(val))
{
return(row);
}
}
return(-1);
}
private void CreateFilesAndRecalculate()
{
//Set up the files.
XlsFile xls1 = new XlsFile();
xls1.NewFile();
xls1.SetCellValue(1, 1, GetValue(CellA1.Text));
xls1.SetCellValue(2, 1, new TFormula("=[Third File.xls]Sheet1!A1 + 7"));
XlsFile xls2 = new XlsFile();
xls2.NewFile();
xls2.SetCellValue(1, 1, new TFormula("=[First File.xls]Sheet1!A1 * 2"));
XlsFile xls3 = new XlsFile();
xls3.NewFile();
xls3.SetCellValue(1, 1, new TFormula("=[Second File.xls]Sheet1!A1 * 5"));
//Create a workspace to recalculate them.
//In this case, as we know what files we need in advance, we will just add them to the workspace
//For an example on how to load files on demand, take a look at the chart example in this demo.
TWorkspace Workspace = new TWorkspace();
Workspace.Add("First File.xls", xls1);
Workspace.Add("Second File.xls", xls2);
Workspace.Add("Third File.xls", xls3);
//Now that the workspace is set, we can recalculate. We could recalc() in the Workspace object or in any of the files in it.
//The effect is the same, all files will be recalculated.
//DO NOT RECALCULATE EVERY FILE. EACH TIME YOU CALCULATE ONE, YOU ARE CALCULATING THEM ALL.
xls1.Recalc();
//Ok, now it is time to show the results.
Cell2.Text = Convert.ToString(((TFormula)xls2.GetCellValue(1, 1)).Result);
Cell3.Text = Convert.ToString(((TFormula)xls3.GetCellValue(1, 1)).Result);
Cell4.Text = Convert.ToString(((TFormula)xls1.GetCellValue(2, 1)).Result);
//In this example both the workspace and the xls files are local objects, so we don't need to worry about memory
//If any of them is a global object, remember that keeping a reference to it will keep a reference to *ALL* the
//files in the workspace (even if you make Workspace = null). You might want to call Workspace.Clear() in that case before setting it to null.
}
private void OpenFile()
{
try
{
if (openXls.FileName.Length == 0)
{
return;
}
XlsFile xls = new XlsFile();
xls.Open(openXls.FileName);
xls.ActiveSheetByName = ReportTag.StrOpen + ReportTag.StrConfigSheet + ReportTag.StrClose;
TreeNode[] FormatCellNode = new TreeNode[1];
FillBasicListView(FormatCellNode);
string XsdName = Convert.ToString(xls.GetCellValue(RowXsd, ColXsd));
if (string.IsNullOrEmpty(XsdName))
{
return;
}
using (DataSet ds = new DataSet())
{
{
XsdName = Path.Combine(Path.GetDirectoryName(openXls.FileName), XsdName);
}
OpenXsd(XsdName, ds);
OpenConfig(xls, ds, FormatCellNode);
}
}
catch (Exception e)
{
MessageBox.Show(e.Message);
}
}
public void UpdateFailTCDefects(XlsFile xls)
{
//XlsFile xls = new XlsFile(fileName);
Dictionary <string, string> defects = GetFailTCDefects(xls);
xls.ActiveSheetByName = "Defects";
int row = 1;//skip header row
while (true)
{
string val = Convert.ToString(xls.GetCellValue(++row, 1));
if (string.IsNullOrEmpty(val))
{
break;
}
if (defects.ContainsKey(val))
{
//column R=18(from 1)
xls.SetCellValue(row, 18, "Yes");
}
}
//SaveToFile(xls, fileName);
}
private void AnalizeFile(string FileName, int Row, int Col)
{
XlsFile xls = new XlsFile();
xls.Open(FileName);
int XF = 0;
MessageBox.Show("Active sheet is \"" + xls.ActiveSheetByName + "\"");
object v = xls.GetCellValue(Row, Col, ref XF);
if (v == null)
{
MessageBox.Show("Cell A1 is empty");
return;
}
//Here we have all the kind of objects FlexCel can return.
switch (Type.GetTypeCode(v.GetType()))
{
case TypeCode.Boolean:
MessageBox.Show("Cell A1 is a boolean: " + (bool)v);
return;
case TypeCode.Double: //Remember, dates are doubles with date format.
TUIColor CellColor = Color.Empty;
bool HasDate, HasTime;
String CellValue = TFlxNumberFormat.FormatValue(v, xls.GetFormat(XF).Format, ref CellColor, xls, out HasDate, out HasTime).ToString();
if (HasDate || HasTime)
{
MessageBox.Show("Cell A1 is a DateTime value: " + FlxDateTime.FromOADate((double)v, xls.OptionsDates1904).ToString() + "\n" +
"The value is displayed as: " + CellValue);
}
else
{
MessageBox.Show("Cell A1 is a double: " + (double)v + "\n" +
"The value is displayed as: " + CellValue + "\n");
}
return;
case TypeCode.String:
MessageBox.Show("Cell A1 is a string: " + v.ToString());
return;
}
TFormula Fmla = v as TFormula;
if (Fmla != null)
{
MessageBox.Show("Cell A1 is a formula: " + Fmla.Text + " Value: " + Convert.ToString(Fmla.Result));
return;
}
TRichString RSt = v as TRichString;
if (RSt != null)
{
MessageBox.Show("Cell A1 is a formatted string: " + RSt.Value);
return;
}
if (v is TFlxFormulaErrorValue)
{
MessageBox.Show("Cell A1 is an error: " + TFormulaMessages.ErrString((TFlxFormulaErrorValue)v));
return;
}
throw new Exception("Unexpected value on cell");
}
public void SaveExcel(string path, string hd_tally_sheet_id)
{
XlsFile myFile = new XlsFile(path);
myFile.ActiveSheet = 1;
int rowCount = myFile.RowCount;
int colCount = myFile.ColCount;
for (int i = 2; i <= rowCount; i++)
{
if (myFile.GetCellValue(i, 1) == null || myFile.GetCellValue(i, 2) == null || myFile.GetCellValue(i, 3) == null || myFile.GetCellValue(i, 4) == null)
{
continue;
}
Puntland_Port_Taxation.Tally_Sheet_Details tsheet = new Tally_Sheet_Details();
tsheet.tally_sheet_id = int.Parse(hd_tally_sheet_id);
tsheet.way_bill_code = myFile.GetCellValue(i, 1).ToString().Trim();
tsheet.importer_name = myFile.GetCellValue(i, 2).ToString().Trim();
tsheet.goods_name = myFile.GetCellValue(i, 3).ToString();
tsheet.total_quantity = Convert.ToInt32((myFile.GetCellValue(i, 4).ToString()));
tsheet.unit_of_measure = (myFile.GetCellValue(i, 5).ToString());
var damaged = (myFile.GetCellValue(i, 6).ToString());
damaged = damaged.ToLower();
if (damaged == "yes")
{
tsheet.is_damaged = true;
}
else
{
tsheet.is_damaged = false;
}
db.Tally_Sheet_Details.Add(tsheet);
db.SaveChanges();
}
}
public void GerarExcel(string Nome_Arquivo_Origem,string Nome_Arquivo_Destino, int CodLayout, Dados d, ProgressBar statusGeracaoArquivo)
{
// Criando Aplicação
XlsFile excelDestino = new XlsFile();
XlsFile excelOrigem = new XlsFile();
excelOrigem.Open(Nome_Arquivo_Origem);
int QtdeLinhas = excelOrigem.RowCount;
//Gerando arquivo de saída
for (int i = 1; i <= excelOrigem.SheetCount; i++)
{
excelOrigem.ActiveSheet = i;
excelDestino.NewFile(1);
//LEITURA DOS DADOS ABAS
int qtdeColunas = excelOrigem.ColCountOnlyData;
if (qtdeColunas > 0)
{
for (int cont = 1; cont <= qtdeColunas; cont++)
{
Object titulo = excelOrigem.GetCellValue(1, cont);
if (titulo != null)
{
string LetraColuna = d.PosicaoVinculada(CodLayout, titulo.ToString());
int numeroColuna;
try
{
numeroColuna = Convert.ToInt16(LetraColuna);
}
catch (Exception)
{
numeroColuna = LetrasParaNumero(LetraColuna);
}
if (LetraColuna != null)
{
excelDestino.InsertAndCopyRange(
new TXlsCellRange(1, cont, QtdeLinhas, cont),
1,
numeroColuna,
1,
TFlxInsertMode.NoneRight,
TRangeCopyMode.All,
excelOrigem,
i
);
}
}
}
int c = 0;
string novoNomeArquivo = Path.GetDirectoryName(Nome_Arquivo_Destino) + Path.DirectorySeparatorChar + Path.GetFileNameWithoutExtension(Nome_Arquivo_Destino) + "_" + excelOrigem.SheetName + Path.GetExtension(Nome_Arquivo_Destino);
while (File.Exists(novoNomeArquivo))
{
c++;
novoNomeArquivo = Path.GetDirectoryName(Nome_Arquivo_Destino) + Path.DirectorySeparatorChar + Path.GetFileNameWithoutExtension(Nome_Arquivo_Destino) + "_" + excelOrigem.SheetName + i + Path.GetExtension(Nome_Arquivo_Destino);
}
excelDestino.Save(novoNomeArquivo);
}
statusGeracaoArquivo.Value = statusGeracaoArquivo.Value + 1;
}
}
public int SaveExcel(string path)
{
XlsFile myFile = new XlsFile(path);
myFile.ActiveSheet = 1;
int rowCount = myFile.RowCount;
int colCount = myFile.ColCount;
//get from excel sheets
int from_currency_id_ = 0;
int to_currency_id_ = 0;
decimal newrate = 0;
string yyyymmdd = string.Empty;
int updated_record_count = 0;
for (int i = 2; i <= rowCount; i++)
{
//for (int j = 1; j <= colCount; j++)
//{
if (myFile.GetCellValue(i, 1) == null)
{
break;
}
from_currency_id_ = GetCurrencyId(myFile.GetCellValue(i, 1).ToString());
to_currency_id_ = GetCurrencyId(myFile.GetCellValue(i, 3).ToString());
newrate = CheckDecimal(myFile.GetCellValue(i, 5).ToString());
yyyymmdd = GetDateTime(myFile.GetCellValue(i, 6).ToString());
if (from_currency_id_.Equals(0) || to_currency_id_.Equals(0) || yyyymmdd.Equals("0") || newrate.Equals(0))
goto skipSave;
updated_record_count++;
//update the old data
var updateOld = from ord in db.Exchange_Rate
where ord.from_currency_id == from_currency_id_ && ord.to_currency_id == to_currency_id_ && ord.end_date == "9999/12/31"
select ord;
foreach (Exchange_Rate ord in updateOld)
{
ord.end_date = yyyymmdd;
}
db.SaveChanges();
//adding a new record
Exchange_Rate myDm = new Exchange_Rate();
myDm.from_currency_id = from_currency_id_;
myDm.to_currency_id = to_currency_id_;
myDm.month_id = 1;
myDm.exchange_rate1 = newrate;
myDm.start_date = yyyymmdd;
myDm.end_date = "9999/12/31";
db.Exchange_Rate.Add(myDm);
db.SaveChanges();
skipSave:
continue;
//}
}
return updated_record_count;
}
public void SaveExcel(string path, string hd_tally_sheet_id)
{
XlsFile myFile = new XlsFile(path);
myFile.ActiveSheet = 1;
int rowCount = myFile.RowCount;
int colCount = myFile.ColCount;
for (int i = 2; i <= rowCount; i++)
{
if (myFile.GetCellValue(i, 1) == null)
{
break;
}
Puntland_Port_Taxation.Tally_Sheet_Details tsheet = new Tally_Sheet_Details();
tsheet.tally_sheet_id = int.Parse(hd_tally_sheet_id);
tsheet.way_bill_code = myFile.GetCellValue(i, 1).ToString();
tsheet.goods_name = myFile.GetCellValue(i, 2).ToString();
tsheet.units = (myFile.GetCellValue(i, 3).ToString());
tsheet.quantity = myFile.GetCellValue(i, 4).ToString();
tsheet.unit_of_measure = (myFile.GetCellValue(i, 5).ToString());
tsheet.total_quantity = (myFile.GetCellValue(i, 6).ToString());
db.Tally_Sheet_Details.Add(tsheet);
db.SaveChanges();
}
}
private void CompareXls(XlsFile xls1, XlsFile xls2, DataTable table)
{
int DiffCount = 0;
xls1.Recalc();
for (int sheet = 1; sheet <= xls1.SheetCount; sheet++)
{
xls1.ActiveSheet = sheet;
xls2.ActiveSheet = sheet;
int aColCount = xls1.ColCount;
for (int r = 1; r <= xls1.RowCount; r++)
{
for (int c = 1; c <= aColCount; c++)
{
TFormula f = xls1.GetCellValue(r, c) as TFormula;
if (f != null)
{
TCellAddress ad = new TCellAddress(r, c);
TFormula f2 = (TFormula)xls2.GetCellValue(r, c);
if (f.Result == null)
{
f.Result = "";
}
if (f2.Result == null)
{
f2.Result = "";
}
double eps = 0;
if (f.Result is Double && f2.Result is Double)
{
if ((Double)f2.Result == 0)
{
if (Math.Abs((double)f.Result) < Double.Epsilon)
{
eps = 0;
}
else
{
eps = Double.NaN;
}
}
else
{
eps = (double)f.Result / (Double)f2.Result;
}
if (Math.Abs(eps - 1) < 0.001)
{
f.Result = f2.Result;
}
}
if (!f.Result.Equals(f2.Result))
{
if (table == null)
{
report.Text += "\nSheet:" + xls1.SheetName + " --- Cell:" + ad.CellRef + " --- Calculated: " + f.Result.ToString() + " Excel: " + f2.Result.ToString() + " dif: " + eps.ToString() + " formula: " + f.Text;
Application.DoEvents();
}
else
{
table.Rows.Add(new object[] { xls1.SheetName, ad.CellRef, f.Result.ToString(), f2.Result.ToString(), eps.ToString(), f.Text });
}
DiffCount++;
}
}
}
}
}
if (table == null)
{
report.Text += "\nFinished Comparing.";
if (DiffCount == 0)
{
report.Text += "\n**********No differences found!**********";
}
else
{
report.Text += String.Format("\n --->Found {0} differences", DiffCount);
}
}
}
/// <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++;
}
protected string ExtractString(int rowIndex, int columnIndex)
{
var val = Xls.GetCellValue(rowIndex, columnIndex);
return(val != null?val.ToString() : string.Empty);
}
