ASP.NET - How to create excel file with forumlas without using excel interop

Asked By balaji mogadali on 01-May-12 05:44 AM
hi 
How to create excel file with forumlas without using excel interop

please help

S K replied to balaji mogadali on 01-May-12 05:50 AM
When you want to use excel formula than you must need to use excel library dll so you should use Office interop for that type of excel creation in code.
kalpana aparnathi replied to balaji mogadali on 01-May-12 06:58 AM
hi,

Try below code:

private void CreateExcel(DataTable dt)
    {
        try
        {

        FilePath =  "\\\\192.168.1.252\\GNC Reports\\TallyExport.xls";
            Excel.Application oXL = new Excel.Application();

            //Get a new workbook.
            Excel._Workbook oWB = (Excel._Workbook)(oXL.Workbooks.Add(Type.Missing));

            // *************** Sheet 1
            Excel._Worksheet oSheet = (Excel._Worksheet)oWB.Sheets["Sheet1"];
            oSheet.Name = "Journal";
            WriteWxcel(oSheet, dt, 3);

            //***************** Sheet 2
            oSheet = (Excel._Worksheet)oWB.Sheets["Sheet2"];
            oSheet.Name = "Payroll";
            WriteWxcel(oSheet, dt, 4);

            // ************* Sheet 3
            oSheet = (Excel._Worksheet)oWB.Sheets["Sheet3"];
            oSheet.Name = "Receipt";
            WriteWxcel(oSheet, dt, 2);

            //Save Excel File
            oWB.SaveAs(FilePath, Excel.XlFileFormat.xlWorkbookNormal, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            oXL.Quit();

        }
        catch (Exception ex)
        {
            BussinessLayer.CMSException.Instance.HandleMe(this, ex);
        }

    }

Regards,
dipa ahuja replied to balaji mogadali on 01-May-12 07:12 AM
protected void btnExport_Click(object sender, EventArgs e)
{
  DataTable dt = new DataTable();
  dt.Columns.Add();
  dt.Columns.Add();
 
  dt.Rows.Add(TextBox1.Text, TextBox2.Text);
  ExportDataTable(dt);
 
}
public void ExportDataTable(DataTable dt)
{
  HttpContext.Current.Response.Clear();
  HttpContext.Current.Response.AddHeader("content-disposition", "attachment;filename=e1.xls");
  HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
  string sTab = "";
  foreach (DataColumn dc in dt.Columns)
  {
    HttpContext.Current.Response.Write(sTab + dc.ColumnName);
    sTab = "\t";
  }
  HttpContext.Current.Response.Write("\n");
  int i;
  foreach (DataRow dr in dt.Rows)
  {
    sTab = "";
    for (i = 0; i < dt.Columns.Count; i++)
    {
      HttpContext.Current.Response.Write(sTab + dr[i].ToString());
      sTab = "\t";
    }
    HttpContext.Current.Response.Write("\n");
  }
  HttpContext.Current.Response.End();
}
 
 
Venkat K replied to balaji mogadali on 01-May-12 07:22 AM

There are couple of options if you doesn't want to install Excel on your machines and not use the excel interop.

  • NPOI [http://npoi.codeplex.com/]  - Which is free and open source.
  • Spreadsheet ML [http://msdn.microsoft.com/en-us/library/aa140066%28office.10%29.aspx] - Basically XML for creating spreadsheets.

Using the Interop will require that the Excel be installed on the machine from which it is running. In a server side solution, this will be awful. Instead, you should use a tool like the ones above that lets you build an Excel file without Excel being installed.