/// <summary>
/// FineUIPro.Grid数据导出
/// </summary>
/// <param name="sql">获取数据的SQL语句</param>
/// <param name="grid"></param>
/// <param name="columnIndex">从第几列开始导出</param>
/// <param name="fileName">导出的文件名</param>
/// <param name="headerText">表头文本</param>
public static void GridExport(string sql, FineUIPro.Grid grid, int columnIndex, string fileName, string headerText)
{
DataTable dtData = DbHelperOra.Query(sql).Tables[0];
if (dtData == null || dtData.Rows.Count == 0)
{
Alert.Show("暂时没有查询到符合条件的数据,无法导出！", "消息提示", MessageBoxIcon.Warning);
return;
}
if (columnIndex < 1)
{
columnIndex = 1;
}
//ArrayList arrColumName = new ArrayList();
string[] columnNames = new string[grid.Columns.Count - 1];
for (int index = columnIndex - 1; index < grid.Columns.Count; index++)
{
GridColumn column = grid.Columns[index];
if (column is FineUIPro.BoundField)
{
//dtData.Columns[((FineUIPro.BoundField)(column)).DataField.ToUpper()].DataType = Type.GetType("System.String");
dtData.Columns[((FineUIPro.BoundField)(column)).DataField.ToUpper()].ColumnName = column.HeaderText;
columnNames[index - 1] = column.HeaderText;
//arrColumName.Add(column.HeaderText);
}
}
ExcelHelper.ExportByWeb(dtData.DefaultView.ToTable(true, columnNames), headerText, string.Format("{0}_{1}.xls", fileName, DateTime.Now.ToString("yyyyMMddHHmmss")));
}
protected void CheckPowerWithBoundField(string powerName, FineUIPro.Grid grid, string columnID)
{
if (!CheckPower(powerName))
{
CheckPowerFailWithBoundField(grid, columnID);
}
}
/// <summary>
/// 批量加载数据
/// </summary>
public static void GridRowAdd(FineUIPro.Grid grid, DataTable table)
{
JArray defaultObj = new JArray();
defaultObj = JsonConvert.DeserializeObject <JArray>(JsonConvert.SerializeObject(table));
PageContext.RegisterStartupScript(getAddNewRecordReference(grid, defaultObj, true));
}
/// <summary>
/// 得到Grid 相应单元格数据
/// </summary>
/// <param name="grid">指定Grid</param>
/// <param name="rowId">行ID</param>
/// <param name="colID">列ID</param>
/// <returns></returns>
public static String GetGridInf(FineUIPro.Grid grid, string rowId, string colID)
{
JToken jtdata = grid.GetMergedData().Where(a => a["id"].ToString() == rowId).ToList()[0];
String res = (jtdata.SelectToken(string.Format("$..values.{0}", colID)) ?? "").ToString();
return(res);
}
/// <summary>
/// 批量加载Grid数据
/// </summary>
/// <returns></returns>
public static void GridRowAdd(FineUIPro.Grid grid, DataTable table)
{
JArray defaultObj = new JArray();
defaultObj = JsonConvert.DeserializeObject <JArray>(JsonConvert.SerializeObject(table));
PageContext.RegisterStartupScript("F('" + grid.ClientID + "').addNewRecords(" + defaultObj + ",'true');");
}
/// <summary>
/// 通过TABLE定义JOBJECT
/// </summary>
public static List <JObject> GetJObject(DataTable dtGoods, FineUIPro.Grid GoodsAdd)
{
List <JObject> Lobj = new List <JObject>();
foreach (DataRow rows in dtGoods.Rows)
{
JObject Obj = new JObject();
foreach (GridColumn gc in GoodsAdd.AllColumns)
{
string Columns = "";
if (gc is FineUIPro.RenderField)
{
Columns = ((FineUIPro.RenderField)(gc)).DataField.ToUpper();
}
if (gc is FineUIPro.BoundField)
{
Columns = ((FineUIPro.BoundField)(gc)).DataField.ToUpper();
}
if (Columns.Length > 0)
{
try
{
Obj.Add(Columns, rows[Columns].ToString());
}
catch
{
Obj.Add(Columns, "");
}
}
}
Lobj.Add(Obj);
}
return(Lobj);
}
protected void CheckPowerFailWithWindowField(FineUIPro.Grid grid, string columnID)
{
FineUIPro.WindowField btn = grid.FindColumn(columnID) as FineUIPro.WindowField;
btn.Hidden = true;
btn.Enabled = false;
btn.ToolTip = CHECK_POWER_FAIL_ACTION_MESSAGE;
}
private void GridBoundAdd(FineUIPro.Grid grid, DataRow[] rows, DataTable table, GroupField gf)
{
foreach (DataRow row in rows)
{
DataRow[] drs = table.Select("SJCODE='" + row["CODE"].ToString() + "'");
if (drs.Length > 0)
{
GroupField gf1 = new GroupField();
gf1.HeaderText = row["NAME"].ToString();
gf1.TextAlign = FineUIPro.TextAlign.Center;
GridBoundAdd(grid, drs, table, gf1);
gf.Columns.Add(gf1);
}
else
{
GroupField gf2 = new GroupField();
gf2.HeaderText = row["NAME"].ToString();
gf2.TextAlign = FineUIPro.TextAlign.Center;
FineUIPro.BoundField bfjs = new FineUIPro.BoundField();
bfjs.DataField = row["CODE"].ToString() + "_JS";
bfjs.HeaderText = "入库数量";
bfjs.TextAlign = FineUIPro.TextAlign.Right;
gf2.Columns.Add(bfjs);
FineUIPro.BoundField bfje = new FineUIPro.BoundField();
bfje.DataField = row["CODE"].ToString() + "_JE";
bfje.HeaderText = "入库金额";
bfje.TextAlign = FineUIPro.TextAlign.Right;
gf2.Columns.Add(bfje);
FineUIPro.BoundField bfxs = new FineUIPro.BoundField();
bfxs.DataField = row["CODE"].ToString() + "_XS";
bfxs.HeaderText = "出库数量";
bfxs.TextAlign = FineUIPro.TextAlign.Right;
gf2.Columns.Add(bfxs);
FineUIPro.BoundField bfxe = new FineUIPro.BoundField();
bfxe.DataField = row["CODE"].ToString() + "_XE";
bfxe.HeaderText = "出库金额";
bfxe.TextAlign = FineUIPro.TextAlign.Right;
gf2.Columns.Add(bfxe);
FineUIPro.BoundField bfcs = new FineUIPro.BoundField();
bfcs.DataField = row["CODE"].ToString() + "_CS";
bfcs.HeaderText = "库存数量";
bfcs.TextAlign = FineUIPro.TextAlign.Right;
gf2.Columns.Add(bfcs);
FineUIPro.BoundField bfce = new FineUIPro.BoundField();
bfce.DataField = row["CODE"].ToString() + "_CE";
bfce.HeaderText = "库存数量";
bfce.TextAlign = FineUIPro.TextAlign.Right;
gf2.Columns.Add(bfce);
gf.Columns.Add(gf2);
dtSource.Columns.Add(row["CODE"].ToString() + "_JS", Type.GetType("System.Decimal"));
dtSource.Columns.Add(row["CODE"].ToString() + "_JE", Type.GetType("System.Decimal"));
dtSource.Columns.Add(row["CODE"].ToString() + "_XS", Type.GetType("System.Decimal"));
dtSource.Columns.Add(row["CODE"].ToString() + "_XE", Type.GetType("System.Decimal"));
dtSource.Columns.Add(row["CODE"].ToString() + "_CS", Type.GetType("System.Decimal"));
dtSource.Columns.Add(row["CODE"].ToString() + "_CE", Type.GetType("System.Decimal"));
}
}
}
public static String GetGridTableHtml(FineUIPro.Grid grid)
{
StringBuilder sb = new StringBuilder();
MultiHeaderTable mht = new MultiHeaderTable();
mht.ResolveMultiHeaderTable(grid.Columns);
sb.Append("<meta http-equiv=\"content-type\" content=\"application/excel; charset=UTF-8\"/>");
sb.Append("<table cellspacing=\"0\" rules=\"all\" border=\"1\" style=\"border-collapse:collapse;\">");
foreach (List <object[]> rows in mht.MultiTable)
{
sb.Append("<tr>");
foreach (object[] cell in rows)
{
int rowspan = Convert.ToInt32(cell[0]);
int colspan = Convert.ToInt32(cell[1]);
GridColumn column = cell[2] as GridColumn;
sb.AppendFormat("<th{0}{1}{2}>{3}</th>",
rowspan != 1 ? " rowspan=\"" + rowspan + "\"" : "",
colspan != 1 ? " colspan=\"" + colspan + "\"" : "",
colspan != 1 ? " style=\"text-align:center;\"" : "",
column.HeaderText);
}
sb.Append("</tr>");
}
foreach (GridRow row in grid.Rows)
{
sb.Append("<tr>");
foreach (GridColumn column in mht.Columns)
{
string html = row.Values[column.ColumnIndex].ToString();
if (column.ColumnID == "tfNumber")
{
html = (row.FindControl("spanNumber") as System.Web.UI.HtmlControls.HtmlGenericControl).InnerText;
}
else if (column.ColumnID == "tfNumber1")
{
html = (row.FindControl("spanNumber1") as System.Web.UI.HtmlControls.HtmlGenericControl).InnerText;
}
else if (column.ColumnID == "tfGender")
{
html = (row.FindControl("labGender") as FineUIPro.Label).Text;
}
sb.AppendFormat("<td>{0}</td>", html);
}
sb.Append("</tr>");
}
sb.Append("</table>");
return(sb.ToString());
}
// 排序后分页
protected IQueryable <T> SortAndPage <T>(IQueryable <T> q, FineUIPro.Grid grid)
{
if (grid.PageIndex >= grid.PageCount && grid.PageCount >= 1)
{
grid.PageIndex = grid.PageCount - 1;
}
return(Sort(q, grid).Skip(grid.PageIndex * grid.PageSize).Take(grid.PageSize));
}
/// <summary>
/// 得到Grid 更新列数据
/// </summary>
/// <param name="grid">指定Grid</param>
/// <param name="rowId">行ID</param>
/// <returns></returns>
public static JObject GetJObject(FineUIPro.Grid grid, string rowId)
{
JObject defaultObj = new JObject();
try
{
foreach (JToken key in grid.GetMergedData().Where(a => a["id"].ToString() == rowId).ToList()[0].SelectToken("$.values").ToList())
{
defaultObj.Add(((JProperty)(key)).Name, ((JProperty)(key)).Value);
}
}
catch { }
return(defaultObj);
}
/// <summary>
/// FineUIPro.Grid控件的数据转换成DataTable
/// </summary>
/// <param name="grid">Grid控件</param>
/// <returns>DataTable数据源</returns>
public static DataTable GridDataGet(FineUIPro.Grid grid, FineUIPro.GridRowCollection rows)
{
int rowCount = 0;
DataTable dtGrid = new DataTable();
foreach (GridColumn gc in grid.Columns)
{
if (gc is FineUIPro.BoundField)
{
dtGrid.Columns.Add(new DataColumn(((FineUIPro.BoundField)(gc)).DataField.ToUpper(), typeof(string)));
}
else if (gc is FineUIPro.RenderField)
{
dtGrid.Columns.Add(new DataColumn(((FineUIPro.RenderField)(gc)).DataField.ToUpper(), typeof(string)));
}
else if (gc is FineUIPro.TemplateField)
{
dtGrid.Columns.Add(new DataColumn(((FineUIPro.TemplateField)(gc)).ColumnID.ToUpper(), typeof(string)));
}
rowCount++;
}
if (rowCount > 0)
{
foreach (GridRow gr in rows)
{
DataRow row = dtGrid.NewRow();
foreach (GridColumn gc in grid.Columns)
{
if (gc is FineUIPro.BoundField)
{
row[((FineUIPro.BoundField)(gc)).DataField.ToUpper()] = gr.Values[gc.ColumnIndex];
}
if (gc is FineUIPro.RenderField)
{
row[((FineUIPro.RenderField)(gc)).DataField.ToUpper()] = gr.Values[gc.ColumnIndex];
}
else if (gc is FineUIPro.TemplateField)
{
row[((FineUIPro.TemplateField)(gc)).ColumnID.ToUpper()] = gr.Values[gc.ColumnIndex];
}
}
dtGrid.Rows.Add(row);
}
}
return(dtGrid);
}
public static void DataExport(string sql, FineUIPro.Grid grid, string title)
{
DataTable dtData = DbHelperOra.QueryForTable(sql);
if (dtData == null || dtData.Rows.Count == 0)
{
Alert.Show("没有数据,无法导出！");
return;
}
DataTable table = new DataTable();
table.Columns.Add(new DataColumn("序号", typeof(int)));
foreach (DataColumn col in dtData.Columns)
{
table.Columns.Add(new DataColumn(col.ColumnName, col.DataType));
}
decimal rowno = 0;
foreach (DataRow row in dtData.Rows)
{
DataRow dr = table.NewRow();
dr["序号"] = ++rowno;
foreach (DataColumn col in dtData.Columns)
{
dr[col.ColumnName] = row[col.ColumnName];
}
table.Rows.Add(dr);
}
List <string> colmns = new List <string>();
colmns.Add("序号");
for (int index = 1; index < grid.Columns.Count; index++)
{
GridColumn column = grid.Columns[index];
if (column is FineUIPro.BoundField)
{
table.Columns[((FineUIPro.BoundField)(column)).DataField.ToUpper()].ColumnName = column.HeaderText;
colmns.Add(column.HeaderText);
}
}
ExcelHelper.ExportByWeb(dtData, title, title + "_" + DateTime.Now.ToString("yyyyMMddHH") + ".xls");
}
public static String GetGridTableHtml(FineUIPro.Grid grid, DataTable dataSource)
{
StringBuilder sb = new StringBuilder();
MultiHeaderTable mht = new MultiHeaderTable();
mht.ResolveMultiHeaderTable(grid.Columns);
sb.Append("<meta http-equiv=\"content-type\" content=\"application/excel; charset=UTF-8\"/>");
sb.Append("<table cellspacing=\"0\" rules=\"all\" border=\"1\" style=\"border-collapse:collapse;\">");
foreach (List <object[]> rows in mht.MultiTable)
{
sb.Append("<tr>");
foreach (object[] cell in rows)
{
int rowspan = Convert.ToInt32(cell[0]);
int colspan = Convert.ToInt32(cell[1]);
GridColumn column = cell[2] as GridColumn;
sb.AppendFormat("<th{0}{1}{2}>{3}</th>",
rowspan != 1 ? " rowspan=\"" + rowspan + "\"" : "",
colspan != 1 ? " colspan=\"" + colspan + "\"" : "",
colspan != 1 ? " style=\"text-align:center;\"" : "",
column.HeaderText);
}
sb.Append("</tr>");
}
foreach (DataRow row in dataSource.Rows)
{
sb.Append("<tr>");
for (int i = 0; i < dataSource.Columns.Count; i++)
{
sb.AppendFormat("<td>{0}</td>", row[i]);
}
sb.Append("</tr>");
}
sb.Append("</table>");
return(sb.ToString());
}
/// <summary>
/// 绑定FineUIPro.Grid分页方法 获取删除的页面
/// </summary>
/// <param name="columns">显示列名</param>
/// <param name="grid">FineUIPro.Grid</param>
/// <param name="tableName">表名</param>
/// <param name="strSql">where条件(加and)</param>
/// <param name="orderbyColumn">排序条件</param>
public void BindFineUI_del(string columns, FineUIPro.Grid grid, string tableName, string strSql, string orderbyColumn)
{
if (orderbyColumn.Length == 0)
{
orderbyColumn = string.Format(" {0} {1} ", grid.SortField, grid.SortDirection);
}
string sql = string.Format(@"select {0},row_number() over(order by {1}) rid into #tt from {2}
where isdel=1 {3} Order By {1} select * from #tt where rid> {4} and rid<={5} drop table #tt",
columns, orderbyColumn, tableName, strSql, grid.PageSize * grid.PageIndex, grid.PageSize * (grid.PageIndex + 1));
grid.RecordCount = Getdatatablecount(string.Format("select count(1) from {0} where isdel=1 {1}", tableName, strSql));
DataTable dt = GetPagedDataTable(sql);
DataView view1 = dt.DefaultView;
view1.Sort = orderbyColumn;
grid.DataSource = null;
grid.DataSource = view1;
grid.DataBind();
}
/// <summary>
/// 通过字符串，解析得到JOBJECT
/// </summary>
public static JObject GetJObject(string goods, FineUIPro.Grid GoodsAdd)
{
JObject Obj = new JObject();
List <JObject> Lobj = new List <JObject>();
if (GoodsAdd != null)
{
DataTable table = new DataTable();
DataRow dr;
string[] goodsRows = goods.Split('♂');
foreach (string rowValue in goodsRows)
{
dr = table.NewRow();
string[] row = rowValue.Split('♀');
if (row.Length != table.Columns.Count)
{
continue;
}
for (int i = 0; i < table.Columns.Count; i++)
{
dr[i] = row[i] == "★" ? "" : row[i];
}
table.Rows.Add(dr);
}
foreach (DataRow rows in table.Rows)
{
foreach (GridColumn gc in GoodsAdd.AllColumns)
{
try
{
Obj.Add(gc.ProductName, rows[gc.ProductName].ToString());
}
catch
{ }
}
}
}
return(Obj);
}
/// <summary>
/// 根据Grid列获取导出Excel的数据源
/// </summary>
/// <param name="gridData"></param>
/// <param name="dtData"></param>
/// <returns></returns>
public static DataTable ExportDataTable(FineUIPro.Grid gridData, DataTable dtData, string filed = "")
{
List <String> columnNames = new List <string>();
for (int index = 1; index < gridData.Columns.Count; index++)
{
GridColumn column = gridData.Columns[index];
if (column.Hidden == false)
{
if (column is FineUIPro.BoundField)
{
//dtData.Columns[((FineUIPro.BoundField)(column)).DataField.ToUpper()].DataType = Type.GetType("System.String");
dtData.Columns[((FineUIPro.BoundField)column).DataField.ToUpper()].ColumnName = column.HeaderText;
}
else if (column is FineUIPro.RenderField)
{
dtData.Columns[((FineUIPro.RenderField)column).DataField.ToUpper()].ColumnName = column.HeaderText;
}
columnNames.Add(column.HeaderText);
}
}
return(dtData.DefaultView.ToTable(true, columnNames.ToArray()));
}
public static string getAddNewRecordReference(FineUIPro.Grid grid, JArray ja, Boolean last)
{
string result = "F('" + grid.ClientID + "').f_addNewRecord(" + JsonConvert.SerializeObject(ja) + "," + last.ToString().ToLower() + ");";
return(result);
}
// 排序
protected IQueryable <T> Sort <T>(IQueryable <T> q, FineUIPro.Grid grid)
{
return(q.SortBy(grid.SortField + " " + grid.SortDirection));
}
/// <summary>
/// 绑定FineUIPro.Grid分页方法
/// </summary>
/// <param name="grid">FineUIPro.Grid</param>
/// <param name="tableName">表名</param>
/// <param name="strSql">where条件(加and)</param>
/// <param name="orderbyColumn">排序条件</param>
public void BindFineUI(FineUIPro.Grid grid, string tableName, string strSql, string orderbyColumn)
{
BindFineUI(" * ", grid, tableName, strSql, orderbyColumn);
}
/// <summary>
/// 绑定FineUIPro.Grid分页方法
/// </summary>
/// <param name="columns">显示列名</param>
/// <param name="grid">FineUIPro.Grid</param>
/// <param name="tableName">表名</param>
/// <param name="whereSql">where条件(加and)</param>
/// <param name="orderbyColumn">排序条件</param>
public void BindFineUI(string columns, FineUIPro.Grid grid, string tableName, string whereSql, string orderbyColumn)
{
FineuiProHelper.Grid.BindFineUI(columns, grid, tableName, whereSql, orderbyColumn);
}
/// <summary>
/// 绑定FineUIPro.Grid分页方法
/// </summary>
/// <param name="columns">显示列名</param>
/// <param name="grid">FineUIPro.Grid</param>
/// <param name="tableName">表名</param>
/// <param name="whereSql">where条件(加and)</param>
/// <param name="dic">条件参数和赋值</param>
/// <param name="orderbyColumn">排序条件</param>
public void BindFineUI(string columns, FineUIPro.Grid grid, string tableName, string whereSql, Dictionary <string, object> dic, string orderbyColumn)
{
FineuiProHelper.Grid.BindFineUI(columns, grid, tableName, whereSql, dic, orderbyColumn);
}
/// <summary>
/// 获取选中行的主id
/// </summary>
/// <param name="grid">FineUIPro.Grid</param>
/// <param name="keyNumIndex">第几个主键（从0开始）</param>
/// <returns></returns>
public static string GetDataKeysBySelectedRow(FineUIPro.Grid grid, int keyNumIndex)
{
return(FineuiProHelper.Grid.GetDataKeysBySelectedRow(grid, keyNumIndex));
}
/// <summary>
/// 获取选中行的主id，第一主键
/// </summary>
/// <param name="grid">FineUIPro.Grid</param>
/// <returns></returns>
public static string GetDataKeysBySelectedRow(FineUIPro.Grid grid)
{
return(FineuiProHelper.Grid.GetDataKeysBySelectedRow(grid, 0));
}
