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