private void btnShiTiImport_Click(object sender, EventArgs e)
{
string path = string.Empty;
string fileName_Short = string.Empty;
OpenFileDialog ofd = new OpenFileDialog();
ofd.Filter = "标签|*.xls;*.xlsx";
if (ofd.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
path = ofd.FileName;
}
if (!string.IsNullOrEmpty(path))
{
try
{
fileName_Short = ofd.SafeFileName.Substring(0, ofd.SafeFileName.LastIndexOf("."));
//a
//DataSet dsq = OfficeTools.GetExcelToDataTableBySheet(path);
//if (dsq != null)
//{ }
//a
DataTable excelData = NPOIExeclHelper.RenderFromExcel(path);
if (excelData != null && excelData.Rows.Count > 0)
{
bool bRight = ImportCheck(excelData);
if (!bRight)
{
return;
}
SaveExcelData(excelData, fileName_Short);
}
else
{
MessageBox.Show("导入的Excel不存在数据行！");
return;
}
}
catch (Exception ex)
{
MessageBox.Show("导入的Excel异常," + ex.Message);
return;
}
}
}
/// <summary>
/// 导入的Excel进行校验
/// </summary>
/// <param name="dt"></param>
private bool ImportCheck(DataTable dt)
{
if (dt == null)
{
MessageBox.Show("Excel 数据读取错误！");
return(false);
}
if (dt.Rows.Count == 0)
{
MessageBox.Show("Excel 中没有读取到数据！");
return(false);
}
if (dt.Columns.Count != 21)
{
MessageBox.Show("Excel 模板列不正确！");
return(false);
}
/*
* 商品编码
* 商品名称
* 规格
* 采购数量
*/
//克隆结构
bool status;
//List<string> skuList = new List<string>();
DataTable ErrorDataTable = dt.Clone();
int errorCount = 0; //失败行数
int successCount = 0; //成功行数
foreach (DataRow row in dt.Rows)
{
DataRow rowDetail = row;// ErrorDataTable.NewRow();
//去掉重复项
status = true;
string strTiXing = row[5].ToString().Trim(); //题型
string strZhengWen = row[6].ToString().Trim(); //正文
string strXuanXiang = row[7].ToString().Trim(); //试题选项
string strDaAn = row[8].ToString().Trim(); //试题答案
if (strTiXing.Length == 0)
{
rowDetail[5] = "@?@必填";
status = false;
}
if (strZhengWen.Length == 0)
{
rowDetail[6] = "@?@必填";
status = false;
}
if (strTiXing != "判断题")
{
if (strXuanXiang.Length == 0)
{
rowDetail[7] = "@?@必填";
status = false;
}
}
if (strDaAn.Length == 0)
{
rowDetail[8] = "@?@必填";
status = false;
}
if (status == false)
{
errorCount++;
ErrorDataTable.Rows.Add(rowDetail.ItemArray);
}
else
{
successCount++;
ErrorDataTable.Rows.Add(rowDetail.ItemArray);
}
}
//如果有失败的打开失败的Excel
if (errorCount > 0)
{
MessageBox.Show("有" + errorCount + "条记录未通过格式检查,请修改后重写导入！");
try
{
string path = AppDomain.CurrentDomain.BaseDirectory;
string templatePath = Path.Combine(path, "ErrorFile");
if (!Directory.Exists(templatePath))
{
Directory.CreateDirectory(templatePath);
}
string errorPath = templatePath + "\\导入试题错误信息" + String.Format("{0:yyyyMMdd}.xls", DateTime.Now);
if (File.Exists(errorPath))
{
File.Delete(errorPath);
}
NPOIExeclHelper.ExportExcelFileContainCss(ErrorDataTable, "错误的试题", errorPath);
try
{
System.Diagnostics.Process process = new System.Diagnostics.Process();
process.StartInfo.FileName = errorPath;
process.StartInfo.Verb = "Open";
process.StartInfo.WindowStyle = System.Diagnostics.ProcessWindowStyle.Normal;
process.Start();
return(false);
}
catch
{
MessageBox.Show("找不到此文件类型的默认打开！");
return(false);
}
}
catch (Exception ex)
{
MessageBox.Show("导入试题库错误信息处理失败！" + ex.Message);
return(false);
}
}
return(true);
}
