static private SheetData GenerateSheetdataForDetails(SummaryModelList data)
{
SheetData sheetData1 = new SheetData();
sheetData1.Append(CreateHeaderRowForExcelS());
foreach (SummaryModel testmodel in data.testData)
{
Row partsRows = GenerateRowForChildPartDetail(testmodel);
sheetData1.Append(partsRows);
}
return(sheetData1);
}
static private void CreatePartsForExcel(SpreadsheetDocument document, SummaryModelList data)
{
SheetData partSheetData = GenerateSheetdataForDetails(data);
WorkbookPart workbookPart1 = document.AddWorkbookPart();
GenerateWorkbookPartContent(workbookPart1);
WorkbookStylesPart workbookStylesPart1 = workbookPart1.AddNewPart <WorkbookStylesPart>("rId3");
GenerateWorkbookStylesPartContent(workbookStylesPart1);
WorksheetPart worksheetPart1 = workbookPart1.AddNewPart <WorksheetPart>("rId1");
GenerateWorksheetPartContent(worksheetPart1, partSheetData);
}
static public MemoryStream CreateExcelFile(SummaryModelList data, string OutPutFileDirectory)
{
var datetime = DateTime.Now.ToString().Replace("/", "_").Replace(":", "_");
string fileFullname = String.Empty;
fileFullname = Path.Combine(OutPutFileDirectory, "Outputz_" + datetime + ".xlsx");
using (SpreadsheetDocument package = SpreadsheetDocument.Create(fileFullname, SpreadsheetDocumentType.Workbook))
{
CreatePartsForExcel(package, data);
}
MemoryStream ms = new MemoryStream();
using (FileStream file = new FileStream(fileFullname, FileMode.Open, FileAccess.Read))
file.CopyTo(ms);
File.Delete(fileFullname);
return(ms);
}
public static async Task <IActionResult> Run(
[HttpTrigger(AuthorizationLevel.Function, "get", "post", Route = null)] HttpRequest req,
ILogger log)
{
log.LogInformation("C# HTTP trigger function processed a request.");
string result;
int counter = 0;
reportType = req.Headers["reporttype"];
orderby = req.Headers["orderby"];
// log.LogInformation(orderby + "*************************");
if (String.IsNullOrEmpty(reportType))
{
reportType = "A";
}
if (String.IsNullOrEmpty(orderby))
{
orderby = "";
}
result = String.Empty;
if (reportType != "S")
{
TestModelList tmList = new TestModelList();
tmList.testData = new List <TestModel>();
TestModel tm;
try
{
using (SqlConnection conn = new SqlConnection(System.Environment.GetEnvironmentVariable("SQLDB")))
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.CommandText = "sp_CodeCdt_StaffList";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@order", orderby);
cmd.Connection = conn;
conn.Open();
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
tm = new TestModel();
if (reader.IsDBNull(0))
{
tm.EmployeeId = "";
}
else
{
tm.EmployeeId = reader.GetString(0);
}
if (tm.EmployeeId.StartsWith('0'))
{
tm.EmployeeId = "'" + tm.EmployeeId;
}
if (reader.IsDBNull(1))
{
tm.StaffNo = "";
}
else
{
tm.StaffNo = reader.GetString(1);
}
if (reader.IsDBNull(2))
{
tm.DisplayName = "";
}
else
{
tm.DisplayName = reader.GetString(2);
}
if (reader.IsDBNull(3))
{
tm.IsConflictOfInterest = "";
}
else
{
tm.IsConflictOfInterest = reader.GetBoolean(3).ToString();
}
if (reader.IsDBNull(4))
{
tm.signed_Date = "";
}
else
{
tm.signed_Date = reader.GetString(4);
}
if (reader.IsDBNull(5))
{
tm.Company = "";
}
else
{
tm.Company = reader.GetString(5);
}
if (reader.IsDBNull(6))
{
tm.BusinessUnit = "";
}
else
{
tm.BusinessUnit = reader.GetString(6);
}
if (reader.IsDBNull(7))
{
tm.DivisionName = "";
}
else
{
tm.DivisionName = reader.GetString(7);
}
if (reader.IsDBNull(8))
{
tm.DepartmentName = "";
}
else
{
tm.DepartmentName = reader.GetString(8);
}
if (reader.IsDBNull(9))
{
tm.SectionName = "";
}
else
{
tm.SectionName = reader.GetString(9);
}
if (reader.IsDBNull(10))
{
tm.LastUpdateDate = "";
}
else
{
tm.LastUpdateDate = reader.GetString(10); //.ToString("yyyy-MM-dd", CultureInfo.InvariantCulture);
}
if (reader.IsDBNull(14))
{
tm.OriginalFileName = "";
}
else
{
tm.OriginalFileName = reader.GetString(14);
}
if (reader.IsDBNull(15))
{
tm.HRStatus = "Invalid";
}
else
{
if (reader.GetBoolean(15) == true)
{
tm.HRStatus = "Reviewed";
}
else
{
tm.HRStatus = "Reviewing";
}
}
tmList.testData.Add(tm);
counter++;
}
}
}
}
MemoryStream ms = CreateExcelFile(tmList, @"D:\local\Temp");
byte[] data = ms.ToArray();
return(new FileContentResult(data, "application/octet-stream"));
//data = File.ReadAllBytes(file.toPath());
}
catch (Exception ex)
{
log.LogError(ex.Message);
return(new OkObjectResult(ex.Message));
}
//string requestBody = await new StreamReader(req.Body).ReadToEndAsync();
// parse query parameter
}
else
{
log.LogInformation("***********Summary*****************");
SummaryModelList smList = new SummaryModelList();
smList.testData = new List <SummaryModel>();
SummaryModel sm;
try
{
log.LogInformation(yeartermId.ToString());
using (SqlConnection conn = new SqlConnection(System.Environment.GetEnvironmentVariable("SQLDB")))
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.CommandText = "sp_CodeCdt_Summary";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@orderby", orderby);
cmd.Connection = conn;
conn.Open();
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
sm = new SummaryModel();
if (reader.IsDBNull(0))
{
sm.BusinessUnit = String.Empty;
}
else
{
sm.BusinessUnit = reader.GetString(0);
}
if (reader.IsDBNull(1))
{
sm.Division = String.Empty;
}
else
{
sm.Division = reader.GetString(1);
}
if (reader.IsDBNull(2))
{
sm.Company = String.Empty;
}
else
{
sm.Company = reader.GetString(2);
}
if (reader.IsDBNull(3))
{
sm.Department = String.Empty;
}
else
{
sm.Department = reader.GetString(3);
}
sm.Signed = reader.GetInt32(4).ToString();
sm.NotSigned = reader.GetInt32(5).ToString();
sm.Reviewing = reader.GetInt32(6).ToString();
sm.Reviewed = reader.GetInt32(7).ToString();
sm.Total = (reader.GetInt32(4) + reader.GetInt32(5) + reader.GetInt32(6) + reader.GetInt32(7)).ToString();
smList.testData.Add(sm);
counter++;
}
}
}
}
log.LogInformation("start generate excel---------");
MemoryStream ms = CreateExcelFile(smList, @"D:\local\Temp");
byte[] data = ms.ToArray();
return(new FileContentResult(data, "application/octet-stream"));
}
catch (Exception ex)
{
log.LogError(ex.Message);
return(new OkObjectResult(ex.Message));
}
}
//return new OkObjectResult("Import Success");
}
