private void ExportInventoryInfor(string SavePath)
{
IExcelHelper excel = new AsposeCellsExcelHelper(System.Windows.Forms.Application.StartupPath + @"\Template\各部门库存情况.xlsx");
excel.InsertCell("Sheet1", 2, 1, EndDateTime.Value.ToString("yyyy-MM-dd") + "总库存");
excel.InsertCell("Sheet1", 2, 2, "入库≥9个月库存(" + EndDateTime.Value.AddMonths(-9).ToString("yyyy-MM-dd") + ")前入库");
excel.InsertCell("Sheet1", 2, 3, "入库≥12个月库存(" + EndDateTime.Value.AddMonths(-12).ToString("yyyy-MM-dd") + ")前入库");
excel.InsertCell("Sheet1", 2, 4, "入库≥18个月库存(" + EndDateTime.Value.AddMonths(-18).ToString("yyyy-MM-dd") + ")前入库");
excel.InsertCell("Sheet1", 2, 5, "入库≥24个月库存(" + EndDateTime.Value.AddMonths(-24).ToString("yyyy-MM-dd") + ")前入库");
excel.SaveExcel(SavePath);
}
private void ExportInventory(string SavePath)
{
//初始化数据库操作类
DataTable dt;
string sql = @"select * from [INVENTORY]";
//初始化excel操作类，新建Excel
IExcelHelper excel = new AsposeCellsExcelHelper(System.Windows.Forms.Application.StartupPath + @"\Template\material inventry report.xlsx");
//读取库存表
dt = odbc.ExecuteDataTable(sql);
excel.InsertDataTable("ALL", 8, dt, true);
excel.InsertFormula("ALL", dt.Rows.Count + 8, 6, "=SUM(G1:G" + (dt.Rows.Count + 7) + ")");
dt = odbc.ExecuteDataTable(sql + " where [Depart Desc]='Production Dept.'");
excel.InsertDataTable("Production Dept.", 8, dt, true);
//excel.InsertFormula("Production Dept.", dt.Rows.Count + 8, 6, "=SUM(G1:G" + (dt.Rows.Count + 7) + ")");
dt = odbc.ExecuteDataTable(sql + " where [Depart Desc]='Field Operation Dept.' or [Depart Desc]='Base camp'");
excel.InsertDataTable("Field Operation Dept.", 8, dt, true);
//excel.InsertFormula("Field Operation Dept.", dt.Rows.Count + 8, 6, "=SUM(G1:G" + (dt.Rows.Count + 7) + ")");
dt = odbc.ExecuteDataTable(sql + " where [Depart Desc]='Pipeline'");
excel.InsertDataTable("Pipeline", 8, dt, true);
//excel.InsertFormula("Pipeline", dt.Rows.Count + 8, 6, "=SUM(G1:G" + (dt.Rows.Count + 7) + ")");
dt = odbc.ExecuteDataTable(sql + " where [Depart Desc]='Development Dep.'");
excel.InsertDataTable("Development Dep.", 8, dt, true);
//excel.InsertFormula("Development Dep.", dt.Rows.Count + 8, 6, "=SUM(G1:G" + (dt.Rows.Count + 7) + ")");
dt = odbc.ExecuteDataTable(sql + " where [Depart Desc]='Operation Dept.'");
excel.InsertDataTable("Operation Dept.", 8, dt, true);
//excel.InsertFormula("Operation Dept.", dt.Rows.Count + 8, 6, "=SUM(G1:G" + (dt.Rows.Count + 7) + ")");
dt = odbc.ExecuteDataTable(sql + " where [Depart Desc]='Engineering & Construction'");
excel.InsertDataTable("Engineering & Construction", 8, dt, true);
//excel.InsertFormula("Engineering & Construction", dt.Rows.Count + 8, 6, "=SUM(G1:G" + (dt.Rows.Count + 7) + ")");
dt = odbc.ExecuteDataTable(sql + " where [Depart Desc]='HSE Dept.'");
excel.InsertDataTable("HSE Dept.", 8, dt, true);
//excel.InsertFormula("HSE Dept.", dt.Rows.Count + 8, 6, "=SUM(G1:G" + (dt.Rows.Count + 7) + ")");
dt = odbc.ExecuteDataTable(sql + " where [Depart Desc]='Administration Dept.'");
excel.InsertDataTable("Administration Dept.", 8, dt, true);
//excel.InsertFormula("Administration Dept.", dt.Rows.Count + 8, 6, "=SUM(G1:G" + (dt.Rows.Count + 7) + ")");
dt = odbc.ExecuteDataTable(sql + " where [Depart Desc]='P&L Dept.'");
excel.InsertDataTable("P&L Dept.", 8, dt, true);
//excel.InsertFormula("P&L Dept.", dt.Rows.Count + 8, 6, "=SUM(G1:G" + (dt.Rows.Count + 7) + ")");
excel.SaveExcel(SavePath);
}
private void ExportIssue(string SavePath)
{
//初始化数据库操作类
DataTable dt;
string sql = @"select * from [FLF_IssueReport] where [Issue Date]>='" + StartDateTime.Value.ToString("yyyy-MM-dd") + "' and [Issue Date]<='" + EndDateTime.Value.ToString("yyyy-MM-dd") + "'";
//初始化excel操作类，新建Excel
IExcelHelper excel = new AsposeCellsExcelHelper(System.Windows.Forms.Application.StartupPath + @"\Template\material issue report.xlsx");
//读取库存表
dt = odbc.ExecuteDataTable(sql);
if (dt.Rows.Count > 0)
{
excel.InsertDataTable("Sheet1", 8, dt, false);
excel.SaveExcel(SavePath);
}
}
private void SDTWH()
{
string sql = "";
IExcelHelper excel = new AsposeCellsExcelHelper(System.Windows.Forms.Application.StartupPath + @"\Template\SDT-WH.xlsx");
excel.InsertCell("Sheet1", 10, 0, StartDateTime.Value.ToString("yyyy-MM-dd"));
excel.InsertCell("Sheet1", 10, 4, EndDateTime.Value.ToString("yyyy-MM-dd"));
sql = @"SELECT dbo.WH_Receive.ReceiveNO AS Expr1 FROM
dbo.WH_Receive LEFT OUTER JOIN
dbo.WH_ReceiveMaterial ON dbo.WH_Receive.ReceiveID = dbo.WH_ReceiveMaterial.ReceiveID
WHERE(dbo.WH_ReceiveMaterial.MaterialName <> N'Diesel Fuel')
GROUP BY dbo.WH_Receive.ReceiveNO
HAVING (MAX(dbo.WH_Receive.ReceiveDate) >= '" + StartDateTime.Value.ToString("yyyy-MM-dd") + @"') AND
(MAX(dbo.WH_Receive.ReceiveDate) <= '" + EndDateTime.Value.ToString("yyyy-MM-dd") + @"')
ORDER BY MAX(dbo.WH_Receive.ReceiveDate), dbo.WH_Receive.ReceiveNO";
NewMethod(excel, 14, 3, sql);
sql = @"SELECT dbo.WH_Receive.ReceiveNO AS Expr1 FROM
dbo.WH_Receive LEFT OUTER JOIN
dbo.WH_ReceiveMaterial ON dbo.WH_Receive.ReceiveID = dbo.WH_ReceiveMaterial.ReceiveID
WHERE(dbo.WH_ReceiveMaterial.MaterialName = N'Diesel Fuel')
GROUP BY dbo.WH_Receive.ReceiveNO
HAVING (MAX(dbo.WH_Receive.ReceiveDate) >= '" + StartDateTime.Value.ToString("yyyy-MM-dd") + @"') AND
(MAX(dbo.WH_Receive.ReceiveDate) <= '" + EndDateTime.Value.ToString("yyyy-MM-dd") + @"')
ORDER BY MAX(dbo.WH_Receive.ReceiveDate), dbo.WH_Receive.ReceiveNO";
NewMethod(excel, 15, 3, sql);
sql = @"SELECT dbo.WH_Issue.IssueNo
FROM dbo.WH_Issue INNER JOIN
dbo.WH_IssueMaterial ON dbo.WH_Issue.IssueID = dbo.WH_IssueMaterial.IssueID
WHERE (dbo.WH_IssueMaterial.MaterialName <> N'Diesel Fuel')
GROUP BY dbo.WH_Issue.IssueNo
HAVING (MAX(dbo.WH_Issue.IssueDate) >= '" + StartDateTime.Value.ToString("yyyy-MM-dd") + @"') AND
(MAX(dbo.WH_Issue.IssueDate) <= '" + EndDateTime.Value.ToString("yyyy-MM-dd") + @"')
ORDER BY MAX(dbo.WH_Issue.IssueDate),dbo.WH_Issue.IssueNo";
NewMethod(excel, 18, 3, sql);
sql = @"SELECT dbo.WH_Issue.IssueNo
FROM dbo.WH_Issue INNER JOIN
dbo.WH_IssueMaterial ON dbo.WH_Issue.IssueID = dbo.WH_IssueMaterial.IssueID
WHERE (dbo.WH_IssueMaterial.MaterialName = N'Diesel Fuel')
GROUP BY dbo.WH_Issue.IssueNo
HAVING (MAX(dbo.WH_Issue.IssueDate) >= '" + StartDateTime.Value.ToString("yyyy-MM-dd") + @"') AND
(MAX(dbo.WH_Issue.IssueDate) <= '" + EndDateTime.Value.ToString("yyyy-MM-dd") + @"')
ORDER BY MAX(dbo.WH_Issue.IssueDate),dbo.WH_Issue.IssueNo";
NewMethod(excel, 19, 3, sql);
sql = @"SELECT TOP 100 PERCENT ReturnNo, ReturnDate
FROM dbo.WH_Return
WHERE (ReturnDate >= '" + StartDateTime.Value.ToString("yyyy-MM-dd") + @"') AND (ReturnDate <= '" + EndDateTime.Value.ToString("yyyy-MM-dd") + @"')
ORDER BY ReturnDate,ReturnNo";
NewMethod(excel, 20, 3, sql);
sql = @"SELECT TOP 100 PERCENT TransferWell2WellNo, TransferDate
FROM dbo.WH_TransferWell2Well
WHERE (TransferDate >= '" + StartDateTime.Value.ToString("yyyy-MM-dd") + @"') AND (TransferDate <= '" + EndDateTime.Value.ToString("yyyy-MM-dd") + @"')
ORDER BY TransferDate,TransferWell2WellNo";
NewMethod(excel, 21, 3, sql);
excel.InsertCell("Sheet1", 26, 0, EndDateTime.Value.ToString("yyyy-MM-dd"));
excel.SaveExcel(folderBrowserDialog1.SelectedPath + @"SDT-WH " + StartDateTime.Value.ToString("yyyy-MM-dd") + " to " + EndDateTime.Value.ToString("yyyy-MM-dd") + ".xls");
}
