private void btnTotalProfitCalculator_Click(object sender, RoutedEventArgs e)
{
try
{
var path = "total-profit.xlsx";
var mySqlCOntext = new MySqlModel();
var products = ProductsReportsLoader.GetReportsFromMySql(mySqlCOntext);
// TODO: Change it with entity framework
var vendorsProduct = SqliteParser.GetVendorsProducts();
ExcelGenerator.Generate(products, vendorsProduct, path);
MessageBox.Show("The total profit report was successfully generated!",
"Generated successfully",
MessageBoxButton.OK,
MessageBoxImage.Information);
Process.Start(path);
}
catch (Exception)
{
MessageBox.Show("Cannot generate the total profit report!",
"Generation failed",
MessageBoxButton.OK,
MessageBoxImage.Error);
}
}
public async Task <HttpResponseMessage> ExecuteAsync(CancellationToken cancellationToken)
{
var response = new HttpResponseMessage();
if (_Source != null)
{
ExcelGenerator.ExportToFile(_Source, _Title, "D:\\text.xls");
MemoryStream ms = ExcelGenerator.Generate(this._Source, this._Title, this._MapInfos);
response.Content = new ByteArrayContent(ms.ToArray());
response.Content.Headers.ContentDisposition = new ContentDispositionHeaderValue("attachment");
response.Content.Headers.ContentType = new MediaTypeHeaderValue("application/vnd.ms-excel");
response.Content.Headers.ContentDisposition.FileName = string.Format("{0}.xls", _Title);
response.Content.Headers.LastModified = new DateTimeOffset(DateTime.Now);
//response.Content.Headers.ContentLength = ms.Length;
response.StatusCode = HttpStatusCode.OK;
}
return(await Task.FromResult(response));
}
public byte[] GetAllUsersExcel()
{
var applicationUsers = _usersDbSet
.Include(user => user.WorkingHours)
.Include(user => user.JobPosition)
.Where(user => user.OrganizationId == 2)
.ToList();
using (var printer = new ExcelGenerator("Users"))
{
printer.CenterColumns(2, 6);
printer.AddHeaderRow(HeaderRow());
foreach (var user in applicationUsers)
{
printer.AddRow(UserToUserRow(user));
}
return(printer.Generate());
}
}
public static void ExportEmployee(int id)
{
Employee emp = new Employee();
using (SqlConnection dbConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["dbConnection"].ConnectionString))
{
if (dbConnection.State == ConnectionState.Open)
{
dbConnection.Close();
}
dbConnection.Open();
using (SqlCommand cmd = new SqlCommand("spGetEmployeeById", dbConnection))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@id", id);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
emp.ID = int.Parse(reader["ID"].ToString());
emp.Code = reader["Code"].ToString();
emp.First_Name = reader["First_Name"].ToString();
emp.Last_Name = reader["Last_Name"].ToString();
emp.Contact_No = reader["Contact_No"].ToString();
emp.Address = reader["Address"].ToString();
emp.Date_Hired = reader["Date_Hired"].ToString();
emp.Devices = GetEmployeeDevices(id);
}
}
}
ExcelGenerator excelGenerator = new ExcelGenerator(emp);
excelGenerator.Generate();
}
public ExcelFile BuildExcelFile(object data, FormMetadata metadata, string property)
{
var columns = new List <Column <object> >();
IEnumerable <object> array;
var value = data.GetPropertyValue(property);
if (value.GetType().GetGenericTypeDefinition() == typeof(PaginatedData <>))
{
var paginatedData = value.GetPropertyValue(nameof(PaginatedData <object> .Results));
array = (IEnumerable <object>)paginatedData;
}
else
{
array = (IEnumerable <object>)value;
}
var fieldMetadata = metadata.OutputFields
// Only select metadata for properties to be exported.
.SingleOrDefault(t => t.Id.Equals(property));
var dataItems = array.ToList();
if (fieldMetadata != null)
{
var propertyMetadatas = ((IEnumerable <OutputFieldMetadata>)fieldMetadata.CustomProperties["Columns"])
.OrderBy(t => t.OrderIndex);
foreach (var propertyMetadata in propertyMetadatas)
{
var collection = this.columnRenderer.GetColumns(propertyMetadata.Id, propertyMetadata, dataItems);
columns.AddRange(collection);
}
}
return(ExcelGenerator.Generate(property, columns, dataItems));
}
public TimeSheetController()
{
ExcelGenerator.Generate();
_db = new TimeSheetContext();
}
static void ToExcel(Dictionary <string, string> streetsNumbers)
{
IFileGenerator generator = new ExcelGenerator(Directory.GetCurrentDirectory(), "dachs");
generator.Generate(streetsNumbers);
}
public void Excel(ClientFilterViewModel filter)
{
var userId = UserLoggedId;
var username = UserLoggedUserName;
Task.Run(() =>
{
try
{
using (var context = new AppDbContext().WithUsername(username))
{
var query = filter.Apply(context);
var recordsTotal = query.Count();
// esse valor deve variar conforme o desempenho da consulta
// quanto menor o desempenho da consulta menor deve ser o valor
// para evitar timeout de banco de dados
// consultas com poucos registros ou alta performance talvez nem seja necessário
// paginação
var recordsByPage = 100;
var totalPages = (int)Math.Ceiling((double)recordsTotal / (double)recordsByPage);
var clients = new List <dynamic>();
for (int i = 0; i < totalPages; i++)
{
clients.AddRange(query.Skip(i).Take(recordsByPage).Select(c => new
{
c.Id,
c.Name,
c.CNPJ,
c.CPF,
c.Telephone,
c.Email,
c.Active
}).ToArray());
}
// Generate file
// Forma A: Simplesmente exporta o que veio da consulta
var excelGenerator = new ExcelGenerator("Customers", UploadHelper.GetDirectoryTempPathOrCreateIfNotExists(), username);
var fileName = excelGenerator.Generate(clients);
// Forma B: Formata os dados que veio da consulta para exportar
//excelGenerator.Generate(clients, (e) =>
//{
// e.Cells[1, 1].Value = "Id";
// e.Cells[1, 2].Value = "CPF/CNPJ";
// e.Cells[1, 3].Value = "E-mail";
// e.Cells[1, 4].Value = "Telefone";
// e.Cells[1, 5].Value = "Status";
//}, (e, i, data) =>
//{
// e.Cells[i, 1].Value = data.Id;
// e.Cells[i, 2].Value = data.CPF ?? data.CNPJ;
// e.Cells[i, 3].Value = data.Email;
// e.Cells[i, 4].Value = data.Telephone;
// e.Cells[i, 5].Value = data.Active ? "Ativo" : "Inativo";
//});
// Generate notification
var user = new User {
Id = userId
};
context.Users.Attach(user);
user.NotifyFileGenerated(fileName);
context.Configuration.ValidateOnSaveEnabled = false;
context.SaveChanges();
}
}
catch (Exception ex)
{
// TODA ROTINA ASSINCRONA PRECISA TER TRATAMENTO DE ERRO EXPLICITO
var logger = LogManager.GetLogger(GetType());
logger.Error(ex);
}
});
}
