private static void ReadExcelFileForSqlScript()
{
AGEMKOContext _context = new AGEMKOContext();
string filePath = System.AppDomain.CurrentDomain.BaseDirectory;
filePath = Path.Combine(filePath, "NewReceivedExcel.xlsx");
DataSet result = new DataSet();
if (File.Exists(filePath))
{
FileStream stream = File.Open(filePath, FileMode.Open, FileAccess.Read);
Encoding.RegisterProvider(CodePagesEncodingProvider.Instance);
//1. Reading from a binary Excel file ('97-2003 format; *.xls)
//IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
//...
//2. Reading from a OpenXml Excel file (2007 format; *.xlsx)
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
//...
//3. DataSet - The result of each spreadsheet will be created in the result.Tables
result = excelReader.AsDataSet();
//...
//4. DataSet - Create column names from first row
//5. Data Reader methods
//6. Free resources (IExcelDataReader is IDisposable)
excelReader.Close();
foreach (DataRow row in result.Tables[0].Rows)
{
if (!row.IsNull(10))
{
MainTable record = new MainTable();
string katigoria = row.IsNull(1) ? string.Empty : row[1].ToString();
string katastasi = row.IsNull(3) ? string.Empty : row[3].ToString();
string afm = row.IsNull(5) ? string.Empty : row[5].ToString();
string epwnumia = row[6].ToString().Trim();
string diakritosTitlos = row.IsNull(7) ? string.Empty : row[7].ToString();
string drastiriotita = row.IsNull(9) ? string.Empty : row[9].ToString();
string address = row[10].ToString().Trim();
string email = row.IsNull(14) ? string.Empty : row[14].ToString();
DateTime year = row.IsNull(16) ? DateTime.Now : Convert.ToDateTime(row[16].ToString());
string telephone = row.IsNull(14) ? string.Empty : row[18].ToString().Replace(',', '|');
record.Catigoria = katigoria.Trim();
record.Katastasi = katastasi.Trim();;
record.Afm = afm.Trim();
record.Epwnumia = epwnumia.Trim();
record.DiakritosTitlos = diakritosTitlos.Trim();
record.Drastiriotita = drastiriotita.Trim();
record.Address = address.Trim();
record.Email = email.Trim();
record.Year = year;
record.Telephone = telephone;
_context.MainTable.Add(record);
_context.SaveChanges();
}
}
}
DataSet result2 = new DataSet();
string filePath2 = System.AppDomain.CurrentDomain.BaseDirectory;
filePath2 = Path.Combine(filePath2, "All.xlsx");
if (File.Exists(filePath2))
{
FileStream stream = File.Open(filePath2, FileMode.Open, FileAccess.Read);
//Encoding.RegisterProvider(CodePagesEncodingProvider.Instance);
//1. Reading from a binary Excel file ('97-2003 format; *.xls)
//IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
//...
//2. Reading from a OpenXml Excel file (2007 format; *.xlsx)
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
//...
//3. DataSet - The result of each spreadsheet will be created in the result.Tables
result2 = excelReader.AsDataSet();
//...
//4. DataSet - Create column names from first row
//5. Data Reader methods
//6. Free resources (IExcelDataReader is IDisposable)
excelReader.Close();
foreach (DataRow row in result2.Tables[0].Rows)
{
string addresskey = row[0].ToString();
string lat = row[1].ToString();
string longitude = row[2].ToString();
if (!row.IsNull(0))
{
MainTable record = _context.MainTable.FirstOrDefault(x => x.Address.Equals(row[0].ToString()));
if (record != default(MainTable))
{
record.Latitude = lat;
record.Longitude = longitude;
_context.SaveChanges();
}
else
{
if (!row.IsNull(5) && !row.IsNull(6))
{
record = _context.MainTable.FirstOrDefault(x => x.Address.Equals(row[6].ToString().Trim()) && x.DiakritosTitlos.Equals(row[5].ToString().Trim()));
if (record != default(MainTable))
{
record.Latitude = lat;
record.Longitude = longitude;
_context.SaveChanges();
}
}
}
}
}
}
}
private static void CreateScript()
{
AGEMKOContext _context = new AGEMKOContext();
/*
* 1= ΕΠΙΜΕΡΟΥΣ ΚΑΤΗΓΟΡΙΑ
* 6 = ΕΠΩΝΥΜΙΑ
* 7 = ΔΙΑΚΡΙΤΙΚΟΣ ΤΙΤΛΟΣ
* 10 = ΣΤΟΙΧΕΙΑ Δ/ΝΣΗΣ
* 14 = email
*
* Διακριτικό τίτλο
* Επωνυμία
* Κατηγορία
* Ετος ίδρυσης
* Στοιχεία επικοινωνίας (δ/νση, τηλ, email)
*/
StringBuilder sbwpgmza = new StringBuilder();
StringBuilder sbemail = new StringBuilder();
StringBuilder sbyear = new StringBuilder();
StringBuilder sbtel = new StringBuilder();
List <MainTable> listOfRecords = _context.MainTable.ToList();
foreach (MainTable record in listOfRecords)
{
//style='list-style: none'
string description = string.Format("<div><ul><li><b>Δ.ΤΙΤΛΟΣ:</b> {0}</li><li><b>ΔΡΑΣΤΗΡΙΟΤΗΤΑ:</b> {1}</li><li><b>ΕΙΔΟΣ:</b> {2}</li></ul></div>", record.DiakritosTitlos.Replace("'", "''"), record.Drastiriotita.Replace("'", "''"), record.Catigoria.Replace("'", "''"));
//var output = Regex.Replace(record.Address.Replace("'", "''"), @"[\d]{3,7}", string.Empty);
var output = Regex.Replace(record.Address.Replace("'", "''"), @"[\d]", string.Empty);
string query = string.Format(@"
SET @g = 'POINT({0} {1})';
INSERT INTO `wpct_3_wpgmza`(`id`, `map_id`, `address`, `description`, `pic`, `link`, `icon`, `lat`, `lng`, `anim`, `title`, `infoopen`, `category`, `approved`, `retina`, `type`, `did`, `other_data`, `latlng`) VALUES
({2}, 1, '{3}', '{4}', '', '', '', '{0}', '{1}', '0', '{5}', '0', '0', 1, 0, 0, '', '{6}', ST_PointFromText(@g));
"
, !string.IsNullOrWhiteSpace(record.Latitude) ? record.Latitude.Replace(',', '.') : string.Empty
, !string.IsNullOrWhiteSpace(record.Longitude) ? record.Longitude.Replace(',', '.') : string.Empty
, record.Id
, output //record.Address.Replace("'", "''")
, description
, record.Epwnumia.Replace("'", "''")
, "a:1:{i:0;s:1:''0'';}");
sbwpgmza.Append(query);
sbwpgmza.AppendLine();
if (!string.IsNullOrWhiteSpace(record.Email))
{
string emailInsert = string.Format("INSERT INTO `wpct_3_wpgmza_markers_has_custom_fields`(`field_id`, `object_id`, `value`) VALUES (2,{0},'{1}');", record.Id, record.Email);
sbemail.AppendLine(emailInsert);
}
if (!string.IsNullOrWhiteSpace(record.Email))
{
string yearInsert = string.Format("INSERT INTO `wpct_3_wpgmza_markers_has_custom_fields`(`field_id`, `object_id`, `value`) VALUES (1,{0},'{1}');", record.Id, record.Year.Value.Year);
sbyear.AppendLine(yearInsert);
}
if (!string.IsNullOrWhiteSpace(record.Telephone))
{
string tel = string.Format("INSERT INTO `wpct_3_wpgmza_markers_has_custom_fields`(`field_id`, `object_id`, `value`) VALUES (3,{0},'{1}');", record.Id, record.Telephone.Trim());
sbtel.AppendLine(tel);
}
}
sbwpgmza.Append(sbemail);
sbwpgmza.Append(sbyear);
sbwpgmza.Append(sbtel);
using (StreamWriter writer = new StreamWriter($"FinalSqlScript{DateTime.Now.ToString("dd_MM_yyyy_hh_mm_ss")}.txt"))
{
writer.Write(sbwpgmza.ToString());
}
}