Click here to Skip to main content
15,947,472 members
Please Sign up or sign in to vote.
1.00/5 (3 votes)
See more:
Hi,
I am using the following code to create an excel object and render it to client however I am facing lot problems, the file saved to excel has garbage values in it. Sometimes it has access errors. All I want is to render this excel object to the client as an excel file. please refer and kindly help.

C#
public void ExportToExcel(string ischarge, string selectedDate, int pageNumber, int pageSize, string rowArea, string modalityFilter, string locationFilter, string regionFilter, string practiceFilter,string procName)
   {
       SqlConnection connection = new SqlConnection(sqlConnectionString);
       string storedProcedureName = procName;
       string orderBy = rowArea.Replace(" ", "");
       int parameterCount = 10;
       int i = 0;
       string[] rowAreaFields = rowArea.Split(',').Select(field => field.Trim()).ToArray();
       SqlParameter[] parameters = new SqlParameter[parameterCount];
       parameters[i] = new SqlParameter("@Date", SqlDbType.Date);
       parameters[i].Value = selectedDate;
       i++;
       parameters[i] = new SqlParameter("@RoleID", SqlDbType.NVarChar);
       parameters[i].Value = SessionVariables.RoleID;
       i++;
       parameters[i] = new SqlParameter("@GroupBy", SqlDbType.NVarChar);
       parameters[i].Value = rowArea.Replace(" ", "");
       i++;
       parameters[i] = new SqlParameter("@Where", SqlDbType.NVarChar);
       string where = string.Empty;
       where = locationFilter == string.Empty ? where : where + "Location in ('" + locationFilter.Replace("'", "''") + "') and ";
       where = regionFilter == string.Empty ? where : where + "Region in ('" + regionFilter.Replace("'", "''") + "') and ";
       where = modalityFilter == string.Empty ? where : where + "Modality in ('" + modalityFilter.Replace("'", "''") + "') and ";
       where = practiceFilter == string.Empty ? where : where + "Practice in ('" + practiceFilter.Replace("'", "''") + "') and ";
       where = where == string.Empty ? string.Empty : where.Remove(where.LastIndexOf("and"), "and".Length).Replace(",", "','");
       parameters[i].Value = where;
       i++;
       parameters[i] = new SqlParameter("@isCharge", SqlDbType.Int);
       parameters[i].Value = Convert.ToInt32(ischarge);
       i++;
       parameters[i] = new SqlParameter("@OrderByColumn", SqlDbType.NVarChar);
       parameters[i].Value = orderBy;
       i++;
       parameters[i] = new SqlParameter("@OrderBy", SqlDbType.NVarChar);
       parameters[i].Value = "asc";
       i++;
       parameters[i] = new SqlParameter("@PageSize", SqlDbType.Int);
       parameters[i].Value = pageSize;
       i++;
       parameters[i] = new SqlParameter("@CurrentPageIndex", SqlDbType.Int);
       parameters[i].Value = pageNumber;
       i++;
       parameters[i] = new SqlParameter("@TotalCount", SqlDbType.Int);
       parameters[i].Value = -1;

       DataSet ds = SqlHelper.ExecuteDataset(connectionString: sqlConnectionString, commandType: CommandType.StoredProcedure,
                        commandText: storedProcedureName, commandParameters: parameters);
       System.Data.DataTable dt = ds.Tables[0];
       string username = ConfigurationManager.AppSettings["UserName"].ToString();
       string password = ConfigurationManager.AppSettings["Password"].ToString();
       string domain = ConfigurationManager.AppSettings["Domain"].ToString();
       //DumpExcel(ds.Tables[0],"Daily Procedures_"+DateTime.Now.ToString().Replace(':','_')+".xls");

       if (RadnetBI.Impersonate.ImpersonateValidUser(username, domain, password))
       {
           Microsoft.Office.Interop.Excel.Application oXL = new Microsoft.Office.Interop.Excel.ApplicationClass();
           Microsoft.Office.Interop.Excel.Workbooks oWBs = oXL.Workbooks;
           Microsoft.Office.Interop.Excel.Workbook oWB = null;
           Microsoft.Office.Interop.Excel.Worksheet oSheet;
           Microsoft.Office.Interop.Excel.Range oRange;
           Microsoft.Office.Interop.Excel.Range oRng_Rowhdr;
           oWB = oWBs.Count > 0 ? oWB = oWBs[0] : oWBs.Add(System.Reflection.Missing.Value);

           /* Set some properties oXL.Visible = true;*/
           oXL.DisplayAlerts = false;
           // Get a new workbook.
           oWB = oXL.Workbooks.Add(System.Reflection.Missing.Value);
           // Get the active sheet
           oSheet = (Microsoft.Office.Interop.Excel.Worksheet)oWB.Worksheets.get_Item(1);
           //oSheet.Name = Dts.Variables["User::SheetName"].Value.ToString();
           oSheet.Name = "DPA_" + DateTime.Now.ToString().Replace(':', '_');
           int RowAreaCount = rowArea.Split(',').Length;
           int rowCount = 1;
           foreach (DataRow dr in dt.Rows)
           {
               rowCount += 1;
               for (int ij = 1; ij < dt.Columns.Count + 1; ij++)
               {
                   // Add the Column header first time only
                   if (rowCount == 2)
                   {
                       oSheet.Cells[1, ij] = dt.Columns[ij - 1].ColumnName;
                   }
                   //Add value to excel cell
                   oSheet.Cells[rowCount, ij] = dr[ij - 1].ToString();

                   if (dt.Columns[ij - 1].ColumnName.Contains("Avg") || dt.Columns[ij - 1].ColumnName.Contains("Var") || dt.Columns[ij - 1].ColumnName.Contains("Budget"))
                   {
                       oRange = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[rowCount, ij];
                       oRange.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.ColorTranslator.FromHtml("#F0DCDD"));
                   }

                   //Coloring to Subtotal
                   if (dr[ij - 1].ToString().Trim() == "Total")
                   {
                       oRange = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[rowCount, ij];
                       oRange.EntireRow.Cells.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.ColorTranslator.FromHtml("#F0DCDD"));
                   }
               }
           }

           //Color and Border to Row Header
           for (int j = 2; j <= dt.Rows.Count + 1; j++)
           {
               for (int k = 1; k <= RowAreaCount; k++)
               {
                   oRng_Rowhdr = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[j, k];
                   oRng_Rowhdr.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.ColorTranslator.FromHtml("#723B3E"));
                   oRng_Rowhdr.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.ColorTranslator.FromHtml("#FFFFFF"));
               }
           }

           //Color and Border to Row Header
           for (int j = 2; j <= dt.Rows.Count + 1; j++)
           {
               for (int k = 1; k <= RowAreaCount; k++)
               {
                   oRng_Rowhdr = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[j, k];
                   oRng_Rowhdr.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.ColorTranslator.FromHtml("#723B3E"));
                   oRng_Rowhdr.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.ColorTranslator.FromHtml("#FFFFFF"));
               }
           }

           //Autofit Excel column
           for (int ik = 1; ik < dt.Columns.Count + 1; ik++)
           {
               oRange = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[1, ik];

               oRange.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.ColorTranslator.FromHtml("#5A0B0E"));
               oRange.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.ColorTranslator.FromHtml("#FFFFFF"));
               oRange.Font.Bold = true;
               oRange.EntireColumn.AutoFit();
           }

           //Freeze Row and Column Headers
           oSheet.Application.ActiveWindow.SplitColumn = RowAreaCount;
           oSheet.Application.ActiveWindow.FreezePanes = true;
           oSheet.Application.ActiveWindow.SplitRow = 1;
           oSheet.Application.ActiveWindow.FreezePanes = true;

           if (File.Exists(oSheet.Name + ".xls"))
           {
               File.Delete(oSheet.Name + ".xls");
           }
            FileInfo file = new FileInfo(oXL.ActiveWorkbook.FullName);
            oWB.SaveAs(oSheet.Name + ".xls", Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookDefault,
                      Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                      Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive,
                      Missing.Value, Missing.Value, Missing.Value,
                      Missing.Value, Missing.Value);
               HttpContext.Current.Response.AddHeader("content-disposition", "attachment; filename=" + oSheet.Name + ".xls");
               HttpContext.Current.Response.AddHeader("Content-Type", "application/Excel");
               HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
               HttpContext.Current.Response.AddHeader("Content-Length", file.Length.ToString());
               HttpContext.Current.Response.WriteFile(file.FullName);
           oWBs.Close();
           oXL.Quit();
           GC.Collect();
           HttpContext.Current.Response.End();
       }
   }
Posted
Updated 1-Dec-14 4:55am
v2
Comments
ganesh.dks 1-Dec-14 3:47am    
The final section is creating problems.

///////////////////////////////
FileInfo file = new FileInfo(oXL.ActiveWorkbook.FullName);
oWB.SaveAs(oSheet.Name + ".xls", Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookDefault,
Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive,
Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value);
HttpContext.Current.Response.AddHeader("content-disposition", "attachment; filename=" + oSheet.Name + ".xls");
HttpContext.Current.Response.AddHeader("Content-Type", "application/Excel");
HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
HttpContext.Current.Response.AddHeader("Content-Length", file.Length.ToString());
HttpContext.Current.Response.WriteFile(file.FullName);
oWBs.Close();
oXL.Quit();
GC.Collect();
HttpContext.Current.Response.End();
////////////////////////////////////

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900