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
//写入字段
for (int i = 0; i < gridview.Columns.View.VisibleColumns.Count; i++)
{
worksheet.Cells[1, i + 1] = gridview.GetVisibleColumn(i).Caption;
}
//写入数值
int r;
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))
{
worksheet.Cells[r + 2, i + 1] = gridview.GetRowCellDisplayText(r, gridview.GetVisibleColumn(i));//dt.Rows[r];
object obj = table.Rows[r][gridview.GetVisibleColumn(i).FieldName];
worksheet.Cells[r + 2, i + 1] = obj == null ? "": "'"+obj.ToString();
}
}
System.Windows.Forms.Application.DoEvents();
}
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
以上导出的代码在网上铺天盖地了已经,它仅适合导出那些最多几百几千的数据量,当使用上述代码导出10万甚至20多万的海量数据时,至少几个小时甚至导出失败。。。。
经过几天的研究,本人发现上述导出的核心代码存在问题,导致速度缓慢,下边看看我修改后的程序:
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:0x800A03EC。因为: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 = new string[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 = new string[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
着重看上边蓝色代码的部分,经过前后两种导出方式的对比,您会发现,导出的思想改变了:
原来的程序将数据一个表格一个表格地写入到EXCEL中;修改后的程序先将数据存入二维数组中,然后再将数组值赋予EXCEL应用程序对象的VALUE属性
正是导出思想的改变,使导出速度提高不少,请参考我的导出时间数据对照:
2万条:30分钟-->2分钟左右;3万条:大于60分钟-->3分钟;20万7000多条:导出失败--->15分钟左右
可以明显看出,效率提高了不少吧。
还有一个要提醒大家,EXCEL中SHEET最多存储65535条数据,如果数据过多就需要增加sheet继续存储数据,关于如何使用,上边我修改过的代码中已经有详细的使用,请大家自己去看,这里不再重复。
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
//写入字段
for (int i = 0; i < gridview.Columns.View.VisibleColumns.Count; i++)
{
worksheet.Cells[1, i + 1] = gridview.GetVisibleColumn(i).Caption;
}
//写入数值
int r;
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))
{
worksheet.Cells[r + 2, i + 1] = gridview.GetRowCellDisplayText(r, gridview.GetVisibleColumn(i));//dt.Rows[r]
object obj = table.Rows[r][gridview.GetVisibleColumn(i).FieldName];
worksheet.Cells[r + 2, i + 1] = obj == null ? "": "'"+obj.ToString();
}
}
System.Windows.Forms.Application.DoEvents();
}
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
以上导出的代码在网上铺天盖地了已经,它仅适合导出那些最多几百几千的数据量,当使用上述代码导出10万甚至20多万的海量数据时,至少几个小时甚至导出失败。。。。
经过几天的研究,本人发现上述导出的核心代码存在问题,导致速度缓慢,下边看看我修改后的程序:
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:0x800A03EC。因为: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 = new string[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 = new string[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
着重看上边蓝色代码的部分,经过前后两种导出方式的对比,您会发现,导出的思想改变了:
原来的程序将数据一个表格一个表格地写入到EXCEL中;修改后的程序先将数据存入二维数组中,然后再将数组值赋予EXCEL应用程序对象的VALUE属性
正是导出思想的改变,使导出速度提高不少,请参考我的导出时间数据对照:
2万条:30分钟-->2分钟左右;3万条:大于60分钟-->3分钟;20万7000多条:导出失败--->15分钟左右
可以明显看出,效率提高了不少吧。
还有一个要提醒大家,EXCEL中SHEET最多存储65535条数据,如果数据过多就需要增加sheet继续存储数据,关于如何使用,上边我修改过的代码中已经有详细的使用,请大家自己去看,这里不再重复。
![](http://www.cnblogs.com/images/cnblogs_com/920724025/日草.gif)