public NpoiWorksheet(HSSFWorkbook book, ISheet sheet) { sheet.ForceFormulaRecalculation = true; Book = book; Sheet = sheet; Index = book.GetSheetIndex(sheet); Name = book.GetSheetName(Index); }
public static ArrayList GetSheetName(string outputFile) { ArrayList arrayList = new ArrayList(); try { FileStream readfile = new FileStream(outputFile, FileMode.Open, FileAccess.Read); HSSFWorkbook hssfworkbook = new HSSFWorkbook(readfile); for (int i = 0; i < hssfworkbook.NumberOfSheets; i++) { arrayList.Add(hssfworkbook.GetSheetName(i)); } } catch (Exception exception) { //wl.WriteLogs(exception.ToString()); } return arrayList; }
private string ImportStudentPointExcel(HttpPostedFile postedFile, string schoolNo, string banjiName, string examTime, string examType, string examName, List<StudentPointModel> newstudentPoints) { try { var ext = Path.GetExtension(postedFile.FileName); ISheet sheet; if (ext == ".xls") { //office 97-2003 HSSFWorkbook wk = new HSSFWorkbook(postedFile.InputStream); sheet = wk.GetSheet(wk.GetSheetName(0)); } else if (ext == ".xlsx") { //office 2007-now XSSFWorkbook wk = new XSSFWorkbook(postedFile.InputStream); sheet = wk.GetSheet(wk.GetSheetName(0)); } else { return "请使用xls或xlsx格式"; } if (sheet.LastRowNum < 1) { return "表内容不能为空"; } Dictionary<string, int> rowName = new Dictionary<string, int>(); IList<QueryModel> qmList = new List<QueryModel>(); qmList.Add(MakeUtil.getQueryModel("SchoolNo", "'", SqlWhere.WhereOperator.Equal, schoolNo)); if (banjiName != "") { qmList.Add(MakeUtil.getQueryModel("D_Name", "'", SqlWhere.WhereOperator.Equal, banjiName)); } var students = _departStaffEbi.getModelList(qmList, -1); qmList.Clear(); qmList.Add(MakeUtil.getQueryModel("SchoolNo", "'", SqlWhere.WhereOperator.Equal, schoolNo)); if (banjiName != "") { qmList.Add(MakeUtil.getQueryModel("BanjiName", "'", SqlWhere.WhereOperator.Equal, banjiName)); } qmList.Add(MakeUtil.getQueryModel("ExamType", "'", SqlWhere.WhereOperator.Equal, examType)); qmList.Add(MakeUtil.getQueryModel("ExamName", "'", SqlWhere.WhereOperator.Equal, examName)); var studentpoints = _studentPointEbi.getModelList(qmList, -1); // if (sheet.LastRowNum > students.Count) // { // return "Excel的导入内容比学生人数多"; // } for (int j = 0; j <= sheet.LastRowNum; j++) //LastRowNum 是当前表的总行数 { IRow row = sheet.GetRow(j); //读取当前行数据 //先读取表头信息 if (j == 0) { if (row != null) { for (int k = 0; k <= row.LastCellNum; k++) //LastCellNum 是当前行的总列数 { ICell cell = row.GetCell(k); //当前表格 if (cell != null && cell.ToString() != "") { //获取表头信息 rowName.Add(cell.ToString(), k); } } } } else { if (row != null) { var banji = row.GetCell(rowName["班级名称"]).ToString(); var xinming = row.GetCell(rowName["姓名"]).ToString(); var bianhao = row.GetCell(rowName["编号"]).ToString(); if (banjiName != "" && banji != banjiName) { return string.Format("Excel中的班级名称有误,你选择了 {0} 这个班级,Excel中不应该再出现别的班级", banjiName); } //根据前四项找出学生的成绩资料 if (!students.Any(p => p.D_Name == banji && p.DS_Name == xinming && p.DS_JID == bianhao)) { return string.Format("在班级{2}中找不到姓名为{0}编号为{1}的学生", xinming, bianhao, banji); } //然后在excel表中如果有相关资料的话便导入 var studentpointList = studentpoints.Where(p => p.BanjiName == banji && p.XinMing == xinming && p.BianHao == bianhao); if (studentpointList.Any()) { var studentpoint = studentpointList.FirstOrDefault(); if (studentpoint.TempIsOld) { continue; } SetCellToStudentPoint(studentpoint, row, rowName); studentpoint.TempIsOld = true; newstudentPoints.Add(studentpoint); } else { var studentpoint = new StudentPointModel(); studentpoint.SchoolNo = schoolNo; studentpoint.BanjiName = banji; studentpoint.XinMing = xinming; studentpoint.BianHao = bianhao; studentpoint.ExamType = examType; studentpoint.ExamName = examName; studentpoint.ExamTime = Convert.ToDateTime(examTime); SetCellToStudentPoint(studentpoint, row, rowName); newstudentPoints.Add(studentpoint); } } } } if (newstudentPoints.Any()) { _studentPointEbi.add(newstudentPoints); } return "OK"; } catch (Exception ex) { return "Excel格式不正确," + ex.Message; } catch { return "Excel格式不正确"; } }
void DisplayBook(HSSFWorkbook book) { ToolStripButton firstBtn = null; int cx = book.NumberOfSheets; for (int x = 0; x < cx; x++) { int si = x; ToolStripButton tsb = new ToolStripButton(book.GetSheetName(x)); tsb.Click += delegate(object sender, EventArgs e) { for (int t = 1; t < tss.Items.Count; t++) ((ToolStripButton)tss.Items[t]).Checked = (tss.Items[t] == tsb); ActivateSheet((NPOI.SS.UserModel.Sheet)book.GetSheetAt(si)); }; tss.Items.Add(tsb); firstBtn = firstBtn ?? tsb; } if (firstBtn != null) firstBtn.PerformClick(); CloseWIP(); }
public static Dictionary<string, int> GetAllSheets(string excelFilePath) { using (FileStream stream = System.IO.File.OpenRead(excelFilePath)) { HSSFWorkbook workbook = new HSSFWorkbook(stream); Dictionary<string, int> dic = new Dictionary<string, int>(); int count = workbook.NumberOfSheets; for (int i = 0; i < count; i++) { string name = workbook.GetSheetName(i); dic.Add(name, i); } return dic; } }
/** * Asserts that the 'read me' comment page exists, and has this class' name in one of the * cells. This back-link is to make it easy to find this class if a Reader encounters the * spreadsheet first. */ private void ConfirmReadMeSheet(HSSFWorkbook workbook) { String firstSheetName = workbook.GetSheetName(0); if (!firstSheetName.Equals(SS.README_SHEET_NAME, StringComparison.OrdinalIgnoreCase)) { throw new RuntimeException("First sheet's name was '" + firstSheetName + "' but expected '" + SS.README_SHEET_NAME + "'"); } ISheet sheet = workbook.GetSheetAt(0); String specifiedClassName = sheet.GetRow(2).GetCell(0).RichStringCellValue.String; Assert.AreEqual("org.apache.poi.ss.formula.functions.TestLookupFunctionsFromSpreadsheet", specifiedClassName, "Test class name in spreadsheet comment"); }
/// <summary> /// 从Excel中获取数据到DataTable /// </summary> /// <param name="ExcelFileStream">Excel文件流</param> /// <param name="SheetIndex">要获取数据的工作表序号(从0开始)</param> /// <param name="HeaderRowIndex">工作表标题行所在行号(从0开始)</param> /// <returns></returns> public static DataTable RenderDataTableFromExcel(Stream ExcelFileStream, int SheetIndex, int HeaderRowIndex) { IWorkbook workbook = new HSSFWorkbook(ExcelFileStream); ExcelFileStream.Close(); string SheetName = workbook.GetSheetName(SheetIndex); return RenderDataTableFromExcel(workbook, SheetName, HeaderRowIndex); }
/// <summary> /// 从Excel中获取数据到DataTable /// </summary> /// <param name="strFileName">Excel文件全路径(服务器路径)</param> /// <param name="SheetIndex">要获取数据的工作表序号(从0开始)</param> /// <param name="HeaderRowIndex">工作表标题行所在行号(从0开始)</param> /// <returns></returns> public static DataTable RenderDataTableFromExcel(string strFileName, int SheetIndex, int HeaderRowIndex) { using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read)) { IWorkbook workbook = new HSSFWorkbook(file); string SheetName = workbook.GetSheetName(SheetIndex); return RenderDataTableFromExcel(workbook, SheetName, HeaderRowIndex); } }
/// <summary> /// 获取所有的Sheet的名称 /// </summary> /// <param name="FileName"></param> /// <returns></returns> public static string[] GetExcelSheetNames(string FileName) { string[] Result=null; if (File.Exists(FileName)) { try { using (FileStream fs = new FileStream(FileName, FileMode.Open, FileAccess.Read)) { HSSFWorkbook hssfworkbook = new HSSFWorkbook(fs); int Count = hssfworkbook.Count; if (Count > 0) { Result = new string[Count]; for (int Index = 0; Index < Count; Index++) { Result[Index] = hssfworkbook.GetSheetName(Index); } } } } catch (Exception ex) { Debug.WriteLine(ex.Message); } } return Result; }
/// <summary> /// 根据Sheet索引获取Sheet名称 /// </summary> /// <param name="FileName">Excel文件名称</param> /// <param name="SheetIndex">Sheet索引</param> /// <returns></returns> public static string GetSheetName(string FileName, int SheetIndex) { if (File.Exists(FileName)) { try { using (FileStream fs = new FileStream(FileName, FileMode.Open, FileAccess.Read)) { HSSFWorkbook hssfworkbook = new HSSFWorkbook(fs); string SheetName = hssfworkbook.GetSheetName(SheetIndex); return SheetName; } } catch (Exception ex) { Debug.WriteLine(ex.Message); } } return ""; }
/// <summary> /// 读取Excel数据并返回ExcelSheetData类型的集合给Dal进行处理 /// </summary> /// <param name="filePath">Excel文件路径</param> /// <param name="colsCount">记录列数</param> /// <param name="FileUpload1"></param> /// <returns>返回ExcelData实体类</returns> public static ExcelData BllImportExcelData(string filePath, int[] colsCount, FileUpload FileUpload1) { //设定一个自定义ExcelData类型,用于返回全部的Excel数据 ExcelData excelData = null; //出错的工作薄名称 string exceptionSheetName = string.Empty; //出错的行号 int exceptionRowNo = 0; ////出错的列号 //int exceptionColNo = 0; try { List<ExcelSheetData> excelSheetDataArray = new List<ExcelSheetData>(); List<PasswordMapping> excelPasswordMapping = new List<PasswordMapping>(); int sheetCount = 0; using (FileStream file = new FileStream(filePath, FileMode.Open)) { //建立WorkBook HSSFWorkbook HSSFWorkbook = new HSSFWorkbook(file); //获取工作薄的数目 sheetCount = HSSFWorkbook.NumberOfSheets; //循环存储值 for (int i = 0; i < sheetCount; i++) { HSSFSheet Sheet = HSSFWorkbook.GetSheetAt(i); string sheetName = HSSFWorkbook.GetSheetName(i); //设置当前的工作薄名称 exceptionSheetName = sheetName; IEnumerator rows = Sheet.GetRowEnumerator(); //当前行编号 int currentRowNo = 0; while (rows.MoveNext()) { if (currentRowNo > 0) { exceptionRowNo = currentRowNo; HSSFRow row = (HSSFRow)rows.Current; List<string> ilistTemp = new List<string>(); //初始密码(4位数字) string initializePwd = CommonUtility.GenerateRandomPassword(); for (int j = 0; j < colsCount[i]; j++) { //exceptionColNo = j + 1; ilistTemp.Add((row.GetCell(j) != null ? row.GetCell(j).ToString().Trim() : string.Empty)); if (j == (colsCount[i] - 1)) { //try //{ //实例化一个初始密码映射实体类 PasswordMapping passwordMapping = null; switch (sheetName) { case "Sheet1": passwordMapping = new PasswordMapping { userNo = row.GetCell(0).ToString(), userName = row.GetCell(1).ToString(), initializePassword = initializePwd }; passwordMapping.userType = 1; excelPasswordMapping.Add(passwordMapping); ilistTemp.Add(CommonUtility.EncodeUsingMD5(initializePwd)); break; case "Sheet2": passwordMapping = new PasswordMapping { userNo = row.GetCell(0).ToString(), userName = row.GetCell(1).ToString(), initializePassword = initializePwd }; passwordMapping.userType = 2; excelPasswordMapping.Add(passwordMapping); ilistTemp.Add(CommonUtility.EncodeUsingMD5(initializePwd)); break; case "Sheet3": passwordMapping = new PasswordMapping { userNo = row.GetCell(0).ToString(), userName = row.GetCell(1).ToString(), initializePassword = initializePwd }; passwordMapping.userType = 3; excelPasswordMapping.Add(passwordMapping); ilistTemp.Add(CommonUtility.EncodeUsingMD5(initializePwd)); break; default: break; } } } //将要执行的SQL信息添加到ExcelSheetData数据中 ExcelSheetData excelSheetDataTemp = ReturnSqlJudgeBySheetName(sheetName, ReturnModelDataJudgeBySheetName(sheetName, ilistTemp)); excelSheetDataTemp.sheetName = exceptionSheetName; excelSheetDataTemp.sheetRowNo = exceptionRowNo; excelSheetDataArray.Add(excelSheetDataTemp); } //行编号值加1 currentRowNo += 1; } } excelData = new ExcelData { excelSheetData = excelSheetDataArray, excelPasswordMapping = excelPasswordMapping }; } } catch (Exception ex) { MongoDBLog.LogRecord(ex); HttpContext.Current.Response.Write("<script type='text/javascript'>alert('很抱歉,读取Excel文件数据失败!此次操作未更改任何数据库数据,相关信息如下:\\n\\n出错的工作薄名称为:" + exceptionSheetName + "\\n出错的单元格行号为:" + exceptionRowNo //+ "\n出错的单元格列号为:" + exceptionColNo + "\\n可能的原因为:\\n 此单元格数据格式可能不正确,例如:单元格数据是否存在多余的空格。" + "请检查Excel文件数据,修改后重新上传!');history.go(-1);</script>"); } finally { if (File.Exists(HttpContext.Current.Server.MapPath("/" + FileUpload1.FileName))) { File.Delete(HttpContext.Current.Server.MapPath("/" + FileUpload1.FileName)); } } return excelData; }
/** * Asserts that the 'read me' comment page exists, and has this class' name in one of the * cells. This back-link is to make it easy to find this class if a reader encounters the * spreadsheet first. */ private void confirmReadMeSheet(HSSFWorkbook workbook) { String firstSheetName = workbook.GetSheetName(0); if (!firstSheetName.Equals(SS.README_SHEET_NAME, StringComparison.InvariantCultureIgnoreCase)) { throw new Exception("First sheet's name was '" + firstSheetName + "' but expected '" + SS.README_SHEET_NAME + "'"); } //ISheet sheet = workbook.GetSheetAt(0); //String specifiedClassName = sheet.GetRow(2).GetCell(0).RichStringCellValue.String; //Assert.AreEqual(this.GetType().FullName, specifiedClassName, "Test class name in spreadsheet comment"); }