private static DocumentFormat.OpenXml.Spreadsheet.Cell InsertCellInWorksheet(string columnName, uint rowIndex, WorksheetPart worksheetPart) { DocumentFormat.OpenXml.Spreadsheet.Worksheet worksheet = worksheetPart.Worksheet; SheetData sheetData = worksheet.GetFirstChild <SheetData>(); string cellReference = columnName + rowIndex; DocumentFormat.OpenXml.Spreadsheet.Row lastRow = sheetData.Elements <DocumentFormat.OpenXml.Spreadsheet.Row>().LastOrDefault(); // If the worksheet does not contain a row with the specified row index, insert one. DocumentFormat.OpenXml.Spreadsheet.Row row; if (sheetData.Elements <DocumentFormat.OpenXml.Spreadsheet.Row>().Where(r => r.RowIndex == rowIndex).Count() != 0) { row = sheetData.Elements <DocumentFormat.OpenXml.Spreadsheet.Row>().Where(r => r.RowIndex == rowIndex).First(); //set auto height -- don't know how this line is worked sheetData.InsertAfter(new DocumentFormat.OpenXml.Spreadsheet.Row() { RowIndex = (lastRow.RowIndex + 1) }, lastRow); } else { row = new DocumentFormat.OpenXml.Spreadsheet.Row() { RowIndex = rowIndex }; sheetData.Append(row); } // If there is not a cell with the specified column name, insert one. if (row.Elements <DocumentFormat.OpenXml.Spreadsheet.Cell>().Where(c => c.CellReference.Value == columnName + rowIndex).Count() > 0) { return(row.Elements <DocumentFormat.OpenXml.Spreadsheet.Cell>().Where(c => c.CellReference.Value == cellReference).First()); } else { // Cells must be in sequential order according to CellReference. Determine where to insert the new cell. DocumentFormat.OpenXml.Spreadsheet.Cell refCell = null; foreach (DocumentFormat.OpenXml.Spreadsheet.Cell cell in row.Elements <DocumentFormat.OpenXml.Spreadsheet.Cell>()) { if (string.Compare(cell.CellReference.Value, cellReference, true) > 0) { refCell = cell; break; } } DocumentFormat.OpenXml.Spreadsheet.Cell newCell = new DocumentFormat.OpenXml.Spreadsheet.Cell() { CellReference = cellReference }; row.InsertBefore(newCell, refCell); worksheet.Save(); return(newCell); } }
/// <summary> /// Sets the column width /// </summary> /// <param name="worksheet">Worksheet to use</param> /// <param name="columnIndex">Index of the column</param> /// <param name="width">Width to set</param> /// <returns>True if succesful</returns> public static bool SetColumnWidth(DocumentFormat.OpenXml.Spreadsheet.Worksheet worksheet, int columnIndex, int width) { DocumentFormat.OpenXml.Spreadsheet.Columns columns; DocumentFormat.OpenXml.Spreadsheet.Column column; // Get the column collection exists columns = worksheet.Elements <DocumentFormat.OpenXml.Spreadsheet.Columns>().FirstOrDefault(); if (columns == null) { return(false); } // Get the column column = columns.Elements <DocumentFormat.OpenXml.Spreadsheet.Column>().Where(item => item.Min == columnIndex).FirstOrDefault(); if (column == null) { return(false); } column.Width = width; column.CustomWidth = true; worksheet.Save(); return(true); }
/// <summary> /// Sets a cell value. The row and the cell are created if they do not exist. If the cell exists, the contents of the cell is overwritten /// </summary> /// <param name="spreadsheet">Spreadsheet to use</param> /// <param name="worksheet">Worksheet to use</param> /// <param name="columnIndex">Index of the column</param> /// <param name="rowIndex">Index of the row</param> /// <param name="valueType">Type of the value</param> /// <param name="value">The actual value</param> /// <param name="styleIndex">Index of the style to use. Null if no style is to be defined</param> /// <param name="save">Save the worksheet?</param> /// <returns>True if succesful</returns> private static bool SetCellValue(DocumentFormat.OpenXml.Packaging.SpreadsheetDocument spreadsheet, DocumentFormat.OpenXml.Spreadsheet.Worksheet worksheet, uint columnIndex, uint rowIndex, DocumentFormat.OpenXml.Spreadsheet.CellValues valueType, string value, uint?styleIndex, bool save = true) { DocumentFormat.OpenXml.Spreadsheet.SheetData sheetData = worksheet.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.SheetData>(); DocumentFormat.OpenXml.Spreadsheet.Row row; DocumentFormat.OpenXml.Spreadsheet.Row previousRow = null; DocumentFormat.OpenXml.Spreadsheet.Cell cell; DocumentFormat.OpenXml.Spreadsheet.Cell previousCell = null; DocumentFormat.OpenXml.Spreadsheet.Columns columns; DocumentFormat.OpenXml.Spreadsheet.Column previousColumn = null; string cellAddress = ExcelProc.ColumnNameFromIndex(columnIndex) + rowIndex; // Check if the row exists, create if necessary if (sheetData.Elements <DocumentFormat.OpenXml.Spreadsheet.Row>().Where(item => item.RowIndex == rowIndex).Count() != 0) { row = sheetData.Elements <DocumentFormat.OpenXml.Spreadsheet.Row>().Where(item => item.RowIndex == rowIndex).First(); } else { row = new DocumentFormat.OpenXml.Spreadsheet.Row() { RowIndex = rowIndex }; //sheetData.Append(row); for (uint counter = rowIndex - 1; counter > 0; counter--) { previousRow = sheetData.Elements <DocumentFormat.OpenXml.Spreadsheet.Row>().Where(item => item.RowIndex == counter).FirstOrDefault(); if (previousRow != null) { break; } } sheetData.InsertAfter(row, previousRow); } // Check if the cell exists, create if necessary if (row.Elements <DocumentFormat.OpenXml.Spreadsheet.Cell>().Where(item => item.CellReference.Value == cellAddress).Count() > 0) { cell = row.Elements <DocumentFormat.OpenXml.Spreadsheet.Cell>().Where(item => item.CellReference.Value == cellAddress).First(); } else { // Find the previous existing cell in the row for (uint counter = columnIndex - 1; counter > 0; counter--) { previousCell = row.Elements <DocumentFormat.OpenXml.Spreadsheet.Cell>().Where(item => item.CellReference.Value == ExcelProc.ColumnNameFromIndex(counter) + rowIndex).FirstOrDefault(); if (previousCell != null) { break; } } cell = new DocumentFormat.OpenXml.Spreadsheet.Cell() { CellReference = cellAddress }; row.InsertAfter(cell, previousCell); } // Check if the column collection exists columns = worksheet.Elements <DocumentFormat.OpenXml.Spreadsheet.Columns>().FirstOrDefault(); if (columns == null) { columns = worksheet.InsertAt(new DocumentFormat.OpenXml.Spreadsheet.Columns(), 0); } // Check if the column exists if (columns.Elements <DocumentFormat.OpenXml.Spreadsheet.Column>().Where(item => item.Min == columnIndex).Count() == 0) { // Find the previous existing column in the columns for (uint counter = columnIndex - 1; counter > 0; counter--) { previousColumn = columns.Elements <DocumentFormat.OpenXml.Spreadsheet.Column>().Where(item => item.Min == counter).FirstOrDefault(); if (previousColumn != null) { break; } } columns.InsertAfter( new DocumentFormat.OpenXml.Spreadsheet.Column() { Min = columnIndex, Max = columnIndex, CustomWidth = true, Width = 9 }, previousColumn); } // Add the value cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(value); if (styleIndex != null) { cell.StyleIndex = styleIndex; } if (valueType != DocumentFormat.OpenXml.Spreadsheet.CellValues.Date) { cell.DataType = new DocumentFormat.OpenXml.EnumValue <DocumentFormat.OpenXml.Spreadsheet.CellValues>(valueType); } if (save) { worksheet.Save(); } return(true); }
public void Save() { worksheet.Save(); }