When exporting a large number of Excel files, the usual method can take a long time. Below is a personal collection of methods that can handle massive amounts of data:
```csharp
protected void CreateExecl(string swhere, string title)
{
string saveFileName = Server.MapPath("http://www.cnblogs.com/uploads/file/" + title);
bool fileSaved = false;
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
{
return;
}
Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1]; // Get sheet1
System.Data.DataTable table = new System.Data.DataTable();
table.Columns.Add("tagno", typeof(string));
table.Columns.Add("AddDate", typeof(string));
SqlDataReader dr = mydb.ExecuteReaderSQL("SELECT tagno, AddDate FROM tagno WHERE " + swhere + " ORDER BY tagnoid ASC", null);
while (dr.Read())
{
table.Rows.Add(dr["tagno"].ToString(), dr["AddDate"].ToString());
}
dr.Close();
long rows = table.Rows.Count;
Int32 _pagecount = 60000;
if (rows > 60000)
{
long pageRows = _pagecount; // Define the number of rows to display per page; the number must be less than ...
int scount = (int)(rows / pageRows);
if (scount * pageRows 1)
{
object missing = System.Reflection.Missing.Value;
worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets.Add(missing, missing, missing, missing); // Add a sheet
}
else
{
worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[sc]; // Get sheet1
}
string[,] datas = new string[pageRows + 1, 2];
datas[0, 0] = "吊牌号"; // Tag number
datas[0, 1] = "生成时间"; // Generation time
Microsoft.Office.Interop.Excel.Range range = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, 2]);
range.Interior.ColorIndex = 15; // 15 represents gray
range.Font.Bold = true;
range.Font.Size = 9;
int init = int.Parse(((sc - 1) * pageRows).ToString());
int r = 0;
int index = 0;
Int32 result;
if (pageRows * sc >= table.Rows.Count)
{
result = table.Rows.Count;
}
else
{
result = int.Parse((pageRows * sc).ToString());
}
for (r = init; r < result; r++)
{
index = index + 1;
for (int i = 0; i < 2; i++)
{
object obj = table.Rows[r][i];
datas[index, i] = obj == null ? "" : "’" + obj.ToString().Trim();
}
}
Microsoft.Office.Interop.Excel.Range fchR = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[index + 1, 2]);
fchR.Value2 = datas;
worksheet.Columns.EntireColumn.AutoFit(); // Auto-fit column width.
range = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[index + 1, 2]);
range.Font.Size = 9;
range.RowHeight = 14.25;
range.Borders.LineStyle = 1;
range.HorizontalAlignment = 1;
}
}
else
{
string[,] datas = new string[table.Rows.Count + 2, 2];
datas[0, 0] = "吊牌号"; // Tag number
datas[0, 1] = "生成时间"; // Generation time
Microsoft.Office.Interop.Excel.Range range = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, 2]);
range.Interior.ColorIndex = 15; // 15 represents gray
range.Font.Bold = true;
range.Font.Size = 9;
int r = 0;
for (r = 0; r < table.Rows.Count; r++)
{
for (int i = 0; i < 2; i++)
{
object obj = table.Rows[r][i];
datas[r + 1, i] = obj == null ? "" : "’" + obj.ToString().Trim();
}
}
Microsoft.Office.Interop.Excel.Range fchR = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[table.Rows.Count + 2, 2]);
fchR.Value2 = datas;
worksheet.Columns.EntireColumn.AutoFit(); // Auto-fit column width.
range = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[table.Rows.Count + 1, 2]);
range.Font.Size = 9;
range.RowHeight = 14.25;
range.Borders.LineStyle = 1;
range.HorizontalAlignment = 1;
}
if (!string.IsNullOrEmpty(saveFileName))
{
workbook.Saved = true;
workbook.SaveCopyAs(saveFileName);
fileSaved = true;
}
else
{
fileSaved = false;
}
xlApp.Quit();
GC.Collect(); // Forcefully destroy
if (fileSaved && System.IO.File.Exists(saveFileName)) System.Diagnostics.Process.Start(saveFileName); // Open Excel
}
```
**Original Article**: [Eden Network](http://www.edenw.com/tech/devdeloper/net/2010-07-18/4784.html)