说真的解决这个问题当时我是查了许多资料：
分为两种方式 ：
第一种： 是导出excel只要一个指定存在的excel就可以了。可提供下载，最好不要删除。
第二种 ：是存在一个所谓的模板的excel，引用原有的标题头，算出你开始写入的行数，打开以后，写入数据，别存为第二个excel文件，下载完毕并删除第二个excel文件。模板不变。
这里的参数，d代表可选列的导出，saveGileName表示另存为的第二个文件名，sourfile表示第一个excel模板文件，excelname表示表格总标题，传进去的dt,就代表类别的表名
另外，注意，这个导出文件的话需要一个组件：excel.dll，这里暂时不能上传，到时自己在网上下载吧
public bool ExportToExcel(string d,string saveFileName,string sourfile,string excelname,System .Data .DataTable dt)
{
Excel.Application objExcelApp=new Excel.Application();
try
{
string ExeclFile="";
Object oMissing =System.Reflection.Missing.Value ;
Excel .Range range;
ExeclFile=sourfile;
Excel.Application excel =new Excel.ApplicationClass();
Excel.Workbook workbook =excel.Application.Workbooks.Open(sourfile,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing);
int er=0;
int sheetindex=0;
Excel.Worksheet worksheet=(Excel.Worksheet)workbook.Worksheets.get_Item(1);//取得sheet1
//第一个表
sheetindex=1;
if(sheetindex==1)
{
//写入数值
//这里的第一个sheet表要求,第一列，是列出所有的类型，并且能分辨类型的级别，第二列及以后则为根据第一列类型查出的相关记录
//这个表里面的dt代表一个类别表，而dtt3代表引用dt表数据的值，并根据这个值取得到的数据值
System .Data.DataTable dt1=new System .Data.DataTable();
for(int r=0;r<dt.Rows.Count;r++)//0.3.5为ID，类别，排序的名称
{
for(int i=0;i<22;i++)
{
//excel表的行数
if(i==0)
{
if(dt.Rows[r]["sequence"].ToString().Substring((dt.Rows[r]["sequence"].ToString()).Length -1,1)=="0")//一级菜单
{
if(dt.Rows[r]["name"].ToString()=="GSM网络建设"){dt.Rows[r]["name"]="一、GSM网络建设";}
if(dt.Rows[r]["name"].ToString()=="传输网"){dt.Rows[r]["name"]="三、传输网";}
if(dt.Rows[r]["name"].ToString()=="新技术新业务"){dt.Rows[r]["name"]="二、新技术新业务";}
if(dt.Rows[r]["name"].ToString()=="IT支撑系统"){dt.Rows[r]["name"]="四、IT支撑系统";}
if(dt.Rows[r]["name"].ToString()=="房屋建筑"){dt.Rows[r]["name"]="五、房屋建筑";}
if(dt.Rows[r]["name"].ToString()=="6其他"){dt.Rows[r]["name"]="六、其他";}
}
else
{
if(dt.Rows[r]["sequence"].ToString().Length==2)//二级菜单
{
string twonum=dt.Rows[r]["sequence"].ToString().Substring(1,1);
if(twonum=="1"){twonum="(一)";}
if(twonum=="2"){twonum="(二)";}
if(twonum=="3"){twonum="(三)";}
if(twonum=="4"){twonum="(四)";}
if(twonum=="5"){twonum="(五)";}
if(twonum=="6"){twonum="(六)";}
if(twonum=="7"){twonum="(七)";}
if(twonum=="8"){twonum="(八)";}
if(twonum=="9"){twonum="(九)";}
dt.Rows[r]["name"]=twonum+"."+dt.Rows[r]["name"].ToString();
}
else{
if(dt.Rows[r]["sequence"].ToString().Length==3)//三级菜单
{
string twonum=dt.Rows[r]["sequence"].ToString().Substring(2,1);
dt.Rows[r]["name"]=twonum+"."+dt.Rows[r]["name"].ToString();
}
else{
if(dt.Rows[r]["sequence"].ToString().Length==4)//四级菜单
{
string twonum=dt.Rows[r]["sequence"].ToString().Substring(3,1);
if(twonum=="1"){twonum="(1)";}
if(twonum=="2"){twonum="(2)";}
if(twonum=="3"){twonum="(3)";}
if(twonum=="4"){twonum="(4)";}
if(twonum=="5"){twonum="(5)";}
if(twonum=="6"){twonum="(6)";}
if(twonum=="7"){twonum="(7)";}
if(twonum=="8"){twonum="(8)";}
if(twonum=="9"){twonum="(9)";}
dt.Rows[r]["name"]=twonum+"."+dt.Rows[r]["name"].ToString();
}
}
}
}
worksheet.Cells[er+6,i+1]=dt.Rows[r]["name"].ToString();//这个是类别表，传入部门下达表同进度表之间联合查询的结果
if(d!="0")
{
dt1=DAO.sqlTable("select b.prj_sn,b.prj_name,b.scope,b.start_stop,b.content,b.total_plan,t=null,b.invest_plan,t=null,t=null,t=null,t=null,t=null,t=null,a.plan_explain,a.load_fulfill_invest,a.load_fulfill_plan,a.money_fulfill_invest,a.money_fulfill_plan,a.contract_fulfill_invest,a.contract_fulfill_plan,a.plan_explain,a.redressal_money,b.reason from tbzs_project_application as b left join tbzs_project_redressal as a on a.application_id=b.id where b.apply_class_id=2 and b.id in ("+d+") and b.prj_class_id in( select id from tbzs_project_class where p_id="+dt.Rows[r]["id"]+")");
}
else
{
dt1=DAO.sqlTable("select b.prj_sn,b.prj_name,b.scope,b.start_stop,b.content,b.total_plan,t=null,b.invest_plan,t=null,t=null,t=null,t=null,t=null,t=null,a.plan_explain,a.load_fulfill_invest,a.load_fulfill_plan,a.money_fulfill_invest,a.money_fulfill_plan,a.contract_fulfill_invest,a.contract_fulfill_plan,a.plan_explain,a.redressal_money,b.reason from tbzs_project_application as b left join tbzs_project_redressal as a on a.application_id=b.id where b.apply_class_id=2 and b.prj_class_id in( select id from tbzs_project_class where p_id="+dt.Rows[r]["id"]+")");
}
dt1=DAO.sqlTable("select b.prj_sn,b.prj_name,b.scope,b.start_stop,b.content,b.total_plan,t=null,b.invest_plan,t=null,t=null,t=null,t=null,t=null,t=null,a.plan_explain,a.load_fulfill_invest,a.load_fulfill_plan,a.money_fulfill_invest,a.money_fulfill_plan,a.contract_fulfill_invest,a.contract_fulfill_plan,a.plan_explain,a.redressal_money,b.reason from tbzs_project_application as b left join tbzs_project_redressal as a on a.application_id=b.id where b.apply_class_id=2 and b.id in ("+d+") and b.prj_class_id in( select id from tbzs_project_class where p_id="+dt.Rows[r]["id"]+")");
}
if(dt1!=null&& dt1.Rows.Count>0)
{
for(int cr=0;cr<dt1.Rows .Count ;cr++)//字段行
{
for(int cc=cr;cc<i;cc++)
{
worksheet.Cells[er+6,i+2]=(dt1.Rows[cr]);
}
}
}
}
//这里看有多少条这个项目的记录，留多少空格
if(dt1.Rows.Count==0){er=er+1;}
else{er=er+dt1.Rows.Count+1;}
}
range=worksheet.get_Range(worksheet.Cells[6,2],worksheet.Cells[dt.Rows.Count+6,22]);
range.BorderAround(Excel.XlLineStyle.xlContinuous,Excel.XlBorderWeight.xlThin,Excel.XlColorIndex.xlColorIndexAutomatic,null);
range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;
range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle =Excel.XlLineStyle.xlContinuous;
range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].Weight =Excel.XlBorderWeight.xlThin;
}
//第二个表
Excel.Worksheet worksheet2=(Excel.Worksheet)workbook.Worksheets.get_Item(2);
sheetindex=2;
if(sheetindex==2)
{
System .Data.DataTable dt2=new System .Data.DataTable();
int er2=0;
for(int r=0;r<dt.Rows.Count;r++)//0.3.5为ID，类别，排序的名称
{
for(int i=0;i<23;i++)
{
//excel表的行数
if(i==0)
{
if(dt.Rows[r]["sequence"].ToString().Length<5){
}else{}
worksheet2.Cells[er2+6,i+1]=dt.Rows[r]["name"].ToString();//这个是类别表，传入部门下达表同进度表之间联合查询的结果
if(d!="0")
{
dt2=DAO.sqlTable("select b.prj_sn,c.pass_file_number,b.prj_name,b.scope,b.start_stop,b.content,b.total_plan,t=null,b.invest_plan,t=null,t=null,t=null,t=null,t=null,t=null,a.plan_explain,a.load_fulfill_invest,a.load_fulfill_plan,a.money_fulfill_invest,a.money_fulfill_plan,a.contract_fulfill_invest,a.contract_fulfill_plan,a.plan_explain,a.redressal_money,b.reason from tbzs_project_application as b left join tbzs_project_redressal as a on a.application_id=b.id left join tbzs_project as c on b.prj_sn=c.prj_sn where b.apply_class_id=2 and b.id in ("+d+") and b.prj_class_id in ( select id from tbzs_project_class where p_id="+dt.Rows[r]["id"]+")");
}
else
{
dt2=DAO.sqlTable("select b.prj_sn,c.pass_file_number,b.prj_name,b.scope,b.start_stop,b.content,b.total_plan,t=null,b.invest_plan,t=null,t=null,t=null,t=null,t=null,t=null,a.plan_explain,a.load_fulfill_invest,a.load_fulfill_plan,a.money_fulfill_invest,a.money_fulfill_plan,a.contract_fulfill_invest,a.contract_fulfill_plan,a.plan_explain,a.redressal_money,b.reason from tbzs_project_application as b left join tbzs_project_redressal as a on a.application_id=b.id left join tbzs_project as c on b.prj_sn=c.prj_sn where b.apply_class_id=2 and b.prj_class_id in ( select id from tbzs_project_class where p_id="+dt.Rows[r]["id"]+")");
}
}
if(dt2!=null&& dt2.Rows.Count>0)
{
for(int cr=0;cr<dt2.Rows .Count ;cr++)//字段行
{
for(int cc=cr;cc<i;cc++)
{
if(i==8)//累计完成（万元）
{
string workall=DAO.sqlValue("select sum(cast(howwork as int)) from tbzs_visualize_doing where dept_prj_id in (select id from tbzs_dept_project where prj_id in(select id from tbzs_project where prj_sn='"+dt2.Rows[cr]["prj_sn"]+"'))");
worksheet2.Cells[er2+6,i+1]=workall;
}
else{worksheet2.Cells[er2+6,i+2]=(dt2.Rows[cr]); }
}
}
}
}
//这里看有多少条这个项目的记录，留多少空格
if(dt2.Rows.Count==0){er2=er2+1;}
else{er2=er2+dt2.Rows.Count+1;}
}
range=worksheet2.get_Range(worksheet2.Cells[6,2],worksheet2.Cells[dt.Rows.Count+6,23]);
range.BorderAround(Excel.XlLineStyle.xlContinuous,Excel.XlBorderWeight.xlThin,Excel.XlColorIndex.xlColorIndexAutomatic,null);
range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;
range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle =Excel.XlLineStyle.xlContinuous;
range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].Weight =Excel.XlBorderWeight.xlThin;
}
//第三个表
Excel.Worksheet worksheet3=(Excel.Worksheet)workbook.Worksheets.get_Item(3);
sheetindex=3;
if(sheetindex==3)
{
System .Data .DataTable dt3=new System .Data.DataTable();
int er3=0;
for(int r=0;r<dt.Rows.Count;r++)//0.3.5为ID，类别，排序的名称
{
for(int i=0;i<8;i++)
{
//excel表的行数
if(i==0)
{
worksheet3.Cells[er3+6,i+1]=dt.Rows[r]["name"].ToString();//这个是类别表，传入部门下达表同进度表之间联合查询的结果
dt3=DAO.sqlTable("select prj_sn,prj_name,scope,start_stop,content,total_plan,invest_plan,reason from tbzs_project_application where apply_class_id=1 and id in ("+d+") and prj_class_id in ( select id from tbzs_project_class where p_id="+dt.Rows[r]["id"]+")");
}
if(dt3!=null&& dt3.Rows.Count>0)
{
for(int cr=0;cr<dt3.Rows.Count ;cr++)//字段行
{
int tt=dt3.Columns .Count ;
for(int cc=cr;cc<i;cc++)
{
worksheet3.Cells[er3+6,i+3]=(dt3.Rows[cr]);
}
}
}
}
//这里看有多少条这个项目的记录，留多少空格
if(dt3.Rows.Count==0){er3=er3+1;}
else{er3=er3+dt3.Rows.Count+1;}
}
range=worksheet3.get_Range(worksheet3.Cells[6,3],worksheet3.Cells[dt.Rows.Count+6,9]);
range.BorderAround(Excel.XlLineStyle.xlContinuous,Excel.XlBorderWeight.xlThin,Excel.XlColorIndex.xlColorIndexAutomatic,null);
range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;
range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle =Excel.XlLineStyle.xlContinuous;
range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].Weight =Excel.XlBorderWeight.xlThin;
}
if(saveFileName!="")
{
workbook.Saved =true;
workbook.SaveCopyAs(saveFileName);
objExcelApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(objExcelApp);
}
else
{
}
}
catch(Exception yy){Response .Write(yy); return false;}
finally
{
if(objExcelApp != null)
{
objExcelApp = null;
GC.Collect();
}
}
return true;
}
