Excel formula and tricks

generate datetime format

=YEAR(C2) & "-" & RIGHT("0" & MONTH(C2),2) & "-" & RIGHT("0" & DAY(C2),2) & " " & HOUR(C2) & ":" & RIGHT("0" & MINUTE(C2),2)

 

=YEAR(E2) & "-"&TEXT(MONTH(E2),"00") & "-" & TEXT(DAY(E2),"00") &" "&TEXT(HOUR(E2),"00") &":"&TEXT(MINUTE(E2),"00") &":"&TEXT(SECOND(E2),"00") &".000"

 

generate Json update SQL query

="update [surveyentry_" & A2 & "] set SurveyData = json_modify(SurveyData, '$." & C2 & "', '" & E2 & "') where SurveyEntryGuid = '" & B2 & "'"

 

c#

hide rows

report.Worksheets["Week key metrics"].Cells.HideRows(8 + mainData[2].Data.Count, 12 - mainData[2].Data.Count);

 

run sql in c#

public static readonly IList<DataQuery> preQueries = new List<DataQuery>
{
new DataQuery()
{
DataStoreGuid = "62c07a47-3809-41d2-87ef-a474eec1b630",
SqlQuery = @"declare @EndOfPeriod datetime

select top 1 @EndOfPeriod = weekto from (
select min(u_weekfrom) weekfrom, max(u_weekto) weekto, u_periodid, u_periodname [@from] group by u_periodid, u_periodname
) a
where weekto < '" + SetDate + @"'
order by u_periodid desc

if @EndofPeriod < dateadd(week, -1, '" + SetDate + @"')
select 0
else
select 1"
}
};

 

 

export database into excel

 

SqlConnection SQLConnection = new SqlConnection();
SQLConnection.ConnectionString = GlobalHelper.DBConnectionString;

//Load Data into DataTable from by executing Stored Procedure
string queryString = GlobalHelper.SQLQuery;
SqlDataAdapter adapter = new SqlDataAdapter(queryString, SQLConnection);
DataSet ds = new DataSet();
adapter.Fill(ds);

logger.LogInformation($"Saving data into Excel.");

//store data table to excel
XLWorkbook workbook = new XLWorkbook();
for (int i = 0; i < ds.Tables.Count;i++)
{
logger.LogInformation($"Generating sheet " + (i + 1).ToString());

var sheet = workbook.Worksheets.Add(ds.Tables[i], GlobalHelper.Sheetname + (i+1).ToString());
foreach (var col in GlobalHelper.DatetimeColsToFormat)
{
sheet.Column(col).Style.DateFormat.Format = GlobalHelper.DatetimeFormat;
}
sheet.Row(1).Height = 18; //increase header height
sheet.Columns().AdjustToContents();

}

workbook.SaveAs(GlobalHelper.ExcelnamePath);