I have an Excel file template that my office use to filter their data, i need just update the first sheet "data", i have here my code, i spent more than 3 days to build this routine.
But i find out that is REALLY SLOW, now but i think there is a fastest way to do this.
What can i do?
private void EsportaInTemplateExcelVL(string NomeFile)
{
string fileNameTemp = NomeFile;//System.IO.Path.GetTempPath() Guid.NewGuid().ToString() ".xlsx";
string FileXls = Application.StartupPath @"\Support\PrintFormat\VL_LAB_updated.xls";
if (!File.Exists(FileXls))
{
MessageBox("File template VL_LAB_updated.xls not found");
return;
}
System.IO.File.Copy(FileXls, fileNameTemp);
Excel.Application excelApp = new Excel.Application();
Excel.Workbook wb = excelApp.Application.Workbooks.Open(fileNameTemp, Type.Missing, Type.Missing,
Type.Missing, Type.Missing,
Type.Missing, Type.Missing,
Type.Missing, Type.Missing,
Type.Missing, Type.Missing,
Type.Missing, Type.Missing,
Type.Missing, Type.Missing);
Excel.Sheets excelSheets = excelApp.Worksheets;// var ws = excelApp.Worksheets;
Excel.Worksheet excelWorksheet = (Excel.Worksheet)excelSheets.get_Item("DATA");
string[] col = { "A", "B", "C", "D", "E", "F", "G", "H", "I","J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X","Y", "Z" };
//Head Columns
for (int C = 0; C < dt.Columns.Count ; C )
{
string Cella = col[C].ToString() (0 1).ToString();//i parte da zero
Excel.Range excelCell = (Excel.Range)excelWorksheet.get_Range(Cella, Cella);
excelCell.Value = dt.Columns[C].ColumnName;
}
//all data
for (int i = 0; i < dt.Rows.Count ; i )
{
for (int C = 0; C < dt.Columns.Count ; C )
{
string Cella = col[C].ToString() (i 2).ToString();
Excel.Range excelCell = (Excel.Range)excelWorksheet.get_Range(Cella, Cella);
excelCell.Value = "'" dt.Rows[i][C].ToString();
}
}
wb.Close(true);
excelApp.Quit();
MessageBox("Done");
}
CodePudding user response：
You are using ranges but you are defining cells one by one. The good solution is to define cells by range (x rows and x columns) This is much faster in Excel
CodePudding user response：
I propose to use Data Engine to export your data to Excel as it is faster than excel object model. Take a look at following scenario if it fits your needs.
Step 1
Add a new project to your solution. In this demo I named it "MyExtensions".
Step 2
After creating the project, add a new class. In this demo I named it "Extensions".
using System;
using System.Data;
using System.Data.OleDb;
using System.Collections.Generic;
using System.Linq;
namespace MyExtensions
{
public static class Extensions
{
}
}
Step 3
Add following methods to this class.
Method 1
// This method returns Dictionary to convert
// .Net types to Excel types
static Dictionary<Type, string> GetTypeConverter(this OleDbConnection oleDbConnection)
{
string typeName = "TypeName";
string dataType = "DataType";
string columnSize = "ColumnSize";
Dictionary<Type, string> dictionary = new Dictionary<Type, string>();
var table = oleDbConnection.GetSchema("DataTypes");
foreach (DataRow row in table.Select().OrderBy(r => r[columnSize]))
{
var type = Type.GetType(row[dataType].ToString());
if (!dictionary.Keys.Contains(type))
{
dictionary.Add(type, row[typeName].ToString());
}
}
return dictionary;
}
Method 2
// This method is helper method. This action will be repeated.
// So it is better to make it an extension method.
public static DataColumn[] ToArray(this DataColumnCollection collection)
{
var columns = new DataColumn[collection.Count];
collection.CopyTo(columns, 0);
return columns;
}
Method 3
// This method adds a new Worksheet to Excel file
private static void CreateWorksheet(this DataTable dataTable, string name,
OleDbConnection oleDbConnection, Dictionary<Type, string> converter)
{
var columns = dataTable.Columns.ToArray();
var query = columns
.Select(c => $"{c.ColumnName} {converter[c.DataType]}");
var str1 = string.Join(", ", query);
var cmdText = $"CREATE TABLE [{name}] ({str1})";
using (var oleDbCommand = new OleDbCommand(cmdText))
{
oleDbCommand.Connection = oleDbConnection;
oleDbCommand.ExecuteNonQuery();
}
}
Method 4
// This method exports contents of DataTable.
private static void Export(this DataTable dataTable,
string worksheetName, OleDbConnection oleDbConnection)
{
var columns = dataTable.Columns.ToArray();
var str1 = string.Join(", ", columns.Select(c => $"[{c.ColumnName}]"));
var str2 = string.Join(", ", columns.Select(c => $"?"));
var cmdText = $"INSERT INTO [{worksheetName}] ({str1}) VALUES({str2})";
var parameters = columns.Select(c => new OleDbParameter()).ToArray();
using (var oleDbCommand = new OleDbCommand(cmdText))
{
oleDbCommand.Connection = oleDbConnection;
oleDbCommand.Parameters.AddRange(parameters);
var rows = dataTable.Select();
foreach (var row in rows)
{
for (int i = 0; i < columns.Length; i )
{
parameters[i].Value = row[i];
}
oleDbCommand.ExecuteNonQuery();
}
}
}
Method 5
// This method creates Worksheet in Excel file then
// exports contents of DataTable.
public static void Export(this DataTable dataTable,
string excel_FileName, string worksheetName)
{
var builder = new OleDbConnectionStringBuilder()
{
Provider = "Microsoft.ACE.OLEDB.12.0",
DataSource = excel_FileName
};
builder.Add("Extended Properties", "Excel 12.0");
using (var oleDbConnection = new OleDbConnection(builder.ConnectionString))
{
oleDbConnection.Open();
var converter = oleDbConnection.GetTypeConverter();
dataTable.CreateWorksheet(worksheetName, oleDbConnection, converter);
dataTable.Export(worksheetName, oleDbConnection);
}
}
Step 4
Create a new empty excel file in "My Documents" folder. Name it "Template.xlsx".
Step 5
Now we can use our API in a real situation. I created a console application and added the class library to references node of the project.
I added to my console application a new class to generate a sample DataTable to test to export it to excel.
using System;
using System.Data;
namespace ConsoleApp1
{
class Factory
{
public static DataTable GetSample()
{
DataTable dataTable = new DataTable()
{
TableName = "DataTable1"
};
dataTable.Columns.Add("Id", typeof(int));
dataTable.Columns.Add("LastName", typeof(string));
dataTable.Columns.Add("FirstName", typeof(string));
dataTable.Columns.Add("DateOfBirth", typeof(DateTime));
dataTable.Rows.Add(1, "John", "Robinson", new DateTime(1980, 6, 12));
dataTable.Rows.Add(2, "Sara", "Connor", new DateTime(1986, 2, 7));
return dataTable;
}
}
}
Here is Main method of the console application
using System;
namespace ConsoleApp1
{
using MyExtensions;
class Program
{
static void Main(string[] args)
{
var dataTable = Factory.GetSample();
var path = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);
var filePath = $"{path}\\Template.xlsx";
dataTable.Export(filePath, "Data");
}
}
}
I had it run and it was successful.