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();//кому надо можно получить байты, пока жив класс, правда я пока не тестил } } } }