protected Dictionary <string, SLStyle> CreateFieldStyles(Dictionary <string, FieldInfoAttribute> fieldInfo)
{
var result = new Dictionary <string, SLStyle>();
// стиль для ячейки таблицы
foreach (string name in fieldInfo.Keys)
{
SLStyle style = Document.CreateStyle();
style.Font.FontSize = FontSize;
style.Font.FontName = FontName;
style.SetLeftBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
style.SetTopBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
style.SetRightBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
style.SetBottomBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
style.FormatCode = fieldInfo[name].Format ?? string.Empty;
style.Alignment.WrapText = true;
style.SetVerticalAlignment(VerticalAlignmentValues.Center);
style.SetHorizontalAlignment(fieldInfo[name].HorizontalAlignment);
result[name] = style;
}
return(result);
}
public SLStyle FormattingExcelCells(SLDocument sl, bool val)
{
SLStyle style1 = sl.CreateStyle();
if (val == true)
{
style1.SetBottomBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
style1.SetTopBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
style1.SetLeftBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
style1.SetRightBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
style1.Font.FontName = "Arial";
style1.Font.FontSize = 9;
style1.Font.Bold = true;
style1.Alignment.Horizontal = HorizontalAlignmentValues.Center;
return(style1);
}
else
{
SLStyle style2 = sl.CreateStyle();
style2.Font.FontName = "Arial Cyr";
style2.Font.FontSize = 9;
style2.Font.Bold = true;
return(style2);
}
}
/// <summary> создание стилей </summary>
protected void CreateDefaultStyles()
{
// стиль для титула отчета
TitleStyle = Document.CreateStyle();
TitleStyle.SetHorizontalAlignment(HorizontalAlignmentValues.Left);
TitleStyle.SetVerticalAlignment(VerticalAlignmentValues.Center);
TitleStyle.Font.FontSize = FontSize + 5;
TitleStyle.Font.FontName = FontName;
TitleStyle.Font.Bold = true;
// стиль для заголовка полей таблицы
CaptionStyle = Document.CreateStyle();
CaptionStyle.SetLeftBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
CaptionStyle.SetTopBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
CaptionStyle.SetRightBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
CaptionStyle.SetBottomBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
CaptionStyle.Fill.SetPattern(PatternValues.Solid, System.Drawing.Color.Silver, System.Drawing.Color.Silver);
CaptionStyle.SetHorizontalAlignment(HorizontalAlignmentValues.Center);
CaptionStyle.SetVerticalAlignment(VerticalAlignmentValues.Center);
CaptionStyle.Font.FontSize = FontSize;
CaptionStyle.Font.FontName = FontName;
CaptionStyle.Font.Bold = true;
CaptionStyle.Alignment.WrapText = true;
}
private void InitForm()
{
stylHeader = sld.CreateStyle();
stylHeader.Fill.SetPattern(PatternValues.Solid, SLThemeColorIndexValues.Accent4Color, System.Drawing.Color.White);//4 3
stylHeader.Font.FontColor = System.Drawing.Color.White;
stylHeader2 = sld.CreateStyle();
stylHeader2.Fill.SetPattern(PatternValues.Solid, SLThemeColorIndexValues.Accent5Color, System.Drawing.Color.White);//2 5
stylHeader2.Font.FontColor = System.Drawing.Color.Yellow;
stylNormal = sld.CreateStyle();
stylNormal.Fill.SetPattern(PatternValues.Solid, System.Drawing.Color.White, System.Drawing.Color.White);
stylNormal.Font.FontColor = System.Drawing.Color.Black;
stylNormal.SetTopBorder(BorderStyleValues.Thin, System.Drawing.Color.LightGray);
stylNormal.SetBottomBorder(BorderStyleValues.Thin, System.Drawing.Color.LightGray);
stylNormal.SetRightBorder(BorderStyleValues.Thin, System.Drawing.Color.LightGray);
LoadDropList();
}
private void GeraArquivo(System.Data.DataTable dt, string dataInicial, string dataFinal)
{
SLDocument sl = new SLDocument();
int linha = 1;
//Set das configurações do arquivos e variaveis de style
SLPageSettings ps = new SLPageSettings();
ps.Orientation = OrientationValues.Landscape;
ps.PaperSize = SLPaperSizeValues.A4Paper;
sl.SetPageSettings(ps);
SLStyle bordaAcima = sl.CreateStyle();
bordaAcima.SetTopBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
SLStyle resultBold = sl.CreateStyle();
resultBold.SetFontBold(true);
resultBold.SetTopBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
resultBold.SetRightBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
resultBold.SetBottomBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
resultBold.SetLeftBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
SLStyle resultRed = sl.CreateStyle();
resultRed.SetFontBold(true);
resultRed.SetFontColor(System.Drawing.Color.Red);
resultRed.SetTopBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
resultRed.SetRightBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
resultRed.SetBottomBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
resultRed.SetLeftBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
SLStyle bordaContorno = sl.CreateStyle();
bordaContorno.SetTopBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
bordaContorno.SetRightBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
bordaContorno.SetBottomBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
bordaContorno.SetLeftBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
SLStyle alinhadoCentro = sl.CreateStyle();
alinhadoCentro.Alignment.Horizontal = HorizontalAlignmentValues.Center;
alinhadoCentro.Alignment.Vertical = VerticalAlignmentValues.Center;
alinhadoCentro.SetWrapText(true);
sl.SetCellValue(linha, 1, "Código Empresa");
sl.SetCellValue(linha, 2, "Número do registro do empregado");
sl.SetCellValue(linha, 3, "Nome");
sl.SetCellValue(linha, 4, "E-mail");
sl.SetCellValue(linha, 5, "Data da compra");
sl.SetCellValue(linha, 6, "Valor da compra");
sl.SetCellValue(linha, 7, "Quantidade de ingressos");
sl.SetCellValue(linha, 8, "Parcelas");
sl.SetCellValue(linha, 9, "Forma de pagamento");
sl.SetCellValue(linha, 10, "Data de vencimento");
sl.SetCellStyle(linha, 1, bordaContorno);
sl.SetCellStyle(linha, 2, bordaContorno);
sl.SetCellStyle(linha, 3, bordaContorno);
sl.SetCellStyle(linha, 4, bordaContorno);
sl.SetCellStyle(linha, 5, bordaContorno);
sl.SetCellStyle(linha, 6, bordaContorno);
sl.SetCellStyle(linha, 7, bordaContorno);
sl.SetCellStyle(linha, 8, bordaContorno);
sl.SetCellStyle(linha, 9, bordaContorno);
sl.SetCellStyle(linha, 10, bordaContorno);
sl.SetCellStyle(linha, 1, resultBold);
sl.SetCellStyle(linha, 2, resultBold);
sl.SetCellStyle(linha, 3, resultBold);
sl.SetCellStyle(linha, 4, resultBold);
sl.SetCellStyle(linha, 5, resultBold);
sl.SetCellStyle(linha, 6, resultBold);
sl.SetCellStyle(linha, 7, resultBold);
sl.SetCellStyle(linha, 8, resultBold);
sl.SetCellStyle(linha, 9, resultBold);
sl.SetCellStyle(linha, 10, resultBold);
linha++;
foreach (DataRow dr in dt.Rows)
{
sl.SetCellValue(linha, 1, dr[0].ToString());
sl.SetCellValue(linha, 2, dr[1].ToString());
sl.SetCellValue(linha, 3, dr[2].ToString());
sl.SetCellValue(linha, 4, dr[3].ToString());
sl.SetCellValue(linha, 5, dr[4].ToString());
sl.SetCellValue(linha, 6, dr[5].ToString());
sl.SetCellValue(linha, 7, dr[6].ToString());
sl.SetCellValue(linha, 8, dr[7].ToString());
sl.SetCellValue(linha, 9, dr[8].ToString());
sl.SetCellValue(linha, 10, dr[9].ToString());
sl.SetCellStyle(linha, 1, bordaContorno);
sl.SetCellStyle(linha, 2, bordaContorno);
sl.SetCellStyle(linha, 3, bordaContorno);
sl.SetCellStyle(linha, 4, bordaContorno);
sl.SetCellStyle(linha, 5, bordaContorno);
sl.SetCellStyle(linha, 6, bordaContorno);
sl.SetCellStyle(linha, 7, bordaContorno);
sl.SetCellStyle(linha, 8, bordaContorno);
sl.SetCellStyle(linha, 9, bordaContorno);
sl.SetCellStyle(linha, 10, bordaContorno);
linha++;
}
sl.AutoFitColumn(1);
sl.AutoFitColumn(2);
sl.AutoFitColumn(3);
sl.AutoFitColumn(4);
sl.AutoFitColumn(5);
sl.AutoFitColumn(6);
sl.AutoFitColumn(7);
sl.AutoFitColumn(8);
sl.AutoFitColumn(9);
sl.AutoFitColumn(10);
string arquivo;
if (!String.IsNullOrEmpty(dataInicial) && !String.IsNullOrEmpty(dataFinal))
{
arquivo = Server.MapPath(@"UploadFile\\" + "Relatorio_Festa_Aposentados "
+ dataInicial +
" á " + dataFinal + ".xlsx");
}
else
{
arquivo = Server.MapPath(@"UploadFile\\" + "Relatorio_Festa_Aposentados_Geral.xlsx");
}
sl.SaveAs(arquivo);
}
public void CreateCalendar(SLDocument document)
{
SLStyle date_style = document.CreateStyle();
/* Date_style is the style which must have the date's cells */
date_style.Alignment.Indent = 5;
date_style.Alignment.JustifyLastLine = true;
date_style.Alignment.ReadingOrder = SLAlignmentReadingOrderValues.RightToLeft;
date_style.Alignment.ShrinkToFit = true;
date_style.Alignment.TextRotation = 90;
date_style.Font.FontColor = System.Drawing.Color.Black;
date_style.Font.FontName = "Gill-Sans";
date_style.Font.FontSize = 12;
date_style.Font.Bold = true;
date_style.SetBottomBorder(DocumentFormat.OpenXml.Spreadsheet.BorderStyleValues.Thick, System.Drawing.Color.Blue);
date_style.Alignment.Horizontal = DocumentFormat.OpenXml.Spreadsheet.HorizontalAlignmentValues.Center;
/* Style for separating weeks with a column left and a column right colored in blue */
SLStyle date_column_left_style = document.CreateStyle();
SLStyle date_column_right_style = document.CreateStyle();
date_column_left_style.SetLeftBorder(DocumentFormat.OpenXml.Spreadsheet.BorderStyleValues.Thick, System.Drawing.Color.Blue);
date_column_right_style.SetRightBorder(DocumentFormat.OpenXml.Spreadsheet.BorderStyleValues.Thick, System.Drawing.Color.Blue);
/* Now I separe the weeks with blue colums, calling the date_column_left_style and the date_column_right_style */
/* divide column from January to the end of April */
document.SetCellStyle("B2", date_column_left_style); // put the column for divide weeks
document.SetCellStyle("H2", date_column_right_style); // put the column for divide weeks
document.SetCellStyle("O2", date_column_right_style); // put the column for divide weeks
document.SetCellStyle("V2", date_column_right_style); // put the column for divide weeks
document.SetCellStyle("AC2", date_column_right_style); // put the column for divide weeks
document.SetCellStyle("AJ2", date_column_right_style); // put the column for divide weeks
document.SetCellStyle("AQ2", date_column_right_style); // put the column for divide weeks
document.SetCellStyle("AX2", date_column_right_style); // put the column for divide weeks
document.SetCellStyle("BE2", date_column_right_style); // put the column for divide weeks
document.SetCellStyle("BL2", date_column_right_style); // put the column for divide weeks
document.SetCellStyle("BS2", date_column_right_style); // put the column for divide weeks
document.SetCellStyle("BZ2", date_column_right_style); // put the column for divide weeks
document.SetCellStyle("CG2", date_column_right_style); // put the column for divide weeks
document.SetCellStyle("CN2", date_column_right_style); // put the column for divide weeks
document.SetCellStyle("CU2", date_column_right_style); // put the column for divide weeks
document.SetCellStyle("DB2", date_column_right_style); // put the column for divide weeks
document.SetCellStyle("DI2", date_column_right_style); // put the column for divide weeks
document.SetCellStyle("DP2", date_column_right_style); // put the column for divide weeks
/* divide column from May to the end of July */
document.SetCellStyle("DQ2", date_column_left_style); // put the column for divide weeks
document.SetCellStyle("DX2", date_column_left_style); // put the column for divide weeks
document.SetCellStyle("EE2", date_column_left_style); // put the column for divide weeks
document.SetCellStyle("EL2", date_column_left_style); // put the column for divide weeks
document.SetCellStyle("ES2", date_column_left_style); // put the column for divide weeks
document.SetCellStyle("EZ2", date_column_left_style); // put the column for divide weeks
document.SetCellStyle("FG2", date_column_left_style); // put the column for divide weeks
document.SetCellStyle("FN2", date_column_left_style); // put the column for divide weeks
document.SetCellStyle("FU2", date_column_left_style); // put the column for divide weeks
document.SetCellStyle("GB2", date_column_left_style); // put the column for divide weeks
document.SetCellStyle("GI2", date_column_left_style); // put the column for divide weeks
document.SetCellStyle("GP2", date_column_left_style); // put the column for divide weeks
document.SetCellStyle("GW2", date_column_left_style); // put the column for divide weeks
document.SetCellStyle("HD2", date_column_left_style); // put the column for divide weeks
/* divide column from August to the end of November */
document.SetCellStyle("HK2", date_column_left_style); // put the column for divide weeks
document.SetCellStyle("HR2", date_column_left_style); // put the column for divide weeks
document.SetCellStyle("HY2", date_column_left_style); // put the column for divide weeks
document.SetCellStyle("IF2", date_column_left_style); // put the column for divide weeks
document.SetCellStyle("IM2", date_column_left_style); // put the column for divide weeks
document.SetCellStyle("IT2", date_column_left_style); // put the column for divide weeks
document.SetCellStyle("JA2", date_column_left_style); // put the column for divide weeks
document.SetCellStyle("JH2", date_column_left_style); // put the column for divide weeks
document.SetCellStyle("JO2", date_column_left_style); // put the column for divide weeks
document.SetCellStyle("JV2", date_column_left_style); // put the column for divide weeks
document.SetCellStyle("KC2", date_column_left_style); // put the column for divide weeks
document.SetCellStyle("KJ2", date_column_left_style); // put the column for divide weeks
document.SetCellStyle("KQ2", date_column_left_style); // put the column for divide weeks
document.SetCellStyle("KX2", date_column_left_style); // put the column for divide weeks
document.SetCellStyle("LE2", date_column_left_style); // put the column for divide weeks
document.SetCellStyle("LL2", date_column_left_style); // put the column for divide weeks
document.SetCellStyle("LS2", date_column_left_style); // put the column for divide weeks
/* divide column of December */
document.SetCellStyle("LZ2", date_column_left_style); // put the column for divide weeks
document.SetCellStyle("MG2", date_column_left_style); // put the column for divide weeks
document.SetCellStyle("MN2", date_column_left_style); // put the column for divide weeks
document.SetCellStyle("MU2", date_column_left_style); // put the column for divide weeks
document.SetCellStyle("NB2", date_column_right_style); // put the column for divide weeks
/* Style for displaing the weeks */
SLStyle week_style = document.CreateStyle();
week_style.Alignment.Indent = 5;
week_style.Alignment.Horizontal = DocumentFormat.OpenXml.Spreadsheet.HorizontalAlignmentValues.Center;
week_style.Font.FontColor = System.Drawing.Color.Blue;
week_style.SetTopBorder(DocumentFormat.OpenXml.Spreadsheet.BorderStyleValues.Thick, System.Drawing.Color.Blue);
week_style.SetLeftBorder(DocumentFormat.OpenXml.Spreadsheet.BorderStyleValues.Thick, System.Drawing.Color.Blue);
week_style.SetRightBorder(DocumentFormat.OpenXml.Spreadsheet.BorderStyleValues.Thick, System.Drawing.Color.Blue);
/* Creation of a calendar which is putted in the sheet */
Calendar myCal = CultureInfo.InvariantCulture.Calendar;
DateTime localDateToday = DateTime.Today;
string week = "W ";
for (int i = 1; i <= 366; i++) // print the sequence of the weeks of a year
{
int f = 1;
/* Print days from January to the end of April */
document.MergeWorksheetCells("B1", "H1");
document.MergeWorksheetCells("I1", "O1");
document.MergeWorksheetCells("P1", "V1");
document.MergeWorksheetCells("W1", "AC1");
document.MergeWorksheetCells("AD1", "AJ1");
document.MergeWorksheetCells("AK1", "AQ1");
document.MergeWorksheetCells("AR1", "AX1");
document.MergeWorksheetCells("AY1", "BE1");
document.MergeWorksheetCells("BF1", "BL1");
document.MergeWorksheetCells("BM1", "BS1");
document.MergeWorksheetCells("BT1", "BZ1");
document.MergeWorksheetCells("CA1", "CG1");
document.MergeWorksheetCells("CH1", "CN1");
document.MergeWorksheetCells("CO1", "CU1");
document.MergeWorksheetCells("CV1", "DB1");
document.MergeWorksheetCells("DC1", "DI1");
document.MergeWorksheetCells("DJ1", "DP1");
/* Print the numbered weeks from January to the end of April */
document.SetCellValue("B1", week + f++.ToString());
document.SetCellValue("I1", week + f++.ToString());
document.SetCellValue("P1", week + f++.ToString());
document.SetCellValue("W1", week + f++.ToString());
document.SetCellValue("AD1", week + f++.ToString());
document.SetCellValue("AK1", week + f++.ToString());
document.SetCellValue("AR1", week + f++.ToString());
document.SetCellValue("AY1", week + f++.ToString());
document.SetCellValue("BF1", week + f++.ToString());
document.SetCellValue("BM1", week + f++.ToString());
document.SetCellValue("BT1", week + f++.ToString());
document.SetCellValue("CA1", week + f++.ToString());
document.SetCellValue("CH1", week + f++.ToString());
document.SetCellValue("CO1", week + f++.ToString());
document.SetCellValue("CV1", week + f++.ToString());
document.SetCellValue("DC1", week + f++.ToString());
document.SetCellValue("DJ1", week + f++.ToString());
/* Print days from May to the end of July */
document.MergeWorksheetCells("DQ1", "DW1");
document.MergeWorksheetCells("DX1", "ED1");
document.MergeWorksheetCells("EE1", "EK1");
document.MergeWorksheetCells("EL1", "ER1");
document.MergeWorksheetCells("ES1", "EY1");
document.MergeWorksheetCells("EZ1", "FF1");
document.MergeWorksheetCells("FG1", "FM1");
document.MergeWorksheetCells("FN1", "FT1");
document.MergeWorksheetCells("FU1", "GA1");
document.MergeWorksheetCells("GB1", "GH1");
document.MergeWorksheetCells("GI1", "GO1");
document.MergeWorksheetCells("GP1", "GV1");
document.MergeWorksheetCells("GW1", "HC1");
document.MergeWorksheetCells("HD1", "HJ1");
/* Print the numbered weeks from May to the end of July */
document.SetCellValue("DQ1", week + f++);
document.SetCellValue("DX1", week + f++);
document.SetCellValue("EE1", week + f++);
document.SetCellValue("EL1", week + f++);
document.SetCellValue("ES1", week + f++);
document.SetCellValue("EZ1", week + f++);
document.SetCellValue("FG1", week + f++);
document.SetCellValue("FN1", week + f++);
document.SetCellValue("FU1", week + f++);
document.SetCellValue("GB1", week + f++);
document.SetCellValue("GI1", week + f++);
document.SetCellValue("GP1", week + f++);
document.SetCellValue("GW1", week + f++);
document.SetCellValue("HD1", week + f++);
/* Print days from August to the end of November */
document.MergeWorksheetCells("HK1", "HQ1");
document.MergeWorksheetCells("HR1", "HX1");
document.MergeWorksheetCells("HY1", "IE1");
document.MergeWorksheetCells("IF1", "IL1");
document.MergeWorksheetCells("IM1", "IS1");
document.MergeWorksheetCells("IT1", "IZ1");
document.MergeWorksheetCells("JA1", "JG1");
document.MergeWorksheetCells("JH1", "JN1");
document.MergeWorksheetCells("JO1", "JU1");
document.MergeWorksheetCells("JV1", "KB1");
document.MergeWorksheetCells("KC1", "KI1");
document.MergeWorksheetCells("KJ1", "KP1");
document.MergeWorksheetCells("KQ1", "KW1");
document.MergeWorksheetCells("KX1", "LD1");
document.MergeWorksheetCells("LE1", "LK1");
document.MergeWorksheetCells("LL1", "LR1");
document.MergeWorksheetCells("LS1", "LY1");
/* Print the numbered weeks from August to the end of November */
document.SetCellValue("HK1", week + f++);
document.SetCellValue("HR1", week + f++);
document.SetCellValue("HY1", week + f++);
document.SetCellValue("IF1", week + f++);
document.SetCellValue("IM1", week + f++);
document.SetCellValue("IT1", week + f++);
document.SetCellValue("JA1", week + f++);
document.SetCellValue("JH1", week + f++);
document.SetCellValue("JO1", week + f++);
document.SetCellValue("JV1", week + f++);
document.SetCellValue("KC1", week + f++);
document.SetCellValue("KJ1", week + f++);
document.SetCellValue("KQ1", week + f++);
document.SetCellValue("KX1", week + f++);
document.SetCellValue("LE1", week + f++);
document.SetCellValue("LL1", week + f++);
document.SetCellValue("LS1", week + f++);
/* Print days of December */
document.MergeWorksheetCells("LZ1", "MF1");
document.MergeWorksheetCells("MG1", "MM1");
document.MergeWorksheetCells("MN1", "MT1");
document.MergeWorksheetCells("MU1", "NB1");
/* Print the numbered weeks of December */
document.SetCellValue("LZ1", week + f++);
document.SetCellValue("MG1", week + f++);
document.SetCellValue("MN1", week + f++);
document.SetCellValue("MU1", week + f++);
DateTime myDT = new DateTime(localDateToday.Year - 1, 12, 30, new GregorianCalendar()); // for show the complete current year
// IDK why if I want to show the first of january on cell B2 I must set calendar two day before ???
document.SetCellStyle(2, i, date_style);
document.SetCellStyle(1, i, week_style);
document.SetCellValue("A2", " ");
myDT = myCal.AddDays(myDT, i);
var PrintDays = document.SetCellValue(2, i, myDT.Day + "/" + myDT.Month + "/" + myDT.Year);
}
localDateToday = myCal.AddDays(localDateToday, +1).Date;
}
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);
}
public bool generateExcel(List <List <List <Factura> > > IngresoSemanas, List <List <List <Egreso> > > EgresoSemanas, string folderpath, string fileName, int pYear, int pMonth)
{
bool result = true;
try
{
//decimal TotalIngresos = 0;
//decimal TotalEgresos = 0;
//decimal Total = 0;
SLDocument sl = new SLDocument();
List <Producto> productosTable = new List <Producto>();
int dayMonthIngrsos = 1;
int dayMonthEgresos = 1;
decimal lastTotalIngresos = 0;
decimal lastTotalEgresos = 0;
for (int s = 0; s < IngresoSemanas.Count; s++)
{
int dayWeek = 1;
decimal totalSemana = 0;
List <List <Factura> > SemanaIngresos = IngresoSemanas[s];
List <Detfactura> DetallesSemana = new List <Detfactura>();
List <List <Egreso> > SemanaEgresos = EgresoSemanas[s];
List <Egreso> ConceptosEgresos = new List <Egreso>();
productosTable.Clear();
//procesando ingresos
sl.AddWorksheet("Week " + (s + 1));
sl.MergeWorksheetCells(2, 2, 2, SemanaIngresos.Count + 3, BorderStyleValues.Thin);
sl.MergeWorksheetCells("C3", "C4");
//set height for each column
sl.SetRowHeight(1, 25);
sl.SetRowHeight(2, 25);
sl.SetRowHeight(3, 25);
sl.SetRowHeight(4, 25);
//set width for each header cell
sl.SetColumnWidth(2, 20);
sl.SetColumnWidth(3, 17);
sl.SetCellValue("B2", "INGRESOS");
SLStyle styleTitle = sl.CreateStyle();
styleTitle.Fill.SetPattern(PatternValues.Solid, System.Drawing.Color.LightGray, System.Drawing.Color.Black);
styleTitle.SetVerticalAlignment(VerticalAlignmentValues.Center);
styleTitle.SetHorizontalAlignment(HorizontalAlignmentValues.Center);
styleTitle.Font.Bold = true;
styleTitle.Font.FontSize = 11;
sl.SetCellStyle(2, 2, styleTitle);
SLStyle styleProducto = sl.CreateStyle();
styleProducto.SetBottomBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
styleProducto.SetTopBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
styleProducto.SetLeftBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
styleProducto.SetRightBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
styleProducto.SetVerticalAlignment(VerticalAlignmentValues.Center);
styleProducto.SetHorizontalAlignment(HorizontalAlignmentValues.Left);
styleProducto.Font.Bold = false;
styleProducto.Font.FontSize = 10;
SLStyle styleDiaIngreso = sl.CreateStyle();
styleDiaIngreso.SetBottomBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
styleDiaIngreso.SetTopBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
styleDiaIngreso.SetLeftBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
styleDiaIngreso.SetRightBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
styleDiaIngreso.SetVerticalAlignment(VerticalAlignmentValues.Center);
styleDiaIngreso.SetHorizontalAlignment(HorizontalAlignmentValues.Right);
styleDiaIngreso.Font.Bold = false;
styleDiaIngreso.Font.FontSize = 10;
SLStyle styleDates = sl.CreateStyle();
styleDates.SetBottomBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
styleDates.SetTopBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
styleDates.SetLeftBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
styleDates.SetRightBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
styleDates.SetVerticalAlignment(VerticalAlignmentValues.Center);
styleDates.SetHorizontalAlignment(HorizontalAlignmentValues.Center);
styleDates.Font.Bold = true;
styleDates.Font.FontSize = 10;
styleDates.SetWrapText(true);
SLStyle styleIndexes = sl.CreateStyle();
styleIndexes.SetBottomBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
styleIndexes.SetTopBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
styleIndexes.SetLeftBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
styleIndexes.SetRightBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
styleIndexes.SetVerticalAlignment(VerticalAlignmentValues.Center);
styleIndexes.SetHorizontalAlignment(HorizontalAlignmentValues.Left);
styleIndexes.Font.Bold = true;
styleIndexes.Font.FontSize = 10;
SLStyle styleTotalIngresos = sl.CreateStyle();
styleTotalIngresos.SetVerticalAlignment(VerticalAlignmentValues.Center);
styleTotalIngresos.SetHorizontalAlignment(HorizontalAlignmentValues.Center);
styleTotalIngresos.Font.FontSize = 10;
styleTotalIngresos.Font.FontColor = System.Drawing.Color.Green;
SLStyle styleSubTotalIngresos = sl.CreateStyle();
styleSubTotalIngresos.SetBottomBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
styleSubTotalIngresos.SetTopBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
styleSubTotalIngresos.SetLeftBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
styleSubTotalIngresos.SetRightBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
styleSubTotalIngresos.SetVerticalAlignment(VerticalAlignmentValues.Center);
styleSubTotalIngresos.SetHorizontalAlignment(HorizontalAlignmentValues.Right);
styleSubTotalIngresos.Font.FontSize = 10;
styleSubTotalIngresos.Font.FontColor = System.Drawing.Color.Green;
SLStyle styleTotalEgresos = sl.CreateStyle();
styleTotalEgresos.SetVerticalAlignment(VerticalAlignmentValues.Center);
styleTotalEgresos.SetHorizontalAlignment(HorizontalAlignmentValues.Center);
styleTotalEgresos.Font.FontSize = 10;
styleTotalEgresos.Font.FontColor = System.Drawing.Color.Red;
SLStyle styleSubTotalEgresos = sl.CreateStyle();
styleSubTotalEgresos.SetBottomBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
styleSubTotalEgresos.SetTopBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
styleSubTotalEgresos.SetLeftBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
styleSubTotalEgresos.SetRightBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
styleSubTotalEgresos.SetVerticalAlignment(VerticalAlignmentValues.Center);
styleSubTotalEgresos.SetHorizontalAlignment(HorizontalAlignmentValues.Right);
styleSubTotalEgresos.Font.FontSize = 10;
styleSubTotalEgresos.Font.FontColor = System.Drawing.Color.Red;
sl.SetCellValue("B4", "DETALLES");
sl.SetCellStyle(3, 2, styleIndexes);
sl.SetCellStyle(4, 2, styleIndexes);
sl.SetCellValue("C3", "GANANCIAS DE LA SEMANA PASADA");
sl.SetCellStyle(3, 3, styleDates);
foreach (List <Factura> Dia in SemanaIngresos)
{
foreach (Factura ingreso in Dia)
{
foreach (Detfactura det in ingreso.DetsFactura)
{
if (productosTable.Where(a => a.Id == det.IdProducto).FirstOrDefault() == null)
{
productosTable.Add(det.Producto);
sl.SetCellValue(4 + productosTable.Count, dayWeek + 3, "DETALLES");
}
}
}
}
for (int d = 0; d < SemanaIngresos.Count; d++)
{
sl.SetCellValue("B3", new DateTime(pYear, pMonth, 1).ToString("MMMM", new CultureInfo("es-ES")).ToUpper());
List <Factura> Dia = SemanaIngresos[d];
sl.SetColumnWidth(dayWeek + 3, 12);
sl.SetCellValue(3, dayWeek + 3, new DateTime(pYear, pMonth, dayMonthIngrsos).ToShortDateString());
sl.SetCellStyle(3, dayWeek + 3, styleDates);
sl.SetCellValue(4, dayWeek + 3, new DateTime(pYear, pMonth, dayMonthIngrsos).DayOfWeek.ToString());
sl.SetCellStyle(4, dayWeek + 3, styleDates);
foreach (Factura ingreso in Dia)
{
foreach (Detfactura det in ingreso.DetsFactura)
{
DetallesSemana.Add(det);
}
}
for (int prod = 0; prod < productosTable.Count; prod++)
{
Producto producto = productosTable[prod];
decimal ingresoDiaProducto = 0;
List <Factura> ingresoInDia = new List <Factura>();
if (Dia.Where(a => Convert.ToDateTime(a.FhRegistro).Day == dayMonthIngrsos && a.DetsFactura.Where(dt => dt.IdProducto == producto.Id).Count() > 0).Count() > 0)
{
ingresoInDia = Dia.Where(a => Convert.ToDateTime(a.FhRegistro).Day == dayMonthIngrsos && a.DetsFactura.Where(dt => dt.IdProducto == producto.Id).Count() > 0).ToList();
}
foreach (Factura fact in ingresoInDia)
{
foreach (Detfactura det in fact.DetsFactura.Where(a => a.IdProducto == productosTable[prod].Id))
{
ingresoDiaProducto += (det.Total - det.Descuento);
totalSemana += (det.Total - det.Descuento);
}
}
sl.SetCellValue(5 + prod, dayWeek + 3, ingresoDiaProducto == 0 ? "" : "$" + Decimal.Round(ingresoDiaProducto, 2).ToString());
sl.SetCellStyle(5 + prod, dayWeek + 3, styleDiaIngreso);
sl.SetCellStyle(5 + prod, 3, styleProducto);
}
sl.SetCellValue("C" + 5, "$" + Decimal.Round(lastTotalIngresos, 2));
//MessageBox.Show(new DateTime(2019,3,1).DayOfWeek.ToString());
decimal ingresoDia = 0;
List <Factura> ingresoInDiaSub = new List <Factura>();
if (Dia.Where(a => Convert.ToDateTime(a.FhRegistro).Day == dayMonthIngrsos).Count() > 0)
{
ingresoInDiaSub = Dia.Where(a => Convert.ToDateTime(a.FhRegistro).Day == dayMonthIngrsos).ToList();
}
foreach (Factura fact in ingresoInDiaSub)
{
foreach (Detfactura det in fact.DetsFactura)
{
ingresoDia += (det.Total - det.Descuento);
}
}
sl.SetCellValue("B" + (productosTable.Count + 5), "SUBTOTAL DE INGRESOS");
sl.SetCellStyle((productosTable.Count + 5), 2, styleIndexes);
sl.SetRowHeight(productosTable.Count + 5, 30);
sl.SetCellStyle((productosTable.Count + 5), dayWeek + 3, styleSubTotalIngresos);
sl.SetCellValue((productosTable.Count + 5), dayWeek + 3, "$" + Decimal.Round(ingresoDia, 2).ToString());
dayWeek++;
dayMonthIngrsos++;
}
for (int p = 0; p < productosTable.Count; p++)
{
sl.SetCellValue("B" + (p + 5), productosTable[p].Nombre);
sl.SetCellStyle(p + 5, 2, styleProducto);
sl.SetRowHeight(p + 5, 30);
}
sl.MergeWorksheetCells(5, 3, productosTable.Count + 5, 3, BorderStyleValues.Thin);
sl.MergeWorksheetCells(productosTable.Count + 6, 3, productosTable.Count + 6, SemanaIngresos.Count + 3, BorderStyleValues.Thin);
sl.SetCellValue("B" + (productosTable.Count + 6), "TOTAL SEMANA");
sl.SetCellStyle((productosTable.Count + 6), 2, styleIndexes);
sl.SetRowHeight(productosTable.Count + 6, 30);
sl.SetCellStyle((productosTable.Count + 6), 3, styleTotalIngresos);
sl.SetCellStyle(5, 3, styleDates);
sl.SetCellStyle((productosTable.Count + 5), 3, styleDates);
sl.SetCellValue("C" + (productosTable.Count + 6), "$" + Decimal.Round(totalSemana, 2));
//procesando egresos
sl.MergeWorksheetCells(3 + productosTable.Count + 5, 2, 3 + productosTable.Count + 5, SemanaIngresos.Count + 3, BorderStyleValues.Thin);
sl.MergeWorksheetCells("C" + (3 + productosTable.Count + 6), "C" + (3 + productosTable.Count + 7));
sl.SetCellValue("B" + (3 + productosTable.Count + 5), "EGRESOS");
sl.SetCellStyle((3 + productosTable.Count + 5), 2, styleTitle);
sl.SetRowHeight(3 + productosTable.Count + 2, 25);
sl.SetRowHeight(3 + productosTable.Count + 2 + 1, 25);
sl.SetRowHeight(3 + productosTable.Count + 2 + 2, 25);
sl.SetRowHeight(3 + productosTable.Count + 2 + 3, 25);
sl.SetRowHeight(3 + productosTable.Count + 2 + 4, 25);
sl.SetRowHeight(3 + productosTable.Count + 2 + 5, 25);
sl.SetCellValue("B" + (3 + productosTable.Count + 6), new DateTime(pYear, pMonth, 1).ToString("MMMM", new CultureInfo("es-ES")).ToUpper());
sl.SetCellValue("B" + (3 + productosTable.Count + 7), "DETALLES");
sl.SetCellStyle((3 + productosTable.Count + 6), 2, styleDates);
sl.SetCellStyle((3 + productosTable.Count + 7), 2, styleIndexes);
sl.SetCellValue("C" + (3 + productosTable.Count + 6), "EGRESOS DE LA SEMANA PASADA");
sl.SetCellStyle((3 + productosTable.Count + 6), 3, styleDates);
dayWeek = 1;
decimal totalSemanaEgr = 0;
ConceptosEgresos.Clear();
for (int d = 0; d < SemanaEgresos.Count; d++)
{
foreach (Egreso egreso in SemanaEgresos[d])
{
if (ConceptosEgresos.Where(a => a.Nombre.ToUpper().Trim() == egreso.Nombre.ToUpper().Trim()).FirstOrDefault() == null)
{
ConceptosEgresos.Add(egreso);
}
}
}
sl.SetCellStyle((3 + productosTable.Count + ConceptosEgresos.Count + 8), 2, styleIndexes);
for (int d = 0; d < SemanaEgresos.Count; d++)
{
List <Egreso> Dia = SemanaEgresos[d];
decimal TotalEgresoDia = 0;
sl.SetCellValue("B" + (3 + productosTable.Count + 6), new DateTime(pYear, pMonth, 1).ToString("MMMM", new CultureInfo("es-ES")).ToUpper());
sl.SetCellValue((3 + productosTable.Count + 6), dayWeek + 3, new DateTime(pYear, pMonth, dayMonthEgresos).ToShortDateString());
sl.SetCellStyle((3 + productosTable.Count + 6), dayWeek + 3, styleDates);
sl.SetCellValue((3 + productosTable.Count + 7), dayWeek + 3, new DateTime(pYear, pMonth, dayMonthEgresos).DayOfWeek.ToString());
sl.SetCellStyle((3 + productosTable.Count + 7), dayWeek + 3, styleDates);
for (int egr = 0; egr < ConceptosEgresos.Count; egr++)
{
decimal EgresoDia = 0;
Egreso egreso = ConceptosEgresos[egr];
List <Egreso> egresoInDia = new List <Egreso>();
if (Dia.Where(a => Convert.ToDateTime(a.FhRegistro).Day == dayMonthEgresos).Count() > 0)
{
egresoInDia = Dia.Where(a => Convert.ToDateTime(a.FhRegistro).Day == dayMonthEgresos && a.Nombre.ToUpper().Trim() == egreso.Nombre.ToUpper().Trim()).ToList();
}
foreach (Egreso fact in egresoInDia)
{
EgresoDia += (fact.Total);
TotalEgresoDia += fact.Total;
totalSemanaEgr += (fact.Total);
}
sl.SetCellValue((3 + productosTable.Count + 8) + egr, dayWeek + 3, EgresoDia == 0 ? "" : "$" + Decimal.Round(EgresoDia, 2).ToString());
sl.SetCellStyle((3 + productosTable.Count + 8) + egr, 3, styleDiaIngreso);
sl.SetCellStyle((3 + productosTable.Count + 8) + egr, dayWeek + 3, styleDiaIngreso);
}
for (int p = 0; p < ConceptosEgresos.Count; p++)
{
sl.SetCellValue("B" + ((3 + productosTable.Count + 8) + p), ConceptosEgresos[p].Nombre);
sl.SetCellStyle(((3 + productosTable.Count + 8) + p), 2, styleProducto);
sl.SetRowHeight(((3 + productosTable.Count + 8) + p), 30);
}
sl.SetCellStyle((3 + productosTable.Count + ConceptosEgresos.Count + 8), dayWeek + 3, styleSubTotalEgresos);
sl.SetCellValue((3 + productosTable.Count + ConceptosEgresos.Count + 8), dayWeek + 3, "$" + Decimal.Round(TotalEgresoDia, 2).ToString());
dayMonthEgresos++;
dayWeek++;
}
sl.MergeWorksheetCells((3 + productosTable.Count + 8), 3, (3 + productosTable.Count + ConceptosEgresos.Count + 8), 3, BorderStyleValues.Thin);
sl.MergeWorksheetCells((3 + productosTable.Count + ConceptosEgresos.Count + 9), 3, (3 + productosTable.Count + ConceptosEgresos.Count + 9), 3 + SemanaEgresos.Count, BorderStyleValues.Thin);
sl.SetCellValue("B" + (3 + productosTable.Count + ConceptosEgresos.Count + 8), "SUBTOTAL EGRESOS SEMANA");
sl.SetCellValue("B" + (3 + productosTable.Count + ConceptosEgresos.Count + 9), "TOTAL SEMANA");
sl.SetRowHeight((3 + productosTable.Count + ConceptosEgresos.Count + 8), 30);
sl.SetCellValue("C" + (3 + productosTable.Count + 8), "$" + Decimal.Round(lastTotalEgresos, 2));
sl.SetCellStyle((3 + productosTable.Count + 8), 3, styleDates);
sl.SetCellStyle((3 + productosTable.Count + ConceptosEgresos.Count + 9), 2, styleIndexes);
sl.SetCellStyle((3 + productosTable.Count + ConceptosEgresos.Count + 9), 3, styleTotalEgresos);
sl.SetRowHeight((3 + productosTable.Count + ConceptosEgresos.Count + 9), 30);
sl.SetCellValue("C" + (3 + productosTable.Count + ConceptosEgresos.Count + 9), "$" + Decimal.Round(totalSemanaEgr, 2));
//lastTotalIngresos -= lastTotalEgresos;
//resumen
sl.MergeWorksheetCells((3 + productosTable.Count + ConceptosEgresos.Count + 11), 2, (3 + productosTable.Count + ConceptosEgresos.Count + 11), 3 + SemanaEgresos.Count, BorderStyleValues.Thin);
sl.MergeWorksheetCells((3 + productosTable.Count + ConceptosEgresos.Count + 12), 3, (3 + productosTable.Count + ConceptosEgresos.Count + 12), 3 + SemanaEgresos.Count, BorderStyleValues.Thin);
sl.MergeWorksheetCells((3 + productosTable.Count + ConceptosEgresos.Count + 13), 3, (3 + productosTable.Count + ConceptosEgresos.Count + 13), 3 + SemanaEgresos.Count, BorderStyleValues.Thin);
sl.MergeWorksheetCells((3 + productosTable.Count + ConceptosEgresos.Count + 14), 3, (3 + productosTable.Count + ConceptosEgresos.Count + 14), 3 + SemanaEgresos.Count, BorderStyleValues.Thin);
sl.SetRowHeight((3 + productosTable.Count + ConceptosEgresos.Count + 10), 25);
sl.SetRowHeight((3 + productosTable.Count + ConceptosEgresos.Count + 11), 25);
sl.SetRowHeight((3 + productosTable.Count + ConceptosEgresos.Count + 12), 25);
sl.SetRowHeight((3 + productosTable.Count + ConceptosEgresos.Count + 13), 25);
sl.SetRowHeight((3 + productosTable.Count + ConceptosEgresos.Count + 14), 25);
sl.SetCellStyle((3 + productosTable.Count + ConceptosEgresos.Count + 11), 2, styleTitle);
sl.SetCellStyle((3 + productosTable.Count + ConceptosEgresos.Count + 12), 2, styleDates);
sl.SetCellStyle((3 + productosTable.Count + ConceptosEgresos.Count + 13), 2, styleDates);
sl.SetCellStyle((3 + productosTable.Count + ConceptosEgresos.Count + 14), 2, styleDates);
sl.SetCellStyle((3 + productosTable.Count + ConceptosEgresos.Count + 12), 3, styleTotalIngresos);
sl.SetCellStyle((3 + productosTable.Count + ConceptosEgresos.Count + 13), 3, styleTotalEgresos);
sl.SetCellStyle((3 + productosTable.Count + ConceptosEgresos.Count + 14), 3, (Decimal.Round((totalSemana - totalSemanaEgr) + (lastTotalIngresos), 2)) == 0 ? styleDates : (Decimal.Round((totalSemana - totalSemanaEgr) + (lastTotalIngresos), 2)) < 0 ? styleTotalEgresos : styleTotalIngresos);
sl.SetCellValue("B" + (3 + productosTable.Count + ConceptosEgresos.Count + 11), "RESUMEN FINAL DE SEMANA ENTREGADO AL ADMINISTRADOR");
sl.SetCellValue("B" + (3 + productosTable.Count + ConceptosEgresos.Count + 12), "INGRESOS");
sl.SetCellValue("B" + (3 + productosTable.Count + ConceptosEgresos.Count + 13), "GASTOS");
sl.SetCellValue("B" + (3 + productosTable.Count + ConceptosEgresos.Count + 14), "TOTAL");
sl.SetCellValue("C" + (3 + productosTable.Count + ConceptosEgresos.Count + 12), "$" + Decimal.Round(totalSemana, 2));
sl.SetCellValue("C" + (3 + productosTable.Count + ConceptosEgresos.Count + 13), "$" + Decimal.Round(totalSemanaEgr, 2));
sl.SetCellValue("C" + (3 + productosTable.Count + ConceptosEgresos.Count + 14), "$" + Decimal.Round((totalSemana - totalSemanaEgr) + (lastTotalIngresos), 2));
lastTotalEgresos = totalSemanaEgr;
lastTotalIngresos = lastTotalIngresos + (totalSemana - totalSemanaEgr);
//lastTotalIngresos = totalSemana==0? lastTotalIngresos: (totalSemana - totalSemanaEgr) + (lastTotalIngresos);
// lastTotalIngresos = totalSemana == 0? lastTotalIngresos:(lastTotalIngresos -= lastTotalEgresos);
// MessageBox.Show(lastTotalIngresos.ToString());
}
sl.DeleteWorksheet("Sheet1");
if (!Directory.Exists(folderpath))
{
DirectoryInfo di = Directory.CreateDirectory(folderpath);
sl.SaveAs(folderpath + "\\" + fileName);
}
else
{
sl.SaveAs(folderpath + "\\" + fileName);
}
}
catch (Exception Ex)
{
throw Ex;
}
return(result);
}
private void GeraArquivoGeral(DataSet ds, string mes, string ano)
{
string dtInicio = Convert.ToDateTime(txtDtInicioRelEstouro.Text).ToString("dd-MM-yyyy");
string dtFinal = Convert.ToDateTime(txtDtFinalRelEstouro.Text).ToString("dd-MM-yyyy");
int qtdestouro = 0, resperiodo = 0, linha = 6;
int previsao = objBLL.GeraTotalEstouros(Convert.ToDateTime(dtInicio), Convert.ToDateTime(dtFinal));
SLDocument sl = new SLDocument();
//Set das configurações do arquivos e variaveis de style
SLPageSettings ps = new SLPageSettings();
ps.Orientation = OrientationValues.Landscape;
ps.PaperSize = SLPaperSizeValues.A4Paper;
sl.SetPageSettings(ps);
SLStyle bordaAcima = sl.CreateStyle();
bordaAcima.SetTopBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
SLStyle resultBold = sl.CreateStyle();
resultBold.SetFontBold(true);
resultBold.SetTopBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
resultBold.SetRightBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
resultBold.SetBottomBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
resultBold.SetLeftBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
SLStyle resultRed = sl.CreateStyle();
resultRed.SetFontBold(true);
resultRed.SetFontColor(System.Drawing.Color.Red);
resultRed.SetTopBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
resultRed.SetRightBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
resultRed.SetBottomBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
resultRed.SetLeftBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
SLStyle bordaContorno = sl.CreateStyle();
bordaContorno.SetTopBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
bordaContorno.SetRightBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
bordaContorno.SetBottomBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
bordaContorno.SetLeftBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
SLStyle alinhadoCentro = sl.CreateStyle();
alinhadoCentro.Alignment.Horizontal = HorizontalAlignmentValues.Center;
alinhadoCentro.Alignment.Vertical = VerticalAlignmentValues.Center;
alinhadoCentro.SetWrapText(true);
//Loops de preenchimento do arquivo
foreach (DataRow LAreaRel in ds.Tables["AREAS_REL"].Rows)
{
foreach (DataRow LQtdEstouro in ds.Tables["QTD_ESTOURO"].Rows)
{
if (LAreaRel[0].ToString() == LQtdEstouro[0].ToString())
{
qtdestouro = Convert.ToInt32(LQtdEstouro[1]);
break;
}
qtdestouro = 0;
}
foreach (DataRow LRespPeriodo in ds.Tables["RESP_PERIODO"].Rows)
{
if (LAreaRel[0].ToString() == LRespPeriodo[0].ToString())
{
resperiodo = Convert.ToInt32(LRespPeriodo[1]);
break;
}
resperiodo = 0;
}
sl.SetCellValue(linha, 1, LAreaRel[0].ToString());
sl.SetCellValue(linha, 2, qtdestouro);
sl.SetCellValue(linha, 3, resperiodo);
sl.SetCellStyle(linha, 1, bordaContorno);
sl.SetCellStyle(linha, 2, bordaContorno);
sl.SetCellStyle(linha, 3, bordaContorno);
sl.SetCellStyle(linha, 4, bordaContorno);
if (qtdestouro != 0 && resperiodo != 0)
{
sl.SetCellValue(linha, 4, "=ROUND(((B" + linha + "/C" + linha + ")*100),2)");
}
else
{
sl.SetCellValue(linha, 4, 0);
}
linha++;
}
//Atribuindo valores fixos e stylo das celulas
int auxlinha = linha - 1;
sl.SetCellValue(1, 1, "Manifestações com previsão de encerramento entre " + dtInicio + " e " + dtFinal);
sl.SetCellValue(1, 2, previsao.ToString());
sl.SetCellStyle(1, 1, alinhadoCentro);
sl.SetCellStyle(1, 2, alinhadoCentro);
sl.SetCellValue(2, 1, "Respostas das Áreas Gerenciadoras:");
sl.SetCellValue(2, 2, "=C" + linha + @"&"" ou ""&ROUND(B1/C" + linha + @",4)*100&""%""");
sl.SetCellValue(3, 1, "Manifestações encerradas após a previsão:");
sl.SetCellValue(3, 2, "=B" + linha + @"&"" ou ""&ROUND(B" + linha + @"/B1,4)*100&""%""");
sl.SetCellValue(5, 1, "Áreas Gerenciadoras");
sl.SetCellValue(5, 2, "Quantidade de estouros dos prazos");
sl.SetCellValue(5, 3, "Respostas no período");
sl.SetCellValue(5, 4, "%");
sl.SetCellStyle(5, 1, resultBold);
sl.SetCellStyle(5, 2, resultBold);
sl.SetCellStyle(5, 3, resultBold);
sl.SetCellStyle(5, 4, resultBold);
sl.SetCellValue(linha, 1, "Total");
sl.SetCellValue(linha, 2, "=SUM(B6:B" + auxlinha + ")");
sl.SetCellValue(linha, 3, "=SUM(C6:C" + auxlinha + ")");
sl.SetCellStyle(linha, 1, resultBold);
sl.SetCellStyle(linha, 2, resultRed);
sl.SetCellStyle(linha, 3, resultRed);
sl.SetRowHeight(5, 38);
sl.SetColumnWidth(1, 39);
sl.SetRowHeight(1, 40);
sl.AutoFitColumn(2);
sl.AutoFitColumn(3);
sl.SetColumnWidth(4, 7);
sl.SetColumnStyle(1, alinhadoCentro);
sl.SetColumnStyle(2, alinhadoCentro);
sl.SetColumnStyle(3, alinhadoCentro);
sl.SetColumnStyle(4, alinhadoCentro);
string caminho = Server.MapPath(@"UploadFile\\" + "Relatorio_Estouro "
+ mes +
" " + ano + ".xlsx");
sl.SaveAs(caminho);
}
/// <summary>
/// Exports the poll result report.
/// </summary>
/// <param name="poll">The poll.</param>
/// <param name="pollResponse">The poll response.</param>
/// <param name="workSheetName">Name of the work sheet.</param>
/// <returns>
/// excel byte array
/// </returns>
public byte[] GetFailureExportReport(IEnumerable <AssetManager.Models.FailureView> failures, string workSheetName)
{
var textCenterBold = new SLStyle()
{
Alignment = new SLAlignment()
{
Horizontal = HorizontalAlignmentValues.Center, Vertical = VerticalAlignmentValues.Center
}, Font = { Bold = true }
};
var textCenter = new SLStyle()
{
Alignment = { Horizontal = HorizontalAlignmentValues.Center, Vertical = VerticalAlignmentValues.Center }
};
var textLeft = new SLStyle()
{
Alignment = { Horizontal = HorizontalAlignmentValues.Left }
};
var headColoumnStyle = new SLStyle()
{
Font = { FontSize = 12 }
};
SLStyle highlightStyle = new SLStyle()
{
Font = { FontSize = 12, Bold = true }
};
highlightStyle.SetWrapText(true);
highlightStyle.SetLeftBorder(BorderStyleValues.Thin, System.Drawing.Color.Gray);
highlightStyle.SetTopBorder(BorderStyleValues.Thin, System.Drawing.Color.Gray);
highlightStyle.SetRightBorder(BorderStyleValues.Thin, System.Drawing.Color.Gray);
highlightStyle.SetBottomBorder(BorderStyleValues.Thin, System.Drawing.Color.Gray);
textCenter.SetLeftBorder(BorderStyleValues.Thin, System.Drawing.Color.Gray);
textCenter.SetTopBorder(BorderStyleValues.Thin, System.Drawing.Color.Gray);
textCenter.SetRightBorder(BorderStyleValues.Thin, System.Drawing.Color.Gray);
textCenter.SetBottomBorder(BorderStyleValues.Thin, System.Drawing.Color.Gray);
textCenter.SetWrapText(true);
highlightStyle.Alignment.Horizontal = HorizontalAlignmentValues.Center;
highlightStyle.Alignment.Vertical = VerticalAlignmentValues.Center;
MemoryStream memoryStream = new MemoryStream();
using (SLDocument document = new SLDocument())
{
var currentSheetName = document.GetCurrentWorksheetName();
document.RenameWorksheet(currentSheetName, workSheetName);
document.SetCellValue(1, 1, string.Format("SFR Report {0}", DateTime.Now.ToString("dd/MM/yyyy")));
document.SetCellStyle(1, 1, textCenterBold);
document.MergeWorksheetCells(1, 1, 1, 16);
document.SetCellValue(2, 1, "SL");
document.SetColumnWidth(1, 4);
document.SetColumnWidth(2, 4);
document.SetColumnWidth(3, 3.84);
document.SetColumnWidth(4, 8);
document.SetColumnWidth(5, 8);
document.SetColumnWidth(6, 8);
document.SetColumnWidth(7, 8);
document.SetColumnWidth(8, 19.84);
document.SetColumnWidth(9, 10);
document.SetColumnWidth(10, 10);
document.SetColumnWidth(11, 9.75);
document.SetColumnWidth(12, 11);
document.SetColumnWidth(13, 9);
document.SetColumnWidth(14, 11);
document.SetColumnWidth(15, 9);
document.SetColumnWidth(16, 8.75);
//document.SetColumnWidth(17, 8.75);
document.SetCellValue(2, 2, "SFRNo");
document.SetCellValue(2, 3, "Reported");
document.SetCellValue(2, 4, "Chargeable / Non Chargeable");
document.SetCellValue(2, 5, "Station");
document.SetCellValue(2, 6, "Gear at Fault");
document.SetCellValue(2, 7, "Sub Gear at Fault");
document.SetCellValue(2, 8, "Brief Description");
document.SetCellValue(2, 9, "Cause of Failure");
document.SetCellValue(2, 10, "Sub Cause of Failure");
//document.SetCellValue(1, 7, "Train Loss Punctuality");
document.SetCellValue(2, 11, "Train Detained");
//document.SetCellValue(1, 9, "Failure Chargeable");
//document.SetCellValue(1, 8, "Department");
document.SetCellValue(2, 12, "Time of Occ urrence");
document.SetCellValue(2, 13, "Time Signal Main In formed");
document.SetCellValue(2, 14, "Time Signal Main Reached");
document.SetCellValue(2, 15, "Time Rectified");
document.SetCellValue(2, 16, "Dur ation");
//document.SetCellValue(2, 17, "Last Visit");
var pageSettings = document.GetPageSettings();
pageSettings.ScalePage(95);
pageSettings.PaperSize = SLPaperSizeValues.A4Paper;
pageSettings.Orientation = OrientationValues.Landscape;
pageSettings.HeaderMargin = 0.3;
pageSettings.TopMargin = 0.2;
pageSettings.BottomMargin = 0.2;
pageSettings.LeftMargin = 0.2;
pageSettings.RightMargin = 0.2;
pageSettings.FooterMargin = 0.3;
pageSettings.OddFooterText = "Designed and Developded by Krishna Infotech";
pageSettings.AppendOddFooter(SLHeaderFooterFormatCodeValues.Right);
document.SetPageSettings(pageSettings);
document.SetCellStyle(2, 1, 2, 16, highlightStyle);
int lastActiveRow = 3;
failures.ForEach(failure =>
{
document.SetCellValue(lastActiveRow, 1, lastActiveRow - 2);
document.SetCellValue(lastActiveRow, 2, failure.SFRNo);
document.SetCellValue(lastActiveRow, 3, failure.Reported);
document.SetCellValue(lastActiveRow, 4, failure.FailureChargeable ? "Yes" : "No");
document.SetCellValue(lastActiveRow, 5, failure.Station);
document.SetCellValue(lastActiveRow, 6, failure.GearFault);
document.SetCellValue(lastActiveRow, 7, failure.SubGearFault);
document.SetCellValue(lastActiveRow, 8, $"{failure.Description} ---- SSE:{failure.SSELastVisitOn.ToString()} JE:{failure.JELastVisitOn.ToString()} ESM: {failure.ESMLastVisitOn.ToString()}");
document.SetCellValue(lastActiveRow, 9, failure.CauseOfFailureValue);
document.SetCellValue(lastActiveRow, 10, failure.SubCauseOfFailureValue);
document.SetCellValue(lastActiveRow, 11, failure.TrainDetained);
document.SetCellValue(lastActiveRow, 12, failure.TimeOfOccurance.ToString());
document.SetCellValue(lastActiveRow, 13, failure.TimeSignalMainInformed.ToString());
document.SetCellValue(lastActiveRow, 14, failure.TimeSignalMainReached.ToString());
document.SetCellValue(lastActiveRow, 15, failure.TimeRectified.ToString());
document.SetCellValue(lastActiveRow, 16, failure.TotalDuration.HasValue ? Math.Round(TimeSpan.FromHours(failure.TotalDuration.Value).TotalMinutes).ToString() : string.Empty);
lastActiveRow++;
});
document.SetCellStyle(3, 1, lastActiveRow, 16, textCenter);
var footerIndex = failures.Count() + 6;
document.SetCellValue(footerIndex, 1, string.Format("Generated on {0}", DateTime.Now.ToString("dd/MM/yyyy")));
document.MergeWorksheetCells(footerIndex, 1, footerIndex, 3);
document.SetCellValue(footerIndex, 4, "This report is generated by SFR Software");
document.MergeWorksheetCells(footerIndex, 4, footerIndex, 9);
document.SaveAs(memoryStream);
return(memoryStream.ToArray());
}
}
