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)); }