using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Reflection;
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
namespace WebApplication3
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
![](/Images/OutliningIndicators/ContractedBlock.gif)
{
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// WebForm1 的摘要说明。
/// </summary>
public class WebForm1 : System.Web.UI.Page
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
protected System.Web.UI.WebControls.DataGrid DataGrid1;
protected System.Web.UI.WebControls.Button Button1;
private void Page_Load(object sender, System.EventArgs e)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
// 在此处放置用户代码以初始化页面
}
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
Web 窗体设计器生成的代码#region Web 窗体设计器生成的代码
override protected void OnInit(EventArgs e)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
//
// CODEGEN: 该调用是 ASP.NET Web 窗体设计器所必需的。
//
InitializeComponent();
base.OnInit(e);
}
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 设计器支持所需的方法 - 不要使用代码编辑器修改
/// 此方法的内容。
/// </summary>
private void InitializeComponent()
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
this.Button1.Click += new System.EventHandler(this.Button1_Click);
this.Load += new System.EventHandler(this.Page_Load);
![](/Images/OutliningIndicators/InBlock.gif)
}
#endregion
![](/Images/OutliningIndicators/InBlock.gif)
private void Button1_Click(object sender, System.EventArgs e)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
SaveToExcel();
}
![](/Images/OutliningIndicators/InBlock.gif)
private string [,] myData=
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{"车牌号","类型","品 牌","型 号","颜 色","附加费证号","车架号"},
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{"浙KA3676","危险品","货车","铁风SZG9220YY","白","1110708900","022836"},
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{"浙KA4109","危险品","货车","解放CA4110P1K2","白","223132","010898"},
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{"浙KA0001A","危险品","货车","南明LSY9190WS","白","1110205458","0474636"},
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{"浙KA0493","上普货","货车","解放LSY9190WS","白","1110255971","0094327"},
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{"浙KA1045","普货","货车","解放LSY9171WCD","蓝","1110391226","0516003"},
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{"浙KA1313","普货","货车","解放9190WCD","蓝","1110315027","0538701"},
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{"浙KA1322","普货","货车","解放LSY9190WS","蓝","24323332","0538716"},
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{"浙KA1575","普货","货车","解放LSY9181WCD","蓝","1110314149","0113018"},
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{"浙KA1925","普货","货车","解放LSY9220WCD","蓝","1110390626","00268729"},
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{"浙KA2258","普货","货车","解放LSY9220WSP","蓝","111048152","00320"}
![](/Images/OutliningIndicators/InBlock.gif)
};
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
private void SaveToExcel()
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
//创建一个Excel文件
![](/Images/OutliningIndicators/InBlock.gif)
Excel.Application myExcel = new Excel.Application ( ) ;
![](/Images/OutliningIndicators/InBlock.gif)
myExcel.Application.Workbooks.Add ( true ) ;
![](/Images/OutliningIndicators/InBlock.gif)
//让Excel文件可见
![](/Images/OutliningIndicators/InBlock.gif)
myExcel.Visible=true;
![](/Images/OutliningIndicators/InBlock.gif)
//第一行为报表名称
![](/Images/OutliningIndicators/InBlock.gif)
myExcel.Cells[1,4]="普通报表";
![](/Images/OutliningIndicators/InBlock.gif)
//逐行写入数据,
![](/Images/OutliningIndicators/InBlock.gif)
for(int i=0;i<11;i++)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
for(int j=0;j<7;j++)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
//以单引号开头,表示该单元格为纯文本
myExcel.Cells[2+i,1+j]="'"+myData[i,j];
}
}
}
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
}
}
![](/Images/OutliningIndicators/None.gif)
2
private string SaveToExcel(DataTable dtDetail)
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
![](/Images/OutliningIndicators/InBlock.gif)
// string urlPath = HttpContext.Current.Request.ApplicationPath ;
// string physicPath = HttpContext.Current.Server.MapPath(urlPath);
// string strFullName = System.IO.Path.Combine(physicPath,xmlFileName.P_TempXlsFile);
//创建一个Excel文件
Excel.Application myExcel = new Excel.Application ( ) ;
![](/Images/OutliningIndicators/InBlock.gif)
// myExcel.Application.Workbooks.Open(strFullName);
// myExcel.Cells.Clear();
![](/Images/OutliningIndicators/InBlock.gif)
myExcel.Application.Workbooks.Add ( true ) ;
![](/Images/OutliningIndicators/InBlock.gif)
// myExcel.ActiveWorkbook.Open(strFullName);
![](/Images/OutliningIndicators/InBlock.gif)
//
// myExcel.AutoRecover = true;
// myExcel.ActiveWorkbook.Save();
![](/Images/OutliningIndicators/InBlock.gif)
//让Excel文件可见
![](/Images/OutliningIndicators/InBlock.gif)
//myExcel.Visible=true;
![](/Images/OutliningIndicators/InBlock.gif)
//第一行为报表名称
![](/Images/OutliningIndicators/InBlock.gif)
myExcel.Cells[1,4]="材料统计";
![](/Images/OutliningIndicators/InBlock.gif)
//逐行写入数据,
myExcel.Cells[2,1]= "材料编号";
myExcel.Cells[2,2]= "项目分类";
myExcel.Cells[2,3]= "期初数量";
myExcel.Cells[2,4]= "入库数量";
myExcel.Cells[2,5]= "出库数量";
myExcel.Cells[2,6]= "损益量";
myExcel.Cells[2,7]= "期末数量";
myExcel.Cells[2,8]= "订单损耗";
myExcel.Cells[2,9]= "损益比";
myExcel.Cells[2,10]= "备注";
![](/Images/OutliningIndicators/InBlock.gif)
DataView dv = dtDetail.DefaultView;
dv.Sort = "NewSort,SortOrder";
![](/Images/OutliningIndicators/InBlock.gif)
for(int i = 0 ; i < dv.Count ; i++)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
for(int j = 0; j < dv.Table.Columns.Count; j++)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
//以单引号开头,表示该单元格为纯文本
//myExcel.Cells[2+i,1+j]="'" + dtDetail.Rows[i][j].ToString().Trim();
![](/Images/OutliningIndicators/InBlock.gif)
if (dv.Table.Columns[j].ColumnName == "MaterialNo")
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
if (!dv.Table.Rows[i].IsNull(j))
myExcel.Cells[3+i,1]= dv[i][j].ToString().Trim();
}
else if (dv.Table.Columns[j].ColumnName == "NewSort")
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
if (!dv.Table.Rows[i].IsNull(j))
myExcel.Cells[3+i,2]= dv[i][j].ToString().Trim();
}
else if (dv.Table.Columns[j].ColumnName == "PeriodStartAmount")
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
if (!dv.Table.Rows[i].IsNull(j))
myExcel.Cells[3+i,3]= dv[i][j].ToString().Trim();
}
else if (dv.Table.Columns[j].ColumnName == "InComeSumByMat")
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
if (!dv.Table.Rows[i].IsNull(j))
myExcel.Cells[3+i,4]= dv[i][j].ToString().Trim();
}
else if (dv.Table.Columns[j].ColumnName == "TheoryOut")
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
if (!dv.Table.Rows[i].IsNull(j))
myExcel.Cells[3+i,5]= dv[i][j].ToString().Trim();
}
else if (dv.Table.Columns[j].ColumnName == "Waste")
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
if (!dv.Table.Rows[i].IsNull(j))
myExcel.Cells[3+i,6]= dv[i][j].ToString().Trim();
}
else if (dv.Table.Columns[j].ColumnName == "PeriodEndAmount")
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
if (!dv.Table.Rows[i].IsNull(j))
myExcel.Cells[3+i,7]= dv[i][j].ToString().Trim();
}
else if (dv.Table.Columns[j].ColumnName == "WorkSumByMat")
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
if (!dv.Table.Rows[i].IsNull(j))
myExcel.Cells[3+i,8]= dv[i][j].ToString().Trim();
}
else if (dv.Table.Columns[j].ColumnName == "WasteRate")
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
if (!dv.Table.Rows[i].IsNull(j))
myExcel.Cells[3+i,9]= dv[i][j].ToString().Trim();
}
else if (dv.Table.Columns[j].ColumnName == "Memo")
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
if (!dv.Table.Rows[i].IsNull(j))
myExcel.Cells[3+i,10]= dv[i][j].ToString().Trim();
}
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
}
}
![](/Images/OutliningIndicators/InBlock.gif)
string urlPath = HttpContext.Current.Request.ApplicationPath + "/Temp/";
string physicPath = HttpContext.Current.Server.MapPath(urlPath);
string fileName = Guid.NewGuid() + ".xls";
![](/Images/OutliningIndicators/InBlock.gif)
string strFullName = physicPath + fileName;
![](/Images/OutliningIndicators/InBlock.gif)
//myExcel.ActiveWorkbook.SaveAs(strFullName,Excel.XlFileFormat.xlXMLSpreadsheet, Type.Missing, Type.Missing, Type.Missing, Type.Missing,Excel.XlSaveAsAccessMode.xlShared, Type.Missing,Type.Missing, Type.Missing, Type.Missing, Type.Missing );
myExcel.SaveWorkspace(strFullName);
myExcel.ActiveWorkbook.Close(true,strFullName,Type.Missing);
![](/Images/OutliningIndicators/InBlock.gif)
myExcel.AlertBeforeOverwriting = false;
//myExcel.Save(strFullName);
![](/Images/OutliningIndicators/InBlock.gif)
myExcel.Quit();
return strFullName;
![](/Images/OutliningIndicators/InBlock.gif)
}
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
private void StartOutPort()
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
//获得从表
DataSet dsDetail = new DataSet();
GetDsForDetailTJ(ref dsDetail);
if (dsDetail == null || dsDetail.Tables.Count <= 0)
return;
![](/Images/OutliningIndicators/InBlock.gif)
DataTable Detaildt1 = null;
![](/Images/OutliningIndicators/InBlock.gif)
Detaildt1 = GetFullDt(ref dsDetail,"detail");
![](/Images/OutliningIndicators/InBlock.gif)
string strFullfileName = SaveToExcel(Detaildt1);
![](/Images/OutliningIndicators/InBlock.gif)
// dsDetail.WriteXml(physicPath+fileName);
![](/Images/OutliningIndicators/InBlock.gif)
HttpResponse response = HttpContext.Current.Response;
response.Clear();
response.WriteFile(strFullfileName);
string httpHeader="attachment;filename=newFile.xls";
response.AppendHeader("Content-Disposition", httpHeader);
response.Flush();
![](/Images/OutliningIndicators/InBlock.gif)
System.IO.File.Delete(strFullfileName);//删除临时文件
response.End();
}3
private void SaveToExcel()
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
![](/Images/OutliningIndicators/InBlock.gif)
string urlPath = HttpContext.Current.Request.ApplicationPath + "/Temp/";
![](/Images/OutliningIndicators/InBlock.gif)
string physicPath = HttpContext.Current.Server.MapPath(urlPath);
![](/Images/OutliningIndicators/InBlock.gif)
string fileName = Guid.NewGuid() + ".Xls";
![](/Images/OutliningIndicators/InBlock.gif)
string connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + physicPath + fileName +";Extended Properties=Excel 8.0;";
![](/Images/OutliningIndicators/InBlock.gif)
OleDbConnection objConn = new OleDbConnection(connString);
OleDbCommand objCmd = new OleDbCommand();
objCmd.Connection = objConn;
objConn.Open();
![](/Images/OutliningIndicators/InBlock.gif)
//建立表结构
![](/Images/OutliningIndicators/InBlock.gif)
objCmd.CommandText = @"CREATE TABLE TBL_Customer (CustomerName varchar,CustomerNo varchar)";
![](/Images/OutliningIndicators/InBlock.gif)
objCmd.ExecuteNonQuery();
![](/Images/OutliningIndicators/InBlock.gif)
objCmd.CommandText = "INSERT INTO TBL_Customer(CustomerName, CustomerNo) VALUES ('毛消化', 'good')";
![](/Images/OutliningIndicators/InBlock.gif)
objCmd.ExecuteNonQuery();
![](/Images/OutliningIndicators/InBlock.gif)
//提供下载
![](/Images/OutliningIndicators/InBlock.gif)
objCmd.Dispose();
objConn.Dispose();
![](/Images/OutliningIndicators/InBlock.gif)
HttpResponse response = HttpContext.Current.Response;
response.Clear();
response.WriteFile(physicPath + fileName);
string httpHeader="attachment;filename=backup.Xls";
response.AppendHeader("Content-Disposition", httpHeader);
response.Flush();
![](/Images/OutliningIndicators/InBlock.gif)
System.IO.File.Delete(physicPath + fileName);//删除临时文件
response.End();
}