/// <summary> /// 读取Excel流到DataSet /// </summary> /// <param name="stream">Excel流</param> /// <param name="dict">字典参数,key:sheet名,value:列头所在行号,-1表示没有列头</param> /// <returns>Excel中的数据</returns> public static DataSet ImportExceltoDs(Stream stream, Dictionary<string, int> dict) { try { DataSet ds = new DataSet(); IWorkbook wb; using (stream) { wb = WorkbookFactory.Create(stream); } foreach (string key in dict.Keys) { DataTable dt = new DataTable(); ISheet sheet = wb.GetSheet(key); dt = ImportDt(sheet, dict[key], true); dt.TableName = key; ds.Tables.Add(dt); } return ds; } catch (Exception) { throw; } } /// <summary> /// 将制定sheet中的数据导出到datatable中 /// </summary> /// <param name="sheet">需要导出的sheet</param> /// <param name="HeaderRowIndex">列头所在行号,-1表示没有列头</param> /// <returns></returns> static DataTable ImportDt(ISheet sheet, int HeaderRowIndex, bool needHeader) { DataTable table = new DataTable(); IRow headerRow; int cellCount; try { if (HeaderRowIndex < 0 || !needHeader) { headerRow = sheet.GetRow(0); cellCount = headerRow.LastCellNum; for (int i = headerRow.FirstCellNum; i <= cellCount; i++) { DataColumn column = new DataColumn(Convert.ToString(i)); table.Columns.Add(column); } } else { headerRow = sheet.GetRow(HeaderRowIndex); cellCount = headerRow.LastCellNum; for (int i = headerRow.FirstCellNum; i < cellCount; i++) { if (headerRow.GetCell(i) == null) { if (table.Columns.IndexOf(Convert.ToString(i)) > 0) { DataColumn column = new DataColumn(Convert.ToString("重复列名" + i)); table.Columns.Add(column); } else { DataColumn column = new DataColumn(Convert.ToString(i)); table.Columns.Add(column); } } else if (table.Columns.IndexOf(headerRow.GetCell(i).ToString()) > 0) { DataColumn column = new DataColumn(Convert.ToString("重复列名" + i)); table.Columns.Add(column); } else { DataColumn column = new DataColumn(headerRow.GetCell(i).ToString()); table.Columns.Add(column); } } } int rowCount = sheet.LastRowNum; for (int i = (HeaderRowIndex + 1); i <= sheet.LastRowNum; i++) { try { IRow row; if (sheet.GetRow(i) == null) { row = sheet.CreateRow(i); } else { row = sheet.GetRow(i); } DataRow dataRow = table.NewRow(); for (int j = row.FirstCellNum; j <= cellCount; j++) { try { if (row.GetCell(j) != null) { switch (row.GetCell(j).CellType) { case CellType.String: string str = row.GetCell(j).StringCellValue; if (str != null && str.Length > 0) { dataRow[j] = str.ToString(); } else { dataRow[j] = null; } break; case CellType.Numeric: if (DateUtil.IsCellDateFormatted(row.GetCell(j))) { dataRow[j] = DateTime.FromOADate(row.GetCell(j).NumericCellValue); } else { dataRow[j] = Convert.ToDouble(row.GetCell(j).NumericCellValue); } break; case CellType.Boolean: dataRow[j] = Convert.ToString(row.GetCell(j).BooleanCellValue); break; case CellType.Error: dataRow[j] = ErrorEval.GetText(row.GetCell(j).ErrorCellValue); break; case CellType.Formula: switch (row.GetCell(j).CachedFormulaResultType) { case CellType.String: string strFORMULA = row.GetCell(j).StringCellValue; if (strFORMULA != null && strFORMULA.Length > 0) { dataRow[j] = strFORMULA.ToString(); } else { dataRow[j] = null; } break; case CellType.Numeric: dataRow[j] = Convert.ToString(row.GetCell(j).NumericCellValue); break; case CellType.Boolean: dataRow[j] = Convert.ToString(row.GetCell(j).BooleanCellValue); break; case CellType.Error: dataRow[j] = ErrorEval.GetText(row.GetCell(j).ErrorCellValue); break; default: dataRow[j] = ""; break; } break; default: dataRow[j] = ""; break; } } } catch (Exception exception) { throw; } } table.Rows.Add(dataRow); } catch (Exception exception) { throw; } } } catch (Exception exception) { throw; } return table; } /// <summary> /// 读取CSV文件 /// </summary> public static DataTable DarazReadCSV(Stream fs) { try { DataTable dt = new DataTable(); StreamReader sr = new StreamReader(fs, Encoding.UTF8); //记录每次读取的一行记录 string strLine = null; //记录每行记录中的各字段内容 //string[] arrayLine = null; //分隔符 //string[] separators = { "," }; //判断,若是第一次,建立表头 bool isFirst = true; string[] aryline; //逐行读取CSV文件 while ((strLine = sr.ReadLine()) != null) { strLine = strLine.Trim();//去除头尾空格 if (isFirst) { aryline = strToAry(strLine);//请注意:此处变了 } else { MatchCollection mc = Regex.Matches(strLine, "(?<=^|,)(\"(?:[^\"]|\"\")*\"|[^,]*)"); aryline = new string[mc.Count]; for (int i = 0; i < mc.Count; i++) { aryline[i] = mc[i].Groups[0].Value; } } int dtColumns = aryline.Length;//列的个数 if (isFirst) //建立表头 { for (int i = 0; i < dtColumns; i++) { dt.Columns.Add(aryline[i]);//每一列名称 } isFirst = false; } else //表内容 { //设置变量num,获取i的最大值 int y = 0; DataRow dataRow = dt.NewRow();//新建一行 for (int j = 0; j < dtColumns; j++) { y = j; if (j == dtColumns) break; dataRow[j] = aryline[j]; } if (y != dtColumns && dataRow != null) { dt.Rows.Add(dataRow);//添加一行 } } } sr.Close(); fs.Close(); return dt; } catch (Exception ex) { throw; } } } /// <summary> /// 将excel导入到datatable /// </summary> /// <param name="filePath">excel路径</param> /// <param name="isColumnName">第一行是否是列名</param> /// <returns>返回datatable</returns> public static DataTable ExcelToDataTable(string filePath, bool isColumnName) { 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)) { // 2007版本 if (filePath.IndexOf(".xlsx") > 0) workbook = new XSSFWorkbook(fs); // 2003版本 else if (filePath.IndexOf(".xls") > 0) workbook = new HSSFWorkbook(fs); if (workbook != null) { sheet = workbook.GetSheetAt(0);//读取第一个sheet,当然也可以循环读取每个sheet dataTable = new DataTable(); 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 = new DataColumn(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 { //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) // { // if (string.IsNullOrEmpty(cell.StringCellValue)) dataRow[j] = cell.NumericCellValue; // dataRow[j] = cell.DateCellValue; // } // else { // dataRow[j] = cell.NumericCellValue; // } // break; //case CellType.String: // dataRow[j] = cell.StringCellValue; // break; case CellType.String: string str = row.GetCell(j).StringCellValue; if (str != null && str.Length > 0) { dataRow[j] = str.ToString(); } else { dataRow[j] = null; } break; case CellType.Numeric: if (DateUtil.IsCellDateFormatted(row.GetCell(j))) { dataRow[j] = DateTime.FromOADate(row.GetCell(j).NumericCellValue); } else { dataRow[j] = Convert.ToDouble(row.GetCell(j).NumericCellValue); } break; case CellType.Boolean: dataRow[j] = Convert.ToString(row.GetCell(j).BooleanCellValue); break; case CellType.Error: dataRow[j] = ErrorEval.GetText(row.GetCell(j).ErrorCellValue); break; case CellType.Formula: switch (row.GetCell(j).CachedFormulaResultType) { case CellType.String: string strFORMULA = row.GetCell(j).StringCellValue; if (strFORMULA != null && strFORMULA.Length > 0) { dataRow[j] = strFORMULA.ToString(); } else { dataRow[j] = null; } break; case CellType.Numeric: dataRow[j] = Convert.ToString(row.GetCell(j).NumericCellValue); break; case CellType.Boolean: dataRow[j] = Convert.ToString(row.GetCell(j).BooleanCellValue); break; case CellType.Error: dataRow[j] = ErrorEval.GetText(row.GetCell(j).ErrorCellValue); break; default: dataRow[j] = ""; break; } break; default: dataRow[j] = ""; break; } } } dataTable.Rows.Add(dataRow); } } } } } return dataTable; } catch (Exception ex) { if (fs != null) { fs.Close(); } return null; } } #region /// <summary> /// 导入Excel得到 DataTable /// </summary> public static DataTable ImportExceltoDt(Stream stream, int HeaderRowIndex=0) { IWorkbook wb; using (stream) { wb = WorkbookFactory.Create(stream); } ISheet sheet = wb.GetSheetAt(0); DataTable table = new DataTable(); IRow headerRow; int cellCount; try { headerRow = sheet.GetRow(HeaderRowIndex); cellCount = headerRow.LastCellNum; for (int i = headerRow.FirstCellNum; i < cellCount; i++) { if (headerRow.GetCell(i) == null) { if (table.Columns.IndexOf(Convert.ToString(i)) > 0) { DataColumn column = new DataColumn(Convert.ToString("重复列名" + i)); table.Columns.Add(column); } else { DataColumn column = new DataColumn(Convert.ToString(i)); table.Columns.Add(column); } } else if (table.Columns.IndexOf(headerRow.GetCell(i).ToString()) > 0) { DataColumn column = new DataColumn(Convert.ToString("重复列名" + i)); table.Columns.Add(column); } else { DataColumn column = new DataColumn(headerRow.GetCell(i).ToString()); table.Columns.Add(column); } } int rowCount = sheet.LastRowNum; for (int i = (HeaderRowIndex + 1); i <= sheet.LastRowNum; i++) { try { IRow row; if (sheet.GetRow(i) == null) { row = sheet.CreateRow(i); } else { row = sheet.GetRow(i); } DataRow dataRow = table.NewRow(); for (int j = row.FirstCellNum; j <= cellCount; j++) { try { if (row.GetCell(j) != null) { switch (row.GetCell(j).CellType) { case CellType.String: string str = row.GetCell(j).StringCellValue; if (str != null && str.Length > 0) { dataRow[j] = str.ToString(); } else { dataRow[j] = null; } break; case CellType.Numeric: if (DateUtil.IsCellDateFormatted(row.GetCell(j))) { dataRow[j] = DateTime.FromOADate(row.GetCell(j).NumericCellValue); } else { dataRow[j] = Convert.ToDouble(row.GetCell(j).NumericCellValue); } break; case CellType.Boolean: dataRow[j] = Convert.ToString(row.GetCell(j).BooleanCellValue); break; case CellType.Error: dataRow[j] = ErrorEval.GetText(row.GetCell(j).ErrorCellValue); break; case CellType.Formula: switch (row.GetCell(j).CachedFormulaResultType) { case CellType.String: string strFORMULA = row.GetCell(j).StringCellValue; if (strFORMULA != null && strFORMULA.Length > 0) { dataRow[j] = strFORMULA.ToString(); } else { dataRow[j] = null; } break; case CellType.Numeric: dataRow[j] = Convert.ToString(row.GetCell(j).NumericCellValue); break; case CellType.Boolean: dataRow[j] = Convert.ToString(row.GetCell(j).BooleanCellValue); break; case CellType.Error: dataRow[j] = ErrorEval.GetText(row.GetCell(j).ErrorCellValue); break; default: dataRow[j] = ""; break; } break; default: dataRow[j] = ""; break; } } } catch (Exception exception) { throw; } } table.Rows.Add(dataRow); } catch (Exception exception) { throw; } } } catch (Exception exception) { throw; } return table; }
标签:帮助,Excel,break,dataRow,ToString,GetCell,Net,null,row From: https://www.cnblogs.com/JerrChamplons/p/13792822.html