62,047
社区成员
发帖
与我相关
我的任务
分享
public void ToExcel(Control ctl)
{
string style = @"<style> .text { mso-number-format:\@;} </script> ";
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.Buffer = true;
HttpContext.Current.Response.Charset = "GB2312";
HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
HttpContext.Current.Response.ContentType = "application/ms-excel";
HttpContext.Current.Response.Write("<meta http-equiv=Content-Type content=\"text/html; charset=GB2312\">");
HttpContext.Current.Response.Write("<script type=\"text/javascript\">window.opener=null;window.open(\"\",\"_self\"); window.close();</script>");
HttpContext.Current.Response.Write(style);
HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + DateTime.Now.ToString("yyyyMMddhh24mss") + ".xls");
System.IO.StringWriter tw = new System.IO.StringWriter();
HtmlTextWriter hw = new HtmlTextWriter(tw);
ctl.RenderControl(hw);
HttpContext.Current.Response.Write(tw.ToString());
//HttpContext.Current.ApplicationInstance.CompleteRequest();
HttpContext.Current.Response.End();
}
/// <summary>
/// 运用NET自带的office组件导出Excel
/// </summary>
/// <param name="dt">DataTable数据源(要导出到Excel的数据)</param>
/// <param name="CoumnsName">Excel的数据列标题</param>
/// <param name="isShowExcle">是否显示Excel弹出框</param>
public void DataGridviewShowToExcel(DataTable dt, string[] CoumnsName, bool isShowExcle)
{
if (dt.Rows.Count == 0)//如果弹数据源为空,出异常信息
return;
//建立Excel对象
Microsoft.Office.Interop.Excel.Application excel = null;
try
{
excel = new Microsoft.Office.Interop.Excel.Application();
}
catch
{
System.Web.HttpContext.Current.Response.Write("<script>alert('EXCEL无法启动,请检查是否安装了Excel!')</script>");
return;
}
//新建工作簿
Microsoft.Office.Interop.Excel.Workbook xBk;
//新建工作表
Microsoft.Office.Interop.Excel.Worksheet xSt;
xBk = excel.Application.Workbooks.Add(true);
xSt = (Microsoft.Office.Interop.Excel.Worksheet)xBk.Worksheets[1];
xSt.Name = "导出数据";
//默认设置标题为DataTable的列名
if (CoumnsName.Length == 0)
{
//生成字段名称
for (int i = 0; i < dt.Columns.Count; i++)
{
excel.Cells[1, i + 1] = dt.Columns[i].ColumnName;
Microsoft.Office.Interop.Excel.Range CardHeadRange = xSt.Cells[1, i + 1] as Microsoft.Office.Interop.Excel.Range; //获取表头单元格对齐方式
CardHeadRange.Font.Bold = true;//设置表头字体加粗
CardHeadRange.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;//设置居中对齐
CardHeadRange.EntireRow.AutoFit();//自动调整行列宽度
CardHeadRange.EntireColumn.AutoFit();
}
}
else
{
//生成字段名称
for (int i = 0; i < CoumnsName.Length; i++)
{
excel.Cells[1, i + 1] = CoumnsName[i];
Microsoft.Office.Interop.Excel.Range CardHeadRange = xSt.Cells[1, i + 1] as Microsoft.Office.Interop.Excel.Range; //获取表头单元格对齐方式
CardHeadRange.Font.Bold = true;//设置表头字体加粗
CardHeadRange.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;//设置居中对齐
CardHeadRange.EntireRow.AutoFit();//自动调整行列宽度
CardHeadRange.EntireColumn.AutoFit();
}
}
//填充数据
for (int i = 0; i < dt.Rows.Count; i++)
{
for (int j = 0; j < dt.Columns.Count; j++)
{
if (dt.Rows[i][j].GetType() == typeof(string))
{
excel.Cells[i + 2, j + 1] = "'" + dt.Rows[i][j].ToString();
Microsoft.Office.Interop.Excel.Range CardRange = xSt.Cells[i + 2, j + 1] as Microsoft.Office.Interop.Excel.Range; //获取表内容对齐方式
CardRange.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;//设置居中对齐
CardRange.EntireRow.AutoFit();//自动调整行列宽度
CardRange.EntireColumn.AutoFit();
}
else
{
excel.Cells[i + 2, j + 1] = dt.Rows[i][j].ToString();
Microsoft.Office.Interop.Excel.Range CardRange = xSt.Cells[i + 2, j + 1] as Microsoft.Office.Interop.Excel.Range; //获取表内容对齐方式
CardRange.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;//设置居中对齐
CardRange.EntireRow.AutoFit();//自动调整行列宽度
CardRange.EntireColumn.AutoFit();
}
}
}
//显示excel
excel.Visible = isShowExcle;
excel.DisplayAlerts = false;
excel.AlertBeforeOverwriting = false;
}