在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文件
[解决办法]
#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
