说真的解决这个问题当时我是查了许多资料:
分为两种方式 :
第一种: 是导出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;
} |