excel文本寫入 npoi_C#中NPOI操作excel之讀取和寫入excel資料

usingSystem;usingSystem.Data;usingSystem.IO;usingNPOI.SS.UserModel;usingNPOI.HSSF.UserModel;usingNPOI.XSSF.UserModel;usingSystem.Collections.Generic;usingSystem.Reflection;namespaceIETMAuthor.Toolbox

{public classExcelUtility

{///

///将excel導入到datatable///

/// excel路徑

/// 第一行是否是列名

/// 傳回datatable

public static DataTable ExcelToDataTable(string filePath, boolisColumnName)

{

DataTable dataTable= null;

FileStream fs= null;

DataColumn column= null;

DataRow dataRow= null;

IWorkbook workbook= null;

ISheet sheet= null;

IRow row= null;

ICell cell= null;int startRow = 0;try{using (fs =File.OpenRead(filePath))

{//2003版本

if (filePath.IndexOf(".xls") > 0)

workbook= newHSSFWorkbook(fs);//2007版本

else if (filePath.IndexOf(".xlsx") > 0)

workbook= newXSSFWorkbook(fs);if (workbook != null)

{

sheet= workbook.GetSheetAt(0);//讀取第一個sheet，當然也可以循環讀取每個sheet

dataTable = newDataTable();if (sheet != null)

{int rowCount = sheet.LastRowNum;//總行數

if (rowCount > 0)

{

IRow firstRow= sheet.GetRow(0);//第一行

int cellCount = firstRow.LastCellNum;//列數//建構datatable的列

if(isColumnName)

{

startRow= 1;//如果第一行是列名，則從第二行開始讀取

for (int i = firstRow.FirstCellNum; i < cellCount; i++)

{

cell=firstRow.GetCell(i);if (cell != null)

{if (cell.StringCellValue != null)

{

column= newDataColumn(cell.StringCellValue);

dataTable.Columns.Add(column);

}

}

}

}else{for (int i = firstRow.FirstCellNum; i < cellCount; i++)

{

column= new DataColumn("column" + (i + 1));

dataTable.Columns.Add(column);

}

}//填充行

for (int i = startRow; i <= rowCount; i++)

{

row=sheet.GetRow(i);if (row == null) continue;

dataRow=dataTable.NewRow();for (int j = row.FirstCellNum; j < cellCount; j++)

{

cell=row.GetCell(j);if (cell == null)

{

dataRow[j]= "";

}else{switch(cell.CellType)

{case(NPOI.SS.UserModel.CellType)CellType.Blank:

dataRow[j]= "";break;case(NPOI.SS.UserModel.CellType)CellType.Numeric:short format =cell.CellStyle.DataFormat;//對時間格式(2015.12.5、2015/12/5、2015-12-5等)的處理

if (format == 14 || format == 31 || format == 57 || format == 58)

dataRow[j]=cell.DateCellValue;elsedataRow[j]=cell.NumericCellValue;break;case(NPOI.SS.UserModel.CellType)CellType.String:

dataRow[j]=cell.StringCellValue;break;

}

}

}

dataTable.Rows.Add(dataRow);

}

}

}

}

}returndataTable;

}catch(Exception)

{if (fs != null)

{

fs.Close();

}return null;

}

}///

///DataTable轉List

///

/// 資料項類型

/// DataTable

/// List資料集

public static List DataTableToList(DataTable dt) where T : new()

{

List list = new List();if (dt != null && dt.Rows.Count > 0)

{foreach (DataRow dr indt.Rows)

{

T t= DataRowToModel(dr);

list.Add(t);

}

}returnlist;

}///

///DataRow轉實體///

/// 資料型類

/// DataRow

/// 模式

public static T DataRowToModel(DataRow dr) where T : new()

{//T t = (T)Activator.CreateInstance(typeof(T));

T t = newT();if (dr == null) return default(T);//獲得此模型的公共屬性

PropertyInfo[] propertys =t.GetType().GetProperties();

DataColumnCollection Columns=dr.Table.Columns;foreach (PropertyInfo p inpropertys)

{string columnName =p.Name;if(Columns.Contains(columnName))

{object value =dr[columnName];if (value is DBNull || value ==DBNull.Value)continue;try{switch(p.PropertyType.ToString())

{case "System.String":

p.SetValue(t, Convert.ToString(value),null);break;case "System.Int32":

p.SetValue(t, Convert.ToInt32(value),null);break;case "System.Int64":

p.SetValue(t, Convert.ToInt64(value),null);break;case "System.DateTime":

p.SetValue(t, Convert.ToDateTime(value),null);break;case "System.Boolean":

p.SetValue(t, Convert.ToBoolean(value),null);break;case "System.Double":

p.SetValue(t, Convert.ToDouble(value),null);break;case "System.Decimal":

p.SetValue(t, Convert.ToDecimal(value),null);break;default:

p.SetValue(t, value,null);break;

}

}catch(Exception ex)

{continue;}

}

}returnt;

}

}enumCellType

{

Unknown= -1, Numeric = 0, String = 1, Formula = 2, Blank = 3, Boolean = 4, Error = 5}

}