前台： <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();
}
