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
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
|
public static DataTable ListToDataTable(List<string> list)
{
DataTable dt = new DataTable();
dt.Columns.Add(new DataColumn("cloom", typeof(string)));
foreach (string t in list)
{
DataRow row = dt.NewRow();
row["cloom"] = t;
dt.Rows.Add(row);
}
return dt;
}
public static List<string> DataTableToList(DataTable tabl)
{
List<string> lt = new List<string>();
foreach (DataRow item in tabl.Rows)
{
lt.Add(item.ItemArray[0] as string);
}
return lt;
}
public static DataTable ReadExcelFile(string path)
{
DataTable atable = new DataTable();
using (StreamReader stream = new StreamReader(path))
{
OfficeOpenXml.ExcelPackage packet = new ExcelPackage(stream.BaseStream);
ExcelWorkbook workbook = packet.Workbook;
if (workbook == null)
{
throw new Exception("Excel file has no workbooks!");
}
else
{
if (workbook.Worksheets.Count == 0)
{
throw new Exception("Excel file has no worksheets!");
}
else
{
ExcelWorksheet sheet = workbook.Worksheets.First();
int columnindex = 1;
for (int rowindex = 1; rowindex <= sheet.Dimension.End.Row; rowindex++)
{
if (rowindex == 1)
{
bool foundlastcolumn = false;
while (!foundlastcolumn)
{
object nullobject = null;
DataColumn acolumn;
if ((sheet.Cells[rowindex, columnindex].Value != nullobject))
{
acolumn = new
DataColumn(sheet.Cells[rowindex, columnindex].Value.ToString());
atable.Columns.Add(acolumn);
}
else
{
foundlastcolumn = true;
}
columnindex++;
}
}
else
{
int excelcolumnindex = 1;
DataRow arow = atable.NewRow();
for (int columnindexvalue = 0; columnindexvalue < atable.Columns.Count; columnindexvalue++)
{
arow[columnindexvalue] = sheet.Cells[rowindex, excelcolumnindex].Value;
excelcolumnindex++;
}
atable.Rows.Add(arow);
excelcolumnindex = 1;
}
}
}
}
}
return atable;
}
public static void ExportToExcel(DataTable table, string path, ExcelColumnHeaderStyle headerstyle)
{
using (FileStream stream = new FileStream(path, FileMode.Create, FileAccess.Write))
{
ExcelPackage packet = new ExcelPackage();
ExcelWorkbook werkboekie = packet.Workbook;
ExcelWorksheet mysheet = werkboekie.Worksheets.Add((string.IsNullOrEmpty(table.TableName) ? "HappyTable" : table.TableName));
mysheet.InsertRow(0, table.Rows.Count + 1);
int columnindex = 1;
foreach (DataColumn acolumn in table.Columns)
{
mysheet.Cells[1, columnindex].Value = acolumn.ColumnName;
columnindex++;
}
string columnrangeTo = "A1:" + GetColumnName(table.Columns.Count) + "1";
mysheet.Cells[columnrangeTo].Style.Font.Name =
(string.IsNullOrEmpty(headerstyle.Font) ? "Arial" : headerstyle.Font);
//set the font color
mysheet.Cells[columnrangeTo].Style.Font.Color.SetColor(headerstyle.FontColor);
//if font size is not set set it to 11 (default)
mysheet.Cells[columnrangeTo].Style.Font.Size =
(headerstyle.FontSize == 0 ? 11 : headerstyle.FontSize);
//this must be set in order to set the background color of each header column
mysheet.Cells[columnrangeTo].Style.Fill.PatternType = ExcelFillStyle.Solid;
//if the background color is not set, set it to white (default) else set it to chosen color
if (headerstyle.BackgroundColor.R == 0 && headerstyle.BackgroundColor.A == 0 && headerstyle.BackgroundColor.B == 0 && headerstyle.BackgroundColor.G == 0)
mysheet.Cells[columnrangeTo].Style.Fill.BackgroundColor.SetColor(Color.White);
else
mysheet.Cells[columnrangeTo].Style.Fill.BackgroundColor.SetColor(headerstyle.BackgroundColor);
mysheet.Cells[columnrangeTo].Style.Font.Bold = headerstyle.Bold;
mysheet.Cells[columnrangeTo].Style.Font.UnderLine = headerstyle.Underline;
int DatatableRowIndex = 0;
int DatatableColIndex = 0;
for (int rowindex = 2; rowindex <= table.Rows.Count+1; rowindex++)
{
for (int colindex = 1; colindex <= table.Columns.Count; colindex++)
{
string cellindex = GetColumnName(colindex) + rowindex;
mysheet.Cells[cellindex].Value = table.Rows[DatatableRowIndex][DatatableColIndex].ToString();
DatatableColIndex++;
}
DatatableColIndex = 0;
DatatableRowIndex++;
}
packet.SaveAs(stream);
}
}
private static string GetColumnName(int index)
{
const int alphabetsCount = 26;
if (index > alphabetsCount)
{
int mod = index % alphabetsCount;
int columnIndex = index / alphabetsCount;
// if mod is 0 (clearly divisible) we reached end of one combination. Something like AZ
if (mod == 0)
{
// reducing column index as index / alphabetsCount will give the next value and we will miss
//one column.
columnIndex -= 1;
// passing 0 to the function will return character '@' which is invalid
// mod should be the alphabets count. So it takes the last char in the alphabet.
mod = alphabetsCount;
}
return GetColumnName(columnIndex) + GetColumnName(mod);
}
else
{
int code = (index - 1) + (int)'A';
return char.ConvertFromUtf32(code);
}
}
static void Main(string[] args)
{
List<String> toto = new List<string>()
{
"kdd",
"uefo",
"plop",
"wqe",
};
DataTable tabl = ListToDataTable(toto);
ExportToExcel(tabl, "AAheretest.xlsx", new ExcelColumnHeaderStyle());
tabl = ReadExcelFile("AAheretest.xlsx");
toto = DataTableToList(tabl);
} |
C# import export de donnés vers excel
Répondre avec citation
Partager