wpf - WPF数据网格导出到excel编码问题
问题描述
我想将数据网格行导出到 Excel 文件中。有些信息是英文的,有些信息是 Perisan 的。我进行了很多搜索并测试了两种不同的方式。第一种方式使用 ApplicationCommands.Copy.Execute 来复制数据。这种方式的问题是波斯语中的字符变为??? (问号),即使我使用 utf-8 编码或任何其他编码。我还使用 Microsoft.Office.Interop.Excel.Application 来复制行。这种方式的问题是只复制了 7 个顶行,但通过这种方式解决了编码问题。第一种方式(https://www.codeproject.com/Questions/354557/WPF-DataGrid-to-Excel)如下:
DgReport.SelectAllCells();
DgReport.ClipboardCopyMode = DataGridClipboardCopyMode.IncludeHeader;
ApplicationCommands.Copy.Execute(null, DgReport);
String resultat = (string)Clipboard.GetData(DataFormats.CommaSeparatedValue);
String result = (string)Clipboard.GetData(DataFormats.Text);
DgReport.UnselectAllCells();
System.IO.StreamWriter file1 = new System.IO.StreamWriter(@"C:\Users\test.csv", false, Encoding.UTF8);
file1.WriteLine(result.Replace(',', ' '));
file1.Close();
第二种方式(https://www.codeproject.com/Questions/678267/Data-Export-from-DataGrid-to-Excel-is-not-working)如下。这种方式只需复制 7 顶行,但编码没有问题。
int i1 = 0;
int k1 = 1, h = 1;
string strFullFilePathNoExt = @"C:\Users\TestExcel4.xls";
var rows = GetDataGridRows(DgReport);
Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel._Workbook ExcelBook;
Microsoft.Office.Interop.Excel._Worksheet ExcelSheet;
ExcelBook = (Microsoft.Office.Interop.Excel._Workbook)ExcelApp.Workbooks.Add(1);
ExcelSheet = (Microsoft.Office.Interop.Excel._Worksheet)ExcelBook.ActiveSheet;
for (i1 = 1; i1 <= DgReport.Columns.Count; i1++)
{
ExcelSheet.Cells[1, i1] = DgReport.Columns[i1 - 1].Header.ToString();
}
foreach (DataGridRow r in rows)
{
DataRowView rv = (DataRowView)r.Item;
foreach (DataGridColumn column in DgReport.Columns)
{
if (column.GetCellContent(r) is TextBlock)
{
TextBlock cellContent = column.GetCellContent(r) as TextBlock;
ExcelSheet.Cells[h + 1, k1] = cellContent.Text.Trim();
k1++;
}
}
k1 = 1;
h++;
}
ExcelApp.Visible = false;
ExcelBook.SaveAs(strFullFilePathNoExt, Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbook, Missing.Value, Missing.Value, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
Microsoft.Office.Interop.Excel.XlSaveConflictResolution.xlUserResolution, true,
Missing.Value, Missing.Value, Missing.Value);
ExcelBook.Close(strFullFilePathNoExt, Missing.Value, Missing.Value);
ExcelSheet = null;
ExcelBook = null;
ExcelApp = null;
}
public IEnumerable<DataGridRow> GetDataGridRows(DataGrid grid)
{
var itemsSource = grid.ItemsSource as IEnumerable;
if (null == itemsSource) yield return null;
foreach (var item in itemsSource)
{
var row = grid.ItemContainerGenerator.ContainerFromItem(item) as DataGridRow;
if (null != row) yield return row;
}
}
我定义我的数据网格的方式如下:
<DataGrid Name="DgReport" IsReadOnly="True" HeadersVisibility="Column" ItemsSource="{Binding}" ScrollViewer.HorizontalScrollBarVisibility="Visible" AutoGenerateColumns="False" SelectionChanged="DgReport_SelectionChanged" FlowDirection="RightToLeft" ></DataGrid>
我填充数据网格的方式是将数据从数据库发送到名为:Reportdt 的数据表,然后
DgReport.DataContext = Reportdt;
DataGridTextColumn c3 = new DataGridTextColumn();
Binding b3 = new Binding("FromNumber");
c3.Binding = b3;
c3.CanUserResize = false;
DgReport.Columns.Add(c3);
任何想法?
解决方案
我以这种方式解决了这个问题:
string strFullFilePathNoExt = @"MyFile_" + DateTime.Now.Year + DateTime.Now.Month + DateTime.Now.Day + DateTime.Now.Hour + DateTime.Now.Minute + ".xls";
var rows = GetDataGridRows(DgReport);
Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel._Workbook ExcelBook;
Microsoft.Office.Interop.Excel._Worksheet ExcelSheet2;
ExcelBook = (Microsoft.Office.Interop.Excel._Workbook)ExcelApp.Workbooks.Add(1);
ExcelSheet2 = (Microsoft.Office.Interop.Excel._Worksheet)ExcelBook.ActiveSheet;
ExcelSheet2.Cells[1, 6] = "Col6";
ExcelSheet2.Cells[1, 5] = "Col5";
ExcelSheet2.Cells[1, 1].Value = " Col1";
ExcelSheet2.Cells[1, 3].Value = "Col3";
ExcelSheet2.Cells[1, 2].Value = "Col2";
ExcelSheet2.Cells[1, 4].Value = "Col4";
for (int Idx = 0; Idx < Reportdt.Rows.Count; Idx++)
{
// ws.Range["A2"].Offset[Idx].Resize[1, dt.Columns.Count].Value = dt.Rows[Idx].ItemArray[14];
ExcelSheet2.Cells[Idx + 2, 6].Value = Reportdt.Rows[Idx].ItemArray[5];
ExcelSheet2.Cells[Idx + 2, 5].Value = Reportdt.Rows[Idx].ItemArray[13];
ExcelSheet2.Cells[Idx + 2, 1].Value = Reportdt.Rows[Idx].ItemArray[14];
ExcelSheet2.Cells[Idx + 2, 1].NumberFormat = "############";
ExcelSheet2.Cells[Idx + 2, 3].Value = Reportdt.Rows[Idx].ItemArray[16];
ExcelSheet2.Cells[Idx + 2, 2].Value = Reportdt.Rows[Idx].ItemArray[18];
//ExcelSheet2.Cells[Idx + 1, 6].Value = Reportdt.Rows[Idx].ItemArray[18];
ExcelSheet2.Cells[Idx + 2, 4].Value = Reportdt.Rows[Idx].ItemArray[17];
}
ExcelApp.Visible = false;
ExcelBook.SaveAs(strFullFilePathNoExt, Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbook, Missing.Value, Missing.Value, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
Microsoft.Office.Interop.Excel.XlSaveConflictResolution.xlUserResolution, true,
Missing.Value, Missing.Value, Missing.Value);
ExcelBook.Close(strFullFilePathNoExt, Missing.Value, Missing.Value);
ExcelSheet2 = null;
ExcelBook = null;
ExcelApp = null;
推荐阅读
- node.js - graphql 函数找不到字体文件
- sql - 如何根据月/年计算发生率
- python - 解析日期字符串后如何获取无关文本?
- spring - 在 Project Reactor 中编写命令式代码
- python - pygame屏幕不刷新
- r-lavaan - 使用 lavaan 运行 EFA 时,如何获得问卷每个项目的社区性?
- r - 使用 Keras 和 Tensorflow 下载 mnist 数据
- r - R函数聚合列表中的所有元素
- python - 使用 Angular 在同一台机器上拒绝 Flask 连接
- angular - .subscribe 中的 router.navigate() 会暂停应用程序的功能