欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

.NET导出Gridview到excel 带模板列显示

程序员文章站 2023-02-28 12:07:21
界面内容如下:   导出后显示查询到的数据如下:   c#调用代码如下:   protected void btnOutput_Cli...
界面内容如下:

 

导出后显示查询到的数据如下:

 

c#调用代码如下:

 

protected void btnOutput_Click(object sender, EventArgs e)  
 {  
     gvEquData.AllowPaging = false;  
     BindGridViewData();  
     ExcelHelper helper = new ExcelHelper();  
     helper.ExportExcel(gvEquData, "设备状态信息列表"+DateTime.Now.ToString("yyyyMMddHHmmss")+".xls", "设备状态信息列表");  
 }  

 

 

这里我使用了NPOI这个dll来操作excel,这个dll需要去网上下载。然后新建一个类用来操作excel,如下:

 

public class ExcelHelper  
{  
    #region  NPOI Excel导出  
    /// <summary>  
    /// 导出Excel   
    /// </summary>  
    /// <param name="GV">控件名称(GridView) 如有需要稍加修改可应用于DateGird等.Net数据控件</param>  
    /// <param name="ExcleName">保存的Excel名字</param>  
    /// <param name="SheetName">工作簿名字</param>  
    /// <param name="cols">图片列 如果没有图片列 该参数可赋 NULL </param>  
    public void ExportExcel(GridView GV, string ExcleName, string SheetName)  
    {  
        HSSFWorkbook hssfworkbook = new HSSFWorkbook();  
        InitializeWorkbook(hssfworkbook, "雄帝", " Export  Excel ");  
        HSSFSheet sheet1 = (HSSFSheet)hssfworkbook.CreateSheet(SheetName);  
        HSSFPatriarch patriarch = (HSSFPatriarch)sheet1.CreateDrawingPatriarch();//插入图片所用  
        HSSFRow row;  
        HSSFCell cell;  
        //合并单元格信息  
        int startRow = 0;  
        int startColumn = 0;  
        int span = 0;  
        int col = 0;  
        //当前的格数  
        int rownum = 0;  
  
        row = (HSSFRow)sheet1.CreateRow(0);  
        //添加Excel标题  
        for (int K = 0; K < GV.HeaderRow.Cells.Count; K++)//GV.Columns.Count  
        {  
            cell = (HSSFCell)row.CreateCell(K);  
            if (GV.HeaderRow.Cells[K].HasControls())  
            {  
                ControlCollection cc=GV.HeaderRow.Cells[K].Controls;  
                if (cc.Count < 2)  
                {  
                    if (cc[0] is Literal)  
                    {  
                        Literal ltl = cc[0] as Literal;  
                        cell.SetCellValue(ltl.Text);  
                    }  
                    else  
                    {  
                        cell.SetCellValue(GV.Columns[K].HeaderText);  
                    }  
                }  
                else  
                {  
                    if (cc[1] is Literal)  
                    {  
                        Literal ltl = cc[1] as Literal;  
                        cell.SetCellValue(ltl.Text);  
                    }  
                    else  
                    {  
                        cell.SetCellValue(GV.Columns[K].HeaderText);  
                    }  
                }  
            }  
            else  
            {  
                cell.SetCellValue(GV.Columns[K].HeaderText);//  
            }  
            //cell.SetCellValue(getCellText(GV.HeaderRow.Cells[K]));//  
        }  
        //加载数据  
        for (int i = 0; i < GV.Rows.Count; i++)//  
        {  
            row = (HSSFRow)sheet1.CreateRow(i + 1);  
            rownum = i + 1;  
            for (int j = 0; j < GV.HeaderRow.Cells.Count; j++)//GV.Columns.Count  
            {  
                if (GV.HeaderRow.Cells[j].Controls.Count>1)  
                {  
                    cell = (HSSFCell)row.CreateCell(j);  
                    if (GV.HeaderRow.Cells[j].Controls[0] is CheckBox)  
                    {  
                          
                        CheckBox cbx = GV.HeaderRow.Cells[j].Controls[0] as CheckBox;  
                        if (cbx.Checked)  
                        {  
                            cell.SetCellValue("是");  
                        }  
                        else  
                        {  
                            cell.SetCellValue("否");  
                        }  
                    }  
                }  
                else  
                {  
                TableCell Usecell = GV.Rows[i].Cells[j];  
                if (Usecell.RowSpan != 0 || Usecell.ColumnSpan != 0)//当含有和并列(行)的时候记录该合并数据  
                {  
                    startRow = i + 1;//起始行  
                    startColumn = j;//起始列  
                    span = Usecell.RowSpan;//合并的行数  
                    col = Usecell.ColumnSpan;//合并的列数  
                }  
                cell = (HSSFCell)row.CreateCell(j);  
                //当处于合并状时忽略该格式内容  
                if (i + 1 > startRow && j > startColumn && (startRow + span) > i + 1 && (startColumn + col) > j)  
                {  
  
                }  
                else if (i + 1 == startRow && j == startColumn)  
                {  
                    //进行单元格的合并  
                    int row2 = (span == 0) ? 0 : (span - 1);  
                    int col2 = (col == 0) ? 0 : (col - 1);  
                    sheet1.AddMergedRegion(new Region(i + 1, j, i + row2 + 1, j + col2));  
                    cell.SetCellValue(getCellText(GV.Rows[i].Cells[j]));  
                }  
                else  
                {  
                    cell.SetCellValue(getCellText(GV.Rows[i].Cells[j]));  
                }  
                }  
            }  
        }  
  
        //加载Footer部分数据  
        row = (HSSFRow)sheet1.CreateRow(rownum + 1);  
  
        int footerAt = 0;  
        int footSpan = 0;  
        if (GV.FooterRow != null)  
        {  
            for (int footNum = 0; footNum < GV.FooterRow.Cells.Count; footNum++)  
            {  
                TableCell footTc = GV.FooterRow.Cells[footNum];  
                if (footTc.ColumnSpan != 0)  
                {  
                    footSpan = footTc.ColumnSpan;  
                    footerAt = footNum;  
                }  
  
                cell = (HSSFCell)row.CreateCell(footNum);  
  
                if (footNum > footerAt && footNum < footSpan + footerAt)  
                {  
  
                }  
                else if (footNum == footerAt)//合并单元格  
                {  
                    int footercol2 = (footSpan == 0) ? 0 : (footSpan - 1);  
                    sheet1.AddMergedRegion(new Region(rownum + 1, footerAt, rownum + 1, footerAt + footercol2)); 
                    cell.SetCellValue(getCellText(GV.FooterRow.Cells[footNum]));  
                }  
                else  
                {  
                    cell.SetCellValue(getCellText(footTc));  
                }  
  
            }  
        }  
        string path = ExcleName;  
        ExportToExcel(hssfworkbook, ExcleName);  
  
    }  
  
    /// <summary>  
    /// 导出Excel  
    /// </summary>  
    /// <param name="Dt">数据源</param>  
    /// <param name="ExcleName">导入文件名称</param>  
    /// <param name="SheetName">工作薄名称</param>  
    /// <param name="titleArr">标题栏</param>  
    /// <param name="clumnArr">栏位名</param>  
    public void ExportExcel(DataTable Dt, string ExcleName, string SheetName, string[] titleArr, string[] clumnArr)  
    {  
        HSSFWorkbook hssfworkbook = new HSSFWorkbook();  
        InitializeWorkbook(hssfworkbook, "雄帝", " Export  Excel ");  
        HSSFSheet excelSheet = (HSSFSheet)hssfworkbook.CreateSheet(SheetName);  
        int rowCount = 0;  
        HSSFRow newRow = (HSSFRow)excelSheet.CreateRow(0);  
        rowCount++;  
        //循环写出列头           
        for (int i = 0; i < titleArr.Length; i++)  
        {  
            HSSFCell newCell = (HSSFCell)newRow.CreateCell(i);  
            newCell.SetCellValue(titleArr[i]);  
        }  
        for (int i = 0; i < Dt.Rows.Count; i++)  
        {  
            rowCount++;  
            HSSFRow newRowData = (HSSFRow)excelSheet.CreateRow(rowCount);  
            DataRow dr = Dt.Rows[i];  
            for (int j = 0; j < clumnArr.Length; j++)  
            {  
                HSSFCell newCell = (HSSFCell)newRow.CreateCell(rowCount);  
                newCell.SetCellValue(dr[titleArr[j]].ToString());  
            }  
        }  
        string path = ExcleName;  
        ExportToExcel(hssfworkbook, ExcleName);  
    }  
  
    //获取图片路径  
    string getCellText(TableCell tc)  
    {  
        string result = HttpUtility.HtmlDecode(tc.Text);//HttpUtility.HtmlDecode(str);  
        foreach (Control child in tc.Controls)  
        {  
            if (child is Label)  
            {  
                result = HttpUtility.HtmlDecode(((Label)child).Text);  
                result = result.Trim();  
                break;  
            }  
        }  
        string textLast = result.Trim();  
        return textLast;  
    }  
  
    /// <summary>  
    /// 对产生的Excel进行文本输入  
    /// </summary>  
    /// <param name="Path">输出路径</param>  
    public void WriteToFile(string Path)  
    {  
        ////Write the stream data of workbook to the root directory  
        //FileStream file = new FileStream(Path, FileMode.Create);  
        //hssfworkbook.Write(file);  
        //file.Close();  
    }  
  
    /// <summary>  
    /// 填写Excel文本属性  如有需要可以进行函数扩展 添加更多的属性值  
    /// </summary>  
    /// <param name="CompanyName">公司名称</param>  
    /// <param name="Subject">文档主题</param>  
    public void InitializeWorkbook(HSSFWorkbook hssfworkbook, string CompanyName, string Subject)  
    {  
        //hssfworkbook = new HSSFWorkbook();  
  
        //create a entry of DocumentSummaryInformation  
        DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();  
        dsi.Company = CompanyName;  
        hssfworkbook.DocumentSummaryInformation = dsi;  
  
        //create a entry of SummaryInformation  
        SummaryInformation si = PropertySetFactory.CreateSummaryInformation();  
        si.Subject = Subject;  
        hssfworkbook.SummaryInformation = si;  
    }  
  
    MemoryStream WriteToStream(HSSFWorkbook hssfworkbook)  
    {  
        //Write the stream data of workbook to the root directory  
        MemoryStream file = new MemoryStream();  
        hssfworkbook.Write(file);  
        return file;  
    }  
  
    public void ExportToExcel(HSSFWorkbook hssfworkbook, string filePath)  
    {  
        #region  //以字符流的形式下载文件  
        //FileStream fs = new FileStream(Apppath + filePath, FileMode.Open);  
        //byte[] bytes = new byte[(int)fs.Length];  
        //fs.Read(bytes, 0, bytes.Length);  
        //fs.Close();  
        #endregion  
  
        HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";  
        HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" +   
HttpUtility.UrlEncode(filePath, System.Text.Encoding.UTF8));  
        HttpContext.Current.Response.Clear();  
  
        //HttpContext.Current.Response.BinaryWrite(bytes);  
        HttpContext.Current.Response.BinaryWrite(WriteToStream(hssfworkbook).GetBuffer());  
        HttpContext.Current.Response.Flush();  
        //HttpContext.Current.Response.End();  
        //HttpContext.Current.Response.IsClientConnected  
  
    }  
    #endregion  
}