使用VB.NET应用程序将HTML表从剪贴板粘贴到Excel工作表 [英] Pasting HTML table from clipboard to Excel worksheet with VB.NET Application

问题描述

我有一个具有7000行和60列的 DataTable ，我需要将其保存在Excel文件中。

I have a DataTable with 7000 rows and 60 columns, and I need to save it in an Excel file.

循环通过 DataTable 需要很多时间， ClosedXml libary引发了一个我无法解决的异常（多个星期）。因此，我决定从 DataTable 创建一个HTML表，将其添加到剪贴板并将其粘贴到网络上的通用Excel文件中。

Looping through the DataTable takes to much time, and the ClosedXml libary is raising an exception that I could not solve (for many weeks). So I have decided to create an HTML table from the DataTable, add it to the clipboard and paste it to the generic Excel file on the network.

问题是所有的字符串都是用希伯来语写的，当应用程序粘贴数据时，所有的字符串都不能正确编码，而Excel会将它们显示为问号。 strong>

the problem is that all the strings are written in Hebrew, and when the app is pasting the data all the strings don't encode properly, and Excel presents them as question marks.

注意：当我使用鼠标粘贴应用程序在剪贴板上设置的数据时，应该像这样粘贴数据，没有问号。
此外，我还尝试使用 PasteSpecial（）方法的所有枚举参数，而不成功。
请参阅我添加的相关代码部分。

Note: When I paste the data that the application set on the clipboard using my mouse the data is pasted like it should, without question marks. Also I have tried to use all the Enum parameters of the PasteSpecial() method without success. Please see the relevant code parts I have added.

 Public Function LoadDataTableToDivisinReport(ByVal d As DataTable, ByVal pathAndFileNameToSave As String, ByVal DbsheetNameToReplaceData As String, Optional ByVal pathToSaveImages As String = "") As Boolean
........

                Dim t As Threading.Thread
                t = New System.Threading.Thread(AddressOf createHtmlTableToClipBoard)
                t.SetApartmentState(Threading.ApartmentState.STA)
                t.Start()
                ws.Range("a1").PasteSpecial(Excel.XlPasteType.xlPasteFormats)

.........
End Function


    Private Sub createHtmlTableToClipBoard()

        Dim b As New StringBuilder
        b.Append("<table>")
        b.Append("<tr>")
        For i = 0 To dd.Columns.Count - 1
            b.Append("<th>" & dd.Columns(i).ColumnName & "</th>")

        Next
        b.Append("</tr>")



        For j = 0 To dd.Rows.Count - 1
            b.Append("<tr>")
            For i = 0 To dd.Columns.Count - 1
                b.Append("<td>")
                b.Append(dd.Rows(j).Item(i).ToString)
                b.Append("</td>")
            Next
            b.Append("</tr>")
        Next

        b.Append("</table>")

        Clipboard.SetText(b.ToString())

    End Sub

    Private dd As DataTable

更新：
感谢@ClearLogic注释问题已解决，通用工作簿中的宏是导致该行为的原因。请参阅下面的讨论@ClearLogic标记的答案。

UPDATE: thanks to @ClearLogic comment the issue was solved, macros in the generic workbook was the cause for that behavior. Please see the discussion below @ClearLogic marked answer.

推荐答案

我会推荐 EPPlus 。它可用于nuget.Very易于使用。

I would recommend EPPlus. Its available on nuget.Very easy to use.

这是C＃中的一个工作的代码片段。使用（ExcelPackage excelFile = new ExcelPackage（new FileInfo（fileName）））

Here is a working Code snippet in c#. Translation to vb.net would not be difficult

using (ExcelPackage excelFile = new ExcelPackage(new FileInfo(fileName)))
{
    ExcelWorksheet ws = excelFile.Workbook.Worksheets.Add("Sheet1");
    ws.Cells["A1"].LoadFromDataTable(datatable, true);
    for (int i = 1; i <= datatable.Columns.Count; i++)
    {
        ws.Column(i).Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Left;
        ws.Column(i).AutoFit();
    }

    ws.Row(1).Style.Font.Bold = true;
    excelFile.Save();
}

修改
Vb.net代码

Edit Vb.net Code

Using excelFile As New ExcelPackage(New FileInfo(fileName))
        Dim ws As ExcelWorksheet = excelFile.Workbook.Worksheets.Add("Sheet1")
        ws.Cells("A1").LoadFromDataTable(datatable, True)
        For i As Integer = 1 To datatable.Columns.Count
            ws.Column(i).Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Left
            ws.Column(i).AutoFit()
        Next

        ws.Row(1).Style.Font.Bold = True
        excelFile.Save()
    End Using

这篇关于使用VB.NET应用程序将HTML表从剪贴板粘贴到Excel工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！