一键导入Excel 文件 （同步）
引用类库：using NPOI.SS.UserModel;
string strSql = string.Empty;
DataTable dt = new DataTable();
DataSet ds = null;
string Msg = string.Empty;
string filePath = "";//打开文件路径
Dictionary<string, int> Columns = new Dictionary<string, int>();//记录字段在当前表格中的顺序
ISheet Currentsheet = null;//当前的Sheet;
int Step = 3000;//每个线程处理的行数
int currentThreadQty = 0;//当前的子线程数
int ShowMessageCount = 50;//多少行才提示
int CurrentValue = 0;
/// <summary>
/// 读取excel 到DataTable 中
/// </summary>
/// <param name="strFilePath">excel文档路径 这里是绝对路径，如果不是绝对路径</param>
/// <param name="StartRowIndex">开始的行下标(包括标题行)，起始值是0</param>
/// <param name="SheetIndex">Shoee的下标，默认是0起始值是0</param>
/// <param name="returnRowCount">返回指定行数的数据，主要是为了获取预览数据，0表示返回所有数据，否则就是指定的行数</param>
/// <param name="FrisRowToColumn">第一行是否当作转换成数据表的列</param>
/// <returns></returns>
public DataTable Import(string strFilePath, int StartRowIndex, int SheetIndex = 0, int returnRowCount = 0, bool FrisRowToColumn = true)
{
Columns = new Dictionary<string, int>();//初始化
DataTable dt = new DataTable();
IWorkbook hssfworkbook;
using (FileStream file = new FileStream(strFilePath, FileMode.Open, FileAccess.Read))
{
hssfworkbook = WorkbookFactory.Create(file);
}
Currentsheet = hssfworkbook.GetSheetAt(SheetIndex);
if (Currentsheet.LastRowNum < Step)
{
ShowMessageCount = 50;
}
else if (Currentsheet.LastRowNum > Step && Currentsheet.LastRowNum < 10000)
{
ShowMessageCount = 100;
}
else
{
ShowMessageCount = 1000;
}
System.Collections.IEnumerator rows = Currentsheet.GetRowEnumerator();
IRow headerRow = Currentsheet.GetRow(StartRowIndex);
if (headerRow == null) return dt;//这里为什么读不到数据，还要再看一下
int cellCount = headerRow.LastCellNum;
if (FrisRowToColumn)//第一行转换成字段
{
for (int j = 0; j < cellCount; j++)
{
ICell cell = headerRow.GetCell(j);
if (cell != null)
{
Columns[cell.ToString().Trim()] = j;
dt.Columns.Add(cell.ToString());
}
else
{
dt.Columns.Add("F" + j);
}
}
#region 验证表头是否正确
try
{
int ii = Columns["Sending NO."];
ii = Columns["Kolliner"];
ii = Columns["Customer Material"];
ii = Columns["ID NO."];
ii = Columns["INA Description"];
ii = Columns["Bath"];
ii = Columns["Quantity"];
ii = Columns["Net-kilos"];
ii = Columns["Gross-kilos"];
ii = Columns["Colli-Abmessung"];
ii = Columns["Volume"];
}
catch
{
MessageBox.Show("请将表头设置成正确的值");
return dt;
}
#endregion
StartRowIndex++;
}
else
{
for (int j = 0; j < cellCount; j++)
{
dt.Columns.Add("F" + j);
}
}
int rowCount = 0;
for (int i = StartRowIndex; i <= Currentsheet.LastRowNum; i++)
{
IRow row = Currentsheet.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);
//如果已经到达要求的行数，返回
rowCount++;
if (returnRowCount > 0 && rowCount >= returnRowCount)
{
break;
}
}
return dt;
}
/// <summary>
/// 导入
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnInput_Click(object sender, EventArgs e)
{
OpenFileDialog fileDialog = new OpenFileDialog();
fileDialog.Multiselect = true;
fileDialog.Title = "请选择文件";
fileDialog.Filter = "所有文件(*.*)|*.*";
if (fileDialog.ShowDialog() == DialogResult.OK)
{
//文件路径
filePath = fileDialog.FileName;
}
bool FrisRowToColumn = true;
try
{
this.dataGridView1.DataSource = Import(filePath, 0, 0, 0, FrisRowToColumn);
// MessageBox.Show("Excel表格加载完毕", "提示信息");
int endIndex = this.dataGridView1.RowCount;
DoAsyncImport(1, endIndex);
if (!string.IsNullOrEmpty(Msg.Trim()))
{
MessageBox.Show(Msg, "提示信息");
return;
}
else
{
MessageBox.Show("数据导入成功！", "提示信息");
this.btnQuary_Click(null, null);
}
}
catch (Exception)
{
MessageBox.Show("请关闭当前需要上传的文档，再进行加载尝试！", "提示信息");
return;
}
}
/// <summary>
/// 执行异步导入动作
/// </summary>
private void DoAsyncImport(int startIndex, int endIndex)
{
if (endIndex > Currentsheet.LastRowNum) endIndex = Currentsheet.LastRowNum + 1;
for (int i = startIndex; i < endIndex; i++)
{
IRow row = Currentsheet.GetRow(i);
if (i == 0 || row == null)//第一行是表头，不处理
{
continue;
}
//插入到指定表
DoInsertDB("NeedleShippmentLists", row);
CurrentValue++;
}
currentThreadQty--;
}
/// <summary>
/// 处理单行数据，将数据插入到数据库中
/// </summary>
/// <param name="Table"></param>
/// <param name="row"></param>
private void DoInsertDB(string Table, IRow row)
{
NeedleShippmentLists nList = new NeedleShippmentLists();
string strgk = "";
nList.Shippment_No = Convert.ToString(row.GetCell(Columns["Sending NO."]));
if (string.IsNullOrEmpty(nList.Shippment_No))
{
return;
}
nList.Case_No = Convert.ToString(row.GetCell(Columns["Kolliner"]));
nList.Custom_Material = Convert.ToString(row.GetCell(Columns["Customer Material"]));
string Material = Convert.ToString(row.GetCell(Columns["ID NO."]));
nList.Material = Material.Replace("-","");
nList.Material_Description = Convert.ToString(row.GetCell(Columns["INA Description"]));
// nList.Batch = Convert.ToString(row.GetCell(Columns["Bath"]));
string strBatch = Convert.ToString(row.GetCell(Columns["Bath"]));
if (strBatch.Length == 8)
{
//批次10 位数，不足用0补齐
strBatch = "00" + strBatch;
}
nList.Batch = strBatch;
nList.Quantity = Convert.ToInt32(row.GetCell(Columns["Quantity"]).ToString());
nList.Net_Kilogram = Convert.ToDecimal(row.GetCell(Columns["Net-kilos"]).ToString()==""?"0": row.GetCell(Columns["Net-kilos"]).ToString());
try
{
nList.Gross_Kilogram = Convert.ToDecimal(row.GetCell(Columns["Gross-kilos"]).ToString() == "" ? "0" : row.GetCell(Columns["Gross-kilos"]).ToString());
}
catch (Exception)
{ strgk = row.GetCell(Columns["Gross-kilos"]).ToString() == "" ? "0" : row.GetCell(Columns["Gross-kilos"]).ToString();
string[] Arrarygk = strgk.Split('+');
nList.Gross_Kilogram = nList.Net_Kilogram + Convert.ToDecimal(Arrarygk[1]);
}
nList.Container_Size = row.GetCell(Columns["Colli-Abmessung"]).ToString();
nList.Volume = Convert.ToDecimal(row.GetCell(Columns["Volume"]).ToString() == "" ? "0" : row.GetCell(Columns["Volume"]).ToString());
bool isOk = nList.Exists(nList);
//执行插入sql
if (!isOk)
{
nList.Add(nList);
}
else
{
nList.Update(nList);
}
}
