public static void ClearQueryCache(string busiNo)
{
try
{
string queryNo = null;
string[] sps = new string[] { "_|_" };
foreach (var key in dicCache.Keys)
{
if (!key.ToString().StartsWith("GetQueryDataByPageCustFilter_"))
{
continue;
}
queryNo = key.ToString().Split(sps, StringSplitOptions.None)[1];
bf_Query query = bf_Querys.GetItem(queryNo);
if (query == null)
{
continue;
}
bf_Busi busi = bf_Busis.GetItem(busiNo);
var flg = false;
foreach (bf_Query_Item qi in query.Items)
{
if (qi.Business.TableNameDB == busi.TableNameDB)
{
flg = true;
break;
}
}
if (flg)
{
break;
}
queryNo = null;
}
if (string.IsNullOrEmpty(queryNo))
{
return;
}
List <object> lstKey = new List <object>();
foreach (var key in dicCache.Keys)
{
if (!key.ToString().StartsWith("GetQueryDataByPageCustFilter_"))
{
continue;
}
if (key.ToString().Split(sps, StringSplitOptions.None)[1] == queryNo)
{
lstKey.Add(key);
}
}
foreach (var k in lstKey)
{
Remove(k);
}
}
catch (Exception ex)
{
LogHelper.Error("ClearQueryCache", ex);
}
}
public static BooleanValue <XSSFWorkbook> TwoLeveltoExcelFile(DataTable dtMain, DataTable dtSub, string mainBusiNo, string subBusiNo, bf_Query query, string joinCondition = null)
{
XSSFWorkbook workbook = new XSSFWorkbook();
ISheet sheet = workbook.CreateSheet("Sheet1");
IRow head = sheet.CreateRow(0);
bf_Busi_Fields fieldsMain = bf_Busis.GetItem(mainBusiNo).Fields;
bf_Busi_Fields fieldsSub = bf_Busis.GetItem(subBusiNo).Fields;
//新建一个字体样式对象
IFont font = workbook.CreateFont();
ICellStyle headStyle = workbook.CreateCellStyle();
//设置字体加粗样式
font.FontHeightInPoints = 11;
font.FontName = "微软雅黑";
font.IsBold = true;
//使用SetFont方法将字体样式添加到单元格样式中
headStyle.SetFont(font);
ICellStyle dateStyle = workbook.CreateCellStyle();
IDataFormat dataformat = workbook.CreateDataFormat();
dateStyle.DataFormat = dataformat.GetFormat("yyyy-MM-dd HH:mm:ss");
int idx = 0;
int colsCount = 0;
foreach (bf_Busi_Field field in fieldsMain)
{
if (field.VisibleType != 1)
{
continue;
}
ICell headCell = head.CreateCell(idx);
headCell.SetCellValue(field.Caption);
headCell.CellStyle = headStyle;
idx++;
colsCount++;
}
idx = 1;
string mAlias = ((bf_Query_Item)query.Items.AsEnumerable().FirstOrDefault(s => ((bf_Query_Item)s).BusiNo == mainBusiNo)).Alias;
string sAlias = ((bf_Query_Item)query.Items.AsEnumerable().FirstOrDefault(s => ((bf_Query_Item)s).BusiNo == subBusiNo)).Alias;
// m.MRP_NO=s1.MRP_NO AND m.ID_NO=s1.BOM_NO
string[] joinCd = !string.IsNullOrEmpty(joinCondition)?joinCondition.Replace(".", "_").Split(' '):
((bf_Query_Item)query.Items.AsEnumerable().FirstOrDefault(s => ((bf_Query_Item)s).BusiNo == subBusiNo)).Condition.Replace(".", "_").Split(' ');
StringBuilder sbd = new StringBuilder();
List <string> parrentFields = new List <string>();
foreach (var cd in joinCd)
{
var items = cd.Split('=');
if (items.Length > 1)
{
var pcd = items.FirstOrDefault(f => f.ToUpper().StartsWith(mAlias.ToUpper()));
parrentFields.Add(pcd);
var ccd = items.FirstOrDefault(f => f.ToUpper().StartsWith(sAlias.ToUpper()));
sbd.Append(ccd);
sbd.Append("='{");
sbd.Append(parrentFields.Count() - 1);
sbd.Append("}'");
}
else
{
sbd.Append(" ");
sbd.Append(cd);
sbd.Append(" ");
}
}
foreach (DataRow mr in dtMain.Rows)
{
IRow row = sheet.CreateRow(idx);
fillRowData(fieldsMain, mAlias, row, mr, dateStyle);
idx++;
head = sheet.CreateRow(idx);
int colIdx = 1;
foreach (bf_Busi_Field field in fieldsSub)
{
if (field.VisibleType != 1)
{
continue;
}
ICell headCell = head.CreateCell(colIdx);
headCell.SetCellValue(field.Caption);
headCell.CellStyle = headStyle;
colIdx++;
}
idx++;
DataRow[] srs = null;
switch (parrentFields.Count)
{
case 1:
srs = dtSub.Select(string.Format(sbd.ToString(), mr[parrentFields[0]]));
break;
case 2:
srs = dtSub.Select(string.Format(sbd.ToString(), mr[parrentFields[0]], mr[parrentFields[1]]));
break;
case 3:
srs = dtSub.Select(string.Format(sbd.ToString(), mr[parrentFields[0]], mr[parrentFields[1]], mr[parrentFields[2]]));
break;
case 4:
srs = dtSub.Select(string.Format(sbd.ToString(), mr[parrentFields[0]], mr[parrentFields[1]], mr[parrentFields[2]], mr[parrentFields[3]]));
break;
}
if (srs == null)
{
continue;
}
foreach (DataRow sr in srs)
{
row = sheet.CreateRow(idx);
fillRowData(fieldsSub, sAlias, row, sr, dateStyle, 1);
idx++;
}
}
EXCELFileTools.SetColumnWidth(sheet, colsCount);
return(new BooleanValue <XSSFWorkbook>(workbook));
}
