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");
}
