asp.net html 转execl,ASP.NET 动态输出EXCEL，并以网页形式打开

using System.Text;

using System.IO;

using System.Data.OracleClient;

using System.Reflection;

using Microsoft.Office.Interop.Excel;

GC.Collect();

Application excel;

_Workbook xBk;

_Worksheet xSt;

excel = new ApplicationClass();

xBk = excel.Workbooks.Add(true);

xSt = (_Worksheet)xBk.ActiveSheet;

int qs_row = 1;

int int_ls = 16;

int int_ls_b = 8;

xSt.get_Range(xSt.Cells[1, 1], xSt.Cells[1, 16]).NumberFormatLocal

= "@";

//设置列宽

xSt.get_Range(xSt.Cells[1, 1], xSt.Cells[1, 1]).ColumnWidth =

3.5;

xSt.get_Range(xSt.Cells[1, 2], xSt.Cells[1, 2]).ColumnWidth =

13;

xSt.get_Range(xSt.Cells[1, 3], xSt.Cells[1, 3]).ColumnWidth =

7;

xSt.get_Range(xSt.Cells[1, 4], xSt.Cells[1, 4]).ColumnWidth =

3;

xSt.get_Range(xSt.Cells[1, 5], xSt.Cells[1, 5]).ColumnWidth =

5;

xSt.get_Range(xSt.Cells[1, 6], xSt.Cells[1, 6]).ColumnWidth =

5;

xSt.get_Range(xSt.Cells[1, 7], xSt.Cells[1, 7]).ColumnWidth =

5;

xSt.get_Range(xSt.Cells[1, 8], xSt.Cells[1, 8]).ColumnWidth =

5;

xSt.get_Range(xSt.Cells[1, 9], xSt.Cells[1, 9]).ColumnWidth =

3.5;

xSt.get_Range(xSt.Cells[1, 10], xSt.Cells[1, 10]).ColumnWidth =

13;

xSt.get_Range(xSt.Cells[1, 11], xSt.Cells[1, 11]).ColumnWidth =

7;

xSt.get_Range(xSt.Cells[1, 12], xSt.Cells[1, 12]).ColumnWidth =

3;

xSt.get_Range(xSt.Cells[1, 13], xSt.Cells[1, 13]).ColumnWidth =

5;

xSt.get_Range(xSt.Cells[1, 14], xSt.Cells[1, 14]).ColumnWidth =

5;

xSt.get_Range(xSt.Cells[1, 15], xSt.Cells[1, 15]).ColumnWidth =

5;

xSt.get_Range(xSt.Cells[1, 16], xSt.Cells[1, 16]).ColumnWidth =

5;

//班级人数

int int_rs = Convert.ToInt32(my_pub.Getstr("select count(xh) rs

from xsjbxxb where

xzb='"+DDL_BJMC.SelectedItem.Text.ToString().Trim()+"' and

sfzx='是'","rs"));

int int_page =Convert.ToInt32( my_pub.Getstr("select

floor("+int_rs+"/60)+1 pg from dual","pg"));

int int_yhs = 40;//每页行数

string sql = "select rownum xuh,xh,xm,xb from (select xh,xm,xb from

xsjbxxb where

xzb='"+DDL_BJMC.SelectedItem.Text.ToString().Trim()+"' order by

xh)";

for (int i = 0; i < int_page; i++)

{

//合并单元格

xSt.get_Range(xSt.Cells[qs_row + i * int_yhs, 1], xSt.Cells[qs_row

+ i * int_yhs, int_ls]).Merge(xSt.get_Range(xSt.Cells[qs_row + i *

int_yhs, 1], xSt.Cells[qs_row + i * int_yhs,

int_ls]).MergeCells);

xSt.get_Range(xSt.Cells[qs_row + i * int_yhs, 1], xSt.Cells[qs_row

+ i * int_yhs, int_ls]).HorizontalAlignment =

XlHAlign.xlHAlignCenter;

xSt.get_Range(xSt.Cells[qs_row + i * int_yhs, 1], xSt.Cells[qs_row

+ i * int_yhs, int_ls]).VerticalAlignment =

XlVAlign.xlVAlignCenter;

xSt.get_Range(xSt.Cells[qs_row + i * int_yhs, 1], xSt.Cells[qs_row

+ i * int_yhs, int_ls]).RowHeight = 40;

xSt.get_Range(xSt.Cells[qs_row + i * int_yhs, 1], xSt.Cells[qs_row

+ i * int_yhs, int_ls]).Font.Name = "黑体";

xSt.get_Range(xSt.Cells[qs_row + i * int_yhs, 1], xSt.Cells[qs_row

+ i * int_yhs, int_ls]).Font.Size = 14;

xSt.Cells[qs_row + i * int_yhs, 1] = my_pub.Getstr("select xxmc

from xxmc", "xxmc") + "成绩登分表";

qs_row = qs_row + 1;

xSt.get_Range(xSt.Cells[qs_row + i * int_yhs, 1], xSt.Cells[qs_row

+ i * int_yhs, int_ls]).Merge(xSt.get_Range(xSt.Cells[qs_row + i *

int_yhs, 1], xSt.Cells[qs_row + i * int_yhs,

int_ls]).MergeCells);

xSt.get_Range(xSt.Cells[qs_row + i * int_yhs, 1], xSt.Cells[qs_row

+ i * int_yhs, int_ls]).HorizontalAlignment =

XlHAlign.xlHAlignCenter;

xSt.get_Range(xSt.Cells[qs_row + i * int_yhs, 1], xSt.Cells[qs_row

+ i * int_yhs, int_ls]).VerticalAlignment =

XlVAlign.xlVAlignCenter;

xSt.get_Range(xSt.Cells[qs_row + i * int_yhs, 1], xSt.Cells[qs_row

+ i * int_yhs, int_ls]).RowHeight = 30;

xSt.get_Range(xSt.Cells[qs_row + i * int_yhs, 1], xSt.Cells[qs_row

+ i * int_yhs, int_ls]).Font.Name = "宋体";

xSt.get_Range(xSt.Cells[qs_row + i * int_yhs, 1], xSt.Cells[qs_row

+ i * int_yhs, int_ls]).Font.Size = 12;

xSt.Cells[qs_row + i * int_yhs, 1] =

"( 学年 季)";

qs_row = qs_row + 1;

xSt.get_Range(xSt.Cells[qs_row + i * int_yhs, 1], xSt.Cells[qs_row

+ i * int_yhs, int_ls]).RowHeight = 30;

xSt.get_Range(xSt.Cells[qs_row + i * int_yhs, 1], xSt.Cells[qs_row

+ i * int_yhs, int_ls_b]).Merge(xSt.get_Range(xSt.Cells[qs_row + i

* int_yhs, 1], xSt.Cells[qs_row + i * int_yhs,

int_ls_b]).MergeCells);

xSt.get_Range(xSt.Cells[qs_row + i * int_yhs, 1], xSt.Cells[qs_row

+ i * int_yhs, int_ls_b]).HorizontalAlignment =

XlHAlign.xlHAlignLeft;

xSt.get_Range(xSt.Cells[qs_row + i * int_yhs, 1], xSt.Cells[qs_row

+ i * int_yhs, int_ls_b]).VerticalAlignment =

XlVAlign.xlVAlignCenter;

xSt.Cells[qs_row + i * int_yhs, 1] =

"班 级：" +

DDL_BJMC.SelectedItem.Text.ToString().Trim();

xSt.get_Range(xSt.Cells[qs_row + i * int_yhs, int_ls_b + 1],

xSt.Cells[qs_row + i * int_yhs,

int_ls]).Merge(xSt.get_Range(xSt.Cells[qs_row + i * int_yhs,

int_ls_b + 1], xSt.Cells[qs_row + i * int_yhs,

int_ls]).MergeCells);

xSt.get_Range(xSt.Cells[qs_row + i * int_yhs, int_ls_b + 1],

xSt.Cells[qs_row + i * int_yhs, int_ls]).HorizontalAlignment =

XlHAlign.xlHAlignLeft;

xSt.get_Range(xSt.Cells[qs_row + i * int_yhs, int_ls_b + 1],

xSt.Cells[qs_row + i * int_yhs, int_ls]).VerticalAlignment =

XlVAlign.xlVAlignCenter;

xSt.Cells[qs_row + i * int_yhs, int_ls_b + 1] = "教 学 站：" +

my_pub.Getstr("select xymc from xydmb where xydm

in (select ssxydm from zydmb where zydm=(select

sszydm from bjdmb where bjmc='" +

DDL_BJMC.SelectedItem.Text.ToString().Trim() + "')) ", "xymc");

qs_row = qs_row + 1;

xSt.get_Range(xSt.Cells[qs_row + i * int_yhs, 1], xSt.Cells[qs_row

+ i * int_yhs, int_ls]).RowHeight = 30;

xSt.get_Range(xSt.Cells[qs_row + i * int_yhs, 1], xSt.Cells[qs_row

+ i * int_yhs, int_ls_b]).Merge(xSt.get_Range(xSt.Cells[qs_row + i

* int_yhs, 1], xSt.Cells[qs_row + i * int_yhs,

int_ls_b]).MergeCells);

xSt.get_Range(xSt.Cells[qs_row + i * int_yhs, 1], xSt.Cells[qs_row

+ i * int_yhs, int_ls_b]).HorizontalAlignment =

XlHAlign.xlHAlignLeft;

xSt.get_Range(xSt.Cells[qs_row + i * int_yhs, 1], xSt.Cells[qs_row

+ i * int_yhs, int_ls_b]).VerticalAlignment =

XlVAlign.xlVAlignCenter;

xSt.Cells[qs_row + i * int_yhs, 1] = "课程名称：";

xSt.get_Range(xSt.Cells[qs_row + i * int_yhs, int_ls_b + 1],

xSt.Cells[qs_row + i * int_yhs,

int_ls]).Merge(xSt.get_Range(xSt.Cells[qs_row + i * int_yhs,

int_ls_b + 1], xSt.Cells[qs_row + i * int_yhs,

int_ls]).MergeCells);

xSt.get_Range(xSt.Cells[qs_row + i * int_yhs, int_ls_b + 1],

xSt.Cells[qs_row + i * int_yhs, int_ls]).HorizontalAlignment =

XlHAlign.xlHAlignLeft;

xSt.get_Range(xSt.Cells[qs_row + i * int_yhs, int_ls_b + 1],

xSt.Cells[qs_row + i * int_yhs, int_ls]).VerticalAlignment =

XlVAlign.xlVAlignCenter;

xSt.Cells[qs_row + i * int_yhs, int_ls_b + 1] =

"考(试/查)时间___________年______月_____日";

qs_row = qs_row + 1;

xSt.get_Range(xSt.Cells[qs_row + i * int_yhs, 1], xSt.Cells[qs_row

+ i * int_yhs, int_ls]).RowHeight = 30;

xSt.get_Range(xSt.Cells[qs_row + i * int_yhs, 1], xSt.Cells[qs_row

+ i * int_yhs, int_ls]).HorizontalAlignment =

XlHAlign.xlHAlignCenter;

xSt.get_Range(xSt.Cells[qs_row + i * int_yhs, 1], xSt.Cells[qs_row

+ i * int_yhs, int_ls]).VerticalAlignment =

XlVAlign.xlVAlignCenter;

xSt.Cells[qs_row + i * int_yhs, 1] = "序号";

xSt.Cells[qs_row + i * int_yhs, 2] = "学号";

xSt.Cells[qs_row + i * int_yhs, 3] = "姓名";

xSt.Cells[qs_row + i * int_yhs, 4] = "性别";

xSt.Cells[qs_row + i * int_yhs, 5] = "平时";

xSt.Cells[qs_row + i * int_yhs, 6] = "作业";

xSt.Cells[qs_row + i * int_yhs, 7] = "考试";

xSt.Cells[qs_row + i * int_yhs, 8] = "总评";

xSt.Cells[qs_row + i * int_yhs, 9] = "序号";

xSt.Cells[qs_row + i * int_yhs, 10] = "学号";

xSt.Cells[qs_row + i * int_yhs, 11] = "姓名";

xSt.Cells[qs_row + i * int_yhs, 12] = "性别";

xSt.Cells[qs_row + i * int_yhs, 13] = "平时";

xSt.Cells[qs_row + i * int_yhs, 14] = "作业";

xSt.Cells[qs_row + i * int_yhs, 15] = "考试";

xSt.Cells[qs_row + i * int_yhs, 16] = "总评";

qs_row = qs_row + 1;

for (int j= 1; j <= 30; j++)

{

xSt.Cells[qs_row + i * int_yhs, 1] = Convert.ToString(j + i *

60);

xSt.get_Range(xSt.Cells[qs_row + i * int_yhs, 2], xSt.Cells[qs_row

+ i * int_yhs, 2]).NumberFormatLocal = "@";

xSt.Cells[qs_row + i * int_yhs, 2] = my_pub.Getstr("select xh from

(" + sql + ") where xuh='" + Convert.ToString(j + i * 60) + "'",

"xh");

xSt.Cells[qs_row + i * int_yhs, 3] = my_pub.Getstr("select xm from

(" + sql + ") where xuh='" + Convert.ToString(j + i * 60) + "'",

"xm");

xSt.Cells[qs_row + i * int_yhs, 4] = my_pub.Getstr("select xb from

(" + sql + ") where xuh='" + Convert.ToString(j + i * 60) + "'",

"xb");

xSt.Cells[qs_row + i * int_yhs, 9] = Convert.ToString(j + i * 60 +

30);

xSt.get_Range(xSt.Cells[qs_row + i * int_yhs, 10], xSt.Cells[qs_row

+ i * int_yhs, 10]).NumberFormatLocal = "@";

xSt.Cells[qs_row + i * int_yhs, 10] = my_pub.Getstr("select xh from

(" + sql + ") where xuh='" + Convert.ToString(j + i * 60 + 30) +

"'", "xh");

xSt.Cells[qs_row + i * int_yhs, 11] = my_pub.Getstr("select xm from

(" + sql + ") where xuh='" + Convert.ToString(j + i * 60 + 30) +

"'", "xm");

xSt.Cells[qs_row + i * int_yhs, 12] = my_pub.Getstr("select xb from

(" + sql + ") where xuh='" + Convert.ToString(j + i * 60 + 30) +

"'", "xb");

qs_row = qs_row + 1;

}

xSt.get_Range(xSt.Cells[5 + i * int_yhs, 1], xSt.Cells[qs_row + i *

int_yhs - 1, int_ls]).Borders.LineStyle =

XlLineStyle.xlContinuous;

xSt.get_Range(xSt.Cells[6 + i * int_yhs, 1], xSt.Cells[qs_row + i *

int_yhs, int_ls]).HorizontalAlignment =

XlHAlign.xlHAlignCenter;

xSt.get_Range(xSt.Cells[6 + i * int_yhs, 1], xSt.Cells[qs_row + i *

int_yhs, int_ls]).VerticalAlignment =

XlVAlign.xlVAlignCenter;

xSt.get_Range(xSt.Cells[6 + i * int_yhs, 1], xSt.Cells[qs_row + i *

int_yhs, int_ls]).RowHeight = 23;

xSt.get_Range(xSt.Cells[3 + i * int_yhs, 1], xSt.Cells[qs_row + i *

int_yhs, int_ls]).Font.Name = "宋体";

xSt.get_Range(xSt.Cells[3 + i * int_yhs, 1], xSt.Cells[qs_row + i *

int_yhs, int_ls]).Font.Size = 10;

qs_row = qs_row + 1;

qs_row = qs_row + 1;

xSt.get_Range(xSt.Cells[qs_row + i * int_yhs, 1], xSt.Cells[qs_row

+ i * int_yhs, int_ls_b]).RowHeight = 20;

xSt.get_Range(xSt.Cells[qs_row + i * int_yhs, 1], xSt.Cells[qs_row

+ i * int_yhs, int_ls_b]).Merge(xSt.get_Range(xSt.Cells[qs_row + i

* int_yhs, 1], xSt.Cells[qs_row + i * int_yhs,

int_ls_b]).MergeCells);

xSt.get_Range(xSt.Cells[qs_row + i * int_yhs, 1], xSt.Cells[qs_row

+ i * int_yhs, int_ls_b]).HorizontalAlignment =

XlHAlign.xlHAlignLeft;

xSt.get_Range(xSt.Cells[qs_row + i * int_yhs, 1], xSt.Cells[qs_row

+ i * int_yhs, int_ls_b]).VerticalAlignment =

XlVAlign.xlVAlignCenter;

xSt.Cells[qs_row + i * int_yhs, 1] =

" 任课教师签字：___________________(签，章)";

xSt.get_Range(xSt.Cells[qs_row + i * int_yhs, int_ls_b + 1],

xSt.Cells[qs_row + i * int_yhs,

int_ls]).Merge(xSt.get_Range(xSt.Cells[qs_row + i * int_yhs,

int_ls_b + 1], xSt.Cells[qs_row + i * int_yhs,

int_ls]).MergeCells);

xSt.get_Range(xSt.Cells[qs_row + i * int_yhs, int_ls_b + 1],

xSt.Cells[qs_row + i * int_yhs, int_ls]).HorizontalAlignment =

XlHAlign.xlHAlignLeft;

xSt.get_Range(xSt.Cells[qs_row + i * int_yhs, int_ls_b + 1],

xSt.Cells[qs_row + i * int_yhs, int_ls]).VerticalAlignment =

XlVAlign.xlVAlignCenter;

xSt.Cells[qs_row + i * int_yhs, int_ls_b + 1] =

" 平时：_____%，作业：____%，考试_____%";

qs_row = 1;

}

excel.Visible = true;

//string str_filename = Server.MapPath("excel/xsmd/") +

"testsss.xls";

//xBk.SaveCopyAs(str_filename);

string filename3 =

"excel/xsmd/"+DDL_BJMC.SelectedItem.Value.ToString().Trim()+".html";

if (File.Exists(Server.MapPath(filename3)))

{

File.Delete(Server.MapPath(filename3));

}

object missing = Type.Missing;

object saveFormat =

Microsoft.Office.Interop.Excel.XlFileFormat.xlHtml;

xBk.SaveAs(Server.MapPath(filename3), saveFormat, missing, missing,

false, false, XlSaveAsAccessMode.xlNoChange, missing, missing,

missing, missing, missing);

xBk.Close(false, null, null);

excel.Quit();

System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk);

System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);

System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt);

xBk = null;

excel = null;

xSt = null;

GC.Collect();

string strUrl = "./show_excel.aspx?filename=" + filename3;

Page.ClientScript.RegisterStartupScript(Page.GetType(),

"OpenNewWindow", string.Format(@"

//show_execl页面

Response.Redirect("~/" + filename3,true);