来自 编程 2019-09-27 11:02 的文章
当前位置: 澳门三合彩票 > 编程 > 正文

实现的可以导出与导入excel的代码一例,//导出的

  1 给出 demo源码: http://pan.baidu.com/s/1hqGMudY
            提取码:pw4n
首先要引用 NPOI.dll   
  2 //导入  3 public void OnSubmit()  4         {  5             string path = Server.MapPath("/upload/201410/27/201410271103461051.xls");  6             FileStream fs = File.Open(path, FileMode.Open);  7             System.Data.DataTable dt = ConvertToDataTable;  8               9             foreach (DataRow row in dt.Rows) 10             { 11                 if (row["Mobile1"] != null) 12                 { 13                     Response.Write(row["Mobile1"].ToString() + " " + row["Mobile2"].ToString() + " 14 "); 15                 } 16             } 17             Response.End(); 18         } 19  20  21 //excel转DataTable 22 public static DataTable ConvertToDataTable(System.IO.Stream excelFileStream) 23         { 24             HSSFWorkbook HSSFWorkbook = new HSSFWorkbook(excelFileStream); 25             DataTable dt = new DataTable(); 26             HSSFSheet sheet = (HSSFSheet)HSSFWorkbook.GetSheetAt; 27             System.Collections.IEnumerator rows = sheet.GetRowEnumerator(); 28             int n = 0; 29             while (rows.MoveNext 30             { 31                 HSSFRow row = rows.Current; 32                 if  33                 { 34                     for (int i = 0; i < row.LastCellNum; i++) 35                     { 36                         HSSFCell cell = row.GetCell; 37                         if (cell == null) 38                             continue; 39                         DataColumn column = new DataColumn(cell.StringCellValue); 40  41                         dt.Columns.Add; 42                     } 43                 } 44                 else 45                 { 46                     DataRow dtRow = dt.NewRow(); 47                     string rValue = ""; 48                     for (int i = 0, j = 0; i < row.LastCellNum; i++) 49                     { 50                         HSSFCell cell = row.GetCell; 51                         if (cell == null) 52                         { 53                             dtRow[i] = ""; 54                         } 55                         else 56                         { 57                             dtRow[j] = cell.ToString(); 58                             rValue = cell.ToString(); 59                             j++; 60                         } 61                     } 62                     if (string.IsNullOrEmpty(rValue.Trim 63                         break; 64                     dt.Rows.Add; 65                 } 66                 n++; 67             } 68             return dt; 69  70         } 71   72

            HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(filename + ".xls", System.Text.Encoding.UTF8));

**代码2  
**   
**代码示例:  
**#region 导出Excel 自定义格式    
/// &lt;summary&gt;    
/// 导出Excel      
/// 1.文本:vnd.ms-excel.numberformat:@    
/// 2.日期:vnd.ms-excel.numberformat:yyyy/mm/dd    
/// 3.数字:vnd.ms-excel.numberformat:#,##0.00    
/// 4.货币:vnd.ms-excel.numberformat:¥#,##0.00    
/// 5.百分比:vnd.ms-excel.numberformat: #0.00%    
/// &lt;/summary&gt;    
/// &lt;param name="fileName"&gt;&lt;/param&gt;    
/// &lt;param name="dt"&gt;&lt;/param&gt;    
/// &lt;returns&gt;&lt;/returns&gt;    
public bool Export(string fileName, DataTable dt)    
{    
   try    
   {    
       HttpResponse resp;    
       resp = System.Web.HttpContext.Current.Response;    
       resp.ContentEncoding =
System.Text.Encoding.GetEncoding("utf-8");    
       resp.AppendHeader("Content-Disposition",
"attachment;filename=" + fileName + ".xls");    
       resp.AppendHeader("Content-Type",
"application/ms-excel");    
        
       StringBuilder colHeaders = new StringBuilder();    
       StringBuilder ls_item = new StringBuilder();    
       DataRow[] myRow = dt.Select();    
       int cl = dt.Columns.Count;    
        
       colHeaders.Append(" &lt;html&gt;&lt;head&gt; \n ");    
       colHeaders.Append(" &lt;meta http-equiv='Content-Type'
content='text/html; charset=gb2312' /&gt; \n ");    
       colHeaders.Append(" &lt;/head&gt; \n ");    
       colHeaders.Append(" &lt;body&gt; \n ");    
       colHeaders.Append(" &lt;table border='1'&gt; ");    
       colHeaders.Append(" &lt;tr&gt; ");    
       //输出列名    
       for (int i = 0; i &lt; dt.Columns.Count; i++)    
colHeaders.Append("&lt;td  style='background-color:#CCCCCC'&gt;" +
dt.Columns[i].ColumnName + "&lt;/td&gt;");    
       colHeaders.Append("&lt;/tr&gt; ");    
       resp.Write(colHeaders.ToString());    
       foreach (DataRow row in myRow)    
       {    
ls_item.Append("&lt;tr&gt;");    
for (int i = 0; i &lt; cl; i++)    
{    
   if (i == (cl - 1))    
   {    
       ls_item.Append("&lt;td style='vnd.ms-excel.numberformat:@
'&gt;" + row[i].ToString() + "&lt;/td&gt;" + "\n");    
   }    
   else    
   {    
       ls_item.Append("&lt;td style= 'vnd.ms-excel.numberformat:@
'&gt;" + row[i].ToString() + "&lt;/td&gt;");    
   }    
}    
ls_item.Append("&lt;/tr&gt;");    
       }    
       ls_item.Append(" &lt;/table&gt; \n ");    
       ls_item.Append(" &lt;/body&gt; \n ");    
       ls_item.Append(" &lt;/html&gt;");    
       resp.Write(ls_item.ToString());    
       resp.End();    
       return true;    
   }    
   catch    
   {    
       return false;    
   }    
}    
#endregion   
**代码3  
**   
**代码示例:  
**#region 导入Excel    
public string ImportExcel(string[] list, string filePath)    
{    
        string isXls =
System.IO.Path.GetExtension(filePath).ToLower(); //System.IO.Path.GetExtension获得文件的扩展名    
        if (isXls != ".xls")    
        return "请选择Excel文件导入!";    
        DataSet ds = ExecleDataSet(filePath); //调用自定义方法    
        DataRow[] dr =
ds.Tables[0].Select(); //定义一个DataRow数组    
        int rowsnum = ds.Tables[0].Rows.Count;    
        if (ds.Tables[0].Rows.Count == 0)    
        return "Excel无数据!";    
        return "";    
    }    
        
    //OleDB连接读取Excel中数据    
    public DataSet ExecleDataSet(string filePath)    
    {    
        string OleDbConnection =
"Provider=Microsoft.Jet.OleDb.4.0;" + "data source=" + filePath +
";Extended Properties='Excel 8.0; HDR=YES; IMEX=1'";    
        OleDbConnection conn
= new OleDbConnection(OleDbConnection);    
        conn.Open();    
        DataSet ds = new DataSet();    
        OleDbDataAdapter odda = new OleDbDataAdapter("select * from
[Sheet1$]", conn);    
        odda.Fill(ds);    
        conn.Close();    
        return ds;    
}    
#endregion

/// <summary>
/// Excel导出数据
/// </summary>
/// <param name="sbHtml">html标签</param>
/// <param name="fileName">文件名</param>
public static void ExportExcel(StringBuilder sbHtml, string fileName)
{
try
{
if (sbHtml.Length > 0)
{
HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8;
HttpContext.Current.Response.Charset = "Utf-8";
//HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(fileName + ".xls", System.Text.Encoding.UTF8));
HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + fileName + ".xls" );
HttpContext.Current.Response.Write(sbHtml.ToString;
HttpContext.Current.Response.End();
}
}
catch (Exception ex)
{
Logger.WriteLog("-----------Excel导出数据异常-----------rn" + ex.ToString() + "rn");
}
}

            //Excel            

    • 本文介绍下,C#实现的可以导出与导入excel的代码一例,有需要的朋友,参考下吧。

    C#实现导出与导入excel。
    代码1:
     
    复制代码 代码示例:
    #region  导出Excel  
    /// <summary>  
    /// 导出Excel  
    /// </summary>  
    /// <param name="page">请求的页面this</param>  
    /// <param name="dataTable">导出的数据源</param>  
    /// <param name="fileName">保存文件名称</param>  
    /// <returns>布尔值</returns>  
    public bool ExportExcel(Page page, DataTable dataTable, string fileName)  
    {  
        try  
        {  
            HttpContext.Current.Response.Clear();  
            HttpContext.Current.Response.Buffer = true;  
            HttpContext.Current.Response.ContentType = "application/vnd.ms-excel.numberformat:@";  
            page.EnableViewState = false;  
            HttpContext.Current.Response.Charset = "UTF-8";  
            HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312");//设置输出流为简体中文  
            HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + fileName + ".xls");  
            //输出列名  
            for (int i = 0; i < dataTable.Columns.Count; i++)  
              HttpContext.Current.Response.Write(dataTable.Columns[i].ColumnName + "t");  
              HttpContext.Current.Response.Write("rn");  
              //输出数据  
            for (int i = 0; i < dataTable.Rows.Count; i++)  
            {  
    for (int j = 0; j < dataTable.Columns.Count; j++)  
    {  
        HttpContext.Current.Response.Write(dataTable.Rows[i][j].ToString() + "t");  
    }  
    HttpContext.Current.Response.Write("rn");  
            }  
            //输出当前缓存内容  
            //HttpContext.Current.Response.Flush();  
            HttpContext.Current.Response.End();  
            return true;  
        }  
        catch  
        {  
            return false;  
    澳门三合彩票,    }  
    }  
    #endregion

  /// </summary>

  • 转载自:

 

   

    {

        HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8;

            HttpContext.Current.Response.Write("<meta http-equiv=Content-Type content=text/html;charset=UTF-8>");

        {

        }

    /// <param name="filename">保存文件名</param>

    public override void VerifyRenderingInServerForm(Control control)

            //Word

 

<%@ Page Language="C#" CodeFile="111.aspx.cs" Inherits="111" EnableEventValidation="false" %>

            //防止出现乱码,加上这行可以防止在只有一行数据时出现乱码澳门三合彩票 1

        HttpContext.Current.Response.End();

  /// <param name="sender"></param>

    {

    gvData.AllowPaging = false;

    gvData.Columns[0].Visible = true;

        HttpContext.Current.Response.Write(writer.ToString());

    gvData.AllowPaging = true;

//导出的具体实现逻辑

本文由澳门三合彩票发布于编程,转载请注明出处:实现的可以导出与导入excel的代码一例,//导出的

关键词: