海量数据导出（感谢国外大神的热情帮助）

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220

using System; using System.Collections.Generic; using System.IO; using System.Linq; using System.Data; using System.Data.SqlClient; using System.Reflection; using DocumentFormat.OpenXml; using DocumentFormat.OpenXml.Packaging; using DocumentFormat.OpenXml.Spreadsheet;   namespace VankeWeb.BaseClass {     public  class ExportToExcel     {         public int rowsPerSheet = 10000;         public  DataTable ResultsData=new DataTable();         public  void ExportToExcels(DataTable dt,string path)         {                 DataTableReader reader = dt.CreateDataReader();                                           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,path);                             ResultsData.Clear();                             firstTime = false;                         }                     }                     if (ResultsData.Rows.Count > 0)                     {                         ExportToOxml(firstTime,path);                         ResultsData.Clear();                     }                     // Call Close when done reading.                     reader.Close();                                                                    }           private  void ExportToOxml(bool firstTime,string path)         {              string fileName = path;               //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 = 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 = 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 = 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 = 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();             }         }     }         }<br><br><br>

string str4 = "UploadFiles/Excel/" + exportFileName + ".xls";
bool flag = new ExportExcel().ExportExcelData(baseDirectory, exportFileName, dtExport);
if (flag)
{
int startIndex = str4.LastIndexOf("/") + 1;
string str2 = str4.Substring(startIndex, str4.Length - startIndex);
Response.Clear();
Response.Charset = "utf-8";
Response.Buffer = true;
EnableViewState = false;
Response.ContentEncoding = Encoding.UTF8;
Response.ContentType = "application/vnd.ms-excel";
Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(str2, Encoding.UTF8));
Response.WriteFile(Server.MapPath("~") + str4);
Response.Flush();
Response.Close();
Response.End();
}

　　谢绝盗版