protected override void ParseLine(IXLRow row)
{
if (row.Cell(1).DataType == XLCellValues.Number)
{
int itemId = row.Cell(1).GetValue<int>();
var item = repository.Get<Item, int>(itemId);
if (item == null)
repository.Save<Item>(new Item
{
ItemId = itemId,
Name = row.Cell(2).GetValue<string>(),
Frame = 0,
Price = 0,
Type = row.Cell(4).GetValue<string>(),
CategoryName = row.Cell(3).GetValue<string>(),
Special = row.Cell(5).GetValue<string>(),
Upgrade = row.Cell(6).GetValue<string>(),
Premium = row.Cell(7).GetValue<string>()
});
else
{
item.Name = row.Cell(2).GetValue<string>();
item.Type = row.Cell(4).GetValue<string>();
item.CategoryName = row.Cell(3).GetValue<string>();
item.Special = row.Cell(5).GetValue<string>();
item.Upgrade = row.Cell(6).GetValue<string>();
item.Premium = row.Cell(7).GetValue<string>();
repository.Update<Item>(item);
}
}
}
private void AddDateCell(IXLRow row, int cellIdx, DateTime date)
{
var cell = row.Cell(cellIdx);
cell.SetValue(date).SetDataType(XLDataType.DateTime);
cell.Style.DateFormat.Format = "yyyy-mm-dd";
}
private static void FillCalculManqueDispo(IXLRow row, FestivArtsContext ctx, JourEvenement j)
{
IXLCell ce = row.Cell(1);
ce.Style.Font.Bold = true;
ce.Value = "Total manque/surplus dispo";
int i = 2;
foreach (CreneauDef cd in ctx.CreneauDefs.Include("Dispoes").Include("Creneaux").Where(c => c.JourId == j.Id).ToList())
{
int minBen = cd.Creneaux.Sum(cr => cr.NbBenevoleMin);
int maxBen = cd.Creneaux.Sum(cr => cr.NbBenevoleMax);
int dispo = cd.Dispoes.Where(d => d.EstDispo).Count();
IXLCell c = row.Cell(i++);
c.Value = dispo - minBen;
c.Style.Font.Bold = true;
if (dispo < minBen)
{
c.Style.Font.FontColor = XLColor.Red;
}
else if (dispo > maxBen)
{
c.Style.Font.FontColor = XLColor.DarkGreen;
}
else
{
c.Style.Font.FontColor = XLColor.DarkOrange;
}
}
}
private CalculationCase GetCaseFromRow(IXLRow row)
{
string caseString = row.Cell(colCase).GetString();
switch (caseString)
{
case "1":
return(CalculationCase.Case1);
case "2":
return(CalculationCase.Case2);
case "3a":
return(CalculationCase.Case3a);
case "3b":
return(CalculationCase.Case3b);
case "4a":
return(CalculationCase.Case4a);
case "4b":
return(CalculationCase.Case4b);
default:
return(CalculationCase.CaseNone);
}
}
public void TestRowCopyContents()
{
var workbook = new XLWorkbook();
IXLWorksheet originalSheet = workbook.Worksheets.Add("original");
IXLWorksheet copyRowSheet = workbook.Worksheets.Add("copy row");
IXLWorksheet copyRowAsRangeSheet = workbook.Worksheets.Add("copy row as range");
IXLWorksheet copyRangeSheet = workbook.Worksheets.Add("copy range");
originalSheet.Cell("A2").SetValue("test value");
originalSheet.Range("A2:E2").Merge();
{
IXLRange originalRange = originalSheet.Range("A2:E2");
IXLRange destinationRange = copyRangeSheet.Range("A2:E2");
originalRange.CopyTo(destinationRange);
}
CopyRowAsRange(originalSheet, 2, copyRowAsRangeSheet, 3);
{
IXLRow originalRow = originalSheet.Row(2);
IXLRow destinationRow = copyRowSheet.Row(2);
copyRowSheet.Cell("G2").Value = "must be removed after copy";
originalRow.CopyTo(destinationRow);
}
TestHelper.SaveWorkbook(workbook, @"Misc\CopyRowContents.xlsx");
}
public void ExportToExcel(DataTable resultTable, string fileLocation, string sheetName, string color = "", int numberOfLastRow = 0, int startingCellIndex = 1)
{
XLWorkbook Workbook = new XLWorkbook(fileLocation);
IXLWorksheet Worksheet = Workbook.Worksheet(sheetName);
//Gets the last used row
if (numberOfLastRow == 0)
{
numberOfLastRow = Worksheet.LastRowUsed().RowNumber();
}
//Defines the starting cell for appeding (Row , Column)
IXLCell CellForNewData = Worksheet.Cell(numberOfLastRow + 1, startingCellIndex);
if (!color.Equals(""))
{
for (int i = 0; i < resultTable.Rows.Count; i++)
{
IXLRow RowForNewData = Worksheet.Row(numberOfLastRow + 1 + i);
RowForNewData.Style.Font.FontColor = XLColor.Red;
}
}
//InsertData - the data from the DataTable without the Column names ; InsertTable - inserts the data with the Column names
CellForNewData.InsertData(resultTable);
Workbook.SaveAs(fileLocation);
}
private static void FillNewTache(IXLWorksheet sheet, Tache tache, JourEvenement jour, ref int line, Planning p, IEnumerable <Affectation> affectations, bool readableExport)
{
IXLCell c = sheet.Cell("A" + line);
c.Value = tache.Id;
c = sheet.Cell("B" + line);
c.Value = tache.Nom;
c.Style.Font.FontSize = 20;
c.Style.Font.Bold = true;
line++;
IXLRow r = sheet.Row(line);
int j = FIRST_PLAN_COLUMN;
foreach (var def in jour.CreneauDefs.OrderBy(s => s.NoCreneau))
{
c = r.Cell(j);
c.Value = "'" + def.Debut.ToString("HH:mm", CultureInfo.InvariantCulture);
j++;
}
line++;
int maxB = tache.GetMaxBenevoleByDay(jour.Id);
for (int i = 0; i < maxB; i++)
{
r = sheet.Row(line);
FIllNewRow(r, jour, tache, p, i == 0, i == maxB - 1, i, affectations, readableExport);
line++;
}
}
public static IEnumerable <string> GetStringArray(this IXLRow row)
{
foreach (var cell in row.Cells())
{
yield return(cell.Value.ToString());
}
}
private IXLRow SetSummary(IXLWorksheet worksheet, IXLRow row, IEnumerable <Item> items, IEnumerable <Rests> rests, IEnumerable <IGrouping <string, Model.Action> > actions)
{
var currentActions = actions.Where(x => items.Any(i => i.Code == x.Key)).SelectMany(x => x);
var totalLabelCell = row.Cell("O");
totalLabelCell.Value = "Итого";
var factNumberCell = row.Cell("Q");
factNumberCell.FormulaA1 = $"=SUM(Q{row.Cell("Q").Address.RowNumber - items.Count()}:Q{row.Cell("Q").Address.RowNumber - 1 })";
factNumberCell.Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
var factSumCell = worksheet.Range(row.Cell("R"), row.Cell("S")).Merge(false);
factSumCell.FormulaA1 = $"=SUM(R{row.Cell("R").Address.RowNumber - items.Count()}:R{row.Cell("R").Address.RowNumber - 1 })";
factSumCell.Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
var planNumberCell = row.Cell("T");
planNumberCell.Value = rests.Sum(x => x.Count);
planNumberCell.FormulaA1 = $"=SUM(T{row.Cell("T").Address.RowNumber - items.Count()}:T{row.Cell("T").Address.RowNumber - 1 })";
planNumberCell.Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
var planSumCell = worksheet.Range(row.Cell("U"), row.Cell("W")).Merge(false);
planSumCell.FormulaA1 = $"=SUM(U{row.Cell("U").Address.RowNumber - items.Count()}:U{row.Cell("U").Address.RowNumber - 1 })";
planSumCell.Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
return(row.RowBelow(2));
}
public void EditRowColor(string fileLocation, string sheetName, int startIndex, string date)
{
XLWorkbook Workbook = new XLWorkbook(fileLocation);
IXLWorksheet Worksheet = Workbook.Worksheet(sheetName);
int NumberOfLastRow = Worksheet.LastRowUsed().RowNumber();
for (int i = startIndex; i < NumberOfLastRow; i++)
{
var cellValue = (Worksheet.Cell(i, 4).Value).ToString();
if (cellValue.Equals(""))
{
break;
}
var validity = DateTime.ParseExact(cellValue, "dd.MM.yyyy", null);
var todayDate = DateTime.ParseExact(date, "dd.MM.yyyy", null);
IXLRow excelRow = Worksheet.Row(i);
if (DateTime.Compare(validity, todayDate) < 0)
{
excelRow.Style.Fill.BackgroundColor = XLColor.Red;
}
else if (DateTime.Compare(validity, todayDate) == 0)
{
excelRow.Style.Fill.BackgroundColor = XLColor.Yellow;
}
}
Workbook.SaveAs(fileLocation);
}
protected bool ParseTimeStamp(IXLRow row, IXLCell timeStampCell, out long?value)
{
value = null;
if (!string.IsNullOrWhiteSpace(timeStampCell.GetString()))
{
try
{
value = long.Parse(timeStampCell.RichText.Text, CultureInfo.InvariantCulture);
}
catch (FormatException exception)
{
Logger.Error($"Unable to parse '{timeStampCell.GetString()}' in row {row.RowNumber()} as Int64", exception);
return(false);
}
catch (ArgumentException exception)
{
Logger.Error($"Unable to parse '{timeStampCell.GetString()}' in row {row.RowNumber()} as Int64", exception);
return(false);
}
catch (OverflowException exception)
{
Logger.Error($"Unable to parse '{timeStampCell.GetString()}' in row {row.RowNumber()} as Int64", exception);
return(false);
}
}
return(true);
}
public virtual List <PropertyMapParser> GetDefaultPropertyMapParsers <T>(IXLRow headerRow) where T : class
{
var result = new List <PropertyMapParser>();
var headerCells = headerRow.Cells(
firstColumn: headerRow.FirstCellUsed().WorksheetColumn().ColumnNumber(),
lastColumn: headerRow.LastCellUsed().WorksheetColumn().ColumnNumber());
foreach (var p in typeof(T).GetProperties(BindingFlags.Instance | BindingFlags.Public).Where(p => p.CanWrite && p.CanRead))
{
var cell = headerCells.SingleOrDefault(
c => String.Compare(
strA: c.GetString(),
strB: this.GetColumnName(p),
comparisonType: StringComparison.InvariantCultureIgnoreCase) == 0);
if (cell != null)
{
var mapping = new PropertyMapParser {
ObjectPropertyInfo = p, ExcelColumnIndex = cell.WorksheetColumn().ColumnNumber(), TryGetProperty = this.TryParseProperty
};
result.Add(mapping);
}
else
{
throw new Exception($"Excel did not provide required column: {p.Name}");
}
}
return(result);
}
Rolle LeseRolle(IXLRow row)
{
var datum = row.Cell(colVeranstaltungsDatum).GetValue <string>();
if (datum.ToLower() == "datum")
{
return(null);
}
var name = row.Cell(colRollenName).GetValue <string>();
if (string.IsNullOrWhiteSpace(name))
{
return(null);
}
var tmp = name.Split(' ');
if (tmp.Length > 1)
{
if (tmp[1] == "/")
{
return(new Rolle($"{tmp[0]}/{tmp[2]}"));
}
return(new Rolle(tmp[0]));
}
return(new Rolle(name));
}
public Floodlight(IXLRow row)
{
this.Source = row.Cell(2).GetValue<string>().ToLower().Trim();
this.Type = row.Cell(3).GetValue<string>().ToLower().Trim();
this.Category = new FloodlightProperty(row);
this.Session = row.Cell(7).GetValue<string>().ToLower().Trim();
}
public DataTable ImportToDataTable <tt>(FileInfo fileInfo, Dictionary <string, string> columnMapping = null) where tt : class
{
var file = new XLWorkbook(fileInfo.FullName);
var sheet = file.Worksheets.Worksheet(0);
var tp = typeof(tt);
var item = (tt)tp.Assembly.CreateInstance(tp.FullName, true);
IXLRow headerLine = null;
var headerLineNumber = 0;
// Dim propList = item.GetPropertyNames()
var cnt = 1;
var fnd = false;
while ((headerLineNumber == 0 && cnt <= sheet.Rows().Count()) && !fnd)
{
var line = sheet.Row(cnt); // file.Lines(cnt)
foreach (var col in line.Cells())
{
if ((item.DoesPropertyExist(col.Value.ToString().Trim()) || (columnMapping != null && columnMapping.ContainsKey(col.Value.ToString().Trim()))))
{
headerLine = line;
headerLineNumber = cnt;
fnd = true;
}
}
cnt += 1;
}
Debug.WriteLine("Header Line Number = " + headerLineNumber);
var table = ToDataTable(sheet, headerLine: headerLineNumber);
return(table);
}
private void WritingLog(IReadOnlyDayWorkLog Logs, IXLRow row)
{
var cell = row.Cell((int)Columns.詳細);
cell.Style.NumberFormat.Format = ";;;ログ";
cell.Value = Logs.ToString();
}
private void CreateProgramsForAgency(SpaceAgencies newAgency, IXLRow row)
{
int indexOfFirstProgramField = 6;
try
{
SpacePrograms program;
var existingPrograms = (from programs in _context.SpacePrograms
where programs.Title.Contains(row.Cell(indexOfFirstProgramField).Value.ToString())
select programs).ToList();
if (existingPrograms.Count > 0)
{
program = existingPrograms[0];
}
else
{
program = CreateProgram(row, indexOfFirstProgramField);
States state = _context.States.FirstOrDefault(s => s.StateName == row.Cell(indexOfFirstProgramField + 3)
.Value.ToString());
_context.SpacePrograms.Add(program);
AddNewProgramAndStatePairToContext(program, state);
AddNewAgencyAndProgramPairToContext(newAgency, program);
}
}
catch (Exception ex)
{
}
}
private bool ParseRow(IXLRow row, out string error)
{
var word = row.Cell(WORD_IND).Value.ToString();
var lang = row.Cell(LANG_IND).Value.ToString();
var cat = row.Cell(CAT_IND).Value.ToString();
var translation = row.Cell(TRAN_IND).Value.ToString();
var model = new WordData {
Word = word, Language = lang, Category = cat, Translation = translation
};
if (IsRowErronous(model, out error))
{
return(false);
}
CreateNewLangAndCatIfNeeded(model);
_helper.CreateWord(model, out int wordId);
_helper.CreateCatWord(model, wordId, out int catWordId);
_helper.CreateTranslations(model, catWordId, out bool commaError);
if (!_helper.ValidateComma(commaError, wordId))
{
error = ERR_TRAN + ERR_END;
return(false);
}
return(true);
}
private void AgregarHistoricoKilometrajeDTO(IXLRow dataRow, DateTime?fecha, ref List <HistoricoKilometrajeDTO> listaResultado)
{
string vinRAW = dataRow.Cell(listaColumnasGuardar["VIN"]).Value.ToString().Trim();
string vin = Regex.Replace(vinRAW, "[^a-zA-Z0-9]", "").Replace("-", "").Trim();
if (vin.Length < 12)
{
return;
}
int kilometros = dataRow.Cell(listaColumnasGuardar["Odometer"]).GetValue <int>();
double horasSerial = dataRow.Cell(listaColumnasGuardar["Hrs"]).GetValue <double>();
DateTime fechaSerialInicio = DateTime.FromOADate(0);
DateTime fechaSerial = DateTime.FromOADate(horasSerial);
TimeSpan timeSpan = fechaSerial - fechaSerialInicio;
HistoricoKilometrajeDTO item = new HistoricoKilometrajeDTO()
{
VIN = vin,
Fecha = fecha ?? DateTime.Now,
Kilometros = kilometros,
Horas = Convert.ToInt32((timeSpan.TotalMinutes - (timeSpan.TotalMinutes % 60)) / 60),
Minutos = fechaSerial.Minute % 60,
CreationUser = "******"
};
lock (listaResultado) listaResultado.Add(item);
}
public static UpdateRecord ToUpdateRecord(this IXLRow row, string header, System.Collections.Generic.IDictionary <string, string> comments)
{
var name = row.GetValue <string>("A");
var health = row.GetValue <string>("C");
var teams = row.GetValue <string>("D");
var pm = row.GetValue <string>("E");
var leadEng = row.GetValue <string>("F");
var exitOriginalStr = row.GetValue <string>("I");
var exitCurrentStr = row.GetValue <string>("J");
var id = row.GetValue <string>("L");
if (name == "Name" && health == "Health" && teams == "Teams")
{
return(null);
}
if (string.IsNullOrEmpty(name))
{
return(null);
}
comments.TryGetValue(id, out string comment);
return(new UpdateRecord
{
Id = id,
Name = name,
Health = health,
Comment = comment,
Header = header,
ProductManager = pm,
LeadEng = leadEng,
ExitDateCurrent = exitCurrentStr.TryToDateTime(),
ExitDateOriginal = exitOriginalStr.TryToDateTime(),
});
}
private dynamic[] GetRowData(IXLRow row, List <ColumnDefine> columns)
{
var data = new dynamic[columns.Count];
var isEmpty = true;
foreach (var column in columns)
{
var value = row.Cell(column.Index)?.Value?.ToString()?.Trim();
if (!string.IsNullOrWhiteSpace(value))
{
isEmpty = false;
}
object result = value;
if (column.DataType == "DateTime")
{
if (value == "0000-00-00 00:00:00" || string.IsNullOrWhiteSpace(value))
{
result = DateTime.MinValue;
}
else
{
result = DateTime.TryParse(value, out var time) ? time : DateTime.MinValue;
}
}
data[column.Index - 1] = result;
}
return(isEmpty ? null : data);
}
private Street ParseStreet(IXLRow row, Street street)
{
var str = (from st in _context.Streets
where st.StreetName.Contains(row.Cell(nameNumberDictionary[STREET_NAME_STRING]).Value.ToString())
select st).ToList();
//если такая улица есть
if (str.Count > 0) //запишем её в адрес, но всё равно нужен номер
{
street = str[0];
}
else //если улицы с таким именем нету, то надо создать улицу
{
//запишем её имя
street.StreetName = row.Cell(nameNumberDictionary[STREET_NAME_STRING]).Value.ToString();
//создадим город
Town town = new Town();
//проверим формат колонок города
if (!CheckTownColumnsFormat(row))
{
throw new Exception("Town columns not in a correct format!");
}
//проверим существует ли указанный город
town = ParseTown(row, town);
//запишем город в лицу и добавим улицу
street.Town = town;
_context.Streets.Add(street);
}
return(street);
}
private void ProcessarColunas(DataTable dataTable, IXLRow linha)
{
foreach (var item in linha.CellsUsed())
{
dataTable.Rows[dataTable.Rows.Count - 1][item.Address.ColumnNumber - 1] = item.Value.ToString();
}
}
private void WritingDay(IReadOnlyDayWorkLog Logs, IXLRow row)
{
var cell = row.Cell((int)Columns.日付);
cell.Style.NumberFormat.Format = Format.Day2;
cell.Value = Logs.GetStartTime()?.ToString(Format.Day2) ?? "-----";
}
private void WritingBreakTime(IReadOnlyDayWorkLog Logs, IXLRow row)
{
var cell = row.Cell((int)Columns.休憩時間);
cell.Style.NumberFormat.Format = Format.ExcelTimeSpan;
cell.Value = Logs.GetBreakTime().ToString(Format.TimeSpan);
}
public void InsertingRowsPreservesFormatting()
{
var wb = new XLWorkbook();
IXLWorksheet ws = wb.Worksheets.Add("Sheet");
IXLRow row1 = ws.Row(1);
row1.Style.Fill.SetBackgroundColor(XLColor.FrenchLilac);
row1.Cell(2).Style.Fill.SetBackgroundColor(XLColor.Fulvous);
IXLRow row2 = ws.Row(2);
row2.Style.Fill.SetBackgroundColor(XLColor.Xanadu);
row2.Cell(2).Style.Fill.SetBackgroundColor(XLColor.MacaroniAndCheese);
row1.InsertRowsBelow(1);
row1.InsertRowsAbove(1);
row2.InsertRowsAbove(1);
Assert.AreEqual(ws.Style.Fill.BackgroundColor, ws.Row(1).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FrenchLilac, ws.Row(2).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FrenchLilac, ws.Row(3).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FrenchLilac, ws.Row(4).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.Xanadu, ws.Row(5).Style.Fill.BackgroundColor);
Assert.AreEqual(ws.Style.Fill.BackgroundColor, ws.Cell(1, 2).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.Fulvous, ws.Cell(2, 2).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.Fulvous, ws.Cell(3, 2).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.Fulvous, ws.Cell(4, 2).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.MacaroniAndCheese, ws.Cell(5, 2).Style.Fill.BackgroundColor);
}
/// <summary>
/// Gets all the misc attributes that are not in the database.
/// </summary>
/// <param name="currentRow"></param>
/// <param name="neededAttributes"></param>
/// <returns>A CSV formatted string of the misc attributes not tracked.</returns>
public string GetMiscAttributes(IXLRow currentRow, List <string> neededAttributes)
{
string miscAttributes = String.Empty;
List <string> headers = RowToList(GetColumnHeaders());
string columnLetter = String.Empty;
string tempHeader;
string tempattribute;
foreach (string header in headers.ToList())
{
tempHeader = header.Replace(" ", String.Empty);
foreach (string attribute in neededAttributes)
{
tempattribute = attribute.Replace(" ", String.Empty);
if (tempHeader.Equals(tempattribute, StringComparison.InvariantCultureIgnoreCase))
{
headers.Remove(header);
}
}
}
foreach (string header in headers)
{
columnLetter = FindColumnHeader(header);
miscAttributes += $"{header},{currentRow.Cell(columnLetter).Value.ToString()},";
}
return(miscAttributes);
}
private LopHP GetLopHP(int?IDNamHoc, IXLRow row)
{
try
{
var smh = row.Cell("C").Value.ToString();
var mh = db.MonHocs.FirstOrDefault(q => q.TenMonHoc.Trim().ToLower() == smh.Trim().ToLower());
if (mh == null)
{
return(null);
}
var res = new LopHP
{
IDNamHoc = IDNamHoc,
Active = true,
SoTietTKB = int.Parse(row.Cell("F").Value.ToString()),
Kip = row.Cell("H").Value.ToString(),
DiaDiem = row.Cell("K").Value.ToString(),
TenLop = row.Cell("J").Value.ToString(),
MaHocPhan = row.Cell("B").Value.ToString(),
SiSo = int.Parse(row.Cell("I").Value.ToString()),
};
res.MaMH = mh.MaMH;
DateTime dateTime = DateTime.Now;
if (DateTime.TryParseExact(row.Cell("P").Value.ToString(), new string[] { "dd/MM/yyyy", "dd/M/yyyy" }, System.Globalization.CultureInfo.InvariantCulture, System.Globalization.DateTimeStyles.None, out dateTime))
{
res.TGTHIKT = dateTime;
}
res.IDNamHoc = IDNamHoc;
return(res);
}
catch (Exception ex)
{
return(null);
}
}
private void WritingEndTime(IReadOnlyDayWorkLog Logs, IXLRow row)
{
var cell = row.Cell((int)Columns.退勤);
cell.Style.NumberFormat.Format = Format.Time;
cell.Value = Logs.GetEndTime()?.ToString() ?? "-----";
}
public static void Set_AllBorder(IXLRow iXLRow)
{
foreach (var cell in iXLRow.Cells())
{
cell.Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
//cell.Style.Border.RightBorder = XLBorderStyleValues.Thin;
}
}
public DataSheetRow(IXLRow row)
{
_row = row ?? throw new ArgumentNullException(nameof(row));
_getCellByColumnNumber = row.Cell;
_getCellByColumnLetter = row.Cell;
_getRowNumber = row.RowNumber;
_hideRow = row.Hide;
}
private void WrtingDayOfWeak(IXLRow row)
{
var Day = GetCellAdress(Columns.日付, row);
var cell = row.Cell((int)Columns.曜日);
cell.FormulaA1 = $@"=TEXT({Day},""aaa"")";
}
public FloodlightProperty(IXLRow row)
{
this.DefaultValue = row.Cell(6).GetValue<string>().ToLower().Trim();
this.VariableName = row.Cell(4).GetValue<string>().ToLower().Trim();
if (!string.IsNullOrEmpty(this.VariableName)) {
this.Items = new Dictionary<string, string>();
this.Items.Add(
row.Cell(5).GetValue<string>().ToLower().Trim(),
row.Cell(6).GetValue<string>().ToLower().Trim()
);
}
}
private static void SetData(IXLRow row, Letter data)
{
row.Cell(1).SetValue(data.Number).SetDataType(XLCellValues.Number);
row.Cell(2).SetValue(data.CreationDate).SetDataType(XLCellValues.DateTime);
row.Cell(3).SetValue(data.EventDate).SetDataType(XLCellValues.DateTime);
row.Cell(4).SetValue(data.LetterDate).SetDataType(XLCellValues.DateTime);
row.Cell(5).SetValue(data.Provider).SetDataType(XLCellValues.Text);
row.Cell(6).SetValue(data.Type).SetDataType(XLCellValues.Text);
row.Cell(7).SetValue(data.From).SetDataType(XLCellValues.Text);
row.Cell(8).SetValue(data.To).SetDataType(XLCellValues.Text);
row.Cell(9).SetValue(data.Money).SetDataType(XLCellValues.Number).Style.NumberFormat.SetFormat("0.00");
row.Cell(10).SetValue(data.Money2).SetDataType(XLCellValues.Number).Style.NumberFormat.SetFormat("0.00");
row.Cell(11).SetValue(data.User).SetDataType(XLCellValues.Text);
row.Cell(12).SetValue(data.Inc).SetDataType(XLCellValues.Number);
}
public Omniture(IXLRow row)
{
this.ParentId = row.Cell(2).GetValue<string>().ToLower().Trim();
this.Domain = row.Cell(3).GetValue<string>().ToLower().Trim();
this.SiteSection = row.Cell(4).GetValue<string>().ToLower().Trim();
this.SubSection = row.Cell(5).GetValue<string>().ToLower().Trim();
this.ActiveState = row.Cell(6).GetValue<string>().ToLower().Trim();
this.ObjectValue = row.Cell(7).GetValue<string>().ToLower().Trim();
this.ObjectDescription = row.Cell(8).GetValue<string>().ToLower().Trim();
this.CallToAction = row.Cell(9).GetValue<string>().ToLower().Trim();
this.Events = row.Cell(11).GetValue<string>().ToLower().Trim();
this.Skip = string.Empty;
this.Exceptions = new Dictionary<string, string>();
}
//public CreateLine ToCreateLine(DateTime timestamp, BudgetId budgetId, string userId, IEnumerable<MyBudget.Projections.Category> categories)
//{
// var category = Categoria.Trim().Replace((char)160, ' ');
// var categoryId = categories.FirstOrDefault(d => string.Compare(d.Name, category, true) == 0).Id;
// var expense = new Expense(new Amount(Currencies.Euro(), Spesa), Data, categoryId, Descrizione, DistributionKey);
// return new CreateLine
// {
// Id = Guid.NewGuid(),
// Timestamp = timestamp,
// BudgetId = budgetId.ToString(),
// LineId = LineId.Create(budgetId).ToString(),
// UserId = userId,
// Expense = expense,
// };
//}
public static Movimento TryParse(IXLRow row)
{
try
{
var data = (DateTime)row.Cell("A").Value;
var categoria = (string)row.Cell("B").Value;
var descrizione = (string)row.Cell("C").Value;
var spesa = Convert.ToDecimal(row.Cell("D").Value);
return new Movimento
{
Data = data,
Categoria = categoria,
Descrizione = descrizione,
Spesa = spesa,
};
}
catch
{
return null;
}
}
protected override void ParseLine(IXLRow row)
{
DateTime date;
if (row.Cell(1).DataType == XLCellValues.Number)
{
date = DateTime.FromOADate(row.Cell(1).GetDouble());
var facebookInsigh = repository.Get<FacebookInsights, DateTime>(date);
if (facebookInsigh == null)
repository.Save<FacebookInsights>(new FacebookInsights
{
Date = date,
DailyActiveUsers = row.Cell(2).GetValue<int>(),
MonthlyActiveUsers = row.Cell(4).GetValue<int>(),
DailyAppInstalls = row.Cell(5).GetValue<int>()
});
else
{
facebookInsigh.DailyActiveUsers = row.Cell(2).GetValue<int>();
facebookInsigh.MonthlyActiveUsers = row.Cell(4).GetValue<int>();
facebookInsigh.DailyAppInstalls = row.Cell(5).GetValue<int>();
}
}
}
private static void SetHeader(IXLRow row)
{
row.Cell(1).SetValue("#").SetDataType(XLCellValues.Text);
row.Cell(2).SetValue("შექმნის თარიღი").SetDataType(XLCellValues.Text);
row.Cell(3).SetValue("მოვლენის თარიღი").SetDataType(XLCellValues.Text);
row.Cell(4).SetValue("წერილის თარიღი").SetDataType(XLCellValues.Text);
row.Cell(5).SetValue("პროვაიდერი").SetDataType(XLCellValues.Text);
row.Cell(6).SetValue("ტიპი").SetDataType(XLCellValues.Text);
row.Cell(7).SetValue("საიდან").SetDataType(XLCellValues.Text);
row.Cell(8).SetValue("სად").SetDataType(XLCellValues.Text);
row.Cell(9).SetValue("ათვისებული თანხა").SetDataType(XLCellValues.Text);
row.Cell(10).SetValue("დასაბრუნებელი თანხა").SetDataType(XLCellValues.Text);
row.Cell(11).SetValue("მომხმარებელი").SetDataType(XLCellValues.Text);
row.Cell(12).SetValue("ინციდენტი").SetDataType(XLCellValues.Text);
}
private static string BuildSkipList(IXLRow row, int lastColumnUsed)
{
StringBuilder skipList = new StringBuilder();
for (int cellIndex = 12; cellIndex <= lastColumnUsed - 2; cellIndex++) {
if (row.Cell(cellIndex).GetValue<string>() == string.Empty) {
if (skipList.Length > 0) {
skipList.Append(",");
}
skipList.Append(cellIndex - 11);
}
}
return skipList.ToString();
}
public XLSRow(List<string> columns, IXLRow baseRow)
{
this.Columns = columns;
this.BaseRow = baseRow;
}
IXLRow InsertRow(IXLRow rowBefore, int startColumn, int endColumn, int fontSize)
{
IXLRow newRow = rowBefore.InsertRowsBelow(1).Last();
newRow.Cells(startColumn, endColumn).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
newRow.Cells(startColumn, endColumn).Style.Border.InsideBorder = XLBorderStyleValues.Thin;
newRow.Cells(startColumn, endColumn).Style.Alignment.WrapText = true;
newRow.Cells(startColumn, endColumn).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
newRow.Cells(startColumn, endColumn).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
newRow.Cells(startColumn, endColumn).Style.Font.FontName = "Calibri";
newRow.Cells(startColumn, endColumn).Style.Font.FontSize = fontSize;
return newRow;
}
internal Row(IXLRow row)
{
_row = row;
_currentCell = new Cell(_row.Cell(_currentColNum));
}
public void ExpandDescription(IXLWorksheet ws, IXLRow row)
{
ws.Range(row.Cell((int)CustomsInvoiceHeaders.Description), row.Cell((int)CustomsInvoiceHeaders.Amount)).Merge();
}
public IXLRow CopyTo(IXLRow row)
{
row.Clear();
var newRow = (XLRow)row;
newRow._height = _height;
newRow.Style = GetStyle();
using (var asRange = AsRange())
asRange.CopyTo(row).Dispose();
return newRow;
}
protected virtual void ParseLine(IXLRow row)
{
}
