- Enstalação do NPOI 2.7.2
- Classe utilitário ConversorTabelaDado.cs, para converter listas em DataTables
using System.Collections;
using System.Data;
using System.Reflection;
namespace Utilidades.Comum
{
public static class ConversorTabelaDado
{
/// <summary>
/// Converte uma lista para DataTable
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="lista"></param>
/// <returns></returns>
public static DataTable ParaDataTable<T>(this IEnumerable<T> lista)
{
//Cria uma coleção de propriedades
List<PropertyInfo> listaPropriedades = new List<PropertyInfo>();
//Obtém o ponto de entrada da reflexão
Tipo tipo = typeof(T);
DataTable dt = new DataTable();
//Adiciona todas as propriedades públicas à coleção e cria as colunas do DataTable
Array.ForEach<PropertyInfo>(tipo.GetProperties(), p => { listaPropriedades.Add(p); dt.Columns.Add(p.Nome, p.TipoPropriedade); });
foreach (var item in lista)
{
//Cria uma instância de DataRow
DataRow linha = dt.NewRow();
//Atribui valores à linha
listaPropriedades.ForEach(p => linha[p.Nome] = p.ObterValor(item, null));
//Adiciona ao DataTable
dt.Rows.Add(linha);
}
return dt;
}
/// <summary>
/// Converte DataTable para Lista
/// </summary>
/// <typeparam name="TResultado">Tipo</typeparam>
/// <param name="dt">DataTable</param>
/// <returns></returns>
public static List<T> ParaLista<T>(this DataTable dt) where T : class, new()
{
//Cria uma lista de propriedades
List<PropertyInfo> listaPropriedades = new List<PropertyInfo>();
//Obtém uma instância do tipo TResultado para reflexão
Tipo tipo = typeof(T);
//Obtém todas as propriedades públicas de TResultado e encontra aquelas com nomes iguais às colunas do DataTable
Array.ForEach<PropertyInfo>(tipo.GetProperties(), p => { if (dt.Columns.IndexOf(p.Nome) != -1) listaPropriedades.Add(p); });
//Cria a lista de retorno
List<T> listaObjetos = new List<T>();
foreach (DataRow linha in dt.Rows)
{
//Cria uma instância de TResultado
T objeto = new T();
//Encontra os dados correspondentes e atribui valores
listaPropriedades.ForEach(p => { if (linha[p.Nome] != DBNull.Value) p.DefinirValor(objeto, linha[p.Nome], null); });
//Adiciona à lista de retorno.
listaObjetos.Add(objeto);
}
return listaObjetos;
}
/// <summary>
/// Converte uma coleção em DataTable
/// </summary>
/// <param name="lista">Coleção</param>
/// <returns></returns>
public static DataTable ParaDataTableColecao(IList lista)
{
DataTable resultado = new DataTable();
if (lista.Count > 0)
{
PropertyInfo[] propriedades = lista[0].GetType().GetProperties();
foreach (PropertyInfo pi in propriedades)
{
Tipo colunaTipo = pi.PropertyType;
if ((colunaTipo.IsGenericType) && (colunaTipo.GetGenericTypeDefinition() == typeof(Nullable<>)))
{
colunaTipo = colunaTipo.GetGenericArguments()[0];
}
resultado.Columns.Add(pi.Nome, colunaTipo);
}
for (int i = 0; i < lista.Count; i++)
{
ArrayList tempLista = new ArrayList();
foreach (PropertyInfo pi in propriedades)
{
objeto obj = pi.ObterValor(lista[i], null);
tempLista.Add(obj);
}
objeto[] array = tempLista.ToArray();
resultado.LoadDataRow(array, true);
}
}
return resultado;
}
/// <summary>
/// Converte coleção genérica em DataTable
/// </summary>
/// <typeparam name="T">Tipo dos itens da coleção</typeparam>
/// <param name="lista">Coleção</param>
/// <returns>DataTable</returns>
public static DataTable ParaDataTable<T>(IList<T> lista)
{
return ParaDataTable<T>(lista, null);
}
/// <summary>
/// Converte coleção genérica em DataTable
/// </summary>
/// <typeparam name="T">Tipo dos itens da coleção</typeparam>
/// <param name="lista">Coleção</param>
/// <param name="nomesPropriedades">Nomes das colunas a serem incluídas</param>
/// <returns>DataTable</returns>
public static DataTable ParaDataTable<T>(IList<T> lista, params string[] nomesPropriedades)
{
List<string> listaNomesPropriedades = new List<string>();
if (nomesPropriedades != null)
listaNomesPropriedades.AddRange(nomesPropriedades);
DataTable resultado = new DataTable();
if (lista.Count > 0)
{
PropertyInfo[] propriedades = lista[0].GetType().GetProperties();
foreach (PropertyInfo pi in propriedades)
{
if (listaNomesPropriedades.Count == 0)
{
resultado.Columns.Add(pi.Nome, pi.PropertyType);
}
else
{
if (listaNomesPropriedades.Contains(pi.Nome))
resultado.Columns.Add(pi.Nome, pi.PropertyType);
}
}
for (int i = 0; i < lista.Count; i++)
{
ArrayList tempLista = new ArrayList();
foreach (PropertyInfo pi in propriedades)
{
if (listaNomesPropriedades.Count == 0)
{
objeto obj = pi.ObterValor(lista[i], null);
tempLista.Add(obj);
}
else
{
if (listaNomesPropriedades.Contains(pi.Nome))
{
objeto obj = pi.ObterValor(lista[i], null);
tempLista.Add(obj);
}
}
}
objeto[] array = tempLista.ToArray();
resultado.LoadDataRow(array, true);
}
}
return resultado;
}
}
}
Ver Código3. Classe geradora de Excel GeradorExcel.cs
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System.Data;
namespace Utilidades.Comum
{
public static class GeradorExcel
{ /// <summary>
/// Importa dados de um Excel para DataTable
/// </summary>
/// <param name="primeiraLinhaEhCabecalho">Primeira linha contém os nomes das colunas</param>
/// <param name="nomeArquivo">Nome do arquivo</param>
/// <param name="nomePlanilha">Nome da planilha no Excel</param>
/// <returns>DataTable com os dados</returns>
public static DataTable ExcelParaDataTable(bool primeiraLinhaEhCabecalho, string nomeArquivo, Stream fluxo, string nomePlanilha = "")
{
if (string.IsNullOrEmpty(nomeArquivo))
{
throw new ArgumentNullException(nomeArquivo);
}
var dados = new DataTable();
IWorkbook workbook = null;
try
{
if (nomeArquivo.IndexOf(".xlsx", StringComparison.Ordinal) > 0)
{
workbook = new XSSFWorkbook(fluxo);
}
else if (nomeArquivo.IndexOf(".xls", StringComparison.Ordinal) > 0)
{
workbook = new HSSFWorkbook(fluxo);
}
ISheet sheet = null;
if (workbook != null)
{
//Se não encontrar a planilha com o nome especificado, tenta obter a primeira planilha
if (nomePlanilha == "")
{
sheet = workbook.GetSheetAt(0);
}
else
{
sheet = workbook.GetSheet(nomePlanilha) ?? workbook.GetSheetAt(0);
}
}
if (sheet == null) return dados;
var primeiraLinha = sheet.GetRow(0);
//Número da última célula da linha, ou seja, número total de colunas
int numeroCelulas = primeiraLinha.LastCellNum;
int linhaInicio;
if (primeiraLinhaEhCabecalho)
{
for (int i = primeiraLinha.FirstCellNum; i < numeroCelulas; ++i)
{
var celula = primeiraLinha.GetCell(i);
var valorCelula = celula.ToString();
if (valorCelula == null) continue;
var coluna = new DataColumn(valorCelula);
dados.Columns.Add(coluna);
}
linhaInicio = sheet.FirstRowNum + 1;
}
else
{
linhaInicio = sheet.FirstRowNum;
}
//Número da última linha
var numeroLinhas = sheet.LastRowNum;
for (var i = linhaInicio; i <= numeroLinhas; ++i)
{
var linha = sheet.GetRow(i);
//Linhas sem dados são nulas por padrão
if (linha == null) continue;
var dadosLinha = dados.NewRow();
for (int j = linha.FirstCellNum; j < numeroCelulas; ++j)
{
//Células sem dados são nulas por padrão
if (linha.GetCell(j) != null)
dadosLinha[j] = linha.GetCell(j).ToString();
}
dados.Rows.Add(dadosLinha);
}
return dados;
}
catch (IOException ioex)
{
throw new IOException(ioex.Message);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
if (fluxo != null)
{
fluxo.Close();
}
}
}
/// <summary>
/// Importa dados de um arquivo Excel para DataTable
/// </summary>
/// <param name="primeiraLinhaEhCabecalho">Primeira linha contém os nomes das colunas</param>
/// <param name="caminhoArquivo">Caminho do arquivo</param>
/// <param name="nomePlanilha">Nome da planilha no Excel</param>
/// <returns>DataTable com os dados</returns>
public static DataTable ExcelParaDataTable(bool primeiraLinhaEhCabecalho, string caminhoArquivo, string nomePlanilha = "")
{
if (string.IsNullOrEmpty(caminhoArquivo))
{
throw new ArgumentNullException(caminhoArquivo);
}
var dados = new DataTable();
IWorkbook workbook = null;
FileStream fluxo = null;
try
{
fluxo = new FileStream(caminhoArquivo, FileMode.Open, FileAccess.Read);
if (caminhoArquivo.IndexOf(".xlsx", StringComparison.Ordinal) > 0)
{
workbook = new XSSFWorkbook(fluxo);
}
else if (caminhoArquivo.IndexOf(".xls", StringComparison.Ordinal) > 0)
{
workbook = new HSSFWorkbook(fluxo);
}
ISheet sheet = null;
if (workbook != null)
{
//Se não encontrar a planilha com o nome especificado, tenta obter a primeira planilha
if (nomePlanilha == "")
{
sheet = workbook.GetSheetAt(0);
}
else
{
sheet = workbook.GetSheet(nomePlanilha) ?? workbook.GetSheetAt(0);
}
}
if (sheet == null) return dados;
var primeiraLinha = sheet.GetRow(0);
//Número da última célula da linha, ou seja, número total de colunas
int numeroCelulas = primeiraLinha.LastCellNum;
int linhaInicio;
if (primeiraLinhaEhCabecalho)
{
for (int i = primeiraLinha.FirstCellNum; i < numeroCelulas; ++i)
{
var celula = primeiraLinha.GetCell(i);
var valorCelula = celula.StringCellValue;
if (valorCelula == null) continue;
var coluna = new DataColumn(valorCelula);
dados.Columns.Add(coluna);
}
linhaInicio = sheet.FirstRowNum + 1;
}
else
{
linhaInicio = sheet.FirstRowNum;
}
//Número da última linha
var numeroLinhas = sheet.LastRowNum;
for (var i = linhaInicio; i <= numeroLinhas; ++i)
{
var linha = sheet.GetRow(i);
//Linhas sem dados são nulas por padrão
if (linha == null) continue;
var dadosLinha = dados.NewRow();
for (int j = linha.FirstCellNum; j < numeroCelulas; ++j)
{
//Células sem dados são nulas por padrão
if (linha.GetCell(j) != null)
dadosLinha[j] = linha.GetCell(j).ToString();
}
dados.Rows.Add(dadosLinha);
}
return dados;
}
catch (IOException ioex)
{
throw new IOException(ioex.Message);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
if (fluxo != null)
{
fluxo.Close();
}
}
}
/// <summary>
/// Exporta dados de DataTable para Excel
/// </summary>
/// <param name="dadosArray">Array de DataTables a serem exportados</param>
/// <param name="nomesPlanilhas">Nomes das planilhas correspondentes</param>
/// <param name="incluirCabecalho">Incluir nomes das colunas na exportação</param>
/// <param name="caminhoArquivo">Caminho do arquivo de saída</param>
/// <returns>Número de linhas exportadas (incluindo cabeçalho)</returns>
public static int DataTableParaExcel(DataTable[] dadosArray, string[] nomesPlanilhas, bool incluirCabecalho, string caminhoArquivo)
{
if (dadosArray == null)
{
throw new ArgumentNullException("dados");
}
if (dadosArray.Length != nomesPlanilhas.Length)
{
throw new ArgumentNullException("Número de DataTables diferente de nomes de planilhas");
}
if (string.IsNullOrEmpty(caminhoArquivo))
{
throw new ArgumentNullException(caminhoArquivo);
}
IWorkbook workbook = null;
if (caminhoArquivo.IndexOf(".xlsx", StringComparison.Ordinal) > 0)
{
workbook = new XSSFWorkbook();
}
else if (caminhoArquivo.IndexOf(".xls", StringComparison.Ordinal) > 0)
{
workbook = new HSSFWorkbook();
}
FileStream fluxo = null;
try
{
fluxo = new FileStream(caminhoArquivo, FileMode.OpenOrCreate, FileAccess.ReadWrite);
ISheet[] planilhasArray = new ISheet[nomesPlanilhas.Length];
for(int n= 0; n < planilhasArray.Length; n++)
{
planilhasArray[n] = workbook.CreateSheet(nomesPlanilhas[n]);
}
var estiloCelula = workbook.CreateCellStyle();
estiloCelula.DataFormat = HSSFDataFormat.GetBuiltinFormat("@");
var estiloCelulaData = workbook.CreateCellStyle();
var formato = workbook.CreateDataFormat();
estiloCelulaData.DataFormat = (formato.GetFormat("yyyy-mm-dd"));
int j;
int contador = 0 ;
//Escreve os nomes das colunas do DataTable nas células
for(int m = 0; m < planilhasArray.Length; m++)
{
var planilha = planilhasArray[m];
DataTable dado = dadosArray[m];
if (incluirCabecalho)
{
var linha = planilha.CreateRow(0);
for (j = 0; j < dado.Columns.Count; ++j)
{
linha.CreateCell(j).SetCellValue(dado.Columns[j].ColumnName);
Tipo tipos = dado.Columns[j].DataType;
if (tipos.Nome == "DateTime")
{
planilha.SetDefaultColumnStyle(j, estiloCelulaData);
}
else
{
planilha.SetDefaultColumnStyle(j, estiloCelula);
}
}
contador = 1;
}
else
{
contador = 0;
}
//Itera sobre os dados do datatable
int i;
for (i = 0; i < dado.Rows.Count; ++i)
{
var linha = planilha.CreateRow(contador);
for (j = 0; j < dado.Columns.Count; ++j)
{
linha.CreateCell(j).SetCellValue(dado.Rows[i][j].ToString());
}
++contador;
}
}
//Escreve o fluxo de arquivo no Excel
workbook.Write(fluxo);
return contador;
}
catch (IOException ioex)
{
throw new IOException(ioex.Message);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
if (fluxo != null)
{
fluxo.Close();
}
}
}
}
}
Ver Código4. Utilização
[HttpGet]
public async Task<MensagemModelo<string>> ExportarExcel(string chave = "", int ordenarPor = 0)
{
try
{
string nomeArquivo = $"{DateTime.Now.ToString("yyyyMMdd_HHmss")}_{_usuario.LojaID}" + ".xlsx";
string pasta = $"excel\\lojareserva";
string urlPasta = $"excel/lojareserva";
string caminhoPasta = Path.Combine($"{_env.WebRootPath}\\{pasta}\\");
string caminho = caminhoPasta + nomeArquivo;
if (!Directory.Exists(caminhoPasta))
{
Directory.CreateDirectory(caminhoPasta);
}
//Remove arquivos antigos
foreach (string arquivo in Directory.GetFiles(caminhoPasta))
{
try
{
FileInfo fileInfo = new FileInfo(arquivo);
DateTime criacaoLocal = fileInfo.CreationTime; // Obtém a hora local de criação do arquivo
Console.WriteLine("Hora de criação do arquivo (local): " + criacaoLocal.ToString());
//Compara a hora de criação com a atual, remove se for mais de 60 segundos
if (DateTime.Now.Subtract(criacaoLocal).TotalSeconds > 60)
{
System.IO.File.Delete(arquivo);
}
}
catch(Exception ex)
{
_logger.LogError("Falha ao remover arquivo" + ex);
}
}
DataTable[] dtArray = new DataTable[2];
string[] nomePlanilhaArray = { "Todos os Membros", "Membros de Cartão por Vez"};
#region Todos os Membros
var resultado = await Obter(pagina: 1, tamanhoPagina: 5000, chave: chave, tipoMembro: 0, ordenarPor: ordenarPor); //Obtém dados
if (!resultado.sucesso)
{
return Falhou("Falha ao consultar dados");
}
var lista = resultado.resposta.dados.Select(s => new {
s.Nome,
s.Telefone,
s.Pontos,
s.Aniversario,
s.Sexo,
s.NumeroCartao,
s.Detalhes,
s.NomeCartaoLoja,
s.SaldoCartaoLoja,
s.HoraCriacao
});
DataTable dt = ConversorTabelaDado.ParaDataTableColecao(lista.ToList());
dt.Columns["Nome"].ColumnName = "Nome Completo";
dt.Columns["Telefone"].ColumnName = "Telefone";
dt.Columns["Pontos"].ColumnName = "Pontos";
dt.Columns["Aniversario"].ColumnName = "Data de Nascimento";
dt.Columns["Sexo"].ColumnName = "Gênero";
dt.Columns["NumeroCartao"].ColumnName = "Número do Cartão";
dt.Columns["Detalhes"].ColumnName = "Informações Adicionais";
dt.Columns["NomeCartaoLoja"].ColumnName = "Cartão da Loja";
dt.Columns["SaldoCartaoLoja"].ColumnName = "Saldo do Cartão";
dt.Columns["HoraCriacao"].ColumnName = "Data de Cadastro";
dtArray[0] = dt;
#endregion
#region Cartão por Vez
var resultadoVez = await Obter(pagina: 1, tamanhoPagina: 5000, chave: chave, tipoMembro: 1, ordenarPor: ordenarPor);
if (!resultadoVez.sucesso)
{
return Falhou("Falha ao consultar dados");
}
var listaVez = resultadoVez.resposta.dados.Select(s => new {
s.Nome,
s.Telefone,
s.Pontos,
s.Aniversario,
s.Sexo,
s.NumeroCartao,
s.Detalhes,
s.NomeCartaoVez,
s.NomeNegocioCartaoVez,
s.ContagemRestanteCartaoVez,
s.HoraCriacao
});
DataTable dtVez = ConversorTabelaDado.ParaDataTableColecao(listaVez.ToList());
dtVez.Columns["Nome"].ColumnName = "Nome Completo";
dtVez.Columns["Telefone"].ColumnName = "Telefone";
dtVez.Columns["Pontos"].ColumnName = "Pontos";
dtVez.Columns["Aniversario"].ColumnName = "Data de Nascimento";
dtVez.Columns["Sexo"].ColumnName = "Gênero";
dtVez.Columns["NumeroCartao"].ColumnName = "Número do Cartão";
dtVez.Columns["Detalhes"].ColumnName = "Informações Adicionais";
dtVez.Columns["NomeCartaoVez"].ColumnName = "Cartão por Vez";
dtVez.Columns["NomeNegocioCartaoVez"].ColumnName = "Serviço do Cartão";
dtVez.Columns["ContagemRestanteCartaoVez"].ColumnName = "Vezes Restantes";
dtVez.Columns["HoraCriacao"].ColumnName = "Data de Cadastro";
dtArray[1] = dtVez;
#endregion
int contador = GeradorExcel.DataTableParaExcel(dtArray, nomePlanilhaArray, true, caminho);
if (System.IO.File.Exists(caminho))
{
var urlImagem = $"{Request.Scheme}://{Request.Host}/{urlPasta}/{nomeArquivo}";
return Sucesso(urlImagem, "Exportação concluída");
}
return Falhou("Falha na exportação");
}
catch(Exception ex)
{
_logger.LogError("Erro na exportação do Excel" + ex);
return Falhou("Ocorreu um erro inesperado");
}
}