这几天完成老板的项目,做一个叫中拼网的招聘网站。涉及到了DataSet和DataTable转换为excel文件的需求。参考了csdn上大多数人的方法,自己写了一个类。可以关闭用过的excel进程。可是需要你先自己先得到由com转换来的excel.dll NET 组件。
这个代码也是参考csdn上的帖子写出来的。所以发出来给大家参考。就是进行最简单的转换。没有加上任何复杂设置。其实是我门的项目没有这个需要。保留了很大的扩展性。
希望对大家有启发。
//-----------------------------------------------------------------------------------------------------------------------------------
using Excel;
using System.Reflection;
using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
namespace ParentClassNameSpace
{
/// <summary>
/// 生成Excel类说明
/// 需要Excel.dll,Office.dll
/// VBIDE.dll,Word.dll
/// 四个NET组件
/// </summary>
public class MyExcel
{
public MyExcel()
{
}
/// <summary>
/// 传入DataSet,路径。写excel文件函数
/// </summary>
/// <param name="ds">DataSet对象</param>
/// <param name="strurl">文件要保留路径</param>
/// <returns>文件所在位置的字符串</returns>
public string DataSetToExcelXls(DataSet ds,string strurl)
{
try
{
if(ds.Tables.Count<1)
return "DataSet对象错误";
if(ds.Tables.Count==1)
{
return DataTableToExcelXls(ds.Tables[0],strurl);
}
else
{
Random rd=new Random(int.Parse(DateTime.Now.ToString("MMddhhmmss")));
string strFileName =DateTime.Now.ToString("yyyyMMdd")+DateTime.Now.Hour+DateTime.Now.Minute+DateTime.Now.Second+rd.Next(999999).ToString()+ ".XLS";
object my_object=Missing.Value;
Excel.Application excel= new Excel.ApplicationClass();
Excel.WorkbookClass oWB;
Excel.Worksheet oSheet;
oWB = (Excel.WorkbookClass)(excel.Workbooks.Add(true));
oWB.Sheets.Add(my_object,my_object,ds.Tables.Count==1?1:ds.Tables.Count-1,my_object);
int dstabcount=ds.Tables.Count;
for(int dst=0;dst<dstabcount;dst++)
{
oSheet=(Excel.Worksheet)oWB.Sheets.get_Item(dst+1);
for (int i = 0; i < ds.Tables[dst].Columns.Count; i++)
{
oSheet.Cells[1,i+1] =ds.Tables[dst].Columns[i].ToString();
}
DataRow dr;
int count=ds.Tables[dst].Rows.Count+1;
int I,J;
for(I = 2;I< count;I++)
{
dr=ds.Tables[dst].Rows[I-2];
for(J = 1;J<(ds.Tables[dst].Columns.Count +1);J++)
{
oSheet.Cells[I,J] = dr[J-1].ToString().Trim() ;
}
}
oSheet.Name=ds.Tables[dst].TableName.ToString();
}
string path=HttpContext.Current.Server.MapPath(strurl) + "//" + strFileName;
oWB.SaveAs(path,my_object,"","",true,false,Excel.XlSaveAsAccessMode.xlShared,1,false,my_object,my_object);
oWB.Close(false,null,null);
excel.Workbooks.Close();
excel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject (excel);
System.Runtime.InteropServices.Marshal.ReleaseComObject (oWB);
oSheet = null;
oWB= null;
excel = null;
GC.Collect();
GC.WaitForPendingFinalizers();
return path;
}
}
catch
{
return null;
}
}
/// <summary>
/// 传入DataTable,路径。写excel文件函数
/// </summary>
/// <param name="ds">DataTable对象</param>
/// <param name="strurl">文件要保留路径</param>
/// <returns>文件所在位置的字符串</returns>
public string DataTableToExcelXls(System.Data.DataTable ds,string strurl)
{
try
{
Random rd=new Random(int.Parse(DateTime.Now.ToString("MMddhhmmss")));
string strFileName =DateTime.Now.ToString("yyyyMMdd")+DateTime.Now.Hour+DateTime.Now.Minute+DateTime.Now.Second+rd.Next(999999).ToString()+ ".XLS";
Excel.Application excel = new Excel.ApplicationClass();
Excel.WorkbookClass oWB;
Excel.Worksheet oSheet;
oWB = (Excel.WorkbookClass)(excel.Workbooks.Add(true));
oSheet = (Excel.Worksheet)oWB.ActiveSheet;
object my_object=Missing.Value;
for (int i = 0; i < ds.Columns.Count; i++)
{
oSheet.Cells[1,i+1] =ds.Columns[i].ToString() ;
}
DataRow dr;
int count=ds.Rows.Count+2;
int I,J;
for(I = 2;I< count;I++)
{
dr=ds.Rows[I-2];
for(J = 1;J<(ds.Columns.Count +1) ;J++)
{
oSheet.Cells[I,J] = dr[J-1].ToString().Trim() ;
}
}
oSheet.Name=ds.TableName;
string path=HttpContext.Current.Server.MapPath(strurl) + "//" + strFileName;
oWB.SaveAs(path,my_object,"","",true,false,Excel.XlSaveAsAccessMode.xlNoChange,1,false,my_object,my_object);
oWB.Close(false,null,null);
excel.Workbooks.Close();
excel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject (excel);
System.Runtime.InteropServices.Marshal.ReleaseComObject (oWB);
System.Runtime.InteropServices.Marshal.ReleaseComObject (oSheet);
oSheet = null;
oWB= null;
excel = null;
GC.Collect();
GC.WaitForPendingFinalizers();
return path;
}
catch
{
return null;
}
}
}
}