//Временное быстрое решения расчитанное на 2 уровня
//При большем уровне вложенности необходимо переделать
private static TagBuilder FormThead <T>(ReportTableModel <T> model)
{
var thead = new TagBuilder("thead");
thead.AddCssClass("box");
var trTop = new TagBuilder("tr");
trTop.AddCssClass("box-title");
var trBottom = new TagBuilder("tr");
trBottom.AddCssClass("box-title");
foreach (var reportTableColumn in model.Columns)
{
var th = CreateColumn(reportTableColumn);
var childs = reportTableColumn.Childs;
if (childs == null)
{
th.Attributes.Add("rowspan", "2");
}
else
{
th.Attributes.Add("colspan", childs.Count.ToString(CultureInfo.InvariantCulture));
foreach (var tableColumn in childs)
{
trBottom.InnerHtml += CreateColumn(tableColumn);
}
}
trTop.InnerHtml += th;
}
thead.InnerHtml += trTop;
thead.InnerHtml += trBottom;
return(thead);
}
protected void btn_save_Click(object sender, ImageClickEventArgs e)
{
ReportTableModel report = new ReportTableModel();
string tablestr = string.Empty;
for (int i = 0; i < this.tablelist.Items.Count; i++)
{
if (tablelist.Items[i].Selected)
{
tablestr += "," + tablelist.Items[i].Value;
}
}
report.Tablelist = tablestr.TrimStart(',');
string id = "0";
if (Request.QueryString["getid"] != null)
{
id = Request.QueryString["getid"].ToString();
}
else
{
id = HidControl.Value;
}
report.Menuname = this.txt_menu.Text;
report.SqlStr = this.txt_sql.Text;
report.timeFlag = Convert.ToInt32(this.ddl_timeflag.SelectedValue);
report.Excelhead = "";
HidControl.Value = TableReportBus.InsertReport(report, UserInfo, id, hiduserid.Value).ToString();
}
private DataCell[,] CreateDataGrid(ReportTableModel reportTable)
{
if (reportTable.TableData == null || reportTable.TableData.Count == 0)
{
return(null);
}
DataCell[,] result = new DataCell[reportTable.TableData.Count + 1, reportTable.Header.Count];
for (int i = 0; i < reportTable.Header.Count; i++)
{
result[0, i] = new DataCell(reportTable.Header[i]);
}
for (int i = 0; i < reportTable.TableData.Count; i++)
{
ReportTableRow row = reportTable.TableData[i];
for (int j = 0; j < row.Count; j++)
{
string value = row[j].Value;
if (value == null)
{
value = string.Empty;
}
CellColor cellColor = row[j].Color;
result[i + 1, j] = new DataCell(value,
Color.FromArgb(255, cellColor.Red, cellColor.Green, cellColor.Blue));
}
}
return(result);
}
/// <summary>
///
/// </summary>
/// <param name="report"></param>
/// <param name="flag">判断是添加还是修改</param>
/// <returns></returns>
public static int InsertReport(ReportTableModel report, XBase.Common.UserInfoUtil userinfo, string ID, string useridlist)
{
int num = 0;
StringBuilder strSql = new StringBuilder();
if (ID == "0") //添加
{
strSql.Append("insert into defdba.ReportTable(Menuname,CompanyCD,sqlstr,timeFlag,tablelist,excelhead) values(@Menuname,@CompanyCD,@SqlStr,@timeFlag,@tablelist,@excelhead)");
strSql.Append(";select @@IDENTITY");
SqlParameter[] parameters =
{
new SqlParameter("@Menuname", SqlDbType.VarChar, 200),
new SqlParameter("@CompanyCD", SqlDbType.VarChar, 200),
new SqlParameter("@SqlStr", SqlDbType.Text),
new SqlParameter("@timeFlag", SqlDbType.Int, 4),
new SqlParameter("@tablelist", SqlDbType.VarChar, 100),
new SqlParameter("@excelhead", SqlDbType.VarChar, 100)
};
parameters[0].Value = report.Menuname;
parameters[1].Value = userinfo.CompanyCD;
parameters[2].Value = report.SqlStr;
parameters[3].Value = report.timeFlag;
parameters[4].Value = report.Tablelist;
parameters[5].Value = report.Excelhead;
num = Convert.ToInt32(SqlHelper.ExecuteScalar(strSql.ToString(), parameters));
}
else //修改
{
strSql.Append("update defdba.ReportTable set Menuname=@menuname,timeFlag=@timeFlag,sqlstr=@SqlStr,tablelist=@tablelist,excelhead=@excelhead where ID=@id");
SqlParameter[] parameters =
{
new SqlParameter("@Menuname", SqlDbType.VarChar, 200),
new SqlParameter("@CompanyCD", SqlDbType.VarChar, 200),
new SqlParameter("@SqlStr", SqlDbType.Text),
new SqlParameter("@timeFlag", SqlDbType.Int, 4),
new SqlParameter("@tablelist", SqlDbType.VarChar, 100),
new SqlParameter("@ID", SqlDbType.VarChar, 100),
new SqlParameter("@excelhead", SqlDbType.VarChar, 500)
};
parameters[0].Value = report.Menuname;
parameters[1].Value = userinfo.CompanyCD;
parameters[2].Value = report.SqlStr;
parameters[3].Value = report.timeFlag;
parameters[4].Value = report.Tablelist;
parameters[5].Value = ID;
parameters[6].Value = report.Excelhead;
try
{
SqlHelper.ExecuteTransSql(strSql.ToString(), parameters);
num = Convert.ToInt32(ID);
}
catch { return(0); }
}
//添加菜单
CreateMenu(report.Menuname, num.ToString(), userinfo, useridlist);
return(num);
}
public static MvcHtmlString CreateTable <T>(ReportTableModel <T> model, bool withTotal = true)
{
var table = new TagBuilder("table");
table.AddCssClass("table");
table.InnerHtml += FormThead(model);
IEnumerable <ReportTableColumn> columns = GetValueColumns(model.Columns);
table.InnerHtml += FormBody(model, columns, withTotal);
return(MvcHtmlString.Create(table.ToString()));
}
private static TagBuilder FormBody <T>(ReportTableModel <T> model, IEnumerable <ReportTableColumn> columns, bool withTotal)
{
var tbody = new TagBuilder("tbody");
foreach (var o in model.Data)
{
tbody.InnerHtml += FormRow(columns, o);
}
if (withTotal)
{
tbody.InnerHtml += FormRow(columns, model.Total);
}
return(tbody);
}
public static int InsertReport(ReportTableModel report, XBase.Common.UserInfoUtil userinfo, string ID, string useridlist)
{
return(TableReportDBHelper.InsertReport(report, userinfo, ID, useridlist));
}
public static MemoryStream CreateExcelFile <T>(ReportTableModel <T> model, bool withTotal = true)
{
var wb = new Workbook();
wb.Worksheets.Clear();
var sheet = wb.Worksheets.Add("Report");
var i = 0;
var start = 1;
var headerStyle = sheet.Cells[start, 0].GetStyle();
headerStyle.VerticalAlignment = TextAlignmentType.Center;
headerStyle.HorizontalAlignment = TextAlignmentType.Center;
headerStyle.Borders[BorderType.BottomBorder].Color = Color.Black;
headerStyle.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Medium;
headerStyle.Borders[BorderType.LeftBorder].Color = Color.Black;
headerStyle.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Medium;
headerStyle.Borders[BorderType.RightBorder].Color = Color.Black;
headerStyle.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Medium;
headerStyle.Borders[BorderType.TopBorder].Color = Color.Black;
headerStyle.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Medium;
headerStyle.Pattern = BackgroundType.Solid;
headerStyle.Font.IsBold = true;
headerStyle.ForegroundColor = GetColor(Color.LightGray, wb);
foreach (ReportTableColumn reportTableColumn in model.Columns)
{
var childs = reportTableColumn.Childs;
if (childs == null)
{
sheet.Cells.Merge(start, i, 2, 1);
var cell = sheet.Cells[start, i];
cell.SetStyle(headerStyle);
var s = cell.GetStyle();
s.IsTextWrapped = true;
cell.SetStyle(s);
sheet.Cells[start + 1, i].SetStyle(headerStyle);
cell.PutValue(FormatCaption(reportTableColumn.Caption));
++i;
}
else
{
var count = childs.Count;
sheet.Cells.Merge(start, i, 1, count);
var cell = sheet.Cells[start, i];
cell.PutValue(reportTableColumn.Caption);
for (int y = 0; y < count; y++)
{
sheet.Cells[start, i + y].SetStyle(headerStyle);
}
foreach (ReportTableColumn tableColumn in childs)
{
var c = sheet.Cells[start + 1, i];
c.PutValue(tableColumn.Caption);
c.SetStyle(headerStyle);
++i;
}
}
}
var columns = GetValueColumns(model.Columns);
int row = start + 1;
foreach (var o in model.Data)
{
FormExcelRow(columns, o, sheet.Cells, ++row);
}
if (withTotal)
{
FormExcelRow(columns, model.Total, sheet.Cells, ++row);
}
return(wb.SaveToStream());
}