private WorksheetPart wsSheet = null; // WorkSheetPart
#endregion Fields
#region Constructors
public LogGenerator(string fn)
{
//// get spreadsheet path from constructor
//path = folder;
//// File name is based on date and time
//DateTime now = DateTime.Now;
//// Construct the spreadsheet filename
//string fn = string.Format("{0}\\report_{1}-{2}-{3}_{4}{5}{6}.xlsx",
// path, now.Year, now.Month, now.Day, now.Hour, now.Minute, now.Second);
if (File.Exists(fn))
{
doc = SpreadsheetDocument.Open(fn, true);
}
else
{
//
// Create the Spreadsheet document
//
doc = SpreadsheetDocument.Create(fn, SpreadsheetDocumentType.Workbook);
//
// Add WoorkBookPart to the document
//
wbPart = doc.AddWorkbookPart();
wbPart.Workbook = new Workbook();
wbPart.Workbook.AddNamespaceDeclaration("x", "http://schemas.openxmlformats.org/spreadsheetml/2006/main");
//
// Add WorkSheetPart to the WorkBookPart
//
wsSheet = wbPart.AddNewPart<WorksheetPart>();
wsSheet.Worksheet = new Worksheet(new SheetData());
wsSheet.Worksheet.AddNamespaceDeclaration("x", "http://schemas.openxmlformats.org/spreadsheetml/2006/main");
//
// Add sheets to the WorkBook
//
sheets = doc.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());
//
// Append new sheet and associate it with the workbook
//
sheet = new Sheet() { Id = doc.WorkbookPart.GetIdOfPart(wsSheet), SheetId = 1, Name = wsName };
sheet.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
sheets.Append(sheet);
wbPart.Workbook.Save();
//CreatePackage(fn);
//if (File.Exists(fn))
//{
// TestXls();
//}
}
}
public override void Save(ResearchResult result)
{
if (!Directory.Exists(storageStr))
{
Directory.CreateDirectory(storageStr);
}
string fileName = storageStr + result.ResearchName;
if (File.Exists(fileName + ".xls") || File.Exists(fileName + ".xlsx"))
fileName += result.ResearchID;
document = SpreadsheetDocument.Create(fileName + ".xlsx", SpreadsheetDocumentType.Workbook);
workbook = document.AddWorkbookPart();
workbook.Workbook = new Workbook();
// Add a WorksheetPart to the WorkbookPart.
WorksheetPart worksheetPart = workbook.AddNewPart<WorksheetPart>();
worksheetPart.Worksheet = new Worksheet(new SheetData());
// Add Sheets to the Workbook.
Sheets sheets = document.WorkbookPart.Workbook.
AppendChild<Sheets>(new Sheets());
// Append a new worksheet and associate it with the workbook.
Sheet sheet = new Sheet()
{
Id = document.WorkbookPart.
GetIdOfPart(worksheetPart),
SheetId = 1,
Name = "mySheet"
};
sheets.Append(sheet);
/*SaveResearchInfo(result.ResearchID, result.ResearchName,
result.ResearchType, result.ModelType, result.RealizationCount,
result.Size, result.Edges);
SaveResearchParameters(result.ResearchParameterValues);
SaveGenerationParameters(result.GenerationParameterValues);
for (int i = 0; i < result.EnsembleResults.Count; ++i)
{
SaveEnsembleResult(result.EnsembleResults[i], i);
}*/
workbook.Workbook.Save();
document.Close();
}
/// <summary>
/// Creates the sheet
/// </summary>
/// <param name="sheets">The sheet for this item</param>
/// <param name="spreadsheetDocument">The spreadsheet containing the sheets</param>
/// <param name="workbookPart">The workbookpart associated with the spreadsheet</param>
public void CreateSheet(Sheets sheets, SpreadsheetDocument spreadsheetDocument, WorkbookPart workbookPart)
{
// Add a WorksheetPart to the WorkbookPart.
WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
worksheetPart.Worksheet = new Worksheet(new SheetData());
Sheet sheet = new Sheet
{
Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart),
SheetId = 3U, // <-- Change for each sheet that is created
Name = _groupOrder.SheetName
};
sheets.Append(sheet);
//Add cells to the sheet
InsertTextIntoCells(spreadsheetDocument, worksheetPart);
}
public void CreateSheet(Sheets sheets, SpreadsheetDocument spreadsheetDocument, WorkbookPart workbookPart)
{
// Add a WorksheetPart to the WorkbookPart.
WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
worksheetPart.Worksheet = new Worksheet(new SheetData());
Sheet sheet = new Sheet
{
Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart),
SheetId = 4U,
Name = _resources.SheetName
};
sheets.Append(sheet);
//Add cells to the sheet
InsertTextIntoCells(spreadsheetDocument, worksheetPart);
}
/// <summary>
/// Adds a given worksheet to the document
/// </summary>
/// <param name="worksheet">Worksheet document to add</param>
/// <returns>Worksheet part just added</returns>
public OpenXmlSDK.WorksheetPart Add(XDocument worksheet)
{
// Associates base content to a new worksheet part
OpenXmlSDK.WorkbookPart workbook = ((OpenXmlSDK.SpreadsheetDocument)parentDocument.Document).WorkbookPart;
OpenXmlSDK.WorksheetPart worksheetPart = workbook.AddNewPart <OpenXmlSDK.WorksheetPart>();
XDocument worksheetDocument = parentDocument.GetXDocument(worksheetPart);
if (worksheetDocument.Root == null)
{
worksheetDocument.Add(
new XElement("root")
);
}
worksheetDocument.Root.ReplaceWith(worksheet.Root);
// Associates the worksheet part to the workbook part
XDocument document = parentDocument.GetXDocument(((OpenXmlSDK.SpreadsheetDocument)parentDocument.Document).WorkbookPart);
int sheetId =
document.Root
.Element(ns + "sheets")
.Elements(ns + "sheet")
.Count() + 1;
int worksheetCount =
document.Root
.Element(ns + "sheets")
.Elements(ns + "sheet")
.Where(
t =>
t.Attribute("name").Value.StartsWith("sheet", StringComparison.OrdinalIgnoreCase)
)
.Count() + 1;
// Adds content to workbook document to reference worksheet document
document.Root
.Element(ns + "sheets")
.Add(
new XElement(ns + "sheet",
new XAttribute("name", string.Format("sheet{0}", worksheetCount)),
new XAttribute("sheetId", sheetId),
new XAttribute(relationshipsns + "id", workbook.GetIdOfPart(worksheetPart))
)
);
return(worksheetPart);
}
public Worksheet AddSheet(WorkbookPart workbookPart, string name)
{
var sheets = workbookPart.Workbook.GetFirstChild<Sheets>();
// add single Sheet
var workSheetpart = workbookPart.AddNewPart<WorksheetPart>();
workSheetpart.Worksheet = new Worksheet(new SheetData());
workSheetpart.Worksheet.Save();
var sheet = new Sheet
{
Id = workbookPart.GetIdOfPart(workSheetpart),
SheetId = (uint)(workbookPart.Workbook.Sheets.Count() + 1),
Name = name
};
sheets.Append(sheet);
workbookPart.Workbook.Save();
return workSheetpart.Worksheet;
}
/*
* static Excel.SharedStringTable SaveSharedStringTable()
* {
* var exportedSharedStringTable = new Excel.SharedStringTable();
*
* return exportedSharedStringTable;
* }
*/
static void SaveSheet(Package.WorkbookPart exportedWorkbookPart, Excel.Stylesheet styleSheet, Dictionary <CellFormat, uint> cellFormatList, Excel.Sheets exportedSheets, Sheet sheet, uint sheetId)
{
var exportedWorksheetPart = exportedWorkbookPart.AddNewPart <Package.WorksheetPart>();
string relId = exportedWorkbookPart.GetIdOfPart(exportedWorksheetPart);
var exportedWorksheet = new Excel.Worksheet();
exportedWorksheetPart.Worksheet = exportedWorksheet;
var exportedColumns = new Excel.Columns();
exportedWorksheet.Append(exportedColumns);
var exportedSheetData = new Excel.SheetData();
exportedWorksheet.Append(exportedSheetData);
var exportedSheet = new Excel.Sheet()
{
Name = sheet.Name, Id = relId, SheetId = sheetId
};
if (sheet.Hidden)
{
exportedSheet.State = Excel.SheetStateValues.Hidden;
}
exportedSheets.Append(exportedSheet);
foreach (var column in sheet.Columns.OrderBy(r => r.Index))
{
SaveColumn(exportedColumns, column);
}
foreach (var row in sheet.Rows.OrderBy(r => r.Index))
{
SaveRow(exportedSheetData, styleSheet, cellFormatList, row);
}
exportedWorksheetPart.Worksheet.Save();
}
private static WorksheetPart InsertWorksheet(WorkbookPart workbookPart, string sheetName)
{
Sheet foundSheet = null;
foreach (Sheet sheet in workbookPart.Workbook.Sheets)
{
if (sheet.Name == sheetName)
{
foundSheet = sheet;
break;
}
}
if (foundSheet == null)
{
// Add a new worksheet part to the workbook.
WorksheetPart newWorksheetPart = workbookPart.AddNewPart<WorksheetPart>();
newWorksheetPart.Worksheet = new Worksheet(new SheetData());
newWorksheetPart.Worksheet.Save();
Sheets sheets = workbookPart.Workbook.GetFirstChild<Sheets>();
string relationshipId = workbookPart.GetIdOfPart(newWorksheetPart);
// Get a unique ID for the new sheet.
uint sheetId = 1;
if (sheets.Elements<Sheet>().Count() > 0)
{
sheetId = sheets.Elements<Sheet>().Select(s => s.SheetId.Value).Max() + 1;
}
// Append the new worksheet and associate it with the workbook.
Sheet sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = sheetName };
sheets.Append(sheet);
workbookPart.Workbook.Save();
return newWorksheetPart;
}
else
{
return (WorksheetPart)workbookPart.GetPartById(foundSheet.Id);
}
}
// Given the main workbook part, and a text value, insert the text into
// the shared string table. Create the table if necessary. If the value
// already exists, return its index. If it doesn't exist, insert it and
// return its new index.
internal static int InsertSharedStringItem(WorkbookPart wbPart, string value)
{
int index = 0;
bool found = false;
var stringTablePart = wbPart
.GetPartsOfType<SharedStringTablePart>().FirstOrDefault();
// If the shared string table is missing, something's wrong.
// Just return the index that you found in the cell.
// Otherwise, look up the correct text in the table.
if (stringTablePart == null)
{
// Create it.
stringTablePart = wbPart.AddNewPart<SharedStringTablePart>();
}
var stringTable = stringTablePart.SharedStringTable;
if (stringTable == null)
{
stringTable = new SharedStringTable();
stringTablePart.SharedStringTable = stringTable;
}
// Iterate through all the items in the SharedStringTable.
// If the text already exists, return its index.
foreach (SharedStringItem item in stringTable.Elements<SharedStringItem>())
{
if (item.InnerText == value)
{
found = true;
break;
}
index += 1;
}
if (!found)
{
stringTable.AppendChild(new SharedStringItem(new DocumentFormat.OpenXml.Spreadsheet.Text(value)));
stringTable.Save();
}
return index;
}
//private static void AddTextToWorkSheet(string path, string sheetName, string text, string columnName, uint rowIndex)
//{
// // Open the document for editing.
// using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(path, true))
// {
// // Get the SharedStringTablePart. If it does not exist, create a new one.
// SharedStringTablePart shareStringPart;
// if (spreadSheet.WorkbookPart.GetPartsOfType<SharedStringTablePart>().Count() > 0)
// {
// shareStringPart = spreadSheet.WorkbookPart.GetPartsOfType<SharedStringTablePart>().First();
// }
// else
// {
// shareStringPart = spreadSheet.WorkbookPart.AddNewPart<SharedStringTablePart>();
// }
// // Insert the text into the SharedStringTablePart.
// int index = InsertSharedStringItem(spreadSheet.WorkbookPart, text);
// // Insert a new worksheet.
// WorksheetPart worksheetPart = InsertWorksheet(spreadSheet.WorkbookPart, sheetName);
// // Insert cell A1 into the new worksheet.
// Cell cell = InsertCellInWorksheet(worksheetPart, columnName, rowIndex);
// // Set the value of cell A1.
// cell.CellValue = new CellValue(index.ToString());
// cell.DataType = new EnumValue<CellValues>(CellValues.SharedString);
// // Save the new worksheet.
// worksheetPart.Worksheet.Save();
// }
//}
public static WorksheetPart InsertWorksheet(WorkbookPart workbookPart, string sheetName)
{
Sheet foundSheet = workbookPart.Workbook.Sheets.Cast<Sheet>().FirstOrDefault(sheet => sheet.Name == sheetName);
if (foundSheet == null)
{
// Add a new worksheet part to the workbook.
var newWorksheetPart = workbookPart.AddNewPart<WorksheetPart>();
newWorksheetPart.Worksheet = new Worksheet(new SheetData());
newWorksheetPart.Worksheet.Save();
var sheets = workbookPart.Workbook.GetFirstChild<Sheets>();
string relationshipId = workbookPart.GetIdOfPart(newWorksheetPart);
// Get a unique ID for the new sheet.
uint sheetId = 1;
if (sheets.Elements<Sheet>().Count() > 0)
{
sheetId = sheets.Elements<Sheet>().Select(s => s.SheetId.Value).Max() + 1;
}
// Append the new worksheet and associate it with the workbook.
var sheet = new Sheet { Id = relationshipId, SheetId = sheetId, Name = sheetName };
sheets.Append((IEnumerable<OpenXmlElement>)sheet);
return newWorksheetPart;
}
return (WorksheetPart)workbookPart.GetPartById(foundSheet.Id);
}
//
// Insert the string into the shared string table of the WorkBookPart
//
private int insertSharedStringItem(WorkbookPart wbPart, string Value)
{
//
// Look if the string already exists into the SharedStringTable, if it does return it
// else return a new one
//
int index = 0;
bool found = false;
//
// If the shared string table is missing, something's wrong.
// Just return the index that you found in the cell.
// Otherwise, look up the correct text in the table.
//
var stringTablePart = wbPart.GetPartsOfType<SharedStringTablePart>().FirstOrDefault();
if (stringTablePart == null)
{
// Create a new SharedString
stringTablePart = wbPart.AddNewPart<SharedStringTablePart>();
}
var stringTable = stringTablePart.SharedStringTable;
if (stringTable == null)
{
stringTable = new SharedStringTable();
}
//
// Iterate through all the items in the SharedStringTable.
// If the text already exists, return its index.
//
foreach (SharedStringItem item in stringTable.Elements<SharedStringItem>())
{
if (item.InnerText == Value)
{
found = true;
break;
}
index++;
}
//
// if not found append a new SharedString to the table
//
if (!found)
{
stringTable.AppendChild(new SharedStringItem(new Text(Value)));
try
{
stringTable.Save();
}
catch (Exception) { }
}
return index;
}
public void LoadData(string sheetName, uint sheetId, DataTable dt)
{
DocumentFormat.OpenXml.Packaging.WorksheetPart wsPart = null;
DocumentFormat.OpenXml.Spreadsheet.SheetData sheetData = null;
DocumentFormat.OpenXml.Spreadsheet.Sheets sheets = null;
DocumentFormat.OpenXml.Spreadsheet.Sheet sheet = null;
uint rowIndex = 1;
uint colIndex = 1;
string collName = string.Empty;
Row row = null;
Cell cell = null;
sheetData = new SheetData();
// Header Row
row = new Row()
{
RowIndex = rowIndex
};
sheetData.Append(row);
// Header Row cellls
foreach (DataColumn dc in dt.Columns)
{
collName = ExcelHelper.ConvertColumnNumberToName((int)colIndex);
cell = new Cell()
{
CellReference = collName + rowIndex
};
row.AppendChild(cell);
Text txt = new Text();
txt.Text = dc.ColumnName;
InlineString inStr = new InlineString();
inStr.AppendChild(txt);
cell.DataType = CellValues.InlineString;
cell.AppendChild(inStr);
colIndex++;
}
rowIndex++;
foreach (DataRow dr in dt.Rows)
{
row = new Row()
{
RowIndex = rowIndex
};
sheetData.Append(row);
colIndex = 1;
foreach (DataColumn dc in dt.Columns)
{
collName = ExcelHelper.ConvertColumnNumberToName((int)colIndex);
cell = new Cell()
{
CellReference = collName + rowIndex
};
row.AppendChild(cell);
Text txt = new Text();
txt.Text = dr[dc.ColumnName].ToString();
InlineString inStr = new InlineString();
inStr.AppendChild(txt);
cell.DataType = CellValues.InlineString;
cell.AppendChild(inStr);
colIndex++;
}
rowIndex++;
}
wsPart = wbPart.AddNewPart <WorksheetPart>();
wsPart.Worksheet = new Worksheet(sheetData);
sheet = new Sheet();
sheet.Id = wbPart.GetIdOfPart(wsPart);
sheet.SheetId = sheetId;
sheet.Name = sheetName;
sheets = wbPart.Workbook.AppendChild(new Sheets());
sheets.Append(sheet);
}
/// <summary>
/// Generate service order report
/// </summary>
/// <param name="itemsource">Item source</param>
/// <param name="workbookPart">Worbook part</param>
private static void GenerateServiceOrder(ExportInspectionReportsModel itemsource, WorkbookPart workbookPart, Sheets sheets,int sheetId,string logoPath)
{
if (itemsource.IsSelectedServiceOrder)
{
// Remove the sheet reference from the workbook.
WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
// The SheetData object will contain all the data.
SheetData sheetData = new SheetData();
Worksheet worksheet = new Worksheet();
Form serviceOrder = itemsource.ServiceOrderData;
Row rowTitle;
//get the string name of the columns
string[] excelColumnNamesTitle = new string[9];
for (int n = 0; n < 9; n++)
excelColumnNamesTitle[n] = GetExcelColumnName(n);
//build the title
for (int i = 1; i <= 6; i++)
{
rowTitle = new Row() { RowIndex = (UInt32Value)(uint)i };
for (int cellval = 0; cellval < 9; cellval++)
{
AppendTextCell(excelColumnNamesTitle[cellval] + i, string.Empty, rowTitle, 3);
}
sheetData.Append(rowTitle);
}
MergeCells mergeCells = new MergeCells();
Row currentRowTitle = sheetData.Elements<Row>().FirstOrDefault(row => row.RowIndex.Value == (uint)2);
//add the business application name
UpdateStringCellValue("B2", itemsource.BusinessApplicationName, currentRowTitle, 5);
//merge all cells in the title
MergeCell mergeCell = new MergeCell();
mergeCell.Reference = "B2:E2";
mergeCells.Append(mergeCell);
currentRowTitle = sheetData.Elements<Row>().FirstOrDefault(row => row.RowIndex.Value == (uint)4);
//add the form name
UpdateStringCellValue("B4", itemsource.ServiceOrderSheetName, currentRowTitle, 5);
//merge all cell in the form name
mergeCell = new MergeCell();
mergeCell.Reference = "B4:E4";
mergeCells.Append(mergeCell);
Drawing drawing = AddLogo(logoPath, worksheetPart);
Columns columns = new Columns();
columns.Append(CreateColumnData((UInt32Value)(uint)1, (UInt32Value)(uint)1, 26));
columns.Append(CreateColumnData((UInt32Value)(uint)2, (UInt32Value)(uint)2, 73));
worksheet.Append(columns);
int rowIndex = 8;
Row sectionRow;
foreach (var section in serviceOrder.Sections)
{
sectionRow = new Row() { RowIndex = (UInt32Value)(uint)rowIndex };
mergeCell = new MergeCell();
mergeCell.Reference = "A" + rowIndex + ":B" + rowIndex;
mergeCells.Append(mergeCell);
AppendTextCell("A" + rowIndex, section.Caption, sectionRow, 6);
AppendTextCell("B" + rowIndex, string.Empty, sectionRow, 6);
sheetData.Append(sectionRow);
foreach (var element in section.FormElements)
{
rowIndex++;
//The current row is obtained for updating the value of the cell
Row rowData = new Row() { RowIndex = (UInt32Value)(uint)rowIndex };
switch (element.Field.FieldType)
{
case FieldType.Catalogue:
AppendTextCell("A" + rowIndex.ToString(), element.Field.Caption, rowData, 1);
if (!string.IsNullOrEmpty(element.Field.FieldValue))
{
string catalogueValue = CatalogueBusiness.GetCatalogueValue(new Guid(element.Field.FieldValue)).CatalogueValueData;
AppendTextCell("B" + rowIndex.ToString(), catalogueValue, rowData, 1);
}
else
{
AppendTextCell("B" + rowIndex.ToString(), string.Empty, rowData, 1);
}
break;
case FieldType.RegularExpressionText:
case FieldType.Time:
case FieldType.SingleTextLine:
case FieldType.MultipleTextLine:
case FieldType.Datepicker:
AppendTextCell("A" + rowIndex.ToString(), element.Field.Caption, rowData, 1);
AppendTextCell("B" + rowIndex.ToString(), element.Field.FieldValue, rowData, 1);
break;
case FieldType.Boolean:
string boolValue = element.Field.FieldValue == "True" ? LanguageResource.Yes : LanguageResource.No;
AppendTextCell("A" + rowIndex.ToString(), element.Field.Caption, rowData, 1);
AppendTextCell("B" + rowIndex.ToString(), boolValue, rowData, 1);
break;
case FieldType.Integer:
case FieldType.Decimal:
AppendTextCell("A" + rowIndex.ToString(), element.Field.Caption, rowData, 1);
AppendNumberCell("B" + rowIndex.ToString(), element.Field.FieldValue, rowData, 1);
break;
default:
break;
}
sheetData.Append(rowData);
}
rowIndex+=2;
}
worksheet.Append(sheetData);
//add merged cells
worksheet.InsertAfter(mergeCells, worksheet.Elements<SheetData>().First());
worksheet.Append(drawing);
worksheetPart.Worksheet = worksheet;
worksheetPart.Worksheet.Save();
//create the new sheet for this report
Sheet sheet = new Sheet() { Name = itemsource.ServiceOrderSheetName, SheetId = (UInt32Value)(uint)sheetId, Id = workbookPart.GetIdOfPart(worksheetPart) };
sheets.Append(sheet);
sheetId++;
}
}
private void GenerateCalculationChainPartContent(WorkbookPart workbookPart, SaveContext context)
{
var thisRelId = context.RelIdGenerator.GetNext(RelType.Workbook);
if (workbookPart.CalculationChainPart == null)
workbookPart.AddNewPart<CalculationChainPart>(thisRelId);
if (workbookPart.CalculationChainPart.CalculationChain == null)
workbookPart.CalculationChainPart.CalculationChain = new CalculationChain();
var calculationChain = workbookPart.CalculationChainPart.CalculationChain;
calculationChain.RemoveAllChildren<CalculationCell>();
foreach (var worksheet in WorksheetsInternal)
{
var cellsWithoutFormulas = new HashSet<String>();
foreach (var c in worksheet.Internals.CellsCollection.GetCells())
{
if (XLHelper.IsNullOrWhiteSpace(c.FormulaA1))
cellsWithoutFormulas.Add(c.Address.ToStringRelative());
else
{
if (c.FormulaA1.StartsWith("{"))
{
var cc = new CalculationCell
{
CellReference = c.Address.ToString(),
SheetId = worksheet.SheetId
};
if (c.FormulaReference.FirstAddress.Equals(c.Address))
{
cc.Array = true;
calculationChain.AppendChild(cc);
calculationChain.AppendChild(new CalculationCell {CellReference = c.Address.ToString(), InChildChain = true});
}
else
{
calculationChain.AppendChild(cc);
}
}
else
{
calculationChain.AppendChild(new CalculationCell
{
CellReference = c.Address.ToString(),
SheetId = worksheet.SheetId
});
}
}
}
//var cCellsToRemove = new List<CalculationCell>();
var m = from cc in calculationChain.Elements<CalculationCell>()
where !(cc.SheetId != null || cc.InChildChain != null)
&& calculationChain.Elements<CalculationCell>()
.Where(c1 => c1.SheetId != null)
.Select(c1 => c1.CellReference.Value)
.Contains(cc.CellReference.Value)
|| cellsWithoutFormulas.Contains(cc.CellReference.Value)
select cc;
//m.ToList().ForEach(cc => cCellsToRemove.Add(cc));
m.ToList().ForEach(cc => calculationChain.RemoveChild(cc));
}
if (!calculationChain.Any())
workbookPart.DeletePart(workbookPart.CalculationChainPart);
}
// Given a WorkbookPart, inserts a new worksheet.
public static WorksheetPart InsertWorksheet(WorkbookPart workbookPart)
{
// Add a new worksheet part to the workbook.
WorksheetPart newWorksheetPart = workbookPart.AddNewPart<WorksheetPart>();
newWorksheetPart.Worksheet = new Worksheet(new SheetData());
newWorksheetPart.Worksheet.Save();
// GetFirstChild <T>: Find the first child element in type T
Sheets sheets = workbookPart.Workbook.GetFirstChild<Sheets>();
string relationshipId = workbookPart.GetIdOfPart(newWorksheetPart);
// Get a unique ID for the new worksheet.
uint sheetId = 1;
if (sheets.Elements<Sheet>().Count() > 0)
{
sheetId = sheets.Elements<Sheet>().Select(s => s.SheetId.Value).Max() + 1;
}
// Give the new worksheet a name.
string sheetName = "Sheet" + sheetId;
// Append the new worksheet and associate it with the workbook.
Sheet sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = sheetName };
sheets.Append(sheet);
workbookPart.Workbook.Save();
return newWorksheetPart;
}
/// <summary>
/// 插入worksheet
/// </summary>
/// <param name="workbookPart"></param>
/// <returns></returns>
private static WorksheetPart InsertWorksheet(WorkbookPart workbookPart, string sheetName = null)
{
//創建一個新的WorkssheetPart（後面將用它來容納具體的Sheet）
WorksheetPart newWorksheetPart = workbookPart.AddNewPart<WorksheetPart>();
newWorksheetPart.Worksheet = new Worksheet(new SheetData());
newWorksheetPart.Worksheet.Save();
//取得Sheet集合
Sheets sheets = workbookPart.Workbook.GetFirstChild<Sheets>();
if (sheets == null)
{
sheets = workbookPart.Workbook.AppendChild<Sheets>(new Sheets());
}
string relationshipId = workbookPart.GetIdOfPart(newWorksheetPart);
//得到Sheet的唯一序號
uint sheetId = 1;
if (sheets.Elements<Sheet>().Count() > 0)
{
sheetId = sheets.Elements<Sheet>().Select(s => s.SheetId.Value).Max() + 1;
}
string sheetTempName = "Sheet" + sheetId;
if (sheetName != null)
{
bool hasSameName = false;
//檢測是否有重名
foreach (var item in sheets.Elements<Sheet>())
{
if (item.Name == sheetName)
{
hasSameName = true;
break;
}
}
if (!hasSameName)
{
sheetTempName = sheetName;
}
}
//創建Sheet實例並將它與sheets關聯
Sheet sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = sheetTempName };
sheets.Append(sheet);
workbookPart.Workbook.Save();
return newWorksheetPart;
}
static Excel.Stylesheet SaveStyleSheet(Package.WorkbookPart exportedWorkbookPart, ref Dictionary <CellFormat, uint> cellFormatList, Spreadsheet document)
{
var exportedStyleSheetPart = exportedWorkbookPart.AddNewPart <Package.WorkbookStylesPart>();
cellFormatList = new Dictionary <CellFormat, uint>();
Excel.Stylesheet exportedStyleSheet = new Excel.Stylesheet();
exportedStyleSheetPart.Stylesheet = exportedStyleSheet;
exportedStyleSheet.CellFormats = new Excel.CellFormats();
exportedStyleSheet.CellFormats.Append(new Excel.CellFormat()
{
NumberFormatId = 0, FontId = 0, FillId = 0, BorderId = 0, FormatId = 0
});
exportedStyleSheet.Fills = new Excel.Fills();
exportedStyleSheet.Fills.Append(new Excel.Fill(new Excel.PatternFill()
{
PatternType = new OpenXml.EnumValue <Excel.PatternValues>(Excel.PatternValues.None)
}));
exportedStyleSheet.Fills.Append(new Excel.Fill(new Excel.PatternFill()
{
PatternType = new OpenXml.EnumValue <Excel.PatternValues>(Excel.PatternValues.Gray125)
}));
exportedStyleSheet.Fonts = new Excel.Fonts();
exportedStyleSheet.Fonts.Append(new Excel.Font()
{
FontSize = new Excel.FontSize()
{
Val = 11
},
Color = new Excel.Color()
{
Theme = 1
},
FontName = new Excel.FontName()
{
Val = "Calibri"
},
FontFamilyNumbering = new Excel.FontFamilyNumbering()
{
Val = 2
},
FontScheme = new Excel.FontScheme()
{
Val = new OpenXml.EnumValue <Excel.FontSchemeValues>(Excel.FontSchemeValues.Minor)
}
});
exportedStyleSheet.Borders = new Excel.Borders();
exportedStyleSheet.Borders.Append(new Excel.Border()
{
LeftBorder = new Excel.LeftBorder(),
RightBorder = new Excel.RightBorder(),
TopBorder = new Excel.TopBorder(),
BottomBorder = new Excel.BottomBorder(),
DiagonalBorder = new Excel.DiagonalBorder()
});
exportedStyleSheet.CellStyleFormats = new Excel.CellStyleFormats();
exportedStyleSheet.CellStyleFormats.Append(new Excel.CellFormat()
{
NumberFormatId = 0, FontId = 0, FillId = 0, BorderId = 0
});
exportedStyleSheet.CellStyles = new Excel.CellStyles();
exportedStyleSheet.CellStyles.Append(new Excel.CellStyle()
{
Name = "Normal", FormatId = 0, BuiltinId = 0
});
exportedStyleSheet.DifferentialFormats = new Excel.DifferentialFormats();
exportedStyleSheet.TableStyles = new Excel.TableStyles()
{
DefaultTableStyle = "TableStyleMedium9", DefaultPivotStyle = "PivotStyleLight16"
};
foreach (var cellFormat in document.CellFormats())
{
if (cellFormat.Count() > 0)
{
cellFormatList.Add(cellFormat, (uint)exportedStyleSheet.CellFormats.ChildElements.Count());
exportedStyleSheet.CellFormats.Append(new Excel.CellFormat()
{
NumberFormatId = 0,
FontId = 0,
BorderId = 0,
FormatId = 0,
FillId = (uint)exportedStyleSheet.Fills.ChildElements.Count(),
ApplyFill = true
});
exportedStyleSheet.Fills.Append(new Excel.Fill(new Excel.PatternFill()
{
PatternType = new OpenXml.EnumValue <Excel.PatternValues>(Excel.PatternValues.Solid),
ForegroundColor = new Excel.ForegroundColor()
{
Rgb = new OpenXml.HexBinaryValue(cellFormat.FillColor)
}
}));
}
}
exportedStyleSheet.Save();
return(exportedStyleSheet);
}
protected internal override void CreateDocument()
{
Report r = base.Report();
_workbookPart = _spreadSheet.AddWorkbookPart();
var openXmlExportHelper = new OpenXmlWriterHelper();
_styleSheet = openXmlExportHelper.CreateDefaultStylesheet();
_workbookPart.Workbook = _workbook;
var sheets = _workbook.AppendChild<Sheets>(new Sheets());
// create worksheet 1
var worksheetPart =_workbookPart.AddNewPart<WorksheetPart>();
var sheet = new Sheet() { Id = _workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = r.Name };
sheets.Append(sheet);
_workSheetSettings.Add(new WorkSheetSetting(r.Name));
_writer = OpenXmlWriter.Create(worksheetPart);
_writer.WriteStartElement(new Worksheet());
_writer.WriteStartElement(new SheetData());
_currentWorkSheet = _workSheetSettings.Last();
}
public virtual void SaveCustomStylesheet(WorkbookPart workbookPart)
{
//get a copy of the default excel style sheet then add additional styles to it
var stylesheet = CreateDefaultStylesheet();
// ***************************** Fills *********************************
var fills = stylesheet.Fills;
//header fills background color
var fill = new Fill();
var patternFill = new PatternFill();
patternFill.PatternType = PatternValues.Solid;
//patternFill.ForegroundColor = new ForegroundColor { Rgb = HexBinaryValue.FromString("C8EEFF") };
patternFill.BackgroundColor = new BackgroundColor() { Indexed = 64 };
fill.PatternFill = patternFill;
fills.AppendChild(fill);
fills.Count = (uint)fills.ChildElements.Count;
// *************************** numbering formats ***********************
var nfs = stylesheet.NumberingFormats;
//number less than 164 is reserved by excel for default formats
uint iExcelIndex = 165;
NumberingFormat nf;
nf = new NumberingFormat();
nf.NumberFormatId = iExcelIndex++;
nf.FormatCode = @"[$-409]m/d/yy\ h:mm\ AM/PM;@";
nfs.Append(nf);
nfs.Count = (uint)nfs.ChildElements.Count;
//************************** cell formats ***********************************
var cfs = stylesheet.CellFormats;//this should already contain a default StyleIndex of 0
var cf = new CellFormat();// Date time format is defined as StyleIndex = 1
cf.NumberFormatId = nf.NumberFormatId;
cf.FontId = 0;
cf.FillId = 0;
cf.BorderId = 0;
cf.FormatId = 0;
cf.ApplyNumberFormat = true;
cfs.Append(cf);
cf = new CellFormat();// Header format is defined as StyleINdex = 2
cf.NumberFormatId = 0;
cf.FontId = 0;
cf.FillId = 2;
cf.ApplyFill = true;
cf.BorderId = 0;
cf.FormatId = 0;
cfs.Append(cf);
cfs.Count = (uint)cfs.ChildElements.Count;
var workbookStylesPart = workbookPart.AddNewPart<WorkbookStylesPart>();
var style = workbookStylesPart.Stylesheet = stylesheet;
style.Save();
}
/// <summary>
/// write out the share string xml. Call this after writing out all shared string values in sheet
/// </summary>
/// <param name="workbookPart"></param>
public void CreateShareStringPart(WorkbookPart workbookPart)
{
if (_shareStringMaxIndex > 0)
{
var sharedStringPart = workbookPart.AddNewPart<SharedStringTablePart>();
using (var writer = OpenXmlWriter.Create(sharedStringPart))
{
writer.WriteStartElement(new SharedStringTable());
foreach (var item in _shareStringDictionary)
{
writer.WriteStartElement(new SharedStringItem());
writer.WriteElement(new Text(item.Key));
writer.WriteEndElement();
}
writer.WriteEndElement();
}
}
}
// Given a WorkbookPart, inserts a new worksheet.
private WorksheetPart InsertWorksheet(WorkbookPart workbookPart)
{
// We need single sheet only, if there is a sheet, return
if (workbookPart.WorksheetParts.Count() > 0)
{
return workbookPart.WorksheetParts.FirstOrDefault<WorksheetPart>();
}
// Add a new worksheet part to the workbook.
WorksheetPart newWorksheetPart = workbookPart.AddNewPart<WorksheetPart>();
newWorksheetPart.Worksheet = new Worksheet(new SheetData());
newWorksheetPart.Worksheet.Save();
Sheets sheets = workbookPart.Workbook.GetFirstChild<Sheets>();
string relationshipId = workbookPart.GetIdOfPart(newWorksheetPart);
// Get a unique ID for the new sheet.
uint sheetId = 1;
if (sheets.Elements<Sheet>().Count() > 0)
{
sheetId = sheets.Elements<Sheet>().Select(s => s.SheetId.Value).Max() + 1;
}
string sheetName = "Sheet" + sheetId;
// Append the new worksheet and associate it with the workbook.
Sheet sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = sheetName };
sheets.Append(sheet);
workbookPart.Workbook.Save();
return newWorksheetPart;
}
//private void GenerateDrawingsPartContent(DrawingsPart drawingsPart, XLWorksheet worksheet)
//{
// if (drawingsPart.WorksheetDrawing == null)
// drawingsPart.WorksheetDrawing = new Xdr.WorksheetDrawing();
// var worksheetDrawing = drawingsPart.WorksheetDrawing;
// if (!worksheetDrawing.NamespaceDeclarations.Contains(new KeyValuePair<string, string>("xdr", "http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing")))
// worksheetDrawing.AddNamespaceDeclaration("xdr", "http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing");
// if (!worksheetDrawing.NamespaceDeclarations.Contains(new KeyValuePair<string, string>("a", "http://schemas.openxmlformats.org/drawingml/2006/main")))
// worksheetDrawing.AddNamespaceDeclaration("a", "http://schemas.openxmlformats.org/drawingml/2006/main");
// foreach (var chart in worksheet.Charts.OrderBy(c => c.ZOrder).Select(c => c))
// {
// Xdr.TwoCellAnchor twoCellAnchor = new Xdr.TwoCellAnchor();
// worksheetDrawing.AppendChild(twoCellAnchor);
// if (chart.Anchor == XLDrawingAnchor.MoveAndSizeWithCells)
// twoCellAnchor.EditAs = Xdr.EditAsValues.TwoCell;
// else if (chart.Anchor == XLDrawingAnchor.MoveWithCells)
// twoCellAnchor.EditAs = Xdr.EditAsValues.OneCell;
// else
// twoCellAnchor.EditAs = Xdr.EditAsValues.Absolute;
// if (twoCellAnchor.FromMarker == null)
// twoCellAnchor.FromMarker = new Xdr.FromMarker();
// twoCellAnchor.FromMarker.RowId = new Xdr.RowId((chart.FirstRow - 1).ToString());
// twoCellAnchor.FromMarker.RowOffset = new Xdr.RowOffset(chart.FirstRowOffset.ToString());
// twoCellAnchor.FromMarker.ColumnId = new Xdr.ColumnId((chart.FirstColumn - 1).ToString());
// twoCellAnchor.FromMarker.ColumnOffset = new Xdr.ColumnOffset(chart.FirstColumnOffset.ToString());
// if (twoCellAnchor.ToMarker == null)
// twoCellAnchor.ToMarker = new Xdr.ToMarker();
// twoCellAnchor.ToMarker.RowId = new Xdr.RowId((chart.LastRow - 1).ToString());
// twoCellAnchor.ToMarker.RowOffset = new Xdr.RowOffset(chart.LastRowOffset.ToString());
// twoCellAnchor.ToMarker.ColumnId = new Xdr.ColumnId((chart.LastColumn - 1).ToString());
// twoCellAnchor.ToMarker.ColumnOffset = new Xdr.ColumnOffset(chart.LastColumnOffset.ToString());
// Xdr.GraphicFrame graphicFrame = new Xdr.GraphicFrame();
// twoCellAnchor.AppendChild(graphicFrame);
// if (graphicFrame.NonVisualGraphicFrameProperties == null)
// graphicFrame.NonVisualGraphicFrameProperties = new Xdr.NonVisualGraphicFrameProperties();
// if (graphicFrame.NonVisualGraphicFrameProperties.NonVisualDrawingProperties == null)
// graphicFrame.NonVisualGraphicFrameProperties.NonVisualDrawingProperties = new Xdr.NonVisualDrawingProperties() { Id = (UInt32)chart.Id, Name = chart.Name, Description = chart.Description, Hidden = chart.Hidden };
// if (graphicFrame.NonVisualGraphicFrameProperties.NonVisualGraphicFrameDrawingProperties == null)
// graphicFrame.NonVisualGraphicFrameProperties.NonVisualGraphicFrameDrawingProperties = new Xdr.NonVisualGraphicFrameDrawingProperties();
// if (graphicFrame.Transform == null)
// graphicFrame.Transform = new Xdr.Transform();
// if (chart.HorizontalFlip)
// graphicFrame.Transform.HorizontalFlip = true;
// else
// graphicFrame.Transform.HorizontalFlip = null;
// if (chart.VerticalFlip)
// graphicFrame.Transform.VerticalFlip = true;
// else
// graphicFrame.Transform.VerticalFlip = null;
// if (chart.Rotation != 0)
// graphicFrame.Transform.Rotation = chart.Rotation;
// else
// graphicFrame.Transform.Rotation = null;
// if (graphicFrame.Transform.Offset == null)
// graphicFrame.Transform.Offset = new A.Offset();
// graphicFrame.Transform.Offset.X = chart.OffsetX;
// graphicFrame.Transform.Offset.Y = chart.OffsetY;
// if (graphicFrame.Transform.Extents == null)
// graphicFrame.Transform.Extents = new A.Extents();
// graphicFrame.Transform.Extents.Cx = chart.ExtentLength;
// graphicFrame.Transform.Extents.Cy = chart.ExtentWidth;
// if (graphicFrame.Graphic == null)
// graphicFrame.Graphic = new A.Graphic();
// if (graphicFrame.Graphic.GraphicData == null)
// graphicFrame.Graphic.GraphicData = new A.GraphicData() { Uri = "http://schemas.openxmlformats.org/drawingml/2006/chart" };
// if (!graphicFrame.Graphic.GraphicData.Elements<C.ChartReference>().Any())
// {
// C.ChartReference chartReference = new C.ChartReference() { Id = "rId" + chart.Id.ToStringLookup() };
// chartReference.AddNamespaceDeclaration("c", "http://schemas.openxmlformats.org/drawingml/2006/chart");
// chartReference.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
// graphicFrame.Graphic.GraphicData.AppendChild(chartReference);
// }
// if (!twoCellAnchor.Elements<Xdr.ClientData>().Any())
// twoCellAnchor.AppendChild(new Xdr.ClientData());
// }
//}
//private void GenerateChartPartContent(ChartPart chartPart, XLChart xlChart)
//{
// if (chartPart.ChartSpace == null)
// chartPart.ChartSpace = new C.ChartSpace();
// C.ChartSpace chartSpace = chartPart.ChartSpace;
// if (!chartSpace.NamespaceDeclarations.Contains(new KeyValuePair<string, string>("c", "http://schemas.openxmlformats.org/drawingml/2006/chart")))
// chartSpace.AddNamespaceDeclaration("c", "http://schemas.openxmlformats.org/drawingml/2006/chart");
// if (!chartSpace.NamespaceDeclarations.Contains(new KeyValuePair<string, string>("a", "http://schemas.openxmlformats.org/drawingml/2006/main")))
// chartSpace.AddNamespaceDeclaration("a", "http://schemas.openxmlformats.org/drawingml/2006/main");
// if (!chartSpace.NamespaceDeclarations.Contains(new KeyValuePair<string, string>("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships")))
// chartSpace.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
// if (chartSpace.EditingLanguage == null)
// chartSpace.EditingLanguage = new C.EditingLanguage() { Val = CultureInfo.CurrentCulture.Name };
// else
// chartSpace.EditingLanguage.Val = CultureInfo.CurrentCulture.Name;
// C.Chart chart = new C.Chart();
// chartSpace.AppendChild(chart);
// if (chart.Title == null)
// chart.Title = new C.Title();
// if (chart.Title.Layout == null)
// chart.Title.Layout = new C.Layout();
// if (chart.View3D == null)
// chart.View3D = new C.View3D();
// if (chart.View3D.RightAngleAxes == null)
// chart.View3D.RightAngleAxes = new C.RightAngleAxes();
// chart.View3D.RightAngleAxes.Val = xlChart.RightAngleAxes;
// if (chart.PlotArea == null)
// chart.PlotArea = new C.PlotArea();
// if (chart.PlotArea.Layout == null)
// chart.PlotArea.Layout = new C.Layout();
// OpenXmlElement chartElement = GetChartElement(xlChart);
// chart.PlotArea.AppendChild(chartElement);
// C.CategoryAxis categoryAxis1 = new C.CategoryAxis();
// C.AxisId axisId4 = new C.AxisId() { Val = (UInt32Value)71429120U };
// C.Scaling scaling1 = new C.Scaling();
// C.Orientation orientation1 = new C.Orientation() { Val = C.OrientationValues.MinMax };
// scaling1.AppendChild(orientation1);
// C.AxisPosition axisPosition1 = new C.AxisPosition() { Val = C.AxisPositionValues.Bottom };
// C.TickLabelPosition tickLabelPosition1 = new C.TickLabelPosition() { Val = C.TickLabelPositionValues.NextTo };
// C.CrossingAxis crossingAxis1 = new C.CrossingAxis() { Val = (UInt32Value)71432064U };
// C.Crosses crosses1 = new C.Crosses() { Val = C.CrossesValues.AutoZero };
// C.AutoLabeled autoLabeled1 = new C.AutoLabeled() { Val = true };
// C.LabelAlignment labelAlignment1 = new C.LabelAlignment() { Val = C.LabelAlignmentValues.Center };
// C.LabelOffset labelOffset1 = new C.LabelOffset() { Val = (UInt16Value)100U };
// categoryAxis1.AppendChild(axisId4);
// categoryAxis1.AppendChild(scaling1);
// categoryAxis1.AppendChild(axisPosition1);
// categoryAxis1.AppendChild(tickLabelPosition1);
// categoryAxis1.AppendChild(crossingAxis1);
// categoryAxis1.AppendChild(crosses1);
// categoryAxis1.AppendChild(autoLabeled1);
// categoryAxis1.AppendChild(labelAlignment1);
// categoryAxis1.AppendChild(labelOffset1);
// C.ValueAxis valueAxis1 = new C.ValueAxis();
// C.AxisId axisId5 = new C.AxisId() { Val = (UInt32Value)71432064U };
// C.Scaling scaling2 = new C.Scaling();
// C.Orientation orientation2 = new C.Orientation() { Val = C.OrientationValues.MinMax };
// scaling2.AppendChild(orientation2);
// C.AxisPosition axisPosition2 = new C.AxisPosition() { Val = C.AxisPositionValues.Left };
// C.MajorGridlines majorGridlines1 = new C.MajorGridlines();
// C.NumberingFormat numberingFormat1 = new C.NumberingFormat() { FormatCode = "General", SourceLinked = true };
// C.TickLabelPosition tickLabelPosition2 = new C.TickLabelPosition() { Val = C.TickLabelPositionValues.NextTo };
// C.CrossingAxis crossingAxis2 = new C.CrossingAxis() { Val = (UInt32Value)71429120U };
// C.Crosses crosses2 = new C.Crosses() { Val = C.CrossesValues.AutoZero };
// C.CrossBetween crossBetween1 = new C.CrossBetween() { Val = C.CrossBetweenValues.Between };
// valueAxis1.AppendChild(axisId5);
// valueAxis1.AppendChild(scaling2);
// valueAxis1.AppendChild(axisPosition2);
// valueAxis1.AppendChild(majorGridlines1);
// valueAxis1.AppendChild(numberingFormat1);
// valueAxis1.AppendChild(tickLabelPosition2);
// valueAxis1.AppendChild(crossingAxis2);
// valueAxis1.AppendChild(crosses2);
// valueAxis1.AppendChild(crossBetween1);
// plotArea.AppendChild(bar3DChart1);
// plotArea.AppendChild(categoryAxis1);
// plotArea.AppendChild(valueAxis1);
// C.Legend legend1 = new C.Legend();
// C.LegendPosition legendPosition1 = new C.LegendPosition() { Val = C.LegendPositionValues.Right };
// C.Layout layout3 = new C.Layout();
// legend1.AppendChild(legendPosition1);
// legend1.AppendChild(layout3);
// C.PlotVisibleOnly plotVisibleOnly1 = new C.PlotVisibleOnly() { Val = true };
// chart.AppendChild(legend1);
// chart.AppendChild(plotVisibleOnly1);
// C.PrintSettings printSettings1 = new C.PrintSettings();
// C.HeaderFooter headerFooter1 = new C.HeaderFooter();
// C.PageMargins pageMargins4 = new C.PageMargins() { Left = 0.70000000000000018D, Right = 0.70000000000000018D, Top = 0.75000000000000022D, Bottom = 0.75000000000000022D, Header = 0.3000000000000001D, Footer = 0.3000000000000001D };
// C.PageSetup pageSetup1 = new C.PageSetup();
// printSettings1.AppendChild(headerFooter1);
// printSettings1.AppendChild(pageMargins4);
// printSettings1.AppendChild(pageSetup1);
// chartSpace.AppendChild(printSettings1);
//}
//private OpenXmlElement GetChartElement(XLChart xlChart)
//{
// if (xlChart.ChartTypeCategory == XLChartTypeCategory.Bar3D)
// return GetBar3DChart(xlChart);
// else
// return null;
//}
//private OpenXmlElement GetBar3DChart(XLChart xlChart)
//{
// C.Bar3DChart bar3DChart = new C.Bar3DChart();
// bar3DChart.BarDirection = new C.BarDirection() { Val = GetBarDirection(xlChart) };
// bar3DChart.BarGrouping = new C.BarGrouping() { Val = GetBarGrouping(xlChart) };
// C.BarChartSeries barChartSeries = new C.BarChartSeries();
// barChartSeries.Index = new C.Index() { Val = (UInt32Value)0U };
// barChartSeries.Order = new C.Order() { Val = (UInt32Value)0U };
// C.SeriesText seriesText1 = new C.SeriesText();
// C.StringReference stringReference1 = new C.StringReference();
// C.Formula formula1 = new C.Formula();
// formula1.Text = "Sheet1!$B$1";
// stringReference1.AppendChild(formula1);
// seriesText1.AppendChild(stringReference1);
// C.CategoryAxisData categoryAxisData1 = new C.CategoryAxisData();
// C.StringReference stringReference2 = new C.StringReference();
// C.Formula formula2 = new C.Formula();
// formula2.Text = "Sheet1!$A$2:$A$3";
// C.StringCache stringCache2 = new C.StringCache();
// C.PointCount pointCount2 = new C.PointCount() { Val = (UInt32Value)2U };
// C.StringPoint stringPoint2 = new C.StringPoint() { Index = (UInt32Value)0U };
// C.NumericValue numericValue2 = new C.NumericValue();
// numericValue2.Text = "A";
// stringPoint2.AppendChild(numericValue2);
// C.StringPoint stringPoint3 = new C.StringPoint() { Index = (UInt32Value)1U };
// C.NumericValue numericValue3 = new C.NumericValue();
// numericValue3.Text = "B";
// stringPoint3.AppendChild(numericValue3);
// stringCache2.AppendChild(pointCount2);
// stringCache2.AppendChild(stringPoint2);
// stringCache2.AppendChild(stringPoint3);
// stringReference2.AppendChild(formula2);
// stringReference2.AppendChild(stringCache2);
// categoryAxisData1.AppendChild(stringReference2);
// C.Values values1 = new C.Values();
// C.NumberReference numberReference1 = new C.NumberReference();
// C.Formula formula3 = new C.Formula();
// formula3.Text = "Sheet1!$B$2:$B$3";
// C.NumberingCache numberingCache1 = new C.NumberingCache();
// C.FormatCode formatCode1 = new C.FormatCode();
// formatCode1.Text = "General";
// C.PointCount pointCount3 = new C.PointCount() { Val = (UInt32Value)2U };
// C.NumericPoint numericPoint1 = new C.NumericPoint() { Index = (UInt32Value)0U };
// C.NumericValue numericValue4 = new C.NumericValue();
// numericValue4.Text = "5";
// numericPoint1.AppendChild(numericValue4);
// C.NumericPoint numericPoint2 = new C.NumericPoint() { Index = (UInt32Value)1U };
// C.NumericValue numericValue5 = new C.NumericValue();
// numericValue5.Text = "10";
// numericPoint2.AppendChild(numericValue5);
// numberingCache1.AppendChild(formatCode1);
// numberingCache1.AppendChild(pointCount3);
// numberingCache1.AppendChild(numericPoint1);
// numberingCache1.AppendChild(numericPoint2);
// numberReference1.AppendChild(formula3);
// numberReference1.AppendChild(numberingCache1);
// values1.AppendChild(numberReference1);
// barChartSeries.AppendChild(index1);
// barChartSeries.AppendChild(order1);
// barChartSeries.AppendChild(seriesText1);
// barChartSeries.AppendChild(categoryAxisData1);
// barChartSeries.AppendChild(values1);
// C.Shape shape1 = new C.Shape() { Val = C.ShapeValues.Box };
// C.AxisId axisId1 = new C.AxisId() { Val = (UInt32Value)71429120U };
// C.AxisId axisId2 = new C.AxisId() { Val = (UInt32Value)71432064U };
// C.AxisId axisId3 = new C.AxisId() { Val = (UInt32Value)0U };
// bar3DChart.AppendChild(barChartSeries);
// bar3DChart.AppendChild(shape1);
// bar3DChart.AppendChild(axisId1);
// bar3DChart.AppendChild(axisId2);
// bar3DChart.AppendChild(axisId3);
// return bar3DChart;
//}
//private C.BarGroupingValues GetBarGrouping(XLChart xlChart)
//{
// if (xlChart.BarGrouping == XLBarGrouping.Clustered)
// return C.BarGroupingValues.Clustered;
// else if (xlChart.BarGrouping == XLBarGrouping.Percent)
// return C.BarGroupingValues.PercentStacked;
// else if (xlChart.BarGrouping == XLBarGrouping.Stacked)
// return C.BarGroupingValues.Stacked;
// else
// return C.BarGroupingValues.Standard;
//}
//private C.BarDirectionValues GetBarDirection(XLChart xlChart)
//{
// if (xlChart.BarOrientation == XLBarOrientation.Vertical)
// return C.BarDirectionValues.Column;
// else
// return C.BarDirectionValues.Bar;
//}
//--
private static void GeneratePivotTables(WorkbookPart workbookPart, WorksheetPart worksheetPart,
XLWorksheet xlWorksheet,
SaveContext context)
{
foreach (var pt in xlWorksheet.PivotTables)
{
var ptCdp = context.RelIdGenerator.GetNext(RelType.Workbook);
var pivotTableCacheDefinitionPart = workbookPart.AddNewPart<PivotTableCacheDefinitionPart>(ptCdp);
GeneratePivotTableCacheDefinitionPartContent(pivotTableCacheDefinitionPart, pt);
var pivotCaches = new PivotCaches();
var pivotCache = new PivotCache {CacheId = 0U, Id = ptCdp};
pivotCaches.AppendChild(pivotCache);
workbookPart.Workbook.AppendChild(pivotCaches);
var pivotTablePart =
worksheetPart.AddNewPart<PivotTablePart>(context.RelIdGenerator.GetNext(RelType.Workbook));
GeneratePivotTablePartContent(pivotTablePart, pt);
pivotTablePart.AddPart(pivotTableCacheDefinitionPart, context.RelIdGenerator.GetNext(RelType.Workbook));
}
}
private static SharedStringTablePart GetSharedStringPart(WorkbookPart workbookPart)
{
return workbookPart.GetPartsOfType<SharedStringTablePart>().Count() > 0 ? workbookPart.GetPartsOfType<SharedStringTablePart>().First() : workbookPart.AddNewPart<SharedStringTablePart>();
}
/// <summary>
/// Creates a chartsheet part from given data
/// </summary>
/// <param name="chartType">Type of chart to generate</param>
/// <param name="values">Values to represent in the chart</param>
/// <param name="headerReference">Columns to be used as series</param>
/// <param name="categoryReference">Column to be used as category</param>
/// <returns>Chartsheet part with contents related</returns>
public OpenXmlSDK.ChartsheetPart Create(ChartType chartType, List <string> values, List <string> headerReference, string categoryReference)
{
//Creates base content and associates it to a new chartsheet part
XDocument chartsheet = CreateEmptyChartsheet();
OpenXmlSDK.WorkbookPart workbook = ((OpenXmlSDK.SpreadsheetDocument)parentDocument.Document).WorkbookPart;
OpenXmlSDK.ChartsheetPart chartsheetPart = workbook.AddNewPart <OpenXmlSDK.ChartsheetPart>();
XDocument chartsheetDocument = parentDocument.GetXDocument(chartsheetPart);
XDocument newChartsheetDocument = CreateEmptyChartsheet();
if (chartsheetDocument.Root == null)
{
chartsheetDocument.Add(
newChartsheetDocument.Root
);
}
else
{
chartsheetDocument.Root.ReplaceWith(newChartsheetDocument.Root);
}
//Creates a base drawings part and associates it to the chartsheet part
OpenXmlSDK.DrawingsPart drawingsPart = chartsheetPart.AddNewPart <OpenXmlSDK.DrawingsPart>();
XDocument drawingsDocument = parentDocument.GetXDocument(drawingsPart);
XDocument newDrawingDocument = CreateEmptyDrawing();
if (drawingsDocument.Root == null)
{
drawingsDocument.Add(
newDrawingDocument.Root
);
}
else
{
drawingsDocument.Root.ReplaceWith(newDrawingDocument.Root);
}
//Adds content to chartsheet document to reference drawing document
chartsheetDocument
.Element(ns + "chartsheet")
.Add(
new XElement(ns + "drawing",
new XAttribute(relationshipsns + "id", chartsheetPart.GetIdOfPart(drawingsPart))
)
);
//creates the chart part and associates it to the drawings part
OpenXmlSDK.ChartPart chartPart = drawingsPart.AddNewPart <OpenXmlSDK.ChartPart>();
XDocument chartDocument = parentDocument.GetXDocument(chartPart);
XDocument newChartDocument = CreateChart(chartType, values, headerReference, categoryReference);// CreateEmptyChart();
if (chartDocument.Root == null)
{
chartDocument.Add(
newChartDocument.Root
);
}
else
{
chartDocument.Root.ReplaceWith(newChartDocument.Root);
}
//Adds content to drawing document to reference chart document
drawingsDocument
.Descendants(drawingns + "graphicData")
.First()
.Add(
new XAttribute("uri", chartns),
new XElement(chartns + "chart",
new XAttribute(XNamespace.Xmlns + "c", chartns),
new XAttribute(XNamespace.Xmlns + "r", relationshipsns),
new XAttribute(relationshipsns + "id", drawingsPart.GetIdOfPart(chartPart))
)
);
//Associates the chartsheet part to the workbook part
XDocument document = parentDocument.GetXDocument(((OpenXmlSDK.SpreadsheetDocument)parentDocument.Document).WorkbookPart);
int sheetId = document.Root.Element(ns + "sheets").Elements(ns + "sheet").Count() + 1;
int chartsheetCount =
document.Root
.Element(ns + "sheets")
.Elements(ns + "sheet")
.Where(
t =>
t.Attribute("name").Value.StartsWith("chart")
)
.Count() + 1;
//Adds content to workbook document to reference chartsheet document
document.Root
.Element(ns + "sheets")
.Add(
new XElement(ns + "sheet",
new XAttribute("name", string.Format("chart{0}", chartsheetCount)),
new XAttribute("sheetId", sheetId),
new XAttribute(relationshipsns + "id", workbook.GetIdOfPart(chartsheetPart))
)
);
return(chartsheetPart);
}
/// <summary>
/// Generate inspection reports
/// </summary>
/// <param name="itemsource">Item source</param>
/// <param name="workbookPart">Worbook part</param>
private static void GenerateAllInspectionReports(ExportInspectionReportsModel itemsource, WorkbookPart workbookPart, Sheets sheets, int sheetId, string logoPath)
{
// Loop through each of the DataTables in our DataSet, and create a new Excel Worksheet for each.
foreach (var item in itemsource.InspectionReports)
{
WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
Worksheet worksheet = new Worksheet();
SheetData sheetData1 = new SheetData();
//get the number of columns in the report
Row rowTitle;
int numberOfColumnsCaption = item.Value.Captions.Count;
//get the string name of the columns
string[] excelColumnNamesTitle = new string[numberOfColumnsCaption];
for (int n = 0; n < numberOfColumnsCaption; n++)
excelColumnNamesTitle[n] = GetExcelColumnName(n);
//build the title
for (int i = 1; i <= 6; i++)
{
rowTitle = new Row() { RowIndex = (UInt32Value)(uint)i };
for (int cellval = 0; cellval < numberOfColumnsCaption; cellval++)
{
AppendTextCell(excelColumnNamesTitle[cellval] + i, string.Empty, rowTitle, 3);
}
sheetData1.Append(rowTitle);
}
MergeCells mergeCells = new MergeCells();
Row currentRowTitle = sheetData1.Elements<Row>().FirstOrDefault(row => row.RowIndex.Value == (uint)2);
//add the business application name
UpdateStringCellValue("B2", item.Value.BusinessApplicationName, currentRowTitle, 5);
string lastColumnName = excelColumnNamesTitle.Last() + "2";
//merge all cells in the title
MergeCell mergeCell = new MergeCell();
mergeCell.Reference = "B2:" + lastColumnName;
mergeCells.Append(mergeCell);
currentRowTitle = sheetData1.Elements<Row>().FirstOrDefault(row => row.RowIndex.Value == (uint)4);
//add the form name
UpdateStringCellValue("B4", item.Key, currentRowTitle, 5);
lastColumnName = lastColumnName.Replace("2", "4");
//merge all cell in the form name
mergeCell = new MergeCell();
mergeCell.Reference = "B4:" + lastColumnName;
mergeCells.Append(mergeCell);
Drawing drawing = AddLogo(logoPath, worksheetPart);
int rowIndex = 7;
//get the names of the columns
string[] excelColumnNamesCaptions = new string[numberOfColumnsCaption];
for (int n = 0; n < numberOfColumnsCaption; n++)
excelColumnNamesCaptions[n] = GetExcelColumnName(n);
Row rowCaption = new Row() { RowIndex = (UInt32Value)(uint)rowIndex };
//build column names of the report
Columns columns = new Columns();
for (int i = 0; i < item.Value.Captions.Count; i++)
{
var caption = item.Value.Captions[i];
AppendTextCell(excelColumnNamesCaptions[i] + rowIndex.ToString(), caption.Caption, rowCaption, 2);
columns.Append(CreateColumnData((UInt32Value)(uint)i + 1, (UInt32Value)(uint)i + 1, caption.ExcelColumnWidth));
}
sheetData1.Append(rowCaption);
//add the new row with the name of the columns
worksheet.Append(columns);
rowIndex = 8;
//write the data of the report
foreach (var row in item.Value.DataRows)
{
int numberOfColumnsData = row.FieldValues.Count;
//get column names
string[] excelColumnNamesData = new string[numberOfColumnsData];
for (int n = 0; n < numberOfColumnsData; n++)
excelColumnNamesData[n] = GetExcelColumnName(n);
//build the row
Row rowData = new Row() { RowIndex = (UInt32Value)(uint)rowIndex };
for (int colInx = 0; colInx < numberOfColumnsData; colInx++)
{
DynamicDataRowValue col = row.FieldValues[colInx];
switch (col.FieldType)
{
case (int)FieldType.Catalogue:
case (int)FieldType.RegularExpressionText:
case (int)FieldType.Time:
case (int)FieldType.SingleTextLine:
case (int)FieldType.MultipleTextLine:
case (int)FieldType.Datepicker:
case (int)FieldType.Boolean:
case (int)FieldType.AutoComplete:
case (int)FieldType.StatusField:
AppendTextCell(excelColumnNamesData[colInx] + rowIndex.ToString(), col.FieldValue, rowData, 1);
break;
case (int)FieldType.Integer:
case (int)FieldType.Decimal:
AppendNumberCell(excelColumnNamesData[colInx] + rowIndex.ToString(), col.FieldValue, rowData, 1);
break;
default:
break;
}
}
//add the new row to the report
sheetData1.Append(rowData);
rowIndex++;
}
//add the information of the current sheet
worksheet.Append(sheetData1);
//add merged cells
worksheet.InsertAfter(mergeCells, worksheet.Elements<SheetData>().First());
worksheet.Append(drawing);
worksheetPart.Worksheet = worksheet;
worksheetPart.Worksheet.Save();
//create the new sheet for this report
Sheet sheet = new Sheet() { Name = item.Key, SheetId = (UInt32Value)(uint)sheetId, Id = workbookPart.GetIdOfPart(worksheetPart) };
sheets.Append(sheet);
sheetId++;
}
}
/// <summary>
/// 創建一個SharedStringTablePart(相當於各Sheet共用的存放字符串的容器)
/// </summary>
/// <param name="workbookPart"></param>
/// <returns></returns>
private static SharedStringTablePart CreateSharedStringTablePart(WorkbookPart workbookPart)
{
SharedStringTablePart shareStringPart = null;
if (workbookPart.GetPartsOfType<SharedStringTablePart>().Count() > 0)
{
shareStringPart = workbookPart.GetPartsOfType<SharedStringTablePart>().First();
}
else
{
shareStringPart = workbookPart.AddNewPart<SharedStringTablePart>();
}
return shareStringPart;
}
/// <summary>
/// Заполнение ячейки данными
/// </summary>
/// <param name="wbPart"></param>
/// <param name="value"></param>
/// <returns></returns>
private int InsertSharedStringItem(WorkbookPart wbPart, string value)
{
var index = 0;
var found = false;
var stringTablePart = wbPart.GetPartsOfType<SharedStringTablePart>().FirstOrDefault() ??
wbPart.AddNewPart<SharedStringTablePart>();
var stringTable = stringTablePart.SharedStringTable ?? new SharedStringTable();
foreach (var item in stringTable.Elements<SharedStringItem>())
{
if (item.InnerText == value)
{
found = true;
break;
}
index += 1;
}
if (found) return index;
stringTable.AppendChild(new SharedStringItem(new Text(value)));
stringTable.Save();
return index;
}
