最近在做一个报表系统的时候,需要把DATASET中的数据导到EXCEL当中,于是在网上找了一遍,发现了好几种方法,本来以为应该差不多,但后来经过一一试用后,发现在性能上真的差别很大,现在就介绍一下,同时有不对的希望可以指正:
1. 原理:利用office组件把dataset中的数据填充到excel文件当中。
这里我不贴出全部代码了,只把关键部分贴出来:
Code ,,,,,,
2. 原理:利用office组件,同时把dataset的数据导到
Clipboard
中,然后通过粘贴到excel中。
Excel.XlSaveAsAccessMode savemode = new Excel.XlSaveAsAccessMode();
xlApp.Application.Workbooks.Add (true) ;
xlApp.DefaultFilePath = @"c:/";
xlApp.ActiveWorkbook.SaveAs("exportExcel.xls",xlApp.ActiveWorkbook.FileFormat,"","",xlApp.ActiveWorkbook.ReadOnlyRecommended,xlApp.ActiveWorkbook.CreateBackup,savemode,xlApp.ActiveWorkbook.ConflictResolution,false,"","","");
Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(filePath, oMissing, oMissing, oMissing, oMissing, oMissing,
oMissing, oMissing, oMissing, oMissing, oMissing, oMissing,
oMissing,oMissing,oMissing);
Excel.Worksheet xlWorksheet;
//
循环所有DataTable
for( int i=0; i<ds.Tables.Count; i++ )
{
xlWorksheet = (Excel.Worksheet)xlWorkbook.Worksheets.Add(oMissing,oMissing,1,oMissing);
//
以TableName作为新加的Sheet页名。
xlWorksheet.Name = ds.Tables[i].TableName;
//
取出这个DataTable中的所有值,暂存于stringBuffer中。
string stringBuffer = "";
//
向Excel中写入表格的表头
if(node != null)
{
XmlNode nodec=node.SelectSingleNode("./Method/ShowField");
int ii = 1;
foreach(XmlNode xnode in nodec.ChildNodes )
{
xlApp.Cells[1,ii] =xnode.Attributes["displayname"].Value;
ii++;
}
for( int j=0; j<ds.Tables[i].Rows.Count; j++ )
{
for( int k=0; k<ds.Tables[i].Columns.Count; k++ )
{
stringBuffer += ds.Tables[i].Rows[j][k].ToString();
if( k < ds.Tables[i].Columns.Count - 1 )
stringBuffer += "/t";
}
stringBuffer += "/n";
}
}
else
{
int ii = 1;
for(int c = 0;c<ds.Tables[i].Columns.Count; c++)
{
xlApp.Cells[1,ii] = ds.Tables[i].Columns[c].Caption;
ii++;
}
for( int j=0; j<ds.Tables[i].Rows.Count; j++ )
{
for( int k=0; k<ds.Tables[i].Columns.Count; k++ )
{
stringBuffer += ds.Tables[i].Rows[j][k].ToString();
if( k < ds.Tables[i].Columns.Count - 1 )
stringBuffer += "/t";
}
stringBuffer += "/n";
}
}
System.Windows.Forms.Clipboard.SetDataObject("");
//
将stringBuffer放入剪切板。
System.Windows.Forms.Clipboard.SetDataObject(stringBuffer);
//
选中这个sheet页中的第一个单元格
((Excel.Range)xlWorksheet.Cells[2,1]).Select();
//
粘贴!
xlWorksheet.Paste(oMissing,oMissing);
//
清空系统剪切板。
System.Windows.Forms.Clipboard.SetDataObject("");
}
//
保存并关闭这个工作簿。
xlApp.ActiveWorkbook.Close( Excel.XlSaveAction.xlSaveChanges, oMissing, oMissing );
// xlWorkbook.Close( Excel.XlSaveAction.xlSaveChanges, oMissing, oMissing );
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkbook);
xlWorkbook = null;
MessageBox.Show(@"Excel
文件:C:/exportExcel.xls 导出成功!");
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
//
释放...
xlApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
xlApp = null;
GC.Collect();
}
总结:这个方法比上面的方法性能好点,但还是很不好用,比原来的提高了2倍左右。
3. 原理:利用OLEDB,以excel为数据库,把dataset中的数据导入到excel文件中
public static void exportToExcelByDataset(string filePath, DataSet ds,XmlNode node)
{
string sqlstr;
{
string sqlstr;
if(fi.Exists)
{
fi.Delete();
// throw new Exception("文件删除失败");
// throw new Exception("文件删除失败");
}
else
{
fi.Create();
}
string sqlcon=@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended ProPerties=Excel 8.0;";
OleDbConnection olecon = new OleDbConnection(sqlcon);
OleDbCommand olecmd = new OleDbCommand();
olecmd.Connection = olecon;
olecmd.CommandType = CommandType.Text;
else
{
fi.Create();
}
string sqlcon=@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended ProPerties=Excel 8.0;";
OleDbConnection olecon = new OleDbConnection(sqlcon);
OleDbCommand olecmd = new OleDbCommand();
olecmd.Connection = olecon;
olecmd.CommandType = CommandType.Text;
try
{
olecon.Open();
XmlNode nodec=node.SelectSingleNode("./Method/ShowField");
int ii = 0;
sqlstr = "CREATE TABLE sheet1(";
foreach(XmlNode xnode in nodec.ChildNodes )
{
if(ii == nodec.ChildNodes.Count - 1)
{
if(xnode.Attributes["type"].Value.ToLower() == "int"||xnode.Attributes["type"].Value.ToLower() == "decimal")
{
sqlstr=sqlstr + xnode.Attributes["displayname"].Value + " number)";
}
else
{
sqlstr=sqlstr + xnode.Attributes["displayname"].Value + " text)";
}
// sqlstr=sqlstr + xnode.Attributes["displayname"].Value + " text)";
}
else
{
if(xnode.Attributes["type"].Value.ToLower() == "int"||xnode.Attributes["type"].Value.ToLower() == "decimal")
{
sqlstr=sqlstr + xnode.Attributes["displayname"].Value + " number,";
}
else
{
sqlstr=sqlstr + xnode.Attributes["displayname"].Value + " text,";
}
int ii = 0;
sqlstr = "CREATE TABLE sheet1(";
foreach(XmlNode xnode in nodec.ChildNodes )
{
if(ii == nodec.ChildNodes.Count - 1)
{
if(xnode.Attributes["type"].Value.ToLower() == "int"||xnode.Attributes["type"].Value.ToLower() == "decimal")
{
sqlstr=sqlstr + xnode.Attributes["displayname"].Value + " number)";
}
else
{
sqlstr=sqlstr + xnode.Attributes["displayname"].Value + " text)";
}
// sqlstr=sqlstr + xnode.Attributes["displayname"].Value + " text)";
}
else
{
if(xnode.Attributes["type"].Value.ToLower() == "int"||xnode.Attributes["type"].Value.ToLower() == "decimal")
{
sqlstr=sqlstr + xnode.Attributes["displayname"].Value + " number,";
}
else
{
sqlstr=sqlstr + xnode.Attributes["displayname"].Value + " text,";
}
}
// sqlstr =sqlstr + xnode.Attributes["displayname"].Value + " text";
ii++;
}
// sqlstr =sqlstr + xnode.Attributes["displayname"].Value + " text";
ii++;
}
olecmd.CommandText = sqlstr;
olecmd.ExecuteNonQuery();
olecmd.ExecuteNonQuery();
for(int i=0;i<ds.Tables[0].Rows.Count;i++)
{
sqlstr = "INSERT INTO sheet1 VALUES(";
int jj=0;
{
sqlstr = "INSERT INTO sheet1 VALUES(";
int jj=0;
foreach(XmlNode inode in nodec.ChildNodes )
{
if(jj == nodec.ChildNodes.Count-1)
{
if(inode.Attributes["type"].Value.ToLower() == "int"||inode.Attributes["type"].Value.ToLower() == "decimal")
{
sqlstr = sqlstr + isnull(ds.Tables[0].Rows[i].ItemArray[jj].ToString()) + ")" ;
}
else
{
sqlstr = sqlstr + "'" + isnull(ds.Tables[0].Rows[i].ItemArray[jj].ToString().Replace("'","''")) + "')" ;
}
{
if(jj == nodec.ChildNodes.Count-1)
{
if(inode.Attributes["type"].Value.ToLower() == "int"||inode.Attributes["type"].Value.ToLower() == "decimal")
{
sqlstr = sqlstr + isnull(ds.Tables[0].Rows[i].ItemArray[jj].ToString()) + ")" ;
}
else
{
sqlstr = sqlstr + "'" + isnull(ds.Tables[0].Rows[i].ItemArray[jj].ToString().Replace("'","''")) + "')" ;
}
}
else
{
if(inode.Attributes["type"].Value.ToLower() == "int"||inode.Attributes["type"].Value.ToLower() == "decimal")
{
sqlstr = sqlstr + isnull(ds.Tables[0].Rows[i].ItemArray[jj].ToString()) + "," ;
}
else
{
sqlstr = sqlstr + "'" + isnull(ds.Tables[0].Rows[i].ItemArray[jj].ToString().Replace("'","''")) + "'," ;
}
}
jj++;
else
{
if(inode.Attributes["type"].Value.ToLower() == "int"||inode.Attributes["type"].Value.ToLower() == "decimal")
{
sqlstr = sqlstr + isnull(ds.Tables[0].Rows[i].ItemArray[jj].ToString()) + "," ;
}
else
{
sqlstr = sqlstr + "'" + isnull(ds.Tables[0].Rows[i].ItemArray[jj].ToString().Replace("'","''")) + "'," ;
}
}
jj++;
}
olecmd.CommandText = sqlstr;
olecmd.ExecuteNonQuery();
olecmd.ExecuteNonQuery();
}
MessageBox.Show(@"Excel文件:" + filePath + " 导出成功!");
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
olecmd.Dispose();
olecon.Close();
olecon.Dispose();
}
MessageBox.Show(@"Excel文件:" + filePath + " 导出成功!");
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
olecmd.Dispose();
olecon.Close();
olecon.Dispose();
}
}
/// <summary>
/// change to string "null" if input is null
/// </summary>
/// <param name="obj"></param>
/// <returns></returns>
private static string isnull(string obj)
{
if(obj.Length >0)
{
return obj;
}
else
{
return "null";
}
/// <summary>
/// change to string "null" if input is null
/// </summary>
/// <param name="obj"></param>
/// <returns></returns>
private static string isnull(string obj)
{
if(obj.Length >0)
{
return obj;
}
else
{
return "null";
}
}
总 结:这个方法是最好的,速度飞快,比上面两种提高不止10倍,而且关键是不需要用到office组件,所以我正在用着这种方法,客户也满意。当然这个也有 它不好的地方,有时候会受到导入的数据不符的异常困扰,而且为了赶时间,代码写的不好,一句话,能用但要改进的地方很多:)