作者：乔山办公网日期：
返回目录：excel表格制作
<HTML>
<HEAD>
<title>WEB页面导出为EXCEL文档的方法
</title>
</HEAD>
<body>
<BR>
<table id = "PrintA" width="100%" border="1" cellspacing="0" cellpadding="0" bgcolor = "#61FF13">
<TR style="text-align : center;">
<TD>单元格A</TD>
<TD>单元格A</TD>
<TD>单元格A</TD>
<TD>单元格A</TD>
</TR>
<TR>
<TD colSpan=4 style="text-align : center;"><font color="BLUE" face="Verdana">单元格合并行A</FONT></TD>
</TR>
</TABLE>
<BR>
<table id = "PrintB" width="100%" border="1" cellspacing="0" cellpadding="0">
<TR style="text-align : center;">
<TD>单元格B</TD>
<TD>单元格B</TD>
<TD>单元格B</TD>
<TD>单元格B</TD>
</TR>
<TR>
<TD colSpan=4 style="text-align : center;">单元格合并行B</TD>
</TR>
</TABLE>
<input type="button" onclick="javascript:AllAreaWord();" value="导出页面指定区域e5a48de588b6e79fa5e98193366内容到Word">
<input type="button" onclick="javascript:AllAreaExcel();" value="导出页面指定区域内容到Excel">
<input type="button" onclick="javascript:CellAreaExcel();" value="导出表单单元格内容到Excel">
<SCRIPT LANGUAGE="javascript">
//指定页面区域内容导入Excel
function AllAreaExcel()
{
var oXL = new ActiveXObject("Excel.Application");
var oWB = oXL.Workbooks.Add();
var oSheet = oWB.ActiveSheet;
var sel=document.body.createTextRange();
sel.moveToElementText(PrintA);
sel.select();
sel.execCommand("Copy");
oSheet.Paste();
oXL.Visible = true;
}
//指定页面区域“单元格”内容导入Excel
function CellAreaExcel()
{
var oXL = new ActiveXObject("Excel.Application");
var oWB = oXL.Workbooks.Add();
var oSheet = oWB.ActiveSheet;
var Lenr = PrintA.rows.length;
for (i=0;i<Lenr;i++)
{
var Lenc = PrintA.rows(i).cells.length;
for (j=0;j<Lenc;j++)
{
oSheet.Cells(i+1,j+1).value = PrintA.rows(i).cells(j).innerText;
}
}
oXL.Visible = true;
}
//指定页面区域内容导入Word
function AllAreaWord()
{
var oWD = new ActiveXObject("Word.Application");
var oDC = oWD.Documents.Add("",0,1);
var oRange =oDC.Range(0,1);
var sel = document.body.createTextRange();
sel.moveToElementText(PrintA);
sel.select();
sel.execCommand("Copy");
oRange.Paste();
oWD.Application.Visible = true;
//window.close();
}
</SCRIPT>
</body></html>
读取EXCEL工作表的数据，生成HTML的<table>。如果要把excel工作簿发送到客户端实现预览，那么加载客户端的Active控件应该可以，但需要用户授权才能加载。
public static void ExportCostSummary()
{
Object Opt = System.Type.Missing;
Application m_xlApp = new Application();
Workbooks workbooks = m_xlApp.Workbooks;
Workbook workbook;
Worksheet wksheet;
workbook = m_xlApp.Workbooks.Open(System.Web.HttpContext.Current.Server.MapPath("模板excel") + ".xls", Opt, Opt, Opt, Opt, Opt, Opt, Opt, Opt, Opt, Opt, Opt, Opt, Opt, Opt); ;
m_xlApp.Visible = false; // Excel不显示
m_xlApp.DisplayAlerts = false; // 关闭提示，采用默认的方案执行（合并单元格的时候，如果两个单元格都有数据，会出现一个确认提示）
string sqltext = "select * from table ";
System.Data.DataTable dt = DBCallCommon.GetDTUsingSqlText(sqltext);
wksheet = (Worksheet)workbook.Sheets[1];//获取工作表
wksheet.Name = "导出excel-（导出日期" + DateTime.Now.ToShortDateString() + ")";//工作表名称
//设置表头居中
wksheet.get_Range(wksheet.Cells[1, 1], wksheet.Cells[1, dt.Rows.Count]).HorizontalAlignment = XlHAlign.xlHAlignCenter;
// 填充数据
object[,] dataArry1 = new object[dt.Rows.Count, 12];
for (int i = 0; i < dt.Rows.Count; i++)
{
dataArry1[i, 0] = dt.Rows[i]["Row_Num"].ToString();
dataArry1[i, 1] = dt.Rows[i]["PPL_SCZH"].ToString();
dataArry1[i, 2] = dt.Rows[i]["PPL_CPMC"].ToString();
dataArry1[i, 3] = dt.Rows[i]["PPL_WGSJ"].ToString();
dataArry1[i, 4] = dt.Rows[i]["PMS_HSJSSL"].ToString();
dataArry1[i, 5] = dt.Rows[i]["PMS_HSJS"].ToString();
dataArry1[i, 6] = dt.Rows[i]["PMS_BZJ"].ToString();
dataArry1[i, 7] = dt.Rows[i]["PMS_CNPJ"].ToString();
dataArry1[i, 8] = dt.Rows[i]["PMS_DL"].ToString();
dataArry1[i, 9] = dt.Rows[i]["PMS_GJFM"].ToString();
dataArry1[i, 10] = dt.Rows[i]["PMS_HCL"].ToString();
dataArry1[i, 11] = dt.Rows[i]["PMS_HGXJ"].ToString();
}
wksheet.get_Range("A5", wksheet.Cells[dt.Rows.Count + 4, 12]).Value2 = dataArry1;
//设置列宽
wksheet.Columns.EntireColumn.AutoFit();//列宽自适应
string filename = System.Web.HttpContext.Current.Server.MapPath(DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xls");
//下载到客户端
ExportExcel_Exit(filename, workbook, m_xlApp, wksheet);
}
private static void ExportExcel_Exit(string filename, Workbook workbook, Application m_xlApp, Worksheet wksheet)
{
try
{
System.IO.FileInfo path = new System.IO.FileInfo(filename);
workbook.SaveAs(filename, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
workbook.Close(Type.Missing, Type.Missing, Type.Missing);
m_xlApp.Workbooks.Close();
m_xlApp.Quit();
m_xlApp.Application.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(wksheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_xlApp);
#region kill excel process
System.Diagnostics.Process[] procs = System.Diagnostics.Process.GetProcessesByName("EXCEL");
foreach (System.Diagnostics.Process p in procs)
{
int baseAdd = p.MainModule.BaseAddress.ToInt32();
//oXL is Excel.ApplicationClass object
if (baseAdd == m_xlApp.Hinstance)
{
p.Kill();
break;
}
}
#endregion
wksheet = null;
workbook = null;
m_xlApp = null;
GC.Collect(); // 强制垃圾回收
System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + System.Web.HttpContext.Current.Server.UrlEncode(filename));
System.Web.HttpContext.Current.Response.ContentType = "application/ms-excel";// 指定返回的是一个不能被客户端读取的流，必须被下载
//System.Web.HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
System.Web.HttpContext.Current.Response.WriteFile(filename); // 把文件流发送到客户端
System.Web.HttpContext.Current.Response.Flush();
path.Delete();//删除7a64e4b893e5b19e363服务器文件
}
catch (Exception e)
{
throw e;
}
}
卓正PageOffice，非常专业