public static ReturnProc RunProcedure(SqlConnection conn, string procedureName, string projectName, DateTime? startdate = null, DateTime? enddate = null) { Log.Write("Start RunProcedure: " + procedureName); var sql = "EXEC " + procedureName; var cmd = new SqlCommand(sql, conn); cmd.CommandTimeout = 180; var lst = new List<ValidationRows>(); var lstData = new List<QueryData>(); var indexData = new List<IndexData>(); try { if (conn.State.ToString() == "Closed") { conn.Open(); } var rdr = cmd.ExecuteReader(); Log.Write("Start: " + cmd.CommandText); // Получаем список запросов валидации if (rdr.HasRows) { while (rdr.Read()) { lst.Add(new ValidationRows { s1 = rdr.GetString(0), s2 = rdr.GetString(1), s3 = rdr.GetString(2), s4 = rdr.GetString(3), s5 = rdr.GetString(4), s6 = rdr.GetString(5), }); } } rdr.Close(); rdr = null; Log.Write("Exit: " + cmd.CommandText); if (startdate != null && enddate != null) { foreach (var validationRowse in lst) { validationRowse.s6 = validationRowse.s6 + " @start_date = N'" + startdate.ToString() + "', @end_date = N'" + enddate.ToString() + "'"; } } else { foreach (var validationRowse in lst) { if (validationRowse.s6.Contains("GET_USER")) { validationRowse.s6 = validationRowse.s6 + " @start_date = NULL, @end_date = NULL"; } } } Log.Write("Start: Parsing"); foreach (var item in lst) { var obj = GetQueryData(item.s2, item.s1, item.s5, item.s3, item.s4, item.s6, projectName, conn); if (obj != null) { lstData.Add(obj); indexData.Add(new IndexData { Description = item.s3, NameList = item.s4, SelectCommand = item.s6, ValidationRule = item.s1 }); } } Log.Write("Exit: Parsing"); } catch (Exception e) { Log.Write(e.Message + " - " + procedureName + " - "); } finally { Log.Write("Exit RunProcedure: " + procedureName); } var res = new ReturnProc(lstData, indexData); return res; }
public static string GenerateDocument(string filePath,ReturnProc res) { Excel.Application ExcelApp; Excel.Worksheet ExcelSheet; Excel.Workbook ExcelWorkbook; Excel.Workbooks ExcelWorkbooks; Excel.Range ExcelRange; int rowsCount; int colsCount; Log.Write("Start GenerateDocument"); try { if (String.IsNullOrEmpty(filePath)) { filePath = Path.Combine(filePath + res.Data[0].ProjectName + "_Validation_" + DateTime.Now.ToShortDateString()); //if (Environment.OSVersion.Version.Major >= 6) //{ // filePath = Directory.GetParent(filePath).FullName; //} } var filename = res.Data[0].ProjectName + "_Validation_" + DateTime.Now.ToShortDateString() + ".xlsx"; ExcelApp = CreateExcelObj(); ExcelWorkbooks = ExcelApp.Workbooks; ExcelApp.ScreenUpdating = false; ExcelApp.DisplayAlerts = false; ExcelWorkbook = ExcelWorkbooks.Add(); res.Data = SortData(res.Data); foreach (var itemData in res.Data) { Log.Write(itemData.NameList + " " + itemData.ValidationRule); ExcelSheet = ExcelWorkbook.Sheets.Add(); ExcelSheet.Name = itemData.NameList; FormatSheet(ExcelSheet, itemData); rowsCount = itemData.Data.Rows.Count; colsCount = itemData.Data.Columns.Count; ExcelSheet.Cells[1, 1] = "Проект:"; ExcelSheet.Cells[1, 2] = itemData.ProjectName; ExcelSheet.Range[ExcelSheet.Cells[1, 2], ExcelSheet.Cells[1, colsCount]].Merge(); ExcelSheet.Cells[2, 1] = "Правило:"; ExcelSheet.Cells[2, 2] = itemData.ValidationRule; ExcelSheet.Range[ExcelSheet.Cells[2, 2], ExcelSheet.Cells[2, colsCount]].Merge(); ExcelSheet.Cells[3, 1] = "Описание:"; ExcelSheet.Cells[3, 2] = itemData.Description; ExcelSheet.Range[ExcelSheet.Cells[3, 2], ExcelSheet.Cells[3, colsCount]].Merge(); object[,] dataSet = new object[itemData.Data.Rows.Count, itemData.Data.Columns.Count]; for (int i = 1; i <= itemData.FieldsName.Count; i++) { ExcelSheet.Cells[5, i] = itemData.FieldsName[i - 1]; } for (int i = 0; i < itemData.Data.Rows.Count; i++) { for (int j = 0; j < itemData.FieldsName.Count; j++) { dataSet[i, j] = itemData.Data.Rows[i][j].ToString(); } } Excel.Range rng = ExcelSheet.Range[ ExcelSheet.Cells[6, 1], ExcelSheet.Cells[5 + itemData.Data.Rows.Count, itemData.FieldsName.Count]]; rng.Value = dataSet; FormatDataArea(ExcelSheet.Range[ExcelSheet.Cells[5, 1], ExcelSheet.Cells[5 + rowsCount, colsCount]], itemData.ValidationRule); FormatDescription(ExcelSheet.Range[ExcelSheet.Cells[1, 1], ExcelSheet.Cells[3, 2]]); ExcelSheet.Range[ExcelSheet.Cells[3, 2], ExcelSheet.Cells[3, colsCount]].WrapText = true; ExcelSheet.Range[ExcelSheet.Cells[3, 2], ExcelSheet.Cells[3, colsCount]].RowHeight = 60; } //Adding Index object[,] dataIndex = new object[res.Index.Count, 4]; for (int j = 0; j < res.Index.Count; j++) { dataIndex[j, 0] = res.Index[j].NameList; dataIndex[j, 1] = res.Index[j].ValidationRule; dataIndex[j, 2] = res.Index[j].Description; dataIndex[j, 3] = res.Index[j].SelectCommand; } ExcelSheet = ExcelWorkbook.Sheets.Add(); ExcelSheet.Name = "Index"; FormatSheet(ExcelSheet, res.Data[0]); ExcelSheet.Cells[1, 1] = "Проект:"; ExcelSheet.Cells[1, 2] = res.Data[0].ProjectName; ExcelSheet.Cells[2, 1] = "Описание:"; ExcelSheet.Range[ExcelSheet.Cells[2, 1], ExcelSheet.Cells[2, 3]].Merge(); FormatDescription(ExcelSheet.Range[ExcelSheet.Cells[1, 1], ExcelSheet.Cells[2, 2]]); ExcelSheet.Cells[4, 1] = "Имя листа"; ExcelSheet.Cells[4, 2] = "Правило валидации"; ExcelSheet.Cells[4, 3] = "Описание правила для поиска ошибок"; ExcelSheet.Cells[4, 4] = "SQL"; Excel.Range rngIndex = ExcelSheet.Range[ExcelSheet.Cells[5, 1], ExcelSheet.Cells[4 + res.Index.Count, 4]]; rngIndex.Value = dataIndex; FormatDataArea(ExcelSheet.Range[ExcelSheet.Cells[4, 1], ExcelSheet.Cells[4 + res.Index.Count, 4]], "index"); ExcelSheet.Range[ExcelSheet.Cells[5, 3], ExcelSheet.Cells[4 + res.Index.Count, 3]].ColumnWidth = 90; ExcelSheet.Range[ExcelSheet.Cells[5, 3], ExcelSheet.Cells[4 + res.Index.Count, 3]].WrapText = true; //End Creating Index ExcelWorkbook.SaveAs(); ExcelWorkbook.SaveAs(filePath + filename, Excel.XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing, false, false, Excel.XlSaveAsAccessMode.xlNoChange, Excel.XlSaveConflictResolution.xlLocalSessionChanges, Type.Missing, Type.Missing); while (Marshal.ReleaseComObject(ExcelWorkbook) > 0) { } while (Marshal.ReleaseComObject(ExcelWorkbooks) > 0) { } ExcelApp.Quit(); while (Marshal.ReleaseComObject(ExcelApp) > 0) { } Log.Write("Document created successfully !"); return filePath + "\\" + filename; } catch (Exception ex) { Log.Write(ex.Data + "\r\n" + ex.Message + "\r\n" + ex.Source + "\r\n" + ex.InnerException + "\r\n" + ex.StackTrace); return null; } finally { GC.Collect(); Log.Write("Exit GenerateDocument"); } }
private static void Main(string[] args) { try { var strPath = args[0]; var strServer = args[1]; var strDBName = args[2]; var strUser = args[3]; var strPassword = args[4]; var strMethod = args[5]; var strProject = args[6]; var conn = Tools.GetConnectionString(strServer, strDBName, strUser, strPassword); if (strMethod.Equals("RUN_VALIDATION")) { using (conn) { var res = new ReturnProc(null, null); try { res = Tools.RunProcedure(conn, "RUN_VALIDATION", strProject); // index = Tools.GetIndex(conn, "RUN_VALIDATION"); if (res.Data.Count > 0) { ExcelGeneration.GenerateDocument(strPath, res); } else { Log.Write("NoData"); } } catch (Exception ex) { Log.Write(ex); } finally { Log.Write("Finish"); } } } else if (strMethod.Equals("RUN_EXPORT")) { // var data = new List<QueryData>(); var res = new ReturnProc(null, null); try { res = Tools.RunProcedure(conn, "RUN_EXPORT", strProject); if (res.Data.Count > 0) { ExcelGeneration.GenerateDocument2(strPath, res); Log.Write("Finish"); } else { Log.Write("NoData"); } } catch (Exception ex) { Log.Write(ex); } finally { Log.Write("Finish"); } } else if (strMethod.Equals("RUN_EXPORT_CSV")) { string fileName = ""; string sql = ""; var index = new List<IndexData>(); try { index = Tools.GetIndex(conn, "RUN_EXPORT"); Log.Write("Finish"); } catch (Exception ex) { Log.Write(ex); } finally { if (index.Count > 0) { foreach (var queryData in index) { fileName = queryData.NameList; sql = queryData.SelectCommand; Tools.ExportToCSVFile(strPath, strProject, fileName, sql, conn, Encoding.GetEncoding(1251), ";", true); } } else { Log.Write("NoData"); } Log.Write("Finish"); } } else if (strMethod.Equals("RUN_QUERY")) { try { Tools.GetQueriesInFormat(conn, strProject, strPath); } catch (Exception ex) { Log.Write(ex); } finally { Log.Write("Finish"); } } else if (strMethod.Equals("RUN_ACTIVITY")) { var res = new ReturnProc(null, null); try { res = Tools.RunProcedure(conn, "RUN_ACTIVITY", strProject); // var index = Tools.GetIndex(conn, "RUN_ACTIVITY"); if (res.Data.Count > 0) { ExcelGeneration.GenerateDocument(strPath, res); } } catch (Exception ex) { Log.Write(ex); } finally { Log.Write("Finish"); } } else if (strMethod.Equals("RUN_SYNC")) { try { Tools.RunProcedureNonQuery(conn, "RUN_SYNC"); } catch (Exception ex) { Log.Write(ex); } finally { Log.Write("Finish"); } } else { } } catch (Exception) { Log.Write("Error input"); } finally { Console.ReadKey(); } }