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