using System;
using System.Globalization;
using System.IO;
using System.Linq;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        File.Copy(Server.MapPath("template.xlsx"), Server.MapPath("generated.xlsx"), true);

        using (SpreadsheetDocument myWorkbook = SpreadsheetDocument.Open(Server.MapPath("generated.xlsx"), true))
        {
            WorkbookPart workbookPart = myWorkbook.WorkbookPart;
            WorksheetPart worksheetPart = GetWorkSheetPart(workbookPart, "Лист1");

            SheetData sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();

            using (DataClassesDataContext db = new DataClassesDataContext())
            {
                int index = 2;
                var query = from t in db.Tests select t;

                foreach (var item in query)
                {
                    Row contentRow = CreateContentRow((UInt32)index, item.Title, item.Id, DateTime.Now.AddDays(index));
                    index++;
                    sheetData.AppendChild(contentRow);
                }
            }           
        }
    }   

    WorksheetPart GetWorkSheetPart(WorkbookPart workbookPart, string sheetName)
    {
        string relId = workbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name.Value.Equals(sheetName)).First().Id;
        return (WorksheetPart)workbookPart.GetPartById(relId);
    }

    Row CreateContentRow(UInt32 index, string title, int id, DateTime date)
    {
        Row r = new Row { RowIndex = (UInt32) index };
        Cell cell1 = CreateNumberCell("A", index, id);
        Cell cell2 = CreateTextCell("B", index, title);
        Cell cell3 = CreateTextCell("C", index, date.ToString(CultureInfo.InvariantCulture.DateTimeFormat));
        r.Append(cell1);
        r.Append(cell2);
        r.Append(cell3);
        return r;
    }

    Cell CreateTextCell(string header, UInt32 index, string text)
    {
        Cell c = new Cell { DataType = CellValues.InlineString, CellReference = header + index };
        InlineString istring = new InlineString();
        Text t = new Text { Text = text };
        istring.Append(t);
        c.Append(istring);
        return c;
    }

    Cell CreateNumberCell(string header, UInt32 index, int number)
    {
        Cell c = new Cell { CellReference = header + index };
        CellValue v = new CellValue { Text = number.ToString() };
        c.Append(v);
        return c;
    }
}