public static void FHDataGrid_btn_导入Excel(string pageTittle, DataGrid dataGrid, List <FHFormLb> listFormResult, ref Microsoft.Office.Interop.Excel.Application application)
{
try
{
int rowDistance = 1;
if (_excelother == null)
{
//创建excel应用程序
_excelother = new Microsoft.Office.Interop.Excel.Application();
}
application = _excelother;
//创建一个excel工作簿
Microsoft.Office.Interop.Excel._Worksheet ws = new Microsoft.Office.Interop.Excel.WorksheetClass();
_excelother.WorkbookBeforeClose += new AppEvents_WorkbookBeforeCloseEventHandler(excelother_WorkbookBeforeClose);
//启用
_excelother.Application.Workbooks.Add(true);
#region 生成数据
int count = 0;
List <string> tittleList = new List <string>();
bool isWait = true;
dataGrid.Dispatcher.BeginInvoke(new System.Action(() =>
{
count = dataGrid.Columns.Count;
foreach (var item in dataGrid.Columns)
{
tittleList.Add(item.Header.ToString());
}
isWait = false;
}));
while (isWait)
{
//生成小标题
for (int i = 1; i < count; i++)
{
_excelother.Cells[1 + rowDistance, i] = tittleList[i];
}
}
////生成数据
PropertyInfo[] propertyInes = typeof(FHFormLb).GetProperties();
for (int x = 1; x < count; x++)
{
for (int y = 1; y < listFormResult.Count; y++)
{
string columnName = tittleList[x];
string columnValue = string.Empty;
foreach (var property in propertyInes)
{
if (property.Name == columnName)
{
if (property.Name == "附件")
{
columnName = listFormResult[y].是否有附件;
break;
}
else
{
columnValue = property.GetValue(listFormResult[y - 1], null).ToString();
break;
}
}
}
_excelother.Cells[y + 1 + rowDistance, x] = columnValue;
}
}
#endregion
#region 设置页面标题
ws = (Microsoft.Office.Interop.Excel._Worksheet)_excelother.ActiveSheet;
Microsoft.Office.Interop.Excel.Range rangTittle = null;
rangTittle = ws.get_Range(ws.Cells[1, 1], ws.Cells[1, count - 1]);
rangTittle.MergeCells = true;
_excelother.Cells[1, 1] = pageTittle;
rangTittle.ColumnWidth = 26;
rangTittle.Font.Size = 17;
rangTittle.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
rangTittle.Borders.LineStyle = XlLineStyle.xlContinuous;
#endregion
#region 单元格属性设置
//设置标题属性
for (int i = 1; i < count; i++)
{
//设置标题单元格属性
Microsoft.Office.Interop.Excel.Range r = ws.get_Range(ws.Cells[1 + rowDistance, i], ws.Cells[1 + rowDistance, i]);
r.Borders.LineStyle = XlLineStyle.xlContinuous;
r.Font.Size = 13;
r.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
string columnHeader = tittleList[i];
if (columnHeader.Contains("日期时间") || columnHeader.Contains("影响范围") || columnHeader.Contains("单位") || columnHeader.Contains("障碍设备") || columnHeader.Contains("线别"))
{
r.ColumnWidth = 20;
}
}
//数据属性
for (int x = 1; x < count; x++)
{
for (int y = 1; y < listFormResult.Count; y++)
{
//设置数据单元格属性
Microsoft.Office.Interop.Excel.Range r = ws.get_Range(ws.Cells[y + 1 + rowDistance, x], ws.Cells[y + 1 + rowDistance, x]);
r.Borders.LineStyle = XlLineStyle.xlContinuous;
r.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
r.RowHeight = 26;
}
}
_excelother.Visible = true;
#endregion
}
catch (Exception ex)
{
MethodLb.CreateLog(typeof(CommonMethod).FullName, "FHDataGrid_btn_导入Excel", ex.ToString());
}
}
/// <summary>
/// 将Datagrid导出到Excel
/// </summary>
/// <param name="tittle">工作簿的名称</param>
/// <param name="datagrid">要导出的列表</param>
public static void Excel_LeadingOut(DataGrid datagrid, Type type)
{
try
{
int rowDistance = 1;
int columnDistance = 1;
//创建excel应用程序
Microsoft.Office.Interop.Excel.Application excelother = new Microsoft.Office.Interop.Excel.Application();
//创建一个excel工作簿
Microsoft.Office.Interop.Excel._Worksheet ws = new Microsoft.Office.Interop.Excel.WorksheetClass();
//启用
excelother.Application.Workbooks.Add(true);
#region 设置页面标题
ws = (Microsoft.Office.Interop.Excel.Worksheet)excelother.ActiveSheet;
Microsoft.Office.Interop.Excel.Range rangTittle = null;
#endregion
#region 生成数据
//生成数据
PropertyInfo[] propertyInes = type.GetProperties();
for (int x = 0; x < datagrid.Columns.Count; x++)
{
for (int y = 0; y < datagrid.Items.Count; y++)
{
var strText = propertyInes[x].GetValue(datagrid.Items[y], null);
if (strText == null)
{
continue;
}
excelother.Cells[y + rowDistance + 1, x + columnDistance] = strText.ToString();
}
}
#endregion
//生成大标题
for (int i = 0; i < datagrid.Columns.Count; i++)
{
rangTittle = ws.get_Range(ws.Cells[rowDistance, i + columnDistance], ws.Cells[rowDistance, i + columnDistance]);
excelother.Cells[rowDistance, i + columnDistance] = datagrid.Columns[i].Header.ToString();
rangTittle.RowHeight = 25;
rangTittle.ColumnWidth = 13;
rangTittle.Font.Size = 17;
rangTittle.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
rangTittle.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
}
excelother.Visible = true;
}
catch (Exception ex)
{
MethodLb.CreateLog(typeof(FrameworkElement).ToString(), "Excel_LeadingOut", ex.ToString(), datagrid, type);
}
finally
{
}
}