static void Main(string[] args)
{
string _strInFileName = "Book1.csv";
if (File.Exists(_strInFileName))
{
string[] _strCompnayNames = File.ReadAllLines(_strInFileName);
List <WastageValues> _lst = new List <WastageValues>();
Dictionary <string, Dictionary <int, WastageValues> > _dic1 = new Dictionary <string, Dictionary <int, WastageValues> >();
int _i = 1;
foreach (string _strCompnayName in _strCompnayNames)
{
string _strBaseLink = $"http://ec2-52-202-150-226.compute-1.amazonaws.com/tri/tri.php?database=tri&reptype=f&reporting_year=2014&first_year_range=1987&last_year_range=2014&facility_name=&parent={_strCompnayName.Replace(" ", "+")}&combined_name=&parent_duns=&facility_id=&city=&county=&state=&zip=&district=&naics=&primall=&chemcat=&corechem=n&casno=&casno2=&chemname=&detail=-1&datype=X&rsei=y&sortp=D";
HttpWebRequest req = WebRequest.Create(_strBaseLink) as HttpWebRequest;
req.Accept = "*/*";
req.Host = "ec2-52-202-150-226.compute-1.amazonaws.com";
req.UserAgent = "Mozilla/5.0 (Windows NT 10.0; WOW64; rv:52.0) Gecko/20100101 Firefox/52.0";
req.Method = "GET";
req.KeepAlive = true;
string strResult = string.Empty;
using (HttpWebResponse res = req.GetResponse() as HttpWebResponse)
{
using (Stream s = res.GetResponseStream())
{
using (StreamReader sr = new StreamReader(s))
{
strResult = sr.ReadToEnd().ToString().Trim();
if (strResult.Contains("<error>No records found</error>"))
{
continue;
}
Dictionary <int, WastageValues> _dic = new Dictionary <int, WastageValues>();
XmlSerializer ser = new XmlSerializer(typeof(RTKNetSearchResults));
using (TextReader stream = new StringReader(strResult))
{
RTKNetSearchResults _searchResult = (RTKNetSearchResults)ser.Deserialize(stream);
if (_searchResult == null)
{
throw new NullReferenceException("Invalid xml format");
}
bool _bHasData = false;
foreach (ReportingYear _reportingYear in _searchResult.Data.Record.ReleaseTrend.ReportingYears)
{
_bHasData = true;
WastageValues _values = null;
_dic.TryGetValue(_reportingYear.Year, out _values);
if (_values == null)
{
_values = new WastageValues();
}
_values.Release = _reportingYear.Value;
_dic[_reportingYear.Year] = _values;
}
foreach (ReportingYear _reportingYear in _searchResult.Data.Record.WasteTrend.ReportingYears)
{
_bHasData = true;
WastageValues _values = null;
_dic.TryGetValue(_reportingYear.Year, out _values);
if (_values == null)
{
_values = new WastageValues();
}
_values.Waste = _reportingYear.Value;
_dic[_reportingYear.Year] = _values;
}
foreach (ReportingYear _reportingYear in _searchResult.Data.Record.RseiScoreTrend.ReportingYears)
{
_bHasData = true;
WastageValues _values = null;
_dic.TryGetValue(_reportingYear.Year, out _values);
if (_values == null)
{
_values = new WastageValues();
}
_values.Score = _reportingYear.Value;
_dic[_reportingYear.Year] = _values;
}
if (_bHasData)
{
_dic1.Add(_strCompnayName, _dic);
Console.WriteLine($"{_i}. {_strCompnayName} pulled!");
_i++;
}
}
}
}
}
}
if (_dic1.Count > 0)
{
strOutfileName = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, strOutfileName);
Dictionary <int, int> _dicYear = CreateExcel();
UpdateExcel(_dicYear, _dic1);
}
else
{
Console.WriteLine("No data to process");
}
}
else
{
Console.WriteLine($"Input CSV file {_strInFileName}for company names not found");
}
}
private static bool UpdateExcel(Dictionary <int, int> dicYear, Dictionary <string, Dictionary <int, WastageValues> > _dic1)
{
bool bRtnVal = false;
Workbook workBook = null;
Application excelApp = null;
Worksheet workSheet = null;
try
{
excelApp = new Application();
Workbooks workBooks = excelApp.Workbooks;
workBook = excelApp.Workbooks.Open(strOutfileName, Type.Missing, false, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
workSheet = workBook.Worksheets[1];
int nColumns = workSheet.UsedRange.Columns.Count;
int nRows = workSheet.UsedRange.Rows.Count;
int _nCurrentRow = 3;
int _i = 1;
foreach (KeyValuePair <string, Dictionary <int, WastageValues> > kvp in _dic1)
{
workSheet.Cells[_nCurrentRow, 1] = kvp.Key;
int i = 2;
foreach (KeyValuePair <int, WastageValues> kvp1 in kvp.Value)
{
i = dicYear[kvp1.Key];
WastageValues _value = kvp1.Value;
workSheet.Cells[_nCurrentRow, i] = _value.Release;
workSheet.Cells[_nCurrentRow, ++i] = _value.Waste;
workSheet.Cells[_nCurrentRow, ++i] = _value.Score;
i++;
}
Console.WriteLine($"{_i}. {kvp.Key} pushed!");
_i++;
_nCurrentRow++;
}
//To aut
workSheet.Columns.AutoFit();
excelApp.DisplayAlerts = false;
workBook.Save();
workBook.Close(0);
excelApp.Quit();
Console.WriteLine($"Excel file update completed at '{DateTime.Now}'");
bRtnVal = true;
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
finally
{
if (workSheet != null)
{
Marshal.ReleaseComObject(workSheet);
}
if (workBook != null)
{
Marshal.ReleaseComObject(workBook);
}
if (excelApp != null)
{
Marshal.ReleaseComObject(excelApp);
}
}
return(bRtnVal);
}
