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.
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();
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);
oXL.DisplayAlerts = false;
oWB = oXL.Workbooks.Add(System.Reflection.Missing.Value);
oSheet = (Microsoft.Office.Interop.Excel.Worksheet)oWB.Worksheets.get_Item(1);
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++)
{
if (rowCount == 2)
{
oSheet.Cells[1, ij] = dt.Columns[ij - 1].ColumnName;
}
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"));
}
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"));
}
}
}
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"));
}
}
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"));
}
}
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();
}
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();
}
}