将从数据库中得到的数据,导出到excel表中(得到的数据使用的List泛型)

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
            {

           }
        }

 

 

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值