Merhaba, zaman zaman bir seferlik raporlar isteniyor.
Siz de SQL’de bir sorgu yazıp sonucu excele kopyalayıp gönderiyorsunuz.
Sonra o raporu ayda bir istemeye başlıyorlar, siz o yazdığınız scripti tekrar bir yerlerde bulup gönderiyorsunuz.
Sonra bu düzenli excel olarak gelsin diyorlar.
O zaman da ufak bir konsol uygulaması yazıyordum.
Ancak sonra düşündüm, aslında ben scripti hazırlamışım zaten, excel olarak göndermek de her dataset için benzer işlem.
Acaba dedim: “Bir ufak exe yazsam ve her gün çalışsa ve tanım tablomdaki kayıtlara göre stored prosedürümü çalıştırsa ve çıkan sonucu excel ile ilgili yerlere gönderse nasıl olur?”
Bunun üzerine aşağıdaki uygulamayı yazdım.
CREATE TABLE [dbo].[BatchRaporJob](
[Id] [int] IDENTITY(1,1) NOT NULL,
[StoredProcedureName] [varchar](64) NOT NULL,
[DayList] [varchar](100) NOT NULL,
[DayNumber] [int] NOT NULL,
[ToList] [varchar](250) NOT NULL,
[CcList] [varchar](250) NOT NULL,
[Subject] [varchar](250) NOT NULL,
[FileName] [varchar](250) NOT NULL,
[MessageContentHtml] [nvarchar](4000) NOT NULL,
[CreatedDate] [datetime] NOT NULL,
[CreatedUserId] [int] NOT NULL,
[ChangedDate] [datetime] NOT NULL,
[ChangedUserId] [int] NOT NULL
) List<BatchJob> lstBatchJob = BatchJobOperations.GetBatchJobs();
foreach (BatchJob job in lstBatchJob)
{
if (job.DayNumber == 0)
{
List<string> dayNames = job.DayList.Split(';').ToList();
if (dayNames.Contains(DateTime.Now.DayOfWeek.ToString()))
{
sendReport(job);
}
}
else
{
if (job.DayNumber == DateTime.Now.Day)
{
sendReport(job);
}
}
}internal static class BatchJobOperations
{
internal static List<BatchJob> GetBatchJobs()
{
List<BatchJob> lst = new List<BatchJob>();
DataTable dt = new DatabaseOperations().ReturnDataTable("SP_L_GETBATCHJOB", new List<SqlParameter>() { });
foreach (DataRow dr in dt.Rows)
{
lst.Add(new BatchJob()
{
Id = Convert.ToInt32(dr["Id"]),
StoredProcedureName = dr["StoredProcedureName"].ToString(),
DayList = dr["DayList"].ToString(), // Like Monday;Friday
DayNumber = Convert.ToInt32(dr["DayNumber"]),
ToList = dr["ToList"].ToString(), // Like zamkinos@gmail.com;ramazan@zamkinos.net
CcList = dr["CcList"].ToString(), // Like zamkinos@gmail.com;ramazan@zamkinos.net
Subject = dr["Subject"].ToString(),
FileName = dr["FileName"].ToString(), // Like Sales_Report_{CurrentDate}
MessageContentHtml = dr["MessageContentHtml"].ToString(),
CreatedDate = Convert.ToDateTime(dr["CreatedDate"]),
CreatedUserId = Convert.ToInt32(dr["CreatedUserId"]),
ChangedDate = Convert.ToDateTime(dr["ChangedDate"]),
ChangedUserId = Convert.ToInt32(dr["ChangedUserId"])
});
}
return lst;
}
}static void sendReport(BatchJob job)
{
try
{
DataTable dataTable = new VeritabaniIslemleri().ReturnDataTable(job.StoredProcedureName);
if (dataTable.Rows.Count > 0)
{
string fileName = job.FileName.Replace("{CurrentDate}", DateTime.Now.ToString("yyyyMMdd"));
List<byte[]> attachments = new List<byte[]>() { ExcelOperations.CreateExcel(dataTable, fileName) };
List<string> lstFileName = new List<string>() { fileName + ".xlsx" };
string eMailResult = new EMailOperations().SendEmailWithAttachment(
job.ToList.Split(';').ToList(),
job.CcList.Split(';').ToList(),
job.MessageContentHtml,
job.Subject,
"your from Address",
"your From Display",
attachments,
lstFileName
);
if (!string.IsNullOrEmpty(eMailResult))
{
Console.WriteLine(eMailResult);
}
}
}
catch (Exception ex)
{
new EMailOperations().SendEmailWithAttachment(
new List<string>() { "your email address" },
new List<string>(),
ex.Message,
"BatchJobOperations Error!",
"your from Address",
"your From Display",
new List<byte[]>(),
new List<string>()
);
}
}internal static class ExcelOperations
{
public static byte[] CreateExcel(DataTable dt, string sheetName)
{
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
byte[] byteResult;
using (ExcelPackage pck = new ExcelPackage())
{
var ws = pck.Workbook.Worksheets.Add(sheetName);
// Firstly, the headers.
int columnCounter = 1;
int rowCounter = 1;
foreach (DataColumn column in dt.Columns)
{
string baslik = column.ColumnName;
ws.Cells[rowCounter, columnCounter].Value = baslik;
ws.Cells[rowCounter, columnCounter].Style.Font.Bold = true;
ws.Cells[rowCounter, columnCounter].Style.Font.Name = "Tahoma";
ws.Cells[rowCounter, columnCounter].Style.Font.Size = 12;
ws.Cells[rowCounter, columnCounter].Style.Fill.PatternType = ExcelFillStyle.Solid;
ws.Cells[rowCounter, columnCounter].Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.AliceBlue);
columnCounter++;
}
// Then, the content.
rowCounter = 2;
for (int rowIdx = 0; rowIdx < dt.Rows.Count; rowIdx++)
{
foreach (DataColumn col in dt.Columns)
{
columnCounter = col.Ordinal + 1;
object propValue = dt.Rows[rowIdx][col.Ordinal];
if (propValue == null)
{
ws.Cells[rowCounter, columnCounter].Value = "";
}
else
{
Type typeCode = dt.Columns[col.Ordinal].DataType;
if (typeCode == typeof(Int32))
ws.Cells[rowCounter, columnCounter].Value = Convert.ToInt32(propValue.ToString().Replace(".", ","), CultureInfo.GetCultureInfo("tr-TR"));
else if (typeCode == typeof(Decimal))
ws.Cells[rowCounter, columnCounter].Value = Convert.ToDecimal(propValue.ToString().Replace(".", ","), CultureInfo.GetCultureInfo("tr-TR"));
else
ws.Cells[rowCounter, columnCounter].Value = Convert.ToString(propValue).Trim();
}
ws.Cells[rowCounter, columnCounter].Style.Font.Name = "Tahoma";
ws.Cells[rowCounter, columnCounter].Style.Font.Size = 11;
ws.Cells[rowCounter, columnCounter].Style.Fill.PatternType = ExcelFillStyle.Solid;
if (rowCounter % 2 == 0)
{
ws.Cells[rowCounter, columnCounter].Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.OldLace);
}
else
{
ws.Cells[rowCounter, columnCounter].Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.PaleGoldenrod);
}
}
rowCounter++;
}
ws.DefaultRowHeight = 25;
ws.Cells.AutoFitColumns();
byteResult = pck.GetAsByteArray();
}
return byteResult;
}
}public class EMailOperations
{
public string SendEmailWithAttachment(List<string> lstTo, List<string> lstCc, string body, string subject, string fromAddress, string fromDisplay, List<byte[]> attachments, List<string> lstFileName)
{
string result = "";
try
{
using (var client = new SmtpClient())
{
MailMessage msg = new MailMessage();
foreach (string item in lstTo)
{
msg.To.Add(new MailAddress(item));
}
foreach (string item in lstCc)
{
msg.CC.Add(item);
}
msg.From = new MailAddress(fromAddress, fromDisplay, Encoding.UTF8);
msg.Body = body;
msg.Subject = subject;
msg.IsBodyHtml = true;
for (int i = 0; i < lstFileName.Count; i++)
{
msg.Attachments.Add(new EMailAttachment(attachments[i], lstFileName[i]).File);
}
client.UseDefaultCredentials = true;
client.Credentials = new System.Net.NetworkCredential(Constants.SmtpUsername, Constants.SmtpPassword);
client.Host = Constants.SmtpHost;
client.Port = Constants.SmtpPort;
client.EnableSsl = true;
client.Send(msg);
}
}
catch (Exception ex)
{
result = ex.Message;
}
return result;
}
}public class EMailAttachment
{
#region Private Fields
private MemoryStream stream;
private string filename;
private string mediaType;
#endregion Private Fields
#region Public Properties
public Stream Data { get { return stream; } }
public string Filename { get { return filename; } }
public string MediaType { get { return mediaType; } }
public Attachment File { get { return new Attachment(Data, Filename, MediaType); } }
#endregion Public Properties
#region Constructors
public EMailAttachment(byte[] data, string filename)
{
this.stream = new MemoryStream(data);
this.filename = filename;
this.mediaType = MediaTypeNames.Application.Octet;
}
public EMailAttachment(string data, string filename)
{
this.stream = new MemoryStream(System.Text.Encoding.ASCII.GetBytes(data));
this.filename = filename;
this.mediaType = MediaTypeNames.Text.Html;
}
#endregion Constructors
}Selamlar.