예제 #1
0
        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);
        }
예제 #3
0
 public abstract void WriteData(OpenXmlWriter oxw, IDataReader reader, StyleManager styleMgr);
예제 #4
0
        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);
        }