public void TestClassCast_bug44861()
{
HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("44861.xls");
// Check direct
HSSFFormulaEvaluator.EvaluateAllFormulaCells(wb);
// And via calls
int numSheets = wb.NumberOfSheets;
for (int i = 0; i < numSheets; i++)
{
NPOI.SS.UserModel.ISheet s = wb.GetSheetAt(i);
HSSFFormulaEvaluator eval = new HSSFFormulaEvaluator(wb);
for (IEnumerator rows = s.GetRowEnumerator(); rows.MoveNext();)
{
IRow r = (IRow)rows.Current;
for (IEnumerator cells = r.GetEnumerator(); cells.MoveNext();)
{
ICell c = (ICell)cells.Current;
eval.EvaluateFormulaCell(c);
}
}
}
wb.Close();
}
private static DataTable ReadFromFile(string filePath)
{
IWorkbook hssfworkbook;
#region 初始化信息
using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
{
hssfworkbook = WorkbookFactory.Create(file) as IWorkbook;
}
#endregion
NPOI.SS.UserModel.ISheet sheet = hssfworkbook.GetSheetAt(0);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
DataTable dt = new DataTable();
rows.MoveNext();
IRow row = rows.Current as IRow;
for (int j = 0; j < (sheet.GetRow(0).LastCellNum); j++)
{
//将第一列作为列表头
DataColumn dtc = new DataColumn(row.GetCell(j).ToString(), typeof(string));
dt.Columns.Add(dtc);
}
while (rows.MoveNext())
{
try
{
row = (IRow)rows.Current;
DataRow dr = dt.NewRow();
for (int i = 0; i < row.LastCellNum; i++)
{
NPOI.SS.UserModel.ICell cell = row.GetCell(i);
Console.WriteLine(cell == null ? "" : cell + "\t\t");
Console.WriteLine();
if (cell == null)
{
dr[i] = null;
}
else
{
dr[i] = cell.ToString();
}
}
dt.Rows.Add(dr);
}
catch (Exception ex)
{
continue;
// throw;
}
}
return(dt);
}
/// <summary>读取excel
/// 默认第一行为标头
/// </summary>
/// <param name="strFileName">excel文档路径</param>
/// <returns></returns>
public static DataTable Import(string strFileName)
{
DataTable dt = new DataTable();
// HSSFWorkbook hssfworkbook;
IWorkbook hssfworkbook;
using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
{
string fileExt = Path.GetExtension(strFileName);
if (fileExt == ".xls")
{
hssfworkbook = new HSSFWorkbook(file);
}
else if (fileExt == ".xlsx")
{
hssfworkbook = new XSSFWorkbook(file);
}
else
{
return(new DataTable());
}
}
NPOI.SS.UserModel.ISheet sheet = hssfworkbook.GetSheetAt(0);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
NPOI.SS.UserModel.IRow headerRow = sheet.GetRow(0);
int cellCount = headerRow.LastCellNum;
for (int j = 0; j < cellCount; j++)
{
NPOI.SS.UserModel.ICell cell = headerRow.GetCell(j);
dt.Columns.Add(cell.ToString());
}
for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
{
NPOI.SS.UserModel.IRow row = sheet.GetRow(i);
DataRow dataRow = dt.NewRow();
for (int j = row.FirstCellNum; j < cellCount; j++)
{
if (row.GetCell(j) != null)
{
dataRow[j] = row.GetCell(j).ToString();
}
}
dt.Rows.Add(dataRow);
}
return(dt);
}
/// <summary>
/// xlsx
/// </summary>
/// <param name="Postfile"></param>
/// <param name="dt"></param>
/// <param name="iStartRow"></param>
/// <param name="iStartColumn"></param>
/// <returns></returns>
private static string ImportExcelFileXSSF(HttpPostedFileBase Postfile, DataTable dt, int iStartRow, int iStartColumn)
{
XSSFWorkbook Xssfworkbook;
#region//初始化信息
try
{
//.xlsx应该XSSFWorkbook workbook = new XSSFWorkbook(file);
//而xls应该用 HSSFWorkbook workbook = new HSSFWorkbook(file);
Stream file = Postfile.InputStream;
Xssfworkbook = new XSSFWorkbook(file);
//HSSFWorkbook workbook = new HSSFWorkbook(file);
}
catch (Exception e)
{
throw e;
}
#endregion
NPOI.SS.UserModel.ISheet sheet = Xssfworkbook.GetSheetAt(0);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
for (int i = 0; i < iStartRow; i++)
{
rows.MoveNext();
}
while (rows.MoveNext())
{
XSSFRow row = (XSSFRow)rows.Current;
DataRow dr = dt.NewRow();
int j = 0;
for (int i = iStartColumn; i < row.LastCellNum; i++)
{
NPOI.SS.UserModel.ICell cell = row.GetCell(i);
if (cell == null)
{
dr[j] = null;
}
else
{
if (dt.Columns[j].DataType.FullName == "System.Int32")
{
dr[j] = Convert.ToInt32(cell.ToString());
}
else
{
dr[j] = cell;
}
}
j++;
}
dt.Rows.Add(dr);
}
return(JsonHelper.DataTable2Json(dt));
}
public static DataTable GetData(string filePath)
{
IWorkbook workbook;
#region//初始化信息
try
{
using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
{
//hssfworkbook = new HSSFWorkbook(file);
workbook = WorkbookFactory.Create(file);
}
}
catch (Exception e)
{
throw e;
}
#endregion
NPOI.SS.UserModel.ISheet sheet = workbook.GetSheetAt(0);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
DataTable dt = new DataTable();
rows.MoveNext();
//HSSFRow row = (HSSFRow)rows.Current;
IRow row = (IRow)rows.Current;
for (int j = 0; j < (sheet.GetRow(0).LastCellNum); j++)
{
//dt.Columns.Add(Convert.ToChar(((int)'A') + j).ToString());
//将第一列作为列表头
dt.Columns.Add(row.GetCell(j).ToString());
}
while (rows.MoveNext())
{
row = (IRow)rows.Current;
DataRow dr = dt.NewRow();
for (int i = 0; i < row.LastCellNum; i++)
{
NPOI.SS.UserModel.ICell cell = row.GetCell(i);
if (cell == null)
{
dr[i] = null;
}
else
{
dr[i] = cell.ToString();
}
}
dt.Rows.Add(dr);
}
return(dt);
}
public static DataTable ImportExcelFileXSSF_Org(HttpFileCollectionBase files)
{
XSSFWorkbook hssfworkbook;
#region//初始化信息
try
{
//.xlsx应该XSSFWorkbook workbook = new XSSFWorkbook(file);
//而xls应该用 HSSFWorkbook workbook = new HSSFWorkbook(file);
Stream file = files[0].InputStream;
hssfworkbook = new XSSFWorkbook(file);
//HSSFWorkbook workbook = new HSSFWorkbook(file);
}
catch (Exception e)
{
throw e;
}
#endregion
NPOI.SS.UserModel.ISheet sheet = hssfworkbook.GetSheetAt(0);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
DataTable dt = new DataTable();
rows.MoveNext();
XSSFRow row = (XSSFRow)rows.Current;
for (int j = 0; j < (sheet.GetRow(0).LastCellNum); j++)
{
//将第一列作为列表头
dt.Columns.Add(row.GetCell(j).ToString());
}
while (rows.MoveNext())
{
row = (XSSFRow)rows.Current;
DataRow dr = dt.NewRow();
for (int i = 0; i < row.LastCellNum; i++)
{
NPOI.SS.UserModel.ICell cell = row.GetCell(i);
if (cell == null)
{
dr[i] = null;
}
else
{
dr[i] = cell.ToString();
}
}
dt.Rows.Add(dr);
}
return(dt);
}
private static void Process(HSSFWorkbook wb)
{
HSSFFormulaEvaluator eval = new HSSFFormulaEvaluator(wb);
for (int i = 0; i < wb.NumberOfSheets; i++)
{
NPOI.SS.UserModel.ISheet s = wb.GetSheetAt(i);
IEnumerator it = s.GetRowEnumerator();
while (it.MoveNext())
{
IRow r = (IRow)it.Current;
Process(r, eval);
}
}
}
/// <summary>
/// 将excel的数据加载到datatable中
/// </summary>
/// <param name="path"></param>
/// <author>wangwei</author>
/// <returns></returns>
public static DataTable ExcelToTable(string path)
{
HSSFWorkbook hssfworkbook;
#region//初始化信息
try
{
using (FileStream file = new FileStream(path, FileMode.Open, FileAccess.Read))
{
hssfworkbook = new HSSFWorkbook(file);
}
}
catch (Exception e)
{
throw e;
}
#endregion
NPOI.SS.UserModel.ISheet sheet = hssfworkbook.GetSheetAt(0);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
DataTable dtss = new DataTable();
for (int j = 0; j < (sheet.GetRow(0).LastCellNum); j++)
{
dtss.Columns.Add(Convert.ToChar(((int)'A') + j).ToString());
}
while (rows.MoveNext())
{
HSSFRow row = (HSSFRow)rows.Current;
DataRow dr = dtss.NewRow();
for (int i = 0; i < row.LastCellNum; i++)
{
NPOI.SS.UserModel.ICell cell = row.GetCell(i);
if (cell == null)
{
dr[i] = null;
}
else
{
dr[i] = cell.ToString();
}
}
dtss.Rows.Add(dr.ItemArray);
}
return(dtss);
}
/// <summary>
/// 由Excel导入DataTable
/// </summary>
/// <param name="excelFilePath">Excel文件路径，为物理路径。</param>
/// <param name="sheetName">Excel工作表名称</param>
/// <param name="headerRowIndex">Excel表头行索引</param>
/// <returns>DataTable</returns>
public static DataTable ImportDataTableFromExcel(string excelFilePath)
{
HSSFWorkbook hssfworkbook;
//excelFilePath = excelFilePath.Replace("http://","").Replace("/","\\");
using (FileStream file = new FileStream(excelFilePath, FileMode.Open, FileAccess.Read))
{
hssfworkbook = new HSSFWorkbook(file);
NPOI.SS.UserModel.ISheet sheet = hssfworkbook.GetSheetAt(0);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
DataTable dt = new DataTable();
for (int j = 0; j < 4; j++)
{
dt.Columns.Add(Convert.ToChar(((int)'A') + j).ToString());
}
while (rows.MoveNext())
{
NPOI.SS.UserModel.IRow row = (HSSFRow)rows.Current;
DataRow dr = dt.NewRow();
for (int i = 0; i < row.LastCellNum; i++)
{
NPOI.SS.UserModel.ICell cell = row.GetCell(i);
if (cell == null)
{
dr[i] = null;
}
else
{
dr[i] = cell.ToString();
}
}
dt.Rows.Add(dr);
}
return(dt);
}
}
private static DataTable GetDataTableByXls(string excelFilePath, DataTable dt)
{
HSSFWorkbook hssfworkbook;
using (FileStream file = new FileStream(excelFilePath, FileMode.Open, FileAccess.Read))
{
hssfworkbook = new HSSFWorkbook(file);
NPOI.SS.UserModel.ISheet sheet = hssfworkbook.GetSheetAt(0);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
int r = 0;
while (rows.MoveNext())
{
r++;
if (r == 1)
{
continue;
}
NPOI.SS.UserModel.IRow row = (HSSFRow)rows.Current;
DataRow rw = dt.NewRow();
for (int i = 1; i <= row.LastCellNum - 1; i++)
{
NPOI.SS.UserModel.ICell cell = row.GetCell(i);
if (cell == null)
{
continue;
}
else
{
rw[i - 1] = cell.ToString();
}
}
dt.Rows.Add(rw);
}
return(dt);
}
}
/// <summary>
/// 导入老师表格
/// </summary>
/// <param name="path"></param>
/// <param name="exam"></param>
/// <returns></returns>
public static string ImportTchExcel(string path, E_Info exam, string SchoolID, out int errnum)
{
HSSFWorkbook hssfworkbook;
#region//初始化信息
try
{
using (FileStream fs = new FileStream(path, FileMode.Open, FileAccess.Read))
{
hssfworkbook = new HSSFWorkbook(fs);
}
}
catch (Exception e)
{
throw e;
}
#endregion
NPOI.SS.UserModel.ISheet sheet = hssfworkbook.GetSheetAt(0);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
DataTable dt = new DataTable();
rows.MoveNext();
HSSFRow row = (HSSFRow)rows.Current;
List <string> cols = new List <string>();
for (int j = 0; j < (sheet.GetRow(0).LastCellNum); j++)
{
cols.Add(row.GetCell(j).ToString().Trim());
dt.Columns.Add(row.GetCell(j).ToString());
}
var colHead = GetTchColumn();
if (!(colHead.All(cols.Contains) && cols.All(colHead.Contains)))
{
errnum = 0;
return("表格格式错误，请重新下载模板");
}
cols.Add("错误原因");
dt.Columns.Add("错误原因");
while (rows.MoveNext())
{
row = (HSSFRow)rows.Current;
if (!IsNull(row, 0) || !IsNull(row, 1) || !IsNull(row, 2))
{
continue;
}
var school = MongoDbHelper.QueryOne <U_Info>(DbName.U_Info, w => w._id == row.GetCell(0).ToString());
var ifTrue = false;
if (SchoolID != "SchoolID" && school != null)
{
ifTrue = !(SchoolID == school._id);
}
var sbsnm = exam.sbs.Select(s => s.sbnm).ToList();
if (!sbsnm.Contains(row.GetCell(2).ToString()) || school == null || ifTrue || !IsNull(row, 4) || !IsNull(row, 5) || !IsNull(row, 6) || !Function.MathPhone(row.GetCell(6).ToString()))
{
DataRow dr = dt.NewRow();
for (int i = 0; i < (sheet.GetRow(0).LastCellNum); i++)
{
NPOI.SS.UserModel.ICell cell = row.GetCell(i);
if (cell == null)
{
dr[i] = null;
}
else
{
dr[i] = cell.ToString();
}
}
if (school == null)
{
dr[sheet.GetRow(0).LastCellNum] += "学校不存在;";
}
if (ifTrue)
{
dr[sheet.GetRow(0).LastCellNum] += "学校代号错误;";
}
if (!sbsnm.Contains(row.GetCell(2).ToString()))
{
dr[sheet.GetRow(0).LastCellNum] += "学科不存在;";
}
if (!Function.MathPhone(row.GetCell(6).ToString()))
{
dr[sheet.GetRow(0).LastCellNum] += "手机号不正确;";
}
dt.Rows.Add(dr);
continue;
}
Tch_Info tch = new Tch_Info();
tch.eid = exam._id;
tch.sid = row.GetCell(0).ToString();
tch.snm = school.snm;
tch.sb = row.GetCell(2).ToString().Replace(" ", "");
tch.nm = row.GetCell(3).ToString();
tch.sx = row.GetCell(4).ToString() == "男" ? 1 : 0;
tch.zc = row.GetCell(5).ToString();
tch.ph = Convert.ToInt64(row.GetCell(6).ToString());
var sbinfo = exam.sbs.Where(w => w.sbnm == tch.sb).FirstOrDefault();
if (sbinfo != null)
{
sbinfo.tchct++;
}
MongoDbHelper.Insert(tch, DbName.Tch_Info);
}
MongoDbHelper.ReplaceOne(exam._id.ToString(), exam, DbName.E_Info);
errnum = dt.Rows.Count;
if (dt.Rows.Count > 0)
{
return(BuildExcel1(cols.ToArray(), dt));
}
return("");
}
/// <summary>
/// 导入excel文件
/// </summary>
/// <param name="filePath"></param>
/// <returns></returns>
private static string ImportExcel(string filePath)
{
string result = "";
NPOI.SS.UserModel.ISheet sheet = null;
try
{
using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
{
HSSFWorkbook hssfworkbook = new HSSFWorkbook(file);
sheet = hssfworkbook.GetSheetAt(0);
}
}
catch (Exception)
{
using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
{
NPOI.XSSF.UserModel.XSSFWorkbook hssfworkbook2 = new NPOI.XSSF.UserModel.XSSFWorkbook(file);
sheet = hssfworkbook2.GetSheetAt(0);
}
}
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
DataTable dt = new DataTable();
for (int j = 0; j < (sheet.GetRow(0).LastCellNum); j++)
{
dt.Columns.Add(Convert.ToChar(((int)'A') + j).ToString());
}
while (rows.MoveNext())
{
NPOI.SS.UserModel.IRow row = (NPOI.SS.UserModel.IRow)rows.Current;
DataRow dr = dt.NewRow();
for (int i = 0; i < row.LastCellNum; i++)
{
NPOI.SS.UserModel.ICell cell = row.GetCell(i);
if (cell == null)
{
dr[i] = null;
}
else
{
dr[i] = cell.ToString();
}
}
if (row.LastCellNum != -1)
{
if ((dr[0] == null | dr[0].ToString() == "") && (dr[1] == null | dr[1].ToString() == "") && (dr[2] == null | dr[2].ToString() == ""))
{
}
else
{
dt.Rows.Add(dr);
}
}
}
dt = Distinct(dt, new string[] { "A", "B", "C" });
DataView myDataView = new DataView(dt);
string[] strComuns = { "C" };
if (myDataView.ToTable(true, strComuns).Rows.Count < dt.Rows.Count)
{
result = "用户表存在相同的账号数据";
return(result);
}
DbHelper dp = new DbHelper();
foreach (DataRow dr in dt.Rows)
{
string strsql = "insert into hs_user1 values ('" + dr["B"] + "','" + dr["C"] + "')";
dp.RunTxt(strsql);
}
result = "导入成功";
return(result);
}
public static string ImportScoreExcel(string path, E_Info exam, out int errnum)
{
HSSFWorkbook hssfworkbook;
#region//初始化信息
try
{
using (FileStream fs = new FileStream(path, FileMode.Open, FileAccess.Read))
{
hssfworkbook = new HSSFWorkbook(fs);
}
}
catch (Exception e)
{
throw e;
}
#endregion
NPOI.SS.UserModel.ISheet sheet = hssfworkbook.GetSheetAt(0);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
DataTable dt = new DataTable();
rows.MoveNext();
HSSFRow row = (HSSFRow)rows.Current;
List <string> cols = new List <string>();
for (int j = 0; j < (sheet.GetRow(0).LastCellNum); j++)
{
cols.Add(row.GetCell(j).ToString());
dt.Columns.Add(row.GetCell(j).ToString());
}
cols.Add("错误原因");
dt.Columns.Add("错误原因");
while (rows.MoveNext())
{
row = (HSSFRow)rows.Current;
if (!IsNull(row, 0) || !IsNull(row, 1) || !IsNull(row, 2))
{
continue;
}
St_Sc stsc = new St_Sc();
stsc.eid = exam._id;
stsc.stid = row.GetCell(0).ToString();
var st = MongoDbHelper.QueryOne <St_Info>(DbName.St_Info, w => w.eid == stsc.eid && w.stid == stsc.stid);
if (st == null || !IsNull(row, 0) || !IsNull(row, 1) || !IsNull(row, 2) || !IsNull(row, 3))
{
DataRow dr = dt.NewRow();
for (int i = 0; i < (sheet.GetRow(0).LastCellNum); i++)
{
NPOI.SS.UserModel.ICell cell = row.GetCell(i);
if (cell == null)
{
dr[i] = null;
}
else
{
dr[i] = cell.ToString();
}
}
if (st == null)
{
dr[sheet.GetRow(0).LastCellNum] += "考号不存在;";
}
else
{
dr[sheet.GetRow(0).LastCellNum] += "考生信息不能为空;";
}
dt.Rows.Add(dr);
continue;
}
stsc.cls = st.cls;
stsc.nm = st.nm;
stsc.idcd = st.idcd;
stsc.sid = st.sid;
stsc.s1 = IsNull(row, 3) ? Convert.ToDouble(row.GetCell(3).ToString()) : -1; //语文
stsc.s2 = IsNull(row, 4) ? Convert.ToDouble(row.GetCell(4).ToString()) : -1; //文科数学
stsc.s3 = IsNull(row, 5) ? Convert.ToDouble(row.GetCell(5).ToString()) : -1; //理科数学
stsc.s4 = IsNull(row, 6) ? Convert.ToDouble(row.GetCell(6).ToString()) : -1;
stsc.s5 = IsNull(row, 7) ? Convert.ToDouble(row.GetCell(7).ToString()) : -1;
stsc.s6 = IsNull(row, 8) ? Convert.ToDouble(row.GetCell(8).ToString()) : -1;
stsc.s7 = IsNull(row, 9) ? Convert.ToDouble(row.GetCell(9).ToString()) : -1;
stsc.s8 = IsNull(row, 10) ? Convert.ToDouble(row.GetCell(10).ToString()) : -1;
stsc.s9 = IsNull(row, 11) ? Convert.ToDouble(row.GetCell(11).ToString()) : -1;
stsc.s10 = IsNull(row, 12) ? Convert.ToDouble(row.GetCell(12).ToString()) : -1;
stsc.sc = IsNull(row, 13) ? Convert.ToDouble(row.GetCell(13).ToString()) : 0;
MongoDbHelper.Insert(stsc, DbName.St_Sc);
}
var stus = MongoDbHelper.QueryBy <St_Info>(DbName.St_Info, w => w.eid == exam._id);
var stscs = MongoDbHelper.QueryBy <St_Sc>(DbName.St_Sc, w => w.eid == exam._id).Select(s => s.stid).ToList();
var errStu = stus.Where(w => !stscs.Contains(w.stid)).ToList();
for (int st = 0; st < errStu.Count; st++)
{
DataRow dr = dt.NewRow();
dr[0] = errStu[st].stid;
dr[1] = errStu[st].cls;
dr[2] = errStu[st].nm;
for (int i = 3; i < (sheet.GetRow(0).LastCellNum); i++)
{
dr[i] = "0";
}
dr[sheet.GetRow(0).LastCellNum] += "缺少该考生成绩;";
dt.Rows.Add(dr);
}
errnum = dt.Rows.Count;
if (dt.Rows.Count > 0)
{
return(BuildExcel1(cols.ToArray(), dt));
}
return("");
}
/// <summary>
/// 导入
/// </summary>
/// <param name="strFileName"></param>
/// <param name="headIndex">表头开始的行索引</param>
/// <returns></returns>
public static DataSet Import2(string strFileName, int[] headIndexArr, int sheetCount)
{
string extName = Path.GetExtension(strFileName);
if (extName != ".xlsx")
{
DataSet ds = new DataSet();
for (int ii = 0; ii < sheetCount; ii++)
{
DataTable dt = new DataTable();
int headIndex = headIndexArr[ii];
HSSFWorkbook hssfworkbook;
using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
{
hssfworkbook = new HSSFWorkbook(file);
}
NPOI.SS.UserModel.ISheet sheet = hssfworkbook.GetSheetAt(ii);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
//sheet.FirstRowNum：获取第一行（表头通常是第一行）
NPOI.SS.UserModel.IRow headerRow = sheet.GetRow(headIndex);
int cellCount = headerRow.LastCellNum;
//表头
for (int j = 0; j < cellCount; j++)
{
NPOI.SS.UserModel.ICell cell = headerRow.GetCell(j);
if (cell == null || cell.ToString() == string.Empty)
{
dt.Columns.Add(new DataColumn("Columns" + j.ToString()));
//continue;
}
else
{
if (dt.Columns.Contains(cell.ToString()))//说明重复了
{
dt.Columns.Add(new DataColumn(Guid.NewGuid() + cell.ToString()));
}
else
{
dt.Columns.Add(new DataColumn(cell.ToString()));
}
}
}
//数据
for (int i = (headIndex + 1); i <= sheet.LastRowNum; i++)
{
NPOI.SS.UserModel.IRow row = sheet.GetRow(i);
DataRow dataRow = dt.NewRow();
for (int j = row.FirstCellNum; j < cellCount; j++)
{
if (row.GetCell(j) != null)
{
dataRow[j] = row.GetCell(j).ToString();
}
}
dt.Rows.Add(dataRow);
}
ds.Tables.Add(dt);
}
return(ds);
}
else
{
return(ExcelToTableForXLSX2(strFileName, headIndexArr, sheetCount));
}
}
public static string ImportStuExcel(string path, E_Info exam, string SchoolID, out int errnum)
{
HSSFWorkbook hssfworkbook;
#region//初始化信息
try
{
using (FileStream fs = new FileStream(path, FileMode.Open, FileAccess.Read))
{
hssfworkbook = new HSSFWorkbook(fs);
}
}
catch (Exception e)
{
throw e;
}
#endregion
NPOI.SS.UserModel.ISheet sheet = hssfworkbook.GetSheetAt(0);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
DataTable dt = new DataTable();
rows.MoveNext();
HSSFRow row = (HSSFRow)rows.Current;
List <string> cols = new List <string>();
for (int j = 0; j < (sheet.GetRow(0).LastCellNum); j++)
{
cols.Add(row.GetCell(j).ToString().Trim());
dt.Columns.Add(row.GetCell(j).ToString());
}
var colHead = GetStuColumn(exam);
if (!(colHead.All(cols.Contains) && cols.All(colHead.Contains)))
{
errnum = 0;
return("表格格式错误，请重新下载模板");
}
cols.Add("错误原因");
dt.Columns.Add("错误原因");
while (rows.MoveNext())
{
row = (HSSFRow)rows.Current;
St_Info st = new St_Info();
if (!IsNull(row, 0) || !IsNull(row, 1) || !IsNull(row, 2))
{
continue;
}
var stInfo = MongoDbHelper.QueryOne <St_Info>(DbName.St_Info, w => w.eid == exam._id && (w.idcd == row.GetCell(row.LastCellNum - 2).ToString() || w.stid == row.GetCell(2).ToString()));
var school = MongoDbHelper.QueryOne <U_Info>(DbName.U_Info, w => w._id == row.GetCell(0).ToString());
var ifTrue = false;
if (SchoolID != "SchoolID" && school != null)
{
ifTrue = !(SchoolID == school._id);
}
if (school == null || ifTrue || stInfo != null || !IsNull(row, 0) || !IsNull(row, 1) || !IsNull(row, 2) || !IsNull(row, 3) || !IsNull(row, 4) || !IsNull(row, row.LastCellNum - 2) || !IsNull(row, row.LastCellNum - 1) || !CommonHelper.Function.MathIdCard(row.GetCell(row.LastCellNum - 2).ToString()))
{
DataRow dr = dt.NewRow();
for (int i = 0; i < (sheet.GetRow(0).LastCellNum); i++)
{
NPOI.SS.UserModel.ICell cell = row.GetCell(i);
if (cell == null)
{
dr[i] = null;
}
else
{
dr[i] = cell.ToString();
}
}
if (school == null)
{
dr[sheet.GetRow(0).LastCellNum] += "学校不存在;";
}
if (ifTrue)
{
dr[sheet.GetRow(0).LastCellNum] += "学校代号错误;";
}
if (stInfo != null)
{
dr[sheet.GetRow(0).LastCellNum] += "考号或身份证号已存在;";
}
if (!CommonHelper.Function.MathIdCard(row.GetCell(row.LastCellNum - 2).ToString()))
{
dr[sheet.GetRow(0).LastCellNum] += "身份证号不正确;";
}
dt.Rows.Add(dr);
continue;
}
st.eid = exam._id;
st.stid = row.GetCell(2).ToString();
st.sid = row.GetCell(0).ToString();
st.snm = row.GetCell(1).ToString();
st.cls = row.GetCell(3).ToString();
st.nm = row.GetCell(4).ToString();
st.idcd = row.GetCell(row.LastCellNum - 2).ToString();
st.tp = row.GetCell(row.LastCellNum - 1).ToString();
var schPp = MongoDbHelper.QueryOne <Pp_Nm>(DbName.Pp_Nm, w => w.sid == st.sid && w.eid == exam._id); //查询该学校这次考试试卷数量
int firstpp = 0; //0代表不是新建
if (schPp == null)
{
firstpp = 1;
schPp = new Pp_Nm();
schPp.sid = st.sid;
schPp.snm = st.snm;
schPp.eid = exam._id;
schPp.ct = 0;
}
schPp.ct++;//学校考试人数加1
for (int i = 0; i < exam.sbs.Count; i++)
{
if (IsNull(row, 5 + i * 3) && IsNull(row, 6 + i * 3) && IsNull(row, 7 + i * 3))
{
SubE sube = new SubE();
sube.sbid = exam.sbs[i]._id;
sube.sbnm = exam.sbs[i].sbnm;
sube.sbrm = row.GetCell(5 + i * 3).ToString();
sube.sbst = row.GetCell(6 + i * 3).ToString();
sube.sbtch = row.GetCell(7 + i * 3).ToString();
st.subEs.Add(sube);
exam.sbs[i].stct++;//考试人数加1
var Sbnm = schPp.sbnms.Where(w => w.sbid == sube.sbid).FirstOrDefault();
if (Sbnm == null)
{
Sbnm = new Sbnm();
Sbnm.sbid = sube.sbid;
Sbnm.sbnm = sube.sbnm;
Sbnm.sct = 1;
Sbnm.ac = 0;
schPp.sbnms.Add(Sbnm);
}
else
{
Sbnm.sct++;
}
}
}
if (firstpp == 0)
{
MongoDbHelper.ReplaceOne(schPp._id.ToString(), schPp, DbName.Pp_Nm);
}
else
{
MongoDbHelper.Insert(schPp, DbName.Pp_Nm);
}
MongoDbHelper.Insert(st, DbName.St_Info);
}
MongoDbHelper.ReplaceOne(exam._id.ToString(), exam, DbName.E_Info);
errnum = dt.Rows.Count;
if (dt.Rows.Count > 0)
{
return(BuildExcel1(cols.ToArray(), dt));
}
return("");
}
///<summary>
/// #region 少量excel数据导入数据库
/// </summary>
public static async Task <DataTable> ImExport(DataTable dt, IWorkbook hssfworkbook)
{
NPOI.SS.UserModel.ISheet sheet = hssfworkbook.GetSheetAt(0);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
for (int j = 0; j < (sheet.GetRow(0).LastCellNum); j++)
{
dt.Columns.Add(sheet.GetRow(0).Cells[j].ToString());
}
while (rows.MoveNext())
{
XSSFRow row = (XSSFRow)rows.Current;
DataRow dr = dt.NewRow();
for (int i = 0; i < row.LastCellNum; i++)
{
NPOI.SS.UserModel.ICell cell = row.GetCell(i);
if (cell == null)
{
dr[i] = null;
}
else
{
dr[i] = cell.ToString();
}
}
dt.Rows.Add(dr);
}
dt.Rows.RemoveAt(0);
#region 往数据库表添加数据
using (WuLinEntities1 db = new WuLinEntities1())
{
if (dt != null && dt.Rows.Count != 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
string 时间 = dt.Rows[i]["时间"].ToString();
string 国产进口 = dt.Rows[i]["国产/进口"].ToString();
string 省 = dt.Rows[i]["省"].ToString();
string 市 = dt.Rows[i]["市"].ToString();
string 县 = dt.Rows[i]["县"].ToString();
string 制造商 = dt.Rows[i]["制造商"].ToString();
string 车辆型号 = dt.Rows[i]["车辆型号"].ToString();
string 品牌 = dt.Rows[i]["品牌"].ToString();
string 车型 = dt.Rows[i]["车型"].ToString();
string 排量 = dt.Rows[i]["排量"].ToString();
string 变速器 = dt.Rows[i]["变速器"].ToString();
string 车辆类型 = dt.Rows[i]["车辆类型"].ToString();
string 车身型式 = dt.Rows[i]["车身型式"].ToString();
string 燃油类型 = dt.Rows[i]["燃油类型"].ToString();
string 使用性质 = dt.Rows[i]["使用性质"].ToString();
string 所有权 = dt.Rows[i]["所有权"].ToString();
string 抵押标记 = dt.Rows[i]["抵押标记"].ToString();
string 性别 = dt.Rows[i]["性别"].ToString();
string 年龄 = dt.Rows[i]["年龄"].ToString();
string 车身颜色 = dt.Rows[i]["车身颜色"].ToString();
string 发动机型号 = dt.Rows[i]["发动机型号"].ToString();
string 功率 = dt.Rows[i]["功率"].ToString();
string 排放标准 = dt.Rows[i]["排放标准"].ToString();
string 轴距 = dt.Rows[i]["轴距"].ToString();
string 轮胎规格 = dt.Rows[i]["轮胎规格"].ToString();
string 车外廓长 = dt.Rows[i]["车外廓长"].ToString();
string 车外廓宽 = dt.Rows[i]["车外廓宽"].ToString();
string 车外廓高 = dt.Rows[i]["车外廓高"].ToString();
string 准确排量 = dt.Rows[i]["准确排量"].ToString();
string 核定载客 = dt.Rows[i]["核定载客"].ToString();
string 总质量 = dt.Rows[i]["总质量"].ToString();
string 整备质量 = dt.Rows[i]["整备质量"].ToString();
string 轴数 = dt.Rows[i]["轴数"].ToString();
string 前轮距 = dt.Rows[i]["前轮距"].ToString();
string 后轮距 = dt.Rows[i]["后轮距"].ToString();
string 保有量 = dt.Rows[i]["保有量"].ToString();;
//int.TryParse(dt.Rows[i]["保有量"].ToString(), out 保有量);
PassengerVehicle pv = new PassengerVehicle();
pv.Id = Guid.NewGuid();
pv.使用性质 = 使用性质;
pv.保有量 = Convert.ToInt32(保有量);
pv.准确排量 = (准确排量);
pv.制造商 = 制造商;
pv.前轮距 = (前轮距);
pv.功率 = (功率);
pv.县 = 县;
pv.发动机型号 = 发动机型号;
pv.变速器 = 变速器;
pv.后轮距 = (后轮距);
pv.品牌 = 品牌;
pv.国产_进口 = 国产进口;
pv.市 = 市;
pv.年龄 = (年龄);
pv.性别 = 性别;
pv.总质量 = (总质量);
pv.所有权 = 所有权;
pv.抵押标记 = 抵押标记;
pv.排放标准 = 排放标准;
pv.排量 = (排量);
pv.整备质量 = (整备质量);
pv.时间 = 时间;
pv.核定载客 = (核定载客);
pv.燃油类型 = 燃油类型;
pv.省 = 省;
pv.车型 = 车型;
pv.车外廓宽 = (车外廓宽);
pv.车外廓长 = (车外廓长);
pv.车外廓高 = (车外廓高);
pv.车身型式 = 车身型式;
pv.车身颜色 = 车身颜色;
pv.车辆型号 = 车辆型号;
pv.车辆类型 = 车辆类型;
pv.轮胎规格 = 轮胎规格;
pv.轴数 = (轴数);
pv.轴距 = (轴距);
db.PassengerVehicles.Add(pv);
try
{
await db.SaveChangesAsync();
}
catch (Exception e)
{
Console.WriteLine("第" + i + "条：" + e.Message);
}
}
}
#endregion
}
return(dt);
}
// static HSSFWorkbook hssfworkbook;
// static XSSFWorkbook xssfworkbook;
public static DataTable GetData(string filePath)
{
IWorkbook wk = null;
bool isHss = false;
try
{
using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
{
if (filePath.ToLower().EndsWith(".xls"))
{
wk = new HSSFWorkbook(file);
isHss = true;
}
else
{
wk = new XSSFWorkbook(file);
}
}
}
catch (Exception e)
{
throw e;
}
DataTable dt = new DataTable();
NPOI.SS.UserModel.ISheet sheet = wk.GetSheetAt(0);
try
{
//获取标题
var row1 = sheet.GetRow(0); //获取第一行即标头
int cellCount = row1.LastCellNum; //第一行的列数
string excelColName;
for (int j = 0; j < cellCount; j++)
{
excelColName = row1.GetCell(j).StringCellValue.ToUpper().Trim();
// if (!string.IsNullOrEmpty(excelColName))
// {
DataColumn column = new DataColumn(excelColName);
dt.Columns.Add(column);
// }
// dt.Columns.Add(Convert.ToChar(((int)'A') + j).ToString());
}
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
rows.MoveNext();
while (rows.MoveNext())
{
IRow row = null;
if (isHss)
{
row = (HSSFRow)rows.Current;
}
else
{
row = (XSSFRow)rows.Current;
}
DataRow dr = dt.NewRow();
for (int i = 0; i < cellCount; i++)
{
ICell cell = row.GetCell(i);
if (cell == null || cell.ToString().ToUpper() == "NULL")
{
dr[i] = null;
}
else
{
if (cell.CellType == CellType.Numeric && DateUtil.IsCellDateFormatted(cell))
{
dr[i] = cell.DateCellValue;
}
else if (cell.CellType == CellType.Formula)
{
dr[i] = cell.NumericCellValue.ToString();
}
else
{
dr[i] = cell.ToString().Trim();
}
}
}
dt.Rows.Add(dr);
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
wk = null;
sheet = null;
}
return(dt);
}
public static DataTable ImportExcelFile(string filePath, string sheetName)
{
IWorkbook hssfworkbook;
#region//初始化信息
try
{
using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
{
hssfworkbook = new XSSFWorkbook(file);
}
}
catch (Exception e)
{
using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
{
hssfworkbook = new HSSFWorkbook(file);
}
}
#endregion
NPOI.SS.UserModel.ISheet sheet = !string.IsNullOrEmpty(sheetName) ? hssfworkbook.GetSheet(sheetName) : hssfworkbook.GetSheetAt(0);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
DataTable dt = new DataTable();
rows.MoveNext();
IRow row = null;
try
{
row = (HSSFRow)rows.Current;
}
catch
{
row = (XSSFRow)rows.Current;
}
for (int j = 0; j < (sheet.GetRow(0).LastCellNum); j++)
{
//dt.Columns.Add(Convert.ToChar(((int)'A') + j).ToString());
//将第一列作为列表头
dt.Columns.Add(row.GetCell(j).ToString());
}
while (rows.MoveNext())
{
try
{
row = (HSSFRow)rows.Current;
}
catch
{
row = (XSSFRow)rows.Current;
}
DataRow dr = dt.NewRow();
for (int i = 0; i < row.LastCellNum; i++)
{
NPOI.SS.UserModel.ICell cell = row.GetCell(i);
if (cell == null)
{
dr[i] = null;
}
else
{
switch (cell.CellType)
{
case CellType.NUMERIC:
if (HSSFDateUtil.IsCellDateFormatted(cell))
{
dr[i] = cell.DateCellValue.ToString();
}
else
{
dr[i] = cell.ToString();
}
break;
default:
dr[i] = cell.ToString();
break;
}
}
}
dt.Rows.Add(dr);
}
return(dt);
}
public void TestEvaluateAll()
{
HSSFWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.ISheet s1 = wb.CreateSheet();
NPOI.SS.UserModel.ISheet s2 = wb.CreateSheet();
wb.SetSheetName(0, "S1");
wb.SetSheetName(1, "S2");
IRow s1r1 = s1.CreateRow(0);
IRow s1r2 = s1.CreateRow(1);
IRow s2r1 = s2.CreateRow(0);
ICell s1r1c1 = s1r1.CreateCell(0);
ICell s1r1c2 = s1r1.CreateCell(1);
ICell s1r1c3 = s1r1.CreateCell(2);
s1r1c1.SetCellValue(22.3);
s1r1c2.SetCellValue(33.4);
s1r1c3.CellFormula = ("SUM(A1:B1)");
ICell s1r2c1 = s1r2.CreateCell(0);
ICell s1r2c2 = s1r2.CreateCell(1);
ICell s1r2c3 = s1r2.CreateCell(2);
s1r2c1.SetCellValue(-1.2);
s1r2c2.SetCellValue(-3.4);
s1r2c3.CellFormula = ("SUM(A2:B2)");
ICell s2r1c1 = s2r1.CreateCell(0);
s2r1c1.CellFormula = ("S1!A1");
// Not Evaluated yet
Assert.AreEqual(0.0, s1r1c3.NumericCellValue, 0);
Assert.AreEqual(0.0, s1r2c3.NumericCellValue, 0);
Assert.AreEqual(0.0, s2r1c1.NumericCellValue, 0);
// Do a full Evaluate, as per our docs
// uses EvaluateFormulaCell()
for (int sheetNum = 0; sheetNum < wb.NumberOfSheets; sheetNum++)
{
NPOI.SS.UserModel.ISheet sheet = wb.GetSheetAt(sheetNum);
HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(wb);
for (IEnumerator rit = sheet.GetRowEnumerator(); rit.MoveNext();)
{
IRow r = (IRow)rit.Current;
for (IEnumerator cit = r.GetEnumerator(); cit.MoveNext();)
{
ICell c = (ICell)cit.Current;
if (c.CellType == NPOI.SS.UserModel.CellType.FORMULA)
{
evaluator.EvaluateFormulaCell(c);
// For Testing - all should be numeric
Assert.AreEqual(NPOI.SS.UserModel.CellType.NUMERIC, evaluator.EvaluateFormulaCell(c));
}
}
}
}
// Check now as expected
Assert.AreEqual(55.7, wb.GetSheetAt(0).GetRow(0).GetCell(2).NumericCellValue, 0);
Assert.AreEqual("SUM(A1:B1)", wb.GetSheetAt(0).GetRow(0).GetCell(2).CellFormula);
Assert.AreEqual(NPOI.SS.UserModel.CellType.FORMULA, wb.GetSheetAt(0).GetRow(0).GetCell(2).CellType);
Assert.AreEqual(-4.6, wb.GetSheetAt(0).GetRow(1).GetCell(2).NumericCellValue, 0);
Assert.AreEqual("SUM(A2:B2)", wb.GetSheetAt(0).GetRow(1).GetCell(2).CellFormula);
Assert.AreEqual(NPOI.SS.UserModel.CellType.FORMULA, wb.GetSheetAt(0).GetRow(1).GetCell(2).CellType);
Assert.AreEqual(22.3, wb.GetSheetAt(1).GetRow(0).GetCell(0).NumericCellValue, 0);
Assert.AreEqual("'S1'!A1", wb.GetSheetAt(1).GetRow(0).GetCell(0).CellFormula);
Assert.AreEqual(NPOI.SS.UserModel.CellType.FORMULA, wb.GetSheetAt(1).GetRow(0).GetCell(0).CellType);
// Now do the alternate call, which zaps the formulas
// uses EvaluateInCell()
for (int sheetNum = 0; sheetNum < wb.NumberOfSheets; sheetNum++)
{
NPOI.SS.UserModel.ISheet sheet = wb.GetSheetAt(sheetNum);
HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(wb);
for (IEnumerator rit = sheet.GetRowEnumerator(); rit.MoveNext();)
{
IRow r = (IRow)rit.Current;
for (IEnumerator cit = r.GetEnumerator(); cit.MoveNext();)
{
ICell c = (ICell)cit.Current;
if (c.CellType == NPOI.SS.UserModel.CellType.FORMULA)
{
evaluator.EvaluateInCell(c);
}
}
}
}
Assert.AreEqual(55.7, wb.GetSheetAt(0).GetRow(0).GetCell(2).NumericCellValue, 0);
Assert.AreEqual(NPOI.SS.UserModel.CellType.NUMERIC, wb.GetSheetAt(0).GetRow(0).GetCell(2).CellType);
Assert.AreEqual(-4.6, wb.GetSheetAt(0).GetRow(1).GetCell(2).NumericCellValue, 0);
Assert.AreEqual(NPOI.SS.UserModel.CellType.NUMERIC, wb.GetSheetAt(0).GetRow(1).GetCell(2).CellType);
Assert.AreEqual(22.3, wb.GetSheetAt(1).GetRow(0).GetCell(0).NumericCellValue, 0);
Assert.AreEqual(NPOI.SS.UserModel.CellType.NUMERIC, wb.GetSheetAt(1).GetRow(0).GetCell(0).CellType);
}
public static Dictionary <string, DataTable> ExcelToDataTable(string filePath)
{
Dictionary <string, DataTable> result = new Dictionary <string, DataTable>();
HSSFWorkbook hssfworkbook;
int ColumnDataNum = 0;
#region//初始化信息
try
{
using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
{
hssfworkbook = new HSSFWorkbook(file);
}
}
catch (Exception e)
{
throw e;
}
#endregion
for (int i = 0; i < hssfworkbook.NumberOfSheets; i++)
{
NPOI.SS.UserModel.ISheet sheet = hssfworkbook.GetSheetAt(i);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
DataTable dt = new DataTable();
rows.MoveNext();
HSSFRow row = (HSSFRow)rows.Current;
for (int j = 0; j < (sheet.GetRow(0).LastCellNum); j++)
{
//dt.Columns.Add(Convert.ToChar(((int)'A') + j).ToString());
//将第一列作为列表头
dt.Columns.Add(row.GetCell(j).ToString());
}
while (rows.MoveNext())
{
row = (HSSFRow)rows.Current;
DataRow dr = dt.NewRow();
for (int j = 0; j < row.LastCellNum; j++)
{
NPOI.SS.UserModel.ICell cell = row.GetCell(j);
if (cell == null)
{
dr[j] = null;
}
else
{
dr[j] = cell.ToString();
if (cell.ToString() == "")
{
ColumnDataNum = ColumnDataNum + 1;
}
}
}
if (ColumnDataNum != row.LastCellNum)
{
dt.Rows.Add(dr);
}
ColumnDataNum = 0;
}
result.Add(sheet.SheetName, dt);
}
//文件是否存在
if (System.IO.File.Exists(filePath))
{
}
return(result);
}
/// <summary>
/// Goes through the Workbook, optimising the fonts by
/// removing duplicate ones.
/// For now, only works on fonts used in HSSFCellStyle
/// and HSSFRichTextString. Any other font uses
/// (eg charts, pictures) may well end up broken!
/// This can be a slow operation, especially if you have
/// lots of cells, cell styles or rich text strings
/// </summary>
/// <param name="workbook">The workbook in which to optimise the fonts</param>
public static void OptimiseFonts(HSSFWorkbook workbook)
{
// Where each font has ended up, and if we need to
// delete the record for it. Start off with no change
short[] newPos =
new short[workbook.Workbook.NumberOfFontRecords + 1];
bool[] zapRecords = new bool[newPos.Length];
for (int i = 0; i < newPos.Length; i++)
{
newPos[i] = (short)i;
zapRecords[i] = false;
}
// Get each font record, so we can do deletes
// without Getting confused
FontRecord[] frecs = new FontRecord[newPos.Length];
for (int i = 0; i < newPos.Length; i++)
{
// There is no 4!
if (i == 4)
{
continue;
}
frecs[i] = workbook.Workbook.GetFontRecordAt(i);
}
// Loop over each font, seeing if it is the same
// as an earlier one. If it is, point users of the
// later duplicate copy to the earlier one, and
// mark the later one as needing deleting
// Note - don't change built in fonts (those before 5)
for (int i = 5; i < newPos.Length; i++)
{
// Check this one for being a duplicate
// of an earlier one
int earlierDuplicate = -1;
for (int j = 0; j < i && earlierDuplicate == -1; j++)
{
if (j == 4)
{
continue;
}
FontRecord frCheck = workbook.Workbook.GetFontRecordAt(j);
if (frCheck.SameProperties(frecs[i]))
{
earlierDuplicate = j;
}
}
// If we got a duplicate, mark it as such
if (earlierDuplicate != -1)
{
newPos[i] = (short)earlierDuplicate;
zapRecords[i] = true;
}
}
// Update the new positions based on
// deletes that have occurred between
// the start and them
// Only need to worry about user fonts
for (int i = 5; i < newPos.Length; i++)
{
// Find the number deleted to that
// point, and adjust
short preDeletePos = newPos[i];
short newPosition = preDeletePos;
for (int j = 0; j < preDeletePos; j++)
{
if (zapRecords[j])
{
newPosition--;
}
}
// Update the new position
newPos[i] = newPosition;
}
// Zap the un-needed user font records
for (int i = 5; i < newPos.Length; i++)
{
if (zapRecords[i])
{
workbook.Workbook.RemoveFontRecord(
frecs[i]
);
}
}
// Tell HSSFWorkbook that it needs to
// re-start its HSSFFontCache
workbook.ResetFontCache();
// Update the cell styles to point at the
// new locations of the fonts
for (int i = 0; i < workbook.Workbook.NumExFormats; i++)
{
ExtendedFormatRecord xfr = workbook.Workbook.GetExFormatAt(i);
xfr.FontIndex = (
newPos[xfr.FontIndex]
);
}
// Update the rich text strings to point at
// the new locations of the fonts
// Remember that one underlying unicode string
// may be shared by multiple RichTextStrings!
ArrayList doneUnicodeStrings = new ArrayList();
for (int sheetNum = 0; sheetNum < workbook.NumberOfSheets; sheetNum++)
{
NPOI.SS.UserModel.ISheet s = workbook.GetSheetAt(sheetNum);
IEnumerator rIt = s.GetRowEnumerator();
while (rIt.MoveNext())
{
HSSFRow row = (HSSFRow)rIt.Current;
IEnumerator cIt = row.GetEnumerator();
while (cIt.MoveNext())
{
ICell cell = (HSSFCell)cIt.Current;
if (cell.CellType == NPOI.SS.UserModel.CellType.STRING)
{
HSSFRichTextString rtr = (HSSFRichTextString)cell.RichStringCellValue;
UnicodeString u = rtr.RawUnicodeString;
// Have we done this string already?
if (!doneUnicodeStrings.Contains(u))
{
// Update for each new position
for (short i = 5; i < newPos.Length; i++)
{
if (i != newPos[i])
{
u.SwapFontUse(i, newPos[i]);
}
}
// Mark as done
doneUnicodeStrings.Add(u);
}
}
}
}
}
}
public static List <DataTable> ImportExcelFile(string filePath, MyDataGridView dgv1, MyDataGridView dgv2, MyDataGridView dgv3, MyDataGridView dgv4, MyDataGridView dgv5)
{
List <DataTable> dts = new List <DataTable>();
HSSFWorkbook hssfworkbook;
#region//初始化信息
try
{
using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
{
hssfworkbook = new HSSFWorkbook(file);
}
}
catch (Exception e)
{
throw e;
}
#endregion
NPOI.SS.UserModel.ISheet sheet = hssfworkbook.GetSheetAt(0);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
DataTable dt1 = (DataTable)dgv1.DataSource;
DataTable dt2 = (DataTable)dgv2.DataSource;
DataTable dt3 = (DataTable)dgv3.DataSource;
DataTable dt4 = (DataTable)dgv4.DataSource;
DataTable dt5 = (DataTable)dgv5.DataSource;
int cols = sheet.GetRow(0).LastCellNum;
if (cols != 15 && cols != 13)
{
return(dts);
}
dt1.Clear();
dt2.Clear();
dt3.Clear();
dt4.Clear();
dt5.Clear();
//前两行为标题
rows.MoveNext();
rows.MoveNext();
int count = 0;
while (rows.MoveNext())
{
count++;
if (count > 10)
{
break;
}
HSSFRow row = (HSSFRow)rows.Current;
DataRow dr1 = dt1.NewRow();
DataRow dr2 = dt2.NewRow();
DataRow dr3 = dt3.NewRow();
DataRow dr4 = dt4.NewRow();
DataRow dr5 = dt5.NewRow();
NPOI.SS.UserModel.ICell cell = null;
createColumn(dt1, cell, row, dr1, 0, 1); //输入功率标定
createColumn(dt2, cell, row, dr2, 3, 4); //输出功率标定
createColumn(dt3, cell, row, dr3, 6, 7); //反射功率标定
createColumn(dt4, cell, row, dr4, 9, 10); //ALC功率标定
//衰减补偿
if (count > 3)
{
continue;
}
createColumn(dt5, cell, row, dr5, 12, 14);
}
dts.Add(dt1);
dts.Add(dt2);
dts.Add(dt3);
dts.Add(dt4);
dts.Add(dt5);
return(dts);
}