导出excel表功能

时间:2022-07-07 17:32:13
前台: <asp:Button ID="btndao" runat="server"  Text="导出excel文件" onclick="btndao_Click"></asp:Button>

。cs:
protected void btndao_Click(object sender, EventArgs e)
    {
        this.ExportExcel();
    }
    /// <summary>
    /// 导出数据到Ecxc
    /// </summary>
    private void ExportExcel()
    {
        using (aosmithnewEntities db = new aosmithnewEntities())
        {
            List<ReservationForm> formlist = db.ReservationForm.OrderBy(o => o.States).ThenByDescending(o => o.AddTime).ToList();
            string SavaFilesPath = globalVariables.Download + Guid.NewGuid() + ".xls";
            if (formlist == null || formlist.Count == 0)
            {
                throw new Exception("没有数据可导出。");
            }
            //建立一个Excel进程 Application
            Application excelApplication = new Application();             //默认值为 True。如果不想在宏运行时被无穷无尽的提示和警告消息所困扰,请将本属性设置为 False;这样每次出现需用户应答的消息时,Microsoft Excel
            // 将选择默认应答。
            //如果将该属性设置为 False,则在代码运行结束后,Micorosoft Excel 将该属性设置为 True,除非正运行交叉处理代码。
            //如果使用工作簿的 SaveAs 方法覆盖现有文件,“覆盖”警告默认为“No”,当 DisplayAlerts 属性值设置为 True 时,Excel 选择“Yes”。
            excelApplication.DisplayAlerts = false;             //  建立或打开一个 Workbook对象生成新Workbook
            Workbook workbook = excelApplication.Workbooks.Add(Missing.Value);
            int i = 2;
            Worksheet lastWorksheet = (Worksheet)workbook.Worksheets.get_Item(workbook.Worksheets.Count);
            Worksheet newSheet = (Worksheet)workbook.Worksheets.Add(Type.Missing, lastWorksheet, Type.Missing, Type.Missing);
            newSheet.Cells[1, 1] = "产品类型";
            newSheet.Cells[1, 2] = "型号";
            newSheet.Cells[1, 3] = "安装/维保";
            newSheet.Cells[1, 4] = "问题描述";
            newSheet.Cells[1, 5] = "手机";
            newSheet.Cells[1, 6] = "其他联系方式";
            newSheet.Cells[1, 7] = "联系人姓名";
            newSheet.Cells[1, 8] = "省份";
            newSheet.Cells[1, 9] = "城市";
            newSheet.Cells[1, 10] = "城(区)县";
            newSheet.Cells[1, 11] = "详细地址";
            newSheet.Cells[1, 12] = "上门时间";
            newSheet.Cells[1, 13] = "其他要求";
            newSheet.Cells[1, 14] = "表单状态";
            ((Range)newSheet.Rows.get_Item(1)).Font.Bold = true;
            foreach (var dt in formlist)
            {
                //DateTime dtime = DateTime.Now;
                newSheet.Cells[i, 1] = dt.ProductCategoryName;
                newSheet.Cells[i, 2] = dt.Model;
                newSheet.Cells[i, 3] = dt.FormName;
                newSheet.Cells[i, 4] = dt.Description;
                newSheet.Cells[i, 5] = dt.Mobile;
                newSheet.Cells[i, 6] = dt.TelPhone;
                newSheet.Cells[i, 7] = dt.ContactsName;
                newSheet.Cells[i, 8] = dt.ProvinceName;
                newSheet.Cells[i, 9] = dt.CityName;
                newSheet.Cells[i, 10] = dt.DistrictName;
                newSheet.Cells[i, 11] = dt.Address;
                newSheet.Cells[i, 12] = dt.ServicesTime;
                newSheet.Cells[i, 13] = dt.Other;
                if (int.Parse(dt.States.ToString()) != 0)
                {
                    newSheet.Cells[i, 14] = "已处理";                 }
                else 
                {
                    newSheet.Cells[i, 14] = "未处理";
                }
                i++;
            }
            newSheet.Cells.Columns.AutoFit();
            //删除原来的空Sheet
            ((Worksheet)workbook.Worksheets.get_Item(1)).Delete();
            ((Worksheet)workbook.Worksheets.get_Item(1)).Delete();
            ((Worksheet)workbook.Worksheets.get_Item(1)).Delete();
            //设置默认选中是第一个Sheet 类似于Select();
            ((Worksheet)workbook.Worksheets.get_Item(1)).Activate();
            try
            {
                workbook.Close(true, SavaFilesPath, Missing.Value);
            }
            catch (Exception e)
            {
                throw e;
            }
            UploadExcel(SavaFilesPath, true);
            excelApplication.Quit();
        }
    }
    /// <summary>
    /// 提供下载
    /// </summary>
    /// <param name="path"></param>
    /// <param name="page"></param>
    ///  <param name="isDelete"></param>
    private static void UploadExcel(string path, bool isDelete)
    {
        FileInfo file = new FileInfo(path);
        HttpContext.Current.Response.Clear();
        HttpContext.Current.Response.Charset = "GB2312";
        HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8;
        // 添加头信息,为"文件下载/另存为"对话框指定默认文件名
        HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode(file.Name));
        // 添加头信息,指定文件大小,让浏览器能够显示下载进度
        HttpContext.Current.Response.AddHeader("Content-Length", file.Length.ToString());
        // 指定返回的是一个不能被客户端读取的流,必须被下载
        HttpContext.Current.Response.ContentType = "application/ms-excel";
        // 把文件流发送到客户端
        HttpContext.Current.Response.WriteFile(file.FullName);
        HttpContext.Current.Response.Flush();
        if (isDelete)
        {
            File.Delete(path);
        }
        // 停止页面的执行
        HttpContext.Current.Response.End();     }