Torna al Thread
public void EsportaDataTable(System.Data.DataTable tab, string pathFile, bool WriteColumnName, bool soloColonneConCaption)
{
try
{
//Avvio una istanza di Excel
Microsoft.Office.Interop.Excel.Application exc = new Microsoft.Office.Interop.Excel.Application();
try
{
Microsoft.Office.Interop.Excel.Workbooks workbooks = exc.Workbooks;
Microsoft.Office.Interop.Excel._Workbook workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel._Worksheet worksheet = (_Worksheet)workbook.Worksheets[1];
worksheet.Name = "Foglio1";
int startRow = 1;
int cntCol = 1;
string colName = "";
if (WriteColumnName)
{
cntCol = 1;
for (int c = 0; c < tab.Columns.Count; c++)
{
if (soloColonneConCaption)
{
if (!Convert.ToString("" + tab.Columns[c].Caption).Equals(""))
{
colName = Utility.ConvertColumnToChar(cntCol) + startRow.ToString();
Microsoft.Office.Interop.Excel.Range range = worksheet.get_Range(colName, System.Reflection.Missing.Value);
Object[] data = new Object[] { tab.Columns[c].Caption.Trim().ToUpper() };
range.GetType().InvokeMember("Value", System.Reflection.BindingFlags.SetProperty, null, range, data);
cntCol++;
}
}
else
{
colName = Utility.ConvertColumnToChar(cntCol) + startRow.ToString();
Microsoft.Office.Interop.Excel.Range range = worksheet.get_Range(colName, System.Reflection.Missing.Value);
Object[] data = new Object[] { tab.Columns[c].Caption.Trim().ToUpper() };
range.GetType().InvokeMember("Value", System.Reflection.BindingFlags.SetProperty, null, range, data);
cntCol++;
}
}
startRow++;
}
foreach (DataRow riga in tab.Rows)
{
cntCol = 1;
for (int c = 0; c < tab.Columns.Count; c++)
{
if (soloColonneConCaption)
{
if (!Convert.ToString("" + tab.Columns[c].Caption).Equals(""))
{
colName = Utility.ConvertColumnToChar(cntCol) + startRow.ToString();
Microsoft.Office.Interop.Excel.Range range = worksheet.get_Range(colName, System.Reflection.Missing.Value);
Object[] data = new Object[] { riga[c] };
range.GetType().InvokeMember("Value", System.Reflection.BindingFlags.SetProperty, null, range, data);
cntCol++;
}
}
else
{
colName = Utility.ConvertColumnToChar(cntCol) + startRow.ToString();
Microsoft.Office.Interop.Excel.Range range = worksheet.get_Range(colName, System.Reflection.Missing.Value);
Object[] data = new Object[] { riga[c] };
range.GetType().InvokeMember("Value", System.Reflection.BindingFlags.SetProperty, null, range, data);
cntCol++;
}
}
startRow++;
}
workbook.Close(true, pathFile, null);
}
catch (Exception)
{
}
finally
{
exc.Quit();
}
}
catch (Exception ex)
{
}
}
public class Utility
{
public static string ConvertColumnToChar(int numCol)
{
string res = "-";
try
{
if (numCol <= 26)
{
switch (numCol)
{
case 0:
res = "-";
break;
case 1:
res = "A";
break;
case 2:
res = "B";
break;
case 3:
res = "C";
break;
case 4:
res = "D";
break;
case 5:
res = "E";
break;
case 6:
res = "F";
break;
case 7:
res = "G";
break;
case 8:
res = "H";
break;
case 9:
res = "I";
break;
case 10:
res = "J";
break;
case 11:
res = "K";
break;
case 12:
res = "L";
break;
case 13:
res = "M";
break;
case 14:
res = "N";
break;
case 15:
res = "O";
break;
case 16:
res = "P";
break;
case 17:
res = "Q";
break;
case 18:
res = "R";
break;
case 19:
res = "S";
break;
case 20:
res = "T";
break;
case 21:
res = "U";
break;
case 22:
res = "V";
break;
case 23:
res = "W";
break;
case 24:
res = "X";
break;
case 25:
res = "Y";
break;
case 26:
res = "Z";
break;
default:
break;
}
}
else
{
int Param1 = Convert.ToInt32(numCol / 26);
int Param2 = numCol - 26;
res = ConvertColumnToChar(Param1) + ConvertColumnToChar(Param2);
}
}
catch (Exception)
{
}
return res;
}
}