protected void exportDataSetByTemplateBtn_Click(object sender, EventArgs e)
{
XmlDocument xmlDoc = WebXmlDocumentCache.GetXmlDocument("Interco upload template.xml");
xmlDoc.PreserveWhitespace = true;
WorkbookNode workbook = new WorkbookNode();
workbook.LoadXml(xmlDoc.OuterXml);
DataTable table = CreateDemoDataTable();
DataViewExportOptions options = new DataViewExportOptions()
{
ExportColumnHeader = false
};
table.DefaultView.FillIntoSpreadSheet(workbook, "Sheet1", options);
Response.AppendHeader("CONTENT-DISPOSITION",
string.Format("{0};filename={1}", "inline", HttpUtility.UrlEncode("test.xml")));
Response.ContentType = "text/xml";
Response.Clear();
workbook.Save(Response.OutputStream);
Response.End();
}
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);
}
}
public void ExportToExcelXml(WorkbookNode workbook, WfMatrixDefinitionExportOptions options)
{
workbook.NullCheck("workbook");
options.NullCheck("options");
FillWorkbook(this, workbook, options);
}
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 void ExportMatrixData()
{
WfMatrix matrix = WfMatrixAdapter.Instance.Load(this.ExportKey);
matrix.Loaded = true;
switch (this.ExFormat)
{
case ExportFormat.Xlsx:
Response.Clear();
Response.ClearHeaders();
using (MemoryStream fileSteam = matrix.ExportToExcel2007(this.RoleAsPerson))
{
fileSteam.CopyTo(Response.OutputStream);
}
//Response.BinaryWrite(bytes);
//Response.ContentType = MediaTypeNames.Text.Xml;
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AppendHeader("CONTENT-DISPOSITION", "attachment;filename=" + matrix.ProcessKey + ".xlsx");
Response.Flush();
Response.End();
break;
case ExportFormat.Xml:
WorkbookNode workbook = matrix.ExportToExcelXml(this.RoleAsPerson);
workbook.Response(matrix.ProcessKey);
break;
}
}
public static void ExportToExcelXml(WorkbookNode workbook, SOARolePropertyDefinitionCollection definition, string propertySheetName)
{
workbook.NullCheck("workbook");
propertySheetName.NullCheck("propertySheetName");
FillWorkSheetTitle(workbook, definition, propertySheetName);
}
protected void generateMatrixButton_Click(object sender, EventArgs e)
{
WfMatrix matrix = CreateMatrix();
WorkbookNode workbook = matrix.ExportToExcelXml(false);
workbook.Response("Matrix");
}
private void ExportMatrixDefinition()
{
WfMatrixDefinition definition = WfMatrixDefinitionAdapter.Instance.Load(this.ExportKey);
WorkbookNode workbook = definition.ExportToExcelXml();
workbook.Response(definition.Key);
}
private void InitStyles()
{
StylesNode = XmlDocument.CreateElement(LocalNames.Styles, ROOT_NAMESPACE);
WorkbookNode.AppendChild(StylesNode);
AddStyle(StyleIds.ShortDate, "NumberFormat", "Format", "Short Date");
AddStyle(StyleIds.Header, "Font", "Bold", "1");
AddStyle(StyleIds.Paragraph, "Alignment", "WrapText", "1");
}
/// <summary>
/// 将矩阵导出成Xml格式
/// </summary>
/// <param name="roleAsPerson"></param>
/// <returns></returns>
public WorkbookNode ExportToExcelXml(bool roleAsPerson)
{
this.Definition.NullCheck("Matrix.Definition");
WorkbookNode workbook = this.Definition.ExportToExcelXml();
FillMatrixRowsToWorksheet(workbook, this, roleAsPerson);
return(workbook);
}
/// <summary>
/// 导出到Excel Xml中，非OpenXml
/// </summary>
/// <param name="coll"></param>
/// <param name="propertySheetName"></param>
/// <returns></returns>
public static WorkbookNode ExportToExcelXml(SOARolePropertyDefinitionCollection definition, string propertySheetName)
{
WorkbookNode workbook = new WorkbookNode();
string filePath = HttpContext.Current.Server.MapPath("RolePropertyTemplate.xml");
workbook.Load(filePath);
ExportToExcelXml(workbook, definition, propertySheetName);
return(workbook);
}
private static Dictionary<string, CellLocation> BuildNameColumnDictionary(WorkbookNode workbook)
{
Dictionary<string, CellLocation> result = new Dictionary<string, CellLocation>();
foreach (NamedRangeNode nr in workbook.Names)
{
result.Add(nr.Name, nr.ParseReferTo());
}
return result;
}
/// <summary>
/// 输出到Http的Response中。从UserSettings中读取文档类型和扩展名。
/// </summary>
/// <param name="workbook">WorkbookNode对象</param>
/// <param name="fileNameWithoutExt">不带扩展名的文件名</param>
public static void Response(this WorkbookNode workbook, string fileNameWithoutExt)
{
ExceptionHelper.FalseThrow <ArgumentNullException>(workbook != null, "workbook");
HttpResponse response = HttpContext.Current.Response;
response.AddExcelXmlHeader(fileNameWithoutExt);
response.Clear();
workbook.Save(response.OutputStream);
response.End();
}
private static void ImportExcelXml(Stream stream, WfMatrix matrix, Action notifier)
{
WorkbookNode workbook = new WorkbookNode();
workbook.Load(stream);
ExceptionHelper.FalseThrow(workbook.Worksheets.Contains("Matrix"),
"The workbook must contains a 'Matrix' worksheet.");
NamedLocationCollection fieldLocations = workbook.Names.ToLocations();
TableNode table = workbook.Worksheets["Matrix"].Table;
int baseRowIndex = GetStartRow(fieldLocations);
RowNode titleRow = table.GetRowByIndex(baseRowIndex);
int currentRowIndex = table.Rows.IndexOf(titleRow) + 1;
if (table.Rows.Count > currentRowIndex)
{
int currentVirtualRow = baseRowIndex;
int count = table.Rows.Count - currentRowIndex;
for (int i = 0; i < count; i++)
{
RowNode row = table.Rows[currentRowIndex];
if (row.Index > 0)
{
currentVirtualRow = row.Index;
}
else
{
currentVirtualRow++;
}
GenerateMatrixRow(matrix, row, fieldLocations, i);
if (notifier != null)
{
notifier();
}
currentRowIndex++;
}
}
WfMatrixAdapter.Instance.DeleteByProcessKey(matrix.ProcessKey);
WfMatrixAdapter.Instance.Update(matrix);
}
public WorkbookNode ExportToExcelXml()
{
WorkbookNode workbook = new WorkbookNode();
WfMatrixDefinitionExportOptions options = new WfMatrixDefinitionExportOptions();
options.StartRow = 3;
options.TitleCellStyleID = "s17";
workbook.Load(ResourceHelper.GetResourceStream(Assembly.GetExecutingAssembly(), this.GetType().Namespace + ".Matrix.MatrixTemplate.xml"));
ExportToExcelXml(workbook, options);
return(workbook);
}
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++;
}
}
protected void exportDataSetBtn_Click(object sender, EventArgs e)
{
DataTable table = CreateDemoDataTable();
DataViewExportOptions options = new DataViewExportOptions()
{
ExportColumnHeader = false
};
WorkbookNode workbook = table.DefaultView.ExportToSpreadSheet("Default", options);
Response.AppendHeader("CONTENT-DISPOSITION",
string.Format("{0};filename={1}", "inline", HttpUtility.UrlEncode("test.xml")));
Response.ContentType = "text/xml";
Response.Clear();
workbook.Save(Response.OutputStream);
Response.End();
}
private void ExportExcelXPXml()
{
SOARolePropertyDefinitionCollection definition = SOARolePropertyDefinitionAdapter.Instance.LoadByRoleID(ExportRoleProperty.DefinitionID);
string PropertySheetName = "Matrix";
WorkbookNode workbook = ExportToExcelXml(definition, PropertySheetName);
SOARole role = new SOARole(definition)
{
ID = RoleID
};
SOARolePropertyRowCollection rows = SOARolePropertiesAdapter.Instance.LoadByRole(role, definition);
if (rows.Count > 0)
{
FillMatrixRowsToWorksheet(workbook, rows);
}
workbook.Response(GetFileNameByRole(RoleID));
}
/// <summary>
/// Add a Worksheet to the end of the list of Worksheets, containing an empty Table.
/// </summary>
/// <param name="title">Worksheet title.</param>
/// <returns>The index of the worksheet/table.</returns>
public int AddWorksheet(string title)
{
XmlNode worksheetNode = XmlDocument.CreateElement("ss", LocalNames.Worksheet, PREFIX_NAMESPACEURI["ss"]);
WorkbookNode.AppendChild(worksheetNode);
XmlAttribute titleAttribute = XmlDocument.CreateAttribute("ss", Attributes.Name, PREFIX_NAMESPACEURI["ss"]);
titleAttribute.Value = title;
worksheetNode.Attributes.Append(titleAttribute);
XmlNode tableNode = XmlDocument.CreateElement(LocalNames.Table, ROOT_NAMESPACE);
worksheetNode.AppendChild(tableNode);
tableNodes.Add(tableNode);
XmlNode optionsNode = XmlDocument.CreateElement("x", LocalNames.WorksheetOptions, PREFIX_NAMESPACEURI["x"]);
worksheetNode.AppendChild(optionsNode);
return(tableNodes.Count - 1);
}
//导出带数据的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);
}
}
protected void generateButton_Click(object sender, EventArgs e)
{
WorkbookNode workbook = CreateMatrixDefinition().ExportToExcelXml();
workbook.Response("Matrix");
}
protected void uploadProgress_DoUploadProgress(HttpPostedFile file, UploadProgressResult result)
{
ExceptionHelper.FalseThrow(Path.GetExtension(file.FileName).ToLower() == ".xml",
"'{0}' must be a xml file.", file.FileName);
WorkbookNode workbook = new WorkbookNode();
workbook.Load(file.InputStream);
ExceptionHelper.FalseThrow(workbook.Worksheets.Contains("Matrix"),
"The workbook must contains a 'Matrix' worksheet.");
NamedLocationCollection fieldLocations = workbook.Names.ToLocations();
TableNode table = workbook.Worksheets["Matrix"].Table;
StringBuilder strB = new StringBuilder();
int baseRowIndex = GetStartRow(fieldLocations);
RowNode titleRow = table.GetRowByIndex(baseRowIndex);
int currentRowIndex = table.Rows.IndexOf(titleRow) + 1;
if (table.Rows.Count > currentRowIndex)
{
UploadProgressStatus status = new UploadProgressStatus();
status.CurrentStep = 1;
status.MinStep = 0;
status.MaxStep = table.Rows.Count - currentRowIndex;
int currentVirtualRow = baseRowIndex;
for (int i = status.MinStep; i < status.MaxStep; i++)
{
RowNode row = table.Rows[currentRowIndex];
if (row.Index > 0)
{
currentVirtualRow = row.Index;
}
else
{
currentVirtualRow++;
}
if (strB.Length > 0)
{
strB.Append("\n");
}
strB.AppendFormat("Processed={0}, Row={1}:", (i + 1), currentVirtualRow);
foreach (CellLocation location in fieldLocations)
{
CellNode cell = row.GetCellByIndex(location.Column);
strB.AppendFormat(";Name={0}, Value={1}", location.Name, cell != null ? cell.Data.Value : string.Empty);
}
status.CurrentStep = i;
status.Response();
currentRowIndex++;
}
status.CurrentStep = status.MaxStep;
status.Response();
}
result.DataChanged = true;
result.CloseWindow = false;
result.ProcessLog = strB.ToString();
}
protected void uploadProgress_DoUploadProgress(HttpPostedFile file, UploadProgressResult result)
{
string tag = uploadProgress.Tag;
const int baseRowIndex = 3;
ExceptionHelper.FalseThrow(Path.GetExtension(file.FileName).ToLower() == ".xml",
"'{0}' must be a xml file.", file.FileName);
WorkbookNode workbook = new WorkbookNode();
workbook.Load(file.InputStream);
ExceptionHelper.FalseThrow(workbook.Worksheets.Contains("PC Tracker Form"),
"The workbook must contains a 'PC Tracker Form' worksheet.");
Dictionary<string, CellLocation> fieldLocations = BuildNameColumnDictionary(workbook);
TableNode table = workbook.Worksheets["PC Tracker Form"].Table;
StringBuilder strB = new StringBuilder();
if (table.Rows.Count > 3)
{
int currentRowIndex = baseRowIndex;
UploadProgressStatus status = new UploadProgressStatus();
status.CurrentStep = 1;
status.MinStep = 0;
status.MaxStep = table.Rows.Count - currentRowIndex;
int currentVirtualRow = baseRowIndex;
for (int i = status.MinStep; i < status.MaxStep; i++)
{
currentRowIndex = baseRowIndex + i;
RowNode row = table.Rows[currentRowIndex];
if (row.Index > 0)
currentVirtualRow = row.Index;
else
currentVirtualRow++;
if (strB.Length > 0)
strB.Append("\n");
strB.AppendFormat("Processed={0}, Row={1}:", (i + 1), currentVirtualRow);
foreach (KeyValuePair<string, CellLocation> kp in fieldLocations)
{
CellNode cell = row.GetCellByIndex(kp.Value.Column);
strB.AppendFormat(";Name={0}, Value={1}", kp.Key, cell != null ? cell.Data.Value : string.Empty);
}
status.CurrentStep = i;
status.Response();
int ms = 1000;
if (Request.Form["longProgress"] == "true")
ms = 2000;
Thread.Sleep(ms); //假装等待
}
status.CurrentStep = status.MaxStep;
status.Response();
}
result.DataChanged = true;
result.CloseWindow = false;
result.ProcessLog = strB.ToString();
}
protected void uploadProgress_DoUploadProgress(HttpPostedFile file, UploadProgressResult result)
{
ExceptionHelper.FalseThrow(Path.GetExtension(file.FileName).ToLower() == ".xml", "'{0}'权限矩阵必须是xml电子表格", file.FileName);
WorkbookNode workbook = new WorkbookNode();
workbook.Load(file.InputStream);
//矩阵在文件第1个sheet，sheet名称为矩阵引用的定义名称
TableNode table = workbook.Worksheets[0].Table;
var matrixDef = WfMatrixDefinitionAdapter.Instance.Load(workbook.Worksheets[0].Name);
WfMatrix matrix = new WfMatrix(matrixDef)
{
MatrixID = Guid.NewGuid().ToString()
};
if (table.Rows.Count <= 1)
{
PrepareResultInfo(result, "没有数据或格式不正确");
return;
}
UploadProgressStatus status = new UploadProgressStatus();
status.CurrentStep = 1;
status.MinStep = 0;
status.MaxStep = table.Rows.Count;
for (int i = status.CurrentStep; i < status.MaxStep; i++)
{
var newRow = new WfMatrixRow()
{
RowNumber = i
};
for (int j = 0; j < table.Rows[i].Cells.Count; j++)
{
if (table.Rows[0].Cells[j].Data.InnerText == "操作人")
{
newRow.Operator = table.Rows[i].Cells[j].Data.InnerText;
continue;
}
var newCell = new WfMatrixCell(matrixDef.Dimensions[table.Rows[0].Cells[j].Data.InnerText])
{
StringValue = table.Rows[i].Cells[j].Data.InnerText
};
newRow.Cells.Add(newCell);
}
matrix.Rows.Add(newRow);
status.CurrentStep = i;
status.Response();
Thread.Sleep(100);
}
status.CurrentStep = status.MaxStep;
status.Response();
WfMatrixAdapter.Instance.Update(matrix);
string logInfo = string.Format("导入成功，权限矩阵名称：{0}{1}共导入{2}行数据.", matrix.MatrixID, Environment.NewLine, table.Rows.Count - 1);
PrepareResultInfo(result, logInfo);
}
/// <summary>
/// 导入Excel Xml格式的文件
/// </summary>
/// <param name="stream"></param>
private void ImportFromXml(Stream stream)
{
WorkbookNode workbook = new WorkbookNode();
workbook.Load(stream);
ExceptionHelper.FalseThrow(workbook.Worksheets.Contains("Matrix"),
"The workbook must contains a 'Matrix' worksheet.");
SOARole role = null;
ServiceBrokerContext.Current.SaveContextStates();
try
{
if (this.AppCodeName.IsNotEmpty() && this.RoleCodeName.IsNotEmpty())
{
role = new SOARole(this.AppCodeName + ":" + this.RoleCodeName);
}
else
{
role = new SOARole(this.Definition)
{
ID = RoleID
}
};
role.Rows.Clear();
NamedLocationCollection fieldLocations = workbook.Names.ToLocations();
TableNode table = workbook.Worksheets["Matrix"].Table;
int baseRowIndex = GetStartRow(fieldLocations);
RowNode titleRow = table.GetRowByIndex(baseRowIndex);
int currentRowIndex = table.Rows.IndexOf(titleRow) + 1;
if (table.Rows.Count > currentRowIndex)
{
UploadProgressStatus status = new UploadProgressStatus();
status.CurrentStep = 1;
status.MinStep = 0;
status.MaxStep = table.Rows.Count - currentRowIndex;
int currentVirtualRow = baseRowIndex;
for (int i = status.MinStep; i < status.MaxStep; i++)
{
RowNode row = table.Rows[currentRowIndex];
if (row.Index > 0)
{
currentVirtualRow = row.Index;
}
else
{
currentVirtualRow++;
}
GenerateMatrixRow(role, row, fieldLocations, i);
status.CurrentStep = i;
status.Response();
currentRowIndex++;
}
status.CurrentStep = status.MaxStep;
status.Response();
}
//插入记录
SOARolePropertiesAdapter.Instance.Update(role);
}
finally
{
ServiceBrokerContext.Current.RestoreSavedStates();
}
}
