Knowledgebase (2311)
Children categories
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
class Program
{
static void Main(string[] args)
{
string docName = @"..\..\Documents\Sheet5.xlsx";
string worksheetName = "Joe";
string title = "New Chart";
Dictionary data = new Dictionary();
data.Add("abc", 1);
InsertChartInSpreadsheet(docName, worksheetName, title, data);
}
// Given a document name, a worksheet name, a chart title, and a Dictionary collection of text keys
// and corresponding integer data, creates a column chart with the text as the series and the integers as the values.
private static void InsertChartInSpreadsheet(string docName, string worksheetName, string title,
Dictionary data)
{
// 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);
// Add a new drawing to the worksheet.
DrawingsPart drawingsPart = worksheetPart.AddNewPart();
worksheetPart.Worksheet.Append(new DocumentFormat.OpenXml.Spreadsheet.Drawing()
{ Id = worksheetPart.GetIdOfPart(drawingsPart) });
worksheetPart.Worksheet.Save();
// Add a new chart and set the chart language to English-US.
ChartPart chartPart = drawingsPart.AddNewPart();
chartPart.ChartSpace = new ChartSpace();
chartPart.ChartSpace.Append(new EditingLanguage() { Val = new StringValue("en-US") });
DocumentFormat.OpenXml.Drawing.Charts.Chart chart = chartPart.ChartSpace.AppendChild(
new DocumentFormat.OpenXml.Drawing.Charts.Chart());
// Create a new clustered column chart.
PlotArea plotArea = chart.AppendChild(new PlotArea());
Layout layout = plotArea.AppendChild(new Layout());
BarChart barChart = plotArea.AppendChild(new BarChart(new BarDirection()
{ Val = new EnumValue(BarDirectionValues.Column) },
new BarGrouping() { Val = new EnumValue(BarGroupingValues.Clustered) }));
uint i = 0;
// Iterate through each key in the Dictionary collection and add the key to the chart Series
// and add the corresponding value to the chart Values.
foreach (string key in data.Keys)
{
BarChartSeries barChartSeries = barChart.AppendChild(new BarChartSeries(new Index()
{
Val =
new UInt32Value(i)
},
new Order() { Val = new UInt32Value(i) },
new SeriesText(new NumericValue() { Text = key })));
StringLiteral strLit = barChartSeries.AppendChild(new CategoryAxisData()).AppendChild(new StringLiteral());
strLit.Append(new PointCount() { Val = new UInt32Value(1U) });
strLit.AppendChild(new StringPoint() { Index = new UInt32Value(0U) }).Append(new NumericValue(title));
NumberLiteral numLit = barChartSeries.AppendChild(
new DocumentFormat.OpenXml.Drawing.Charts.Values()).AppendChild(new NumberLiteral());
numLit.Append(new FormatCode("General"));
numLit.Append(new PointCount() { Val = new UInt32Value(1U) });
numLit.AppendChild(new NumericPoint() { Index = new UInt32Value(0u) }).Append
(new NumericValue(data[key].ToString()));
i++;
}
barChart.Append(new AxisId() { Val = new UInt32Value(48650112u) });
barChart.Append(new AxisId() { Val = new UInt32Value(48672768u) });
// Add the Category Axis.
CategoryAxis catAx = plotArea.AppendChild(new CategoryAxis(new AxisId()
{ Val = new UInt32Value(48650112u) }, new Scaling(new Orientation()
{
Val = new EnumValue(DocumentFormat.OpenXml.Drawing.Charts.OrientationValues.MinMax)
}),
new AxisPosition() { Val = new EnumValue(AxisPositionValues.Bottom) },
new TickLabelPosition() { Val = new EnumValue(TickLabelPositionValues.NextTo) },
new CrossingAxis() { Val = new UInt32Value(48672768U) },
new Crosses() { Val = new EnumValue(CrossesValues.AutoZero) },
new AutoLabeled() { Val = new BooleanValue(true) },
new LabelAlignment() { Val = new EnumValue(LabelAlignmentValues.Center) },
new LabelOffset() { Val = new UInt16Value((ushort)100) }));
// Add the Value Axis.
ValueAxis valAx = plotArea.AppendChild(new ValueAxis(new AxisId() { Val = new UInt32Value(48672768u) },
new Scaling(new Orientation()
{
Val = new EnumValue(
DocumentFormat.OpenXml.Drawing.Charts.OrientationValues.MinMax)
}),
new AxisPosition() { Val = new EnumValue(AxisPositionValues.Left) },
new MajorGridlines(),
new DocumentFormat.OpenXml.Drawing.Charts.NumberingFormat()
{
FormatCode = new StringValue("General"),
SourceLinked = new BooleanValue(true)
}, new TickLabelPosition()
{
Val = new EnumValue
(TickLabelPositionValues.NextTo)
}, new CrossingAxis() { Val = new UInt32Value(48650112U) },
new Crosses() { Val = new EnumValue(CrossesValues.AutoZero) },
new CrossBetween() { Val = new EnumValue(CrossBetweenValues.Between) }));
// Add the chart Legend.
Legend legend = chart.AppendChild
Published in
OpenXML
