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 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"); }