PDA

Visualizza versione completa : HELP!!Generare export Excel da IEnumerable


giu_arg
23-12-2008, 19.14.11
Ciao a tutti, sono nuovo del forum e mi sono affacciato da poco alla programmazione con c# . Di recente mi si presentato un enorme problema, dovrei generare un file excel da un origine dati IEnumerable.
Il problema che in qualsiasi modo faccio non ottengo il risultato desiderato cio mi crea un file excel completamente vuoto.

Il codice della classe :


namespace HRA_WF.ToolExportToExcel
{
public class ExportToExcel
{
public void dataGridView2Excel(DataGridView dataGridView, string pFullPath_toExport, string nameSheet)
{
Object obj = dataGridView.DataSource;
System.Data.DataTable dt = new System.Data.DataTable();

if (dataGridView.DataSource is IEnumerable)
{

IEnumerable ie = (IEnumerable)obj;
dt = (System.Data.DataTable)DataTableFromIenumerable(ie );
}
else if(dataGridView.DataSource is DataSet)
{
if (((System.Data.DataSet)dataGridView.DataSource).Ta bles.Count > 0)
dt = ((System.Data.DataSet)dataGridView.DataSource).Tab les[0];
else
dt = new System.Data.DataTable();
}
else if(dataGridView.DataSource is System.Data.DataTable)
{
dt = (System.Data.DataTable)dataGridView.DataSource;
}
dataTable2Excel(dt, dataGridView, pFullPath_toExport, nameSheet);
}


public void dataTable2Excel(System.Data.DataTable pDataTable, DataGridView dgv, string pFullPath_toExport, string nameSheet)
{
string vFileName = Path.GetTempFileName();
FileSystem.FileOpen(1, vFileName, OpenMode.Output, OpenAccess.Default, OpenShare.Default, -1);

string sb = string.Empty;
//si existe datagridview, tomar de l los nombres de columnas y la visibilidad de las mismas
if (dgv != null)
{
foreach (DataColumn dc in pDataTable.Columns)
{
System.Windows.Forms.Application.DoEvents();
string title = string.Empty;
if (dgv.Columns[dc.Caption] != null)
{
title = dgv.Columns[dc.Caption].HeaderText;
sb += title + ControlChars.Tab;
}
}
}
else
{
foreach (DataColumn dc in pDataTable.Columns)
{
System.Windows.Forms.Application.DoEvents();
string title = string.Empty;

title = dc.Caption;
sb += title + ControlChars.Tab;
}
}
FileSystem.PrintLine(1, sb);
int i = 0;
foreach (DataRow dr in pDataTable.Rows)
{
System.Windows.Forms.Application.DoEvents();
i = 0;
sb = string.Empty;

foreach (DataColumn dc in pDataTable.Columns)
{

if (dgv != null && dgv.Columns[dc.Caption] != null)
{
System.Windows.Forms.Application.DoEvents();

sb = sb + (Information.IsDBNull(dr[i]) ? string.Empty : FormatCell(dr[i])) + ControlChars.Tab;
}
else if (dgv == null)
{
System.Windows.Forms.Application.DoEvents();

sb = sb + (Information.IsDBNull(dr[i]) ? string.Empty : FormatCell(dr[i])) + ControlChars.Tab;
}
i++;
}
FileSystem.PrintLine(1, sb);
}
FileSystem.FileClose(1);
TextToExcel(vFileName, pFullPath_toExport, nameSheet);
}


private string FormatCell(Object cell)
{
string TextToParse = Convert.ToString(cell);
return TextToParse.Replace(",",string.Empty);
}


private void TextToExcel(string pFileName, string pFullPath_toExport, string nameSheet)
{
System.Globalization.CultureInfo vCultura = System.Threading.Thread.CurrentThread.CurrentCultu re;
System.Threading.Thread.CurrentThread.CurrentCultu re = System.Globalization.CultureInfo.CreateSpecificCul ture("it-IT");
Microsoft.Office.Interop.Excel.Application Exc = new Microsoft.Office.Interop.Excel.Application();
Exc.Workbooks.OpenText(pFileName, Missing.Value, 1,
XlTextParsingType.xlDelimited,
XlTextQualifier.xlTextQualifierNone,
Missing.Value, Missing.Value,
Missing.Value, true,
Missing.Value, Missing.Value,
Missing.Value, Missing.Value,
Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value);
Workbook Wb = Exc.ActiveWorkbook;
Worksheet Ws = (Worksheet)Wb.ActiveSheet;
Ws.Name = nameSheet;
try
{

Ws.get_Range(Ws.Cells[1, 1], Ws.Cells[Ws.UsedRange.Rows.Count, Ws.UsedRange.Columns.Count]).AutoFormat(XlRangeAutoFormat.xlRangeAutoFormatCl assic1, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
}
catch
{
Ws.get_Range(Ws.Cells[1, 1], Ws.Cells[Ws.UsedRange.Rows.Count, Ws.UsedRange.Columns.Count]);
}

string tempPath = Path.GetTempFileName();

pFileName = tempPath.Replace("tmp", "xls");
File.Delete(pFileName);

if (File.Exists(pFullPath_toExport))
{
File.Delete(pFullPath_toExport);
}
Exc.ActiveWorkbook.SaveAs(pFullPath_toExport, 1, null, null, null, null, XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);

Exc.Workbooks.Close();
System.Runtime.InteropServices.Marshal.ReleaseComO bject(Ws);
Ws = null;
System.Runtime.InteropServices.Marshal.ReleaseComO bject(Wb);
Wb = null;
Exc.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComO bject(Exc);
Exc = null;
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
System.Threading.Thread.CurrentThread.CurrentCultu re = vCultura;
}



private System.Data.DataTable DataTableFromIenumerable(IEnumerable ien)
{
try
{
System.Data.DataTable dt = new System.Data.DataTable();
DataRow dr;
foreach (object obj in ien)
{
Type t = obj.GetType();
PropertyInfo[] pis = t.GetProperties();
if (dt.Columns.Count == 0)
{
foreach (PropertyInfo pi in pis)
{
if (!pi.Name.Equals("AsseY"))
{
dt.Columns.Add(pi.Name, pi.PropertyType);
}
}
}
dr = dt.NewRow();
foreach (PropertyInfo pi in pis)
{
object value = pi.GetValue(obj, null);
if (!pi.Name.Equals("AsseY"))
{
dr[pi.Name.ToString()] = value.ToString();
}
}
dt.Rows.Add(dr);
}
return dt;
}
catch (Exception ex)
{
Console.Write(ex.Message);
return null;
}
}

public static string readcell(Range oRange)
{
String result = string.Empty;
if (oRange != null)
{
if (oRange.Text != null)
{
result = oRange.Text.ToString();
}
}
return result;
}

}
}

La classe interessata "DataTableFromIenumerable".... ( Il resto del codice lo ho preso da un altro sito)

Sarei enormemente grato se qualcuno mi sa dare qualche dritta per fare questo export!!!!

GRAZIE!!

giu_arg
24-12-2008, 12.50.50
Grazie a tutti ma ho risolto il problema, se qualcuno vuole avere il codice completo me lo dica che lo posto

macprudens
04-06-2009, 01.51.04
SALVE,
POTRESTI CONVERTIRE IL CODICE IN VB 2005 ?

gRAZIE