-
Notifications
You must be signed in to change notification settings - Fork 0
/
aposeCell.aspx.cs
304 lines (232 loc) · 11.7 KB
/
aposeCell.aspx.cs
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
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
using Aspose.Cells;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
namespace Excel导入导出
{
public partial class aposeCell : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void import_Click(object sender, EventArgs e)
{
HttpPostedFile file = FileUpload1.PostedFile;
if (file.ContentLength <= 0)
{
info.Text = "请上传文件";
return;
}
string[] vaildExt = new string[] {".xls",".xlsx" };
string fileExt = System.IO.Path.GetExtension(file.FileName);
if (!vaildExt.Contains(fileExt))
{
info.Text = "文件类型不符合要求";
return;
}
#region 保存excel文件
string newName = Guid.NewGuid().ToString() + fileExt;
string saveDir = "upload/Excel/";
if(!System.IO.Directory.Exists(Server.MapPath(saveDir)))
{
System.IO.Directory.CreateDirectory(Server.MapPath(saveDir));
}
try
{
file.SaveAs(Server.MapPath(saveDir + newName));
info.Text = "upload success";
}
catch
{
info.Text = "upload failed";
}
#endregion
improtExcel(Server.MapPath(saveDir + newName)); //从本地文件 读取excel
Response.Flush();
improtExcel(file.InputStream);
//从文件流中读取文件
}
//导入excel,从本地文件读取
private void improtExcel(string ExcelName)
{
Aspose.Cells.Workbook wk = new Aspose.Cells.Workbook();
wk.Open(ExcelName);// 这儿是需要导入的文件
DataTable dt = new DataTable();
DataColumn dc1 = new DataColumn("col1", typeof(string));
DataColumn dc2 = new DataColumn("col2", typeof(string));
DataColumn dc3 = new DataColumn("col3", typeof(string));
DataColumn dc4 = new DataColumn("col4", typeof(string));
DataColumn dc5 = new DataColumn("col5", typeof(string));
dt.Columns.AddRange(new DataColumn [] {dc1,dc2,dc3,dc4,dc5});
int totalRowCount = wk.Worksheets[0].Cells.Rows.Count;
for (int i = 0; i <totalRowCount; i++)//用于EXCEL数据的等号,可以自行固定如:149,也可以自行去读取它的等号;
{
DataRow dr = dt.NewRow();
dr["col1"] = wk.Worksheets[0].Cells[i, 0].Value;//读取文件里面对应的信息
dr["col2"] = wk.Worksheets[0].Cells[i, 1].Value;
dr["col3"] = wk.Worksheets[0].Cells[i, 2].Value;
dr["col4"] = wk.Worksheets[0].Cells[i, 3].Value;
dr["col5"] = wk.Worksheets[0].Cells[i, 4].Value;
dt.Rows.Add(dr);
}
GridView1.DataSource = dt;
GridView1.DataBind();
if (System.IO.File.Exists(ExcelName))
System.IO.File.Delete(ExcelName);
}
//导入excel,从流中读取
private void improtExcel(System.IO.Stream excelStream)
{
Aspose.Cells.Workbook wk = new Aspose.Cells.Workbook(excelStream);
wk.Open(excelStream);// 这儿是需要导入的文件
DataTable dt = new DataTable();
DataColumn dc1 = new DataColumn("col1", typeof(string));
DataColumn dc2 = new DataColumn("col2", typeof(string));
DataColumn dc3 = new DataColumn("col3", typeof(string));
DataColumn dc4 = new DataColumn("col4", typeof(string));
DataColumn dc5 = new DataColumn("col5", typeof(string));
dt.Columns.AddRange(new DataColumn[] { dc1, dc2, dc3, dc4, dc5 });
int totalRowCount = wk.Worksheets[0].Cells.Rows.Count;
for (int i = 0; i < totalRowCount; i++)//用于EXCEL数据的等号,可以自行固定如:149,也可以自行去读取它的等号;
{
DataRow dr = dt.NewRow();
dr["col1"] = wk.Worksheets[0].Cells[i, 0].Value;//读取文件里面对应的信息
dr["col2"] = wk.Worksheets[0].Cells[i, 1].Value;
dr["col3"] = wk.Worksheets[0].Cells[i, 2].Value;
dr["col4"] = wk.Worksheets[0].Cells[i, 3].Value;
dr["col5"] = wk.Worksheets[0].Cells[i, 4].Value;
dt.Rows.Add(dr);
}
GridView2.DataSource = dt;
GridView2.DataBind();
}
protected void export_Click(object sender, EventArgs e)
{
Workbook workbook = new Workbook(); //工作簿
Worksheet sheet = workbook.Worksheets[0]; //工作表
workbook.Worksheets[0].Name = "排考表";
#region 样式 已注释
//Cells cells = sheet.Cells;//单元格
////样式2
//Aspose.Cells.Style style2 = workbook.Styles[workbook.Styles.Add()];//新增样式
//style2.HorizontalAlignment = TextAlignmentType.Center;//文字居中
//style2.VerticalAlignment = TextAlignmentType.Center;
//style2.Font.Name = "宋体";//文字字体
//style2.Font.Size = 12;//文字大小
//style2.Font.IsBold = true;//粗体
//style2.BackgroundColor = System.Drawing.Color.Yellow;
//style2.IsTextWrapped = true;//单元格内容自动换行
//style2.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
//style2.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
//style2.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
//style2.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
//cells.SetRowHeight(3, 10);//设高
//cells.SetColumnWidth(1, 50);//设宽
//cells[0, 2].PutValue("样式使用");
//cells[0, 2].SetStyle(style2); //0表示行号,2表示列号
//cells.Merge(1, 2, 3, 4);//合并单元格 1表示行号,2表示列号,3表示合并的行号,4表示合并的列数; 把3或者4其中一个改变成1 ,表示不合并行或者列;如cells.Merge(1, 2, 3, 1);只合并三行,不合并列
#endregion
List<string> headName = new List<string> { "col1", "col2", "col3", "col4", "col5" };
//动态生成excel 利用apose.cells
for(int i=0;i<headName.Count;i++)
{
sheet.Cells[0, i].PutValue(headName[i]);
}
for (int i = 0; i < GridView2.Rows.Count; i++)
{
for(int j=0;j<GridView2.Rows[i].Cells.Count;j++)
{
sheet.Cells[i + 1, j].PutValue(GridView2.Rows[i].Cells[j].Text);
}
}
#region 先保存 本地本件,再下载,已注释
//string filename = Server.MapPath(Guid.NewGuid().ToString() + ".xls");
//workbook.Save(filename);
////以字符流的形式下载文件
//FileStream fs = new FileStream(filename, FileMode.Open);
//byte[] bytes1 = new byte[(int)fs.Length];
//fs.Read(bytes1, 0, bytes1.Length);
//fs.Close();
#endregion
Response.ContentType = "application/octet-stream";
//通知浏览器下载文件而不是打开
Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode("zxc.xls", System.Text.Encoding.UTF8));
// Response.BinaryWrite(bytes1);
Response.BinaryWrite(workbook.SaveToStream().ToArray());
Response.Flush();
Response.End();
}
//导出模板
protected void downloadModel_Click(object sender, EventArgs e)
{
Workbook workbook = new Workbook(); //工作簿
Worksheet sheet = workbook.Worksheets[0]; //工作表
workbook.Worksheets[0].Name = "排考表";
#region 样式 已注释
//Cells cells = sheet.Cells;//单元格
////样式2
//Aspose.Cells.Style style2 = workbook.Styles[workbook.Styles.Add()];//新增样式
//style2.HorizontalAlignment = TextAlignmentType.Center;//文字居中
//style2.VerticalAlignment = TextAlignmentType.Center;
//style2.Font.Name = "宋体";//文字字体
//style2.Font.Size = 12;//文字大小
//style2.Font.IsBold = true;//粗体
//style2.BackgroundColor = System.Drawing.Color.Yellow;
//style2.IsTextWrapped = true;//单元格内容自动换行
//style2.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
//style2.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
//style2.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
//style2.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
//cells.SetRowHeight(3, 10);//设高
//cells.SetColumnWidth(1, 50);//设宽
//cells[0, 2].PutValue("样式使用");
//cells[0, 2].SetStyle(style2); //0表示行号,2表示列号
//cells.Merge(1, 2, 3, 4);//合并单元格 1表示行号,2表示列号,3表示合并的行号,4表示合并的列数; 把3或者4其中一个改变成1 ,表示不合并行或者列;如cells.Merge(1, 2, 3, 1);只合并三行,不合并列
#endregion
// Get the first worksheet.
Worksheet worksheet1 = workbook.Worksheets[0];
// Add a new worksheet and access it.
// Get the validations collection.
ValidationCollection validations = worksheet1.Validations;
// Create a new validation to the validations list.
Validation validation = validations[validations.Add()];
// Set the validation type.
validation.Type = Aspose.Cells.ValidationType.List;
// Set the operator.
validation.Operator = OperatorType.None;
// Set the in cell drop down.
validation.InCellDropDown = true;
// Set the formula1.
validation.Formula1 = string.Join(",",new string[] { "102教室", "202教室" } );
// Enable it to show error.
validation.ShowError = true;
// Set the alert type severity level.
validation.AlertStyle = ValidationAlertType.Stop;
// Set the error title.
validation.ErrorTitle = "请选择正确的班级";
// Set the error message.
validation.ErrorMessage = "班级不存在于下拉框中!";
// Specify the validation area.
CellArea area;
area.StartRow = 0;
area.EndRow = 4;
area.StartColumn = 0;
area.EndColumn = 0;
// Add the validation area.
validation.AreaList.Add(area);
// Save the Excel file.
// workbook.Save("d:\\test\\validationtypelist.xls");
Response.ContentType = "application/octet-stream";
//通知浏览器下载文件而不是打开
Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode("zxc.xls", System.Text.Encoding.UTF8));
Response.BinaryWrite(workbook.SaveToStream().ToArray());
Response.Flush();
Response.End();
}
}
}