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; } }
Copyright © dotNetHell.it 2002-2025
Running on Windows Server 2008 R2 Standard, SQL Server 2012 & ASP.NET 3.5