/// <summary>
/// Processes the paragraph part.
/// </summary>
/// <param name="wkb">The WKB.</param>
/// <param name="part">The part.</param>
/// <param name="placeholder">The placeholder.</param>
/// <param name="mapping">The mapping.</param>
/// <param name="worksheetPart">The worksheet part.</param>
/// <param name="reportWSPart">The report ws part.</param>
/// <returns></returns>
private static DrawingSpreadsheet.Shape ProcessParagraphPart(OpenXmlSpreadsheet.Workbook wkb, ExportPart part, MappingPlaceholder placeholder, ParagraphMapping mapping, WorksheetPart worksheetPart, WorksheetPart reportWSPart)
{
string SheetName = "";
uint RowStart = 0;
uint RowEnd = 0;
uint ColStart = 0;
uint ColEnd = 0;
// if no placeholder is specified then return now
if (placeholder == null)
{
return(null);
}
DrawingSpreadsheet.Shape matchShape = worksheetPart.GetShapeByName(placeholder.Id);
if (matchShape == null)
{
return(null);
}
// save the location of this shape,
// this information will be used to position the incoming chart
Extents extents = matchShape.ShapeProperties.Transform2D.Extents;
Offset offset = matchShape.ShapeProperties.Transform2D.Offset;
OpenXmlSpreadsheet.DefinedName rtfXmlDefinedName = wkb.GetDefinedNameByName(string.Format("{0}_{1}", part.DataSheetName, mapping.SourceFieldName));
wkb.BreakDownDefinedName(rtfXmlDefinedName, ref SheetName, ref RowStart, ref RowEnd, ref ColStart, ref ColEnd);
WorksheetPart wksp = wkb.GetWorksheetPartByName(SheetName);
OpenXmlSpreadsheet.SheetData sheetData = wksp.Worksheet.GetFirstChild <OpenXmlSpreadsheet.SheetData>();
OpenXmlSpreadsheet.Cell rtfXmlCell = sheetData.GetCell(ColStart, RowStart + 1);
// Use the cell on the hidden data sheet as source for the XAML reader
Section RTFSection = XamlSectionDocumentReader(rtfXmlCell.CellValue.InnerText);
// The paragraph in the cell.inlinestring have a very different class structure to the paragraphs in the shape.textbody
// So, the paragraph will need to go through a converter to do this.
DrawingSpreadsheet.Shape targetShape = ConvertParagraph(worksheetPart, RTFSection, matchShape);
// positon the new graphic frame after the shape its going to replace
matchShape.Parent.InsertAfter <OpenXmlElement>(targetShape, matchShape);
matchShape.Remove();
return(targetShape);
}
static void SaveDocument(Package.WorkbookPart exportedWorkbookPart, Excel.Stylesheet styleSheet, Dictionary <CellFormat, uint> cellFormatList, Spreadsheet document)
{
var exportedWorkbook = new Excel.Workbook();
exportedWorkbookPart.Workbook = exportedWorkbook;
Excel.Sheets exportedSheets = new Excel.Sheets();
exportedWorkbook.AppendChild(exportedSheets);
uint sheetId = 1;
foreach (var sheet in document.Sheets)
{
SaveSheet(exportedWorkbookPart, styleSheet, cellFormatList, exportedSheets, sheet, sheetId++);
}
}
public static void Read(Spreadsheet document, string path)
{
document.Clear();
var buffer = new MemoryStream();
var stream = File.Open(path, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
stream.CopyTo(buffer);
stream.Close();
var importDocument = Package.SpreadsheetDocument.Open(buffer, false);
var importWorkbook = new Excel.Workbook();
importWorkbook.Load(importDocument.WorkbookPart);
var sharedStringTable = ReadSharedStringTable(importDocument);
var cellFormats = ReadStyleSheet(document, importDocument);
foreach (Excel.Sheet importSheet in importWorkbook.Sheets)
{
ReadSheet(document, importDocument, importSheet, sharedStringTable, cellFormats);
}
importDocument.Close();
}
static Dictionary <string, Panel> SharepointPull(string xlFileLink, XLGuide guide, out List <string> panFilter, out Dictionary <string, Panel> panList)
{
panFilter = new List <string>();
panList = new Dictionary <string, Panel>();
ClientContext ctx = new ClientContext("https://islandcompanies.sharepoint.com/sites/SiteDeliveries/");
SecureString pw = new SecureString();
foreach (char a in "Michael1994")
{
pw.AppendChar(a);
}
ctx.Credentials = new SharePointOnlineCredentials("*****@*****.**", pw);
//ctx.Credentials = new NetworkCredential("*****@*****.**", "Michael1994", "islandcompanies");
Web web = ctx.Web;
ctx.Load(web, w => w.Title);
getXLFileName(xlFileLink, out string xlFileName);
getSharepointTree(xlFileLink, out List <String> sharepointTree);
try
{
Folder sourceFolder = web.GetFolderByServerRelativeUrl(sharepointTree.Aggregate((i, j) => i + "/" + j));
ctx.Load(ctx.Web);
ctx.Load(sourceFolder);
ctx.ExecuteQuery();
CamlQuery camlQuery = new CamlQuery();
camlQuery.ViewXml = @"<View Scope='Recursive'>
<Query>
<Where>
<Eq>
<FieldRef Name='FileLeafRef'></FieldRef>
<Value Type='Text'>" + xlFileName + @"</Value>
</Eq>
</Where>
</Query>
</View>";
//string srvRel = "sites/SiteDeliveries/Shared%20Documents/Packlists/01-000-000%20Panel%20Tracker/";
ListItemCollection listItems = ctx.Web.Lists.GetByTitle("Documents").GetItems(camlQuery);
ctx.Load(listItems);
ctx.ExecuteQuery();
Microsoft.SharePoint.Client.File file = listItems[0].File;
ctx.Load(file);
ctx.ExecuteQuery();
FileInformation fileInfo = Microsoft.SharePoint.Client.File.OpenBinaryDirect(ctx, file.ServerRelativeUrl);
ctx.ExecuteQuery();
string filePath = @"C:\Documents\Panel Tracker\" + file.Name;
using (var fileStream = new System.IO.FileStream(filePath, System.IO.FileMode.Create))
{
fileInfo.Stream.CopyTo(fileStream);
}
SpreadsheetDocument document = SpreadsheetDocument.Open(filePath, true);
DocumentFormat.OpenXml.Spreadsheet.Workbook workbook = document.WorkbookPart.Workbook;
string sheetName = "Panel Tracker";
WorkbookPart workbookpt = document.WorkbookPart;
Sheet sheet = workbook.Descendants <Sheet>().Where(S => S.Name == sheetName).FirstOrDefault();
WorksheetPart wsPart = (WorksheetPart)(workbookpt.GetPartById(sheet.Id));
bool run = true;
int iter = 0;
while (run == true)
{
Panel p = new Panel();
bool hasVal = false;
bool record = false;
string panName = "";
foreach (KeyValuePair <string, string> entry in guide.Info)
{
try
{
Cell cell = new Cell();
cell = GetCell(document, sheetName, entry.Value.ToString() + guide.Row.ToString(), out cell);
hasVal = true;
string v = cell.CellValue.InnerText;
if (entry.Key == "Panel Name")
{
p.Parameters.Add(entry.Key, v);
panName = v;
}
else
{
record = true;
string searchval = v.Split(')').Last();
p.Parameters.Add(entry.Key, searchval);
}
}
catch //(Exception ex)
{
p.Parameters.Add(entry.Key, null);
//Console.WriteLine(ex.Message);
}
iter++;
}
if (!hasVal)
{
run = false;
}
else
{
if (record)
{
panFilter.Add(panName);
}
panList.Add(panName, p);
}
guide.Row++;
}
}
catch (Exception ex)
{
//MessageBox.Show(ex.Message);
}
//Console.ReadKey();
return(panList);
}