这个导入是两个月前做的，其中引用了一些文章的想法，但是忘记是哪些文章了，抱歉。
读取的是一个excel里面某一列，用“/”连接起来的字符串，如：
a/b/c/d/e
a/b/c/d/g
a/b/c/h
a/b/c/h/k
…
里面的层级数据有些复杂，后面有时间我来再去看下。
StringBuilder str = new StringBuilder();
DataTable _dt = null;
private void btnChooseFile_Click(object sender, EventArgs e)
{
_dt = new DataTable();
string err = "";
ExcelToTable tab = new ExcelToTable();
int successCount = 0, failCount = 0;
try
{
//初始化一个OpenFileDialog类
OpenFileDialog fileDialog = new OpenFileDialog();
//判断用户是否正确的选择了文件
if (fileDialog.ShowDialog() == DialogResult.OK)
{
//获取用户选择文件的后缀名
string extension = Path.GetExtension(fileDialog.FileName);
//声明允许的后缀名
string[] str = new string[] { ".xls", ".xlsx" };
if (!str.Contains(extension))
{
MessageBox.Show("不是Excel！");
return;
}
else
{
System.Diagnostics.Stopwatch watch = new System.Diagnostics.Stopwatch();
watch.Start();//开始计时
this.textBox1.Text = fileDialog.FileName;
DataSet dt = new DataSet();
dt = tab.ExcelToDS(fileDialog.FileName, out err);
if (dt != null)
{
_dt = dt.Tables[0];
int rowCount = _dt.Rows.Count;
int columnCount = _dt.Columns.Count;
DataTable rtDB = _dt.Clone();
int maxLength = 0;
List<string> firstLevels = new List<string>();
List<string> fullPathList = new List<string>();
List<string[]> datas = new List<string[]>();
foreach (DataRow row in _dt.Rows)
{
var fullPath = row["全路径"].ToString();
var fullPathArray = fullPath.Split('/');
var resultArray = fullPathArray.Skip(5).Take(fullPathArray.Length - 5).ToArray();
maxLength = resultArray.Length > maxLength ? resultArray.Length : maxLength;
datas.Add(resultArray);
}
var res = InsertBatch(datas, maxLength);
this.label1.Text = "总共" + rowCount + "条，" + columnCount + "列";
watch.Stop();//停止计时
TimeSpan ts2 = watch.Elapsed;
this.label6.Text = "成功" + successCount + "条，失败" + failCount + "条";
this.label3.Text = string.Format("{0:F}", ts2.TotalMinutes) + "分钟";
var msg = failCount > 0 ? "但是导入有失败数据，失败数据请根据路径查看导出的Excel！" : "全部导入成功。";
MessageBox.Show("导入完成。" + msg);
}
}
}
}
catch (Exception ex)
{
//Log.WriteLog(str.ToString());
err = err + ex.Message;
}
if (!string.IsNullOrEmpty(err))
{
MessageBox.Show(err);
}
}
public Dictionary<string, object> InsertBatch(List<string[]> datas, int maxLength)
{
List<string> sqlList = new List<string>();
List<List<string>> result = new List<List<string>>();
foreach (var item in datas)
{
result.Add(GetArray3(item));
}
// 定义方法返回值
Dictionary<string, object> message = new Dictionary<string, object>();
// 定义主键id 对应数据库中cat_id
int index = 1;
int parentId = 0;
string levelName = null;//分类名称
// tempMap为临时变量,记录那些已经添加过了 key为CatName+层级 value 为记录对象,例如
// <Home_1,category> 表示存储第一级对象
string parentLevelName = "";
Dictionary<string, DeviceSort> tempMap = new Dictionary<string, DeviceSort>();
// 记录最终需要插入数据库的数据
List<DeviceSort> insertDatas = new List<DeviceSort>();
DeviceSort level = null;
for (int columnIndex = 0; columnIndex < maxLength; columnIndex++)
{
for (int i = 0; i < result.Count; i++)
{
List<string> rows = result[i];
if (columnIndex >= rows.Count)
{
continue;
}
//设置层级的关联关系
if (columnIndex == 0)
{
// 如果是第一列,parentid 默认为0
parentId = 0;
}
else if (columnIndex == 1)
{
// 如果不是第一列,则在tempMap中寻找对应的父类CategoryID作为parentid,当前列向后退一列
parentId = tempMap[rows[columnIndex - 1] + "_" + columnIndex].CategoryID;
parentLevelName = rows[columnIndex - 1];
}
else
{
parentLevelName = rows[columnIndex - 1];
parentId = tempMap[rows[columnIndex - 2] + "_" + rows[columnIndex - 1] + "_" + columnIndex].CategoryID;
}
levelName = rows[columnIndex];
//tempMap中没有记录过则表示是要插入的数据
if (columnIndex == 0 && !tempMap.ContainsKey(levelName + "_" + (columnIndex + 1)))
{
level = new DeviceSort();
level.CategoryID = index++;
level.LevelFlag = "";
level.LevelName = levelName;
level.CodeName = levelName;
level.ParentCode = parentId;
level.LevelIndex = columnIndex + 1;
level.IsEnd = 0;//columnIndex == rows.Count - 1 ? 1 : 0;
tempMap.Add(levelName + "_" + (columnIndex + 1), level);
//添加到最终要导入的列表中
insertDatas.Add(level);
sqlList.Add($"INSERT INTO `be2-bim-afmp-hydz2.1`.`om_sys_devicesort_backup` (`CategoryID`,`LevelFlag`,`LevelName`,`CodeName`,`LevelIndex`,`ParentCode`,`IsEnd`,`SortIndex`,`Usable`,`Qty_Sum`) VALUES({level.CategoryID},'','{level.LevelName}','{level.CodeName}',{level.LevelIndex},{level.ParentCode},{level.IsEnd},1,0,1);");
}
else if (columnIndex != 0 && columnIndex != rows.Count - 1 && !tempMap.ContainsKey(parentLevelName + "_" + levelName + "_" + (columnIndex + 1)))
{
level = new DeviceSort();
level.CategoryID = index++;
level.LevelFlag = "";
level.LevelName = levelName;
level.CodeName = levelName;
level.ParentCode = parentId;
level.LevelIndex = columnIndex + 1;
level.IsEnd = 0;//columnIndex == rows.Count - 1 ? 1 : 0;
tempMap.Add(parentLevelName + "_" + levelName + "_" + (columnIndex + 1), level);
//添加到最终要导入的列表中
insertDatas.Add(level);
sqlList.Add($"INSERT INTO `be2-bim-afmp-hydz2.1`.`om_sys_devicesort_backup` (`CategoryID`,`LevelFlag`,`LevelName`,`CodeName`,`LevelIndex`,`ParentCode`,`IsEnd`,`SortIndex`,`Usable`,`Qty_Sum`) VALUES({level.CategoryID},'','{level.LevelName}','{level.CodeName}',{level.LevelIndex},{level.ParentCode},{level.IsEnd},1,0,1);");
}
if (columnIndex == rows.Count - 1)//当前路径下的最后一个层级
{
level = new DeviceSort();
level.CategoryID = index++;
level.LevelFlag = "";
level.LevelName = levelName;
level.CodeName = levelName;
level.ParentCode = parentId;
level.LevelIndex = columnIndex + 1;
level.IsEnd = 1;//最后一个层级的标志
//tempMap.Add(levelName + "_" + (columnIndex + 1), level);
//添加到最终要导入的列表中
insertDatas.Add(level);
sqlList.Add($"INSERT INTO `be2-bim-afmp-hydz2.1`.`om_sys_devicesort_backup` (`CategoryID`,`LevelFlag`,`LevelName`,`CodeName`,`LevelIndex`,`ParentCode`,`IsEnd`,`SortIndex`,`Usable`,`Qty_Sum`) VALUES({level.CategoryID},'','{level.LevelName}','{level.CodeName}',{level.LevelIndex},{level.ParentCode},{level.IsEnd},1,0,1);");
}
}
}
int mmm = CoreMySqlHelper.ExecuteList(sqlList);
return message;
}
public List<string> GetArray3(string[] array)
{
List<string> list = new List<string>();
foreach (var item in array)
{
list.Add(item);
}
return list;
}
public class CoreMySqlHelper
{
//数据库连接字符串
public static string ConnectionString = "server=192.168.11.21;port=1234;database=aaaa;user=root;password=a123456;Allow User Variables=TRUE;Charset=utf8;";
public static int ExecuteNonQuery(string sql)
{
int count = 0;
try
{
using (MySqlConnection connection = new MySqlConnection(ConnectionString))
{
try
{
connection.Open();
MySqlCommand command = new MySqlCommand(sql, connection);
count = command.ExecuteNonQuery();
}
catch (Exception ex)
{
connection.Close();
return count;
}
finally
{
connection.Close();
}
}
}
catch (Exception)
{
}
return count;
}
/// <summary>
/// 查找数据
/// </summary>
public static DataTable GetTable(string sql)
{
DataTable dt = new DataTable();
using (MySqlConnection connection = new MySqlConnection(ConnectionString))
{
try
{
connection.Open();
using (MySqlDataAdapter adpter = new MySqlDataAdapter(sql, connection))
{
adpter.Fill(dt);
}
}
catch (Exception e)
{
connection.Close();
throw e;
}
finally
{
connection.Close();
}
}
return dt;
}
public static DataSet Query(string sql)
{
using (MySqlConnection connection = new MySqlConnection(ConnectionString))
{
DataSet ds = new DataSet();
try
{
connection.Open();
MySqlDataAdapter command = new MySqlDataAdapter(sql, connection);
command.Fill(ds, "ds");
}
catch (Exception e)
{
connection.Close();
}
finally
{
connection.Close();
}
return ds;
}
}
/// <summary>
/// 简单事务
/// </summary>
public static void Transaction(string sql)
{
using (MySqlConnection connection = new MySqlConnection(ConnectionString))
{
connection.Open();
MySqlTransaction transaction = connection.BeginTransaction();
try
{
MySqlCommand cmd = new MySqlCommand(sql, connection, transaction);
cmd.ExecuteNonQuery();
transaction.Rollback();
transaction = connection.BeginTransaction();
cmd = new MySqlCommand($"insert into person values (null,'王五','{DateTime.Now}');", connection, transaction);
cmd.ExecuteNonQuery();
transaction.Commit();
}
catch (Exception e)
{
transaction.Rollback();
throw e;
}
connection.Close();
}
}
/// <summary>
/// 执行存储过程
/// </summary>
public static void StoredProcedure(string ProcedureName)
{
using (MySqlConnection connection = new MySqlConnection(ConnectionString))
{
try
{
connection.Open();
MySqlCommand command = new MySqlCommand(ProcedureName, connection);
command.CommandType = CommandType.StoredProcedure;
IAsyncResult asyncResult = command.BeginExecuteNonQuery();
}
catch (Exception e)
{
throw e;
}
finally
{
connection.Close();
}
}
}
public static int ExecuteList(List<string> list)
{
int count = 0;
using (MySqlConnection Conn = new MySqlConnection(ConnectionString))
{
Conn.Open();
MySqlCommand cmd = new MySqlCommand();
cmd.Connection = Conn;
MySqlTransaction tx = Conn.BeginTransaction();
cmd.Transaction = tx;
try
{
for (int n = 0; n < list.Count; n++)
{
string sql = list[n];
if (sql.Trim().Length > 1)
{
cmd.CommandText = sql;
count += cmd.ExecuteNonQuery();
}
}
tx.Commit();
return count;
}
catch (Exception e)
{
tx.Rollback();
return 0;
}
}
}
}
public class ExcelToTable
{
public static string sheetName = ConfigurationManager.AppSettings["sheetName"];
/// <summary>
/// 读取excel文件内容并存放在DataSet中.
/// </summary>
/// <returns>返回DataSet对象</returns>
public DataSet ExcelToDS(string path, out string err)
{
DataSet ds = null;
err = "";
try
{
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;"
+ "Data Source=" + @path + ";" + "Extended Properties=Excel 8.0;";
//string strConn = "Provider=Microsoft.Ace.OleDb.12.0;" + "data source=" + @path + ";Extended Properties='Excel 12.0; HDR=Yes; IMEX=1'";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
string strExcel = "";
OleDbDataAdapter myCommand = null;
strExcel = "select * from ["+ sheetName + "$]";
myCommand = new OleDbDataAdapter(strExcel, strConn);
DataTable table1 = new DataTable();
ds = new DataSet();
myCommand.Fill(table1);
myCommand.Fill(ds);
}
catch (Exception ex)
{
err = ex.Message;
}
return ds;
}
}
public class NpoiExport
{
//DataTable导出到Excel，速度非常快！！！！！！！！！！！！！！！！
public static string sheetName = ConfigurationManager.AppSettings["sheetName"];
const int MaximumNumberOfRowsPerSheet = 65500;
protected HSSFWorkbook workbook { get; set; }
public NpoiExport()
{
this.workbook = new HSSFWorkbook();
}
protected ISheet CreateExportDataTableSheetAndHeaderRow(DataTable exportData)
{
var sheet = this.workbook.CreateSheet(sheetName);
// Create the header row
var row = sheet.CreateRow(0);
for (var colIndex = 0; colIndex < exportData.Columns.Count; colIndex++)
{
var cell = row.CreateCell(colIndex);
cell.SetCellValue(exportData.Columns[colIndex].ColumnName);
}
return sheet;
}
public void ExportDataTableToWorkbook(DataTable exportData)
{
// Create the header row cell style
var headerLabelCellStyle = this.workbook.CreateCellStyle();
//headerLabelCellStyle.BorderBottom = CellBorderType.THIN;
var headerLabelFont = this.workbook.CreateFont();
headerLabelCellStyle.SetFont(headerLabelFont);
var sheet = CreateExportDataTableSheetAndHeaderRow(exportData);
var currentNPOIRowIndex = 1;
var sheetCount = 1;
for (var rowIndex = 0; rowIndex < exportData.Rows.Count; rowIndex++)
{
if (currentNPOIRowIndex >= MaximumNumberOfRowsPerSheet)
{
sheetCount++;
currentNPOIRowIndex = 1;
sheet = CreateExportDataTableSheetAndHeaderRow(exportData);
}
var row = sheet.CreateRow(currentNPOIRowIndex++);
for (var colIndex = 0; colIndex < exportData.Columns.Count; colIndex++)
{
var cell = row.CreateCell(colIndex);
cell.SetCellValue(exportData.Rows[rowIndex][colIndex].ToString());
}
}
}
public void ExportDataTableToExcel(DataTable exportData, string filePath)
{
this.ExportDataTableToWorkbook(exportData);
using (FileStream sw = File.Create(filePath, 10000, FileOptions.WriteThrough))
{
this.workbook.Write(sw);
}
}
}
public class DeviceSort
{
public int mocode { get; set; }
public int CategoryID { get; set; }
public string LevelFlag { get; set; }
public string LevelName { get; set; }
public string CodeName { get; set; }
public int LevelIndex { get; set; }
public int ParentCode { get; set; }
public int IsEnd { get; set; }
public int SortIndex { get; set; }
public int Usable { get; set; }
public int Qty_Sum { get; set; }
public decimal Lng { get; set; }
public decimal Lat { get; set; }
public decimal Height { get; set; }
public decimal Heading { get; set; }
public decimal Pitch { get; set; }
public decimal Roll { get; set; }
public decimal Scale { get; set; }
public List<DeviceSort> Children { get; set; }
}
public class sys_device
{
public int id { get; set; }
public int mocode { get; set; }
public int categoryId { get; set; }
public int pId { get; set; }
public int isEnd { get; set; }
public string name { get; set; }
public string codename { get; set; }
}
953
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
