public static void CreateMatrixHeaderRow(this IWfMatrixContainer matrix, WorkSheet sheet)
{
matrix.NullCheck("matrix");
sheet.NullCheck("sheet");
Row headRow = new Row(3);
headRow.Style.Fill.SetBackgroundColor(Color.Gold, ExcelFillStyle.Solid);
headRow.Style.Border.Top.Style = ExcelBorderStyle.Thin;
headRow.Style.Border.Top.Color.SetColor(Color.Black);
headRow.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
headRow.Style.Border.Bottom.Color.SetColor(Color.Black);
headRow.Style.Border.Left.Style = ExcelBorderStyle.Thin;
headRow.Style.Border.Left.Color.SetColor(Color.Black);
headRow.Style.Font.Bold = true;
sheet.Rows.Add(headRow);
int columnIndex = 1;
foreach (SOARolePropertyDefinition dimension in matrix.PropertyDefinitions)
{
sheet.Cells[headRow.Index, columnIndex].Value = dimension.Description.IsNotEmpty() ? dimension.Description : dimension.Name;
sheet.Names.Add(CellAddress.Parse(columnIndex, headRow.Index).ToString(), dimension.Name);
columnIndex++;
}
}
/// <summary>
/// 从WorkSheet填充矩阵
/// </summary>
/// <param name="matrix"></param>
/// <param name="sheet"></param>
public static WfApprovalMatrix ToApprovalMatrix(this WorkSheet sheet)
{
sheet.NullCheck("sheet");
DataTable matrixTable = DocumentHelper.GetRangeValuesAsTable(sheet, "A3");
return(new WfApprovalMatrix(matrixTable));
}
public static void FillMatrixSheetData(this IWfMatrixContainer matrix, WorkSheet sheet)
{
matrix.NullCheck("matrix");
sheet.NullCheck("sheet");
int rowIndex = 4;
SOARolePropertyRowCollection rows = matrix.Rows;
foreach (SOARolePropertyRow row in rows)
{
foreach (DefinedName name in sheet.Names)
{
var propertyValue = row.Values.FindByColumnName(name.Name);
object dataValue = null;
if (propertyValue != null)
{
if (propertyValue.Column.DataType != Data.DataObjects.ColumnDataType.String)
{
dataValue = DataConverter.ChangeType(typeof(string),
propertyValue.Value,
propertyValue.Column.RealDataType);
}
else
{
dataValue = propertyValue.Value;
}
}
else
{
if (matrix.MatrixType != WfMatrixType.ApprovalMatrix)
{
switch (name.Name.ToLower())
{
case "operatortype":
dataValue = row.OperatorType.ToString();
break;
case "operator":
dataValue = row.Operator;
break;
}
}
}
if (dataValue != null)
{
sheet.Cells[rowIndex, name.Address.StartColumn].Value = dataValue;
}
}
rowIndex++;
}
}
/// <summary>
/// 将WfActivityMatrixResourceDescriptor填充到Excel的WorkSheet中
/// </summary>
/// <param name="sheet"></param>
/// <param name="activityMatrix"></param>
public static void FillActivityMatrixResourceDescriptor(this WorkSheet sheet, WfActivityMatrixResourceDescriptor activityMatrix)
{
sheet.NullCheck("sheet");
activityMatrix.NullCheck("activityMatrix");
int startRowIndex = 1;
Row titleRow = new Row(startRowIndex)
{
Height = 30d
};
titleRow.Style.Fill.SetBackgroundColor(Color.LightGray, ExcelFillStyle.Solid);
titleRow.Style.Font.Size = 20;
sheet.Rows.Add(titleRow);
sheet.Cells[titleRow.Index, 1].Value = "角色属性";
startRowIndex += 2;
CreateMatrixHeaderRow(sheet, activityMatrix, startRowIndex++);
FillMatrixSheetData(sheet, activityMatrix, startRowIndex);
}
public static void FillMatrixSheetData(this IWfMatrixContainer matrix, WorkSheet sheet)
{
matrix.NullCheck("matrix");
sheet.NullCheck("sheet");
int rowIndex = 4;
SOARolePropertyRowCollection rows = matrix.Rows;
foreach (SOARolePropertyRow row in rows)
{
foreach (DefinedName name in sheet.Names)
{
var propertyValue = row.Values.FindByColumnName(name.Name);
object dataValue = null;
if (propertyValue != null)
{
if (propertyValue.Column.DataType != Data.DataObjects.ColumnDataType.String)
{
dataValue = DataConverter.ChangeType(typeof(string),
propertyValue.Value,
propertyValue.Column.RealDataType);
}
else
{
dataValue = propertyValue.Value;
}
}
else
{
if (matrix.MatrixType != WfMatrixType.ApprovalMatrix)
{
switch (name.Name.ToLower())
{
case "operatortype":
dataValue = row.OperatorType.ToString();
break;
case "operator":
dataValue = row.Operator;
break;
}
}
}
if (dataValue != null)
sheet.Cells[rowIndex, name.Address.StartColumn].Value = dataValue;
}
rowIndex++;
}
}
public static void CreateMatrixHeaderRow(this IWfMatrixContainer matrix, WorkSheet sheet)
{
matrix.NullCheck("matrix");
sheet.NullCheck("sheet");
Row headRow = new Row(3);
headRow.Style.Fill.SetBackgroundColor(Color.Gold, ExcelFillStyle.Solid);
headRow.Style.Border.Top.Style = ExcelBorderStyle.Thin;
headRow.Style.Border.Top.Color.SetColor(Color.Black);
headRow.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
headRow.Style.Border.Bottom.Color.SetColor(Color.Black);
headRow.Style.Border.Left.Style = ExcelBorderStyle.Thin;
headRow.Style.Border.Left.Color.SetColor(Color.Black);
headRow.Style.Font.Bold = true;
sheet.Rows.Add(headRow);
int columnIndex = 1;
foreach (SOARolePropertyDefinition dimension in matrix.PropertyDefinitions)
{
sheet.Cells[headRow.Index, columnIndex].Value = dimension.Description.IsNotEmpty() ? dimension.Description : dimension.Name;
sheet.Names.Add(CellAddress.Parse(columnIndex, headRow.Index).ToString(), dimension.Name);
columnIndex++;
}
}
/// <summary>
/// 从WorkSheet提取DataTable
/// </summary>
/// <param name="sheet">工作表对象</param>
/// <param name="beginAddress">开始位置</param>
/// <param name="throwException">数据不存在时是否抛出异常</param>
/// <returns>返回首行创建成TableHeader</returns>
public static DataTable GetRangeValuesAsTable(WorkSheet sheet, string beginAddress, bool throwException = false)
{
sheet.NullCheck("workbook");
DataTable dt = new DataTable("RangValue");
CellAddress begionCell = CellAddress.Parse(beginAddress);
CellAddress endCell = CellAddress.Parse(sheet.Dimension.EndColumn, sheet.Dimension.EndRow);
for (int i = begionCell.ColumnIndex; i <= endCell.ColumnIndex; i++)
{
var headCellAdress = CellAddress.Parse(i, begionCell.RowIndex);
var namedRange = sheet.Names.FirstOrDefault(p => p.Address.StartRow == headCellAdress.RowIndex &&
p.Address.StartRow == p.Address.EndRow &&
p.Address.EndColumn == headCellAdress.ColumnIndex &&
p.Address.StartColumn == p.Address.EndColumn);
if (namedRange == null)
{
object objValue = sheet.Cells[begionCell.RowIndex, i].Value;
if (objValue != null)
dt.Columns.Add(new DataColumn(objValue.ToString()));
}
else
{
dt.Columns.Add(new DataColumn(namedRange.Name));
}
}
for (int j = begionCell.RowIndex + 1; j <= endCell.RowIndex; j++)
{
DataRow dr = dt.NewRow();
int temCol = begionCell.ColumnIndex;
int colIndex = 0;
while (temCol <= endCell.ColumnIndex && colIndex < dt.Columns.Count)
{
dr[colIndex] = sheet.Cells[j, temCol].Value;
temCol++;
colIndex++;
}
bool isEmptyRow = true;
for (int i = 0; i < dt.Columns.Count; i++)
{
if (!string.IsNullOrEmpty(dr[i].ToString()))
{
isEmptyRow = false;
break;
}
}
if (isEmptyRow == false)
dt.Rows.Add(dr);
}
ExceptionHelper.TrueThrow(throwException && dt.Rows.Count == 0, "未在Excel中找到数据");
return dt;
}