在ASP.NET MVC2 中导出excel
目前开发环境是：vs2010 ASP.NET MVC2 ，想在MVC中导出Excel,方式无所谓，我可以传一个dataTable或者dataSet，说白了就是在mvc2中实现dataTable或者dataSet导出Excel。(以前在普通的asp.net项目中也开发过类似导出的功能，但是在MVC中貌似不同)，请做过类似功能的把源代码粘贴出来，包括引用什么命名空间？在线等，比较急，遇到合适答案马上结贴给分。谢谢！
[解决办法]
给个例子吧，希望对你有帮助。
#region 导出用户信息到Excel
/// <summary>
/// 导出用户信息到Excel
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnExcelOut_Click(object sender, EventArgs e)
{
T_SYS_UserBLL userBll = new T_SYS_UserBLL();
Response.Clear();
Response.ContentType = "application/vnd.ms-excel";
Response.Charset = "GB2312";
Page.EnableViewState = false;
Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");//使输出的中文不乱码
string FileName = "人员导出Execel" + DateTime.Now.ToString("yyyy-MM-dd") + ".xls";
Response.AppendHeader("Content-Disposition", "attachment;filename= " + HttpUtility.UrlEncode(FileName, Encoding.UTF8).ToString());
Response.Write("<html><head><meta http-equiv=Content-Type content=\"text/html;charset=GB2312\"><title>Copyright by SDU</title></head><body><form runat=server id=b1>");
Response.Write("<table border='1' cellpadding='0' cellspacing='0'><tr><td width='280' hight='40' align=\"center\"><strong>用户编号</strong>"
+ "</td><td width='140' align=\"center\"><strong>用户姓名</strong></td><td width='140' align=\"center\"><strong>用户登录名</strong>"
+ "</td><td width='140' align=\"center\"><strong>用户性别</strong></td><td width='140' align=\"center\"><strong>用户QQ</strong>"
+ "</td><td width='140' align=\"center\"><strong>用户手机</strong></td><td width='140' align=\"center\"><strong>用户状态</strong>"
+ "<td width='140' align=\"center\"><strong>用户备注</strong></td></tr>");
DataSet ds = userBll.GetUserInfoList("");
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
string userID = ds.Tables[0].Rows[i]["User_ID"].ToString(); //用户编号
string loginName = ds.Tables[0].Rows[i]["User_LoginName"].ToString(); //用户登录名
string realName = ds.Tables[0].Rows[i]["User_RealName"].ToString(); //用户登录名
string userSex = ""; //用户性别
int Sex = Convert.ToInt32(ds.Tables[0].Rows[i]["User_SEX"]);
if (Sex == 1)
{
userSex = "男";
}
else if (Sex == 0)
{
userSex = "女";
}
string userQQ = ds.Tables[0].Rows[i]["User_QQ"].ToString();
string mobile = ds.Tables[0].Rows[i]["User_Mobile"].ToString();
string userStatus = "";
int status = Convert.ToInt32(ds.Tables[0].Rows[i]["User_Status"]);
if (status == 1)
{
userStatus = "已停用";
}
else if (status == 0)
{
userStatus = "正常";
}
string userDes = ds.Tables[0].Rows[i]["User_Des"].ToString();
Response.Write("<tr><td align=\"center\" height=\"20px\">" + userID + "</td>"
+ "<td align=\"center\" height=\"20px\">" + realName + "</td>"
+ "<td align=\"center\" height=\"20px\">" + loginName + "</td>"
+ "<td align=\"center\" height=\"20px\">" + userSex + "</td>"
+ "<td align=\"center\" height=\"20px\">" + userQQ + "</td>"
+ "<td align=\"center\" height=\"20px\">" + mobile + "</td>"
+ "<td align=\"center\" height=\"20px\">" + userStatus + "</td>"
+ "<td align=\"center\" height=\"20px\">" + userDes + "</td>"
+ "</tr>");
}
Response.Write("</table></form></body></html>");
Response.End();
}
#endregion
[解决办法]
http://www.codeproject.com/KB/aspnet/Streaming_Excel_ASP_NET.aspx
[解决办法]
http://download.csdn.net/source/2997557
我写的 生成了excel文件
[解决办法]
- C# code
#region 导出Excel public string ExportExcel(string fileState, string jobState) { string title = int.Parse(jobState) == 0 ? "在职员工信息表" : "离职员工信息表"; try { if ((Session["user"] as user_info).degree_type != 2) { throw new Exception("对不起！你没有权限导出这些数据！"); } //立即加载 IsAtOnceLoad.IsLazyLoad = false; //保存文件的路径 string savePath = string.Empty; //获取即将要导出的数据 var list = es.GetEmployeeInfoByStates(int.Parse(fileState), int.Parse(jobState)); //实例化Excel帮助类 ExcelOperate excel = new ExcelOperate(); //生成文件存放路径 string filePath = Server.MapPath("~/ExcelFile"); //创建文件夹 if (!Directory.Exists(filePath)) { Directory.CreateDirectory(filePath); } //建立一个Excel.Application进程 Application app = new Application(); if (app == null) { throw new Exception(); } app.Visible = false; app.UserControl = true; //创建工作区集合 Workbooks workbooks = app.Workbooks; //继承一个空模板 _Workbook workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet); //添加Sheet集合 Sheets sheets = workbook.Worksheets; //只有一个Sheet集合 _Worksheet worksheet = (_Worksheet)sheets.get_Item(1); //判断sheet是否为空 if (worksheet == null) { throw new Exception(); } //合并单元格 excel.Merge(worksheet, worksheet.Cells[1, 1], worksheet.Cells[1, 32]); //在指定单元格里添加标题 excel.WriteRange(worksheet, worksheet.Cells[1, 1], worksheet.Cells[1, 1], title); //粗体 excel.SetBold(worksheet, worksheet.Cells[1, 1], worksheet.Cells[1, 1]); //居中 excel.SetHAlignCenter(worksheet, worksheet.Cells[1, 1], worksheet.Cells[1, 1]); //背景色 excel.SetBgColor(worksheet, worksheet.Cells[1, 1], worksheet.Cells[1, 1], System.Drawing.Color.Silver); //字体大小 excel.SetFontSize(worksheet, worksheet.Cells[1, 1], worksheet.Cells[1, 1], 16); //行高 excel.SetRowHeight(worksheet, worksheet.Cells[1, 1], worksheet.Cells[1, 1], 20); //设置标题 worksheet.Cells[2, 1] = "档案编号"; worksheet.Cells[2, 2] = "员工姓名"; worksheet.Cells[2, 3] = "所在部门"; worksheet.Cells[2, 4] = "所属职位"; worksheet.Cells[2, 5] = "性别"; worksheet.Cells[2, 6] = "出生年月"; worksheet.Cells[2, 7] = "学历"; //设置字体 excel.SetFontSize(worksheet, worksheet.Cells[2, 1], worksheet.Cells[2, 32], 12); //设置字体颜色 worksheet.get_Range(worksheet.Cells[2, 1], worksheet.Cells[2, 32]).Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black); //设置文本对齐方式 excel.SetHAlignCenter(worksheet, worksheet.Cells[2, 1], worksheet.Cells[2, 32]); //绑定数据 for (int i = 0; i < list.Count; i++) { worksheet.Cells[3 + i, 1] = list[i].employee_id; worksheet.Cells[3 + i, 2] = list[i].employee_name; worksheet.Cells[3 + i, 3] = list[i].department_info.department_name; worksheet.Cells[3 + i, 4] = list[i].major_info.major_name; worksheet.Cells[3 + i, 5] = list[i].employee_sex; worksheet.Cells[3 + i, 6] = list[i].employee_brithday.Value.ToShortDateString(); worksheet.Cells[3 + i, 7] = list[i].employee_educated_degreee; worksheet.Cells[3 + i, 8] = list[i].employee_educated_major; worksheet.Cells[3 + i, 9] = "'" + list[i].employee_card_number; worksheet.Cells[3 + i, 10] = list[i].employee_native; worksheet.Cells[3 + i, 11] = list[i].employee_folk; worksheet.Cells[3 + i, 12] = list[i].employee_government; worksheet.Cells[3 + i, 13] = list[i].employee_religion; worksheet.Cells[3 + i, 14] = list[i].employee_marriage; worksheet.Cells[3 + i, 15] = list[i].employee_job_date; worksheet.Cells[3 + i, 16] = list[i].employee_phone; worksheet.Cells[3 + i, 17] = list[i].employee_address; worksheet.Cells[3 + i, 18] = list[i].employee_postcode; worksheet.Cells[3 + i, 19] = list[i].employee_resume; worksheet.Cells[3 + i, 20] = list[i].employee_intro; worksheet.Cells[3 + i, 21] = list[i].employee_remove_number; worksheet.Cells[3 + i, 22] = list[i].employee_train_number; worksheet.Cells[3 + i, 23] = string.IsNullOrEmpty(list[i].regist_time.ToString()) ? "" : list[i].regist_time.Value.ToShortDateString(); worksheet.Cells[3 + i, 24] = string.IsNullOrEmpty(list[i].check_time.ToString()) ? "" : list[i].check_time.Value.ToShortDateString(); worksheet.Cells[3 + i, 25] = string.IsNullOrEmpty(list[i].change_time.ToString()) ? "" : list[i].change_time.Value.ToShortDateString(); worksheet.Cells[3 + i, 26] = string.IsNullOrEmpty(list[i].leave_time.ToString()) ? "" : list[i].leave_time.Value.ToShortDateString(); worksheet.Cells[3 + i, 27] = string.IsNullOrEmpty(list[i].delete_time.ToString()) ? "" : list[i].delete_time.Value.ToShortDateString(); worksheet.Cells[3 + i, 28] = string.IsNullOrEmpty(list[i].redelete_time.ToString()) ? "" : list[i].redelete_time.Value.ToShortDateString(); worksheet.Cells[3 + i, 29] = list[i].check_status == 0 ? "未审核" : "已审核"; worksheet.Cells[3 + i, 30] = list[i].file_status == 0 ? "正常" : "已删除"; worksheet.Cells[3 + i, 31] = string.IsNullOrEmpty(list[i].comeback_time.ToString()) ? "" : list[i].comeback_time.Value.ToShortDateString(); worksheet.Cells[3 + i, 32] = list[i].job_status == 0 ? "在职" : "离职"; //设置文本对齐方式 excel.SetHAlignCenter(worksheet, worksheet.Cells[3 + i, 1], worksheet.Cells[3 + i, 32]); } //设置列宽 var array = new string[] { "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z", "AA", "AB", "AC", "AD", "AE", "AF" }; for (int i = 0; i < array.Length; i++) { excel.SetColumnWidth(worksheet, array[i], 20); } //重新设置特殊栏宽度 excel.SetColumnWidth(worksheet, "E", 5); excel.SetColumnWidth(worksheet, "G", 10); excel.SetColumnWidth(worksheet, "K", 10); excel.SetColumnWidth(worksheet, "U", 10); excel.SetColumnWidth(worksheet, "V", 10); excel.SetColumnWidth(worksheet, "AC", 10); excel.SetColumnWidth(worksheet, "AD", 10); excel.SetColumnWidth(worksheet, "AF", 15); //设置sheet名称 worksheet.Name = title; //获取保存路径(防止导出文件重名) string fileName = worksheet.Name + "_" + RundNum() + "_" + DateTime.Now.Hour + "_" + DateTime.Now.Minute + ".xls"; savePath = filePath + "\\" + fileName; //保存Excel excel.SaveAs(workbook, savePath); //关闭进程 excel.Dispose(worksheet, workbook, app); //将文件名称保存到缓存中 HttpContext.Cache.Insert("fileName", fileName, null, DateTime.UtcNow.AddMinutes(2), TimeSpan.Zero); return "ok/" + fileName; } catch (Exception ex) { return ex.Message; } } /// <summary> /// 获取随机数，防止文件导出名字冲突 /// </summary> /// <returns></returns> public string RundNum() { Random r = new Random(); return r.Next(1000000, 999999999).ToString(); } #endregion
[解决办法]