Open XML Create Excel from scratch

  • Article

Open XML SDK allows you to work with Office products without installing any COM component. It also solves the age old issue of Office automation in server. Mainly in web scenario, it is recommended that one should not automate any office components or activities. Reason behind it is, if anything is crashed while automating then the server will hold that object and over a period of time it may pile up and eat the required memory. So we were generating office documents from Web Application with utter caution. Only if it is absolutely necessary. After Open XML SDK were available all these has become very easy but we need more understanding of the structure of the document how it creates,

static void CreateExcelFile(string filePath, List<Employee> lstEmps )
{
using (SpreadsheetDocument spreedDoc = SpreadsheetDocument.Create(filePath,
DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook))
{
WorkbookPart wbPart = spreedDoc.WorkbookPart;
if (wbPart == null)
{
wbPart = spreedDoc.AddWorkbookPart();
wbPart.Workbook = new Workbook();
}

        string sheetName = "Wriju";
WorksheetPart worksheetPart = null;
worksheetPart = wbPart.AddNewPart<WorksheetPart>();
var sheetData = new SheetData();

        worksheetPart.Worksheet = new Worksheet(sheetData);

        if (wbPart.Workbook.Sheets == null)
{
wbPart.Workbook.AppendChild<Sheets>(new Sheets());
}

        var sheet = new Sheet()
{
Id = wbPart.GetIdOfPart(worksheetPart),
SheetId = 1,
Name = sheetName
};

        var workingSheet = ((WorksheetPart)wbPart.GetPartById(sheet.Id)).Worksheet;

        int rowindex = 1;
foreach (var emp in lstEmps)
{
Row row = new Row();
row.RowIndex = (UInt32)rowindex;

            if (rowindex == 1) //Header
{
row.AppendChild(AddCellWithText("Name"));
row.AppendChild(AddCellWithText("Email"));
}
else //Data
{
row.AppendChild(AddCellWithText(emp.Name));
row.AppendChild(AddCellWithText(emp.Email));
}

            sheetData.AppendChild(row);
rowindex++;
}

        wbPart.Workbook.Sheets.AppendChild(sheet);

        //Set Border
//wbPark

        wbPart.Workbook.Save();
}
}

static Cell AddCellWithText(string text)
{
Cell c1 = new Cell();
c1.DataType = CellValues.InlineString;

    InlineString inlineString = new InlineString();
Text t = new Text();
t.Text = text;
inlineString.AppendChild(t);

    c1.AppendChild(inlineString);

    return c1;
}

Namoskar!!!