方法1：
//sql语句
SELECT cu.CustomerCode as '客户编号'
,CustomerName as '客户名称',cu.Address as '联系地址',cu.Telephone as '电话'
,cu.TelAreaCode as '电话区号' ,cu.Fax as '传真号',cu.FaxAreaCode as '传真区号'
,cu.FaxExtNo as '传真',CityID as '所属城市',WebSiteUrl as '公司网站',
StatusName as '客户状态',SourceName as '客户来源',TypeName as '客户类型',LevelName as '客户级别',IndustryName as '客户所属行业'
FROM T_Customer as cu
inner join T_AddressBook as d on d.CustomerCode =cu.CustomerCode
inner join dbo.T_Customer_Source as cus on cus.SourceId=cu.SourceId
inner join dbo.T_Customer_Status as sta on sta.StatusId=cu.StatusId
inner join dbo.T_Customer_Type as typ on typ.TypeId=cu.TypeId
inner join dbo.T_Customer_Level as lev on lev.LevelId=cu.LevelId
inner join dbo.T_Customer_Industry as ind on ind.IndustryId=cu.IndustryId
//导出按钮事件
protected void Button1_Click(object sender, EventArgs e)
{
Export();
}
//导出方法
private void Export()
{
string sql = "select * from T_Role";
DataSet ds = DbHelperSQL.Query(sql);
DataTable dt = ds.Tables[0];
if (dt != null && dt.Rows.Count > 0)
{
string time = DateTime.Now.ToString("yyyyMMddHHmmss");
this.ExportExcel(dt, time + "_统计客户数据");
}
}
/// <summary>
/// 导出Excel数据
/// </summary>
/// <param name="data"></param>
/// <param name="fileName"></param>
protected void ExportExcel(System.Data.DataTable data, string fileName)
{
if (data != null && data.Rows.Count > 0)
{
Response.ContentType = "application/vnd.ms-excel";
Response.ContentEncoding = System.Text.Encoding.UTF8;
Response.Charset = "Utf-8";
Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(fileName + ".xls", System.Text.Encoding.UTF8));
System.Text.StringBuilder sbHtml = new System.Text.StringBuilder();
sbHtml.AppendLine("<meta http-equiv=\"Content-Type\" content=\"text/html; charset=utf-8\">");
sbHtml.AppendLine("<table cellspacing=\"0\" cellpadding=\"5\" rules=\"all\" border=\"1\">");
//写出列名
sbHtml.AppendLine("<tr style=\"font-weight: bold; white-space: nowrap;\">");
foreach (System.Data.DataColumn column in data.Columns)
{
sbHtml.AppendLine("<td>" + column.ColumnName + "</td>");
}
sbHtml.AppendLine("</tr>");
//写数据
bool isWarn = false;
foreach (System.Data.DataRow row in data.Rows)
{
isWarn = false;
sbHtml.Append("<tr>");
foreach (System.Data.DataColumn column in data.Columns)
{
if ("状态".Equals(column.ColumnName) && !"正常".Equals(row[column].ToString()))
{
isWarn = true;
break;
}
}
foreach (System.Data.DataColumn column in data.Columns)
{
if (isWarn)
{
sbHtml.Append("<td style='background-color:Tomato;color:Navy;font-weight:bold;'>");
}
else
{
sbHtml.Append("<td>");
}
sbHtml.Append(row[column].ToString() + "</td>");
}
sbHtml.AppendLine("</tr>");
}
sbHtml.AppendLine("</table>");
Response.Write(sbHtml.ToString());
Response.End();
}
}
方法2：
/// <summary>
/// 导出发货单点击事件
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void LinkButton1_Click(object sender, EventArgs e)
{
LinkButton lkbtn = (LinkButton)sender;
string InvoceID = lkbtn.CommandArgument;
ExportToExcel(InvoceID);
}
/// <summary>
/// 导出客户数据到 Excel
///
/// </summary>
public void ExportToExcel(string InvoceID)
{
System.Data.DataTable dt = null;string strSQL = @"SELECT TI.ID
,TI.Invoice_Code
,TOR.OrderCode
,TOR.CustomerCode
,TOR.CallTime
,TOR.DeliveryAddress
,TOR.PayType
,TOR.ProductCount
,TOR.TotalAmount
,TOR.IsReceived
,TOR.TransportWay
,TC.CustomerName
,TCT.TypeName AS CustomerType
,TP.ProvinceName AS Area
,TC.Phone
,TC.Post
FROM T_Invoice TI
LEFT JOIN T_Order TOR
ON TOR.OrderCode=TI.Order_Code
LEFT JOIN T_Customer TC
ON TC.CustomerCode=TOR.CustomerCode
LEFT JOIN T_Customer_Type TCT
ON TCT.TypeId=TC.CustomerType
LEFT JOIN T_Province TP
ON TP.ProvinceID=TC.Area
WHERE TI.ID='{0}'";
strSQL = string.Format(strSQL, InvoceID);
dt = ClsDbAccess.getDataSet(strSQL).Tables[0];
if (dt != null && dt.Rows.Count > 0)
{
System.Data.DataRow r = dt.Rows[0];
string fileURL = Server.MapPath("~/temp/客户发货单"+DateTime.Now.ToString ("yyyyMMddHHmmss")+".xls");//实例化操作Excel的对象
ApplicationClass xlsApp = new ApplicationClass();if (xlsApp == null)
{
//对此实例进行验证，如果为null则表示运行此代码的机器可能未安装Excel
Response.Write("未安装Excel!");
}string templeFilePath = Server.MapPath("~/Template/客户发货单_Template.xls");
Workbook workbook = xlsApp.Workbooks.Open(templeFilePath, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing);
string InvoceCode = r["Invoice_Code"].ToString();
Worksheet worksheetCopy = workbook.Sheets[1] as Worksheet; //第一个sheet页
string sheetName = InvoceCode + DateTime.Now.ToString("客户订购单") + "";worksheetCopy.Name = sheetName; //这里修改sheet名称
worksheetCopy.Cells[5, 1] = "NO:" + r["Invoice_Code"].ToString();
worksheetCopy.Cells[8, 5] = r["CustomerName"].ToString();
worksheetCopy.Cells[8, 10] = r["OrderCode"].ToString();
worksheetCopy.Cells[9, 5] = r["Area"].ToString();
worksheetCopy.Cells[9, 10] = r["Phone"].ToString();
worksheetCopy.Cells[10, 5] = r["DeliveryAddress"].ToString();
worksheetCopy.Cells[10, 10] = r["Post"].ToString();
worksheetCopy.Cells[11, 10] ="'"+ r["CustomerCode"].ToString();worksheetCopy.Cells[25, 9] = "'" + r["ProductCount"].ToString();
worksheetCopy.Cells[25, 12] = "'" + r["TotalAmount"].ToString();List<Excel.CheckBox> boxes = new List<Excel.CheckBox>();
for (int i = 1; i <= 15; i++)
{
boxes.Add(worksheetCopy.CheckBoxes(i) as Excel.CheckBox);
}
Excel.CheckBox cbx = null;
for (int i = 0; i < boxes.Count; i++)
{
cbx = boxes[i];
if (cbx.Caption.Trim() == r["CustomerType"].ToString().Trim() || cbx.Caption.Trim() == r["IsReceived"].ToString().Trim () || cbx.Caption.Trim () == r["TransportWay"].ToString().Trim ())
{
cbx.Value = true;
}}
strSQL = @"
select b.CASE_NUMBER as '箱号'
,b.PRODUCT_PRODUCTDATE as '生产日期'
,b.PRODUCT_CODE as '产品货号'
,b.PRODUCT_NAME as '产品名称'
,b.PRODUCT_MODEL as '规格'
,a.Product_Num as '数量'
,b.PRODUCT_PRICE as '单价'
,a.ZheKou as '折扣'
,a.Amount as '金额'
from T_Order_Product a
left join T_Product b
on b.PRODUCT_CODE=a.Product_Code
where Order_Code='{0}' ";
strSQL = string.Format(strSQL, r["OrderCode"].ToString());
System.Data.DataTable _dt = ClsDbAccess.getDataSet(strSQL).Tables[0];int rowIndex = 15;
foreach (System.Data.DataRow row in _dt.Rows)
{
worksheetCopy.Cells[rowIndex, 2] ="'"+ row["箱号"].ToString();
worksheetCopy.Cells[rowIndex, 3] = "'" + row["生产日期"].ToString();
worksheetCopy.Cells[rowIndex, 4] = "'" + row["产品货号"].ToString();
worksheetCopy.Cells[rowIndex, 6] = "'" + row["产品名称"].ToString();
worksheetCopy.Cells[rowIndex, 8] = row["规格"].ToString();
worksheetCopy.Cells[rowIndex, 9] = "'"+row["数量"].ToString();
worksheetCopy.Cells[rowIndex, 10] = "'"+row["单价"].ToString();
worksheetCopy.Cells[rowIndex, 11] = "'" + row["折扣"].ToString();
worksheetCopy.Cells[rowIndex, 12] = "'" + row["金额"].ToString();
rowIndex++;
}
//另存为
string filepath = fileURL;
xlsApp.DisplayAlerts = true;
xlsApp.AlertBeforeOverwriting = true;
workbook.Saved = true;
workbook.SaveCopyAs(filepath);//释放excel资源
workbook.Close(true, Type.Missing, Type.Missing);
workbook = null;
xlsApp.Quit();
xlsApp = null;
DownLoadFile(fileURL);
}
}/// <summary>
/// 下载文件
/// </summary>
/// <param name="fileURL"></param>
private void DownLoadFile(string fileURL)
{
System.IO.FileInfo fileInfo = new System.IO.FileInfo(fileURL);
Response.Clear();
Response.AddHeader("content-disposition", "attachment;filename=" + Server.UrlEncode(fileInfo.Name.ToString()));
Response.AddHeader("content-length", fileInfo.Length.ToString());
Response.ContentType = "application/octet-stream";
Response.ContentEncoding = System.Text.Encoding.Default;
Response.WriteFile(fileURL);
}