90 lines
4.4 KiB
C#
90 lines
4.4 KiB
C#
using System.Data;
|
|
using System.IO;
|
|
using Kreta.Core.ConnectionType;
|
|
using Kreta.DataAccessManual;
|
|
using Kreta.Resources;
|
|
using OfficeOpenXml;
|
|
|
|
namespace Kreta.BusinessLogic.Classes.ExcelHelpers
|
|
{
|
|
public class EslRiportExportHelper : LogicBase
|
|
{
|
|
public EslRiportExportHelper(IConnectionType connectionType) : base(connectionType) { }
|
|
|
|
public MemoryStream GetLemorzsolodasEslRiportExport(int? feladatEllatasiHelyId, bool isFelevi)
|
|
{
|
|
DataSet dataSet = Dal.CustomConnection.Run(ConnectionType, h =>
|
|
{
|
|
DataSet result = h.AdatszolgaltatasokDAL().GetLemorzsolodasEslRiportExport(IntezmenyId, TanevId, feladatEllatasiHelyId, isFelevi);
|
|
return result;
|
|
});
|
|
var stream = new MemoryStream();
|
|
if (dataSet.Tables.Count > 0)
|
|
{
|
|
DataTable dataTable = dataSet.Tables[0];
|
|
DataRowCollection dataRowCollection = dataTable.Rows;
|
|
for (int columnIndex = 0; columnIndex < dataTable.Columns.Count; columnIndex++)
|
|
{
|
|
string columnName = dataTable.Columns[columnIndex].ColumnName;
|
|
|
|
if (columnName != "Leírás" && columnName != "Összesen" && columnName != "Lány")
|
|
{
|
|
for (int rowIndex = 0; rowIndex < dataTable.Rows.Count; rowIndex++)
|
|
{
|
|
dataRowCollection[rowIndex][columnIndex] = dataRowCollection[rowIndex][columnIndex].ToString().Replace("¤", System.Environment.NewLine);
|
|
}
|
|
}
|
|
}
|
|
|
|
using (var excel = new ExcelPackage(stream))
|
|
{
|
|
var workSheet = excel.Workbook.Worksheets.Add(AdatszolgaltatasokResource.ReszletesAdatok);
|
|
workSheet.Cells[1, 1].LoadFromDataTable(dataTable, true);
|
|
int sectionStartIndex = 2;
|
|
string sectioName = dataTable.Rows[0].Field<string>("Leírás");
|
|
int lastColumn = dataTable.Columns.Count;
|
|
for (int i = 1; i < dataTable.Rows.Count; i++)
|
|
{
|
|
if (dataTable.Rows[i].Field<string>("Leírás") != sectioName || i == dataTable.Rows.Count - 1)
|
|
{
|
|
workSheet.Cells[sectionStartIndex, 1, i + 1, 1].Merge = true; //Leírás
|
|
workSheet.Cells[i + 1, 1, i + 1, lastColumn].Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
|
|
workSheet.Cells[sectionStartIndex, lastColumn - 1, i + 1, lastColumn - 1].Merge = true; // Összesen db
|
|
workSheet.Cells[sectionStartIndex, lastColumn, i + 1, lastColumn].Merge = true; // Lány db
|
|
sectionStartIndex = i + 2;
|
|
sectioName = dataTable.Rows[i].Field<string>("Leírás");
|
|
}
|
|
}
|
|
|
|
workSheet.View.FreezePanes(2, 2);
|
|
|
|
for (int columnIndex = workSheet.Dimension.Start.Column; columnIndex <= workSheet.Dimension.End.Column; columnIndex++)
|
|
{
|
|
workSheet.Column(columnIndex).Width = 45;
|
|
workSheet.Cells[1, columnIndex].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
|
|
|
|
for (int rowIndex = workSheet.Dimension.Start.Row; rowIndex <= workSheet.Dimension.End.Row; rowIndex++)
|
|
{
|
|
if (columnIndex < lastColumn - 1)
|
|
{
|
|
workSheet.Cells[rowIndex, columnIndex].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
|
|
workSheet.Cells[rowIndex, columnIndex].Style.WrapText = true;
|
|
}
|
|
}
|
|
}
|
|
excel.Save();
|
|
}
|
|
}
|
|
else
|
|
{
|
|
using (var excel = new ExcelPackage(stream))
|
|
{
|
|
var workSheet = excel.Workbook.Worksheets.Add(AdatszolgaltatasokResource.ReszletesAdatok);
|
|
excel.Save();
|
|
}
|
|
}
|
|
stream.Position = 0;
|
|
return stream;
|
|
}
|
|
}
|
|
}
|