public void WriteToExcel(string filename, IDataReader reader) { using (SpreadsheetDocument xl = SpreadsheetDocument.Create(filename, SpreadsheetDocumentType.Workbook)) { xl.AddWorkbookPart(); WorkbookPart workbookPart = xl.WorkbookPart; StyleManager styleMgr = new StyleManager(); WorkbookStylesPart stylePart = workbookPart.AddNewPart <WorkbookStylesPart>(); #region Create worksheet var sheets = WriteSheet(workbookPart, reader, styleMgr, stylePart); #endregion Create worksheet var oxw = OpenXmlWriter.Create(xl.WorkbookPart); oxw.WriteStartElement(new Workbook()); oxw.WriteStartElement(new Sheets()); // you can use object initialisers like this only when the properties // are actual properties. SDK classes sometimes have property-like properties // but are actually classes. For example, the Cell class has the CellValue // "property" but is actually a child class internally. // If the properties correspond to actual XML attributes, then you're fine. foreach (var sheet in sheets) { oxw.WriteElement(sheet); } // this is for Sheets oxw.WriteEndElement(); // this is for Workbook oxw.WriteEndElement(); oxw.Close(); WriteStyle(stylePart, styleMgr); xl.Close(); } }
public override void WriteHeader(OpenXmlWriter oxw, IDataReader reader, StyleManager styleMgr) { rowCount = 0; hasNext = true; var fontId = styleMgr.GetXmlId(new xFont() { Name = "Arial", Family = 2, Size = 10, Bold = true }); var borderId = styleMgr.GetXmlId(new xBorder() { Left = true, Right = true, Top = true, Bottom = true }); var fillId = styleMgr.GetXmlId(new xFill() { PatternType = PatternValues.Solid, FillCollor = "BFBFBF" }); var styleId = styleMgr.GetXmlId(new xCellFormat() { FontId = fontId, HAlign = "Center", VAlign = "Center", BorderId = borderId, FillId = fillId, WrapText = true }); cellFactory = new Dictionary <int, Func <uint, string, Cell> >(); var numFmtId = styleMgr.GetXmlId(new xNumberFormat { FormatCode = "#,##0" }); var textFontId = styleMgr.GetXmlId(new xFont() { Name = "Arial", Family = 2, Size = 8 }); var bodyBorderId = styleMgr.GetXmlId(new xBorder() { Left = true, Right = true, Top = true, Bottom = true }); var textStyleId = styleMgr.GetXmlId(new xCellFormat() { FontId = textFontId, HAlign = "Left", BorderId = bodyBorderId, WrapText = true, VAlign = "Center" }); var numberStyleId = styleMgr.GetXmlId(new xCellFormat() { FontId = textFontId, HAlign = "Right", BorderId = bodyBorderId, WrapText = true, NumberFormatId = numFmtId, VAlign = "Center" }); var row = new Row() { RowIndex = 1 }; for (int i = 0; i < reader.FieldCount; i++) { var colname = xCellAddress.GetColumnName((uint)i + 1); var cell = new Cell() { CellReference = colname + "1", StyleIndex = styleId, DataType = CellValues.InlineString, InlineString = new InlineString() { Text = new Text(reader.GetName(i)) } }; row.Append(cell); if (IsNumericType(reader.GetFieldType(i))) { var func = new Func <uint, string, Cell>((idx, value) => { return(new Cell() { CellReference = colname + idx, StyleIndex = numberStyleId, DataType = CellValues.Number, CellValue = new CellValue() { Text = value } }); }); cellFactory.Add(i, func); } else { var func = new Func <uint, string, Cell>((idx, value) => { return(new Cell() { CellReference = colname + idx, StyleIndex = textStyleId, DataType = CellValues.InlineString, InlineString = new InlineString() { Text = new Text(value) } }); }); cellFactory.Add(i, func); } } oxw.WriteElement(row); }
public abstract void WriteData(OpenXmlWriter oxw, IDataReader reader, StyleManager styleMgr);
protected virtual List <Sheet> WriteSheet(WorkbookPart workbookPart, IDataReader reader, StyleManager styleMgr, WorkbookStylesPart stylePart) { uint sheetId = 1; var sheets = new List <Sheet>(); do { WorksheetPart wsp = workbookPart.AddNewPart <WorksheetPart>(); var oxw = OpenXmlWriter.Create(wsp); oxw.WriteStartElement(new Worksheet()); var columns = GetColumnFormat().Select(x => x.ToXmlStyle()).ToArray(); if (columns.Length > 0) { var colFmt = new Columns(columns); oxw.WriteElement(colFmt); } oxw.WriteStartElement(new SheetData()); WriteHeader(oxw, reader, styleMgr); WriteData(oxw, reader, styleMgr); // this is for SheetData oxw.WriteEndElement(); var mergeCells = GetMergeCells() .Select(x => new MergeCell { Reference = new StringValue(x) }) .ToArray(); if (mergeCells.Length > 0) { oxw.WriteElement(new MergeCells(mergeCells) { Count = (uint)mergeCells.Length }); } // this is for Worksheet oxw.WriteEndElement(); oxw.Close(); sheets.Add(new Sheet() { Name = string.Format("Data{0}", sheetId), SheetId = sheetId, Id = workbookPart.GetIdOfPart(wsp) }); sheetId++; } while (NextSheet()); return(sheets); }