class Program    {        static private int rowsPerSheet = 100;        static private DataTable ResultsData=http://www.mamicode.com/new DataTable();        static void Main(string[] args)        {            const string queryString = "SELECT OrderID, CustomerID FROM dbo.Orders;";            using (var connection =                   new SqlConnection(@"Data Source=localhost\SQL2008;Initial Catalog=Northwind;Integrated Security=SSPI"))                {                    var command =new SqlCommand(queryString, connection);                    connection.Open();                    SqlDataReader reader = command.ExecuteReader();                                        int c = 0;                    bool firstTime = true;                    //Get the Columns names, types, this will help when we need to format the cells in the excel sheet.                    DataTable dtSchema = reader.GetSchemaTable();                    var listCols = new List<DataColumn>();                    if (dtSchema != null)                    {                        foreach (DataRow drow in dtSchema.Rows)                        {                            string columnName = Convert.ToString(drow["ColumnName"]);                            var column = new DataColumn(columnName, (Type)(drow["DataType"]));                            column.Unique = (bool)drow["IsUnique"];                            column.AllowDBNull = (bool)drow["AllowDBNull"];                            column.AutoIncrement = (bool)drow["IsAutoIncrement"];                            listCols.Add(column);                            ResultsData.Columns.Add(column);                        }                    }                    // Call Read before accessing data.                     while (reader.Read())                    {                        DataRow dataRow = ResultsData.NewRow();                        for (int i = 0; i < listCols.Count; i++)                        {                            dataRow[(listCols[i])] = reader[i];                        }                        ResultsData.Rows.Add(dataRow);                        c++;                        if (c == rowsPerSheet)                        {                            c = 0;                            ExportToOxml(firstTime);                            ResultsData.Clear();                            firstTime = false;                        }                    }                    if (ResultsData.Rows.Count > 0)                    {                        ExportToOxml(firstTime);                        ResultsData.Clear();                    }                    // Call Close when done reading.                    reader.Close();                }        }        private static void ExportToOxml(bool firstTime)        {            const string fileName = @"C:\MyExcel.xlsx";            //Delete the file if it exists.             if (firstTime && File.Exists(fileName))            {                File.Delete(fileName);            }            uint sheetId = 1; //Start at the first sheet in the Excel workbook.                        if (firstTime)            {                //This is the first time of creating the excel file and the first sheet.                // Create a spreadsheet document by supplying the filepath.                // By default, AutoSave = true, Editable = true, and Type = xlsx.                SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.                    Create(fileName, SpreadsheetDocumentType.Workbook);                // Add a WorkbookPart to the document.                WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();                workbookpart.Workbook = new Workbook();                // Add a WorksheetPart to the WorkbookPart.                var worksheetPart = workbookpart.AddNewPart<WorksheetPart>();                var sheetData = http://www.mamicode.com/new SheetData();                worksheetPart.Worksheet = new Worksheet(sheetData);                var bold1 = new Bold();                CellFormat cf = new CellFormat();                // Add Sheets to the Workbook.                Sheets sheets;                sheets = spreadsheetDocument.WorkbookPart.Workbook.                    AppendChild<Sheets>(new Sheets());                // Append a new worksheet and associate it with the workbook.                var sheet = new Sheet()                {                    Id = spreadsheetDocument.WorkbookPart.                        GetIdOfPart(worksheetPart),                    SheetId = sheetId,                    Name = "Sheet" + sheetId                };                sheets.Append(sheet);                //Add Header Row.                var headerRow = new Row();                foreach (DataColumn column in ResultsData.Columns)                {                    var cell = new Cell { DataType = CellValues.String, CellValue = http://www.mamicode.com/new CellValue(column.ColumnName) };                    headerRow.AppendChild(cell);                }                sheetData.AppendChild(headerRow);                foreach (DataRow row in ResultsData.Rows)                {                    var newRow = new Row();                    foreach (DataColumn col in ResultsData.Columns)                    {                        var cell = new Cell                        {                            DataType = CellValues.String,                            CellValue = new CellValue(row[col].ToString())                        };                        newRow.AppendChild(cell);                    }                    sheetData.AppendChild(newRow);                }                workbookpart.Workbook.Save();                spreadsheetDocument.Close();            }            else            {                // Open the Excel file that we created before, and start to add sheets to it.                var spreadsheetDocument = SpreadsheetDocument.Open(fileName, true);                var workbookpart = spreadsheetDocument.WorkbookPart;                if (workbookpart.Workbook == null)                    workbookpart.Workbook = new Workbook();                var worksheetPart = workbookpart.AddNewPart<WorksheetPart>();                var sheetData = http://www.mamicode.com/new SheetData();                worksheetPart.Worksheet = new Worksheet(sheetData);                var sheets = spreadsheetDocument.WorkbookPart.Workbook.Sheets;                if (sheets.Elements<Sheet>().Any())                {                    //Set the new sheet id                    sheetId = sheets.Elements<Sheet>().Max(s => s.SheetId.Value) + 1;                }                else                {                    sheetId = 1;                }                // Append a new worksheet and associate it with the workbook.                var sheet = new Sheet()                {                    Id = spreadsheetDocument.WorkbookPart.                        GetIdOfPart(worksheetPart),                    SheetId = sheetId,                    Name = "Sheet" + sheetId                };                sheets.Append(sheet);                //Add the header row here.                var headerRow = new Row();                foreach (DataColumn column in ResultsData.Columns)                {                    var cell = new Cell { DataType = CellValues.String, CellValue = http://www.mamicode.com/new CellValue(column.ColumnName) };                    headerRow.AppendChild(cell);                }                sheetData.AppendChild(headerRow);                                foreach (DataRow row in ResultsData.Rows)                {                    var newRow = new Row();                    foreach (DataColumn col in ResultsData.Columns)                    {                        var cell = new Cell                        {                            DataType = CellValues.String,                            CellValue = new CellValue(row[col].ToString())                        };                        newRow.AppendChild(cell);                    }                    sheetData.AppendChild(newRow);                }                workbookpart.Workbook.Save();                // Close the document.                spreadsheetDocument.Close();            }        }    }