经过在网上搜索,我们可以看到.net方法有很多缺陷,不是数据库就是什么属性修改,很麻烦。以下就是我个人写的代码,关于.net方法导出excel,不过这个方法要引用Excel类,可以在外部或是.net自身所带的,一般情况下,最好用2000以上的机子!因为我用了2000的机子在公司试过,经常的错误是太多了,最后的错误还是无法解决,都是硬件的限制。不过.net方法不可以,还有脚本语言的方法,这个方法不需要任何的设置,只要你机子上装有excel,我们也可以检测的到!希望我的能给自己也给大家一些灵感或是想法,可以想到更好的方法。大家互相学习!
.net方法:
首先导入:
using WavenetWebFrameWork.FrameWorkBase;
using Excel;
using System.Reflection;
代码如下:
--------------------------------------------Mondify By LiFuyun--------------------------------------------------------
/// <summary>
/// 导出Excel
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void lkbexcel_Click(object sender, EventArgs e)
{
if (TargetFile.Items.Count <= 0)
{
Response.Write("<script>alert('对不起,您必须选择属性字段后才能导出到Excel!');location='javascript:history.go(-1)';</script>");
}
else
{
gettable();
}
}
//-------------------------------------数据表写入-----
/// <summary>
/// 拿数据表Table
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void gettable()
{
strshuizha = "select ";
for (int i = 0; i < TargetFile.Items.Count;i++)
{
if (i == TargetFile.Items.Count - 1)
{
strshuizha += TargetFile.Items[i].Value;
}
else
{
strshuizha += TargetFile.Items[i].Value + ",";
}
}
strshuizha += " from dataset where TIME_STAMP >=" + Convert.ToDateTime(tbxprevious.Text) + " and TIME_STAMP <=" + Convert.ToDateTime(tbxnext.Text) + " order by TIME_STAMP desc";
myDataTable = (new LOGIC.DataAccessOra(constr_xxzx)).RunOracleDT(strshuizha);
Excel.Application excelKccx = new Excel.Application();//创建excel对象
//Excel.Range range;
//Excel.Sheets sheets;
//Excel.Workbook workbook;
//Excel._Workbook _workbook;
//Excel._Worksheet _worksheet;
if (excelKccx != null)
{
excelKccx.Workbooks.Add(true);//创建excel工作薄
excelKccx.Caption = tbxtitle.Text;//标题
int row = 4;
//合并单元格
excelKccx.get_Range(excelKccx.Cells[1, 1], excelKccx.Cells[1, 15]).MergeCells = true;
excelKccx.get_Range(excelKccx.Cells[2, 1], excelKccx.Cells[2, 15]).MergeCells = true;
excelKccx.get_Range("A1:A2", Type.Missing).RowHeight = 80;//1-2行行高设置
FormatCells(excelKccx, "A1:A2", Excel.Constants.xlAutomatic, Excel.Constants.xlCenter, "宋体", true, 12, 30, true, true);
excelKccx.Cells[1, 1] = tbxtitle.Text;
excelKccx.Cells[2, 1] = tbxbz.Text;
//把数据表的各个信息输入到excel表中
for (int i = 0; i < myDataTable.Columns.Count; i++)//取字段名
{
excelKccx.Cells[3, i + 1] = TargetFile.Items[i].ToString();
}
for (int i = 0; i < myDataTable.Rows.Count; i++)//取记录值
{
for (int j = 0; j < myDataTable.Columns.Count; j++)
{
excelKccx.Cells[row, j + 1] = myDataTable.Rows[i][j].ToString();
}
row++;
}
excelKccx.Visible = true;//使excel可见*/
}
else
{
Response.Write("<script>alert('对不起,您的电脑没有安装Excel!');location='javascript:history.go(-1)';</script>");
}
}
/// <summary>格式化单元格</summary>
/// <param name="Ex">Excel.Appliaction实例</param>
/// <param name="szRang">格式范围</param>
/// <param name="HAlign">水平对齐方式</param>
/// <param name="VAlign">垂直对齐方式</param>
/// <param name="szFong">字体</param>
/// <param name="FondBold">字体类型,缺省为false,不加粗</param>
/// <param name="nFontSize">字体大小</param>
/// <param name="nFontColor">字体颜色</param>
/// <param name="bWarp">单元格自动换行,缺省为false</param>
/// <param name="bMerge">单元格合并,缺省为false</param>
public static void FormatCells(Excel.Application Ex, String szRang, Excel.Constants HAlign, Excel.Constants VAlign, String szFong,bool FondBold, int nFontSize, int nFontColor, bool bWarp, bool bMerge)
{
//Range对象包含于Worksheet对象,表示Excel工作表中的一个或多个单元格。
Excel.Range rng = Ex.get_Range(szRang, Type.Missing);
rng.Select();
if (HAlign != 0) rng.HorizontalAlignment = HAlign;
if (VAlign != 0) rng.VerticalAlignment = VAlign;
rng.Font.Name = szFong;
rng.Font.Bold = FondBold;
if (nFontSize > 0) rng.Font.Size = nFontSize;
if (nFontColor > 0) rng.Font.ColorIndex = nFontColor;
rng.WrapText = bWarp;
if (bMerge == true) rng.Merge(Type.Missing);
}
protected void ddlzz_SelectedIndexChanged(object sender, EventArgs e)
{
tbxtitle.Text = ddlzz.SelectedItem.Text + "时间段详细表";
}
protected void tbxprevious_TextChanged(object sender, EventArgs e)
{
tbxbz.Text = "时间是从" + tbxprevious.Text + "至" + tbxnext.Text + "闸站详细数据情况表";
}
protected void tbxnext_TextChanged(object sender, EventArgs e)
{
tbxbz.Text = "时间是从" + tbxprevious.Text + "至" + tbxnext.Text + "闸站详细数据情况表";
}
protected void img_execl_Click(object sender, ImageClickEventArgs e)
{
if (TargetFile.Items.Count <= 0)
{
Response.Write("<script>alert('对不起,您必须选择属性字段后才能导出到Excel!');location='javascript:history.go(-1)';</script>");
}
else
{
strzhazhan = "";
strzzname = "";
strdata = ddlzz.SelectedValue + "|" + ddlzz.SelectedItem.Text + "|" + tbxprevious.Text + "|" + tbxnext.Text + "|" + tbxtitle.Text + "|" + tbxbz.Text;
sum = TargetFile.Items.Count;
for (int i = 0; i < TargetFile.Items.Count; i++)
{
if (i == TargetFile.Items.Count - 1)
{
strzhazhan += TargetFile.Items[i].Value;
strzzname += TargetFile.Items[i].Text;
}
else
{
strzhazhan += TargetFile.Items[i].Value + "|";
strzzname += TargetFile.Items[i].Text + "|";
}
}
url = "producexcel.aspx?strdata=" + strdata + "&strzhazhan=" + strzhazhan + "&strzzname=" + strzzname + "&sum=" + sum;
Response.Write("<script language='javascript'>window.open('" + url + " ','line','height=120,width=500,top=50,left=50,toolbar=no,menubar=no,scrollbars=no, resizable=no,location=no, status=no')</script>");
}
}
--------------------------------------------------------------------END------------------------------------------------------------------------
Javascript方法:
只需要在客户端写代码:
<%
///这里可以自己设计一个正在导出的界面,因为我本人处理的数据很大,所以有的时候出来比较慢,可以用等待界面设置
//--------------------------------------------------------------------Mondify By LiFuyun----------------------------------------------------
Response.Write("<head id='Head1' runat='server'><title>请稍等!Excel导出中......</title></head><body style='background:black'><form id='form1' runat='server'><asp:ImageButton ID='ImageButton1' runat='server' ImageUrl='~/Image/loading_16x16.gif'/>");
Response.Write("<table><tr align='left' style='color:White;' width='200px'>Educe Excel Loading.............</tr></table></form></body>");
string strdata, strzhazhan, strzzname, id, name, title, bz, ptime, ntime;
string strshuizha = "select ";
string constr_xxzx = System.Configuration.ConfigurationSettings.AppSettings.Get("sz_xxzx");
int sum;
strdata = Request["strdata"];
strzhazhan = Request["strzhazhan"];
strzzname = Request["strzzname"];
sum = Convert.ToInt32(Request["sum"]);
string[] shezhi = strdata.Split(new char[1] { '|' });
string[] strid = strzhazhan.Split(new char[1] { '|'});
string[] strname = strzzname.Split(new char[1] { '|' });
id = shezhi[0].ToString();
name = shezhi[1].ToString();
ptime=shezhi[2].ToString();
ntime=shezhi[3].ToString();
title = shezhi[4].ToString();
bz = shezhi[5].ToString();
for (int i = 0; i < sum; i++)
{
if (i == sum - 1)
{
strshuizha += strid[i];
}
else
{
strshuizha += strid[i] + ",";
}
}
strshuizha += " from dataset where TIME_STAMP >=to_date('" + ptime + "','yyyy-MM-dd hh24-mi-ss') and TIME_STAMP <= to_date('" + ntime + "','yyyy-MM-dd hh24-mi-ss') and STATION_ID =" + id + " order by TIME_STAMP desc";
System.Data.DataTable dtTemp = (new LOGIC.DataAccessOra(constr_xxzx)).RunOracleDT(strshuizha);
%>
//-------------------------------------------写入到EXCEL当中----------------------By LiFuyun-----
<script language="javascript" type="text/javascript">
// <!CDATA[
var CharArray=new Array("A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z");
var Merge="<%=sum%>";
//alert(Merge);
var xls = new ActiveXObject ( "Excel.Application" );
xls.visible = false;
var newBook = xls.Workbooks.Add;
newBook.Worksheets.Add;
newBook.Worksheets(1).Activate;
xls.ActiveWorkBook.ActiveSheet.PageSetup.Orientation = 2;
xls.ActiveWorkBook.ActiveSheet.PageSetup.PaperSize = 5;
//首行Title
newBook.Worksheets(1).Range("A1:" + CharArray[Merge-1] + "1").merge(); // 合并单元格区域 A1:D1
newBook.Worksheets(1).Range("A1:" + CharArray[Merge-1] + "1").HorizontalAlignment=3; //居中对齐
newBook.Worksheets(1).Cells(1,1).value=GetTitle();
newBook.Worksheets(1).Cells(1,1).Font.Bold = true; // 粗体
newBook.Worksheets(1).Range("A2:" + CharArray[Merge-1] + "2").merge(); // 合并单元格区域 A1:D1
newBook.Worksheets(1).Range("A2:" + CharArray[Merge-1] + "2").HorizontalAlignment=3; //居中对齐
newBook.Worksheets(1).Cells(2,1).value="<%=bz%>";
newBook.Worksheets(1).Cells(2,1).Font.Bold = true; // 粗体
newBook.Worksheets(1).Columns("A").columnwidth=27; // 设置 A 列宽度为40px
<%
for (int i = 0; i < sum; i++)
{
%>
newBook.Worksheets(1).Cells(3,(<%=i%>+1)).value="<%=strname[i]%>";
newBook.Worksheets(1).Cells(3,(<%=i%>+1)).HorizontalAlignment=1; //居中对齐
newBook.Worksheets(1).Columns(CharArray[<%=i%>+1]).columnwidth=20; // 设置 A 列宽度为40px
newBook.Worksheets(1).Cells(3,(<%=i%>+1)).Font.Bold = true; // 粗体
<%
}
for (int j = 0; j < dtTemp.Rows.Count; j++)
{
for (int i = 0; i < sum; i++)
{
%>
newBook.Worksheets(1).Cells((<%=j%>+4),(<%=i%>+1)).value="<%=dtTemp.Rows[j][i].ToString()%>";
newBook.Worksheets(1).Cells((<%=j%>+4),(<%=i%>+1)).HorizontalAlignment=1; //居中对齐
newBook.Worksheets(1).Columns(CharArray[<%=i%>+1]).columnwidth=20; // 设置 A 列宽度为40px
<%
}
}
%>
newBook.Worksheets(1).Cells(3,1).HorizontalAlignment=3; //居中对齐
newBook.Worksheets(1).Cells(3,2).HorizontalAlignment=3; //居中对齐
function GetTitle()
{
var Station
Station = "<%=title%>";
return Station;
}
window.close();
xls.visible = true;
// ]]>
</script>
//------------------------------------------------End By LiFuyun----------------------------------------------------------------------------
有任何疑问请在我的QQ空间里留言,请您点击下面进入: