generate an excel file from template in asp.net

Question

I have a desktop app in which I generate an excel file from a template, basically what I do is to make a copy of the template excel file to a new directory, open it, and add records to it.

this is a code snippet:

string ExcelFiles = txtFileOutput.Text; //Application.StartupPath + @"\ExcelFiles\"; 
        string DestFile = ExcelFiles + FinalListOfTrucks[0].Date.ToShortDateString().Replace('/', '_') + ".xlsx"; 
        if (File.Exists(DestFile))
        {
            try
            {
                File.Delete(DestFile);
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error deleting file: " + ex.Message);
            }
        }

        try
        {
            File.Copy(Application.StartupPath + @"\ExcelTemplate.xlsx", DestFile);
        }
        catch (Exception ex)
        {
            MessageBox.Show("Error copying file: " + ex.Message);
        }

        string appPath = Path.GetDirectoryName(Application.ExecutablePath);            
        string strConnectionXls = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + DestFile + ";Extended Properties=Excel 12.0;";
        OleDbConnection cno = new OleDbConnection(strConnectionXls);
        OleDbCommand cmo = new OleDbCommand();
        cmo.Connection = cno;
        cmo.CommandType = CommandType.Text;

string QueryHeader = "INSERT INTO [RptTotal$] (NUMERO,FECHA,JORNADA,PLACA,HORA_DE_LLEGADA,HORA_FIN_CARGUE,HORA_FIN_DESCARGUE,VOLUMEN,DISTANCIA,TIPO_DE_VEHICULO,MATERIAL,MISSED_DATE)";

cmo.CommandText = QueryHeader + " VALUES ('" + No.ToString() + "','" + Fecha + "','" + Jornada + "','" + Placa + "','" + HoraDeLlegada + "','" + HoraFinCargue + "','" +
                                                        HoraFinDescargue + "','" + Volumen + "','" + Distancia + "','" + TipoDeVehiculo + "','" + Material + "','" + MissedDate + "')";
                cmo.ExecuteNonQuery();

Now, let's say that I can Add a folder to my webapp, and place the excel template there, how to start from there to achieve the same functionality in web :\ ?

Emmanuel N · Accepted Answer · 2012-11-09 15:58:52Z

1

Same thing you are doing on your desktop application, just need to clean up your code.

Copy template, save templete same where in your server
Need to remove all message boxes
Use OleDbConnection to modify your excel file( would strongly recommed OpenXML or libraries build on top of openXML like Epplus)
Save changes
Stream the file to client
Figure out how to clean streamed files

answered Nov 9, 2012 at 15:58

Emmanuel N

7,4592 gold badges30 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Somebody Over a year ago

But I want to keep the template file empty, that's why I first make a copy of it to another location, I guess I'll need to do the same thing here right? Something like upload the template file to the server, change it and then stream it to the client. Yeap, that's what you said in the first point :|

Emmanuel N Over a year ago

You will need to do the samething, but you need to figure out naming scheme(so that different requests will have different files) and how to delete files after you sent them to the user.

Collectives™ on Stack Overflow

generate an excel file from template in asp.net

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related