/// <summary>If the user has specified that there is a header row, remap the class properties based /// on the columns in the row</summary> /// <param name="headerRow">The top (first) row with header text</param> /// <param name="columns">Information about the property that corresponds to the column (cell)</param> /// <param name="sst"></param> private void RemapExcelColumnLetters(Row headerRow, List <ClassToExcelColumn> columns, SharedStringTable sst) { // Erase all existing column letters foreach (var column in columns) { column.ExcelColumnLetter = string.Empty; } // Assign new column letter foreach (Cell oneCell in headerRow.Elements <Cell>()) { if (oneCell.CellValue == null) { continue; } string columnName = GetCellText(oneCell, sst); ClassToExcelColumn column = columns.FirstOrDefault(w => string.CompareOrdinal(w.ColumnName, columnName) == 0); if (column != null) { column.ExcelColumnLetter = GetColumnLetter(oneCell); } } // Log problems foreach (var column in columns) { if (string.IsNullOrWhiteSpace(column.ExcelColumnLetter) && column.IsOptional == false) { LogMessage(new ClassToExcelMessage(ClassToExcelMessageType.HeaderProblem, 1, column, "Could not find column in the header row!")); } } }
/// <summary>Use PropertyInfo and column information assign a property a value based on incoming cell data.</summary> /// <param name="newItem">The entire object</param> /// <param name="column">Information about the property that corresponds to the column (cell)</param> /// <param name="oneCell">A single OpenXML cell with data in it</param> /// <param name="sst">The shared strings table</param> /// <param name="rowIndex">The row number</param> private void AssignValue(T newItem, ClassToExcelColumn column, Cell oneCell, SharedStringTable sst, int rowIndex) { if (column == null || column.Property == null || oneCell.CellValue == null) { return; } string stringValue = GetCellText(oneCell, sst); if (string.IsNullOrEmpty(stringValue)) { return; } var resultType = _stringToPropertyConverter.AssignValue(column.Property, newItem, stringValue); if (resultType == StringToPropertyConverterEnum.Error || resultType == StringToPropertyConverterEnum.Warning) { var classToExcelMessageType = resultType == StringToPropertyConverterEnum.Error ? ClassToExcelMessageType.Error : ClassToExcelMessageType.Warning; LogMessage(new ClassToExcelMessage(classToExcelMessageType, rowIndex, column, _stringToPropertyConverter.LastMessage)); } }
/// <summary>Finds or creates a style index. Style indices point to the INDEX position of a cell format. In other words, /// if I return 5 from this method, it is the 6th element (yes, 6 it is zero based array) in the CellFormats array that will tell Excel /// how to format the column.</summary> private uint FindOrCreateStyleIndex(ClassToExcelColumn column) { uint styleIndex = 0; // There are a bunch of built in numeric (includes dates) formatting styles in Excel. Look to see if the user is trying // to use one of them. If they are, we don't need to create a custome numeric format; otherwise, we do. // Creating numeric formats helpful links: // http://stackoverflow.com/questions/16607989/numbering-formats-in-openxml-c-sharp uint numberFormatId = GetStandardNumericStyle(column.StyleFormat); if (numberFormatId == NumberFormatDoesNotExist) { for (int i = 0; i < _spreadSheet.WorkbookPart.WorkbookStylesPart.Stylesheet.NumberingFormats.ChildElements.Count; i++) { var data = (NumberingFormat)_spreadSheet.WorkbookPart.WorkbookStylesPart.Stylesheet.NumberingFormats.ChildElements[i]; if (data.FormatCode == column.StyleFormat) { numberFormatId = data.NumberFormatId; break; } } if (numberFormatId == NumberFormatDoesNotExist) { numberFormatId = _numberFormatId++; var newNumberingFormat = new NumberingFormat { NumberFormatId = UInt32Value.FromUInt32(numberFormatId), FormatCode = StringValue.FromString(column.StyleFormat) }; _spreadSheet.WorkbookPart.WorkbookStylesPart.Stylesheet.NumberingFormats.AppendChild(newNumberingFormat); } } // Ok, at this point we know the numberFormatId. Is there already an existing CellFormat record point to it? // If so, great return its index in the CellFormats array; otherwise, create it. bool foundCellFormat = false; for (int i = 0; i < _spreadSheet.WorkbookPart.WorkbookStylesPart.Stylesheet.CellFormats.ChildElements.Count; i++) { var data = (CellFormat)_spreadSheet.WorkbookPart.WorkbookStylesPart.Stylesheet.CellFormats.ChildElements[i]; if (data.NumberFormatId != null && data.NumberFormatId.Value == numberFormatId) { styleIndex = (uint)i; foundCellFormat = true; break; } } if (foundCellFormat == false) { var newCellFormat = new CellFormat { NumberFormatId = numberFormatId, ApplyNumberFormat = true }; _spreadSheet.WorkbookPart.WorkbookStylesPart.Stylesheet.CellFormats.AppendChild(newCellFormat); styleIndex = (uint)_spreadSheet.WorkbookPart.WorkbookStylesPart.Stylesheet.CellFormats.ChildElements.Count - 1; } return(styleIndex); }
/// <summary>Using reflection, create the columns and pull the attribute information off the class so /// that we know how to format the columns later.</summary> protected List <ClassToExcelColumn> CreateColumns(string worksheetName, Type typeOfClass) { List <ClassToExcelColumn> columns = new List <ClassToExcelColumn>(); foreach (PropertyInfo property in typeOfClass.GetProperties()) { if ((property.PropertyType != typeof(string) && property.PropertyType.IsClass) || property.PropertyType.IsArray) { continue; } if (property.CanWrite == false) { LogMessage(ClassToExcelMessageType.Info, string.Format("Ignoring property because it is read-only: {0}", property.Name)); continue; } var newData = new ClassToExcelColumn { ColumnName = property.Name, Order = 99999, Property = property }; // If the property has a dislay name attribute, use it as the column name. object firstAttribute = property.GetCustomAttributes(typeof(ClassToExcelAttribute), false).FirstOrDefault(); if (firstAttribute != null) { ClassToExcelAttribute displayAttribute = firstAttribute as ClassToExcelAttribute; if (displayAttribute != null) { if (displayAttribute.Ignore) { continue; // do not add this column } // Does it have a column name? if (string.IsNullOrWhiteSpace(displayAttribute.ColumnName) == false) { newData.ColumnName = displayAttribute.ColumnName; } // Is it optional? newData.IsOptional = displayAttribute.IsOptional; // Does it have an order? if (displayAttribute.Order > 0) { newData.Order = displayAttribute.Order; } // Is it a number, date or boolean? if (property.PropertyType != typeof(string)) { if (property.PropertyType == typeof(int) || property.PropertyType == typeof(int?)) { newData.IsInteger = true; } else if (property.PropertyType == typeof(double) || property.PropertyType == typeof(double?)) { newData.IsDouble = true; } else if (property.PropertyType == typeof(decimal) || property.PropertyType == typeof(decimal?)) { newData.IsDecimal = true; } else if (property.PropertyType == typeof(DateTime) || property.PropertyType == typeof(DateTime?)) { newData.IsDate = true; } else if (property.PropertyType == typeof(bool) || property.PropertyType == typeof(bool?)) { newData.IsBoolean = true; } } // Any special styling? if (string.IsNullOrWhiteSpace(displayAttribute.StyleFormat) == false && (newData.IsNumber() || newData.IsDate)) { newData.StyleFormat = displayAttribute.StyleFormat.Trim(); } } } columns.Add(newData); } columns = columns.OrderBy(o => o.Order).ThenBy(o => o.ColumnName).ToList(); for (int i = 1; i <= columns.Count; i++) { columns[i - 1].ExcelColumnLetter = GetExcelColumnName(i); } if (WorksheetColumns.ContainsKey(worksheetName)) { WorksheetColumns[worksheetName] = columns.OrderBy(o => o.Order).ToList(); } else { WorksheetColumns.Add(worksheetName, columns); } return(WorksheetColumns[worksheetName]); }
/// <summary>Reads data from a work sheet.</summary> /// <param name="dataStream">The stream that contains the worksheet</param> /// <param name="worksheetName">The worksheet name/tab that you want to read.</param> /// <param name="hasHeaderRow">Indicates if the first row in the worksheet is a header row. If it does, we will use it /// to determine how things should map to the class; otherwise, we will just assue the class is indexed properly with /// the ExcelMappperAtttribute (Order property) and try to pull the data out that way.</param> /// <param name="startRow">Starting row. This is a ONE based number, but keep in mind that if hasHeaderRow is true, the header row IS row 1. /// If the value is null, it is not used.</param> /// <param name="endRow">Ending row. This is a ONE based number, but keep in mind that if hasHeaderRow is true, the header row IS row 1. /// If the value is null, it is not used.</param> /// <returns>List of objects</returns> public List <T> ReadWorksheet(Stream dataStream, string worksheetName, bool hasHeaderRow = true, int?startRow = null, int?endRow = null) { var result = new List <T>(); if (dataStream == null) { throw new ArgumentException("You must specify data!"); } if (string.IsNullOrEmpty(worksheetName)) { throw new ArgumentException("You must specify a worksheet name!"); } List <ClassToExcelColumn> columns = CreateColumns(worksheetName, typeof(T)); //Open the Excel file using (SpreadsheetDocument doc = SpreadsheetDocument.Open(dataStream, false)) { WorkbookPart workbookPart = doc.WorkbookPart; SharedStringTablePart sstpart = workbookPart.GetPartsOfType <SharedStringTablePart>().FirstOrDefault(); SharedStringTable sst = sstpart != null ? sstpart.SharedStringTable : null; Sheet theSheet = workbookPart.Workbook.Descendants <Sheet>().FirstOrDefault(s => s.Name == worksheetName); if (theSheet == null) { return(result); } WorksheetPart theWorksheetPart = (WorksheetPart)workbookPart.GetPartById(theSheet.Id); Worksheet theWorksheet = theWorksheetPart.Worksheet; var rows = theWorksheet.Descendants <Row>(); //Loop through the Worksheet rows. int rowIndex = 0; foreach (Row oneRow in rows) { rowIndex++; // Use the first row to add columns to DataTable. if (hasHeaderRow && rowIndex == 1) { RemapExcelColumnLetters(oneRow, columns, sst); } if (startRow.HasValue && startRow.Value > rowIndex) { continue; } if (hasHeaderRow == false || rowIndex > 1) { //Add rows to DataTable. var newItem = new T(); foreach (Cell oneCell in oneRow.Elements <Cell>()) { string columnLetter = GetColumnLetter(oneCell); ClassToExcelColumn column = columns.FirstOrDefault(w => w.ExcelColumnLetter == columnLetter); AssignValue(newItem, column, oneCell, sst, rowIndex); } result.Add(newItem); } if (endRow.HasValue && endRow.Value == rowIndex) { break; } } } return(result); }