public void ColorCells(SLDocument document) { float value = 0; DateTime today_from = new DateTime(); today_from = DateTime.Today; DateTime current_date = new DateTime(today_from.Year, today_from.Month, today_from.Day, new GregorianCalendar()); float.TryParse(oreMacchina.Text, out value); // parso la stringa in un float, lo faccio per poter fare i calcoli var list = new List <string>(); SLWorksheetStatistics stats = document.GetWorksheetStatistics(); int endColumnIndex = stats.EndColumnIndex; // adesso faccio i calcoli per prima fase float result_1 = (value * storage_value_data_1) / 100; // risultato per le ore relative alla prima lavorazione TimeSpan timespan_1 = TimeSpan.FromHours(result_1); // con il timespan converto i decimali in ore // method which don't respect the DRY guide line SLConditionalFormatting cf; // I need this for color int i, j; for (i = today_from.Day; i < 5; ++i) // control and set the value of the cell { cf = new SLConditionalFormatting("B5", "H5"); // control the color of the cells for (j = 1; j < 7; ++j) // control and set the value of the cell { if (result_1 > 16) { cf = new SLConditionalFormatting("B5", "H5"); // control the color of the cells } document.SetCellValue(i, j, result_1); // set a custom color for cf.SetCustom3ColorScale(SLConditionalFormatMinMaxValues.Value, "0", SLThemeColorIndexValues.Accent1Color, 0.2, SLConditionalFormatRangeValues.Percentile, "35", SLThemeColorIndexValues.Accent3Color, -0.1, SLConditionalFormatMinMaxValues.Value, "0", SLThemeColorIndexValues.Accent6Color, 0.5); document.AddConditionalFormatting(cf); } } foreach (var sheetName in document.GetWorksheetNames()) { // with this for I read data as string in the second row of the excel sheet for (int z = 1; z <= endColumnIndex; z++) { list.Add(document.GetCellValueAsString(3, z)); } // NON FUNZIONA, NON VIENE INSERITA LA DATA ODIERNA NELLA CELLA D4 var print_current_day_test_1_cell = document.SetCellValue("D4", current_date.Day + "/" + current_date.Month + "/" + current_date.Year); // se metto diverso funziona ma dovrebbe funzionare con l'uguale uguale if (document.GetCellValueAsDateTime("2") != document.GetCellValueAsDateTime("D4")) // !!!!!!!!!!!!!!!!!!!!!! scorta di punti esclamativi { // works with the dateTime if was stored in a cell but if I try to get the cell value and compare it with DateTime.Today or current_date it doesn't work MessageBox.Show("Ho trovato la data odierna"); for (int y = 3; y <= 500; y++) { if (document.GetCellValueAsInt32("A" + y) == 0) { document.SetCellValue("A" + y, y); } } document.SetCellValue("G4", "Sono riuscito a trovare la data odierna"); } } }
public SLDocument MakeEXCEL(List <DocType> dts) { int i = 1; SLDocument sl = new SLDocument(); sl.RenameWorksheet(SLDocument.DefaultFirstSheetName, "工作表1"); SLConditionalFormatting cf = new SLConditionalFormatting("C2", "C" + (dts.Count + 1).ToString()); cf.HighlightCellsWithDuplicates(SLHighlightCellsStyleValues.LightRedFill); cf.HighlightCellsEqual(true, "-1", SLHighlightCellsStyleValues.LightRedFill); sl.AddConditionalFormatting(cf); cf = new SLConditionalFormatting("B2", "B" + (dts.Count + 1).ToString()); cf.HighlightCellsWithDuplicates(SLHighlightCellsStyleValues.LightRedFill); cf.HighlightCellsEqual(true, "0", SLHighlightCellsStyleValues.LightRedFill); sl.AddConditionalFormatting(cf); cf = new SLConditionalFormatting("A2", "A" + (dts.Count + 1).ToString()); cf.HighlightCellsWithDuplicates(SLHighlightCellsStyleValues.LightRedFill); cf.HighlightCellsEqual(true, "0", SLHighlightCellsStyleValues.LightRedFill); sl.AddConditionalFormatting(cf); cf = new SLConditionalFormatting("J2", "J" + (dts.Count + 1).ToString()); cf.HighlightCellsWithFormula("=DATE(YEAR($J2),MONTH($J2)-1,DAY($J2)) <= TODAY()", SLHighlightCellsStyleValues.LightRedFill); sl.AddConditionalFormatting(cf); SLStyle style = sl.CreateStyle(); style.Alignment.WrapText = true; style.Alignment.Horizontal = HorizontalAlignmentValues.Center; style.Alignment.Vertical = VerticalAlignmentValues.Center; sl.SetCellStyle(1, 1, style); sl.SetCellStyle(1, 2, style); sl.SetCellStyle(1, 3, style); sl.SetCellStyle(1, 4, style); sl.SetCellStyle(1, 5, style); sl.SetCellStyle(1, 6, style); sl.SetCellStyle(1, 7, style); sl.SetCellStyle(1, 8, style); sl.SetCellStyle(1, 9, style); sl.SetCellStyle(1, 10, style); sl.SetCellStyle(1, 11, style); sl.SetCellStyle(1, 12, style); sl.SetCellStyle(1, 13, style); sl.SetColumnWidth(1, 10); sl.SetColumnWidth(2, 15); sl.SetColumnWidth(3, 10); sl.SetColumnWidth(4, 60); sl.SetColumnWidth(5, 10); sl.SetColumnWidth(6, 20); sl.SetColumnWidth(7, 15); sl.SetColumnWidth(8, 20); sl.SetColumnWidth(9, 20); sl.SetColumnWidth(10, 20); sl.SetColumnWidth(11, 10); sl.SetColumnWidth(12, 10); sl.SetColumnWidth(13, 50); sl.SetCellValue(1, 1, "表單序號"); sl.SetCellValue(1, 2, "表單代號"); sl.SetCellValue(1, 3, "網頁代碼"); sl.SetCellValue(1, 4, "表單名稱"); sl.SetCellValue(1, 5, "版本"); sl.SetCellValue(1, 6, "制訂單位"); sl.SetCellValue(1, 7, "文件類別"); sl.SetCellValue(1, 8, "首次公佈時間"); sl.SetCellValue(1, 9, "最近檢視時間"); sl.SetCellValue(1, 10, "預計檢視時間"); sl.SetCellValue(1, 11, "負責同仁"); sl.SetCellValue(1, 12, "備註"); sl.SetCellValue(1, 13, "備註(2)"); style.Font.FontColor = System.Drawing.Color.Red; foreach (var y in dts) { //sl.SetCellValue(i + 1, 1, Convert.ToInt64(y.Index)); sl.SetCellValue(i + 1, 2, y.ID); if (y.Eng) { sl.SetCellStyle(i + 1, 2, style); } sl.SetCellValue(i + 1, 3, Convert.ToInt64(y.webID)); sl.SetCellValue(i + 1, 4, y.Name); sl.SetCellValue(i + 1, 5, Convert.ToDouble(y.Version)); sl.SetCellValue(i + 1, 6, y.Depart); sl.SetCellValue(i + 1, 7, y.doctp); sl.SetCellValue(i + 1, 8, y.Stime); sl.SetCellValue(i + 1, 9, y.Rtime); //sl.SetCellValue(i + 1, 10, y.Ntime.ToString("yyy-MM-dd")); sl.SetCellValue(i + 1, 10, string.Format("=IF(I{0}=\"\",\"\",DATE(YEAR(I{0})+{1},MONTH(I{0}),DAY(I{0})))", i + 1, y.Ntime.Year - y.Rtime.Year)); if (y.Ntime.AddMonths(-1) < DateTime.Now) { sl.SetCellStyle(i + 1, 5, style); sl.SetCellStyle(i + 1, 9, style); sl.SetCellStyle(i + 1, 10, style); } sl.SetCellValue(i + 1, 11, y.Own); //sl.SetCellValue(i + 1, 13, y.Color); SLStyle st2 = sl.CreateStyle(); st2.FormatCode = "#,##0.0"; sl.SetCellStyle(i + 1, 5, st2); st2.FormatCode = "yyyy/mm/dd"; sl.SetCellStyle(i + 1, 8, st2); sl.SetCellStyle(i + 1, 9, st2); sl.SetCellStyle(i + 1, 10, st2); SLStyle stp = sl.CreateStyle(); stp.Protection.Locked = false; stp.Fill.SetPattern(PatternValues.Solid, System.Drawing.Color.FromArgb(255, 204, 255, 255), System.Drawing.Color.DarkSalmon); stp.SetRightBorder(BorderStyleValues.Thin, System.Drawing.Color.DarkSalmon); stp.SetLeftBorder(BorderStyleValues.Thin, System.Drawing.Color.DarkSalmon); stp.SetTopBorder(BorderStyleValues.Thin, System.Drawing.Color.DarkSalmon); stp.SetBottomBorder(BorderStyleValues.Thin, System.Drawing.Color.DarkSalmon); sl.SetCellStyle(i + 1, 5, stp); sl.SetCellStyle(i + 1, 9, stp); i++; } SLSheetProtection sp = new SLSheetProtection(); sp.AllowInsertRows = false; sp.AllowInsertColumns = false; sp.AllowFormatCells = true; sp.AllowDeleteColumns = false; sp.AllowDeleteRows = false; sp.AllowSelectUnlockedCells = true; sp.AllowSelectLockedCells = false; sl.ProtectWorksheet(sp); return(sl); }