OpenXML (13)
Retrieve the values of cells in a spreadsheet document
2016-01-15 06:52:17 Written by support iceblue
class Program
{
static void Main(string[] args)
{
const string fileName = @"..\..\Documents\Sheets13.xlsx";
// Retrieve the value in cell A1.
string value = GetCellValue(fileName, "Sheet1", "A1");
Console.WriteLine(value);
// Retrieve the date value in cell A2.
value = GetCellValue(fileName, "Sheet1", "A2");
Console.WriteLine(DateTime.FromOADate(double.Parse(value)).ToShortDateString());
Console.ReadLine();
}
// Retrieve the value of a cell, given a file name, sheet name,
// and address name.
public static string GetCellValue(string fileName,
string sheetName,
string addressName)
{
string value = null;
// Open the spreadsheet document for read-only access.
using (SpreadsheetDocument document =
SpreadsheetDocument.Open(fileName, false))
{
// Retrieve a reference to the workbook part.
WorkbookPart wbPart = document.WorkbookPart;
// Find the sheet with the supplied name, and then use that
// Sheet object to retrieve a reference to the first worksheet.
Sheet theSheet = wbPart.Workbook.Descendants().
Where(s => s.Name == sheetName).FirstOrDefault();
// Throw an exception if there is no sheet.
if (theSheet == null)
{
throw new ArgumentException("sheetName");
}
// Retrieve a reference to the worksheet part.
WorksheetPart wsPart =
(WorksheetPart)(wbPart.GetPartById(theSheet.Id));
// Use its Worksheet property to get a reference to the cell
// whose address matches the address you supplied.
Cell theCell = wsPart.Worksheet.Descendants().
Where(c => c.CellReference == addressName).FirstOrDefault();
// If the cell does not exist, return an empty string.
if (theCell != null)
{
value = theCell.InnerText;
// If the cell represents an integer number, you are done.
// For dates, this code returns the serialized value that
// represents the date. The code handles strings and
// Booleans individually. For shared strings, the code
// looks up the corresponding value in the shared string
// table. For Booleans, the code converts the value into
// the words TRUE or FALSE.
if (theCell.DataType != null)
{
switch (theCell.DataType.Value)
{
case CellValues.SharedString:
// For shared strings, look up the value in the
// shared strings table.
var stringTable =
wbPart.GetPartsOfType()
.FirstOrDefault();
// If the shared string table is missing, something
// is wrong. Return the index that is in
// the cell. Otherwise, look up the correct text in
// the table.
if (stringTable != null)
{
value =
stringTable.SharedStringTable
.ElementAt(int.Parse(value)).InnerText;
}
break;
case CellValues.Boolean:
switch (value)
{
case "0":
value = "FALSE";
break;
default:
value = "TRUE";
break;
}
break;
}
}
}
}
return value;
}
}
Published in
OpenXML
Retrieve a list of the worksheets in a spreadsheet document
2016-01-15 06:51:24 Written by support iceblue
class Program
{
static void Main(string[] args)
{
const string DEMOFILE = @"..\..\Documents\Sheets12.xlsx";
var results = GetAllWorksheets(DEMOFILE);
foreach (Sheet item in results)
{
Console.WriteLine(item.Name);
}
Console.ReadLine();
}
// Retrieve a List of all the sheets in a workbook.
// The Sheets class contains a collection of
// OpenXmlElement objects, each representing one of
// the sheets.
public static Sheets GetAllWorksheets(string fileName)
{
Sheets theSheets = null;
using (SpreadsheetDocument document = SpreadsheetDocument.Open(fileName, false))
{
WorkbookPart wbPart = document.WorkbookPart;
theSheets = wbPart.Workbook.Sheets;
}
return theSheets;
}
}
Published in
OpenXML
Retrieve a list of the hidden worksheets in a spreadsheet document
2016-01-15 06:50:07 Written by support iceblue
class Program
{
static void Main(string[] args)
{
// Revise this path to the location of a file that contains hidden worksheets.
const string DEMOPATH = @"..\..\Documents\Sheets11.xlsx";
List sheets = GetHiddenSheets(DEMOPATH);
foreach (var sheet in sheets)
{
Console.WriteLine(sheet.Name);
}
Console.ReadLine();
}
public static List GetHiddenSheets(string fileName)
{
List returnVal = new List();
using (SpreadsheetDocument document = SpreadsheetDocument.Open(fileName, false))
{
WorkbookPart wbPart = document.WorkbookPart;
var sheets = wbPart.Workbook.Descendants();
// Look for sheets where there is a State attribute defined,
// where the State has a value,
// and where the value is either Hidden or VeryHidden.
var hiddenSheets = sheets.Where((item) => item.State != null &&
item.State.HasValue &&
(item.State.Value == SheetStateValues.Hidden ||
item.State.Value == SheetStateValues.VeryHidden));
returnVal = hiddenSheets.ToList();
}
return returnVal;
}
}
Published in
OpenXML
Retrieve a dictionary of all named ranges in a spreadsheet document
2016-01-15 06:49:26 Written by support iceblue
class Program
{
static void Main(string[] args)
{
var result = GetDefinedNames(@"..\..\Documents\Sheet10.xlsx");
foreach (var dn in result)
Console.WriteLine("{0} {1}", dn.Key, dn.Value);
Console.ReadLine();
}
public static Dictionary GetDefinedNames(String fileName)
{
// Given a workbook name, return a dictionary of defined names.
// The pairs include the range name and a string representing the range.
var returnValue = new Dictionary();
// Open the spreadsheet document for read-only access.
using (SpreadsheetDocument document = SpreadsheetDocument.Open(fileName, false))
{
// Retrieve a reference to the workbook part.
var wbPart = document.WorkbookPart;
// Retrieve a reference to the defined names collection.
DefinedNames definedNames = wbPart.Workbook.DefinedNames;
// If there are defined names, add them to the dictionary.
if (definedNames != null)
{
foreach (DefinedName dn in definedNames)
returnValue.Add(dn.Name.Value, dn.Text);
}
}
return returnValue;
}
}
Published in
OpenXML
class Program
{
static void Main(string[] args)
{
string strDoc = @"..\..\Documents\Sheet9.xlsx";
Stream stream = File.Open(strDoc, FileMode.Open);
OpenAndAddToSpreadsheetStream(stream);
stream.Close();
}
public static void OpenAndAddToSpreadsheetStream(Stream stream)
{
// Open a SpreadsheetDocument based on a stream.
SpreadsheetDocument spreadsheetDocument =SpreadsheetDocument.Open(stream, true);
// Add a new worksheet.
WorksheetPart newWorksheetPart = spreadsheetDocument.WorkbookPart.AddNewPart();
newWorksheetPart.Worksheet = new Worksheet(new SheetData());
newWorksheetPart.Worksheet.Save();
Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.GetFirstChild();
string relationshipId = spreadsheetDocument.WorkbookPart.GetIdOfPart(newWorksheetPart);
// Get a unique ID for the new worksheet.
uint sheetId = 1;
if (sheets.Elements().Count() > 0)
{
sheetId = sheets.Elements().Select(s => s.SheetId.Value).Max() + 1;
}
// Give the new worksheet a name.
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);
spreadsheetDocument.WorkbookPart.Workbook.Save();
// Close the document handle.
spreadsheetDocument.Close();
// Caller must close the stream.
}
}
Published in
OpenXML
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
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
class Program
{
static void Main(string[] args)
{
GetSheetInfo(@"..\..\Documents\Sheet4.xlsx");
}
public static void GetSheetInfo(string fileName)
{
// Open file as read-only.
using (SpreadsheetDocument mySpreadsheet = SpreadsheetDocument.Open(fileName, false))
{
S sheets = mySpreadsheet.WorkbookPart.Workbook.Sheets;
// For each sheet, display the sheet information.
foreach (E sheet in sheets)
{
foreach (A attr in sheet.GetAttributes())
{
Console.WriteLine("{0}: {1}", attr.LocalName, attr.Value);
}
}
}
Console.ReadLine();
}
}
Published in
OpenXML
