NPOI 生成Excel 的——常用

把自己常用的整理了出来，(*^__^*) 嘻嘻……

有关文件输出文件名乱码的问题

有设置Excel中的一些样式问题

有直接保存Excel的

更多NPOI的实例说明：http://www.cnblogs.com/tonyqus/archive/2009/04/12/1434209.html   这个可是别个大虾滴。

[csharp] view plain copy print ?

1. using System; 
2. using System.Collections; 
3. using System.Configuration; 
4. using System.Data; 
5. using System.Web; 
6. using System.Web.Security; 
7. using System.Web.UI; 
8. using System.Web.UI.HtmlControls; 
9. using System.Web.UI.WebControls; 
10. using System.Web.UI.WebControls.WebParts; 
11.  
12. using System.IO; 
13. using NPOI.SS.UserModel; 
14. using NPOI.HSSF.UserModel; 
15. using NPOI.HSSF.Util; 
16.  
17. public partial class _Default : System.Web.UI.Page 
18. { 
19.     protected void Page_Load(object sender, EventArgs e) 
20.     { 
21.         DownLoadExcel(); 
22.     } 
23.  
24.     /// <summary> 
25.     /// 下载Excel 
26.     /// </summary> 
27.     public void DownLoadExcel() 
28.     { 
29.         /*
30.          * ①：输出文档
31.          */ 
32.         string fileName = DateTime.Now.ToString("yyyyMMdd") + "测试.xls"; 
33.  
34.         string UserAgent = Request.ServerVariables["http_user_agent"].ToLower(); 
35.         // Firfox和IE下输出中文名显示正常 
36.         if (UserAgent.IndexOf("firefox") == -1) 
37.         { 
38.             fileName = HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8); 
39.         } 
40.  
41.         Response.ContentType = "application/vnd.ms-excel;charset=UTF-8"; 
42.         Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", fileName)); 
43.         Response.Clear(); 
44.         //写入内容到Excel 
45.         HSSFWorkbook hssfworkbook = writeToExcel(); 
46.         //将Excel内容写入到流中 
47.         MemoryStream file = new MemoryStream(); 
48.         hssfworkbook.Write(file); 
49.         //输出 
50.         Response.BinaryWrite(file.GetBuffer()); 
51.         Response.End(); 
52.  
53.         /*
54.          * ②：将文档保存到指定路径
55.          */ 
56.         string destFileName = @"D:\test.xls"; 
57.         HSSFWorkbook hssfworkbook2 = writeToExcel(); 
58.         MemoryStream msfile = new MemoryStream(); 
59.         hssfworkbook.Write(msfile); 
60.         System.IO.File.WriteAllBytes(destFileName, msfile.ToArray()); 
61.     } 
62.  
63.     /// <summary> 
64.     /// 写入内容到Excel中 
65.     /// </summary> 
66.     /// <returns></returns> 
67.     private HSSFWorkbook writeToExcel() 
68.     { 
69.         //string template = "模板路径.xls"; 
70.         //FileStream file = new FileStream(template, FileMode.Open, FileAccess.Read); 
71.         //HSSFWorkbook hssfworkbook = new HSSFWorkbook(file);// 创建对象 
72.         //HSSFSheet excelSheet = (HSSFSheet)hssfworkbook.GetSheetAt(0);// 获得sheet 
73.  
74.         HSSFWorkbook hssfworkbook = new HSSFWorkbook(); 
75.  
76.         HSSFSheet excelSheet = (HSSFSheet)hssfworkbook.CreateSheet("sheet1"); 
77.  
78.         Row row0 = excelSheet.CreateRow(0); 
79.         Cell cell0 = CreateCell(0, row0); 
80.         cell0.SetCellValue("NUM"); 
81.         cell0.CellStyle = GetCellStyle(hssfworkbook, CellBorderType.THIN, CellBorderType.THIN, CellBorderType.THIN, CellBorderType.THIN, HSSFColor.LIGHT_YELLOW.index, "#,##0"); 
82.  
83.         for (int i = 100, j = 1; i < 10000; i++, j++) 
84.         { 
85.             Row row = CreateRow(j, excelSheet); 
86.             Cell cell = CreateCell(0, row); 
87.             cell.SetCellValue(i); 
88.             cell.CellStyle = GetCellStyle(hssfworkbook, CellBorderType.THIN, CellBorderType.THIN, CellBorderType.THIN, CellBorderType.THIN, HSSFColor.LIGHT_GREEN.index, "#,##0"); 
89.         } 
90.  
91.         return hssfworkbook; 
92.     } 
93.  
94.  
95.     /// <summary> 
96.     /// 设置样式 
97.     /// </summary> 
98.     /// <param name="hssfworkbook"></param> 
99.     /// <param name="borderLeft">左边框</param> 
100.     /// <param name="borderBottom">下边框</param> 
101.     /// <param name="borderRight">右边框</param> 
102.     /// <param name="borderTop">上边框</param> 
103.     /// <param name="fillforgeroundColor">背景填充色</param> 
104.     /// <param name="dataFormat">数据格式</param> 
105.     /// <returns></returns> 
106.     private CellStyle GetCellStyle(HSSFWorkbook hssfworkbook 
107.         , CellBorderType borderLeft, CellBorderType borderBottom, CellBorderType borderRight, CellBorderType borderTop 
108.         , short fillforgeroundColor 
109.         , string dataFormat) 
110.     { 
111.         CellStyle styleInfo = hssfworkbook.CreateCellStyle(); 
112.  
113.         styleInfo.BorderLeft = borderLeft; 
114.         styleInfo.BorderBottom = borderBottom; 
115.         styleInfo.BorderRight = borderRight; 
116.         styleInfo.BorderTop = borderTop; 
117.  
118.         styleInfo.Alignment = HorizontalAlignment.CENTER; 
119.         styleInfo.VerticalAlignment = VerticalAlignment.CENTER; 
120.  
121.         styleInfo.FillForegroundColor = fillforgeroundColor;//设置填充色 
122.         styleInfo.FillPattern = FillPatternType.SOLID_FOREGROUND;//设置填充色的时候必须设置这个 
123.  
124.         styleInfo.DataFormat = HSSFDataFormat.GetBuiltinFormat(dataFormat); 
125.         // 当前日期格式的需要以下这样设置 
126.         //HSSFDataFormat format = (HSSFDataFormat)hssfworkbook.CreateDataFormat(); 
127.         //styleInfo.DataFormat = format.GetFormat("yyyy年m月d日"); 
128.  
129.         return styleInfo; 
130.     } 
131.  
132.     /// <summary> 
133.     /// 创建行对象 
134.     /// </summary> 
135.     /// <param name="rowID"></param> 
136.     /// <param name="excelSheet"></param> 
137.     /// <returns></returns> 
138.     private Row CreateRow(int rowID, HSSFSheet excelSheet) 
139.     { 
140.         Row row = excelSheet.GetRow(rowID); 
141.         if (row == null) 
142.         { 
143.             row = excelSheet.CreateRow(rowID); 
144.         } 
145.         return row; 
146.     } 
147.  
148.     /// <summary> 
149.     /// 创建列对象 
150.     /// </summary> 
151.     /// <param name="rowID"></param> 
152.     /// <param name="excelSheet"></param> 
153.     /// <returns></returns> 
154.     private Cell CreateCell(int cellID, Row row) 
155.     { 
156.         Cell cell = row.GetCell(cellID); 
157.         if (cell == null) 
158.         { 
159.             cell = row.CreateCell(cellID); 
160.         } 
161.         return cell; 
162.     } 
163.  
164. } 

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;

using System.IO;
using NPOI.SS.UserModel;
using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;

public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        DownLoadExcel();
    }

    /// <summary>
    /// 下载Excel
    /// </summary>
    public void DownLoadExcel()
    {
        /*
         * ①：输出文档
         */
        string fileName = DateTime.Now.ToString("yyyyMMdd") + "测试.xls";

        string UserAgent = Request.ServerVariables["http_user_agent"].ToLower();
        // Firfox和IE下输出中文名显示正常
        if (UserAgent.IndexOf("firefox") == -1)
        {
            fileName = HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8);
        }

        Response.ContentType = "application/vnd.ms-excel;charset=UTF-8";
        Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", fileName));
        Response.Clear();
        //写入内容到Excel
        HSSFWorkbook hssfworkbook = writeToExcel();
        //将Excel内容写入到流中
        MemoryStream file = new MemoryStream();
        hssfworkbook.Write(file);
        //输出
        Response.BinaryWrite(file.GetBuffer());
        Response.End();

        /*
         * ②：将文档保存到指定路径
         */
        string destFileName = @"D:\test.xls";
        HSSFWorkbook hssfworkbook2 = writeToExcel();
        MemoryStream msfile = new MemoryStream();
        hssfworkbook.Write(msfile);
        System.IO.File.WriteAllBytes(destFileName, msfile.ToArray());
    }

    /// <summary>
    /// 写入内容到Excel中
    /// </summary>
    /// <returns></returns>
    private HSSFWorkbook writeToExcel()
    {
        //string template = "模板路径.xls";
        //FileStream file = new FileStream(template, FileMode.Open, FileAccess.Read);
        //HSSFWorkbook hssfworkbook = new HSSFWorkbook(file);// 创建对象
        //HSSFSheet excelSheet = (HSSFSheet)hssfworkbook.GetSheetAt(0);// 获得sheet

        HSSFWorkbook hssfworkbook = new HSSFWorkbook();

        HSSFSheet excelSheet = (HSSFSheet)hssfworkbook.CreateSheet("sheet1");

        Row row0 = excelSheet.CreateRow(0);
        Cell cell0 = CreateCell(0, row0);
        cell0.SetCellValue("NUM");
        cell0.CellStyle = GetCellStyle(hssfworkbook, CellBorderType.THIN, CellBorderType.THIN, CellBorderType.THIN, CellBorderType.THIN, HSSFColor.LIGHT_YELLOW.index, "#,##0");

        for (int i = 100, j = 1; i < 10000; i++, j++)
        {
            Row row = CreateRow(j, excelSheet);
            Cell cell = CreateCell(0, row);
            cell.SetCellValue(i);
            cell.CellStyle = GetCellStyle(hssfworkbook, CellBorderType.THIN, CellBorderType.THIN, CellBorderType.THIN, CellBorderType.THIN, HSSFColor.LIGHT_GREEN.index, "#,##0");
        }

        return hssfworkbook;
    }


    /// <summary>
    /// 设置样式
    /// </summary>
    /// <param name="hssfworkbook"></param>
    /// <param name="borderLeft">左边框</param>
    /// <param name="borderBottom">下边框</param>
    /// <param name="borderRight">右边框</param>
    /// <param name="borderTop">上边框</param>
    /// <param name="fillforgeroundColor">背景填充色</param>
    /// <param name="dataFormat">数据格式</param>
    /// <returns></returns>
    private CellStyle GetCellStyle(HSSFWorkbook hssfworkbook
        , CellBorderType borderLeft, CellBorderType borderBottom, CellBorderType borderRight, CellBorderType borderTop
        , short fillforgeroundColor
        , string dataFormat)
    {
        CellStyle styleInfo = hssfworkbook.CreateCellStyle();

        styleInfo.BorderLeft = borderLeft;
        styleInfo.BorderBottom = borderBottom;
        styleInfo.BorderRight = borderRight;
        styleInfo.BorderTop = borderTop;

        styleInfo.Alignment = HorizontalAlignment.CENTER;
        styleInfo.VerticalAlignment = VerticalAlignment.CENTER;

        styleInfo.FillForegroundColor = fillforgeroundColor;//设置填充色
        styleInfo.FillPattern = FillPatternType.SOLID_FOREGROUND;//设置填充色的时候必须设置这个

        styleInfo.DataFormat = HSSFDataFormat.GetBuiltinFormat(dataFormat);
        // 当前日期格式的需要以下这样设置
        //HSSFDataFormat format = (HSSFDataFormat)hssfworkbook.CreateDataFormat();
        //styleInfo.DataFormat = format.GetFormat("yyyy年m月d日");

        return styleInfo;
    }

    /// <summary>
    /// 创建行对象
    /// </summary>
    /// <param name="rowID"></param>
    /// <param name="excelSheet"></param>
    /// <returns></returns>
    private Row CreateRow(int rowID, HSSFSheet excelSheet)
    {
        Row row = excelSheet.GetRow(rowID);
        if (row == null)
        {
            row = excelSheet.CreateRow(rowID);
        }
        return row;
    }

    /// <summary>
    /// 创建列对象
    /// </summary>
    /// <param name="rowID"></param>
    /// <param name="excelSheet"></param>
    /// <returns></returns>
    private Cell CreateCell(int cellID, Row row)
    {
        Cell cell = row.GetCell(cellID);
        if (cell == null)
        {
            cell = row.CreateCell(cellID);
        }
        return cell;
    }

}

最近被Excel的給整疯了。

整理一下，希望可以给人帮助！