/// <summary>
/// Set the width for a range of columns
/// </summary>
/// <param name="worksheet">Worksheet containing the columns to be affected</param>
/// <param name="fromColumn">Initial column to affect</param>
/// <param name="toColumn">Final column to affect</param>
/// <param name="width">Column width</param>
public static void SetColumnWidth(OpenXmlSDK.WorksheetPart worksheet, short fromColumn, short toColumn, int width)
{
//Get the worksheet markup
XDocument worksheetXDocument = XDocument.Load(new XmlTextReader(worksheet.GetStream()));
//Look for worksheet cols element
XElement colsXElement = worksheetXDocument.Root.Element(ns + "cols");
if (colsXElement == null)
{
//cols elements does not exist
//create a new one
colsXElement = new XElement(ns + "cols");
//create a new col element (for setting the width)
//the col element could span more than one column -span is controlled by min (initial column) and max (final column) attributes
colsXElement.Add(new XElement(ns + "col",
new XAttribute("min", fromColumn.ToString()),
new XAttribute("max", toColumn.ToString()),
new XAttribute("width", width.ToString()),
new XAttribute("customWidth", "1")));
//cols element must be added before worksheet sheetData element
worksheetXDocument.Root.Element(ns + "sheetData").AddBeforeSelf(colsXElement);
}
else
{
//look for a col element for the column range indicated for fromColumn and toColumn
XElement colXElement = colsXElement.Elements(ns + "col")
.Where(c => (System.Convert.ToInt32(c.Attribute("min").Value) == fromColumn) && (System.Convert.ToInt32(c.Attribute("max").Value) == toColumn)).FirstOrDefault();
if (colXElement != null)
{
//col element does exist
//change its width value
colXElement.SetAttributeValue("width", width);
}
else
{
//col element does not exist
//create a new one
colsXElement.Add(new XElement(ns + "col",
new XAttribute("min", fromColumn.ToString()),
new XAttribute("max", toColumn.ToString()),
new XAttribute("width", width.ToString()),
new XAttribute("customWidth", "1")));
}
}
//Update the worksheet part markup at worksheet part stream
XmlWriter worksheetWriter = XmlTextWriter.Create(worksheet.GetStream(System.IO.FileMode.Create));
worksheetXDocument.WriteTo(worksheetWriter);
worksheetWriter.Flush();
worksheetWriter.Close();
}
/// <summary>
/// Set the value for a specific cell
/// </summary>
/// <param name="worksheet">Worksheet part containing the cell to be affected</param>
/// <param name="fromColumn">Initial column for setting the value</param>
/// <param name="fromRow">Initial row for setting the value</param>
/// <param name="toColumn">Final column for setting the value</param>
/// <param name="toRow">Final row for setting the value</param>
/// <param name="value">Cell value</param>
public static void SetCellValue(OpenXmlSDK.WorksheetPart worksheet, int fromRow, int toRow, short fromColumn, short toColumn, string value)
{
XDocument worksheetXDocument = XDocument.Load(new XmlTextReader(worksheet.GetStream()));
for (int row = fromRow; row <= toRow; row++)
{
for (int col = fromColumn; col <= toColumn; col++)
{
AddValue(worksheetXDocument, row, col, value);
}
}
XmlWriter worksheetWriter = XmlTextWriter.Create(worksheet.GetStream(System.IO.FileMode.Create));
worksheetXDocument.WriteTo(worksheetWriter);
worksheetWriter.Flush();
worksheetWriter.Close();
}
/// <summary>
/// Apply a cell style to a specific cell
/// </summary>
/// <param name="worksheet">worksheet containing the cell to be affected</param>
/// <param name="fromColumn">Starting Cell Column</param>
/// <param name="toColumn">Ending Cell Column</param>
/// <param name="fromRow">Starting Cell Row</param>
/// <param name="toRow">Ending Cell Row</param>
/// <param name="cellStyle">Cell Style</param>
public static void SetCellStyle(OpenXmlSDK.WorksheetPart worksheet, short fromColumn, short toColumn, int fromRow, int toRow, string cellStyle)
{
XDocument worksheetXDocument = XDocument.Load(new XmlTextReader(worksheet.GetStream()));
for (int row = fromRow; row <= toRow; row++)
{
for (short col = fromColumn; col <= toColumn; col++)
{
XElement cellXelement = GetCell(worksheetXDocument, col, row);
cellXelement.SetAttributeValue("s", styles.GetCellStyleIndex(cellStyle));
}
}
XmlWriter worksheetWriter = XmlTextWriter.Create(worksheet.GetStream(System.IO.FileMode.Create));
worksheetXDocument.WriteTo(worksheetWriter);
worksheetWriter.Flush();
worksheetWriter.Close();
}
/// <summary>
/// Get value for a cell in a worksheet
/// </summary>
/// <param name="worksheet"></param>
/// <param name="column"></param>
/// <param name="row"></param>
/// <remarks>Author:Johann Granados Company: Staff DotNet Creation Date: 8/30/2008</remarks>
/// <returns></returns>
public static string GetValue(OpenXmlSDK.WorksheetPart worksheet, short column, int row)
{
XDocument worksheetXDocument = XDocument.Load(new XmlTextReader(worksheet.GetStream()));
XElement cellValueXElement = GetCell(worksheetXDocument, column, row);
if (cellValueXElement != null)
{
if (cellValueXElement.Element(ns + "v") != null)
{
return(sharedStrings.GetSharedString(System.Convert.ToInt32(cellValueXElement.Value)));
}
else
{
return(cellValueXElement.Element(ns + "is").Element(ns + "t").Value);
}
}
else
{
return(string.Empty);
}
}
/// <summary>
/// Get value for a cell in a worksheet
/// </summary>
/// <param name="worksheet"></param>
/// <param name="column"></param>
/// <param name="row"></param>
/// <remarks>Author:Johann Granados Company: Staff DotNet Creation Date: 8/30/2008</remarks>
/// <returns></returns>
public static string GetValue(SpreadsheetDocument document, WorksheetPart worksheet, short column, int row)
{
XDocument worksheetXDocument = XDocument.Load(new XmlTextReader(worksheet.GetStream()));
XElement cellValueXElement = GetCell(worksheetXDocument, column, row);
if (cellValueXElement != null)
{
if (cellValueXElement.Element(ns + "v") != null)
{
return GetSharedString(document, System.Convert.ToInt32(cellValueXElement.Value));
}
else
{
return cellValueXElement.Element(ns + "is").Element(ns + "t").Value;
}
}
else
{
return string.Empty;
}
}
