/// <summary> /// Check if the excel file contains the correct sheetnames /// </summary> /// <param name="path">Path to the excel file</param> /// <returns></returns> public bool CheckSheetNames(string path) { using (SpreadsheetDocument document = SpreadsheetDocument.Open(path, true)) { //References to the workbook _workBook = document.WorkbookPart.Workbook; _workSheets = _workBook.Descendants <Sheet>(); if (_workSheets.Any(x => x.Name == this._workSheetktExaminedGroup.SheetName || x.Name == this._workSheetUIDesign.SheetName || x.Name == this._workSheetktUIFieldIncludedType.SheetName || x.Name == this._workSheetktUIGroupOrder.SheetName || x.Name == this._workSheetktUIOrder.SheetName || x.Name == this._workSheetktResources.SheetName || x.Name == this._workSheetktResourceTranslation.SheetName || x.Name == this._workSheetktResourceType.SheetName || x.Name == this._workSheetktUIPageType.SheetName || x.Name == this._workSheetQAGroups.SheetName || x.Name == this._workSheetQAktUIDesign.SheetName)) { return(true); } if (_workSheets.Any(x => x.Name == this._workSheetktExaminedGroup.SheetName || x.Name == this._workSheetktUIGroupOrder.SheetName || x.Name == this._workSheetktUIOrder.SheetName || x.Name == this._workSheetktResources.SheetName || x.Name == this._workSheetktResourceTranslation.SheetName)) { return(true); } SheetNameOk = false; return(false); } }
/// <summary> /// Получить лист по его названию. Возвращает null если такой лист не найден /// </summary> /// <param name="workbook">Рабочая книга документа</param> /// <param name="sheetName">Название листа</param> /// <returns>Рабочий лист с указанным названием или null если такой лист не найден</returns> public static Worksheet GetWorksheet(this Workbook workbook, string sheetName) { if (workbook == null) { throw new ArgumentNullException("workbook"); } if (workbook.WorkbookPart == null) { throw new InvalidDocumentStructureException(); } var rel = workbook.Descendants <Sheet>() .Where(s => s.Name.Value.Equals(sheetName)) .FirstOrDefault(); if (rel == null || rel.Id == null) { return(null); } var wsPart = workbook.WorkbookPart.GetPartById(rel.Id) as WorksheetPart; if (wsPart == null) { return(null); } return(wsPart.Worksheet); }
/// <summary> /// Gets the name of the sheet by. /// </summary> /// <param name="workbook">The workbook.</param> /// <param name="sheetName">Name of the sheet.</param> /// <returns></returns> public static DocumentFormat.OpenXml.Spreadsheet.Sheet GetSheetByName(this Workbook workbook, string sheetName) { return((from sheet in workbook.Descendants <DocumentFormat.OpenXml.Spreadsheet.Sheet>() where sheet.Name.HasValue && sheet.Name.Value.CompareTo(sheetName) == 0 select sheet).FirstOrDefault()); }
public static IEnumerable <Appointment> FromInitialDict(Workbook workbook, Dictionary <string, string> columnShiftLink, string dateCol = "A") { // Open the spreadsheet document for read-only access. // Find the sheet with the supplied name, and then use that // Sheet object to retrieve a reference to the first worksheet. int year = DateTime.Today.Year; string[] years = new[] { year.ToString(), (year + 1).ToString() }; var sheets = workbook.Descendants <Sheet>() .Where(s => years.Contains(s.Name?.Value)); List <Appointment> shiftModels = new List <Appointment>(); char[] splitters = new[] { ',', '/' }; foreach (var s in sheets) { var rows = s.GetFirstChild <SheetData>().Elements <Row>(); foreach (var row in rows) { DateTime? rowDate = null; List <Appointment> rowShifts = new List <Appointment>(); var cells = row.Elements <Cell>(); foreach (var cell in cells) { string colLetter = FromSheet.GetColLetter(cell); if (colLetter == dateCol) { if (cell.DataType.Value == CellValues.Date) { rowDate = DateTime.FromOADate(double.Parse(cell.CellValue.Text)); } else { break; } } else if (!string.IsNullOrEmpty(cell.CellValue.Text) && columnShiftLink.TryGetValue(colLetter, out string shift)) { rowShifts.Add(new Appointment { ShiftCode = shift, StaffInitials = cell.CellValue.Text.Split(splitters) }); } } if (rowDate.HasValue) { foreach (var rs in rowShifts) { rs.Date = rowDate.Value; } shiftModels.AddRange(rowShifts); } } } return(shiftModels); }
/// <summary> /// Получить информацию о существовании листа с указанным названием /// </summary> /// <param name="workbook">Рабочая книга документа</param> /// <param name="sheetName">Название листа</param> /// <returns>true если лист с таким названием существует в книге, false в обратном случае</returns> public static bool HasWorksheet(this Workbook workbook, string sheetName) { if (workbook == null) { throw new ArgumentNullException("workbook"); } if (workbook.WorkbookPart == null) { throw new InvalidDocumentStructureException(); } return(workbook.Descendants <Sheet>() .Where(s => s.Name.Value.Equals(sheetName)) .Count() > 0); }
//gavdcodeend 05 //gavdcodebegin 06 public static void ExcelOpenXmlFindAllValuesInCells() { using (SpreadsheetDocument myExcelDoc = SpreadsheetDocument.Open(@"C:\Temporary\ExcelDoc01.xlsx", false)) { WorkbookPart myWorkbookPart = myExcelDoc.WorkbookPart; Workbook myWorkbook = myWorkbookPart.Workbook; IEnumerable <Sheet> mySheets = myWorkbook.Descendants <Sheet>(); foreach (Sheet oneSheet in mySheets) { WorksheetPart myWorksheetPart = (WorksheetPart)myWorkbookPart. GetPartById(oneSheet.Id); SharedStringTablePart mySSTablePart = myWorkbookPart. SharedStringTablePart; SharedStringItem[] ssValues = mySSTablePart.SharedStringTable. Elements <SharedStringItem>().ToArray(); IEnumerable <Cell> myCells = myWorksheetPart. Worksheet.Descendants <Cell>(); foreach (Cell oneCell in myCells) { Console.Write(oneCell.CellReference); if (oneCell.DataType != null && oneCell.DataType.Value == CellValues.SharedString) { var cellIndex = int.Parse(oneCell.CellValue.Text); var cellValue = ssValues[cellIndex].InnerText; Console.Write(" - " + cellValue); } else { Console.WriteLine(" - " + oneCell.CellValue.Text); } if (oneCell.CellFormula != null) { Console.WriteLine(" - " + oneCell.CellFormula.Text); } Console.WriteLine(""); } } } }
//NumberFormatId Table //0 General //1 0 //2 0.00 //3 #,##0 //4 #,##0.00 //9 0% //10 0.00% //11 0.00E+00 //12 # ?/? //13 # ??/?? //14 d/m/yyyy //15 d-mmm-yy //16 d-mmm //17 mmm-yy //18 h:mm tt //19 h:mm:ss tt //20 H:mm //21 H:mm:ss //22 m/d/yyyy H:mm //37 #,##0 ;(#,##0) //38 #,##0 ;[Red](#,##0) //39 #,##0.00;(#,##0.00) //40 #,##0.00;[Red](#,##0.00) //45 mm:ss //46 [h]:mm:ss //47 mmss.0 //48 ##0.0E+0 //49 @ public static List <Row> RetrieveExcelRowsFromWorkSheetName(Stream stream, string workSheetName, out SharedStringTable sharedStrings, out List <CellFormats> cellFormats) { IEnumerable <Row> dataRows = new List <Row>(); using (SpreadsheetDocument document = SpreadsheetDocument.Open(stream, false)) { Workbook workBook = document.WorkbookPart.Workbook; IEnumerable <Sheet> workSheets = workBook.Descendants <Sheet>(); sharedStrings = document.WorkbookPart.SharedStringTablePart.SharedStringTable; cellFormats = new List <CellFormats>(document.WorkbookPart.WorkbookStylesPart.Stylesheet.Descendants <CellFormats>()); //Reference to Excel worksheet with order data. string id = workSheets.First(s => s.Name == workSheetName).Id; WorksheetPart sheet = (WorksheetPart)document.WorkbookPart.GetPartById(id); dataRows = from row in sheet.Worksheet.Descendants <Row>() where row.RowIndex > 0 select row; } return(dataRows.ToList()); }
/// <summary> /// Imports all data from the original Excel file /// </summary> public void ImportExcelFromFile(string path) { _workSheetktExaminedGroup = WorkSheetktExaminedGroup.Instance; _workSheetUIDesign = WorkSheetktUIDesign.Instance; _workSheetktUIFieldIncludedType = WorkSheetktUIFieldIncludedType.Instance; _workSheetktUIGroupOrder = WorkSheetktUIGroupOrder.Instance; _workSheetktUIOrder = WorkSheetktUIOrder.Instance; _workSheetktResources = WorkSheetktResources.Instance; _workSheetktResourceTranslation = WorkSheetktResourceTranslation.Instance; _workSheetktResourceType = WorkSheetktResourceType.Instance; _workSheetktUIPageType = WorkSheetUIPageType.Instance; _workSheetQAGroups = WorkSheetQAGroup.Instance; _workSheetQAktUIDesign = WorkSheetQAktUIDesign.Instance; //Check that the file is an excel file if (CheckFileType(path)) { if (CheckSheetNames(path)) { //Open the Excel workbook. using (SpreadsheetDocument document = SpreadsheetDocument.Open(path, true)) { //References to the workbook and Shared String Table. _workBook = document.WorkbookPart.Workbook; _workSheets = _workBook.Descendants <Sheet>(); _sharedStrings = document.WorkbookPart.SharedStringTablePart.SharedStringTable; //Reference to Excel Worksheet with ktExaminedGroup data. _ktExaminedID = _workSheets.First(s => s.Name == this._workSheetktExaminedGroup.SheetName).Id; _ktExaminedGroupSheet = (WorksheetPart)document.WorkbookPart.GetPartById(_ktExaminedID); //Reference to Excel Worksheet with ktUIDesign data. _ktUiDesignID = _workSheets.First(s => s.Name == this._workSheetUIDesign.SheetName).Id; _ktUiDesignSheet = (WorksheetPart)document.WorkbookPart.GetPartById(_ktUiDesignID); //Reference to Excel Worksheet with ktUIFieldIncludedTypeSheet data. _ktUiFieldID = _workSheets.First(s => s.Name == this._workSheetktUIFieldIncludedType.SheetName).Id; _ktUiFieldIncludedTypeSheet = (WorksheetPart)document.WorkbookPart.GetPartById(_ktUiFieldID); //Load ktUIFieldIncludedType data to business object. this._workSheetktUIFieldIncludedType.LoadUIFieldIncludedType(_ktUiFieldIncludedTypeSheet.Worksheet, _sharedStrings); //Reference to Excel Worksheet with ktUIFieldIncludedTypeSheet data. _ktUiGroupOrderID = _workSheets.First(s => s.Name == this._workSheetktUIGroupOrder.SheetName).Id; _ktUiGroupOrderSheet = (WorksheetPart)document.WorkbookPart.GetPartById(_ktUiGroupOrderID); //Reference to Excel Worksheet with ktUIFieldIncludedTypeSheet data. _ktUiOrderID = _workSheets.First(s => s.Name == this._workSheetktUIOrder.SheetName).Id; _ktUiOrderSheet = (WorksheetPart)document.WorkbookPart.GetPartById(_ktUiOrderID); //Load ktUIFieldIncludedType data to business object. //this._workSheetktUIOrder.LoadUIOrder(_ktUiOrderSheet.Worksheet, _sharedStrings); //Reference to Excel Worksheet with ktResource data. _ktResourcesID = _workSheets.First(s => s.Name == this._workSheetktResources.SheetName).Id; _ktResourcesSheet = (WorksheetPart)document.WorkbookPart.GetPartById(_ktResourcesID); ////Load ktResource data to business object. this._workSheetktResources.LoadktResources(_ktResourcesSheet.Worksheet, _sharedStrings); ////Reference to Excel Worksheet with ktResourceTranslation data. _ktResourceTranslationID = _workSheets.First(s => s.Name == this._workSheetktResourceTranslation.SheetName).Id; _ktResourceTranslationSheet = (WorksheetPart)document.WorkbookPart.GetPartById(_ktResourceTranslationID); ////Load ktResouceTranslation data to business object. this._workSheetktResourceTranslation.LoadktResourceTranslation(_ktResourceTranslationSheet.Worksheet, _sharedStrings); ////Reference to Excel Worksheet with ktResource data. _ktResourceTypeID = _workSheets.First(s => s.Name == this._workSheetktResourceType.SheetName).Id; _ktResourceTypeSheet = (WorksheetPart)document.WorkbookPart.GetPartById(_ktResourceTypeID); ////Load ktResource data to business object. this._workSheetktResourceType.LoadktResourceType(_ktResourceTypeSheet.Worksheet, _sharedStrings); ////Reference to Excel Worksheet with ktUIPageType data. _ktUiPageTypeID = _workSheets.First(s => s.Name == this._workSheetktUIPageType.SheetName).Id; _ktUiPageTypeSheet = (WorksheetPart)document.WorkbookPart.GetPartById(_ktUiPageTypeID); ////Load ktResource data to business object. this._workSheetktUIPageType.LoadUIPageType(_ktUiPageTypeSheet.Worksheet, _sharedStrings); ////Reference to Excel Worksheet with QAGroups data. _qaGroupsID = _workSheets.First(s => s.Name == this._workSheetQAGroups.SheetName).Id; _qaGroupsSheet = (WorksheetPart)document.WorkbookPart.GetPartById(_qaGroupsID); ////Load QAGroups data to business object. this._workSheetQAGroups.LoadQAGroups(_qaGroupsSheet.Worksheet, _sharedStrings); ////Reference to Excel Worksheet with QAGroups data. _qAktUiDesignID = _workSheets.First(s => s.Name == this._workSheetQAktUIDesign.SheetName).Id; _qAktUiDesignSheet = (WorksheetPart)document.WorkbookPart.GetPartById(_qAktUiDesignID); ////Load QAGroups data to business object. this._workSheetQAktUIDesign.LoadQAktUIDesign(_qAktUiDesignSheet.Worksheet, _sharedStrings); if (this._workSheetktExaminedGroup.LoadExaminedGroup(_ktExaminedGroupSheet.Worksheet, _sharedStrings) && this._workSheetktUIGroupOrder.LoadUIGroupOrder(_ktUiGroupOrderSheet.Worksheet, _sharedStrings) && this._workSheetUIDesign.LoadUIDesign(_ktUiDesignSheet.Worksheet, _sharedStrings) && this._workSheetktResources.LoadktResources(_ktResourcesSheet.Worksheet, _sharedStrings) && this._workSheetktResourceTranslation.LoadktResourceTranslation(_ktResourceTranslationSheet.Worksheet, _sharedStrings) && this._workSheetktUIOrder.LoadUIOrder(_ktUiOrderSheet.Worksheet, _sharedStrings) // +++ ) { _workSheetktExaminedGroup.AcceptChanges(); _workSheetUIDesign.AcceptChanges(); _workSheetktUIGroupOrder.AcceptChanges(); _workSheetktResources.AcceptChanges(); _workSheetktResourceTranslation.AcceptChanges(); _workSheetktUIOrder.AcceptChanges(); } } } } if (!FileTypeOk) { ImportFileOK = false; MessageBox.Show("This file is not an excel file with the correct (.xlsx) file extension", "Wrong file type", MessageBoxButton.OK, MessageBoxImage.Error); } if (!SheetNameOk) { ImportFileOK = false; MessageBox.Show("The configuration excel file does not contain the correct excel sheets", "Wrong excel file", MessageBoxButton.OK, MessageBoxImage.Error); } if (!_workSheetktExaminedGroup.ColumnHeadersOk || !_workSheetktUIGroupOrder.ColumnHeadersOk || !_workSheetUIDesign.ColumnHeadersOk || !_workSheetktResources.ColumnHeadersOk || !_workSheetktResourceTranslation.ColumnHeadersOk || !_workSheetktUIOrder.ColumnHeadersOk // +++ ) { ImportFileOK = false; MessageBox.Show("One or all of the excel sheet does not have the right column headers", "Wrong column names", MessageBoxButton.OK, MessageBoxImage.Error); } if (!_workSheetktExaminedGroup.DataOnSheetOk || !_workSheetktUIGroupOrder.DataOnSheetOk || !_workSheetUIDesign.DataOnSheetOk || !_workSheetktResources.ColumnHeadersOk || !_workSheetktResourceTranslation.ColumnHeadersOk || !_workSheetktUIOrder.ColumnHeadersOk // +++ ) { ImportFileOK = false; MessageBox.Show("One or all of the excel sheets does not have any data", "No data on sheet", MessageBoxButton.OK, MessageBoxImage.Error); } }
/// <summary> /// Imports all data from the original Excel file /// </summary> public void ImportExcelFromFile(string path) { _workSheetktExaminedGroup = WorkSheetktExaminedGroup.Instance; _workSheetUIDesign = WorkSheetktUIDesign.Instance; _workSheetktUIFieldIncludedType = WorkSheetktUIFieldIncludedType.Instance; _workSheetktUIGroupOrder = WorkSheetktUIGroupOrder.Instance; _workSheetktUIOrder = WorkSheetktUIOrder.Instance; _workSheetktResources = WorkSheetktResources.Instance; _workSheetktResourceTranslation = WorkSheetktResourceTranslation.Instance; _workSheetktResourceType = WorkSheetktResourceType.Instance; _workSheetktUIPageType = WorkSheetUIPageType.Instance; _workSheetQAGroups = WorkSheetQAGroup.Instance; _workSheetQAktUIDesign = WorkSheetQAktUIDesign.Instance; //Check that the file is an excel file if (CheckFileType(path)) { if (CheckSheetNames(path)) { //Open the Excel workbook. using (SpreadsheetDocument document = SpreadsheetDocument.Open(path, true)) { //References to the workbook and Shared String Table. _workBook = document.WorkbookPart.Workbook; _workSheets = _workBook.Descendants<Sheet>(); _sharedStrings = document.WorkbookPart.SharedStringTablePart.SharedStringTable; //Reference to Excel Worksheet with ktExaminedGroup data. _ktExaminedID = _workSheets.First(s => s.Name == this._workSheetktExaminedGroup.SheetName).Id; _ktExaminedGroupSheet = (WorksheetPart)document.WorkbookPart.GetPartById(_ktExaminedID); //Reference to Excel Worksheet with ktUIDesign data. _ktUiDesignID = _workSheets.First(s => s.Name == this._workSheetUIDesign.SheetName).Id; _ktUiDesignSheet = (WorksheetPart)document.WorkbookPart.GetPartById(_ktUiDesignID); //Reference to Excel Worksheet with ktUIFieldIncludedTypeSheet data. _ktUiFieldID = _workSheets.First(s => s.Name == this._workSheetktUIFieldIncludedType.SheetName).Id; _ktUiFieldIncludedTypeSheet = (WorksheetPart)document.WorkbookPart.GetPartById(_ktUiFieldID); //Load ktUIFieldIncludedType data to business object. this._workSheetktUIFieldIncludedType.LoadUIFieldIncludedType(_ktUiFieldIncludedTypeSheet.Worksheet, _sharedStrings); //Reference to Excel Worksheet with ktUIFieldIncludedTypeSheet data. _ktUiGroupOrderID = _workSheets.First(s => s.Name == this._workSheetktUIGroupOrder.SheetName).Id; _ktUiGroupOrderSheet = (WorksheetPart)document.WorkbookPart.GetPartById(_ktUiGroupOrderID); //Reference to Excel Worksheet with ktUIFieldIncludedTypeSheet data. _ktUiOrderID = _workSheets.First(s => s.Name == this._workSheetktUIOrder.SheetName).Id; _ktUiOrderSheet = (WorksheetPart)document.WorkbookPart.GetPartById(_ktUiOrderID); //Load ktUIFieldIncludedType data to business object. //this._workSheetktUIOrder.LoadUIOrder(_ktUiOrderSheet.Worksheet, _sharedStrings); //Reference to Excel Worksheet with ktResource data. _ktResourcesID = _workSheets.First(s => s.Name == this._workSheetktResources.SheetName).Id; _ktResourcesSheet = (WorksheetPart)document.WorkbookPart.GetPartById(_ktResourcesID); ////Load ktResource data to business object. this._workSheetktResources.LoadktResources(_ktResourcesSheet.Worksheet, _sharedStrings); ////Reference to Excel Worksheet with ktResourceTranslation data. _ktResourceTranslationID = _workSheets.First(s => s.Name == this._workSheetktResourceTranslation.SheetName).Id; _ktResourceTranslationSheet = (WorksheetPart)document.WorkbookPart.GetPartById(_ktResourceTranslationID); ////Load ktResouceTranslation data to business object. this._workSheetktResourceTranslation.LoadktResourceTranslation(_ktResourceTranslationSheet.Worksheet, _sharedStrings); ////Reference to Excel Worksheet with ktResource data. _ktResourceTypeID = _workSheets.First(s => s.Name == this._workSheetktResourceType.SheetName).Id; _ktResourceTypeSheet = (WorksheetPart)document.WorkbookPart.GetPartById(_ktResourceTypeID); ////Load ktResource data to business object. this._workSheetktResourceType.LoadktResourceType(_ktResourceTypeSheet.Worksheet, _sharedStrings); ////Reference to Excel Worksheet with ktUIPageType data. _ktUiPageTypeID = _workSheets.First(s => s.Name == this._workSheetktUIPageType.SheetName).Id; _ktUiPageTypeSheet = (WorksheetPart)document.WorkbookPart.GetPartById(_ktUiPageTypeID); ////Load ktResource data to business object. this._workSheetktUIPageType.LoadUIPageType(_ktUiPageTypeSheet.Worksheet, _sharedStrings); ////Reference to Excel Worksheet with QAGroups data. _qaGroupsID = _workSheets.First(s => s.Name == this._workSheetQAGroups.SheetName).Id; _qaGroupsSheet = (WorksheetPart)document.WorkbookPart.GetPartById(_qaGroupsID); ////Load QAGroups data to business object. this._workSheetQAGroups.LoadQAGroups(_qaGroupsSheet.Worksheet, _sharedStrings); ////Reference to Excel Worksheet with QAGroups data. _qAktUiDesignID = _workSheets.First(s => s.Name == this._workSheetQAktUIDesign.SheetName).Id; _qAktUiDesignSheet = (WorksheetPart)document.WorkbookPart.GetPartById(_qAktUiDesignID); ////Load QAGroups data to business object. this._workSheetQAktUIDesign.LoadQAktUIDesign(_qAktUiDesignSheet.Worksheet, _sharedStrings); if (this._workSheetktExaminedGroup.LoadExaminedGroup(_ktExaminedGroupSheet.Worksheet, _sharedStrings) && this._workSheetktUIGroupOrder.LoadUIGroupOrder(_ktUiGroupOrderSheet.Worksheet, _sharedStrings) && this._workSheetUIDesign.LoadUIDesign(_ktUiDesignSheet.Worksheet, _sharedStrings) && this._workSheetktResources.LoadktResources(_ktResourcesSheet.Worksheet, _sharedStrings) && this._workSheetktResourceTranslation.LoadktResourceTranslation(_ktResourceTranslationSheet.Worksheet, _sharedStrings) && this._workSheetktUIOrder.LoadUIOrder(_ktUiOrderSheet.Worksheet, _sharedStrings) // +++ ) { _workSheetktExaminedGroup.AcceptChanges(); _workSheetUIDesign.AcceptChanges(); _workSheetktUIGroupOrder.AcceptChanges(); _workSheetktResources.AcceptChanges(); _workSheetktResourceTranslation.AcceptChanges(); _workSheetktUIOrder.AcceptChanges(); } } } } if (!FileTypeOk) { ImportFileOK = false; MessageBox.Show("This file is not an excel file with the correct (.xlsx) file extension", "Wrong file type", MessageBoxButton.OK, MessageBoxImage.Error); } if (!SheetNameOk) { ImportFileOK = false; MessageBox.Show("The configuration excel file does not contain the correct excel sheets", "Wrong excel file", MessageBoxButton.OK, MessageBoxImage.Error); } if (!_workSheetktExaminedGroup.ColumnHeadersOk || !_workSheetktUIGroupOrder.ColumnHeadersOk || !_workSheetUIDesign.ColumnHeadersOk || !_workSheetktResources.ColumnHeadersOk || !_workSheetktResourceTranslation.ColumnHeadersOk || !_workSheetktUIOrder.ColumnHeadersOk // +++ ) { ImportFileOK = false; MessageBox.Show("One or all of the excel sheet does not have the right column headers", "Wrong column names", MessageBoxButton.OK, MessageBoxImage.Error); } if (!_workSheetktExaminedGroup.DataOnSheetOk || !_workSheetktUIGroupOrder.DataOnSheetOk || !_workSheetUIDesign.DataOnSheetOk || !_workSheetktResources.ColumnHeadersOk || !_workSheetktResourceTranslation.ColumnHeadersOk || !_workSheetktUIOrder.ColumnHeadersOk // +++ ) { ImportFileOK = false; MessageBox.Show("One or all of the excel sheets does not have any data", "No data on sheet", MessageBoxButton.OK, MessageBoxImage.Error); } }
/// <summary> /// Check if the excel file contains the correct sheetnames /// </summary> /// <param name="path">Path to the excel file</param> /// <returns></returns> public bool CheckSheetNames(string path) { using (SpreadsheetDocument document = SpreadsheetDocument.Open(path, true)) { //References to the workbook _workBook = document.WorkbookPart.Workbook; _workSheets = _workBook.Descendants<Sheet>(); if (_workSheets.Any(x => x.Name == this._workSheetktExaminedGroup.SheetName || x.Name == this._workSheetUIDesign.SheetName || x.Name == this._workSheetktUIFieldIncludedType.SheetName || x.Name == this._workSheetktUIGroupOrder.SheetName || x.Name == this._workSheetktUIOrder.SheetName || x.Name == this._workSheetktResources.SheetName || x.Name == this._workSheetktResourceTranslation.SheetName || x.Name == this._workSheetktResourceType.SheetName || x.Name == this._workSheetktUIPageType.SheetName || x.Name == this._workSheetQAGroups.SheetName || x.Name == this._workSheetQAktUIDesign.SheetName)) { return true; } if (_workSheets.Any(x => x.Name == this._workSheetktExaminedGroup.SheetName || x.Name == this._workSheetktUIGroupOrder.SheetName || x.Name == this._workSheetktUIOrder.SheetName || x.Name == this._workSheetktResources.SheetName || x.Name == this._workSheetktResourceTranslation.SheetName)) { return true; } SheetNameOk = false; return false; } }
public void GetChart(string fileName) { string txt; //string fileName = @"D:\c#file\excelfile\test1.xlsx"; using (SpreadsheetDocument doc = SpreadsheetDocument.Open(fileName, false)) { //Console.WriteLine("*********"+ doc.WorkbookPart); WorkbookPart bkPart = doc.WorkbookPart; Workbook workbook = bkPart.Workbook; //Console.WriteLine(workbook.Ancestors()); //Sheet s = workbook.Descendants<Sheet>().Where(sht => sht.Name == "Sheet1").FirstOrDefault(); //Sheet s = workbook.Descendants<Sheet>().Where(sht => sht.Name == "Razem").FirstOrDefault(); //IEnumerable s1 = workbook.Descendants<Sheet>(); //Console.WriteLine(workbook.Descendants<Sheet>()); Sheet s = workbook.Descendants <Sheet>().FirstOrDefault(); //Console.WriteLine(s); //Console.WriteLine(s.Id); //Console.WriteLine("$$$$$$$"); WorksheetPart wsPart = (WorksheetPart)bkPart.GetPartById(s.Id); DrawingsPart dp = (DrawingsPart)wsPart.DrawingsPart; /* * Console.WriteLine("$$$$$$$"); * Console.WriteLine(dp.ChartParts); * Console.WriteLine("$$$$$$$"); * foreach (var chartPart in dp.ChartParts) { * Console.WriteLine(chartPart.ChartColorStyleParts); * Console.WriteLine("$$$$$$$"); * }*/ WorksheetDrawing dWs = dp.WorksheetDrawing; Console.WriteLine("The count of the charts is : " + dWs.ChildElements.Count); //Console.WriteLine(dWs.ChildElements[1]); //Console.WriteLine(dWs); Console.WriteLine("**********************"); //Console.WriteLine(dWs.Descendants()); txt = dWs.Descendants <A.Spreadsheet.NonVisualDrawingProperties>().FirstOrDefault().Name; Console.WriteLine("The name(not title) of the charts is : " + txt); Console.WriteLine(dWs.Descendants <A.Spreadsheet.NonVisualDrawingProperties>().Count()); //Console.WriteLine(dWs.Descendants<A.Spreadsheet.NonVisualDrawingProperties>().FirstOrDefault().ChildElements + "############"); txt = dWs.Descendants <A.Spreadsheet.NonVisualDrawingProperties>().ElementAtOrDefault(0).Name; //Console.WriteLine(txt ); Console.WriteLine("**********************"); //Console.WriteLine(dp.ChartParts.Count()); ChartPart cp = dp.ChartParts.FirstOrDefault(); Console.WriteLine("the chart space language is :" + cp.ChartSpace.EditingLanguage.Val); Console.WriteLine("**********************"); //Console.WriteLine("the XXXXX is :" + cp.ChartSpace.RoundedCorners); A.Charts.ChartShapeProperties cs = cp.ChartSpace.Descendants <A.Charts.ChartShapeProperties>().FirstOrDefault(); //Console.WriteLine("the property of the Charts :" ); //Console.WriteLine(cs.LocalName); A.Charts.AxisDataSourceType adst = cp.ChartSpace.Descendants <A.Charts.AxisDataSourceType>().FirstOrDefault(); Console.Write("the reference of the catagory is :"); //Console.WriteLine(adst.StringReference.Formula.InnerText); // if there is a reference Console.WriteLine("the reference of the Number is :"); //Console.WriteLine(adst.NumberReference.Formula.InnerText); // if there is a reference Console.WriteLine("**********************"); A.Charts.CategoryAxis ca = cp.ChartSpace.Descendants <A.Charts.CategoryAxis>().FirstOrDefault(); Console.WriteLine("the title of the Category axix is :"); Console.WriteLine(ca.Title.InnerText); A.Charts.ValueAxis va = cp.ChartSpace.Descendants <A.Charts.ValueAxis>().FirstOrDefault(); Console.WriteLine("the title of the Value axix is :"); Console.WriteLine(va.Title.InnerText); A.Charts.Chart c = (A.Charts.Chart)cp.ChartSpace.Descendants <A.Charts.Chart>().FirstOrDefault(); //Console.WriteLine(c.LocalName); Console.WriteLine("**********************"); Console.WriteLine("the title of the chart is :" + c.Title.InnerText); //Console.WriteLine( c.LocalName); //Console.WriteLine("chart title is :" + c.Title.InnerXml); //Console.WriteLine("chart title is :" + c.Title.ChartText.RichText.InnerText); Console.WriteLine("**********************"); Console.WriteLine("the type of the chart is :" + c.PlotArea.ChildElements[1].LocalName.ToString()); Console.WriteLine("**********************"); Console.WriteLine("the other of the chart is :" + c.PlotArea.LocalName); //Console.WriteLine("the type of the chart is :" + c.PlotArea.ChildElements[1].ChildElements.Count); //Console.WriteLine(txt + "############"); //Console.WriteLine(c.PlotArea.ChildElements[2].LocalName.ToString() + "############"); //Console.WriteLine(c.PlotArea.ChildElements[3].LocalName.ToString()); //Console.WriteLine(c.PlotArea.ChildElements[4].LocalName.ToString()); //Console.WriteLine(c.PlotArea.ChildElements[5].LocalName.ToString()); Console.ReadKey(); //this.txtMessages.Text = txt; } }