110,502
社区成员
发帖
与我相关
我的任务
分享
/// <summary>
/// 读取excel
/// </summary>
/// <param name="fileName"></param>
/// <returns></returns>
private DataTable LoadExcel(string fileName)
{
int iColumnsCount = 0;//所需要的列的数量判断,用来判断excel格式是否正确.
dtXls = new DataTable();
int iCodeIndex = 0, iNameIndex = 0, iSpecIndex = 0, iUnitIndex = 0;
using (FileStream fs = new FileStream(fileName, FileMode.Open))
{
HSSFWorkbook workbook = new HSSFWorkbook(fs);
ISheet sheet = workbook.GetSheetAt(0);//获取表1
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
if (rows.MoveNext())//读取列名
{
HSSFRow row = (HSSFRow)(rows.Current);
for (int i = 0; i < row.LastCellNum; i++)//遍历行的每一个单元格
{
if (row.Cells[i] != null)
{
string strName = row.Cells[i].ToString().Trim();
switch (strName)
{
case "代码":
iCodeIndex = i;
dtXls.Columns.Add(strName);
iColumnsCount++;
break;
case "全名":
iNameIndex = i;
dtXls.Columns.Add(strName);
iColumnsCount++;
break;
case "规格型号":
iSpecIndex = i;
dtXls.Columns.Add(strName);
iColumnsCount++;
break;
case "基本计量单位":
iUnitIndex = i;
dtXls.Columns.Add(strName);
iColumnsCount++;
break;
}
}
}
}
dtXls.Columns.Add("类别");
if (iColumnsCount != 4)
{
MessageBox.Show("无法获取所需的excel数据!");
return null;
}
//读取内容
while (rows.MoveNext())
{
DataRow dr = dtXls.NewRow();
HSSFRow row = (HSSFRow)rows.Current;
ICell cellCode = row.GetCell(iCodeIndex);
ICell cellName = row.GetCell(iNameIndex);
ICell cellSpec = row.GetCell(iSpecIndex);
ICell cellUnit = row.GetCell(iUnitIndex);
if (cellCode == null)
{
dr[0] = "";
continue;
}
else
{
if ((rdoycl.Checked && !isMathcCode(cellCode.ToString()))||(rdozj.Checked && !isMatchTecCode(cellCode.ToString())))
{
continue;
}
dr[0] = cellCode.ToString();
}
if (cellName == null)
dr[1] = "";
else
dr[1] = cellName.ToString();
if (cellSpec == null)
dr[2] = "";
else
dr[2] = cellSpec.ToString();
if (cellUnit == null)
dr[3] = "";
else
dr[3] = cellUnit.ToString();
dr[4] = GetTypeNameById(dr[0].ToString());
dtXls.Rows.Add(dr);
}
}
return dtXls;
}