private static void CriarLinha(DocumentFormat.OpenXml.OpenXmlWriter writer, System.Data.DataTable dt, int indexColumn, int rowDefault, int styleSheetIndex) { foreach (DataRow dr in dt.Rows) { rowDefault++; writer.WriteStartElement(new OpenXmlSpread.Row { RowIndex = (UInt32)(rowDefault) }); for (int col = 0; col < dt.Columns.Count; col++) { var reference = (GetExcelColumnName(indexColumn + col) + rowDefault.ToString()); writer.WriteElement(new OpenXmlSpread.Cell { CellReference = reference, CellValue = new OpenXmlSpread.CellValue(dr.ItemArray[col].ToString()), DataType = new EnumValue <OpenXmlSpread.CellValues>(OpenXmlSpread.CellValues.Date), StyleIndex = (UInt32)styleSheetIndex }); } writer.WriteEndElement(); } writer.WriteEndElement(); writer.WriteEndElement(); }
public void WriteCell(OpenXmlWriter writer, object value) { writer.WriteStartElement(new Cell(), CellAttributes); writer.WriteStartElement(new InlineString()); writer.WriteStartElement(new Text()); writer.WriteString(value.ToString()); writer.WriteEndElement(); writer.WriteEndElement(); writer.WriteEndElement(); }
public void WriteCell(OpenXmlWriter writer, object value) { var dateTime = (DateTime) value; writer.WriteStartElement(new Cell(), new [] { new OpenXmlAttribute("s", null, "1"), }); writer.WriteStartElement(new CellValue()); writer.WriteString(dateTime.ToOADate().ToString(CultureInfo.InvariantCulture)); writer.WriteEndElement(); writer.WriteEndElement(); }
public void WriteCell(OpenXmlWriter writer, object value) { writer.WriteStartElement(new Cell(), new [] { new OpenXmlAttribute("t", null, "b"), }); var b = (bool) value; writer.WriteStartElement(new CellValue()); writer.WriteString(BooleanValue.FromBoolean(b).ToString()); writer.WriteEndElement(); writer.WriteEndElement(); }
private static void AjustaLarguraColunas(DocumentFormat.OpenXml.OpenXmlWriter writer, int startColunaIndex, int larguraColuna, int ColunsCount) { var xmlCols = new List <DocumentFormat.OpenXml.OpenXmlAttribute>(); xmlCols.Add(new DocumentFormat.OpenXml.OpenXmlAttribute("min", null, startColunaIndex.ToString())); xmlCols.Add(new DocumentFormat.OpenXml.OpenXmlAttribute("max", null, (startColunaIndex + ColunsCount).ToString())); xmlCols.Add(new DocumentFormat.OpenXml.OpenXmlAttribute("width", null, larguraColuna.ToString())); writer.WriteStartElement(new OpenXmlSpread.Column(), xmlCols); writer.WriteEndElement(); writer.WriteEndElement(); }
private static void CriarColunas(DocumentFormat.OpenXml.OpenXmlWriter writer, System.Data.DataTable dt, int indexColumn, int rowDefault, int styleIndexDefault) { for (int col = 0; col < dt.Columns.Count; col++) { var reference = (GetExcelColumnName(indexColumn + col) + rowDefault.ToString()); DataColumn cols = dt.Columns[col]; writer.WriteElement(new OpenXmlSpread.Cell { CellReference = reference, CellValue = new OpenXmlSpread.CellValue(cols.ColumnName.ToUpper()), StyleIndex = (UInt32)styleIndexDefault, DataType = OpenXmlSpread.CellValues.String }); } writer.WriteEndElement(); }
/// <summary> /// CellValues = Boolean -> expects cellValue "True" or "False" /// CellValues = InlineString -> stores string within sheet /// CellValues = SharedString -> stores index within sheet. If this is called, please call CreateShareStringPart after creating all sheet data to create the shared string part /// CellValues = Date -> expects ((DateTime)value).ToOADate().ToString(CultureInfo.InvariantCulture) as cellValue /// and new OpenXmlAttribute[] { new OpenXmlAttribute("s", null, "1") }.ToList() as attributes so that the correct formatting can be applied /// /// </summary> /// <param name="writer"></param> /// <param name="cellValue"></param> /// <param name="dataType"></param> /// <param name="attributes"></param> public void WriteCellValueSax(OpenXmlWriter writer, string cellValue, CellValues dataType, List<OpenXmlAttribute> attributes = null) { switch (dataType) { case CellValues.InlineString: { if (attributes == null) { attributes = new List<OpenXmlAttribute>(); } attributes.Add(new OpenXmlAttribute("t", null, "inlineStr")); writer.WriteStartElement(new Cell(), attributes); writer.WriteElement(new InlineString(new Text(cellValue))); writer.WriteEndElement(); break; } case CellValues.SharedString: { if (attributes == null) { attributes = new List<OpenXmlAttribute>(); } attributes.Add(new OpenXmlAttribute("t", null, "s"));//shared string type writer.WriteStartElement(new Cell(), attributes); if (!_shareStringDictionary.ContainsKey(cellValue)) { _shareStringDictionary.Add(cellValue, _shareStringMaxIndex); _shareStringMaxIndex++; } //writing the index as the cell value writer.WriteElement(new CellValue(_shareStringDictionary[cellValue].ToString())); writer.WriteEndElement();//cell break; } case CellValues.Date: { if (attributes == null) { writer.WriteStartElement(new Cell() { DataType = CellValues.Number }); } else { writer.WriteStartElement(new Cell() { DataType = CellValues.Number }, attributes); } writer.WriteElement(new CellValue(cellValue)); writer.WriteEndElement(); break; } case CellValues.Boolean: { if (attributes == null) { attributes = new List<OpenXmlAttribute>(); } attributes.Add(new OpenXmlAttribute("t", null, "b"));//boolean type writer.WriteStartElement(new Cell(), attributes); writer.WriteElement(new CellValue(cellValue == "True" ? "1" : "0")); writer.WriteEndElement(); break; } default: { if (attributes == null) { writer.WriteStartElement(new Cell() { DataType = dataType }); } else { writer.WriteStartElement(new Cell() { DataType = dataType }, attributes); } writer.WriteElement(new CellValue(cellValue)); writer.WriteEndElement(); break; } } }
public void WriteCell(OpenXmlWriter writer, object value) { writer.WriteStartElement(new Cell()); writer.WriteStartElement(new CellValue()); writer.WriteString(string.Format(CultureInfo.InvariantCulture, "{0}", value)); writer.WriteEndElement(); writer.WriteEndElement(); }