Como gerar arquivo excel xls ou xlsx usando ASP.NET MVC e C#


Como gerar um arquivo excel (XLS ou XSLX) usando ASP.NET MVC e C#, é uma pergunta recorrente e a popularidade dessa pergunta no StackOverflow comprova isso: http://stackoverflow.com/questions/151005/create-excel-xls-and-xlsx-file-from-c-sharp

Abaixo estou incluindo o exemplo de algumas dessas formas:

1- CSV

Criar um arquivo de texto com os itens separados por “;” e salvar como .csv

public ActionResult TesteCSV()
{
	StringBuilder sb = new StringBuilder();
	sb.Append("Nome;Telefone\r\n");
	sb.Append("Eduardo;11111\r\n");
	sb.Append("Coutinho;22222\r\n");

	HttpContext.Response.Clear();
	HttpContext.Response.AddHeader("content-disposition", string.Format("attachment;filename=Teste_{0}.csv", DateTime.Now.ToString("yyyy-MM-dd_HH-mm-ss")));

	HttpContext.Response.ContentType = "application/CSV";
	HttpContext.Response.ContentEncoding = System.Text.Encoding.Default;

	HttpContext.Response.Cache.SetCacheability(HttpCacheability.NoCache);

	HttpContext.Response.Write(sb.ToString());

	HttpContext.Response.End();

	return null;
}

Problemas:

  1. Formato: Um arquivo .csv renomeado não é um arquivo excel de verdade, embora o excel exiba o arquivo;
  2. Problema de Enconding no MAC: Notei que abrindo o Excel no MAC ocorre problema de encoding (a acentuação fica bagunçada). Fiz vários testes trocando o encoding, mas não encontrei um encoding compatível entre ambos;

2- Tabela HTML

Você pode gerar um arquivo HTML normal, com os dados e salvar o arquivo com a extensão “.xls”, o excel abre esse arquivo, mas antes apresenta uma mensagem de compatibilidade

public ActionResult TesteTableHTML()
{
    HttpContext.Response.Clear();
    HttpContext.Response.AddHeader("content-disposition", string.Format("attachment;filename=Teste_{0}.xls", DateTime.Now.ToString("yyyy-MM-dd_HH-mm-ss")));
     
    HttpContext.Response.ContentType = "application/ms-excel";
    HttpContext.Response.ContentEncoding = System.Text.Encoding.Default;
 
    StringBuilder sb = new StringBuilder();
    sb.Append("<style type=\"text/css\">\r\n");
    sb.Append(".tabHead\r\n");
    sb.Append("{\r\n");
    sb.Append("   background-color: #cccccc;\r\n");
    sb.Append("   border: solid 1px black;\r\n");
    sb.Append("}\r\n");
    sb.Append(".tabRow\r\n");
    sb.Append("{\r\n");
    sb.Append("   border: solid 1px black;\r\n");
    sb.Append("}\r\n");
    sb.Append("</style>\r\n\r\n");
 
 
    //Header
    sb.AppendFormat("<table>\r\n");
    sb.AppendFormat("<thead>\r\n");
    sb.AppendFormat("<tr>\r\n");
    sb.AppendFormat("\t<td class=\"tabHead\">Nome</td>\r\n");
    sb.AppendFormat("\t<td class=\"tabHead\">Telefone</td>\r\n");
    sb.AppendFormat("</tr>\r\n");
    sb.AppendFormat("</thead>\r\n");
    sb.AppendFormat("<tbody>\r\n");
 
    //Row
    sb.AppendFormat("<tr>\r\n");
    sb.AppendFormat("\t<td class=\"tabRow\">Eduardo</td>\r\n");
    sb.AppendFormat("\t<td class=\"tabRow\">11111</td>\r\n");
    sb.AppendFormat("</tr>\r\n");
 
    sb.AppendFormat("<tr>\r\n");
    sb.AppendFormat("\t<td class=\"tabRow\">Coutinho</td>\r\n");
    sb.AppendFormat("\t<td class=\"tabRow\">22222</td>\r\n");
    sb.AppendFormat("</tr>\r\n");
 
    //Footer
    sb.AppendFormat("</tbody>\r\n");
    sb.AppendFormat("</table>\r\n");
     
    HttpContext.Response.Cache.SetCacheability(HttpCacheability.NoCache);
    HttpContext.Response.Write(sb.ToString());
    HttpContext.Response.End();
 
    return null;
}

Problemas:

  1. O Excel abre o arquivo, no entanto antes ele mostra um erro: O arquivo que você está tentando abrir, “X”, está em um formato diferente do especificado pela extensão do arquivo. Verifique se o arquivo não está corrompido e se é de uma fonte confiável antes de abri-lo. Deseja abrir o arquivo agora?

  2. Problema de Enconding no MAC Notei que abrindo o Excel no MAC ocorre problema de encoding (a acentuação fica bagunçada).
    Fiz vários testes trocando o encoding, mas não encontrei um encoding compatível entre ambos.

3- ODBC

http://www.codeproject.com/Articles/8500/Reading-and-Writing-Excel-using-OLEDB

O problema do ODBC é que você vai ter usar 32 bits, então no AppPool da sua aplicação no servidor você precisa habilitar o “Enable 32-bit Application” nas opções avançadas.

4- EPPlus

http://epplus.codeplex.com/

Você pode gravar um arquivo e retorna-lo, o que é ruim pois os arquivo ficam armazenados em uma pasta do servidor e depois você tem que limpa-la:

public ActionResult TesteEPPlus1()
{
	DataTable dt = new DataTable();
	dt.Columns.Add("Nome");
	dt.Columns.Add("Telefone");
	dt.Rows.Add("eduardo", "11111");
	dt.Rows.Add("coutinho", "22222");

	FileInfo fileInfoTemplate = new FileInfo(System.Web.HttpContext.Current.Server.MapPath("~/Template/Template.xlsx"));

	OfficeOpenXml.ExcelPackage excel = new ExcelPackage(fileInfoTemplate);

	ExcelWorksheet worksheet = excel.Workbook.Worksheets.Add("teste");
	worksheet.Cells["A1"].LoadFromDataTable(dt, true);

	//salva arquivo
	string pastaTemp = System.Web.HttpContext.Current.Server.MapPath("~/_temp");

	FileInfo fileInfo = new FileInfo(System.IO.Path.Combine(pastaTemp, string.Format("{0}.xlsx", DateTime.Now.ToString("yyyy-MM-dd_HH-mm-ss_fff"))));
	excel.SaveAs(fileInfo);

	return File(fileInfo.FullName, "application/ms-excel", string.Format("Teste_{0}.xlsx", DateTime.Now.ToString("yyyy-MM-dd_HH-mm-ss")));
}

Ou então, usar o MemomyStream:

public ActionResult TesteEPPlus2()
{
	DataTable dt = new DataTable();
	dt.Columns.Add("Nome");
	dt.Columns.Add("Telefone");
	dt.Rows.Add("eduardo", "11111");
	dt.Rows.Add("coutinho", "22222");

	FileInfo fileInfoTemplate = new FileInfo(System.Web.HttpContext.Current.Server.MapPath("~/Template/Template.xlsx"));

	OfficeOpenXml.ExcelPackage excel = new ExcelPackage(fileInfoTemplate);

	ExcelWorksheet worksheet = excel.Workbook.Worksheets.Add("teste");
	worksheet.Cells["A1"].LoadFromDataTable(dt, true);

	System.IO.MemoryStream stream = new System.IO.MemoryStream();
	excel.SaveAs(stream);


	HttpContext.Response.Clear();
	HttpContext.Response.AddHeader("content-disposition", string.Format("attachment;filename=Teste_{0}.xlsx", DateTime.Now.ToString("yyyy-MM-dd_HH-mm-ss")));

	HttpContext.Response.ContentType = "application/vnd.ms-excel";
	HttpContext.Response.ContentEncoding = System.Text.Encoding.Default;

	HttpContext.Response.Cache.SetCacheability(HttpCacheability.NoCache);

	stream.WriteTo(Response.OutputStream);

	Response.End();

	return null;
}

Isso vai gerar um arquivo excel com base no modelo. Ao abrir o arquivo, procure pela planilha “teste” ela terá os dados inseridos.

Importante notar que as planilhas que já estavam no arquivo ficam vazias.

Problemas:

  1. Não funcionou com a extensão “xls” somente “xlsx” Quando tentei gerar um arquivo XLS o seguinte erro ocorreu:

    Exception: O arquivo contém dados corrompidos.
    linha: OfficeOpenXml.ExcelPackage excel = new ExcelPackage(fileInfoTemplate);
    Stack:
    [FileFormatException: O arquivo contém dados corrompidos.]
    MS.Internal.IO.Zip.ZipIOEndOfCentralDirectoryBlock.FindPosition(Stream archiveStream) +1388966
    MS.Internal.IO.Zip.ZipIOBlockManager.LoadEndOfCentralDirectoryBlock() +31
    MS.Internal.IO.Zip.ZipArchive.OpenOnStream(Stream stream, FileMode mode, FileAccess access, Boolean streaming) +222
    System.IO.Packaging.ZipPackage..ctor(Stream s, FileMode mode, FileAccess access, Boolean streaming) +1253961
    System.IO.Packaging.Package.Open(Stream stream, FileMode packageMode, FileAccess packageAccess, Boolean streaming) +1425729
    OfficeOpenXml.ExcelPackage.ConstructNewFile(Stream stream, String password) +640
    

5- ExcelLibrary

Biblioteca criada pelo Google, sem suporte a XSLX
https://code.google.com/p/excellibrary/

public ActionResult TesteExcelLibrary()
{
	DataSet ds = new DataSet("New_DataSet");

	DataTable dt = new DataTable("Teste1");
	dt.Columns.Add("Nome");
	dt.Columns.Add("Telefone");
	dt.Rows.Add("eduardo", "11111");
	dt.Rows.Add("coutinho", "22222");

	ds.Tables.Add(dt);

	MemoryStream stream = new MemoryStream();
	ExcelLibrary.DataSetHelper.CreateWorkbook(stream, ds);

	Response.Clear();
	Response.ContentType = "application/vnd.ms-excel";
	Response.AddHeader("content-disposition", string.Format("attachment;filename=Teste_{0}.xls", DateTime.Now.ToString("yyyy-MM-dd_HH-mm-ss")));

	stream.WriteTo(Response.OutputStream);

	Response.End();

	return null;
}

Problema:

  1. Erro exibido no excel: Ao abrir o arquivo no excel aparecia o erro: O Excel encontrou conteúdo ilegível em “[arquivo]”. Deseja recuperar o conteúdo desta pasta de trabalho? Se você confia na origem desta pasta de trabalho, clique em Sim. E depois a mensagem: O Excel recuperou fórmulas e valores de células, mas talvez alguns dados tenham sido perdidos. Encontrei o seguinte artigo sobre esse erro: Invalid or corrupt file (unreadable content) when opening in Excel 2010 E como diz no fórum, o problema é que o arquivo é muito pequeno, adicionando algumas linhas em branco no arquivo o problema não ocorre mais.
public ActionResult TesteExcelLibrary1()
{
	DataSet ds = new DataSet("New_DataSet");

	DataTable dt = new DataTable("Teste1");
	dt.Columns.Add("Nome");
	dt.Columns.Add("Telefone");
	dt.Rows.Add("eduardo", "11111");
	dt.Rows.Add("coutinho", "22222");

	//Resolve problema: O Excel encontrou conteúdo ilegível / Invalid or corrupt file (unreadable content)
	for (int i = 0; i &lt; 100; i++)
		dt.Rows.Add(" ", " ");

	ds.Tables.Add(dt);

	MemoryStream stream = new MemoryStream();
	ExcelLibrary.DataSetHelper.CreateWorkbook(stream, ds);

	Response.Clear();
	Response.ContentType = "application/vnd.ms-excel";
	Response.AddHeader("content-disposition", string.Format("attachment;filename=Teste_{0}.xls", DateTime.Now.ToString("yyyy-MM-dd_HH-mm-ss")));

	stream.WriteTo(Response.OutputStream);

	Response.End();

	return null;
}

E um exemplo sem Dataset:

public ActionResult TesteExcelLibrary2()
{
	Workbook workbook = new Workbook();
	Worksheet worksheet = new Worksheet("First Sheet");

	int row = 0;

	worksheet.Cells[row, 0] = new Cell((short)1);
	worksheet.Cells[row, 1] = new Cell(9999999);
	worksheet.Cells[row, 2] = new Cell((decimal)3.45);
	worksheet.Cells[row, 3] = new Cell("Text string");
	worksheet.Cells[row, 4] = new Cell("Second string");
	worksheet.Cells[row, 5] = new Cell(32764.5, "#,##0.00");
	worksheet.Cells[row, 6] = new Cell(DateTime.Now, @"YYYY\-MM\-DD");
	worksheet.Cells.ColumnWidth[0, 0] = 3000;

	//Resolve problema: O Excel encontrou conteúdo ilegível / Invalid or corrupt file (unreadable content)
	while (row &lt; 100)
	{
		row++;
		worksheet.Cells[row, 0] = new Cell(" ");
	}

	workbook.Worksheets.Add(worksheet);

	MemoryStream stream = new MemoryStream();
	workbook.Save(stream);

	Response.Clear();
	Response.ContentType = "application/vnd.ms-excel";
	Response.AddHeader("content-disposition", string.Format("attachment;filename=Teste_{0}.xls", DateTime.Now.ToString("yyyy-MM-dd_HH-mm-ss")));

	stream.WriteTo(Response.OutputStream);

	Response.End();

	return null;
}

Dicas:

  1. Tamanho das colunas: Para ajustar o tamanho das colunas, utilize:
	worksheet.Cells.ColumnWidth[0, 0] = 3000;
	worksheet.Cells.ColumnWidth[1, 1] = 8000;
	worksheet.Cells.ColumnWidth[2, 2] = 3000;
	
Se quiser fazer um ajuste de tamanho automático, tente pegar o length do maior valor escrito nessa coluna (você pode manter um array com o tamanho do número de colunas e se o length do conteúdo da linha atual for maior do que está nessa array atualiza-lo).

Nos meus testes, multiplicar o tamanho máximo da célula dessa coluna (quantidade de caracteres da célula com mais texto nessa coluna) por 500 e limitar um tamanho máximo de 20.000 trouxe uma apresentação bacana.

Problemas:

  1. Estilo da célula:

    Isso aqui não funciona:

	worksheet.Cells[0, 0].Style = new CellStyle();
	worksheet.Cells[0, 0].Style.BackColor = System.Drawing.Color.Yellow;
	
Aqui: <a href="http://stackoverflow.com/questions/3117227/how-can-i-change-cell-style-in-an-excel-file-with-excellibrary" target="_blank" rel="nofollow">http://stackoverflow.com/questions/3117227/how-can-i-change-cell-style-in-an-excel-file-with-excellibrary</a> diz que esse parâmetro está no fonte mas não é salvo no arquivo final.

6- NPOI

http://npoi.codeplex.com/

public ActionResult TesteNPOI()
{
	IWorkbook workbook = new XSSFWorkbook();
	ISheet sheet = workbook.CreateSheet("Plan 1");

	int rowNumer = 0;

	//---- HEADER

	IRow row = sheet.CreateRow(rowNumer);
	ICell cell;
				
	ICellStyle style = workbook.CreateCellStyle();
	style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.GREY_25_PERCENT.index;
	style.FillPattern = FillPatternType.SOLID_FOREGROUND;
	
	cell = row.CreateCell(0);
	cell.SetCellValue("Nome");
	cell.CellStyle = style;

	cell = row.CreateCell(1);
	cell.SetCellValue("Telefone");
	cell.CellStyle = style;

	cell = row.CreateCell(2);
	cell.SetCellValue("X");
	cell.CellStyle = style;

	cell = row.CreateCell(3);
	cell.SetCellValue("Y");
	cell.CellStyle = style;

	cell = row.CreateCell(4);
	cell.SetCellValue("Soma");
	cell.CellStyle = style;

	//---- row
	rowNumer++;
	row = sheet.CreateRow(rowNumer);
	row.CreateCell(0).SetCellValue("Eduardo");
	row.CreateCell(1).SetCellValue("111111");
	row.CreateCell(2).SetCellValue("10");
	row.CreateCell(3).SetCellValue("7");
	row.CreateCell(4).SetCellFormula("C2+D2");
	
	//---- row
	rowNumer++;
	row = sheet.CreateRow(rowNumer);
	row.CreateCell(0).SetCellValue("Coutinho");
	row.CreateCell(1).SetCellValue("222222");
	row.CreateCell(2).SetCellValue("1");
	row.CreateCell(3).SetCellValue("2");
	row.CreateCell(4).SetCellFormula("C3+D3");

	//Resolve problema: O Excel encontrou conteúdo ilegível / Invalid or corrupt file (unreadable content)
	while (rowNumer &lt; 20)
	{
		rowNumer++;
		row = sheet.CreateRow(rowNumer);
		row.CreateCell(0).SetCellValue(" ");
		row.CreateCell(1).SetCellValue(" ");
	}

	//Tamanho das colunas
	sheet.SetColumnWidth(0, 40 * 256);
	sheet.SetColumnWidth(1, 20 * 256);
	
	MemoryStream stream = new MemoryStream();
	workbook.Write(stream);

	return File(stream.ToArray(), //The binary data of the XLS file
		"application/vnd.ms-excel", //MIME type of Excel files
		string.Format("Teste_{0}.xlsx", DateTime.Now.ToString("yyyy-MM-dd_HH-mm-ss"))); //Suggested file name in the "Save as" dialog which will be displayed to the end user
}

Dicas:

  1. Alterar cor do background da célula:
	ICellStyle styleHeader = workbook.CreateCellStyle();
	styleHeader.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.GREY_25_PERCENT.index;
	styleHeader.FillPattern = FillPatternType.SOLID_FOREGROUND;

	cell = row.CreateCell(i);
	cell.SetCellValue("Texto");
	cell.CellStyle = styleHeader;
	
  1. Formatar número:
	ICellStyle styleDecimal = workbook.CreateCellStyle();
	styleDecimal.DataFormat = workbook.CreateDataFormat().GetFormat("#,##0.00_);(#,##0.00)");
	
  1. Inserir fórmula:
	var row = sheet.CreateRow(rowExcel);
	row.CreateCell(i).SetCellFormula("SUM(A1:)");
	
Para funções do excel, utilize o nome em inglês da função, por exemplo:

* "IF" ao invés de "SE"
* "VLOOKUP" ao invés de "PROCV"
* "SUM" ao invés de "SOMA"
  
E passe os parâmetros separados por vírgulas e não ponto e vírgula.

* "=VLOOKUP(A5,’Plan 1′!$A$2:$F$1000,6,FALSO)" ao invés de: "=VLOOKUP(A5;’Plan 1′!$A$2:$F$1000;6;FALSO)"

Problemas:

  1. Erro ao abrir quando salvo com a extensão “xls” ao invés de “xlsx”: Nos testes que fiz, salvando o arquivo como XLS, ao abrir o arquivo exibia mensagem: O arquivo que você está tentando abrir, “[arquivo]”, está em um formato diferente do especificado pela extensão do arquivo. Verifique se o arquivo não está corrompido e se é de uma fonte confiável antes de abri-lo. Deseja abrir o arquivo agora?

7- Interop

Dá para gerar o arquivo utilizando as próprias bibliotecas do Excel e Interop, assim:

http://msdn.microsoft.com/en-us/library/ms173186(v=vs.80).aspx

Problemas:

  1. Excel no servidor: Você vai precisar ter o Excel instalado no servidor

8- Outras opções

Conclusão

Como dá para notar, existem várias opções então você pode escolhe a que mais lhe agradar.

Atualmente a opção que mais gosto de utilizar é o NPOI porque ele tem mais possibilidades de configuração.

Baixe aqui o programa de exemplo: http://www.educoutinho.com.br/Downloads/GeraExcel.zip


Comentários