public FlowControll(ConfigXmlIO MyXml, logManager alogManager)
{
MyLogManager = alogManager;
MyEquipmentManage = new EquipmentManage(alogManager);
MyEquipList = new EquipmentList();
MyTestModelList = new TestModelList();
MyTestModelManage = new TestModelManage();
EquipmenNameArray = new SortedList <string, string>();
// EquipmenNameArray = GetEquipmentNameList(TestPlanId);
if (MyXml.DatabaseType.ToUpper() == "LOCATIONDATABASE")
{
MyDataio = new LocalDatabase(MyXml.DatabasePath);
}
else//SqlDatabase
{
MyXml.DatabasePath = @"INPCSZ0518\ATS_HOME";
MyDataio = new SqlDatabase(MyXml.DatabasePath);
}
dtCurrentTestModelTestData = new DataTable();
dtCurrentConditionResultData = new DataTable();
dtCurrentTestModelList = new DataTable();
dtCurrentTestModeParameterlList = new DataTable();
//-----------------------------------
TotalTestData = new DataTable();
DtMyConditionDataTable = new DataTable();
DtMyTestModelParameter = new DataTable();
DtMyDutInf = new DataTable();
}
public TestModelList GetList()
{
var result = new TestModelList();
var item1 = new TestModel();
item1.id = "00001";
item1.username = "******";
item1.name = "กิตติศักดิ์";
item1.surname = "จิตรเพียร";
item1.date = new DateTime(1995, 10, 04, 0, 0, 0);
var item2 = new TestModel();
item2.id = "00002";
item2.username = "******";
item2.name = "พัสกร";
item2.surname = "สังสัมฤทธิ์";
item2.date = new DateTime(1995, 10, 11, 0, 0, 0);
var item3 = new TestModel();
item3.id = "00003";
item3.username = "******";
item3.name = "ปนัดดา";
item3.surname = "ฮ่อคำ";
item3.date = new DateTime(1995, 10, 12, 0, 0, 0);
var item4 = new TestModel();
item4.id = "00004";
item4.username = "******";
item4.name = "บอย";
item4.surname = "ตั้งใจเรียน";
item4.date = new DateTime(1995, 10, 03, 0, 0, 0);
var item5 = new TestModel();
item5.id = "00005";
item5.username = "******";
item5.name = "สมชาย";
item5.surname = "ตั้แมนมนุษย์";
item5.date = new DateTime(1995, 10, 25, 0, 0, 0);
var item6 = new TestModel();
item6.id = "00006";
item6.username = "******";
item6.name = "ภาณุพงษ์";
item6.surname = "แจ่มแจ้ง";
item6.date = new DateTime(1995, 10, 25, 0, 0, 0);
result.Datatable = new List <TestModel>();
result.Datatable.Add(item1);
result.Datatable.Add(item2);
result.Datatable.Add(item3);
result.Datatable.Add(item4);
result.Datatable.Add(item5);
result.Datatable.Add(item6);
return(result);
}
public static void CreateExcelFile(string OutPutFileDirectory)
{
TestModelList data = TestModelData.GetList();
var fileFullname = Path.Combine(OutPutFileDirectory, $"output_{GetTimestamp()}.xlsx");
using (SpreadsheetDocument package = SpreadsheetDocument.Create(fileFullname, SpreadsheetDocumentType.Workbook))
{
CreatePartsForExcel(package, data);
}
}
private void datiProva(TestModelList list)
{
for (int i = 0, y = 0; i < 4; i++, y--)
{
TestModel tm = new TestModel();
tm.TestId = i + 1;
tm.TestName = $"Test{i + 1}";
tm.TestDesc = $"Tested {i + 1} time";
tm.TestDate = DateTime.Now.AddDays(y);
list.testData.Add(tm);
}
}
static private SheetData GenerateSheetdataForDetails(TestModelList data)
{
SheetData sheetData1 = new SheetData();
sheetData1.Append(CreateHeaderRowForExcel());
foreach (TestModel testmodel in data.testData)
{
Row partsRows = GenerateRowForChildPartDetail(testmodel);
sheetData1.Append(partsRows);
}
return(sheetData1);
}
static private void CreatePartsForExcel(SpreadsheetDocument document, TestModelList 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);
}
public void CreateExcelFile(TestModelList data, string OutPutFileDirectory)
{
var datetime = DateTime.Now.ToString().Replace("/", "_").Replace(":", "_");
string fileFullname = Path.Combine(OutPutFileDirectory, "Output.xlsx");
if (File.Exists(fileFullname))
{
fileFullname = Path.Combine(OutPutFileDirectory, "Output_" + datetime + ".xlsx");
}
using (SpreadsheetDocument package = SpreadsheetDocument.Create(fileFullname, SpreadsheetDocumentType.Workbook))
{
CreatePartsForExcel(package, data);
}
}
static public MemoryStream CreateExcelFile(TestModelList data, string OutPutFileDirectory)
{
var datetime = DateTime.Now.ToString().Replace("/", "_").Replace(":", "_");
string fileFullname = String.Empty;
fileFullname = Path.Combine(OutPutFileDirectory, "Output_" + 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 ActionResult Test()
{
var lst = new List <TestModel> {
new TestModel {
Name = "李軍", Birthday = DateTime.Parse("1983/11/21"), Age = 30, Address = ""
},
new TestModel {
Name = "鴎外", Birthday = DateTime.Parse("1982/11/21"), Age = 30, Address = ""
},
new TestModel {
Name = "竜間", Birthday = DateTime.Parse("1981/11/21"), Age = 30, Address = ""
},
new TestModel {
Name = "生簀", Birthday = DateTime.Parse("1980/11/21"), Age = 30, Address = ""
}
};
var mdllist = new TestModelList();
mdllist.Items = lst;
return(View(mdllist));
}
private void button1_Click(object sender, EventArgs e)
{
TestModelList tmList = new TestModelList();
tmList.testData = new List <TestModel>();
TestModel tm = new TestModel();
tm.TestId = 1;
tm.TestName = "Test1";
tm.TestDesc = "Tested 1 time";
tm.TestDate = DateTime.Now.Date;
tmList.testData.Add(tm);
TestModel tm1 = new TestModel();
tm1.TestId = 2;
tm1.TestName = "Test2";
tm1.TestDesc = "Tested 2 times";
tm1.TestDate = DateTime.Now.AddDays(-1);
tmList.testData.Add(tm1);
TestModel tm2 = new TestModel();
tm2.TestId = 3;
tm2.TestName = "Test3";
tm2.TestDesc = "Tested 3 times";
tm2.TestDate = DateTime.Now.AddDays(-2);
tmList.testData.Add(tm2);
TestModel tm3 = new TestModel();
tm3.TestId = 4;
tm3.TestName = "Test4";
tm3.TestDesc = "Tested 4 times";
tm3.TestDate = DateTime.Now.AddDays(-3);
tmList.testData.Add(tm);
ClsOpenXmlExcel.CreateExcelFile(tmList, "d:\\", "Calibri", true);
}
private void btnExcel_Click(object sender, EventArgs e)
{
string completePath = OpenXMLUtilities_common.createPath("xlsx");
TestModelList item = new TestModelList();
item.testData = new List <TestModel>();
datiProva(item);
try
{
using (SpreadsheetDocument package = SpreadsheetDocument.Create(completePath, SpreadsheetDocumentType.Workbook))
{
OpenXMLUtilities_excel.CreatePartsForExcel(package, item);
MessageBox.Show("File creato correttamente.");
Process.Start(completePath);
}
}
catch (Exception)
{
MessageBox.Show("Problemi con la creazione del file. Chiuderlo e riprovare o riavviare l'applicazione.");
}
}
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");
}
static void teste()
{
// Create a spreadsheet document by supplying the file name.
SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.
Create(@"C:\Code\teste.xlsx", SpreadsheetDocumentType.Workbook);
// Add a WorkbookPart to the document.
WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
// Add a WorksheetPart to the WorkbookPart.
WorksheetPart worksheetPart = workbookpart.AddNewPart <WorksheetPart>();
worksheetPart.Worksheet = new Worksheet(new SheetData());
// Add Sheets to the Workbook.
Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.
AppendChild <Sheets>(new Sheets());
// Append a new worksheet and associate it with the workbook.
Sheet sheet = new Sheet()
{
Id = spreadsheetDocument.WorkbookPart.
GetIdOfPart(worksheetPart),
SheetId = 1,
Name = "mySheet"
};
sheets.Append(sheet);
// Close the document.
spreadsheetDocument.Close();
Console.WriteLine("The spreadsheet document has been created.\nPress a key.");
Console.ReadKey();
TestModelList tmList = new TestModelList();
tmList.TestData = new List <TestModel>();
TestModel tm = new TestModel();
tm.TestId = 1;
tm.TestName = "Test1";
tm.TestDesc = "Tested 1 time";
tm.TestDate = DateTime.Now.Date;
tmList.TestData.Add(tm);
TestModel tm1 = new TestModel();
tm1.TestId = 2;
tm1.TestName = "Test2";
tm1.TestDesc = "Tested 2 times";
tm1.TestDate = DateTime.Now.AddDays(-1);
tmList.TestData.Add(tm1);
TestModel tm2 = new TestModel();
tm2.TestId = 3;
tm2.TestName = "Test3";
tm2.TestDesc = "Tested 3 times";
tm2.TestDate = DateTime.Now.AddDays(-2);
tmList.TestData.Add(tm2);
TestModel tm3 = new TestModel();
tm3.TestId = 4;
tm3.TestName = "Test4";
tm3.TestDesc = "Tested 4 times";
tm3.TestDate = DateTime.Now.AddDays(-3);
tmList.TestData.Add(tm);
var p = new CreateExcel();
p.CreateExcelFile(tmList, @"C:\Code");
}
