Create EXCEL and open it

Question

I'm creating an excel file, and I must open it immediately. Obviously, when I create it on localhost, I can get the file, but when I publish my web application on IIS this not works because I suppose It's creating on server side...

So I create the excel file and try to upload to the server and after that download it, but I can't create it, If I run this on localhost works, but when I publish don't work.

Any advice?

this is my code:

I use this libraries for Excel:

using Microsoft.Office.Core;
using Excelo = Microsoft.Office.Interop.Excel; 
using Microsoft.Office.Interop;
using System.Reflection;
using System.Globalization;

Then, I start my excel file:

private void sacConsulPend_GenRemito()
{   // DB 
    string conn = ConfigurationManager.ConnectionStrings["conn"].ConnectionString;
    DataSet dsRemito = new DataSet();
    DataTable dtCons = new DataTable();



    using (SqlConnection sqlCon = new SqlConnection(conn))
    {
        using (SqlCommand cmd = new SqlCommand())
        {
            try
            {   //SP for data query
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                cmd.CommandText = "SACBUS_CONSUL_SOLICITUDEXIST";
                cmd.Parameters.AddWithValue("@ID_CONSULTA", 
                    Convert.ToInt32(lblSac_ConPenDet_idCons.Text.Replace(" ", "")));

                cmd.Connection = sqlCon;
                sqlCon.Open();

                SqlDataAdapter dapC = new SqlDataAdapter(cmd);
                dapC.Fill(dsRemito);


            }
            catch (Exception sqlex)
            {
                throw sqlex;
            }
        }
    }

    Excelo.Application oXL;
    Excelo._Workbook oWB;
    Excelo._Worksheet oSheet;
    Excelo.Range oRng;

    try
    {

        //Start Excel 
        oXL = new Excelo.Application();

        //new blank workbook.
        oWB = (Excelo._Workbook)(oXL.Workbooks.Add(System.Reflection.Missing.Value));
        oSheet = (Excelo._Worksheet)oWB.ActiveSheet;
        object misValue = System.Reflection.Missing.Value;

        //Text Format
        oSheet.get_Range("A1", "Z1000").Font.Name = "Courier New";
        oSheet.get_Range("A1", "Z1000").Font.Size = 7;
        oSheet.get_Range("A1", "Z1000").Font.Bold = true;
        oSheet.get_Range("A1", "Z1000").NumberFormat = "@";

        oSheet.get_Range("D1", "E1").Cells.Merge();
        oSheet.get_Range("A1", "F1").Font.Bold = true;
        oSheet.get_Range("A1", "F1").Font.Italic = true;
        oSheet.get_Range("A1", "F1").VerticalAlignment = Excelo.XlVAlign.xlVAlignCenter;
        oSheet.get_Range("A1", "F1").HorizontalAlignment = Excelo.XlVAlign.xlVAlignCenter;
        oSheet.get_Range("A1", "F1").Font.Size = 9;

        //Logo Lockers
        oSheet.Shapes.AddPicture(Server.MapPath("~/images/LockersLogo.jpg"), MsoTriState.msoFalse, MsoTriState.msoCTrue, 0, 0, 60, 20);


        /* 
        Excel Content
        */


         //Save ExcelFile
        var filePath = Path.Combine(HttpContext.Current.Server.MapPath("~/Files/Envios/"), lblSac_ConPenDet_idCons.Text.Replace(" ", "") + ".xlsx");
        oWB.SaveAs(filePath, Excelo.XlFileFormat.xlOpenXMLWorkbook, Missing.Value,
        Missing.Value, false, false, Excelo.XlSaveAsAccessMode.xlNoChange,
        Excelo.XlSaveConflictResolution.xlUserResolution, true,
        Missing.Value, Missing.Value, Missing.Value);



    }
    catch (Exception thex)
    {
        String errorMessage;
        errorMessage = "Error en la creacion del archivo Excel: ";
        errorMessage = String.Concat(errorMessage, thex.Message);
        errorMessage = String.Concat(errorMessage, " Line: ");
        errorMessage = String.Concat(errorMessage, thex.Source);
    }

}

Answer 1

This is my code now, Using EEPlus It's not finished, but works, creating one file in server side, after that, I download the same created file.

using OfficeOpenXml;
using System.IO;
using System.Drawing;
using OfficeOpenXml.Drawing;

using System.Data;
using System.Data.SqlClient;
using System.Configuration;

This is the most relevant:

public static string GenerarExcel(DirectoryInfo outputDir)
{
    string conn = ConfigurationManager.ConnectionStrings["conn"].ConnectionString;
    DataSet dsRemito = new DataSet();
    DataTable dtCons = new DataTable();


    using (SqlConnection sqlCon = new SqlConnection(conn))
    {
        using (SqlCommand cmd = new SqlCommand())
        {
            try
            {//SP for sql query
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                cmd.CommandText = "SACBUS_CONSUL_SOLICITUDEXIST";
                cmd.Parameters.AddWithValue("@ID_CONSULTA", 66);
                //66 wil be a variable, this is only for my tests

                cmd.Connection = sqlCon;
                sqlCon.Open();

                SqlDataAdapter dapC = new SqlDataAdapter(cmd);
                dapC.Fill(dsRemito);


            }
            catch (Exception sqlex)
            {
                throw sqlex;
            }
        }
    }


    FileInfo newFile = new FileInfo(outputDir.FullName + @"\Sample1.xlsx");

    if (newFile.Exists)
    {
        newFile.Delete();
        newFile = new FileInfo(outputDir.FullName + @"\Sample1.xlsx"); //preparar nombre archivo
    }

    using (ExcelPackage package = new ExcelPackage(newFile))
    {
        // Agregar hoja a worbook
        ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Envios");

        // Header
        worksheet.Cells[1, 3].Value = "SISTEMA DE GESTIÓN DE CALIDAD ISO 9001:2008";
        worksheet.Cells[2, 3].Value = "PEDIDO DE CONSULTA A BODEGA";
        worksheet.Cells[1, 5].Value = "Código:";
        worksheet.Cells[1, 6].Value = "FOR-PCV-COM-SAC-PEBOD-02  ";
        worksheet.Cells[2, 5, 2, 6].Merge = true;

        // Estilo del Encabezado
        using (var range = worksheet.Cells[1, 1, 1, 5]) // De la celda 1,1 a la 1,5
        {
            range.Style.Font.Bold = true; // Negrita
            range.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid; // Solido lleno
            range.Style.Fill.BackgroundColor.SetColor(Color.DarkBlue); //Background
            range.Style.Font.Color.SetColor(Color.White);// Font color
        }


        // Pie de Pagina
        // lineas en la cuadricula
        worksheet.Cells["A5:E5"].Style.Border.Top.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
        worksheet.Cells["A5:E5"].Style.Font.Bold = true; // estilo del pie de pagina



        //Formatos
        //Formato Numerico
        worksheet.Cells["C2:C5"].Style.Numberformat.Format = "#,##0";   // Formato Entero
        worksheet.Cells["D2:E5"].Style.Numberformat.Format = "#,##0.00";    // Formato Decimal


               worksheet.Cells["A2:A4"].Style.Numberformat.Format = "@"; //Formato texto
        worksheet.Cells.AutoFitColumns(0); //AutoAjustar celdas


        // Numero de pagina + total de paginas a la derecha del pie de pagina
        worksheet.HeaderFooter.OddFooter.RightAlignedText = string.Format("Pagina {0} of {1}", ExcelHeaderFooter.PageNumber, ExcelHeaderFooter.NumberOfPages);

        // Nombre de la hoja al centro del pie de pagina
        worksheet.HeaderFooter.OddFooter.CenteredText = ExcelHeaderFooter.SheetName;

        // Ruta a la izquierda del pie de pagina
        worksheet.HeaderFooter.OddFooter.LeftAlignedText = ExcelHeaderFooter.FilePath + ExcelHeaderFooter.FileName;


        //System.Drawing.Image img = System.Drawing.Image.FromFile(@"C:\temporal\asp\ExcelLibrary\LockersLogo.jpg");
        System.Drawing.Image img = System.Drawing.Image.FromFile(HttpContext.Current.Server.MapPath("~/image/LockersLogo.jpg"));

        ExcelPicture pic = worksheet.Drawings.AddPicture("Sample", img);
        pic.SetPosition(0, 0, 6, 0);
        pic.SetSize(150, 75);



        //Items consultados
        var registros = 0;
        for (int i = 0; i < dsRemito.Tables[0].Rows.Count; i++)
        {
            worksheet.Cells[8 + i, 1].Value = dsRemito.Tables[0].Rows[i]["CAJA_CODIGO"];
            worksheet.Cells[8 + i, 2].Value = dsRemito.Tables[0].Rows[i]["CAJA_NUMERO"];
            worksheet.Cells[8 + i, 3].Value = dsRemito.Tables[0].Rows[i]["CAJA_CONTENIDO_NUMERO"];
            worksheet.Cells[8 + i, 4].Value = dsRemito.Tables[0].Rows[i]["ITEM"];



            registros = i;
        }




        package.Save(); //guardar workbook

    }

    return newFile.FullName;
}


protected void DownloadFile()
{

    string rutaUsr = "~/" + "Files/";
    string filePath = rutaUsr + "Sample1" + ".xlsx";
    Response.ContentType = ContentType;
    Response.AppendHeader("Content-Disposition", "attachment; filename=" + Path.GetFileName(filePath));
    Response.WriteFile(filePath);
    Response.End();

}

protected void Page_Load(object sender, EventArgs e)
{
    try
    {
        //DirectoryInfo outputDir = new DirectoryInfo(@"C:\temporal\asp\ExcelLibrary");
        DirectoryInfo outputDir = new DirectoryInfo(HttpContext.Current.Server.MapPath("~/Files/"));
        GenerarExcel(outputDir);

        DownloadFile();
    }

    catch (Exception ex)
    {
        Console.WriteLine("Error: {0}", ex.Message);
    }

}

Answer 2

You could use the (official) OpenXML SDK that is intended for scenarios like yours. It doesn't allow you to actually calculate and run functions in the server environment but its good for creating and manipulating workbooks. Download However, the OpenXML SDK is not everyone's favorite since it requires some learning and at times a lot of code to achieve some simple things.

Luckily there are many other libraries that allow creating XLS/X files without Office like the ExcelLibrary or its successor EEPlus, or NPOI (see johnleniel), and many commercial third-party components.

I could imagine that a dedicated reporting component like Crystal Reports (that also exports to XLS/X) could also be of great use for you.