/// <summary>
/// 转换为OpenXml的WorkBook
/// </summary>
/// <param name="role"></param>
/// <returns></returns>
public static WorkBook ToExcelWorkBook(this SOARole role)
{
role.NullCheck("role");
WorkBook workBook = WorkBook.CreateNew();
FillFileInfo(workBook, role);
WorkSheet workSheet = workBook.Sheets[WorkBook.DefaultSheetName];
workSheet.Name = "Matrix";
Row titleRow = new Row(1) { Height = 30d };
titleRow.Style.Fill.SetBackgroundColor(Color.LightGray, ExcelFillStyle.Solid);
titleRow.Style.Font.Size = 20;
workSheet.Rows.Add(titleRow);
workSheet.Cells[titleRow.Index, 1].Value = "角色属性";
CreateHeaderRow(role, workSheet);
FillSheetData(role, workSheet);
return workBook;
}
/// <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);
}
private static void CreateMatrixHeaderRow(WorkSheet sheet, WfActivityMatrixResourceDescriptor activityMatrix, int startRowIndex)
{
Row headerRow = new Row(startRowIndex);
headerRow.Style.Fill.SetBackgroundColor(Color.Gold, ExcelFillStyle.Solid);
headerRow.Style.Border.Top.Style = ExcelBorderStyle.Thin;
headerRow.Style.Border.Top.Color.SetColor(Color.Black);
headerRow.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
headerRow.Style.Border.Bottom.Color.SetColor(Color.Black);
headerRow.Style.Border.Left.Style = ExcelBorderStyle.Thin;
headerRow.Style.Border.Left.Color.SetColor(Color.Black);
headerRow.Style.Font.Bold = true;
sheet.Rows.Add(headerRow);
int columnIndex = 1;
foreach (SOARolePropertyDefinition dimension in activityMatrix.PropertyDefinitions)
{
sheet.Cells[headerRow.Index, columnIndex].Value = dimension.Description.IsNotEmpty() ? dimension.Description : dimension.Name;
sheet.Names.Add(CellAddress.Parse(columnIndex, headerRow.Index).ToString(), dimension.Name);
columnIndex++;
}
}
private static void CreateHeaderRow(SOARole role, WorkSheet ws)
{
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;
ws.Rows.Add(headRow);
int columnIndex = 1;
foreach (SOARolePropertyDefinition dimension in role.PropertyDefinitions)
{
ws.Cells[headRow.Index, columnIndex].Value = dimension.Description.IsNotEmpty() ? dimension.Description : dimension.Name;
ws.Names.Add(CellAddress.Parse(columnIndex, headRow.Index).ToString(), dimension.Name);
columnIndex++;
}
}
/// <summary>
/// FileName:sheet.xml
/// <para>NodePath:worksheet/sheetData</para>
/// <para>NodePath:worksheet/sheetData/row</para>
/// </summary>
private XElement WriteWorkSheet_sheetData_row(WorkSheet sheet, Row row)
{
XElement rowXml = new XElement(ExcelCommon.Schema_WorkBook_Main + "row");
rowXml.Add(new XAttribute("r", row.Index.ToString(CultureInfo.InvariantCulture)));
if (sheet.Dimension.StartColumn > 0 && sheet.Dimension.EndColumn > 0)
{
rowXml.Add(new XAttribute("spans", string.Format(CultureInfo.InvariantCulture, "{0}:{1}",
sheet.Dimension.StartColumn, sheet.Dimension.EndColumn)));
}
if (row.Hidden)
{
rowXml.Add(new XAttribute("ht", "0"));
rowXml.Add(new XAttribute("hidden", "1"));
}
if (row.Height != sheet.DefaultRowHeight && row.Height != ExcelCommon.WorkSheet_DefaultRowHeight)
{
if (rowXml.Attributes("ht").Count() == 0)
rowXml.Add(new XAttribute("ht", row.Height.ToString(CultureInfo.InvariantCulture)));
rowXml.Add(new XAttribute("customHeight", "1"));
}
if (row._Style != null)
{
rowXml.Add(new XAttribute("s", GetStyleId(row.Style)));
rowXml.Add(new XAttribute("customFormat", "1"));
}
if (row.OutlineLevel > 0)
{
rowXml.Add(new XAttribute("outlineLevel", row.OutlineLevel.ToString(CultureInfo.InvariantCulture)));
if (row.Collapsed)
{
rowXml.Add(new XAttribute("collapsed", "1"));
if (row.Hidden == false)
rowXml.Add(new XAttribute("hidden", "1"));
}
}
if (row.Phonetic)
rowXml.Add(new XAttribute("ph", "1"));
return rowXml;
}
public void CopyTo(Row row)
{
row.Hidden = this.Hidden;
row.Height = this._Height;
row.Style = this._Style;
row.StyleID = this._StyleId;
row.StyleName = this._StyleName;
row.Phonetic = this.Phonetic;
row.Collapsed = this.Collapsed;
row.OutlineLevel = this.OutlineLevel;
}
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>
/// 转换为Excel的WorkSheet
/// </summary>
/// <param name="matrix"></param>
/// <param name="workBook"></param>
/// <returns></returns>
public static WorkSheet ToWorkSheet(this IWfMatrixContainer matrix, WorkBook workBook)
{
matrix.NullCheck("matrix");
WorkSheet sheet = new WorkSheet(workBook, "Matrix");
Row titleRow = new Row(1) { 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 = "审批矩阵";
CreateMatrixHeaderRow(matrix, sheet);
FillMatrixSheetData(matrix, sheet);
return sheet;
}
public Cell(Row row, Column column)
{
this.Column = column;
this.Row = row;
}
internal static Cell CreateNewCell(Row row, Column column)
{
return new Cell(row, column);
}
/// <summary>
/// FileName:sheet1.xml
/// <para>NodePath:worksheet/sheetData/row/c</para>
/// </summary>
/// <param name="bookViewsRoot"></param>
internal Cell ReadWorkSheet_sheetData_row_c(WorkSheet target, XElement item)
{
int rowIndex, columnIndex;
ExcelHelper.GetRowCol(item.Attribute("r").Value, out rowIndex, out columnIndex, true);
Row row = null;
Column col = null;
if (target.Rows.ContainsKey(rowIndex))
{
row = target.Rows[rowIndex];
}
else
{
row = new Row(rowIndex);
target.Rows.Add(row);
}
if (target.Columns.ContainsKey(columnIndex))
{
col = target.Columns[columnIndex];
}
else
{
col = new Column(columnIndex);
target.Columns.Add(col);
}
Cell cell = new Cell(row, col);
if (item.Attribute("t") != null)
{
cell.DataType = item.Attribute("t").Value;
}
if (item.Attribute("s") == null)
{
cell.StyleID = 0;
cell.Style = this.Context.GlobalStyles.CellStyleXfs[0];
}
else
{
cell.StyleID = int.Parse(item.Attribute("s").Value);
cell.Style = this.Context.GlobalStyles.CellXfs[cell.StyleID];
}
return cell;
}
/// <summary>
/// FileName:sheet1.xml
/// <para>NodePath:worksheet/sheetData/row</para>
/// </summary>
/// <param name="bookViewsRoot"></param>
internal void ReadWorkSheet_sheetData_row(Row target, XElement item)
{
target.Collapsed = item.Attribute("collapsed") != null && item.Attribute("collapsed").Value == "1" ? true : false;
if (item.Attribute("ht") != null)
{
target.Height = double.Parse(item.Attribute("ht").Value, CultureInfo.InvariantCulture);
}
target.Hidden = item.Attribute("hidden") != null && item.Attribute("hidden").Value == "1" ? true : false; ;
target.OutlineLevel = item.Attribute("outlineLevel") == null ? 0 : int.Parse(item.Attribute("outlineLevel").Value, CultureInfo.InvariantCulture); ;
target.Phonetic = item.Attribute("ph") != null && item.Attribute("ph").Value == "1" ? true : false; ;
if (item.Attribute("s") != null)
{
target.StyleID = int.Parse(item.Attribute("s").Value);
target.Style = this.Context.GlobalStyles.CellXfs[target.StyleID];
}
//r.StyleID = xr.GetAttribute("s") == null ? 0 : int.Parse(xr.GetAttribute("s"), CultureInfo.InvariantCulture);
}
/// <summary>
/// FileName:sheet1.xml
/// <para>NodePath:worksheet/sheetData</para>
/// </summary>
/// <param name="bookViewsRoot"></param>
internal void ReadWorkSheet_sheetData(WorkSheet target, XElement item)
{
foreach (XElement rowNode in item.Nodes())
{
int rowIndex = Convert.ToInt32(rowNode.Attribute("r").Value);
if (rowNode.Attributes().Count() > 2 || (rowNode.Attributes().Count() == 2 && rowNode.Attribute("spans") != null))
{
Row newRow = new Row(rowIndex);
ReadWorkSheet_sheetData_row(newRow, rowNode);
target.Rows.Add(newRow);
}
foreach (XElement cNode in rowNode.Nodes())
{
Cell newCell = ReadWorkSheet_sheetData_row_c(target, cNode);
foreach (XElement node in cNode.Nodes())
{
switch (node.Name.LocalName)
{
case "v":
ReadWorkSheet_sheetData_row_c_v(newCell, node);
break;
case "f":
ReadWorkSheet_sheetData_row_c_f(target, newCell, node);
break;
}
}
target.Cells.Add(newCell);
}
}
}