using Google.Apis.Auth.OAuth2;
using Google.Apis.Gmail.v1;
using Google.Apis.Gmail.v1.Data;
using Google.Apis.Services;
using Google.Apis.Util.Store;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace MailReading
{
public class
StockistDetails
{
public string
mail { get; set; }
public string name { get; set; }
public string sub { get; set; }
}
internal static
class Program
{
///
<summary>
/// The main
entry point for the application.
///
</summary>
[STAThread]
static void
Main()
{
Application.EnableVisualStyles();
Application.SetCompatibleTextRenderingDefault(false);
string
uplaodDirectory = @"\\192.168. \bbggh\";
string
uplaodxlsDirectory = @"\\192.168 \brr\";
DataTable
dataTable = new DataTable();
dataTable.Columns.Add("name", typeof(string));
dataTable.Columns.Add("mail", typeof(string));
dataTable.Columns.Add("subject", typeof(string));
dataTable.Columns.Add("shortname", typeof(string));
dataTable.Rows.Add("vv", "vv@gmail.com",
"Doc.No","met_");
string[]
Scopes = { GmailService.Scope.GmailReadonly };
string[]
Scopevs = {
GmailService.Scope.GmailReadonly,
GmailService.Scope.GmailModify,
GmailService.Scope.MailGoogleCom
};
string
ApplicationName = "GmailApi";
// string
pathToServiceAccount = "C:\\Users\\rrr\\Downloads\\client.googleusercontent.com.json";
string
pathToTockenStore = Path.Combine(Application.StartupPath,
"token_store");
UserCredential credential;
string
startTime = "9:30 AM";
string
endTime = "6:30 PM";
string
pathToServiceAccount = Path.Combine(Application.StartupPath, "client_.json");
if
(DateTime.Now >= DateTime.Parse(startTime) && System.DateTime.Now
<= DateTime.Parse(endTime))
{
WriteToFile("In -" + DateTime.Now.ToString());
getEmails();
}
async Task
getEmails()
{
try
{
WriteToFile("In Process-" + DateTime.Now.ToString());
//
UserCredential credential;
using (var stream = new FileStream(pathToServiceAccount, FileMode.Open,
FileAccess.Read))
{
credential =
GoogleWebAuthorizationBroker.AuthorizeAsync(
GoogleClientSecrets.FromStream(stream).Secrets,
// This OAuth 2.0 access scope allows for read-only access to the
authenticated
// user's account, but not other types of account access.
Scopevs,
"gmailprofilename",
CancellationToken.None,
new FileDataStore("token_store",true), new
LocalServerCodeReceiver()
).Result;
}
var gmailService = new GmailService(new BaseClientService.Initializer()
{
HttpClientInitializer = credential,
ApplicationName = "GmailApi"
});
var emailListRequest = gmailService.Users.Messages.List("me");
emailListRequest.LabelIds = "INBOX";
emailListRequest.IncludeSpamTrash = false;
emailListRequest.Q = "has:attachment newer_than:2d (filename:xls OR
filename:xlsx OR filename:csv) (from:vv@gmail.com OR from:vvv@gmail.com)";
//
Get our emails
var emailListResponse =
emailListRequest.Execute();
if
(emailListResponse != null && emailListResponse.Messages != null)
{
// Loop through each email and get what fields you want...
foreach (var email in emailListResponse.Messages)
{
WriteToFile("Fetch Process-" + DateTime.Now.ToString());
var emailInfoRequest = gmailService.Users.Messages.Get("me",
email.Id);
//var emailInfoRequest =
gmailService.Users.Messages.Get("donotreply@angelbroking.com",
email.Id);
// Make another request for that email id...
var emailInfoResponse =
emailInfoRequest.Execute();
if (emailInfoResponse != null)
{
String from =
"";
String date =
"";
String subject =
"";
String body =
"";
// Loop through
the headers and get the fields we need...
foreach (var mParts
in emailInfoResponse.Payload.Headers)
{
if
(mParts.Name == "Date" || mParts.Name == "From" ||
mParts.Name == "Subject")
{
if (mParts.Name
== "Date")
{
date = mParts.Value;
}
else if
(mParts.Name == "From")
{
from = mParts.Value;
}
else if (mParts.Name ==
"Subject")
{
subject = mParts.Value;
}
var
stockist = dataTable.AsEnumerable()
//.Where(d =>
d.Field<string>("mail") == from)
//.Where(d =>
d.Field<string>("mail")?.Contains(from) == true)
.Where(d =>
from.Contains(d.Field<string>("mail")) == true)
.FirstOrDefault();
if (date != "" &&
from != "" && stockist != null && subject !=
"" &&
subject.Contains(stockist["subject"].ToString()))
{
var
validExtensions = new[] { ".xls", ".xlsx", ".csv"
};
if
(emailInfoResponse.Payload.Parts != null)
{
foreach (var part in emailInfoResponse.Payload.Parts)
{
if (!string.IsNullOrEmpty(part.Filename) &&
part.Body?.AttachmentId != null)
{
var ext = Path.GetExtension(part.Filename).ToLowerInvariant();
if (validExtensions.Contains(ext))
{
var attachPart =
gmailService.Users.Messages.Attachments.Get("me", email.Id,
part.Body.AttachmentId).Execute();
//
Decode and save attachment
byte[] attachmentData = Convert.FromBase64String(
attachPart.Data.Replace('-', '+').Replace('_', '/')
.PadRight((attachPart.Data.Length + 3) / 4 * 4, '='));
string filename = stockist["shortname"].ToString() +
part.Filename;
WriteToFile(filename + "time -" + DateTime.Now.ToString());
if (filename.EndsWith(".xls", StringComparison.OrdinalIgnoreCase))
{
//filename = Path.GetFileNameWithoutExtension(filename) +
".xlsx";
string filePath
= Path.Combine(uplaodxlsDirectory + stockist["name"].ToString() +
"\\", filename);
File.WriteAllBytes(filePath, attachmentData);
var
excelApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook workbook = null;
excelApp.DisplayAlerts
= false;
workbook = excelApp.Workbooks.Open(filePath);
var worksheet =
(Microsoft.Office.Interop.Excel.Worksheet)workbook.Sheets[1];
var usedRange = worksheet.UsedRange;
int rows = usedRange.Rows.Count;
int cols = usedRange.Columns.Count;
// Loop through all cells and replace commas with colons in text content
for (int i = 1; i <= rows; i++)
{
for (int j = 1; j <= cols; j++)
{
var cell = usedRange.Cells[i, j];
if (cell.Value2 != null && cell.Value2 is string)
{
string text = cell.Value2.ToString();
text
= text.Replace(",", ":");
// Replace comma with colon
cell.Value2 = text;
}
}
}
// Save as CSV
string
cpath= Path.ChangeExtension(filePath, ".csv");
//string csvPath = Path.Combine(uplaodDirectory +
stockist["name"].ToString() + "\\", filename);
workbook.SaveAs(
cpath,
Microsoft.Office.Interop.Excel.XlFileFormat.xlCSV,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing
);
workbook.Close(false);
excelApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
File.Delete(filePath); // Optionally delete the original XLS
}
else
{
string filePath = Path.Combine(uplaodDirectory +
stockist["name"].ToString() + "\\", filename);
File.WriteAllBytes(filePath, attachmentData);
}
//string filePath = Path.Combine("D:\\Attachments",
part.Filename);
var mods = new ModifyMessageRequest
{
RemoveLabelIds = new List<string> { "UNREAD" }
};
gmailService.Users.Messages.Modify(mods,
"me", email.Id).Execute();
}
}
}
}
//
Now you have the data you want...
}
}
}
}
}
}
}
catch
(Exception ex)
{
WriteToFile("error -"+ex.Message+" --
"+DateTime.Now.ToString()+"InnerExc -- "+ex.InnerException
!=null?ex.InnerException.Message.ToString():"");
}
}
void
ConvertXlsToXlsx(string xlsPath, string outputDirectory = null)
{
//if
(!File.Exists(xlsPath))
//{
// Console.WriteLine("❌
File does not exist.");
// return;
//}
string
xlsxPath = Path.ChangeExtension(xlsPath, ".csv");
if
(!string.IsNullOrEmpty(outputDirectory))
{
string fileName = Path.GetFileNameWithoutExtension(xlsPath) +
".csv";
xlsxPath = Path.Combine(outputDirectory, fileName);
}
var
excelApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook workbook = null;
try
{
excelApp.DisplayAlerts = false;
workbook = excelApp.Workbooks.Open(xlsPath);
string newFileName = Path.Combine(outputDirectory,
Path.GetFileNameWithoutExtension(xlsPath) + ".csv");
//
workbook.SaveAs("D:\\Attachments\\p.xlsx",
Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbook);
workbook.SaveAs(
newFileName,
Microsoft.Office.Interop.Excel.XlFileFormat.xlCSV,
// File format for .xlsx
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing);
}
catch(Exception ex)
{
Console.WriteLine($"✅ Converted to:
{ex.Message}");
}
finally
{
workbook?.Close(false);
excelApp.Quit();
File.Delete(xlsPath);
//
Cleanup COM objects
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
}
}
string
DecodeBase64String(string base64Url)
{
string
base64 = base64Url.Replace('-', '+').Replace('_', '/');
int
padding = 4 - (base64.Length % 4);
if
(padding < 4) base64 = base64.PadRight(base64.Length + padding, '=');
byte[]
decodedBytes = Convert.FromBase64String(base64);
return
Encoding.UTF8.GetString(decodedBytes);
}
String
getNestedParts(IList<MessagePart> part, string curr)
{
string
str = curr;
if
(part == null)
{
return str;
}
else
{
foreach (var parts in part)
{
if (parts.Parts == null)
{
if (parts.Body != null && parts.Body.Data != null)
{
str +=
parts.Body.Data;
}
}
else
{
return getNestedParts(parts.Parts, str);
}
}
return str;
}
}
Sign up here with your email
ConversionConversion EmoticonEmoticon