讀寫Excle,不用office環境

1.下載NPOI.dll,并添加引用

2.ExcelHelper幫助類,以下為讀寫的參照方法

        HSSFWorkbook hssfworkbook;          #region  導入Excel 返回Table        public DataTable ImportExcelFile(string filePath)        {            #region//初始化信息            try            {                using (FileStream file = new FileStream(filePath, FileMode.Open, Fileaccess.Read))                {                    hssfworkbook = new HSSFWorkbook(file);                }            }            catch (Exception e)            {                throw e;            }            #endregion            NPOI.SS.UserModel.ISheet sheet = hssfworkbook.GetSheetAt(0);            System.Collections.IEnumerator rows = sheet.GetRowEnumerator();            DataTable dt = new DataTable();            for (int j = 0; j < (sheet.GetRow(0).LastCellNum); j++)            {                dt.Columns.Add(Convert.ToChar(((int)'A') + j).ToString());            }            while (rows.MoveNext())            {                HSSFRow row = (HSSFRow)rows.Current;                DataRow dr = dt.NewRow();                for (int i = 0; i < row.LastCellNum; i++)                {                    NPOI.SS.UserModel.ICell cell = row.GetCell(i);                    if (cell == null)                    {                        dr[i] = null;                    }                    else                    {                        dr[i] = cell.ToString();                    }                }                dt.Rows.Add(dr);            }            return dt;        }        #endregion        #region 導出excel        //Datatable導出Excel        public   MemoryStream  GridToExcelByNPOI(DataTable dt)        {            try            {                HSSFWorkbook workbook = new HSSFWorkbook();                ISheet sheet = workbook.CreateSheet("Sheet1");                ICellStyle HeadercellStyle = workbook.CreateCellStyle();                HeadercellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;                HeadercellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;                HeadercellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;                HeadercellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;                HeadercellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;                //字體                NPOI.SS.UserModel.IFont headerfont = workbook.CreateFont();                headerfont.Boldweight = (short)FontBoldWeight.Bold;                HeadercellStyle.SetFont(headerfont);                //用column name 作為列名                int icolIndex = 0;                IRow headerRow = sheet.CreateRow(0);                foreach (DataColumn item in dt.Columns)                {                    ICell cell = headerRow.CreateCell(icolIndex);                    cell.SetCellValue(item.ColumnName);                    cell.CellStyle = HeadercellStyle;                    icolIndex++;                }                ICellStyle cellStyle = workbook.CreateCellStyle();                //為避免日期格式被Excel自動替換，所以設定 format 為 『@』 表示一率當成text來看                cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@");                cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;                cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;                cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;                cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;                NPOI.SS.UserModel.IFont cellfont = workbook.CreateFont();                cellfont.Boldweight = (short)FontBoldWeight.Normal;                cellStyle.SetFont(cellfont);                //建立內容行                int iRowIndex = 1;                int iCellIndex = 0;                foreach (DataRow Rowitem in dt.Rows)                {                    IRow DataRow = sheet.CreateRow(iRowIndex);                    foreach (DataColumn Colitem in dt.Columns)                    {                        ICell cell = DataRow.CreateCell(iCellIndex);                        cell.SetCellValue(Rowitem[Colitem].ToString());                        cell.CellStyle = cellStyle;                        iCellIndex++;                    }                    iCellIndex = 0;                    iRowIndex++;                }                //自適應列寬度                for (int i = 0; i < icolIndex; i++)                {                    sheet.AutoSizeColumn(i);                }                //寫Excel                MemoryStream  ms = new MemoryStream ();                workbook.Write(ms);                ms.Flush();                ms.Position = 0;                return ms;            }            catch (Exception ex)            {                throw ex;            }        }        #endregion