1:在DAL层里获取数据。使用List<类型>集合保存起来。比如在DAL层里建立一个InFoService类。并且在GetAllEmail()方法中得到list泛型集合
2:界面上调用InFoService中的GetAllEmail()方法
public void GetEmail(){
InFoService info = new InFoService();
List<EmailInfo> Emails = info.GetAllEmail();
CreateExcelAndExport(Emails);
}
3:完成excle表的设置。将设置写在方法中
public void CreateExcelAndExport(List<EmailInfo> Emails)
{
Microsoft.Office.Interop.Excel.Application excel;
Microsoft.Office.Interop.Excel._Workbook workBook;
Microsoft.Office.Interop.Excel._Worksheet workSheet;
object misValue = System.Reflection.Missing.Value;
excel = new Microsoft.Office.Interop.Excel.ApplicationClass();
workBook = excel.Workbooks.Add(misValue);
workSheet = (Microsoft.Office.Interop.Excel._Worksheet)workBook.ActiveSheet;
//excle表下面的标题
workSheet.Name = "邮件发送表";
//行的初始坐标
int rowIndex = 1;
//列的初始坐标
int colIndex = 0;
//设置标题:一共18列
String[] ColumnNames = new String[]
{
"序号","问题编号","产品","状态","客户名称","致电客户","联系电话","问题类型","优先级","录入日期","预计解决日期","结束时间",
"录入员","转接人"," 解决者","问题描述","解决方法","问题分析"
};
//取得标题,自动设置excle表中的表头
for (int i = 0; i < ColumnNames.Length; i++)
{
colIndex++;
//excel表从1行1列进行开始
excel.Cells[1, colIndex] = ColumnNames[i];
}
//取得集合中的数据 :EmailInFo是自己创建的实体类,循环的得取数据。有18列,根据标题的数量进行增加
foreach (EmailInfo e in Emails)
{
excel.Cells[rowIndex + 1, 1] = rowIndex;
excel.Cells[rowIndex + 1, 2] = e.WorkItemID;
excel.Cells[rowIndex + 1, 3] = e.product;
excel.Cells[rowIndex + 1, 4] = e.statue;
excel.Cells[rowIndex + 1, 5] = e.customerName;
excel.Cells[rowIndex + 1, 6] = e.phoneCustomer;
excel.Cells[rowIndex + 1, 7] = e.phone;
excel.Cells[rowIndex + 1, 8] = e.QLeiXing;
excel.Cells[rowIndex + 1, 9] = e.youXianJi;
excel.Cells[rowIndex + 1, 10] = e.LuRuRiQi;
excel.Cells[rowIndex + 1, 11] = e.YuJiJieJueRiQi;
excel.Cells[rowIndex + 1, 12] = e.JieShuShiJian;
excel.Cells[rowIndex + 1, 13] = e.LuRuYuan;
excel.Cells[rowIndex + 1, 14] = e.ZhuanJieRen;
excel.Cells[rowIndex + 1, 15] = e.JieJueRen;
excel.Cells[rowIndex + 1, 16] = e.WenTiMiaoShu;
excel.Cells[rowIndex + 1, 17] = e.JieJueFangFA;
excel.Cells[rowIndex + 1, 18] = e.WenTiFenXi;
rowIndex++;
}
excel.Visible = false;
//保存文件的类型(过滤:只能是xls文件)
saveFileDialog.Filter = "Execl files (*.xls)|*.xls";
//设置默认文件类型显示顺序
saveFileDialog.FilterIndex = 0;
//保存对话框是否记忆上次打开的目录
saveFileDialog.RestoreDirectory = true;
//是否做出判断,该文件存不存在
saveFileDialog.CreatePrompt = true;
//显示对话框
saveFileDialog.ShowDialog();
//获取用户选择的路径,已经文件名称
string SaveFile = saveFileDialog.FileName;
//保存
workBook.SaveAs(SaveFile, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, misValue,
misValue, misValue, misValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive,
misValue, misValue, misValue, misValue, misValue);
//清空集合
Emails = null;
//关闭
workBook.Close(true, misValue, misValue);
//退出
excel.Quit();
//调用kill当前excel进程
PublicMethod.Kill(excel);
releaseObject(workSheet);
releaseObject(workBook);
releaseObject(excel);
}
//进行回收
private static void releaseObject(object obj)
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
obj = null;
}
catch
{
obj = null;
}
finally
{
GC.Collect();
}
}
}
public class PublicMethod
{
[DllImport("User32.dll", CharSet = CharSet.Auto)]
public static extern int GetWindowThreadProcessId(IntPtr hwnd, out int ID);
public static void Kill(Microsoft.Office.Interop.Excel.Application excel)
{
try
{
IntPtr t = new IntPtr(excel.Hwnd);
int k = 0;
GetWindowThreadProcessId(t, out k);
System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k);
p.Kill();
}
catch
{
}
}