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);
}
}
}
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));
}
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 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 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 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 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 static Titolo Parse(IXLRow row)
{
return(new Titolo
{
Simbolo = row.Cell("A").Value.ToString(),
Descrizione = row.Cell("B").Value.ToString()
});
}
public ActionResult ExportDataToExcel(DataTable dtExcelData, string sheetName, string fileNameWithoutExtension, bool totalRequired = true, bool passwordRequired = true)
{
using (XLWorkbook wb = new XLWorkbook())
{
sheetName = sheetName.Replace(" ", "_");
fileNameWithoutExtension = fileNameWithoutExtension.Replace(" ", "_");
dtExcelData.TableName = sheetName;
IXLWorksheet workSheet = wb.Worksheets.Add(dtExcelData);
IXLRow row = workSheet.Row(dtExcelData.Rows.Count + 1);
IXLRange range = workSheet.RangeUsed();
IXLTable xlTable = range.AsTable();
if (totalRequired)
{
string colLetterForSNo = GetExcelColumnLetter(xlTable, "#");
if (!string.IsNullOrEmpty(colLetterForSNo))
{
row.Cell(colLetterForSNo).Value = string.Empty;
}
string colLetterForDaysPresent = GetExcelColumnLetter(xlTable, "Days Present");
if (!string.IsNullOrEmpty(colLetterForDaysPresent))
{
row.Cell(colLetterForDaysPresent).Value = string.Empty;
}
}
wb.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
wb.Style.Font.Bold = true;
if (passwordRequired)
{
wb.Worksheet(sheetName).Protect("123456");
}
Response.Clear();
Response.Buffer = true;
Response.Charset = "";
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("content-disposition", "attachment;filename= " + fileNameWithoutExtension + ".xlsx");
using (MemoryStream MyMemoryStream = new MemoryStream())
{
wb.SaveAs(MyMemoryStream);
MyMemoryStream.WriteTo(Response.OutputStream);
Response.Flush();
Response.End();
}
return(null);
}
}
/// <summary>
/// Reads a value from the spreadsheet.
/// </summary>
/// <returns>The value that was read.</returns>
public PlannerTask Read()
{
while (!_currentRow.IsEmpty())
{
/*
* Percentage complete (0-100)
* todo: 0
* in progress: 50
* completed: 100
* Task priority (0-10)
* urgent: 0, 1*
* important: 2, 3*, 4
* medium: 5*, 6, 7
* low: 8, 9*, 10
*/
PlannerTask task;
try
{
var title = _currentRow.Cell(1).GetValue <string>();
var percentComplete = _currentRow.Cell(2).GetValue <int>();
var priority = _currentRow.Cell(3).GetValue <int>();
var orderHint = _currentRow.Cell(4).GetValue <string>();
var description = _currentRow.Cell(5).GetValue <string>();
task = new PlannerTask
{
Title = title,
PercentComplete = percentComplete,
Priority = priority,
OrderHint = orderHint,
TaskDetail = new TaskDetailResponse
{
Description = description,
Checklist = new Dictionary <string, Checklist>(),
References = new Dictionary <string, Reference>(),
},
};
}
catch
{
// Skip row
continue;
}
finally
{
GoToNextRow();
}
return(task);
}
// End of file
return(null);
}
Party GetParty(IXLRow row)
{
Party party = new Party
{
Id = row.Cell(1).GetValue <int>(),
NomenclatureId = row.Cell(2).GetValue <int>()
};
return(party);
}
MachineTool GetMachineTool(IXLRow row)
{
MachineTool tool = new MachineTool
{
Id = row.Cell(1).GetValue <int>(),
Name = row.Cell(2).GetValue <string>()
};
return(tool);
}
Nomenclature GetNomenclature(IXLRow row)
{
Nomenclature nomenclature = new Nomenclature
{
Id = row.Cell(1).GetValue <int>(),
NomenclatureName = row.Cell(2).GetValue <string>()
};
return(nomenclature);
}
private bool ParseRow(IXLRow row, out string error)
{
Publishers publisher = new Publishers();
string name = row.Cell(1).Value.ToString();
string earnings = row.Cell(2).Value.ToString();
string email = row.Cell(3).Value.ToString();
string regexName = @"^[А-ЯІЇЄа-яіїєA-Za-z'-'' ']*$";
string regexEarn = @"^[1-9][0-9]*$";
string regexCont = @"^(?("")(""[^""]+?""@)|(([0-9a-z]((\.(?!\.))|[-!#\$%&'\*\+/=\?\^`\{\}\|~\w])*)(?<=[0-9a-z])@))" +
@"(?(\[)(\[(\d{1,3}\.){3}\d{1,3}\])|(([0-9a-z][-\w]*[0-9a-z]*\.)+[a-z0-9]{2,17}))$";
if (Regex.IsMatch(name, regexName, RegexOptions.IgnoreCase))
{
bool dublicate = _context.Publishers.Any(l => l.Name.Equals(name));
if (dublicate)
{
error = "Наявна існуюча в базі назва";
return(false);
}
else
{
publisher.Name = name;
}
}
else
{
error = "У файлі наявне некоректна назва";
return(false);
}
if (Regex.IsMatch(earnings, regexEarn, RegexOptions.IgnoreCase))
{
publisher.Earnings = Convert.ToDouble(earnings);
}
else
{
error = "У файлі наявний некоректний дохід";
return(false);
}
if (Regex.IsMatch(email, regexCont, RegexOptions.IgnoreCase))
{
publisher.Contacts = email;
}
else
{
error = "У файлі вказана некоректна електронна адреса";
return(false);
}
_context.Publishers.Add(publisher);
_context.SaveChanges();
error = "";
return(true);
}
Time GetTime(IXLRow row)
{
Time time = new Time
{
MachineToolId = row.Cell(1).GetValue <int>(),
NomenclatureId = row.Cell(2).GetValue <int>(),
OperationTime = row.Cell(3).GetValue <int>()
};
return(time);
}
private void ParseProduct(IXLRow row, Product newProduct)
{
//check column formats
if (!CheckProductColumnsFormat(row))
{
throw new Exception("Product columns not in a correct format!");
}
//parse values
newProduct.ProductName = row.Cell(nameNumberDictionary[NAME_STRING]).Value.ToString();
newProduct.ProductPrice = double.Parse(row.Cell(nameNumberDictionary[PRICE_STRING]).Value.ToString());
newProduct.ProductRemainingQuantity = (int)double.Parse(row.Cell(nameNumberDictionary[QUANTITY_STRING]).Value.ToString());
}
private void WriteDefaultValue(IXLRow currentRow, ResourceItem resource)
{
string defaultValue;
if (!resource.Values.TryGetValue(DefaultCultureColumn, out defaultValue))
{
throw new Exception($"Resource default culture not found for {resource.Key.Project} {resource.Key.File} {resource.Key.Name}");
}
currentRow.Cell(_defaultCultureColumn.ColumnNumber()).SetDataType(XLCellValues.Text);
currentRow.Cell(_defaultCultureColumn.ColumnNumber()).SetValue(defaultValue);
}
public async Task <ActionResult> ProjectCreateWithFile(HttpPostedFileBase fileExcel)
{
var projectTypes = await DataStore.GetTypesAsync();
var projectsSkills = await DataStore.GetSkillsAsync();
using (XLWorkbook workBook = new XLWorkbook(fileExcel.InputStream, XLEventTracking.Disabled))
{
foreach (IXLWorksheet worksheet in workBook.Worksheets)
{
ProjectCreateViewModel project = new ProjectCreateViewModel();
project.Title = worksheet.Name;
foreach (IXLColumn column in worksheet.ColumnsUsed())
{
IXLRow firstRow = worksheet.RowsUsed().Skip(1).First();
project.Description = firstRow.Cell(1).Value.ToString();
project.Organization = firstRow.Cell(2).Value.ToString();
project.Start = (DateTime)firstRow.Cell(3).Value;
project.End = (DateTime)firstRow.Cell(4).Value;
project.ProjectTypeID = projectTypes.Where(w => w.Name == firstRow.Cell(5).Value.ToString()).ToList().FirstOrDefault()?.Id.ToString();
project.ProjectsSkills = new List <SkillViewModel>();
foreach (IXLRow row in worksheet.RowsUsed().Skip(1))
{
try
{
var skill = projectsSkills.Where(w => w.Name == row.Cell(6).Value.ToString()).ToList().FirstOrDefault()?.Id;
if (skill != null)
{
project.ProjectsSkills.Add(new SkillViewModel {
Id = (int)skill
});
}
}
catch (Exception e)
{
//logging
}
}
}
var data = Newtonsoft.Json.JsonConvert.SerializeObject(project);
if (data != null)
{
await DataStore.CreateProjectAsync(data);
}
}
}
var items = await DataStore.GetTypesAsync();
return(View("Index", items));
}
public void InsertingRowsAbove2()
{
var wb = new XLWorkbook();
IXLWorksheet ws = wb.Worksheets.Add("Sheet1");
ws.Rows("1,3").Style.Fill.SetBackgroundColor(XLColor.Red);
ws.Row(2).Style.Fill.SetBackgroundColor(XLColor.Yellow);
ws.Cell(2, 2).SetValue("X").Style.Fill.SetBackgroundColor(XLColor.Green);
IXLRow row1 = ws.Row(1);
IXLRow row2 = ws.Row(2);
IXLRow row3 = ws.Row(3);
IXLRow rowIns = ws.Row(2).InsertRowsAbove(1).First();
Assert.AreEqual(XLColor.Red, ws.Row(1).Cell(1).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.Red, ws.Row(1).Cell(2).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.Red, ws.Row(1).Cell(3).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.Red, ws.Row(2).Cell(1).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.Red, ws.Row(2).Cell(2).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.Red, ws.Row(2).Cell(3).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.Yellow, ws.Row(3).Cell(1).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.Green, ws.Row(3).Cell(2).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.Yellow, ws.Row(3).Cell(3).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.Red, ws.Row(4).Cell(1).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.Red, ws.Row(4).Cell(2).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.Red, ws.Row(4).Cell(3).Style.Fill.BackgroundColor);
Assert.AreEqual("X", ws.Row(3).Cell(2).GetString());
Assert.AreEqual(XLColor.Red, rowIns.Cell(1).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.Red, rowIns.Cell(2).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.Red, rowIns.Cell(3).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.Red, row1.Cell(1).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.Red, row1.Cell(2).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.Red, row1.Cell(3).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.Yellow, row2.Cell(1).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.Green, row2.Cell(2).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.Yellow, row2.Cell(3).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.Red, row3.Cell(1).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.Red, row3.Cell(2).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.Red, row3.Cell(3).Style.Fill.BackgroundColor);
Assert.AreEqual("X", row2.Cell(2).GetString());
}
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 bool CheckTownColumnsFormat(IXLRow row)
{
try
{
string townName = (string)row.Cell(nameNumberDictionary[TOWN_STRING]).Value;
int postCode = (int)double.Parse(row.Cell(nameNumberDictionary[PCODE_STRING]).Value.ToString());
return(true);
}
catch
{
return(false);
}
}
private async Task <AgentStartStops> GetAgentStartStopFromRowAsync(IXLWorksheet worksheet, int rowNumber, string agentNameColumn, string twelveAmColumn, ExcelCell phoneColorKeyCell)
{
AgentStartStops agentStartStop = new AgentStartStops();
List <int> phoneTimeColumns = new List <int>();
if (_log.IsDebugEnabled)
{
_log.Debug($"ExcelReader.GetAgentStartStopFromRowAsync - Creating row object from worksheet and rowNumber { rowNumber }");
}
IXLRow row = await Task.Run(() => worksheet.Row(rowNumber));
agentStartStop.AgentName = row.Cell(XLHelper.GetColumnNumberFromLetter(agentNameColumn)).Value.ToString();
if (_log.IsDebugEnabled)
{
_log.Debug($"ExcelReader.GetAgentStartStopFromRowAsync - Setting AgentName = { agentStartStop.AgentName }");
}
int twelveAmColumnInt = XLHelper.GetColumnNumberFromLetter(twelveAmColumn);
for (int i = twelveAmColumnInt; i <= twelveAmColumnInt + 23; i++)
{
if (row.Cell(i).Style.Fill.ToString() == await GetPhoneTimeCellFill(worksheet, phoneColorKeyCell))
{
if (_log.IsDebugEnabled)
{
_log.Debug($"ExcelReader.GetAgentStartStopFromRowAsync - Adding {i} to phoneTimeColumns List<int>");
}
phoneTimeColumns.Add(i);
}
}
List <Task <StartStop> > tasks = new List <Task <StartStop> >();
foreach (int column in phoneTimeColumns)
{
tasks.Add(GetStartStopByCellPositionAsync(column - twelveAmColumnInt));
}
StartStop[] results = await Task.WhenAll(tasks);
foreach (StartStop startStop in results)
{
if (_log.IsDebugEnabled)
{
_log.Debug($"ExcelReader.GetAgentStartStopFromRowAsync - Adding start:{ startStop.Start } and stop: { startStop.Stop } to agentStartStop.StartStopList");
}
agentStartStop.StartStopList.Add(startStop);
}
return(agentStartStop);
}
private bool CheckProductColumnsFormat(IXLRow row)
{
try
{
string productName = (string)row.Cell(nameNumberDictionary[NAME_STRING]).Value;
double productPrice = double.Parse(row.Cell(nameNumberDictionary[PCODE_STRING]).Value.ToString());
int remainingQuantity = (int)double.Parse(row.Cell(nameNumberDictionary[QUANTITY_STRING]).Value.ToString());
return(true);
}
catch
{
return(false);
}
}
private bool CheckAddressColumnsFormat(IXLRow row)
{
try
{
int streetNumber = (int)double.Parse(row.Cell(nameNumberDictionary[STREET_NUM_STRING]).Value.ToString());
string streetName = (string)row.Cell(nameNumberDictionary[STREET_NAME_STRING]).Value;
string notes = (string)row.Cell(nameNumberDictionary[NOTES_STRING]).Value;
return(true);
}
catch
{
return(false);
}
}
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);
}
}
public string GetValue(IXLRow row, int col)
{
string result = "";
try
{
result = row.Cell(col).HasFormula ? row.Cell(col).CachedValue.ToString().Trim() : row.Cell(col).Value.ToString().Trim();
}
catch (Exception)
{
result = "";
}
return(result);
}
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);
}
private void WritingLog(IReadOnlyDayWorkLog Logs, IXLRow row)
{
var cell = row.Cell((int)Columns.詳細);
cell.Style.NumberFormat.Format = ";;;ログ";
cell.Value = Logs.ToString();
}
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++;
}
}
/// <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 void WritingEndTime(IReadOnlyDayWorkLog Logs, IXLRow row)
{
var cell = row.Cell((int)Columns.退勤);
cell.Style.NumberFormat.Format = Format.Time;
cell.Value = Logs.GetEndTime()?.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) ?? "-----";
}
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);
}
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 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);
}
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);
}
public void ExpandDescription(IXLWorksheet ws, IXLRow row)
{
ws.Range(row.Cell((int)CustomsInvoiceHeaders.Description), row.Cell((int)CustomsInvoiceHeaders.Amount)).Merge();
}
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();
}
internal Row(IXLRow row)
{
_row = row;
_currentCell = new Cell(_row.Cell(_currentColNum));
}