C#如何快速高效地导出10万以上的大量数据？

C#如何快速高效地导出10万以上的大量数据？

string saveFileName = "";

bool fileSaved = false;

SaveFileDialog saveDialog = new SaveFileDialog();

saveDialog.DefaultExt = "xls";

saveDialog.Filter = "Excel文件|*.xls";

saveDialog.FileName = "Sheet1";

saveDialog.ShowDialog();

saveFileName = saveDialog.FileName;

if (saveFileName.IndexOf(":") < 0) return; //被点了取消

ExcelApp xlApp = new ExcelApp();

if (xlApp == null)

{

strMessage = "无法创建Excel对象，可能您的计算机上未安装Excel软件。";

MessageBox.Show("无法创建Excel对象，可能您的计算机上未安装Excel软件。");

return;

}

Workbooks workbooks = xlApp.Workbooks;

Workbook workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet);

Worksheet worksheet = (Worksheet)workbook.Worksheets[1];//取得sheet1

long rows = table.Rows.Count;

/*下边注释的两行代码当数据行数超过行时，出现异常：异常来自HRESULT:0x03EC。因为：Excel 2003每个sheet只支持最大行数据

//Range

fchR = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[table.Rows.Count+2, gridview.Columns.View.VisibleColumns.Count+1]);

//fchR.Value2 = datas;*/

if (rows > 65535)

{

long pageRows = 60000;//定义每页显示的行数,行数必须小于

int scount = (int)(rows / pageRows);

if (scount * pageRows < table.Rows.Count)//当总行数不被pageRows整除时，经过四舍五入可能页数不准

{

scount = scount + 1;

}

for (int sc = 1; sc <= scount; sc++)

{

if (sc > 1)

{

object missing = System.Reflection.Missing.Value;

worksheet = (Excel.Worksheet)workbook.Worksheets.Add(

missing, missing, missing, missing);//添加一个sheet

}

else

{

worksheet = (Worksheet)workbook.Worksheets[sc];//取得sheet1

}

string[,]datas=newstring[pageRows+ 1, gridview.Columns.View.VisibleColumns.Count + 1];

for (int i = 0; i < gridview.Columns.View.VisibleColumns.Count; i++) //写入字段

{

datas[0, i] = gridview.GetVisibleColumn(i).Caption;

}

Range range=worksheet.get_Range(worksheet.Cells[1,1],worksheet.Cells[1, gridview.Columns.View.VisibleColumns.Count]);

range.Interior.ColorIndex = 15;//15代表灰色

range.Font.Bold = true;

range.Font.Size = 9;

int init = int.Parse(((sc - 1) * pageRows).ToString());

int r = 0;

int index = 0;

int 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 < gridview.Columns.View.VisibleColumns.Count; i++)

{

if(gridview.GetVisibleColumn(i).ColumnType==typeof(string)|| gridview.GetVisibleColumn(i).ColumnType==typeof(Decimal)|| gridview.GetVisibleColumn(i).ColumnType == typeof(DateTime))

{

object obj = table.Rows[r][gridview.GetVisibleColumn(i).FieldName];

datas[index, i] = obj == null ? "" : "'" + obj.ToString().Trim();//在obj.ToString()前加单引号是为了防止自动转化格式

}

}

System.Windows.Forms.Application.DoEvents();

}

Range fchR = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[index + 2, gridview.Columns.View.VisibleColumns.Count + 1]);

fchR.Value2 = datas;

worksheet.Columns.EntireColumn.AutoFit();//列宽自适应。

range = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[index + 1, gridview.Columns.View.VisibleColumns.Count]);

//15代表灰色

range.Font.Size = 9;

range.RowHeight = 14.25;

range.Borders.LineStyle = 1;

range.HorizontalAlignment = 1;

}

}

else

{

string[,]datas=newstring[table.Rows.Count+2,

gridview.Columns.View.VisibleColumns.Count + 1];

for (int i = 0; i < gridview.Columns.View.VisibleColumns.Count; i++) //写入字段

{

datas[0, i] = gridview.GetVisibleColumn(i).Caption;

}

Range range = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, gridview.Columns.View.VisibleColumns.Count]);

range.Interior.ColorIndex = 15;//15代表灰色

range.Font.Bold = true;

range.Font.Size = 9;

int r = 0;

for (r = 0; r < table.Rows.Count; r++)

{

for (int i = 0; i < gridview.Columns.View.VisibleColumns.Count; i++)

{

if(gridview.GetVisibleColumn(i).ColumnType==typeof(string)||

gridview.GetVisibleColumn(i).ColumnType==typeof(Decimal)||

gridview.GetVisibleColumn(i).ColumnType == typeof(DateTime))

{

object obj = table.Rows[r][gridview.GetVisibleColumn(i).FieldName];

datas[r + 1, i] = obj == null ? "" : "'" + obj.ToString().Trim();//在obj.ToString()前加单引号是为了防止自动转化格式

}

}

System.Windows.Forms.Application.DoEvents();

}

Range fchR = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[table.Rows.Count + 2, gridview.Columns.View.VisibleColumns.Count + 1]);

fchR.Value2 = datas;

worksheet.Columns.EntireColumn.AutoFit();//列宽自适应。

range = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[table.Rows.Count + 1, gridview.Columns.View.VisibleColumns.Count]);

//15代表灰色

range.Font.Size = 9;

range.RowHeight = 14.25;

range.Borders.LineStyle = 1;

range.HorizontalAlignment = 1;

}

if (saveFileName != "")

{

try

{

workbook.Saved = true;

workbook.SaveCopyAs(saveFileName);

fileSaved = true;

}

catch (Exception ex)

{

strMessage = "导出文件时出错,文件可能正被打开";

fileSaved = false;

MessageBox.Show("导出文件时出错,文件可能正被打开！"n" + ex.Message);

}

}

else

{

fileSaved = false;

}

xlApp.Quit();

GC.Collect();//强行销毁

If(fileSaved&&System.IO.File.Exists(saveFileName))

System.Diagnostics.Process.Start(saveFileName); //打开EXCEL

转载于:https://www.cnblogs.com/jrzlldsj/archive/2012/09/25/2703176.html