private void buttonExcel_Click(object sender, EventArgs e)
{
FileStream fs = null;
List<ISheet> lstISheet = new List<ISheet>();
string Msg = "";
string filePath = "";
//初始化一个OpenFileDialog类
OpenFileDialog fileDialog = new OpenFileDialog();
//判断用户是否正确的选择了文件
if (fileDialog.ShowDialog() == DialogResult.OK)
{
//获取用户选择文件的后缀名
string extension = Path.GetExtension(fileDialog.FileName);
//声明允许的后缀名
string[] str = new string[] { ".xls", ".xlsx" };
if (!((IList)str).Contains(extension))
{
MessageBox.Show("仅能选择Excel文件");
}
else
{
filePath = fileDialog.FileName;
}
}
if(filePath!="")
{
IWorkbook book2003; IWorkbook book2007;
bool bl2003 = true; bool bl2007 = true;
fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
try
{
book2003 = new HSSFWorkbook(fs);// 2003版本
int t = 0;
while (true)
{
try
{
ISheet sheettemp = book2003.GetSheetAt(t);
lstISheet.Add(sheettemp);
t = t + 1;
}
catch (Exception ex)
{
break;
}
}
}
catch (Exception ex)
{
bl2003 = false;
Msg = ex.Message.ToString() + "\r\n" + ex.StackTrace.ToString();
fs.Dispose();
fs.Close();
}
//
if(bl2003==false)
{
fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
try
{
book2007 = new XSSFWorkbook(fs);// 2007版本
int t = 0;
while (true)
{
try
{
ISheet sheettemp = book2007.GetSheetAt(t);
lstISheet.Add(sheettemp);
t = t+1;
}
catch (Exception ex)
{
break;
}
}
}
catch (Exception ex)
{
bl2007 = false;
Msg = ex.Message.ToString() + "\r\n" + ex.StackTrace.ToString();
}
}
ReLstDataTable(lstISheet);
fs.Dispose();
fs.Close();
}
}
public List<DataTable> ReLstDataTable(List<ISheet> lstISheet)
{
List<DataTable> lstTable = new List<DataTable>();
for (int t = 0; t < lstISheet.Count; t++)
{
ISheet sheet = lstISheet[t];
int rowCount = sheet.LastRowNum;//总行数
if (rowCount > 0)
{
IRow firstRow = sheet.GetRow(0);//第一行
int cellCount = firstRow.LastCellNum;//列数
DataTable dt = new DataTable();
int startRow = 1;//如果第一行是列名，则从第二行开始读取
for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
{
ICell cell = firstRow.GetCell(i);
if (cell != null)
{
if (cell.StringCellValue != null)
{
DataColumn column = new DataColumn(cell.StringCellValue);
dt.Columns.Add(column);
}
}
}
//------------------------------------------------
//填充行
for (int i = startRow; i <= rowCount; ++i)
{
IRow row = sheet.GetRow(i);
if (row == null) continue;
DataRow dataRow = dt.NewRow();
for (int j = row.FirstCellNum; j < cellCount; ++j)
{
ICell cell = row.GetCell(j);
if (cell == null)
{
dataRow[j] = "";
}
else
{
//CellType(Unknown = -1,Numeric = 0,String = 1,Formula = 2,Blank = 3,Boolean = 4,Error = 5,)
switch (cell.CellType)
{
case CellType.Blank:
dataRow[j] = "";
break;
case 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;
else
dataRow[j] = cell.NumericCellValue;
break;
case CellType.String:
dataRow[j] = cell.StringCellValue;
break;
}
}
}
dt.Rows.Add(dataRow);
}
//------------------------------------------------
lstTable.Add(dt);
}
}
return lstTable;
}
public class NPOIExcel
{
#region ========================获取Sheet集合========================
/// <summary>
/// 获取工作表列表
/// </summary>
/// <param name="lstsheet"></param>
/// <returns></returns>
public List<string> LstSheetName(List<ISheet> lstsheet)
{
List<string> lst = new List<string>();
for (int i = 0; i < lstsheet.Count; i++)
{
lst.Add(lstsheet[i].SheetName);
}
return lst;
}
/// <summary>
/// 返回sheet表集合
/// </summary>
/// <param name="file"></param>
/// <returns></returns>
public List<ISheet> ExcelToISheetList(string file)
{
string fileExt = Path.GetExtension(file).ToLower();
List<ISheet> lstSheet = new List<ISheet>();
bool bl = true;
//XSSFWorkbook 适用XLSX格式:2007以上
try
{
lstSheet = GetExcelSheetList2007(file);
}
catch (Exception ex)
{
bl = false;
}
//HSSFWorkbook 适用XLS格式：2003版
if (bl == false)
{
bl = true;
try
{
lstSheet = GetExcelSheetList2003(file);
}
catch (Exception ex)
{
bl = false;
}
}
return lstSheet;
}
/// <summary>
/// 03版获取 sheet列表
/// </summary>
/// <param name="files">文件路径</param>
/// <param name="sheetname">sheet表名</param>
/// <returns>ISheet</returns>
public List<ISheet> GetExcelSheetList2003(string files)
{
List<ISheet> lstsheet = new List<ISheet>();
//HSSFWorkbook 适用XLS格式：2003版
using (FileStream fs = new FileStream(files, FileMode.Open, FileAccess.Read))
{
IWorkbook workbook2003 = new HSSFWorkbook(fs);
int t = 0;
while (true)
{
try
{
ISheet sheet = workbook2003.GetSheetAt(t);
if (sheet != null)
{
lstsheet.Add(sheet);
}
else
{
break;
}
}
catch (Exception)
{
break;
}
t = t + 1;
}
}
return lstsheet;
}
/// <summary>
///获取 2007版列表
/// </summary>
/// <param name="files">文件路径</param>
/// <param name="sheetname">sheet表名</param>
/// <returns>ISheet</returns>
public List<ISheet> GetExcelSheetList2007(string files)
{
List<ISheet> lstsheet = new List<ISheet>();
//XSSFWorkbook 适用XLSX格式:2007以上
using (FileStream fs = new FileStream(files, FileMode.Open, FileAccess.Read))
{
IWorkbook workbook2007 = new XSSFWorkbook(fs);
int t = 0;
while (true)
{
try
{
ISheet sheet = workbook2007.GetSheetAt(t);
if (sheet != null)
{
lstsheet.Add(sheet);
}
else
{
break;
}
}
catch (Exception)
{
break;
}
t = t + 1;
}
}
return lstsheet;
}
#endregion========================获取Sheet集合========================
#region ========================获取sheet表数据========================
/// <summary>
/// 获取sheet的所有列
/// </summary>
/// <param name="sheet"></param>
/// <returns></returns>
public List<string> GetSheetColName(ISheet sheet)
{
//表头
IRow header = sheet.GetRow(sheet.FirstRowNum);
List<string> lstSheetColName = new List<string>();
for (int i = 0; i < header.LastCellNum; i++)
{
object obj = GetValueType(header.GetCell(i));
if (obj == null || obj.ToString() == string.Empty)
{
lstSheetColName.Add("列" + (i + 1));
}
else
{
lstSheetColName.Add(obj.ToString());
}
}
return lstSheetColName;
}
/// <summary>
/// 获取 sheet表数据
/// </summary>
/// <param name="files">文件路径</param>
/// <param name="sheetname">sheet表名</param>
/// <returns>ISheet</returns>
public ISheet GetExcelsheet(string files, string sheetname)
{
List<ISheet> lstsheet = new List<ISheet>();
bool bl = true;
try
{
ISheet sheet = GetExcelsheet2007(files, sheetname);
lstsheet.Add(sheet);
}
catch (Exception ex)
{
bl = false;
}
//HSSFWorkbook 适用XLS格式：2003版
if (bl == false)
{
bl = true;
try
{
ISheet sheet = GetExcelsheet2003(files, sheetname);
lstsheet.Add(sheet);
}
catch (Exception ex)
{
bl = false;
}
}
return lstsheet[0];
}
/// <summary>
/// 03版获取 sheet表数据
/// </summary>
/// <param name="files">文件路径</param>
/// <param name="sheetname">sheet表名</param>
/// <returns>ISheet</returns>
public ISheet GetExcelsheet2003(string files, string sheetname)
{
DataTable dt = new DataTable();
ISheet sheet;
//HSSFWorkbook 适用XLS格式：2003版
using (FileStream fs = new FileStream(files, FileMode.Open, FileAccess.Read))
{
IWorkbook workbook2003 = new HSSFWorkbook(fs);
sheet = workbook2003.GetSheet(sheetname);
}
return sheet;
}
/// <summary>
/// 2007
/// </summary>
/// <param name="files">文件路径</param>
/// <param name="sheetname">sheet表名</param>
/// <returns>ISheet</returns>
public ISheet GetExcelsheet2007(string files, string sheetname)
{
ISheet sheet;
//XSSFWorkbook 适用XLSX格式:2007以上
using (FileStream fs = new FileStream(files, FileMode.Open, FileAccess.Read))
{
IWorkbook workbook2007 = new XSSFWorkbook(fs);
sheet = workbook2007.GetSheet(sheetname);
}
return sheet;
}
#endregion========================获取sheet表数据========================
/// <summary>
/// 获取单元格类型
/// </summary>
/// <param name="cell"></param>
/// <returns></returns>
private static object GetValueType(ICell cell)
{
if (cell == null)
return null;
switch (cell.CellType)
{
case CellType.Blank: //BLANK:
return null;
case CellType.Boolean: //BOOLEAN:
return cell.BooleanCellValue;
case CellType.Numeric: //NUMERIC:
return cell.NumericCellValue;
case CellType.String: //STRING:
return cell.StringCellValue;
case CellType.Error: //ERROR:
return cell.ErrorCellValue;
case CellType.Formula: //FORMULA:
default:
return "=" + cell.CellFormula;
}
}
}
public class ZCDExcel
{
/// <summary>
/// 文件路径
/// </summary>
public string FilePath { get; set; }
public ZCDExcel()
{
}
/// <summary>
/// 配置Excel文件路径
/// </summary>
/// <param name="files"></param>
public ZCDExcel(string files)
{
FilePath = files;
}
#region ========================获取所有sheet表名称的List<string>集合========================
/// <summary>
/// 根据Excel文件名，获取工作表列表
/// </summary>
/// <returns>返回List string 集合</returns>
public List<string> LstSheetName()
{
List<ISheet> lstsheet = ExcelToISheetList(FilePath);
List<string> lst = new List<string>();
for (int i = 0; i < lstsheet.Count; i++)
{
lst.Add(lstsheet[i].SheetName);
}
return lst;
}
/// <summary>
/// 根据Excel文件名，获取工作表列表
/// </summary>
/// <param name="files">文件名</param>
/// <returns>返回List string 集合</returns>
public List<string> LstSheetName(string files)
{
List<ISheet> lstsheet = ExcelToISheetList(files);
List<string> lst = new List<string>();
for (int i = 0; i < lstsheet.Count; i++)
{
lst.Add(lstsheet[i].SheetName);
}
return lst;
}
/// <summary>
/// List ISheet对象集合 获取工作表列表
/// </summary>
/// <param name="lstsheet">List ISheet 对象集合</param>
/// <returns>返回List string 集合</returns>
public List<string> LstSheetName(List<ISheet> lstsheet)
{
List<string> lst = new List<string>();
for (int i = 0; i < lstsheet.Count; i++)
{
lst.Add(lstsheet[i].SheetName);
}
return lst;
}
#endregion========================获取所有sheet表名称的List<string>集合========================
#region ============================获取ISheet对象的List<ISheet>表集合============================
/// <summary>
/// 返回sheet表集合
/// </summary>
/// <returns></returns>
public List<ISheet> ExcelToISheetList()
{
string fileExt = Path.GetExtension(FilePath).ToLower();
List<ISheet> lstSheet = new List<ISheet>();
bool bl = true;
//XSSFWorkbook 适用XLSX格式:2007以上
try
{
lstSheet = GetExcelSheetList2007(FilePath);
}
catch (Exception ex)
{
bl = false;
}
//HSSFWorkbook 适用XLS格式：2003版
if (bl == false)
{
bl = true;
try
{
lstSheet = GetExcelSheetList2003(FilePath);
}
catch (Exception ex)
{
bl = false;
}
}
return lstSheet;
}
/// <summary>
/// 返回sheet表集合
/// </summary>
/// <param name="file">文件路径</param>
/// <returns>ISheet的list集合</returns>
public List<ISheet> ExcelToISheetList(string file)
{
string fileExt = Path.GetExtension(file).ToLower();
List<ISheet> lstSheet = new List<ISheet>();
bool bl = true;
//XSSFWorkbook 适用XLSX格式:2007以上
try
{
lstSheet = GetExcelSheetList2007(file);
}
catch (Exception ex)
{
bl = false;
}
//HSSFWorkbook 适用XLS格式：2003版
if (bl == false)
{
bl = true;
try
{
lstSheet = GetExcelSheetList2003(file);
}
catch (Exception ex)
{
bl = false;
}
}
return lstSheet;
}
#endregion ============================获取ISheet对象的List<ISheet>表集合============================
#region ============================03版和07版获取 List<ISheet>============================
/// <summary>
/// 03版获取 sheet列表
/// </summary>
/// <param name="files">文件路径</param>
/// <param name="sheetname">sheet表名</param>
/// <returns>ISheet</returns>
public List<ISheet> GetExcelSheetList2003(string files)
{
List<ISheet> lstsheet = new List<ISheet>();
//HSSFWorkbook 适用XLS格式：2003版
using (FileStream fs = new FileStream(files, FileMode.Open, FileAccess.Read))
{
IWorkbook workbook2003 = new HSSFWorkbook(fs);
int t = 0;
while (true)
{
try
{
ISheet sheet = workbook2003.GetSheetAt(t);
if (sheet != null)
{
lstsheet.Add(sheet);
}
else
{
break;
}
}
catch (Exception)
{
break;
}
t = t + 1;
}
}
return lstsheet;
}
/// <summary>
///获取 2007版列表
/// </summary>
/// <param name="files">文件路径</param>
/// <param name="sheetname">sheet表名</param>
/// <returns>ISheet</returns>
public List<ISheet> GetExcelSheetList2007(string files)
{
List<ISheet> lstsheet = new List<ISheet>();
//XSSFWorkbook 适用XLSX格式:2007以上
using (FileStream fs = new FileStream(files, FileMode.Open, FileAccess.Read))
{
IWorkbook workbook2007 = new XSSFWorkbook(fs);
int t = 0;
while (true)
{
try
{
ISheet sheet = workbook2007.GetSheetAt(t);
if (sheet != null)
{
lstsheet.Add(sheet);
}
else
{
break;
}
}
catch (Exception)
{
break;
}
t = t + 1;
}
}
return lstsheet;
}
#endregion ==============03版和07版获取 List<ISheet>==============
#region ========================获取sheet对象========================
/// <summary>
/// 根据sheet表索引 获取 sheet表数据 ；文件路径需要事先配置好文件路径
/// </summary>
/// <param name="Index">工作表（sheet表）索引</param>
/// <returns>ISheet</returns>
public ISheet GetExcelsheet(int Index)
{
List<ISheet> lstsheet = new List<ISheet>();
bool bl = true;
try
{
ISheet sheet = GetExcelsheet2007(FilePath, Index);
lstsheet.Add(sheet);
}
catch (Exception ex)
{
bl = false;
}
//HSSFWorkbook 适用XLS格式：2003版
if (bl == false)
{
bl = true;
try
{
ISheet sheet = GetExcelsheet2003(FilePath, Index);
lstsheet.Add(sheet);
}
catch (Exception ex)
{
bl = false;
}
}
return lstsheet[0];
}
/// <summary>
/// 根据sheet表名 获取 sheet表数据 ；文件路径需要事先配置好文件路径
/// </summary>
/// <param name="sheetname">sheet表名</param>
/// <returns>ISheet</returns>
public ISheet GetExcelsheet(string sheetname)
{
List<ISheet> lstsheet = new List<ISheet>();
bool bl = true;
try
{
ISheet sheet = GetExcelsheet2007(FilePath, sheetname);
lstsheet.Add(sheet);
}
catch (Exception ex)
{
bl = false;
}
//HSSFWorkbook 适用XLS格式：2003版
if (bl == false)
{
bl = true;
try
{
ISheet sheet = GetExcelsheet2003(FilePath, sheetname);
lstsheet.Add(sheet);
}
catch (Exception ex)
{
bl = false;
}
}
return lstsheet[0];
}
/// <summary>
/// 根据sheet表索引 获取 sheet表数据
/// </summary>
/// <param name="files">文件路径</param>
/// <param name="Index">工作表（sheet表）索引</param>
/// <returns>ISheet</returns>
public ISheet GetExcelsheet(string files, int Index)
{
List<ISheet> lstsheet = new List<ISheet>();
bool bl = true;
try
{
ISheet sheet = GetExcelsheet2007(files, Index);
lstsheet.Add(sheet);
}
catch (Exception ex)
{
bl = false;
}
//HSSFWorkbook 适用XLS格式：2003版
if (bl == false)
{
bl = true;
try
{
ISheet sheet = GetExcelsheet2003(files, Index);
lstsheet.Add(sheet);
}
catch (Exception ex)
{
bl = false;
}
}
return lstsheet[0];
}
/// <summary>
/// 根据sheet表名 获取 sheet表数据
/// </summary>
/// <param name="files">文件路径</param>
/// <param name="sheetname">sheet表名</param>
/// <returns>ISheet</returns>
public ISheet GetExcelsheet(string files, string sheetname)
{
List<ISheet> lstsheet = new List<ISheet>();
bool bl = true;
try
{
ISheet sheet = GetExcelsheet2007(files, sheetname);
lstsheet.Add(sheet);
}
catch (Exception ex)
{
bl = false;
}
//HSSFWorkbook 适用XLS格式：2003版
if (bl == false)
{
bl = true;
try
{
ISheet sheet = GetExcelsheet2003(files, sheetname);
lstsheet.Add(sheet);
}
catch (Exception ex)
{
bl = false;
}
}
return lstsheet[0];
}
/// <summary>
/// 03版 根据sheet表名 获取 sheet表数据
/// </summary>
/// <param name="files">文件路径</param>
/// <param name="sheetname">sheet表名</param>
/// <returns>ISheet</returns>
public ISheet GetExcelsheet2003(string files, string sheetname)
{
DataTable dt = new DataTable();
ISheet sheet;
//HSSFWorkbook 适用XLS格式：2003版
using (FileStream fs = new FileStream(files, FileMode.Open, FileAccess.Read))
{
IWorkbook workbook2003 = new HSSFWorkbook(fs);
sheet = workbook2003.GetSheet(sheetname);
}
return sheet;
}
/// <summary>
/// 03版 根据sheet表索引 获取 sheet表数据
/// </summary>
/// <param name="files">文件路径</param>
/// <param name="Index">sheet表索引</param>
/// <returns>ISheet</returns>
public ISheet GetExcelsheet2003(string files, int Index)
{
DataTable dt = new DataTable();
ISheet sheet;
//HSSFWorkbook 适用XLS格式：2003版
using (FileStream fs = new FileStream(files, FileMode.Open, FileAccess.Read))
{
IWorkbook workbook2003 = new HSSFWorkbook(fs);
sheet = workbook2003.GetSheetAt(Index);
}
return sheet;
}
/// <summary>
/// 2007版 更加sheet表名 获取sheet对象
/// </summary>
/// <param name="files">文件路径</param>
/// <param name="sheetname">sheet表名</param>
/// <returns>ISheet</returns>
public ISheet GetExcelsheet2007(string files, string sheetname)
{
ISheet sheet;
//XSSFWorkbook 适用XLSX格式:2007以上
using (FileStream fs = new FileStream(files, FileMode.Open, FileAccess.Read))
{
IWorkbook workbook2007 = new XSSFWorkbook(fs);
sheet = workbook2007.GetSheet(sheetname);
}
return sheet;
}
/// <summary>
/// 2007版 根据sheet表索引 获取sheet对象
/// </summary>
/// <param name="files">文件路径</param>
/// <param name="Index">sheet表索引</param>
/// <returns>ISheet</returns>
public ISheet GetExcelsheet2007(string files, int Index)
{
ISheet sheet;
//XSSFWorkbook 适用XLSX格式:2007以上
using (FileStream fs = new FileStream(files, FileMode.Open, FileAccess.Read))
{
IWorkbook workbook2007 = new XSSFWorkbook(fs);
sheet = workbook2007.GetSheetAt(Index);//.GetSheet(sheetname);
}
return sheet;
}
#endregion========================获取sheet表数据========================
#region ======================返回工作表数据DataTable或者DataSet=======================
/// <summary>
/// 返回第一个sheet表的数据
/// </summary>
/// <returns>返回DataTable</returns>
public DataTable GetFistSheetTable()
{
ISheet sheet = GetExcelsheet(FilePath, 0);
DataTable dt = GetSheetTable(sheet);
return dt;
}
/// <summary>
/// 返回第一个sheet表的数据
/// </summary>
/// <param name="files">文件路径</param>
/// <returns>返回DataTable</returns>
public DataTable GetFistSheetTable(string files)
{
ISheet sheet = GetExcelsheet(files, 0);
DataTable dt = GetSheetTable(sheet);
return dt;
}
/// <summary>
/// 返回最后个sheet表的数据
/// </summary>
/// <returns>返回DataTable</returns>
public DataTable GetLastSheetTable()
{
List<string> lst = LstSheetName(FilePath);
ISheet sheet = GetExcelsheet(FilePath, lst.Count - 1);
DataTable dt = GetSheetTable(sheet);
return dt;
}
/// <summary>
/// 返回最后个sheet表的数据
/// </summary>
/// <param name="files">文件路径</param>
/// <returns>返回DataTable</returns>
public DataTable GetLastSheetTable(string files)
{
List<string> lst = LstSheetName(files);
ISheet sheet = GetExcelsheet(files, lst.Count - 1);
DataTable dt = GetSheetTable(sheet);
return dt;
}
/// <summary>
/// 返回第一个sheet表的数据
/// </summary>
/// <param name="Index">文件路径</param>
/// <returns>返回DataTable</returns>
public DataTable GetSheetTable(int Index)
{
ISheet sheet = GetExcelsheet(FilePath, Index);
DataTable dt = GetSheetTable(sheet);
return dt;
}
/// <summary>
/// 返回第一个sheet表的数据
/// </summary>
/// <param name="SheetName">工作表名称</param>
/// <returns>返回DataTable</returns>
public DataTable GetSheetTable(string SheetName)
{
ISheet sheet = GetExcelsheet(FilePath, SheetName);
DataTable dt = GetSheetTable(sheet);
return dt;
}
/// <summary>
/// 返回第一个sheet表的数据
/// </summary>
/// <param name="files">文件路径</param>
/// <param name="SheetName">工作表名称</param>
/// <returns>返回DataTable</returns>
public DataTable GetSheetTable(string files,string SheetName)
{
ISheet sheet = GetExcelsheet(files, SheetName);
DataTable dt = GetSheetTable(sheet);
return dt;
}
/// <summary>
/// 返回第一个sheet表的数据
/// </summary>
/// <param name="files">文件路径</param>
/// <param name="Index">工作表索引</param>
/// <returns>返回DataTable</returns>
public DataTable GetSheetTable(string files, int Index)
{
ISheet sheet = GetExcelsheet(files, Index);
DataTable dt = GetSheetTable(sheet);
return dt;
}
/// <summary>
/// 返回 Excel所有工作表的数据
/// </summary>
/// <returns>DataSet</returns>
public DataSet GetSheetDataSet()
{
List<ISheet> LstSheet = ExcelToISheetList(FilePath);
DataSet ds = GetSheetDataSet(LstSheet);
return ds;
}
/// <summary>
/// 返回 Excel所有工作表的数据
/// </summary>
/// <param name="file">Excel文件路径</param>
/// <returns>DataSet</returns>
public DataSet GetSheetDataSet(string file)
{
List<ISheet> LstSheet = ExcelToISheetList(file);
DataSet ds = GetSheetDataSet(LstSheet);
return ds;
}
/// <summary>
/// 根据Sheet对象，获取sheet表的数据
/// </summary>
/// <param name="sheet">工作表对象</param>
/// <returns>返回DataTable</returns>
public DataTable GetSheetTable(ISheet sheet)
{
DataTable dt = new DataTable();
dt.TableName = sheet.SheetName;
//表头
IRow header = sheet.GetRow(sheet.FirstRowNum);
List<int> columns = new List<int>();
for (int i = 0; i < header.LastCellNum; i++)
{
object obj = GetValueType(header.GetCell(i));
if (obj == null || obj.ToString() == string.Empty)
{
dt.Columns.Add(new DataColumn("列" + (i + 1).ToString()));
}
else
{
dt.Columns.Add(new DataColumn(obj.ToString()));
}
columns.Add(i);
}
//数据
for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)
{
DataRow dr = dt.NewRow();
bool hasValue = false;
foreach (int j in columns)
{
dr[j] = GetValueType(sheet.GetRow(i).GetCell(j));
if (dr[j] != null && dr[j].ToString() != string.Empty)
{
hasValue = true;
}
}
if (hasValue)
{
dt.Rows.Add(dr);
}
}
return dt;
}
/// <summary>
/// 根据Sheet对象，获取sheet表的数据
/// </summary>
/// <param name="sheet">工作表对象</param>
/// <returns>返回DataTable</returns>
public DataSet GetSheetDataSet(List<ISheet> LstSheet)
{
DataSet ds = new DataSet();
for (int t = 0; t < LstSheet.Count; t++)
{
DataTable dt = new DataTable();
ISheet sheet = LstSheet[t];
if (sheet != null)
{
dt.TableName = sheet.SheetName;
}
//表头
IRow header = sheet.GetRow(sheet.FirstRowNum);
List<int> columns = new List<int>();
for (int i = 0; i < header.LastCellNum; i++)
{
object obj = GetValueType(header.GetCell(i));
if (obj == null || obj.ToString() == string.Empty)
{
dt.Columns.Add(new DataColumn("列" + (i + 1).ToString()));
}
else
{
dt.Columns.Add(new DataColumn(obj.ToString()));
}
columns.Add(i);
}
//数据
for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)
{
DataRow dr = dt.NewRow();
bool hasValue = false;
foreach (int j in columns)
{
dr[j] = GetValueType(sheet.GetRow(i).GetCell(j));
if (dr[j] != null && dr[j].ToString() != string.Empty)
{
hasValue = true;
}
}
if (hasValue)
{
dt.Rows.Add(dr);
}
}
ds.Tables.Add(dt);
}
return ds;
}
#endregion ======================返回工作表数据DataTable或者DataSet=======================
#region ==============================获取特定的sheet的所有列的名称=============================
/// <summary>
/// 根据Sheet表索引，获取sheet的所有列的名称
/// </summary>
/// <param name="Index">Sheet表的索引</param>
/// <returns> List字符串集合 列名称 </returns>
public List<string> GetSheetColName(int Index)
{
ISheet sheet = GetExcelsheet(FilePath, Index);
//表头
IRow header = sheet.GetRow(sheet.FirstRowNum);
List<string> lstSheetColName = new List<string>();
for (int i = 0; i < header.LastCellNum; i++)
{
object obj = GetValueType(header.GetCell(i));
if (obj == null || obj.ToString() == string.Empty)
{
lstSheetColName.Add("列" + (i + 1));
}
else
{
lstSheetColName.Add(obj.ToString());
}
}
return lstSheetColName;
}
/// <summary>
/// 获取最后一个sheet表的所有列的名称
/// </summary>
/// <returns> List字符串集合 列名称 </returns>
public List<string> GetLastSheetColName()
{
List<string> lst = LstSheetName(FilePath);
ISheet sheet = GetExcelsheet(FilePath,lst.Count-1);
//表头
IRow header = sheet.GetRow(sheet.FirstRowNum);
List<string> lstSheetColName = new List<string>();
for (int i = 0; i < header.LastCellNum; i++)
{
object obj = GetValueType(header.GetCell(i));
if (obj == null || obj.ToString() == string.Empty)
{
lstSheetColName.Add("列" + (i + 1));
}
else
{
lstSheetColName.Add(obj.ToString());
}
}
return lstSheetColName;
}
/// <summary>
/// 获取最后一个sheet表的所有列的名称
/// </summary>
/// <param name="Files">文件路径</param>
/// <returns> List字符串集合 列名称 </returns>
public List<string> GetLastSheetColName(string Files)
{
List<string> lst = LstSheetName(FilePath);
ISheet sheet = GetExcelsheet(Files, lst.Count-1);
//表头
IRow header = sheet.GetRow(sheet.FirstRowNum);
List<string> lstSheetColName = new List<string>();
for (int i = 0; i < header.LastCellNum; i++)
{
object obj = GetValueType(header.GetCell(i));
if (obj == null || obj.ToString() == string.Empty)
{
lstSheetColName.Add("列" + (i + 1));
}
else
{
lstSheetColName.Add(obj.ToString());
}
}
return lstSheetColName;
}
/// <summary>
/// 根据Sheet表名称，获取sheet的所有列的名称
/// </summary>
/// <returns> List字符串集合 列名称 </returns>
public List<string> GetSheetColName(string SheetName)
{
ISheet sheet = GetExcelsheet(FilePath, SheetName);
//表头
IRow header = sheet.GetRow(sheet.FirstRowNum);
List<string> lstSheetColName = new List<string>();
for (int i = 0; i < header.LastCellNum; i++)
{
object obj = GetValueType(header.GetCell(i));
if (obj == null || obj.ToString() == string.Empty)
{
lstSheetColName.Add("列" + (i + 1));
}
else
{
lstSheetColName.Add(obj.ToString());
}
}
return lstSheetColName;
}
/// <summary>
/// 根据Sheet对象，获取sheet的所有列的名称
/// </summary>
/// <param name="sheet">ISheet 对象</param>
/// <returns> List字符串集合 列名称 </returns>
public List<string> GetSheetColName(ISheet sheet)
{
//表头
IRow header = sheet.GetRow(sheet.FirstRowNum);
List<string> lstSheetColName = new List<string>();
for (int i = 0; i < header.LastCellNum; i++)
{
object obj = GetValueType(header.GetCell(i));
if (obj == null || obj.ToString() == string.Empty)
{
lstSheetColName.Add("列" + (i + 1));
}
else
{
lstSheetColName.Add(obj.ToString());
}
}
return lstSheetColName;
}
/// <summary>
/// 根据Sheet表索引，获取sheet的所有列的名称
/// </summary>
/// <param name="Index">Sheet表的索引</param>
/// <returns> List字符串集合 列名称 </returns>
public List<string> GetSheetColName(string files, int Index)
{
ISheet sheet = GetExcelsheet(files, Index);
//表头
IRow header = sheet.GetRow(sheet.FirstRowNum);
List<string> lstSheetColName = new List<string>();
for (int i = 0; i < header.LastCellNum; i++)
{
object obj = GetValueType(header.GetCell(i));
if (obj == null || obj.ToString() == string.Empty)
{
lstSheetColName.Add("列" + (i + 1));
}
else
{
lstSheetColName.Add(obj.ToString());
}
}
return lstSheetColName;
}
/// <summary>
/// 根据Sheet表名称，获取sheet的所有列的名称
/// </summary>
/// <param name="SheetName">Sheet表的名称</param>
/// <returns> List字符串集合 列名称 </returns>
public List<string> GetSheetColName(string files, string SheetName)
{
ISheet sheet = GetExcelsheet(files, SheetName);
//表头
IRow header = sheet.GetRow(sheet.FirstRowNum);
List<string> lstSheetColName = new List<string>();
for (int i = 0; i < header.LastCellNum; i++)
{
object obj = GetValueType(header.GetCell(i));
if (obj == null || obj.ToString() == string.Empty)
{
lstSheetColName.Add("列" + (i + 1));
}
else
{
lstSheetColName.Add(obj.ToString());
}
}
return lstSheetColName;
}
#endregion =====================获取特定的sheet的所有列的名称====================
/// <summary>
/// 获取单元格类型
/// </summary>
/// <param name="cell"></param>
/// <returns></returns>
private static object GetValueType(ICell cell)
{
if (cell == null)
return null;
switch (cell.CellType)
{
case CellType.Blank: //BLANK:
return null;
case CellType.Boolean: //BOOLEAN:
return cell.BooleanCellValue;
case CellType.Numeric: //NUMERIC:
return cell.NumericCellValue;
case CellType.String: //STRING:
return cell.StringCellValue;
case CellType.Error: //ERROR:
return cell.ErrorCellValue;
case CellType.Formula: //FORMULA:
default:
return "=" + cell.CellFormula;
}
}
}
