在平时的项目中,经常会有要将数据库中的数据或者GridView中的数据导出到Excel表中的工作,虽然方法很多会比较多,但是导出效率就是一个严重的问题了。几千条数据用普通的方法能够勉强应付,但是如果数据达到了几万几十万呢?这个恐怕就有点难受了。下面介绍一种效率还能过得去的方法,以前在开发项目时使用过百万条数据进行导出测试,测试结果大概在30S左右。
首先在项目中使用GemBox.ExcelLite.dll文件,然后就可以开始工作了。下面是代码,不复杂并且有注释,所以不再累述。
![ContractedBlock.gif](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
![ExpandedBlockStart.gif](https://images.cnblogs.com/OutliningIndicators/ExpandedBlockStart.gif)
///
<summary>
/// 导出GridView中的数据到Excel表,每个sheet最多包含60000条数据
/// </summary>
/// <param name="GridView"> GridView控件 </param>
/// <param name="filePath"> 保存文件的路径 </param>
/// <returns > 导出成功返回true,失败false </returns>
public bool SQLToExcel(GridView dataGridView, string filePath)
{
try
{
ExcelFile excelFile = new ExcelFile();
string filename = DateTime.Now.ToString( " yyyyMMdd " );
int columns = dataGridView.Columns .Count;
int rows = dataGridView.Rows.Count;
int pages = rows / 60000 + 1 ;
ExcelWorksheet[] sheet = new ExcelWorksheet[pages];
int i = 1 ;
for ( int p = 0 ; p < pages; p ++ )
{
sheet[p] = excelFile.Worksheets.Add(filename + p.ToString());
int m = p * 60000 ;
for ( int j = 0 ; j < columns; j ++ )
{
sheet[p].Cells[ 0 , j].Value = dataGridView.Columns[j].HeaderText;
}
for (; i <= rows; i ++ )
{
if (i == 60000 * (p + 1 ))
{
i = i + 1 ;
break ;
}
for ( int j = 0 ; j < columns; j ++ )
{
sheet[p].Cells[i - m, j].Value = dataGridView.Rows[i].Cells[i - 1 ].ToString();
}
}
}
excelFile.SaveXls(filePath + filename + " .xls " );
return true ;
}
catch
{
return false ;
}
}
/// <summary>
/// 根据DataTable导出excel表
/// </summary>
/// <param name="dt"></param>
/// <param name="filePath"></param>
/// <returns></returns>
public bool SQLToExcelDataTable(DataTable dt, string filePath)
{
try
{
ExcelFile excelFile = new ExcelFile();
string filename = DateTime.Now.ToString( " yyyyMMdd " );
int colums = dt.Columns.Count;
int rows = dt.Rows.Count;
int pages = rows / 60000 + 1 ;
ExcelWorksheet[] sheet = new ExcelWorksheet[pages];
int i = 1 ;
for ( int p = 0 ; p < pages; p ++ )
{
sheet[p] = excelFile.Worksheets.Add(filename + p.ToString());
int m = p * 60000 ;
for ( int j = 0 ; j < colums; j ++ )
{
sheet[p].Cells[ 0 , j].Value = dt.Columns[j].ColumnName.ToString();
}
for (; i <= rows; i ++ )
{
if (i == 60000 * (p + 1 ))
{
i = i + 1 ;
break ;
}
for ( int j = 0 ; j < colums; j ++ )
{
sheet[p].Cells[i - m, j].Value = dt.Rows[j][i - 1 ].ToString();
}
}
}
excelFile.SaveXls(filePath + filename + " .xls " );
return true ;
}
catch
{
return false ;
}
}
/// 导出GridView中的数据到Excel表,每个sheet最多包含60000条数据
/// </summary>
/// <param name="GridView"> GridView控件 </param>
/// <param name="filePath"> 保存文件的路径 </param>
/// <returns > 导出成功返回true,失败false </returns>
public bool SQLToExcel(GridView dataGridView, string filePath)
{
try
{
ExcelFile excelFile = new ExcelFile();
string filename = DateTime.Now.ToString( " yyyyMMdd " );
int columns = dataGridView.Columns .Count;
int rows = dataGridView.Rows.Count;
int pages = rows / 60000 + 1 ;
ExcelWorksheet[] sheet = new ExcelWorksheet[pages];
int i = 1 ;
for ( int p = 0 ; p < pages; p ++ )
{
sheet[p] = excelFile.Worksheets.Add(filename + p.ToString());
int m = p * 60000 ;
for ( int j = 0 ; j < columns; j ++ )
{
sheet[p].Cells[ 0 , j].Value = dataGridView.Columns[j].HeaderText;
}
for (; i <= rows; i ++ )
{
if (i == 60000 * (p + 1 ))
{
i = i + 1 ;
break ;
}
for ( int j = 0 ; j < columns; j ++ )
{
sheet[p].Cells[i - m, j].Value = dataGridView.Rows[i].Cells[i - 1 ].ToString();
}
}
}
excelFile.SaveXls(filePath + filename + " .xls " );
return true ;
}
catch
{
return false ;
}
}
/// <summary>
/// 根据DataTable导出excel表
/// </summary>
/// <param name="dt"></param>
/// <param name="filePath"></param>
/// <returns></returns>
public bool SQLToExcelDataTable(DataTable dt, string filePath)
{
try
{
ExcelFile excelFile = new ExcelFile();
string filename = DateTime.Now.ToString( " yyyyMMdd " );
int colums = dt.Columns.Count;
int rows = dt.Rows.Count;
int pages = rows / 60000 + 1 ;
ExcelWorksheet[] sheet = new ExcelWorksheet[pages];
int i = 1 ;
for ( int p = 0 ; p < pages; p ++ )
{
sheet[p] = excelFile.Worksheets.Add(filename + p.ToString());
int m = p * 60000 ;
for ( int j = 0 ; j < colums; j ++ )
{
sheet[p].Cells[ 0 , j].Value = dt.Columns[j].ColumnName.ToString();
}
for (; i <= rows; i ++ )
{
if (i == 60000 * (p + 1 ))
{
i = i + 1 ;
break ;
}
for ( int j = 0 ; j < colums; j ++ )
{
sheet[p].Cells[i - m, j].Value = dt.Rows[j][i - 1 ].ToString();
}
}
}
excelFile.SaveXls(filePath + filename + " .xls " );
return true ;
}
catch
{
return false ;
}
}
下载地址:GemBox.ExcelLite.dll