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(); } } } }