/// <summary> /// 是否是明细数据所需要的主键ObjectID /// </summary> /// <param name="reportWidget"></param> /// <param name="ReportSource"></param> /// <param name="ChildCodes"></param> /// <returns></returns> private bool DetailNeedMainObjectId(ReportWidget reportWidget, ReportSource ReportSource, out Dictionary <string, ColumnSummary> ChildCodes) { ChildCodes = new Dictionary <string, ColumnSummary>(); Dictionary <string, ColumnSummary> Codes = new Dictionary <string, ColumnSummary>(); if (ReportSource.ReportSourceAssociations == null || ReportSource.ReportSourceAssociations.Length == 0) { return(false); } List <string> schemaCodes = new List <string>(); List <string> propertyCodes = new List <string>(); Dictionary <string, string> propertyNames = new Dictionary <string, string>(); Dictionary <string, string> displayNameTable = new Dictionary <string, string>(); //数据模型是否存在,子对象显示名称 { List <string> tempSchemaCodes = new List <string>(); List <string> childSchemaCodes = new List <string>(); foreach (ReportSourceAssociation q in ReportSource.ReportSourceAssociations) { if (q.IsSubSheet && q.MasterField.ToLowerInvariant() == ("I" + q.RootSchemaCode + "_ObjectID").ToLowerInvariant() && q.SubField.ToLowerInvariant() == ("I" + q.SchemaCode + "_ParentObjectID").ToLowerInvariant()) { tempSchemaCodes.Add(q.SchemaCode); tempSchemaCodes.Add(q.RootSchemaCode); childSchemaCodes.Add(q.SchemaCode); } } // if (tempSchemaCodes.Count == 0 || (tempSchemaCodes.Count > 0 && !this.Engine.BizObjectManager.ExistSchemas(tempSchemaCodes.ToArray()))) if (tempSchemaCodes.Count == 0) { return(false); } if (childSchemaCodes.Count > 0) { displayNameTable = this.GetSchemasDisplayName(OThinker.Data.Utility.RemoveRedundant <string>(childSchemaCodes.ToArray())); } } foreach (ReportSourceAssociation q in ReportSource.ReportSourceAssociations) { if (q.IsSubSheet && q.MasterField.ToLowerInvariant() == ("I" + q.RootSchemaCode + "_ObjectID").ToLowerInvariant() && q.SubField.ToLowerInvariant() == ("I" + q.SchemaCode + "_ParentObjectID").ToLowerInvariant()) { schemaCodes.Add(q.RootSchemaCode); propertyCodes.Add(q.SchemaCode); //前面有schema的存在性判断,这里不需要再判断 string displayname = displayNameTable[q.SchemaCode]; Codes.Add(q.SchemaCode, new ColumnSummary() { DisplayName = displayname }); } } if (schemaCodes.Count > 0) { propertyNames = this.GetPropertiesName(schemaCodes.ToArray(), propertyCodes.ToArray()); } foreach (ReportSourceAssociation q in ReportSource.ReportSourceAssociations) { if (q.IsSubSheet && q.MasterField.ToLowerInvariant() == ("I" + q.RootSchemaCode + "_ObjectID").ToLowerInvariant() && q.SubField.ToLowerInvariant() == ("I" + q.SchemaCode + "_ParentObjectID").ToLowerInvariant()) { if (propertyNames.ContainsKey(q.RootSchemaCode + q.SchemaCode)) { Codes.Add(q.SchemaCode, new ColumnSummary() { DisplayName = propertyNames[q.RootSchemaCode + q.SchemaCode] }); } } else { return(false); } } //树; foreach (ReportWidgetColumn q in reportWidget.Columns) { string originalcode = this.GetOriginalCode(q.ColumnCode); ColumnSummary t = new ColumnSummary(); t.DisplayName = q.DisplayName; t.Code = q.ColumnCode; if (Codes.ContainsKey(originalcode)) { if (ChildCodes.ContainsKey(originalcode)) { ChildCodes[originalcode].ChildeColumnSummary.Add(q.ColumnCode, t); } else { ChildCodes.Add(originalcode, Codes[originalcode]); ChildCodes[originalcode].ChildeColumnSummary.Add(q.ColumnCode, t); } } else { ChildCodes.Add(q.ColumnCode, new ColumnSummary() { DisplayName = q.DisplayName, Code = q.ColumnCode }); } } return(true); }
/// <summary> /// 明细表导出 /// </summary> /// <param name="SourceData"></param> /// <param name="ResultSourceColumns"></param> /// <param name="ExcelName"></param> /// <param name="ChildCodes"></param> protected void DetailExportToExcel(List <Dictionary <string, object> > SourceData, List <OThinker.Reporting.ReportWidgetColumn> ResultSourceColumns, string ExcelName, Dictionary <string, ColumnSummary> ChildCodes) { XSSFWorkbook book = new XSSFWorkbook(); ISheet sheet = book.CreateSheet("Sheet1"); int firstcolumnnum = 0; int secondcolumnnum = 0; #region 格式 ICellStyle style = book.CreateCellStyle(); style.WrapText = true; style.VerticalAlignment = VerticalAlignment.Center; style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left; #endregion HashSet <string> childecodes = new HashSet <string>(); //--------------------------------------------导出逻辑-------------------------------------- if (ChildCodes != null && ChildCodes.Count > 0) { #region 子表 //表头, sheet.CreateRow(0); IRow firstrow = sheet.GetRow(0); sheet.CreateRow(1); IRow secondrow = sheet.GetRow(1); int i = 0; foreach (string key in ChildCodes.Keys) { ColumnSummary node = ChildCodes[key]; if (node.ChildeColumnSummary != null && node.ChildeColumnSummary.Count > 0) { ICell cell = firstrow.CreateCell(firstcolumnnum); sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, firstcolumnnum, firstcolumnnum + node.ChildeColumnSummary.Count - 1)); cell.SetCellValue(node.DisplayName); firstcolumnnum = firstcolumnnum + node.ChildeColumnSummary.Count; foreach (string childkey in node.ChildeColumnSummary.Keys) { childecodes.Add(childkey); ColumnSummary childnode = node.ChildeColumnSummary[childkey]; ICell childcell = secondrow.CreateCell(secondcolumnnum); childcell.SetCellValue(childnode.DisplayName); childcell.CellStyle = style; secondcolumnnum++; } } else { ICell cell = firstrow.CreateCell(firstcolumnnum); cell.SetCellValue(node.DisplayName); cell.CellStyle = style; sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 1, firstcolumnnum, firstcolumnnum)); firstcolumnnum++; secondcolumnnum++; } i++; } //end表头 //表体 int RowNum = 2; int ColumnNum = 0; foreach (Dictionary <string, object> bo in SourceData) { ColumnNum = 0; sheet.CreateRow(RowNum); IRow row = sheet.GetRow(RowNum); int MaxHeight = 0; //取子表最大行数 foreach (string q in childecodes) { if (bo.ContainsKey(q)) { List <string> values = (List <string>)bo[q]; if (values != null) { if (values.Count > MaxHeight) { MaxHeight = values.Count; } } } } //准备行 if (MaxHeight > 1) { for (var tempnum = RowNum + 1; tempnum < RowNum + MaxHeight; tempnum++) { sheet.CreateRow(tempnum); } } foreach (OThinker.Reporting.ReportWidgetColumn Column in ResultSourceColumns) { if (childecodes.Contains(Column.ColumnCode)) { //子表从上到下填数据,从rownum到rownum+maxheight-1行 List <string> values = (List <string>)bo[Column.ColumnCode]; if (values != null && values.Count > 0) { int childRownum = RowNum; foreach (string v in values) { IRow childrow = sheet.GetRow(childRownum); ICell childcell = childrow.CreateCell(ColumnNum); switch (Column.ColumnType) { case ColumnType.Numeric: double tempresult = 0; if (double.TryParse(v, out tempresult)) { childcell.SetCellValue(tempresult); } else { childcell.SetCellValue(v); } break; default: childcell.SetCellValue(v); break; } childcell.CellStyle = style; childRownum++; } } } else { ICell cell = row.CreateCell(ColumnNum); switch (Column.ColumnType) { case ColumnType.Numeric: double tempresult = 0; if (double.TryParse(bo[Column.ColumnCode].ToString(), out tempresult)) { cell.SetCellValue(tempresult); } else { cell.SetCellValue(bo[Column.ColumnCode].ToString()); } break; default: cell.SetCellValue(bo[Column.ColumnCode].ToString()); break; } cell.CellStyle = style; sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(RowNum, RowNum + MaxHeight - 1, ColumnNum, ColumnNum)); } ColumnNum++; } RowNum++; } //end表体 #endregion } else { #region 无子表 //表头,如果有主表子表,需要改,暂时不改 sheet.CreateRow(0); IRow firstrow = sheet.GetRow(0); int i = 0; foreach (OThinker.Reporting.ReportWidgetColumn Column in ResultSourceColumns) { ICell cell = firstrow.CreateCell(i); cell.SetCellValue(Column.DisplayName); cell.CellStyle = style; i++; } //end表头 //表体 int RowNum = 1; int ColumnNum = 0; foreach (Dictionary <string, object> bo in SourceData) { ColumnNum = 0; sheet.CreateRow(RowNum); IRow row = sheet.GetRow(RowNum); foreach (OThinker.Reporting.ReportWidgetColumn Column in ResultSourceColumns) { ICell cell = row.CreateCell(ColumnNum); switch (Column.ColumnType) { case ColumnType.Numeric: double tempresult = 0; if (double.TryParse(bo[Column.ColumnCode].ToString(), out tempresult)) { cell.SetCellValue(tempresult); } else { cell.SetCellValue(bo[Column.ColumnCode].ToString()); } break; default: cell.SetCellValue(bo[Column.ColumnCode].ToString()); break; } cell.CellStyle = style; ColumnNum++; } RowNum++; } //end表体 #endregion } //----------------------------------------end导出逻辑--------------------------------------- ExcelName = ExcelName + "_" + DateTime.Now.ToString("yyyy-MM-dd"); using (System.IO.MemoryStream ms = new System.IO.MemoryStream()) { book.Write(ms); Response.Clear(); Response.Buffer = true; Response.Charset = "UTF-8"; Response.ContentType = "application/ms-excel;charset=UFT-8"; Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xlsx", ExcelName)); Response.BinaryWrite(ms.ToArray()); // Response.OutputStream.ToString(); Response.Flush(); // Response.Close(); Response.End(); book = null; ms.Close(); ms.Dispose(); } }