读写Excle,不用office环境

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

 

2.ExcelHelper帮助类,以下为读写的参照方法

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131

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