/// <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);
}
public WorkBook ClientDynamicProcessToExcel(WfClientProcessDescriptor client)
{
bool isDynamic = client.Variables.GetValue("ClientDynamicProcess", false);
ExceptionHelper.TrueThrow <ArgumentException>(isDynamic == false,
Translator.Translate(Define.DefaultCulture, "非动态活动流程不能导出{0}", client.Key));
WorkBook wb = WorkBook.CreateNew();
wb.Sheets.Clear();
WorkSheet workSheet = null;
Row titleRow = null;
#region 流程定义信息
workSheet = new WorkSheet(wb, "Process");
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 = "流程属性";
CreateCommonHeaderRow(client, workSheet);
FillCommonSheetData(client, workSheet);
wb.Sheets.Add(workSheet);
#endregion
#region 矩阵信息
WfClientActivityDescriptor activity = client.Activities.Find(w => w.Resources.Count > 0 && w.Resources[0] is WfClientActivityMatrixResourceDescriptor);
if (activity == null)
{
return(wb);
}
WfClientActivityMatrixResourceDescriptor matrix = activity.Resources[0] as WfClientActivityMatrixResourceDescriptor;
workSheet = new WorkSheet(wb, "Matrix");
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 = "角色属性";
CreateMatrixHeaderRow(matrix, workSheet);
FillMatrixSheetData(matrix, workSheet);
wb.Sheets.Add(workSheet);
#endregion
return(wb);
}
/// <summary>
/// 矩阵转换为Excel的WorkBook
/// </summary>
/// <param name="matrix"></param>
/// <returns></returns>
public static WorkBook ToWorkBook(this IWfMatrixContainer matrix)
{
WorkBook workBook = WorkBook.CreateNew();
workBook.Sheets.Clear();
WorkSheet sheet = matrix.ToWorkSheet(workBook);
workBook.Sheets.Add(sheet);
return(workBook);
}
public void WriteWorkBook()
{
WorkBook wb = WorkBook.CreateNew();
wb.Sheets["sheet1"].Name = "FirstSheet";
wb.Save("WriteWorkBook.xlsx");
WorkBook loaded = WorkBook.Load("WriteWorkBook.xlsx");
Assert.IsNotNull(loaded.Sheets["FirstSheet"]);
}
private void ResponseExcelWorkBook(WfActivityMatrixResourceDescriptor activityMatrix, string fileName)
{
WorkBook workBook = WorkBook.CreateNew();
workBook.Sheets.Clear();
workBook.FillActivityMatrixResourceDescriptor(activityMatrix);
Response.AppendExcelOpenXmlHeader(fileName);
workBook.Save(Response.OutputStream);
Response.End();
}
public void WriteBookWithAttributesToCells()
{
WorkBook wb = WorkBook.CreateNew();
WorkSheet sheet = wb.Sheets["sheet1"];
sheet.Name = "FirstSheet";
IEnumerable <BookWithAttributes> books = FillBookWithAttributesData(10240);
sheet.LoadFromCollection(books);
wb.Save("WriteBookWithAttributesToCells.xlsx");
}
public void WriteDataTableToCells()
{
WorkBook wb = WorkBook.CreateNew();
WorkSheet sheet = wb.Sheets["sheet1"];
sheet.Name = "FirstSheet";
DataTable table = PrepareDataTable();
FillDataTableData(table, 10240);
sheet.LoadFromDataView(table.DefaultView);
wb.Save("WriteDateTableToCells.xlsx");
CheckColumnDataType("WriteDateTableToCells.xlsx", table);
}
public void WriteCellsToWorkBook()
{
WorkBook wb = WorkBook.CreateNew();
WorkSheet sheet = wb.Sheets["sheet1"];
sheet.Name = "FirstSheet";
for (int r = 1; r <= 2000; r++)
{
for (int c = 1; c <= 50; c++)
{
sheet.Cells[r, c].Value = CellAddress.Parse(c, r).ToString();
}
}
wb.Save("WriteCellsToWorkBook.xlsx");
}
public void WriteBookWithoutAttributesToCells()
{
WorkBook wb = WorkBook.CreateNew();
WorkSheet sheet = wb.Sheets["sheet1"];
sheet.Name = "FirstSheet";
IEnumerable <BookWithoutAttributes> books = FillBookWithoutAttributesData(10240);
TableDescription tableDesp = new TableDescription("Books");
tableDesp.AllColumns.Add(new TableColumnDescription(new DataColumn("书名", typeof(string)))
{
PropertyName = "Name"
});
tableDesp.AllColumns.Add(new TableColumnDescription(new DataColumn("价格", typeof(double)))
{
PropertyName = "Price"
});
tableDesp.AllColumns.Add(new TableColumnDescription(new DataColumn("发行日期", typeof(DateTime)))
{
PropertyName = "IssueDate"
});
sheet.LoadFromCollection(books, tableDesp, (cell, dcp) =>
{
cell.Value = dcp.PropertyValue;
if (dcp.ColumnName == "发行日期")
{
cell.Value = string.Format("{0:yyyy-MM-dd HH:mm}", dcp.PropertyValue);
}
});
wb.Save("WriteBookWithoutAttributesToCells.xlsx");
}
public void WriteSimpleValidationRangeToWorkBook()
{
WorkBook wb = WorkBook.CreateNew();
WorkSheet sheet = wb.Sheets["sheet1"];
sheet.Name = "FirstSheet";
for (int r = 1; r <= 2000; r++)
{
for (int c = 1; c <= 50; c++)
{
sheet.Cells[r, c].Value = CellAddress.Parse(c, r).ToString();
}
}
IDataValidationList validationList = sheet.Validations.AddListValidation("A$1:A$10");
validationList.Formula.Values.Add("简单");
validationList.Formula.Values.Add("标准");
validationList.Formula.Values.Add("复杂");
wb.Save("WriteValidationRangeToWorkBook.xlsx");
}
/// <summary>
/// 将矩阵导出成OpenXml
/// </summary>
/// <param name="roleAsPerson"></param>
/// <returns></returns>
public MemoryStream ExportToExcel2007(bool roleAsPerson)
{
this.Definition.NullCheck("Matrix.Definition");
WorkBook wb = WorkBook.CreateNew();
WorkSheet ws = wb.Sheets["sheet1"];
ws.Name = "Matrix";
//new WorkSheet(wb, "Matrix");
//wb.Sheets.Clear();
//wb.Sheets.Add(ws);
Row titleRow = new Row(1)
{
Height = 30d
};
titleRow.Style.Fill.SetBackgroundColor(Color.LightGray, ExcelFillStyle.Solid);
titleRow.Style.Font.Size = 20;
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(titleRow);
ws.Rows.Add(headRow);
ws.Cells[titleRow.Index, 1].Value = "授权矩阵";
int columnIndex = 1;
foreach (var dimension in this.Definition.Dimensions)
{
ws.Cells[headRow.Index, columnIndex].Value = dimension.Name;
ws.Names.Add(CellAddress.Parse(columnIndex, headRow.Index).ToString(), dimension.DimensionKey);
columnIndex++;
}
int rowIndex = 4;
foreach (var row in this.Rows)
{
foreach (var cell in row.Cells)
{
var cIndex = ws.Names[cell.Definition.DimensionKey].Address.StartColumn;
ws.Cells[rowIndex, cIndex].Value = GetCellValue(roleAsPerson, cell, row);
}
rowIndex++;
}
MemoryStream exdelStream = new MemoryStream();
wb.Save(exdelStream);
//exdelStream.Seek(0, SeekOrigin.Begin);
return(exdelStream);
}
