/// <summary>
/// Make EA Excel File from EA SQLQuery format (string)
/// </summary>
/// <param name="x"></param>
/// <param name="fileName"></param>
/// <returns>string</returns>
public static bool MakeExcelFileFromSqlResult(string x, string fileName = @"d:\temp\sql\sql.xlsx")
{
if (string.IsNullOrEmpty(x))
{
return(false);
}
XDocument xDoc = XDocument.Parse(x);
// get field names
var fieldNames = xDoc.Descendants("Row").FirstOrDefault()?.Descendants();
if (fieldNames == null)
{
return(false);
}
DataTable dt = new DataTable();
HoUtil.TryToDeleteFile(fileName);
Cursor.Current = Cursors.WaitCursor;
try
{
foreach (var field in fieldNames)
{
dt.Columns.Add(field.Name.ToString(), typeof(string));
}
var xRows = xDoc.Descendants("Row");
foreach (var xRow in xRows)
{
DataRow row = dt.NewRow();
int column = 0;
foreach (var value in xRow.Elements())
{
row[column] = value.Value;
column = column + 1;
}
dt.Rows.Add(row);
}
if (!SaveTableToExcel(ref fileName, dt))
{
return(false);
}
return(true);
}
catch (Exception e)
{
Cursor.Current = Cursors.Default;
MessageBox.Show($"Error {e.Message}\r\n{e.Source}");
return(false);
}
}