Excel导入导出

using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Web;
using NPOI.SS.UserModel;
using NPOI.HSSF.UserModel;
using NPOI.XSSF.UserModel;
using System.Data.OleDb;

namespace BLL
{
    public class Excel
    {
        /// <summary>
        /// Datable导出成Excel
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="file"></param>
        public static void TableToExcel(DataTable dt, string file, string spath, int count)
        {
            IWorkbook workbook;
            MemoryStream stream = new MemoryStream();
            try
            {
                string fileExt = Path.GetExtension(file).ToLower();
                FileStream fsm = new FileStream(spath, FileMode.Open, FileAccess.Read);
                if (fileExt == ".xlsx") { workbook = new XSSFWorkbook(fsm); } else if (fileExt == ".xls") { workbook = new HSSFWorkbook(fsm); } else { workbook = null; }
                if (workbook == null) { return; }
                ISheet sheet = string.IsNullOrEmpty(workbook.GetSheetName(0)) ? workbook.GetSheet("Sheet1") : workbook.GetSheet(workbook.GetSheetName(0));
                sheet.ForceFormulaRecalculation = true;

                //数据  
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    IRow row1 = sheet.CreateRow(i + count);
                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        ICell cell = row1.CreateCell(j);
                        cell.SetCellValue(dt.Rows[i][j].ToString());
                    }
                }

                //转为字节数组  

                workbook.Write(stream);
                HttpContext.Current.Response.Clear();
                HttpContext.Current.Response.Charset = "utf-8";
                HttpContext.Current.Response.ContentEncoding = Encoding.UTF8;//注意编码
                HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + file);
                HttpContext.Current.Response.ContentType = "application/ms-excel";
                HttpContext.Current.Response.BinaryWrite(stream.ToArray());
                HttpContext.Current.Response.End();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                stream.Dispose();
            }
        }
        /// <summary>
        /// Datable生成Excel文件
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="file"></param>
        public static void TableToExcelFile(DataTable dt, string filepath, string spath, int count)
        {
            IWorkbook workbook;
            MemoryStream stream = new MemoryStream();
            try
            {
                string fileExt = Path.GetExtension(filepath).ToLower();
                FileStream fsm = new FileStream(spath, FileMode.Open, FileAccess.Read);
                if (fileExt == ".xlsx") { workbook = new XSSFWorkbook(fsm); } else if (fileExt == ".xls") { workbook = new HSSFWorkbook(fsm); } else { workbook = null; }
                if (workbook == null) { return; }
                ISheet sheet = string.IsNullOrEmpty(workbook.GetSheetName(0)) ? workbook.GetSheet("Sheet1") : workbook.GetSheet(workbook.GetSheetName(0));
                sheet.ForceFormulaRecalculation = true;

                ICellStyle cellStyle = GetCellStyle(workbook);
                //设置单元格上下左右边框线  
                cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;

                //数据  
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    IRow row1 = sheet.CreateRow(i + count);
                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        ICell cell = row1.CreateCell(j);
                        cell.CellStyle = cellStyle;
                        cell.SetCellValue(dt.Rows[i][j].ToString());
                    }
                }

                //转为字节数组  

                workbook.Write(stream);
                var buf = stream.ToArray();

                //保存为Excel文件  
                using (FileStream fs = new FileStream(filepath, FileMode.Create, FileAccess.Write))
                {
                    fs.Write(buf, 0, buf.Length);
                    fs.Flush();
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                stream.Dispose();
            }
        }
        protected static ICellStyle GetCellStyle(IWorkbook workbook)
        {
            ICellStyle cellStyle = workbook.CreateCellStyle();
            //设置单元格上下左右边框线  
            cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
            cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
            cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
            cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
            //文字水平和垂直对齐方式  
            cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
            cellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
            //字体设置
            IFont font = workbook.CreateFont();
            font.FontHeightInPoints = 10;
            font.FontName = "Arial";

            cellStyle.SetFont(font);

            return cellStyle;
        }
        /// <summary>
        /// 讀取Excel數據
        /// </summary>
        /// <param name="FilePath"></param>
        /// <param name="strSQL"></param>
        /// <returns></returns>
        public static DataTable ExcelToTable(string FilePath, string strSQL)
        {
            DataSet myDataSet = new DataSet();
            OleDbDataAdapter objAdapter = new OleDbDataAdapter();
            string strconn = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + FilePath + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1' ";
            OleDbConnection objConnection = new OleDbConnection(strconn);
            try
            {
                objConnection.Open();
                objAdapter = new OleDbDataAdapter(strSQL, objConnection);
                objAdapter.Fill(myDataSet);
                return myDataSet.Tables[0];
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                myDataSet.Dispose();
                objAdapter.Dispose();
                objConnection.Dispose();
                objConnection.Close();
            }
        }
        /// <summary>
        /// 将excel中的数据导入到DataTable中
        /// </summary>
        /// <param name="fileName">文件名稱</param>
        /// <param name="startRow">表頭行數</param>
        /// <param name="cellCount">數據總列數</param>
        /// <returns></returns>
        public static DataTable ExcelToTable(string fileName, int startRow, int cellCount)
        {
            IWorkbook workbook = null;
            ISheet sheet = null;
            FileStream fsm = null;
            DataTable data = new DataTable();
            try
            {
                fsm = new FileStream(fileName, FileMode.Open, FileAccess.Read);
                if (fileName.IndexOf(".xlsx") > 0) // 2007版本
                    workbook = new XSSFWorkbook(fsm);
                else if (fileName.IndexOf(".xls") > 0) // 2003版本
                    workbook = new HSSFWorkbook(fsm);

                sheet = workbook.GetSheetAt(0);
                if (sheet != null)
                {
                    IRow firstRow = sheet.GetRow(0);
                    if (cellCount <= 0)
                        cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数

                    for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                    {
                        ICell cell = firstRow.GetCell(i);
                        if (cell != null)
                        {
                            string cellValue = cell.StringCellValue;
                            if (cellValue != null)
                            {
                                DataColumn column = new DataColumn(cellValue);
                                data.Columns.Add(column);
                            }
                        }
                    }
                    if (startRow <= 0)
                        startRow = sheet.FirstRowNum + 1;

                    //最后一列的标号
                    int rowCount = sheet.LastRowNum;
                    for (int i = startRow; i <= rowCount; ++i)
                    {
                        IRow row = sheet.GetRow(i);
                        if (row == null) continue; //没有数据的行默认是null　　　　　　　

                        DataRow dataRow = data.NewRow();
                        for (int j = row.FirstCellNum; j < cellCount; ++j)
                        {
                            if (row.GetCell(j) != null) //同理，没有数据的单元格都默认是null
                                dataRow[j] = row.GetCell(j).ToString();
                        }
                        data.Rows.Add(dataRow);
                    }
                }
                return data;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                data.Dispose();
                fsm.Dispose();
            }
        }
    }
}