第一种方式
/// <summary>
/// 导出Excel 自定义列名
/// </summary>
/// <param name="dt">DataTable数据源</param>
/// <param name="fileName">文件名</param>
/// <param name="list">列名集合 如: “UsernName|用户名”</param>
public static void CreateExcel(DataTable dt, string fileName, ArrayList list)
{
HttpResponse Response = HttpContext.Current.Response;
Response.Buffer = true;
Response.ClearContent();
Response.ClearHeaders();
Response.AppendHeader("Content-Disposition", "attachment;filename=" + fileName);
Response.ContentType = "application/vnd.ms-excel";
Response.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312");
Response.Charset = "gb2312";
try
{
StringBuilder text = new StringBuilder();
//标题
text.Append("<tr>");
foreach (object obj in list)
{
string[] str = obj.ToString().Split('|');
text.Append("<td>" + str[1] + "</td>");
}
text.Append("</tr>");
//内容
double outResul = 0;
foreach (DataRow row in dt.Rows)
{
text.Append("<tr>");
foreach (object obj in list)
{
string[] str = obj.ToString().Split('|');
if (double.TryParse(row[str[0]].ToString(), out outResul))
{
text.Append("<td style=\"vnd.ms-excel.numberformat:@\">" + row[str[0]].ToString() + "</td>");
}
else
{
text.Append("<td>" + row[str[0]].ToString() + "</td>");
}
}
text.Append("</tr>");
}
Response.Write("<meta http-equiv=\"content-type\" content=\"application/ms-excel; charset=gb2312\"/> <table border='1' >" + text.ToString() + "</table>");
}
catch { }
Response.End();
}
第二种方法
//排班数据导出操作
public void ArrangeDataExport(HttpContext context, int departmentID, string departmentName, int year, int month)
{
StringBuilder sb_title = new StringBuilder(); //标题
StringBuilder sb_head_f = new StringBuilder();//月份
StringBuilder sb_head_s = new StringBuilder();//星期
StringBuilder sb_body = new StringBuilder(); //内容
ArrayList arrayList = arrangeBll.EmployeesInfoList(departmentID); //获取所有员工信息
tb_ArrangeTable arrangeTable = null; //才创建排班表对象
string name = string.Format("{0}{1}年{2}月排班表", departmentName, year, month);//命名格式
int allDays = Utility.DaysInMonth(year, month); //月中的所有天数
List<DayOfWeek> lst_DayOfWeek = Utility.AllWeek(year, month); //指定月中所有星期集合
using (StringWriter sw = new StringWriter())
{
sb_head_f.Append("\"\"\t\"\"\t\"\"\t");
for (int j = 1; j <= allDays; j++)
{
if (j == allDays)
sb_head_f.AppendFormat("{0}", j);
else
sb_head_f.AppendFormat("{0}\t", j);
}
sw.WriteLine(sb_head_f.ToString());//输出月份
sb_head_s.Append("职务\t序号\t姓名\t");
for (int j = 0; j < lst_DayOfWeek.Count; j++)
{
if (j == lst_DayOfWeek.Count - 1)
sb_head_s.AppendFormat("{0}", Utility.ConvetWeek(lst_DayOfWeek[j]));
else
sb_head_s.AppendFormat("{0}\t", Utility.ConvetWeek(lst_DayOfWeek[j]));
}
sw.WriteLine(sb_head_s.ToString());//输入星期
for (int i = 0; i < arrayList.Count; i++)
{
//获取员工及部门信息
tb_EmployeesInfoTable employee = (tb_EmployeesInfoTable)arrayList[i];
sb_body.Clear();//清空内容
arrangeTable = new tb_ArrangeTable();
arrangeTable.EmployeeInfoID = employee.ID;
arrangeTable.SerialNumber = employee.SerialNumber;
arrangeTable.IsDel = false;
ArrayList arrangeList = arrangeBll.ArrangeList(arrangeTable, year, month); //获取指定员工的指定月份的所有考勤信息
//设置人员信息
sb_body.AppendFormat("{0}\t{1}\t{2}\t", employee.Job.G_CName, (i + 1).ToString(), employee.Name);
for (int j = 0; j < arrangeList.Count; j++)
{
//赋值排班信息
if (j == arrangeList.Count - 1)
sb_body.AppendFormat("{0}", Utility.ReturnMsg((tb_ArrangeTable)arrangeList[j]));
else
sb_body.AppendFormat("{0}\t", Utility.ReturnMsg((tb_ArrangeTable)arrangeList[j]));
}
sw.WriteLine(sb_body.ToString());//一个人的排班信息
}
//context.Response.AddHeader("Content-Disposition", "attachment; filename=test.csv");
//context.Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", name));
context.Response.AddHeader("Content-Disposition", "attachment; filename=ArrangeTable.xls");
context.Response.ContentType = "application/ms-excel";
context.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
context.Response.Write(sw);
context.Response.End();
}
}