NPOI导出与读取Excel

[c#]代码库

using System;


using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Data;

using System.IO;

using NPOI;

using NPOI.HPSF;

using NPOI.HSSF;

using NPOI.HSSF.UserModel;

using NPOI.HSSF.Util;

using NPOI.POIFS;

using NPOI.Util;  




namespace ConsoleApplication2

{

    internal class Program

    {

        private static void Main(string[] args)

        {

            DataTable newdtb = new DataTable();

            newdtb.Columns.Add("Id", typeof (int));

            newdtb.Columns.Add("ProName", typeof (string));

            newdtb.Columns.Add("ProPrice", typeof (decimal));

            newdtb.Columns["Id"].AutoIncrement = true;




            for (int i = 1; i < 11; i++)

            {

                DataRow newRow = newdtb.NewRow();

                newRow["ProCardNo"] = "12312345612543256" + i.ToString();

                newRow["ProPrice"] = 12.3m;

                newdtb.Rows.Add(newRow);

            }

            DataTable dt = newdtb;

            


                Export(dt, "aaa", "d:\\222.xls");




        }





        #region  NPOI导出到Excel




        /// <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;

            }

        }




        #endregion




        #region  Excl读取

        /// <summary>

        /// 默认第一行为标头

        /// </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;

        }




        #endregion

    }

}