Problem in Exporting to Excel in browser

• • Reply

  12 years ago

  by deepakvirdi

  Deepak Virdi

  Pune, India

  Joined 12 years ago
  Hi, I have a code that is working fine as i required but only problem is that it is saving the excel sheet to disk. I want that it should be render in browser and pop should be there to ask user for "save, open or cancel option". I have more one datatable in one dataset which i have to show in same excel. and in particular format. formating and everything is working fine. But i want it in browser with pop asking to user. Please give soltion ASAP. My Code : using System; using System.Collections; using System.Configuration; using System.Data; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.HtmlControls; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using Microsoft.Office.Interop.Excel; using System.Reflection; using System.Runtime.InteropServices; using System.IO; using System.Data.SqlClient; public partial class ExportToExcel : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { } string strName; string strLocation; private void createDataInExcel(DataSet ds) { Application objApplication; _Workbook objWorkbook; _Worksheet objSheet; Range objRange; string strCurrentDir = Server.MapPath("."); try { objApplication = new Application(); objApplication.Visible = false; //Get a new workbook. objWorkbook = (_Workbook)(objApplication.Workbooks.Add(Missing.Value)); objSheet = (_Worksheet)objWorkbook.ActiveSheet; //table 1 if (ds.Tables[0].Rows.Count > 0) { int iFirstRow = 1; int iTableRows=1; int iTableCols=1; int iTableColsTotal = (ds.Tables[0].Columns.Count)/2; for (int noOfTables = 0; noOfTables < ds.Tables.Count; noOfTables++) { //int iRow = (ds.Tables[noOfTables].Rows.Count + iFirstRow + 1); if (noOfTables == 0) { for (int j = 0; j < ds.Tables[noOfTables].Columns.Count; j++) { for (int z = 0; z < ds.Tables[noOfTables].Columns.Count; z++) { objSheet.Cells[iTableCols, iTableRows] = ds.Tables[noOfTables].Columns[z].ColumnName; objSheet.Cells[iTableCols, iTableRows + 1] = ds.Tables[noOfTables].Rows[0][z].ToString(); iTableCols++; if (iTableCols == (iTableColsTotal + 1)) { iTableCols = 1; iTableRows = 5; } } } // For each row, print the values of each column. } else { for (int j = 0; j < ds.Tables[noOfTables].Columns.Count; j++) { objSheet.Cells[iFirstRow, j + 1] = ds.Tables[noOfTables].Columns[j].ColumnName; } // For each row, print the values of each column. for (int rowNo = 0; rowNo < ds.Tables[noOfTables].Rows.Count; rowNo++) { for (int colNo = 0; colNo < ds.Tables[noOfTables].Columns.Count; colNo++) { objSheet.Cells[iFirstRow + 1, colNo + 1] = ds.Tables[noOfTables].Rows[rowNo][colNo].ToString(); } iFirstRow++; } } iFirstRow = (iFirstRow + iTableColsTotal + 2); } } objRange = objSheet.get_Range("A1", "IV1"); objRange.EntireColumn.AutoFit(); objApplication.Visible = false; objApplication.UserControl = false; string strFile = "Reports" + DateTime.Now.Ticks.ToString() + ".xls"; objWorkbook.Save(); strLocation = strCurrentDir + strFile; objWorkbook.SaveAs(strCurrentDir + strFile, XlFileFormat.xlWorkbookNormal, null, null, false, false, XlSaveAsAccessMode.xlShared, false, false, null, null,null); //Need all following code to clean up and remove all references!!! objWorkbook.Close(null, null, null); objApplication.Workbooks.Close(); objApplication.Quit(); Marshal.ReleaseComObject(objRange); Marshal.ReleaseComObject(objApplication); Marshal.ReleaseComObject(objSheet); Marshal.ReleaseComObject(objWorkbook); objSheet = null; objWorkbook = null; objApplication = null; lblMsg.Text = "Reports Generated Successfully"; } catch (Exception theException) { Response.Write(theException.Message); } } public System.Data.DataSet GetDetails(string strGroupName) { string strSPName = "SDIsp_HLIC_Get_EmployeeInforceList"; SqlConnection objSqlConnection = new SqlConnection("Connectionstring"); SqlCommand objSqlCommand = new SqlCommand(strSPName, objSqlConnection); SqlDataAdapter objAdapter = new SqlDataAdapter(); DataSet dsDetails = new DataSet(); SqlParameter p1 = new SqlParameter("@P_GROUPNAME", SqlDbType.VarChar, 30); p1.Value = strGroupName; objSqlCommand.Parameters.Add(p1); objSqlCommand.CommandType = CommandType.StoredProcedure; objAdapter.SelectCommand = objSqlCommand; objSqlConnection.Open(); objAdapter.Fill(dsDetails); objSqlConnection.Close(); System.Data.DataTable dtDetails = dsDetails.Tables[0]; return dsDetails; } protected void btnGenerate_Click(object sender, EventArgs e) { strName = txtGroupName.Text; DataSet ds = GetDetails(strName); //Getting data from database. (Three Datatables in One Dataset) createDataInExcel(ds); //btnGenerate.Enabled = false; } }
  Report abuse