AspxGridView设置多表头，并利用HtmlTextWriter和MyXls两种方法分别导出数据到Excel

AspxGridView设置多表头

Code

protected void gvProject_HtmlRowCreated(object sender, DevExpress.Web.ASPxGridView.ASPxGridViewTableRowEventArgs e)

{

if (e.RowType == DevExpress.Web.ASPxGridView.GridViewRowType.Data && e.VisibleIndex == gvProject.PageIndex * gvProject.SettingsPager.PageSize)

{

Table tb = e.Row.Parent as Table;

TableRow tr = null;

TableCell tc = null;

tr = new TableRow();

tc = new TableCell();

tc.RowSpan = 2;

tc.CssClass = "HeadTc";

tc.Text = "序号";

tr.Cells.Add(tc);

tc = new TableCell();

tc.RowSpan = 2;

tc.CssClass = "HeadTc";

tc.Text = "借款人";

tr.Cells.Add(tc);

tc = new TableCell();

tc.RowSpan = 2;

tc.CssClass = "HeadTc";

tc.Text = "银团总额度";

tr.Cells.Add(tc);

tc = new TableCell();

tc.RowSpan = 2;

tc.CssClass = "HeadTc";

tc.Text = "我行承担额度";

tr.Cells.Add(tc);

tc = new TableCell();

tc.RowSpan = 2;

tc.CssClass = "HeadTc";

tc.Text = "我行角色";

tr.Cells.Add(tc);

tc = new TableCell();

tc.RowSpan = 2;

tc.CssClass = "HeadTc";

tc.Text = "经办机构";

tr.Cells.Add(tc);

tc = new TableCell();

tc.ColumnSpan = 1;

tc.CssClass = "HeadTc";

tc.Text = "意向";

tr.Cells.Add(tc);

tc = new TableCell();

tc.ColumnSpan = 3;

tc.CssClass = "HeadTc";

tc.Text = "立项流程";

tr.Cells.Add(tc);

tc = new TableCell();

tc.ColumnSpan = 2;

tc.CssClass = "HeadTc";

tc.Text = "尽职调查流程";

tr.Cells.Add(tc);

tc = new TableCell();

tc.ColumnSpan = 3;

tc.CssClass = "HeadTc";

tc.Text = "分行授信";

tr.Cells.Add(tc);

tc = new TableCell();

tc.ColumnSpan = 2;

tc.CssClass = "HeadTc";

tc.Text = "总行授信";

tr.Cells.Add(tc);

tc = new TableCell();

tc.ColumnSpan = 2;

tc.CssClass = "HeadTc";

tc.Text = "组团流程";

tr.Cells.Add(tc);

tc = new TableCell();

tc.ColumnSpan = 2;

tc.CssClass = "HeadTc";

tc.Text = "合同文本审定";

tr.Cells.Add(tc);

tc = new TableCell();

tc.RowSpan = 2;

tc.CssClass = "HeadTc";

tc.Text = "签约";

tr.Cells.Add(tc);

tc = new TableCell();

tc.RowSpan = 2;

tc.CssClass = "HeadTc";

tc.Text = "首次提款";

tr.Cells.Add(tc);

tc = new TableCell();

tc.RowSpan = 2;

tc.CssClass = "HeadTc";

tc.Text = "银团费收取";

tr.Cells.Add(tc);

tc = new TableCell();

tc.RowSpan = 2;

tc.CssClass = "HeadTc";

tc.Text = "费率及金额";

tr.Cells.Add(tc);

tc = new TableCell();

tc.RowSpan = 2;

tc.CssClass = "HeadTc";

tc.Text = "当前余额";

tr.Cells.Add(tc);

tc = new TableCell();

tc.RowSpan = 2;

tc.CssClass = "HeadTc";

tc.Text = "进度描述";

tr.Cells.Add(tc);

tc = new TableCell();

tc.RowSpan = 2;

tc.CssClass = "HeadTc";

tc.Text = "备注";

tr.Cells.Add(tc);

tc = new TableCell();

tc.RowSpan = 2;

tc.CssClass = "HeadTc";

tc.Text = "主评估人";

tr.Cells.Add(tc);

tb.Rows.AddAt(0, tr);

string[] sub ={ "接受委托", "支行上报", "报总行", "总行立项下发", "开始调查", "完成评估", "上报分行授信", "上会时间", "批复下发", "上报总行授信", "批复下发", "发出邀请函", "银团会议召开", "合同文本草拟完成", "合同文本审定完成" };

tr = new TableRow();

for (int i = 0; i < sub.Length; i++)

{

tc = new TableCell();

tc.CssClass = "HeadSubTc";

tc.Text = sub[i];

tr.Cells.Add(tc);

}

tb.Rows.AddAt(1, tr);

//tb.Rows.RemoveAt(0);

}

遗憾的是，AspxGridViewExporter不能将多表头导出到Excel，而常用的Response+HtmlTextWriter方法又会将AspxGridView的loading、pager等图片导出。可利用HtmlTextWriter的另一方法导出数据到Excel，不过这个Excel文件不是真正的Excel格式。

Code

protected void ASPxButton2_Click(object sender, EventArgs e)

{

Response.Clear();

Response.ContentType = "application/vnd.ms-excel";

string excelname = "报表";

//Response.AddHeader("content-disposition", "inline; filename=" + System.Web.HttpUtility.UrlEncode(excelname, System.Text.Encoding.UTF8) + ".xls"); //直接在浏览器中打开

Response.AddHeader("content-disposition", "attachment; filename=" + System.Web.HttpUtility.UrlEncode(excelname, System.Text.Encoding.UTF8) + ".xls");

this.EnableViewState = false;

System.IO.StringWriter oStringWriter = new System.IO.StringWriter();

System.Web.UI.HtmlTextWriter writer = new System.Web.UI.HtmlTextWriter(oStringWriter);

System.Text.StringBuilder builder = new System.Text.StringBuilder();

builder.Append("<table border='1'>");

builder.Append("<tr align='center' bgcolor='#BDCB85'>");

builder.Append("<td rowspan='2'>序号</td>");

builder.Append("<td rowspan='2'>借款人</td>");

builder.Append("<td rowspan='2'>银团总额度</td>");

builder.Append("<td rowspan='2'>我行承担额度</td>");

builder.Append("<td rowspan='2'>我行角色</td>");

builder.Append("<td rowspan='2'>经办机构</td>");

builder.Append("<td colspan='1'>意向</td>");

builder.Append("<td colspan='3'>立项流程</td>");

builder.Append("<td colspan='2'>尽职调查流程</td>");

builder.Append("<td colspan='3'>分行授信</td>");

builder.Append("<td colspan='2'>总行授信</td>");

builder.Append("<td colspan='2'>组团流程</td>");

builder.Append("<td colspan='2'>合同文本审定</td>");

builder.Append("<td rowspan='2'>签约</td>");

builder.Append("<td rowspan='2'>首次提款</td>");

builder.Append("<td rowspan='2'>银团费收取</td>");

builder.Append("<td rowspan='2'>费率及金额</td>");

builder.Append("<td rowspan='2'>当前余额</td>");

builder.Append("<td rowspan='2'>进度描述</td>");

builder.Append("<td rowspan='2'>备注</td>");

builder.Append("<td rowspan='2'>主评估人</td>");

builder.Append("</tr>");

builder.Append("<tr align='center' bgcolor='#BDB76B'>");

builder.Append("<td>接受委托</td>");

builder.Append("<td>支行上报</td>");

builder.Append("<td>报总行</td>");

builder.Append("<td>总行立项下发</td>");

builder.Append("<td>开始调查</td>");

builder.Append("<td>完成评估</td>");

builder.Append("<td>上报分行授信</td>");

builder.Append("<td>上会时间</td>");

builder.Append("<td>批复下发</td>");

builder.Append("<td>上报总行授信</td>");

builder.Append("<td>批复下发</td>");

builder.Append("<td>发出邀请函</td>");

builder.Append("<td>银团会议召开</td>");

builder.Append("<td>合同文本草拟完成</td>");

builder.Append("<td>合同文本审定完成</td>");

builder.Append("</tr>");

DataView dv = (DataView)AccessDataSource1.Select(DataSourceSelectArguments.Empty);

if (dv != null)

{

int i, j;

int rows = dv.Count;

int cols = gvProject.Columns.Count;

for (i = 0; i < rows; i++)

{

builder.Append("<tr>");

builder.Append("<td>");

builder.Append((i + 1).ToString() + "</td>");

for (j = 1; j < cols; j++)

{

if (dv[i][j] is System.DateTime)

builder.Append("<td style='vnd.ms-excel.numberformat:yyyy/MM/dd'>");

else

builder.Append("<td style='vnd.ms-excel.numberformat:#,###'>");

builder.Append(dv[i][j].ToString());

builder.Append("</td>");

}

builder.Append("</tr>");

}

builder.Append("</table>");

writer.Write(builder.ToString());

writer.Close();

Response.Write(oStringWriter.ToString());

Response.End();

}

其中Css样式文件部分内容如下：

Code

但是，从代码上看，利用HtmlTextWriter导出的Excel并不是真正的Excel格式文件，而且上述方法设置的Excel单元格背景色无效。我们可以采用MyXls来完善导出操作。

Code

protected void ASPxButton1_Click(object sender, EventArgs e)

{

XlsDocument xls = new XlsDocument();

xls.FileName = "Wacky.xls";

Worksheet sheet = xls.Workbook.Worksheets.Add("Sheet1");

Cells cells = sheet.Cells;

ColumnInfo ci = null;

ci = new ColumnInfo(xls, sheet);

ci.ColumnIndexStart = 0;

ci.ColumnIndexEnd = 0;

ci.Width = 5 * 256;

sheet.AddColumnInfo(ci);

ci = new ColumnInfo(xls, sheet);

ci.ColumnIndexStart = 1;

ci.ColumnIndexEnd = 1;

ci.Width = 35*256;

sheet.AddColumnInfo(ci);

ushort index;

for (index = 2; index <= 25;index++ )

{

ci = new ColumnInfo(xls, sheet);

ci.ColumnIndexStart = index;

ci.ColumnIndexEnd = index;

ci.Width = 10 * 256;

sheet.AddColumnInfo(ci);

}

ci = new ColumnInfo(xls, sheet);

ci.ColumnIndexStart = 26;

ci.ColumnIndexEnd = 26;

ci.Width = 15 * 256;

sheet.AddColumnInfo(ci);

ci = new ColumnInfo(xls, sheet);

ci.ColumnIndexStart = 27;

ci.ColumnIndexEnd = 27;

ci.Width = 35 * 256;

sheet.AddColumnInfo(ci);

ci = new ColumnInfo(xls, sheet);

ci.ColumnIndexStart = 28;

ci.ColumnIndexEnd = 28;

ci.Width = 30 * 256;

sheet.AddColumnInfo(ci);

MergeArea ma;

XF xfCell = null;

int i;

string[] BasicInfo ={ "序号", "借款人", "银团总额度", "我行承担额度", "我行角色", "经办机构" };

for (i = 1; i <= BasicInfo.Length; i++)

{

xfCell = xls.NewXF();

xfCell.HorizontalAlignment = HorizontalAlignments.Centered;

xfCell.VerticalAlignment = VerticalAlignments.Centered;

xfCell.Pattern = 1;

//xfCell.PatternBackgroundColor = Colors.Default16;

xfCell.PatternColor = Colors.Default16;

xfCell.LeftLineStyle = 1;

xfCell.LeftLineColor = Colors.Black;

xfCell.TopLineStyle = 1;

xfCell.TopLineColor = Colors.Black;

xfCell.BottomLineStyle = 1;

xfCell.BottomLineColor = Colors.Black;

xfCell.RightLineStyle = 1;

xfCell.RightLineColor = Colors.Black;

xfCell.TextWrapRight = true;

xfCell.Font.Bold = true;

cells.Add(1, i, BasicInfo[i - 1], xfCell);

cells.Add(2, i, "", xfCell);

ma = new MergeArea(1, 2, i, i);

sheet.AddMergeArea(ma);

}

string[] HostNode ={ "意向", "立项流程", "尽职调查流程", "分行授信", "总行授信", "组团流程", "合同文本审定" };

int[] SubNodeNum ={ 1, 3, 2, 3, 2, 2, 2 };

int SubNodeStartCol = i;

int j, startCol, endCol;

for (j = 1; j <= HostNode.Length; j++)

{

startCol = i;

endCol = startCol + SubNodeNum[j - 1] - 1;

xfCell = xls.NewXF();

xfCell.HorizontalAlignment = HorizontalAlignments.Centered;

xfCell.VerticalAlignment = VerticalAlignments.Centered;

xfCell.Pattern = 1;

xfCell.PatternColor = Colors.Default16;

xfCell.LeftLineStyle = 1;

xfCell.LeftLineColor = Colors.Black;

xfCell.TopLineStyle = 1;

xfCell.TopLineColor = Colors.Black;

xfCell.BottomLineStyle = 1;

xfCell.BottomLineColor = Colors.Black;

xfCell.RightLineStyle = 1;

xfCell.RightLineColor = Colors.Black;

xfCell.TextWrapRight = true;

xfCell.Font.Bold = true;

for (int id = startCol; id <= endCol;id++ )

cells.Add(1, id, "", xfCell);

ma = new MergeArea(1, 1, startCol, endCol);

sheet.AddMergeArea(ma);

cells.Add(1, startCol, HostNode[j - 1]);

i = endCol + 1;

}

string[] others ={ "签约", "首次提款", "银团费收取", "费率及金额", "当前余额", "进度描述", "备注", "主评估人" };

int k;

for (k = 0; k < others.Length; k++)

{

xfCell = xls.NewXF();

xfCell.HorizontalAlignment = HorizontalAlignments.Centered;

xfCell.VerticalAlignment = VerticalAlignments.Centered;

xfCell.Pattern = 1;

xfCell.PatternColor = Colors.Default16;

xfCell.LeftLineStyle = 1;

xfCell.LeftLineColor = Colors.Black;

xfCell.TopLineStyle = 1;

xfCell.TopLineColor = Colors.Black;

xfCell.BottomLineStyle = 1;

xfCell.BottomLineColor = Colors.Black;

xfCell.RightLineStyle = 1;

xfCell.RightLineColor = Colors.Black;

xfCell.TextWrapRight = true;

xfCell.Font.Bold = true;

cells.Add(1, k + i, others[k], xfCell);

cells.Add(2, k + i, "", xfCell);

ma = new MergeArea(1, 2, k + i, k + i);

sheet.AddMergeArea(ma);

}

string[] SubNode ={ "接受委托", "支行上报", "报总行", "总行立项下发", "开始调查", "完成评估", "上报分行授信", "上会时间", "批复下发", "上报总行授信", "批复下发", "发出邀请函", "银团会议召开", "合同文本草拟完成", "合同文本审定完成" };

Cell myCell = null;

for (k = 0; k < SubNode.Length; k++)

{

myCell = cells.Add(2, k + SubNodeStartCol, SubNode[k]);

myCell.HorizontalAlignment = HorizontalAlignments.Centered;

myCell.VerticalAlignment = VerticalAlignments.Centered;

myCell.Pattern = 1;

myCell.PatternColor = Colors.Default37;

myCell.LeftLineStyle = 1;

myCell.LeftLineColor = Colors.Black;

myCell.TopLineStyle = 1;

myCell.TopLineColor = Colors.Black;

myCell.BottomLineStyle = 1;

myCell.BottomLineColor = Colors.Black;

myCell.RightLineStyle = 1;

myCell.RightLineColor = Colors.Black;

myCell.TextWrapRight = true;

myCell.Font.Bold = true;

}

DataView dv = (DataView)AccessDataSource1.Select(DataSourceSelectArguments.Empty);

if (dv != null)

{

int rows = dv.Count;

int cols = gvProject.Columns.Count;

for (i = 0; i < rows; i++)

{

for (j = 0; j < cols; j++)

{

if (j == 0)

myCell = cells.Add(3 + i, 1, i + 1);

else

{

if (dv[i][j] is System.DateTime)

myCell = cells.Add(3 + i, j + 1, String.Format("{0:d}", dv[i][j]));

else if (dv[i][j] is System.Int32)

{

myCell = cells.Add(3 + i, j + 1, dv[i][j]);

myCell.Format = StandardFormats.Decimal_3; //数字带千分位：#,##0

}

else

myCell = cells.Add(3 + i, j + 1, dv[i][j].ToString());

}

myCell.LeftLineStyle = 1;

myCell.LeftLineColor = Colors.Black;

myCell.TopLineStyle = 1;

myCell.TopLineColor = Colors.Black;

myCell.BottomLineStyle = 1;

myCell.BottomLineColor = Colors.Black;

myCell.RightLineStyle = 1;

myCell.RightLineColor = Colors.Black;

myCell.TextWrapRight = true;

myCell.VerticalAlignment = VerticalAlignments.Centered;

}

xls.Send();

Response.Flush();

Response.End();

}

posted on 2009-05-11 20:43 ms_dos 阅读(2382) 评论(1) 收藏举报

刷新页面返回顶部