熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> .NET編程 >> 正文

.NET導出Gridview到excel 帶模板列顯示

2013-11-13 09:56:01  來源: .NET編程 

  界面內容如下
    導出後顯示查詢到的數據如下
c#調用代碼如下
    protected void btnOutput_Click(object sender EventArgs e)
    {
    gvEquDataAllowPaging = false;
    BindGridViewData()
    ExcelHelper helper = new ExcelHelper()
    helperExportExcel(gvEquData 設備狀態信息列表+DateTimeNowToString(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 sheet = (HSSFSheet)hssfworkbookCreateSheet(SheetName)
    HSSFPatriarch patriarch = (HSSFPatriarch)sheetCreateDrawingPatriarch()//插入圖片所用
    HSSFRow row;
    HSSFCell cell;
    //合並單元格信息
    int startRow = ;
    int startColumn = ;
    int span = ;
    int col = ;
    //當前的格數
    int rownum = ;
    row = (HSSFRow)sheetCreateRow(
    //添加Excel標題
    for (int K = ; K < GVHeaderRowCellsCount; K++)//GVColumnsCount
    {
    cell = (HSSFCell)rowCreateCell(K)
    if (GVHeaderRowCells[K]HasControls())
    {
    ControlCollection cc=GVHeaderRowCells[K]Controls;
    if (ccCount <
    {
    if (cc[] is Literal)
    {
    Literal ltl = cc[] as Literal;
    cellSetCellValue(ltlText)
    }
    else
    {
    cellSetCellValue(GVColumns[K]HeaderText)
    }
    }
    else
    {
    if (cc[] is Literal)
    {
    Literal ltl = cc[] as Literal;
    cellSetCellValue(ltlText)
    }
    else
    {
    cellSetCellValue(GVColumns[K]HeaderText)
    }
    }
    }
    else
    {
    cellSetCellValue(GVColumns[K]HeaderText)//
    }
    //cellSetCellValue(getCellText(GVHeaderRowCells[K]))//
    }
    //加載數據
    for (int i = ; i < GVRowsCount; i++)//
    {
    row = (HSSFRow)sheetCreateRow(i +
    rownum = i + ;
    for (int j = ; j < GVHeaderRowCellsCount; j++)//GVColumnsCount
    {
    if (GVHeaderRowCells[j]ControlsCount>
    {
    cell = (HSSFCell)rowCreateCell(j)
    if (GVHeaderRowCells[j]Controls[] is CheckBox)
    {
    CheckBox cbx = GVHeaderRowCells[j]Controls[] as CheckBox;
    if (cbxChecked)
    {
    cellSetCellValue(
    }
    else
    {
    cellSetCellValue(
    }
    }
    }
    else
    {
    TableCell Usecell = GVRows[i]Cells[j];

  if (UsecellRowSpan != || UsecellColumnSpan != )//當含有和並列(行)的時候記錄該合並數據
    {
    startRow = i + ;//起始行
    startColumn = j;//起始列
    span = UsecellRowSpan;//合並的行數
    col = UsecellColumnSpan;//合並的列數
    }
    cell = (HSSFCell)rowCreateCell(j)
    //當處於合並狀時忽略該格式內容
    if (i + > startRow && j > startColumn && (startRow + span) > i + && (startColumn + col) > j)
    {
    }
    else if (i + == startRow && j == startColumn)
    {
    //進行單元格的合並
    int row = (span == ) ? : (span
    int col = (col == ) ? : (col
    sheetAddMergedRegion(new Region(i + j i + row + j + col))
    cellSetCellValue(getCellText(GVRows[i]Cells[j]))
    }
    else
    {
    cellSetCellValue(getCellText(GVRows[i]Cells[j]))
    }
    }
    }
    }
    //加載Footer部分數據
    row = (HSSFRow)sheetCreateRow(rownum +
    int footerAt = ;
    int footSpan = ;
    if (GVFooterRow != null)
    {
    for (int footNum = ; footNum < GVFooterRowCellsCount; footNum++)
    {
    TableCell footTc = GVFooterRowCells[footNum];
    if (footTcColumnSpan !=
    {
    footSpan = footTcColumnSpan;
    footerAt = footNum;
    }
    cell = (HSSFCell)rowCreateCell(footNum)
    if (footNum > footerAt && footNum < footSpan + footerAt)
    {
    }
    else if (footNum == footerAt)//合並單元格
    {
    int footercol = (footSpan == ) ? : (footSpan
    sheetAddMergedRegion(new Region(rownum + footerAt rownum + footerAt + footercol))
    cellSetCellValue(getCellText(GVFooterRowCells[footNum]))
    }
    else
    {
    cellSetCellValue(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)hssfworkbookCreateSheet(SheetName)
    int rowCount = ;
    HSSFRow newRow = (HSSFRow)excelSheetCreateRow(
    rowCount++;
    //循環寫出列頭
    for (int i = ; i < titleArrLength; i++)
    {
    HSSFCell newCell = (HSSFCell)newRowCreateCell(i)
    newCellSetCellValue(titleArr[i])
    }
    for (int i = ; i < DtRowsCount; i++)
    {
    rowCount++;
    HSSFRow newRowData = (HSSFRow)excelSheetCreateRow(rowCount)
    DataRow dr = DtRows[i];
    for (int j = ; j < clumnArrLength; j++)
    {
    HSSFCell newCell = (HSSFCell)newRowCreateCell(rowCount)
    newCellSetCellValue(dr[titleArr[j]]ToString())
    }
    }
    string path = ExcleName;
    ExportToExcel(hssfworkbook ExcleName)
    }
    //獲取圖片路徑
    string getCellText(TableCell tc)
    {
    string result = HttpUtilityHtmlDecode(tcText)//HttpUtilityHtmlDecode(str)
    foreach (Control child in tcControls)
    {
    if (child is Label)
    {
    result = HttpUtilityHtmlDecode(((Label)child)Text)
    result = resultTrim()
    break;
    }
    }
    string textLast = resultTrim()
    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 FileModeCreate)
    //hssfworkbookWrite(file)
    //fileClose()
    }
    /// <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 = PropertySetFactoryCreateDocumentSummaryInformation()
    dsiCompany = CompanyName;
    hssfworkbookDocumentSummaryInformation = dsi;
    //create a entry of SummaryInformation
    SummaryInformation si = PropertySetFactoryCreateSummaryInformation()
    siSubject = Subject;
    hssfworkbookSummaryInformation = si;
    }
    MemoryStream WriteToStream(HSSFWorkbook hssfworkbook)
    {
    //Write the stream data of workbook to the root directory
    MemoryStream file = new MemoryStream()
    hssfworkbookWrite(file)
    return file;
    }
    public void ExportToExcel(HSSFWorkbook hssfworkbook string filePath)
    {
    #region  //以字符流的形式下載文件
    //FileStream fs = new FileStream(Apppath + filePath FileModeOpen)
    //byte[] bytes = new byte[(int)fsLength];
    //fsRead(bytes bytesLength)
    //fsClose()
    #endregion
    HttpContextCurrentResponseContentType = application/vndmsexcel;
    HttpContextCurrentResponseAddHeader(ContentDisposition attachment; filename= +
    HttpUtilityUrlEncode(filePath SystemTextEncodingUTF))
    HttpContextCurrentResponseClear()
    //HttpContextCurrentResponseBinaryWrite(bytes)
    HttpContextCurrentResponseBinaryWrite(WriteToStream(hssfworkbook)GetBuffer())
    HttpContextCurrentResponseFlush()
    //HttpContextCurrentResponseEnd()
    //HttpContextCurrentResponseIsClientConnected
    }
    #endregion
    }


From:http://tw.wingwit.com/Article/program/net/201311/12029.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.