NPOI操作EXCEL

<p style="margin: 10px auto; font-family: Verdana, 'Lucida Grande', Arial, Helvetica, sans-serif; line-height: 18px;"><strong>1、NPOI</strong><span style="font-family: 宋体;"><strong>官方网站</strong>：</span><a target=_blank href="http://npoi.codeplex.com/" target="_blank" style="color: rgb(29, 88, 209); text-decoration: none;">http://npoi.codeplex.com/</a></p><p style="margin: 10px auto; font-family: Verdana, 'Lucida Grande', Arial, Helvetica, sans-serif; line-height: 18px;"><span style="font-family: 宋体;">   可以到此网站上去下载最新的</span>NPOI<span style="font-family: 宋体;">组件版本</span></p><p style="margin: 10px auto; font-family: Verdana, 'Lucida Grande', Arial, Helvetica, sans-serif; line-height: 18px;"><strong>2、NPOI<span style="font-family: 宋体;">在线学习教程（中文版）：</span></strong></p><p style="margin: 10px auto; font-family: Verdana, 'Lucida Grande', Arial, Helvetica, sans-serif; line-height: 18px;">    <a target=_blank href="http://www.cnblogs.com/tonyqus/archive/2009/04/12/1434209.html" target="_blank" style="color: rgb(29, 88, 209); text-decoration: none;">http://www.cnblogs.com/tonyqus/archive/2009/04/12/1434209.html</a></p><p style="margin: 10px auto; font-family: Verdana, 'Lucida Grande', Arial, Helvetica, sans-serif; line-height: 18px;"><span style="font-family: 宋体;">   感谢</span>Tony Qu<span style="font-family: 宋体;">分享出</span>NPOI<span style="font-family: 宋体;">组件的使用方法</span></p><p style="margin: 10px auto; font-family: Verdana, 'Lucida Grande', Arial, Helvetica, sans-serif; line-height: 18px;"><strong><span style="font-family: 宋体;">3、.NET</span><span style="font-family: 宋体;">调用</span>NPOI<span style="font-family: 宋体;">组件导入导出</span>Excel</strong><span style="font-family: 宋体;"><strong>的操作类</strong>　　此NPOI操作类的优点如下：   （1）支持web及winform从DataTable导出到Excel；    （2）生成速度很快；    （3）准确判断数据类型，不会出现身份证转数值等问题；    （4）如果单页条数大于65535时会新建工作表；    （5）列宽自适应；</span></p>

</pre><pre name="code" class="csharp">

</pre><pre name="code" class="csharp">NPOI操作类 <pre name="code" class="csharp">Code highlighting produced by Actipro CodeHighlighter (freeware)http://www.CodeHighlighter.com/-->  1 using System;using System.Data;using System.Configuration;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 System.Text;using NPOI;using NPOI.HPSF;using NPOI.HSSF;using NPOI.HSSF.UserModel;using NPOI.HSSF.Util;using NPOI.POIFS;using NPOI.Util;  namespace PMS.Common{    public class NPOIHelper    {        /// <summary>        /// DataTable导出到Excel文件        /// </summary>        /// <param name="dtSource">源DataTable</param>        /// <param name="strHeaderText">表头文本</param>        /// <param name="strFileName">保存位置</param>        public static void Export(DataTable dtSource, string strHeaderText, string strFileName)        {            using (MemoryStream ms = Export(dtSource, strHeaderText))            {                using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))                {                    byte[] data = ms.ToArray();                    fs.Write(data, 0, data.Length);                    fs.Flush();                }            }        }        /// <summary>        /// DataTable导出到Excel的MemoryStream        /// </summary>        /// <param name="dtSource">源DataTable</param>        /// <param name="strHeaderText">表头文本</param>        public static MemoryStream Export(DataTable dtSource, string strHeaderText)        {            HSSFWorkbook workbook = new HSSFWorkbook();            HSSFSheet sheet = workbook.CreateSheet();            #region 右击文件 属性信息            {                DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();                dsi.Company = "NPOI";                workbook.DocumentSummaryInformation = dsi;                SummaryInformation si = PropertySetFactory.CreateSummaryInformation();                si.Author = "文件作者信息"; //填加xls文件作者信息                si.ApplicationName = "创建程序信息"; //填加xls文件创建程序信息                si.LastAuthor = "最后保存者信息"; //填加xls文件最后保存者信息                si.Comments = "作者信息"; //填加xls文件作者信息                si.Title = "标题信息"; //填加xls文件标题信息                si.Subject = "主题信息";//填加文件主题信息                si.CreateDateTime = DateTime.Now;                workbook.SummaryInformation = si;            }            #endregion            HSSFCellStyle dateStyle = workbook.CreateCellStyle();            HSSFDataFormat format = workbook.CreateDataFormat();            dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");            //取得列宽            int[] arrColWidth = new int[dtSource.Columns.Count];            foreach (DataColumn item in dtSource.Columns)            {                arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;            }            for (int i = 0; i < dtSource.Rows.Count; i++)            {                for (int j = 0; j < dtSource.Columns.Count; j++)                {                    int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;                    if (intTemp > arrColWidth[j])                    {                        arrColWidth[j] = intTemp;                    }                }            }             int rowIndex = 0;             foreach (DataRow row in dtSource.Rows)            {                #region 新建表，填充表头，填充列头，样式                if (rowIndex == 65535 || rowIndex == 0)                {                    if (rowIndex != 0)                    {                        sheet = workbook.CreateSheet();                    }                    #region 表头及样式                    {                        HSSFRow headerRow = sheet.CreateRow(0);                        headerRow.HeightInPoints = 25;                        headerRow.CreateCell(0).SetCellValue(strHeaderText);                        HSSFCellStyle headStyle = workbook.CreateCellStyle();                        headStyle.Alignment = CellHorizontalAlignment.CENTER;                        HSSFFont font = workbook.CreateFont();                        font.FontHeightInPoints = 20;                        font.Boldweight = 700;                        headStyle.SetFont(font);                        headerRow.GetCell(0).CellStyle = headStyle;                        sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1));                        headerRow.Dispose();                    }                    #endregion                    #region 列头及样式                    {                        HSSFRow headerRow = sheet.CreateRow(1);                         HSSFCellStyle headStyle = workbook.CreateCellStyle();                        headStyle.Alignment = CellHorizontalAlignment.CENTER;                        HSSFFont font = workbook.CreateFont();                        font.FontHeightInPoints = 10;                        font.Boldweight = 700;                        headStyle.SetFont(font);                         foreach (DataColumn column in dtSource.Columns)                        {                            headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);                            headerRow.GetCell(column.Ordinal).CellStyle = headStyle;                            //设置列宽                            sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);                         }                        headerRow.Dispose();                    }                    #endregion                    rowIndex = 2;                }                #endregion                #region 填充内容                HSSFRow dataRow = sheet.CreateRow(rowIndex);                foreach (DataColumn column in dtSource.Columns)                {                    HSSFCell newCell = dataRow.CreateCell(column.Ordinal);                    string drValue = row[column].ToString();                    switch (column.DataType.ToString())                    {                        case "System.String"://字符串类型                            newCell.SetCellValue(drValue);                            break;                        case "System.DateTime"://日期类型                            DateTime dateV;                            DateTime.TryParse(drValue, out dateV);                            newCell.SetCellValue(dateV);                            newCell.CellStyle = dateStyle;//格式化显示                            break;                        case "System.Boolean"://布尔型                            bool boolV = false;                            bool.TryParse(drValue, out boolV);                            newCell.SetCellValue(boolV);                            break;                        case "System.Int16"://整型                        case "System.Int32":                        case "System.Int64":                        case "System.Byte":                            int intV = 0;                            int.TryParse(drValue, out intV);                            newCell.SetCellValue(intV);                            break;                        case "System.Decimal"://浮点型                        case "System.Double":                            double doubV = 0;                            double.TryParse(drValue, out doubV);                            newCell.SetCellValue(doubV);                            break;                        case "System.DBNull"://空值处理                            newCell.SetCellValue("");                            break;                        default:                            newCell.SetCellValue("");                            break;                    }                }                #endregion                rowIndex++;            }             using (MemoryStream ms = new MemoryStream())            {                workbook.Write(ms);                ms.Flush();                ms.Position = 0;                sheet.Dispose();                //workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet                return ms;            }         }        /// <summary>        /// 用于Web导出        /// </summary>        /// <param name="dtSource">源DataTable</param>        /// <param name="strHeaderText">表头文本</param>        /// <param name="strFileName">文件名</param>        public static void ExportByWeb(DataTable dtSource, string strHeaderText, string strFileName)        {             HttpContext curContext = HttpContext.Current;            // 设置编码和附件格式            curContext.Response.ContentType = "application/vnd.ms-excel";            curContext.Response.ContentEncoding = Encoding.UTF8;            curContext.Response.Charset = "";            curContext.Response.AppendHeader("Content-Disposition",                "attachment;filename=" + HttpUtility.UrlEncode(strFileName, Encoding.UTF8));            curContext.Response.BinaryWrite(Export(dtSource, strHeaderText).GetBuffer());            curContext.Response.End();         }         /// <summary>读取excel        /// 默认第一行为标头        /// </summary>        /// <param name="strFileName">excel文档路径</param>        /// <returns></returns>        public static DataTable Import(string strFileName)        {            DataTable dt = new DataTable();            HSSFWorkbook hssfworkbook;            using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))            {                hssfworkbook = new HSSFWorkbook(file);            }            HSSFSheet sheet = hssfworkbook.GetSheetAt(0);            System.Collections.IEnumerator rows = sheet.GetRowEnumerator();            HSSFRow headerRow = sheet.GetRow(0);            int cellCount = headerRow.LastCellNum;            for (int j = 0; j < cellCount; j++)            {                HSSFCell cell = headerRow.GetCell(j);                dt.Columns.Add(cell.ToString());            }            for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)            {                HSSFRow row = sheet.GetRow(i);                DataRow dataRow = dt.NewRow();                for (int j = row.FirstCellNum; j < cellCount; j++)                {                    if (row.GetCell(j) != null)                        dataRow[j] = row.GetCell(j).ToString();                }                dt.Rows.Add(dataRow);            }            return dt;        }    }}