0x00.下载模板
前提是服务器某文件夹中有这个文件。
界面非常简单，2 个 button 按钮控件，1 个 FileUpload1 控件。如图
01.下载事件
相关代码：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
| protected void btnDownload_Click(object sender, EventArgs e)
{
var path = Server.MapPath(("upfiles\\") + "test.xlt");
var name = "test.xlt";
try
{
var file = new FileInfo(path);
Response.Clear();
Response.Charset = "GB2312";
Response.ContentEncoding = System.Text.Encoding.UTF8;
Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(name));
Response.AddHeader("Content-Length", file.Length.ToString());
Response.ContentType = "application/ms-excel";
Response.WriteFile(file.FullName);
HttpContext.Current.ApplicationInstance.CompleteRequest();
}
catch (Exception ex)
{
Response.Write("<script>alert('错误:" + ex.Message + ",请尽快与管理员联系')</script>");
}
}
|
0x02.导入数据
首先准备好一个 Excel 模板。利用 FileUpload1 控件和 Button 按钮进行上传
00.点击导入按钮事件
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
| protected void btnImport_Click(object sender, EventArgs e)
{
if (FileUpload1.HasFile == false)
{
Response.Write("<script>alert('请您选择Excel文件！')</script>");
return;
}
string isXls = Path.GetExtension(FileUpload1.FileName).ToString().ToLower();
var extenLen = isXls.Length;
if (!isXls.Contains(".xls"))
{
Response.Write("<script>alert('只可以选择Excel文件！')</script>");
return;
}
string filename = FileUpload1.FileName;
string savePath = Server.MapPath(("upfiles\\") + filename);
string savePath2 = Server.MapPath(("upfiles\\"));
if (!Directory.Exists(savePath2))
{
Directory.CreateDirectory(savePath2);
}
FileUpload1.SaveAs(savePath);
var ds = ExcelSqlConnection(savePath, filename);
var dtRows = ds.Tables[0].Rows.Count;
var dt = ds.Tables[0];
if (dtRows == 0)
{
Response.Write("<script>alert('Excel表无数据！')</script>");
return;
}
try
{
for(int i = 0; i < dt.Rows.Count; i++)
{
string ve = dt.Rows[i]["车号"].ToString();
if (string.IsNullOrEmpty(ve))
{
continue;
}
var model = new TEST();
model.id = 1;
model.ve = ve;
model.name = dt.Rows[i]["姓名"].ToString();
model.Update();
}
}catch (Exception ex)
{
Response.Write("<script>alert('" + ex.Message + "')</script>");
}
}
|
01.Excel 转 DataSet
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
| private DataSet ExcelSqlConnection(string savePath, string tableName)
{
string strCon = "Provider=Microsoft.Ace.OLEDB.12.0;" + "data source=" + savePath + ";Extended Properties='Excel 12.0; HDR=Yes; IMEX=1'";
var excelConn = new OleDbConnection(strCon);
try
{
string strCom = string.Format("SELECT * FROM [Sheet1$]");
excelConn.Open();
OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, excelConn);
DataSet ds = new DataSet();
myCommand.Fill(ds, "[" + tableName + "$]");
excelConn.Close();
return ds;
}
catch (Exception)
{
excelConn.Close();
return null;
}
}
|
10.导入操作如果报错
错误代码为：未在本地计算机上注册“Microsoft.ACE.OLEDB.12.0”提供程序
解决办法(两者择其一)：
- 1.将平台换成 X86
- 2.安装 AccessDatabaseEngine.exe (详情百度)
0x03.导出到 Excel
插件采用 MyXLS.
引用 using org.in2bits.MyXls;
以下代码大部分基本不用改。
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
| private void Export()
{
XlsDocument xls = new XlsDocument();
org.in2bits.MyXls.Cell cell;
int rowIndex = 2;
xls.FileName = DateTime.Now.ToString().Replace("-", "").Replace(":", "").Replace(" ", "") + HttpUtility.UrlEncode("TEST") + ".xls";
Worksheet sheet = xls.Workbook.Worksheets.AddNamed("TEST");
org.in2bits.MyXls.Cells cells = sheet.Cells;
#region 导出Excel列宽
ColumnInfo colInfo = new ColumnInfo(xls, sheet);
colInfo.ColumnIndexStart = 0;
colInfo.ColumnIndexEnd = 2;
colInfo.Width = 15 * 300;
sheet.AddColumnInfo(colInfo);
#endregion
#region 表头
MergeArea area = new MergeArea(1, 1, 1, 2);
org.in2bits.MyXls.Cell cellTitle = cells.AddValueCell(1, 1, "TEST");
sheet.AddMergeArea(area);
cellTitle.Font.Height = 20 * 20;
cellTitle.Font.Bold = true;
cellTitle.Font.FontFamily = FontFamilies.Roman;
cellTitle.HorizontalAlignment = HorizontalAlignments.Centered;
area = new MergeArea(2, 2, 1, 1);
cellTitle = cells.AddValueCell(2, 1, "车号");
sheet.AddMergeArea(area);
cellTitle.Font.Bold = true;
cellTitle.Font.Height = 16 * 16;
cellTitle.Font.FontFamily = FontFamilies.Roman;
cellTitle.HorizontalAlignment = HorizontalAlignments.Centered;
cellTitle.VerticalAlignment = VerticalAlignments.Centered;
cellTitle.TopLineStyle = 1;
cellTitle.BottomLineStyle = 1;
cellTitle.LeftLineStyle = 1;
cellTitle.RightLineStyle = 1;
area = new MergeArea(2, 2, 2, 2);
cellTitle = cells.AddValueCell(2, 2, "姓名");
sheet.AddMergeArea(area);
cellTitle.Font.Bold = true;
cellTitle.Font.Height = 16 * 16;
cellTitle.Font.FontFamily = FontFamilies.Roman;
cellTitle.HorizontalAlignment = HorizontalAlignments.Centered;
cellTitle.VerticalAlignment = VerticalAlignments.Centered;
cellTitle.TopLineStyle = 1;
cellTitle.BottomLineStyle = 1;
cellTitle.LeftLineStyle = 1;
cellTitle.RightLineStyle = 1;
#endregion
var list = GetList();
for (int i = 0; i < list.Count; i++)
{
rowIndex++;
cell = cells.AddValueCell(rowIndex, 1, list[i].VehicleNO);
cell.TopLineStyle = 1;
cell.BottomLineStyle = 1;
cell.LeftLineStyle = 1;
cell.RightLineStyle = 1;
cell = cells.AddValueCell(rowIndex, 2, list[i].Name);
cell.TopLineStyle = 1;
cell.BottomLineStyle = 1;
cell.LeftLineStyle = 1;
cell.RightLineStyle = 1;
}
xls.Send();
}
|
00.导出报错’服务器无法在发送 HTTP 标头之后设置内容类型’
详见：服务器无法在发送 HTTP 标头之后设置内容类型
