public static void Execute(DownloadServiceOptions options)
{
using (var workbook = new XLWorkbook(options.File))
using (var worksheet = workbook.Worksheet(1))
{
var fullRange = worksheet.RangeUsed();
var rangeUsed = worksheet.Range(2, 1, fullRange.RangeAddress.LastAddress.RowNumber, fullRange.RangeAddress.LastAddress.ColumnNumber);
const int movieNameCell = 1;
const int yearCell = 2;
const int processedCell = 3;
const int downloadedCell = 4;
var fullCount = 0;
foreach (var row in rangeUsed.RowsUsed())
{
if (fullCount > 100) break;
// must have 4 cells, and the processed cell must not contain an x
if (row.CellCount() == 4 && !row.Cell(processedCell).GetValue<string>().Equals("x"))
{
var urls = CreateUrl(options.BaseUrl, row.Cell(movieNameCell).GetValue<string>(), row.Cell(yearCell).GetValue<string>());
var downloaded = false;
foreach (var url in urls)
{
Thread.Sleep(1000 * 30); // wait 30 seconds
var hash = GetTorrentMagnetHash(PullSource(url));
if (string.IsNullOrEmpty(hash)) continue;
var torrent = string.Format("http://torcache.net/torrent/{0}.torrent", hash);
var result = DownloadTorrent(torrent, options.DownloadPath);
if (!result) continue;
downloaded = true;
break;
}
row.Cell(processedCell).SetValue("x");
if (downloaded)
row.Cell(downloadedCell).SetValue("x");
fullCount++;
}
}
workbook.Save();
}
}
static Boolean file_processing(string file_patch, string file_name)
{
string par = "\\";
if (file_name == "argument_check")
{
par = "";
file_name = "";
}
var wb = new XLWorkbook(file_patch + par + file_name);
var ws = wb.Worksheets.Worksheet(1);
ws.PageSetup.Margins.Top = 0.208;
ws.PageSetup.Margins.Bottom = 0.208;
ws.PageSetup.Margins.Left = 0.416;
ws.PageSetup.Margins.Right = 0.208;
ws.PageSetup.Margins.Footer = 0.333;
ws.PageSetup.Margins.Header = 0.333;
wb.SaveAs(file_patch + par + file_name);
if (ws.Cell(16, 1).Value.ToString() == "Инвентаризационная опись")
{
WriteLineColor("Файл " + file_name + " уже конвертирован!", "Red");
Log.log_write("Файл " + file_name + " уже конвертирован!", "WARNING", "warning");
already_con++;
return false;
}
if (ws.Cell(2, 1).Value.ToString() != "Инвентаризационная опись")
{
WriteLineColor("Файл " + file_name + " формат не подходит!", "Red");
Log.log_write("Файл " + file_name + " формат не подходит!", "ERROR", "warning");
failed++;
return false;
}
if (backup_data(file_patch, file_name))
{
WriteLineColor("Создана копия оригинального файла.", "Magenta");
}
else
{
WriteLineColor("Внимание копия файла не сделана!", "Red");
Console.ReadKey();
}
WriteLineColor("Обработка...", "Cyan");
//Удаляем ненужные колонки
ws.Column(9).Delete();
ws.Range("A1:I5").Delete(XLShiftDeletedCells.ShiftCellsLeft);
ws.Range("A7:I11").Delete(XLShiftDeletedCells.ShiftCellsLeft);
ws.Range("A11:I15").Delete(XLShiftDeletedCells.ShiftCellsLeft);
ws.Range("A15:I17").Delete(XLShiftDeletedCells.ShiftCellsLeft);
//Прячем ненужные строки
int i = 1;
while (i <= 15)
{
ws.Row(i).Hide();
i++;
}
//Объединение ячеек
ws.Range("A16:G16").Row(1).Merge();
ws.Range("A17:G17").Row(1).Merge();
ws.Cell(16, 1).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
ws.Cell(16, 1).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
ws.Cell(16, 1).Value = "Инвентаризационная опись № " + file_name.ToLower().Replace("c", "").Replace("с", "").Replace("_", "").Replace(".xlsx", "");
ws.Cell(17, 1).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
ws.Cell(17, 1).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
ws.Cell(17, 1).Value = "товарно-материальных ценностей";
int total_cell = 16;
int total_coll = 1;
while (total_coll <= 7)
{
total_cell = 16;
while (ws.Cell(total_cell, 1).GetString() != "")
{
ws.Cell(total_cell, total_coll).Style.Font.FontName = "Arial";
ws.Cell(total_cell, total_coll).Style.Font.FontSize = 11;
total_cell++;
}
total_coll++;
}
int cel1 = total_cell + 1;
int cel3 = total_cell + 3;
//Добавление в конец файла строк
ws.Cell(cel1, 1).Value = "Материально-ответственное(ые) лицо(а) :";
ws.Cell(cel1, 1).Style.Font.FontName = "Arial";
ws.Cell(cel1, 1).Style.Font.FontSize = 11;
ws.Range("A" + cel1 + ":G"+ cel1).Row(1).Merge();
ws.Cell(cel3, 1).Value = "Начальник комиссии :";
ws.Cell(cel3, 1).Style.Font.FontName = "Arial";
ws.Cell(cel3, 1).Style.Font.FontSize = 11;
ws.Range("A" + cel3 + ":G" + cel3).Row(1).Merge();
//ширина колонок штрихкод,наименование,сумма.
ws.Column(2).Width = 14;
ws.Column(3).Width = 70;
ws.Column(4).Width = 7;
ws.Column(7).Width = 10;
// ws.Column(2).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
// ws.Column(2).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
//
// ws.Column(3).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
// ws.Column(3).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
//
// ws.Column(7).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right;
// ws.Column(7).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
//считаем количество страниц на печать
int pages = (total_cell+4) / 35;
//минимальное количество страниц отправляемых на печать.
if (pages == 0)
pages = 1;
total_page += pages;
//устанавливаем параметры страниц печати (количество страниц в ширину,количество страниц в высоту)
ws.PageSetup.FitToPages(1, pages);
WriteLineColor("Всего строк: " + total_cell.ToString() + " Всего колонок: " + total_coll.ToString() + " Всего страниц на печать: " + pages.ToString(), "Cyan");
wb.Save();
return true;
}
// Public
public void Create(String filePath)
{
#region Create case
{
var workbook = new XLWorkbook();
var ws = workbook.Worksheets.Add("Delete red rows");
// Put a value in a few cells
foreach (var r in Enumerable.Range(1, 5))
foreach (var c in Enumerable.Range(1, 5))
ws.Cell(r, c).Value = string.Format("R{0}C{1}", r, c);
var blueRow = ws.Rows(1, 2);
var redRow = ws.Row(5);
blueRow.Style.Fill.BackgroundColor = XLColor.Blue;
redRow.Style.Fill.BackgroundColor = XLColor.Red;
workbook.SaveAs(filePath);
}
#endregion
#region Remove rows
{
var workbook = new XLWorkbook(filePath);
var ws = workbook.Worksheets.Worksheet("Delete red rows");
ws.Rows(1, 2).Delete();
workbook.Save();
}
#endregion
}
public void Run(string fileSpese, string fileAnalisi, string template)
{
var numeroDiAnniFinoAdOggi = DateTime.Today.Year - 2011;
var anni = Enumerable.Range(2011, numeroDiAnniFinoAdOggi + 1);
File.Copy(template, fileAnalisi, true);
using (var analisiWb = new XLWorkbook(fileAnalisi))
{
var dati = analisiWb.Worksheet("Dati");
int riga = 2;
using (var spese = new XLWorkbook(fileSpese))
{
foreach (var anno in anni)
{
var wsAnno = spese.Worksheet(anno + "");
var movimenti = wsAnno.Rows().Select(Movimento.TryParse).Where(r => r != null).ToArray();
foreach (var movimento in movimenti)
{
dati.Cell(riga, "A").Value = movimento.Data;
dati.Cell(riga, "B").Value = movimento.Categoria;
dati.Cell(riga, "C").Value = movimento.Descrizione;
dati.Cell(riga, "D").Value = movimento.Spesa;
riga++;
}
}
var dataAsTable = dati.RangeUsed().AsTable();
dati.Tables.Add(dataAsTable);
//var range = dati.Range("B1", "B30");
//var pivotSh = analisiWb.Worksheet("Pivot");
//pivotSh.PivotTable("PivotTable1").SetRefreshDataOnOpen(true);
}
analisiWb.Save();
}
}
static void Main(string[] args)
{
// nema17 - 24v datasheet
var MOTOR_TYPE = "nema17";
var MOTOR_VOLTAGE = ElectricPotentialDc.FromVoltsDc(24);
var SPEED_MAX = RotationalSpeed.FromRevolutionsPerMinute(200);
var TORQUE_MAX = Torque.FromNewtonMeters(0.4);
// problem data
var MASS = Mass.FromKilograms(2);
var LEVER_ARM_LEN = Length.FromCentimeters(1);
var ROUND_CNT = Angle.FromRevolutions(1d);
var EXECUTION_TIME = Duration.FromSeconds(2);
// config
var TIME_STEP = Duration.FromMilliseconds(1);
// speed required to achieve given ROUND_CNT in EXECUTION_TIME without cruise
var minTargetSpeed = 2 * ROUND_CNT / EXECUTION_TIME;
if (minTargetSpeed > SPEED_MAX)
{
System.Console.WriteLine($"W: given position {ROUND_CNT} round cannot established due to speed_max:{SPEED_MAX.RevolutionsPerSecond} rps vs actual required target speed:{minTargetSpeed.RevolutionsPerSecond} rps");
return;
}
// s:target speed
var s = minTargetSpeed;
// d:duration
var d = EXECUTION_TIME;
var minHoldingTorque = Torque.FromKilogramForceCentimeters(MASS.Kilograms * LEVER_ARM_LEN.Centimeters);
if (minHoldingTorque > TORQUE_MAX)
{
System.Console.WriteLine($"W: given mass {MASS} at lever arm distance {LEVER_ARM_LEN} generate {minHoldingTorque.KilogramForceCentimeters} kgfcm torque versus max {TORQUE_MAX.KilogramForceCentimeters} kgfcm");
return;
}
var minDynAccel = RotationalAcceleration.FromRevolutionsPerSecondSquared(4 * s.RevolutionsPerSecond / d.Seconds);
var I = MassMomentOfInertia.FromKilogramSquareCentimeters(MASS.Kilograms * Pow(LEVER_ARM_LEN.Centimeters, 2));
// torque = inertia * angularaccel
// F:[M*L*T-2]*r:[L] = I:[M*L2]*a:[T-2]
// [M*L2*T-2] = [M*L2*T-2]
var minDynTorque = Torque.FromKilogramForceMeters(I.KilogramSquareMeters * minDynAccel.RadiansPerSecondSquared);
if (minDynTorque > TORQUE_MAX)
{
System.Console.WriteLine($"W: accelerating given mass {MASS} at angaccel {minDynAccel} generates torque {minDynTorque.NewtonCentimeters} Ncm great than max {TORQUE_MAX.NewtonCentimeters} Ncm");
return;
}
var srcPathfilename = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "template.xlsx");
var dstPathfilename = "output.xlsx";
File.Copy(srcPathfilename, dstPathfilename, true);
using (var wb = new ClosedXML.Excel.XLWorkbook(dstPathfilename))
{
var ws = wb.Worksheets.First();
IXLCell cell = null;
Action <int, int, object> setCell = (r, c, val) =>
{
cell = ws.Cell(r, c);
cell.Value = val;
};
Action <int, int, object> setCellBold = (r, c, val) =>
{
cell = ws.Cell(r, c);
cell.Value = val;
cell.Style.Font.SetBold();
};
var row = 1;
int col = 1;
var colTime = col++;
var colAccel = col++;
var colSpeed = col++;
var colPosRot = col++;
setCellBold(row, colTime, "TIME (s)");
setCellBold(row, colAccel, "ACCEL (rps2)");
setCellBold(row, colSpeed, "SPEED (rps)");
setCellBold(row, colPosRot, "POS (rot)");
++row;
var t = Duration.FromSeconds(0);
var tMax = t + EXECUTION_TIME;
var halfTMax = tMax / 2;
ws.Cell("MotorType").Value = MOTOR_TYPE;
ws.Cell("MotorSpeedMax").Value = SPEED_MAX;
ws.Cell("MotorTorqueMaxAtSpeedMax").Value = TORQUE_MAX;
ws.Cell("MotorVoltage").Value = MOTOR_VOLTAGE;
ws.Cell("ProblemDuration").Value = EXECUTION_TIME;
ws.Cell("ProblemLoadLeverArmLength").Value = LEVER_ARM_LEN;
ws.Cell("ProblemLoadMass").Value = MASS;
ws.Cell("ProblemRevolutions").Value = ROUND_CNT;
ws.Cell("ResultingTorque").Value = minDynTorque.ToUnit(TorqueUnit.NewtonMeter);
ws.Cell("ResultingAccel").Value = minDynAccel;
ws.Cell("ResultingSpeedMax").Value = minTargetSpeed.ToUnit(RotationalSpeedUnit.RevolutionPerMinute);
var tEps = Duration.FromNanoseconds(1);
while (t.LessThanOrEqualsTol(tEps, tMax))
{
setCell(row, colTime, t.Seconds);
var accel = RotationalAcceleration.FromRevolutionsPerSecondSquared(0);
var speed = RotationalSpeed.FromRevolutionsPerSecond(0);
var pos = Angle.FromRevolutions(0);
if (t.LessThanOrEqualsTol(tEps, halfTMax))
{
accel = RotationalAcceleration.FromRevolutionsPerSecondSquared(
2 * s.RevolutionsPerSecond / d.Seconds * (1 - Cos(4 * PI * t / d)));
speed = RotationalSpeed.FromRevolutionsPerSecond(
2 * s.RevolutionsPerSecond / d.Seconds * (t - d * Sin(4 * PI * t / d) / (4 * PI)).Seconds);
pos = s * d * (Cos(4 * PI * t / d) - 1) / (8 * Pow(PI, 2)) + (s * t) * (t / d);
}
if (t.GreatThanOrEqualsTol(tEps, halfTMax))
{
var th = t - d / 2;
accel = RotationalAcceleration.FromRevolutionsPerSecondSquared(
2 * s.RevolutionsPerSecond / d.Seconds * (Cos(4 * PI * th / d) - 1));
speed = RotationalSpeed.FromRevolutionsPerSecond(
2 * s.RevolutionsPerSecond * Sin(4 * PI * th / d) / (4 * PI)
- (2 * s * th / d - s).RevolutionsPerSecond);
pos = s * d * (1 - Cos(4 * PI * th / d)) / (8 * Pow(PI, 2)) - (s * th) * (th / d) + s * th + s * d / 4;
}
setCell(row, colAccel, accel.RevolutionsPerSecondSquared);
setCell(row, colSpeed, speed.RevolutionsPerSecond);
setCell(row, colPosRot, pos.Revolutions);
++row;
t += TIME_STEP;
}
wb.Save();
}
}
public static void UpdateCompetitionArchive(string excelFileName, Competition c)
{
try
{
XLWorkbook xLWorkbook = new XLWorkbook(excelFileName);
CreateScheduleStylesheet(c, xLWorkbook, true);
xLWorkbook.Save();
}
catch (Exception ex)
{
if (ex.Message.Contains("being used"))
MessageBox.Show("Zatvori arhivu pa pokusaj opet!");
else
MessageBox.Show("Greska!");
}
}
/// <summary>
/// 엑셀 오류메세지 쓰기
/// </summary>
/// <param name="filePath"></param>
/// <param name="errList"></param>
/// <returns></returns>
public bool WriteExcelByErrMsg(string filePath, IList<string> errList)
{
bool isSuccess = true;
try
{
XLWorkbook wb = new XLWorkbook(filePath);
IXLWorksheet worksheet = wb.Worksheets.First();
int colCount = worksheet.RangeUsed().ColumnCount();
for (int i = 0; i < errList.Count; i++)
{
worksheet.Cell(i + 1, colCount + 1).SetValue(errList[i]);
}
wb.Save();
}
catch (Exception ex)
{
isSuccess = false;
}
return isSuccess;
}
private void Export_Novy()
{
if (!InvokeRequired)
{
form.timer_ClearInfo.Stop();
}
else
{
this.BeginInvoke(new Action(() => form.timer_ClearInfo.Stop()));
}
form.infoBox.Text = form.jazyk.Message_Exportuji;
form.Update();
DateTime start;
DateTime konec;
over = false;
List <ExportRow> exportRadky = new List <ExportRow>();
int odec = 0;
if (DateTime.Now.AddDays(-(int)DateTime.Now.DayOfWeek).DayOfWeek == DayOfWeek.Sunday && (int)DateTime.Now.AddDays(-(int)DateTime.Now.DayOfWeek).DayOfWeek == 0)
{
odec = 1;
}
if (radioButton3.Checked)
{
start = dateTimePicker1.Value;
konec = dateTimePicker2.Value;
}
else if (radioButton1.Checked)
{
konec = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day);
//tohle zrevidovat, jestli by nestačilo jen to Add day v catch bez try
try
{
start = new DateTime(DateTime.Now.AddDays(-(int)DateTime.Now.DayOfWeek).Year, DateTime.Now.AddDays(-(int)DateTime.Now.DayOfWeek).Month, DateTime.Now.AddDays(-(int)DateTime.Now.DayOfWeek).Day + odec);
}
catch
{
start = new DateTime(DateTime.Now.AddDays(-(int)DateTime.Now.DayOfWeek).Year, DateTime.Now.AddDays(-(int)DateTime.Now.DayOfWeek).Month, DateTime.Now.AddDays(-(int)DateTime.Now.DayOfWeek).Day);
start = start.AddDays(odec);
}
}
else
{
start = DateTime.Now.AddDays(-7);
start = start.AddDays(-(int)(start.DayOfWeek - 1));
start = new DateTime(start.Year, start.Month, start.Day);
konec = new DateTime(start.Year, start.Month, start.Day).AddDays(6);
}
//Projít tickety splňující podmínku a zařadit je správně do exportu
for (DateTime d = start; d <= konec; d = d.AddDays(1))
{
if (form.poDnech.ContainsKey(d))
{
foreach (string s in form.poDnech[d].Keys)
{
foreach (Ticket t in form.poDnech[d][s])
{
DateTime pauzaDohromady = new DateTime();
DateTime cas = new DateTime();
DateTime hrubyCas = new DateTime();
for (int i = 0; i < t.PauzyDo.Count; i++)
{
if (t.PauzyDo[i].ToString("H:mm") != "0:00")
{
pauzaDohromady = pauzaDohromady.AddHours(t.PauzyDo[i].Hour - t.PauzyOd[i].Hour).AddMinutes(t.PauzyDo[i].Minute - t.PauzyOd[i].Minute);
}
}
try
{
cas = cas.AddHours((t.Do.Hour - t.Od.Hour) - pauzaDohromady.Hour).AddMinutes((t.Do.Minute - t.Od.Minute) - pauzaDohromady.Minute);
hrubyCas = form.RoundUp(cas, TimeSpan.FromMinutes(30));
//dny[d][t.TerpT].Add(t, hrubyCas);
string den = "";
switch (d.DayOfWeek)
{
case DayOfWeek.Monday: den = "Pondělí";
break;
case DayOfWeek.Tuesday: den = "Úterý";
break;
case DayOfWeek.Wednesday: den = "Středa";
break;
case DayOfWeek.Thursday: den = "Čtvrtek";
break;
case DayOfWeek.Friday: den = "Pátek";
break;
case DayOfWeek.Saturday: den = "Sobota";
break;
case DayOfWeek.Sunday: den = "Neděle";
break;
}
ExportTyp et;
if (t.TypPrace == 0 || t.TypPrace == 2 || t.TypPrace == 8 || t.TypPrace == 12 || t.TypPrace == 16 || t.TypPrace == 20 || t.TypPrace == 24)
{
et = ExportTyp.Normal;
}
else if (t.TypPrace == 1 || t.TypPrace == 6 || t.TypPrace == 10 || t.TypPrace == 15 || t.TypPrace == 18 || t.TypPrace == 23 || t.TypPrace == 27)
{
et = ExportTyp.Holiday;
}
else if (t.TypPrace == 3 || t.TypPrace == 5 || t.TypPrace == 9 || t.TypPrace == 13 || t.TypPrace == 17 || t.TypPrace == 21 || t.TypPrace == 25)
{
et = ExportTyp.Prescas;
}
else if (t.TypPrace == 4 || t.TypPrace == 7 || t.TypPrace == 11 || t.TypPrace == 14 || t.TypPrace == 19 || t.TypPrace == 22 || t.TypPrace == 26)
{
et = ExportTyp.Compens;
}
else
{
et = ExportTyp.Normal;
}
if (exportRadky.Count == 0)
{
exportRadky.Add(new ExportRow());
}
List <int> toSkip = new List <int>();
if (t.CustomTerp == "")
{
t.CustomTerp = Zakaznici.GetTerp(t.Zakaznik);
}
//když není task, tak defaultně incident 1.2.1 - nová verze by neměla umět uložit bez tasku
if (t.CustomTask == "")
{
t.CustomTask = Zakaznici.Terpy.Get <NbtCompound>("Task").Get <NbtString>("Incident").Value;
}
//přiřazení ticketu ke správnému řádku a dni
for (int i = 0; i < exportRadky.Count; i++)
{
if (exportRadky[i].Terp == null)
{
exportRadky[i].Terp = t.CustomTerp;
exportRadky[i].Task = t.CustomTask;
exportRadky[i].Typ = et;
exportRadky[i].Radek[den].Koment = t.ID + " " + t.Zakaznik + " " + t.Popis + "\r\n";
decimal tCas = hrubyCas.Hour;
if (hrubyCas.Minute == 30)
{
tCas += 0.5m;
}
exportRadky[i].Radek[den].Cas = tCas;
break;
}
else if (exportRadky[i].Terp == t.CustomTerp && exportRadky[i].Task == t.CustomTask && exportRadky[i].Typ == et)
{
if ((exportRadky[i].Radek[den].Koment.Length + (t.ID + " " + t.Zakaznik + " " + t.Popis + "\r\n").Length < 240))
{
exportRadky[i].Radek[den].Koment += t.ID + " " + t.Zakaznik + " " + t.Popis + "\r\n";
decimal tCas = hrubyCas.Hour;
if (hrubyCas.Minute == 30)
{
tCas += 0.5m;
}
exportRadky[i].Radek[den].Cas += tCas;
break;
}
else
{
exportRadky.Add(new ExportRow());
continue;
}
}
else if (i < exportRadky.Count - 1)
{
continue;
}
exportRadky.Add(new ExportRow());
}
}
catch
{
MessageBox.Show(form.jazyk.Windows_Export_Ticket + " " + t.ID + " - " + t.Zakaznik + ", " + form.jazyk.Windows_Export_NaKteremJsiPracoval + " " + t.Datum.ToString("d.MM.yyyy") + ", " + form.jazyk.Windows_Export_Neukoncen);
}
}
}
}
}
//přepočet času na 8h
//celkový čas normálních ticketů (statní se neupravují)
Dictionary <string, decimal> casy = new Dictionary <string, decimal> {
{ "Pondělí", 0 }, { "Úterý", 0 }, { "Středa", 0 }, { "Čtvrtek", 0 }, { "Pátek", 0 }, { "Sobota", 0 }, { "Neděle", 0 }
};
foreach (ExportRow s in exportRadky)
{
if (s.Typ == ExportTyp.Normal)
{
casy["Pondělí"] += s.Radek["Pondělí"].Cas;
casy["Úterý"] += s.Radek["Úterý"].Cas;
casy["Středa"] += s.Radek["Středa"].Cas;
casy["Čtvrtek"] += s.Radek["Čtvrtek"].Cas;
casy["Pátek"] += s.Radek["Pátek"].Cas;
casy["Sobota"] += s.Radek["Sobota"].Cas;
casy["Neděle"] += s.Radek["Neděle"].Cas;
}
}
//výběr řádků, co se upraví čas
foreach (string cs in casy.Keys)
{
if (casy[cs] == 8 || casy[cs] == 0)
{
continue;
}
else if (casy[cs] < 8)
{
Dictionary <int, decimal> pridat = new Dictionary <int, decimal>();
for (int i = 0; i < exportRadky.Count; i++)
{
if (exportRadky[i].Typ == ExportTyp.Normal && exportRadky[i].Radek[cs].Cas > 0)
{
pridat.Add(i, exportRadky[i].Radek[cs].Cas);
}
}
pridat = pridat.OrderBy(x => x.Value).ToDictionary(x => x.Key, x => x.Value);
decimal zbyva = 8 - casy[cs];
decimal prumerNaRadek = Math.Ceiling((zbyva / pridat.Count) / 0.5m) * 0.5m;
//úprava času
int index = 0;
for (decimal d = zbyva; d > 0; d -= prumerNaRadek)
{
if (d - prumerNaRadek < 0)
{
prumerNaRadek = d;
}
pridat[pridat.Keys.ElementAt(index)] += prumerNaRadek;
index++;
zbyva -= prumerNaRadek;
}
foreach (int newI in pridat.Keys)
{
exportRadky[newI].Radek[cs].Cas = pridat[newI];
}
}
else if (casy[cs] > 8)
{
Dictionary <int, decimal> ubrat = new Dictionary <int, decimal>();
for (int i = 0; i < exportRadky.Count; i++)
{
if (exportRadky[i].Typ == ExportTyp.Normal && exportRadky[i].Radek[cs].Cas > 0.5m)
{
ubrat.Add(i, exportRadky[i].Radek[cs].Cas);
}
}
ubrat = ubrat.OrderByDescending(x => x.Value).ToDictionary(x => x.Key, x => x.Value);
decimal zbyva = casy[cs] - 8;
decimal prumerNaRadek = Math.Ceiling((zbyva / ubrat.Count) / 0.5m) * 0.5m;
//úprava času
int index = 0;
for (decimal d = zbyva; d > 0; d -= prumerNaRadek)
{
if (d - prumerNaRadek < 0)
{
prumerNaRadek = d;
}
ubrat[ubrat.Keys.ElementAt(index)] -= prumerNaRadek;
index++;
zbyva -= prumerNaRadek;
}
foreach (int newI in ubrat.Keys)
{
exportRadky[newI].Radek[cs].Cas = ubrat[newI];
}
}
}
//export do souboru
//pro excel
File.WriteAllBytes(Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData) + "\\Ticketnik\\tmp_export.xlsx", Properties.Resources.mytime_template);
Excel.XLWorkbook export = new Excel.XLWorkbook(Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData) + "\\Ticketnik\\tmp_export.xlsx");
Excel.IXLWorksheet exportSheet = export.Worksheet(1);
NumberFormatInfo nfi = new NumberFormatInfo();
nfi.NumberDecimalSeparator = ".";
int row = 2;
foreach (ExportRow s in exportRadky)
{
if (s.Terp != null)
{
//project
exportSheet.Cell(row, 1).Value = s.Terp;
//project name
exportSheet.Cell(row, 2).Value = "najdiSiSam";
//task
exportSheet.Cell(row, 3).SetValue(s.Task);
//task name
exportSheet.Cell(row, 4).Value = "TyVisCo";
//type
exportSheet.Cell(row, 5).Value = s.GetTyp();
//pondělí (čas, comment)
exportSheet.Cell(row, 6).Value = s.Radek["Pondělí"].Cas.ToString() == "0" ? "" : s.Radek["Pondělí"].Cas.ToString(nfi);
exportSheet.Cell(row, 7).Value = s.Radek["Pondělí"].Koment.Replace("\t", " ").Replace("\"", "");
//úterý
exportSheet.Cell(row, 10).Value = s.Radek["Úterý"].Cas.ToString() == "0" ? "" : s.Radek["Úterý"].Cas.ToString(nfi);
exportSheet.Cell(row, 11).Value = s.Radek["Úterý"].Koment.Replace("\t", " ").Replace("\"", "");
//středa
exportSheet.Cell(row, 14).Value = s.Radek["Středa"].Cas.ToString() == "0" ? "" : s.Radek["Středa"].Cas.ToString(nfi);
exportSheet.Cell(row, 15).Value = s.Radek["Středa"].Koment.Replace("\t", " ").Replace("\"", "");
//čtvrtek
exportSheet.Cell(row, 18).Value = s.Radek["Čtvrtek"].Cas.ToString() == "0" ? "" : s.Radek["Čtvrtek"].Cas.ToString(nfi);
exportSheet.Cell(row, 19).Value = s.Radek["Čtvrtek"].Koment.Replace("\t", " ").Replace("\"", "");
//pátek
exportSheet.Cell(row, 22).Value = s.Radek["Pátek"].Cas.ToString() == "0" ? "" : s.Radek["Pátek"].Cas.ToString(nfi);
exportSheet.Cell(row, 23).Value = s.Radek["Pátek"].Koment.Replace("\t", " ").Replace("\"", "");
//sobota
exportSheet.Cell(row, 26).Value = s.Radek["Sobota"].Cas.ToString() == "0" ? "" : s.Radek["Sobota"].Cas.ToString(nfi);
exportSheet.Cell(row, 27).Value = s.Radek["Sobota"].Koment.Replace("\t", " ").Replace("\"", "");
//neděle
exportSheet.Cell(row, 30).Value = s.Radek["Neděle"].Cas.ToString() == "0" ? "" : s.Radek["Neděle"].Cas.ToString(nfi);
exportSheet.Cell(row, 31).Value = s.Radek["Neděle"].Koment.Replace("\t", " ").Replace("\"", "");
row++;
}
}
export.Save();
export.Dispose();
form.infoBox.Text = "";
saveFileDialog1.AddExtension = true;
saveFileDialog1.DefaultExt = "xlsx";
saveFileDialog1.Filter = "Excel|*.xlsx";
saveFileDialog1.FileName = "MyTime Info.xlsx";
saveFileDialog1.ShowDialog();
}
static Boolean clean_num(string file_patch, string file_name)
{
var wb = new XLWorkbook(file_patch + "\\" + file_name);
var ws = wb.Worksheets.Worksheet(1);
//считаем длину таблицы
int total_cell = 16;
while (ws.Cell(total_cell, 1).GetString() != "")
{
total_cell++;
}
//очистка всех числовых данных
ws.Range("C19:D"+(total_cell-1)).Column(2).Clear();
ws.Range("F19:G" + (total_cell - 1)).Column(2).Clear();
//обозначем пунктиром все поля
int start_cell = 19;
while (start_cell < total_cell )
{
ws.Cell(start_cell, 4).Style.Border.BottomBorder = XLBorderStyleValues.Hair;
ws.Cell(start_cell, 4).Style.Border.BottomBorderColor = XLColor.Black;
ws.Cell(start_cell, 7).Style.Border.LeftBorder = XLBorderStyleValues.Hair;
ws.Cell(start_cell, 7).Style.Border.RightBorder = XLBorderStyleValues.Hair;
ws.Cell(start_cell, 7).Style.Border.BottomBorder = XLBorderStyleValues.Hair ;
ws.Cell(start_cell, 7).Style.Border.BottomBorderColor = XLColor.Black;
start_cell++;
}
//очищаем линию в конце документа
int i =1;
while (i < 8)
{
ws.Cell(total_cell-1, i).Style.Border.BottomBorder = XLBorderStyleValues.Hair;
ws.Cell(total_cell-1, i).Style.Border.BottomBorderColor = XLColor.Black;
i++;
}
//очищяем конец документа для добавления доп.строк
i = 1;
while (i <= 9)
{
ws.Range("A" + (total_cell + i).ToString() + ":I" + (total_cell + i).ToString()).Delete(XLShiftDeletedCells.ShiftCellsLeft);
i++;
}
//добавление 10 дополнительных строк для неучтенных сразу строк
i = 0;
while (i <= 10)
{
int z = 1;
while (z < 8)
{
ws.Cell(total_cell + i, z).Style.Border.LeftBorder = XLBorderStyleValues.Hair;
ws.Cell(total_cell + i, z).Style.Border.RightBorder = XLBorderStyleValues.Hair;
ws.Cell(total_cell + i, z).Style.Border.BottomBorder = XLBorderStyleValues.Hair;
ws.Cell(total_cell + i, z).Style.Border.BottomBorderColor = XLColor.Black;
z++;
}
i++;
}
//Линия обозначающая конец акта
i = 1;
while (i < 8)
{
ws.Cell(total_cell + 11, i).Style.Border.TopBorder = XLBorderStyleValues.Medium;
ws.Cell(total_cell + 11, i).Style.Border.BottomBorderColor = XLColor.Black;
i++;
}
int first_insert = total_cell + 12;
ws.Cell(first_insert, 1).Value = "Всего наименований:";
ws.Cell(first_insert , 1).Style.Font.FontName = "Arial";
ws.Cell(first_insert , 1).Style.Font.FontSize = 11;
ws.Range("A" + first_insert + ":G" + first_insert).Row(1).Merge();
first_insert = total_cell + 14;
ws.Cell(first_insert, 1).Value = "Всего единиц товара:";
ws.Cell(first_insert, 1).Style.Font.FontName = "Arial";
ws.Cell(first_insert, 1).Style.Font.FontSize = 11;
ws.Range("A" + first_insert + ":G" + first_insert).Row(1).Merge();
first_insert = total_cell + 16;
ws.Cell(first_insert, 1).Value = "На сумму:";
ws.Cell(first_insert, 1).Style.Font.FontName = "Arial";
ws.Cell(first_insert, 1).Style.Font.FontSize = 11;
ws.Range("A" + first_insert + ":G" + first_insert).Row(1).Merge();
first_insert = total_cell + 18;
ws.Cell(first_insert, 1).Value = "Материально-ответственное(ые) лицо(а) :";
ws.Cell(first_insert, 1).Style.Font.FontName = "Arial";
ws.Cell(first_insert, 1).Style.Font.FontSize = 11;
ws.Range("A" + first_insert + ":G" + first_insert).Row(1).Merge();
first_insert = total_cell + 20;
ws.Cell(first_insert, 1).Value = "Начальник комиссии :";
ws.Cell(first_insert, 1).Style.Font.FontName = "Arial";
ws.Cell(first_insert, 1).Style.Font.FontSize = 11;
ws.Range("A" + first_insert + ":G" + first_insert).Row(1).Merge();
wb.Save();
return true;
}
public void _openExcel(String dashboardFile, String metricsFolder)
{
//grab all the files in the metrics folder
metrics_files = Directory.GetFiles(metricsFolder);
if (File.Exists("Copy.xlsx"))
{
File.Delete("Copy.xlsx");
}
File.Copy(dashboardFile, "Copy.xlsx");
//try openeing the excel sheets listed in metricsFile
try
{
_dashboard = new XLWorkbook(dashboardFile);
//iterate thorugh all the dashboard sheets, prep them for new daata by adding new rows, then extrract data from
//metrics reports and insert into those sheets
int numSheets = _dashboard.Worksheets.Count;
for (int sheetNum = 1; sheetNum < numSheets + 1; sheetNum++)
{
var sheet = _dashboard.Worksheet(sheetNum);
prepDashboardSheets(sheet);
}
_dashboard.Save();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
