/// <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; } }