private static WorksheetNode GetWorksheetFromWorkbook(WorkbookNode workbook, WfMatrixDefinitionExportOptions options) { WorksheetNode worksheet = null; if (workbook.Worksheets.Count > 0) { if (options.MatrixSheetName.IsNotEmpty()) { workbook.Worksheets.Contains(options.MatrixSheetName).FalseThrow("不能在模板中找到名称为{0}的工作簿", options.MatrixSheetName); worksheet = workbook.Worksheets[options.MatrixSheetName]; } else { worksheet = workbook.Worksheets[0]; } } else { worksheet = new WorksheetNode(); workbook.Worksheets.Add(worksheet); } if (options.MatrixSheetName.IsNotEmpty()) { worksheet.Name = options.MatrixSheetName; } else { worksheet.Name = "Matrix"; } return(worksheet); }
private static WorksheetNode GetWorksheetFromWorkbook(WorkbookNode workbook, string propertySheetName) { WorksheetNode worksheet = null; if (workbook.Worksheets.Count > 0) { if (propertySheetName.IsNotEmpty()) { workbook.Worksheets.Contains(propertySheetName).FalseThrow("不能在模板中找到名称为{0}的工作簿", propertySheetName); worksheet = workbook.Worksheets[propertySheetName]; } else { worksheet = workbook.Worksheets[0]; } } else { worksheet = new WorksheetNode(); workbook.Worksheets.Add(worksheet); } if (propertySheetName.IsNotEmpty()) { worksheet.Name = propertySheetName; } else { worksheet.Name = "Matrix"; } return(worksheet); }
private static void FillMatrixRowsToWorksheet(WorkbookNode workbook, WfMatrix matrix, bool roleAsPerson) { NamedLocationCollection locations = workbook.Names.ToLocations(); locations.SortByColumn(); WorksheetNode worksheet = workbook.Worksheets[GetWorksheet(locations)]; int startRowIndex = GetStartRow(locations); int currentRowIndex = -1; foreach (WfMatrixRow matrixRow in matrix.Rows) { RowNode row = new RowNode(); if (currentRowIndex == -1) { currentRowIndex = startRowIndex + 1; row.Index = currentRowIndex; } foreach (CellLocation location in locations) { CellNode cell = new CellNode(); WfMatrixCell mCell = matrixRow.Cells.Find(c => c.Definition.DimensionKey == location.Name); if (mCell != null) { cell.Data.Value = GetCellValue(roleAsPerson, mCell, matrixRow); } row.Cells.Add(cell); } worksheet.Table.Rows.Add(row); } }
private static void FillWorkbook(WfMatrixDefinition definition, WorkbookNode workbook, WfMatrixDefinitionExportOptions options) { WorksheetNode worksheet = GetWorksheetFromWorkbook(workbook, options); worksheet.Names.Clear(); workbook.Names.Clear(); worksheet.Table.Rows[1].Cells.Clear(); int row = options.StartRow; int column = options.StartColumn; RowNode titleRow = null; if (worksheet.Table.Rows.Count > 0) { titleRow = worksheet.Table.Rows[1]; } else { titleRow = new RowNode(); worksheet.Table.Rows.Add(titleRow); } foreach (WfMatrixDimensionDefinition dd in definition.Dimensions) { NamedRangeNode range = new NamedRangeNode(); range.Name = dd.DimensionKey; range.RefersTo = string.Format("={0}!R{1}C{2}", worksheet.Name, row, column); workbook.Names.Add(range); CellNode cell = new CellNode(); cell.Data.Value = dd.Name; if (options.TitleCellStyleID.IsNotEmpty()) { cell.StyleID = options.TitleCellStyleID; } titleRow.Cells.Add(cell); column++; } }
/// <summary> /// 填充Excel Xml的标题列 /// </summary> /// <param name="workbook"></param> /// <param name="definition"></param> /// <param name="propertySheetName"></param> private static void FillWorkSheetTitle(WorkbookNode workbook, SOARolePropertyDefinitionCollection definition, string propertySheetName) { WorksheetNode worksheet = GetWorksheetFromWorkbook(workbook, propertySheetName); worksheet.Names.Clear(); workbook.Names.Clear(); worksheet.Table.Rows[1].Cells.Clear(); int row = 3; int column = 1; RowNode titleRow = null; if (worksheet.Table.Rows.Count > 0) { titleRow = worksheet.Table.Rows[1]; } else { titleRow = new RowNode(); worksheet.Table.Rows.Add(titleRow); } foreach (SOARolePropertyDefinition dd in definition) { NamedRangeNode range = new NamedRangeNode(); range.Name = dd.Name; range.RefersTo = string.Format("={0}!R{1}C{2}", worksheet.Name, row, column); workbook.Names.Add(range); CellNode cell = new CellNode(); cell.Data.Value = dd.Description.IsNotEmpty() ? dd.Description : dd.Name; cell.StyleID = "s17"; titleRow.Cells.Add(cell); column++; } }
private static WorksheetNode GetWorksheetFromWorkbook(WorkbookNode workbook, string propertySheetName) { WorksheetNode worksheet = null; if (workbook.Worksheets.Count > 0) { if (propertySheetName.IsNotEmpty()) { workbook.Worksheets.Contains(propertySheetName).FalseThrow("不能在模板中找到名称为{0}的工作簿", propertySheetName); worksheet = workbook.Worksheets[propertySheetName]; } else { worksheet = workbook.Worksheets[0]; } } else { worksheet = new WorksheetNode(); workbook.Worksheets.Add(worksheet); } if (propertySheetName.IsNotEmpty()) worksheet.Name = propertySheetName; else worksheet.Name = "Matrix"; return worksheet; }
private static void FillIntoWorksheet(WorksheetNode worksheet, DataView view, DataViewExportOptions options) { foreach (DataColumn column in view.Table.Columns) { if (options.IgnoredColumnNames.Exists(c => c == column.ColumnName) == false) { ColumnNode columnNode = new ColumnNode(); columnNode.Caption = column.ColumnName; options.OnDateViewExportColumnHeader(column, columnNode, options.Context); worksheet.Table.Columns.Add(columnNode); } } bool isFirstRow = true; if (options.ExportColumnHeader) { RowNode rowNode = BuildHeaderRow(view.Table.Columns); if (options.StartRow > 0) rowNode.Index = options.StartRow; worksheet.Table.Rows.Add(rowNode); isFirstRow = false; } foreach (DataRowView drv in view) { RowNode rowNode = new RowNode(); if (isFirstRow) { if (options.StartRow > 0) rowNode.Index = options.StartRow; isFirstRow = false; } bool isFirstColumn = true; foreach (DataColumn column in view.Table.Columns) { if (options.IgnoredColumnNames.Exists(c => c == column.ColumnName) == false) { CellNode cellNode = new CellNode(); cellNode.Data.Type = GetCellDataType(column); object dataValue = drv[column.ColumnName]; if (dataValue != null) { if (cellNode.Data.Type == CellDataType.DateTime && (dataValue is DateTime)) { cellNode.Data.Value = string.Format("{0:yyyy-MM-ddTHH:mm:ss}", dataValue); cellNode.StyleID = "NormalDateTime"; } else cellNode.Data.Value = dataValue.ToString(); } if (isFirstColumn) { if (options.StartColumn > 0) cellNode.Index = options.StartColumn; isFirstColumn = false; } options.OnDateViewExportColumnData(column, cellNode, dataValue, options.Context); rowNode.Cells.Add(cellNode); } } worksheet.Table.Rows.Add(rowNode); } }
private static WorksheetNode BuildWorkSheet(string name, DataView view, DataViewExportOptions options) { WorksheetNode worksheet = new WorksheetNode(); worksheet.Name = name; FillIntoWorksheet(worksheet, view, options); return worksheet; }
//导出带数据的Excel private static void FillMatrixRowsToWorksheet(WorkbookNode workbook, SOARolePropertyRowCollection rows) { NamedLocationCollection locations = workbook.Names.ToLocations(); locations.SortByColumn(); WorksheetNode worksheet = workbook.Worksheets[GetWorksheet(locations)]; int startRowIndex = GetStartRow(locations); int currentRowIndex = -1; foreach (SOARolePropertyRow matrixRow in rows) { RowNode row = new RowNode(); if (currentRowIndex == -1) { currentRowIndex = startRowIndex + 1; row.Index = currentRowIndex; } for (int i = 0; i < locations.Count; i++) { CellNode cell = new CellNode(); CellLocation location = locations[i]; SOARolePropertyValue propertyValue = matrixRow.Values.FindByColumnName(location.Name); string dataValue = null; if (propertyValue != null) { dataValue = propertyValue.Value; } else { switch (location.Name.ToLower()) { case "operatortype": dataValue = matrixRow.OperatorType.ToString(); break; case "operator": dataValue = matrixRow.Operator; break; } } if (dataValue != null) { cell.Data.Value = dataValue; } else { cell.Data.Value = string.Empty; } row.Cells.Add(cell); } worksheet.Table.Rows.Add(row); } }