public void startGenerate(DateTime dateFrom, DateTime dateTo, DataBase db, bool NewFormat)
{
var DST = new DataSetTable();
try
{
DST.values.Add("month", BaseClass.getMonthName(dateTo.Month) + " " + dateTo.ToString("yyyy"));
DST.values.Add("dateTo", dateTo.ToString("dd/MM/yyyy"));
DST.values.Add("year", dateTo.Year.ToString());
DateTime dateFromYear = new DateTime(dateTo.Year, 1, 1);
Dictionary <string, string> destinationPathes = new Dictionary <string, string>();
Dictionary <string, string> templatePaths = new Dictionary <string, string>();
string destinationPath = @"c:\dest";
string templatePath = @"c:\templ";;
destinationPathes.Add("RegistersRemains", destinationPath + $"RegistersRemains_{dateTo.ToString("yyyyMMdd")}.xlsx");
//destinationPathes.Add("provision", destinationPath + $"provision_{dateTo.ToString("yyyyMMdd")}.xlsx");
//destinationPathes.Add("DealsReport", destinationPath + $"DealsReport_{dateTo.ToString("yyyyMMdd")}.xlsx");
foreach (string typeList in destinationPathes.Keys)
{
loadData(DST, db, dateFrom, dateTo, typeList);
ReportsGeneration.ReportGeneration(templatePath + typeList + ".xlsx", DST, destinationPathes[typeList]);
}
}
}
private void loadData(DataSetTable dST, DataBase db, DateTime dateFrom, DateTime dateTo, string typelist)
{
try
{
// string dir = AppDomain.CurrentDomain.BaseDirectory + @"SQLFiles\ЗапросыОтчеБугалтерия";
string dir = AppDomain.CurrentDomain.BaseDirectory + @"SQLFiles\ОтчетДляРуководства";
var sqlFiles = System.IO.Directory.GetFiles(dir, typelist + "$*");
DataTable dt = new DataTable();
foreach (var file in sqlFiles)
{
var nameTable = file.Substring(file.IndexOf("$") + 1);
if (nameTable.Contains("$"))
{
nameTable = nameTable.Substring(0, nameTable.IndexOf("$"));
}
if (!dST.Tables.Contains(nameTable))
{
dt = new DataTable(nameTable);
dST.Tables.Add(dt);
}
else
{
dt.PrimaryKey = new DataColumn[1] {
dt.Columns[0]
}
};
string requestStr = SQL2str.translateSQL2str(file, dateFrom, dateTo);
try
{
//добавим таблицу в набор
using (OracleDataReader reader = db.executeQuery(requestStr))
dST.Load(reader, LoadOption.OverwriteChanges, dt);
}
catch (Exception ex)
{
throw new Exception("Ошибка выгрузки тиблиы '" + nameTable + "' :" + ex.Message, ex);
}
}
}
catch (Exception ex)
{
throw new DbException("При генерации данных для отчета для руководства (вкладка \"Динамика остатков за месяц\") возникла ошибка.", ex);
}
}
/// <summary>
/// Метод генерации отчетов
/// </summary>
/// <param name="templateFile">Путь к excel шаблону отчета</param>
/// <param name="_dST">DataSetTable с данными и переменными values</param>
/// <param name="destinationFile">Путь c именем результата</param>
/// <param name="toByte">Если true, то возвращает</param>
public static void ReportGeneration(string templateFile, DataSetTable _dST, string destinationFile, bool toByte = false)
{
dST = _dST;
using (ExcelPackage p = new ExcelPackage(new FileInfo(templateFile), true))
{
using (ExcelWorksheet ws = p.Workbook.Worksheets[1])
{
var startCountRows = 0;
for (int indexRow = 1; indexRow <= ws.Dimension.Rows; indexRow++)
{
for (int indexColum = 1; indexColum <= ws.Dimension.Columns; indexColum++)
{
var cellValue = ws.Cells[indexRow, indexColum].Value; //типо оптимизация
// ref object CellValue =ref ws.Cells[indexRow, indexColum].Value;//создаем ссылку на значение ячейки, но только в новом шарпе
//именно проверяем на null что б не падал, а на пусто ту не проверяем, т.к. следующие условия его обрабатывают
if (cellValue == null)
{
continue;
}
else if (cellValue.ToString().Contains("<sub>")) //дефолтный под итог, потом можно расширить на не дублирование и подведение итогов не в одной колонке
{
ws.Cells[indexRow, indexColum].Value = cellValue.ToString().Replace("<sub>", ""); //очищаем от тех. инфы
startCountRows = indexRow;
string tableName = cellValue.ToString().Replace("${", "").Replace("}", "");
tableName = tableName.Substring(0, tableName.IndexOf(".")); //вычленяем имя
var items = dST.Tables[tableName].Rows; //берем строки из таблицу с нужным названием
List <subStrucktur> subStrucktures = new List <subStrucktur>(); //хранилище структуры подитогов
int nextRow = 1;
int countSub = 0;
while (true)
{
var valueCell = ws.Cells[indexRow + nextRow, indexColum].Value?.ToString();//получаем следующую ячейку
if (valueCell == null)
{
if (!(subStrucktures.Count > 0 && subStrucktures.Count == countSub))
{
nextRow++; //если еще не просмотрели всю структуру, то продолжаем
}
else
{
break;
}
}
else if (valueCell.Contains("<subitog")) //Проверяем есть ли ключевое слово
{
if (countSub == 0) //если количество итогов не задано, парсим
{
countSub = int.Parse(valueCell.Substring(valueCell.IndexOf(":") - 1, 1)); //вынимаем кол-во итогов
}
//добываем ссылку на данные
var tempValue = valueCell.Substring(valueCell.IndexOf(":") + 1);
tempValue = tempValue.Substring(0, tempValue.IndexOf(">")).Replace("${", "");
tempValue = tempValue.Substring(tempValue.IndexOf(".") + 1).Replace("}", "");
if (subStrucktures.Count > 0 && nextRow - 1 != subStrucktures.Last().NumberRow)
{
subStrucktures.Last().nextRowEmpty = true;//флаг что после итога нужна пустая строка
}
subStrucktures.Add(new subStrucktur(tempValue, nextRow, tempValue == "full" ? "full" : items[0][tempValue].ToString(), startCountRows)
{
Row = items[0]
});
ws.Cells[indexRow + nextRow, indexColum].Value = valueCell.Remove(0, valueCell.IndexOf(">") + 1);//вырезаем тех.инфу
nextRow++;
}
else
{
nextRow++;
}
}
foreach (DataRow item in items) //построчная вставка данных, построчная, что бы было проше анализировать формулы и тп.
{
foreach (var pZnach in subStrucktures) //проверяем сформировались ли подитоги
{
if (pZnach.Key != "full")
{
ProverkaItogov(ws, ref indexRow, tableName, pZnach, item);
}
}
InsertData(ws, ref indexRow, tableName, item);//вставляем строку данных с обработкой формул
}
//что бы подвести промежуточные итоги последней строке и итоговые
foreach (var pZnach in subStrucktures)
{
ProverkaItogov(ws, ref indexRow, tableName, pZnach, dST.Tables[tableName].NewRow());
}
// ProverkaItogov(ws, ref indexRow, tableName, subStrucktures.Last(), dST.Tables[tableName].NewRow());//старые итоговые
ws.DeleteRow(indexRow); //удаляем строку шаблона
for (int i = 0; i < subStrucktures.Count; i++) //строки итогов и саму строку удалить
{
if (subStrucktures[i].nextRowEmpty)
{
ws.DeleteRow(indexRow); //удаляем пустую строку шаблона подитогов
}
ws.DeleteRow(indexRow); //удаляем строку шаблона подитогов
}
for (int j = 1; j <= ws.Dimension.Columns; j++) //проходим по всем столбцам проверяем именнованные ряды
{
ReDiapozonNameRange(ws, 0, indexRow, j, startCountRows);
}
break;//так как вставлялка данных проверяет все колонки, то индекс столбца надо сбросить
}
else if (cellValue.ToString().Contains("${"))
{
if (cellValue.ToString().Contains("${graf"))//todo реализовать метки для нескольких графиков думаю через заголовок графика
{
var name = cellValue.ToString().Substring(cellValue.ToString().IndexOf(".") + 1);
name = name.Substring(0, name.IndexOf("}"));//имя можно посмотреть там же где задается именнованный ряд, изменить имя можно на вкладке графика
ws.Cells[indexRow, indexColum].Value = null;
var tempChar = ws.Drawings[name] as ExcelChart;
tempChar.SetPosition(indexRow - 1, 0, indexColum - 1, 0); //двигаем график на нужное место
while (tempChar.Title.Text.Contains("${")) //обработка нескольких переменных в одной ячейке
{
var templateValue = tempChar.Title.Text.Substring(tempChar.Title.Text.IndexOf("${"));
templateValue = templateValue.Substring(0, templateValue.IndexOf("}") + 1);
tempChar.Title.Text = cellValue.ToString().Replace(templateValue, (string)dST.values[templateValue.Replace("${", "").Replace("}", "")]);
}
}
else if (!cellValue.ToString().Substring(cellValue.ToString().IndexOf("${")).Contains("."))//todo в др метса
{
var tempValue = cellValue.ToString();
while (tempValue.Contains("${"))//обработка нескольких переменных в одной ячейке
{
var templateValue = tempValue.Substring(tempValue.IndexOf("${"));
templateValue = templateValue.Substring(0, templateValue.IndexOf("}") + 1);
tempValue = cellValue.ToString().Replace(templateValue, (string)dST.values[templateValue.Replace("${", "").Replace("}", "")]);
}
ws.Cells[indexRow, indexColum].Value = tempValue;
}
else
{
startCountRows = indexRow; //запоминаем начало вставки данных
string tableName = cellValue.ToString().Replace("${", "").Replace("}", "");
tableName = tableName.Substring(0, tableName.IndexOf(".")); //вычленяем имя
var items = dST.Tables[tableName].Rows; //берем строки из таблицу с нужным названием
foreach (DataRow item in items) //построчная вставка данных, построчная, что бы было проше анализировать формулы и тп.
{
InsertData(ws, ref indexRow, tableName, item); //вставляем строку данных с обработкой формул
}
for (int j = 1; j <= ws.Dimension.Columns; j++) //проходим по всем столбцам проверяем именнованные ряды
{
ReDiapozonNameRange(ws, 0, indexRow, j, startCountRows);
}
/* foreach (var nameRange in p.Workbook.Names)
* {
* nameRange.Address = ExcelCellBase.GetAddress(startCountRows, nameRange.Start.Column, startCountRows + countItem, nameRange.End.Column);
* }*/
ws.DeleteRow(indexRow); //удаляем строку шаблона
indexRow--; //удалили строку, уменьшаем индекс текущей строки
break; //так как вставлялка данных проверяет все колонки, то индекс надо сбросить
}
}
else if (cellValue.ToString().IndexOf("$[") != -1)//обработка формул
{
ws.Cells[indexRow, indexColum].Formula = cellValue.ToString().Replace("@", indexRow.ToString()).Replace("$[", "").Replace("]", "").Replace(";", ",");
}
else
{
continue;
}
}
}
ws.Calculate();//просчитываем формулы
if (!toByte)
{
try
{
p.SaveAs(new FileInfo(destinationFile));//сохраняем файл
}
catch (Exception)
{ //если файл открыть или нельзя редактировать, сохраняем файл с указанием времени
p.SaveAs(new FileInfo(destinationFile.Insert(destinationFile.LastIndexOf("."), DateTime.Now.ToString("yyyyMMddHHmmss"))));
}
}
else
{
outresukt = p.GetAsByteArray();//кому надо можно получить байты, пока жив класс, правда я пока не тестил
}
}
}
}
