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