事例是上一篇 DataTable 导出

导出代码:

using
System.Drawing;
using System.IO;
using System.Text;

不久前想整理一些词库,懒得手动找,又怕手一哆嗦有遗漏,决定写程序达成吗。

事例是上一篇 DataTable 导出 Excel
的进级,除了上一篇涉嫌的拍卖乱码难题,本例还增多了拍卖多行表头、合併单元格的功效及管理汉语文件名乱码难题,应该能够知足普通开支的急需了。
废话十分少说了,间接上代码:
[csharp] 
using System; 
using System.Collections.Generic; 
using System.Web; 
using System.Configuration; 
using System.Data; 
using System.Data.Common; 
using System.Data.OleDb; 
using System.Web.UI.WebControls; 
using System.Text.RegularExpressions; 
 
/// <summary> 
/// Common 的摘要表达 
/// 作者:李伟波 
/// 时间:2012-10-18 
/// </summary> 
public class Common 

    public Common() 
    { 
        // 
        //TODO: 在此处增加构造函数逻辑 
        // 
    } 
 
    /// <summary> 
    /// 描述:把DataTable内容导出excel并再次回到看客端  
    /// 作者:李伟波 
    /// 时间:2012-10-18 
    /// </summary> 
    /// <param name=”dtData”></param> 
    /// <param name=”header”></param> 
    /// <param name=”fileName”></param> 
    /// <param name=”mergeCellNums”>要统一的列索引字典
格式:列索引-合併形式(合併情势 1 合并一样项、2 联合空项、3
合併同样项及空项)</param> 
    /// <param
name=”mergeKey”>作为联合项的标志列索引</param> 
    public static void DataTable2Excel(System.Data.DataTable dtData,
TableCell[] header, string fileName, Dictionary<int, int>
mergeCellNums, int? mergeKey) 
    { 
        System.Web.UI.WebControls.GridView gvExport = null; 
        // 当前对话  
        System.Web.HttpContext curContext =
System.Web.HttpContext.Current; 
        // IO用于导出并重临excel文件  
        System.IO.StringWriter strWriter = null; 
        System.Web.UI.HtmlTextWriter htmlWriter = null; 
 
        if (dtData != null) 
        { 
            // 设置编码和附属类小部件格式  
            curContext.Response.ContentType =
“application/vnd.ms-excel”; 
            curContext.Response.ContentEncoding =
System.Text.Encoding.GetEncoding(“gb2312”); 
            curContext.Response.Charset = “gb2312”; 
            if (!string.IsNullOrEmpty(fileName)) 
            { 
                //管理汉语名乱码难题 
                fileName = System.Web.HttpUtility.UrlEncode(fileName,
System.Text.Encoding.UTF8); 
                curContext.Response.AppendHeader(“Content-Disposition”,
(“attachment;filename=” + (fileName.ToLower().EndsWith(“.xls”) ?
fileName : fileName + “.xls”))); 
            } 
            // 导出excel文件  
            strWriter = new System.IO.StringWriter(); 
            htmlWriter = new System.Web.UI.HtmlTextWriter(strWriter); 
 
            // 重新定义一个无分页的GridView  
            gvExport = new System.Web.UI.WebControls.GridView(); 
            gvExport.DataSource = dtData.DefaultView; 
            gvExport.AllowPaging = false; 
            //优化导出数据呈现,如身份ID、12-1等展现格外问题 
            gvExport.RowDataBound += new
System.Web.UI.WebControls.GridViewRowEventHandler(dgExport_RowDataBound); 
 
            gvExport.DataBind(); 
            //管理表头 
            if (header != null && header.Length > 0) 
            { 
                gvExport.HeaderRow.Cells.Clear(); 
                gvExport.HeaderRow.Cells.AddRange(header); 
            } 
            //合併单元格 
            if (mergeCellNums != null && mergeCellNums.Count > 0) 
            { 
                foreach (int cellNum in mergeCellNums.Keys) 
                { 
                    MergeRows(gvExport, cellNum,
mergeCellNums[cellNum], mergeKey); 
                } 
            } 
 
            // 重回客商端  
            gvExport.RenderControl(htmlWriter); 
            curContext.Response.Clear(); 
            curContext.Response.Write(“<meta
http-equiv=\”content-type\” content=\”application/ms-excel;
charset=gb2312\”/>” + strWriter.ToString()); 
            curContext.Response.End(); 
        } 
    } 
    /// <summary> 
    /// 描述:行绑定事件 
    /// </summary> 
    /// <param name=”sender”></param> 
    /// <param name=”e”></param> 
    protected static void dgExport_RowDataBound(object sender,
GridViewRowEventArgs e) 
    { 
        if (e.Row.RowType == DataControlRowType.DataRow) 
        { 
            foreach (TableCell cell in e.Row.Cells) 
            { 
                //优化导出数据展现,如居民身份证、12-1等突显非常难点 
                if (Regex.IsMatch(cell.Text.Trim(), @”^\d{12,}$”) ||
Regex.IsMatch(cell.Text.Trim(), @”^\d+[-]\d+$”)) 
                { 
                    cell.Attributes.Add(“style”,
“vnd.ms-excel.numberformat:@”); 
                } 
            } 
        } 
    } 
 
    /// <summary>    
    /// 描述:合併GridView列中一模一样的行 
    /// 作者:李伟波 
    /// 时间:2012-10-18 
    /// </summary>    
    /// <param   name=”gvExport”>GridView对象</param>    
    /// <param   name=”cellNum”>须求联合的列</param>    
    /// <param name=”mergeMode”>合併情势 1 合併同样项、2
统一空项、3 合併同样项及空项</param> 
    /// <param
name=”mergeKey”>作为联合项的暗记列索引</param> 
    public static void MergeRows(GridView gvExport, int cellNum, int
mergeMode, int? mergeKey) 
    { 
        int i = 0, rowSpanNum = 1; 
        System.Drawing.Color alterColor =
System.Drawing.Color.LightGray; 
        while (i < gvExport.Rows.Count – 1) 
        { 
            GridViewRow gvr = gvExport.Rows[i]; 
            for (++i; i < gvExport.Rows.Count; i++) 
            { 
                GridViewRow gvrNext = gvExport.Rows[i]; 
                if ((!mergeKey.HasValue || (mergeKey.HasValue &&
(gvr.Cells[mergeKey.Value].Text.Equals(gvrNext.Cells[mergeKey.Value].Text)
|| ” “.Equals(gvrNext.Cells[mergeKey.Value].Text)))) && ((mergeMode ==
1 && gvr.Cells[cellNum].Text == gvrNext.Cells[cellNum].Text) ||
(mergeMode == 2 && ” “.Equals(gvrNext.Cells[cellNum].Text.Trim())) ||
(mergeMode == 3 && (gvr.Cells[cellNum].Text ==
gvrNext.Cells[cellNum].Text || ”
“.Equals(gvrNext.Cells[cellNum].Text.Trim()))))) 
                { 
                    gvrNext.Cells[cellNum].Visible = false; 
                    rowSpanNum++; 
                    gvrNext.BackColor = gvr.BackColor; 
                } 
                else 
                { 
                    gvr.Cells[cellNum].RowSpan = rowSpanNum; 
                    rowSpanNum = 1; 
                    //间隔行加底色,便于阅读 
                    if (mergeKey.HasValue && cellNum ==
mergeKey.Value) 
                    { 
                        if (alterColor == System.Drawing.Color.White) 
                        { 
                            gvr.BackColor =
System.Drawing.Color.LightGray; 
                            alterColor =
System.Drawing.Color.LightGray; 
                        } 
                        else 
                        { 
                            alterColor = System.Drawing.Color.White; 
                        } 
                    } 
                    break; 
                } 
                if (i == gvExport.Rows.Count – 1) 
                { 
                    gvr.Cells[cellNum].RowSpan = rowSpanNum; 
                    if (mergeKey.HasValue && cellNum ==
mergeKey.Value) 
                    { 
                        if (alterColor == System.Drawing.Color.White) 
                            gvr.BackColor =
System.Drawing.Color.LightGray; 
                    } 
                } 
            } 
        } 
    } 

public void GridViewToExcel(GridView ctrl, string FileType, string
FileName)
{
HttpContext.Current.Response.Charset = “GB2312”;
HttpContext.Current.Response.ContentEncoding = Encoding.UTF8;
HttpContext.Current.Response.AppendHeader(“Content-Disposition”,
“attachment;filename=” + HttpUtility.UrlEncode(FileName,
Encoding.UTF8).ToString;

protected void
Button3_Click(object sender, EventArgs e)
    {
        Response.Clear();
        Response.Buffer =
false;
        Response.Charset =
“GB2312”;
        Response.AppendHeader(“Content-Disposition”,
“attachment;filename=pkmv_de.xls”);
        Response.ContentEncoding =
System.Text.Encoding.GetEncoding(“GB2312”);
        Response.ContentType =
“application/ms-excel”;
        Response.Write(“<meta http-equiv=Content-Type
content=\”text/html; charset=GB2312\”>”);
        this.EnableViewState =
false;
        System.IO.StringWriter oStringWriter =
new System.IO.StringWriter();
        HtmlTextWriter oHtmlTextWriter =
new HtmlTextWriter(oStringWriter);
        GridView2.RenderControl(oHtmlTextWriter);
        Response.Write(oStringWriter.ToString());
        Response.End();
    }
public override
void VerifyRenderingInServerForm(Control control)
    {
      
    }
  

第一将数据用GridView控件突显到页面上,效果如下:

页面调用如下:
[html] 
TableCell[] header = new TableCell[29]; 
for (int i = 0; i < header.Length; i++) 

    header[i] = new TableHeaderCell(); 

header[0].ColumnSpan = 7; 
header[0].Text = “订单基本音信”; 
header[1].ColumnSpan = 4; 
header[1].Text = “收货人消息”; 
header[2].ColumnSpan = 4; 
header[2].Text = “快递消息”; 
header[3].ColumnSpan = 3; 
header[3].Text = “支付音信”; 
header[4].ColumnSpan = 6; 
header[4].Text = “商品音讯</th></tr><tr>”; 
//第二行 
header[5].Text = “订单号码”; 
header[6].Text = “订单类型”; 
header[7].Text = “订单状态”; 
header[8].Text = “下单时间”; 
header[9].Text = “支付时间”; 
header[10].Text = “发货时间”; 
header[11].Text = “备注”; 
 
header[12].Text = “收货人姓名”; 
header[13].Text = “地址”; 
header[14].Text = “手提式有线电话机号码”; 
header[15].Text = “配送格局”; 
 
header[16].Text = “物流集团名称”; 
header[17].Text = “物流发货单”; 
header[18].Text = “运费收入”; 
header[19].Text = “实际配送费”; 
 
header[20].Text = “订单总额”; 
header[21].Text = “支付格局”; 
header[22].Text = “订单支付金额”; 
 
header[23].Text = “商品编号”; 
header[24].Text = “商品名称”; 
header[25].Text = “商品价位”; 
header[26].Text = “购买数量”; 
header[27].Text = “商品总金额”; 
header[28].Text = “巨惠金额</th>”; 
 
DataTable dt = Common.DbHelper.DBClass_GetDataToTable(sqlDHD, sqlParam,
ref rMsg); 
 
Dictionary<int, int> mergeCellNums = new Dictionary<int,
int>(); 
for (int i = 0; i < dt.Columns.Count; i++) 

    mergeCellNums.Add(i, 2); 

Common.DataTable2Excel(dt, header, “数据导出” +
Date提姆e.Now.ToString(“yyyyMMdd”), mergeCellNums, 0); 

HttpContext.Current.Response.ContentType =
FileType;//image/JPEG;text/HTML;image/GIF;vnd.ms-excel/msword
ctrl.Page.EnableViewState = false;
StringWriter tw = new StringWriter();
HtmlTextWriter hw = new HtmlTextWriter;
ctrl.AllowPaging = false;
bind();
ctrl.RenderControl;
HttpContext.Current.Response.Write(tw.ToString;
HttpContext.Current.Response.End();
ctrl.AllowPaging = true;
bind();
}

或者

图片 1

以上代码未经严峻测试,或有错漏,请引用或应用本文代码的诸位注意。
导出效果如下图:

重写方法,此措施必得须求:

 
    /// 定义导出 Excel  Word  的函数
    private
void Export(string
FileType, string FileName)
    {
        Response.Charset =
“GB2312”;
        Response.ContentEncoding =
System.Text.Encoding.UTF8;
        Response.AppendHeader(“Content-Disposition”,
“attachment;filename=”

 

图片 2

public override void VerifyRenderingInServerForm(Control control)
{

  • HttpUtility.UrlEncode(FileName,
    Encoding.UTF8).ToString());
            Response.ContentType = FileType;
            this.EnableViewState =
    false;
            StringWriter tw =
    new StringWriter();
            HtmlTextWriter hw =
    new HtmlTextWriter(tw);
            GridView1.RenderControl(hw);
            Response.Write(tw.ToString());
            Response.End();
        }

接下去将GridView中的内容导出到Excel,方法如下:

}

    /// 此格局必重写,不然会出错
    public
override void
VerifyRenderingInServerForm(Control control)
    {
    }

 

钦定合併列:

     protected void
Button1_Click(object sender, EventArgs e)   //
Excel
    {
        Export(“application/ms-excel”,
“Employee information.xls”);
    }

 /// <summary>
    /// 由GridView导出Excel
    /// </summary>
    /// <param name=”ctl”>GridView控件ID</param>
    /// <param name=”FileName”>导出Excel名称</param>
    private void ToExcel(Control ctl, string FileName)
    {
        HttpContext.Current.Response.Charset = “UTF-8”;
        HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8;
        HttpContext.Current.Response.ContentType = “application/ms-excel”;
        HttpContext.Current.Response.AppendHeader(“Content-Disposition”, “attachment;filename=” + “” + FileName);
        ctl.Page.EnableViewState = false;
        System.IO.StringWriter tw = new System.IO.StringWriter();
        HtmlTextWriter hw = new HtmlTextWriter(tw);
        ctl.RenderControl(hw);
        HttpContext.Current.Response.Write(tw.ToString());
        HttpContext.Current.Response.End();
    }

protected void GridView1_DataBound(object sender, EventArgs e)
{
int[] arr = new int[] { 1,3,5 };
GroupRows(GridView1, arr, 2);
GroupRows(GridView1, arr, 0);
GroupRows(GridView1, arr, 1);
}

    protected void
Button2_Click(object sender, EventArgs e)  //Word
    {
        //Export(“application/ms-excel”,
“Employee.doc”);
        Export(“application/ms-word”,
“职员和工人信息.doc”);//都足以
    }

 

//合并
public static void GroupRows(GridView GridView1, int[] cellIndex, int
mostlyid)
{
int i = 0, rowSpanNum = 1;
while (i < GridView1.Rows.Count – 1)
{
GridViewRow gvr = GridView1.Rows[i];
for (++i; i < GridView1.Rows.Count; i++)
{
GridViewRow gvrNext = GridView1.Rows[i];
if (gvr.Cells[cellIndex[mostlyid]].Text ==
gvrNext.Cells[cellIndex[mostlyid]].Text)// &&
gvr.Cells[cellIndex[mostlyid]].Text ==
gvrNext.Cells[cellIndex[mostlyid]].Text)
{
gvrNext.Cells[cellIndex[mostlyid]].Visible =
false;//不然会把任何的挤走,形成行特出
rowSpanNum++;
}
else
{
gvr.Cells[cellIndex[mostlyid]].RowSpan = rowSpanNum;
rowSpanNum = 1;
break;
}
if (i == GridView1.Rows.Count – 1)
{
gvr.Cells[cellIndex[mostlyid]].RowSpan = rowSpanNum;
}
}
}
}

点击 下载
按钮

 

protected void ButtonDownloadFile(string fileUrlPath)
    {
        //用戶端的物件
        System.Net.WebClient wc = new System.Net.WebClient();
        byte[] file = null;
        try
        {
            //用戶端下載檔案到byte陣列
            file = wc.DownloadData(fileUrlPath);
        }
        catch (Exception ex)
        {
            HttpContext.Current.Response.Write(“ASP.net禁绝下載此敏感檔案(平日為:.cs、.vb、微軟資料庫mdb、mdf和config組態檔等)。<br/>檔案路徑:” + fileUrl帕特h + “<br/>錯誤訊息:” + ex.ToString());
            return;
        }
        HttpContext.Current.Response.Clear();
        string fileName = System.IO.Path.GetFileName(fileUrlPath);
        //跳出視窗,讓用戶端選擇要儲存的地方                         //使用Server.UrlEncode()編碼中文字才不會下載時,檔名為亂碼
        HttpContext.Current.Response.AddHeader(“content-disposition”, “attachment;filename=” + HttpContext.Current.Server.UrlEncode(fileName));
        //設定MIME類型為二進位檔案
        HttpContext.Current.Response.ContentType = “application/octet-stream”;

在开关上助长事件来导出Excel。注意,因为GridView是有分页和排序设置的,为了能够展现完整的数额,要先打消分页排序,导出之后再回复设置。代码如下:

        try
        {
            //檔案有每一样各樣,所以用BinaryWrite
            HttpContext.Current.Response.BinaryWrite(file);

 

        }
        catch (Exception ex)
        {
            HttpContext.Current.Response.Write(“檔案輸出有誤,您能够在瀏覽器的UTiggoL網址貼上以下路徑嘗試看看。<br/>檔案路徑:” + fileUrlPath + “<br/>錯誤訊息:” + ex.ToString());
            return;
        }

  protected void btnExport_Click(object sender, EventArgs e)
    {
        gvWord.AllowPaging = false;
        gvWord.AllowSorting = false;
        Display();
        ToExcel(gvWord, “word.xls”);
        gvWord.AllowSorting = true;
        gvWord.AllowPaging = true;
        Display();
    }

        //這是專門寫文字的
        //HttpContext.Current.Response.Write();
        HttpContext.Current.Response.End();
    }

 

 

 

而是运转之后报错:

1.上傳Excel檔。2.
ASP.net讀Excel資料,然後Insert into
Table。3.把上傳的Excel檔宰掉,制止硬碟空間不夠。

项目“GridView”的控件“GridView1”必须放在具备 runat=server 的窗体标识内。

using System;
using System.Collections.Generic;
using System.Web;
/***Copy Start***/

 

//援引Microsoft Excel相關參考
using Microsoft.Office.Interop;
using Microsoft.Office.Interop.Excel;
//移機時記得Bin底下的Microsoft.Office.Interop.Excel.dll和office.dll等,Excel相關dll也要Copy過去
/***Copy End***/

增添重写方法:

相关文章