| public GetSheetName ( int sheetIx ) : String | ||
| sheetIx | int | |
| Результат | String | |
public void TestLoadSample()
{
XSSFWorkbook workbook = new XSSFWorkbook(_ssSampels.OpenResourceAsStream("sample.xlsx"));
Assert.AreEqual(3, workbook.NumberOfSheets);
Assert.AreEqual("Sheet1", workbook.GetSheetName(0));
ISheet sheet = workbook.GetSheetAt(0);
IRow row = sheet.GetRow(0);
ICell cell = row.GetCell((short)1);
Assert.IsNotNull(cell);
Assert.AreEqual(111.0, cell.NumericCellValue, 0.0);
cell = row.GetCell((short)0);
Assert.AreEqual("Lorem", cell.RichStringCellValue.String);
}
public void Bug47813()
{
XSSFWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("47813.xlsx");
Assert.AreEqual(3, wb.NumberOfSheets);
Assert.IsNotNull(wb.GetCalculationChain());
Assert.AreEqual("Numbers", wb.GetSheetName(0));
//the second sheet is of type 'chartsheet'
Assert.AreEqual("Chart", wb.GetSheetName(1));
Assert.IsTrue(wb.GetSheetAt(1) is XSSFChartSheet);
Assert.AreEqual("SomeJunk", wb.GetSheetName(2));
wb.RemoveSheetAt(2);
Assert.AreEqual(2, wb.NumberOfSheets);
Assert.IsNull(wb.GetCalculationChain());
wb = (XSSFWorkbook)XSSFTestDataSamples.WriteOutAndReadBack(wb);
Assert.AreEqual(2, wb.NumberOfSheets);
Assert.IsNull(wb.GetCalculationChain());
Assert.AreEqual("Numbers", wb.GetSheetName(0));
Assert.AreEqual("Chart", wb.GetSheetName(1));
}
public void TestXSSFFactory()
{
XSSFWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("chart_sheet.xlsx");
Assert.AreEqual(4, wb.NumberOfSheets);
//the third sheet is of type 'chartsheet'
Assert.AreEqual("Chart1", wb.GetSheetName(2));
Assert.IsTrue(wb.GetSheetAt(2) is XSSFChartSheet);
Assert.AreEqual("Chart1", wb.GetSheetAt(2).SheetName);
CT_Chartsheet ctChartsheet = ((XSSFChartSheet)wb.GetSheetAt(2)).GetCTChartsheet();
Assert.IsNotNull(ctChartsheet);
}
public String GetSheetName(int sheetIndex)
{
return(_uBook.GetSheetName(sheetIndex));
}
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格式不正确";
}
}
