Main
Main
文章目录
  1. 0x00.下载模板
    1. 00.运行环境是Visual Studio 2015,界面采用WebForm。
    2. 01.下载事件
  2. 0x02.导入数据
    1. 00.点击导入按钮事件
    2. 01.Excel转DataSet
    3. 10.导入操作如果报错
  3. 0x03.导出到Excel
    1. 00.导出报错’服务器无法在发送HTTP标头之后设置内容类型’

Asp.Net之Excel下载模板、导入、导出操作

0x00.下载模板

前提是服务器某文件夹中有这个文件。

00.运行环境是Visual Studio 2015,界面采用WebForm。

界面非常简单,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"); //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
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")) //判断是否 是excel文件
{
Response.Write("<script>alert('只可以选择Excel文件!')</script>");
return;
}

string filename = FileUpload1.FileName; //获取Excle文件名
string savePath = Server.MapPath(("upfiles\\") + filename);//Server.MapPath 获得虚拟服务器相对路径
string savePath2 = Server.MapPath(("upfiles\\"));

if (!Directory.Exists(savePath2)) //如果不存在upfiles文件夹则创建
{
Directory.CreateDirectory(savePath2);
}
FileUpload1.SaveAs(savePath); //SaveAs 将上传的文件内容保存在服务器上
var ds = ExcelSqlConnection(savePath, filename); //将Excel转成DataSet
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;
}
//用自己的方式保存进数据库ADO/EF/...
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
private DataSet ExcelSqlConnection(string savePath, string tableName)
{
//string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + savePath + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1'";
string strCon = "Provider=Microsoft.Ace.OLEDB.12.0;" + "data source=" + savePath + ";Extended Properties='Excel 12.0; HDR=Yes; IMEX=1'"; //HDR=YES Excel文件的第一行是列名而不是数据 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();
//Response.Write("<script>alert('" + ex.Message + "')</script>");
return null;
}

}
10.导入操作如果报错

错误代码为:未在本地计算机上注册“Microsoft.ACE.OLEDB.12.0”提供程序
解决办法(两者择其一):

  • 1.将平台换成X86
  • 2.安装 AccessDatabaseEngine.exe (详情百度)

0x03.导出到Excel

插件采用MyXLS.
引用 using org.in2bits.MyXls;
以下代码大部分基本不用改。

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"; //TEST要改
    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); //MergeArea(int rowMin, int rowMax, int colMin, int colMax)
    org.in2bits.MyXls.Cell cellTitle = cells.AddValueCell(1, 1, "TEST");    //Excel 第一行第1到2列显示TEST
    sheet.AddMergeArea(area);
    cellTitle.Font.Height = 20 * 20;
    cellTitle.Font.Bold = true;//设置标题行的字体为粗体
    cellTitle.Font.FontFamily = FontFamilies.Roman;//设置标题行的字体为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标头之后设置内容类型

支持一下
  • 微信扫一扫
  • 支付宝扫一扫