private void SetLockedState()
{
XElement root = new XElement("divisions");
foreach (DataGridViewRow row in dgvLockedDivisions.Rows)
{
root.Add(new XElement("division",
new XElement("id", Routines.GetString(row.Cells["colId"].Value)),
new XElement("state", Routines.GetString(row.Cells["colIsLocked"].Value))
));
}
DialogResult =
DatabaseUtils.CallProcedure(_connection, "pkg$trep_reports.setdivisionlockedstate",
new OracleParameter[]
{
new OracleParameter()
{
ParameterName = "pAgentsStateList",
OracleDbType = OracleDbType.Clob,
Value = new XDocument(root).ToString(SaveOptions.DisableFormatting)
}
}) ? DialogResult.OK : DialogResult.Cancel;
}
public static void OutloadExcelEpplus(string pathTemplate, string pathOutput, DataTable dtRows,
DataTable dtFormat, bool isColorize, bool isOpenAfterCreate, DataTable dtLists = null)
{
try
{
//exApp.TemplatesPath = Path.GetDirectoryName(pathTemplate);
using (ExcelPackage pack = new ExcelPackage(new FileInfo(pathOutput), new FileInfo(pathTemplate)))
{
ExcelWorksheet wsNotFormat = null;
if (dtLists != null)
{
foreach (DataRow row in dtLists.Rows)
{
int listNum = Routines.GetInt(row["list_num"]);
if (listNum <= 0)
{
continue;
}
/*for (int i = 1; i <= listNum - pack.Workbook.Worksheets.Count; i++)
* pack.Workbook.Worksheets.Add("test");*/
string listName = Routines.GetString(row["list_name"]);
int? copyListNum = Routines.TryGetInt(row["copy_list_num"]);
if (copyListNum == null)
{
pack.Workbook.Worksheets.First(worksheet => worksheet.Index == listNum).Name = listName; continue;
}
if (copyListNum > 0)
{
pack.Workbook.Worksheets.Copy(
pack.Workbook.Worksheets.First(worksheet => worksheet.Index == copyListNum).Name,
listName);
}
else
{
wsNotFormat = pack.Workbook.Worksheets.Add(listName);
}
}
}
foreach (DataRow row in dtFormat.Rows)
{
int listNum = Routines.GetInt(row["list_num"]);
if (listNum > pack.Workbook.Worksheets.Count)
{
MessageBox.Show($"Прерывание формирования excel. Лист с номером {listNum} отсутствует.");
}
ExcelWorksheet ws = pack.Workbook.Worksheets[listNum];
string cellAddress = Routines.GetString(row["range"]);
ExcelRange er = ws.Cells[cellAddress];
er.Style.Font.Name = "Arial";
int?cellFontSize = Routines.TryGetInt(row["font_size"]);
er.Style.Font.Size = cellFontSize ?? 8;
int?cellBorderLineStyle = Routines.TryGetInt(row["border"]);
if (cellBorderLineStyle != null)
{
er.Style.Border.Top.Style =
er.Style.Border.Right.Style =
er.Style.Border.Bottom.Style = er.Style.Border.Left.Style = ExcelBorderStyle.Thin;
}
if ("Y".Equals(Routines.GetString(row["is_merged"])))
{
er.Merge = true;
}
if ("Y".Equals(Routines.GetString(row["is_colored"])))
{
er.Style.Font.Color.SetColor(Color.Red);
}
}
foreach (DataRow row in dtRows.Rows)
{
int listNum = Routines.GetInt(row["list_num"]);
if (listNum > pack.Workbook.Worksheets.Count)
{
MessageBox.Show($"Прерывание формирования excel. Лист с номером {listNum} отсутствует.");
}
ExcelWorksheet ws = pack.Workbook.Worksheets[listNum];
string cellAddress = Routines.GetString(row["col_name"]) + Routines.GetString(row["row_num"]);
ExcelRange er = ws.Cells[cellAddress];
string cellValue = Routines.GetString(row["value"]);
if ((cellValue.Length > 0) && (cellValue[0] == '='))
{
if (isColorize)
{
var fill = er.Style.Fill;
fill.PatternType = ExcelFillStyle.Solid;
fill.BackgroundColor.SetColor(Color.LightBlue);
}
er.Formula = cellValue;
er.Style.Numberformat.Format = "### ### ##0.00";
}
else
{
if (isColorize)
{
var fill = er.Style.Fill;
fill.PatternType = ExcelFillStyle.Solid;
fill.BackgroundColor.SetColor(Color.Yellow);
}
decimal cellFloatValue;
if (decimal.TryParse(cellValue, out cellFloatValue))
{
er.Value = cellFloatValue;
//er.AutoFitColumns();
}
else
{
er.Value = cellValue;
}
if (wsNotFormat == ws)
{
er.AutoFitColumns();
}
}
//er.Style.WrapText = true; //cells.Rows.AutoFit();
}
//pack.Workbook.Calculate();
pack.Save();
if (dtLists != null)
{
foreach (DataRow row in dtLists.Rows)
{
int listNum = Routines.GetInt(row["list_num"]);
if (listNum <= 1)
{
continue;
}
string listName = Routines.GetString(row["list_name"]);
string newFileName = pathOutput.Replace(".xlsx", $"_{listName.Replace("\"", "")}.xlsx");
using (ExcelPackage packList = new ExcelPackage(new FileInfo(newFileName)))
{
packList.Workbook.Worksheets.Add(listName,
pack.Workbook.Worksheets.First(worksheet => worksheet.Index == listNum));
packList.Save();
packList.Dispose();
}
}
}
pack.Dispose();
}
if (!isOpenAfterCreate)
{
return;
}
Excel.Application createdExcel = new Excel.Application {
Visible = false
};
createdExcel.Workbooks.Open(pathOutput);
createdExcel.Visible = true;
}
catch (Exception e)
{
MessageBox.Show($"{e.Message}\r{pathOutput}");
//exApp.Quit();
}
}
public static void OutloadExcel(string pathTemplate, string pathOutput, DataTable dtRows, DataTable dtFormat,
bool isColorize, bool isOpenAfterCreate)
{
Excel.Application exApp = new Excel.Application();
try
{
exApp.Visible = false;
//exApp.TemplatesPath = Path.GetDirectoryName(pathTemplate);
exApp.Workbooks.Add(pathTemplate);
Excel.Workbook exWorkBook = exApp.Workbooks[1];
foreach (DataRow row in dtFormat.Rows)
{
int listNum = Routines.GetInt(row["list_num"]);
if (listNum > exWorkBook.Sheets.Count)
{
MessageBox.Show($"Прерывание формирования excel. Лист с номером {listNum} отсутствует.");
}
Excel.Worksheet exSheet = (Excel.Worksheet)exWorkBook.Sheets.Item[listNum];
string cellAddress = Routines.GetString(row["range"]);
Excel.Range cells = exSheet.Range[cellAddress, Type.Missing];
cells.Font.Name = "Arial";
int?cellFontSize = Routines.TryGetInt(row["font_size"]);
cells.Font.Size = cellFontSize ?? 8;
int?cellBorderLineStyle = Routines.TryGetInt(row["border"]);
if (cellBorderLineStyle != null)
{
cells.Borders.LineStyle = cellBorderLineStyle;
}
if ("Y".Equals(Routines.GetString(row["is_merged"])))
{
cells.Merge();
}
}
foreach (DataRow row in dtRows.Rows)
{
int listNum = Routines.GetInt(row["list_num"]);
if (listNum > exWorkBook.Sheets.Count)
{
MessageBox.Show($"Прерывание формирования excel. Лист с номером {listNum} отсутствует.");
}
Excel.Worksheet exSheet = (Excel.Worksheet)exWorkBook.Sheets.Item[listNum];
string cellAddress = Routines.GetString(row["col_name"]) + Routines.GetString(row["row_num"]);
Excel.Range cells = exSheet.Range[cellAddress, Type.Missing];
string cellValue = Routines.GetString(row["value"]);
if ((cellValue.Length > 0) && (cellValue[0] == '='))
{
if (isColorize)
{
cells.Interior.Color = Color.LightBlue;
}
cells.Formula = cellValue;
}
else
{
if (isColorize)
{
cells.Interior.Color = Color.Yellow;
}
cells.Value2 = cellValue;
}
cells.Rows.AutoFit();
}
exWorkBook.SaveAs(pathOutput);
exApp.Quit();
if (isOpenAfterCreate)
{
Excel.Application createdExcel = new Excel.Application();
createdExcel.Visible = false;
createdExcel.Workbooks.Open(pathOutput);
createdExcel.Visible = true;
}
}
catch (Exception e)
{
MessageBox.Show(e.Message);
exApp.Quit();
}
}
private void Calc(ReportType type)
{
var tCalc = new Thread(() =>
{
Lock = true;
lock (this)
{
_timeCalcBegin = DateTime.Now;
}
if (!CalcData(type))
{
return;
}
SetStatusText("Идет подготовка к выгрузке файлов.");
if (new [] { ReportType.Route, ReportType.Terminal, ReportType.TransportVehicle, ReportType.TransportCard, ReportType.Organisation }.Contains(type))
{
var query = ReportUtils.GetReaderQuery(type);
var reader = (type == ReportType.TransportCard)
? DatabaseUtils.GetReader(_connection, query, new[]
{
new OracleParameter
{
ParameterName = "pActivationBeginDate",
OracleDbType = OracleDbType.Date,
Value = dtActivePassActivationBeginDate.Value
},
new OracleParameter
{
ParameterName = "pActivationEndDate",
OracleDbType = OracleDbType.Date,
Value = dtActivePassActivationEndDate.Value
}
})
: DatabaseUtils.GetReader(_connection, query);
if ((reader == null) || (!reader.HasRows))
{
return;
}
_timeOutputBegin = DateTime.Now;
ClearThreadVars();
while (reader.Read())
{
var id = Routines.GetLong(reader["id_element"]);
var name = Routines.GetString(reader["name_element"]);
var t = new Thread(() =>
{
lock (this)
{
_threadWaitingCount++;
_threadFullCount++;
}
while (true)
{
lock (this)
{
if (_threadActiveCount < ((new List <ReportType> {
ReportType.TransportVehicle
}.Contains(type)) ? 4 : 16))
{
_threadActiveCount++;
_threadWaitingCount--;
UpdateStatusProgressText();
break;
}
}
Thread.Sleep(50);
}
CalcOutput(type, id, name);
lock (this)
{
_threadActiveCount--;
_threadFinishedCount++;
UpdateStatusProgressText();
}
if ((_threadActiveCount != 0) || (_threadWaitingCount != 0))
{
return;
}
//Если это последний поток
Lock = false;
MessageBox.Show(
$"Выгрузилось {_threadFullCount} отчетов за {(DateTime.Now - _timeCalcBegin).ToString(@"dd\.hh\:mm\:ss")}!");
SetStatusText("");
})
{
IsBackground = true
};
if (_threadWaitingCount < 500)
{
t.Start();
}
else
{
while (true)
{
if (_threadWaitingCount < 50)
{
t.Start();
break;
}
Thread.Sleep(50);
}
}
}
}
else
{
CalcOutput(type);
Lock = false;
MessageBox.Show(
$"Выгрузился за {(DateTime.Now - _timeCalcBegin).ToString(@"dd\.hh\:mm\:ss")}!");
SetStatusText("");
}
});
tCalc.Start();
}