NPOI 生成Excel 的——常用

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

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

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

有直接保存Excel的

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

[csharp] view plaincopyprint?

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的給整疯了。

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