static async Task SeasonalImport(string path, int year)
{
using var seasonalRetriever = new MaddenImporter.Core.SeasonalRetriever();
var workbook = new ClosedXML.Excel.XLWorkbook();
var players = (await seasonalRetriever.GetAllPlayers(year)).ToList();
ExcelExtensions.WritePlayerSheet <PassingPlayer>(workbook, Extensions.GetPlayersOfType <PassingPlayer>(players));
ExcelExtensions.WritePlayerSheet <RushingPlayer>(workbook, Extensions.GetPlayersOfType <RushingPlayer>(players));
ExcelExtensions.WritePlayerSheet <DefensePlayer>(workbook, Extensions.GetPlayersOfType <DefensePlayer>(players));
ExcelExtensions.WritePlayerSheet <ReturningPlayer>(workbook, Extensions.GetPlayersOfType <ReturningPlayer>(players));
ExcelExtensions.WritePlayerSheet <KickingPlayer>(workbook, Extensions.GetPlayersOfType <KickingPlayer>(players));
ExcelExtensions.WritePlayerSheet <ReceivingPlayer>(workbook, Extensions.GetPlayersOfType <ReceivingPlayer>(players));
workbook.SaveAs(path + "/players.xlsx");
workbook.Dispose();
}
/// <summary>
/// Write ACLs of a directory and his subdirectories into an Excel file.
/// </summary>
/// <param name="scanDirectoryResult">Object returned by an ACL scan on a UNC path</param>
/// <param name="filename">The name to use for the report (filename_ShareScanResult.xls)</param>
public static void GenerateSMBDirectoryScanResultReport(ScanDirectoryResult scanDirectoryResult, string filename)
{
int row = 1;
int col = 1;
var workbook = new ClosedXML.Excel.XLWorkbook();
var worksheet = workbook.Worksheets.Add("Scan Result");
row = WriteHeader(worksheet, row, col);
WriteShareDirectories(worksheet, new Dictionary <string, ScanDirectoryResult> {
{ scanDirectoryResult.shareDirectoryACL.shareDirectory, scanDirectoryResult }
}, row);
workbook.SaveAs(filename + "_ShareDirectoryScan.xlsx");
}
private async void ErrorExport_Click(object sender, EventArgs e)
{
OpenFileDialog fdlg = new OpenFileDialog();
fdlg.Title = "Select your log file";
fdlg.InitialDirectory = location;
fdlg.Filter = "All files (*.*)|*.*|All files (*.*)|*.*";
fdlg.FilterIndex = 2;
fdlg.RestoreDirectory = true;
if (fdlg.ShowDialog() == DialogResult.OK)
{
fileLocated = fdlg.FileName;
}
else
{
return;
}
statusLabel.Text = "Busy... Please Wait";
string[] lines = File.ReadAllLines(fileLocated);
IEnumerable <string> selectLines = lines.Where(line => line.Contains(": Error:"));
List <String> lista = new List <String>();
foreach (var item in selectLines)
{
lista.Add(item);
}
using (var workBook = new ClosedXML.Excel.XLWorkbook())
{
var worksheet = workBook.Worksheets.Add("Error list");
for (int i = 1; i < lista.Count; i++)
{
worksheet.Cell("A" + i).Value = lista[i - 1];
}
workBook.SaveAs(location + "Error Report.xlsx");
}
await Task.Delay(1000);
statusLabel.Text = "Done!";
await Task.Delay(1000);
statusLabel.Text = "Idle";
}
static void Main(string[] args)
{
ClosedXML.Excel.XLWorkbook workbook = null;
workbook = new ClosedXML.Excel.XLWorkbook("../../WaterPumpSystem.xlsx");
foreach (var sheet in workbook.Worksheets)
{
if (sheet.IsEmpty())
{
System.Console.WriteLine(sheet.Name + " is empty");
}
else
{
System.Console.WriteLine(sheet.Name + " is NOT empty");
}
}
}
public async Task <byte[]> ConvertToXlsx(IDictionary <string, IList <string> > data)
{
return(await Task.Factory.StartNew(() =>
{
_logger.Information("trying to convert lists of data to xlsx file");
_logger.Information("trying to open Excel Work Book.");
ClosedXML.Excel.XLWorkbook wbook = new ClosedXML.Excel.XLWorkbook(ClosedXML.Excel.XLEventTracking.Disabled);
_logger.Information("trying to add Excel Work Sheet.");
var sheet = wbook.Worksheets.Add("sheet1");
int row = 1;
int col = 1;
int rowsCount = data[data.Keys.First()].Count;
int columnsCount = data.Keys.Count;
var keys = data.Keys.ToArray();
_logger.Information($"start printing data to excel file, ColumnsCount={columnsCount} . RowsCount={rowsCount}");
for (row = 1; row <= rowsCount + 1; row++)
{
for (col = 1; col <= columnsCount; col++)
{
string key = keys[col - 1];
var values = data[key];
if (row == 1)
{
//write the column header
sheet.Cell(1, col).Value = key;
}
else
{
sheet.Cell(row, col).Value = values[row - 2];
}
}
}
_logger.Information($"all data has been printed successfully to the excel file.");
sheet.ExpandColumns();
setHeaderStyle(sheet, 2, 1, 1, columnsCount);
setBorder(sheet, 1, 1, rowsCount + 1, columnsCount);
wbook.Author = "Orphanage Service V" + System.Reflection.Assembly.GetExecutingAssembly().GetName().Version.ToString();
using (MemoryStream memoryStream = new MemoryStream())
{
wbook.SaveAs(memoryStream);
_logger.Information($"the excel file has been saved successfully, an array of bytes will be returned.");
return memoryStream.ToArray();
}
}));
}
public static ClosedXML.Excel.XLWorkbook ConvertToExcel(this DataSet ds)
{
ClosedXML.Excel.XLWorkbook xls = new ClosedXML.Excel.XLWorkbook();
try
{
for (int i = 0; i < ds.Tables.Count; i++)
{
ds.Tables[i].TableName = $"_{ds.Tables[i].TableName}";
}
xls.Worksheets.Add(ds);
}
catch (Exception ex)
{
ApplicationLogging.CreateLogger("QueryExtensionMethods").LogError(ex, ex.Message);
}
return(xls);
}
public void ClosedXmlXlsx()
{
var pkg = new ClosedXML.Excel.XLWorkbook(file);
var ws = pkg.Worksheets.First();
var rc = ushort.MaxValue;
var cc = 14;
for (int r = 1; r <= rc; r++)
{
var row = ws.Row(r);
for (int i = 1; i <= cc; i++)
{
var cell = row.Cell(i);
var value = cell.Value;
}
}
}
public async Task <IActionResult> GerarRelatorio([FromServices] EvolucionalContext context)
{
var alunos = await context.Alunos.ToListAsync();
var disciplinas = await context.Disciplinas.ToListAsync();
using (var workbook = new ClosedXML.Excel.XLWorkbook())
{
var worksheet = workbook.Worksheets.Add("Aluno");
var currentRow = 1;
var coluna = 1;
worksheet.Cell(currentRow, 1).Value = "Aluno";
worksheet.Cell(currentRow, 2).Value = "Matemática";
worksheet.Cell(currentRow, 3).Value = "Português";
worksheet.Cell(currentRow, 4).Value = "História";
worksheet.Cell(currentRow, 5).Value = "Geografica";
worksheet.Cell(currentRow, 6).Value = "Inglês";
worksheet.Cell(currentRow, 7).Value = "Biologia";
worksheet.Cell(currentRow, 8).Value = "FIlosofia";
worksheet.Cell(currentRow, 9).Value = "Física";
worksheet.Cell(currentRow, 10).Value = "Química";
worksheet.Cell(currentRow, 11).Value = "Media";
foreach (var aluno in alunos.OrderBy(el => el.Nome))
{
currentRow++;
worksheet.Cell(currentRow, 1).Value = aluno.Nome;
worksheet.Cell(currentRow, 2).Value = aluno.Notas.Where(_ => _.Disciplina.Nome == "Matemática").FirstOrDefault().NotaAluno;
worksheet.Cell(currentRow, 3).Value = aluno.Notas.Where(_ => _.Disciplina.Nome == "Português").FirstOrDefault().NotaAluno;
worksheet.Cell(currentRow, 4).Value = aluno.Notas.Where(_ => _.Disciplina.Nome == "História").FirstOrDefault().NotaAluno;
worksheet.Cell(currentRow, 5).Value = aluno.Notas.Where(_ => _.Disciplina.Nome == "Geografica").FirstOrDefault().NotaAluno;
worksheet.Cell(currentRow, 6).Value = aluno.Notas.Where(_ => _.Disciplina.Nome == "Inglês").FirstOrDefault().NotaAluno;
worksheet.Cell(currentRow, 7).Value = aluno.Notas.Where(_ => _.Disciplina.Nome == "Biologia").FirstOrDefault().NotaAluno;
worksheet.Cell(currentRow, 8).Value = aluno.Notas.Where(_ => _.Disciplina.Nome == "FIlosofia").FirstOrDefault().NotaAluno;
worksheet.Cell(currentRow, 9).Value = aluno.Notas.Where(_ => _.Disciplina.Nome == "Física").FirstOrDefault().NotaAluno;
worksheet.Cell(currentRow, 10).Value = aluno.Notas.Where(_ => _.Disciplina.Nome == "Química").FirstOrDefault().NotaAluno;
worksheet.Cell(currentRow, 11).Value = (aluno.Notas.Sum(_ => _.NotaAluno) / disciplinas.Count());
}
var stream = new System.IO.MemoryStream();
workbook.SaveAs(stream);
return(File(stream.ToArray(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "Faturamento.xls"));
}
}
// сохранение. путь к папке, лист с данными
public void SaveExcel(string fileName, ObservableCollection <Raspisanie> raspisanie)
{
using (ClosedXML.Excel.XLWorkbook workbook = new ClosedXML.Excel.XLWorkbook())
{
workbook.AddWorksheet("Расписание");
var ws = workbook.Worksheet("Расписание");
int row = 1;
foreach (var c in raspisanie)
{
ws.Cell("A" + row.ToString()).Value = c.Party;
ws.Cell("B" + row.ToString()).Value = c.Equipment;
ws.Cell("C" + row.ToString()).Value = c.TStart;
ws.Cell("D" + row.ToString()).Value = c.TStop;
row++;
}
workbook.SaveAs(fileName + "\\" + "Raspisanie.xlsx");
}
}
public FileResult OnGetExcelDownload()
{
// get the query this dashboard uses
var qry = GetQuery();
// get the query's underlying SQL, resolving conditional criteria and parameters
var sql = QueryUtil.ResolveSql(qry.Sql, qry);
using (var cn = Data.GetConnection())
{
// query the data and return a data table
var data = cn.QueryTableWithParams(sql, qry);
data.TableName = "Open Work Items";
var wb = new ClosedXML.Excel.XLWorkbook();
var ws = wb.AddWorksheet(data);
return(wb.Deliver("OpenWorkItems.xlsx"));
}
}
string AsExcel(DataTable dataTable, string fileName)
{
using (ClosedXML.Excel.XLWorkbook wbook = new ClosedXML.Excel.XLWorkbook())
{
wbook.Worksheets.Add(dataTable, fileName);
var memoryStream = new MemoryStream();
wbook.SaveAs(memoryStream);
var strPdfPath = Path.Combine(
Directory.GetCurrentDirectory(), "wwwroot/Documents",
fileName);
var file = File.Create(strPdfPath);
memoryStream.Position = 0;
memoryStream.WriteTo(file);
memoryStream.Close();
file.Close();
return(fileName);
}
}
static void Main(string[] args)
{
if (args.Count() < 1)
{
Console.WriteLine("Please enter a file path:");
args = new string[1];
args[0] = Console.ReadLine();
}
var path = args[0];
if (!File.Exists(path) || !path.ToUpper().Contains("CSV"))
{
Console.WriteLine("Must provide a valid CSV");
System.Threading.Thread.Sleep(500);
return;
}
List <dynamic> issues;
using (var reader = new StreamReader(path))
{
using (var csv = new CsvReader(reader))
{
issues = csv.GetRecords <dynamic>().ToList();
}
}
using (var wb = new ClosedXML.Excel.XLWorkbook())
{
DataTable table = ToDataTable(issues);
wb.AddWorksheet(table, "Sheet1");
foreach (var ws in wb.Worksheets)
{
ws.Columns().AdjustToContents();
}
var output = path.Substring(0, path.Length - 3) + "xlsx";
wb.SaveAs(output);
Console.WriteLine("wrote to : " + output);
System.Threading.Thread.Sleep(500);
return;
}
}
public static void WriteExcel(string templateName, string fileName, ExcelDelegate xlsd)
{
string templatePath = Path.Combine(Util.GetAppPath(), templateName);
ClosedXML.Excel.XLWorkbook workbook = new ClosedXML.Excel.XLWorkbook(templatePath);
xlsd(workbook);
SaveFileDialog saveFileDialog = new SaveFileDialog();
//saveFileDialog.InitialDirectory = "%USERPROFILE%";
saveFileDialog.Filter = "Excel|*.xlsx";
saveFileDialog.RestoreDirectory = true;
saveFileDialog.ShowHelp = true;
saveFileDialog.FileName = fileName;
if (saveFileDialog.ShowDialog().Equals(DialogResult.OK))
{
workbook.SaveAs(saveFileDialog.FileName);
}
workbook.Dispose();
}
public string[,] OpenExcel(string filename)
{
using (ClosedXML.Excel.XLWorkbook workbook = new ClosedXML.Excel.XLWorkbook(filename))
{
ClosedXML.Excel.IXLWorksheet worksheet = workbook.Worksheet(1);
int rc = worksheet.RangeUsed().RowCount();
int cc = worksheet.RangeUsed().Row(1).CellCount();
string[,] buff = new string[rc, cc];
for (int i = 0; i < rc; i++)
{
ClosedXML.Excel.IXLRow row = worksheet.Row(i + 1);
for (int j = 0; j < cc; j++)
{
ClosedXML.Excel.IXLCell cell = row.Cell(j + 1);
string value = cell.GetValue <string>();
buff[i, j] = value;
}
}
return(buff);
}
}
static void PrintCountries()
{
string contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
string fileName = "authors.xlsx";
Console.WriteLine("Input Address city");
var city = Console.ReadLine().ToString();
var AddressDAL = new AddressDAL(_iconfiguration);
var listAddressModel = AddressDAL.GetList(city);
Console.WriteLine("Your city adress is:");
listAddressModel.ForEach(item =>
{
Console.WriteLine($"Your address is: {item.AddressLine1}");
});
using (var wb = new ClosedXML.Excel.XLWorkbook())
{
var ws = wb.AddWorksheet("Duy");
var currentRow = 1;
ws.Cell(currentRow, 1).SetValue("Id");
ws.Cell(currentRow, 2).SetValue("Address");
ws.Cell(currentRow, 3).SetValue("City");
foreach (var item in listAddressModel)
{
currentRow++;
ws.Cell(currentRow, 1).SetValue(item.AddressId);
ws.Cell(currentRow, 2).SetValue(item.AddressLine1);
ws.Cell(currentRow, 3).SetValue(item.City);
}
string desktopPath = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
string savePath = Path.Combine(desktopPath, "test.xlsx");
wb.SaveAs(savePath, false);
}
Console.WriteLine("Press any key to stop.");
Console.ReadKey();
}
public async Task ExtractDataValid()
{
ClosedXML.Excel.XLWorkbook generatedWorkbook = null;
DataRetrievalViewModel sut = new DataRetrievalViewModel(ioServiceMock.Object, xlIOServiceMock.Object, uiControlsServiceMock.Object);
sut.ExtractionDirectory = Path.Combine(debugDirectory, "XLSXFiles");
sut.DirectoryForXLSXExtractionFiles = Path.Combine(debugDirectory, "XLSXFiles");
sut.DataRetrievalRequests = new ObservableCollection <DataRetrievalRequest>();
sut.DataRetrievalRequests.Add(new DataRetrievalRequest {
FieldName = "Test", ColumnName = 1.ToString(), Row = 1
});
sut.DataRetrievalRequests.Add(new DataRetrievalRequest {
FieldName = "Test2", ColumnName = 2.ToString(), Row = 2
});
xlIOServiceMock.Setup(x => x.SaveWorkbook(It.IsAny <string>(), It.IsAny <ClosedXML.Excel.XLWorkbook>()))
.Callback <string, ClosedXML.Excel.XLWorkbook>((path, workbook) => generatedWorkbook = workbook)
.Returns(new Common.ReturnMessage(true, "workbook generated"));
await sut.BeginExtractionCommand.ExecuteAsync();
AssertCellValueForWorksheet(2, 1, "Postcode", generatedWorkbook);
AssertCellValueForWorksheet(2, 2, "456", generatedWorkbook);
}
private void SavePokemonData_Click(object sender, EventArgs e)
{
TempPokemon.Character = SeikakuList.Text;
TempPokemon.Ability = Tokusei_Set.Text;
TempPokemon.Item = Items.usrItem;
TempPokemon.Weapon = Weapon_Set.Text.Split('\n');
var book = new ClosedXML.Excel.XLWorkbook(Parameter_BD.PreSet.DBLocation + "SaveData.xlsx");
int next = 1;
while (book.Worksheet(1).Cell(next, 1).Value.ToString() != "")
{
next++;
}
if (SaveSequence(book.Worksheet(1), next))
{
book.Save();
//Init
isDialogue(false);
isDialogue(true);
InputName.Text = "";
}
}
public ClosedXML.Excel.XLWorkbook exporttoexcel(DataGridView dgv, DataTable Details, string Name, string date)
{
ClosedXML.Excel.XLWorkbook wbook = new ClosedXML.Excel.XLWorkbook();
try
{
DataTable dt = _objCommon.DataGridView2DataTable(dgv, "", 0);
Name = Name + date;
wbook.Worksheets.Add(dt, "Items");
//wbook.Worksheets.Add("Details");
wbook.Worksheets.Add(Details, "Details");
try
{
string folder = Environment.CurrentDirectory + "/ExcelReport/";
// If directory does not exist, don't even try
if (!Directory.Exists(folder))
{
Directory.CreateDirectory(folder);
}
wbook.Protect(true);
wbook.SaveAs(Environment.CurrentDirectory + "/ExcelReport/" + Name + ".xlsx");
}
catch (Exception ex)
{ MessageBox.Show(ex.Message); }
MessageBox.Show("Excelsheet saved");
}
catch (Exception ex)
{
MessageBox.Show("Please Check Closed.XML if missing");
int line = _error.GetLineNumber(ex);
_error.AddException(ex, "ExportToExcel");
}
return(wbook);
}
protected void ExportDataFromServer()
{
DataTable table = new DataTable();
using (var db = DBConfig.Connection)
{
db.Open();
MySqlDataReader reader = null;
string selectCmd = "Select p.animal_name, p.animal_age, p.animal_color, c.animal_type_name, b.compartment_name from passport p join animal а using (passport_id) join compartment b using (compartment_id) join animal_type c using (animal_type_id)";
MySqlCommand command = new MySqlCommand(selectCmd, db);
reader = command.ExecuteReader();
table.Load(reader);
ClosedXML.Excel.XLWorkbook wbook = new ClosedXML.Excel.XLWorkbook();
var ws = wbook.Worksheets.Add(table, "Животни");
ws.Row(1).InsertRowsAbove(1);
ws.Cell(1, 1).Value = "Всички животни";
var saveFileDialog = new SaveFileDialog
{
Filter = "Excel files|*.xlsx",
Title = "Save an Excel File"
};
saveFileDialog.ShowDialog();
if (!String.IsNullOrWhiteSpace(saveFileDialog.FileName))
{
wbook.SaveAs(saveFileDialog.FileName);
}
wbook.Dispose();
}
}
/// <summary>
/// Generate an Excel file from a list of SMBScanResult.
/// </summary>
/// <param name="hosts">A list of SMBScanResult objects</param>
/// <param name="filename">The name to use for the report (filename_SharesScanResult.xls)</param>
public static void GenerateSMBHostsReport(Dictionary <string, SMBHost> hosts, string filename)
{
int row = 1;
int col = 1;
var workbook = new ClosedXML.Excel.XLWorkbook();
var worksheet = workbook.Worksheets.Add("Scan Result");
row = WriteHeader(worksheet, row, col);
foreach (SMBHost host in hosts.Values)
{
if (host.hostSharesScanResult.Count > 0)
{
foreach (SMBScanResult scanResult in host.hostSharesScanResult.Values)
{
row = WriteShare(worksheet, scanResult.shareACL, row);
if (scanResult.shareSubDirectories.Count > 0)
{
row = WriteShareDirectories(worksheet, scanResult.shareSubDirectories, row, 1, scanResult.shareACL.share.shareInfo.shi1_remark);
}
}
}
else
{
//TODO : write host without shares
}
}
worksheet.Range("E2", "E" + row.ToString()).AddConditionalFormat().WhenContains("New ACE").Fill.SetBackgroundColor(ClosedXML.Excel.XLColor.Green);
worksheet.Range("E2", "E" + row.ToString()).AddConditionalFormat().WhenContains("No change").Fill.SetBackgroundColor(ClosedXML.Excel.XLColor.Orange);
worksheet.Range("E2", "E" + row.ToString()).AddConditionalFormat().WhenContains("ACE Removed").Fill.SetBackgroundColor(ClosedXML.Excel.XLColor.Red);
worksheet.Range("E2", "E" + row.ToString()).AddConditionalFormat().WhenContains("No ACE").Fill.SetBackgroundColor(ClosedXML.Excel.XLColor.Gray);
workbook.SaveAs(filename + "_ShareScanResult.xlsx");
}
private void btn_excel_Click(object sender, EventArgs e)
{
DataTable dt = new DataTable();
foreach (DataGridViewColumn column in dataGridView1.Columns)
{
if (column.Index <= 16)
{
dt.Columns.Add(column.HeaderText);
}
}
foreach (DataGridViewRow rows in dataGridView1.Rows)
{
dt.Rows.Add();
foreach (DataGridViewCell cell in rows.Cells)
{
if (cell.ColumnIndex <= 16)
{
dt.Rows[dt.Rows.Count - 1][cell.ColumnIndex] = cell.Value.ToString();
}
}
}
string folderPath = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments) + "\\POS" + "\\DayEnd Record Excel\\Dayend Detail\\";
if (!Directory.Exists(folderPath))
{
Directory.CreateDirectory(folderPath);
}
using (ClosedXML.Excel.XLWorkbook wb = new ClosedXML.Excel.XLWorkbook())
{
wb.Worksheets.Add(dt, "DayEnd Detail Report");
wb.SaveAs(folderPath + DateTime.Now.ToString("yyyy-MM-dd hh") + "Dayend.xlsx");
MessageBox.Show("Data Has Been Export To Document");
}
}
/// <summary>
/// Импорт категории из XLSX-файла.
/// </summary>
/// <param name="fileName"></param>
/// <param name="categoryId"></param>
public static void ImportCategory(string fileName, int categoryId)
{
LogHelper.Info(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType, "Начат импорт категории.");
importStartTime = DateTime.UtcNow;
try
{
ImportProgress = 0m;
Helper.LogInfo("Чтение XLSX-файла категории.");
var filePath = Path.Combine(ImportPath, fileName);
if (!System.IO.File.Exists(filePath))
{
throw new Exception("Файл не существует.");
}
using (var wb = new ClosedXML.Excel.XLWorkbook(filePath))
{
var contentService = ApplicationContext.Current.Services.ContentService;
var category = contentService.GetById(categoryId); //импортируемая категория
var categorySynonym = category.GetValue<string>("synonym").Trim();
if (String.IsNullOrEmpty(categorySynonym))
{
throw new Exception("Для категории не задан синоним.");
}
categorySynonym = categorySynonym.Trim();
var ws = wb.Worksheets.FirstOrDefault(x => String.Compare(x.Name.Trim(), categorySynonym, true) == 0); //импортируются данные с листа, имеющего имя — синоним названия категории
if (ws == null)
{
throw new Exception("Для категории с синонимом \"" + categorySynonym + "\" не найден соответствующий лист.");
}
var headerRow = ws.FirstRowUsed();
if (headerRow == null)
{
throw new Exception("Обрабатываемый лист файла пуст.");
}
var headerUsedRange = headerRow.RowUsed();
var brandHeaderCell = headerUsedRange.FirstCellUsed(x => x.GetString().ToLower().Contains("производитель"));
if (brandHeaderCell == null)
{
throw new Exception("Не найден столбец производителей.");
}
var brandColumnNumber = brandHeaderCell.Address.ColumnNumber;
var skuHeaderCell = headerUsedRange.FirstCellUsed(x => x.GetString().ToLower().Contains("артикул"));
if (skuHeaderCell == null)
{
throw new Exception("Не найден столбец артикулов.");
}
var skuColumnNumber = skuHeaderCell.Address.ColumnNumber;
var etechnoIdHeaderCell = headerUsedRange.FirstCellUsed(x => x.GetString().ToLower().Contains("id артикул"));
if (etechnoIdHeaderCell == null)
{
throw new Exception("Не найден столбец ID артикулов.");
}
var etechnoIdColumnNumber = etechnoIdHeaderCell.Address.ColumnNumber;
var visibilityHeaderCell = headerUsedRange.FirstCellUsed(x => x.GetString().ToLower().Contains("видимость"));
if (visibilityHeaderCell == null)
{
throw new Exception("Не найден столбец видимости.");
}
var visibilityColumnNumber = visibilityHeaderCell.Address.ColumnNumber;
var activityHeaderCell = headerUsedRange.FirstCellUsed(x => x.GetString().ToLower().Contains("актуальность"));
if (activityHeaderCell == null)
{
throw new Exception("Не найден столбец актуальности.");
}
var activityColumnNumber = activityHeaderCell.Address.ColumnNumber;
var accessoryColumnPattern = new Regex(@"^\s*аксессуар\s+\d+\s*$", RegexOptions.IgnoreCase);
var sameProductColumnPattern = new Regex(@"^\s*схожий\s+товар\s+\d+\s*$", RegexOptions.IgnoreCase);
//коллекция туплей (ячейка названия критерия, номер столбца начала критерия, номер столбца конца критерия, ячейки значений критерия)
var criteria = new List<Tuple<ClosedXML.Excel.IXLCell, int, int, IEnumerable<ClosedXML.Excel.IXLCell>>>();
var activityHeaderCellColumnNumber = activityHeaderCell.Address.ColumnNumber;
var criteriaHeaderCells = headerUsedRange.CellsUsed(x =>
{
var str = x.GetString();
return x.Address.ColumnNumber > activityHeaderCellColumnNumber && !accessoryColumnPattern.IsMatch(str) && !sameProductColumnPattern.IsMatch(str);
}).ToArray();
var criteriaValuesRow = headerRow.RowBelow();
int criteriaStartColumnNumber = -1, criteriaStopColumnNumber = -1;
if (criteriaHeaderCells.Any())
{
criteriaStartColumnNumber = criteriaHeaderCells.First().Address.ColumnNumber;
foreach (var criterionHeaderCell in criteriaHeaderCells)
{
int criterionStartColumnNumber, criterionStopColumnIndex;
var criterionMergedRange = ws.MergedRanges.FirstOrDefault(x => x.Contains(criterionHeaderCell));
if (criterionMergedRange == null)
{
//критерий из одного значения
criterionStartColumnNumber = criterionStopColumnIndex = criterionHeaderCell.Address.ColumnNumber;
}
else
{
//критерий из нескольких значений
criterionStartColumnNumber = criterionMergedRange.RangeAddress.FirstAddress.ColumnNumber;
criterionStopColumnIndex = criterionMergedRange.RangeAddress.LastAddress.ColumnNumber;
}
criteriaStopColumnNumber = criterionStopColumnIndex;
var criterion = Tuple.Create<ClosedXML.Excel.IXLCell, int, int, IEnumerable<ClosedXML.Excel.IXLCell>>(
criterionHeaderCell,
criterionStartColumnNumber,
criterionStopColumnIndex,
criteriaValuesRow.Cells(criterionStartColumnNumber, criterionStopColumnIndex)
);
criteria.Add(criterion);
}
}
//аксессуары и схожие товары импортируются из подстолбцов ID, которые на один смещены вправо относительно столбца аксессуара или схожего товара
//(первый подстолбец — sku, второй — etechnoId, договорились импортировать по etechnoId)
IEnumerable<int> accessoryColumnNumbers = headerUsedRange
.CellsUsed(x => x.Address.ColumnNumber > activityHeaderCellColumnNumber && accessoryColumnPattern.IsMatch(x.GetString()))
.Select(x => x.Address.ColumnNumber + 1);
IEnumerable<int> sameProductColumnNumbers = headerUsedRange
.CellsUsed(x => x.Address.ColumnNumber > activityHeaderCellColumnNumber && sameProductColumnPattern.IsMatch(x.GetString()))
.Select(x => x.Address.ColumnNumber + 1);
var productRows = ws.RowsUsed().Skip(2)/*.Where(x => !x.IsHidden)*/; //строки товаров; первые две строки заняты заголовком
//не ясно, нужно ли учитывать скрытость строк товаров (клиент не сообщил); сейчас у всех скрытых проставлена видимость 'N', то есть учитывать скрытость не нужно, видимость будет учтена по значению
Helper.LogInfo("Данные по критериям из файла прочитаны.");
var workAmount = productRows.Count();
var workDone = 0;
//пересохранение критериев в категории
var criterionIndex = 0;
var categoryCriteria = criteria.Select(x => new { text = x.Item1.GetString(), values = x.Item4.Select(y => new { id = criterionIndex++, text = y.GetString() }) });
var categoryCriteriaJson = Newtonsoft.Json.JsonConvert.SerializeObject(categoryCriteria);
if (category.GetValue<string>("criteria") != categoryCriteriaJson)
{
category.SetValue("criteria", categoryCriteriaJson);
contentService.SaveAndPublish(category);
}
Helper.LogInfo("Критерии фильтрации записаны в категорию.");
int productsSuccesses = 0, productsErrors = 0;
Helper.LogInfo("Заполнение внутренней структуры данных для внесения критериев в товары (длительная операция).");
var productsNode = contentService.GetRootContent().FirstOrDefault(x => x.ContentType.Alias == "catProducts");
//var productNodes = productsNode == null ? Enumerable.Empty<IContent>() : productsNode.Children().Where(x => x.ContentType.Alias == Product.ProductDocumentTypeAlias).ToArray();
//по какой-то причине, таящейся в недрах Убраки, метод Children() с бо́льшей вероятностью вызывает ошибку, связанную с SQL-сервером, чем метод Descendants()
var productNodes = productsNode == null ? Enumerable.Empty<IContent>() : productsNode.Descendants().Where(x => x.ContentType.Alias == Product.ProductDocumentTypeAlias).ToArray();
var products = MakeProductsByEtechnoIdsDictionary(productNodes);
IContent brandsNode;
IEnumerable<IContent> brandNodes;
try
{
brandsNode = contentService.GetRootContent().First(x => x.ContentType.Alias == "pubMainPage").Children().FirstOrDefault(x => x.ContentType.Alias == "pubBrands");
brandNodes = brandsNode.Children().Where(x => x.ContentType.Alias == "pubBrand");
}
catch
{
brandsNode = null;
brandNodes = Enumerable.Empty<IContent>();
}
var brands = MakeBrandsByNamesDictionary(brandNodes);
Helper.LogInfo("Заполнение внутренней структуры завершено.");
foreach (var productRow in productRows)
{
var etechnoId = productRow.Cell(etechnoIdColumnNumber).GetString();
try
{
IContent product;
if (!products.TryGetValue(etechnoId, out product))
{
throw new Exception("Товар не найден в базе.");
}
//получение значений свойств товара
var sku = productRow.Cell(skuColumnNumber).GetString();
if(sku != null)
{
sku = sku.Replace('_', ' ');
}
var brandName = productRow.Cell(brandColumnNumber).GetString();
IContent brand;
brands.TryGetValue(brandName, out brand);
var visibility = String.Compare(productRow.Cell(visibilityColumnNumber).GetString(), "y", true) == 0;
var criteriaValuesList = new List<int>();
if (criteriaStartColumnNumber >= 0 && criteriaStopColumnNumber >= 0)
{
var valueIndex = 0;
foreach (var valueCell in productRow.Cells(criteriaStartColumnNumber, criteriaStopColumnNumber))
{
if (!valueCell.IsEmpty())
{
criteriaValuesList.Add(valueIndex);
}
valueIndex++;
}
}
var criteriaValues = String.Join(",", criteriaValuesList);
var accessoryIds = accessoryColumnNumbers.Select(x =>
{
var accessoryEtechnoId = productRow.Cell(x).GetString();
if (!String.IsNullOrEmpty(accessoryEtechnoId))
{
IContent accessory;
if (products.TryGetValue(accessoryEtechnoId, out accessory))
{
return accessory.Id;
}
}
return 0;
})
.Where(x => x != 0)
.Distinct();
var accessoryIdsString = String.Join(",", accessoryIds);
var sameProductIds = sameProductColumnNumbers.Select(x =>
{
var sameProductEtechnoId = productRow.Cell(x).GetString();
if (!String.IsNullOrEmpty(sameProductEtechnoId))
{
IContent sameProduct;
if (products.TryGetValue(sameProductEtechnoId, out sameProduct))
{
return sameProduct.Id;
}
}
return 0;
})
.Where(x => x != 0)
.Distinct();
var sameProductIdsString = String.Join(",", sameProductIds);
//значения свойств товара получены, далее возможная запись
var isDirty = false;
if (product.GetValue<int>("category") != category.Id)
{
product.SetValue("category", category.Id);
isDirty = true;
}
var oldSku = product.GetValue<string>("sku");
if (oldSku == null) oldSku = String.Empty;
if (oldSku != sku)
{
product.SetValue("sku", sku);
isDirty = true;
}
var oldCriteria = product.GetValue<string>("criteria");
if (oldCriteria == null) oldCriteria = String.Empty;
if (oldCriteria != criteriaValues)
{
product.SetValue("criteria", criteriaValues);
isDirty = true;
}
if (brand != null && product.GetValue<int>("brand") != brand.Id)
{
product.SetValue("brand", brand.Id);
isDirty = true;
}
var oldAccessories = product.GetValue<string>("accessories");
if (oldAccessories == null) oldAccessories = String.Empty;
if (oldAccessories != accessoryIdsString)
{
product.SetValue("accessories", accessoryIdsString);
isDirty = true;
}
var oldSameProducts = product.GetValue<string>("sameProducts");
if (oldSameProducts == null) oldSameProducts = String.Empty;
if (oldSameProducts != sameProductIdsString)
{
product.SetValue("sameProducts", sameProductIdsString);
isDirty = true;
}
if (visibility)
{
//товар должен быть видимым
if (!product.HasPublishedVersion() || isDirty)
{
//товар распубликован или грязный, следует его опубликовать
contentService.SaveAndPublish(product);
}
else
{
//товар уже опубликован и не грязный, не нужно ничего делать
}
}
else
{
//товар должен быть распубликован
if (product.HasPublishedVersion() || isDirty)
{
//товар опубликован или грязный, следует его распубликовать,
//при этом изменения в нем сохраняются
contentService.UnPublish(product);
}
else
{
//товар не опубликован и не грязный, не нужно ничего делать
}
}
productsSuccesses++;
}
catch (Exception e)
{
productsErrors++;
Helper.LogError("Ошибка обработки товара (etechnoId: " + etechnoId + "). " + e.Message);
LogHelper.Error(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType, "Ошибка обработки товара (etechnoId: " + etechnoId + ").", e);
}
Helper.ImportProgress = Math.Round((decimal)(++workDone) / workAmount * 100, 2);
if (Helper.ImportProgress >= 100m)
{
Helper.ImportProgress = 99.99m; //не должно быть равно 100, чтобы клиентский контрол не перестал запрашивать прогресс
}
}
var message = "Обработка товаров завершена (успешно " + productsSuccesses + ", ошибок " + productsErrors + " из " + workAmount + ")";
Helper.LogInfo(message);
LogHelper.Debug(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType, message);
}
DisposeOfFile(filePath);
Helper.LogInfo("Обработка завершена. Чтобы страница отразила новые критерии категории, перезагрузите страницу.");
ImportProgress = 100m;
LogHelper.Info(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType, "Закончен импорт категории (ID " + categoryId + ").");
}
catch (Exception e)
{
Helper.LogError("Общая ошибка импорта категории. " + e.Message + (e.InnerException == null ? null : " " + e.InnerException.Message));
LogHelper.Error(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType, "Общая ошибка импорта категории (ID " + categoryId + ").", e);
throw; //Task после этого принимает ошибочный статус
}
}
public static void WriteDataTable(DataTable Data2Write,string FileName,string SheetName)
{
ClosedXML.Excel.XLWorkbook wb = new ClosedXML.Excel.XLWorkbook();
wb.Worksheets.Add(Data2Write, SheetName);
wb.SaveAs(FileName);
}
public async Task <IActionResult> ExportToExcelAsync(string fileName, string Type)
{
DataTable dt = new DataTable();
Random rand = new Random();
int guid = rand.Next();
string file = fileName;
fileName += guid.ToString() + ".xlsx";
if (Type.ToLower() == "country")
{
List <Country> countries = await _queryRepository.LoadCountries(null, null);
dt = Utilities.ToDataTable(countries);
}
else if (Type.ToLower() == "city")
{
List <City> cities = await _queryRepository.LoadCities(null, null, null);
dt = Utilities.ToDataTable(cities);
}
else if (Type.ToLower() == "area")
{
List <Area> areas = await _queryRepository.LoadAreas(null, null, null, null);
dt = Utilities.ToDataTable(areas);
}
else if (Type.ToLower() == "lockup")
{
List <LockUp> LockUps = await _queryRepository.LoadLockUps(null, null, null, null);
dt = Utilities.ToDataTable(LockUps);
}
else if (Type.ToLower() == "majorcode")
{
List <LockUp> LockUps = await _queryRepository.LoadLockUps(null, null, 0, null);
dt = Utilities.ToDataTable(LockUps);
}
else if (Type.ToLower() == "minorcode")
{
List <LockUp> LockUps = await _queryRepository.LoadLockUpsMinorCode(null, null, null, null);
dt = Utilities.ToDataTable(LockUps);
}
else if (Type.ToLower() == "bank")
{
List <Bank> banks = await _queryRepository.LoadBanks(null, null);
dt = Utilities.ToDataTable(banks);
}
else if (Type.ToLower() == "bankbranches")
{
List <BankBranches> bankBranches = await _queryRepository.LoadBankBranches(null, null, null);
dt = Utilities.ToDataTable(bankBranches);
}
else if (Type.ToLower() == "currency")
{
List <Currency> currencies = await _queryRepository.LoadCurrencies(null, null);
dt = Utilities.ToDataTable(currencies);
}
else
{
return(Ok(new { StatusCode = "Code Not Available" }));
}
ClosedXML.Excel.XLWorkbook wbook = new ClosedXML.Excel.XLWorkbook();
wbook.Worksheets.Add(dt, file);
// Prepare the response
HttpResponse httpResponse = Response;
httpResponse.Clear();
httpResponse.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
//Provide you file name here
Response.Headers["content-disposition"] = "attachment;filename=\"" + fileName + "\"";
// Flush the workbook to the Response.OutputStream
var memoryStream = new MemoryStream();
wbook.SaveAs(memoryStream);
memoryStream.Position = 0;
memoryStream.Close();
return(File(memoryStream.ToArray(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", fileName));
}
private void Generate(object sender, RoutedEventArgs e)
{
#if !DEBUG
try
#endif
{
if (ExcelPathfilename.Text.Trim().Length == 0)
{
MessageBox.Show($"Please specify Excel datasource Pathfilename"); return;
}
if (WordPathfilename.Text.Trim().Length == 0)
{
MessageBox.Show($"Please specify Word template Pathfilename"); return;
}
if (OutputFolder.Text.Trim().Length == 0)
{
MessageBox.Show($"Please specify output directory"); return;
}
if (!File.Exists(ExcelPathfilename.Text))
{
MessageBox.Show($"Unable to find [{ExcelPathfilename}]"); return;
}
if (!File.Exists(WordPathfilename.Text))
{
MessageBox.Show($"Unable to find [{WordPathfilename}]"); return;
}
if (!Directory.Exists(OutputFolder.Text))
{
MessageBox.Show($"Unable to find [{OutputFolder}] folder"); return;
}
var colNameToColIdx = new Dictionary <string, int>();
using (var wb = new ClosedXML.Excel.XLWorkbook(ExcelPathfilename.Text))
{
using (var ws = wb.Worksheets.First())
{
var colCnt = ws.ColumnsUsed().Count();
var rowCnt = ws.RowsUsed().Count();
for (int col = 1; col <= colCnt; ++col)
{
var cellValue = ws.Cell(1, col).Value;
if (!(cellValue is string))
{
continue;
}
var cellText = (string)cellValue;
var q = config.Mapping.FirstOrDefault(w => w.ColumnName.ToUpper() == cellText.ToUpper());
if (q != null)
{
colNameToColIdx.Add(q.ColumnName, col);
}
}
for (int row = 2; row <= rowCnt; ++row)
{
var suffix = string.Format("{0:0000}", row);
var outputFilename = Path.Combine(
OutputFolder.Text,
Path.GetFileNameWithoutExtension(WordPathfilename.Text) + $"-{suffix}.docx");
File.Copy(WordPathfilename.Text, outputFilename, true);
var docx = DocX.Load(outputFilename);
foreach (var x in colNameToColIdx)
{
var columnName = x.Key;
var colIndex = x.Value;
var token = config.Mapping.First(w => w.ColumnName == columnName).TokenToReplace;
var valueToInsert = ws.Cell(row, colIndex).Value;
if (valueToInsert as string == null)
{
continue;
}
docx.ReplaceText(token, (string)valueToInsert);
}
docx.Save();
}
}
}
}
#if !DEBUG
catch (Exception ex)
{
MessageBox.Show($"unhandled exception : {ex.Message}");
}
#endif
}
private async void WarningExport_Click(object sender, EventArgs e)
{
OpenFileDialog fdlg = new OpenFileDialog();
fdlg.Title = "Select your log file";
fdlg.InitialDirectory = location;
fdlg.Filter = "All files (*.*)|*.*|All files (*.*)|*.*";
fdlg.FilterIndex = 2;
fdlg.RestoreDirectory = true;
if (fdlg.ShowDialog() == DialogResult.OK)
{
fileLocated = fdlg.FileName;
}
else
{
return;
}
await Task.Delay(50);
statusLabel.Text = "Busy... Please Wait";
await Task.Delay(50);
string[] lines = File.ReadAllLines(fileLocated);
IEnumerable <string> selectLines = lines.Where(line => line.Contains("Warning:"));
List <String> lista = new List <String>();
foreach (var item in selectLines)
{
lista.Add(item.Substring(30));
}
using (var workBook = new ClosedXML.Excel.XLWorkbook())
{
List <string> linieFinal = new List <String>();
List <string> countFinal = new List <String>();
var worksheet = workBook.Worksheets.Add("Warning list");
var q = from x in lista
group x by x into g
let count = g.Count()
orderby count descending
select new { Value = g.Key, Count = count };
foreach (var x in q)
{
linieFinal.Add(x.Value);
countFinal.Add(x.Count.ToString());
}
for (int i = 2; i < linieFinal.Count; i++)
{
for (int j = 2; j < countFinal.Count; j++)
{
worksheet.Cell("A" + i).Value = countFinal[i - 1];
worksheet.Cell("B" + j).Value = linieFinal[j - 1];
}
}
worksheet.Cell("A1").Value = "Count";
worksheet.Cell("A1").Style.Font.SetBold().Font.FontSize = 16;
worksheet.Cell("B1").Value = "Warnings";
worksheet.Cell("B1").Style.Alignment.SetHorizontal(ClosedXML.Excel.XLAlignmentHorizontalValues.Center);
worksheet.Column(1).Style.Alignment.SetHorizontal(ClosedXML.Excel.XLAlignmentHorizontalValues.Center);
var range = worksheet.Range("B1:Z1");
int n = 10000;
for (int i = 2; i < n; i++)
{
var mergeLinesRange = worksheet.Range("B" + i + ":Z" + i);
mergeLinesRange.Merge();
}
worksheet.Columns("AA:XFD").Hide();
range.Merge().Style.Font.SetBold().Font.FontSize = 16;
workBook.SaveAs(location + "Warning Report.xlsx");
}
await Task.Delay(1000);
statusLabel.Text = "Done!";
await Task.Delay(1000);
statusLabel.Text = "Idle";
}
static public void MakeReport(DateTime dt1, DateTime dt2)
{
string fileName = Path.GetFileNameWithoutExtension(Path.GetTempFileName()) + ".xlsx";
FileStream fs = new FileStream(fileName, FileMode.Create, FileAccess.ReadWrite, FileShare.None);
fs.Write(Tasks.Properties.Resources.blank_Report, 0, Tasks.Properties.Resources.blank_Report.Length);
fs.Close();
ClosedXML.Excel.XLWorkbook xL = new ClosedXML.Excel.XLWorkbook(fileName);
string[,] list = Network.Task_List_Report(dt1.Ticks, dt2.Ticks);
string[] directions = Network.User_Directions();
if (list.Length == 0 || directions.Length == 0)
{
System.Windows.Forms.MessageBox.Show("Нет задач для отображения в отчете за указанный период", "Формирование отчета", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Information);
return;
}
for (int d = 2; d < directions.Length + 2; d++)
{
int line = 7;
int count = 1, tmpcount = 0;
xL.Worksheet(1).CopyTo("Направление " + directions[d - 2]);
xL.Worksheet(d).Cell(1, 1).Value = "Отчет по рабочему плану за " + Tools.DateTimeToString(dt1, "MMMM YYYY").ToUpper() + " года";
xL.Worksheet(d).Cell(2, 1).Value = Config.user_Fio + " (" + (directions[d - 2] == "" ? "Основное направление" : "Направление: " + directions[d - 2]) + ")";
xL.Worksheet(d).Cell(3, 1).Value = Config.user_Post;
// Основные задачи
for (int i = 0; i < list.Length / 12; i++)
{
long taskA = Convert.ToInt64(list[i, 3]);
if (taskA >= dt1.Ticks || list[i, 10] != directions[d - 2] || list[i, 11] != "0")
{
continue;
}
if (count != 1)
{
xL.Worksheet(d).Row(line - 1).InsertRowsBelow(1);
for (int j = 1; j <= 6; j++)
{
xL.Worksheet(d).Cell(line, j).Style = xL.Worksheet(d).Cell(7, j).Style;
}
}
xL.Worksheet(d).Cell(line, 1).Value = count.ToString();
xL.Worksheet(d).Cell(line, 2).Value = list[i, 0].Replace("%newline%", "\r\n");
xL.Worksheet(d).Cell(line, 3).Value = "с " + Tools.DateToString(Convert.ToInt64(list[i, 1]), "D MMMMm") + "\r\nпо " + Tools.DateToString(Convert.ToInt64(list[i, 2]), "D MMMMm");
xL.Worksheet(d).Cell(line, 5).Value = list[i, 5];
string progress = "", progressCom = "";
long taskE = Convert.ToInt64(list[i, 2]), taskF = Convert.ToInt64(list[i, 4]);
if (taskF != 0)
{
progress = "Выполнено" + "\r\n" + Tools.DateToString(taskF, "D MMMMm");
}
else if (list[i, 6] != "0")
{
progress = "Завершено на " + list[i, 6] + "%";
progressCom = "Выполнено:\r\n" + list[i, 7].Replace("%ns%", "\r\n") + "\r\n\r\nВ работе:\r\n" + list[i, 8].Replace("%ns%", "\r\n");
}
else if (taskE > DateTime.Now.Ticks)
{
progress = "В работе";
}
else
{
progress = "Не выполнено";
}
xL.Worksheet(d).Cell(line, 4).Value = progress;
if (progressCom != "")
{
xL.Worksheet(d).Cell(line, 4).Comment.AddText(progressCom);
xL.Worksheet(d).Cell(line, 4).Comment.Style.Alignment.SetAutomaticSize();
}
if (list[i, 9] != "")
{
int k = list[i, 9].LastIndexOf("%nm%");
if (k == -1)
{
xL.Worksheet(d).Cell(line, 6).Value = list[i, 9];
}
else
{
xL.Worksheet(d).Cell(line, 6).Value = list[i, 9].Substring(k + 4);
xL.Worksheet(d).Cell(line, 6).Comment.AddText(list[i, 9].Replace("%nm%", "\r\n\r\n"));
xL.Worksheet(d).Cell(line, 6).Comment.Style.Alignment.SetAutomaticSize();
}
}
count++;
line++;
}
if (count != 1)
{
xL.Worksheet(d).Row(line).InsertRowsBelow(2);
xL.Worksheet(d).Row(5).CopyTo(xL.Worksheet(d).Row(line)); line++;
xL.Worksheet(d).Row(7).CopyTo(xL.Worksheet(d).Row(line));
for (int i = 0; i < 6; i++)
{
xL.Worksheet(d).Cell(line, i + 1).Value = "";
}
xL.Worksheet(d).Cell(line - 1, 1).Value = "Дополнительные";
}
else
{
xL.Worksheet(d).Cell(line - 1, 1).Value = "Дополнительные";
}
tmpcount = count;
// Дополнительные задачи
for (int i = 0; i < list.Length / 12; i++)
{
long taskA = Convert.ToInt64(list[i, 3]);
if (taskA < dt1.Ticks || list[i, 10] != directions[d - 2] || list[i, 11] != "0")
{
continue;
}
if (count != tmpcount)
{
xL.Worksheet(d).Row(line).InsertRowsBelow(1);
for (int j = 1; j <= 6; j++)
{
xL.Worksheet(d).Cell(line, j).Style = xL.Worksheet(d).Cell(7, j).Style;
}
}
xL.Worksheet(d).Cell(line, 1).Value = count.ToString();
xL.Worksheet(d).Cell(line, 2).Value = list[i, 0].Replace("%newline%", "\r\n");
xL.Worksheet(d).Cell(line, 3).Value = "с " + Tools.DateToString(Convert.ToInt64(list[i, 1]), "D MMMMm") + "\r\nпо " + Tools.DateToString(Convert.ToInt64(list[i, 2]), "D MMMMm");
xL.Worksheet(d).Cell(line, 5).Value = list[i, 5];
string progress = "", progressCom = "";
long taskE = Convert.ToInt64(list[i, 2]), taskF = Convert.ToInt64(list[i, 4]);
if (taskF != 0)
{
progress = "Выполнено" + "\r\n" + Tools.DateToString(taskF, "D MMMMm");
}
else if (list[i, 6] != "0")
{
progress = "Завершено на " + list[i, 6] + "%";
progressCom = "Выполнено:\r\n" + list[i, 7].Replace("%ns%", "\r\n") + "\r\n\r\nВ работе:\r\n" + list[i, 8].Replace("%ns%", "\r\n");
}
else
{
progress = "Не выполнено";
}
xL.Worksheet(d).Cell(line, 4).Value = progress;
if (progressCom != "")
{
xL.Worksheet(d).Cell(line, 4).Comment.AddText(progressCom);
xL.Worksheet(d).Cell(line, 4).Comment.Style.Alignment.SetAutomaticSize();
}
if (list[i, 9] != "")
{
int k = list[i, 9].LastIndexOf("%nm%");
if (k == -1)
{
xL.Worksheet(d).Cell(line, 6).Value = list[i, 9];
}
else
{
xL.Worksheet(d).Cell(line, 6).Value = list[i, 9].Substring(k + 4);
xL.Worksheet(d).Cell(line, 6).Comment.AddText(list[i, 9].Replace("%nm%", "\r\n\r\n"));
xL.Worksheet(d).Cell(line, 6).Comment.Style.Alignment.SetAutomaticSize();
}
}
line++;
count++;
}
if (count != tmpcount)
{
xL.Worksheet(d).Row(line).InsertRowsBelow(2);
xL.Worksheet(d).Row(5).CopyTo(xL.Worksheet(d).Row(line)); line++;
xL.Worksheet(d).Row(7).CopyTo(xL.Worksheet(d).Row(line));
for (int i = 0; i < 6; i++)
{
xL.Worksheet(d).Cell(line, i + 1).Value = "";
}
xL.Worksheet(d).Cell(line - 1, 1).Value = "Контроль";
}
else if (count == 1)
{
xL.Worksheet(d).Cell(line - 2, 1).Value = "Контроль";
}
else
{
xL.Worksheet(d).Cell(line - 1, 1).Value = "Контроль";
}
tmpcount = count;
// Контроль
for (int i = 0; i < list.Length / 12; i++)
{
if (list[i, 10] != directions[d - 2] || list[i, 11] != "1")
{
continue;
}
if (count != tmpcount)
{
xL.Worksheet(d).Row(line).InsertRowsBelow(1);
for (int j = 1; j <= 6; j++)
{
xL.Worksheet(d).Cell(line, j).Style = xL.Worksheet(d).Cell(7, j).Style;
}
}
xL.Worksheet(d).Cell(line, 1).Value = count.ToString();
xL.Worksheet(d).Cell(line, 2).Value = list[i, 0].Replace("%newline%", "\r\n");
xL.Worksheet(d).Cell(line, 3).Value = "с " + Tools.DateToString(Convert.ToInt64(list[i, 1]), "D MMMMm") + "\r\nпо " + Tools.DateToString(Convert.ToInt64(list[i, 2]), "D MMMMm");
xL.Worksheet(d).Cell(line, 5).Value = list[i, 5];
string progress = "", progressCom = "";
long taskE = Convert.ToInt64(list[i, 2]), taskF = Convert.ToInt64(list[i, 4]);
if (taskF != 0)
{
progress = "Выполнено" + "\r\n" + Tools.DateToString(taskF, "D MMMMm");
}
else if (list[i, 6] != "0")
{
progress = "Завершено на " + list[i, 6] + "%";
progressCom = "Выполнено:\r\n" + list[i, 7].Replace("%ns%", "\r\n") + "\r\n\r\nВ работе:\r\n" + list[i, 8].Replace("%ns%", "\r\n");
}
else
{
progress = "Не выполнено";
}
xL.Worksheet(d).Cell(line, 4).Value = progress;
if (progressCom != "")
{
xL.Worksheet(d).Cell(line, 4).Comment.AddText(progressCom);
xL.Worksheet(d).Cell(line, 4).Comment.Style.Alignment.SetAutomaticSize();
}
if (list[i, 9] != "")
{
int k = list[i, 9].LastIndexOf("%nm%");
if (k == -1)
{
xL.Worksheet(d).Cell(line, 6).Value = list[i, 9];
}
else
{
xL.Worksheet(d).Cell(line, 6).Value = list[i, 9].Substring(k + 4);
xL.Worksheet(d).Cell(line, 6).Comment.AddText(list[i, 9].Replace("%nm%", "\r\n\r\n"));
xL.Worksheet(d).Cell(line, 6).Comment.Style.Alignment.SetAutomaticSize();
}
}
line++;
count++;
}
if (count == tmpcount)
{
xL.Worksheet(d).Row(line - 1).Delete();
xL.Worksheet(d).Row(line - 1).Delete();
line -= 2;
}
line += 2;
xL.Worksheet(d).Cell(line, 1).Value = Tools.FioToShort(Config.user_Fio) + " ____________________ ";
}
xL.Worksheet(1).Delete();
xL.Worksheet(1).Name = "Основное направление";
xL.Save();
System.Diagnostics.Process p = new System.Diagnostics.Process();
p.StartInfo.FileName = fileName;
p.StartInfo.UseShellExecute = true;
p.Start();
System.Threading.Thread.Sleep(1000);
}
static void Main(string[] args)
{
ObservableCollection <Model.Yard_Container> yard_Containers = Model.Yard_Container.GetYardContainer();
//generate first pass import
//generate first pass special services
ObservableCollection <Model.Paid_Container> paid_Containers = Model.Paid_Container.GetPaidContainer();
ObservableCollection <Model.Extended_Container> ext_Containers = Model.Extended_Container.GetExtended_Containers();
//update Paidthruday of each yardcontainer using paid containers
#region update Paidthruday of each yardcontainer using paid containers
Parallel.ForEach(yard_Containers, (yardContainer) =>
{
try
{
int payment = 0;
string paidthruDate = null;
string plugin = null;
string plugout = null;
try
{ //try to get number of recorded payment, and recorded freeuntil
IEnumerable <Model.Paid_Container> list = paid_Containers.Where(paid => (yardContainer.ContainerNumber.Trim() == paid.ContainerNumber.Trim()) &&
yardContainer.Category == paid.Category &&
(
(yardContainer.Category.StartsWith("IMPRT") && yardContainer.ATA <= paid.SystemDate) ||
(yardContainer.Category.StartsWith("EXPRT") && yardContainer.TimeIn <= paid.SystemDate)));
payment = list.Count();
paidthruDate = list.FirstOrDefault().StorageEnd.ToString();
plugin = list.FirstOrDefault().PlugIn.ToString();
plugout = list.FirstOrDefault().PlugOut.ToString();
if (payment > 0)
{
if (yardContainer.LastFreeDay.Value.Year < 2000)
{
yardContainer.LastFreeDay = list.FirstOrDefault().FreeUntil; //get LFD from GPS if none
}
yardContainer.IsArrastrePaid = true; //arrastre paid
}
}
catch { }
//if freeuntil != null then gatepass payment ; if null then it's manual invoiced
//return freeuntil(gatepass) or ldd+9(manual)
//yardContainer.PaidThruDate = yardContainer.LastFreeDay;
if (Convert.ToDateTime(yardContainer.LastFreeDay).Year < 2000) //no recorded lfd in N4
{
yardContainer.LastFreeDay = yardContainer.Category == "IMPRT" && Convert.ToDateTime(yardContainer.LDD).Year > 2000 ?
Convert.ToDateTime(yardContainer.LDD).AddDays(9) //free day = (ldd+9) iMPORT
: yardContainer.Category == "EXPRT"? Convert.ToDateTime(yardContainer.TimeIn).AddDays(9) //EXPORT T_IN+9
/* : yardContainer.Category == "STRGE"? Convert.ToDateTime(yardContainer.TimeIn).AddDays(4)*/ //STRGE T_IN+4
: Convert.ToDateTime("1970-01-01 00:00:00");
//yardContainer.PaidThruDate =
// !string.IsNullOrEmpty(paidthruDate) ? Convert.ToDateTime(paidthruDate)
// :
// yardContainer.LastFreeDay;
}
//return plugin
yardContainer.PlugIn =
yardContainer.Category == "IMPRT" && !string.IsNullOrEmpty(plugin) ?
Convert.ToDateTime(plugin) : //IMPRT
yardContainer.TimeIn; //assuming timein = plugin
//return plugout
yardContainer.PlugOut =
yardContainer.Category == "IMPRT" && !string.IsNullOrEmpty(plugout) ?
Convert.ToDateTime(plugout) : //IMPRT
yardContainer.PlugIn;
}
catch { }
});
#endregion
//EXTEND using Paidthruday manual invoice TIMEIN <= SYSDTTM
#region EXTEND using Paidthruday special services TIMEIN <= SYSDTTM
Parallel.ForEach(yard_Containers, (yardContainer) =>
{
try
{ //try extending each container using the first recorded payment within specified conditions; using its quantity as added days
DateTime ten_d_before_time_in = Convert.ToDateTime(yardContainer.ATA).AddDays(-10); //10 days before ATA
yardContainer.PaidThruDate =
yardContainer.Category.StartsWith("IMPRT") ?
Convert.ToDateTime(yardContainer.LastFreeDay).
AddDays(ext_Containers.Where(ext => (yardContainer.ContainerNumber.Trim() == ext.ContainerNumber.Trim()) &&
(yardContainer.ATA <= ext.SystemDate) &&
(ext.ChargeType.StartsWith("STOI"))).Sum(val => val.Quantity))
: yardContainer.Category.StartsWith("EXPRT") ?
Convert.ToDateTime(yardContainer.TimeIn).
AddDays(ext_Containers.Where(ext => (yardContainer.ContainerNumber.Trim() == ext.ContainerNumber.Trim()) &&
((ten_d_before_time_in <= ext.SystemDate) ||
(yardContainer.TimeIn <= ext.SystemDate)) &&
(ext.ChargeType.StartsWith("STOE"))).Sum(val => val.Quantity))
: Convert.ToDateTime("1970-01-01 00:00:00");
yardContainer.PlugOut =
yardContainer.Category.StartsWith("IMPRT") ?
Convert.ToDateTime(yardContainer.PlugOut).
AddDays(ext_Containers.Where(ext => (yardContainer.ContainerNumber.Trim() == ext.ContainerNumber.Trim()) &&
(yardContainer.ATA <= ext.SystemDate) &&
(ext.ChargeType.StartsWith("MCRFC1") ||
ext.ChargeType.StartsWith("MCRFC6") ||
ext.ChargeType.StartsWith("MCRFC4") ||
ext.ChargeType.StartsWith("MCRFC5"))).Sum(val => val.Quantity))
: yardContainer.Category.StartsWith("EXPRT") ?
Convert.ToDateTime(yardContainer.PlugOut).
AddDays(ext_Containers.Where(ext => (yardContainer.ContainerNumber.Trim() == ext.ContainerNumber.Trim()) &&
((ten_d_before_time_in <= ext.SystemDate) ||
(yardContainer.TimeIn <= ext.SystemDate)) &&
(ext.ChargeType.StartsWith("MCRFC1") ||
ext.ChargeType.StartsWith("MCRFC6") ||
ext.ChargeType.StartsWith("MCRFC4") ||
ext.ChargeType.StartsWith("MCRFC5"))).Sum(val => val.Quantity))
: Convert.ToDateTime("1970-01-01 00:00:00");
yardContainer.PlugOut =
yardContainer.Category.StartsWith("IMPRT") ?
Convert.ToDateTime(yardContainer.PlugOut).
AddHours(ext_Containers.Where(ext => (yardContainer.ContainerNumber.Trim() == ext.ContainerNumber.Trim()) &&
(yardContainer.ATA <= ext.SystemDate) &&
(ext.ChargeType.StartsWith("MCRFC2") ||
ext.ChargeType.StartsWith("MCRFC3"))).Sum(val => val.Quantity))
: yardContainer.Category.StartsWith("EXPRT") ?
Convert.ToDateTime(yardContainer.PlugOut).
AddHours(ext_Containers.Where(ext => (yardContainer.ContainerNumber.Trim() == ext.ContainerNumber.Trim()) &&
((ten_d_before_time_in <= ext.SystemDate) ||
(yardContainer.TimeIn <= ext.SystemDate)) &&
(ext.ChargeType.StartsWith("MCRFC2") ||
ext.ChargeType.StartsWith("MCRFC3"))).Sum(val => val.Quantity))
: Convert.ToDateTime("1970-01-01 00:00:00");
//remove if not extended
if (yardContainer.PaidThruDate == yardContainer.LastFreeDay)
{
yardContainer.PaidThruDate = Convert.ToDateTime("1970-01-01 00:00:00");
}
if (yardContainer.PaidThruDate == yardContainer.TimeIn)
{
yardContainer.PaidThruDate = Convert.ToDateTime("1970-01-01 00:00:00");
}
if (yardContainer.PlugOut == yardContainer.TimeIn)
{
yardContainer.PlugOut = Convert.ToDateTime("1970-01-01 00:00:00");
}
}
catch { }
});
#endregion
//EXTEND using Paidthruday special services blnum remark
//none yet
//output csv
#region output csv
Output.Output_ReportDataTable output = new Output.Output_ReportDataTable();
foreach (Model.Yard_Container yardContainer in yard_Containers)
{
output.AddOutput_ReportRow(Container_Number: yardContainer.ContainerNumber,
Category: yardContainer.Category,
Transit_State: yardContainer.TransitState,
Paid_Through_Date: Convert.ToDateTime(yardContainer.PaidThruDate),
Time_In: Convert.ToDateTime(yardContainer.TimeIn),
Plugout: Convert.ToDateTime(yardContainer.PlugOut),
IsArrastrePaid: yardContainer.IsArrastrePaid,
LastFreeDay: Convert.ToDateTime(yardContainer.LastFreeDay),
LastDischargeDate: Convert.ToDateTime(yardContainer.LDD),
isReefer: yardContainer.IsReefer,
Plugin: Convert.ToDateTime(yardContainer.PlugIn));
yardContainer.UpdateN4Unit();
}
ClosedXML.Excel.XLWorkbook wb = new ClosedXML.Excel.XLWorkbook();
wb.Worksheets.Add(output, "WorksheetName");
wb.SaveAs("output.xlsx");
#endregion
}
private List <Translation> ReadTranslationDataFromExcel(string fileName)
{
bool fileIsValid = true;
ClosedXML.Excel.XLWorkbook workbook;
try
{
workbook = new ClosedXML.Excel.XLWorkbook(fileName);
}
catch (Exception ex)
{
log.Error($"ERROR: {ex.Message}");
return(null);
}
log.Info($"Excel file opened: {fileName}");
var worksheet = workbook.Worksheets.FirstOrDefault();
log.Info($"Loading translation texts from sheet {worksheet.Name}");
log.Info($"Now checking, if the sheet contains the correct template...");
const int idColumnPSet = 1;
const int idColumnType = 2;
const int idColumnIfdGuid = 3;
const int idColumnIfcGlobalId = 4;
const int idColumnName = 5;
const int idColumnDefinition = 6;
const int idColumnLanguage = 7;
const int idColumnName_TL = 8;
const int idColumnDefinition_TL = 9;
if (worksheet.Cell(1, idColumnPSet).Value.ToString() != "PSet")
{
log.Error($" cell A1 has not the name \"PSet\" ok");
fileIsValid = false;
}
if (worksheet.Cell(1, idColumnType).Value.ToString() != "Type")
{
log.Error($" cell B2 has not the name \"Type\" ok");
fileIsValid = false;
}
if (worksheet.Cell(1, idColumnIfdGuid).Value.ToString() != "IfdGuid")
{
log.Error($" cell C1 has not the name \"IfdGuid\" ok");
fileIsValid = false;
}
if (worksheet.Cell(1, idColumnIfcGlobalId).Value.ToString() != "IfcGlobalId")
{
log.Error($" cell D1 has not the name \"IfcGlobalId\" ok");
fileIsValid = false;
}
if (worksheet.Cell(1, idColumnName).Value.ToString() != "Name")
{
log.Error($" cell E1 has not the name \"Name\" ok");
fileIsValid = false;
}
if (worksheet.Cell(1, idColumnDefinition).Value.ToString() != "Definition")
{
log.Error($" cell F1 has not the name \"Definition\" ok");
fileIsValid = false;
}
if (worksheet.Cell(1, idColumnLanguage).Value.ToString() != "Language")
{
log.Error($" cell G1 has not the name \"Language\" ok");
fileIsValid = false;
}
if (worksheet.Cell(1, idColumnName_TL).Value.ToString() != "Name_TL")
{
log.Error($" cell H1 has not the name \"Name_TL\" ok");
fileIsValid = false;
}
if (worksheet.Cell(1, idColumnDefinition_TL).Value.ToString() != "Definition_TL")
{
log.Error($" cell I1 has not the name \"Definition_TL\" ok");
fileIsValid = false;
}
if (!fileIsValid)
{
log.Error($"Please correct you template and try again...");
return(null);
}
log.Info($"The file has correct template - well done");
var firstRowUsed = worksheet.FirstRowUsed();
var translationRow = firstRowUsed.RowUsed();
translationRow = translationRow.RowBelow(); // Move to the next row (it now has the headers)
List <Translation> translations = new List <Translation>();
while (!translationRow.Cell(idColumnType).IsEmpty())
{
translations.Add(new Translation()
{
pset = translationRow.Cell(idColumnPSet).GetString(),
type = translationRow.Cell(idColumnType).GetString(),
ifdGuid = translationRow.Cell(idColumnIfdGuid).GetString(),
ifcGlobalId = translationRow.Cell(idColumnIfcGlobalId).GetString(),
name = translationRow.Cell(idColumnName).GetString(),
definition = translationRow.Cell(idColumnDefinition).GetString(),
language = translationRow.Cell(idColumnLanguage).GetString(),
name_TL = translationRow.Cell(idColumnName_TL).GetString(),
definition_tl = translationRow.Cell(idColumnDefinition_TL).GetString()
});
translationRow = translationRow.RowBelow();
}
return(translations);
}
protected override void Execute(CodeActivityContext context)
{
ClosedXML.Excel.XLWorkbook wb = new ClosedXML.Excel.XLWorkbook();
var ds = DataSet.Get(context);
var dt = DataTable.Get(context);
if (dt != null)
{
System.Data.DataTable table = dt;
var name = table.TableName;
if (string.IsNullOrEmpty(name))
{
name = "Sheet1";
}
if (!includeHeader.Get(context))
{
System.Data.DataTable t = new System.Data.DataTable()
{
TableName = name
};
var sheet = wb.Worksheets.Add(t, name);
sheet.FirstRow().FirstCell().InsertData(table.Rows);
var table2 = sheet.Tables.FirstOrDefault();
if (table2 != null)
{
table2.ShowAutoFilter = false;
table2.Theme = ClosedXML.Excel.XLTableTheme.FromName(Theme.Get(context));
}
}
else
{
var sheet = wb.Worksheets.Add(table, name);
var table2 = sheet.Tables.First();
table2.ShowAutoFilter = false;
table2.Theme = ClosedXML.Excel.XLTableTheme.FromName(Theme.Get(context));
}
}
else
{
int idx = 0;
foreach (System.Data.DataTable table in ds.Tables)
{
++idx;
var name = table.TableName;
if (string.IsNullOrEmpty(name))
{
name = "Sheet" + idx.ToString();
}
if (!includeHeader.Get(context))
{
System.Data.DataTable t = new System.Data.DataTable()
{
TableName = name
};
var sheet = wb.Worksheets.Add(t, name);
sheet.FirstRow().FirstCell().InsertData(table.Rows);
var table2 = sheet.Tables.FirstOrDefault();
if (table2 != null)
{
table2.ShowAutoFilter = false;
table2.Theme = ClosedXML.Excel.XLTableTheme.FromName(Theme.Get(context));
}
}
else
{
var sheet = wb.Worksheets.Add(table, name);
var table2 = sheet.Tables.First();
table2.ShowAutoFilter = false;
table2.Theme = ClosedXML.Excel.XLTableTheme.FromName(Theme.Get(context));
}
}
}
var filename = Filename.Get(context);
filename = Environment.ExpandEnvironmentVariables(filename);
wb.SaveAs(filename);
}
/// <summary>
/// Exports Data from Gridview to Excel 2007/2010/2013 format
/// </summary>
/// <param name="Title">Title to be shown on Top of Exported Excel File</param>
/// <param name="HeaderBackgroundColor">Background Color of Title</param>
/// <param name="HeaderForeColor">Fore Color of Title</param>
/// <param name="HeaderFont">Font size of Title</param>
/// <param name="DateRange">Specify if Date Range is to be shown or not.</param>
/// <param name="FromDate">Value to be stored in From Date of Date Range</param>
/// <param name="DateRangeBackgroundColor">Background Color of Date Range</param>
/// <param name="DateRangeForeColor">Fore Color of Date Range</param>
/// <param name="DateRangeFont">Font Size of Date Range</param>
/// <param name="gv">GridView Containing Data. Should not be a templated Gridview</param>
/// <param name="ColumnBackgroundColor">Background Color of Columns</param>
/// <param name="ColumnForeColor">Fore Color of Columns</param>
/// <param name="SheetName">Name of Excel WorkSheet</param>
/// <param name="FileName">Name of Excel File to be Created</param>
/// <returns>System.String.</returns>
public static string ExportDataTable2Excel(string Title, Color HeaderBackgroundColor, Color HeaderForeColor, int HeaderFont, bool DateRange, string FromDate, Color DateRangeBackgroundColor, Color DateRangeForeColor, int DateRangeFont, System.Data.DataTable gv, Color ColumnBackgroundColor, Color ColumnForeColor, string SheetName, string FileName)
{
System.Data.DataTable _table = gv;
if (gv != null)
{
//creating a new Workbook
var wb = new ClosedXML.Excel.XLWorkbook();
// adding a new sheet in workbook
var ws = wb.Worksheets.Add(SheetName);
//adding content
//Title
ws.Cell("A1").Value = Title;
// ws.Cell("A2").Value = "Date of Execution" + Utility.GetCurrentDateTime() ;
// Date
// ws.Cell("A2").Value = "Date :" + DateTime.Now.ToString("MM-dd-yyyy") + ' ' + "Total Run" + Constants.TOTALRUN + ' '+ "Total Pass:"******"Total Fail:" +' '+ Constants.FAILCOUNT + " Skipped:" + ' ' + Constants.OTHERSCOUNT+" Error/Abort:" + ' ' + Constants.ERRORCOUNT;
ws.Cell("A2").Value = "Date of execution:" + DateTime.Now.ToString("MM-dd-yyyy") + ":::::: Environment: " + Constants.Environment;
//add columns
string[] cols = new string[_table.Columns.Count];
for (int c = 0; c < _table.Columns.Count; c++)
{
var a = _table.Columns[c].ToString();
cols[c] = _table.Columns[c].ToString().Replace('_', ' ');
}
int rCnt = (ws.LastRowUsed().RowNumber());
char StartCharCols = 'A';
int StartIndexCols = rCnt + 1;
#region CreatingColumnHeaders
for (int i = 1; i <= cols.Length; i++)
{
if (i == cols.Length)
{
string DataCell = StartCharCols.ToString() + StartIndexCols.ToString();
ws.Cell(DataCell).Value = cols[i - 1];
ws.Cell(DataCell).WorksheetColumn().Width = cols[i - 1].ToString().Length + 10;
ws.Cell(DataCell).Style.Font.Bold = true;
ws.Cell(DataCell).Style.Fill.BackgroundColor = ClosedXML.Excel.XLColor.Orange;
ws.Cell(DataCell).Style.Font.FontColor = ClosedXML.Excel.XLColor.White;
}
else
{
string DataCell = StartCharCols.ToString() + StartIndexCols.ToString();
ws.Cell(DataCell).Value = cols[i - 1];
ws.Cell(DataCell).WorksheetColumn().Width = cols[i - 1].ToString().Length + 10;
ws.Cell(DataCell).Style.Font.Bold = true;
ws.Cell(DataCell).Style.Fill.BackgroundColor = ClosedXML.Excel.XLColor.Orange;
ws.Cell(DataCell).Style.Font.FontColor = ClosedXML.Excel.XLColor.White;
StartCharCols++;
}
}
#endregion
//Merging Header
string Range = "A1:" + StartCharCols.ToString() + "1";
ws.Range(Range).Merge();
ws.Range(Range).Style.Font.FontSize = HeaderFont;
ws.Range(Range).Style.Font.Bold = true;
ws.Range(Range).Style.Alignment.SetVertical(ClosedXML.Excel.XLAlignmentVerticalValues.Center);
ws.Range(Range).Style.Alignment.SetHorizontal(ClosedXML.Excel.XLAlignmentHorizontalValues.Center);
if (HeaderBackgroundColor != null && HeaderForeColor != null)
{
ws.Range(Range).Style.Fill.BackgroundColor = ClosedXML.Excel.XLColor.White;
ws.Range(Range).Style.Font.FontColor = ClosedXML.Excel.XLColor.Maroon;
}
//Style definitions for Date range
Range = "A2:" + StartCharCols.ToString() + "2";
ws.Range(Range).Merge();
ws.Range(Range).Style.Font.FontSize = 10;
ws.Range(Range).Style.Font.Bold = true;
ws.Range(Range).Style.Alignment.SetVertical(ClosedXML.Excel.XLAlignmentVerticalValues.Bottom);
ws.Range(Range).Style.Alignment.SetHorizontal(ClosedXML.Excel.XLAlignmentHorizontalValues.Left);
//border definitions for Columns
Range = "A3:" + StartCharCols.ToString() + "3";
ws.Range(Range).Style.Border.LeftBorder = ClosedXML.Excel.XLBorderStyleValues.Thin;
ws.Range(Range).Style.Border.RightBorder = ClosedXML.Excel.XLBorderStyleValues.Thin;
ws.Range(Range).Style.Border.TopBorder = ClosedXML.Excel.XLBorderStyleValues.Thin;
ws.Range(Range).Style.Border.BottomBorder = ClosedXML.Excel.XLBorderStyleValues.Thin;
char StartCharData = 'A';
int StartIndexData = 4;
//char StartCharDataCol = char.MinValue;
for (int i = 0; i < _table.Rows.Count; i++)
{
for (int j = 0; j < _table.Columns.Count; j++)
{
string DataCell = StartCharData.ToString() + StartIndexData;
var a = _table.Rows[i][j].ToString();
a = a.Replace(" ", " ");
a = a.Replace("&", "&");
//check if value is of integer type
int val = 0;
DateTime dt = DateTime.Now;
if (int.TryParse(a, out val))
{
ws.Cell(DataCell).Value = val;
}
//check if datetime type
else if (DateTime.TryParse(a, out dt))
{
ws.Cell(DataCell).Value = dt.ToShortDateString();
}
if (a == globalFunctions._fail)
{
ws.Cell(DataCell).Style.Font.FontColor = ClosedXML.Excel.XLColor.Red;
goto add;
}
else
{
goto add;
}
add:
ws.Cell(DataCell).Style.Font.FontSize = 9;
ws.Cell(DataCell).SetValue(a);
StartCharData++;
}
StartCharData = 'A';
StartIndexData++;
}
char LastChar = Convert.ToChar(StartCharData + _table.Columns.Count - 1);
int TotalRows = _table.Rows.Count + 3;
Range = "A4:" + LastChar + TotalRows;
ws.Range(Range).Style.Border.LeftBorder = ClosedXML.Excel.XLBorderStyleValues.Thin;
ws.Range(Range).Style.Border.RightBorder = ClosedXML.Excel.XLBorderStyleValues.Thin;
ws.Range(Range).Style.Border.TopBorder = ClosedXML.Excel.XLBorderStyleValues.Thin;
ws.Range(Range).Style.Border.BottomBorder = ClosedXML.Excel.XLBorderStyleValues.Thin;
wb.SaveAs(FileName);
return("Ok");
}
else
{
return("Invalid GridView. It is null");
}
}
public ActionResult Report(BaseReportModel model)
{
try
{
if (model.FromDate > model.ToDate)
{
ModelState.AddModelError("FromDate", CurrentUser.GetLanguageTextFromKey("From Date must be less than To Date."));
}
else if (model.Type == Commons.TypeCompanySelected) //Company
{
if (model.ListCompanys == null)
{
ModelState.AddModelError("ListCompanys", CurrentUser.GetLanguageTextFromKey("Please choose company."));
}
}
else //Store
{
if (model.ListStores == null)
{
ModelState.AddModelError("ListStores", CurrentUser.GetLanguageTextFromKey("Please choose store."));
}
}
if (!ModelState.IsValid)
{
return(View("Index", model));
}
//Get Selected Store
List <StoreModels> lstStore = new List <StoreModels>();
////if (model.Type == Commons.TypeCompanySelected) //Company
////{
//// lstStore = model.GetSelectedStoreCompany();
////}
////else //Store
////{
//// List<SelectListItem> vbStore = ViewBag.Stores;
//// lstStore = model.GetSelectedStore(vbStore);
////}
///////======= Updated 072018
if (model.Type == Commons.TypeCompanySelected) //Company
{
lstStore = listStoresInfoSession.Where(ww => model.ListCompanys.Contains(ww.CompanyId)).ToList();
model.ListStores = lstStore.Select(ss => ss.Id).ToList();
}
else //Store
{
lstStore = listStoresInfoSession.Where(ww => model.ListStores.Contains(ww.Id)).ToList();
}
//End Get Selected Store
//Export excel
var cashInOutReportFactory = new CashInOutReportFactory();
DateTimeHelper.GetDateTime(ref model);
ClosedXML.Excel.XLWorkbook wb = cashInOutReportFactory.ExportExcelCashOut(model, lstStore, Commons.CashOut);
ViewBag.wb = wb;
string sheetName = string.Format("Report_Cash_Out_{0}", DateTime.Now.ToString("MMddyyyy"));
Response.Clear();
Response.ClearContent();
Response.ClearHeaders();
Response.Charset = System.Text.UTF8Encoding.UTF8.WebName;
Response.ContentEncoding = System.Text.UTF8Encoding.UTF8;
if (model.FormatExport.Equals(Commons.Html))
{
Response.AddHeader("content-disposition", String.Format(@"attachment;filename={0}.html", sheetName.Replace(" ", "_")));
}
else
{
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("content-disposition", String.Format(@"attachment;filename={0}.xlsx", sheetName.Replace(" ", "_")));
}
using (var memoryStream = new MemoryStream())
{
wb.SaveAs(memoryStream);
if (model.FormatExport.Equals(Commons.Html))
{
Workbook workbook = new Workbook();
workbook.LoadFromStream(memoryStream);
//convert Excel to HTML
Worksheet sheet = workbook.Worksheets[0];
using (var ms = new MemoryStream())
{
sheet.SaveToHtml(ms);
ms.WriteTo(HttpContext.Response.OutputStream);
ms.Close();
}
}
else
{
memoryStream.WriteTo(HttpContext.Response.OutputStream);
}
memoryStream.Close();
}
HttpContext.Response.End();
return(View("Index", model));
}
catch (Exception ex)
{
_logger.Error("Cash In Summary Report Error: " + ex);
return(new HttpStatusCodeResult(400, ex.Message));
}
}
