前台循环DataTable
<table>
<tr>
<td></td>
<td>
姓名
</td>
<td>
性别
</td>
<td>
联系电话
</td>
<td>
地址
</td>
<td>
运单号
</td>
</tr>
<% if (dt != null && dt.Rows.Count>0)
{
foreach (DataRow dt_dr in dt.Rows)
{
%>
<tr>
<td>
<input class="checkbox" type="checkbox" name="choose" id="<%=dt_dr["id"]%> "/></td>
<td>
<%=dt_dr["name"]%>
</td>
<td>
<%=dt_dr["sex"]%>
</td>
<td>
<%=dt_dr["tel"]%>
</td>
<td>
<%=dt_dr["address"]%>
</td>
<td>
<%=dt_dr["yundanhao"]%>
</td>
</tr>
<%
}
}
else
{
%>
<tr>
<td colspan="5">
暂无信息
</td>
</tr>
<% }
%>
</table>
后台
using LiBangWuLiu.leiFile;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Windows.Forms;
namespace LiBangWuLiu
{
public partial class shouhuoren : System.Web.UI.Page
{
protected DataTable dt = new DataTable();//声明一个table表
protected void Page_Load(object sender, EventArgs e)
{
Panel1.Visible = false;
updateData();
}
protected void updateData()
{
string sql = "select top 10 * from T_shoufahuoren where type='收货人'";
DataSet ds = leiFile.SqlHelper.GetDataSet(sql);
if (ds.Tables.Count > 0)
{
dt = ds.Tables[0];
}
}
//增加人员
protected void btnAdd_Click(object sender, EventArgs e)
{
Panel1.Visible = true;
}
//查询人员
protected void search_Click(object sender, EventArgs e)
{
string gjz = Request.Form["nameortel"];//取出用户填入的搜索信息
if (gjz != "")
{
//string gjzstr = " select * from T_shoufahuoren where name='" + gjz.Trim() + "' or tel='"+gjz.Trim()+"' and type='发货人'";
string gjzstr = " select * from T_shoufahuoren where name='" + gjz.Trim() + "'or tel='" + gjz.Trim() + "' and type='收货人'";
DataSet ds = leiFile.SqlHelper.GetDataSet(gjzstr);
if (ds.Tables.Count > 0)
{
dt = ds.Tables[0];
}
else {
Response.Write("<script language=javascript>alert('无此人!')</script>");
}
}
else
{
Response.Write("<script language=javascript>alert('请输入姓名或者手机号!')</script>");
}
}
//保存增加的人到数据库
protected void btnsave_Click(object sender, EventArgs e)
{
string sqlAdd = "insert into T_shoufahuoren(name,sex,tel,type,address,yundanhao) values('" + txtname.Text + "','" + txtsex.Text + "','" + txttel.Text + " ','收货人','" + txtadd.Text + "','" + txtadd.Text + "')";
int result = leiFile.SqlHelper.GetExecuteNonQuery(sqlAdd);//调用类中的方法
if (result > 0)
{
Response.Write("<script language=javascript>alert('新增成功!')</script>");
updateData();
}
else
{
Response.Write("<script language=javascript>alert('新增失败!')</script>");
}
}
///导出到Excel
protected void Button1_Click(object sender, EventArgs e)
{
Microsoft.Office.Interop.Excel.Application appexcel = new Microsoft.Office.Interop.Excel.Application();
SaveFileDialog savefiledialog = new SaveFileDialog();
System.Reflection.Missing miss = System.Reflection.Missing.Value;
appexcel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook workbookdata;
Microsoft.Office.Interop.Excel.Worksheet worksheetdata;
Microsoft.Office.Interop.Excel.Range rangedata;
//设置对象不可见
appexcel.Visible = false;
System.Globalization.CultureInfo currentci = System.Threading.Thread.CurrentThread.CurrentCulture;
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-us");
workbookdata = appexcel.Workbooks.Add(miss);
worksheetdata = (Microsoft.Office.Interop.Excel.Worksheet)workbookdata.Worksheets.Add(miss, miss, miss, miss);
//给工作表赋名称
worksheetdata.Name = "saved";
string sql = "select * from T_shoufahuoren where type='收货人'";
DataSet ds = leiFile.SqlHelper.GetDataSet(sql);
DataTable tb = ds.Tables[0];
//DataChangeExcel.DataSetToExcel(tb, @"F:\output.xls");
if (tb.Rows.Count > 0)
{
for (int i = 0; i < dt.Columns.Count; i++)
{
worksheetdata.Cells[1, i + 1] = dt.Columns[i].ColumnName.ToString();
}
//因为第一行已经写了表头,所以所有数据都应该从a2开始
rangedata = worksheetdata.get_Range("a2", miss);
Microsoft.Office.Interop.Excel.Range xlrang = null;
//irowcount为实际行数,最大行
int irowcount = dt.Rows.Count;
int iparstedrow = 0, icurrsize = 0;
//ieachsize为每次写行的数值,可以自己设置
int ieachsize = 1000;
//icolumnaccount为实际列数,最大列数
int icolumnaccount = dt.Columns.Count;
//在内存中声明一个ieachsize×icolumnaccount的数组,ieachsize是每次最大存储的行数,icolumnaccount就是存储的实际列数
object[,] objval = new object[ieachsize, icolumnaccount];
icurrsize = ieachsize;
while (iparstedrow < irowcount)
{
if ((irowcount - iparstedrow) < ieachsize)
icurrsize = irowcount - iparstedrow;
//用for循环给数组赋值
for (int i = 0; i < icurrsize; i++)
{
for (int j = 0; j < icolumnaccount; j++)
objval[i, j] = dt.Rows[i + iparstedrow][j].ToString();
System.Windows.Forms.Application.DoEvents();
}
string X = "A" + ((int)(iparstedrow + 2)).ToString();
string col = "";
if (icolumnaccount <= 26)
{
col = ((char)('A' + icolumnaccount - 1)).ToString() + ((int)(iparstedrow + icurrsize + 1)).ToString();
}
else
{
col = ((char)('A' + (icolumnaccount / 26 - 1))).ToString() + ((char)('A' + (icolumnaccount % 26 - 1))).ToString() + ((int)(iparstedrow + icurrsize + 1)).ToString();
}
xlrang = worksheetdata.get_Range(X, col);
// 调用range的value2属性,把内存中的值赋给excel
xlrang.Value2 = objval;
iparstedrow = iparstedrow + icurrsize;
}
//保存工作表
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlrang);
xlrang = null;
//调用方法关闭excel进程
appexcel.Visible = true;
}
else
{
Response.Write("<script language=javascript>alert('没有数据!')</script>");
}
}
}
}