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); } }