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(); }
private static void AppendTextCell(string cellReference, string cellStringValue, ref OpenXmlWriter writer) { // Add a new Excel Cell to our Row writer.WriteElement(new Cell { CellValue = new CellValue(cellStringValue), CellReference = cellReference, DataType = CellValues.String }); }
/// <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; } } }
private static void GerarExcelOpXml(System.Data.DataTable dt, string pathExcelFile, string sheetName, string ColunaDefault, int rowDefault, int larguraColuna) { var styleIndexDefault = 0; var styleIndexDefaultLine = 2; DocumentFormat.OpenXml.UInt32Value styleIndex; using (OpenXmlPackaging.SpreadsheetDocument myDoc = OpenXmlPackaging.SpreadsheetDocument.Open(pathExcelFile, true)) { OpenXmlPackaging.WorksheetPart worksheetPart = GetWorksheetPartByName(myDoc, sheetName); OpenXmlSpread.Cell cellDefault = GetCell(worksheetPart.Worksheet, ColunaDefault, Convert.ToUInt32(rowDefault)); styleIndex = cellDefault.StyleIndex; styleIndexDefault = Convert.ToInt32(styleIndex.Value.ToString()); OpenXmlSpread.Cell cellDefaultline = GetCell(worksheetPart.Worksheet, ColunaDefault, Convert.ToUInt32(rowDefault + 1)); styleIndex = cellDefaultline.StyleIndex; styleIndexDefaultLine = Convert.ToInt32(styleIndex.Value.ToString()); } using (OpenXmlPackaging.SpreadsheetDocument myDoc = OpenXmlPackaging.SpreadsheetDocument.Open(pathExcelFile, true)) { OpenXmlPackaging.WorksheetPart worksheetPart = GetWorksheetPartByName(myDoc, sheetName); OpenXmlSpread.Stylesheet stylesheet = myDoc.WorkbookPart.WorkbookStylesPart.Stylesheet; OpenXmlSpread.CellFormat deafultFormat = new OpenXmlSpread.CellFormat() { Alignment = new OpenXmlSpread.Alignment() { Horizontal = OpenXmlSpread.HorizontalAlignmentValues.Left, Vertical = OpenXmlSpread.VerticalAlignmentValues.Center }, ApplyAlignment = true }; stylesheet.CellFormats.AppendChild(deafultFormat); DocumentFormat.OpenXml.OpenXmlWriter writer = DocumentFormat.OpenXml.OpenXmlWriter.Create(worksheetPart); var indexColumn = ColumnIndex(ColunaDefault); var startColunaIndex = indexColumn + 1; writer.WriteStartElement(new OpenXmlSpread.Worksheet()); writer.WriteStartElement(new OpenXmlSpread.Columns()); AjustaLarguraColunas(writer, startColunaIndex, larguraColuna, dt.Columns.Count); writer.WriteStartElement(new OpenXmlSpread.SheetData()); writer.WriteStartElement(new OpenXmlSpread.Row { RowIndex = (UInt32)rowDefault }); CriarColunas(writer, dt, indexColumn, rowDefault, styleIndexDefault); CriarLinha(writer, dt, indexColumn, rowDefault, styleIndexDefaultLine); writer.Dispose(); } }
protected internal override void CreateDocument() { Report r = base.Report(); _workbookPart = _spreadSheet.AddWorkbookPart(); var openXmlExportHelper = new OpenXmlWriterHelper(); _styleSheet = openXmlExportHelper.CreateDefaultStylesheet(); _workbookPart.Workbook = _workbook; var sheets = _workbook.AppendChild<Sheets>(new Sheets()); // create worksheet 1 var worksheetPart =_workbookPart.AddNewPart<WorksheetPart>(); var sheet = new Sheet() { Id = _workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = r.Name }; sheets.Append(sheet); _workSheetSettings.Add(new WorkSheetSetting(r.Name)); _writer = OpenXmlWriter.Create(worksheetPart); _writer.WriteStartElement(new Worksheet()); _writer.WriteStartElement(new SheetData()); _currentWorkSheet = _workSheetSettings.Last(); }
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(); }