datatable导出excel

   第一步用Nuget找到SharpZipLib安装:

第二步 添加NPOI:

第三部检查是否添加成功:

 

 

添加成功后直接在导出事件里面直接调用：

  DataTable dt = GetVehicledt(hql.ToString()); /*获得数据源*/
                string path = "D:\\ssss.xls";
                if (File.Exists(Server.MapPath("~/ssss.xls")))
                {

                }
                else
                {
                    //不存在文件
                    File.Create(MapPath("~/ssss.xls"));//创建该文件
                }
                DataTableToExcel(dt,path);

 

 

public static void DataTableToExcel(DataTable table, string fileName)
        {
            using (MemoryStream ms = RenderDataTableToExcel(table))
            {
                SaveToFile(ms, fileName);
            }
        }
        private static void SaveToFile(MemoryStream ms, string fileName)
        {
            using (FileStream fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.Write))
            {
                byte[] data = ms.ToArray();

                fs.Write(data, 0, data.Length);
                fs.Flush();

                data = null;
            }
        }
        public static MemoryStream RenderDataTableToExcel(DataTable table)
        {
            MemoryStream ms = new MemoryStream();

            IWorkbook workbook = new HSSFWorkbook();
            ISheet sheet = workbook.CreateSheet(table.TableName);
            IRow headerRow = sheet.CreateRow(0);

            ICellStyle headStyle = workbook.CreateCellStyle();
            headStyle.Alignment = HorizontalAlignment.Center;
            headStyle.FillForegroundColor = HSSFColor.Grey25Percent.Index;
            headStyle.FillPattern = FillPattern.SolidForeground;
            IFont font = workbook.CreateFont();
            font.FontName = "Microsoft Yahei";
            font.FontHeightInPoints = 11;
            font.IsBold = true;
            font.Color = HSSFColor.White.Index;
            headStyle.SetFont(font);
            headStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
            headStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
            headStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;

            foreach (DataColumn column in table.Columns)
            {
                ICell cell = headerRow.CreateCell(column.Ordinal);
                cell.SetCellValue(string.Format("    {0}    ", column.Caption));
                cell.CellStyle = headStyle;
            }

            for (int rowIndex = 0; rowIndex < table.Rows.Count; rowIndex++)
            {
                IRow dataRow = sheet.CreateRow(rowIndex + 1);
                foreach (DataColumn column in table.Columns)
                {
                    dataRow.CreateCell(column.Ordinal).SetCellValue(table.Rows[rowIndex][column].ToString());
                }
            }

            workbook.Write(ms);
            ms.Close();

            return ms;
        }