private int ItensToWorksheet(ExcelWorksheet worksheet,
IEnumerable <clsItem> itens2Export, frmExport frm, int row)
{
foreach (var item in itens2Export)
{
var col = 1; row++;
if (frm.FlatTable && frm.CamposSelecionados["Categoria"])
{
worksheet.Cells[row, col++].Value = item.Categoria;
}
if (frm.CamposSelecionados["Produto"])
{
worksheet.Cells[row, col++].Value = item.Produto;
}
if (frm.CamposSelecionados["Nº Corredor"])
{
worksheet.Cells[row, col++].Value = item.CorredorNum;
}
if (frm.CamposSelecionados["Corredor"])
{
worksheet.Cells[row, col++].Value = item.CorredorDescricao;
}
if (frm.CamposSelecionados["Qtd Prevista"])
{
worksheet.Cells[row, col].Value = item.QtdPrevista;
worksheet.Cells[row, col++].Style.Numberformat.Format = "#0.000";
}
if (frm.CamposSelecionados["Unidade"])
{
worksheet.Cells[row, col++].Value = item.Unidade;
}
if (frm.CamposSelecionados["Último Preço"])
{
worksheet.Cells[row, col].Value = item.PrecoUlt;
worksheet.Cells[row, col++].Style.Numberformat.Format = "R$ #,##0.00";
}
if (frm.CamposSelecionados["Marcas Sim"])
{
worksheet.Cells[row, col++].Value = item.MarcasSim;
}
if (frm.CamposSelecionados["Marcas Não"])
{
worksheet.Cells[row, col++].Value = item.MarcasNao;
}
if (frm.CamposSelecionados["Qtd Real"])
{
worksheet.Cells[row, col].Value = item.QtdReal;
worksheet.Cells[row, col++].Style.Numberformat.Format = "#0.000";
}
if (frm.CamposSelecionados["Marca"])
{
worksheet.Cells[row, col++].Value = item.Marca;
}
if (frm.CamposSelecionados["Preço"])
{
worksheet.Cells[row, col].Value = item.Preco;
worksheet.Cells[row, col].Style.Numberformat.Format = "R$ #,##0.00";
}
}
return(row);
}
private void ToolStripMenuItemExportExcel_Click(object sender, EventArgs e)
{
var newFile = ExportFile(@"Lista para Excel", "xlsx", "Arquivo Excel (*.xlsx)|*.xlsx");
if (newFile == null)
{
return;
}
var frm = new frmExport {
Classe = "Lista"
};
//frm.MdiParent = this.MdiParent;
if (frm.ShowDialog() == DialogResult.Cancel)
{
return;
}
using (var package = new ExcelPackage(newFile)) {
// add a new worksheet to the empty workbook
var worksheet = package.Workbook.Worksheets.Add("Lista");
var row = 0;
if (frm.Cabecalhos)
{
var col = 1; row++;
foreach (var kvp in frm.CamposSelecionados)
{
if (!kvp.Value || (!frm.FlatTable && kvp.Key == "Produto"))
{
continue;
}
worksheet.Cells[row, col++].Value = kvp.Key;
}
}
if (frm.FlatTable)
{
ItensToWorksheet(worksheet,
_itens.OrderBy(i => i.Produto).ToList(), frm, row);
}
else
{
var categorias = _itens.Select(i => i.Categoria).Distinct().OrderBy(c => c);
foreach (var cat in categorias)
{
//row++;
worksheet.Cells[++row, 1].Value = cat;
using (var range = worksheet.Cells[row, 1]) {
range.Style.Font.Bold = true;
range.Style.Fill.PatternType = ExcelFillStyle.Solid;
range.Style.Fill.BackgroundColor.SetColor(Color.LightCyan);
range.Style.Font.Color.SetColor(Color.Black);
}
row = ItensToWorksheet(worksheet,
_itens.Where(i => i.Categoria == cat).OrderBy(i => i.Produto).ToList(),
frm, row);
}
}
worksheet.Cells.AutoFitColumns(0); //Autofit columns for all cells
// lets set the header text
worksheet.HeaderFooter.OddHeader.CenteredText = "&16&\"Segoe UI,Regular Bold\" Lista de Compras";
// add the page number to the footer plus the total number of pages
worksheet.HeaderFooter.OddFooter.RightAlignedText =
$"Página {ExcelHeaderFooter.PageNumber} de {ExcelHeaderFooter.NumberOfPages}";
// add the sheet name to the footer
worksheet.HeaderFooter.OddFooter.CenteredText = ExcelHeaderFooter.SheetName;
// add the file path to the footer
worksheet.HeaderFooter.OddFooter.LeftAlignedText = ExcelHeaderFooter.FilePath + ExcelHeaderFooter.FileName;
//worksheet.PrinterSettings.RepeatRows = worksheet.Cells["1:2"];
//worksheet.PrinterSettings.RepeatColumns = worksheet.Cells["A:G"];
// Change the sheet view to show it in page layout mode
//worksheet.View.PageLayoutView = true;
// set some document properties
package.Workbook.Properties.Title = "Lista de Compras";
package.Workbook.Properties.Author = "Nelson Frick";
package.Workbook.Properties.Comments = "";
// set some extended property values
package.Workbook.Properties.Company = "Tyger Systems";
// set some custom property values
package.Workbook.Properties.SetCustomPropertyValue("Checked by", "Nelson Frick");
package.Workbook.Properties.SetCustomPropertyValue("AssemblyName", "EPPlus");
// save our new workbook and we are done!
package.Save();
}
}
