protected void Excle_Onclick(object sender, ImageClickEventArgs e)
{
Pronum = txtProNum.Text.Trim();
ProNam = txtProNam.Text.Trim();
Staff = txtStaff.Text.Trim();
Group = this.DDLGroupname.SelectedValue.ToString();
if (Group == "全部")
{
Group = "";
}
dt = Bf.GetProjectVotes(Staff_ID, Pronum, ProNam, Staff, Group);
string[] worksheet = new string[1];
//画表单,(下面是设置Excel表格下方页脚的名字)
worksheet[0] = GetColumnHelper.GetWorkSheet("tablename",
GetReportXml(dt),
GetColumnHelper.ExcelOrientation.Landscape, "", 0, 0);
//转化xml(输出的文件名,在这里设置)
string xml = GetColumnHelper.GetXml(worksheet);
GetColumnHelper.Output(xml, "tablename" + DateTime.Now.ToString("yyyy-MM-dd") + ".xls");
}
//#region 导出
public string GetReportXml(DataTable dt)
{
StringBuilder sbXml = new StringBuilder();
sbXml.Append(GetColumnHelper.GenerateColumnStr(5, 50, 100, 70, 200, 80, 80, 80, 80, 80, 80, 80));
#region 第一行,标题行
StringBuilder sbRow = new StringBuilder();
sbRow.Append(GetColumnHelper.AssignCell("tablename",
GetColumnHelper.DataType.String,
GetColumnHelper.XLSStyle.HeaderName12,
10, 0, 0, string.Empty));
sbXml.Append(GetColumnHelper.AddRowTag(0, 40, string.Empty, sbRow.ToString()));
Microsoft.Office.Interop.Owc11.SpreadsheetClass xlsheet = new Microsoft.Office.Interop.Owc11.SpreadsheetClass();
xlsheet.get_Range(xlsheet.Cells[1, 1], xlsheet.Cells[1, _col]).set_MergeCells(true);
#endregion
#region 第二行,标识行
//StringBuilder sbRow = new StringBuilder();
sbRow = new StringBuilder();
//下面参数意义:0,0,0 占用列,占用行,所在位置
sbRow.Append(GetColumnHelper.AssignCell("Headname1",
GetColumnHelper.DataType.String,
AllDepartmentsDetailHelper.XLSStyle.TableHeaderCenterSongTiWithBorderNoBold10New,
0, 0, 0, string.Empty));
sbRow.Append(GetColumnHelper.AssignCell("Headname2",
GetColumnHelper.DataType.String,
AllDepartmentsDetailHelper.XLSStyle.TableHeaderCenterSongTiWithBorderNoBold10New,
0, 0, 0, string.Empty));
sbRow.Append(GetColumnHelper.AssignCell("Headname3",
GetColumnHelper.DataType.String,
AllDepartmentsDetailHelper.XLSStyle.TableHeaderCenterSongTiWithBorderNoBold10New,
0, 0, 0, string.Empty));
sbRow.Append(GetColumnHelper.AssignCell("Headname4",
GetColumnHelper.DataType.String,
AllDepartmentsDetailHelper.XLSStyle.TableHeaderCenterSongTiWithBorderNoBold10New,
0, 0, 0, string.Empty));
sbRow.Append(GetColumnHelper.AssignCell("Headname5",
GetColumnHelper.DataType.String,
AllDepartmentsDetailHelper.XLSStyle.TableHeaderCenterSongTiWithBorderNoBold10New,
0, 0, 0, string.Empty));
sbRow.Append(GetColumnHelper.AssignCell("Headname6",
GetColumnHelper.DataType.String,
AllDepartmentsDetailHelper.XLSStyle.TableHeaderCenterSongTiWithBorderNoBold10New,
0, 0, 0, string.Empty));
sbRow.Append(GetColumnHelper.AssignCell("Headname7",
GetColumnHelper.DataType.String,
AllDepartmentsDetailHelper.XLSStyle.TableHeaderCenterSongTiWithBorderNoBold10New,
0, 0, 0, string.Empty));
sbRow.Append(GetColumnHelper.AssignCell("Headname8",
GetColumnHelper.DataType.String,
AllDepartmentsDetailHelper.XLSStyle.TableHeaderCenterSongTiWithBorderNoBold10New,
0, 0, 0, string.Empty));
sbRow.Append(GetColumnHelper.AssignCell("Headname9",
GetColumnHelper.DataType.String,
AllDepartmentsDetailHelper.XLSStyle.TableHeaderCenterSongTiWithBorderNoBold10New,
0, 0, 0, string.Empty));
sbRow.Append(GetColumnHelper.AssignCell("Headname10",
GetColumnHelper.DataType.String,
AllDepartmentsDetailHelper.XLSStyle.TableHeaderCenterSongTiWithBorderNoBold10New,
0, 0, 0, string.Empty));
sbRow.Append(GetColumnHelper.AssignCell("Headname11",
GetColumnHelper.DataType.String,
AllDepartmentsDetailHelper.XLSStyle.TableHeaderCenterSongTiWithBorderNoBold10New,
0, 0, 0, string.Empty));
sbRow.Append(GetColumnHelper.AssignCell("Headname12",
GetColumnHelper.DataType.String,
AllDepartmentsDetailHelper.XLSStyle.TableHeaderCenterSongTiWithBorderNoBold10New,
0, 0, 0, string.Empty));
sbXml.Append(GetColumnHelper.AddRowTag(0, 45, string.Empty, sbRow.ToString()));
#endregion
#region 数据行
if (dt != null)
{
if (dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
sbRow = new StringBuilder();
sbRow.Append(AllDepartmentsDetailHelper.AssignCell(dt.Rows[i]["Cname1"].ToString().Trim(),
AllDepartmentsDetailHelper.DataType.String,
AllDepartmentsDetailHelper.XLSStyle.TableHeaderCenterSongTiWithBorderNoBold10New,
0, 0, 0, string.Empty));
sbRow.Append(AllDepartmentsDetailHelper.AssignCell(dt.Rows[i]["Cname2"].ToString().Trim(),
AllDepartmentsDetailHelper.DataType.String,
AllDepartmentsDetailHelper.XLSStyle.TableHeaderCenterSongTiWithBorderNoBold10New,
0, 0, 0, string.Empty));
sbRow.Append(AllDepartmentsDetailHelper.AssignCell(dt.Rows[i]["Cname3"].ToString().Trim(),
AllDepartmentsDetailHelper.DataType.String,
AllDepartmentsDetailHelper.XLSStyle.TableHeaderCenterSongTiWithBorderNoBold10New,
0, 0, 0, string.Empty));
sbRow.Append(AllDepartmentsDetailHelper.AssignCell(dt.Rows[i]["Cname4"].ToString().Trim(),
AllDepartmentsDetailHelper.DataType.String,
AllDepartmentsDetailHelper.XLSStyle.TableHeaderCenterSongTiWithBorderNoBold10New,
0, 0, 0, string.Empty));
sbRow.Append(AllDepartmentsDetailHelper.AssignCell(dt.Rows[i]["Cname5"].ToString().Trim(),
AllDepartmentsDetailHelper.DataType.String,
AllDepartmentsDetailHelper.XLSStyle.TableHeaderCenterSongTiWithBorderNoBold10New,
0, 0, 0, string.Empty));
sbRow.Append(AllDepartmentsDetailHelper.AssignCell(dt.Rows[i]["Cname6"].ToString().Trim(),
AllDepartmentsDetailHelper.DataType.String,
AllDepartmentsDetailHelper.XLSStyle.TableHeaderCenterSongTiWithBorderNoBold10New,
0, 0, 0, string.Empty));
sbRow.Append(AllDepartmentsDetailHelper.AssignCell(dt.Rows[i]["Cname7"].ToString().Trim(),
AllDepartmentsDetailHelper.DataType.String,
AllDepartmentsDetailHelper.XLSStyle.TableHeaderCenterSongTiWithBorderNoBold10New,
0, 0, 0, string.Empty));
sbRow.Append(AllDepartmentsDetailHelper.AssignCell(dt.Rows[i]["Cname8"].ToString().Trim(),
AllDepartmentsDetailHelper.DataType.String,
AllDepartmentsDetailHelper.XLSStyle.TableHeaderCenterSongTiWithBorderNoBold10New,
0, 0, 0, string.Empty));
sbRow.Append(AllDepartmentsDetailHelper.AssignCell(dt.Rows[i]["Cname9"].ToString().Trim(),
AllDepartmentsDetailHelper.DataType.String,
AllDepartmentsDetailHelper.XLSStyle.TableHeaderCenterSongTiWithBorderNoBold10New,
0, 0, 0, string.Empty));
sbRow.Append(AllDepartmentsDetailHelper.AssignCell(dt.Rows[i]["Cname10"].ToString().Trim(),
AllDepartmentsDetailHelper.DataType.String,
AllDepartmentsDetailHelper.XLSStyle.TableHeaderCenterSongTiWithBorderNoBold10New,
0, 0, 0, string.Empty));
sbRow.Append(AllDepartmentsDetailHelper.AssignCell(dt.Rows[i]["Cname11"].ToString().Trim(),
AllDepartmentsDetailHelper.DataType.String,
AllDepartmentsDetailHelper.XLSStyle.TableHeaderCenterSongTiWithBorderNoBold10New,
0, 0, 0, string.Empty));
sbRow.Append(AllDepartmentsDetailHelper.AssignCell(dt.Rows[i]["Cname12"].ToString().Trim(),
AllDepartmentsDetailHelper.DataType.String,
AllDepartmentsDetailHelper.XLSStyle.TableHeaderCenterSongTiWithBorderNoBold10New,
0, 0, 0, string.Empty));
sbXml.Append(AllDepartmentsDetailHelper.AddRowTag(0, 25, string.Empty, sbRow.ToString()));
}
}
}
#endregion
#region 尾行
sbXml.Append(GetColumnHelper.AddBlankRow());
#endregion
return GetColumnHelper.AddTableTag(sbXml.ToString());
}
#endregion
#endregion