[csharp]
using System;
using System.Data;
using System.IO;
using System.Reflection;
using System.Text;
using System.Web;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
/*
Importante
Baixar as dll’s NPOI.dll e Ionic.Zip.dll
*/
namespace Jbkr.Utils
{
///<summary>
/// Classe para exportar listas para o Excel.
/// </summary>
public class ExportExcel
{
/// <summary>
/// Função de exportação.
/// </summary>
/// <param name="nomeArq">Nome que será dado ao arquivo criado.</param>
/// <param name="lista">Lista que será convertida para excel.</param>
/// Lista para exportação.
public void Exportar(string nomeArq, Array lista)
{
// Create new Excel Workbook
var workbook = new HSSFWorkbook();
// Create new Excel Sheet
var sheet = workbook.CreateSheet();
ICellStyle dateStyle = workbook.CreateCellStyle();
dateStyle.DataFormat = workbook.CreateDataFormat().GetFormat("dd/MM/yyyy");
string dados = string.Empty;
if (lista.Length < 65536)
{
PropertyInfo[] properties = lista.GetType().GetElementType().GetProperties();
DataTable dt = new DataTable();
// Create a header row
var headerRow = sheet.CreateRow(0);
for (int i = 0; i < properties.Length; i++)
{
headerRow.CreateCell(i).SetCellValue(properties[i].Name);
}
int rowNumber = 1;
for (int i = 0; i < lista.Length; i++)
{
// Create a new Row
var row = sheet.CreateRow(rowNumber++);
object o = lista.GetValue(i);
for (int j = 0; j < properties.Length; j++)
{
// Set the Values for Cells
if (properties[j].PropertyType.Name == "DateTime")
{
ICell cell = row.CreateCell(j);
cell.SetCellValue(properties[j].GetValue(o, null) == null ? DateTime.MinValue : Convert.ToDateTime(properties[j].GetValue(o, null)));
cell.CellStyle = dateStyle;
}
else if (properties[j].PropertyType.Name == "Int32")
row.CreateCell(j).SetCellValue(properties[j].GetValue(o, null) == null ? 0 : Convert.ToInt32(properties[j].GetValue(o, null)));
else if ((properties[j].PropertyType.Name == "Decimal") || (properties[j].PropertyType.Name == "Double"))
row.CreateCell(j).SetCellValue(properties[j].GetValue(o, null) == null ? 0D : Convert.ToDouble(properties[j].GetValue(o, null)));
else
row.CreateCell(j).SetCellValue(properties[j].GetValue(o, null) == null ? string.Empty : properties[j].GetValue(o, null).ToString());
}
}
// Write the Workbook to a memory stream
MemoryStream output = new MemoryStream();
workbook.Write(output);
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.Charset = "ISO-8859-1";
HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
HttpContext.Current.Response.AddHeader("content-disposition", "attachment;filename=" + nomeArq + ".xls");
HttpContext.Current.Response.BinaryWrite(output.ToArray());
HttpContext.Current.Response.End();
}
}
}
}
[/csharp]