/// <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;
}
}
