private void ApplyVerticalStyle(SLStyle cellStyle, VerticalAlignment verticalAlignment)
{
switch (verticalAlignment)
{
case VerticalAlignment.Bottom:
cellStyle.SetVerticalAlignment(VerticalAlignmentValues.Bottom);
break;
case VerticalAlignment.Top:
cellStyle.SetVerticalAlignment(VerticalAlignmentValues.Top);
break;
case VerticalAlignment.Center:
cellStyle.SetVerticalAlignment(VerticalAlignmentValues.Center);
break;
case VerticalAlignment.Justify:
cellStyle.SetVerticalAlignment(VerticalAlignmentValues.Justify);
break;
case VerticalAlignment.Distributed:
cellStyle.SetVerticalAlignment(VerticalAlignmentValues.Distributed);
break;
case VerticalAlignment.JustifyDistributed:
throw new NotImplementedException();
default:
throw new ArgumentOutOfRangeException(nameof(verticalAlignment));
}
}
/// <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;
}
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);
}
private SLDocument CreateHeaderExcel(SLDocument slDocument)
{
int iRow = 3;
slDocument.SetCellValue(iRow, 1, "ID");
slDocument.SetColumnWidth(1, 10);
//slDocument.MergeWorksheetCells(iRow, 1, iRow + 1, 1);
slDocument.SetCellValue(iRow, 2, "ID Anggota");
slDocument.SetColumnWidth(2, 15);
//slDocument.MergeWorksheetCells(iRow, 2, iRow + 1, 2);
slDocument.SetCellValue(iRow, 3, "Nama Anggota");
slDocument.SetColumnWidth(3, 25);
//slDocument.MergeWorksheetCells(iRow, 3, iRow + 1, 3);
slDocument.SetCellValue(iRow, 4, "Tanggal Gabung");
slDocument.SetColumnWidth(4, 15);
//slDocument.MergeWorksheetCells(iRow, 4, iRow + 1, 4);
slDocument.SetCellValue(iRow, 5, "Status");
slDocument.SetColumnWidth(5, 15);
SLStyle headerStyle = slDocument.CreateStyle();
headerStyle.Alignment.Horizontal = HorizontalAlignmentValues.Center;
headerStyle.Font.Bold = true;
headerStyle.Border.LeftBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Border.RightBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Border.TopBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Border.BottomBorder.BorderStyle = BorderStyleValues.Thin;
//headerStyle.Fill.SetPattern(PatternValues.Solid, System.Drawing.Color.LightGreen, System.Drawing.Color.LightGreen);
headerStyle.SetWrapText(true);
headerStyle.SetVerticalAlignment(VerticalAlignmentValues.Center);
headerStyle.Font.FontSize = 10;
slDocument.SetCellStyle(3, 1, iRow, 5, headerStyle);
return(slDocument);
}
public static void WriteReportHeader(string fileName, string testMode, Dictionary <string, string> dictionary)
{
try
{
if (File.Exists(fileName) == false)
{
using (SLDocument s = new SLDocument())
{
s.SaveAs(fileName);
}
}
using (SLDocument sl = new SLDocument(fileName))
{
SLStyle headerstyle = sl.CreateStyle();
headerstyle.Alignment.Horizontal = DocumentFormat.OpenXml.Spreadsheet.HorizontalAlignmentValues.Center;
headerstyle.Font.FontSize = 18;
headerstyle.Font.FontName = "宋体";
headerstyle.Font.Bold = true;
SLStyle secondrow_header = sl.CreateStyle();
secondrow_header.Alignment.Horizontal = DocumentFormat.OpenXml.Spreadsheet.HorizontalAlignmentValues.Center;
secondrow_header.SetWrapText(true);
secondrow_header.SetVerticalAlignment(VerticalAlignmentValues.Center);
secondrow_header.Font.FontSize = 12;
secondrow_header.Font.FontName = "宋体";
string rowindex = "1";
string lastCol = "A";
char endRow = Convert.ToChar('A' + dictionary.Keys.Count);
sl.SetCellValue("A1", testMode);
sl.MergeWorksheetCells("A1", endRow.ToString() + '1');
sl.SetCellStyle("A1", headerstyle);
rowindex = "2";
lastCol = "A";
sl.SetCellStyle("A2", endRow.ToString() + '2', secondrow_header);
foreach (var item in dictionary)
{
sl.SetCellValue(lastCol + rowindex, item.Key);
lastCol = getNextColumnCellName(lastCol);
}
sl.Save();
}
}
catch (Exception ex)
{
System.Windows.Forms.MessageBox.Show(ex.Message);
}
}
private SLDocument CreateDataExcel(SLDocument slDocument, List <DataAnggotaDto> listData)
{
int iRow = 4; //starting row data
int row = 1;
try
{
foreach (var data in listData)
{
slDocument.SetCellValue(iRow, 1, data.Id);
slDocument.SetCellValue(iRow, 2, data.IdAnggota);
slDocument.SetCellValue(iRow, 3, data.NamaAnggota);
slDocument.SetCellValue(iRow, 4, data.TanggalGabung);
slDocument.SetCellValue(iRow, 5, data.Status?"Aktif":"Tidak Aktif");
iRow++;
row++;
}
}
catch (Exception)
{
throw;
}
//create style
SLStyle valueStyle = slDocument.CreateStyle();
valueStyle.Border.LeftBorder.BorderStyle = BorderStyleValues.Thin;
valueStyle.Border.RightBorder.BorderStyle = BorderStyleValues.Thin;
valueStyle.Border.TopBorder.BorderStyle = BorderStyleValues.Thin;
valueStyle.Border.BottomBorder.BorderStyle = BorderStyleValues.Thin;
SLStyle dateStyle = slDocument.CreateStyle();
dateStyle.FormatCode = "dd-MMM-yyyy";
SLStyle hourStyle = slDocument.CreateStyle();
hourStyle.FormatCode = "HH:mm";
SLStyle decimalFormat = slDocument.CreateStyle();
decimalFormat.FormatCode = "#,##0.00";
SLStyle decimalFormat2 = slDocument.CreateStyle();
decimalFormat2.FormatCode = "Rp #,##0.00";
slDocument.SetCellStyle(4, 4, iRow, 4, dateStyle);
valueStyle.SetHorizontalAlignment(HorizontalAlignmentValues.Center);
valueStyle.SetVerticalAlignment(VerticalAlignmentValues.Center);
valueStyle.Font.FontSize = 10;
slDocument.SetCellStyle(4, 1, iRow, 5, valueStyle);
return(slDocument);
}
private SLDocument CreateHeaderExcel(SLDocument slDocument)
{
int iRow = 10;
int col = 1;
slDocument.SetCellValue(iRow, col++, "Id");
slDocument.SetColumnWidth(1, 10);
//slDocument.MergeWorksheetCells(iRow, 1, iRow + 1, 1);
slDocument.SetCellValue(iRow, col++, "Id Anggota");
slDocument.SetColumnWidth(2, 20);
//slDocument.MergeWorksheetCells(iRow, 2, iRow + 1, 2);
slDocument.SetCellValue(iRow, col, "Nama Anggota");
slDocument.SetColumnWidth(3, 15);
col++;
//slDocument.MergeWorksheetCells(iRow, 3, iRow + 1, 3);
slDocument.SetCellValue(iRow, col, "Tanggal Gabung");
slDocument.SetColumnWidth(4, 15);
col++;
//slDocument.MergeWorksheetCells(iRow, 4, iRow + 1, 4);
slDocument.SetCellValue(iRow, col, "Pokok");
slDocument.SetColumnWidth(5, 15);
col++;
//slDocument.MergeWorksheetCells(iRow, 5, iRow + 1, 5);
slDocument.SetCellValue(iRow, col, "Wajib");
slDocument.SetColumnWidth(6, 15);
col++;
//slDocument.MergeWorksheetCells(iRow, 6, iRow + 1, 6);
slDocument.SetCellValue(iRow, col, "Sukarela");
slDocument.SetColumnWidth(7, 15);
col++;
//slDocument.MergeWorksheetCells(iRow, 6, iRow + 1, 6);
slDocument.SetCellValue(iRow, col, "Belanja");
slDocument.SetColumnWidth(8, 15);
col++;
//slDocument.MergeWorksheetCells(iRow, 6, iRow + 1, 6);
slDocument.SetCellValue(iRow, col, "Bunga Pinjaman");
slDocument.SetColumnWidth(9, 15);
col++;
SLStyle headerStyle = slDocument.CreateStyle();
headerStyle.Alignment.Horizontal = HorizontalAlignmentValues.Center;
headerStyle.Font.Bold = true;
headerStyle.Border.LeftBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Border.RightBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Border.TopBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Border.BottomBorder.BorderStyle = BorderStyleValues.Thin;
//headerStyle.Fill.SetPattern(PatternValues.Solid, System.Drawing.Color.LightGreen, System.Drawing.Color.LightGreen);
headerStyle.SetWrapText(true);
headerStyle.SetVerticalAlignment(VerticalAlignmentValues.Center);
headerStyle.Font.FontSize = 10;
slDocument.SetCellStyle(10, 1, iRow, 9, headerStyle);
return(slDocument);
}
public static void WriteReportHeader(string fileName, string testMode, string[] header)
{
try
{
if (File.Exists(fileName) == false)
{
using (SLDocument s = new SLDocument())
{
s.SaveAs(fileName);
}
}
using (SLDocument sl = new SLDocument(fileName))
{
SLStyle headerstyle = sl.CreateStyle();
headerstyle.Alignment.Horizontal = DocumentFormat.OpenXml.Spreadsheet.HorizontalAlignmentValues.Center;
headerstyle.Font.FontSize = 18;
headerstyle.Font.FontName = "宋体";
headerstyle.Font.Bold = true;
SLStyle secondrow_header = sl.CreateStyle();
secondrow_header.Alignment.Horizontal = DocumentFormat.OpenXml.Spreadsheet.HorizontalAlignmentValues.Center;
secondrow_header.SetWrapText(true);
secondrow_header.SetVerticalAlignment(VerticalAlignmentValues.Center);
secondrow_header.Font.FontSize = 12;
secondrow_header.Font.FontName = "宋体";
string rowindex = "1";
string lastCol = "A";
sl.SetCellValue("A1", testMode);
sl.MergeWorksheetCells("A1", "M1");
sl.SetCellStyle("A1", headerstyle);
rowindex = "2";
lastCol = "A";
sl.SetCellStyle("A2", "M2", secondrow_header);
for (int i = 0; i < header.Length; i++)
{
sl.SetCellValue(lastCol + rowindex, header[i]);
lastCol = getNextColumnCellName(lastCol);
}
sl.Save();
}
}
catch (Exception ex)
{
System.Windows.Forms.MessageBox.Show(ex.Message);
}
}
//crear los estilos al excel que se esta generando
public void createStyleExcel(SLDocument document)
{
SLStyle style = document.CreateStyle();
style.Alignment.Horizontal = HorizontalAlignmentValues.Left;
style.Fill.SetPattern(PatternValues.Solid, SLThemeColorIndexValues.Accent1Color, SLThemeColorIndexValues.Accent1Color);
style.SetVerticalAlignment(VerticalAlignmentValues.Center);
for (int i = 1; i < 10; i++)
{
document.SetCellStyle(1, i, style);
}
}
static void makeStickers(List <Unit> units, string spName)
{
using (SLDocument doc = new SLDocument()) {
var style_12 = new SLStyle();
style_12.SetFont("arial", 12);
style_12.SetHorizontalAlignment(DocumentFormat.OpenXml.Spreadsheet.HorizontalAlignmentValues.Center);
style_12.SetVerticalAlignment(DocumentFormat.OpenXml.Spreadsheet.VerticalAlignmentValues.Center);
SLPageSettings ps = new SLPageSettings();
ps.TopMargin = 0;
ps.BottomMargin = 0;
ps.LeftMargin = 0;
ps.RightMargin = 0;
doc.SetPageSettings(ps);
int col = 1; int row = 1;
foreach (Unit unit in units)
{
for (int qty = 0; qty < unit.qty; qty++)
{
if (col == 5)
{
col = 1;
row++;
}
doc.SetColumnWidth(col, COLUMN_WIDTH_FOR_ARIAL_14);
doc.SetRowHeight(row, ROW_HEIGHT_FOR_ARIAL_14);
doc.SetCellStyle(row, col, style_12);
doc.SetCellValue(row, col++, unit.GetSticker());
}
}
using (var dlg = new SaveFileDialog()) {
dlg.Title = "Сохранение файла наклеек";
dlg.FileName = "Наклейки -- " + spName.Split('\\').Last();
var res = dlg.ShowDialog();
if (res != DialogResult.OK)
{
return;
}
doc.SaveAs(dlg.FileName);
Console.WriteLine("Сохранено в " + dlg.FileName);
}
}
}
private SLDocument CreateHeaderExcel(SLDocument slDocument)
{
int iRow = 10;
slDocument.SetCellValue(iRow, 1, "ID Anggota");
slDocument.SetColumnWidth(1, 10);
//slDocument.MergeWorksheetCells(iRow, 1, iRow + 1, 1);
slDocument.SetCellValue(iRow, 2, "Nama Anggota");
slDocument.SetColumnWidth(2, 20);
//slDocument.MergeWorksheetCells(iRow, 2, iRow + 1, 2);
slDocument.SetCellValue(iRow, 3, "Total Simpanan");
slDocument.SetColumnWidth(3, 15);
//slDocument.MergeWorksheetCells(iRow, 3, iRow + 1, 3);
slDocument.SetCellValue(iRow, 4, "Total Belanja");
slDocument.SetColumnWidth(4, 15);
//slDocument.MergeWorksheetCells(iRow, 4, iRow + 1, 4);
slDocument.SetCellValue(iRow, 5, "Total Pinjaman");
slDocument.SetColumnWidth(5, 15);
//slDocument.MergeWorksheetCells(iRow, 5, iRow + 1, 5);
slDocument.SetCellValue(iRow, 6, "JMA");
slDocument.SetColumnWidth(6, 15);
//slDocument.MergeWorksheetCells(iRow, 6, iRow + 1, 6);
slDocument.SetCellValue(iRow, 7, "JUA");
slDocument.SetColumnWidth(7, 15);
slDocument.SetCellValue(iRow, 8, "JPA");
slDocument.SetColumnWidth(8, 15);
slDocument.SetCellValue(iRow, 9, "Total");
slDocument.SetColumnWidth(9, 15);
SLStyle headerStyle = slDocument.CreateStyle();
headerStyle.Alignment.Horizontal = HorizontalAlignmentValues.Center;
headerStyle.Font.Bold = true;
headerStyle.Border.LeftBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Border.RightBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Border.TopBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Border.BottomBorder.BorderStyle = BorderStyleValues.Thin;
//headerStyle.Fill.SetPattern(PatternValues.Solid, System.Drawing.Color.LightGreen, System.Drawing.Color.LightGreen);
headerStyle.SetWrapText(true);
headerStyle.SetVerticalAlignment(VerticalAlignmentValues.Center);
headerStyle.Font.FontSize = 10;
slDocument.SetCellStyle(10, 1, iRow, 9, headerStyle);
return(slDocument);
}
void SetColumns(SLDocument document, ProjectReportViewModel reportVm)
{
SLStyle style = document.CreateStyle();
style.SetVerticalAlignment(VerticalAlignmentValues.Center);
style.Alignment.Horizontal = HorizontalAlignmentValues.Left;
style.Alignment.ReadingOrder = SLAlignmentReadingOrderValues.LeftToRight;
style.Alignment.ShrinkToFit = true;
style.Alignment.TextRotation = 90;
//document.SetCellValue(1, 1, "Проект");
//document.SetCellValue(1, 2, "Объект");
//document.SetCellValue(1, 3, "Код проекта");
//document.SetCellValue(1, 4, "Имя");
document.SetCellValue(1, 1, "Объект");
document.SetCellValue(1, 2, "Код проекта");
document.SetCellValue(1, 3, "Название проекта");
document.SetCellValue(1, 4, "Инженер");
document.SetColumnWidth(1, 17);
document.SetColumnWidth(2, 20);
document.SetColumnWidth(3, 17);
document.SetColumnWidth(4, 17);
for (int i = 0; i < reportVm.MyColumns.Count; i++)
{
document.SetCellValue(1, i + 5, reportVm.MyColumns[i]);
document.SetColumnWidth(i + 5, 4);
// style.SetCellStyle();
document.SetCellStyle(1, i + 5, style);
}
document.SetCellValue(1, reportVm.MyColumns.Count + 5, "Всего часов");
document.SetColumnWidth(reportVm.MyColumns.Count + 5, 17);
document.SetCellValue(1, reportVm.MyColumns.Count + 6, "Деньги");
}
public FileResult ExportarEncargado()
{
/*StringBuilder csv = new StringBuilder();
* string Columnas = string.Format("{0};{1};{2};{3};{4}", "N", "Nombre", "Productos", "Precio", "Local");
* csv.AppendLine(Columnas);
*
* decimal costoTotal = 0;
* TanoNEEntities ctx = new TanoNEEntities();
* Vecinos vecino = ctx.Vecinos.FirstOrDefault(a => a.correo == User.Identity.Name);
*
* Tandas ultima = ctx.Tandas.ToList().LastOrDefault(a => a.fechaCerrado != null);
* var lista = ctx.Compras.Where(a => a.tandaId == ultima.idTanda && (vecino.localId == null ? vecino.comuna == a.Locales.comuna : vecino.localId == a.localId)).OrderBy(a => new { a.Locales.idLocal, a.Vecinos.nombres }).ToList().Select(a => new
* {
* idCompra = a.idCompra,
* nombre = a.Vecinos.nombres,
* productos = string.Join(" - ", a.CompraProducto.ToList().Select(b => "(" + b.cantidad + ") " + b.Productos.producto + " - " + b.Productos.marca + " - " + b.Productos.presentacion + " - " + b.Productos.Precios.LastOrDefault(precio => a.fecha > precio.fecha).precio + "\015")),
* precio = a.CompraProducto.ToList().Sum(b => b.cantidad * b.Productos.Precios.LastOrDefault(precio => a.fecha >= precio.fecha).precio),
* retiro = a.EstadosCompra.codigo,
* local = a.Locales.direccion
* }).ToArray();
*
* for (int x = 0; x < lista.Count(); x++)
* {
* var compra = lista[x];
* string filas = string.Format("{0};{1};{2};${3};{4}", compra.idCompra, compra.nombre, compra.productos, compra.precio, compra.local);
* csv.AppendLine(filas);
* }
*
* using (MemoryStream memoryStream = new MemoryStream(Encoding.Default.GetBytes(csv.ToString())))
* {
* memoryStream.Position = 0;
* return File(memoryStream.ToArray() as byte[], "application/vnd.ms-excel", "Reporte.csv");
* }*/
TanoNEEntities ctx = new TanoNEEntities();
Vecinos vecino = ctx.Vecinos.FirstOrDefault(a => a.correo == User.Identity.Name);
Tandas ultima = ctx.Tandas.ToList().LastOrDefault(a => a.fechaCerrado != null);
DateTime ProximaEntrea = ultima.fechaVenta.HasValue ? ultima.fechaVenta.Value : DateTime.Now;
string nombreLibro = ProximaEntrea.ToString("dd-MM-yyyy") + " Entrega";
using (MemoryStream mem = new MemoryStream())
using (SLDocument sl = new SLDocument())
{
sl.RenameWorksheet(SLDocument.DefaultFirstSheetName, nombreLibro);
SLStyle bordeNegrita = sl.CreateStyle();
bordeNegrita.Border.LeftBorder.Color = System.Drawing.Color.Black;
bordeNegrita.Border.TopBorder.Color = System.Drawing.Color.Black;
bordeNegrita.Border.RightBorder.Color = System.Drawing.Color.Black;
bordeNegrita.Border.BottomBorder.Color = System.Drawing.Color.Black;
bordeNegrita.Font.Bold = true;
bordeNegrita.Border.LeftBorder.BorderStyle = BorderStyleValues.Thin;
bordeNegrita.Border.TopBorder.BorderStyle = BorderStyleValues.Thin;
bordeNegrita.Border.RightBorder.BorderStyle = BorderStyleValues.Thin;
bordeNegrita.Border.BottomBorder.BorderStyle = BorderStyleValues.Thin;
SLStyle bordeIz = sl.CreateStyle();
bordeIz.Border.LeftBorder.Color = System.Drawing.Color.Black;
bordeIz.Border.LeftBorder.BorderStyle = BorderStyleValues.Thin;
SLStyle bordeDe = sl.CreateStyle();
bordeDe.Border.RightBorder.Color = System.Drawing.Color.Black;
bordeDe.Border.RightBorder.BorderStyle = BorderStyleValues.Thin;
SLStyle bordeAr = sl.CreateStyle();
bordeAr.Border.TopBorder.Color = System.Drawing.Color.Black;
bordeAr.Border.TopBorder.BorderStyle = BorderStyleValues.Thin;
SLStyle bordeAb = sl.CreateStyle();
bordeAb.Border.BottomBorder.Color = System.Drawing.Color.Black;
bordeAb.Border.BottomBorder.BorderStyle = BorderStyleValues.Thin;
SLStyle saltoLinea = sl.CreateStyle();
saltoLinea.SetVerticalAlignment(VerticalAlignmentValues.Center);
saltoLinea.SetHorizontalAlignment(HorizontalAlignmentValues.Center);
saltoLinea.SetWrapText(true);
SLStyle rojo = sl.CreateStyle();
rojo.Fill.SetPatternType(PatternValues.Solid);//.BottomBorder.Color = System.Drawing.Color.Red;
rojo.Font.FontColor = System.Drawing.Color.White;
rojo.Fill.SetPatternForegroundColor(System.Drawing.Color.Red);
SLStyle centrado = sl.CreateStyle();
centrado.FormatCode = "$ * #,##0.00";
centrado.Font.FontSize = 10;
centrado.SetHorizontalAlignment(HorizontalAlignmentValues.Right);
sl.SetColumnWidth(1, 30);
sl.SetColumnWidth(2, 65);
sl.SetColumnWidth(3, 10);
sl.SetColumnWidth(4, 10);
sl.SetColumnWidth(5, 35);
sl.SetCellValue(1, 1, "Nombre");
sl.SetCellValue(1, 2, "Producto");
sl.SetCellValue(1, 3, "Precio");
sl.SetCellValue(1, 4, "Total");
sl.SetCellValue(1, 5, "Observaciones");
sl.SetCellStyle(1, 1, 1, 5, bordeNegrita);
string urla = ConfigurationManager.AppSettings["UrlSitio"];
int row = 3;
bordeNegrita.Font.Bold = false;
var lista = ctx.Compras.Where(a => a.tandaId == ultima.idTanda && (vecino.localId == null ? vecino.comuna == a.Locales.comuna : vecino.localId == a.localId)).OrderBy(a => new { a.Locales.idLocal, a.Vecinos.nombres });
foreach (var compra in lista)
{
int totalVecinx = (row + compra.CompraProducto.Count - 1);
sl.SetCellStyle(row, 1, totalVecinx, 1, bordeIz);
sl.SetCellStyle(row, 1, row, 5, bordeAr);
sl.SetCellStyle(totalVecinx, 1, totalVecinx, 5, bordeAb);
for (int x = 1; x < 6; x++)
{
sl.SetCellStyle(row, x, totalVecinx, x, bordeDe);
}
sl.SetCellValue(row, 1, new System.Globalization.CultureInfo("en-US", false).TextInfo.ToTitleCase(compra.Vecinos.nombres.ToLower()) + "\n" + compra.Vecinos.telefono + "\n" + compra.Vecinos.correo);
sl.SetCellStyle(row, 1, saltoLinea);
sl.MergeWorksheetCells(row, 1, totalVecinx, 1);
var ordenado = compra.CompraProducto.OrderBy(a => a.Productos.producto);
decimal totaltotal = 0;
foreach (var compraProducto in ordenado)
{
decimal total = compraProducto.Precios.precio * compraProducto.cantidad;
totaltotal += total;
centrado.Font.Bold = false;
sl.SetCellValue(row, 2, compraProducto.cantidad + ": " + compraProducto.Productos.producto + " - " + compraProducto.Productos.marca + " - " + compraProducto.Productos.presentacion);
sl.SetCellValue(row, 3, total);
sl.SetCellStyle(row, 3, centrado);
if (compraProducto.cantidad > 6)
{
sl.SetCellStyle(row, 2, row, 5, rojo);
sl.SetCellValue(row, 6, "Alerta! Mucha cantidad");
}
row++;
}
centrado.Font.Bold = true;
sl.SetCellValue(row - 1, 4, totaltotal);
sl.SetCellStyle(row - 1, 4, centrado);
}
sl.SaveAs(mem);
mem.Position = 0;
return(File(mem.ToArray(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "Reporte.xlsx"));
}
}
/// -----------------------------------------------------------------------------------------------
/// <summary>
/// Sets up the default styling when user does not predefine styles with a Settings configuration
/// </summary>
/// <returns>Settings: Default Styling</returns>
/// -----------------------------------------------------------------------------------------------
public static Settings SetupDefaultStyles()
{
try
{
/* -------------------------------------------------------------
* Setup primary container for the child datasets
* -----------------------------------------------------------*/
var settings = new Settings
{
// Optional name
Name = "Default Settings Container"
};
/* -------------------------------------------------------------
* Setup the column header base style for the child datasets
* -----------------------------------------------------------*/
var baseColumnHeaderStyle = new SLStyle();
baseColumnHeaderStyle.SetHorizontalAlignment(HorizontalAlignmentValues.Center);
baseColumnHeaderStyle.SetVerticalAlignment(VerticalAlignmentValues.Center);
baseColumnHeaderStyle.Fill.SetPattern(PatternValues.Solid, Color.DimGray, Color.White);
baseColumnHeaderStyle.SetBottomBorder(BorderStyleValues.Medium, Color.Black);
baseColumnHeaderStyle.SetTopBorder(BorderStyleValues.Medium, Color.Black);
baseColumnHeaderStyle.SetVerticalBorder(BorderStyleValues.Medium, Color.Black);
baseColumnHeaderStyle.Border.SetRightBorder(BorderStyleValues.Medium, Color.Black);
baseColumnHeaderStyle.Border.SetLeftBorder(BorderStyleValues.Medium, Color.Black);
baseColumnHeaderStyle.SetFont("Helvetica", 11);
baseColumnHeaderStyle.SetFontColor(Color.White);
baseColumnHeaderStyle.SetFontBold(true);
/* -------------------------------------------------------------
* Setup the odd row style for the child datasets
* -----------------------------------------------------------*/
var oddRowStyle = new SLStyle();
oddRowStyle.SetHorizontalAlignment(HorizontalAlignmentValues.Left);
oddRowStyle.SetVerticalAlignment(VerticalAlignmentValues.Center);
oddRowStyle.Fill.SetPattern(PatternValues.Solid, Color.White, Color.Black);
oddRowStyle.SetFont("Helvetica", 10);
oddRowStyle.SetFontColor(Color.Black);
/* -------------------------------------------------------------
* Setup the even row style derived from the odd,
* change only what is necessary.
* -----------------------------------------------------------*/
var evenRowStyle = oddRowStyle.Clone();
evenRowStyle.Fill.SetPattern(PatternValues.Solid, Color.WhiteSmoke, Color.Black);
/* -------------------------------------------------------------
* Define and style base child settings.
* This Child will always be present, it represents the
* primary dataset for every export.
* -----------------------------------------------------------*/
settings.ChildSettings.Add(new ChildSetting
(
// SheetName (Optional)
"Default Base Child Settings",
// Set Overall Column Visibility
true,
// Column offset to the right
0,
// Make the base column header row a little larger
// so it will stand out. Value is in pixels
25,
// Setup the style for Column Headers
baseColumnHeaderStyle,
// Row and Alternating Row Styles
// If set to false then the odd row style will be overall row style
true,
// Setup the style for odd & even rows
oddRowStyle,
evenRowStyle,
// No User-Defined column headers
null
));
/* ------------------------------------------------------------
* The first child column headers stylings will be derived
* from the base, change only what needs to be changed.
* ----------------------------------------------------------*/
var firstColumnHeaderStyle = baseColumnHeaderStyle.Clone();
firstColumnHeaderStyle.Fill.SetPattern(PatternValues.Solid, Color.DarkGray, Color.Black);
firstColumnHeaderStyle.SetBottomBorder(BorderStyleValues.Thin, Color.DarkSlateGray);
firstColumnHeaderStyle.SetTopBorder(BorderStyleValues.Thin, Color.DarkSlateGray);
firstColumnHeaderStyle.SetVerticalBorder(BorderStyleValues.Thin, Color.DarkSlateGray);
firstColumnHeaderStyle.Border.SetRightBorder(BorderStyleValues.Thin, Color.DarkSlateGray);
firstColumnHeaderStyle.Border.SetLeftBorder(BorderStyleValues.Thin, Color.DarkSlateGray);
firstColumnHeaderStyle.SetFont("Helvetica", 10);
firstColumnHeaderStyle.SetFontColor(Color.Black);
/* -------------------------------------------------------------
* Define and add the stylings for the first child, which is
* a child of the base data-set
* -----------------------------------------------------------*/
settings.ChildSettings.Add(new ChildSetting
(
"Default First Child Settings",
true,
null,
null,
firstColumnHeaderStyle,
true,
oddRowStyle,
evenRowStyle,
null
));
/* -------------------------------------------------------------
* The second child column headers stylings will be derived
* from the first, change only what needs to be changed.
* -----------------------------------------------------------*/
var secondColumnHeaderStyle = firstColumnHeaderStyle.Clone();
secondColumnHeaderStyle.Fill.SetPattern(PatternValues.Solid, Color.CadetBlue, Color.White);
secondColumnHeaderStyle.SetFontColor(Color.White);
/* -------------------------------------------------------------
* Define and add the stylings for the second child, which is
* a child of the first data-set
* -----------------------------------------------------------*/
settings.ChildSettings.Add(new ChildSetting
(
"Default Second Child Settings",
true,
null,
null,
secondColumnHeaderStyle,
true,
oddRowStyle,
evenRowStyle,
null
));
/* -------------------------------------------------------------
* The third child column headers stylings will be derived
* from the first, change only what needs to be changed.
* -----------------------------------------------------------*/
var thirdColumnHeaderStyle = firstColumnHeaderStyle.Clone();
thirdColumnHeaderStyle.Fill.SetPattern(PatternValues.Solid, Color.Aqua, Color.Black);
thirdColumnHeaderStyle.SetFontColor(Color.Black);
/* -------------------------------------------------------------
* Define and add the stylings for the third child, which is
* a child of the second data-set
* -----------------------------------------------------------*/
settings.ChildSettings.Add(new ChildSetting
(
"Default Third Child Settings",
true,
null,
null,
thirdColumnHeaderStyle,
true,
oddRowStyle,
evenRowStyle,
null
));
/* -------------------------------------------------------------
* The forth child column headers stylings will be derived
* from the first, change only what needs to be changed.
* -----------------------------------------------------------*/
var fourthColumnHeaderStyle = firstColumnHeaderStyle.Clone();
fourthColumnHeaderStyle.Fill.SetPattern(PatternValues.Solid, Color.Chartreuse, Color.Black);
fourthColumnHeaderStyle.SetFontColor(Color.Black);
/* -------------------------------------------------------------
* Define and add the stylings for the fourth child, which is
* a child of the third data-set
* -----------------------------------------------------------*/
settings.ChildSettings.Add(new ChildSetting
(
"Default Fourth Child Settings",
true,
null,
null,
fourthColumnHeaderStyle,
true,
oddRowStyle,
evenRowStyle,
null
));
/* -------------------------------------------------------------
* If five deep isn't enough let's add a sixth one.
* The fifth child column headers stylings will be derived
* from the first, change only what needs to be changed.
* -----------------------------------------------------------*/
var fifthColumnHeaderStyle = firstColumnHeaderStyle.Clone();
fifthColumnHeaderStyle.Fill.SetPattern(PatternValues.Solid, Color.BlueViolet, Color.Black);
fifthColumnHeaderStyle.SetFontColor(Color.White);
/* -------------------------------------------------------------
* Define and add the stylings for the fifth child, which is
* a child of the fourth data-set
* -----------------------------------------------------------*/
settings.ChildSettings.Add(new ChildSetting
(
"Default Fifth Child Settings",
true,
null,
null,
fifthColumnHeaderStyle,
true,
oddRowStyle,
evenRowStyle,
null
));
return(settings);
}
catch (Exception ex)
{
Log.Error("SpreadsheetLightWrapper.Export.DefaultStyles.SetupDefaultStyles -> " + ex.Message + ": " + ex);
}
return(null);
}
private SLDocument CreateDataExcel(SLDocument slDocument, List <IptAnggotaDto> listData)
{
int iRow = 11; //starting row data
int row = 1;
try
{
foreach (var data in listData)
{
int col = 1;
slDocument.SetCellValue(iRow, col, data.Id);
col++;
slDocument.SetCellValue(iRow, col, data.IdAnggota);
col++;
slDocument.SetCellValue(iRow, col, data.NamaAnggota);
col++;
slDocument.SetCellValue(iRow, col, data.Tanggal);
col++;
slDocument.SetCellValue(iRow, col, data.Pokok);
col++;
slDocument.SetCellValue(iRow, col, data.Wajib);
col++;
slDocument.SetCellValue(iRow, col, data.Sukarela);
col++;
slDocument.SetCellValue(iRow, col, data.Belanja);
col++;
slDocument.SetCellValue(iRow, col, data.BungaPinjaman);
iRow++;
row++;
}
}
catch (Exception)
{
throw;
}
//create style
SLStyle valueStyle = slDocument.CreateStyle();
valueStyle.Border.LeftBorder.BorderStyle = BorderStyleValues.Thin;
valueStyle.Border.RightBorder.BorderStyle = BorderStyleValues.Thin;
valueStyle.Border.TopBorder.BorderStyle = BorderStyleValues.Thin;
valueStyle.Border.BottomBorder.BorderStyle = BorderStyleValues.Thin;
SLStyle dateStyle = slDocument.CreateStyle();
dateStyle.FormatCode = "dd-MMM-yyyy";
SLStyle hourStyle = slDocument.CreateStyle();
hourStyle.FormatCode = "HH:mm";
SLStyle decimalFormat = slDocument.CreateStyle();
decimalFormat.FormatCode = "#,##0.00";
SLStyle decimalFormat2 = slDocument.CreateStyle();
decimalFormat2.FormatCode = "Rp #,##0.00";
slDocument.SetCellStyle(11, 3, iRow, 9, decimalFormat);
valueStyle.SetHorizontalAlignment(HorizontalAlignmentValues.Center);
valueStyle.SetVerticalAlignment(VerticalAlignmentValues.Center);
valueStyle.Font.FontSize = 10;
slDocument.SetCellStyle(10, 1, iRow, 9, valueStyle);
return(slDocument);
}
/// -----------------------------------------------------------------------------------------------
/// <summary>
/// This setting creator has user-defined styles and columns for the four data-tables in
/// mock data; Directors, Managers, Team Leads & Associates.
/// Displays a variety of ways to access the Export library with Constructor and
/// Property Dependency Injection.
/// </summary>
/// <returns>Settings: Custom Styling</returns>
/// -----------------------------------------------------------------------------------------------
public static Settings SetupCustomStyles()
{
try
{
var childList = new List <ChildSetting>();
/* -------------------------------------------------------------
* Setup the column header base style for the child datasets
* -----------------------------------------------------------*/
var baseColumnHeaderStyle = new SLStyle();
baseColumnHeaderStyle.SetHorizontalAlignment(HorizontalAlignmentValues.Center);
baseColumnHeaderStyle.SetVerticalAlignment(VerticalAlignmentValues.Center);
baseColumnHeaderStyle.Fill.SetPattern(PatternValues.Solid, Color.DimGray, Color.White);
baseColumnHeaderStyle.SetBottomBorder(BorderStyleValues.Medium, Color.Black);
baseColumnHeaderStyle.SetTopBorder(BorderStyleValues.Medium, Color.Black);
baseColumnHeaderStyle.SetVerticalBorder(BorderStyleValues.Medium, Color.Black);
baseColumnHeaderStyle.Border.SetRightBorder(BorderStyleValues.Medium, Color.Black);
baseColumnHeaderStyle.Border.SetLeftBorder(BorderStyleValues.Medium, Color.Black);
baseColumnHeaderStyle.SetFont("Britannic Bold", 12);
baseColumnHeaderStyle.SetFontColor(Color.White);
baseColumnHeaderStyle.SetFontBold(true);
/* -------------------------------------------------------------
* Setup the odd row style for the child datasets
* -----------------------------------------------------------*/
var oddRowStyle = new SLStyle();
oddRowStyle.SetHorizontalAlignment(HorizontalAlignmentValues.Left);
oddRowStyle.SetVerticalAlignment(VerticalAlignmentValues.Center);
oddRowStyle.Fill.SetPattern(PatternValues.Solid, Color.White, Color.Black);
oddRowStyle.SetFont("Helvetica", 10);
oddRowStyle.SetFontColor(Color.Black);
/* -------------------------------------------------------------
* Setup the even row style derived from the odd,
* change only what is necessary.
* -----------------------------------------------------------*/
var evenRowStyle = oddRowStyle.Clone();
evenRowStyle.Fill.SetPattern(PatternValues.Solid, Color.WhiteSmoke, Color.Black);
/* ------------------------------------------------------------
* Create the user-defined columns with property dependency
* injection for the base dataset.
* With this method hover the cursor over the property and
* intellisense will show the comments for it.
* ----------------------------------------------------------*/
var columns = new List <Column>
{
// Since this id column is not set to visible, you can just leave it out and it will be ignored
new Column
{
BoundColumnName = "DID",
UserDefinedColumnName = "ID",
NumberFormat = NumberFormats.General,
HorizontalAlignment = HorizontalAlignmentValues.Center,
ShowField = false,
FieldOrder = 0
},
new Column
{
BoundColumnName = "SheetName",
UserDefinedColumnName = "Director",
NumberFormat = NumberFormats.General,
HorizontalAlignment = HorizontalAlignmentValues.Left,
ShowField = true,
FieldOrder = 1
},
new Column
{
BoundColumnName = "Age",
UserDefinedColumnName = "Chronology",
NumberFormat = NumberFormats.Decimal0,
HorizontalAlignment = HorizontalAlignmentValues.Center,
ShowField = true,
FieldOrder = 2
},
new Column
{
BoundColumnName = "Income",
UserDefinedColumnName = "Compensation",
NumberFormat = NumberFormats.Accounting2Red,
HorizontalAlignment = HorizontalAlignmentValues.Right,
ShowField = true,
FieldOrder = 3
},
new Column
{
BoundColumnName = "Member",
UserDefinedColumnName = "Member ?",
NumberFormat = NumberFormats.General,
HorizontalAlignment = HorizontalAlignmentValues.Center,
ShowField = true,
FieldOrder = 4
},
new Column
{
BoundColumnName = "Registered",
UserDefinedColumnName = "Date Registered",
NumberFormat = NumberFormats.DateShort5,
HorizontalAlignment = HorizontalAlignmentValues.Center,
ShowField = true,
FieldOrder = 5
}
};
/* -------------------------------------------------------------
* Define and style base child settings.
* This Child will always be present, it represents the
* primary dataset for every export and is not really a child.
* Using Property Injection Technique
* -----------------------------------------------------------*/
childList.Add(new ChildSetting
{
// Optional name
SheetName = "Directors",
// Set column visibility
ShowColumnHeader = true,
// Make the base column header row a little larger
// so it will stand out. Value is in pixels
ColumnHeaderRowHeight = 25,
// Setup the style for Column Headers
ColumnHeaderStyle = baseColumnHeaderStyle,
// Row and Alternating Row Styles
// If set to false then the odd row style will be overall row style
ShowAlternatingRows = false,
// Setup the style for all rows
OddRowStyle = oddRowStyle,
EvenRowStyle = null,
// Add the user-defined columns
UserDefinedColumns = columns
});
/* ------------------------------------------------------------
* The first child column headers stylings will be derived
* from the base, change only what needs to be changed.
* ----------------------------------------------------------*/
var firstColumnHeaderStyle = baseColumnHeaderStyle.Clone();
firstColumnHeaderStyle.Fill.SetPattern(PatternValues.Solid, Color.DarkGray, Color.Black);
firstColumnHeaderStyle.SetBottomBorder(BorderStyleValues.Thin, Color.DarkSlateGray);
firstColumnHeaderStyle.SetTopBorder(BorderStyleValues.Thin, Color.DarkSlateGray);
firstColumnHeaderStyle.SetVerticalBorder(BorderStyleValues.Thin, Color.DarkSlateGray);
firstColumnHeaderStyle.Border.SetRightBorder(BorderStyleValues.Thin, Color.DarkSlateGray);
firstColumnHeaderStyle.Border.SetLeftBorder(BorderStyleValues.Thin, Color.DarkSlateGray);
firstColumnHeaderStyle.SetFont("Helvetica", 10);
firstColumnHeaderStyle.SetFontColor(Color.Black);
/* ------------------------------------------------------------
* Create the user-defined columns with constructor dependency
* injection for the base dataset.
* Hover the cursor over the property and intellisense will
* show the comments for it.
* ----------------------------------------------------------*/
columns = new List <Column>
{
new Column
(
"SheetName",
"Managers",
NumberFormats.General,
HorizontalAlignmentValues.Left,
true,
1
),
new Column
(
"Age",
"Age",
NumberFormats.Decimal0,
HorizontalAlignmentValues.Center,
true,
2
),
new Column
(
"Income",
"Compensation",
NumberFormats.Currency0Black,
HorizontalAlignmentValues.Right,
true,
3
),
new Column
(
"Registered",
"Date Registered",
NumberFormats.DateShort1,
HorizontalAlignmentValues.Center,
true,
5
)
};
/* -------------------------------------------------------------
* Define and add the first child
* Using Constructor dependency injection
* -----------------------------------------------------------*/
childList.Add(new ChildSetting
(
"Managers", // SheetName
true, // Show Column Headers
1, // Column Offset to the Right
null, // Column Header Row Height
firstColumnHeaderStyle, // Column Header Style
false, // Show Alternating Rows, false will default to Odd
oddRowStyle, // Odd Row Style
null, // Even Row Style
columns // User-Defined Column (UDCs)
));
/* -------------------------------------------------------------
* The second child column headers stylings will be derived
* from the first, change only what needs to be changed.
* -----------------------------------------------------------*/
var secondColumnHeaderStyle = firstColumnHeaderStyle.Clone();
secondColumnHeaderStyle.Fill.SetPattern(PatternValues.Solid, Color.CadetBlue, Color.White);
secondColumnHeaderStyle.SetFontColor(Color.White);
/* -------------------------------------------------------------
* Define and add the second child
* Using Constructor dependency injection
* -----------------------------------------------------------*/
childList.Add(new ChildSetting(
"Team Leads", // SheetName
true, // Show Column Headers
2, // Column Offset to the Right
null, // Column Header Row Height
secondColumnHeaderStyle, // Column Header Style
false, // Show Alternating Rows, false will default to Odd
oddRowStyle, // Odd Row Style
null, // Even Row Style
new List <Column> // User-Defined Column (UDCs)
{
new Column("TLID", "Team Lead ID", NumberFormats.General, HorizontalAlignmentValues.Left, true,
6),
new Column("Registered", "Registration Date", NumberFormats.UserDefined,
HorizontalAlignmentValues.Center, true, 2, "d-mmm-yy"),
new Column("SheetName", "Team Leads", NumberFormats.General, HorizontalAlignmentValues.Left,
true, 0),
new Column("Age", "How Old?", NumberFormats.General, HorizontalAlignmentValues.Center, true, 1),
new Column("Member", "Member?", NumberFormats.General, HorizontalAlignmentValues.Center, true, 3),
new Column("Income", "Income", NumberFormats.Accounting2Red, HorizontalAlignmentValues.Right,
true, 4),
new Column("MID", "Foreign Key", NumberFormats.General, HorizontalAlignmentValues.Right, false)
}
));
/* -------------------------------------------------------------
* The third child column headers stylings will be derived
* from the first, change only what needs to be changed.
* -----------------------------------------------------------*/
var thirdColumnHeaderStyle = firstColumnHeaderStyle.Clone();
thirdColumnHeaderStyle.Fill.SetPattern(PatternValues.Solid, Color.Aqua, Color.Black);
thirdColumnHeaderStyle.SetFont("Blackadder ITC", 11);
thirdColumnHeaderStyle.SetFontColor(Color.Black);
/* -------------------------------------------------------------
* Define and add the third child
* Constructor Injection on all
* -----------------------------------------------------------*/
childList.Add(new ChildSetting("Associates", true, 3, 30, thirdColumnHeaderStyle, true, oddRowStyle,
evenRowStyle,
new List <Column>
{
new Column("Registered", "Date", NumberFormats.TimeStamp124, HorizontalAlignmentValues.Left,
true, 3),
new Column("Member", "Member?", NumberFormats.General, HorizontalAlignmentValues.Center, true, 2),
new Column("SheetName", "Associate", NumberFormats.General, HorizontalAlignmentValues.Left, true,
0)
}
));
/* -------------------------------------------------------------
* Setup and return the primary container for the child datasets
* Using Constructor Injection as well
* -----------------------------------------------------------*/
return(new Settings("Organization", childList));
}
catch (Exception ex)
{
Assert.Fail("Exception Fail: " + ex.Message);
}
return(null);
}
private SLDocument CreateDataExcel(SLDocument slDocument, List <BagiShuDto> listData)
{
int iRow = 11; //starting row data
int row = 1;
try
{
foreach (var data in listData)
{
slDocument.SetCellValue(iRow, 1, data.IdAnggota);
slDocument.SetCellValue(iRow, 2, data.NamaAnggota);
slDocument.SetCellValue(iRow, 3, data.TotalSimpanan);
slDocument.SetCellValue(iRow, 4, data.TotalBelanja);
slDocument.SetCellValue(iRow, 5, data.TotalBungaPinjaman);
slDocument.SetCellValue(iRow, 6, data.Jma);
slDocument.SetCellValue(iRow, 7, data.Jua);
slDocument.SetCellValue(iRow, 8, data.Jpa);
slDocument.SetCellValue(iRow, 9, data.TotalShu);
iRow++;
row++;
}
}
catch (Exception)
{
throw;
}
//create style
SLStyle valueStyle = slDocument.CreateStyle();
valueStyle.Border.LeftBorder.BorderStyle = BorderStyleValues.Thin;
valueStyle.Border.RightBorder.BorderStyle = BorderStyleValues.Thin;
valueStyle.Border.TopBorder.BorderStyle = BorderStyleValues.Thin;
valueStyle.Border.BottomBorder.BorderStyle = BorderStyleValues.Thin;
SLStyle dateStyle = slDocument.CreateStyle();
dateStyle.FormatCode = "dd-MMM-yyyy";
SLStyle hourStyle = slDocument.CreateStyle();
hourStyle.FormatCode = "HH:mm";
SLStyle decimalFormat = slDocument.CreateStyle();
decimalFormat.FormatCode = "#,##0.00";
SLStyle decimalFormat2 = slDocument.CreateStyle();
decimalFormat2.FormatCode = "Rp #,##0.00";
slDocument.SetCellStyle(11, 3, iRow, 9, decimalFormat);
valueStyle.SetHorizontalAlignment(HorizontalAlignmentValues.Center);
valueStyle.SetVerticalAlignment(VerticalAlignmentValues.Center);
valueStyle.Font.FontSize = 10;
slDocument.SetCellStyle(10, 1, iRow, 9, valueStyle);
return(slDocument);
}
public ActionResult CrearExcel(int idTanda, bool porLocal = false)
{
/*string handle = Guid.NewGuid().ToString();
*
* StringBuilder csv = new StringBuilder();
* string Columnas = null;
* if (porLocal)
* Columnas = ""; // string.Format("{0};{1};{2};{3}", "N", "Local", "Productos");
* else
* Columnas = string.Format("{0};{1};{2};{3}", "N", "Producto", "Presentacion", "Cantidad", "Costo(Aprox)");
* csv.AppendLine(Columnas);
*
*
* TanoNEEntities ctx = new TanoNEEntities();
* Tandas actual = ctx.Tandas.FirstOrDefault(a => a.idTanda == idTanda);
*
* if (porLocal)
* {
* decimal costoTotal = 0;
* var locales = ctx.Compras.Where(a => a.tandaId == idTanda).Select(a => a.Locales).Distinct().ToList();
* foreach (var local in locales)
* {
* csv.AppendLine(local.direccion);
* var listado = ctx.CompraProducto.Where(a => a.Compras.localId == local.idLocal && a.Compras.tandaId == idTanda).GroupBy(a => a.productoId).Select(a => new { idProducto = a.Key, Cantidad = a.Sum(b => b.cantidad) }).ToArray();
* for (int x = 0; x < listado.Count(); x++)
* {
* var compra = listado[x];
* Productos prod = ctx.Productos.FirstOrDefault(a => a.idProducto == compra.idProducto);
* Costos ultimo = prod.Costos.Count > 1 ? prod.Costos.LastOrDefault(a => a.fecha.Date <= actual.fechaAbierto.Date) : prod.Costos.FirstOrDefault();
* decimal costo = ultimo.costo * compra.Cantidad;
* costoTotal += costo;
* string filas = string.Format(";{0};{1};${2}", compra.Cantidad, string.Format("{0} - {1} - {2}", prod.producto, prod.marca, prod.presentacion), costo.ToString("0.00", System.Globalization.CultureInfo.InvariantCulture));
* csv.AppendLine(filas);
* }
* }
* string cierre = string.Format("Total;;;${0}", costoTotal.ToString("0.00", System.Globalization.CultureInfo.InvariantCulture));
* csv.AppendLine(cierre);
* }
* else
* {
* var listado = ctx.CompraProducto.Where(a => a.Compras.tandaId == idTanda).GroupBy(a => a.productoId).Select(a => new { idProducto = a.Key, Cantidad = a.Sum(b => b.cantidad) }).ToArray();
* decimal costoTotal = 0;
* for (int x = 0; x < listado.Count(); x++)
* {
* var compra = listado[x];
* Productos prod = ctx.Productos.FirstOrDefault(a => a.idProducto == compra.idProducto);
* Costos ultimo = prod.Costos.Count > 1 ? (prod.Costos.LastOrDefault(a => a.fecha.Date <= actual.fechaAbierto.Date) == null ? prod.Costos.LastOrDefault() : prod.Costos.FirstOrDefault()) : prod.Costos.FirstOrDefault();
* decimal costo = ultimo.costo * compra.Cantidad;
* //decimal costo = prod.Costos.FirstOrDefault(a => a.fecha <= actual.fechaAbierto).costo * compra.Cantidad;
* costoTotal += costo;
* string filas = string.Format("{0};{1};{2};{3};${4}", x + 1, prod.producto, prod.presentacion, compra.Cantidad, costo.ToString("0.00", System.Globalization.CultureInfo.InvariantCulture));
* csv.AppendLine(filas);
* }
*
* string cierre = string.Format("{0};{1};{2};{3};${4}", "", "", "", "", costoTotal.ToString("0.00", System.Globalization.CultureInfo.InvariantCulture));
* csv.AppendLine(cierre);
* }
*
*
*
* using (MemoryStream memoryStream = new MemoryStream(Encoding.Default.GetBytes(csv.ToString())))
* {
* memoryStream.Position = 0;
* TempData[handle] = memoryStream.ToArray();
* }
*
* return Json(new { FileGuid = handle, FileName = "Reporte.csv" });*/
string handle = Guid.NewGuid().ToString();
TanoNEEntities ctx = new TanoNEEntities();
Tandas actual = ctx.Tandas.FirstOrDefault(a => a.idTanda == idTanda);
using (MemoryStream mem = new MemoryStream())
using (SLDocument sl = new SLDocument())
{
sl.RenameWorksheet(SLDocument.DefaultFirstSheetName, porLocal ? "Local" : "Total");
SLStyle bordeNegrita = sl.CreateStyle();
bordeNegrita.Border.LeftBorder.Color = System.Drawing.Color.Black;
bordeNegrita.Border.TopBorder.Color = System.Drawing.Color.Black;
bordeNegrita.Border.RightBorder.Color = System.Drawing.Color.Black;
bordeNegrita.Border.BottomBorder.Color = System.Drawing.Color.Black;
bordeNegrita.Font.Bold = true;
bordeNegrita.Border.LeftBorder.BorderStyle = BorderStyleValues.Thin;
bordeNegrita.Border.TopBorder.BorderStyle = BorderStyleValues.Thin;
bordeNegrita.Border.RightBorder.BorderStyle = BorderStyleValues.Thin;
bordeNegrita.Border.BottomBorder.BorderStyle = BorderStyleValues.Thin;
SLStyle bordeIz = sl.CreateStyle();
bordeIz.Border.LeftBorder.Color = System.Drawing.Color.Black;
bordeIz.Border.LeftBorder.BorderStyle = BorderStyleValues.Thin;
SLStyle bordeDe = sl.CreateStyle();
bordeDe.Border.RightBorder.Color = System.Drawing.Color.Black;
bordeDe.Border.RightBorder.BorderStyle = BorderStyleValues.Thin;
SLStyle bordeAr = sl.CreateStyle();
bordeAr.Border.TopBorder.Color = System.Drawing.Color.Black;
bordeAr.Border.TopBorder.BorderStyle = BorderStyleValues.Thin;
SLStyle bordeAb = sl.CreateStyle();
bordeAb.Border.BottomBorder.Color = System.Drawing.Color.Black;
bordeAb.Border.BottomBorder.BorderStyle = BorderStyleValues.Thin;
SLStyle saltoLinea = sl.CreateStyle();
saltoLinea.SetVerticalAlignment(VerticalAlignmentValues.Center);
saltoLinea.SetWrapText(true);
SLStyle rojo = sl.CreateStyle();
rojo.Fill.SetPatternType(PatternValues.Solid);//.BottomBorder.Color = System.Drawing.Color.Red;
rojo.Font.FontColor = System.Drawing.Color.White;
rojo.Fill.SetPatternForegroundColor(System.Drawing.Color.Red);
SLStyle centrado = sl.CreateStyle();
centrado.FormatCode = "$ * #,##0.00";
centrado.Font.FontSize = 10;
centrado.SetHorizontalAlignment(HorizontalAlignmentValues.Right);
SLStyle negrita = sl.CreateStyle();
negrita.Font.Bold = true;
int row = 3;
if (porLocal)
{
sl.SetColumnWidth(1, 16);
sl.SetColumnWidth(2, 10);
sl.SetColumnWidth(3, 65);
sl.SetColumnWidth(4, 15);
sl.SetColumnWidth(5, 15);
sl.SetCellValue(1, 1, "Local");
sl.SetCellValue(1, 2, "Cantidad");
sl.SetCellValue(1, 3, "Producto");
sl.SetCellValue(1, 4, "Costo");
sl.SetCellValue(1, 5, "Costo Total");
sl.SetCellStyle(1, 1, 1, 5, bordeNegrita);
decimal total = 0;
var locales = ctx.Compras.Where(a => a.tandaId == idTanda).Select(a => a.Locales).Distinct().OrderBy(a => new { a.comuna, a.nombre }).ToList();
foreach (var local in locales)
{
var listado = ctx.CompraProducto.Where(a => a.Compras.localId == local.idLocal && a.Compras.tandaId == idTanda).OrderBy(a => a.Productos.producto).GroupBy(a => a.productoId).Select(a => new { idProducto = a.Key, cp = a.FirstOrDefault(), Cantidad = a.Sum(b => b.cantidad) });
int totalVecinx = (row + listado.Count() - 1);
sl.SetCellStyle(row, 1, totalVecinx, 1, bordeIz);
sl.SetCellStyle(row, 1, row, 5, bordeAr);
sl.SetCellStyle(totalVecinx, 1, totalVecinx, 5, bordeAb);
for (int x = 1; x < 6; x++)
{
sl.SetCellStyle(row, x, totalVecinx, x, bordeDe);
}
sl.SetCellValue(row, 1, local.direccion + (local.nombre != null ? "\n" + local.nombre : ""));
sl.SetCellStyle(row, 1, saltoLinea);
sl.MergeWorksheetCells(row, 1, totalVecinx, 1);
decimal subTotalLocal = 0;
foreach (var productos in listado)
{
Productos prod = ctx.Productos.FirstOrDefault(a => a.idProducto == productos.idProducto);
Costos ultimo = productos.cp.Costos;
decimal costoTotal = ultimo.costo * productos.Cantidad;
subTotalLocal += costoTotal;
sl.SetCellValue(row, 2, productos.Cantidad);
sl.SetCellValue(row, 3, prod.producto + " - " + prod.marca + " - " + prod.presentacion);
sl.SetCellValue(row, 4, ultimo.costo);
sl.SetCellValue(row, 5, costoTotal);
sl.SetCellStyle(row, 4, centrado);
sl.SetCellStyle(row, 5, centrado);
row++;
}
total += subTotalLocal;
sl.SetCellValue(row, 4, "Subtotal local: ");
sl.SetCellStyle(row, 4, negrita);
sl.SetCellValue(row, 5, subTotalLocal);
sl.SetCellStyle(row, 5, centrado);
sl.SetCellStyle(row, 5, negrita);
row++;
row++;
}
row++;
sl.SetCellValue(row, 4, "Total: ");
sl.SetCellStyle(row, 4, negrita);
sl.SetCellValue(row, 5, total);
sl.SetCellStyle(row, 5, centrado);
sl.SetCellStyle(row, 5, negrita);
}
else
{
sl.SetColumnWidth(1, 5);
sl.SetColumnWidth(2, 10);
sl.SetColumnWidth(3, 65);
sl.SetColumnWidth(4, 20);
sl.SetColumnWidth(5, 20);
sl.SetColumnWidth(6, 15);
sl.SetColumnWidth(7, 15);
sl.SetCellValue(1, 1, "N°");
sl.SetCellValue(1, 2, "Cantidad");
sl.SetCellValue(1, 3, "Producto");
sl.SetCellValue(1, 4, "Marca");
sl.SetCellValue(1, 5, "Presentacion");
sl.SetCellValue(1, 6, "Costo");
sl.SetCellValue(1, 7, "Costo Total");
sl.SetCellStyle(1, 1, 1, 7, bordeNegrita);
var listado = ctx.CompraProducto.Where(a => a.Compras.tandaId == idTanda).GroupBy(a => a.productoId).Select(a => new { idProducto = a.Key, Cantidad = a.Sum(b => b.cantidad), Compra = a.FirstOrDefault() }).OrderBy(a => new { a.Compra.Productos.Categorias.nombre, a.Compra.Productos.producto }).ToList();
decimal subTotal = 0;
for (int x = 0; x < listado.Count(); x++)
{
var compra = listado.ElementAt(x);
Costos ultimo = compra.Compra.Costos; //.Costos.Count > 1 ? (compra.Compra.Productos.Costos.LastOrDefault(a => a.fecha.Date <= actual.fechaAbierto.Date) == null ? compra.Compra.Productos.Costos.LastOrDefault() : compra.Compra.Productos.Costos.FirstOrDefault()) : compra.Compra.Productos.Costos.FirstOrDefault();
decimal costoTotal = ultimo.costo * compra.Cantidad;
subTotal += costoTotal;
sl.SetCellValue(row, 1, x + 1);
sl.SetCellValue(row, 2, compra.Cantidad);
sl.SetCellValue(row, 3, compra.Compra.Productos.producto + " - " + compra.Compra.Productos.marca + " - " + compra.Compra.Productos.presentacion);
sl.SetCellValue(row, 4, compra.Compra.Productos.marca);
sl.SetCellValue(row, 5, compra.Compra.Productos.presentacion);
sl.SetCellValue(row, 6, ultimo.costo);
sl.SetCellValue(row, 7, costoTotal);
sl.SetCellStyle(row, 6, centrado);
sl.SetCellStyle(row, 7, centrado);
row++;
}
centrado.Font.Bold = true;
sl.SetCellValue(row, 5, "Total: ");
sl.SetCellValue(row, 7, subTotal);
sl.SetCellStyle(row, 7, centrado);
}
sl.SaveAs(mem);
mem.Position = 0;
TempData[handle] = mem.ToArray();
return(Json(new { FileGuid = handle, FileName = porLocal ? "Local.xlsx" : "Total.xlsx" }));
}
}
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);
}
public ActionResult CrearExcelFinanzas(int idTanda)
{
/*string handle = Guid.NewGuid().ToString();
*
* StringBuilder csv = new StringBuilder();
* string Columnas = string.Format(";{0};{1};{2};{3};{4}", "Cantidad", "Producto", "Costo", "Precio", "Diferencia");
* csv.AppendLine(Columnas);
*
*
* TanoNEEntities ctx = new TanoNEEntities();
* Tandas actual = ctx.Tandas.FirstOrDefault(a => a.idTanda == idTanda);
*
*
* var locales = ctx.Compras.Where(a => a.tandaId == idTanda).Select(a => a.Locales).Distinct().ToList();
* foreach (var local in locales)
* {
* csv.AppendLine(local.direccion);
* decimal costoLocal = 0;
* decimal precioLocal = 0;
* var listado = ctx.CompraProducto.Where(a => a.Compras.localId == local.idLocal && a.Compras.tandaId == idTanda).GroupBy(a => a.productoId).Select(a => new { idProducto = a.Key, Cantidad = a.Sum(b => b.cantidad) }).ToArray();
* for (int x = 0; x < listado.Count(); x++)
* {
* var compra = listado[x];
* Productos prod = ctx.Productos.FirstOrDefault(a => a.idProducto == compra.idProducto);
*
* Costos ultimoc = prod.Costos.Count > 1 ? prod.Costos.LastOrDefault(a => a.fecha.Date <= actual.fechaAbierto.Date) : prod.Costos.FirstOrDefault();
* decimal costo = ultimoc.costo * compra.Cantidad;
* //decimal costo = prod.Costos.FirstOrDefault(a => a.fecha <= actual.fechaAbierto).costo * compra.Cantidad;
*
* Precios ultimop = prod.Precios.Count > 1 ? prod.Precios.LastOrDefault(a => a.fecha.Date <= actual.fechaAbierto.Date) : prod.Precios.FirstOrDefault();
* decimal precio = ultimop.precio * compra.Cantidad;
* //decimal precio = prod.Precios.FirstOrDefault(a => a.fecha <= actual.fechaAbierto).precio * compra.Cantidad;
* costoLocal += costo;
* precioLocal += precio;
* string filas = string.Format(";{0};{1};${2};${3};${4}", compra.Cantidad, string.Format("{0} - {1} - {2}", prod.producto, prod.marca, prod.presentacion), costo.ToString("0.00", System.Globalization.CultureInfo.InvariantCulture), precio.ToString("0.00", System.Globalization.CultureInfo.InvariantCulture), (precio - costo).ToString("0.00", System.Globalization.CultureInfo.InvariantCulture));
* csv.AppendLine(filas);
* }
* csv.AppendLine(string.Format(";;;${0};${1};${2}", costoLocal.ToString("0.00", System.Globalization.CultureInfo.InvariantCulture), precioLocal.ToString("0.00", System.Globalization.CultureInfo.InvariantCulture), (precioLocal - costoLocal).ToString("0.00", System.Globalization.CultureInfo.InvariantCulture)));
* }
* //string cierre = string.Format("Total;;;${0}", costoTotal.ToString("0.00", System.Globalization.CultureInfo.InvariantCulture));
* //csv.AppendLine(cierre);
*
* using (MemoryStream memoryStream = new MemoryStream(Encoding.Default.GetBytes(csv.ToString())))
* {
* memoryStream.Position = 0;
* TempData[handle] = memoryStream.ToArray();
* }
*
* return Json(new { FileGuid = handle, FileName = "Reporte.csv" });*/
string handle = Guid.NewGuid().ToString();
TanoNEEntities ctx = new TanoNEEntities();
Tandas actual = ctx.Tandas.FirstOrDefault(a => a.idTanda == idTanda);
EstadosCompra entre = ctx.EstadosCompra.FirstOrDefault(a => a.codigo == 3);
using (MemoryStream mem = new MemoryStream())
using (SLDocument sl = new SLDocument())
{
sl.RenameWorksheet(SLDocument.DefaultFirstSheetName, "Finanzas");
SLStyle bordeNegrita = sl.CreateStyle();
bordeNegrita.Border.LeftBorder.Color = System.Drawing.Color.Black;
bordeNegrita.Border.TopBorder.Color = System.Drawing.Color.Black;
bordeNegrita.Border.RightBorder.Color = System.Drawing.Color.Black;
bordeNegrita.Border.BottomBorder.Color = System.Drawing.Color.Black;
bordeNegrita.Font.Bold = true;
bordeNegrita.Border.LeftBorder.BorderStyle = BorderStyleValues.Thin;
bordeNegrita.Border.TopBorder.BorderStyle = BorderStyleValues.Thin;
bordeNegrita.Border.RightBorder.BorderStyle = BorderStyleValues.Thin;
bordeNegrita.Border.BottomBorder.BorderStyle = BorderStyleValues.Thin;
SLStyle bordeIz = sl.CreateStyle();
bordeIz.Border.LeftBorder.Color = System.Drawing.Color.Black;
bordeIz.Border.LeftBorder.BorderStyle = BorderStyleValues.Thin;
SLStyle bordeDe = sl.CreateStyle();
bordeDe.Border.RightBorder.Color = System.Drawing.Color.Black;
bordeDe.Border.RightBorder.BorderStyle = BorderStyleValues.Thin;
SLStyle bordeAr = sl.CreateStyle();
bordeAr.Border.TopBorder.Color = System.Drawing.Color.Black;
bordeAr.Border.TopBorder.BorderStyle = BorderStyleValues.Thin;
SLStyle bordeAb = sl.CreateStyle();
bordeAb.Border.BottomBorder.Color = System.Drawing.Color.Black;
bordeAb.Border.BottomBorder.BorderStyle = BorderStyleValues.Thin;
SLStyle saltoLinea = sl.CreateStyle();
saltoLinea.SetVerticalAlignment(VerticalAlignmentValues.Center);
saltoLinea.SetWrapText(true);
SLStyle rojo = sl.CreateStyle();
rojo.Fill.SetPatternType(PatternValues.Solid);//.BottomBorder.Color = System.Drawing.Color.Red;
rojo.Font.FontColor = System.Drawing.Color.White;
rojo.Fill.SetPatternForegroundColor(System.Drawing.Color.Red);
SLStyle centrado = sl.CreateStyle();
centrado.FormatCode = "$ * #,##0.00";
centrado.Font.FontSize = 10;
centrado.SetHorizontalAlignment(HorizontalAlignmentValues.Right);
SLStyle negrita = sl.CreateStyle();
negrita.Font.Bold = true;
int row = 3;
sl.SetColumnWidth(1, 16);
sl.SetColumnWidth(2, 10);
sl.SetColumnWidth(3, 65);
sl.SetColumnWidth(4, 15);
sl.SetColumnWidth(5, 15);
sl.SetColumnWidth(6, 15);
sl.SetCellValue(1, 1, "Local");
sl.SetCellValue(1, 2, "Cantidad");
sl.SetCellValue(1, 3, "Producto");
sl.SetCellValue(1, 4, "Costo");
sl.SetCellValue(1, 5, "Precio");
sl.SetCellValue(1, 6, "Diferencia");
sl.SetCellStyle(1, 1, 1, 5, bordeNegrita);
var locales = ctx.Compras.Where(a => a.tandaId == idTanda && a.estadoId != entre.idEstadoCompra).Select(a => a.Locales).Distinct().OrderBy(a => new { a.comuna, a.nombre }).ToList();
foreach (var local in locales)
{
var listado = ctx.CompraProducto.Where(a => a.Compras.localId == local.idLocal && a.Compras.tandaId == idTanda).GroupBy(a => a.productoId).Select(a => new { idProducto = a.Key, Cantidad = a.Sum(b => b.cantidad), CompraProducto = a.FirstOrDefault() }).ToArray();
int totalVecinx = (row + listado.Count() - 1);
sl.SetCellStyle(row, 1, totalVecinx, 1, bordeIz);
sl.SetCellStyle(row, 1, row, 6, bordeAr);
sl.SetCellStyle(totalVecinx, 1, totalVecinx, 6, bordeAb);
for (int x = 1; x < 7; x++)
{
sl.SetCellStyle(row, x, totalVecinx, x, bordeDe);
}
sl.SetCellValue(row, 1, local.direccion + (local.nombre != null ? "\n" + local.nombre : ""));
sl.SetCellStyle(row, 1, saltoLinea);
sl.MergeWorksheetCells(row, 1, totalVecinx, 1);
decimal costoLocal = 0;
decimal precioLocal = 0;
for (int x = 0; x < listado.Count(); x++)
{
var compra = listado[x];
Productos prod = ctx.Productos.FirstOrDefault(a => a.idProducto == compra.idProducto);
Costos ultimoc = compra.CompraProducto.Costos;
decimal costo = ultimoc.costo * compra.Cantidad;
//decimal costo = prod.Costos.FirstOrDefault(a => a.fecha <= actual.fechaAbierto).costo * compra.Cantidad;
//Precios ultimop = prod.Precios.Count > 1 ? prod.Precios.LastOrDefault(a => a.fecha.Date <= actual.fechaAbierto.Date) : prod.Precios.FirstOrDefault();
decimal precio = compra.CompraProducto.Precios.precio * compra.Cantidad;
//decimal precio = prod.Precios.FirstOrDefault(a => a.fecha <= actual.fechaAbierto).precio * compra.Cantidad;
costoLocal += costo;
precioLocal += precio;
//string filas = string.Format(";{0};{1};${2};${3};${4}", compra.Cantidad, string.Format("{0} - {1} - {2}", prod.producto, prod.marca, prod.presentacion), costo.ToString("0.00", System.Globalization.CultureInfo.InvariantCulture), precio.ToString("0.00", System.Globalization.CultureInfo.InvariantCulture), (precio - costo).ToString("0.00", System.Globalization.CultureInfo.InvariantCulture));
sl.SetCellValue(row, 2, compra.Cantidad);
sl.SetCellValue(row, 3, prod.producto + " - " + prod.marca + " - " + prod.presentacion);
sl.SetCellValue(row, 4, costo);
sl.SetCellValue(row, 5, precio);
sl.SetCellValue(row, 6, precio - costo);
sl.SetCellStyle(row, 4, centrado);
sl.SetCellStyle(row, 5, centrado);
sl.SetCellStyle(row, 6, centrado);
row++;
}
sl.SetCellValue(row, 3, "Totales por local: ");
sl.SetCellValue(row, 4, costoLocal);
sl.SetCellValue(row, 5, precioLocal);
sl.SetCellValue(row, 6, precioLocal - costoLocal);
sl.SetCellStyle(row, 3, negrita);
sl.SetCellStyle(row, 4, negrita);
sl.SetCellStyle(row, 5, negrita);
sl.SetCellStyle(row, 6, negrita);
sl.SetCellStyle(row, 4, centrado);
sl.SetCellStyle(row, 5, centrado);
sl.SetCellStyle(row, 6, centrado);
row++;
row++;
}
sl.SaveAs(mem);
mem.Position = 0;
TempData[handle] = mem.ToArray();
return(Json(new { FileGuid = handle, FileName = "Finanzas.xlsx" }));
}
}