//complete code sample using Mikes Knowlegebase libs    

private static void WriteExcelFile(DataSet ds, SpreadsheetDocument spreadsheet)
    {
      //  Create the Excel file contents.  This function is used when creating an Excel file either writing 
      //  to a file, or writing to a MemoryStream.
      spreadsheet.AddWorkbookPart();
      spreadsheet.WorkbookPart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook();

      //  My thanks to James Miera for the following line of code (which prevents crashes in Excel 2010)
      spreadsheet.WorkbookPart.Workbook.Append(new BookViews(new WorkbookView()));

      //  If we don't add a "WorkbookStylesPart", OLEDB will refuse to connect to this .xlsx file !
      WorkbookStylesPart workbookStylesPart = spreadsheet.WorkbookPart.AddNewPart<WorkbookStylesPart>();
      //Stylesheet stylesheet = new Stylesheet(new CellFormats(
      //  new CellFormat(
      //     new Alignment() { Horizontal = HorizontalAlignmentValues.Left, Vertical = VerticalAlignmentValues.Center }
      //  ) { FontId = 0, FillId = 0, BorderId = 0, ApplyAlignment = true })
      //);

      Stylesheet stylesheet = ExcelStyleSheet.GenerateStyleSheet();

      workbookStylesPart.Stylesheet = stylesheet;
      workbookStylesPart.Stylesheet.Save();

      //  Loop through each of the DataTables in our DataSet, and create a new Excel Worksheet for each.
      uint worksheetNumber = 1;
      foreach (DataTable dt in ds.Tables)
      {
        //  For each worksheet you want to create
        string workSheetID = "rId" + worksheetNumber.ToString();
        string worksheetName = dt.TableName;

        WorksheetPart newWorksheetPart = spreadsheet.WorkbookPart.AddNewPart<WorksheetPart>();
        newWorksheetPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet();

        int numberOfColumns = dt.Columns.Count;
        Columns columns = new Columns();
        for (int n = 0; n < numberOfColumns; ++n)
        {
          //double widthTxt = OpenXMLUtils.GetWidth("Calibri", 11, strText);
          double widthMax = GetMaxColWidth(n, dt);
          Column column = new Column();
          uint uCol = (uint)n + 1;
          column.Min = uCol;
          column.Max = uCol;
          column.Width = widthMax;
          columns.Append(column);
        }
        newWorksheetPart.Worksheet.Append(columns);

        // create sheet data
        newWorksheetPart.Worksheet.AppendChild(new DocumentFormat.OpenXml.Spreadsheet.SheetData());

        // save worksheet
        WriteDataTableToExcelWorksheet(dt, newWorksheetPart);
        newWorksheetPart.Worksheet.Save();

        // create the worksheet to workbook relation
        if (worksheetNumber == 1)
          spreadsheet.WorkbookPart.Workbook.AppendChild(new DocumentFormat.OpenXml.Spreadsheet.Sheets());

        spreadsheet.WorkbookPart.Workbook.GetFirstChild<DocumentFormat.OpenXml.Spreadsheet.Sheets>().AppendChild(new DocumentFormat.OpenXml.Spreadsheet.Sheet() {
          Id = spreadsheet.WorkbookPart.GetIdOfPart(newWorksheetPart),
          SheetId = (uint)worksheetNumber,
          Name = dt.TableName
        });

        worksheetNumber++;
      }
      spreadsheet.WorkbookPart.Workbook.Save();
    }

    private static double GetMaxColWidth(int colInx, DataTable dt)
    {
      string cellValue;
      double cellLen = 0;
      double maxCharLen = OpenXMLUtils.GetWidth("Calibri", 11, dt.Columns[colInx].ColumnName);
      foreach (DataRow dr in dt.Rows)
      {
        if (dr.ItemArray[colInx].GetType() != typeof(System.DBNull))
        {
          if (dr.ItemArray[colInx].GetType() == typeof(System.DateTime))
          {
            cellValue = ((DateTime)dr.ItemArray[colInx]).ToString("M/d/yyyy hh:mm");
          }
          else
          {
            cellValue = dr.ItemArray[colInx].ToString();
          }
          cellLen = OpenXMLUtils.GetWidth("Calibri", 11, cellValue);
          if (maxCharLen < cellLen)
          {
            maxCharLen = cellLen;
          }
        }
      }
      return maxCharLen;
    }