private static Cell GetCell(Worksheet worksheet, string columnName, uint rowIndex) { SheetData sheetData = worksheet.GetFirstChild <SheetData>(); return(sheetData?.Elements <Row>().Where(r => r.RowIndex == rowIndex).FirstOrDefault() ?.Elements <Cell>().Where(c => c.CellReference.Value == columnName + rowIndex).FirstOrDefault()); }
public static Row GetRow(SheetData sheetData, int rowIndex) { try { return sheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).First(); } catch { return null; } }
private static Dictionary<int, string> GetHeaders(SheetData sheetData, WorkbookPart workbookPart) { var headers = new Dictionary<int, string>(); var headerCells = sheetData.Elements<Row>().First().Elements<Cell>().ToList(); for (var i = 0; i < headerCells.Count; i++) { var cellValue = ExcelFileInceptor.GetCellValueAsString(workbookPart, headerCells[i]); headers.Add(i, cellValue); } return headers; }
/// <summary> /// 移除冗余的行 /// </summary> /// <param name="sheetData"><see cref="SheetData"/>对象</param> private void RemoveRedundancyRows(SheetData sheetData) { uint ri = TemplateRowIndexContent; if (UseTemplate && ri > 0) { Row[] rows = sheetData.Elements <Row>().ToArray(); int count = rows.Length; int i = (int)ri; for (; i < count; i++) { rows[i].Remove(); } } }
public Row GetRow(string sheetName, uint RowNo) { Row res = null; if (!string.IsNullOrEmpty(sheetName)) { SheetData sheetdata = GetSheetData(sheetName); var rows = sheetdata.Elements <Row>().Where(x => x.RowIndex == RowNo); if (rows.Count() > 0) { res = rows.First(); } } return(res); }
// Return the row at the specified rowIndex located within // the sheet data passed in via wsData. If the row does not // exist, create it. private Row GetRow(SheetData wsData, UInt32 rowIndex) { var row = wsData.Elements <Row>(). Where(r => r.RowIndex.Value == rowIndex).FirstOrDefault(); if (row == null) { row = new Row { RowIndex = rowIndex }; wsData.Append(row); } return(row); }
private static string GetValueCell(this SheetData sheetData, WorkbookPart workBookPart, int rowIndex, int colIndex) { Row row = sheetData.Elements <Row>().Where(r => r.RowIndex == rowIndex).First(); string rCell = colIndex.GetColumnName() + rowIndex; Cell cell = row.Elements <Cell>().Where(c => string.Compare(c.CellReference.Value, rCell, true) == 0).First(); if (cell.DataType != null && cell.DataType.Value.Equals(CellValues.SharedString)) { SharedStringItem item = GetSharedStringItemById(workBookPart, Convert.ToInt32(cell.CellValue.Text)); return(item.InnerText); } return(cell.CellValue == null ? null : cell.CellValue.Text); }
public Cell GetCellByContent(string sheet, string content, int rowindex) { WorksheetPart currentSheet = excelData.GetWorksheetPartByName(sheet); SheetData sd = currentSheet.Worksheet.Elements <SheetData>().FirstOrDefault(); Row row = sd.Elements <Row>().ElementAt(rowindex); Cell result = row.Elements <Cell>().Where( c => content.Equals(GetCellText(c)) ).FirstOrDefault(); if (result == null) { throw new CellNotFoundException("Impossibile trovare la cella " + content); } return(result); }
private Row GetRow(UInt32 index) // zero-based { Row row = null; SheetData sheetData = worksheet.GetFirstChild <SheetData>(); foreach (Row r in sheetData.Elements <Row>()) { if (r.RowIndex == (index + 1)) { row = r; break; } } return(row); }
/// <summary> /// This function reads Excel file and returns dictionary /// </summary> /// <param name="filePath">Full path to Excel file</param> /// <param name="sheetName">Sheet name containing configurations</param> /// <returns>Dictionary of configurations</returns> public static Dictionary <string, string> ReadFromExcel(string filePath, string sheetName) { if (!(string.IsNullOrWhiteSpace(filePath) || string.IsNullOrWhiteSpace(sheetName))) { Dictionary <string, string> myList = new Dictionary <string, string>(); try { using (SpreadsheetDocument myDoc = SpreadsheetDocument.Open(filePath, false)) { WorkbookPart workbookPart = myDoc.WorkbookPart; IEnumerable <Sheet> sheets = myDoc.WorkbookPart.Workbook.GetFirstChild <Sheets>().Elements <Sheet>().Where(s => s.Name == sheetName); if (sheets.Count() == 0) { throw new ArgumentException("Message: Provided Sheet name is incorrect"); } string relationshipId = sheets.First().Id.Value; WorksheetPart worksheetPart = (WorksheetPart)myDoc.WorkbookPart.GetPartById(relationshipId); SheetData sheetData = worksheetPart.Worksheet.GetFirstChild <SheetData>(); foreach (Row row in sheetData.Elements <Row>()) { List <string> rowValue = new List <string>(); rowValue = ReadRow(row, workbookPart).ToList(); if (null != rowValue && 0 < rowValue.Count()) // Check count to avoid adding of blank row entry which having meta data { myList.Add(rowValue[0].Trim(), rowValue[1].Trim()); } } } } catch (FileNotFoundException exception) { ErrorLogger.LogErrorToTextFile(errorFilePath, "Invalid file path, file not found" + exception.Message); } catch (Exception exception) { if (exception.Message.IndexOf("Invalid Hyperlink", 0, StringComparison.OrdinalIgnoreCase) != -1) { ErrorLogger.DisplayErrorMessage("Unable to read Excel. Please remove all hyperlinks from configuration Excel"); } ErrorLogger.LogErrorToTextFile(errorFilePath, "Exception Details: " + exception.Message); } return(myList); } else { throw new ArgumentException("Message: FilePath or Sheet name missing"); } }
/// <summary> /// Gets the row. /// </summary> /// <param name="sheetData">The sheet data.</param> /// <param name="rowIndex">Index of the row.</param> /// <param name="create">if set to <c>true</c> [create].</param> /// <returns></returns> public static Row GetRow(this SheetData sheetData, int rowIndex, bool create = false) { var row = sheetData.Elements <Row>().FirstOrDefault(r => r.RowIndex == rowIndex); if (row == null) { if (!create) { return(null); } row = new Row(); sheetData.AppendChild(row); } return(row); }
/// <summary> /// Given a worksheet's data it returns the largest column index for any rows. /// </summary> /// <param name="sheetData">Worksheet's data</param> /// <returns>The largest column index found</returns> private static int GetLargestColumnIndex(SheetData sheetData) { var result = 1; foreach (var row in sheetData.Elements <Row>()) { var lastCell = row.LastChild as Cell; var current = GetColumnIndex(lastCell.CellReference); if (current > result) { result = current; } } return(result); }
/// <summary> /// Gets the row. /// </summary> /// <param name="sheetData">The sheet data.</param> /// <param name="cellReference">The cell reference.</param> /// <param name="create">if set to <c>true</c> [create].</param> /// <returns></returns> public static Row GetRow(this SheetData sheetData, string cellReference, bool create = false) { var row = sheetData.Elements <Row>().FirstOrDefault(r => cellReference.EndsWith(r.RowIndex?.ToString())); if (row == null) { if (!create) { return(null); } row = new Row(); sheetData.AppendChild(row); } return(row); }
// Given a worksheet and a row index, return the row. // See: http://msdn.microsoft.com/en-us/library/bb508943(v=office.12).aspx#Y2142 private static Row getRow(SheetData worksheet, uint rowIndex, bool autoCreate) { if (rowIndex < 1) { throw new Exception("The row # can't be less then 1."); } Row foundRow = worksheet.Elements <Row>().Where(r => r.RowIndex == rowIndex).FirstOrDefault(); if (foundRow == null && autoCreate) { foundRow = new Row(); foundRow.RowIndex = rowIndex; worksheet.AppendChild(foundRow); } return(foundRow); }
public static List <ItemArquivo> Ler(string arquivoXlsx) { var arquivoByte = Convert.FromBase64String(arquivoXlsx); MemoryStream documento = new MemoryStream(arquivoByte); List <ItemArquivo> itensArquivo = new List <ItemArquivo>(); using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(documento, false)) { WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart; WorksheetPart worksheetPart = workbookPart.WorksheetParts.First(); SheetData sheetData = worksheetPart.Worksheet.Elements <SheetData>().First(); var stringTable = workbookPart.GetPartsOfType <SharedStringTablePart>().FirstOrDefault(); foreach (Row r in sheetData.Elements <Row>()) { if (Convert.ToInt32(r.RowIndex.InnerText) == 1) { continue; } var listaElementos = r.Elements <Cell>().ToList(); var dataEntrega = DateTime.FromOADate(Convert.ToDouble(listaElementos[0].CellValue.InnerText)); var nomeProduto = listaElementos[1].CellValue.InnerText; if (stringTable != null) { nomeProduto = stringTable.SharedStringTable .ElementAt(int.Parse(nomeProduto)).InnerText; } var quntidade = Convert.ToInt32(listaElementos[2].CellValue.InnerText); var valorUnitario = Convert.ToDecimal(listaElementos[3].CellValue.InnerText); itensArquivo.Add(new ItemArquivo { DataEntrega = dataEntrega, NomeProduto = nomeProduto, Quantidade = quntidade, ValorUnitario = valorUnitario, LinhaImpportada = Convert.ToInt32(r.RowIndex.InnerText) }); } } return(itensArquivo); }
public static Row GetRow(SheetData sheetData, uint line, bool createIfDoesntExists = false) { var row = sheetData?.Elements <Row>().FirstOrDefault(r => r.RowIndex == line); // Se existir a linha retorna. if (row != null) { row.Spans = null; return(row); } else if (!createIfDoesntExists) { return(null); } // Senao cria uma nova linha. row = new Row { RowIndex = line }; var rows = sheetData.Elements <Row>().ToList(); // Caso nao exista linhas pode simplesmente inserir if (!rows.Any()) { sheetData.AppendChild(row); return(row); } // Insere a linha na order correta. var before = rows.Where(w => w.RowIndex < line).OrderBy(o => o.RowIndex.Value).LastOrDefault(); if (before != null) // Existem linhas anteriores a que sera inserida. { sheetData.InsertAfter(row, before); } else // Nao existem nenhuma linha anterior a que sera inserida. { var after = rows.Where(w => w.RowIndex > line).OrderBy(o => o.RowIndex.Value).FirstOrDefault(); // Insere antes do primeiro. sheetData.InsertBefore(row, after); } return(row); }
private void MoveCurrentRow(int rowIndex) { if (currentRow == null) { var rows = currentSheetData.Elements <Row>(); if (rows.GetEnumerator().MoveNext()) { //maybe the last row is still before the curretRow, so in this case no need to iterate trough the rows Row lastRow = (Row)currentSheetData.LastChild; if (lastRow.RowIndex > rowIndex) { //we search from the beginnig foreach (Row item in rows) { if (item.RowIndex.Value > rowIndex) { currentRow = item; break; } } } } } else { //trying to seach from the last position backward or forward if (currentRow.RowIndex > rowIndex) { //going backward Row previousRow; while ((previousRow = currentRow.PreviousSibling <Row>()) != null && previousRow.RowIndex > rowIndex) { currentRow = previousRow; } } else { //going forward while (currentRow != null && currentRow.RowIndex <= rowIndex) { currentRow = currentRow.NextSibling <Row>(); } } } }
private static void ParseViaDOM(string physicalPath) { using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(physicalPath, false)) { WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart; WorksheetPart worksheetPart = workbookPart.WorksheetParts.First(); string text = ""; foreach (WorksheetPart wsp in workbookPart.WorksheetParts) { SheetData sheetData = wsp.Worksheet.Elements <SheetData>().First(); foreach (Row r in sheetData.Elements <Row>()) { foreach (Cell c in r.Elements <Cell>()) { if (c != null && c.DataType != null && c.DataType == CellValues.SharedString) { int id = -1; Int32.TryParse(c.InnerText, out id); var item = GetSharedStringItemById(workbookPart, id); if (item.Text != null) { text = item.Text.Text; } else if (item.InnerText != null) { text = item.InnerText; } else if (item.InnerXml != null) { text = item.InnerXml; } System.Diagnostics.Debug.WriteLine(text + ""); } else { text = c.CellValue.Text; Console.Write(text + " "); System.Diagnostics.Debug.WriteLine(text + ""); } } } } } }
public List <Cell> FindCellsWithText(WorkbookPart wb, SheetData sheetData, string textToFind) { var arr = new List <Cell>(); string values; foreach (Row r in sheetData.Elements <Row>()) { foreach (Cell c in r.Elements <Cell>()) { if (GetCellValue(wb, c).Contains(textToFind)) { arr.Add(c); } } } return(arr); }
public MemoryStream ExportXls <T>(IEnumerable <T> listObjs) { if (listObjs != null && listObjs.Any()) { var stream = OpenTemplate(); var templateSheet = GetTemplateSheet(stream); stream.Seek(0, SeekOrigin.Begin); using (SpreadsheetDocument workbook = SpreadsheetDocument.Open(stream, true)) { var sheet = workbook.WorkbookPart.WorksheetParts.First().Worksheet; #region Fill Range cells var defRange = templateSheet.DefinedNames.FirstOrDefault(n => n.IsRange); var startRow = Convert.ToUInt32(defRange.StartRow); SheetData sheetData = sheet.GetFirstChild <SheetData>(); sheetData.Elements <Row>().FirstOrDefault(r => r.RowIndex == startRow).Remove(); //Row lastRow = sheetData.Elements<Row>().LastOrDefault(); foreach (var item in listObjs) { //startRow++; Row newRow = new Row(); foreach (var col in defRange.Columns) { PropertyInfo propInfo = item.GetType().GetProperty(col.Parameter.Field); Cell cell = new Cell(); cell.DataType = CellValues.String; var value = propInfo.GetValue(item, null); cell.CellValue = new CellValue((value != null) ? value.ToString() : string.Empty); // newRow.AppendChild(cell); } sheetData.AppendChild(newRow); } #endregion workbook.WorkbookPart.Workbook.Save(); } stream.Seek(0, SeekOrigin.Begin); return(stream); } else { return(null); } }
public static void OpenSpreadsheetDocument(string filepath) { // Open a SpreadsheetDocument based on a filepath. using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(filepath, false)) { WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart; WorksheetPart worksheetPart = workbookPart.WorksheetParts.First(); SheetData sheetData = worksheetPart.Worksheet.Elements <SheetData>().First(); string text; foreach (Row r in sheetData.Elements <Row>()) { foreach (Cell c in r.Elements <Cell>()) { text = c.CellValue.Text; } } } }
private IEnumerable <IGrouping <Row, Cell> > FindCellsByRange(CellRangeRef range) { Sheet sheet = FindSheetByName(range.SheetName); WorksheetPart workSheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(sheet.Id); SheetData sheetData = workSheetPart.Worksheet.GetFirstChild <SheetData>(); var result = from row in sheetData.Elements <Row>() where range.Start.Row <= row.RowIndex && row.RowIndex <= range.End.Row let cells = row.Elements <Cell>() from cell in cells where range.Contains(new CellRef(cell.CellReference)) group cell by row into cellsInRange select cellsInRange; return(result); }
private void LaunchOpenSDKExcel(string fullPath, string name) { using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(fullPath, true)) { WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart; //WorksheetPart worksheetPart = workbookPart.Workbook.First(); WorksheetPart worksheetPart = GetWorksheetPart(workbookPart, name); SheetData sheetData = worksheetPart.Worksheet.Elements <SheetData>().First(); string text; foreach (Cell c in sheetData.Elements <Row>().SelectMany(r => r.Elements <Cell>())) { text = c.CellValue.Text; Console.Write(text + " "); } Console.WriteLine(); Console.ReadKey(); } }
public bool ReadRow() { if (_rows == null) { _rows = _sheet_data.Elements <Row>().ToList(); _current_row = -1; } _current_row++; if (_current_row >= _rows.Count) { return(false); } _cells = _rows[_current_row].Elements <Cell>().ToList(); return(true); }
/// <summary> /// Function is used to return all the values that are read from Sheet /// </summary> /// <param name="filePath">File path of the Excel</param> /// <param name="sheetName">Sheet to be read from Excel</param> /// <returns>Two-dimensional list with all values from Sheet</returns> public static Collection <Collection <string> > ReadSheet(string filePath, string sheetName) { Collection <Collection <string> > myList = new Collection <Collection <string> >(); Collection <string> rowList = new Collection <string>(); try { using (SpreadsheetDocument myDoc = SpreadsheetDocument.Open(filePath, false)) { WorkbookPart workbookPart = myDoc.WorkbookPart; IEnumerable <Sheet> sheets = myDoc.WorkbookPart.Workbook.GetFirstChild <Sheets>().Elements <Sheet>().Where(s => s.Name == sheetName); if (sheets.Count() == 0) { ErrorLogger.DisplayErrorMessage("Provided sheet name is invalid!"); return(myList); } string relationshipId = sheets.First().Id.Value; WorksheetPart worksheetPart = (WorksheetPart)myDoc.WorkbookPart.GetPartById(relationshipId); SheetData sheetData = worksheetPart.Worksheet.GetFirstChild <SheetData>(); foreach (Row row in sheetData.Elements <Row>()) { rowList = ReadRow(row, workbookPart); if (null != rowList && 0 < rowList.Count()) // Check count to avoid adding of blank row entry which having meta data { myList.Add(rowList); } } } } catch (FileNotFoundException exception) { ErrorLogger.LogErrorToTextFile(errorFilePath, "Invalid file path, file not found" + exception.Message); } catch (Exception exception) { if (exception.Message.IndexOf("Invalid Hyperlink", 0, StringComparison.OrdinalIgnoreCase) != -1) { ErrorLogger.DisplayErrorMessage("Unable to read Excel. Please remove all hyperlinks from configuration Excel"); } ErrorLogger.LogErrorToTextFile(errorFilePath, "Exception Details: " + exception.Message); } return(myList); }
public static Row InsertRowInWorksheet(SheetData sheetData, int rowIndex) { if (sheetData is null) { throw new ArgumentNullException(nameof(sheetData)); } Row row = sheetData.Elements <Row>().Where(r => r.RowIndex == rowIndex).FirstOrDefault(); if (row is null) { row = new Row() { RowIndex = (uint)rowIndex }; sheetData.Append(row); } return(row); }
public static void CreateBodyData(SheetData sheetdata, int rowIndex, List <string> values) { List <UInt32> bodyStyles = GetBodyStyles(sheetdata); Row row = sheetdata.Elements <Row>().ElementAtOrDefault(rowIndex); bool exist = row != null; if (!exist) { row = new Row(); } FillRow(row, values, bodyStyles); if (!exist) { sheetdata.AppendChild(row); } }
/// <summary> /// Gets the row specified at the row index if it exists /// </summary> public static Row GetRow(SheetData sheetData, uint rowIndex) { Row row = null; uint index = rowIndex; //Make sure the row exists var match = sheetData.Elements <Row>().Where(r => r.RowIndex.Value == index); if (match.Count() != 0) { row = match.First(); } else { return(null); } return(row); }
/// <summary> /// Gets last rowindex. /// </summary> /// <returns></returns> private uint GetLastRowIndex(SpreadsheetDocument spreadSheetDocument) { uint retval = 0; WorkbookPart wbPart = spreadSheetDocument.WorkbookPart; Sheet sheet = wbPart.Workbook.Descendants <Sheet>().Where(s => s.Name.Equals(_sheetName)).FirstOrDefault(); WorksheetPart worksheetPart = (WorksheetPart)(wbPart.GetPartById(sheet.Id)); SheetData sd = worksheetPart.Worksheet.Elements <SheetData>().FirstOrDefault(); if (sd == null) { return(retval); } // we are responsible for supplying our own, correct rowindex // so we need to find out what the last one is Row lastRow = sd.Elements <Row>().OrderBy(o => o.RowIndex).LastOrDefault(); retval = (lastRow == null) ? 0 : lastRow.RowIndex; return(retval); }
private bool editarArchivo(string archivo) { try { using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(archivo, true)) { IEnumerable <Sheet> sheets = spreadsheetDocument.WorkbookPart.Workbook.GetFirstChild <Sheets>().Elements <Sheet>(); Sheet sheets1 = sheets.First(); string relationshipId = sheets.First().Id.Value; WorksheetPart worksheetPart = (WorksheetPart)spreadsheetDocument.WorkbookPart.GetPartById(relationshipId); if (worksheetPart != null) { Worksheet worksheet = worksheetPart.Worksheet; SheetData sheetData = worksheet.GetFirstChild <SheetData>(); string cellReference = "E14"; Row row = sheetData.Elements <Row>().Where(r => r.RowIndex == 14).First(); Cell cell = new Cell(); cell = row.Elements <Cell>().Where(c => c.CellReference.Value == cellReference).First(); //cell.CellReference = "E14"; cell.CellValue = new CellValue(dat.Nombres); worksheetPart.Worksheet.Save(); } spreadsheetDocument.Close(); } return(true); } catch (Exception ex) { throw; } }
private static Dictionary <int, int> GetMaxCharacterWidth(SheetData sheetData, uint numberStyleIndex) { //iterate over all cells getting a max char value for each column Dictionary <int, int> maxColWidth = new Dictionary <int, int>(); var rows = sheetData.Elements <Row>(); foreach (var r in rows) { var cells = r.Elements <Cell>().ToArray(); //using cell index as my column for (int i = 0; i < cells.Length; i++) { var cell = cells[i]; var cellValue = cell.CellValue == null ? string.Empty : cell.CellValue.InnerText; var cellTextLength = cellValue.Length; if (cell.StyleIndex != null && cell.StyleIndex == numberStyleIndex) { int thousandCount = (int)Math.Truncate((double)cellTextLength / 4); //add 3 for '.00' cellTextLength += (3 + thousandCount); } if (maxColWidth.ContainsKey(i)) { var current = maxColWidth[i]; if (cellTextLength > current) { maxColWidth[i] = cellTextLength; } } else { maxColWidth.Add(i, cellTextLength); } } } return(maxColWidth); }
private static DataTable ReadSpreadsheetTable(Table table, SheetData sheetData, WorkbookPart workbookPart, Lazy <IReadOnlyDictionary <uint, string> > lazyNumberingFormatCodeById) { var dataTable = new DataTable(table.DisplayName); foreach (var tableColumn in table.TableColumns.OfType <TableColumn>()) { dataTable.Columns.Add(tableColumn.Name.Value); } var tableReference = TableReference.Parse(table.Reference); int startRowIndex = tableReference.StartCell.RowIndex + 1; // "+1": 열 헤더 제외 int endRowIndex = tableReference.EndCell.RowIndex; int startColumnIndex = tableReference.StartCell.ColumnIndex; int endColumnIndex = tableReference.EndCell.ColumnIndex; foreach (var sheetRow in sheetData.Elements <Row>()) { int rowIndex = int.Parse(sheetRow.RowIndex); if (rowIndex < startRowIndex || rowIndex > endRowIndex) { continue; } var row = dataTable.NewRow(); foreach (var sheetCell in sheetRow.Elements <Cell>()) { var cellReference = CellReference.Parse(sheetCell.CellReference); if (cellReference.ColumnIndex < startColumnIndex || cellReference.ColumnIndex > endColumnIndex) { continue; } row[cellReference.ColumnIndex - 1] = GetCellValue(sheetCell, workbookPart, lazyNumberingFormatCodeById); } dataTable.Rows.Add(row); } return(dataTable); }
private static Row GetRow(SheetData sheetData, int rowIndex) { return sheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).First(); }
private static Row GetRow(SheetData sheetData, uint excelRowIndex, bool autoAdd) { var row = sheetData.Elements<Row>().Where(r => r.RowIndex.Value == excelRowIndex).FirstOrDefault(); if (row == null && autoAdd) { row = new Row() { RowIndex = excelRowIndex }; sheetData.Append(row); } return row; }
/// <summary> /// Generate the report for catalogue categories /// </summary> /// <param name="templatePath">Path of the template</param> /// <param name="itemSource">Item source</param> /// <returns>MemoryStream</returns> public static MemoryStream GenerateCatalogueValueReport(CatalogueValueSearchModel itemSource,string logoPath) { MemoryStream ms = new MemoryStream(); using (SpreadsheetDocument document = SpreadsheetDocument.Create(ms, SpreadsheetDocumentType.Workbook)) { //create the new workbook WorkbookPart workbookPart = document.AddWorkbookPart(); Workbook workbook = new Workbook(); workbookPart.Workbook = workbook; // If we don't add a "WorkbookStylesPart", OLEDB will refuse to connect to this .xlsx file ! WorkbookStylesPart workbookStylesPart = workbookPart.AddNewPart<WorkbookStylesPart>("rIdStyles"); //get and save the stylesheet Stylesheet stylesheet = VestalisStyleSheet(); workbookStylesPart.Stylesheet = stylesheet; workbookStylesPart.Stylesheet.Save(); //add the new workseet WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>(); Worksheet worksheet = new Worksheet(); SheetData sheetData1 = new SheetData(); Sheets sheets = new Sheets(); //get the number of columns in the report Row rowTitle; //get the string name of the columns string[] excelColumnNamesTitle = new string[2]; for (int n = 0; n < 2; 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 < 2; 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", LanguageResource.CatalogueValuesReport, currentRowTitle, 5); //merge all cells in the title MergeCell mergeCell = new MergeCell(); mergeCell.Reference = "B2:B4"; mergeCells.Append(mergeCell); Drawing drawing = AddLogo(logoPath, worksheetPart); Columns columns = new Columns(); columns.Append(CreateColumnData((UInt32Value)(uint)1, (UInt32Value)(uint)1, 50)); columns.Append(CreateColumnData((UInt32Value)(uint)2, (UInt32Value)(uint)2, 71)); worksheet.Append(columns); int rowIndex = 8; Row rowData = new Row() { RowIndex = (UInt32Value)(uint)rowIndex }; AppendTextCell("A" + rowIndex, LanguageResource.BusinessApplicationName, rowData, 2); AppendTextCell("B" + rowIndex, itemSource.BusinessApplicatioName, rowData, 1); sheetData1.Append(rowData); rowIndex = 9; rowData = new Row() { RowIndex = (UInt32Value)(uint)rowIndex }; AppendTextCell("A" + rowIndex, LanguageResource.CatalogueName, rowData, 2); AppendTextCell("B" + rowIndex, itemSource.CatalogueSelectedName, rowData, 1); sheetData1.Append(rowData); rowIndex = 11; rowData = new Row() { RowIndex = (UInt32Value)(uint)rowIndex }; AppendTextCell("A" + rowIndex.ToString(), LanguageResource.Value, rowData, 2); AppendTextCell("B" + rowIndex.ToString(), LanguageResource.Description, rowData, 2); sheetData1.Append(rowData); rowIndex = 12; foreach (var item in itemSource.SearchResult.Collection) { rowData = new Row() { RowIndex = (UInt32Value)(uint)rowIndex }; AppendTextCell("A" + rowIndex.ToString(), item.CatalogueValueData, rowData, 1); AppendTextCell("B" + rowIndex.ToString(), item.CatalogueValueDescription, rowData, 1); 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 = LanguageResource.Report, SheetId = (UInt32Value)1, Id = workbookPart.GetIdOfPart(worksheetPart) }; sheets.Append(sheet); //add the new sheet to the report workbook.Append(sheets); //save all report workbook.Save(); //close the stream. document.Close(); } return ms; }
/// <summary> /// Generate an excel file with the information of certificates /// </summary> /// <param name="dataSource">The list of certificates</param> /// <returns>MemoryStream</returns> public static MemoryStream GenerateCertificateReport(CertificateListModel model, string logoPath) { MemoryStream ms = new MemoryStream(); using (SpreadsheetDocument document = SpreadsheetDocument.Create(ms, SpreadsheetDocumentType.Workbook)) { //create the new workbook WorkbookPart workbookPart = document.AddWorkbookPart(); Workbook workbook = new Workbook(); workbookPart.Workbook = workbook; // If we don't add a "WorkbookStylesPart", OLEDB will refuse to connect to this .xlsx file ! WorkbookStylesPart workbookStylesPart = workbookPart.AddNewPart<WorkbookStylesPart>("rIdStyles"); //get and save the stylesheet Stylesheet stylesheet = VocStyleSheet(); workbookStylesPart.Stylesheet = stylesheet; workbookStylesPart.Stylesheet.Save(); //add the new workseet WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>(); Worksheet worksheet = new Worksheet(); SheetData sheetData1 = new SheetData(); Sheets sheets = new Sheets(); //get the number of columns in the report Row rowTitle; //get the string name of the columns string[] excelColumnNamesTitle = new string[4]; for (int n = 0; n < 4; 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 < 4; cellval++) { AppendTextCell(excelColumnNamesTitle[cellval] + i, string.Empty, rowTitle, 3); } sheetData1.Append(rowTitle); } List<CertificateDocument> dataSource = model.Certificates.Collection; MergeCells mergeCells = new MergeCells(); Row currentRowTitle = sheetData1.Elements<Row>().FirstOrDefault(row => row.RowIndex.Value == (uint)2); //add the title UpdateStringCellValue("A2", Resources.Common.CertificateList, currentRowTitle, 5); //set min date and max date in header Row currentRowDateTitle = sheetData1.Elements<Row>().FirstOrDefault(row => row.RowIndex.Value == (uint)5); string minDate, maxDate; //get dates if (string.IsNullOrEmpty(model.IssuanceDateFrom) || string.IsNullOrEmpty(model.IssuanceDateTo)) { minDate = dataSource.Select(x => x.Certificate.IssuanceDate.GetValueOrDefault()).Min().ToString("dd/MM/yyyy"); maxDate = dataSource.Select(x => x.Certificate.IssuanceDate.GetValueOrDefault()).Max().ToString("dd/MM/yyyy"); } else { minDate = model.IssuanceDateFrom; maxDate = model.IssuanceDateTo; } //write both dates UpdateStringCellValue("B5", Resources.Common.IssuanceDateFrom + ": "+minDate, currentRowDateTitle, 7); UpdateStringCellValue("C5", Resources.Common.IssuanceDateTo + ": " + maxDate, currentRowDateTitle, 7); //merge all cells in the title MergeCell mergeCell = new MergeCell(); mergeCell.Reference = "A2:D4"; mergeCells.Append(mergeCell); Drawing drawing = AddLogo(logoPath, worksheetPart); Columns columns = new Columns(); columns.Append(CreateColumnData((UInt32Value)(uint)1, (UInt32Value)(uint)1, 32)); columns.Append(CreateColumnData((UInt32Value)(uint)2, (UInt32Value)(uint)2, 30)); columns.Append(CreateColumnData((UInt32Value)(uint)3, (UInt32Value)(uint)3, 33)); columns.Append(CreateColumnData((UInt32Value)(uint)4, (UInt32Value)(uint)4, 45)); worksheet.Append(columns); int rowIndex = 8; Row rowData = new Row() { RowIndex = (UInt32Value)(uint)rowIndex }; AppendTextCell("A" + rowIndex, Resources.Common.CertificateNumber, rowData, 2); AppendTextCell("B" + rowIndex, Resources.Common.IssuanceDate, rowData, 2); AppendTextCell("C" + rowIndex, Resources.Common.CertificateStatus, rowData, 2); AppendTextCell("D" + rowIndex, Resources.Common.EntryPoint, rowData, 2); sheetData1.Append(rowData); rowIndex = 9; //build the data foreach (var item in dataSource) { rowData = new Row() { RowIndex = (UInt32Value)(uint)rowIndex }; AppendTextCell("A" + rowIndex.ToString(), item.Certificate.Sequential, rowData, 1); AppendTextCell("B" + rowIndex.ToString(), item.Certificate.IssuanceDate.HasValue ? item.Certificate.IssuanceDate.Value.ToString("dd/MM/yyyy") : "", rowData, 1); AppendTextCell("C" + rowIndex.ToString(), item.Certificate.CertificateStatusId.ToString(), rowData, 1); AppendTextCell("D" + rowIndex.ToString(), item.Certificate.EntryPoint != null ? item.Certificate.EntryPoint.Name : "", rowData, 1); 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 = Resources.Common.CertificateList, SheetId = (UInt32Value)1, Id = workbookPart.GetIdOfPart(worksheetPart) }; sheets.Append(sheet); //add the new sheet to the report workbook.Append(sheets); //save all report workbook.Save(); //close the stream. document.Close(); } return ms; }
/// <summary> /// Generate an excel file with the list of users /// </summary> /// <param name="dataSource">The list of users</param> /// <returns>MemoryStream</returns> public static MemoryStream GenerateUserReport(List<UserModel> dataSource, string logoPath) { MemoryStream ms = new MemoryStream(); using (SpreadsheetDocument document = SpreadsheetDocument.Create(ms, SpreadsheetDocumentType.Workbook)) { //create the new workbook WorkbookPart workbookPart = document.AddWorkbookPart(); Workbook workbook = new Workbook(); workbookPart.Workbook = workbook; // If we don't add a "WorkbookStylesPart", OLEDB will refuse to connect to this .xlsx file ! WorkbookStylesPart workbookStylesPart = workbookPart.AddNewPart<WorkbookStylesPart>("rIdStyles"); //get and save the stylesheet Stylesheet stylesheet = VocStyleSheet(); workbookStylesPart.Stylesheet = stylesheet; workbookStylesPart.Stylesheet.Save(); //add the new workseet WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>(); Worksheet worksheet = new Worksheet(); SheetData sheetData1 = new SheetData(); Sheets sheets = new Sheets(); //get the number of columns in the report Row rowTitle; //get the string name of the columns string[] excelColumnNamesTitle = new string[4]; for (int n = 0; n < 4; 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 < 4; 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("A2", Resources.Common.UserList, currentRowTitle, 5); //merge all cells in the title MergeCell mergeCell = new MergeCell(); mergeCell.Reference = "A2:D4"; mergeCells.Append(mergeCell); Drawing drawing = AddLogo(logoPath, worksheetPart); Columns columns = new Columns(); columns.Append(CreateColumnData((UInt32Value)(uint)1, (UInt32Value)(uint)1, 45)); columns.Append(CreateColumnData((UInt32Value)(uint)2, (UInt32Value)(uint)2, 42)); columns.Append(CreateColumnData((UInt32Value)(uint)3, (UInt32Value)(uint)3, 10)); columns.Append(CreateColumnData((UInt32Value)(uint)4, (UInt32Value)(uint)4, 32)); worksheet.Append(columns); int rowIndex = 8; Row rowData = new Row() { RowIndex = (UInt32Value)(uint)rowIndex }; AppendTextCell("A" + rowIndex, Resources.Common.Email, rowData, 2); AppendTextCell("B" + rowIndex, Resources.Common.FullName, rowData, 2); AppendTextCell("C" + rowIndex, Resources.Common.Active, rowData, 2); AppendTextCell("D" + rowIndex, Resources.Common.Role, rowData, 2); sheetData1.Append(rowData); rowIndex = 9; //build the data foreach (var item in dataSource) { rowData = new Row() { RowIndex = (UInt32Value)(uint)rowIndex }; AppendTextCell("A" + rowIndex.ToString(), item.Email, rowData, 1); AppendTextCell("B" + rowIndex.ToString(), item.FullName, rowData, 1); AppendTextCell("C" + rowIndex.ToString(), item.IsActive, rowData, 1); AppendTextCell("D" + rowIndex.ToString(), item.Role, rowData, 1); 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 = Resources.Common.UserList, SheetId = (UInt32Value)1, Id = workbookPart.GetIdOfPart(worksheetPart) }; sheets.Append(sheet); //add the new sheet to the report workbook.Append(sheets); //save all report workbook.Save(); //close the stream. document.Close(); } return ms; }
/// <summary> /// Generate an excel report dinamically /// </summary> /// <param name="model">Data source</param> public static MemoryStream GenerateReportDinamically(DynamicDataGrid model, string logoPath) { MemoryStream report = new MemoryStream(); using (SpreadsheetDocument document = SpreadsheetDocument.Create(report, SpreadsheetDocumentType.Workbook)) { //create the new workbook WorkbookPart workbookPart = document.AddWorkbookPart(); Workbook workbook = new Workbook(); workbookPart.Workbook = workbook; // If we don't add a "WorkbookStylesPart", OLEDB will refuse to connect to this .xlsx file ! WorkbookStylesPart workbookStylesPart = workbookPart.AddNewPart<WorkbookStylesPart>("rIdStyles"); //get and save the stylesheet Stylesheet stylesheet = VestalisStyleSheet(); workbookStylesPart.Stylesheet = stylesheet; workbookStylesPart.Stylesheet.Save(); //add the new workseet WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>(); Worksheet worksheet = new Worksheet(); SheetData sheetData1 = new SheetData(); Sheets sheets = new Sheets(); //get the number of columns in the report Row rowTitle; int numberOfColumnsCaption = model.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", model.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); Drawing drawing = AddLogo(logoPath, worksheetPart); currentRowTitle = sheetData1.Elements<Row>().FirstOrDefault(row => row.RowIndex.Value == (uint)4); //add the form name UpdateStringCellValue("B4", model.FormName, currentRowTitle,5); lastColumnName = lastColumnName.Replace("2", "4"); //merge all cell in the form name mergeCell = new MergeCell(); mergeCell.Reference = "B4:" + lastColumnName; mergeCells.Append(mergeCell); 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 < model.Captions.Count; i++) { var caption = model.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 item in model.DataRows) { int numberOfColumnsData = item.FieldValues.Count; //get column names string[] excelColumnNamesData = new string[numberOfColumnsData]; for (int n = 0; n < numberOfColumnsData; n++) excelColumnNamesData[n] = GetExcelColumnName(n); //build the data information Row rowData = new Row() { RowIndex = (UInt32Value)(uint)rowIndex }; for (int colInx = 0; colInx < numberOfColumnsData; colInx++) { DynamicDataRowValue col = item.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 = model.FormName, SheetId = (UInt32Value)1, Id = workbookPart.GetIdOfPart(worksheetPart) }; sheets.Append(sheet); //add the new sheet to the report workbook.Append(sheets); //save all report workbook.Save(); //close the stream. document.Close(); } return report; }
/// <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++; } }
// Return the row at the specified rowIndex located within // the sheet data passed in via wsData. If the row does not // exist, create it. private static Row GetRow(SheetData wsData, UInt32 rowIndex) { var row = wsData.Elements<Row>(). Where(r => r.RowIndex.Value == rowIndex).FirstOrDefault(); if (row == null) { row = new Row(); row.RowIndex = rowIndex; wsData.Append(row); } return row; }
// // Get the row of a given row index // private Row getRow(SheetData wsData, UInt32 rowIndex) { // // Look for the rowIndex, if it exists return it otherwise create a new row and retourn it // var row = wsData.Elements<Row>().Where(r => r.RowIndex.Value == rowIndex).FirstOrDefault(); if (row == null) { row = new Row(); row.RowIndex = rowIndex; wsData.Append(row); } return row; }
/// <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++; } }