1 using System; 2 using System.Collections.Generic; 3 using System.Linq; 4 using System.Text; 5 using NPOI.SS.UserModel; 6 using NPOI.XSSF.U ...
1 using System; 2 using System.Collections.Generic; 3 using System.Linq; 4 using System.Text; 5 using NPOI.SS.UserModel; 6 using NPOI.XSSF.UserModel; 7 using NPOI.HSSF.UserModel; 8 using System.IO; 9 using System.Data; 10 11 namespace NetUtilityLib 12 { 13 public class ExcelHelper : IDisposable 14 { 15 private get='_blank'>string fileName = null; //文件名 16 private IWorkbook workbook = null; 17 private FileStream fs = null; 18 private bool disposed; 19 20 public ExcelHelper(string fileName) 21 { 22 this.fileName = fileName; 23 disposed = false; 24 } 25 26 /// <summary> 27 /// 将DataTable数据导入到excel中 28 /// </summary> 29 /// <param name="data">要导入的数据</param> 30 /// <param name="isColumnWritten">DataTable的列名是否要导入</param> 31 /// <param name="sheetName">要导入的excel的sheet的名称</param> 32 /// <returns>导入数据行数(包含列名那一行)</returns> 33 public int DataTableToExcel(DataTable data, string sheetName, bool isColumnWritten) 34 { 35 int i = 0; 36 int j = 0; 37 int count = 0; 38 ISheet sheet = null; 39 40 fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite); 41 if (fileName.IndexOf(".xlsx") > 0) // 2007版本 42 workbook = new XSSFWorkbook(); 43 else if (fileName.IndexOf(".xls") > 0) // 2003版本 44 workbook = new HSSFWorkbook(); 45 46 try 47 { 48 if (workbook != null) 49 { 50 sheet = workbook.CreateSheet(sheetName); 51 } 52 else 53 { 54 return -1; 55 } 56 57 if (isColumnWritten == true) //写入DataTable的列名 58 { 59 IRow row = sheet.CreateRow(0); 60 for (j = 0; j < data.Columns.Count; ++j) 61 { 62 row.CreateCell(j).SetCellValue(data.Columns[j].ColumnName); 63 } 64 count = 1; 65 } 66 else 67 { 68 count = 0; 69 } 70 71 for (i = 0; i < data.Rows.Count; ++i) 72 { 73 IRow row = sheet.CreateRow(count); 74 for (j = 0; j < data.Columns.Count; ++j) 75 { 76 row.CreateCell(j).SetCellValue(data.Rows[i][j].ToString()); 77 } 78 ++count; 79 } 80 workbook.Write(fs); //写入到excel 81 return count; 82 } 83 catch (Exception ex) 84 { 85 Console.WriteLine("Exception: " + ex.Message); 86 return -1; 87 } 88 } 89 90 /// <summary> 91 /// 将excel中的数据导入到DataTable中 92 /// </summary> 93 /// <param name="sheetName">excel工作薄sheet的名称</param> 94 /// <param name="isFirstRowColumn">第一行是否是DataTable的列名</param> 95 /// <returns>返回的DataTable</returns> 96 public DataTable ExcelToDataTable(string sheetName, bool isFirstRowColumn) 97 { 98 ISheet sheet = null; 99 DataTable data = new DataTable();100 int startRow = 0;101 try102 {103 fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);104 if (fileName.IndexOf(".xlsx") > 0) // 2007版本105 workbook = new XSSFWorkbook(fs);106 else if (fileName.IndexOf(".xls") > 0) // 2003版本107 workbook = new HSSFWorkbook(fs);108 109 if (sheetName != null)110 {111 sheet = workbook.GetSheet(sheetName);112 if (sheet == null) //如果没有找到指定的sheetName对应的sheet,则尝试获取第一个sheet113 {114 sheet = workbook.GetSheetAt(0);115 }116 }117 else118 {119 sheet = workbook.GetSheetAt(0);120 }121 if (sheet != null)122 {123 IRow firstRow = sheet.GetRow(0);124 int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数125 126 if (isFirstRowColumn)127 {128 for (int i = firstRow.FirstCellNum; i < cellCount; ++i)129 {130 ICell cell = firstRow.GetCell(i);131 if (cell != null)132 {133 string cellValue = cell.StringCellValue;134 if (cellValue != null)135 {136 DataColumn column = new DataColumn(cellValue);137 data.Columns.Add(column);138 }139 }140 }141 startRow = sheet.FirstRowNum + 1;142 }143 else144 {145 startRow = sheet.FirstRowNum;146 }147 148 //最后一列的标号149 int rowCount = sheet.LastRowNum;150 for (int i = startRow; i <= rowCount; ++i)151 {152 IRow row = sheet.GetRow(i);153 if (row == null) continue; //没有数据的行默认是null 154 155 DataRow dataRow = data.NewRow();156 for (int j = row.FirstCellNum; j < cellCount; ++j)157 {158 if (row.GetCell(j) != null) //同理,没有数据的单元格都默认是null159 dataRow[j] = row.GetCell(j).ToString();160 }161 data.Rows.Add(dataRow);162 }163 }164 165 return data;166 }167 catch (Exception ex)168 {169 Console.WriteLine("Exception: " + ex.Message);170 return null;171 }172 }173 174 public void Dispose()175 {176 Dispose(true);177 GC.SuppressFinalize(this);178 }179 180 protected virtual void Dispose(bool disposing)181 {182 if (!this.disposed)183 {184 if (disposing)185 {186 if (fs != null)187 fs.Close();188 }189 190 fs = null;191 disposed = true;192 }193 }194 }195 }
原标题:NPOI帮助类(Excel转DataTable、DataTable转Excel)
关键词:DataTable
*特别声明:以上内容来自于网络收集,著作权属原作者所有,如有侵权,请联系我们:
admin#shaoqun.com
(#换成@)。