when i create workbook object oWB with all data i wrote this line.. HttpContext.Current.Response.Write(oWB); HttpContext.Current.Response.End() so the filedialog opens ..when i select open it popups with two excel application one contail the data of workbook and another one contain one line..says" Microsoft.Office.Interop.Excel.Workbook" in excel
[VBA]
public static void ExportMediaPlan(string fileName, DataSet dsMediaPlan , int custId ,string export)
{
//HttpContext.Current.Response.Clear();
//HttpContext.Current.Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", fileName));
//HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
System.Collections.Generic.List<string> List = null;
Microsoft.Office.Interop.Excel.Application oXL;
Microsoft.Office.Interop.Excel.Workbook oWB;
Microsoft.Office.Interop.Excel.Worksheet oSheetSecond;
Microsoft.Office.Interop.Excel.Worksheet oSheet;
Microsoft.Office.Interop.Excel.Range oRng;
Microsoft.Office.Interop.Excel.Range xlsRange;
Microsoft.Office.Interop.Excel.Range xlRangecell;
object oMissing = System.Reflection.Missing.Value;
try
{
GC.Collect();// clean up any other excel guys hangin' around...
oXL = new Microsoft.Office.Interop.Excel.Application();
oXL.Visible = false;
//oXL.DisplayAlerts = false;
oWB = (Microsoft.Office.Interop.Excel.Workbook)(oXL.Workbooks.Add(true));
oSheet = (Worksheet)oWB.Worksheets[1];
oSheet.Name = "Media Plan";
oSheetSecond = (Worksheet)oWB.Worksheets.Add(Type.Missing, oSheet, 1, Type.Missing);
oSheetSecond.Name = "Lists";
oSheet.Activate();
//create header for Sheet 2
Array lstFieldName = System.Enum.GetNames(typeof(Enumeration.FieldName));
for (int i = 0; i < lstFieldName.Length; i++)
{
oSheetSecond.Cells[1, i + 1] = lstFieldName.GetValue(i).ToString();
List = GetCatAndSubCatList(lstFieldName.GetValue(i).ToString(),custId );
int iRowNew = 2;
for (int j = 0; j < List.Count; j++)
{
oSheetSecond.Cells[iRowNew, i + 1] = List[j].ToString();
iRowNew++;
}
Enumeration.FieldName fName = (Enumeration.FieldName)Enum.Parse(typeof(Enumeration.FieldName), lstFieldName.GetValue(i).ToString());
switch (fName)
{
case Enumeration.FieldName.CategoryName:
oSheetSecond.get_Range("A2", "A" + (List.Count + 1)).Name = Enumeration.FieldName.CategoryName.ToString();
break;
case Enumeration.FieldName.CureName:
oSheetSecond.get_Range("F2", "F" + (List.Count + 1)).Name = Enumeration.FieldName.CureName.ToString();
break;
case Enumeration.FieldName.PlanName:
oSheetSecond.get_Range("G2", "G" + (List.Count + 1)).Name = Enumeration.FieldName.PlanName.ToString();
break;
case Enumeration.FieldName.ProgramName:
oSheetSecond.get_Range("D2", "D" + (List.Count + 1)).Name = Enumeration.FieldName.ProgramName.ToString();
break;
case Enumeration.FieldName.Status:
oSheetSecond.get_Range("E2", "E" + (List.Count + 1)).Name = Enumeration.FieldName.Status.ToString();
break;
case Enumeration.FieldName.SubCategoryName:
oSheetSecond.get_Range("B2", "B" + (List.Count + 1)).Name = Enumeration.FieldName.SubCategoryName.ToString();
break;
case Enumeration.FieldName.Vendor:
oSheetSecond.get_Range("C2", "C" + (List.Count + 1)).Name = Enumeration.FieldName.Vendor.ToString();
break;
case Enumeration.FieldName .CostType :
oSheetSecond.get_Range("H2", "H" + (List.Count + 1)).Name = Enumeration.FieldName.CostType.ToString();
break;
default:
break;
}
}
// Create Header and sheet...
int iRow = 2;
for (int j = 0; j < dsMediaPlan.Tables[0].Columns.Count; j++)
{
oSheet.Cells[1, j + 1] = dsMediaPlan.Tables[0].Columns[j].ColumnName;
}
if (export == "Import")
{
for (int lnum = 2; lnum <= 50; lnum++)
{
int cellNum = 0;
foreach (DataColumn dc in dsMediaPlan.Tables[0].Columns)
{
string s1 = string.Empty;
string formula = string.Empty;
switch (cellNum)
{
case 20:
s1 = "U" + lnum;
formula = "=" + Enumeration.FieldName.CategoryName.ToString();
break;
case 21:
s1 = "V" + lnum;
formula = "=" + Enumeration.FieldName.SubCategoryName.ToString();
break;
case 22:
s1 = "W" + lnum;
formula = "=" + Enumeration.FieldName.Vendor.ToString();
break;
case 23:
s1 = "X" + lnum;
formula = "=" + Enumeration.FieldName.Status.ToString();
break;
case 28:
s1 = "AC" + lnum;
formula = "=" + Enumeration.FieldName.CureName.ToString();
break;
case 41:
s1 = "AP" + lnum;
formula = "=" + Enumeration.FieldName.ProgramName.ToString();
break;
case 44:
s1 = "AS" + lnum;
formula = "=" + Enumeration.FieldName.PlanName.ToString();
break;
case 45:
s1 = "AT" + lnum;
formula = "=" + Enumeration.FieldName.CostType.ToString();
break;
default:
break;
}
if (!string.IsNullOrEmpty(formula))
{
xlsRange = oSheet.get_Range(s1, s1);
xlsRange.Validation.Add(XlDVType.xlValidateList, XlDVAlertStyle.xlValidAlertStop, System.Type.Missing, formula, System.Type.Missing);
xlsRange.Validation.ShowError = false;
}
cellNum++;
}
}
}
else
{
// build the sheet contents
foreach (DataRow dr in dsMediaPlan.Tables[0].Rows)
{
int cellNum = 0;
foreach (DataColumn dc in dsMediaPlan.Tables[0].Columns)
{
string s1 = string.Empty;
string formula = string.Empty;
switch (cellNum)
{
case 20:
s1 = "U" + iRow;
formula = "=" + Enumeration.FieldName.CategoryName.ToString();
break;
case 21:
s1 = "V" + iRow;
formula = "=" + Enumeration.FieldName.SubCategoryName.ToString();
break;
case 22:
s1 = "W" + iRow;
formula = "=" + Enumeration.FieldName.Vendor.ToString();
break;
case 23:
s1 = "X" + iRow;
formula = "=" + Enumeration.FieldName.Status.ToString();
break;
case 28:
s1 = "AC" + iRow;
formula = "=" + Enumeration.FieldName.CureName.ToString();
break;
case 41:
s1 = "AP" + iRow;
formula = "=" + Enumeration.FieldName.ProgramName.ToString();
break;
case 44:
s1 = "AS" + iRow;
formula = "=" + Enumeration.FieldName.PlanName.ToString();
break;
case 45:
s1 = "AT" + iRow;
formula = "=" + Enumeration.FieldName.CostType.ToString();
break;
default:
oSheet.Cells[iRow, cellNum + 1] = dr[dc].ToString();
xlRangecell = (Range)oSheet.Cells[iRow, cellNum + 1];
xlRangecell.Interior.Color = System.Drawing.ColorTranslator.ToWin32(System.Drawing.Color.LightBlue);
break;
}
if (!string.IsNullOrEmpty(formula))
{
xlsRange = oSheet.get_Range(s1, s1);
xlsRange.Validation.Add(XlDVType.xlValidateList, XlDVAlertStyle.xlValidAlertStop, System.Type.Missing, formula, System.Type.Missing);
xlsRange.Validation.ShowError = false;
xlsRange.Formula = dr[dc].ToString();
xlsRange.Interior.Color = System.Drawing.ColorTranslator.ToWin32(System.Drawing.Color.LightBlue);
}
cellNum++;
}
iRow++;
}
}
dsMediaPlan.Dispose();
dsMediaPlan = null;
//Format A1:Z1 as bold, vertical alignment = center.
oSheetSecond.get_Range("A1", "IV1").Font.Bold = true;
oSheetSecond.get_Range("A1", "IV1").VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
//AutoFit columns A:Z.
oRng = oSheetSecond.get_Range("A1", "IV1");
oRng.EntireColumn.AutoFit();
//Format A1:Z1 as bold, vertical alignment = center.
oSheet.get_Range("A1", "IV1").Font.Bold = true;
oSheet.get_Range("A1", "IV1").VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
//AutoFit columns A:Z.
oRng = oSheet.get_Range("A1", "IV1");
oRng.EntireColumn.AutoFit();
oXL.Visible = false;
oXL.UserControl = false;
//HttpContext.Current.Response.Write(oWB);
//HttpContext.Current.Response.End();
//int ilm = oXL.Workbooks.Count;
oWB.SaveAs(fileName, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlNoChange, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing);
HttpContext.Current.Response.Buffer = true;
HttpContext.Current.Response.Charset = "";
HttpContext.Current.Response.Cache.SetCacheability(HttpCacheability.NoCache );
HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
HttpContext.Current.Response.AddHeader("content-disposition", "attachment;filename=" + fileName);
HttpContext.Current.Response.WriteFile(fileName); //put filepath here
HttpContext.Current.Response.Flush();
HttpContext.Current.Response.End();
// Need all following code to clean up and extingush all references!!!
oWB.Close(null, null, null);
oXL.Workbooks.Close();
oXL.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(oRng);
System.Runtime.InteropServices.Marshal.ReleaseComObject(oXL);
System.Runtime.InteropServices.Marshal.ReleaseComObject(oSheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(oWB);
System.Runtime.InteropServices.Marshal.ReleaseComObject(oSheetSecond);
GC.Collect(); // force final cleanup!
}
catch (Exception theException)
{
string s = theException.Message;
}
}
[/VBA]
Reply With Quote
