Guest User

Untitled

a guest
May 24th, 2018
82
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 2.74 KB | None | 0 0
  1. // имитация данных
  2. var t = new DataTable();
  3. t.Columns.Add("STR1", typeof(string));
  4. t.Columns.Add("STR2", typeof(string));
  5. t.Columns.Add("NUM1", typeof(int));
  6.  
  7. for(int i=0; i<100; i++)
  8. {
  9. t.Rows.Add(string.Format("String#{0}", i), string.Format("---{0}---", i), i);
  10. }
  11.  
  12. var filename = Path.GetTempFileName()+ ".xlsx";
  13. File.WriteAllBytes(filename, Resources.res.TemplateForExcelExport_grid);
  14.  
  15. OleDbConnection connection = null;
  16. OleDbCommand command = null;
  17. string connectionString = "";
  18. string columns = "";
  19. string values = "";
  20. string[] columnNames = new string[t.Columns.Count];
  21. for (int i = 0; i < t.Columns.Count; i++) columnNames[i] = string.Format("F{0}", i+1);
  22.  
  23. string sheetName = "data$"; // этот пустой лист уже есть в xls-шаблоне
  24. try
  25. {
  26.  
  27. connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filename + ";Extended Properties="Excel 12.0 Xml; HDR=NO";";
  28. int iCount = 0;
  29.  
  30. using (connection = new OleDbConnection(connectionString))
  31. {
  32.  
  33. connection.Open();
  34. var colCount = 1;// columnNames.Length; - количесвто добавляемых колонок
  35. columns = "";
  36. for (int index = 0; index < colCount; index++)
  37. columns += index==0 ? "[" + columnNames[index] + "]" : ", [" + columnNames[index] + "]";
  38.  
  39. var rows = t.Rows.Count;
  40. for (int irow = 0; irow < rows; irow++)
  41. {
  42. var theValues = t.Rows[irow].ItemArray;
  43. values = "";
  44. for (int index = 0; index < colCount;index++)
  45. {
  46. var v = Convert.ToString(theValues[index]);
  47.  
  48. values += (index == 0) ? "'" + Regex.Replace(v, @"t|n|r", """) + "'" : ", '" + Regex.Replace(v, @"t|n|r", "") + "'";
  49. }
  50.  
  51. using (command = connection.CreateCommand())
  52. {
  53. command.CommandText = string.Format("Insert into [" + sheetName + "] ({0}) values({1})", columns, values);
  54. iCount += command.ExecuteNonQuery();
  55. }
  56. }
  57. }
  58.  
  59. // запускаем Excel
  60. Messages.InformationMessage(iCount.ToString());
  61. Process process = new Process();
  62. process.StartInfo = new ProcessStartInfo("excel", filename);
  63. process.Start();
  64. }
  65. catch (Exception ex)
  66. {
  67.  
  68. Messages.ExclamationMessage(ex.Message, "Error");
  69.  
  70. }
Add Comment
Please, Sign In to add comment