Excel download uploaded to the IIS ASP server

Question

Excel download uploaded to the IIS ASP server

Navigation

#1 by (0 votes)
#2 by (0 votes)
#3 by (0 votes)

1

Good I have a problem I was doing a module to download excel and if it works correctly for which I am now uploading this module in a IIS and if you download but when it opens it appears that the file is damaged and is being downloaded in the folder of downloads since I would have put it on the disk C:// and if it generates correctly on my local server localhost

This is my code:

string query = "SP_ReporteExcel";
using (SqlConnection con = new SqlConnection(ObtenerCadenaConexion()))
{
    using (SqlCommand cmd = new SqlCommand(query))
    {
        cmd.CommandTimeout = 90000000;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add("opt", SqlDbType.Int).Value = 1;
        cmd.Parameters.Add("fechainicio", SqlDbType.DateTime).Value = txtFinicio.Text;
        cmd.Parameters.Add("fechafin", SqlDbType.DateTime).Value = txtFFin.Text;
        cmd.Parameters.Add("rubro", SqlDbType.VarChar, 250).Value = dprAgencia.SelectedItem.Value;
        using (SqlDataAdapter sda = new SqlDataAdapter())
        {
            cmd.Connection = con;
            sda.SelectCommand = cmd;

            using (DataSet ds = new DataSet())
            {
                sda.Fill(ds);

                //Set Name of DataTables.

                ds.Tables[0].TableName = "Recibos-Energia Electrica";
                ds.Tables[1].TableName = "Recibos-Agua Potable";

                using (XLWorkbook wb = new XLWorkbook())
                {      
                    foreach (DataTable dt in ds.Tables)
                    {

                        DateTime d3 = DateTime.Parse(txtFinicio.Text);
                        string Mes = d3.ToString("MMMM");

                        DateTime d4 = DateTime.Parse(txtFinicio.Text);
                        string Anio = d4.ToString("yyyy");

                        wb.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
                        wb.Style.Font.Bold = true;
                        wb.Style.Font.FontColor = XLColor.Black;
                        //wb.Style.Font.FontName = "calibri";
                        //wb.Style.Font.FontSize = 13;
                        wb.Style.Border.DiagonalBorderColor = XLColor.Red;
                        wb.Style.Border.RightBorderColor = XLColor.Red;
                        wb.Style.Border.LeftBorderColor = XLColor.Red;
                        wb.Style.Border.BottomBorderColor = XLColor.Red;

                        var ws = wb.Worksheets.Add(dt);

                        ws.Row(1).InsertRowsAbove(1);
                        ws.Cells("A2:H2").Style.Fill.BackgroundColor = XLColor.Black;
                        ws.Cells("A2:H2").Style.Font.FontColor = XLColor.Yellow;

                        ws.Cells("D1:H1").Style.Fill.BackgroundColor = XLColor.Black;
                        ws.Cells("D1:H1").Style.Font.FontColor = XLColor.Yellow;
                        string datetimeStringFileName = string.Format("{0:yyyy-MM-dd}", DateTime.Now);
                        ws.Range("D1:H1").Merge();

                        ws.Range("D1").Value = "Mes de " + Mes + " del " + Anio;
                        //ws.Cells("D1:H1").Value = "Mes";

                        //wb.Style.Fill.BackgroundColor = XLColor.Black;
                        //wb.Worksheets.Add(dt);

                    }

                    Response.Clear();
                    Response.Buffer = true;
                    Response.Charset = "";

                    Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                    Response.AddHeader("content-disposition", "attachment;filename=\"Reporte_Mensuales.xlsx\"");

                    using (MemoryStream MyMemoryStream = new MemoryStream())
                    {
                        var aux = Guid.NewGuid();
                        wb.SaveAs("C:\DatosSiga\Logistica\Reporte_Mensualess" + aux + ".xlsx");

                        MyMemoryStream.WriteTo(Response.OutputStream);
                        Response.Flush();
                        Response.End();
                        Response.Redirect("FrmLogeo.aspx");
                    }
                }
            }
        }
    }
}

c# asp.net iis

asked by PieroDev 25.03.2017 в 16:32

source

3 answers

0

Try placing these values in the webconfig to be taken by IIS

<configuration>
 <system.webServer>
    <staticContent>
        <remove fileExtension=".xlsx" />
        <mimeMap fileExtension=".xlsx" mimeType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" />
    </staticContent>
  </system.webServer>
</configuration>

Reference: link

answered by 25.03.2017 в 19:20

0

According to the documentation :

WriteTo Write all the contents of the memory stream to another memory stream.

You are using WriteTo() in a Stream that is empty. You only initialize MyMemoryStream but you write nothing.

 using (MemoryStream MyMemoryStream = new MemoryStream())
 {
       MyMemoryStream.WriteTo(Response.OutputStream);
       //...
 }

So when you write to the response MyMemoryStream.WriteTo(Response.OutputStream); you do not actually write anything and therefore the file is downloaded corrupted.

You must save the WorkBook as a file and then read the file and pass it to MyMemoryStream .

Example:

// aqui guardas el archivo...

//..

// Ahora lo lees
var excelBytes = File.ReadAllBytes("RutaDondeLoGuardaste");

using (MemoryStream MyMemoryStream = new MemoryStream(excelBytes))
 {
       MyMemoryStream.WriteTo(Response.OutputStream);
       //...
 }

Already that part of keeping I leave it to you because I do not know how it is done. But you already have the idea of why the downloaded file does not work.

answered by 11.07.2017 в 14:33

Create in the same view (with iframe) a PDF in Codeigniter 3 with FPDF Syntax error in Postgresql in java

score 0 · Accepted Answer

Let's understand a bit what your code does, when you do this wb.SaveAs("C:\DatosSiga... and you run it locally it probably works well because the code takes the data from where the application currently exists. Once you upload it to a server, it will now take server routes, this leads us to check read and write permissions to other folders by the IIS user, causing a malfunction because the user IIS is not an admin user.

Another point that I see is that it is not necessary to save the file directly in a physical route, let's do it by means of a Stream and then download that buffer of data:

Stream fs = new MemoryStream();
wb.SaveAs(fs);
fs.Position = 0;

MemoryStream stream = GetStream(ExcelWorkbook);

Response.Clear();
Response.Buffer = true;
Response.AddHeader("content-disposition", "attachment; filename=" + "Reporte_Mensualess" + aux + ".xlsx");
Response.ContentType = "application/vnd.ms-excel";
Response.BinaryWrite(stream.ToArray());
Response.End();
Response.Redirect("FrmLogeo.aspx");