public static System.Data.DataTable GetDataTabelFromExcelFile(string fileName, int startRowIndex)
{
System.Data.DataTable dataSource = new System.Data.DataTable();
dataSource.Columns.Add("Index", typeof(string));
dataSource.Columns.Add("IndexNPOI", typeof(string));
dataSource.Columns.Add("Name", typeof(string));
dataSource.Columns.Add("Description", typeof(string));
dataSource.Columns.Add("HexString", typeof(string));
// 获取Workbook。
NPOI.SS.UserModel.IWorkbook workbook = NPOIExtension.GetWorkbookFromExcelFile(fileName);
if (workbook == null)
{
return(dataSource);
}
// 获取Sheet
NPOI.SS.UserModel.ISheet sheet = workbook.GetSheetAt(0);
for (int i = startRowIndex; i < sheet.LastRowNum; i++)
{
NPOI.SS.UserModel.IRow row = sheet.GetRow(i);
System.Data.DataRow dataSourceRow = dataSource.NewRow();
dataSource.Rows.Add(dataSourceRow);
for (int j = 0; j < dataSource.Columns.Count; j++)
{
dataSourceRow[j] = row.GetCell(j).ToString();
}
}
return(dataSource);
}
/// <summary>
/// Implementació default
/// </summary>
/// <param name="workbook"></param>
/// <param name="resumenProceso"></param>
/// <returns></returns>
public override bool ValidarPlantillaExcelImportacion(NPOI.SS.UserModel.IWorkbook workBookImportar, Common.Dtos.ResumenProcesoImportacionExcelDto resumenProceso)
{
bool puedeContinuar = true;
var espacioNombresPlantilla = "";
var valorDefaultValidacionPlantilla2014 = "";
if (workBookImportar.NumberOfSheets > 0)
{
//2014 Validar opcionalmente que la plantilla no corresponda al espacio de nombres de la taxonomía
//en la hoja 1, renglón 1, columna 2 debe encontrarse un espacio.
var hoja1 = workBookImportar.GetSheetAt(0);
var renglon1 = hoja1.GetRow(0);
if (renglon1 != null)
{
var celdaValidacion = renglon1.GetCell(1);
if (celdaValidacion != null)
{
espacioNombresPlantilla = celdaValidacion.StringCellValue;
}
}
}
if (!valorDefaultValidacionPlantilla2014.Equals(espacioNombresPlantilla))
{
resumenProceso.InformeErrores.Add(new InformeErrorImportacion()
{
Mensaje = "La plantilla de importación para la taxonomía (" + this.documentoInstancia.Taxonomia.nombreAbax + ") " +
"\n no es la esperada."
});
puedeContinuar = false;
}
return(puedeContinuar);
}
/// <summary>
/// Implementació default
/// </summary>
/// <param name="workbook"></param>
/// <param name="resumenProceso"></param>
/// <returns></returns>
public override bool ValidarPlantillaExcelImportacion(NPOI.SS.UserModel.IWorkbook workBookImportar, Common.Dtos.ResumenProcesoImportacionExcelDto resumenProceso)
{
bool puedeContinuar = true;
var espacioNombresPlantilla = "";
if (workBookImportar.NumberOfSheets > 0)
{
//2019 Validar opcionalmente que la plantilla corresponda al espacio de nombres de la taxonomía
//en la hoja 1, renglón 1, columna 2 se colocará el espacio de nombres de la taxonomía
var hoja1 = workBookImportar.GetSheetAt(0);
var renglon1 = hoja1.GetRow(0);
if (renglon1 != null)
{
var celdaValidacion = renglon1.GetCell(1);
if (celdaValidacion != null)
{
espacioNombresPlantilla = celdaValidacion.StringCellValue;
}
}
}
if (!this.documentoInstancia.EspacioNombresPrincipal.Equals(espacioNombresPlantilla))
{
resumenProceso.InformeErrores.Add(new InformeErrorImportacion()
{
Mensaje = "El documento Excel no pertenece a la taxonomía que está utilizando (" + this.documentoInstancia.Taxonomia.nombreAbax + ")." +
"\nAsegúrese que el documento corresponda a la taxonomía de la que desea importar los datos."
});
puedeContinuar = false;
}
return(puedeContinuar);
}
public static System.Data.DataTable GetDataTableFromExcelActiveSheet(string fileName, int startRowIndex, int startColumnIndex, bool firstRowIsColumnHead = true, bool autoAddColumn = false, bool ignoreBlankRow = true)
{
NPOI.SS.UserModel.IWorkbook workbook = GetWorkbookFromExcelFile(fileName);
if (workbook == null)
{
return(null);
}
NPOI.SS.UserModel.ISheet sheet = workbook.GetSheetAt(workbook.ActiveSheetIndex);
if (sheet == null)
{
return(null);
}
return(GetDataTableFromSheet(sheet, startRowIndex, startColumnIndex, firstRowIsColumnHead, autoAddColumn, ignoreBlankRow));
}
public static System.Data.DataSet GetDataSet(string fileName, bool firstRowIsColumnHead, bool ignoreHiddenSheet)
{
NPOI.SS.UserModel.IWorkbook iWorkbook = Extension.NPOIMethod.GetWorkbookFromExcelFile(fileName);
if (iWorkbook == null)
{
return(null);
}
System.Data.DataSet dataSet = new System.Data.DataSet();
for (int iSheetIndex = 0; iSheetIndex < iWorkbook.NumberOfSheets; iSheetIndex++)
{
bool isHiddenSheet = iWorkbook.IsSheetHidden(iSheetIndex) || iWorkbook.IsSheetHidden(iSheetIndex);
if (ignoreHiddenSheet && isHiddenSheet)
{
continue;
}
dataSet.Tables.Add(GetDataTable(iWorkbook.GetSheetAt(iSheetIndex), firstRowIsColumnHead, 0, 0, 0));
}
return(dataSet);
}
public static System.Data.DataSet GetDataSetFromExcelFile(string fileName, bool firstRowIsColumnHead = true, bool ignoreBlankRow = true, bool ignoreHiddenSheet = true)
{
NPOI.SS.UserModel.IWorkbook workbook = GetWorkbookFromExcelFile(fileName);
if (workbook == null)
{
return(null);
}
System.Data.DataSet dataSet = new System.Data.DataSet();
for (int i = 0; i < workbook.NumberOfSheets; i++)
{
bool isHiddenSheet = workbook.IsSheetHidden(i) || workbook.IsSheetVeryHidden(i);
if (ignoreHiddenSheet && isHiddenSheet)
{
continue;
}
dataSet.Tables.Add(GetDataTableFromSheet(workbook.GetSheetAt(i), 0, 0, true, true, true));
}
return(dataSet);
}
public static System.Data.DataTable GetDataTableFromExcelActiveSheet(string fileName, int startRowIndex, int startColumnIndex, bool ignoreBlankRow = true, params string[] columnHeadNames)
{
NPOI.SS.UserModel.IWorkbook workbook = GetWorkbookFromExcelFile(fileName);
if (workbook == null)
{
return(null);
}
NPOI.SS.UserModel.ISheet sheet = workbook.GetSheetAt(workbook.ActiveSheetIndex);
if (sheet == null)
{
return(null);
}
System.Data.DataTable dataTable = new System.Data.DataTable(sheet.SheetName);
for (int i = 0; i < columnHeadNames.Length; i++)
{
dataTable.Columns.Add(columnHeadNames[i]);
}
if (columnHeadNames.Length > 0)
{
for (int rowIndex = startRowIndex; rowIndex < sheet.PhysicalNumberOfRows; rowIndex++)
{
NPOI.SS.UserModel.IRow row = sheet.GetRow(rowIndex);
if (row != null)
{
System.Data.DataRow dataRow = dataTable.NewRow();
dataTable.Rows.Add(dataRow);
int dataTableColumnIndex = 0;
for (int columnIndex = startColumnIndex; columnIndex < row.PhysicalNumberOfCells; columnIndex++)
{
if (dataTableColumnIndex >= dataTable.Columns.Count)
{
break;
}
dataRow[dataTableColumnIndex] = GetCellValue(row.GetCell(columnIndex));
dataTableColumnIndex++;
}
}
}
}
return(dataTable);
}
private void button6_Click(object sender, EventArgs e)
{
List <FishEntity.CallRecordsEntity> list = new List <FishEntity.CallRecordsEntity>();
for (int i = 0; i < 10; i++)
{
FishEntity.CallRecordsEntity entity = new FishEntity.CallRecordsEntity();
entity.code = "00000" + i;
entity.customer = "杭州" + i;
entity.linkman = "金" + i;
entity.mobile = "13757193476";
list.Add(entity);
}
string startDate = "2015-03-01";
string endDate = "2015-03-25";
string templatepath = Application.StartupPath + "\\template\\callrecord.xls";
FileStream fs = new FileStream(templatepath, FileMode.Open, FileAccess.Read);
NPOI.SS.UserModel.IWorkbook workbook = null;
workbook = NPOI.SS.UserModel.WorkbookFactory.Create(fs);
NPOI.SS.UserModel.ISheet sheet = workbook.GetSheetAt(0);
int firstRow = sheet.FirstRowNum;
int lastRow = sheet.LastRowNum;
System.Collections.Hashtable hs = new System.Collections.Hashtable();
hs.Add("startDate", startDate);
hs.Add("endDate", endDate);
hs.Add("items", list);
int arrStart = -1;
int arrItem = -1;
int arrEnd = -1;
List <FishEntity.CallRecordsEntity> items = null;
for (int idx = firstRow; idx <= lastRow; idx++)
{
NPOI.SS.UserModel.IRow row = sheet.GetRow(idx);
foreach (NPOI.SS.UserModel.ICell cell in row.Cells)
{
string val = cell.ToString();
if (string.IsNullOrEmpty(val))
{
continue;
}
foreach (System.Collections.DictionaryEntry entry in hs)
{
string key1 = "<jx:forEach items=\"${" + entry.Key.ToString() + "}\" var=\"item\"}>";
if (val.Equals(key1))
{
arrStart = idx;
items = entry.Value as List <FishEntity.CallRecordsEntity>;
break;
}
string key2 = "</jx:forEach>";
if (val.Equals(key2))
{
arrEnd = idx;
break;
}
string key = "${" + entry.Key.ToString() + "}";
if (val.Contains(key))
{
val = val.Replace(key, entry.Value.ToString());
}
}
}
}
if (arrStart >= 0 && arrEnd >= 2)
{
arrItem = arrStart + 1;
NPOI.SS.UserModel.IRow rrrow = sheet.GetRow(arrItem);
List <KV> columns = GetItemNames(rrrow);
NPOI.SS.UserModel.IRow row = null;
for (int idx = 0; idx < items.Count; idx++)
{
int tttidx = arrStart + idx;
if (tttidx != arrItem)
{
row = sheet.CreateRow(tttidx);
NPOI.SS.UserModel.IRow ffrow = sheet.GetRow(arrItem);
//row = sheet.CopyRow(arrItem, tttidx);
for (int i = 0; i < columns.Count; i++)
{
row.CreateCell(i);
}
}
else
{
row = rrrow;
}
foreach (NPOI.SS.UserModel.ICell cell in row.Cells)
{
foreach (KV kv in columns)
{
if (kv.colIdx == cell.ColumnIndex)
{
System.Reflection.PropertyInfo prop = items[idx].GetType().GetProperty(kv.name);
if (prop != null)
{
string temp = prop.GetValue(items[idx], null).ToString();
cell.SetCellValue(temp);
}
}
}
}
}
if ((arrEnd - arrStart + 1) > items.Count)
{
//TODO
}
string fpath = Application.StartupPath + "\\test.xls";
System.IO.FileStream fss = new FileStream(fpath, FileMode.Create, FileAccess.Write);
workbook.Write(fss);
fss.Close();
}
}
public static System.Data.DataTable GetDataTabelFromExcelFile(string fileName, bool firstLineIsColumnHead)
{
System.Data.DataTable dataSource = new System.Data.DataTable();
NPOI.SS.UserModel.IWorkbook workbook = GetWorkbookFromExcelFile(fileName);
if (workbook == null)
{
return(dataSource);
}
NPOI.SS.UserModel.ISheet sheet = workbook.GetSheetAt(workbook.ActiveSheetIndex);
if (sheet == null)
{
return(dataSource);
}
NPOI.SS.UserModel.IRow firstRow = sheet.GetRow(0);
if (firstRow != null)
{
for (int columnIndex = 0; columnIndex < firstRow.LastCellNum; columnIndex++)
{
if (firstLineIsColumnHead)
{
NPOI.SS.UserModel.ICell cell = firstRow.GetCell(columnIndex);
if (cell == null)
{
dataSource.Columns.Add();
}
else
{
dataSource.Columns.Add(cell.ToString());
}
}
else
{
dataSource.Columns.Add();
}
}
}
int startRowIndex = firstLineIsColumnHead ? 1 : 0;
for (int rowIndex = startRowIndex; rowIndex <= sheet.LastRowNum; rowIndex++)
{
System.Data.DataRow dataSourceRow = dataSource.NewRow();
dataSource.Rows.Add(dataSourceRow);
NPOI.SS.UserModel.IRow row = sheet.GetRow(rowIndex);
if (row != null)
{
for (int columnIndex = 0; columnIndex < dataSource.Columns.Count; columnIndex++)
{
NPOI.SS.UserModel.ICell cell = row.GetCell(columnIndex);
if (cell != null)
{
switch (cell.CellType)
{
case NPOI.SS.UserModel.CellType.Blank:
dataSourceRow[columnIndex] = "";
break;
case NPOI.SS.UserModel.CellType.Boolean:
dataSourceRow[columnIndex] = cell.BooleanCellValue;
break;
case NPOI.SS.UserModel.CellType.Error:
dataSourceRow[columnIndex] = cell.ErrorCellValue;
break;
case NPOI.SS.UserModel.CellType.Formula:
dataSourceRow[columnIndex] = cell.NumericCellValue;
break;
case NPOI.SS.UserModel.CellType.Numeric:
if (NPOI.SS.UserModel.DateUtil.IsCellDateFormatted(cell))
{
dataSourceRow[columnIndex] = cell.DateCellValue;
}
else
{
dataSourceRow[columnIndex] = cell.NumericCellValue;
}
break;
case NPOI.SS.UserModel.CellType.String:
dataSourceRow[columnIndex] = cell.StringCellValue;
break;
default:
dataSourceRow[columnIndex] = cell.ToString();
break;
}
}
}
}
}
return(dataSource);
}
public static System.Data.DataTable GetDataTabelFromExcelFile(string fileName, int sheetNumber, int startColumnNumber, int startRowNumber, params string[] columnNames)
{
System.Data.DataTable dataSource = new System.Data.DataTable();
if (columnNames.Length < 1)
{
return(dataSource);
}
foreach (string columnName in columnNames)
{
dataSource.Columns.Add(columnName);
}
NPOI.SS.UserModel.IWorkbook workbook = GetWorkbookFromExcelFile(fileName);
if (workbook == null)
{
return(dataSource);
}
if (sheetNumber > workbook.NumberOfSheets)
{
return(dataSource);
}
int sheetIndex = sheetNumber > 0 ? sheetNumber - 1 : workbook.ActiveSheetIndex;
NPOI.SS.UserModel.ISheet sheet = workbook.GetSheetAt(sheetIndex);
int startRowIndex = startRowNumber > 0 ? startRowNumber - 1 : 0;
for (int i = startRowIndex; i <= sheet.LastRowNum; i++)
{
System.Data.DataRow dataSourceRow = dataSource.NewRow();
dataSource.Rows.Add(dataSourceRow);
NPOI.SS.UserModel.IRow row = sheet.GetRow(i);
if (row != null)
{
int startColumnIndex = startColumnNumber > 0 ? startColumnNumber - 1 : 0;
int columnIndex = 0;
Enumerable.Range(startColumnIndex, dataSource.Columns.Count).ToList().ForEach(cellIndex =>
{
NPOI.SS.UserModel.ICell cell = row.GetCell(cellIndex);
if (cell != null)
{
switch (cell.CellType)
{
case NPOI.SS.UserModel.CellType.Blank:
dataSourceRow[columnIndex] = "";
break;
case NPOI.SS.UserModel.CellType.Boolean:
dataSourceRow[columnIndex] = cell.BooleanCellValue;
break;
case NPOI.SS.UserModel.CellType.Error:
dataSourceRow[columnIndex] = cell.ErrorCellValue;
break;
case NPOI.SS.UserModel.CellType.Formula:
dataSourceRow[columnIndex] = cell.NumericCellValue;
break;
case NPOI.SS.UserModel.CellType.Numeric:
if (NPOI.SS.UserModel.DateUtil.IsCellDateFormatted(cell))
{
dataSourceRow[columnIndex] = cell.DateCellValue;
}
else
{
dataSourceRow[columnIndex] = cell.NumericCellValue;
}
break;
case NPOI.SS.UserModel.CellType.String:
dataSourceRow[columnIndex] = cell.StringCellValue;
break;
default:
dataSourceRow[columnIndex] = cell.ToString();
break;
}
}
columnIndex++;
});
}
}
return(dataSource);
}
public ActionResult Import(VModel.SyUserTeacher.FormImport m)
{
if (ModelState.IsValid)
{
if (m.File.FileName.ToLower().IndexOf("xls") == -1)
{
ModelState.AddModelError("File", string.Format("导入文件必须为Excel文件"));
return(View(m));
}
NPOI.SS.UserModel.IWorkbook book = NPOI.SS.UserModel.WorkbookFactory.Create(m.File.InputStream);
NPOI.SS.UserModel.ISheet sheet = book.GetSheetAt(0);
if (sheet != null)
{
List <VModel.SyUserTeacher.ImportData> list = new List <VModel.SyUserTeacher.ImportData>();
NPOI.SS.UserModel.IRow firstRow = sheet.GetRow(0);
int startRow = sheet.FirstRowNum + 2;
int rowCount = sheet.LastRowNum;
for (int i = startRow; i <= rowCount; ++i)
{
NPOI.SS.UserModel.IRow row = sheet.GetRow(i);
if (row == null)
{
continue; //没有数据的行默认是null
}
if (row.GetCell(0) == null || string.IsNullOrWhiteSpace(row.GetCell(0).ToString()))
{
ModelState.AddModelError("File", string.Format("第{0}行“用户名”不能为空", i + 1));
return(View(m));
}
else if (Bll.SyTeacherBll.IsExist(row.GetCell(0).ToString(), null))
{
ModelState.AddModelError("File", string.Format("第{0}行“用户名”已经存在", i + 1));
return(View(m));
}
else if (row.GetCell(0).ToString().Length > 20)
{
ModelState.AddModelError("File", string.Format("第{0}行“用户名”最大长度不超过20个字符的内容", i + 1));
return(View(m));
}
else if (row.GetCell(1) == null || string.IsNullOrWhiteSpace(row.GetCell(1).ToString()))
{
ModelState.AddModelError("File", string.Format("第{0}行“姓名”不能为空", i + 1));
return(View(m));
}
//else if (Bll.SyTeacherBll.IsExist(null,row.GetCell(1).ToString()))
//{
// ModelState.AddModelError("File", string.Format("第{0}行“姓名”已经存在", i + 1));
// return View(m);
//}
else if (row.GetCell(1).ToString().Length > 20)
{
ModelState.AddModelError("File", string.Format("第{0}行“姓名”最大长度不超过20个字符的内容", i + 1));
return(View(m));
}
else if (row.GetCell(2) == null || string.IsNullOrWhiteSpace(row.GetCell(2).ToString()))
{
ModelState.AddModelError("File", string.Format("第{0}行“学科”不能为空", i + 1));
return(View(m));
}
else if (Bll.DictSubjectBll.IsExist(row.GetCell(2).ToString()) == false)
{
ModelState.AddModelError("File", string.Format("第{0}行“学科”不存在", i + 1));
return(View(m));
}
VModel.SyUserTeacher.ImportData data = new VModel.SyUserTeacher.ImportData();
data.Account = row.GetCell(0).ToString();
data.Name = row.GetCell(1).ToString();
data.SubjectName = row.GetCell(2).ToString();
list.Add(data);
}
int r = Bll.SyUserBll.ImportTeacher(list);
switch (r)
{
case 200:
return(RedirectToAction("Index"));
}
}
}
return(View(m));
}
public void Save(string FileName, Model model)
{
try
{
int idx = FileName.LastIndexOf('.');
string ext = FileName.Substring(idx + 1);
NPOI.SS.UserModel.IWorkbook workbook = null;
if (File.Exists(FileName))
{
using (FileStream fs = new FileStream(FileName, FileMode.Open, FileAccess.Read))
{
workbook = NPOI.SS.UserModel.WorkbookFactory.Create(fs);
int nSheets = workbook.NumberOfSheets;
if (nSheets > 0)
{
var sheet = workbook.GetSheetAt(0);
int nRows = sheet.LastRowNum + 1;
var row = sheet.CreateRow(nRows);
row.CreateCell(0).SetCellValue(model.Id);
row.CreateCell(1).SetCellValue(model.Time);
row.CreateCell(2).SetCellValue(model.QRCode);
row.CreateCell(3).SetCellValue(model.Flow);
row.CreateCell(4).SetCellValue(model.APressure);
row.CreateCell(5).SetCellValue(model.BPressure);
}
fs.Close();
}
}
else
{
if (ext == "xls")
{
workbook = new NPOI.HSSF.UserModel.HSSFWorkbook();
}
else if (ext == "xlsx")
{
workbook = new NPOI.XSSF.UserModel.XSSFWorkbook();
}
else
{
return;
}
var sheet = workbook.CreateSheet();
var row0 = sheet.CreateRow(0);
row0.CreateCell(0).SetCellValue("序号");
row0.CreateCell(1).SetCellValue("时间");
row0.CreateCell(2).SetCellValue("条码");
row0.CreateCell(3).SetCellValue("流量/ml");
row0.CreateCell(4).SetCellValue("A胶压力/psi");
row0.CreateCell(5).SetCellValue("B胶压力/psi");
var row = sheet.CreateRow(1);
row.CreateCell(0).SetCellValue(model.Id);
row.CreateCell(1).SetCellValue(model.Time);
row.CreateCell(2).SetCellValue(model.QRCode);
row.CreateCell(3).SetCellValue(model.Flow);
row.CreateCell(4).SetCellValue(model.APressure);
row.CreateCell(5).SetCellValue(model.BPressure);
}
using (FileStream fs_write = new FileStream(FileName, FileMode.Create))
{
workbook.Write(fs_write);
fs_write.Close();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
/// <summary>
/// 将excel文件内容读取到DataTable数据表中
/// </summary>
/// <param name="fileName">文件完整路径名</param>
/// <param name="sheetName">指定读取excel工作薄sheet的名称</param>
/// <param name="isFirstRowColumn">第一行是否是DataTable的列名：true=是，false=否</param>
/// <returns>DataTable数据表</returns>
public static DataTable ReadExcelToDataTable(string fileName, string sheetName = null, bool isFirstRowColumn = true)
{
//定义要返回的datatable对象
DataTable data = new DataTable();
//excel工作表
NPOI.SS.UserModel.ISheet sheet = null;
//数据开始行(排除标题行)
int startRow = 0;
try
{
if (!File.Exists(fileName))
{
return(null);
}
//根据指定路径读取文件
FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
//根据文件流创建excel数据结构
NPOI.SS.UserModel.IWorkbook workbook = NPOI.SS.UserModel.WorkbookFactory.Create(fs);
//IWorkbook workbook = new HSSFWorkbook(fs);
//如果有指定工作表名称
if (!string.IsNullOrEmpty(sheetName))
{
sheet = workbook.GetSheet(sheetName);
//如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet
if (sheet == null)
{
sheet = workbook.GetSheetAt(0);
}
}
else
{
//如果没有指定的sheetName，则尝试获取第一个sheet
sheet = workbook.GetSheetAt(0);
}
if (sheet != null)
{
NPOI.SS.UserModel.IRow firstRow = sheet.GetRow(0);
//一行最后一个cell的编号 即总的列数
int cellCount = firstRow.LastCellNum;
//如果第一行是标题列名
if (isFirstRowColumn)
{
for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
{
NPOI.SS.UserModel.ICell cell = firstRow.GetCell(i);
cell.SetCellType(NPOI.SS.UserModel.CellType.String);
if (cell != null)
{
string cellValue = cell.StringCellValue;
if (cellValue != null)
{
DataColumn column = new DataColumn(cellValue);
data.Columns.Add(column);
}
}
}
startRow = sheet.FirstRowNum + 1;
}
else
{
startRow = sheet.FirstRowNum;
}
//最后一列的标号
int rowCount = sheet.LastRowNum;
for (int i = startRow; i <= rowCount; ++i)
{
NPOI.SS.UserModel.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 ex;
}
}
/// <summary>
/// 将excel中的数据导入到DataTable中
/// </summary>
/// <param name="sheetName">excel工作薄sheet的名称</param>
/// <param name="isFirstRowColumn">第一行是否是DataTable的列名</param>
/// <returns>返回的DataTable</returns>
/// <author>范永坚 2017-08-09</author>
public DataTable ExcelToDataTable(string sheetName, bool isFirstRowColumn)
{
NPOI.SS.UserModel.ISheet sheet = null;
var data = new DataTable();
data.TableName = sheetName;
int startRow = 0;
try
{
sheet = sheetName != null?_workbook.GetSheet(sheetName) : _workbook.GetSheetAt(0);
if (sheet != null)
{
var firstRow = sheet.GetRow(0);
if (firstRow == null)
{
return(data);
}
int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数
startRow = isFirstRowColumn ? sheet.FirstRowNum + 1 : sheet.FirstRowNum;
for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
{
//.StringCellValue;
var column = new DataColumn(Convert.ToChar(((int)'A') + i).ToString());
if (isFirstRowColumn)
{
var columnName = firstRow.GetCell(i).StringCellValue;
column = new DataColumn(columnName);
}
data.Columns.Add(column);
}
//最后一列的标号
int rowCount = sheet.LastRowNum;
for (int i = startRow; i <= rowCount; ++i)
{
NPOI.SS.UserModel.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, NPOI.SS.UserModel.MissingCellPolicy.RETURN_NULL_AND_BLANK).ToString();
}
}
data.Rows.Add(dataRow);
}
}
else
{
throw new Exception("Don not have This Sheet");
}
return(data);
}
catch (Exception ex)
{
Console.WriteLine("Exception: " + ex.Message);
return(null);
}
}
