Knowledgebase (2328)
Children categories
class Program
{
static void Main(string[] args)
{
string docName = @"..\..\Documents\Sheet3.xlsx";
string sheetName = "Sheet1";
string colName = "A";
uint rowIndex = 3;
DeleteTextFromCell(docName, sheetName, colName, rowIndex);
}
// Given a document, a worksheet name, a column name, and a one-based row index,
// deletes the text from the cell at the specified column and row on the specified worksheet.
public static void DeleteTextFromCell(string docName, string sheetName, string colName, uint rowIndex)
{
// Open the document for editing.
using (SpreadsheetDocument document = SpreadsheetDocument.Open(docName, true))
{
IEnumerable sheets = document.WorkbookPart.Workbook.GetFirstChild().Elements().Where(s => s.Name == sheetName);
if (sheets.Count() == 0)
{
// The specified worksheet does not exist.
return;
}
string relationshipId = sheets.First().Id.Value;
WorksheetPart worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(relationshipId);
// Get the cell at the specified column and row.
Cell cell = GetSpreadsheetCell(worksheetPart.Worksheet, colName, rowIndex);
if (cell == null)
{
// The specified cell does not exist.
return;
}
cell.Remove();
worksheetPart.Worksheet.Save();
}
}
// Given a worksheet, a column name, and a row index, gets the cell at the specified column and row.
private static Cell GetSpreadsheetCell(Worksheet worksheet, string columnName, uint rowIndex)
{
IEnumerable rows = worksheet.GetFirstChild().Elements().Where(r => r.RowIndex == rowIndex);
if (rows.Count() == 0)
{
// A cell does not exist at the specified row.
return null;
}
IEnumerable cells = rows.First().Elements().Where(c => string.Compare(c.CellReference.Value, columnName + rowIndex, true) == 0);
if (cells.Count() == 0)
{
// A cell does not exist at the specified column, in the specified row.
return null;
}
return cells.First();
}
// Given a shared string ID and a SpreadsheetDocument, verifies that other cells in the document no longer
// reference the specified SharedStringItem and removes the item.
private static void RemoveSharedStringItem(int shareStringId, SpreadsheetDocument document)
{
bool remove = true;
foreach (var part in document.WorkbookPart.GetPartsOfType())
{
Worksheet worksheet = part.Worksheet;
foreach (var cell in worksheet.GetFirstChild().Descendants())
{
// Verify if other cells in the document reference the item.
if (cell.DataType != null &&
cell.DataType.Value == CellValues.SharedString &&
cell.CellValue.Text == shareStringId.ToString())
{
// Other cells in the document still reference the item. Do not remove the item.
remove = false;
break;
}
}
if (!remove)
{
break;
}
}
// Other cells in the document do not reference the item. Remove the item.
if (remove)
{
SharedStringTablePart shareStringTablePart = document.WorkbookPart.SharedStringTablePart;
if (shareStringTablePart == null)
{
return;
}
SharedStringItem item = shareStringTablePart.SharedStringTable.Elements().ElementAt(shareStringId);
if (item != null)
{
item.Remove();
// Refresh all the shared string references.
foreach (var part in document.WorkbookPart.GetPartsOfType())
{
Worksheet worksheet = part.Worksheet;
foreach (var cell in worksheet.GetFirstChild().Descendants())
{
if (cell.DataType != null &&
cell.DataType.Value == CellValues.SharedString)
{
int itemIndex = int.Parse(cell.CellValue.Text);
if (itemIndex > shareStringId)
{
cell.CellValue.Text = (itemIndex - 1).ToString();
}
}
}
worksheet.Save();
}
document.WorkbookPart.SharedStringTablePart.SharedStringTable.Save();
}
}
}
}
Published in
OpenXML
class Program
{
static void Main(string[] args)
{
CreateSpreadsheetWorkbook(@"..\..\Documents\Sheet2.xlsx");
}
public static void CreateSpreadsheetWorkbook(string filepath)
{
// Create a spreadsheet document by supplying the filepath.
// By default, AutoSave = true, Editable = true, and Type = xlsx.
SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(filepath, SpreadsheetDocumentType.Workbook);
// Add a WorkbookPart to the document.
WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
// Add a WorksheetPart to the WorkbookPart.
WorksheetPart worksheetPart = workbookpart.AddNewPart();
worksheetPart.Worksheet = new Worksheet(new SheetData());
// Add Sheets to the Workbook.
Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.
AppendChild(new Sheets());
// Append a new worksheet and associate it with the workbook.
Sheet sheet = new Sheet()
{
Id = spreadsheetDocument.WorkbookPart.
GetIdOfPart(worksheetPart),
SheetId = 1,
Name = "mySheet"
};
sheets.Append(sheet);
workbookpart.Workbook.Save();
// Close the document.
spreadsheetDocument.Close();
}
}
Published in
OpenXML
Calculate the sum of a range of cells in a spreadsheet document
2016-01-15 06:33:57 Written by Koohji
class Program
{
static void Main(string[] args)
{
string docName = @"..\..\documents\Sheet1.xlsx";
string worksheetName = "John";
string firstCellName = "A1";
string lastCellName = "A3";
string resultCell = "A4";
CalculateSumOfCellRange(docName, worksheetName, firstCellName, lastCellName, resultCell);
}
private static void CalculateSumOfCellRange(string docName, string worksheetName, string firstCellName, string lastCellName, string resultCell)
{
// Open the document for editing.
using (SpreadsheetDocument document = SpreadsheetDocument.Open(docName, true))
{
IEnumerable sheets = document.WorkbookPart.Workbook.Descendants().Where(s => s.Name == worksheetName);
if (sheets.Count() == 0)
{
// The specified worksheet does not exist.
return;
}
WorksheetPart worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(sheets.First().Id);
Worksheet worksheet = worksheetPart.Worksheet;
// Get the row number and column name for the first and last cells in the range.
uint firstRowNum = GetRowIndex(firstCellName);
uint lastRowNum = GetRowIndex(lastCellName);
string firstColumn = GetColumnName(firstCellName);
string lastColumn = GetColumnName(lastCellName);
double sum = 0;
// Iterate through the cells within the range and add their values to the sum.
foreach (Row row in worksheet.Descendants().Where(r => r.RowIndex.Value >= firstRowNum && r.RowIndex.Value <= lastRowNum))
{
foreach (Cell cell in row)
{
string columnName = GetColumnName(cell.CellReference.Value);
if (CompareColumn(columnName, firstColumn) >= 0 && CompareColumn(columnName, lastColumn) <= 0)
{
sum += double.Parse(cell.CellValue.Text);
}
}
}
// Get the SharedStringTablePart and add the result to it.
// If the SharedStringPart does not exist, create a new one.
SharedStringTablePart shareStringPart;
if (document.WorkbookPart.GetPartsOfType().Count() > 0)
{
shareStringPart = document.WorkbookPart.GetPartsOfType().First();
}
else
{
shareStringPart = document.WorkbookPart.AddNewPart();
}
// Insert the result into the SharedStringTablePart.
int index = InsertSharedStringItem("Result: " + sum, shareStringPart);
Cell result = InsertCellInWorksheet(GetColumnName(resultCell), GetRowIndex(resultCell), worksheetPart);
// Set the value of the cell.
result.CellValue = new CellValue(index.ToString());
result.DataType = new EnumValue(CellValues.SharedString);
worksheetPart.Worksheet.Save();
}
}
// 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);
}
// 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 two columns, compares the columns.
private static int CompareColumn(string column1, string column2)
{
if (column1.Length > column2.Length)
{
return 1;
}
else if (column1.Length < column2.Length)
{
return -1;
}
else
{
return string.Compare(column1, column2, true);
}
}
// 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 it.
if (shareStringPart.SharedStringTable == null)
{
shareStringPart.SharedStringTable = new SharedStringTable();
}
int i = 0;
foreach (SharedStringItem item in shareStringPart.SharedStringTable.Elements())
{
if (item.InnerText == text)
{
// The text already exists in the part. Return its index.
return i;
}
i++;
}
// The text does not exist in the part. Create the SharedStringItem.
shareStringPart.SharedStringTable.AppendChild(new SharedStringItem(new DocumentFormat.OpenXml.Spreadsheet.Text(text)));
shareStringPart.SharedStringTable.Save();
return i;
}
// 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
