public void CanConvertXLColorToColorType()
{
var xlColor1 = XLColor.Red;
var xlColor2 = XLColor.FromIndex(20);
var xlColor3 = XLColor.FromTheme(XLThemeColor.Accent1);
var xlColor4 = XLColor.FromTheme(XLThemeColor.Accent2, 0.4);
var color1 = new ForegroundColor().FromClosedXMLColor <ForegroundColor>(xlColor1);
var color2 = new ForegroundColor().FromClosedXMLColor <ForegroundColor>(xlColor2);
var color3 = new BackgroundColor().FromClosedXMLColor <BackgroundColor>(xlColor3);
var color4 = new BackgroundColor().FromClosedXMLColor <BackgroundColor>(xlColor4);
Assert.AreEqual("FFFF0000", color1.Rgb.Value);
Assert.IsNull(color1.Indexed);
Assert.IsNull(color1.Theme);
Assert.IsNull(color1.Tint);
Assert.IsNull(color2.Rgb);
Assert.AreEqual(20, color2.Indexed.Value);
Assert.IsNull(color2.Theme);
Assert.IsNull(color2.Tint);
Assert.IsNull(color3.Rgb);
Assert.IsNull(color3.Indexed);
Assert.AreEqual(4, color3.Theme.Value);
Assert.IsNull(color3.Tint);
Assert.IsNull(color4.Rgb);
Assert.IsNull(color4.Indexed);
Assert.AreEqual(5, color4.Theme.Value);
Assert.AreEqual(0.4, color4.Tint.Value);
}
/// <summary>
/// Here we perform the actual convertion from OpenXML color to ClosedXML color.
/// </summary>
/// <param name="openXMLColor">OpenXML color. Must be either <see cref="ColorType"/> or <see cref="X14.ColorType"/>.
/// Since these types do not implement a common interface we use dynamic.</param>
/// <param name="colorCache">The dictionary containing parsed colors to optimize performance.</param>
/// <returns>The color in ClosedXML format.</returns>
private static XLColor ConvertToClosedXMLColor(dynamic openXMLColor, IDictionary <string, Drawing.Color> colorCache)
{
XLColor retVal = null;
if (openXMLColor != null)
{
if (openXMLColor.Rgb != null)
{
String htmlColor = "#" + openXMLColor.Rgb.Value;
Drawing.Color thisColor;
if (colorCache?.ContainsKey(htmlColor) ?? false)
{
thisColor = colorCache[htmlColor];
}
else
{
thisColor = ColorStringParser.ParseFromHtml(htmlColor);
colorCache?.Add(htmlColor, thisColor);
}
retVal = XLColor.FromColor(thisColor);
}
else if (openXMLColor.Indexed != null && openXMLColor.Indexed <= 64)
{
retVal = XLColor.FromIndex((Int32)openXMLColor.Indexed.Value);
}
else if (openXMLColor.Theme != null)
{
retVal = openXMLColor.Tint != null
? XLColor.FromTheme((XLThemeColor)openXMLColor.Theme.Value, openXMLColor.Tint.Value)
: XLColor.FromTheme((XLThemeColor)openXMLColor.Theme.Value);
}
}
return(retVal ?? XLColor.NoColor);
}
public void CanConvertXlColorToX14ColorType()
{
var xlColor1 = XLColor.Red;
var xlColor2 = XLColor.FromIndex(20);
var xlColor3 = XLColor.FromTheme(XLThemeColor.Accent1);
var xlColor4 = XLColor.FromTheme(XLThemeColor.Accent2, 0.4);
var color1 = new X14.AxisColor().FromClosedXMLColor <X14.AxisColor>(xlColor1);
var color2 = new X14.BorderColor().FromClosedXMLColor <X14.BorderColor>(xlColor2);
var color3 = new X14.FillColor().FromClosedXMLColor <X14.FillColor>(xlColor3);
var color4 = new X14.HighMarkerColor().FromClosedXMLColor <X14.HighMarkerColor>(xlColor4);
Assert.AreEqual("FFFF0000", color1.Rgb.Value);
Assert.IsNull(color1.Indexed);
Assert.IsNull(color1.Theme);
Assert.IsNull(color1.Tint);
Assert.IsNull(color2.Rgb);
Assert.AreEqual(20, color2.Indexed.Value);
Assert.IsNull(color2.Theme);
Assert.IsNull(color2.Tint);
Assert.IsNull(color3.Rgb);
Assert.IsNull(color3.Indexed);
Assert.AreEqual(4, color3.Theme.Value);
Assert.IsNull(color3.Tint);
Assert.IsNull(color4.Rgb);
Assert.IsNull(color4.Indexed);
Assert.AreEqual(5, color4.Theme.Value);
Assert.AreEqual(0.4, color4.Tint.Value);
}
/// <summary>
/// Here we perform the actual convertion from OpenXML color to ClosedXML color.
/// </summary>
/// <param name="openXMLColor">OpenXML color. Must be either <see cref="ColorType"/> or <see cref="X14.ColorType"/>.
/// Since these types do not implement a common interface we use dynamic.</param>
/// <param name="colorCache">The dictionary containing parsed colors to optimize performance.</param>
/// <returns>The color in ClosedXML format.</returns>
private static XLColor ConvertToClosedXMLColor(IColorTypeAdapter openXMLColor, IDictionary <string, Drawing.Color> colorCache)
{
XLColor retVal = null;
if (openXMLColor != null)
{
if (openXMLColor.Rgb != null)
{
String htmlColor = "#" + openXMLColor.Rgb.Value;
if (colorCache == null || !colorCache.TryGetValue(htmlColor, out Drawing.Color thisColor))
{
thisColor = ColorStringParser.ParseFromHtml(htmlColor);
colorCache?.Add(htmlColor, thisColor);
}
retVal = XLColor.FromColor(thisColor);
}
else if (openXMLColor.Indexed != null && openXMLColor.Indexed <= 64)
{
retVal = XLColor.FromIndex((Int32)openXMLColor.Indexed.Value);
}
else if (openXMLColor.Theme != null)
{
retVal = openXMLColor.Tint != null
? XLColor.FromTheme((XLThemeColor)openXMLColor.Theme.Value, openXMLColor.Tint.Value)
: XLColor.FromTheme((XLThemeColor)openXMLColor.Theme.Value);
}
}
return(retVal ?? XLColor.NoColor);
}
public void SetInsideBorderPreservesOutsideBorders()
{
using (var wb = new XLWorkbook())
{
var ws = wb.AddWorksheet();
ws.Cells("B2:C2").Style
.Border.SetOutsideBorder(XLBorderStyleValues.Thin)
.Border.SetOutsideBorderColor(XLColor.FromTheme(XLThemeColor.Accent1, 0.5));
//Check pre-conditions
Assert.AreEqual(XLBorderStyleValues.Thin, ws.Cell("B2").Style.Border.LeftBorder);
Assert.AreEqual(XLBorderStyleValues.Thin, ws.Cell("B2").Style.Border.RightBorder);
Assert.AreEqual(XLThemeColor.Accent1, ws.Cell("B2").Style.Border.LeftBorderColor.ThemeColor);
Assert.AreEqual(XLThemeColor.Accent1, ws.Cell("B2").Style.Border.RightBorderColor.ThemeColor);
ws.Range("B2:C2").Style.Border.SetInsideBorder(XLBorderStyleValues.None);
Assert.AreEqual(XLBorderStyleValues.Thin, ws.Cell("B2").Style.Border.LeftBorder);
Assert.AreEqual(XLBorderStyleValues.None, ws.Cell("B2").Style.Border.RightBorder);
Assert.AreEqual(XLBorderStyleValues.None, ws.Cell("C2").Style.Border.LeftBorder);
Assert.AreEqual(XLBorderStyleValues.Thin, ws.Cell("C2").Style.Border.RightBorder);
Assert.AreEqual(XLThemeColor.Accent1, ws.Cell("B2").Style.Border.LeftBorderColor.ThemeColor);
Assert.AreEqual(XLThemeColor.Accent1, ws.Cell("C2").Style.Border.RightBorderColor.ThemeColor);
}
}
public IXLRange ApplyDataInvalidCellFormat(IXLRange range)
{
range.Style.Border.DiagonalBorder = XLBorderStyleValues.Thin;
range.Style.Border.DiagonalUp = true;
range.Style.Border.DiagonalDown = true;
range.Style.Fill.BackgroundColor = XLColor.FromTheme(XLThemeColor.Text2);
return(range);
}
public IXLRange ApplyHeaderRcCodeFormat(IXLRange range, HeaderType axis, bool bIsOpenAspect = false)
{
range.DataType = XLDataType.Text;
range.Style.Alignment.WrapText = true;
range.Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
range.Style.Fill.BackgroundColor = XLColor.FromTheme(XLThemeColor.Background2, -0.2);
range.Style.Font.FontColor = XLColor.FromTheme(XLThemeColor.Text1);
range.Style.NumberFormat.NumberFormatId = 49; // @
range.DataType = XLDataType.Text;
return(range);
}
private static void FillRow(IXLRow row1)
{
row1.Cell(1).Style.Fill.SetBackgroundColor(XLColor.Red);
row1.Cell(2).Style.Fill.SetBackgroundColor(XLColor.FromArgb(1, 1, 1));
row1.Cell(3).Style.Fill.SetBackgroundColor(XLColor.FromHtml("#CCCCCC"));
row1.Cell(4).Style.Fill.SetBackgroundColor(XLColor.FromIndex(26));
row1.Cell(5).Style.Fill.SetBackgroundColor(XLColor.FromColor(Color.MediumSeaGreen));
row1.Cell(6).Style.Fill.SetBackgroundColor(XLColor.FromName("Blue"));
row1.Cell(7).Style.Fill.SetBackgroundColor(XLColor.FromTheme(XLThemeColor.Accent3));
row1.Cell(2).AddConditionalFormat().WhenEquals("=" + row1.FirstCell().CellRight(6).Address.ToStringRelative()).Fill.SetBackgroundColor(XLColor.Blue);
}
public IXLRange ApplyTitleHeaderFormat(IXLRange range)
{
range.Merge();
range.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
range.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
range.Style.Alignment.WrapText = true;
range.Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
range.Style.Fill.BackgroundColor = XLColor.FromTheme(XLThemeColor.Background2);
range.Style.Font.Bold = true;
range.Style.Font.FontColor = XLColor.FromTheme(XLThemeColor.Text1);
range.Style.NumberFormat.NumberFormatId = 49; // @
range.DataType = XLDataType.Text;
return(range);
}
public IXLRange ApplyTitleFormat(IXLRange range)
{
if (range.ColumnCount() > 1)
{
range.Merge();
}
range.Style.Alignment.WrapText = true;
range.Style.Border.OutsideBorder = XLBorderStyleValues.None;
range.Style.Fill.BackgroundColor = XLColor.FromTheme(XLThemeColor.Accent1);
range.Style.Font.FontColor = XLColor.FromTheme(XLThemeColor.Text1);
range.Style.Font.Bold = true;
range.Style.NumberFormat.NumberFormatId = 49; // @
range.DataType = XLDataType.Text;
return(range);
}
public static void ExportDataToExcel(string file_name, string report_title, DataTable data, HttpContext context, string[] column_names)
{
var work_book = new XLWorkbook();
var work_sheet = work_book.Worksheets.Add("Sheet 1");
string export_path = context.Server.MapPath("~/exports/data/");
int counter = 0;
var title_styles = work_book.Style;
// Background color for the cell
title_styles.Fill.BackgroundColor = XLColor.FromTheme(XLThemeColor.Accent1, 0.5);
// Font configuration
title_styles.Font.Bold = true;
title_styles.Font.FontColor = XLColor.White;
title_styles.Font.FontSize = 30;
// Text Alignment
title_styles.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
// Adding the Title in the Cell A1,1
work_sheet.Cell(1, 1).Value = report_title;
// Get workbook range for Title
var title_range = work_sheet.Range(1, 1, 3, 24);
// Merge the cells in the above range to form the title.
title_range.Merge();
// Apply the styles defined before
title_range.Style = title_styles;
title_styles.Font.FontSize = 14;
// Manually adding the column names, as the data adding functions don't display the column names.
for (counter = 0; counter < column_names.Length; counter++)
{
work_sheet.Cell(4, counter + 1).Value = column_names[counter];
work_sheet.Cell(4, counter + 1).Style.Font.Bold = true;
work_sheet.Cell(4, counter + 1).Style.Font.FontSize = 16;
}
// Adding the datatable to the selected range
work_sheet.Cell(5, 1).Value = data.AsEnumerable();
// Save the workbook to the below path
work_book.SaveAs(export_path + file_name);
}
private void CreateHeader(List <GridColumnBase> columNameList, IXLWorksheet ws)
{
foreach (GridColumnBase cName in columNameList)
{
ws.Cell($"{Columns[columNameList.IndexOf(cName)]}{1}").Value = cName.Name;
ws.Column(Columns[columNameList.IndexOf(cName)]).CellsUsed().SetDataType(GetDataType(cName));
}
var rngTable = ws.Range($"{Columns[0]}{1}:{Columns.Last()}{1}");
var rngHeaders = rngTable.Range($"{Columns[0]}1:{Columns.Last()}1"); // The address is relative to rngTable (NOT the worksheet)
rngHeaders.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
rngHeaders.Style.Font.Bold = true;
rngHeaders.Style.Fill.BackgroundColor = XLColor.FromTheme(XLThemeColor.Accent1, 0.5);
rngHeaders.Style.Border.BottomBorder = XLBorderStyleValues.Thick;
rngTable.Style.Border.BottomBorder = XLBorderStyleValues.Thin;
}
// Public
// Private
#endregion
#region Properties
// Public
// Private
// Override
#endregion
#region Events
// Public
// Private
// Override
#endregion
#region Methods
// Public
public void Create(String filePath)
{
var wb = new XLWorkbook();
var wsRed = wb.Worksheets.Add("Red").SetTabColor(XLColor.Red);
var wsAccent3 = wb.Worksheets.Add("Accent3").SetTabColor(XLColor.FromTheme(XLThemeColor.Accent3));
var wsIndexed = wb.Worksheets.Add("Indexed");
wsIndexed.TabColor = XLColor.FromIndex(24);
var wsArgb = wb.Worksheets.Add("Argb");
wsArgb.TabColor = XLColor.FromArgb(23, 23, 23);
wb.SaveAs(filePath);
}
/// <summary>
/// https://github.com/ClosedXML/ClosedXML/wiki/Tab-Colors
/// </summary>
/// <returns></returns>
public ActionResult TabColors()
{
GetInstance("Red", out XLWorkbook wb, out IXLWorksheet ws);
ws.SetTabColor(XLColor.Red);
var wsAccent3 = wb.Worksheets.Add("Accent3");
wsAccent3.SetTabColor(XLColor.FromTheme(XLThemeColor.Accent3));
var wsIndexed = wb.Worksheets.Add("Indexed");
wsIndexed.TabColor = XLColor.FromIndex(24);
var wsArgb = wb.Worksheets.Add("Argb");
wsArgb.TabColor = XLColor.FromArgb(23, 23, 23);
return(ExportExcel(wb, "TabColors"));
}
public void ToStringFormatString(string expected, string format)
{
using var wb = new XLWorkbook();
var ws = wb.AddWorksheet("Sheet1");
var c = ws.FirstCell().CellBelow(2).CellRight(3);
var formula = "YEAR(DATE(2018, 1, 1))";
c.FormulaA1 = formula;
var numberFormat = "0000.00";
c.Style.NumberFormat.Format = numberFormat;
c.Style.Font.FontColor = XLColor.Red;
c.Style.Fill.BackgroundColor = XLColor.FromTheme(XLThemeColor.Accent5);
Assert.AreEqual(expected, c.ToString(format));
Assert.Throws <FormatException>(() => c.ToString("dummy"));
}
public void CopyingColumns()
{
var wb = new XLWorkbook();
IXLWorksheet ws = wb.Worksheets.Add("Sheet");
IXLColumn column1 = ws.Column(1);
column1.Cell(1).Style.Fill.SetBackgroundColor(XLColor.Red);
column1.Cell(2).Style.Fill.SetBackgroundColor(XLColor.FromArgb(1, 1, 1));
column1.Cell(3).Style.Fill.SetBackgroundColor(XLColor.FromHtml("#CCCCCC"));
column1.Cell(4).Style.Fill.SetBackgroundColor(XLColor.FromIndex(26));
column1.Cell(5).Style.Fill.SetBackgroundColor(XLColor.FromColor(Color.MediumSeaGreen));
column1.Cell(6).Style.Fill.SetBackgroundColor(XLColor.FromName("Blue"));
column1.Cell(7).Style.Fill.SetBackgroundColor(XLColor.FromTheme(XLThemeColor.Accent3));
ws.Cell(1, 2).Value = column1;
ws.Cell(1, 3).Value = column1.Column(1, 7);
IXLColumn column2 = ws.Column(2);
Assert.AreEqual(XLColor.Red, column2.Cell(1).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromArgb(1, 1, 1), column2.Cell(2).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromHtml("#CCCCCC"), column2.Cell(3).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromIndex(26), column2.Cell(4).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromColor(Color.MediumSeaGreen),
column2.Cell(5).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromName("Blue"), column2.Cell(6).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromTheme(XLThemeColor.Accent3), column2.Cell(7).Style.Fill.BackgroundColor);
IXLColumn column3 = ws.Column(3);
Assert.AreEqual(XLColor.Red, column3.Cell(1).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromArgb(1, 1, 1), column3.Cell(2).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromHtml("#CCCCCC"), column3.Cell(3).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromIndex(26), column3.Cell(4).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromColor(Color.MediumSeaGreen),
column3.Cell(5).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromName("Blue"), column3.Cell(6).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromTheme(XLThemeColor.Accent3), column3.Cell(7).Style.Fill.BackgroundColor);
}
public HttpResponseMessage getTemplate()
{
var template = new XLWorkbook();
IXLWorksheet ws = template.AddWorksheet(fileName.Replace(".xlsx", ""));
var tittle = ws.Range(1, 1, 2, columns.Length);
tittle.Cell(1, 1).Value = fileName.Replace(".xlsx", "").ToUpper();//"Base de Datos Nacional de Capital Humano";
tittle.Cell(1, 1).Style.Font.Bold = true;
tittle.Cell(1, 1).Style.Fill.BackgroundColor = XLColor.FromTheme(XLThemeColor.Accent1);
tittle.Cell(1, 1).Style.Font.FontName = "Bahnschrift SemiLight";
tittle.Cell(1, 1).Style.Font.FontSize = 20;
tittle.Cell(1, 1).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
tittle.Merge();
for (int i = 0; i < columns.Length; i++)
{
ws.Column(i + 1).Width = 13;
ws.Cell(headerin, i + 1).Value = columns[i].headers;
/*if(columns[i].typeofcol==typeof(double))
* for (int j = headerin + 1; j < 1000; j++)
* {
* ws.Cell(j, i + 1).Style.NumberFormat.Format = "#,##0.00";
* var validation = ws.Cell(j, i + 1).DataValidation;
* validation.Decimal.Between(0, 9999999);
* validation.InputTitle = "Columna Numerica";
* validation.InputMessage = "Por favor ingresar solo numeros.";
* validation.ErrorStyle = XLErrorStyle.Warning;
* validation.ErrorTitle = "Error de tipo de valor";
* validation.ErrorMessage = "Esta celda debe ser tipo numerica.";
* }*/
ws.Cell(headerin, i + 1).Style.Alignment.WrapText = true;
ws.Cell(headerin, i + 1).Style.Font.Bold = true;
ws.Cell(headerin, i + 1).Style.Fill.BackgroundColor = XLColor.FromTheme(XLThemeColor.Accent1);
}
valid = true;
return(toResponse(template));
}
public void CopyingRows()
{
var wb = new XLWorkbook();
IXLWorksheet ws = wb.Worksheets.Add("Sheet");
IXLRow row1 = ws.Row(1);
row1.Cell(1).Style.Fill.SetBackgroundColor(XLColor.Red);
row1.Cell(2).Style.Fill.SetBackgroundColor(XLColor.FromArgb(1, 1, 1));
row1.Cell(3).Style.Fill.SetBackgroundColor(XLColor.FromHtml("#CCCCCC"));
row1.Cell(4).Style.Fill.SetBackgroundColor(XLColor.FromIndex(26));
row1.Cell(5).Style.Fill.SetBackgroundColor(XLColor.FromKnownColor(KnownColor.MediumSeaGreen));
row1.Cell(6).Style.Fill.SetBackgroundColor(XLColor.FromName("Blue"));
row1.Cell(7).Style.Fill.SetBackgroundColor(XLColor.FromTheme(XLThemeColor.Accent3));
ws.Cell(2, 1).Value = row1;
ws.Cell(3, 1).Value = row1.Row(1, 7);
IXLRow row2 = ws.Row(2);
Assert.AreEqual(XLColor.Red, row2.Cell(1).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromArgb(1, 1, 1), row2.Cell(2).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromHtml("#CCCCCC"), row2.Cell(3).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromIndex(26), row2.Cell(4).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromKnownColor(KnownColor.MediumSeaGreen), row2.Cell(5).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromName("Blue"), row2.Cell(6).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromTheme(XLThemeColor.Accent3), row2.Cell(7).Style.Fill.BackgroundColor);
IXLRow row3 = ws.Row(3);
Assert.AreEqual(XLColor.Red, row3.Cell(1).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromArgb(1, 1, 1), row3.Cell(2).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromHtml("#CCCCCC"), row3.Cell(3).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromIndex(26), row3.Cell(4).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromKnownColor(KnownColor.MediumSeaGreen), row3.Cell(5).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromName("Blue"), row3.Cell(6).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromTheme(XLThemeColor.Accent3), row3.Cell(7).Style.Fill.BackgroundColor);
}
public IXLRange ApplyHeaderFormat(IXLRange range, HeaderType axis, bool bIsOpenAspect = false)
{
if (range.ColumnCount() > 1 || range.RowCount() > 1)
{
range.Merge();
}
if (axis == HeaderType.XAxis && range.ColumnCount() > 1)
{
range.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
}
else if (axis == HeaderType.YAxis && range.RowCount() > 1)
{
range.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
}
range.Style.Alignment.WrapText = axis != HeaderType.YAxis;
range.Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
range.Style.Fill.BackgroundColor = bIsOpenAspect ? XLColor.FromTheme(XLThemeColor.Background2, 0.4) : XLColor.FromTheme(XLThemeColor.Background2);
range.Style.Font.FontColor = XLColor.FromTheme(XLThemeColor.Text1);
range.Style.NumberFormat.NumberFormatId = 49; // @
range.DataType = XLDataType.Text;
return(range);
}
public void CopyingRows()
{
var wb = new XLWorkbook();
IXLWorksheet ws = wb.Worksheets.Add("Sheet");
IXLRow row1 = ws.Row(1);
FillRow(row1);
ws.Cell(2, 1).Value = row1;
ws.Cell(3, 1).Value = row1.Row(1, 7);
IXLRow row2 = ws.Row(2);
Assert.AreEqual(XLColor.Red, row2.Cell(1).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromArgb(1, 1, 1), row2.Cell(2).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromHtml("#CCCCCC"), row2.Cell(3).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromIndex(26), row2.Cell(4).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromColor(Color.MediumSeaGreen), row2.Cell(5).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromName("Blue"), row2.Cell(6).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromTheme(XLThemeColor.Accent3), row2.Cell(7).Style.Fill.BackgroundColor);
IXLRow row3 = ws.Row(3);
Assert.AreEqual(XLColor.Red, row3.Cell(1).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromArgb(1, 1, 1), row3.Cell(2).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromHtml("#CCCCCC"), row3.Cell(3).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromIndex(26), row3.Cell(4).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromColor(Color.MediumSeaGreen), row3.Cell(5).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromName("Blue"), row3.Cell(6).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromTheme(XLThemeColor.Accent3), row3.Cell(7).Style.Fill.BackgroundColor);
Assert.AreEqual(3, ws.ConditionalFormats.Count());
Assert.IsTrue(ws.ConditionalFormats.Single(x => x.Range.RangeAddress.ToStringRelative() == "B1:B1").Values.Any(v => v.Value.Value == "G1" && v.Value.IsFormula));
Assert.IsTrue(ws.ConditionalFormats.Single(x => x.Range.RangeAddress.ToStringRelative() == "B2:B2").Values.Any(v => v.Value.Value == "G2" && v.Value.IsFormula));
Assert.IsTrue(ws.ConditionalFormats.Single(x => x.Range.RangeAddress.ToStringRelative() == "B3:B3").Values.Any(v => v.Value.Value == "G3" && v.Value.IsFormula));
}
public void GerarPDFPontoo(String mes)
{
DAOCaminho daoCaminho = new DAOCaminho();
string caminho = daoCaminho.CaminhoPonto();
//PADRÂO
var wb = new XLWorkbook();
var ws = wb.Worksheets.Add("Follha de ponto ");
//TITULO
ws.Cell("B2").Value = "Folha de ponto do mês " + mes;//TITULO
//PRIMEIRAS LINHAS
ws.Cell("B4").Value = "Data";
ws.Cell("C4").Value = "Chegada";
ws.Cell("D4").Value = "Saída almoço";
ws.Cell("E4").Value = "Volta almoço";
ws.Cell("F4").Value = "Saída";
ws.Cell("G4").Value = "Nome";
//AJUSTAR A NUMERACAO
var linha = 5;
//PEGAR PONTO DO BANCO
DAOPonto dao = new DAOPonto();
List <PontoModel> ListaPonto = new List <PontoModel>();
ListaPonto = dao.ListarPorMes(mes);
//COLOCAR DATA
linha = 5;
foreach (var item in ListaPonto)
{
ws.Cell("B" + linha.ToString()).Value = item.Data.ToString();
linha++;
}
//COLOCAR HORA1 (CHEGADA)
linha = 5;
foreach (var item in ListaPonto)
{
ws.Cell("C" + linha.ToString()).Value = item.Hora1.ToString();
linha++;
}
//COLOCAR HORA2
linha = 5;
foreach (var item in ListaPonto)
{
ws.Cell("D" + linha.ToString()).Value = item.Hora2.ToString();
linha++;
}
//COLOCAR HORA3
linha = 5;
foreach (var item in ListaPonto)
{
ws.Cell("E" + linha.ToString()).Value = item.Hora3.ToString();
linha++;
}
//COLOCAR HORA4
linha = 5;
foreach (var item in ListaPonto)
{
ws.Cell("F" + linha.ToString()).Value = item.Hora4.ToString();
linha++;
}
//COLOCAR HORA4
linha = 5;
foreach (var item in ListaPonto)
{
ws.Cell("G" + linha.ToString()).Value = item.Nome.ToString();
linha++;
}
var rngTable = ws.Range("B2:G35");
int LinhaParaBorda = 4;
//TUDO COM BORDA
for (int i = 1; i <= 61; i++)
{
rngTable = ws.Range("B" + LinhaParaBorda.ToString());
rngTable.Style.Border.OutsideBorder = XLBorderStyleValues.Thin;//BORDA fina
LinhaParaBorda = LinhaParaBorda + 1;
}
LinhaParaBorda = 4;
//TUDO COM BORDA
for (int i = 1; i <= 61; i++)
{
rngTable = ws.Range("C" + LinhaParaBorda.ToString());
rngTable.Style.Border.OutsideBorder = XLBorderStyleValues.Thin;//BORDA fina
LinhaParaBorda = LinhaParaBorda + 1;
}
LinhaParaBorda = 4;
//TUDO COM BORDA
for (int i = 1; i <= 61; i++)
{
rngTable = ws.Range("D" + LinhaParaBorda.ToString());
rngTable.Style.Border.OutsideBorder = XLBorderStyleValues.Thin;//BORDA fina
LinhaParaBorda = LinhaParaBorda + 1;
}
LinhaParaBorda = 4;
//TUDO COM BORDA
for (int i = 1; i <= 61; i++)
{
rngTable = ws.Range("E" + LinhaParaBorda.ToString());
rngTable.Style.Border.OutsideBorder = XLBorderStyleValues.Thin;//BORDA fina
LinhaParaBorda = LinhaParaBorda + 1;
}
LinhaParaBorda = 4;
//TUDO COM BORDA
for (int i = 1; i <= 61; i++)
{
rngTable = ws.Range("F" + LinhaParaBorda.ToString());
rngTable.Style.Border.OutsideBorder = XLBorderStyleValues.Thin;//BORDA fina
LinhaParaBorda = LinhaParaBorda + 1;
}
LinhaParaBorda = 4;
//TUDO COM BORDA
for (int i = 1; i <= 61; i++)
{
rngTable = ws.Range("G" + LinhaParaBorda.ToString());
rngTable.Style.Border.OutsideBorder = XLBorderStyleValues.Thin;//BORDA fina
LinhaParaBorda = LinhaParaBorda + 1;
}
//TITULO
rngTable = ws.Range("B2:G3"); //SELECIONAR
rngTable.Row(1).Merge(); //JUNTAR
rngTable.Style.Border.OutsideBorder = XLBorderStyleValues.Thin; //BORDA fina
rngTable.Style.Fill.BackgroundColor = XLColor.FromTheme(XLThemeColor.Accent1); //COR FUNDO
rngTable.Style.Font.FontColor = XLColor.White; //COR FONTE
rngTable.Style.Font.Bold = true; //FONT BOLD
rngTable.Style.Font.FontSize = 15; //FONT TAmANHO
rngTable.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; //ALINHAR CENTRO
rngTable.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center; //ALINHAR CENTRO
ws.Columns(2, 6).AdjustToContents(); //AJUSTAR LARGURA CELULA
//LINHAS CIMA
rngTable = ws.Range("B4:G4");
rngTable.Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
rngTable.Style.Fill.BackgroundColor = XLColor.FromTheme(XLThemeColor.Accent1);
rngTable.Style.Font.FontColor = XLColor.White;
rngTable.Style.Font.Bold = true;
rngTable.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
////TAMANHO DA PLANILHA
//rngTable = ws.Range("B2:G35");
//rngTable.Style.Border.OutsideBorder = XLBorderStyleValues.Medium;
//SALVAR PLANILHA
//wb.SaveAs(@"C:\Users\keven.barauna\Desktop\Roll Festas Versão Alpha Solutis\Ponto\PontoPlanilhaDePonto" + mes + ".xlsx");
wb.SaveAs(@"" + caminho + @"\PontoPlanilha.xlsx");
wb.Dispose();
}
private static void GenerateExcel(JArray input)
{
var workbook = new XLWorkbook();
workbook.Use1904DateSystem = true;
var ws = workbook.Worksheets.Add("Coupons info");
ws.Column(1).Width = 16;
ws.Column(2).Width = 65;
ws.Column(3).Width = 30;
ws.Column(4).Width = 16;
ws.Column(5).Width = 10;
var columns = new[] { "ISIN", "Эмитент", "Корпоративное действие", "Дата", "Размер" };
foreach (var j in Enumerable.Range(0, columns.Length))
{
ws.Cell(1, j + 1).Value = columns[j];
ws.Cell(1, j + 1).Style.Fill.BackgroundColor = XLColor.FromTheme(XLThemeColor.Accent6);
}
var data = new List <List <JToken> >();
foreach (var bond in input)
{
var corpActions = bond["corp_actions"]?.ToArray();
if (corpActions == null || corpActions.Length == 0)
{
continue;
}
foreach (var c in corpActions)
{
try
{
if (!Payments.Contains(c["corp_action_type"]["code"].ToString()))
{
continue;
}
if (c["corp_action_type"]["name_en"].ToString() == "Interest Payment")
{
data.Add(new List <JToken>()
{
bond["isin"], bond["issuer"]["name_full"],
c["corp_action_type"]["name"], c["action_date_plan"], c["coupon"]["size"]
});
}
else if (c["corp_action_type"]["name_en"].ToString() == "Principal repayment")
{
data.Add(new List <JToken>()
{
bond["isin"], bond["issuer"]["name_full"],
c["corp_action_type"]["name"], c["action_date_plan"], c["repayment"]["size_cur"]
});
}
} catch (Exception ex)
{
Console.WriteLine(ex);
continue;
}
}
}
for (int i = 0; i < data.Count; i++)
{
for (int j = 0; j < columns.Length; j++)
{
ws.Cell(i + 2, j + 1).SetValue <string>(Convert.ToString(data[i][j]));
ws.Cell(i + 2, j + 1).Style.Fill.BackgroundColor = i % 2 == 0 ? XLColor.FromTheme(XLThemeColor.Accent6, 0.5) : XLColor.FromTheme(XLThemeColor.Accent6, 0.8);
}
}
ws.RangeUsed().SetAutoFilter();
workbook.SaveAs(FileName);
System.Diagnostics.Process.Start(FileName);
}
public void Create(String filePath)
{
var wb = new XLWorkbook();
var ws = wb.Worksheets.Add("Using Colors");
Int32 ro = 0;
// From Known color
ws.Cell(++ro, 1).Style.Fill.BackgroundColor = XLColor.Red;
ws.Cell(ro, 2).Value = "XLColor.Red";
// From Color not so known
ws.Cell(++ro, 1).Style.Fill.BackgroundColor = XLColor.Byzantine;
ws.Cell(ro, 2).Value = "XLColor.Byzantine";
ro++;
// FromArgb(Int32 argb) using Hex notation
ws.Cell(++ro, 1).Style.Fill.BackgroundColor = XLColor.FromArgb(0xFF00FF);
ws.Cell(ro, 2).Value = "XLColor.FromArgb(0xFF00FF)";
// FromArgb(Int32 argb) using an integer (you need to convert the hex value to an int)
ws.Cell(++ro, 1).Style.Fill.BackgroundColor = XLColor.FromArgb(16711935);
ws.Cell(ro, 2).Value = "XLColor.FromArgb(16711935)";
// FromArgb(Int32 r, Int32 g, Int32 b)
ws.Cell(++ro, 1).Style.Fill.BackgroundColor = XLColor.FromArgb(255, 0, 255);
ws.Cell(ro, 2).Value = "XLColor.FromArgb(255, 0, 255)";
// FromArgb(Int32 a, Int32 r, Int32 g, Int32 b)
// Note: Excel ignores the alpha value
ws.Cell(++ro, 1).Style.Fill.BackgroundColor = XLColor.FromArgb(0, 255, 0, 255);
ws.Cell(ro, 2).Value = "XLColor.FromArgb(0, 255, 0, 255)";
ro++;
// FromColor(Color color)
ws.Cell(++ro, 1).Style.Fill.BackgroundColor = XLColor.FromColor(Color.Red);
ws.Cell(ro, 2).Value = "XLColor.FromColor(Color.Red)";
ro++;
// FromHtml(String htmlColor)
ws.Cell(++ro, 1).Style.Fill.BackgroundColor = XLColor.FromHtml("#FF996515");
ws.Cell(ro, 2).Value = "XLColor.FromHtml(\"#FF996515\")";
ro++;
// FromIndex(Int32 indexedColor)
ws.Cell(++ro, 1).Style.Fill.BackgroundColor = XLColor.FromIndex(25);
ws.Cell(ro, 2).Value = "XLColor.FromIndex(25)";
ro++;
// FromKnownColor(KnownColor knownColor)
ws.Cell(++ro, 1).Style.Fill.BackgroundColor = XLColor.FromKnownColor(KnownColor.Plum);
ws.Cell(ro, 2).Value = "XLColor.FromKnownColor(KnownColor.Plum)";
ro++;
// FromName(String colorName)
ws.Cell(++ro, 1).Style.Fill.BackgroundColor = XLColor.FromName("PowderBlue");
ws.Cell(ro, 2).Value = "XLColor.FromName(\"PowderBlue\")";
ro++;
// From Theme color
ws.Cell(++ro, 1).Style.Fill.BackgroundColor = XLColor.FromTheme(XLThemeColor.Accent1);
ws.Cell(ro, 2).Value = "XLColor.FromTheme(XLThemeColor.Accent1)";
// From Theme color with tint
ws.Cell(++ro, 1).Style.Fill.BackgroundColor = XLColor.FromTheme(XLThemeColor.Accent1, 0.5);
ws.Cell(ro, 2).Value = "XLColor.FromTheme(XLThemeColor.Accent1, 0.5)";
ws.Columns().AdjustToContents();
wb.SaveAs(filePath);
}
public ActionResult GerarExcel(int?txtCliente, DateTime txtDataInicio, DateTime txtDataFim, StatusContaReceber?txtStatus)
{
try
{
if (TempData["txtCliente"] != null)
{
txtCliente = (int)TempData["txtCliente"];
}
if (TempData["txtDataInicio"] != null)
{
txtDataInicio = (DateTime)TempData["txtDataInicio"];
}
if (TempData["txtDataFim"] != null)
{
txtDataFim = (DateTime)TempData["txtDataFim"];
}
if (TempData["txtStatus"] != null)
{
txtStatus = (StatusContaReceber)TempData["txtStatus"];
}
var relatorio = _contas.ObterTodos().Where(x => x.ClienteID == 0);
if (txtCliente != null && txtDataFim != null && txtDataFim != null && txtStatus == null)
{
relatorio = _contas.ObterTodos().Where(x => x.ClienteID == txtCliente && x.DataVencimento.Date >= txtDataInicio && x.DataVencimento.Date <= txtDataFim);
}
if (txtCliente == null && txtDataInicio != null && txtDataFim != null && txtStatus == null)
{
relatorio = _contas.ObterTodos().Where(x => x.DataVencimento.Date >= txtDataInicio && x.DataVencimento.Date <= txtDataFim);
}
if (txtCliente == null && txtDataInicio != null && txtDataFim != null && txtStatus != null)
{
relatorio = _contas.ObterTodos().Where(x => x.DataVencimento.Date >= txtDataInicio && x.DataVencimento.Date <= txtDataFim && x.Status == txtStatus);
}
if (txtCliente != null && txtDataInicio != null && txtDataFim != null && txtStatus != null)
{
relatorio = _contas.ObterTodos().Where(x => x.ClienteID == txtCliente && x.DataVencimento.Date >= txtDataInicio && x.DataVencimento.Date <= txtDataFim && x.Status == txtStatus);
}
using (var workbook = new XLWorkbook())
{
var worksheet = workbook.Worksheets.Add("ContasReceber");
var currentRow = 1;
worksheet.Cell(currentRow, 1).Value = "DATA DE CADASTRO";
worksheet.Cell(currentRow, 1).Style.Font.Bold = true;
worksheet.Cell(currentRow, 1).Style.Fill.BackgroundColor = XLColor.FromTheme(XLThemeColor.Accent1, 0.5);
worksheet.Cell(currentRow, 2).Value = "DATA COMPETÊNCIA";
worksheet.Cell(currentRow, 2).Style.Font.Bold = true;
worksheet.Cell(currentRow, 2).Style.Fill.BackgroundColor = XLColor.FromTheme(XLThemeColor.Accent1, 0.5);
worksheet.Cell(currentRow, 3).Value = "DATA VENCIMENTO";
worksheet.Cell(currentRow, 3).Style.Font.Bold = true;
worksheet.Cell(currentRow, 3).Style.Fill.BackgroundColor = XLColor.FromTheme(XLThemeColor.Accent1, 0.5);
worksheet.Cell(currentRow, 4).Value = "DATA PAGAMENTO";
worksheet.Cell(currentRow, 4).Style.Font.Bold = true;
worksheet.Cell(currentRow, 4).Style.Fill.BackgroundColor = XLColor.FromTheme(XLThemeColor.Accent1, 0.5);
worksheet.Cell(currentRow, 5).Value = "CATEGORIA";
worksheet.Cell(currentRow, 5).Style.Font.Bold = true;
worksheet.Cell(currentRow, 5).Style.Fill.BackgroundColor = XLColor.FromTheme(XLThemeColor.Accent1, 0.5);
worksheet.Cell(currentRow, 6).Value = "SUB-CATEGORIA";
worksheet.Cell(currentRow, 6).Style.Font.Bold = true;
worksheet.Cell(currentRow, 6).Style.Fill.BackgroundColor = XLColor.FromTheme(XLThemeColor.Accent1, 0.5);
worksheet.Cell(currentRow, 7).Value = "CENTRO DE CUSTO";
worksheet.Cell(currentRow, 7).Style.Font.Bold = true;
worksheet.Cell(currentRow, 7).Style.Fill.BackgroundColor = XLColor.FromTheme(XLThemeColor.Accent1, 0.5);
worksheet.Cell(currentRow, 8).Value = "CLIENTE";
worksheet.Cell(currentRow, 8).Style.Font.Bold = true;
worksheet.Cell(currentRow, 8).Style.Fill.BackgroundColor = XLColor.FromTheme(XLThemeColor.Accent1, 0.5);
worksheet.Cell(currentRow, 9).Value = "DESCRIÇÃO";
worksheet.Cell(currentRow, 9).Style.Font.Bold = true;
worksheet.Cell(currentRow, 9).Style.Fill.BackgroundColor = XLColor.FromTheme(XLThemeColor.Accent1, 0.5);
worksheet.Cell(currentRow, 10).Value = "STATUS";
worksheet.Cell(currentRow, 10).Style.Font.Bold = true;
worksheet.Cell(currentRow, 10).Style.Fill.BackgroundColor = XLColor.FromTheme(XLThemeColor.Accent1, 0.5);
worksheet.Cell(currentRow, 11).Value = "VALOR";
worksheet.Cell(currentRow, 11).Style.Font.Bold = true;
worksheet.Cell(currentRow, 11).Style.Fill.BackgroundColor = XLColor.FromTheme(XLThemeColor.Accent1, 0.5);
worksheet.Cell(currentRow, 12).Value = "JUROS";
worksheet.Cell(currentRow, 12).Style.Font.Bold = true;
worksheet.Cell(currentRow, 12).Style.Fill.BackgroundColor = XLColor.FromTheme(XLThemeColor.Accent1, 0.5);
worksheet.Cell(currentRow, 13).Value = "MULTA";
worksheet.Cell(currentRow, 13).Style.Font.Bold = true;
worksheet.Cell(currentRow, 13).Style.Fill.BackgroundColor = XLColor.FromTheme(XLThemeColor.Accent1, 0.5);
worksheet.Cell(currentRow, 14).Value = "DESCONTOS";
worksheet.Cell(currentRow, 14).Style.Font.Bold = true;
worksheet.Cell(currentRow, 14).Style.Fill.BackgroundColor = XLColor.FromTheme(XLThemeColor.Accent1, 0.5);
worksheet.Cell(currentRow, 15).Value = "VALOR RECEBIDO";
worksheet.Cell(currentRow, 15).Style.Font.Bold = true;
worksheet.Cell(currentRow, 15).Style.Fill.BackgroundColor = XLColor.FromTheme(XLThemeColor.Accent1, 0.5);
foreach (var rel in relatorio)
{
currentRow++;
worksheet.Cell(currentRow, 1).Value = rel.DataCadastro.ToString("dd/MM/yyyy");
worksheet.Cell(currentRow, 2).Value = rel.Competencia.ToString("MM/yyyy");
worksheet.Cell(currentRow, 3).Value = rel.DataVencimento.ToString("dd/MM/yyyy");
worksheet.Cell(currentRow, 4).Value = rel.DataPagamento;
worksheet.Cell(currentRow, 5).Value = rel.Categoria.Nome ?? " - ";
worksheet.Cell(currentRow, 6).Value = rel.SubCategoria.Nome ?? " - ";
worksheet.Cell(currentRow, 7).Value = rel.CentroDeCusto.Nome ?? " - ";
worksheet.Cell(currentRow, 8).Value = rel.Cliente.RazaoSocial ?? " - ";
worksheet.Cell(currentRow, 9).Value = rel.Descricao ?? " - ";
worksheet.Cell(currentRow, 10).Value = rel.Status.ToString() ?? " - ";
worksheet.Cell(currentRow, 11).Value = rel.Valor.ToString() ?? "R$ 0,00";
worksheet.Cell(currentRow, 12).Value = rel.Juros ?? 0;
worksheet.Cell(currentRow, 13).Value = rel.Multa ?? 0;
worksheet.Cell(currentRow, 14).Value = rel.Desconto ?? 0;
worksheet.Cell(currentRow, 15).Value = rel.ValorRecebido ?? 0;
}
using (var stream = new MemoryStream())
{
workbook.SaveAs(stream);
var content = stream.ToArray();
return(File(content, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "ContasReceber.xlsx"));
}
}
}
catch (Exception ex)
{
Mensagem = ex.Message.ToString();
ModelState.AddModelError(String.Empty, Mensagem);
return(RedirectToAction(nameof(GerarRelatorio)));
}
}
public ActionResult Index(FormCollection form, string searchBy, string search, string searching, string searching1)
{
DataTable dtData = new DataTable();
string fileName = Guid.NewGuid().ToString();
String reportHeader = "This is a header.";
try
{
if (searchBy == "Date")
{
DateTime searchfrom = Convert.ToDateTime(searching);
DateTime searchto = Convert.ToDateTime(searching1);
{
string conString = ConfigurationManager.ConnectionStrings["TapalsEntitiesContect"].ConnectionString;
//string sql = "select * from Inward where " + searchBy + "='" + search.ToString() + "'";
string sql = "select * from Inward where Date_in between'" + searchfrom + "'and '" + searchto + "'";
using (SqlConnection connection = new SqlConnection(conString))
{
using (SqlCommand command = new SqlCommand(sql, connection))
{
using (SqlDataAdapter da = new SqlDataAdapter(command))
{
connection.Open();
da.Fill(dtData); connection.Close();
}
}
}
}
}
else
{
string conString = ConfigurationManager.ConnectionStrings["TapalsEntitiesContect"].ConnectionString;
string sql = "select * from Inward where " + searchBy + "='" + search.ToString() + "'";
using (SqlConnection connection = new SqlConnection(conString))
{
using (SqlCommand command = new SqlCommand(sql, connection))
{
using (SqlDataAdapter da = new SqlDataAdapter(command))
{
connection.Open();
da.Fill(dtData); connection.Close();
}
}
}
}
var MyWorkBook = new XLWorkbook();
var MyWorkSheet = MyWorkBook.Worksheets.Add("Sheet 1");
int TotalColumns = dtData.Columns.Count;
//-->headline
//first row is intentionaly left blank.
var headLine = MyWorkSheet.Range(MyWorkSheet.Cell(2, 1).Address, MyWorkSheet.Cell(2, TotalColumns).Address);
headLine.Style.Font.Bold = true;
headLine.Style.Font.FontSize = 15;
headLine.Style.Font.FontColor = XLColor.White;
headLine.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
headLine.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
headLine.Style.Fill.BackgroundColor = XLColor.FromTheme(XLThemeColor.Accent1, 0.25);
headLine.Style.Border.TopBorder = XLBorderStyleValues.Medium;
headLine.Style.Border.BottomBorder = XLBorderStyleValues.Medium;
headLine.Style.Border.LeftBorder = XLBorderStyleValues.Medium;
headLine.Style.Border.RightBorder = XLBorderStyleValues.Medium;
headLine.Merge();
// headLine.Value = reportHeader;
//<-- headline
//--> column settings
for (int i = 1; i < dtData.Columns.Count + 1; i++)
{
String combinedHeaderText = dtData.Columns[i - 1].ColumnName.ToString();
string separatedColumnHeader = "";
foreach (char letter in combinedHeaderText)
{
if (Char.IsUpper(letter) && separatedColumnHeader.Length > 0)
{
separatedColumnHeader += " " + letter;
}
else
{
separatedColumnHeader += letter;
}
}
MyWorkSheet.Cell(4, i).Value = separatedColumnHeader;
MyWorkSheet.Cell(4, i).Style.Alignment.WrapText = true;
}
var columnRange = MyWorkSheet.Range(MyWorkSheet.Cell(4, 1).Address, MyWorkSheet.Cell(4, TotalColumns).Address);
columnRange.Style.Font.Bold = true;
columnRange.Style.Font.FontSize = 10;
columnRange.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
columnRange.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
columnRange.Style.Fill.BackgroundColor = XLColor.FromArgb(171, 195, 223);
columnRange.Style.Border.TopBorder = XLBorderStyleValues.Thin;
columnRange.Style.Border.BottomBorder = XLBorderStyleValues.Thin;
columnRange.Style.Border.LeftBorder = XLBorderStyleValues.Thin;
columnRange.Style.Border.RightBorder = XLBorderStyleValues.Thin;
//<-- column settings
//--> row data & settings
for (int i = 0; i < dtData.Rows.Count; i++)
{
DataRow row = dtData.Rows[i];
for (int j = 0; j < dtData.Columns.Count; j++)
{
MyWorkSheet.Cell(i + 5, j + 1).Value = row[j].ToString();
}
}
var dataRowRange = MyWorkSheet.Range(MyWorkSheet.Cell(5, 1).Address, MyWorkSheet.Cell(dtData.Rows.Count + 4, TotalColumns).Address);
dataRowRange.Style.Font.Bold = false;
dataRowRange.Style.Font.FontSize = 10;
dataRowRange.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
dataRowRange.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
dataRowRange.Style.Fill.BackgroundColor = XLColor.FromArgb(219, 229, 241);
dataRowRange.Style.Border.TopBorder = XLBorderStyleValues.Thin;
dataRowRange.Style.Border.BottomBorder = XLBorderStyleValues.Thin;
dataRowRange.Style.Border.LeftBorder = XLBorderStyleValues.Thin;
dataRowRange.Style.Border.RightBorder = XLBorderStyleValues.Thin;
//<-- row data & settings
// Prepare the response
Response.Clear();
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("content-disposition", "attachment;filename=\"" + reportHeader + ".xlsx\"");
// Flush the workbook to the Response.OutputStream
using (MemoryStream memoryStream = new MemoryStream())
{
MyWorkBook.SaveAs(memoryStream);
memoryStream.WriteTo(Response.OutputStream);
memoryStream.Close();
}
Response.End();
return(View());
}
catch (Exception ex)
{
throw ex;
}
}
public void AfterDataTemplatesRender(DataSourceDynamicPanelEventArgs args)
{
args.Range.FirstCell().Style.Fill.BackgroundColor = XLColor.FromTheme(XLThemeColor.Background2);
args.Range.Range(1, 7, 1, 9).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right;
}
public static void ExportDataToExcel(string file_name, string report_title, DataTable data, HttpContext context, string[] column_names, string company_name)
{
string imagePath = context.Server.MapPath("~/uploads/CompanyLogo/");
DBConnection db_connection = new DBConnection();
DataTable leave_status = new DataTable();
string query = string.Empty;
string status_description = string.Empty;
var work_book = new XLWorkbook();
var work_sheet = work_book.Worksheets.Add("Sheet 1");
var dataForExport = new List <string[]>();
string export_path = context.Server.MapPath("~/exports/data/");
int counter = 0, data_count = 0;
DataTable totals = new DataTable();
var title_styles = work_book.Style;
data_count = data.Rows.Count;
// Background color for the cell
title_styles.Fill.BackgroundColor = XLColor.FromTheme(XLThemeColor.Accent1, 0.5);
// Font configuration
title_styles.Font.Bold = true;
title_styles.Font.FontColor = XLColor.White;
title_styles.Font.FontSize = 30;
// Text Alignment
title_styles.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
// Adding the Title in the Cell A1,1
work_sheet.Cell(1, 1).Value = report_title;
// Get workbook range for Title
var title_range = work_sheet.Range(1, 1, 3, 24);
// Merge the cells in the above range to form the title.
title_range.Merge();
// Apply the styles defined before
title_range.Style = title_styles;
// For Showing Company Name below report title
work_sheet.Cell(4, 1).Value = "Company: " + company_name;
work_sheet.Range(4, 1, 4, 20).Style.Font.Bold = true;
work_sheet.Range(4, 1, 4, 20).Style.Font.FontSize = 16;
// Manually adding the column names, as the data adding functions don't display the column names.
for (counter = 0; counter < column_names.Length; counter++)
{
work_sheet.Cell(5, counter + 1).Value = column_names[counter];
work_sheet.Cell(5, counter + 1).Style.Font.Bold = true;
work_sheet.Cell(5, counter + 1).Style.Font.FontSize = 16;
}
// Adding the datatable to the selected range
work_sheet.Cell(6, 1).Value = data.AsEnumerable();
// Added for showing various count //
if (report_title == "DAILY PERFORMANCE REPORT")
{
work_sheet.Cell(7 + data_count, 7).Value = "Summary Sheet";
work_sheet.Cell(7 + data_count, 7).Style.Font.Bold = true;
work_sheet.Cell(7 + data_count, 7).Style.Font.FontSize = 16;
// Getting all the calculated data from function
query = "select * from [FetchTotalCounts]('1')";
totals = db_connection.ReturnDataTable(query);
work_sheet.Cell(8 + data_count, 1).Value = "Total Count";
work_sheet.Cell(8 + data_count, 1).Style.Font.Bold = true;
work_sheet.Cell(8 + data_count, 1).Style.Font.FontSize = 10;
work_sheet.Cell(8 + data_count, 3).Value = "Days";
work_sheet.Cell(8 + data_count, 3).Style.Font.Bold = true;
work_sheet.Cell(8 + data_count, 3).Style.Font.FontSize = 10;
work_sheet.Cell(8 + data_count, 4).Value = totals.Rows[0]["TotalDays"].ToString();
work_sheet.Cell(8 + data_count, 6).Value = "Employees";
work_sheet.Cell(8 + data_count, 6).Style.Font.Bold = true;
work_sheet.Cell(8 + data_count, 6).Style.Font.FontSize = 10;
work_sheet.Cell(8 + data_count, 7).Value = totals.Rows[0]["TotalEmployee"].ToString();
work_sheet.Cell(8 + data_count, 9).Value = "Work Hours";
work_sheet.Cell(8 + data_count, 9).Style.Font.Bold = true;
work_sheet.Cell(8 + data_count, 9).Style.Font.FontSize = 10;
work_sheet.Cell(8 + data_count, 10).Value = totals.Rows[0]["Total_Hours"].ToString();
work_sheet.Cell(8 + data_count, 12).Value = "Early Hours";
work_sheet.Cell(8 + data_count, 12).Style.Font.Bold = true;
work_sheet.Cell(8 + data_count, 12).Style.Font.FontSize = 10;
work_sheet.Cell(8 + data_count, 13).Value = totals.Rows[0]["Total_Earlyby"].ToString();
work_sheet.Cell(8 + data_count, 15).Value = "Late Hours";
work_sheet.Cell(8 + data_count, 15).Style.Font.Bold = true;
work_sheet.Cell(8 + data_count, 15).Style.Font.FontSize = 10;
work_sheet.Cell(8 + data_count, 16).Value = totals.Rows[0]["Total_Lateby"].ToString();
}
status_description = "Description:- P=Present, A=Absent, L=Leave, AHL=Half Absent Half Leave, PHL=Half Day Present Half Day Leave, V=Vacation, OD=On Duty,CO=Comp. Off,MI=Manual in punch,MO=Manual Out Punch,M=Manual Punch,MS=Missing Swipe";
//getting Leave code description
leave_status = db_connection.ReturnDataTable("select leavecode,status from leavemaster where CompanyCode in ( select companycode from CompanyMaster where CompanyName='" + company_name + "') ");
//leaveStatus
string leaveCodeColumn = string.Empty, leaveStatusColumn = string.Empty;
string leaveStatus = string.Empty;
int count = 0;
foreach (DataRow row in leave_status.Rows)
{
count++;
foreach (DataColumn col in leave_status.Columns)
{
leaveCodeColumn = row["leavecode"].ToString();
leaveStatusColumn = row["status"].ToString();
}
if (count == 1)
{
leaveStatus = leaveCodeColumn + " = " + leaveStatusColumn;
}
else
{
leaveStatus = leaveStatus + "," + leaveCodeColumn + " = " + leaveStatusColumn;
}
}
var description_styles = work_book.Style;
description_styles.Fill.BackgroundColor = XLColor.FromTheme(XLThemeColor.Accent1, 0.5);
description_styles.Font.Bold = false;
description_styles.Font.FontColor = XLColor.AliceBlue;
description_styles.Font.FontSize = 11;
description_styles.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
work_sheet.Cell(9 + data_count, 1).Value = status_description;
// description_range = work_sheet.Range(10 + data_count, 1, 10 + data_count, 24);
work_sheet.Range(9 + data_count, 1, 9 + data_count, 24).Merge();
work_sheet.Range(9 + data_count, 1, 9 + data_count, 24).Style = description_styles;
work_sheet.Cell(11 + data_count, 1).Value = leaveStatus;
work_sheet.Range(11 + data_count, 1, 11 + data_count, 21).Style = description_styles;
work_sheet.Range(11 + data_count, 1, 11 + data_count, 15).Merge();
work_book.SaveAs(export_path + file_name);
CompanyLogoStuff companyStuff = new CompanyLogoStuff();
imagePath = imagePath + companyStuff.getCompanyImageUrl(company_name);
string fullPath = export_path + file_name;
// createLogo(fullPath, imagePath);
}
public ActionResult GerarExcel(int?txtCliente, DateTime txtDataInicio, DateTime txtDataFim, StatusOrcamento?txtStatus, string txtTipo)
{
try
{
if (TempData["txtCliente"] != null)
{
txtCliente = (int)TempData["txtCliente"];
}
if (TempData["txtDataInicio"] != null)
{
txtDataInicio = (DateTime)TempData["txtDataInicio"];
}
if (TempData["txtDataFim"] != null)
{
txtDataFim = (DateTime)TempData["txtDataFim"];
}
if (TempData["txtStatus"] != null)
{
txtStatus = (StatusOrcamento)TempData["txtStatus"];
}
if (TempData["txtTipo"] != null)
{
txtTipo = TempData["txtTipo"].ToString();
}
var relatorio = _orcamento.ObterTodos().Where(x => x.ClienteID == 0);
if (txtCliente != null && txtDataInicio != null && txtDataFim != null && txtStatus == null && txtTipo == null)
{
relatorio = _orcamento.ObterTodos().Where(x => x.ClienteID == txtCliente && x.DataOrcamento >= txtDataInicio && x.DataOrcamento <= txtDataFim);
}
if (txtCliente == null && txtDataInicio != null && txtDataFim != null && txtStatus == null && txtTipo == null)
{
relatorio = _orcamento.ObterTodos().Where(x => x.DataOrcamento >= txtDataInicio && x.DataOrcamento <= txtDataFim);
}
if (txtCliente == null && txtDataInicio != null && txtDataFim != null && txtStatus != null && txtTipo == null)
{
relatorio = _orcamento.ObterTodos().Where(x => x.DataOrcamento >= txtDataInicio && x.DataOrcamento <= txtDataFim && x.StatusOrcamento == txtStatus);
}
if (txtCliente == null && txtDataInicio != null && txtDataFim != null && txtStatus == null && txtTipo != null)
{
relatorio = _orcamento.ObterTodos().Where(x => x.DataOrcamento >= txtDataInicio && x.DataOrcamento <= txtDataFim && x.TipoOrcamento == txtTipo);
}
if (txtCliente != null && txtDataInicio != null && txtDataFim != null && txtStatus != null && txtTipo != null)
{
relatorio = _orcamento.ObterTodos().Where(x => x.ClienteID == txtCliente && x.DataOrcamento >= txtDataInicio && x.DataOrcamento <= txtDataFim && x.StatusOrcamento == txtStatus && x.TipoOrcamento == txtTipo);
}
using (var workbook = new XLWorkbook())
{
var worksheet = workbook.Worksheets.Add("Orçamentos");
var currentRow = 1;
worksheet.Cell(currentRow, 1).Value = "DATA DE CADASTRO";
worksheet.Cell(currentRow, 1).Style.Font.Bold = true;
worksheet.Cell(currentRow, 1).Style.Fill.BackgroundColor = XLColor.FromTheme(XLThemeColor.Accent1, 0.5);
worksheet.Cell(currentRow, 2).Value = "DATA ORÇAMENTO";
worksheet.Cell(currentRow, 2).Style.Font.Bold = true;
worksheet.Cell(currentRow, 2).Style.Fill.BackgroundColor = XLColor.FromTheme(XLThemeColor.Accent1, 0.5);
worksheet.Cell(currentRow, 3).Value = "DATA VALIDADE";
worksheet.Cell(currentRow, 3).Style.Font.Bold = true;
worksheet.Cell(currentRow, 3).Style.Fill.BackgroundColor = XLColor.FromTheme(XLThemeColor.Accent1, 0.5);
worksheet.Cell(currentRow, 4).Value = "TIPO ORÇAMENTO";
worksheet.Cell(currentRow, 4).Style.Font.Bold = true;
worksheet.Cell(currentRow, 4).Style.Fill.BackgroundColor = XLColor.FromTheme(XLThemeColor.Accent1, 0.5);
worksheet.Cell(currentRow, 5).Value = "CLIENTE";
worksheet.Cell(currentRow, 5).Style.Font.Bold = true;
worksheet.Cell(currentRow, 5).Style.Fill.BackgroundColor = XLColor.FromTheme(XLThemeColor.Accent1, 0.5);
worksheet.Cell(currentRow, 6).Value = "SOLICITANTE";
worksheet.Cell(currentRow, 6).Style.Font.Bold = true;
worksheet.Cell(currentRow, 6).Style.Fill.BackgroundColor = XLColor.FromTheme(XLThemeColor.Accent1, 0.5);
worksheet.Cell(currentRow, 7).Value = "TITULO";
worksheet.Cell(currentRow, 7).Style.Font.Bold = true;
worksheet.Cell(currentRow, 7).Style.Fill.BackgroundColor = XLColor.FromTheme(XLThemeColor.Accent1, 0.5);
worksheet.Cell(currentRow, 8).Value = "STATUS";
worksheet.Cell(currentRow, 8).Style.Font.Bold = true;
worksheet.Cell(currentRow, 8).Style.Fill.BackgroundColor = XLColor.FromTheme(XLThemeColor.Accent1, 0.5);
worksheet.Cell(currentRow, 9).Value = "TIPO VEICULAÇÃO";
worksheet.Cell(currentRow, 9).Style.Font.Bold = true;
worksheet.Cell(currentRow, 9).Style.Fill.BackgroundColor = XLColor.FromTheme(XLThemeColor.Accent1, 0.5);
worksheet.Cell(currentRow, 10).Value = "PRAÇA VEICULAÇÃO";
worksheet.Cell(currentRow, 10).Style.Font.Bold = true;
worksheet.Cell(currentRow, 10).Style.Fill.BackgroundColor = XLColor.FromTheme(XLThemeColor.Accent1, 0.5);
worksheet.Cell(currentRow, 11).Value = "NOME PEÇA";
worksheet.Cell(currentRow, 11).Style.Font.Bold = true;
worksheet.Cell(currentRow, 11).Style.Fill.BackgroundColor = XLColor.FromTheme(XLThemeColor.Accent1, 0.5);
worksheet.Cell(currentRow, 12).Value = "DURAÇÃO PEÇA";
worksheet.Cell(currentRow, 12).Style.Font.Bold = true;
worksheet.Cell(currentRow, 12).Style.Fill.BackgroundColor = XLColor.FromTheme(XLThemeColor.Accent1, 0.5);
worksheet.Cell(currentRow, 13).Value = "PERÍODO VEICULAÇÃO";
worksheet.Cell(currentRow, 13).Style.Font.Bold = true;
worksheet.Cell(currentRow, 13).Style.Fill.BackgroundColor = XLColor.FromTheme(XLThemeColor.Accent1, 0.5);
worksheet.Cell(currentRow, 14).Value = "TIPO MÍDIA";
worksheet.Cell(currentRow, 14).Style.Font.Bold = true;
worksheet.Cell(currentRow, 14).Style.Fill.BackgroundColor = XLColor.FromTheme(XLThemeColor.Accent1, 0.5);
worksheet.Cell(currentRow, 15).Value = "PEDIDO DE PRODUÇÃO";
worksheet.Cell(currentRow, 15).Style.Font.Bold = true;
worksheet.Cell(currentRow, 15).Style.Fill.BackgroundColor = XLColor.FromTheme(XLThemeColor.Accent1, 0.5);
worksheet.Cell(currentRow, 16).Value = "BV(%)";
worksheet.Cell(currentRow, 16).Style.Font.Bold = true;
worksheet.Cell(currentRow, 16).Style.Fill.BackgroundColor = XLColor.FromTheme(XLThemeColor.Accent1, 0.5);
worksheet.Cell(currentRow, 17).Value = "TAXA LUCRO(%)";
worksheet.Cell(currentRow, 17).Style.Font.Bold = true;
worksheet.Cell(currentRow, 17).Style.Fill.BackgroundColor = XLColor.FromTheme(XLThemeColor.Accent1, 0.5);
worksheet.Cell(currentRow, 18).Value = "TAXA IMPOSTO(%)";
worksheet.Cell(currentRow, 18).Style.Font.Bold = true;
worksheet.Cell(currentRow, 18).Style.Fill.BackgroundColor = XLColor.FromTheme(XLThemeColor.Accent1, 0.5);
worksheet.Cell(currentRow, 19).Value = "TAXA COMISSÃO(%)";
worksheet.Cell(currentRow, 19).Style.Font.Bold = true;
worksheet.Cell(currentRow, 19).Style.Fill.BackgroundColor = XLColor.FromTheme(XLThemeColor.Accent1, 0.5);
worksheet.Cell(currentRow, 20).Value = "ACRÉSCIMOS(R$)";
worksheet.Cell(currentRow, 20).Style.Font.Bold = true;
worksheet.Cell(currentRow, 20).Style.Fill.BackgroundColor = XLColor.FromTheme(XLThemeColor.Accent1, 0.5);
worksheet.Cell(currentRow, 21).Value = "DESCONTOS(R$)";
worksheet.Cell(currentRow, 21).Style.Font.Bold = true;
worksheet.Cell(currentRow, 21).Style.Fill.BackgroundColor = XLColor.FromTheme(XLThemeColor.Accent1, 0.5);
worksheet.Cell(currentRow, 22).Value = "VALOR(R$)";
worksheet.Cell(currentRow, 22).Style.Font.Bold = true;
worksheet.Cell(currentRow, 22).Style.Fill.BackgroundColor = XLColor.FromTheme(XLThemeColor.Accent1, 0.5);
foreach (var rel in relatorio)
{
currentRow++;
worksheet.Cell(currentRow, 1).Value = rel.DataCadastro.ToString("dd/MM/yyyy");
worksheet.Cell(currentRow, 2).Value = rel.DataOrcamento.ToString("dd/MM/yyyy");
worksheet.Cell(currentRow, 3).Value = rel.DataValidade.ToString("dd/MM/yyyy");
worksheet.Cell(currentRow, 4).Value = rel.TipoOrcamento;
worksheet.Cell(currentRow, 5).Value = rel.Cliente.RazaoSocial;
worksheet.Cell(currentRow, 6).Value = rel.Solicitante;
worksheet.Cell(currentRow, 7).Value = rel.Titulo;
worksheet.Cell(currentRow, 8).Value = rel.StatusOrcamento;
worksheet.Cell(currentRow, 9).Value = rel.TipoVeiculacao ?? " - ";
worksheet.Cell(currentRow, 10).Value = rel.PracaVeiculacao ?? " - ";
worksheet.Cell(currentRow, 11).Value = rel.NomePeca ?? " - ";
worksheet.Cell(currentRow, 12).Value = rel.DuracaoPeca ?? " - ";
worksheet.Cell(currentRow, 13).Value = rel.PeriodoVeiculacao ?? " - ";
worksheet.Cell(currentRow, 14).Value = rel.TipoMidia ?? " - ";
worksheet.Cell(currentRow, 15).Value = rel.PedidoProducao ?? " - ";
worksheet.Cell(currentRow, 16).Value = rel.BonificacaoVeiculacao ?? 0;
worksheet.Cell(currentRow, 17).Value = rel.TaxaLucro ?? 0;
worksheet.Cell(currentRow, 18).Value = rel.TaxaImposto ?? 0;
worksheet.Cell(currentRow, 19).Value = rel.TaxaComissao ?? 0;
worksheet.Cell(currentRow, 20).Value = rel.Acrescimo ?? 0;
worksheet.Cell(currentRow, 21).Value = rel.Desconto ?? 0;
worksheet.Cell(currentRow, 22).Value = rel.TotalOrcamento.ToString("C") ?? "R$ 0,00";
}
using (var stream = new MemoryStream())
{
workbook.SaveAs(stream);
var content = stream.ToArray();
return(File(content, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "Orcamentos.xlsx"));
}
}
}
catch (Exception ex)
{
Mensagem = ex.Message.ToString();
ModelState.AddModelError(String.Empty, Mensagem);
return(RedirectToAction(nameof(GerarRelatorio)));
}
}
public string crearReportByEmpleado(int idusuario, string ruta, int[] idperiodos, int idempresa, DateTime fechaInicio, DateTime fechaFin)
{
int i = 4;
var nombreEmpresa = ctx.Empresa.Where(x => x.IdEmpresa == idempresa).Select(x => x.RazonSocial).FirstOrDefault();
var newruta = Utils.ValidarFolderUsuario(idusuario, ruta);
newruta = newruta + nombreEmpresa + ".xlsx";
var reportes = new ListaDeRaya();
var clavecliente = "";
var aux = "";
var wb = new XLWorkbook();
var ws = wb.Worksheets.Add("REPORTE");
ws.Cell("A1").Value = "DATOS PARA LA POLIZA DEL " + fechaInicio.ToString("dd/MM/yyyy") + " AL " + fechaFin.ToString("dd/MM/yyyy");
ws.Range("A1:I1").Merge();
ws.Cell("A1").Style
.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center)
.Font.SetBold();
ws.Cell("A3").Value = "CUENTA";
ws.Cell("A3").Style
.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center)
.Font.SetBold();
ws.Cell("A3").Style.Fill.BackgroundColor = XLColor.FromTheme(XLThemeColor.Accent1, 0.5);
ws.Cell("B3").Value = "NOMBRE";
ws.Cell("B3").Style
.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center)
.Font.SetBold();
ws.Cell("B3").Style.Fill.BackgroundColor = XLColor.FromTheme(XLThemeColor.Accent1, 0.5);
ws.Cell("C3").Value = "CARGO M.E";
ws.Cell("C3").Style
.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center)
.Font.SetBold();
ws.Cell("C3").Style.Fill.BackgroundColor = XLColor.FromTheme(XLThemeColor.Accent1, 0.5);
ws.Cell("D3").Value = "ABONO M.E.";
ws.Cell("D3").Style
.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center)
.Font.SetBold();
ws.Cell("D3").Style.Fill.BackgroundColor = XLColor.FromTheme(XLThemeColor.Accent1, 0.5);
ws.Cell("E3").Value = "CARGO";
ws.Cell("E3").Style
.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center)
.Font.SetBold();
ws.Cell("E3").Style.Fill.BackgroundColor = XLColor.FromTheme(XLThemeColor.Accent1, 0.5);
ws.Cell("F3").Value = "ABONO";
ws.Cell("F3").Style
.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center)
.Font.SetBold();
ws.Cell("F3").Style.Fill.BackgroundColor = XLColor.FromTheme(XLThemeColor.Accent1, 0.5);
ws.Cell("G3").Value = "REFERENCIA";
ws.Cell("G3").Style
.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center)
.Font.SetBold();
ws.Cell("G3").Style.Fill.BackgroundColor = XLColor.FromTheme(XLThemeColor.Accent1, 0.5);
ws.Cell("H3").Value = "CONCEPTO";
ws.Cell("H3").Style
.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center)
.Font.SetBold();
ws.Cell("H3").Style.Fill.BackgroundColor = XLColor.FromTheme(XLThemeColor.Accent1, 0.5);
ws.Cell("I3").Value = "DIARIO";
ws.Cell("I3").Style
.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center)
.Font.SetBold();
ws.Cell("I3").Style.Fill.BackgroundColor = XLColor.FromTheme(XLThemeColor.Accent1, 0.5);
foreach (var p in idperiodos)
{
var periodo = ctx.NOM_PeriodosPago.Where(x => x.IdPeriodoPago == p).FirstOrDefault();
if (periodo.IdTipoNomina != 11)
{
var datos = reportes.conceptosDetalladosByEmpleado(p, idempresa);
foreach (var d in datos)
{
decimal netop = 0;
decimal netod = 0;
string rfc = "";
var pperiodos = ctx.NOM_PeriodosPago.Where(x => x.IdPeriodoPago == p).FirstOrDefault();
var suc = ctx.Sucursal_Empresa.Where(x => x.IdSucursal == pperiodos.IdSucursal && x.IdEmpresa == idempresa).FirstOrDefault();
var claveNominas = ctx.ClavesContables.Where(x => x.IdEmpresa == idempresa && x.IdConcepto == 150).FirstOrDefault();
if (clavecliente == "")
{
clavecliente = suc.Clave_Contable;
}
if (clavecliente != suc.Clave_Contable)
{
ws.Cell("A" + i).Style.Fill.BackgroundColor = XLColor.FromTheme(XLThemeColor.Accent1, 0.5);
ws.Cell("B" + i).Style.Fill.BackgroundColor = XLColor.FromTheme(XLThemeColor.Accent1, 0.5);
ws.Cell("C" + i).Style.Fill.BackgroundColor = XLColor.FromTheme(XLThemeColor.Accent1, 0.5);
ws.Cell("D" + i).Style.Fill.BackgroundColor = XLColor.FromTheme(XLThemeColor.Accent1, 0.5);
ws.Cell("E" + i).Style.Fill.BackgroundColor = XLColor.FromTheme(XLThemeColor.Accent1, 0.5);
ws.Cell("F" + i).Style.Fill.BackgroundColor = XLColor.FromTheme(XLThemeColor.Accent1, 0.5);
ws.Cell("G" + i).Style.Fill.BackgroundColor = XLColor.FromTheme(XLThemeColor.Accent1, 0.5);
ws.Cell("H" + i).Style.Fill.BackgroundColor = XLColor.FromTheme(XLThemeColor.Accent1, 0.5);
ws.Cell("I" + i).Style.Fill.BackgroundColor = XLColor.FromTheme(XLThemeColor.Accent1, 0.5);
i++;
clavecliente = suc.Clave_Contable;
}
foreach (var t in d.listaGeneral.Where(x => x.TipoConcepto == 1 && x.TotalConcepto > 0))
{
ws.Cell("A" + i).Value = t.Deudora;
ws.Cell("B" + i).Value = " ";
ws.Cell("C" + i).Value = " ";
ws.Cell("D" + i).Value = " ";
ws.Cell("E" + i).Value = t.TotalConcepto;
ws.Cell("E" + i).Style.NumberFormat.Format = "$ #,##0.00";
ws.Cell("F" + i).Value = " ";
ws.Cell("G" + i).Value = t.ClaveCliente;
ws.Cell("H" + i).Value = t.rfc + " (" + periodo.Fecha_Inicio.Day + "_" + periodo.Fecha_Fin.Day + "_" + periodo.Fecha_Inicio.ToString("MMM") + ")";
ws.Cell("I" + i).Value = " ";
i++;
netop = netop + t.TotalConcepto;
rfc = t.rfc;
}
foreach (var t in d.listaGeneral.Where(x => x.TipoConcepto == 2 && x.TotalConcepto > 0))
{
ws.Cell("A" + i).Value = t.Acredora;
ws.Cell("B" + i).Value = " ";
ws.Cell("C" + i).Value = " ";
ws.Cell("D" + i).Value = " ";
ws.Cell("E" + i).Value = " ";
ws.Cell("F" + i).Value = t.TotalConcepto;
ws.Cell("F" + i).Style.NumberFormat.Format = "$ #,##0.00";
ws.Cell("G" + i).Value = t.ClaveCliente;
ws.Cell("H" + i).Value = t.rfc + " (" + periodo.Fecha_Inicio.Day + "_" + periodo.Fecha_Fin.Day + "_" + periodo.Fecha_Inicio.ToString("MMM") + ")";
ws.Cell("I" + i).Value = " ";
i++;
netod = netod + t.TotalConcepto;
}
var totalesfinales = netop - netod;
ws.Cell("A" + i).Value = claveNominas == null ? "sin clave" : claveNominas.Deudora;
ws.Cell("B" + i).Value = " ";
ws.Cell("C" + i).Value = " ";
ws.Cell("D" + i).Value = " ";
ws.Cell("E" + i).Value = " ";
ws.Cell("F" + i).Value = totalesfinales;
ws.Cell("F" + i).Style.NumberFormat.Format = "$ #,##0.00";
ws.Cell("G" + i).Value = suc.Clave_Contable;
ws.Cell("H" + i).Value = rfc + " (" + periodo.Fecha_Inicio.Day + "_" + periodo.Fecha_Fin.Day + "_" + periodo.Fecha_Inicio.ToString("MMM") + ")";
ws.Cell("I" + i).Value = " ";
i++;
//netop = netop + t.TotalConcepto;
}
}
}
ws.Columns("3,1").AdjustToContents();
ws.Columns("3,2").AdjustToContents();
ws.Columns("3,3").AdjustToContents();
ws.Columns("3,4").AdjustToContents();
ws.Columns("3,5").AdjustToContents();
ws.Columns("3,6").AdjustToContents();
ws.Columns("3,7").AdjustToContents();
ws.Columns("3,8").AdjustToContents();
ws.Columns("3,9").AdjustToContents();
wb.SaveAs(newruta);
return(newruta);
}
public void GerarPDFPonto(String mes)
{
DAOCaminho daoCaminho = new DAOCaminho();
string caminho = daoCaminho.CaminhoPonto();
//PADRÂO
var wb = new XLWorkbook();
var ws = wb.Worksheets.Add("Follha de ponto ");
var rngTable = ws.Range("B2:G5");
//TITULO
ws.Cell("B3").Value = "Folha de ponto do mês " + mes;//TITULO
//PRIMEIRAS LINHAS
ws.Cell("B5").Value = "Data";
ws.Cell("C5").Value = "Chegada";
ws.Cell("D5").Value = "Saída almoço";
ws.Cell("E5").Value = "Volta almoço";
ws.Cell("F5").Value = "Saída";
ws.Cell("G5").Value = "Nome";
//BORDA NOS TITULOS
rngTable = ws.Range("B5");
rngTable.Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
rngTable = ws.Range("C5");
rngTable.Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
rngTable = ws.Range("D5");
rngTable.Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
rngTable = ws.Range("E5");
rngTable.Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
rngTable = ws.Range("F5");
rngTable.Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
rngTable = ws.Range("G5");
rngTable.Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
//AJUSTAR A NUMERACAO
var linha = 6;
//PEGAR PONTO DO BANCO
DAOPonto dao = new DAOPonto();
List <PontoModel> ListaPonto = new List <PontoModel>();
ListaPonto = dao.ListarPorMes(mes);
//COLOCAR DATA
linha = 6;
foreach (var item in ListaPonto)
{
ws.Cell("B" + linha.ToString()).Value = item.Data.ToString();
string borda = "B" + linha;
rngTable = ws.Range(borda);
rngTable.Style.Border.OutsideBorder = XLBorderStyleValues.Thin;//BORDA FINA
linha++;
}
//COLOCAR HORA1 (CHEGADA)
linha = 6;
foreach (var item in ListaPonto)
{
ws.Cell("C" + linha.ToString()).Value = item.Hora1.ToString();
string borda = "C" + linha;
rngTable = ws.Range(borda);
rngTable.Style.Border.OutsideBorder = XLBorderStyleValues.Thin;//BORDA FINA
linha++;
}
//COLOCAR HORA2
linha = 6;
foreach (var item in ListaPonto)
{
ws.Cell("D" + linha.ToString()).Value = item.Hora2.ToString();
string borda = "D" + linha;
rngTable = ws.Range(borda);
rngTable.Style.Border.OutsideBorder = XLBorderStyleValues.Thin;//BORDA FINA
linha++;
}
//COLOCAR HORA3
linha = 6;
foreach (var item in ListaPonto)
{
ws.Cell("E" + linha.ToString()).Value = item.Hora3.ToString();
string borda = "E" + linha;
rngTable = ws.Range(borda);
rngTable.Style.Border.OutsideBorder = XLBorderStyleValues.Thin;//BORDA FINA
linha++;
}
//COLOCAR HORA4
linha = 6;
foreach (var item in ListaPonto)
{
ws.Cell("F" + linha.ToString()).Value = item.Hora4.ToString();
string borda = "F" + linha;
rngTable = ws.Range(borda);
rngTable.Style.Border.OutsideBorder = XLBorderStyleValues.Thin;//BORDA FINA
linha++;
}
//COLOCAR HORA4
linha = 6;
foreach (var item in ListaPonto)
{
ws.Cell("G" + linha.ToString()).Value = item.Nome.ToString();
string borda = "G" + linha;
rngTable = ws.Range(borda);
rngTable.Style.Border.OutsideBorder = XLBorderStyleValues.Thin;//BORDA FINA
linha++;
}
//TITULO
rngTable = ws.Range("B2:G4"); //SELECIONAR
rngTable.Row(2).Merge(); //JUNTAR
rngTable.Style.Border.OutsideBorder = XLBorderStyleValues.Thin; //BORDA fina
rngTable.Style.Fill.BackgroundColor = XLColor.FromTheme(XLThemeColor.Accent1); //COR FUNDO
rngTable.Style.Font.FontColor = XLColor.White; //COR FONTE
rngTable.Style.Font.Bold = true; //FONT BOLD
rngTable.Style.Font.FontSize = 15; //FONT TAmANHO
rngTable.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; //ALINHAR CENTRO
rngTable.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center; //ALINHAR CENTRO
ws.Columns(2, 6).AdjustToContents(); //AJUSTAR LARGURA CELULA
//LINHAS CIMA
rngTable = ws.Range("B4:G4");
rngTable.Style.Fill.BackgroundColor = XLColor.FromTheme(XLThemeColor.Accent1);
rngTable.Style.Font.FontColor = XLColor.White;
rngTable.Style.Font.Bold = true;
rngTable.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
//SALVAR PLANILHA
wb.SaveAs(@"" + caminho + @"\PontoPlanilha" + mes + ".xlsx");
wb.Dispose();
}