EPPLUS 导出excel，包括文本，图片
string[] ids = this.dDataList.SelectedIds;
if (ids.Length != 1)
{
Soyisoft.Web.WebUtil.ShowMessage(this.Page, "请选择一行数据");
return;
}
string[] idArr = ids[0].Split('-');
if (idArr.Length > 0)
{
string pdcr_info_id = idArr[0].ToString();
string dept_id = idArr[1].ToString();
string url = Server.MapPath(WebCommon.AppRootPath + "/templet/report/plm/Purchase/差异报告.xlsx");
FileStream file = new FileStream(
url,
FileMode.Open,
FileAccess.Read);
#region Excute the Epplus
try
{
#region Using ExcelPackage
// use the Excel package
using (ExcelPackage package = new ExcelPackage(file))
{
ExcelWorksheet sheet = package.Workbook.Worksheets[1];
ExcelWorksheet sheet2 = package.Workbook.Worksheets[2];
#region 主信息
DataTable dtPdcr = GetPdcrInfo(pdcr_info_id, dept_id);
if (dtPdcr != null && dtPdcr.Rows != null && dtPdcr.Rows.Count > 0)
{
sheet.Cells["B3"].Value = dtPdcr.Rows[0]["reply_user"].ToString();//批复人
sheet.Cells["F3"].Value = dtPdcr.Rows[0]["create_user"].ToString();//创建人
sheet.Cells["F5"].Value = dtPdcr.Rows[0]["dept_name_full_cn"].ToString();//公司名称
sheet.Cells["F6"].Value = dtPdcr.Rows[0]["address"].ToString();//公司地址
sheet.Cells["F7"].Value = dtPdcr.Rows[0]["post"].ToString();//邮编
sheet.Cells["F8"].Value = dtPdcr.Rows[0]["tel"].ToString();//电话
sheet.Cells["F9"].Value = dtPdcr.Rows[0]["fax"].ToString(); ;//传真
string applyDate = dtPdcr.Rows[0]["pdcr_date"].ToString();//差异登记日期;
if (applyDate != null && applyDate.Trim() != "")
{
sheet.Cells["B14"].Value = DateTime.Parse(applyDate).ToString("yyyy-MM-dd");//差异登记日期
}
sheet.Cells["B15"].Value = dtPdcr.Rows[0]["dept_no"].ToString(); ;//代理商编码
}
sheet.Cells["B13"].Value = GetArriveDate(pdcr_info_id, dept_id);//到货日期
#endregion
#region 填充数据
DataTable dt = GetPdcrInfoDetail(pdcr_info_id, dept_id);
if (dt != null && dt.Rows != null && dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
int row = i + 18;//从第18行开始
if (dt.Rows[i]["prefix_partsNo"] != DBNull.Value)
{
sheet.Cells[row, 1].Value = dt.Rows[i]["prefix_partsNo"].ToString();//配件编码
}
if (dt.Rows[i]["soi_no"] != DBNull.Value)
{
sheet.Cells[row, 2].Value = dt.Rows[i]["soi_no"].ToString();//订单号码（soi_no）
}
if (dt.Rows[i]["pdcr_qty"] != DBNull.Value)
{
sheet.Cells[row, 4].Value = dt.Rows[i]["pdcr_qty"].ToString();//差异数量
}
if (dt.Rows[i]["row_summary"] != DBNull.Value)
{
sheet.Cells[row, 5].Value = dt.Rows[i]["row_summary"].ToString();//行金额
}
if (dt.Rows[i]["pdcr_type"] != DBNull.Value)
{
sheet.Cells[row, 6].Value = dt.Rows[i]["pdcr_type"].ToString();//差异类型
}
if (dt.Rows[i]["back_parts_str"] != DBNull.Value)
{
sheet.Cells[row, 7].Value = dt.Rows[i]["back_parts_str"].ToString();//是否退回配件
}
}//end for
}// end if
#endregion
#region 导出图片
string path = WebCommon.Upload_path_sv_document;
if (!string.IsNullOrEmpty(pdcr_info_id))
{
string sqlImg = @"select img_url,image_type,image_title,image_name
from @volvodb_plm.pdcr_info_image where dept_id=@dept_id and pdcr_info_id=@pdcr_info_id";
sqlImg = SqlUtil.setValue(sqlImg, "@volvodb_plm", PlmCommon.Database_plm);
sqlImg = SqlUtil.setString(sqlImg, "@dept_id", dept_id);
sqlImg = SqlUtil.setString(sqlImg, "@pdcr_info_id", pdcr_info_id);
DataTable dtImg = PlmCommon.QueryTable(sqlImg);
if (dtImg != null && dtImg.Rows.Count > 0 && dtImg.Rows.Count > 0)
{
for (int i = 0; i < dtImg.Rows.Count; i++)
{
string imageType = dtImg.Rows[i]["image_type"].ToString();
string fileName = dtImg.Rows[i]["image_name"].ToString();
string imgUrl = dtImg.Rows[i]["img_url"].ToString();
string title = dtImg.Rows[i]["image_title"].ToString();
string imgPath = path + "//" + imgUrl + "//" + fileName;
if (!File.Exists(imgPath)) continue;
System.Drawing.Image image = Bitmap.FromFile(imgPath);
MemoryStream ms = new MemoryStream();
image.Save(ms, ImageFormat.Jpeg);
ExcelPicture pic = sheet2.Drawings.AddPicture("pic" + i.ToString(), image);
pic.SetSize(320, 180);
#region switch
switch (imageType)
{
case "1":
pic.SetPosition(65, 20);
break;
case "2":
pic.SetPosition(65,380);
break;
case "3":
pic.SetPosition(280, 20);
break;
case "4":
pic.SetPosition(280, 380);
break;
case "5":
pic.SetPosition(495, 20);
break;
case "6":
pic.SetPosition(495, 380);
break;
}//end of switch
#endregion
}//end for
}// end if
}
#endregion
string strRExportName = DateTime.Now.ToString("yyyyMMddHHmmss") + "_" + PurchaseDefine.EXCEL_EXPORT_PDCR_REPORT_EXPORT_NAME;
//Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("content-disposition", "attachment; filename=" + HttpUtility.UrlEncode(strRExportName, System.Text.Encoding.UTF8));
Response.BinaryWrite(package.GetAsByteArray());
Response.Flush();
Response.End();
package.Dispose();
GC.Collect();
// PurchaseCommon.ReportExport(this.Page, package, PurchaseDefine.EXCEL_EXPORT_PDCR_REPORT_EXPORT_NAME);
} // End of using ExcelPackage
#endregion
file.Close();
file.Dispose();
}
catch (Exception ex)
{
file.Close();
file.Dispose();
throw new Exception(ex.Message);
} // End of try
#endregion
}