1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
|
protected void ExportExcel()
{
Dictionary<string, string> friendlyNames;
using (DataPage dataPage = new DataPage())
{
friendlyNames = dataPage.FriendlyNames;
}
string friendlyName = "Transco";
string connectionString = ConnectionStrings.LoadConnectionString(ConnectionStringType.MainSqlServer);
using (SqlConnection sqlConnection = new SqlConnection(connectionString))
{
sqlConnection.Open();
using (SqlCommand getData = new SqlCommand("select top 2000 * from Transco", sqlConnection))
{
using (SqlDataReader dataReader = getData.ExecuteReader())
{
string templatesPath = Settings.ExcelTemplatesPath;
string emptyBookFilePath = Path.Combine(templatesPath, "book.xls");
string tableTemplatePath = Path.Combine(templatesPath, friendlyName + ".xls");
bool hasTemplate = File.Exists(tableTemplatePath);
string tempFileName = Path.GetTempFileName();
File.Delete(tempFileName);
File.Copy(hasTemplate ? tableTemplatePath : emptyBookFilePath, tempFileName);
string excelProvider = ConnectionStrings.LoadConnectionString(ConnectionStringType.ExcelProvider);
string excelConnectionString = string.Format(CultureInfo.InvariantCulture, excelProvider, tempFileName);
using (OleDbConnection oleConnection = new OleDbConnection(excelConnectionString))
{
oleConnection.Open();
string SafefriendlyName = friendlyName + "_SAF";
if (!hasTemplate)
{
string createTableQuery = string.Format(CultureInfo.InvariantCulture, @"create table {0} ([compte] nvarchar, [devise] nvarchar, [societe] nvarchar, [banque] nvarchar)", SafefriendlyName);
using (OleDbCommand createTable = new OleDbCommand(createTableQuery, oleConnection))
{
createTable.ExecuteNonQuery();
}
}
string addDataQuery = string.Format(CultureInfo.InvariantCulture, "insert into {0} ([Compte], [devise], [societe], [banque]) values (@compte, @devise, @societe, @banque)", SafefriendlyName);
while (dataReader.Read())
{
using (OleDbCommand addData = new OleDbCommand(addDataQuery, oleConnection))
{
addData.Parameters.AddWithValue("@compte", dataReader["COMPTE"]);
addData.Parameters.AddWithValue("@devise", dataReader["DEVISE"]);
addData.Parameters.AddWithValue("@societe", dataReader["SOCIETE"]);
addData.Parameters.AddWithValue("@banque", dataReader["BANQUE"]);
int countAffectedRows = addData.ExecuteNonQuery();
Debug.Assert(countAffectedRows == 1, string.Format(CultureInfo.InvariantCulture, "Wrong number of rows affected: {0} instead of 1.", countAffectedRows));
}
}
}
this.Response.Clear();
this.Response.Buffer = true;
this.Response.AddHeader("content-disposition", string.Format(CultureInfo.InvariantCulture, @"attachment; filename=""{0}.xls""", friendlyName.Replace(@"""", string.Empty)));
this.Response.ContentType = "application/vnd.ms-excel";
this.Response.WriteFile(tempFileName);
}
}
}
} |
