{
List<ImportModel> list = new List<ImportModel>();
ImportModel model = null;
try
{
BParseExcel parseExcel = null;
OpenFileDialog ofDlg = new OpenFileDialog();
ofDlg.Filter = "所有文件 (*.*)|*.*|Excel (*.xls)|*.xls";
ofDlg.Multiselect = false;
//System.Windows.Forms.DialogResult dRet = ofDlg.ShowDialog();
if (ofDlg.ShowDialog() == true)
{
string str_end = ofDlg.FileName.Substring(ofDlg.FileName.LastIndexOf(".") + 1);
if (str_end.Equals("xls"))
{
parseExcel = new BParseExcel(ofDlg.FileName);
//parseExcel.setReadSheet("测试");
//申请人
//if (!IsWu(parseExcel.readCell(1, 1).Trim()))
//{
for (int i = 0; i < parseExcel.getRows(); i++)
{
model = new ImportModel();
for (int j = 0; j < parseExcel.getCols(i); j++)
{
string str = parseExcel.readCell(i, j).Trim();
//MessageBox.Show(jsjdm);
if (j == 0)
{
model.jsjdm = str;
}
else
{
model.ajly = str;
}
}
list.Add(model);
}
//第六行第四列
// string jsjdm = parseExcel.readCell(5, 3).Trim();
//string tt = jsjdm;
//}
}
else
{
CMessageBox.ShowWarningMessage("你选择的文件类型不对,请重新选择(.xls)!");
}
}
}
catch (ExcelException ex)
{
CMessageBox.ShowInfoMessage(ex.Message);
}
return list;
}
public class BParseExcel
{
private HSSFWorkbook _workbook = null;
public HSSFWorkbook Workbook
{
get { return _workbook; }
set { _workbook = value; }
}
private HSSFSheet _curSheet = null;
public HSSFSheet CurSheet
{
get { return _curSheet; }
set { _curSheet = value; }
}
#region 构造方法
private BParseExcel()
{
}
/// <summary>
/// 载入Excel
/// 暂支持xls格式的
/// </summary>
/// <param name="fileName"></param>
public BParseExcel(string fileName)
{
try
{
string str_end = fileName.Substring(fileName.LastIndexOf(".") + 1);
FileStream stream = new FileStream(fileName, FileMode.Open, FileAccess.Read);
Workbook = new HSSFWorkbook(stream);
CurSheet = (HSSFSheet)Workbook.GetSheetAt(0);
stream.Close();
}
catch (Exception)
{
throw new ExcelException("载入Excel异常,注意请不要打开需要载入的表格!");
}
}
/// <summary>
/// 获取单Excel中的总行数
/// </summary>
/// <param name="fileName"></param>
public int getRows()
{
return CurSheet.PhysicalNumberOfRows;
}
public int getCols(int row)
{
return CurSheet.GetRow(row).PhysicalNumberOfCells;
}
/// <summary>
/// 载入Excel
/// 暂支持xls格式的
/// </summary>
/// <param name="fileName"></param>
public BParseExcel(string fileName, string sheetName)
{
try
{
string str_end = fileName.Substring(fileName.LastIndexOf(".") + 1);
FileStream stream = new FileStream(fileName, FileMode.Open, FileAccess.Read);
Workbook = new HSSFWorkbook(stream);
CurSheet = (HSSFSheet)Workbook.GetSheet(sheetName);
stream.Close();
}
catch (Exception)
{
throw new ExcelException("载入Excel异常,注意请不要打开需要载入的表格!");
}
}
#endregion
/// <summary>
/// 设置要读取的sheet表格
/// </summary>
/// <param name="sheetName">表格名</param>
/// <returns></returns>
public void setReadSheet(string sheetName)
{
CurSheet=(HSSFSheet)Workbook.GetSheet(sheetName);
}
/// <summary>
/// readCell
/// </summary>
/// <param name="curSheet"></param>
/// <param name="x">行</param>
/// <param name="y">列</param>
/// <returns></returns>
public string readCell(int row, int col)
{
string value = string.Empty;
value = "";
int c = col;// 列
int r = row;// 行
try
{
int sheetRows = CurSheet.PhysicalNumberOfRows;
int sheetCells = CurSheet.GetRow(row).PhysicalNumberOfCells;
if ((c < sheetCells) && (r < sheetRows))
{
// 得到工作表(c,r)的单元格
HSSFCell cell = (HSSFCell)CurSheet.GetRow(r).GetCell(c);
// getContents()将Cell中的字符转为字符串
value = getCellValue(cell);
//判断是否是合并的单元格
if (cell.IsMergedCell)
{
// 如果是合并的单元格则取左上角的值
value = GetValueMergedInfo(CurSheet, r, c);
}
}
else
{
value = "";
Console.WriteLine("Cell is null");
}
}
catch (Exception)
{
// TODO 自动生成 catch 块
throw new ExcelException("获取单元格的值发生异常!");
}
return value;
}
/// <summary>
/// 获取 Table 某个 TD 合并的列数和行数等信息。与 Excel 中对应 Cell 的合并行数和列数一致。
/// </summary>
/// <param name="rowIndex">行号</param>
/// <param name="colIndex">列号</param>
/// <param name="colspan">TD 中需要合并的行数</param>
/// <param name="rowspan">TD 中需要合并的列数</param>
/// <param name="rowspan">此单元格是否被某个行合并包含在内。如果被包含在内,不输出 </param>
/// <returns></returns>
private string GetValueMergedInfo(HSSFSheet sheet, int rowIndex, int colIndex)
{
string cell_str = string.Empty;
int regionsCuont = sheet.NumMergedRegions;
CellRangeAddress region;
for (int i = 0; i < regionsCuont; i++)
{
region = sheet.GetMergedRegion(i);
if (region.FirstRow <= rowIndex && region.FirstColumn <= colIndex &&
region.LastRow >= rowIndex && region.LastColumn >= colIndex)
{
HSSFCell cell=(HSSFCell)sheet.GetRow(region.FirstRow).GetCell(region.FirstColumn);
cell_str = this.getCellValue(cell);
break;
}
}
return cell_str;
}
/// <summary>
/// 根据类型获取数据
/// 目前只处理日期类型
/// </summary>
/// <param name="cell"></param>
/// <returns></returns>
private string getCellValue(HSSFCell cell){
string cellValue = string.Empty;
if (cell.CellType == CellType.NUMERIC)
{
short format = cell.CellStyle.DataFormat;
/**
* 一下内容摘自网上 具体没有验证 如果用时出现问题请及时沟通
*
*Excel中的日期格式在解析为DataTable时会依格式不同解析为不同的值,因为所有的日期格式都可以通过下面DataFormat来判断,
*excel2013,测试结果如下:*时分(HH:mm) - 20,时间(HH:mm:ss) - 21,日期时间(yyyy-MM-dd HH:mm:ss) - 22,
*年月(yyyy-MM) - 17,年月日(yyyy-MM-dd)-14,yyyy年m月-------57,月日(MM-dd) - 58,
*yyyy年m月d日---31,h时mm分 -------32
**/
if (format == 14 || format == 31 || format == 57 || format == 58)
{
DateTime date = cell.DateCellValue;
cellValue = date.ToString("yyy-MM-dd");
}
else
{
cellValue = cell.ToString();
}
}
else {
cellValue = cell.ToString();
}
return cellValue;
}
}