/// <summary>
/// Export all data in a datatable into an Excel sheet
/// </summary>
/// <param name="DT">DataTable to export</param>
/// <param name="Header">true if headers have to be shown in Excel</param>
public static void apriFile(System.Data.DataTable DT, bool Header)
{
if (DT.Rows.Count == 0)
{
return;
}
Microsoft.Office.Interop.Excel.Application m_objExcel;
try {
m_objExcel = new Microsoft.Office.Interop.Excel.Application();
m_objExcel.Visible = true;
}
catch {
MessageBox.Show("Non è possibile eseguire l'esportazione in Excel. " +
"Excel non è installato su questo computer o è presente una versione " +
"non compatibile con l'oggetto COM: Microsoft Excel 9.0 Object Library",
"Esportazione non riuscita");
return;
}
Microsoft.Office.Interop.Excel.Workbook MyWorkbook = m_objExcel.Workbooks.Add(-4167); //Numero magico by Nino
//Microsoft.Office.Interop.Excel.WorksheetClass Myworksheet = new Microsoft.Office.Interop.Excel.WorksheetClass();
Microsoft.Office.Interop.Excel.Worksheet Myworksheet = (Microsoft.Office.Interop.Excel.Worksheet)MyWorkbook.Worksheets.get_Item(1);
int RowCount = DT.Rows.Count; //Numero Righe del datatable
int ColumnCount = DT.Columns.Count; //Numero Colonne del datatable
int Step = 0;
//Attento: Il Worksheet ha come base per le righe e colonne 1 (prima cella [1,1])
// Il Datatable ha come base 0. (prima cella [0,0])
//Aggiungo i titoli delle colonne
if (Header)
{
Microsoft.Office.Interop.Excel.Range Head = (Microsoft.Office.Interop.Excel.Range)Myworksheet.Range[
Myworksheet.Cells[1, 1],
Myworksheet.Cells[1, ColumnCount + 1]];
Head.EntireRow.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
Head.EntireRow.Font.Bold = true;
Step = 1;
}
DataRow [] Rows = DT.Select(null,
(string)DT.ExtendedProperties["ExcelSort"]);
//per ogni colonna del datatable:
int Excel_Col_Index = 0;
for (int Colonna = 0; Colonna < ColumnCount; Colonna++)
{
DataColumn Col = DT.Columns[Colonna];
string caption = (string)Col.ExtendedProperties["ExcelTitle"];
if (caption == null)
{
caption = DT.Columns[Colonna].Caption;
}
if (caption == "")
{
continue;
}
if ((Col.ExtendedProperties["ListColPos"] == null) &&
(caption.StartsWith(".")))
{
continue;
}
int ColonnaExcel = Excel_Col_Index + 1;
if (Col.ExtendedProperties["ListColPos"] != null)
{
ColonnaExcel = Convert.ToInt32(Col.ExtendedProperties["ListColPos"]);
}
if (ColonnaExcel == -1)
{
continue;
}
if (caption.StartsWith("."))
{
caption = caption.Remove(0, 1);
}
Excel_Col_Index++;
if (Col.ExtendedProperties["ExcelFormat"] != null)
{
try {
Microsoft.Office.Interop.Excel.Range ExcCol = (Microsoft.Office.Interop.Excel.Range)Myworksheet.Range[
Myworksheet.Cells[1 + Step, ColonnaExcel],
Myworksheet.Cells[RowCount + Step, ColonnaExcel]];
ExcCol.NumberFormat = Col.ExtendedProperties["ExcelFormat"].ToString();
}
catch (Exception E) {
MessageBox.Show(E.Message, "Errore");
}
}
Object [,] arr;
if (Header)
{
arr = new Object[RowCount + 1, 1];
arr[0, 0] = caption;
}
else
{
arr = new Object[RowCount, 1];
}
string Tag = "";
Tag = HelpForm.CompleteTag(Tag, Col);
for (int Riga = 0; Riga < RowCount; Riga++)
{
if (Rows[Riga][Colonna] == DBNull.Value)
{
arr[Riga + Step, 0] = "";
continue;
}
if (DT.Columns[Colonna].DataType == typeof(String))
{
arr[Riga + Step, 0] = "'" + HelpForm.StringValue(Rows[Riga][Colonna], Tag);
}
else
{
arr[Riga + Step, 0] = HelpForm.StringValue(Rows[Riga][Colonna], Tag);
}
}
//Formatta le colonne del Worksheet e le giustifica:
try{
Microsoft.Office.Interop.Excel.Range X = (Microsoft.Office.Interop.Excel.Range)Myworksheet.Range[
Myworksheet.Cells[1, ColonnaExcel],
Myworksheet.Cells[RowCount + Step, ColonnaExcel]];
X.Value2 = arr;
X.EntireColumn.AutoFit(); //Giustifica la colonna
}
catch (Exception E) {
MessageBox.Show(E.Message, "Errore");
}
}
}
