第一种是最比较麻烦的也最好理解的Datatable直接response流导出EXCEL的方式不过这种方式如果需要有规定样式和布局排版可能情况会变得复杂~如有需要可留下邮箱发送详细实例
#region 利用DataTable Html流导出EXCEL文档方法
/// <summary>
/// 利用Datatable Html流导出EXCEL
/// </summary>
/// <param name="ds"></param>
/// <param name="fileName"></param>
private void CreateExcel(DataSet ds)
{
HttpResponse response = Page.Response;
string fileName = "ExportFile" + DateTime.Now.ToShortDateString();
string excelHeader = string.Empty;
string excelContent = string.Empty;
StringBuilder sb = new StringBuilder();
DataTable dt = ds.Tables[0];
DataRow[] dr = dt.Select();
int colCount = dt.Columns.Count;
string headName = string.Empty;
sb.Append("<table border='1'><tr>");
for (int i = 0; i < colCount; i++)
{
sb.Append("<td style='font-size:small; text-align:center; font-family:@宋体; font-weight:bold;'>");
headName = GetHeaderName(dt.Columns[i].Caption);
if (i == colCount - 1)
{
//excelHeader += headName + "/n";
sb.Append(headName + "</td></tr>");
}
else
{
//excelHeader += headName + "/t";
sb.Append(headName + "</td>");
}
}
foreach (DataRow row in dt.Rows)
{
sb.Append("<tr>");
for (int i = 0; i < colCount; i++)
{
sb.Append("<td style='font-size:small; text-align:center'>");
if (i == colCount - 1)
{
sb.Append(row[i].ToString() + "</td></tr>");
//excelContent += row[i].ToString() + "/n";
}
else
{
sb.Append(row[i].ToString() + "</td>");
//excelContent += row[i].ToString() + "/t";
}
}
}
sb.Append("</table>");
response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
//octet-stream html流，vnd.ms-excel-->Excel格式
response.ContentType = "application/vnd.ms-excel";
response.AddHeader("Content-Dispositon", "attachment; filename=" + fileName);
response.Write(@"<html><head>");
response.Write(@"<meta http-equiv='content-type' content='text/html; charset=gb2312'>");
response.Write("</head>");
response.Write("<body>");
response.Write(sb.ToString());
response.Write("</body>");
response.Write(@"</html>");
response.Write(excelHeader);
response.Write(excelContent);
response.Flush();
response.End();
}
/// <summary>
/// 得到表头名
/// </summary>
/// <param name="caption"></param>
/// <returns></returns>
private string GetHeaderName(string caption)
{
string headName = string.Empty;
switch (caption)
{
case "ID":
headName = "序号";
break;
case "StudentClass":
headName = "班级";
break;
case "StudentName":
headName = "学生";
break;
case "ChineseGrade":
headName = "语文";
break;
case "MathGrade":
headName = "数学";
break;
case "TotalGrade":
headName = "总成绩";
break;
default:
break;
}
return headName;
}
#endregion
第二种比较大众化一点用DataTable导出EXCEL(主要需要Interop.Excel.Dll类库)
#region 利用DataView与Microsoft.Office.Interop.Excel完成导出EXCEL 调用com组件
private void OutputExcelFile(System.Data.DataTable dt)
{
string ExportFile = HttpContext.Current.Server.MapPath("~/") + @"ExportFile";
string fileName = @"/" + DateTime.Now.ToString("yyyyMMddHHmmss");
if (!Directory.Exists(ExportFile))
{
Directory.CreateDirectory(ExportFile);
}
string fullName = System.Web.HttpContext.Current.Server.MapPath("~/ExportFile/" + fileName + ".xls");
Microsoft.Office.Interop.Excel.Application excel = new ApplicationClass();//引用Excel对象
Microsoft.Office.Interop.Excel._Workbook workBook = excel.Workbooks.Add(true);//引用工作簿对象
Microsoft.Office.Interop.Excel._Worksheet workSheet = workBook.ActiveSheet as Microsoft.Office.Interop.Excel._Worksheet;//建立sheet
//Excel中表格单元索引都是从1开始
int rowIndex = 1;
int colIndex = 1;
string headName = string.Empty;
//循环第一行的所有列取得表头信息并+样式
foreach (DataColumn column in dt.Columns)
{
headName = GetHeaderName(column.ColumnName);
excel.Cells[rowIndex, colIndex] = headName;
workSheet.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
workSheet.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).Font.Bold = true;
workSheet.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
colIndex++;
}
//取得内容信息
for (rowIndex= 2; rowIndex <= dt.Rows.Count; rowIndex++)
{
for (colIndex = 1; colIndex <= dt.Columns.Count; colIndex++)
{
excel.Cells[rowIndex, colIndex] = dt.Rows[rowIndex - 1][colIndex - 1];
workSheet.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
workSheet.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
}
}
//显示当前窗口
//excel.Visible = true;
workBook.SaveCopyAs(fullName);
dt = null;
workBook.Close(false, null, null);
workBook = null;
workSheet = null;
excel.Quit();
excel = null;
GC.Collect();
FileInfo file = new FileInfo(fullName);
Response.Clear();
Response.Charset = "GB2312";
Response.ContentEncoding = System.Text.Encoding.UTF8;
Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(file.Name));
// 添加头信息，指定文件大小，让浏览器能够显示下载进度
Response.AddHeader("Content-Length", file.Length.ToString());
Response.WriteFile(file.FullName);
Response.Flush();
Response.End();
}
/// <summary>
/// 取得表头信息
/// </summary>
/// <param name="caption"></param>
/// <returns></returns>
private string GetHeaderName(string caption)
{
string headName = string.Empty;
switch (caption)
{
case "ID":
headName = "序号";
break;
case "StudentClass":
headName = "班级";
break;
case "StudentName":
headName = "学生";
break;
case "ChineseGrade":
headName = "语文";
break;
case "MathGrade":
headName = "数学";
break;
case "TotalGrade":
headName = "总成绩";
break;
default:
break;
}
return headName;
}
#endregion
第二种方法中还涉及到单元格内数据类型不同可能产生不同的显示~例如时间，电话号码可能会产生科学技术发的显示问题~稍后的更新中会进行进一步修改~
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
