Я реализую доклад в MVC с хранившейся процедурой, которую это считает доходами 3, ты вносишь один это месяц, год, у типа cambio.y на моей странице есть 3 неудар в лунку, внесенный для того, чтобы он поступил, и когда он нажмет на экспортировать, спустился архив excel, но мне не удается создать названный excel
Главным образом я требую, класть параметры, дав Вам click производить excel.
public ActionResult Reporte(int mes,int ano,decimal tipo)
{
var db = new Covi001Entities();
SqlParameter parametro1 = new SqlParameter("@mes", mes);
SqlParameter parametro2 = new SqlParameter("@ano", ano);
SqlParameter parametro3 = new SqlParameter("@tipo", tipo);
var data = db.Database.SqlQuery("exec ReporteExcel @mes @ano @tipo", parametro1, parametro2, parametro3);
return View(data);
}
Мой store procedure получает 3 параметра, что я послан их в fileresult,
USE [Covi001]
GO
/****** Object: StoredProcedure [dbo].[ReporteExcel] Script Date: 06/11/2019 12:38:58 p.m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[ReporteExcel]
@mes int ,
@ano int,
@tipo decimal
AS
BEGIN
SELECT '31/07/2016' as FECHA, a.dpercierre, a.dfechaform as FORMALIZACION, a.dfechavenc as VENCIMIENTO, A.dfepagreal AS FECHAPAGO, a.cnumoperac as OPERACION, b.cidasociad AS ASOCIADO, A.nmontoapro AS APROBADO,
CASE WHEN C.CCODMONEDA = '002' THEN (A.nsaldocred * @tipo /*(SELECT ntipocamve FROM cotipocambio WHERE (CONVERT(Char, cotipocambio.dfechatipo, 103) = '30/06/2019') AND ccodmoneda = '002')*/) ELSE A.nsaldocred END AS SALDO,
CASE WHEN A.cfrecupago = '04' THEN 'MENSUAL' ELSE 'QUINCENAL' END AS FECUENCIAS,
A.ntasainter AS TASA, A.CCODIGOLIN AS LINEA, CASE WHEN B.cestasocio = 'L' THEN 'LIQUIDADO' ELSE 'ACTIVO' END AS ESTATUS
/*a.npergracia, a.cnumoperac, a.cnumsolici, a.ccodifuent, f.dfesolicre, ISNULL(f.ccodigousu, '') AS ccodigous2, a.nmondespro, a.cformadese, UPPER(a.ccodigousu) AS Expr1, a.ccomentari, a.nmontoapro, a.nmontrefun,
a.ntasainter, a.cidasociad, RTRIM(SUBSTRING(b.cnombreaso, 1, 12)) + ' ' + RTRIM(SUBSTRING(b.cnombreaso, 13, 12)) + ' ' + RTRIM(SUBSTRING(b.cnombreaso, 25, 16)) AS cnombreaso, b.dfechanaci, a.ccodigolin, c.cdetalleli,
a.cstatuscre, d.cdetastatu, a.dfechaform, a.ncuotapres, a.nnumcuotas, e.ccodigoges, e.cdetagesti, a.nsaldocred, a.npagosefec, a.dfeculabon, a.dfepagreal, a.ccodigousu,
(SELECT CDESCRICAT
FROM ASCATEGOAS
WHERE (CCODIGOCAT = a.ccodigocat)) AS ccodigocat, c.cprefijoli, a.ndesembols, a.ctipotrans, a.cnumdocume, g.ctipasient, a.cnumasient, g.cdesasien, RTRIM(ISNULL(h.cnombcorto, '')) AS cnombcorto, f.nprincsoli,
i.cdetafuent, a.ccodprovee, UPPER('') AS ccodigrupo, '' AS cdescgrupo, '''' AS cdetagrupo, dbo.Fun_CalcCargCuota(a.ccodigolin, a.nmontoapro, a.nsaldocred, a.ncuotapres, a.cidasociad, a.cnumoperac) AS ncargoperi,
comoneda.ccodmoneda, comoneda.cdescmoned, b.ccodsubdep, ninteredia, a.dfeproxabo, a.dfechavenc, b.ccedulasoc, z.cnombtipop, a.ccodactivi, crcodactec.cdetactivi, a.cnumfactur, a.nporcendeu,
comoneda.ccodmoneda AS Expr2, comoneda.cdescmoned AS Expr3, l.cnombdivis, b.dfechainga*/
FROM [crhprestam] AS a LEFT OUTER JOIN
crcodlinea AS c ON a.ccodigolin = c.ccodigolin LEFT OUTER JOIN
comoneda ON c.ccodmoneda = comoneda.ccodmoneda LEFT OUTER JOIN
geadminusu AS j ON a.ccodigousu = j.ccodigousu LEFT OUTER JOIN
gesucursal ON j.ccodsucurs = gesucursal.ccodsucurs LEFT OUTER JOIN
crcodorgan ON a.ccodigorga = crcodorgan.ccodigorga LEFT OUTER JOIN
cpprovedor ON a.ccodprovee = cpprovedor.ccodprovee LEFT OUTER JOIN
[ashmaestra] AS b ON a.cidasociad = b.cidasociad LEFT OUTER JOIN
crsolicred AS f ON a.cnumsolici = f.cnumsolici LEFT OUTER JOIN
astipoplan AS z ON b.CTIPOPLANI = z.ctipoplani LEFT OUTER JOIN
crstatcred AS d ON a.cstatuscre = d.cstatuscre LEFT OUTER JOIN
crcodgesti AS e ON a.ccodigoges = e.ccodigoges LEFT OUTER JOIN
cotipasien AS g ON a.ctipasient = g.ctipasient LEFT OUTER JOIN
asdepartam AS k ON b.cinstdepto = k.cinstdepto LEFT OUTER JOIN
asdivision AS l ON b.cidivision = l.cidivision LEFT OUTER JOIN
asgrupinte AS m ON b.ccodgrupos = m.ccodgrupos LEFT OUTER JOIN
bactasbanc ON a.cctabancar = bactasbanc.cctabancar LEFT OUTER JOIN
bainstbanc AS h ON bactasbanc.ccodibanca = h.ccodibanca LEFT OUTER JOIN
crfuenrecu AS i ON a.ccodifuent = i.ccodifuent LEFT OUTER JOIN
crcodactec ON a.ccodactivi = crcodactec.ccodactivi LEFT OUTER JOIN
cotipocambio AS tc ON DATEPART(year, tc.dfechatipo) = DATEPART(year, a.dfechaform) AND DATEPART(month, tc.dfechatipo) = DATEPART(month, a.dfechaform) AND DATEPART(day, tc.dfechatipo) = DATEPART(day, a.dfechaform)
AND tc.ccodmoneda = comoneda.ccodmoneda
WHERE (month( a.dpercierre)) = @mes and (year( a.dpercierre))=@ano /*DIGITAR FECHA CIERRE CREDITOS*/ AND (YEAR(a.dpercierre) = YEAR(b.dfeccierre)) AND (MONTH(a.dpercierre) = MONTH(b.dfeccierre)) AND (DAY(a.dpercierre) = DAY(b.dfeccierre)) AND
(nsaldocred BETWEEN 1.00 AND 999999999.00) AND (c.comitecart = 'N')
END
Это результат изменчивой ДАТЫ
Драйвер должен быть Филересульт.
public FileResult Reporte(int mes, int ano, decimal tipo) {
var db = new Covi001Entities();
SqlParameter parametro1 = new SqlParameter("@mes", mes);
SqlParameter parametro2 = new SqlParameter("@ano", ano);
SqlParameter parametro3 = new SqlParameter("@tipo", tipo);
var data = db.Database.SqlQuery<ReporteExcelaudi_Result>("exec ReporteExcel @mes @ano @tipo", parametro1, parametro2, parametro3);
//asumiré que la variable: "data" tiene un arreglo de bytes.
string fileName = "MiSuperReporteEnExcel.xslx";
return File(data, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", fileName);
}
DocumentaciГіn чиновник: https://docs.microsoft.com/es-es/dotnet/api/system.web.mvc.fileresult? view=aspnet-mvc-5.2