Asp.NetエクスポートExcel
11145 ワード
フロントエンドコード:SqlToExcel.aspx
SqlToExcel.aspx.csコード
dal層法
<div>
<asp:GridView ID="GridView1" runat="server" AllowPaging="True"
OnPageIndexChanging="GridView1_PageIndexChanging" AutoGenerateColumns="False"
EnableModelValidation="True">
<Columns>
<asp:BoundField DataField="id" HeaderText=" " />
<asp:BoundField DataField="title" HeaderText=" " />
<asp:BoundField DataField="BarCode" HeaderText=" " />
<asp:BoundField DataField="code" HeaderText=" " />
<asp:BoundField DataField="MarketPrice" HeaderText=" " />
<asp:BoundField DataField="PerfectPrice" HeaderText=" " />
<asp:BoundField DataField="SavePrice" HeaderText=" " />
<asp:BoundField DataField="WebconfigPic" HeaderText=" " />
<asp:BoundField DataField="CompanyID" HeaderText=" ID" />
<asp:BoundField DataField="CompanyName" HeaderText=" " />
</Columns>
</asp:GridView>
<asp:RadioButtonList ID="RadioButtonList1" runat="server" RepeatDirection="Horizontal"
Width="185px">
<asp:ListItem Selected="True"> </asp:ListItem>
<asp:ListItem> </asp:ListItem>
</asp:RadioButtonList>
<br />
<asp:Button ID="Button1" runat="server" Text=" Excel" OnClick="Button1_Click" />
</div>SqlToExcel.aspx.csコード
public partial class SqlToExcel : System.Web.UI.Page
{
private System.Data.DataTable dbt = new System.Data.DataTable("tableInfo");
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
DisplayList();
}
}
// Excel
protected void Button1_Click(object sender, EventArgs e)
{
if (this.GridView1.Rows.Count > 0)
{
creatTable();
DataTabletoExcel(dbt);
}
else
{
ClientScript.RegisterStartupScript(this.GetType(), "", "<script>alert(' ， ……');</script>");
}
}
public void DisplayList()
{
BLLProductNew bll = new BLLProductNew();
GridView1.DataSource = bll.GetListToExcel();
GridView1.DataBind();
}
//
protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
GridView1.PageIndex = e.NewPageIndex;
DisplayList();
}
private void creatTable()
{
DataColumn dbcol = new DataColumn();
dbcol.ColumnName = " ";
dbcol.DataType = Type.GetType("System.String");
dbt.Columns.Add(dbcol);
DataColumn dbper = new DataColumn();
dbper.ColumnName = " ";
dbper.DataType = Type.GetType("System.String");
dbt.Columns.Add(dbper);
DataColumn dbBarCode = new DataColumn();
dbBarCode.ColumnName = " ";
dbBarCode.DataType = Type.GetType("System.String");
dbt.Columns.Add(dbBarCode);
DataColumn dbpwd = new DataColumn();
dbpwd.ColumnName = " ";
dbpwd.DataType = Type.GetType("System.String");
dbt.Columns.Add(dbpwd);
DataColumn dbMarketPrice = new DataColumn();
dbMarketPrice.ColumnName = " ";
dbMarketPrice.DataType = Type.GetType("System.String");
dbt.Columns.Add(dbMarketPrice);
DataColumn dbpPrice = new DataColumn();
dbpPrice.ColumnName = " ";
dbpPrice.DataType = Type.GetType("System.String");
dbt.Columns.Add(dbpPrice);
DataColumn dbpC = new DataColumn();
dbpC.ColumnName = " ";
dbpC.DataType = Type.GetType("System.String");
dbt.Columns.Add(dbpC);
DataColumn dbBrand = new DataColumn();
dbBrand.ColumnName = " ";
dbBrand.DataType = Type.GetType("System.String");
dbt.Columns.Add(dbBrand);
DataColumn dbpCompanyID = new DataColumn();
dbpCompanyID.ColumnName = " ID";
dbpCompanyID.DataType = Type.GetType("System.String");
dbt.Columns.Add(dbpCompanyID);
DataColumn dbpCompanyName = new DataColumn();
dbpCompanyName.ColumnName = " ";
dbpCompanyName.DataType = Type.GetType("System.String");
dbt.Columns.Add(dbpCompanyName);
if (RadioButtonList1.Text == " ")
{
// “ ”
this.GridView1.AllowPaging = true;
}
if (RadioButtonList1.Text == " ")
{
// “ ”
this.GridView1.AllowPaging = false;
}
DisplayList();
foreach (GridViewRow grv in GridView1.Rows)
{
DataRow dr = dbt.NewRow();
dr[" "] = grv.Cells[0].Text;
dr[" "] = grv.Cells[1].Text;
dr[" "] = grv.Cells[2].Text;
dr[" "] = grv.Cells[3].Text;
dr[" "] = grv.Cells[4].Text;
dr[" "] = grv.Cells[5].Text;
dr[" "] = grv.Cells[6].Text;
dr[" "] = grv.Cells[7].Text;
dr[" ID"] = grv.Cells[8].Text;
dr[" "] = grv.Cells[9].Text;
dbt.Rows.Add(dr);
}
this.GridView1.AllowPaging = true;
DisplayList();
}
public void DataTabletoExcel(System.Data.DataTable db)
{
string hour = DateTime.Now.Hour.ToString();
string min = DateTime.Now.Minute.ToString();
string sec = DateTime.Now.Second.ToString();
string path = DateTime.Now.ToString("yyyyMMddhhmmss");
//path += "aaaa";
//string Info = "C:\\" + path + " .xls";
string Info = HttpContext.Current.Server.MapPath("") + "/CreateExcel/" + path + " .xls";
Microsoft.Office.Interop.Excel.Application ex = new Microsoft.Office.Interop.Excel.Application();
ex.Visible = false;
object ms = Type.Missing;
Microsoft.Office.Interop.Excel.Workbook wk = ex.Workbooks.Add(ms);
Microsoft.Office.Interop.Excel.Worksheet ws = wk.Worksheets[1] as Microsoft.Office.Interop.Excel.Worksheet;
//
for (int i = 0; i < db.Columns.Count; i++)
{
ws.Cells[1, i + 1] = db.Columns[i].ColumnName;
}
//
for (int i = 0; i < db.Rows.Count; i++)
{
for (int j = 0; j < db.Columns.Count; j++)
{
ws.Cells[i + 2, j + 1] = db.Rows[i][j].ToString();
}
}
wk.SaveAs(Info, ms, ms, ms, ms, ms, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlShared, ms, ms, ms, ms, ms);
ex.Quit();
}
}dal層法
public List<ProductNewModel> GetListToExcel()
{
StringBuilder sql = new StringBuilder();
sql.Append("SELECT a.id as aid,a.title as atitle,a.BarCode as aBarCode,a.Code as aCode,a.MarketPrice as aMarketPrice,a.PerfectPrice as aPerfectPrice,a.CompanyName as aCompanyName,b.title as btitle,c.title as ctitle ");
sql.Append("FROM WR_ProductNew as a ");
sql.Append("LEFT JOIN WR_Brand as b ");
sql.Append("ON a.brandid = b.id ");
sql.Append("LEFT JOIN WR_Product_Category3 as c ");
sql.Append("ON a.Category3ID = c.id ");
sql.Append("ORDER BY a.id");
DataSet ds = QueryDataSet(sql.ToString());
List<ProductNewModel> list = new List<ProductNewModel>();
ProductNewModel model;
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
model = new ProductNewModel();
model.ID = DataConvert.ToInt32(ds.Tables[0].Rows[i]["aid"].ToString());
model.Title = DataConvert.ToString(ds.Tables[0].Rows[i]["atitle"].ToString());
model.BarCode = DataConvert.ToString(ds.Tables[0].Rows[i]["aBarCode"].ToString());
model.Code = DataConvert.ToString(ds.Tables[0].Rows[i]["aCode"].ToString());
model.MarketPrice = DataConvert.ToString(ds.Tables[0].Rows[i]["aMarketPrice"].ToString());
model.PerfectPrice = DataConvert.ToString(ds.Tables[0].Rows[i]["aPerfectPrice"].ToString());
model.CompanyName = DataConvert.ToString(ds.Tables[0].Rows[i]["acompanyName"].ToString());
model.SavePrice = DataConvert.ToString(ds.Tables[0].Rows[i]["ctitle"].ToString());
model.WebconfigPic = DataConvert.ToString(ds.Tables[0].Rows[i]["btitle"].ToString());
list.Add(model);
}
return list;
}