#region Excel导出
/// <summary>
/// 导出DataSet 到 Excel
/// </summary>
/// <param name="dt">DataTable</param>
/// <param name="fileName">文件名</param>
public void ExportToExcel(DataTable dt, string fileName)
{
System.Drawing.ColorConverter colorAdapter = new System.Drawing.ColorConverter();
Table table = new Table();
#region 表样式
table.BorderStyle = BorderStyle.Solid;
table.BorderWidth = 1;
table.GridLines = GridLines.Both;
table.Width = 1000;
#endregion
TableRow columnTr = new TableRow();
#region 表头样式
columnTr.ForeColor = System.Drawing.Color.White;
columnTr.Font.Bold = true;
columnTr.Font.Size = 10;
columnTr.Height = 25;
columnTr.VerticalAlign = VerticalAlign.Middle;
#endregion
//表头数据
for (int i = 0; i < dt.Columns.Count; i++)
{
//if (dt.Columns[i].ColumnName == "CORP_ID")
//{
// dt.Columns.Remove("CORP_ID");
//}
TableCell td = new TableCell();
td.Text = dt.Columns[i].ColumnName;
td.BackColor = System.Drawing.Color.Blue;
td.HorizontalAlign = HorizontalAlign.Center;
columnTr.Cells.Add(td);
}
table.Rows.Add(columnTr);//添加表头
//表内容填充
for (int i = 0; i < dt.Rows.Count; i++)
{
TableRow tr = new TableRow();
for (int j = 0; j < dt.Columns.Count; j++)
{
TableCell td = new TableCell();
td.Text = dt.Rows[i][j].ToString();
td.Font.Size = 10;
td.HorizontalAlign = j != dt.Columns.Count - 1 ? HorizontalAlign.Center : HorizontalAlign.Left;
//td.BackColor = System.Drawing.Color.LightGray;
td.Height = 25;
tr.Cells.Add(td);
}
table.Rows.Add(tr);
}
HtmlToExcel(table, fileName);
}
/// <summary>
/// 导出DataSet 到 Excel
/// </summary>
/// <param name="dt">DataTable</param>
/// <param name="columnName">指定列名:当DataSet列名为英文时可以自行指定对应中文名,用于在Excel中显示。【注意】指定列名应与DataSet各列相对应,且个数保持一致</param>
/// <param name="fileName">文件名</param>
public void ExportToExcel(DataTable dt, string[] columnName, string fileName)
{
System.Drawing.ColorConverter colorAdapter = new System.Drawing.ColorConverter();
Table table = new Table();
#region 表样式
table.BorderStyle = BorderStyle.Solid;
table.BorderWidth = 1;
table.GridLines = GridLines.Both;
table.Width = 1000;
#endregion
TableRow columnTr = new TableRow();
#region 表头样式
columnTr.ForeColor = System.Drawing.Color.White;
columnTr.Font.Bold = true;
columnTr.Font.Size = 10;
columnTr.Height = 25;
columnTr.VerticalAlign = VerticalAlign.Middle;
#endregion
//表头数据
for (int i = 0; i < columnName.Length; i++)
{
//if (dt.Columns[i].ColumnName == "CORP_ID")
//{
// dt.Columns.Remove("CORP_ID");
//}
//if (dt.Columns[i].ColumnName == "DOMAIN_ID")
//{
// dt.Columns.Remove("DOMAIN_ID");
//}
TableCell td = new TableCell();
td.Text = columnName[i];
td.BackColor = System.Drawing.Color.Blue;
td.HorizontalAlign = HorizontalAlign.Center;
columnTr.Cells.Add(td);
}
table.Rows.Add(columnTr);//添加表头
//表内容填充
for (int i = 0; i < dt.Rows.Count; i++)
{
TableRow tr = new TableRow();
for (int j = 0; j < columnName.Length; j++)
{
TableCell td = new TableCell();
td.Text = dt.Rows[i][j].ToString();
td.Font.Size = 10;
td.HorizontalAlign = HorizontalAlign.Center;
//td.BackColor = (System.Drawing.Color)colorAdapter.ConvertFromString("#e5e8e9");
td.Height = 25;
tr.Cells.Add(td);
}
table.Rows.Add(tr);
}
HtmlToExcel(table, fileName);
}
/// <summary>
/// 导出DataSet 到 Excel
/// </summary>
/// <param name="dt">DataTable</param>
/// <param name="title">表头名</param>
/// <param name="columnName">指定列名:当DataSet列名为英文时可以自行指定对应中文名,用于在Excel中显示。【注意】指定列名应与DataSet各列相对应,且个数保持一致</param>
/// <param name="fileName">文件名</param>
public void ExportToExcel(DataTable dt, string title, string[] columnName, string fileName)
{
System.Drawing.ColorConverter colorAdapter = new System.Drawing.ColorConverter();
Table table = new Table();
#region 标题样式
//table.BorderStyle = BorderStyle.Solid;
//table.BorderWidth = 1;
table.GridLines = GridLines.Both;
table.Width = 1000;
TableRow titleTr = new TableRow();
titleTr.Font.Bold = true;
titleTr.Font.Size = 12;
titleTr.Height = 40;
TableCell titleTc = new TableCell();
titleTc.ColumnSpan = columnName.Length;
titleTc.Text = title;
titleTc.HorizontalAlign = HorizontalAlign.Center;
#endregion
titleTr.Cells.Add(titleTc);
table.Rows.Add(titleTr);
TableRow columnTr = new TableRow();
#region 表头样式
columnTr.ForeColor = System.Drawing.Color.White;
columnTr.Font.Bold = true;
columnTr.Font.Size = 10;
columnTr.Height = 25;
columnTr.VerticalAlign = VerticalAlign.Middle;
#endregion
//表头数据
for (int i = 0; i < columnName.Length; i++)
{
//if (dt.Columns[i].ColumnName == "CORP_ID")
//{
// dt.Columns.Remove("CORP_ID");
//}
//if (dt.Columns[i].ColumnName == "DOMAIN_ID")
//{
// dt.Columns.Remove("DOMAIN_ID");
//}
TableCell td = new TableCell();
//td.BorderStyle = BorderStyle.Solid;
//td.BorderWidth = 1;
td.Text = columnName[i];
td.BackColor = System.Drawing.Color.Blue;
td.HorizontalAlign = HorizontalAlign.Center;
columnTr.Cells.Add(td);
}
table.Rows.Add(columnTr);//添加表头
//表内容填充
for (int i = 0; i < dt.Rows.Count; i++)
{
TableRow tr = new TableRow();
for (int j = 0; j < columnName.Length; j++)
{
TableCell td = new TableCell();
//td.BorderStyle = BorderStyle.Solid;
//td.BorderWidth = 1;
td.Text = dt.Rows[i][j].ToString();
td.Font.Size = 10;
td.HorizontalAlign = HorizontalAlign.Center;
td.VerticalAlign = VerticalAlign.Middle;
//td.BackColor = (System.Drawing.Color)colorAdapter.ConvertFromString("#e5e8e9");
td.Height = 25;
tr.Cells.Add(td);
}
table.Rows.Add(tr);
}
for (int col = 0; col < columnName.Length; col++)
{
int compCol = col < 2 ? col : col - 1;
for (int i = 1; i < table.Rows.Count - 1; i++)
{
table.Rows[i].Cells[col].RowSpan = 1;
for (int j = i + 1; j < table.Rows.Count; j++)
{
if (table.Rows[i].Cells[compCol].Text.Equals(table.Rows[j].Cells[compCol].Text))
{
table.Rows[i].Cells[col].RowSpan += 1;
table.Rows[j].Cells[col].Visible = false;
}
else
{
i = j - 1;
break;
}
}
}
}
HtmlToExcel(table, fileName);
}
/// <summary>
/// 将Web控件输出到Excel
/// </summary>
/// <param name="cHtml">Control Web控件</param>
/// <param name="fileName">文件名</param>
public void HtmlToExcel(Control cHtml, string fileName)
{
string vHtmlToExcel = "<html xmlns:x=\"urn:schemas-microsoft-com:office:excel\">";
vHtmlToExcel += "<head>";
vHtmlToExcel += "<!--[if gte mso 9]><xml>";
vHtmlToExcel += "<x:ExcelWorkbook>";
vHtmlToExcel += "<x:ExcelWorksheets>";
vHtmlToExcel += "<x:ExcelWorksheet>";
vHtmlToExcel += "<x:Name>a</x:Name>";
vHtmlToExcel += "<x:WorksheetOptions>";
vHtmlToExcel += "<x:Print>";
vHtmlToExcel += "<x:ValidPrinterInfo/>";
vHtmlToExcel += "</x:Print>";
vHtmlToExcel += "</x:WorksheetOptions>";
vHtmlToExcel += "</x:ExcelWorksheet>";
vHtmlToExcel += "</x:ExcelWorksheets>";
vHtmlToExcel += "</x:ExcelWorkbook>";
vHtmlToExcel += "</xml>";
vHtmlToExcel += "<![endif]--> ";
vHtmlToExcel += "</head>";
vHtmlToExcel += "<body>";
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.Write(vHtmlToExcel);
HttpContext.Current.Response.Buffer = true;
HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8) + ".xls");
HttpContext.Current.Response.ContentType = "application/ms-excel";//设置输出文件类型为excel文件。
HttpContext.Current.Response.Charset = "UTF-8";
HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("UTF-8");
System.Globalization.CultureInfo myCItrad = new System.Globalization.CultureInfo("ZH-CN", true);
System.IO.StringWriter oStringWriter = new System.IO.StringWriter(myCItrad);
System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
cHtml.RenderControl(oHtmlTextWriter);
HttpContext.Current.Response.Write(oStringWriter.ToString());
HttpContext.Current.Response.Write("</body></html>");
HttpContext.Current.Response.End();
HttpContext.Current.ApplicationInstance.CompleteRequest();
}
#endregion