.NET DataTable转Excel，使用NPOI插件

    /// <summary>
    /// DataTable写入Excel 
    /// </summary>
    /// <param name="data">数据源</param>
    /// <param name="templateFile">导出的文件名</param>
    /// <param name="cell">开始写入行数 （第一条是0）</param>
    /// <param name="isSetRow">是否显示序号</param>
    /// <param name="fontSize">字体大小</param>
    /// <returns></returns>
    public static byte[] DataTableToExcel(DataTable data, string templateFile, int cell, bool isSetRow, short fontSize = 16)
    {

        byte[] byteData = new byte[0];
        using (FileStream stream = new FileStream(templateFile, FileMode.Open))
        {
            HSSFWorkbook book = new HSSFWorkbook(stream);
            ISheet sheet = book.GetSheet("Sheet1");

            //设置表格样式
            ICellStyle cellStyle = book.CreateCellStyle();
            cellStyle.BorderBottom = BorderStyle.Thin;
            cellStyle.BorderLeft = BorderStyle.Thin;
            cellStyle.BorderRight = BorderStyle.Thin;
            cellStyle.BorderTop = BorderStyle.Thin;
            cellStyle.WrapText = true;
            //设置字体样式
            IFont fonts = book.CreateFont();
            //fonts.Boldweight = (short)FontBoldWeight.Bold;   //字体加粗样式    
            fonts.FontHeightInPoints = fontSize;                   //设置字体大小
            fonts.FontName = "宋体";
            fonts.Color = HSSFColor.Black.Index;             //设置字体颜色
            cellStyle.SetFont(fonts);
            cellStyle.Alignment = HorizontalAlignment.Center;   //居中      
            cellStyle.VerticalAlignment = VerticalAlignment.Center;//垂直对齐 

            if (data != null)
            {
                int rownum = 1;
                for (int i = 0; i < data.Rows.Count; i++)  //遍历模板excel中的行
                {
                    IRow currentRow = sheet.CreateRow(i + cell);
                    if (isSetRow)
                    {
                        ICell icell1top0 = currentRow.CreateCell(0);
                        icell1top0.SetCellValue(rownum);
                        icell1top0.CellStyle = cellStyle;

                    }

                    for (int j = 1; j <= data.Columns.Count; j++)
                    {
                        ICell icell1top0 = currentRow.CreateCell(j);

                        //遍历列
                        if (isSetRow)
                        {
                            icell1top0 = currentRow.CreateCell(j);


                            if (data.Columns[j - 1].DataType == System.Type.GetType("System.DateTime"))
                            {

                                if (!string.IsNullOrEmpty(data.Rows[i][j - 1].ToString()))
                                    icell1top0.SetCellValue(Convert.ToDateTime(data.Rows[i][j - 1]).ToString("yyyy-MM-dd"));
                                else
                                    icell1top0.SetCellValue(data.Rows[i][j - 1].ToString());
                            }
                            else if (data.Columns[j - 1].DataType == System.Type.GetType("System.Int32") || data.Columns[j - 1].DataType == System.Type.GetType("System.Int64"))
                            {
                                int intCount = 0;
                                int.TryParse(data.Rows[i][j - 1].ToString(), out intCount);
                                icell1top0.SetCellValue(intCount);
                            }
                            else if (data.Columns[j - 1].DataType == System.Type.GetType("System.Decimal"))
                            {
                                double doubleCount = 0;
                                double.TryParse(data.Rows[i][j - 1].ToString(), out doubleCount);
                                icell1top0.SetCellValue(doubleCount);
                            }
                            else
                            {
                         
                                string thisData = data.Rows[i][j - 1].ToString();
                                if (!string.IsNullOrEmpty(thisData) && thisData.Length>40)
                                {
                                   //业务要求长字符串要求列内文字换行
                                    StringBuilder noteString = new StringBuilder("");
                                    string[] arrDt = thisData.Split('；');
                                    foreach (string dt in arrDt)
                                    {
                                        noteString.Append(dt + "\n");
                                    }
                                    icell1top0.SetCellValue(noteString.ToString());
                                }
                                else
                                {
                                    icell1top0.SetCellValue(thisData);
                                }
                            }

                            icell1top0.CellStyle = cellStyle;
                        }
                        else
                        {
                            icell1top0 = currentRow.CreateCell(j - 1);

                            if (data.Columns[j].DataType == System.Type.GetType("System.DateTime"))
                            {

                                if (!string.IsNullOrEmpty(data.Rows[i][j - 1].ToString()))
                                {
                                    icell1top0.SetCellValue(Convert.ToDateTime(data.Rows[i][j - 1]).ToString("yyyy-MM-dd"));
                                }

                                else
                                    icell1top0.SetCellValue(data.Rows[i][j - 1].ToString());
                            }

                            else if (data.Columns[j].DataType == System.Type.GetType("System.Int32") || data.Columns[j - 1].DataType == System.Type.GetType("System.Decimal"))
                            {
                                icell1top0.SetCellValue(Convert.ToInt32(data.Rows[i][j - 1]));
                            }
                            else
                                icell1top0.SetCellValue(data.Rows[i][j - 1].ToString());

                            icell1top0.CellStyle = cellStyle;
                        }


                    }
                    rownum++;
                }

            }
            using (MemoryStream ms = new MemoryStream())
            {
                book.Write(ms);
                byteData = ms.ToArray();
            }
            return byteData;
        }

    }