Linq 左外连接 导出到Excel 表格
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Threading;
using System.Threading.Tasks;
using System.Windows.Forms;
using Excel;
using Excel02 = Microsoft.Office.Interop.Excel;
namespace 路由评估02基于自定义表
{
public partial class Frm路由评估02基于自定义表 : Form
{
private static string strConnect = @"Data Source = 10.36.136.177;
Initial Catalog = OLTTxRx;
Persist Security Info = True;
User ID = shangweiyu;
Password = Shang@4875/";
/// <summary>
/// The _data table.
/// </summary>
private static DataTable _dataTable;
private static DataTable _table02;
private static DataTable _dt;
private static List<光缆_管道段> listsLst02;
private static readonly OLTTxRxEntities dc = new OLTTxRxEntities();
//新增Form级别的变量，用于取消正在执行的任务
private readonly CancellationTokenSource cancelToken = new CancellationTokenSource();
public Frm路由评估02基于自定义表()
{
InitializeComponent();
}
private void btn获取数据2_Click(object sender, EventArgs e)
{
btn获取数据2.Enabled = false;
Task.Factory.StartNew(() => //Task的Factory属性返回一个TaskFactory对象，
//在调用StartNew（）方法时，传入一个Action<T>委托（这里是lambda表达式）
//指向一个以异步方式调用的方法。
{
数据比对的运算过程(); //使用lambda表达式，来包装一个方法
});
}
private void 数据比对的运算过程()
{
var parOpts = new ParallelOptions();
parOpts.CancellationToken = cancelToken.Token;
parOpts.MaxDegreeOfParallelism = Environment.ProcessorCount;
Action<string> lab = r => Text = "调用方法出现错误：" + r;
var file选中文件的扩展名 = Path.GetExtension(txt文件路径.Text.Trim()).ToLower();
try
{
switch (file选中文件的扩展名)
{
case ".xls":
ExcelDataReader(txt文件路径.Text.Trim(), true);
break;
case ".xlsx":
ExcelDataReader(txt文件路径.Text.Trim(), false);
break;
default:
MessageBox.Show("选择文件错误！必须是Excel文件！！！");
return;
}
求交集();
ExportToExcel(listsLst02);
}
catch (OperationCanceledException ex)
{
Invoke(lab, ex.Message);
return;
}
catch (Exception exception)
{
Invoke(lab, exception.Message);
return;
}
MessageBox.Show("执行完毕，并导出数据！", "完成了", MessageBoxButtons.OK, MessageBoxIcon.Information);
Action<Button> hhAction = r => { r.Enabled = true; };
btn获取数据2.Invoke(hhAction, btn获取数据2);
}
/// <summary>
/// The export to excel.
/// </summary>
/// <param name="lst">
/// The lst.
/// </param>
/// <typeparam name="T">
/// </typeparam>
private void ExportToExcel<T>(IEnumerable<T> lst)
{
var propertys = typeof(T).GetProperties();
// 加载Excel，新建一个空的工作拨
var excelApp = new Excel02.Application
{
// 使Excel可见
Visible = true
};
excelApp.Workbooks.Add();
// 定义一个工作表
Excel02._Worksheet worksheet = excelApp.ActiveSheet;
var ch = 'A';
var i = 0;
// 在单元格中创建列头
foreach (var pi in propertys)
{
if (pi.Name.Equals("id")) continue;
worksheet.Cells[1, ((char) (ch + i)).ToString()] = pi.Name;
i++;
}
// worksheet.Cells[1, "A"] = "管孔ID";
// worksheet.Cells[1, "B"] = "管孔序号";
// worksheet.Cells[1, "C"] = "管道段ID";
// worksheet.Cells[1, "D"] = "管道段名称";
// worksheet.Cells[1, "E"] = "类型ID";
// worksheet.Cells[1, "F"] = "类型名称";
// worksheet.Cells[1, "G"] = "光缆段ID";
// worksheet.Cells[1, "H"] = "光路ID";
// worksheet.Cells[1, "I"] = "光路编码";
// worksheet.Cells[1, "J"] = "相同管道段ID的数量";
// 现在将lst 中的数据映射到工作表中
var row = 1;
foreach (var c in lst)
{
row++;
ch = 'A';
i = 0;
foreach (var pi in propertys)
{
if (pi.Name.Equals("id")) continue;
worksheet.Cells[row, ((char) (ch + i)).ToString()] = pi.GetValue(c);
i++;
}
// worksheet.Cells[row, "A"] = c.光路ID c.管孔ID;
// worksheet.Cells[row, "B"] = c.管孔序号;
// worksheet.Cells[row, "C"] = c.管道段ID;
// worksheet.Cells[row, "D"] = c.管道段名称;
// worksheet.Cells[row, "E"] = c.类型ID;
// worksheet.Cells[row, "F"] = c.类型名称;
// worksheet.Cells[row, "G"] = c.光缆段ID;
// worksheet.Cells[row, "H"] = c.光路ID;
// worksheet.Cells[row, "I"] = c.光路编码;
// worksheet.Cells[row, "J"] = c.相同管道段ID的数量;
}
// 美化表数据
worksheet.Range["A1"].AutoFormat(Excel02.XlRangeAutoFormat.xlRangeAutoFormatClassic2);
// worksheet.SaveAs(string.Format(@"{0}\Inventory.xlsx", Environment.CurrentDirectory));
// excelApp.Quit();
// MessageBox.Show("导出记录完毕");
}
private static void ExcelDataReader(string 文件路径, bool 是03版Excel文件吗)
{
IExcelDataReader excelReader = null;
var stream = File.Open(文件路径, FileMode.Open, FileAccess.Read);
if (是03版Excel文件吗)
excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
else
excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
// excelReader.IsFirstRowAsColumnNames = true;
var result = excelReader.AsDataSet();
_dataTable = result.Tables[0];
// var q = (from r in _dt.AsEnumerable()
// select r).ToList();
// q =
// Console.WriteLine(_dt.Rows[0][0]);
// 2. Reading from a OpenXml Excel file (2007 format; *.xlsx)
// IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
// 3. DataSet - The result of each spreadsheet will be created in the result.Tables
// DataSet result = excelReader.AsDataSet();
// 4. DataSet - Create column names from first row
// excelReader.IsFirstRowAsColumnNames = true;
// DataSet result = excelReader.AsDataSet();
// 5. Data Reader methods
// while (excelReader.Read())
// {
// //excelReader.GetInt32(0); //此处要调试查看
// string str = excelReader.GetString(1);
// string str1 = excelReader.GetString(2);
// string str2 = excelReader.GetString(3);
// }
// string filePath = @"D:\搜狗高速下载\中继光缆-管道段资料\95.xls";
// 6. Free resources (IExcelDataReader is IDisposable)
excelReader.Close();
}
private void btnOpenFile_Click(object sender, EventArgs e)
{
var openFileDialog1 = new OpenFileDialog
{
Filter = "Excel文件|*.xls?",
RestoreDirectory = true,
FilterIndex = 1,
SupportMultiDottedExtensions = true
};
// openFileDialog1.Multiselect = true;
openFileDialog1.ShowDialog();
txt文件路径.Text = openFileDialog1.FileName.Trim();
var file选中文件 = openFileDialog1.FileName; // 带全路径
var file选中文件的扩展名 = Path.GetExtension(file选中文件).ToLower();
if (file选中文件的扩展名 == ".xls")
ExcelDataReader(txt文件路径.Text.Trim(), true);
else if (file选中文件的扩展名 == ".xlsx")
ExcelDataReader(txt文件路径.Text.Trim(), false);
else
MessageBox.Show("选择文件错误！必须是Excel文件！！！");
}
private void btn获取数据_Click(object sender, EventArgs e)
{
// strSQL：获取指定表的全部列名称
var strSql = $@"SELECT
COLUMN_NAME
FROM INFORMATION_SCHEMA.columns
WHERE TABLE_NAME = {txt表名.Text.Trim()}"; // COLUMN_NAME,DATA_TYPE
求交集(strSql, false);
}
/// <summary>
/// The 求交集.
/// </summary>
/// <param name="strSQL">
/// The str sql.
/// </param>
/// <param name="是本地吗">
/// The 是本地吗.
/// </param>
private void 求交集(string strSQL = "", bool 是本地吗 = true)
{
var bb = true;
// 获取指定数据库指定表的字段名称
if (!是本地吗)
{
/* using (var conn远程目标 = new SqlConnection(strConnect))
{
var command = new SqlCommand(strSQL, conn远程目标) {CommandTimeout = 1000};
var dataAdapter = new SqlDataAdapter(command);
_dataTable = new DataTable();
_dataTable.Clear();
dataAdapter.Fill(_dataTable);
for (int i = 0; i < _dataTable.Rows.Count; i++)
{
string 列名 = _dataTable.Rows[i][0].ToString().Trim();
for (int j = i + 1; j < _dataTable.Rows.Count; j++)
{
string 列名02 = _dataTable.Rows[j][0].ToString().Trim();
// strSQL02：找到2个列名称对应的记录的交集，与表光缆_管道段1122
// 找出对应的记录带出更多信息，使用GROUP BY 是为了去掉重复记录
string strSql02 = $@"SELECT
MAX([管道段编码])
,max([管道段名称])
,max([所属区域])
,max([所属局站])
,[管道段ID]
,max([光缆编码])
,'{列名}-->{列名02}'
FROM [OLTTxRx].[dbo].[光缆_管道段]
WHERE [管道段ID] IN(
SELECT
{列名}
FROM {txt表名.Text.Trim()} AS t1
WHERE (
{列名}
IN (SELECT
{列名02}
FROM {txt表名.Text.Trim()} AS t2)
))
GROUP BY [管道段ID] ";
command.CommandText = strSql02;
dataAdapter.SelectCommand = command;
_table02 = new DataTable();
_table02.Clear();
dataAdapter.Fill(_table02);
if (bb)
{
_dt = _table02.Clone();
bb = false;
}
_dt.Merge(_table02);
}
}
dataGridView1.DataSource = _dt;
}*/
}
else
{
//txt表名.Text = string.Empty; // _dataTable.TableName;
var listsLst = new List<List<string>>();
listsLst02 = new List<光缆_管道段>();
listsLst02.Clear();
for (var i = 0; i < _dataTable.Columns.Count; i++)
{
var q = from r in _dataTable.AsEnumerable()
// where r.Field<double>(0)!=null
select r.ItemArray[i].ToString().ToLower().Trim();
var q002 = q.Where(r => r != "null" && !string.IsNullOrEmpty(r)).ToList();
listsLst.Add(q002); // 存放表的列集合数据，按列切分成集合
}
var qq = dc.光缆_管道段.ToList(); //放在这里是为了减少对数据库的访问次数
//var q02 = new List<光缆_管道段>();
for (var i = 0; i < listsLst.Count; i++)
for (var j = i + 1; j < listsLst.Count; j++)
{
//q02.Clear();
var q = listsLst[i].Intersect(listsLst[j]).Distinct().ToList();
//if (q.Contains("9999"))
//{
// int jjjj = 0;//调试代码
//}
try
{
var q02 = (from r in q
join r002 in qq
on r equals r002.管道段ID.ToString().Trim() into Joined光缆_管道段 //【注意】必须使用into子句，才能是左外连接
from r02 in Joined光缆_管道段.DefaultIfEmpty(//对于在左外连接中，右边的表没有匹配上的记录，如何默认赋值的设置
new 光缆_管道段
{//对于在左外连接中，右边的表没有匹配上的记录，如何默认赋值的设置
比较参数 = "",
id = 0,
管道段编码 = "",
管道段名称 = "",
所属区域 = null,
所属局站 = null,
管道段ID = 0,
光缆编码 = null
}
)
select new 光缆_管道段 //注意这里的【 光缆_管道段】拿掉就是匿名类，不拿到就是命名类
{
比较参数 = "",
id = r02.id,
管道段编码 = r02.管道段编码,
管道段名称 = r02.管道段名称,
所属区域 = r02.所属区域,
所属局站 = r02.所属局站,
管道段ID = double.Parse(r), //此处的字段赋值用的是另一个表与其它不同
光缆编码 = r02.光缆编码
}).Distinct(new bean_IEqualityComparer()).ToList();
//MessageBox.Show(q022.ToString());
if (q02.Count > 0)
{
foreach (var item in q02)
{
item.比较参数 = $"{listsLst[i][0]}--->{listsLst[j][0]}";
}
listsLst02.AddRange(q02);
}
}
catch (Exception e)
{
Console.WriteLine(e);
}
}
listsLst02.Sort();
Action<List<光缆_管道段>, DataGridView> gl = (r, r2) => { r2.DataSource = r; };
dataGridView1.Invoke(gl, listsLst02, dataGridView1);
}
}
private void btn取消执行_Click(object sender, EventArgs e)
{
cancelToken.Cancel(); //停止所有工作者线程
MessageBox.Show("取消操作成功！");
btn获取数据2.Enabled = true;
}
}
}
