Knowledgebase (2417)
Children categories
class Program
{
static void Main(string[] args)
{
OpenSpreadsheetDocumentReadonly(@"..\..\Documents\Sheet8.xlsx");
}
public static void OpenSpreadsheetDocumentReadonly(string filepath)
{
// Open a SpreadsheetDocument based on a filepath.
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(filepath, false))
{
// Attempt to add a new WorksheetPart.
// The call to AddNewPart generates an exception because the file is read-only.
WorksheetPart newWorksheetPart = spreadsheetDocument.WorkbookPart.AddNewPart();
// The rest of the code will not be called.
}
}
}
Published in
OpenXML
class Program
{
static void Main(string[] args)
{
string docName = @"..\..\Documents\Sheet7.xlsx";
string sheetName = "Jane";
string cell1Name = "B2";
string cell2Name = "C2";
MergeTwoCells(docName, sheetName, cell1Name, cell2Name);
}
// Given a document name, a worksheet name, and the names of two adjacent cells, merges the two cells.
// When two cells are merged, only the content from one cell is preserved:
// the upper-left cell for left-to-right languages or the upper-right cell for right-to-left languages.
private static void MergeTwoCells(string docName, string sheetName, string cell1Name, string cell2Name)
{
// Open the document for editing.
using (SpreadsheetDocument document = SpreadsheetDocument.Open(docName, true))
{
Worksheet worksheet = GetWorksheet(document, sheetName);
if (worksheet == null || string.IsNullOrEmpty(cell1Name) || string.IsNullOrEmpty(cell2Name))
{
return;
}
// Verify if the specified cells exist, and if they do not exist, create them.
CreateSpreadsheetCellIfNotExist(worksheet, cell1Name);
CreateSpreadsheetCellIfNotExist(worksheet, cell2Name);
MergeCells mergeCells;
if (worksheet.Elements().Count() > 0)
{
mergeCells = worksheet.Elements().First();
}
else
{
mergeCells = new MergeCells();
// Insert a MergeCells object into the specified position.
if (worksheet.Elements().Count() > 0)
{
worksheet.InsertAfter(mergeCells, worksheet.Elements().First());
}
else if (worksheet.Elements().Count() > 0)
{
worksheet.InsertAfter(mergeCells, worksheet.Elements().First());
}
else if (worksheet.Elements().Count() > 0)
{
worksheet.InsertAfter(mergeCells, worksheet.Elements().First());
}
else if (worksheet.Elements().Count() > 0)
{
worksheet.InsertAfter(mergeCells, worksheet.Elements().First());
}
else if (worksheet.Elements().Count() > 0)
{
worksheet.InsertAfter(mergeCells, worksheet.Elements().First());
}
else if (worksheet.Elements().Count() > 0)
{
worksheet.InsertAfter(mergeCells, worksheet.Elements().First());
}
else if (worksheet.Elements().Count() > 0)
{
worksheet.InsertAfter(mergeCells, worksheet.Elements().First());
}
else if (worksheet.Elements().Count() > 0)
{
worksheet.InsertAfter(mergeCells, worksheet.Elements().First());
}
else
{
worksheet.InsertAfter(mergeCells, worksheet.Elements().First());
}
}
// Create the merged cell and append it to the MergeCells collection.
MergeCell mergeCell = new MergeCell() { Reference = new StringValue(cell1Name + ":" + cell2Name) };
mergeCells.Append(mergeCell);
worksheet.Save();
}
}
// Given a Worksheet and a cell name, verifies that the specified cell exists.
// If it does not exist, creates a new cell.
private static void CreateSpreadsheetCellIfNotExist(Worksheet worksheet, string cellName)
{
string columnName = GetColumnName(cellName);
uint rowIndex = GetRowIndex(cellName);
IEnumerable rows = worksheet.Descendants().Where(r => r.RowIndex.Value == rowIndex);
// If the Worksheet does not contain the specified row, create the specified row.
// Create the specified cell in that row, and insert the row into the Worksheet.
if (rows.Count() == 0)
{
Row row = new Row() { RowIndex = new UInt32Value(rowIndex) };
Cell cell = new Cell() { CellReference = new StringValue(cellName) };
row.Append(cell);
worksheet.Descendants().First().Append(row);
worksheet.Save();
}
else
{
Row row = rows.First();
IEnumerable cells = row.Elements().Where(c => c.CellReference.Value == cellName);
// If the row does not contain the specified cell, create the specified cell.
if (cells.Count() == 0)
{
Cell cell = new Cell() { CellReference = new StringValue(cellName) };
row.Append(cell);
worksheet.Save();
}
}
}
// Given a SpreadsheetDocument and a worksheet name, get the specified worksheet.
private static Worksheet GetWorksheet(SpreadsheetDocument document, string worksheetName)
{
IEnumerable sheets = document.WorkbookPart.Workbook.Descendants().Where(s => s.Name == worksheetName);
WorksheetPart worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(sheets.First().Id)
if (sheets.Count() == 0)
return null;
else
return worksheetPart.Worksheet;
}
// Given a cell name, parses the specified cell to get the column name.
private static string GetColumnName(string cellName)
{
// Create a regular expression to match the column name portion of the cell name.
Regex regex = new Regex("[A-Za-z]+");
Match match = regex.Match(cellName);
return match.Value;
}
// Given a cell name, parses the specified cell to get the row index.
private static uint GetRowIndex(string cellName)
{
// Create a regular expression to match the row index portion the cell name.
Regex regex = new Regex(@"\d+");
Match match = regex.Match(cellName);
return uint.Parse(match.Value);
}
}
Published in
OpenXML
class Program
{
static void Main(string[] args)
{
InsertText(@"..\..\Documents\Sheet6.xlsx", "Inserted Text");
}
// Given a document name and text,
// inserts a new work sheet and writes the text to cell "A1" of the new worksheet.
public static void InsertText(string docName, string text)
{
// Open the document for editing.
using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(docName, true))
{
// Get the SharedStringTablePart. If it does not exist, create a new one.
SharedStringTablePart shareStringPart;
if (spreadSheet.WorkbookPart.GetPartsOfType().Count() > 0)
{
shareStringPart = spreadSheet.WorkbookPart.GetPartsOfType().First();
}
else
{
shareStringPart = spreadSheet.WorkbookPart.AddNewPart();
}
// Insert the text into the SharedStringTablePart.
int index = InsertSharedStringItem(text, shareStringPart);
// Insert a new worksheet.
WorksheetPart worksheetPart = InsertWorksheet(spreadSheet.WorkbookPart);
// Insert cell A1 into the new worksheet.
Cell cell = InsertCellInWorksheet("A", 1, worksheetPart);
// Set the value of cell A1.
cell.CellValue = new CellValue(index.ToString());
cell.DataType = new EnumValue(CellValues.SharedString);
// Save the new worksheet.
worksheetPart.Worksheet.Save();
}
}
// Given text and a SharedStringTablePart, creates a SharedStringItem with the specified text
// and inserts it into the SharedStringTablePart. If the item already exists, returns its index.
private static int InsertSharedStringItem(string text, SharedStringTablePart shareStringPart)
{
// If the part does not contain a SharedStringTable, create one.
if (shareStringPart.SharedStringTable == null)
{
shareStringPart.SharedStringTable = new SharedStringTable();
}
int i = 0;
// Iterate through all the items in the SharedStringTable. If the text already exists, return its index.
foreach (SharedStringItem item in shareStringPart.SharedStringTable.Elements())
{
if (item.InnerText == text)
{
return i;
}
i++;
}
// The text does not exist in the part. Create the SharedStringItem and return its index.
shareStringPart.SharedStringTable.AppendChild(new SharedStringItem(new DocumentFormat.OpenXml.Spreadsheet.Text(text)));
shareStringPart.SharedStringTable.Save();
return i;
}
// Given a WorkbookPart, inserts a new worksheet.
private static WorksheetPart InsertWorksheet(WorkbookPart workbookPart)
{
// Add a new worksheet part to the workbook.
WorksheetPart newWorksheetPart = workbookPart.AddNewPart();
newWorksheetPart.Worksheet = new Worksheet(new SheetData());
newWorksheetPart.Worksheet.Save();
Sheets sheets = workbookPart.Workbook.GetFirstChild();
string relationshipId = workbookPart.GetIdOfPart(newWorksheetPart);
// Get a unique ID for the new sheet.
uint sheetId = 1;
if (sheets.Elements().Count() > 0)
{
sheetId = sheets.Elements().Select(s => s.SheetId.Value).Max() + 1;
}
string sheetName = "Sheet" + sheetId;
// Append the new worksheet and associate it with the workbook.
Sheet sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = sheetName };
sheets.Append(sheet);
workbookPart.Workbook.Save();
return newWorksheetPart;
}
// Given a column name, a row index, and a WorksheetPart, inserts a cell into the worksheet.
// If the cell already exists, returns it.
private static Cell InsertCellInWorksheet(string columnName, uint rowIndex, WorksheetPart worksheetPart)
{
Worksheet worksheet = worksheetPart.Worksheet;
SheetData sheetData = worksheet.GetFirstChild();
string cellReference = columnName + rowIndex;
// If the worksheet does not contain a row with the specified row index, insert one.
Row row;
if (sheetData.Elements().Where(r => r.RowIndex == rowIndex).Count() != 0)
{
row = sheetData.Elements().Where(r => r.RowIndex == rowIndex).First();
}
else
{
row = new Row() { RowIndex = rowIndex };
sheetData.Append(row);
}
// If there is not a cell with the specified column name, insert one.
if (row.Elements().Where(c => c.CellReference.Value == columnName + rowIndex).Count() > 0)
{
return row.Elements().Where(c => c.CellReference.Value == cellReference).First();
}
else
{
// Cells must be in sequential order according to CellReference. Determine where to insert the new cell.
Cell refCell = null;
foreach (Cell cell in row.Elements())
{
if (string.Compare(cell.CellReference.Value, cellReference, true) > 0)
{
refCell = cell;
break;
}
}
Cell newCell = new Cell() { CellReference = cellReference };
row.InsertBefore(newCell, refCell);
worksheet.Save();
return newCell;
}
}
}
Published in
OpenXML
