private void ApplyImageCell(ExcelWorksheet ws, string cellAddress, string format, string filename)
{
CLogManager.WriteSL("ApplyImageCell", "Cell:" + cellAddress + " format:" + format + " file:" + filename);
try
{
filename = Path.Combine(ConfigurationManager.AppSettings["UploadDirectory"], filename);
if (!File.Exists(filename))
{
filename = Path.Combine(ConfigurationManager.AppSettings["UploadDirectory"], "NoImageAvailable.png");
}
if (File.Exists(filename))
{
Image image = Image.FromFile(filename);
var picture = ws.Drawings.AddPicture("FrontImage_" + cellAddress, image);
picture.From.Column = GetColIndex(cellAddress) - 1; // colIndex;
picture.From.Row = GetRowIndex(cellAddress) - 1; // rowIndex;
picture.SetSize(220, 220);
// 2x2 px space for better alignment
picture.From.ColumnOff = Pixel2MTU(5);
picture.From.RowOff = Pixel2MTU(5);
}
else
{
CLogManager.WriteSL("ApplyImageCell", "File not found:" + filename);
}
}
catch (Exception ex)
{
CLogManager.WriteSL("ApplyImageCell-Ex", ex.ToString());
}
}
/// <summary>
/// Doc file excel chi tiet dua vao database
/// </summary>
/// <param name="clientKey"></param>
/// <param name="excelfile"></param>
/// <returns></returns>
public string ImportExcel(string clientKey, string excelfile)
{
try
{
CLogManager.WriteSL("IMPORT", "File:" + excelfile);
int startDataRow = 11;
CMixExcel mixExcel = new CMixExcel(excelfile, false);
ExcelPackage pck = (ExcelPackage)mixExcel.ExcelMixCore;
var worksheet = pck.Workbook.Worksheets[1];
bool isAllOk = true;
CLogManager.WriteSL("IMPORT", "Begin For to :" + worksheet.Dimension.End.Row.ToString());
for (int row = startDataRow; row <= worksheet.Dimension.End.Row; row++)
{
string inputValue = GetRowInXml(worksheet, row);
CLogManager.WriteSL("IMPORT", "Input :" + inputValue);
if (string.IsNullOrEmpty(inputValue))
{
continue;
}
inputValue = "<RequestParams Sys_ViewID=\"28\" Action=\"INSERT\" " + inputValue + "/>";
if (ExecuteImport(clientKey, inputValue))
{
//Delete imported row
worksheet.DeleteRow(row);
row--;
}
else
{
isAllOk = false;
}
}
string downloadDir = System.Configuration.ConfigurationManager.AppSettings["DownloadDirectory"];
string tempFile = downloadDir + "\\Product\\Error";// + Guid.NewGuid().ToString() + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx";
if (!Directory.Exists(tempFile))
{
Directory.CreateDirectory(tempFile);
}
string fileName = Guid.NewGuid().ToString() + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx";
tempFile = tempFile + "/" + fileName;
//Khi xong thi luu file con lai vao temp
pck.SaveAs(new System.IO.FileInfo(tempFile));
mixExcel.CloseStream();
if (isAllOk)
{
return("Code\n00-OK");
}
else
{
return("Code\n01-//Product//Error//" + fileName);
}
}
catch (Exception ex)
{
CLogManager.WriteSL("IMPORT", ex.ToString());
return("Code\n02-" + ex.Message);
}
}
public static string SendMessage(string sendTo,
string sendSubject, string sendMessage)
{
try
{
// validate the email address
bool bTest = ValidateEmailAddress(sendTo);
// if the email address is bad, return message
if (bTest == false)
{
return("Invalid recipient email address: " + sendTo);
}
//Mail From
string sendFrom = ConfigurationManager.AppSettings["MailFrom"].ToString();
// create the email message
MailMessage message = new MailMessage(
sendFrom,
sendTo,
sendSubject,
sendMessage);
message.IsBodyHtml = true;
// create smtp client at mail server location
SmtpClient client = new SmtpClient();
client.Credentials = new System.Net.NetworkCredential(ConfigurationManager.AppSettings["MailFrom"].ToString(), ConfigurationManager.AppSettings["MailPass"].ToString());
client.Port = Convert.ToInt32(ConfigurationManager.AppSettings["MailPort"].ToString());
client.Host = ConfigurationManager.AppSettings["MailHost"].ToString();
client.EnableSsl = Convert.ToBoolean(ConfigurationManager.AppSettings["MailSsl"].ToString());
// send message
CLogManager.WriteSL("SendMessage", "To: " + sendTo + " Subject: " + sendSubject);
ServicePointManager.ServerCertificateValidationCallback =
delegate(object s, X509Certificate certificate,
X509Chain chain, SslPolicyErrors sslPolicyErrors)
{ return(true); };
client.Send(message);
CLogManager.WriteSL("SendMessage", "Message sent");
return("00-Message sent to " + sendTo + " at " + DateTime.Now.ToString() + ".");
}
catch (Exception ex)
{
CLogManager.WriteSL("SendMessage", "Error: " + ex.Message);
return("01-" + ex.Message.ToString());
}
}
public string SendReport(string ClientKey, string InputValue)
{
try
{
CApplicationMessage result = new CMessageToCustomer().SendReport(ClientKey, InputValue);
List <CApplicationMessage> list = new List <CApplicationMessage>();
list.Add(result);
return(CApplicationMessage.ToCSV <CApplicationMessage>(list));
}
catch (Exception ex)
{
CLogManager.WriteSL("SendReport", ex.ToString());
List <CApplicationMessage> list = new List <CApplicationMessage>();
list.Add(new CApplicationMessage()
{
Name = "", Description = ex.Message, Result = 0
});
return(CApplicationMessage.ToCSV <CApplicationMessage>(list));
}
}
/// <summary>
/// Transmit an email message with
/// attachments
/// </summary>
/// <param name="sendTo">Recipient Email Address</param>
/// <param name="sendFrom">Sender Email Address</param>
/// <param name="sendSubject">Subject Line Describing Message</param>
/// <param name="sendMessage">The Email Message Body</param>
/// <param name="attachments">A string array pointing to the location of each attachment</param>
/// <returns>Status Message as String</returns>
private static string SendMessageWithAttachment(string sendTo, string sendCC, string sendBCC,
string sendSubject, string sendMessage, String strFilename1, Stream sFile1, string strFilename2, Stream sFile2, string strFilename3, Stream sFile3)
{
try
{
sendTo = sendTo.Replace(";", ",");
sendCC = sendCC.Replace(";", ",");
sendBCC = sendBCC.Replace(";", ",");
sendSubject = sendSubject.Replace("\r", "");
sendSubject = sendSubject.Replace("\n", "");
// validate email address
bool bTest = ValidateEmailAddress(sendTo);
if (bTest == false)
{
return("Invalid recipient email address: " + sendTo);
}
string sendFrom = ConfigurationManager.AppSettings["MailFrom"].ToString();
// Create the basic message
MailMessage message = new MailMessage(
sendFrom,
sendTo,
sendSubject,
sendMessage);
message.IsBodyHtml = true;
//message.CC = new MailAddressCollection();
if (!string.IsNullOrEmpty(sendCC))
{
message.CC.Add(sendCC);
}
//message.Bcc = new MailAddressCollection();
if (!string.IsNullOrEmpty(sendBCC))
{
message.Bcc.Add(sendBCC);
}
// The attachments arraylist should point to a file location where
// the attachment resides - add the attachments to the message
if (sFile1 != null)
{
Attachment attached = new Attachment(sFile1, strFilename1);
message.Attachments.Add(attached);
}
if (sFile2 != null)
{
Attachment attached = new Attachment(sFile2, strFilename2);
message.Attachments.Add(attached);
}
if (sFile3 != null)
{
Attachment attached = new Attachment(sFile3, strFilename3);
message.Attachments.Add(attached);
}
// create smtp client at mail server location
SmtpClient client = new SmtpClient();
client.Credentials = new System.Net.NetworkCredential(ConfigurationManager.AppSettings["MailFrom"].ToString(), ConfigurationManager.AppSettings["MailPass"].ToString());
client.Port = Convert.ToInt32(ConfigurationManager.AppSettings["MailPort"].ToString());
client.Host = ConfigurationManager.AppSettings["MailHost"].ToString();
client.EnableSsl = Convert.ToBoolean(ConfigurationManager.AppSettings["MailSsl"].ToString());
// send message
CLogManager.WriteSL("SendMessageWithAttachment", "To: " + sendTo + " Subject: " + sendSubject);
client.Send(message);
CLogManager.WriteSL("SendMessageWithAttachment", "Message sent");
return("00-Message sent to " + sendTo + " at " + DateTime.Now.ToString() + ".");
}
catch (Exception ex)
{
CLogManager.WriteSL("SendMessageWithAttachment", "Error: " + ex.ToString());
return("01-" + ex.Message);
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (Context.Request.Form["listId"] != null)
{
string listId = Context.Request.Form["listId"];
if (String.IsNullOrEmpty(listId))
{
return;
}
string exportType = "", addressTo = "", fullName = "", telephone = "", email = "", cellPhone = "", position = "", fileName = ""; //"Excel|Pdf
string sysViewId = "13"; // "26|27"
string languageId = "129"; //"Excel|Pdf
if (Context.Request.Form["exportType"] != null)
{
exportType = Context.Request.Form["exportType"];
}
if (Context.Request.Form["sysViewID"] != null)
{
sysViewId = Context.Request.Form["sysViewId"];
}
if (Context.Request.Form["languageId"] != null)
{
languageId = Context.Request.Form["languageId"];
}
if (Context.Request.Form["exportType"] != null)
{
exportType = Context.Request.Form["exportType"];
}
if (Context.Request.Form["sysViewID"] != null)
{
sysViewId = Context.Request.Form["sysViewId"];
}
if (Context.Request.Form["languageId"] != null)
{
languageId = Context.Request.Form["languageId"];
}
if (Context.Request.Form["addressTo"] != null)
{
addressTo = Context.Request.Form["addressTo"];
}
if (Context.Request.Form["fullName"] != null)
{
fullName = Context.Request.Form["fullName"];
}
if (Context.Request.Form["telephone"] != null)
{
telephone = Context.Request.Form["telephone"];
}
if (Context.Request.Form["cellPhone"] != null)
{
cellPhone = Context.Request.Form["cellPhone"];
}
if (Context.Request.Form["position"] != null)
{
position = Context.Request.Form["position"];
}
if (Context.Request.Form["email"] != null)
{
email = Context.Request.Form["email"];
}
///SUA CHU FILE NAM HOA NHE NAM
if (Context.Request.Form["fileName"] != null)
{
fileName = Context.Request.Form["fileName"];
}
if (string.IsNullOrEmpty(fileName))
{
fileName = "NONAME";
}
// CLogManager.WritePL("email", string.Format("email:{0},cellPhone:{1},telephone:{2},addressTo:{3},fullname:{4},position:{5},exportType:{6},sysViewId:{7}", email, cellPhone, telephone, addressTo,fullName, position, exportType, sysViewId));
try
{
string contentType = "application/Excel";
//string filename = ".xlsx";
if (exportType.Equals("pdf", StringComparison.OrdinalIgnoreCase))
{
contentType = "application/pdf";
if (!fileName.EndsWith(".pdf", StringComparison.OrdinalIgnoreCase))
{
fileName += ".pdf";
}
}
else
{
if (!fileName.EndsWith(".xlsx", StringComparison.OrdinalIgnoreCase))
{
fileName += ".xlsx";
}
}
string input = string.Format("<RequestParams ListID=\"{0}\" ListProductId=\"{0}\" LanguageID=\"{1}\" ExportType=\"{2}\" start=\"0\" length=\"1\" Sys_ViewID=\"{3}\" ", listId, languageId, exportType, sysViewId);
input += string.Format(" FullName=\"{0}\" AddressTo=\"{1}\"", HtmlEncode(fullName), HtmlEncode(addressTo));
input += string.Format(" TelePhone=\"{0}\" CellPhone=\"{1}\" Email=\"{2}\" Position=\"{3}\" FileName=\"{4}\"", HtmlEncode(telephone), HtmlEncode(cellPhone), HtmlEncode(email), HtmlEncode(position), HtmlEncode(fileName));
input += "/>";
if (sysViewId.Equals("26"))
{
string inputValue = input;// string.Format("<InputValue UserID=\"2\" /><RequestParams ListID=\"{0}\" ListProductId=\"{0}\" LanguageID=\"{1}\" ExportType=\"{2}\" start=\"0\" length=\"1\" Sys_ViewID=\"{3}\" />", listId, languageId, exportType, sysViewId);
string template = AppDomain.CurrentDomain.BaseDirectory + "\\_Template\\Excel\\Template_BaoGiaCoBan.xlsx";
string result = new CExcelReport().ExportReport("", inputValue, template);
//filename = "BaogiaCoban" + DateTime.Now.ToString("yyyyMMddHHmmssfff") + filename;
WriteResponse(contentType, fileName, result);
}
else if (sysViewId.Equals("27"))
{
string inputValue = input;// string.Format("<InputValue UserID=\"2\" /><RequestParams ListID=\"{0}\" ListProductId=\"{0}\" LanguageID=\"{1}\" ExportType=\"{2}\" start=\"0\" length=\"1\" Sys_ViewID=\"{3}\" />", listId, languageId, exportType, sysViewId);
string template = AppDomain.CurrentDomain.BaseDirectory + "\\_Template\\Excel\\Template_BaoGiaChiTiet.xlsx";
string result = new CExcelReport().ExportReport("", inputValue, template);
//filename = "BaogiaChitiet" + DateTime.Now.ToString("yyyyMMddHHmmssfff") + filename;
WriteResponse(contentType, fileName, result);
}
//Export(sData);
}
catch (Exception ex)
{
CLogManager.WriteSL("ExportTemplate", ex.ToString());
Console.WriteLine(ex.ToString());
Response.Cookies["iscompleteexport"].Value = "false";
Response.Write(ex.ToString());
Response.End();
// Page.ClientScript.RegisterClientScriptBlock(Page.GetType(), "Exception", string.Format("alert('{0}')", ex.ToString()), true);
}
}
}