海量数据导出（感谢国外大神的热情帮助）
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();
}
}
}
}
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();
}
谢绝盗版
