JamesKu.net

在 Server 端產生 Excel 檔案，不用產生檔案於Sever，直接下載，已實做成功...

  詳見:msdn & NPOI 1.2 Chinese Tutorial

    private void genExcel(string filename, string filesubject, string sheetna, string title, string[] columenas)
    {
        Response.ContentType = "application/vnd.ms-excel";
        Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", filename));
        Response.Clear();

        InitializeWorkbook(filesubject);
        GenerateData(sheetna, title, columenas);
        Response.BinaryWrite(WriteToStream().GetBuffer());
        Response.End();
    }
    
    HSSFWorkbook hssfworkbook;

    MemoryStream WriteToStream()
    {
        //Write the stream data of workbook to the root directory
        MemoryStream file = new MemoryStream();
        hssfworkbook.Write(file);
        return file;
    }

    private void GenerateData(string sheetna,string title,string[] columenas)
    {
        HSSFSheet sheet1 = hssfworkbook.CreateSheet(sheetna);

        ///style
        HSSFCellStyle style1 = hssfworkbook.CreateCellStyle();
        style1.Alignment = HSSFCellStyle.ALIGN_CENTER;
        HSSFFont font = hssfworkbook.CreateFont();
        font.FontHeight = 20 * 20;
        style1.SetFont(font);
        HSSFCellStyle style2 = hssfworkbook.CreateCellStyle();
        style2.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.LIGHT_YELLOW.index;
        style2.FillPattern = HSSFCellStyle.SOLID_FOREGROUND;
        //style2.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.ROSE.index;

        ///Title
        sheet1.CreateRow(0).CreateCell(0).SetCellValue(title);
        HSSFRow row0 = sheet1.CreateRow(0);
        HSSFCell cell0 = row0.CreateCell(0);
        cell0.SetCellValue(title);
        cell0.CellStyle = style1;

        sheet1.AddMergedRegion(new NPOI.HSSF.Util.Region(0, 0, 0, 4));
        ///ColumnNames
        HSSFRow row1 = sheet1.CreateRow(1);
        for (int i = 0; i < columenas.Length; i++)
        {
            HSSFCell cell1 = row1.CreateCell(i);
            cell1.SetCellValue(columenas[i]);
            cell1.CellStyle = style2;
            //sheet1.CreateRow(1).CreateCell(i).SetCellValue(columenas[i]);
        }
        
        System.Data.DataView dv = (System.Data.DataView)SqlDataSource1.Select(DataSourceSelectArguments.Empty);
        for (int i = 0; i < dv.Count; i++)
        {
            HSSFRow row = sheet1.CreateRow(i + 2);
            for (int j = 0; j < dv.Table.Columns.Count; j++)
            {
                string data = dv[i][j].ToString().Trim();
                row.CreateCell(j).SetCellValue(data);
            }
        }
    }

    private void InitializeWorkbook(string subject)
    {
        hssfworkbook = new HSSFWorkbook();

        ////create a entry of DocumentSummaryInformation
        DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
        dsi.Company = "TCBank";
        hssfworkbook.DocumentSummaryInformation = dsi;

        ////create a entry of SummaryInformation
        SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
        si.Subject = subject;
        hssfworkbook.SummaryInformation = si;
    }

JamesKu