Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- // имитация данных
- var t = new DataTable();
- t.Columns.Add("STR1", typeof(string));
- t.Columns.Add("STR2", typeof(string));
- t.Columns.Add("NUM1", typeof(int));
- for(int i=0; i<100; i++)
- {
- t.Rows.Add(string.Format("String#{0}", i), string.Format("---{0}---", i), i);
- }
- var filename = Path.GetTempFileName()+ ".xlsx";
- File.WriteAllBytes(filename, Resources.res.TemplateForExcelExport_grid);
- OleDbConnection connection = null;
- OleDbCommand command = null;
- string connectionString = "";
- string columns = "";
- string values = "";
- string[] columnNames = new string[t.Columns.Count];
- for (int i = 0; i < t.Columns.Count; i++) columnNames[i] = string.Format("F{0}", i+1);
- string sheetName = "data$"; // этот пустой лист уже есть в xls-шаблоне
- try
- {
- connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filename + ";Extended Properties="Excel 12.0 Xml; HDR=NO";";
- int iCount = 0;
- using (connection = new OleDbConnection(connectionString))
- {
- connection.Open();
- var colCount = 1;// columnNames.Length; - количесвто добавляемых колонок
- columns = "";
- for (int index = 0; index < colCount; index++)
- columns += index==0 ? "[" + columnNames[index] + "]" : ", [" + columnNames[index] + "]";
- var rows = t.Rows.Count;
- for (int irow = 0; irow < rows; irow++)
- {
- var theValues = t.Rows[irow].ItemArray;
- values = "";
- for (int index = 0; index < colCount;index++)
- {
- var v = Convert.ToString(theValues[index]);
- values += (index == 0) ? "'" + Regex.Replace(v, @"t|n|r", """) + "'" : ", '" + Regex.Replace(v, @"t|n|r", "") + "'";
- }
- using (command = connection.CreateCommand())
- {
- command.CommandText = string.Format("Insert into [" + sheetName + "] ({0}) values({1})", columns, values);
- iCount += command.ExecuteNonQuery();
- }
- }
- }
- // запускаем Excel
- Messages.InformationMessage(iCount.ToString());
- Process process = new Process();
- process.StartInfo = new ProcessStartInfo("excel", filename);
- process.Start();
- }
- catch (Exception ex)
- {
- Messages.ExclamationMessage(ex.Message, "Error");
- }
Add Comment
Please, Sign In to add comment